· 9 years ago · Nov 14, 2016, 05:54 AM
1/****************************************************************
2 View : Doc image file
3
4 Wutirkai Pornchai
5 2016-02-13
6*****************************************************************/
7/******************************************
8View Procedures
9*******************************************/
10 -- * Data selected for underlying user
11drop function if exists "dbInventory".tbImageFileVW_UserData ( in UserID int , in AccID int ) cascade;
12create or replace FUNCTION "dbInventory".tbImageFileVW_UserData ( in UserID int , in AccID int )
13returns table (
14 id int
15 ,Title text
16 ,FileName text
17 ,pid int
18 ,idgrp int
19 ,dis smallint
20 ,OrderNo smallint
21 ,upd timestamp_wtz
22 ,FileSize num
23 ,profile boolean
24 ,token text
25 ,thumbnail text
26 ,uid int
27 ,acc int
28 ,filetype text
29
30)
31 AS $$
32select
33 a.id
34 ,a.title
35 ,a.filename
36 ,a.pid
37 ,a.idgrp
38 ,a.dis
39 ,a.orderno
40 ,a.upd
41 ,a.filesize
42 ,a.profile
43 ,a.token
44 ,a.thumbnail
45 ,a.uid
46 ,a.acc
47 ,a.filetype
48from
49 "dbInventory".tbImageFile a
50
51where
52 --a.UID in (select UID from "dbUserAcc".tbaccuser where PID = AccID )
53 a.acc = accid
54
55$$ LANGUAGE sql;
56
57
58-- * How to update underlying tables whose data is projected to this view
59drop function if exists "dbInventory".tbImageFileVW_UpdData() cascade;
60create or replace function "dbInventory".tbImageFileVW_UpdData()
61returns trigger as $$
62declare sql text = null;
63begin
64 if (TG_OP = 'INSERT') then
65 /*
66 if new.upd is null then
67 new.upd := now();
68 end if;
69 insert into "dbInventory".tbimagefile (
70 filename
71 ,title
72 ,upd
73 ,filesize
74 ,pid
75 ,profile
76 ) values (
77 new.filename
78 ,new.title
79 ,new.upd
80 ,new.filesize
81 ,new.pid
82 ,new.profile
83 )
84 returning id into new.id ;
85 */
86 return NEW;
87 elseif (TG_OP = 'UPDATE') then
88
89 -- Create sql for field that have change
90 /*
91 sql := sqlSetValue( OLD.filename, NEW.filename, sql, 'filename' );
92 sql := sqlSetValue( OLD.upd, NEW.upd, sql, 'upd' );
93 sql := sqlSetValue( OLD.filesize, NEW.filesize, sql, 'filesize' );
94 sql := sqlSetValue( OLD.title, NEW.title, sql, 'title' );
95 sql := sqlSetValue( OLD.profile, NEW.profile, sql, 'profile' );
96 -- * Run sql
97 if sql is not null then
98 sql := 'update "dbInventory".tbimagefile ' || sql || ' where id = ' ;
99 execute sql || new.id ;
100 end if;
101 */
102 return NEW;
103 elseif TG_OP = 'DELETE' then
104
105 --perform "dbSys".grpdelete( old.id, '"dbInventory".tbimagefile' );
106 return OLD;
107 end if;
108end ; $$ LANGUAGE plpgsql;
109
110/************************************************************
111 Registration : inherite the group of dbContact
112*************************************************************/
113 select "dbSys".tblRegistration_Inherit( '"dbInventory".tbImageFileVW', '"dbContact".tbImageFileVW',true,true,true );
114
115
116
117/*****************************
118 View registration
119******************************/
120select "dbSys".viewRegistration( '"dbInventory".tbImageFileVW'
121 -- , pers := '"dbLegal".tbPInv'
122 --, _delete_system_data := true
123 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
124
125 --,_fnCheckPost := 'sumamt' -- Field to check cannot be changed when refered by posting data source
126
127 ,_OrderNo_Updatable:=true
128);