· 9 years ago · Jan 14, 2017, 02:46 AM
1/****************************************************************
2 tbCaseTranDocVW.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 25-04-2014
12*****************************************************************/
13/******************************************
14View Procedures
15*******************************************/
16 -- * Data selected for underlying user
17drop function if exists "dbLegal".tbCaseTranDocVW_UserData ( in UserID int , in AccID int ) cascade;
18create or replace FUNCTION "dbLegal".tbCaseTranDocVW_UserData ( in UserID int , in AccID int )
19returns table (
20 id int
21 -- *
22 ,docid int
23 ,DocTitle text -- ชื่à¸à¹€à¸à¸à¸ªà¸²à¸£
24 ,DocNo text -- เลขที่เà¸à¸à¸ªà¸²à¸£
25 ,DocDate timestamp_wtz --
26 ,DoctypeID int -- b.ID DoctypeID
27 ,DoctypeName varchar -- concat_ws(' ',b.Title ) DoctypeName
28
29 ,QtyOrg smallint -- a.QtyOrg QtyOrg
30 ,QtyCopy smallint -- a.QtyCopy QtyCopy
31 ,Description text
32
33 ,Required boolean
34 ,FromProfile boolean -- if(a.LGStepProItemDoctypeID is not null,'Y','N') FromProfile
35
36 ,LGStepProItemID int -- concat_ws(' ',e.Title, '-',a.LGStepProItemID ) Step
37 ,LGStepProItemTitle varchar
38 ,DocID int
39 ,PID int
40 -- *
41 ,OrderNo smallint
42 ,idgrp int ,fqty smallint
43 ,uid int , acc int
44 ,DCNo smallint
45 ,sysdoc text
46 ,docid_template int
47 ,title_template text
48
49) AS $$
50 -- *
51 select
52 a.id
53 --,case
54 --when a.title is null then concat_ws( ' ',d.Title,'-',d.ID )
55 -- when a.title is null then d.title
56 -- else a.title
57 --end DocTitle
58 ,a.docid
59 ,d.title
60 ,d.DocNo
61 ,d.Date DocDate
62 ,b.ID DoctypeID
63 ,concat_ws(' ',b.Title ) DoctypeName
64
65 ,a.QtyOrg QtyOrg
66 ,a.QtyCopy QtyCopy
67 ,d.Description
68
69 ,c.Required
70 ,if(a.LGStepProItemDoctypeID is not null,'Y'::boolean,'N'::boolean) FromProfile
71
72 ,a.LGStepProItemID
73 ,e.title LGStepProItemTitle --,concat_ws(' ',e.Title, '-',a.LGStepProItemID ) LGStepProItemTitle
74 ,d.id DocID
75
76 ,a.PID
77 ,a.orderno
78 ,a.idgrp ,a.fqty
79 ,a.uid , a.acc
80 ,a2.orderno DCNo
81 ,a.sysdoc
82
83 ,a.docid_template
84 ,a3.title title_template
85
86 from
87 "dbLegal".tbCaseTranDoc a
88 left join "dbDoc".tbDocType b on ( b.id = a.DocTypeID )
89 left join "dbLegal".tbLgstepproitemdoctype c on (c.id = a.LGStepProItemDocTypeID )
90 left join "dbDoc".tbDoc d on (d.ID = a.DocID )
91 left join "dbLegal".tbLGStepProItem e on (e.ID = a.LGStepProItemID )
92 -- left join "dbLegal".tbCaseTran f on (f.id = a.pid )
93
94 --left join "dbSys".grpGetData( tbl:='"dbLegal".tbCaseTranDoc',_pid := f.id ) a1 on (a1.id = a.id )
95 left join "dbSys".grpGetData( tbl:='"dbLegal".tbCaseTranDoc',_pid := a.pid ) a1 on (a1.id = a.id )
96 left join "dbLegal".tbcasetrandc a2 on a2.pid = a.pid and a2.tvalue = a.contactid
97 left join "dbDoc".tbdoc a3 on a3.id = a.docid_template
98
99 where
100 a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
101 order by a1.path
102
103 -- , a.seq
104 ;
105$$ LANGUAGE sql;
106
107-- * How to update underlying tables whose data is projected to this view
108drop function if exists "dbLegal".tbCaseTranDocVW_UpdData() cascade;
109create or replace function "dbLegal".tbCaseTranDocVW_UpdData()
110returns trigger as $$
111declare Grp varchar; Change boolean; sql text=null; lastid int;
112begin
113 if (TG_OP = 'INSERT') then
114 --* Main table
115 insert into "dbLegal".tbCaseTranDoc (
116 LGStepProItemDoctypeID
117 ,DocTypeID
118 ,PID
119 ,LGStepProItemID
120 ,QtyOrg
121 ,QtyCopy
122 ,uid
123 )
124 select
125 null -- Null = manual input, not from profile
126 ,new.DoctypeID
127 ,new.PID
128 ,b.lgstepproitemid -- b.id -- Active step
129 ,new.QtyOrg
130 ,new.QtyCopy
131 ,new.uid
132 from
133 "dbLegal".tbCaseTran a
134 left join "dbLegal".tbCaseTranStep b on ( b.pid = a.id and b.active = 'Y' )
135 where a.id = new.PID
136 returning id, DocID, LGStepProItemID, uid, acc
137 into new.id, new.DocID, new.LGStepProItemID, new.uid, new.acc;
138
139 --update "dbDoc".tbdoc set title = new.doctitle where id = new.docid;
140 --* Joint table : Document
141 if new.docid is not null then
142
143 sql := null;
144 sql := "dbSys".sqlSetValue( null::text , NEW.DocTitle, sql, 'Title' );
145 sql := "dbSys".sqlSetValue( null::text , NEW.DocNo, sql, 'DocNo' );
146 sql := "dbSys".sqlSetValue( null::timestamp_wtz, NEW.DocDate, sql, 'Date' );
147 sql := "dbSys".sqlSetValue( null::text, NEW.Description, sql, 'Description' );
148 -- * Update tbContact, if any change
149 if sql is not null then
150 sql := 'update "dbDoc".tbDoc ' || sql || ' where id = '|| NEW.Docid ;
151 execute sql ;
152 end if;
153
154 end if;
155
156
157 /*
158 if new.docid is null then
159 insert into "dbDoc".tbdoc ( title, docno,date, description, uid, acc )
160 select new.doctitle, new.docno, new.docdate, new.description , new.uid, new.acc
161 returning id into new.docid;
162 update "dbLegal".tbCaseTranDoc set docid = new.docid where id = new.id;
163
164
165 end if; */
166
167
168 return NEW;
169 elseif (TG_OP = 'UPDATE') then
170
171 --* Build change sql
172 -- sql := "dbSys".sqlSetValue( OLD.doctitle, NEW.doctitle, sql, 'title' );
173 sql := "dbSys".sqlSetValue( OLD.DoctypeID, NEW.DoctypeID, sql, 'DoctypeID' );
174 sql := "dbSys".sqlSetValue( OLD.QtyOrg, NEW.QtyOrg, sql, 'QtyOrg' );
175 sql := "dbSys".sqlSetValue( OLD.QtyCopy, NEW.QtyCopy, sql, 'QtyCopy' );
176 sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql, 'uid' );
177 -- raise notice '%', sql;
178 -- * Update tbContact, if any change
179 if sql is not null then
180 sql := 'update "dbLegal".tbCaseTranDoc ' || sql ||' where id = '|| NEW.id
181 || ' returning docid '
182 ;
183 execute sql into new.docid;
184 end if;
185
186 --* Joint table : Document
187 if new.docid is not null then
188
189 sql := null;
190 sql := "dbSys".sqlSetValue( OLD.DocTitle , NEW.DocTitle, sql, 'Title' );
191 sql := "dbSys".sqlSetValue( OLD.DocNo , NEW.DocNo, sql, 'DocNo' );
192 sql := "dbSys".sqlSetValue( OLD.DocDate, NEW.DocDate, sql, 'Date' );
193 sql := "dbSys".sqlSetValue( OLD.Description, NEW.Description, sql, 'Description' );
194 -- * Update tbContact, if any change
195 if sql is not null then
196 sql := 'update "dbDoc".tbDoc ' || sql || ' where id = '|| NEW.Docid ;
197 execute sql ;
198 end if;
199
200 end if;
201 return NEW;
202 elseif TG_OP = 'DELETE' then
203 -- delete from "dbLegal".tbCaseTranDoc where id = old.id ;
204 perform "dbSys".grpDelete (old.id , '"dbLegal".tbCaseTranDoc' );
205
206 return OLD;
207 end if;
208end ; $$ LANGUAGE plpgsql;
209
210/*****************************
211 View registration
212******************************/
213select "dbSys".viewRegistration( '"dbLegal".tbCaseTranDocVW'
214 -- , _delete_system_data := true
215 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
216 , _OrderNo_Updatable := true
217
218);