RFrYqSXd

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  "dbDoc".tbImageFileVW_UserData ( in UserID int , in AccID int )  cascade;
12create  or replace FUNCTION "dbDoc".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)
29 AS $$
30select 
31  a.id
32  ,a.title
33  ,a.filename 
34  ,a.pid
35  ,a.idgrp 
36  ,a.dis 
37  ,a.orderno
38  ,a.upd
39  ,a.filesize
40  ,a.profile
41  ,a.token 
42  ,a.thumbnail 
43  ,a.uid
44  ,a.acc
45from 
46	"dbDoc".tbImageFile a
47 
48where 
49  -- a.UID in    (select UID from  "dbUserAcc".tbaccuser where PID = AccID )
50  a.acc = accid
51
52$$ LANGUAGE sql;
53
54
55-- * How to update underlying tables whose data is projected to this view 
56drop function if exists "dbDoc".tbImageFileVW_UpdData()  cascade;
57create or replace function "dbDoc".tbImageFileVW_UpdData() 
58returns trigger as $$
59declare  sql text = null; 
60begin
61  if (TG_OP = 'INSERT') then 
62
63    if new.upd is null then 
64      new.upd := now();
65    end if;
66    insert into "dbDoc".tbimagefile  ( 
67      filename
68      ,title
69      ,upd
70      ,filesize
71      ,pid
72      ,profile
73			,token 
74			,thumbnail 
75      ,uid
76      ,acc
77    ) values (
78      new.filename
79      ,new.title
80      ,new.upd
81      ,new.filesize
82      ,new.pid
83      ,new.profile
84			,new.token  
85			,NEW.thumbnail  
86      ,new.uid
87      ,new.acc
88    )
89  returning id into new.id ;
90		
91    return NEW;
92  elseif (TG_OP = 'UPDATE') then
93    -- Create sql for field that have change
94    sql := sqlSetValue( OLD.filename, NEW.filename, sql, 'filename' );
95    sql := sqlSetValue( OLD.upd, NEW.upd, sql, 'upd' );
96    sql := sqlSetValue( OLD.filesize, NEW.filesize, sql, 'filesize' );
97    sql := sqlSetValue( OLD.title, NEW.title, sql, 'title' );  
98    sql := sqlSetValue( OLD.profile, NEW.profile, sql, 'profile' );  
99    sql := sqlSetValue( OLD.token, NEW.token, sql, 'token' );      
100    sql := sqlSetValue( OLD.thumbnail, NEW.thumbnail, sql, 'thumbnail' );  
101    sql := sqlSetValue( OLD.pid, NEW.pid, sql, 'pid' );  
102    sql := sqlSetValue( OLD.uid, NEW.uid, sql, 'uid' );  
103    sql := sqlSetValue( OLD.acc, NEW.acc, sql, 'acc' );  
104
105    -- * Run sql 
106    if sql is not null then 
107      sql := 'update "dbDoc".tbimagefile ' || sql || ' where id = ' ;
108      execute sql || new.id ;
109		end if;
110  
111    return NEW;
112  elseif TG_OP = 'DELETE' then 
113
114    --delete from "dbDoc".tbimagefile where id = OLD.id;
115    perform "dbSys".grpdelete( old.id, '"dbDoc".tbimagefile' );
116    return OLD;
117  end if;
118end ; $$ LANGUAGE plpgsql;
119
120
121/*****************************
122  View registration 
123******************************/
124select "dbSys".viewRegistration( '"dbDoc".tbImageFileVW' 
125  -- , pers := '"dbLegal".tbPInv'
126  --, _delete_system_data := true 
127  --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null 
128
129  --,_fnCheckPost := 'sumamt' --  Field to check cannot be changed when refered by posting data source
130
131  ,_OrderNo_Updatable:=true
132);