3jLiCmeL

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