· 8 years ago · Mar 06, 2018, 04:00 PM
1/****************************************************************
2 tbCaseAssetCalcVW.sql
3
4 A collection of procedures related to View. Please follow this cnonvestional here.
5
6 2 functions as mandatory for view :-
7 - Prepare data for VIEW. Function name must be formated "Schema".tablenameVWM_UserData( ... )
8 - Update data fro VIEW. Function name must
9
10 Wutirkai Pornchai
11
12 16-05-2014
13
14*****************************************************************/
15/******************************************
16View Procedures
17*******************************************/
18 -- * Data selected for underlying user
19drop function if exists "dbLegal".tbCaseAssetCalcVW_UserData ( in UserID int , in AccID int
20 ,in _id int
21) cascade;
22create or replace FUNCTION "dbLegal".tbCaseAssetCalcVW_UserData ( in UserID int , in AccID int
23 ,in _id int = null
24
25)
26returns table (
27 id int
28 ,DebtCalcID int
29 ,DebtCalcID_Org int
30 ,upd timestamp
31 ,DateStop date
32 ,Total num
33 ,UpdateAmount boolean
34 ,pid int
35 ,uid int
36 ,acc int
37 ,active boolean
38 ,assettypeid int
39) AS $$
40
41begin
42 return query
43
44 select
45 a.id
46 ,a.DebtCalcID
47 ,a.DebtCalcID_Org
48 ,b.upd
49 ,b.DateStop::date
50 ,b1.tvalue Total
51 ,a.UpdateAmount
52 ,a.pid
53 ,a.uid
54 ,b.acc
55 ,a.active
56 ,a.assettypeid
57 from
58 "dbLegal".tbCaseAssetCalc a
59 left join "dbLegal".tbDebtCalc b on ( b.id = a.DebtCalcID )
60 left join "dbLegal".tbDebtCalcSum b1 on ( b1.pid = b.id and b1.is_total)
61
62 -- inner join "dbLegal".tbCaseAmt b11 on (b11.id = b1.CaseAmtID and b11.title = 'Total' and b11.tcustom is null ) -- Main total
63 --left join "dbLegal".tbCaseAmt b11 on (b11.id = b1.CaseAmtID and b11.title = 'Total' and b11.tcustom is null ) -- Main total
64
65
66 where
67 --a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
68
69 case
70 when _id is null then a.acc=accid
71 else a.id = _id
72 end
73 order by a.pid, b.upd desc ;
74 end;
75$$ LANGUAGE plpgsql ;
76
77-- select * from "dbLegal".tbDebtCalcSum where id=id_total
78
79-- * How to update underlying tables whose data is projected to this view
80drop function if exists "dbLegal".tbCaseAssetCalcVW_UpdData() cascade;
81create or replace function "dbLegal".tbCaseAssetCalcVW_UpdData()
82returns trigger as $$
83declare sql text; err text;
84begin
85 if (TG_OP = 'INSERT') then
86 insert into "dbLegal".tbCaseAssetCalc ( pid )
87 select NEW.pid
88 returning id, DebtCalcID, DebtCalcID_Org, active into new.id, new.DebtCalcID,new.DebtCalcID_Org, new.active ;
89
90
91 return NEW;
92 elseif (TG_OP = 'UPDATE') then
93
94 sql := null;
95 sql := "dbSys".sqlSetValue( OLD.UpdateAmount, NEW.UpdateAmount, sql, 'UpdateAmount' );
96 sql := "dbSys".sqlSetValue( OLD.active, NEW.active, sql, 'active' );
97
98 -- raise notice '78 ,sql =%', sql;
99 if sql is not null then
100
101 sql := 'update "dbLegal".tbCaseAssetCalc ' || sql ||
102 ' where id = '|| new.id ;
103 raise notice '83, sql=%',sql;
104 execute sql ;
105
106 end if;
107 new.UpdateAmount :=false;
108 -- raise notice 'xx';
109 return NEW;
110 elseif TG_OP = 'DELETE' then
111 /*
112 if not old.active then
113 err := 'Can not delete, it is not active';
114 select msg from "dbSys".sysCallErrMsg(
115 93
116 ,null -- talbename = description
117 ,null -- new.id
118 ,null -- NEW.uID
119 ,errmsg := err
120 )
121 into err;
122 -- raise notice '265 err %', err;
123 raise exception '%', format(err );
124 end if;
125 */
126
127 delete from "dbLegal".tbCaseAssetCalc where id = old.id;
128 --perform "dbSys".grpDelete( old.id, '"dbLegal".tbCaseAssetCalc');
129 return OLD;
130 end if;
131end ; $$ LANGUAGE plpgsql;
132
133
134/*****************************
135 View registration
136******************************/
137select "dbSys".viewRegistration( '"dbLegal".tbCaseAssetCalcVW'
138 --, _delete_system_data := true
139 -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := 4 , _init_sysorg := true
140 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null , _init_sysorg := true
141
142);
143
144--* create view
145select "dbSys".tvcreateview(varint('userid'),varint('accid'),'"dbLegal".tbCaseAssetCalc');