· 6 years ago · Oct 14, 2019, 08:26 AM
1drop function if exists "dbDoc".tbRGalleryVW_UserData ( in UserID int , in AccID int
2 ,in _id int
3) cascade;
4create or replace FUNCTION "dbDoc".tbRGalleryVW_UserData ( in UserID int , in AccID int
5 ,in _id int = null
6)
7returns table (
8 id int
9 ,title text
10 ,description text
11 ,alias text
12 ,grp text
13 ,docid int
14 ,doctitle text -- doc title
15 ,galtitle text
16 --------------------------------
17 ,orderno int
18 ,idgrp int
19 ,idprev int
20 ,fqty smallint
21 ,dis smallint
22
23 ,pid int
24 ,uid int
25 ,acc int
26 ,orgid int
27 ,path int[]
28 ,token text
29)
30
31AS $$
32begin
33 return query
34 execute $x$
35 select
36 a.id
37 ,a.title
38 ,a.description
39 ,case when a.alias is null then a.title else a.alias end
40 ,b.title grp
41 ,a.docid
42 ,c.title doctitle
43 , concat_ws(' ',a.description
44 , format('( %s )', concat_ws(' ', case when b.alias is null then b.title else b.alias end , a.alias) )
45 ) altitle
46 ------------------------------------
47 ,a.orderno
48 ,a.idgrp
49 ,a.idprev
50 ,a.fqty
51 ,a.dis
52
53 ,a.pid
54 ,a.uid
55 ,a.acc
56 ,a.orgid
57 ,z.path
58 ,c.token ::text token
59 from
60
61 "dbDoc".tbRGallery a
62 left join "dbDoc".tbrgallery b on b.id =a.idgrp
63 left join "dbSys".grpGetData(
64
65 tbl := '"dbDoc".tbrgallery'
66 , _acc := $2 -- accid
67 , path_only:=true
68 , order_by_path := false
69
70 ) z on ( z.id = a.id )
71 left join "dbDoc".tbdoc c on c.id = a.docid
72 where
73 case
74 when $1 is null then a.acc in ( $2, 4 )
75 else a.id = $1
76 end
77 $x$ using _id , accid;
78
79end $$ LANGUAGE plpgsql stable;
80
81
82/*********************************
83 * Funtion :
84 * Wut,2019-09-24
85 *
86 **************************************/
87create or replace function "dbDoc".tbrgalleryvw_user_custom_only(
88 in uid int
89 ,in acc int
90 ,in id int
91 ,in tg_op text
92 ,out ok boolean
93) returns boolean as $$
94declare grp record;
95begin
96 if equal(uid,45::int) or equal(acc,4::int ) then
97 ok=true;
98 return;
99 end if;
100
101 ok=false;
102 --if tg_op in 'INSERT' then
103 if true then
104 while true loop
105 execute $x$
106 select b.id, b.alias from "dbDoc".tbrgallery a
107 left join "dbDoc".tbrgallery b on b.id = a.idgrp
108 where a.id= $1
109 $x$ using id
110 into grp;
111 if equal( grp.alias ,'Custom' ) then
112 ok=true;
113 exit;
114 else
115 id= grp.id;
116 end if;
117 end loop;
118 end if;
119 --*
120 if not ok then
121 raise exception '0032: "Custom" only';
122 end if;
123end $$ language plpgsql;
124
125
126
127/*********************************************
128 FUnction :
129
130 ***********************************************/
131
132-- * How to update underlying tables whose data is projected to this view
133drop function if exists "dbDoc".tbRGalleryVW_UpdData() cascade;
134create or replace function "dbDoc".tbRGalleryVW_UpdData()
135returns trigger as $$
136declare
137 TABLE_SCHEMA text = tg_argv[0];
138 TABLE_NAME text = tg_argv[1];
139 sql text;
140 find record;
141 report record;
142 main record;
143 stand record;
144 custom record; ok boolean;
145begin
146 if (TG_OP = 'INSERT') then
147 --*
148 -- "dbSys".grpTitles( 'xx', 95, '"dbPM".tbtask' );
149
150 --* Init
151 select count_rgallery from "dbSysRPT".tbReportRegis where id = new.pid into find;
152 if find.count_rgallery is null or find.count_rgallery=0 then
153 -- raise 'xx';
154 select
155 a.id, a.reportname title -- ,null, new.uid, new.acc ,new.pid
156 from "dbSysRPT".tbReportRegis a
157 where a.id = new.pid and a.acc = new.acc
158
159 into report;
160 -- raise '0163: reportid %',report.id;
161
162 if new.uid = 45 and new.acc=4 then
163 insert into "dbDoc".tbrgallery ( title,idgrp,uid,acc, pid ) select report.title,null,new.uid,new.acc, new.pid ;
164 select id from "dbDoc".tbrgallery where acc= new.acc and title = report.title into find;
165
166 insert into "dbDoc".tbrgallery ( title,idgrp,uid,acc ,pid)
167 select 'Standard', find.id , new.uid, new.acc ,new.pid
168 ;
169
170 insert into "dbDoc".tbrgallery ( title,idgrp,uid,acc,pid )
171 select 'Custom', find.id , new.uid, new.acc , new.pid
172 ;
173
174 end if;
175 select id from "dbDoc".tbrgallery where uid = 45 and acc=4 and title = 'Standard' into stand;
176 select id from "dbDoc".tbrgallery where uid = 45 and acc=4 and title = 'Custom' into custom;
177
178 -- raise '0113: new.id %,stand.id %,custom.i %', new.id,stand.id,custom.id ;
179
180 end if;
181
182 --*
183 if new.idgrp is null then
184 if new.uid = 45 and new.acc=4 then
185 select id from "dbDoc".tbrgallery where pid = new.pid and title = 'Standard' into stand;
186 -- raise '0121: standid %',stand.id;
187 new.idgrp = stand.id;
188
189 else
190 -- raise exception '0126:No group, idgrp is null';
191 /*
192 select id from "dbDoc".tbrgallery where acc= new.acc and title = 'Custom' into find;
193 raise '0185: find.id %',find.id;
194 if find.id is not null then
195 new.idgrp = find.id;
196 else
197 raise '0185: No Custom group';
198 end if;
199 */
200 new.idgrp = custom.id;
201 end if;
202
203 else
204 -- select new.idgrp id into stand;
205
206 if not ( new.uid = 45 and new.acc=4) then
207 ok=false;
208 select new.idgrp id into find;
209 while true loop
210 select id,alias,idgrp from "dbDoc".tbrgallery where id = find.id into find;
211 if find.alias = 'Custom' then
212 ok=true;
213 exit;
214 else
215 if find.idgrp is null then
216 exit;
217 else
218 select find.idgrp id into find;
219 end if;
220 end if;
221 end loop;
222 if not ok then
223 raise exception '0219: User template must stay under Custom only';
224 end if;
225 end if;
226
227 end if;
228
229 -- select concat_ws(' ',title,new.alias ) from "dbDoc".tbrgallery where id = stand.id into new.title;
230 -- raise '0124:pid %, new.idgrp % new.title %',new.pid, new.idgrp, new.title;
231
232 -- raise '0106:idgrp %, title %, pid %', new.idgrp, new.title, new.pid ;
233 --*
234 if new.alias is null then
235 select max(a.seq+1)+1 from "dbDoc".tbrgallery a where a.pid = new.pid
236 and case when new.idgrp is null then a.idgrp is null else a.idgrp = new.idgrp end
237 into new.alias;
238 end if;
239
240 --*
241 if new.alias is not null then
242 --* title by alias
243 if true then
244 select title from "dbDoc".tbrgallery where id = new.idgrp into new.title;
245 select concat_ws(' ',new.title,new.alias ) into new.title;
246
247 end if;
248 end if;
249 -- raise '0150:new.title %, idgrp %, new.alias %',new.title, new.idgrp, new.alias ;
250 --*
251 insert into "dbDoc".tbrgallery ( title,alias,description,uid, acc ,pid ,idgrp, docid )
252 select new.title,new.alias,new.description,new.uid,new.acc ,new.pid, new.idgrp , new.docid
253 ;
254 --select id from "dbDoc".tbrgallery where pid = new.pid and title = new.title into new.id;
255 --perform "dbSys".grpMove_ID_TO_ChildOfIDII(NEW.ID, new.idgrp ,'"dbDoc".tbrgallery', to_bottom := true );
256
257 -- raise '0133: new.id % ,new.idgrp %', new.id,new.idgrp;
258 --*
259 -- perform "dbDoc".tbrgalleryvw_user_custom_only(new.uid,new.acc,new.id,tg_op) ;
260
261 return new;
262 elseif (TG_OP = 'UPDATE') then
263
264 --*
265 if diff(new.alias,old.alias ) then
266 --* title by alias
267 if true then
268 select title from "dbDoc".tbrgallery where id = new.idgrp into new.title;
269 select concat_ws(' ',new.title,new.alias ) into new.title;
270 end if;
271 end if;
272
273 --*
274 sql := sqlSetValue( OLD.title, NEW.title, sql, 'title' );
275 sql := sqlSetValue( OLD.alias, NEW.alias, sql, 'alias' );
276 sql := sqlSetValue( OLD.description, NEW.description, sql, 'description' );
277 sql := sqlSetValue( OLD.idgrp, NEW.idgrp, sql, 'idgrp' );
278 sql := sqlSetValue( OLD.orderno, NEW.orderno, sql, 'orderno' );
279 if sql is not null then
280 sql = format('update"dbDoc".tbrgallery %s where id = $1.id', sql );
281 execute sql using new;
282 end if;
283
284 --*
285 perform "dbDoc".tbrgalleryvw_user_custom_only(new.uid,new.acc,new.id,tg_op);
286 return NEW;
287 elseif TG_OP = 'DELETE' then
288
289 perform "dbSys".grpdelete( old.id, '"dbDoc".tbrgallery' );
290 perform "dbDoc".tbrgalleryvw_user_custom_only(old.uid,old.acc,old.id,tg_op);
291 return OLD;
292 end if;
293end ; $$ LANGUAGE plpgsql;
294
295/*****************************
296 View registration
297******************************/
298select "dbSys".viewRegistration( '"dbDoc".tbRGalleryVW'
299 -- , pers := '"dbLegal".trpinv'
300 --, _delete_system_data := true
301 --,_init_data := true, _init_data_uid := varint('userid') ,_init_data_acc := varint('accid')
302 ,_init_data := true, _init_data_uid := 45 ,_init_data_acc := 4 , _init_sysorg := true, _init_count:='count_rgallery'
303
304 ,_init_sync_sysdata :=true
305 --,_fnCheckPost := 'sumamt' -- Field to check cannot be changed when refered by posting data source
306 /*
307 ,_init_merge :=true
308 ,_init_unique_fn := 'ACC,title' -- unique field name
309 ,_init_unique_exc_updatefn := 'count_attr' -- exclude field, not to update if existing
310 */
311
312 ,_OrderNo_Updatable:=true
313
314 -- ,_matview :=true
315 ,_matview :=false
316 -- ,_mvAccID_share := 4
317 --,_mvOrderBy := 'firstname, lastname'
318 --,_mvStrict := true -- Check data is in scope of view when insert
319 ,_fts := false
320 ,_ftsfnvalexc := null -- 'WholeDay,PredecessorID,Complete,docid,count_imagefile, c_tktrack, CreateSchema, Onhand, Onhand_date, UoMID'
321);
322--*
323select "dbSys".tvcreateview( 45,4, '"dbDoc".tbRGallery', dropit:=true );
324
325-- select * from "dbDoc"."dbDoc".tbRGalleryVW4;