· 8 years ago · Mar 06, 2018, 03:56 PM
1/****************************************************************
2 tbCaseTranCalcVW.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".tbCaseTranCalcVW_UserData ( in UserID int , in AccID int
20 ,in _id int
21) cascade;
22create or replace FUNCTION "dbLegal".tbCaseTranCalcVW_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 ,LGStepProItemID int
35 ,LGStepProItemTitle text -- ,concat_ws(' ',d.Title,'-',d.id) ActiveStep
36 ,pid int
37 , uid int , acc int
38 ,loancalcid int
39 ,Active boolean
40 ,assettypeid int
41) AS $$
42
43begin
44 return query
45
46 select
47 a.id
48 ,a.DebtCalcID
49 ,a.DebtCalcID_Org
50 ,b.upd
51 ,b.DateStop::date
52 ,b1.tvalue Total
53 ,a.UpdateAmount
54 ,a.LGStepProItemID
55 ,d.title
56 ,a.pid
57 ,a.uid, a.acc
58 ,a.loancalcid
59 ,a.Active
60 ,a.assettypeid
61 from
62 "dbLegal".tbCaseTranCalc a
63 left join "dbLegal".tbDebtCalc b on ( b.id = a.DebtCalcID )
64 left join "dbLegal".tbDebtCalcSum b1 on ( b1.pid = b.id and b1.is_total )
65 -- inner join "dbLegal".tbCaseAmt b11 on (b11.id = b1.CaseAmtID and b11.title = 'Total' ) -- Main total
66
67 left join "dbLegal". tbLGStepProItem d on ( d.ID = a.LGStepProItemID )
68 where
69 --a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
70
71 case
72 when _id is null then a.acc=accid
73 else a.id = _id
74 end
75
76 order by
77 a.pid, b.upd ;
78 end;
79$$ LANGUAGE plpgsql ;
80
81-- * How to update underlying tables whose data is projected to this view
82drop function if exists "dbLegal".tbCaseTranCalcVW_UpdData() cascade;
83create or replace function "dbLegal".tbCaseTranCalcVW_UpdData()
84returns trigger as $$
85declare sql text; err text;
86begin
87 if (TG_OP = 'INSERT') then
88 insert into "dbLegal".tbCaseTranCalc ( pid, uid, active )
89 select NEW.pid, new.uid, new.active
90 returning id, DebtCalcID, DebtCalcID_Org into new.id, new.DebtCalcID,new.DebtCalcID_Org ;
91
92 return NEW;
93 elseif (TG_OP = 'UPDATE') then
94 sql := null;
95 sql := "dbSys".sqlSetValue( OLD.UpdateAmount, NEW.UpdateAmount, sql, 'UpdateAmount' );
96 sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql, 'uid' );
97 sql := "dbSys".sqlSetValue( OLD.active, NEW.active, sql, 'active' );
98 if sql is not null then
99 sql := 'update "dbLegal".tbCaseTranCalc ' || sql || ' where id = '|| new.id ;
100 execute sql ;
101 end if;
102
103 return NEW;
104 elseif TG_OP = 'DELETE' then
105 /*
106 if not old.active then
107 err := 'Can not delete, it is not active';
108 select msg from "dbSys".sysCallErrMsg(
109 93
110 ,null -- talbename = description
111 ,null -- new.id
112 ,null -- NEW.uID
113 ,errmsg := err
114 )
115 into err;
116 -- raise notice '265 err %', err;
117 raise exception '%', format(err );
118 end if; */
119
120 delete from "dbLegal".tbCaseTranCalc where id = old.id;
121 return OLD;
122 end if;
123end ; $$ LANGUAGE plpgsql;
124
125
126/*****************************
127 View registration
128******************************/
129select "dbSys".viewRegistration( '"dbLegal".tbCaseTranCalcVW'
130 -- , pers := '"dbLegal".tbPInv'
131 --, _delete_system_data := true
132 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
133);
134
135
136
137--* create view
138select "dbSys".tvCreateView ( varint('userid'), varint('accid'), '"dbLegal".tbcasetrancalc' );