· 8 years ago · Mar 06, 2018, 03:26 AM
1/******************************************
2View Procedures
3*******************************************/
4 -- * Data selected for underlying user
5 drop function if exists "dbLegal".tbassettypeamtVW_UserData ( in UserID int , in AccID int
6
7) cascade;
8
9drop function if exists "dbLegal".tbassettypeamtVW_UserData ( in UserID int , in AccID int
10 ,in _id int
11) cascade;
12create or replace FUNCTION "dbLegal".tbassettypeamtVW_UserData ( in UserID int , in AccID int
13 ,in _id int = null
14
15)
16returns table (
17 id int
18 ,Title text -- "dbLegal".CaseAssetAmtType
19 ,TCustom varchar
20 ,TLabel varchar
21 ,Main boolean
22 ,Seq int
23 ,UID int
24 ,ACC int
25 ,orderno smallint
26 ,idgrp int
27 ,hide boolean
28 ,description text
29 ,pid int
30 ,caseamtid int
31) AS $$
32declare sql text; foundACC int;
33begin
34 return query
35 select
36 a.id
37 ,a.Title
38 ,a.TCustom
39 ,a.TLabel
40 ,if ( a.TCustom is null , true, false )
41 ,a.Seq
42 ,a.UID
43 ,a.ACC
44 ,b.orderno
45 ,a.idgrp
46 ,a.hide
47 ,a.description
48 ,a.pid
49 ,a.caseamtid
50 from
51 "dbLegal".tbassettypeamt a
52 left join "dbLegal".tbcaseamt b on b.id = a.caseamtid
53 where --a.acc = accid
54 case
55 when _id is null then a.acc=accid
56 else a.id = _id
57 end
58
59 order by a.pid, b.orderno
60 ;
61 end;
62 $$ LANGUAGE plpgsql ;
63
64-- * How to update underlying tables whose data is projected to this view
65drop function if exists "dbLegal".tbassettypeamtVW_UpdData() cascade;
66create or replace function "dbLegal".tbassettypeamtVW_UpdData()
67returns trigger as $$
68declare Grp varchar; Change boolean; sql text=null; lastid int;
69declare ContID int; LawOfficeContID int; _msg text; _ACC int;
70 rec record;
71begin
72 if (TG_OP = 'INSERT') then
73
74 if (new.tcustom is null ) and (new.tlabel is not null ) then
75 select title
76 from "dbLegal".tbassettypeamt
77 where
78 -- ACC=new.ACC
79 pid = new.pid
80 and Title = new.Title and TCustom is null limit 1
81 into rec;
82 if (rec.title is not null) then
83 new.TCustom := new.TLabel ;
84 end if;
85 end if;
86 -- raise notice '130 new.title %, new.tcustom %', new.title, new.tcustom;
87
88 if new.hide is null then
89 new.hide :=false;
90 end if;
91
92 insert into "dbLegal".tbassettypeamt (
93 Title
94 ,TCustom
95 ,TLabel
96 ,UID
97 ,ACC
98 ,orderno
99 ,hide
100 ,description
101 ,pid
102 ,caseamtid
103 ) select
104 new.Title
105 ,new.TCustom
106 ,new.TLabel
107 ,new.UID
108 ,new.ACC
109 ,new.orderno
110 ,new.hide
111 ,new.description
112 ,new.pid
113 ,new.caseamtid
114 returning id into new.id
115 ;
116
117 return NEW;
118 elseif (TG_OP = 'UPDATE') then
119 --*
120 if diff(new.TLabel, old.TLabel ) then
121 if new.TCustom is not null then
122 new.TCustom := new.TLabel;
123 end if;
124 end if;
125
126 if diff(new.hide,old.hide) and (new.hide) then
127 -- perform "dbLegal".tbcaseamt_delItemFromChilds(new.id, new.acc);
128 -- ** To Implement
129 end if;
130
131 -- Main
132 sql :=null;
133 sql := "dbSys".sqlSetValue( OLD.Title, NEW.Title, sql, 'Title' );
134 sql := "dbSys".sqlSetValue( OLD.TCustom, NEW.TCustom, sql, 'TCustom' );
135 sql := "dbSys".sqlSetValue( OLD.TLabel, NEW.TLabel, sql, 'TLabel' );
136 sql := "dbSys".sqlSetValue( OLD.UID, NEW.UID, sql, 'UID' );
137 sql := "dbSys".sqlSetValue( OLD.orderno, NEW.orderno, sql, 'orderno' );
138 sql := "dbSys".sqlSetValue( OLD.hide, NEW.hide, sql, 'hide' );
139 sql := "dbSys".sqlSetValue( OLD.description, NEW.description, sql, 'description' );
140 sql := "dbSys".sqlSetValue( OLD.caseamtid, NEW.caseamtid, sql, 'caseamtid' );
141 -- sql := update "dbLegal".tbassettypeamt set Title = Interest,TCustom = 'ดà¸à¸à¹€à¸šà¸µà¹‰à¸¢-2',UID = 20 where id = 14;
142
143 if sql is not null then
144 -- raise notice 'sql=%',sql;
145
146 sql := 'update "dbLegal".tbassettypeamt ' || sql ||
147 ' where id = '|| new.id ;
148 execute sql ;
149 end if;
150 return NEW;
151 elseif TG_OP = 'DELETE' then
152 -- raise notice 'del';
153
154 --perform "dbLegal".tbassettypeamt_delItemFromChilds(old.id, old.acc);
155 --delete from "dbLegal".tbassettypeamt where id = old.id;
156 perform "dbSys".grpdelete(OLD.id, '"dbLegal".tbassettypeamt' );
157
158 return OLD;
159 end if;
160end ; $$ LANGUAGE plpgsql;
161
162
163/*****************************
164 View registration
165******************************/
166/*
167select "dbSys".viewRegistration( '"dbLegal".tbassettypeamtVW'
168 , _delete_system_data := true
169 -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := 4 , _init_sysorg := true
170 ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null , _init_sysorg := true
171
172);
173*/
174
175/*****************************
176 View registration
177
178 select * from "dbSys".tbviewregistration where nam='tbassettypeamtvw';
179
180
181******************************/
182select "dbSys".viewRegistration( '"dbLegal".tbassettypeamtVW'
183 --, _delete_system_data := true
184 ,_init_data := true, _init_data_uid := varint('userid') ,_init_data_acc := varint('accid') , _init_sysorg := true
185 ,_init_sync_sysdata :=true, _init_count := 'count_amt'
186
187 ,_init_merge :=true
188 ,_init_unique_fn := 'pid,Title' -- unique field name
189 ,_init_unique_exc_updatefn := null -- exclude field, not to update if existing
190
191
192 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null , _init_sysorg := true
193 --,_OrderNo_Updatable :=true
194);
195
196-- create view
197select "dbSys".tvcreateview( varint('userid'),varint('accid'), '"dbLegal".tbassettypeamt' );
198
199-- select * from "dbLegal".tbassettypeamt