· 8 years ago · Mar 27, 2018, 02:22 AM
1/****************************************************************
2 tbCaseTranStepVW.sql
3
4 A collection of procedures related to View. Please follow this cnonvestional here.
5
6 2 functions as mandatory for view :-
7 - Prepare data for VIEW. Function name must be formated "Schema".tablenameVWM_UserData( ... )
8 - Update data fro VIEW. Function name must
9
10 Wutirkai Pornchai
11 01-05-2014
12*****************************************************************/
13
14/******************************************
15
16View Procedures
17
18 select
19 a.id ,e.id idgrp
20 , repeat(' ', d.dis*4)||d.title pro_title, d.pid pro_pid, d.id pro_id , d.idgrp pro_idgrp, d.dis pro_dis, d.fqty,d.path
21 ,concat_ws(' ',c2.title,'-',c2.id,'-', c2.acc ) Profile
22 from "dbLegal".tbcasetranstep a
23 left join "dbLegal".tbcasetran c on ( c.id = a.pid )
24 left join "dbLegal".tblgtype c1 on ( c1.id = c.lgtypeid )
25 left join "dbLegal".tblgsteppro c2 on (c2.id = c1.lgstepproid )
26 left join "dbLegal".tbLGStepProItemVW_UserData( c.uid, c.acc ) d on ( d.id = a.lgstepproitemid )
27 left join "dbLegal".tbcasetranstep e on (e.lgstepproitemid = d.idgrp and e.pid = a.pid )
28 where a.pid = 2
29 order by d.pid, d.path;
30
31 select a.id, repeat(' ',a.dis*4)||a.activestep step, a.idgrp, a.dis ,a.fqty, a.pid
32 from "dbLegal".tbCaseTranStepVW4 a
33 where a.pid = 2
34 order by a.pid, a.path; -- ** MUST order explictly **
35
36*******************************************/
37
38 -- * Data selected for underlying user
39drop function if exists "dbLegal".tbCaseTranStepVW_UserData ( in UserID int , in AccID int
40 ,in _pid int
41 ,in _id int
42 ) cascade;
43create or replace FUNCTION "dbLegal".tbCaseTranStepVW_UserData ( in UserID int , in AccID int
44 ,in _pid int = null
45 ,in _id int = null
46
47)
48returns table (
49 id int
50 -- ,CaseTitle varchar
51 ,ActiveStep varchar -- ขั้นตà¸à¸™à¸„ดี
52 ,Active boolean
53 ,StartDate timestamp_wtz
54 ,FinishDate timestamp_wtz
55 ,wholeday boolean
56 ,complete numeric -- (3,0)
57 ,duration int
58 --,duration interval
59
60 --,duration timestamp
61
62 ,Description text
63 ,dis smallint
64 ,taskid int
65 ,CalTaskID int
66 ,PID int
67
68 ,idgrp int
69 ,grp text
70 ,fqty smallint
71 ,orderno smallint
72 ,path int[]
73
74
75 --* Calendar Task - More detail
76 ,Title text
77 ,tasktypeName text
78 ,taskTypeID int
79 ,CalenName text
80 ,CalenNameID int
81 ,venue text
82
83 -- ,SenderName text
84 -- ,ReceiverName text
85 -- ,ResponsibleName text
86
87 --,SenderID int
88 --,receiverID int
89 --,ResponsibleID int
90
91 ,UID int
92 ,ACC int
93 ,LGStepProItemID int
94 --* Case tran
95 ,casetranid int
96 ,case_LGNo text -- varchar -- b1.TValue LGNo
97 ,case_lgtypeName text
98 ,case_SueDate timestamp_wtz
99 ,case_CourtName text
100 ,case_BlackNo text -- varchar -- j.TValue คดีดำ --BlackNo
101 ,case_RedNo text -- varchar -- k.TValue คดีà¹à¸”ง -- RedNo
102 ,case_amt num
103 ,Selectable boolean
104 ,Selected boolean
105 ,SysStep text
106 ,lgstepproid int
107
108 ) AS $$
109 select
110 a.id
111 -- ,f.Title CaseTitle
112 --,concat_ws(' ',d.Title ) ActiveStep
113 ,d.title ActiveStep
114 ,a.Active
115
116 ,b1.startDate StartDate
117 ,b1.finishDate FinishDate
118
119 ,b1.wholeday
120 ,b1.complete
121
122 --,(b.finishdate - b.startdate) +1 duration
123 -- ,b.finishdate - b.startdate duration
124
125 ,extract( epoch from (b1.finishdate - b1.startdate) / 3600 )::integer duration -- day
126
127
128 --,b.Description
129 --,a.description
130 ,case
131 when a.description is null then b1.description else a.description
132 end Description
133
134 ,d.dis
135
136 ,a.taskid
137 ,b1.id CalTaskID
138
139 ,a.PID
140
141 ,e.id idgrp
142 , e1.title
143 ,d.fqty
144 ,d.orderno
145 --,d.path
146 ,a.path_prof
147
148 --* Calendar Task - More --- -------------------
149 ,b1.title -- b.Title
150 ,a2.tasktype
151 ,b1.taskType
152 ,a1.Title::text CalenName
153 ,a1.id CalenNameID
154 ,b1.venue::text
155 -- , concat_ws(' ',a3."prefix",a3.firstname, a3.lastname, a3.suffix ) SenderName
156 -- , concat_ws(' ',a4."prefix",a4.firstname, a4.lastname, a4.suffix ) ReceiverName
157 -- , concat_ws(' ',a5."prefix",a5.firstname, a5.lastname, a5.suffix ) ResponsibleName
158 -- ,b.Sender -- its id
159 --,b.Receiver -- its id
160 --,b.Responsible -- its id
161 ,a.uid
162 ,a.acc
163
164 ,a.LGStepProItemID
165 --* case tran
166 ,a.casetranid
167 ,a6.lgno
168 ,a61.title lgtypename
169 ,n2.StartDate :: timestamp_wtz
170 ,concat_ws(' ',a6311.prefix, a6311.firstname, a6311.lastname, a6311.suffix ) courtname
171 ,j.TValue คดีดำ --BlackNo
172 ,k.TValue คดีà¹à¸”ง -- RedNo
173 ,a66.tvalue
174 ,a.Selectable
175 ,a.Selected
176 ,d.SysStep
177 ,a6.lgstepproid
178
179 from
180 "dbLegal". tbCaseTranStep a
181
182
183 left join "dbCalen".tbCalTask b1 on (b1.ID = a.caltaskid )
184 left join "dbCalen".tbcalenname a1 on ( a1.id = b1.calennameid )
185 left join "dbBase".tbtasktype a2 on (a2.id = b1.tasktype )
186
187 left join "dbLegal".tbLGStepProItem d on (d.id = a.LGStepProItemID )
188 left join "dbLegal".tbcasetranstep e on (e.lgstepproitemid = d.idgrp and e.pid = a.pid )
189 left join "dbLegal".tblgstepproitem e1 on ( e1.id = d.idgrp )
190
191 left join "dbLegal".tbcasetran a6 on ( a6.id= a.casetranid )
192 left join "dbLegal".tblgtype a61 on ( a61.id = a6.lgtypeid )
193
194 left join "dbLegal".tbCaseTranEvent a62 on (a62.PID = a6.ID and a62.title = vartext('legal_วันที่ยื่นฟ้à¸à¸‡') )
195 -- join "dbLegal".tblgstepproitemevent n3 on (n3.id = n.LGStepProItemEventID )
196 -- join "dbLegal".tblgstepproitemevent n4 on (n4.id = n3.orgid and n4.title = vartext('legal_วันที่รับเรื่à¸à¸‡') )
197 left join "dbCalen".tbCalTask n2 on(n2.ID = a62.CalTaskID )
198 left join "dbLegal".tbcasetrancourt a63 on ( a63.pid = a6.id and a63.active )
199 left join "dbLegal".tbcourt a631 on ( a631.id = a63.courtid )
200 left join "dbContact".tbcontact a6311 on ( a6311.id = a631.contactid )
201 left outer join "dbLegal".tbCaseTranNo j on (j.PID = a6.ID and j.Title = 'Black' and j.tcustom is null )
202 left outer join "dbLegal".tbCaseTranNo k on (k.PID = a6.ID and k.Title = 'Red' and k.tcustom is null )
203 left join "dbLegal".tbcasetranamt a66 on ( a66.pid = a6.id and a66.title = 'Total' )
204 --- left join "dbLegal".tblgstepproitem a7 on a7.id = a.lgstepproitemid
205 right join "dbSys".grpgetdata(userid, accid , '"dbLegal".tblgstepproitem' ,path_only:=true ) a7 on a7.id = a.lgstepproitemid
206
207 where
208 case
209 when _id is null then a.acc=accid
210 else a.id = _id
211 end
212 and case
213 when _pid is null then true
214 else a.pid = _pid
215 end
216 order by
217 a61.lgstepproid -- d.pid
218 --, d.path
219 ,a.path_prof
220 ;
221
222$$ LANGUAGE sql;
223
224-- * How to update underlying tables whose data is projected to this view
225drop function if exists "dbLegal".tbCaseTranStepVW_UpdData() cascade;
226create or replace function "dbLegal".tbCaseTranStepVW_UpdData()
227returns trigger as $$
228declare Grp varchar; Change boolean; sql text=null; lastid int;
229 dt date; tm time; dt1 date; tm1 time;
230
231begin
232 --raise notice '181';
233
234 if (TG_OP = 'INSERT') then
235 --* No direct insert, it's from profile
236
237 --* Get stepid using its title
238 if new.activestep is not null then
239
240 --if new.grp is null then
241 if true then
242 select b.id, b.idgrp from "dbLegal".tbcasetran a
243 left join "dbLegal".tblgstepproitem b on b.pid = a.lgstepproid and b.title = new.activestep
244 left join "dbLegal".tblgstepproitem b1 on b1.id = b.idgrp
245 where a.id = new.pid
246 and case
247 when new.grp is null then true
248 else b1.title = new.grp
249 end
250 -- order by b.orderno limit 1
251 into new.lgstepproitemid , new.idgrp ;
252
253 -- raise '256:new.lgstepproitemid %, new.idgrp %', new.lgstepproitemid , new.idgrp;
254
255
256 end if;
257
258 end if;
259
260 --*
261 insert into "dbLegal".tbcasetranstep ( pid, lgstepproitemid , uid,acc, sysstep )
262 select
263 new.pid
264 ,new.lgstepproitemid
265 ,new.uid
266 ,new.acc
267 ,new.sysstep
268 ;
269 return NEW;
270
271 elseif (TG_OP = 'UPDATE') then
272
273 --raise notice '198, descriptin =%', new.description;
274
275 --* Joint table : -----------------
276 sql := null;
277 -- sql := "dbSys".sqlSetValue( OLD.Title , NEW.Title, sql, 'Title' );
278 -- sql := "dbSys".sqlSetValue( OLD.TaskTypeID , NEW.TaskTypeID, sql, 'TaskType' );
279
280 sql := "dbSys".sqlSetValue( OLD.Complete , NEW.Complete, sql, 'Complete' );
281
282 --sql := "dbSys".sqlSetValue( OLD.Description, NEW.Description, sql, 'Description' );
283
284 if sql is not null then
285 --sql := 'update "dbCalen".tbCalTask ' || sql ||' where id = '|| NEW.CalTaskID ;
286 sql := 'update "dbPM".tbTask ' || sql ||' where id = '|| NEW.taskid ;
287
288 --raise notice '238 sql=%',sql;
289 execute sql ;
290 end if;
291
292
293 /*
294 if diff( OLD.StartDate, NEW.StartDate ) then
295 select _date,_time from ExtractDateTime( new.StartDate ) into dt, tm; -- Extrat date and time
296 if diff( null::date, dt ) then -- if not null
297 sql := "dbSys".sqlSetValue( null::date, dt, sql, 'StartDate' );
298 end if;
299 if diff( null::time, tm ) then -- if not null
300 sql := "dbSys".sqlSetValue( null::time, tm, sql, 'StartTime' );
301 end if;
302 end if;
303
304 if diff( OLD.FinishDate , NEW.FinishDate ) then
305 select _date,_time from ExtractDateTime( new.FinishDate ) into dt, tm; -- Extrat date and time
306 if diff( null::date, dt ) then -- if not null
307 sql := "dbSys".sqlSetValue( null::date, dt, sql, 'FinishDate' );
308 end if;
309 if diff( null::time, tm ) then -- if not null
310 sql := "dbSys".sqlSetValue( null::time, tm, sql, 'FinishTime' );
311 end if;
312 end if; */
313
314 sql := null;
315 sql := "dbSys".sqlSetValue( OLD.startdate , NEW.startdate, sql, 'startdate' );
316 sql := "dbSys".sqlSetValue( OLD.finishdate , NEW.finishdate, sql, 'finishdate' );
317
318
319 -- raise notic 'sql=%, caltaskid=%',sql, new.caltaskid;
320 sql := "dbSys".sqlSetValue( OLD.wholeday , NEW.wholeday, sql, 'wholeday' );
321
322 --* Calendar Task - More detail
323
324 --sql := "dbSys".sqlSetValue( OLD.Title, NEW.Title, sql, 'Title' );
325
326 sql := "dbSys".sqlSetValue( OLD.taskTypeID, NEW.taskTypeID, sql, 'taskTypeID' );
327 sql := "dbSys".sqlSetValue( OLD.venue, NEW.venue, sql, 'venue' );
328 --sql := "dbSys".sqlSetValue( OLD.complete, NEW.complete, sql, 'complete' );
329 sql := "dbSys".sqlSetValue( OLD.description, NEW.description, sql, 'description' );
330
331 -- sql := "dbSys".sqlSetValue( OLD.calennameid, NEW.calennameid, sql, 'calennameid' );
332
333 --sql := "dbSys".sqlSetValue( OLD.SenderID, NEW.SenderID, sql, 'Sender' );
334 --sql := "dbSys".sqlSetValue( OLD.receiverID, NEW.receiverID, sql, 'receiver' );
335 --sql := "dbSys".sqlSetValue( OLD.ResponsibleID, NEW.ResponsibleID, sql, 'Responsible' );
336
337 -- raise notice '240 sql=%s', sql;
338
339 -- * Update tbContact, if any change
340 if sql is not null then
341 sql := 'update "dbCalen".tbCalTask ' || sql ||' where id = '|| NEW.CalTaskID ;
342 --sql := 'update "dbPM".tbTask ' || sql ||' where id = '|| NEW.CalTaskID ;
343
344 execute sql ;
345 end if;
346
347 --* Main table -----------------
348 sql := null;
349 sql := "dbSys".sqlSetValue( OLD.Active, NEW.Active, sql, 'Active' );
350 sql := "dbSys".sqlSetValue( OLD.casetranid, NEW.casetranid, sql, 'casetranid' );
351 sql := "dbSys".sqlSetValue( OLD.Selectable, NEW.Selectable, sql, 'Selectable' );
352 sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql, 'uid' );
353
354 sql := "dbSys".sqlSetValue( OLD.sysstep, NEW.sysstep, sql, 'sysstep' );
355 sql := "dbSys".sqlSetValue( OLD.lgstepproitemid, NEW.lgstepproitemid, sql, 'lgstepproitemid' );
356
357 -- * Update tbContact, if any change
358 if sql is not null then
359 sql := 'update "dbLegal".tbCaseTranStep' ||' '|| sql ||
360 ' where id = '|| NEW.id ;
361 execute sql ;
362 end if;
363
364 return NEW;
365 elseif TG_OP = 'DELETE' then
366 -- delete from "dbLegal".tbCaseTranStep where id = old.id ;
367 return OLD;
368 end if;
369end ; $$ LANGUAGE plpgsql;
370
371
372/*****************************
373 View registration
374******************************/
375select "dbSys".viewRegistration( '"dbLegal".tbCaseTranStepVW'
376 -- , _delete_system_data := true
377 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
378 -- , _OrderNo_Updatable := true
379
380);
381
382--* Create view
383
384select "dbSys".tvCreateView ( varint('userid'), varint('accid'), '"dbLegal".tbCaseTranStep' ); -- return "dbContact".tbContTypeVW4