· 6 years ago · Aug 10, 2019, 06:28 PM
1alter table "dbContExt".tbComPro add column pw_uppercase boolean null ;
2 alter table "dbContExt".tbComPro add column pw_lowercase boolean null ;
3 alter table "dbContExt".tbComPro add column pw_numeric boolean null ;
4 alter table "dbContExt".tbComPro add column pw_special_char boolean null ;-- !@#$%^&*()_+......
5 alter table "dbContExt".tbComPro add column pw_length int null; -- day
6 alter table "dbContExt".tbComPro add column pw_change_every int null; -- day
7 alter table "dbContExt".tbComPro add column pw_LoginVerify "dbUserReg".tpLoginVerify null ;
8
9
10
11
12
13 /**************************
14 Function :Get login verify
15 Wut, 2019-07-26
16 *******************************/
17create or replace function "dbContExt".tbcomppro_get_loginverify( in accid int, out _loginverify text )
18returns text as $$
19begin
20 select a.pw_loginverify from "dbContExt".tbComPro a where a.acc = accid into _loginverify;
21
22end $$ language plpgsql stable;
23
24-- select * from "dbContExt".tbComPro;
25
26
27
28
29 /******************************
30 * Trigger :
31 * Wut, 2018-07-30
32
33
34 select * from "dbContact".tbaddr;
35
36 ******************************/
37create or replace function "dbContExt".tbcompro_sync_loginverfy()
38returns trigger as $$
39
40begin
41 if diff(new.pw_LoginVerify,old.pw_LoginVerify ) then
42 execute $x$
43 update "dbUserReg".tbuserreg set LoginVerify = $1.pw_LoginVerify
44 where acc= $1.acc and
45 case
46 when LoginVerify is null then true
47 when equal($1.pw_LoginVerify, loginverify) then true
48 else false
49 end
50 $x$ using new;
51
52 end if;
53 return new;
54end $$ language plpgsql;
55
56drop trigger if exists tbaddr_0RefreshAddr on "dbContExt".tbcompro;
57drop trigger if exists tbaddr_0RefreshAddr on "dbContExt".tbcompro;
58create trigger tbaddr_0RefreshAddr
59 after update on "dbContExt".tbcompro
60 -- after insert or update on "dbContExt".tbcompro
61 for each row execute procedure "dbContExt".tbcompro_sync_loginverfy();
62
63
64
65/****************************************************************
66 View : Company Profile
67
68 Wut, 2017-02-13
69
70*****************************************************************/
71
72 -- * Data selected for underlying user
73drop function if exists "dbContExt".tbComProVW_UserData ( in UserID int , in AccID int
74 ,in _id int
75 ,in _idgrp int
76 ) cascade;
77create or replace FUNCTION "dbContExt".tbComProVW_UserData ( in UserID int , in AccID int
78 ,in _id int =null
79 ,in _idgrp int = null
80 )
81returns table (
82 ID int
83 ---------------
84 ,ContactID int -- ID , NEVER used by this view but for compatible(called by ohter ). It will be overrided by its descendant
85 ----------------------------------------
86 ,Prefix varchar ,FirstName varchar ,LastName varchar ,suffix varchar
87 ,PrefixEng varchar, FirstNameEng varchar, LastNameEng varchar, SuffixEng varchar
88 ,StatNames text
89 ,CitizenID varchar, TaxID varchar
90 ,PhoneWork varchar, PhoneWorkExt varchar
91 ,PhoneHome varchar, PhoneHomeExt varchar
92 ,PhoneResident varchar,PhoneResidentExt varchar
93 ,Mobile1 varchar, Mobile2 varchar
94 ,FaxWork varchar, FaxHome varchar, FaxResident varchar
95 ,EmailHome varchar, URLHome varchar
96 ,AddrHome varchar ,AddrWork varchar ,AddrResident varchar
97 ,AddrHomeID int ,AddrWorkID int ,AddrResidentID int
98 ,Groups text
99 ,Note text
100 ,token text
101 ,filetype text
102 ,UID int ,ACC int
103 ,hoid int
104 --------------------
105 ,BirthDay timestamp_wtz
106 ,Job text
107 -- Extend -------------------
108 ,v_enable boolean
109 ,v_inc boolean
110 ,adAutoPrefix boolean
111 ,adprefixfull boolean
112 --* Receipt ------------
113 ,isTaxInv boolean
114 ,isTaxInv_ReceiptNo boolean
115 --* security
116 ,pw_uppercase boolean
117 ,pw_lowercase boolean
118 ,pw_numeric boolean
119 ,pw_special_char boolean -- !@#$%^&*()_+......
120 ,pw_length int
121
122 ,pw_change_every int -- day
123
124 ,pw_loginverify "dbUserReg".tpLoginVerify
125)
126 AS $$
127declare sql text; havemv boolean;
128begin
129
130 return query execute $x$
131 select
132 a.id
133 ---------------------------------------
134 ,b.*
135 --- Extend ----------------------------
136 ,null::timestamp_wtz birthday
137 ,null::text job
138 -----------------------
139 ,a.v_enable
140 ,a.v_inc
141 ,a.adAutoPrefix
142 ,a.adprefixfull
143 ,a.isTaxInv -- boolean
144 ,a.isTaxInv_ReceiptNo --boolean
145 --* security --------------------
146 ,a.pw_uppercase
147
148 ,a.pw_lowercase
149 ,a.pw_numeric
150 ,a.pw_special_char -- !@#$%^&*()_+......
151 ,a.pw_length
152
153 ,a.pw_change_every -- day
154 ,a.pw_loginverify
155
156 from "dbContExt".tbcompro a
157 left join "dbContact".tbContact_GetData(
158 1 -- %s
159 , $2
160 ) b on b.id = a.contactid
161 where
162 case
163 when $1 is null then a.acc = $2
164 else a.id = $1
165 end
166 $x$ using _id,accid
167;
168
169end $$ LANGUAGE plpgsql stable;
170
171/**********************************************************************
172 Function : How to update underlying tables whose data is projected to this view
173 Caution : DO NOT use 'id',but 'ContactID'. 'id'will be overrided by its descendant
174 Wutikrai
175 21-06-2014
176
177**1*********************************************************************/
178drop function if exists "dbContExt".tbComProVW_upddata() cascade;
179create or replace function "dbContExt".tbComProVW_upddata()
180returns trigger as $$
181 declare sql text;
182 sql_datachanged text = tg_argv[2]; changed boolean;
183begin
184 if (TG_OP = 'INSERT') then
185
186 if new.v_inc is null then
187 new.v_inc :=false;
188 end if;
189 if new.v_enable is null then
190 new.v_enable :=true;
191 end if;
192
193 if new.adprefixfull is null then
194 new.adprefixfull:=false ;
195
196 end if;
197
198 if new.isTaxInv is null then
199 new.isTaxInv = true;
200 if new.acc = 4 then -- backward compat for test case
201 new.isTaxInv = false;
202 end if;
203 end if;
204 if new.isTaxInv_ReceiptNo is null then
205 new.isTaxInv_ReceiptNo = true;
206 if new.acc = 4 then -- backward compat for test case
207 new.isTaxInv_ReceiptNo = false;
208 end if;
209 end if;
210
211 --*
212 insert into "dbContExt".tbcompro (
213 ContactID
214
215 ,v_enable
216 ,v_inc
217 , UID, ACC
218 ,adAutoPrefix
219 ,adprefixfull
220 ,isTaxInv
221 ,isTaxInv_ReceiptNo
222 --*
223 ,pw_uppercase
224 ,pw_lowercase
225 ,pw_numeric
226 ,pw_special_char -- !@#$%^&*()_+......
227 ,pw_length
228
229 ,pw_change_every -- day
230
231 ,pw_loginverify
232
233 )
234 select
235 new.ContactID
236
237 ,new.v_enable
238 ,new.v_inc
239 ,new.UID, NEW.ACC
240 ,new.adAutoPrefix
241 ,new.adprefixfull
242 ,new.isTaxInv
243 ,new.isTaxInv_ReceiptNo
244
245 ,new.pw_uppercase
246 ,new.pw_lowercase
247 ,new.pw_numeric
248 ,new.pw_special_char -- !@#$%^&*()_+......
249 ,new.pw_length
250 ,new.pw_change_every -- day
251 ,new.pw_loginverify
252
253 returning ID into new.id;
254
255 return NEW;
256 elseif (TG_OP = 'UPDATE') then
257 sql := null;
258
259 -- sql := "dbSys".sqlSetValue( OLD.firstname, NEW.firstname, sql, 'firstname' );
260
261
262 sql := "dbSys".sqlSetValue( OLD.v_enable, NEW.v_enable, sql, 'v_enable' );
263 sql := "dbSys".sqlSetValue( OLD.v_inc, NEW.v_inc, sql, 'v_inc' );
264 sql := "dbSys".sqlSetValue( OLD.adAutoPrefix, NEW.adAutoPrefix, sql, 'adAutoPrefix' );
265 sql := "dbSys".sqlSetValue( OLD.adprefixfull, NEW.adprefixfull, sql, 'adprefixfull' );
266
267 sql := "dbSys".sqlSetValue( OLD.isTaxInv, NEW.isTaxInv, sql, 'isTaxInv' );
268 sql := "dbSys".sqlSetValue( OLD.isTaxInv_ReceiptNo, NEW.isTaxInv_ReceiptNo, sql, 'isTaxInv_ReceiptNo' );
269
270 sql := "dbSys".sqlSetValue( OLD.pw_uppercase, NEW.pw_uppercase, sql, 'pw_uppercase' );
271 sql := "dbSys".sqlSetValue( OLD.pw_lowercase, NEW.pw_lowercase, sql, 'pw_lowercase' );
272 sql := "dbSys".sqlSetValue( OLD.pw_numeric, NEW.pw_numeric, sql, 'pw_numeric' );
273 sql := "dbSys".sqlSetValue( OLD.pw_special_char, NEW.pw_special_char, sql, 'pw_special_char' );
274 sql := "dbSys".sqlSetValue( OLD.pw_length, NEW.pw_length, sql, 'pw_length' );
275 sql := "dbSys".sqlSetValue( OLD.pw_change_every, NEW.pw_change_every, sql, 'pw_change_every' );
276 sql := "dbSys".sqlSetValue( OLD.pw_loginverify, NEW.pw_loginverify, sql, 'pw_loginverify' );
277
278
279
280
281 --* Any change of MV,force update
282
283 --raise exception '224 sql=%',sql;
284 if sql is not null then
285 -- raise exception '226 ok sql=%', sql;
286 sql := 'update "dbContExt".tbcompro ' || sql ||
287 ' where id = '|| NEW.id ;
288 -- raise exception '224 sql=%',sql;
289 execute sql ;
290 end if;
291
292 --* Sync to registration
293 if diff( new.firstname, old.firstname ) then
294 update "dbUserAcc".tbacc set title = concat_ws(' ',new.prefix, new.firstname, new.lastname, new.suffix )
295 where id = new.acc ;
296
297 end if;
298 return NEW;
299 elseif TG_OP = 'DELETE' then
300
301 delete from "dbContExt".tbcompro where id = old.id ;
302 --return OLD;
303 return null;
304 end if;
305end ; $$ LANGUAGE plpgsql ;
306
307/*****************************
308 View registration
309******************************/
310select "dbSys".viewRegistration( '"dbContExt".tbComProVW'
311 --, _delete_system_data := true
312 -- ,_init_data := true, _init_data_uid := varint('userid') ,_init_data_acc := varint('accid')
313
314
315 ,_matview :=false
316 -- ,_mvAccID_share := 4
317 ,_mvOrderBy := 'firstname, lastname'
318 ,_mvStrict := false -- Check data is in scope of view when insert
319 ,_mvIndInh := true
320 ,_mvAncestor := '"dbContact".tbcontactvw'
321 ,_mvAncPK := 'contactid' -- Data field for ancestor PK
322 ,_mvupd_byanc :=true
323
324
325);
326
327/************************************************************
328 Registration : inherite the group of dbContExt
329*************************************************************/
330-- select "dbSys".tblRegistration_Inherit( '"dbInventory".tbStockVW', '"dbInventory".tbInvenVW',true,false,true );
331--select "dbSys".tblRegistration_Inherit( '"dbContExt".tbComProVW', '"dbContact".tbContactVW',false,false,true );
332
333
334/********************************************************
335 Logg Handling : Register for logging
336
337 --** This kind of log table have view as same name as persistent **--
338 select * from "dbSys".tbviewregistration where nam = 'tbComProvw_log';
339
340 select * from "dbSys".tbtblregis where id = (
341 select tblregis_id from "dbSys".tbviewregistration where nam = 'tbComProvw_log'
342 )
343********************************************************/
344 /*
345select "dbSys".logRegistration( '"dbContExt".tbComProVW'
346 ,'prefix, firstname, lastname, suffix' -- No longer need below trigger, 2015-04-22, Wut.
347 );
348
349*/
350
351/************************
352 Creat view
353*******************************/
354-- select * from "dbSys".tvcreateview( varint('userid'), varint('accid'), '"dbContExt".tbComPro', dropit:=true );
355
356
357-- select * from "dbContExt".tbComProVW4;
358
359
360
361
362/************************
363 function : Default loginverify using company configut
364 Wut, 2019-07-26
365
366*****************************/
367
368drop function if exists "dbUserReg".tbuserreg_loginverify() cascade;
369create or replace function "dbUserReg".tbuserreg_loginverify() returns trigger as $$
370declare ok boolean; rec record;
371begin
372 if new.LoginVerify is null then
373 if "dbContExt".tbcomppro_get_loginverify(new.acc) is not null then
374 new.LoginVerify = "dbContExt".tbcomppro_get_loginverify(new.acc);
375 end if;
376 end if;
377 return new;
378end $$ language plpgsql;
379
380drop trigger if exists tbuserreg_loginverify on "dbUserReg".tbuserreg cascade;
381create trigger tbuserreg_loginverify
382 before insert on "dbUserReg".tbuserreg
383 for each row execute procedure "dbUserReg".tbuserreg_loginverify();