· 6 years ago · Jul 22, 2019, 01:54 PM
1create or replace function "dbSys".sqlAddWhere(
2 in sql text
3 ,in cond text
4 ,in automatch boolean = false
5 ,in multi boolean = false
6 ,in OrderBy_GroupBy boolean = false
7 )
8returns text as $$
9declare where_clause text; p int; sq boolean; sta text[]; st text;
10 st1 text;qc text;
11 fn text; vl text;
12
13 --o int; g int;
14 o record; g record;
15begin
16 if automatch then
17 if position('%' in cond )>0 then
18 cond := replace(cond,'=' , ' like ' );
19 multi := false;
20 end if;
21 end if;
22 if multi then
23 -- raise notice '743';
24 if position(',' in cond ) > 0 then
25 fn := trim(cutstr(cond, '=' ));
26 fn := trim(trim(fn,'='));
27
28 vl := trim(movstr(cond, '=' ));
29
30 sq := case
31 when position(chr(39) in vl) >0 then true
32 else false
33 end ;
34 if sq then
35 vl:=replace(vl,chr(39),'');
36 end if;
37
38 -- raise notice '756, vl=%s=',vl;
39
40 qc := case
41 when sq then chr(39)
42 else ''
43 end ;
44 sta := string_to_array(vl, ',' );
45 st1 := null;
46 foreach st in array sta loop
47 st1 := concat_del(',',st1, format('%s%s%s', qc,st,qc) );
48 --raise notice '42,st1=%=',st1;
49 end loop;
50 cond := format('%s in ( %s )',fn,st1);
51
52 end if;
53 end if;
54 --* Where position
55
56 p:= position('where' in sql ) ;
57
58 if p > 0 then
59 select p_last from postition_set('from where',sql) into p;
60 -- raise '0060, p=%', p; -- 22187
61 if p > 0 then
62 p:=p-5;
63 end if;
64 end if;
65
66 --* Group by/Order by poition , if require
67 if (sql is not null) and OrderBy_GroupBy then
68 select p_first,p_last,s_mid from postition_set('order by' , sql ) into o;
69 --raise notice '74, o.p_last %, o.p_first % length_order %, o.s_mid>%<,emptystr(o.s_mid)=%', o.p_last, o.p_first ,length('order'), o.s_mid, emptystr(o.s_mid);
70 --if o.p_last = o.p_first + length('order')-1 then -- not found "by"
71 --if not emptystr(o.s_mid) then
72 if (o.p_last = o.p_first + length('order')-1) or -- no "by"
73 (o.p_last > (o.p_first + length('order') + 1 + 2 )) then -- "By" of other
74 o.p_first:=0;
75 end if;
76 select p_first,p_last from postition_set('group by' , sql ) into g;
77 if g.p_last = g.p_first + length('group')-1 then -- not found "by"
78 --if not emptystr(g.s_mid) then
79 g.p_first:=0;
80 end if;
81 --raise notice '82 o.p_first %, g.p_first %', o.p_first, g.p_first;
82 else
83 --o:=0; g := 0;
84 select 0::int p_first into o;
85 select 0::int p_first into g;
86 end if;
87
88 --*
89 if (not OrderBy_GroupBy) or (o.p_first=0 and g.p_first=0) then
90 -- raise '0090';
91 if p > 0 then
92 where_clause := split_part(sql,'where',2 );
93 -- raise '0093: where_clause %', where_clause;
94
95 if (where_clause is null) or emptystr(where_clause) then
96 -- leave it
97
98 else
99 sql:= sql||' and ';
100 end if;
101 else
102 -- sql:= sql||' where ';
103 sql := concat_ws('',sql,' where ' );
104 end if;
105 -- sql := sql || ' '|| cond;
106 sql := concat_ws(' ',sql,cond );
107 -- raise '0107:sql=%',sql;
108 else
109 -- raise notice '884 o=%, g=%' , o, g;
110 if p > 0 then
111 if (o.p_first=0) and (g.p_first=0) then
112 where_clause := split_part(sql,'where',2 );
113 if (where_clause is null) or emptystr(where_clause) then
114 sql := concat_ws(' ',sql,cond );
115 end if;
116 else
117 --raise notice '87, sql=%, cond =%, o.p_first %, g.p_first %',sql, cond, o.p_first, g.p_first;
118 if (o.p_first=0) and (g.p_first=0) then
119 sql:= sql||' and ';
120 sql := concat_ws(' ',sql,cond );
121 elseif (o.p_first>0) and (g.p_first=0) then
122 sql = InsertStr(sql, ' and ' || cond || ' ', o.p_first-1 );
123 elseif (o.p_first=0) and (g.p_first>0) then
124 -- raise notice '94, and + cond +SP = %, sql=%,g-1=% ' and ' || cond || ' ', sql, g-1;
125
126 sql = InsertStr(sql, ' and ' || cond || ' ', g.p_first-1 );
127 else
128 sql = InsertStr(sql, ' and ' || cond || ' ', o.p_first-1 );
129 end if;
130 -- raise notice '98 ,sql=%',sql;
131 end if;
132 else
133 if (o.p_first=0) and (g.p_first=0) then
134 sql := concat_ws(' ',sql,' where ' || cond );
135 else
136 if (o.p_first=0) and (g.p_first=0) then
137 sql:= sql||' where ';
138 sql := concat_ws(' ',sql,cond );
139 elseif (o.p_first>0) and (g.p_first=0) then
140 sql = InsertStr(sql, ' where ' || cond || ' ', o.p_first-1 );
141 elseif (o.p_first=0) and (g.p_first>0) then
142 sql = InsertStr(sql, ' where ' || cond || ' ', g.p_first-1 );
143 else
144 sql = InsertStr(sql, ' where ' || cond || ' ', o.p_first-1 );
145 end if;
146
147 end if;
148 end if;
149
150 end if;
151 return sql;
152end $$ language plpgsql;
153
154
155select "dbSys".viewRegistration( '"dbLegal".tbCaseTrn010VW'
156 , pers := '"dbLegal".trcasetran'
157 --, _delete_system_data := true
158 -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := 4 , _init_sysorg := true
159 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null , _init_sysorg := true
160
161 ,_fts := true
162 ,_ftsfnvalexc := 'projectid,DebtCalcID,PeriodID,taskidgrp,count_event,count_asset,count_org,count_dc,count_doc,count_amt,count_gua,count_calc,post_inout,post_casestat,casekind,NoDefProf,create_require_event_by_dc,updateamount
163
164 CustName t ext
165 ,Defendant
166 ,CourtRole
167
168 ,ReceiverName text
169
170 ,OrgName text
171
172 ,LawOfficerName text
173 ,lawofficer_contactid
174
175 ,LawOfficeName
176
177 ,LawyerName
178
179 ,LGTypeName
180
181 ,ProfileName
182
183 ,ActiveStepName
184
185 ,UID int
186 ,ACC int
187 ,Status text
188
189 ,CaseComplete
190 ,CaseComplete_date
191
192 ,CourtName
193 ,CourtAreaName
194 ,sue_date
195
196 ,c_defd
197
198 ,CaseStatusName
199
200 ,Defendant1_Name
201
202
203 ,keyword
204
205 ,auto_copy
206 ,casekind
207 ,assign_lawoff
208
209 ,upddt
210 ,updateamount
211 ,eleadevent_enable
212
213 ,assettype
214 ,refid
215 ,reflgno
216 ,reftrandate
217
218 ,BankName
219
220 ,BankBranchName
221 ,tgCalculate
222 ,tgPrintForm
223 ,caseasset_gen
224'
225
226
227 ,_dup:=null -- disable
228);
229
230
231
232select "dbSys".viewRegistration( '"dbLegal".trCaseTrn040VW'
233 , pers := '"dbLegal".trcasetran'
234 --, _delete_system_data := true
235 -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := 4 , _init_sysorg := true
236 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null , _init_sysorg := true
237
238
239 ,_fts := true
240 ,_ftsfnvalexc := 'projectid,DebtCalcID,PeriodID,taskidgrp,count_event,count_asset,count_org,count_dc,count_doc,count_amt,count_gua,count_calc,post_inout,post_casestat,casekind,NoDefProf,create_require_event_by_dc,updateamount
241
242 CustName t ext
243 ,Defendant
244 ,CourtRole
245
246 ,ReceiverName text
247
248 ,OrgName text
249
250 ,LawOfficerName text
251 ,lawofficer_contactid
252
253 ,LawOfficeName
254
255 ,LawyerName
256
257 ,LGTypeName
258
259 ,ProfileName
260
261 ,ActiveStepName
262
263 ,UID int
264 ,ACC int
265 ,Status text
266
267 ,CaseComplete
268 ,CaseComplete_date
269
270 ,CourtName
271 ,CourtAreaName
272 ,sue_date
273
274 ,c_defd
275
276 ,CaseStatusName
277
278 ,Defendant1_Name
279
280
281 ,keyword
282
283 ,auto_copy
284 ,casekind
285 ,assign_lawoff
286
287 ,upddt
288 ,updateamount
289 ,eleadevent_enable
290
291 ,assettype
292 ,refid
293 ,reflgno
294 ,reftrandate
295
296 ,BankName
297
298 ,BankBranchName
299 ,tgCalculate
300 ,tgPrintForm
301 ,caseasset_gen
302'
303
304
305 ,_dup:=null -- disable
306
307);
308
309select "dbSys".recreate_views( _accid:= null , _tbl :='"dbLegal".tbcasetrn040' , _createview:=true ,_dropview:=true ,_regis:=true,_fts:=null ,_fts_refresh:=true, _mv_refresh :=true, _accid_inc := int_text2array( '179' ) );
310select "dbSys".recreate_views( _accid:= null , _tbl :='"dbLegal".tbcasetrn010' , _createview:=true ,_dropview:=true ,_regis:=true,_fts:=null ,_fts_refresh:=true, _mv_refresh :=true, _accid_inc := int_text2array( '179' ) );
311
312
313/*****************************************
314 Trigger : Clear elead event before delete
315 Wut,2019-07-04
316
317
318
319
320 -- select * from "dbHR".tbEmpVW_UserData( 1, 179 );
321
322 -- select * from "dbLegal".trcasetranevent a where a.uid = 2018
323 delete from "dbLegal".trcasetranevent a where a.uid = 2018
324
325 update "dbLegal".trcasetranevent set eleadevent_id = null where uid = 2018;
326
327
328
329
330*********************************************/
331
332drop function if exists "dbLegal".trcasetranevent_clear_elead() cascade;
333/*
334create or replace function "dbLegal".trcasetranevent_clear_elead()
335returns trigger as $$
336declare find record;
337begin
338 select id from "dbLegal".trcasetranevent where pid = old.pid and eleadevent_id = old.id into find;
339 if find.id is not null then
340 update "dbLegal".trcasetranevent set eleadevent_id = null where id = find.id;
341 end if;
342 return old;
343end ; $$ language plpgsql;
344
345drop trigger if exists trcasetranevent_clear_elead on "dbLegal".trcasetran;
346create trigger trcasetranevent_clear_elead
347 before delete on "dbLegal".trcasetranevent
348 for each row execute procedure "dbLegal".trcasetranevent_clear_elead();
349 */
350 /**************************
351 *
352 *****************************/
353drop trigger if exists trcasetranevent_cannot_del_trail on "dbLegal".trcasetranevent ;
354
355/*
356create trigger trcasetranevent_cannot_del_trail
357 after delete on "dbLegal".trcasetranevent
358 for each row execute procedure "dbLegal".trcasetranevent_cannot_del_trail();
359
360 */
361
362/********************************
363 * UAT Clean Data
364 * Wut,2019-07-01
365 *********************************/
366create or replace function "dbLegal".trcasetran_uat_remove_exist( in accid int ) returns void as
367$$
368declare -- accid int = 179;
369userid int = 1; casetranida int[]; caseassetida int[]; contactida int[];
370 find record;
371 userida int[];
372begin
373 /*
374 select array_agg(id) from "dbContact".tbcontact where acc= accid
375 and
376 ( ( firstname = 'สมประกอบ' and lastname = 'สดใสดี' ) or
377 ( firstname = 'รุ่มโพธิ' and lastname = 'สง่าศรี' )
378 )
379 into contactida;
380 */
381 select array_agg(a.id) from (
382 select id from "dbContact".tbcontact where acc= accid
383 and
384 ( ( firstname = 'สมประกอบ' and lastname = 'สดใสดี' ) or
385 ( firstname = 'รุ่มโพธิ' and lastname = 'สง่าศรี' ) or
386 ( firstname = 'สมส่วน' and lastname = 'สมใจนึก' ) or
387 ( firstname = 'สมสมัย' and lastname = 'ไม่สนใจ' ) or
388 ( firstname = 'จะเด็ด' and lastname = 'ดอกไม้ป่า' ) or -- ทนาย
389
390 ( firstname = 'สมสมัย' and lastname = 'ไม่สนใจ' )
391
392 )
393 union all
394 select b.contactid id from "dbLegal".trcasetran a
395 left join "dbUserReg".tbuserreg b on b.acc = a.acc and b.username like 'testuser%'
396 where a.acc = accid
397 ) a
398 into contactida;
399
400 /*
401 select array_agg( pid) from "dbLegal".trcasetrandc where acc = accid and tvalue = any(contactida)
402 into casetranida ;
403 */
404 select array_agg( a.id) from (
405 select pid id from "dbLegal".trcasetrandc where acc = accid and tvalue = any(contactida)
406 union all
407 select a.id from "dbLegal".trcasetran a
408 left join "dbUserReg".tbuserreg b on b.acc = a.acc and b.userid = a.uid
409 where a.acc= accid and b.contactid = any(contactida) and a.id is not null
410
411 ) a
412 into casetranida;
413
414 select array_agg( caseassetid ) from "dbLegal".trcasetranasset where acc= accid and pid = any(casetranida)
415 into caseassetida;
416
417 -- delete from "dbLegal".trcaseassetamt where acc= accid and pid = any (caseassetida);
418 update "dbLegal".trcaseasset set sum_casetran_caseamt=null, total = null where id = any (caseassetida);
419 delete from "dbLegal".trcaseasset where acc= accid and id = any (caseassetida);
420
421 -- delete from "dbLegal".trcasetranamt where acc = accid and pid = any (casetranida);
422 begin
423 delete from "dbLegal".trcasetranevent where acc = accid and pid = any (casetranida);
424 exception
425 when others then raise exception '0103:%,casetranida % ',sqlerrm, casetranida;
426 end;
427 delete from "dbLegal".trcasetran where acc = accid and id = any (casetranida);
428 -------------------------------------------------------------------------------------------
429
430 drop trigger if exists trcaseasset_caseamt_over_total on "dbLegal".trcaseasset;
431 drop trigger if exists trcaseassetCalc_DeleteDebtCalc on "dbLegal".trcaseassetCalc ;
432
433 select array_agg(id) from "dbUserAcc".tbuser where username like 'testuser%' into userida;
434-- raise 'xx';
435 perform "dbSysWKF".tblDeleteUserData( 'dbLegal', userida , deleteview :=false );
436
437 perform "dbSysWKF".tblDeleteUserData( 'dbCalen', userida , deleteview :=false );
438 perform "dbSysWKF".tblDeleteUserData( 'dbFinance', userida , deleteview :=false );
439 perform "dbSysWKF".tblDeleteUserData( 'dbUserAccReg', userida , deleteview :=false );
440 perform "dbSysWKF".tblDeleteUserData( 'dbUserReg', userida , deleteview :=false );
441 perform "dbSysWKF".tblDeleteUserData( 'dbContExt', userida , deleteview :=false );
442 perform "dbSysWKF".tblDeleteUserData( 'dbContact', userida , deleteview :=false );
443 execute format(' update "dbUserAccReg".tbUserPrdLic set userid = null where uid in ( %s) ',array_to_string(userida,',') ) ;
444
445 create trigger trcaseasset_caseamt_over_total
446 --after insert or update on "dbLegal".trcaseasset
447 after update on "dbLegal".trcaseasset
448 for each row execute procedure "dbLegal".trcaseasset_caseamt_over_total();
449 create trigger trcaseassetCalc_DeleteDebtCalc
450 after delete on "dbLegal".trcaseassetCalc
451 for each row execute procedure "dbLegal".trcaseassetCalc_DeleteDebtCalc();
452
453
454 ---------------------------------------------------------------------------------------------------
455 update "dbPM".tbtaskassg set assneeid = null, assnerid = null where acc= accid and ( assneeid = any(contactida) or assnerid= any(contactida));
456
457------------------------------------------------------------------------
458 delete from "dbUserReg".tbuserreg where acc = accid and contactid = any (contactida);
459 delete from "dbContact".tbcontact where acc = accid and id = any (contactida);
460
461
462 --* profile -----------------------------------
463 select id from "dbLegal".tblgsteppro where acc = accid and title = 'การดำเนินคดีทั่วไป' into find;
464 select a.id, b.plancaltaskid caltaskid, c.id lgstepproitemeventid_judge
465 from "dbLegal".tblgstepproitemevent a
466 left join "dbPM".tbtask b on b.id = a.taskid
467 left join "dbLegal".tblgstepproitemevent c on c.lgstepproid = a.lgstepproid and c.title = 'วันที่พิพากษา'
468 where a.lgstepproid = find.id and a.title = 'วันที่ครบกำหนดยื่นอุธรณ์'
469 into find;
470 if find.id is not null then
471 delete from "dbCalen".tbcalpart where pid = find.caltaskid;
472 delete from "dbCalen".tbcalwarn where pid = find.caltaskid;
473
474 execute format ( '
475 update "dbLegal".tblgstepproitemeventvw%s set leadingid = %s , leadingday = 30 ,warn_day = 1 where id = %s
476 '
477 ,accid
478 ,find.lgstepproitemeventid_judge
479 ,find.id
480 );
481 end if;
482
483 --* profile -----------------------------------
484
485 select id from "dbLegal".tblgsteppro where acc = accid and title = 'คดีพิเศษทดสอบ-ธนาคารเป็นจำเลย' into find;
486 if find.id is not null then
487 delete from "dbLegal".tblgsteppro where id = find.id;
488 end if;
489
490 --* report name master table
491
492 -- select * from "dbSysRPT".tbreportregisvw179;
493 -- select * from "dbSysRPT".tbreportregisvw179 where nam = 'rppmcardimp'; -- PM Task Card
494 execute format('
495 update "dbSysRPT".tbreportregisvw%s set reportcustomname = %L where sch = %L and nam = %L
496 '
497 , accid, 'PM Task Card', '"dbFinance"', 'rppmcardimp'
498 );
499
500end $$ language plpgsql;
501
502
503
504/************************
505 * Clearn all a/c
506 **************************/
507
508select "dbLegal".trcasetran_uat_remove_exist( 179 ); -- BAAC
509select "dbLegal".trcasetran_uat_remove_exist( 194 ); -- Mena
510
511 -- select * from "dbUserAcc".tbacc where id in ( 194, 179); -- ธกส
512 -- select * from "dbLegal".trcasetranvw179 where uid = 17579022;
513 -- select * from "dbLegal".tblgstepproitemeventvw179 a where a.lgstepproid = 65262 and title = 'วันที่ครบกำหนดยื่นอุธรณ์' ;
514 -- select * from "dbUserAcc".tbuser where username like 'testuser%';
515
516
517/*****************************************
518 Trigger : Clear elead event before delete
519 Wut,2019-07-04
520
521
522
523
524 -- select * from "dbHR".tbEmpVW_UserData( 1, 179 );
525
526 -- select * from "dbLegal".trcasetranevent a where a.uid = 2018
527 delete from "dbLegal".trcasetranevent a where a.uid = 2018
528
529 update "dbLegal".trcasetranevent set eleadevent_id = null where uid = 2018;
530
531
532
533
534*********************************************/
535
536drop function if exists "dbLegal".trcasetranevent_clear_elead() cascade;
537
538
539
540 /**************************
541 *
542 *****************************/
543drop trigger if exists trcasetranevent_cannot_del_trail on "dbLegal".trcasetranevent ;
544create trigger trcasetranevent_cannot_del_trail
545 after delete on "dbLegal".trcasetranevent
546 for each row execute procedure "dbLegal".trcasetranevent_cannot_del_trail();
547
548
549
550
551/*****************************************
552
553 *******************************************/
554create or replace function "dbUserReg".tbuserreg_uat_remove_exist(
555 in accid int
556 ,in createit boolean = null
557) returns void as
558$$
559declare -- accid int = 179;
560 -- userid int = 1;
561 userid_admin int;
562 userregida int[];
563 tbview text;
564 userida int[];
565 regn_name text = 'ภาคทดสอบ';
566 prov_name text = 'จังหวัดทดสอบ';
567 bran_name text = 'สาขาทดสอบ';
568 user_name text = 'testuser';
569
570 user_fullname text = 'ยูสเซอร์ทดสอบ';
571begin
572
573
574 select b.id from "dbUserAcc".tbaccuser a
575 left join "dbUserAcc".tbuser b on b.id = a.uid
576 where a.pid = accid and b.sysrole = 'Admin'
577 limit 1
578 into userid_admin;
579 -- raise '0047: userid_admin %', userid_admin;
580 -- select "dbSys".tvcreateview( userid_admin, accid , '"dbUserReg".tbuserreg') into tbview;
581 select "dbSys".tvcreateview( 1, accid , '"dbUserReg".tbuserreg') into tbview;
582------------------------------------------------------------------------------------
583
584 ----------------------------------------------------
585
586 -- raise '0051 tbview %', tbview;
587 /*
588 --select * from "dbUserReg".tbuserregvw179 where fullname like 'ภาค%';
589 --select array_agg(id) from "dbUserReg".tbuserregvw179 where fullname like 'สาขา1%' into userregida;
590 execute format( '
591 select array_agg(id) from "dbUserReg".tbuserregvw%s where username like %L
592 '
593 , accid , 'testuser%'
594 )
595 into userregida;
596
597 -- select * from "dbUserReg".tbuserregvw179 where username like 'testuser%' into userregida;
598 -- raise 'ok';
599 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
600 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
601 */
602 -- * User ----------------------------------------------------------------------------
603 -- select array_agg(id) from "dbUserReg".tbuserregvw179 where fullname like 'สาขาทดสอบ%' into userregida;
604 execute format( '
605 select array_agg(id) from "dbUserReg".tbuserregvw%s where username like %L '
606 , accid , user_name || '%'
607 )
608 into userregida;
609
610 execute format( '
611 select array_agg(userid) from "dbUserReg".tbuserregvw%s where username like %L '
612 , accid , user_name || '%'
613 )
614 into userida;
615
616 --*
617 select array_agg( id ) from "dbUserAcc".tbuser where username like 'testuser%' into userida;
618 delete from "dbUserAcc".tbaccuser where pid = accid and uid = any(userida);
619
620 -- select * from "dbUserAcc".tbuser where username like 'testuser%';
621
622 -- select * from "dbUserReg".tbuserregvw179 where username like 'testuser%' into userregida;
623 -- raise 'ok';
624
625 begin
626 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
627 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
628 raise notice '0093:delete %=%', user_name, format( '{%s}', userregida );
629 exception
630 when others then raise '0092:%, deleteing %',sqlerrm, user_name;
631 end;
632 -- * Branch----------------------------------------------------------------------------
633 -- select array_agg(id) from "dbUserReg".tbuserregvw179 where fullname like 'สาขาทดสอบ%' into userregida;
634 execute format( '
635 select array_agg(id) from "dbUserReg".tbuserregvw%s where trimsp(fullname) like %L '
636 , accid , bran_name || '%'
637 )
638 into userregida;
639
640 -- select replace( 'a b' ,' ', '' );
641
642 -- select * from "dbUserReg".tbuserregvw179 where username like 'testuser%' into userregida;
643
644
645 begin
646 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
647 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
648 raise notice '0111:delete %=%', bran_name, format( '{%s}', userregida );
649 exception
650 when others then raise '0108:%, deleteing %',sqlerrm, bran_name;
651 end;
652
653 -- * Provinc ---------------------------------------
654 -- select array_agg(id) from "dbUserReg".tbuserregvw179 where fullname like 'ภาคทดสอบ%' into userregida;
655 execute format( '
656 select array_agg(id) from "dbUserReg".tbuserregvw%s where fullname like %L '
657 , accid , prov_name|| '%'
658 )
659 into userregida;
660
661
662 -- raise 'ok';
663
664 begin
665 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
666 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
667 raise notice '0130:delete %=%', prov_name, format( '{%s}', userregida );
668 exception
669 when others then raise '0124:%, deleteing %',sqlerrm, prov_name;
670 end;
671
672 -- * Region --------------------------------------------------
673 -- select array_agg(id) from "dbUserReg".tbuserregvw179 where fullname like 'ภาคทดสอบ%' into userregida;
674 execute format( '
675 select array_agg(id) from "dbUserReg".tbuserregvw%s where fullname like %L '
676 , accid , regn_name|| '%'
677 )
678 into userregida;
679
680 -- select * from "dbUserReg".tbuserregvw179 where username like 'testuser%' into userregida;
681 -- raise 'ok';
682 begin
683 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
684 perform "dbSys".tvDelete( userid_admin , accid , '"dbUserReg".tbuserreg' ,format( '{%s}', userregida ) ::int[] );
685 raise notice '0148:delete %=%', regn_name, format( '{%s}', userregida );
686 exception
687 when others then raise '0141:%, deleteing %',sqlerrm, regn_name;
688 end;
689
690 -----------------------
691
692
693 -- delete from "dbUserAcc".tbuser where username like 'testuser_%';
694 -----------------------------
695 delete from "dbContact".tbcontact where acc = accid and firstname like 'สาขา1%' ;
696 delete from "dbContact".tbcontact where acc = accid and firstname like 'ภาค1%' ;
697 delete from "dbContact".tbcontact where acc = accid and firstname like 'ภาค2%' ;
698
699
700 ----------------------------
701 delete from "dbContact".tbcontact where acc = accid and firstname like 'สาขาทดสอบ%' ;
702 delete from "dbContact".tbcontact where acc = accid and firstname like 'จังหวัดทดสอบ%' ;
703 delete from "dbContact".tbcontact where acc = accid and firstname like 'ภาคทดสอบ%' ;
704 delete from "dbContact".tbcontact where acc = accid and concat_ws( ' ', prefix, firstname,lastname ) like '%ทดสอบ%' ;
705
706end $$ language plpgsql;
707
708
709/****************************
710 * Remove All a/c
711 ***********************************/
712select "dbUserReg".tbuserreg_uat_remove_exist( 179 );
713select "dbUserReg".tbuserreg_uat_remove_exist( 194 );
714
715/*
716 select * from "dbUser
717 select * from "dbUserAcc".tbaccuser where uid = 2018;
718
719
720
721 */
722
723 ----------------------------------------------------------------------------------------
724
725-- select "dbLegal".trcasetran_uat_remove_exist( 179 ); -- BAAC
726-- select "dbUserReg".tbuserreg_uat_remove_exist( 179 );
727--------------------------------------
728create or replace function "dbUserReg".tbuserreg_uat_create_user ( in accid int )
729returns void as $$
730
731 declare main record; sub record; sub2 record; sub3 record; sql text; userid_admin int;
732
733 regn_name text = 'ภาคทดสอบ';
734 prov_name text = 'จังหวัดทดสอบ';
735 bran_name text = 'สาขาทดสอบ';
736 user_name text = 'testuser';
737 user_fullname text = 'ยูสเซอร์ทดสอบ';
738
739begin
740 perform var('accid' , accid );
741 perform var( 'userid_admin' ,
742 (
743 select a.id from "dbUserAcc".tbuser a
744 join "dbUserAcc".tbaccuser b on b.uid = a.id and b.pid = varint('accid')
745 where a.sysrole = 'Admin'
746 order by id
747 limit 1
748 ) ::int
749 );
750 select varint('userid_admin' ) into userid_admin;
751
752 -- select * from "dbUserAcc".tbacc where id = 179; -- ธกส
753
754 --if accid = 179 then -- BAAC
755 if false then -- BAAC
756
757 insert into "dbUserReg".tbuserregvw179 ( username , fullname ,user_role, uid, acc, idgrp ) select null, regn_name||'1',false, varint('userid_admin') ,varint('accid'),null returning id into main ;
758 insert into "dbUserReg".tbuserregvw179 ( username , fullname ,user_role, uid, acc, idgrp) select null, prov_name||'11',false, varint('userid_admin') ,varint('accid'), main.id returning id into sub ;
759 insert into "dbUserReg".tbuserregvw179 ( username , fullname ,user_role, uid, acc, idgrp) select null, bran_name||'111',false, varint('userid_admin') ,varint('accid'), sub.id returning id into sub2 ;
760 insert into "dbUserReg".tbuserregvw179 ( username , fullname ,user_role, uid, acc, idgrp) select user_name||'1111', user_fullname||'1111',false, varint('userid_admin') ,varint('accid'), sub2.id returning id into sub3 ;
761 insert into "dbUserReg".tbuserregvw179 ( username , fullname ,user_role, uid, acc, idgrp) select user_name||'1112', user_fullname||'1112',false, varint('userid_admin') ,varint('accid'), sub2.id returning id into sub3 ;
762 insert into "dbUserReg".tbuserregvw179 ( username , fullname ,user_role, uid, acc, idgrp) select null, bran_name||'112',false, varint('userid_admin') ,varint('accid'), sub.id returning id into sub2 ;
763 insert into "dbUserReg".tbuserregvw179 ( username , fullname ,user_role, uid, acc, idgrp) select user_name||'1121', user_fullname||'1121',false, varint('userid_admin') ,varint('accid'), sub2.id returning id into sub3 ;
764
765 elseif accid = 194 then -- Mena
766
767
768 insert into "dbUserReg".tbuserregvw194 ( username , fullname ,user_role, uid, acc, idgrp ) select null, regn_name||'1',false, varint('userid_admin') ,varint('accid'),null returning id into main ;
769 insert into "dbUserReg".tbuserregvw194 ( username , fullname ,user_role, uid, acc, idgrp) select null, prov_name||'11',false, varint('userid_admin') ,varint('accid'), main.id returning id into sub ;
770 insert into "dbUserReg".tbuserregvw194 ( username , fullname ,user_role, uid, acc, idgrp) select null, bran_name||'111',false, varint('userid_admin') ,varint('accid'), sub.id returning id into sub2 ;
771 insert into "dbUserReg".tbuserregvw194 ( username , fullname ,user_role, uid, acc, idgrp) select user_name||'1111', user_fullname||'1111',false, varint('userid_admin') ,varint('accid'), sub2.id returning id into sub3 ;
772 insert into "dbUserReg".tbuserregvw194 ( username , fullname ,user_role, uid, acc, idgrp) select user_name||'1112', user_fullname||'1112',false, varint('userid_admin') ,varint('accid'), sub2.id returning id into sub3 ;
773 insert into "dbUserReg".tbuserregvw194 ( username , fullname ,user_role, uid, acc, idgrp) select null, bran_name||'112',false, varint('userid_admin') ,varint('accid'), sub.id returning id into sub2 ;
774 insert into "dbUserReg".tbuserregvw194 ( username , fullname ,user_role, uid, acc, idgrp) select user_name||'1121', user_fullname||'1121',false, varint('userid_admin') ,varint('accid'), sub2.id returning id into sub3 ;
775
776 else
777 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp ) select %s, %L,false ,%s,%s,%s returning id ', accid,'null', regn_name||'1', userid_admin ,accid, 'null' ) into main ;
778 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp ) select %s, %L,false ,%s,%s,%s returning id ', accid,'null', prov_name||'11', userid_admin ,accid, main.id ) into sub ;
779 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp ) select %s, %L,false ,%s,%s,%s returning id ', accid,'null', bran_name||'111', userid_admin ,accid, sub.id ) into sub2 ;
780 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp ) select %L, %L,false ,%s,%s,%s returning id ', accid, user_name||'1111', user_fullname||'1111', userid_admin ,accid, sub2.id ) into sub3 ;
781 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp ) select %L, %L,false ,%s,%s,%s returning id ', accid, user_name||'1112', user_fullname||'1112', userid_admin ,accid, sub2.id ) into sub3 ;
782 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp ) select %s, %L,false ,%s,%s,%s returning id ', accid,'null', bran_name||'112', userid_admin ,accid, sub.id ) into sub2 ;
783 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp ) select %L, %L,false ,%s,%s,%s returning id ', accid, user_name||'1121', user_fullname||'1121', userid_admin ,accid, sub2.id ) into sub3 ;
784
785
786 /*
787 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp) select null, %L,false,%s,%s,%s returning id ',accid, prov_name||'11',false, varint('userid_admin') ,varint('accid'), main.id ) into sub ;
788 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp) select null, %L,false,%s,%s,%s returning id ',accid, bran_name||'111',false, varint('userid_admin') ,varint('accid'), sub.id) into sub2 ;
789 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp) select %L, %L,false,%s,%s,%s returning id ',accid, user_name||'1111', user_fullname||'1111',false, varint('userid_admin') ,varint('accid'), sub2.id) into sub3 ;
790 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp) select %L, %L,false,%s,%s,%s returning id ',accid, user_name||'1112', user_fullname||'1112',false, varint('userid_admin') ,varint('accid'), sub2.id) into sub3 ;
791 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp) select null, %L,false,%s,%s,%s returning id ',accid, bran_name||'112',false, varint('userid_admin') ,varint('accid'), sub.id) into sub2 ;
792 execute format('insert into "dbUserReg".tbuserregvw%s ( username , fullname ,user_role, uid, acc, idgrp) select %L, %L,false,%s,%s,%s returning id ',accid, user_name||'1121', user_fullname||'1121',false, varint('userid_admin') ,varint('accid'), sub2.id) into sub3 ;
793 */
794
795 end if;
796
797end $$ language plpgsql;
798
799
800/*****************************************
801 * Create ONLY test a/c
802 *******************************************/
803--* create , only 1 at atime
804select "dbUserReg".tbuserreg_uat_create_user( 179 );
805-- select "dbUserReg".tbuserreg_uat_create_user( 194 );
806 -----------------------------------------------------------------------------
807
808
809/*
810
811select id, repeat(' ',dis*4) || fullname, username,fullname , user_role, userid, acc from "dbUserReg".tbuserregvw179 ; -- where fullname like 'ภาคทดสอบ%';
812select id, repeat(' ',dis*4) || concat_ws('',prefix,firstname) ,idgrp from "dbHR".tborgvw179 order by path; -- where fullname like 'ภาคทดสอบ%';
813select id, repeat(' ',dis*4) || fullname,idgrp from "dbContact".tbcontacthiervw179 order by path; -- where fullname like 'ภาคทดสอบ%';
814
815
816---------------------------------------------------------------------------------------------------------------------------
817
818select * from "dbUserAcc".tbuser where username like 'testuser%';
819insert into "dbUserReg".tbuserregvw179 ( username , fullname ,user_role, uid, acc, idgrp)
820 select 'testuser'||'1111', 'ทดสอบ'||'1111',false, varint('userid_admin') ,varint('accid'), null;
821
822select * from "dbUserReg".tbuserregvw179 where username like 'testuser%';
823----------------------------------------------------------------
824
825select id, repeat(' ',dis*4) || fullname, username, user_role from "dbUserReg".tbuserregvw194 ; -- where fullname like 'ภาคทดสอบ%';
826select id, repeat(' ',dis*4) || concat_ws('',prefix,firstname) ,idgrp from "dbHR".tborgvw194 order by path; -- where fullname like 'ภาคทดสอบ%';
827select id, repeat(' ',dis*4) || fullname,idgrp from "dbContact".tbcontacthiervw194 order by path; -- where fullname like 'ภาคทดสอบ%';
828-----------------------------------------------------------------
829delete from "dbUserReg".tbuserregvw194 where id in ( 1328);
830select * from "dbContact".tbcontact where id = 18428;
831select * from "dbUserReg".tbuserregvw194 where contactid = 18428;
832
833
834
835
836select * from "dbUserAcc".tbuserconf where acc = 194;
837 select * from "dbUserAcc".tbuserconf where acc = 194 and no = 'Basic';
838 delete from "dbUserAcc".tbuserconf where acc = 194 and no = 'Basic';
839
840
841*/