· 6 years ago · Sep 16, 2019, 11:10 PM
1/*****************************************************************************************
2 View for "dbUserAccReg".tbUserPrdMenu
3
4 Author : Wutikrai Prnchai
5 Since : 2015-10-20
6
7 select * from "dbSys".CreateViewFunction( '"dbUserAccReg".tbUserPrdMenu' );
8 select "dbSys".tvCreateView ( varint( 'userid'), varint('accid'), '"dbUserAccReg".tbUserPrdMenu');
9 select * from "dbUserAccReg".tbUserPrdMenuVW99;
10
11 select * from "dbSys".tvCreateView ( varint('userid_last') , varint('accid_last' ), '"dbUserAccReg".tbuserprdmenu');
12 select a.id,repeat(' ',4*a.dis)||a.title title, a.privilege,a.edition,a.pid from "dbUserAccReg".tbuserprdmenuVW51 a where a.pid = varint('userprodid');
13
14
15 ******************************************************************************************/
16
17
18/***************************
19 Get data
20 ****************************/
21 drop function if exists "dbUserAccReg".tbUserPrdMenuVW_UserData ( in _UserID int , in AccID int
22 , in _pid int
23 , in _uid int
24 , in _taskid int
25 , in _prodeditionid text
26 , in _id int
27 ) cascade; -- 1
28 create or replace FUNCTION "dbUserAccReg".tbUserPrdMenuVW_UserData ( in _UserID int , in AccID int
29 , in _pid int =null
30 , in _uid int =null
31 , in _taskid int =null
32 , in _prodeditionid text = null
33 , in _id int = null
34 ) -- 2
35 returns table (
36 id int
37 ,taskid integer
38 ,title text
39 ,privilege text
40
41 ,Edition boolean -- Meet Edition
42 ,Visible boolean
43 ,pid integer -- 2.1
44 ,dis smallint
45 ,idgrp int
46 ,uid int
47 ,acc int
48 ,userid int
49
50 ,menu_priv text
51 ,prog_priv text
52 ,group_prod_priv text
53 ,user_priv text
54 ,group_priv text
55 ,group_prog_priv text
56 ,user_group text
57 ,final_priv text
58 ,HelpLink text
59 ,DBSchema text
60 ,DBTable text
61
62 ) AS $x$
63 begin
64 return query execute $y$
65 select
66 a.id
67 ,a.taskid
68 --,repeat(' ',a1.dis*4) || a1.title
69 --, a1.title
70 ,a11.title
71
72 ,a.privilege
73
74 --,a.Edition
75 ,case
76 when a41.edition is null then true
77 when existstr(a3.prodeditionid , a41.edition )>0 then true
78 else false
79 end Edition
80
81 -- ,existstr(a5.final_priv , 'H,T,S' )=0 visible
82 ,existstr(a.privilege , 'H,T,S' )=0 visible
83
84 ,a.pid -- 3
85 ,a41.dis
86 , null::int idgrp
87 ,a.uid
88 ,a.acc
89 ,a.userid
90
91 ,a.privilege menu_priv -- menu of this user
92 ,a41.privilege prog_priv
93 ,a2111.privilege group_prod_priv
94 ,a21.privilege user_priv
95 ,a211.privilege group_priv
96 ,a21111.privilege group_prog_priv
97 ,a211.firstname::text user_group
98 ,a5.final_priv
99 ,a6.tvalue -- HelpLink
100 ,a0301.tvalue ::text DBSchema
101 ,a0302.tvalue ::text DBTable
102
103 from
104 "dbUserAccReg".tbUserPrdMenu a -- 4
105 left join "dbPM".tbtask a1 on (a1.id=a.taskid)
106 left join "dbCalen".tbcaltask a11 on a11.id = a1.plancaltaskid
107 left join "dbUserAccReg".tbuserprod a2 on (a2.id=a.pid) -- 5
108 -- left join "dbUserAcc".tbprod a3 on (a3.id = a.prodid_idgrp )
109 left join "dbUserAcc".tbprod a3 on (a3.id = a2.prodid )
110 left join "dbUserReg".tbuserreg a21 on a21.id = a2.pid
111 left join "dbUserReg".tbuserreg a211 on a211.id = a21.idgrp
112 left join "dbUserAccReg".tbuserprod a2111 on a2111.pid = a211.id and a2111.prodid = a2.prodid
113 left join "dbUserAccReg".tbuserprdmenu a21111 on a21111.pid = a2111.id and a21111.taskid = a.taskid
114 left join "dbUserAcc".tbuserprdmenu a4 on (a4.id = a.userprdmenuid )
115 left join "dbUserAcc".tbprodtask a41 on (a41.id = a4.prodtaskid )
116
117 left join lateral (
118 select a.id
119 ,case
120 when a41.privilege is null then -- prog_priv
121 case
122 when a21.idgrp is null then -- no group
123 case
124 when a41.privilege is null then -- prog_priv
125 case
126 when a2111.privilege is null then -- prod priv
127 case
128 --when a21.privilege is null then -- user priv
129 when a.privilege is null then -- menu priv
130
131 a21.privilege -- user priv
132 else a.privilege
133 end
134 else a2111.privilege
135 end
136 else a41.privilege
137 end
138 else -- have group
139 case
140 -- when a211.privilege is null then -- group_priv
141 when a21111.privilege is null then -- group_prog_priv
142 case
143 --when a21111.privilege is null then -- group_prog_priv
144 when a211.privilege is null then -- group_priv
145
146 -- a21.privilege -- a21
147 case
148 when a41.privilege is null then -- prog_priv
149 case
150 when a2111.privilege is null then -- prod priv
151 case
152 --when a21.privilege is null then -- user priv
153 when a.privilege is null then -- menu priv
154
155 a21.privilege -- user priv
156 else a.privilege
157 end
158 else a2111.privilege
159 end
160 else a41.privilege
161 end
162
163
164 else a211.privilege
165 end
166 else a21111.privilege
167
168 end
169 end
170 else a41.privilege -- Once prog priv is set, override all
171 end final_priv
172
173 ) a5 on a5.id = a.id
174 --* HelpLink : left join "dbPM".tbTkField a02 on (a02.pid = a.id and a02.Title = 'Custom' and a02.TCustom = 'HelpLink' )
175 left join "dbPM".tbtkfield a6 on a6.pid = a1.id and a6.title = 'Custom' and a6. tcustom = 'HelpLink'
176
177 left join "dbPM".tbTkField a0301 on (a0301.pid = a1.id and a0301.Title = 'DB Schema' and a0301.tcustom is null )
178 left join "dbPM".tbTkField a0302 on (a0302.pid = a1.id and a0302.Title = 'DB Table' and a0302.tcustom is null )
179
180 where
181 --a2.uid in (select u.uid from "dbUserAcc".tbaccuser u where u.pid = $6) -- 6
182 a.acc = $6
183 and case
184 when $1 is null then true
185 else a.pid = $1-- _pid
186
187 end
188 and case
189 when $2 is null then true
190 else a.userid = $2
191 end
192 and case
193 when $3 is null then true
194 else a.taskid = $3
195 end
196 and case
197 when $4 is null then true
198 else a.prodeditionid = $4
199
200 end
201 and case
202 when $5 is null then a.acc= $6 -- a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
203 else a.id = $5
204 end
205 order by a.pid, a41.path
206 $y$ using
207 _pid
208 ,_uid
209 ,_taskid
210 ,_prodeditionid
211 ,_id
212 ,accid
213 ;
214
215 end $x$ LANGUAGE plpgsql stable;
216
217
218 /***********************
219 Update data
220 ************************/
221 drop function if exists "dbUserAccReg".tbUserPrdMenuVW_UpdData() cascade; -- 1
222 create or replace function "dbUserAccReg".tbUserPrdMenuVW_UpdData() -- 2
223 returns trigger as $x$
224 declare sql text=null;
225 begin
226 if (TG_OP = 'INSERT') then --
227 /*
228 insert into "dbUserAccReg".tbUserPrdMenu( -- 3.1
229 taskid
230 ,privilege
231
232 ,Edition
233 ,pid -- 3.2
234 ) select
235 new.taskid
236 ,new.privilege
237
238 ,new.Edition
239 ,new.pid -- 3.3
240 returning id into new.id;
241 */
242 return NEW;
243 elseif (TG_OP = 'UPDATE' ) then -- 4
244
245
246 -- sql := "dbSys".sqlSetValue( OLD.taskid, NEW.taskid, sql,'taskid' );
247 sql := "dbSys".sqlSetValue( OLD.privilege, NEW.privilege, sql,'privilege' );
248 sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql,'uid' );
249
250 --sql := "dbSys".sqlSetValue( OLD.Edition, NEW.Edition, sql,'Edition' );
251 --sql := "dbSys".sqlSetValue( OLD.pid, NEW.pid, sql,'pid' ); -- 5.1
252 if sql is not null then
253 sql:= format('update "dbUserAccReg".tbUserPrdMenu %s where id = %s',sql, new.id); -- 5.2
254 execute sql;
255 end if;
256
257 return NEW;
258 elseif TG_OP = 'DELETE' then -- 5
259 -- delete from "dbUserAccReg".tbUserPrdMenu where id = old.id; -- 6
260 return OLD;
261 end if;
262 end $x$ LANGUAGE plpgsql;
263
264
265 /*****************************
266 View registration
267******************************/
268 select "dbSys".viewRegistration( '"dbUserAccReg".tbUserPrdMenuVW'
269 -- , pers := '"dbUserAccReg".tbUserPrdMenu'
270 -- , _delete_system_data := true
271 -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
272 -- ,_fnCheckPost := <fn> -- Field to check cannot be changed when refered by posting data source
273 ,_privSysRole := 'Admin'
274 );
275
276--* create view
277select "dbSys".tvCreateView ( varint('userid'), varint('accid'), '"dbUserAccReg".tbUserPrdMenu', dropit :=true );
278
279-- select * from "dbUserAccReg".tbUserPrdMenuVW51
280-- select * from "dbUserAccReg".tbUserPrdMenuVW4;