· 9 years ago · Oct 13, 2016, 09:14 AM
1
2--* Contact text field
3drop table if exists "dbContact".tpContNameType cascade;
4create table if not exists "dbContact".tpContNameType ( field text not null primary key,tcustom text null, uid int null ); -- null uid = system data
5insert into "dbContact".tpContNameType values
6 ('Native')
7 ,('English')
8 ,('Custom') -- à¸à¸³à¸«à¸™à¸”เà¸à¸‡
9
10;
11
12/*********************************************************************************
13Table : tbContName
14Description : Alternate name of contact
15
16Author : Wutikrai Pornchai
17Since : 02-10-2013
18
19PostgreSQL : 06-11-2013
20
21**********************************************************************************/
22
23drop table if exists "dbContact".tbContName cascade;
24
25drop sequence if exists "dbContact".tbContName_Seq;
26create SEQUENCE "dbContact".tbContName_Seq;
27
28CREATE TABLE IF NOT EXISTS "dbContact".tbContName(
29 ID INT NOT NULL default nextval('"dbContact".tbContName_Seq') primary key
30 ,Title varchar default 'English' references "dbContact".tpContNametype -- "dbContact".ContNameType default 'English' ,
31 ,TCustom varchar null -- comment 'Customer title',
32 ,Prefix varchar NULL -- comment'salutation',
33 ,TValue varchar null -- comment 'First name',
34 ,LastName varchar null
35 ,Suffix varchar null
36 ,PID int not null -- comment 'Contact',
37 ,foreign key (PID) references "dbContact".tbContact(ID) on delete cascade on update cascade
38);
39alter SEQUENCE "dbContact".tbContName_Seq owned by "dbContact".tbContName.ID;
40
41create index idxTContName_PID on "dbContact".tbContName (PID);
42
43create unique index idxunqContName_PID_Title_TCustom on "dbContact".tbContName( PID, Title, TCustom ) where TCustom is not null ;
44create unique index idxunqContName_PID_Title on "dbContact".tbContName( PID, Title ) where TCustom is null ;
45
46 /************************************************************
47 Registration
48
49 select * from "dbSys".CreateViewFunction( '"dbContact".tbContName' );
50
51*************************************************************/
52select "dbSys".tblRegistration( '"dbContact".tbContName',true );
53
54
55 /*****************************************************************************************
56 View for "dbContact".tbContName
57
58 Author : Wutikrai Prnchai
59 Since : 2016-10-13
60
61 select * from "dbSys".CreateViewFunction( '"dbContact".tbContName' );
62 select "dbSys".tvCreateView ( varint( 'userid'), varint('accid'), '"dbContact".tbContName');
63 select * from "dbContact".tbContNameVW99;
64 ******************************************************************************************/
65
66
67/***************************
68 Get data
69 ****************************/
70 drop function if exists "dbContact".tbContNameVW_UserData ( in UserID int , in AccID int ) cascade; -- 1
71 create or replace FUNCTION "dbContact".tbContNameVW_UserData ( in UserID int , in AccID int ) -- 2
72 returns table (
73 id int
74 ,title character varying
75 ,tcustom character varying
76 ,prefix character varying
77 ,tvalue character varying
78 ,lastname character varying
79 ,suffix character varying
80 ,pid integer
81 ,uid integer -- 2.1
82 ) AS $x$
83 begin
84 return query
85 select
86 a.id
87 ,a.title
88 ,a.tcustom
89 ,a.prefix
90 ,a.tvalue
91 ,a.lastname
92 ,a.suffix
93 ,a.pid
94 ,a.uid -- 3
95 from
96 "dbContact".tbContName a -- 4
97 left join "dbContact".tpcontnametype a1 on (a1.field=a.title)
98 left join "dbContact".tbcontact a2 on (a2.id=a.pid)
99 left join "dbUserAcc".tbuser a3 on (a3.id=a.uid) -- 5
100 where
101 a2.uid in (select u.uid from "dbUserAcc".tbaccuser u where u.pid = accid) -- 6
102 ;
103 end $x$ LANGUAGE plpgsql;
104
105
106/***********************
107 Update data
108 ************************/
109 drop function if exists "dbContact".tbContNameVW_UpdData() cascade; -- 1
110 create or replace function "dbContact".tbContNameVW_UpdData() -- 2
111 returns trigger as $x$
112 declare sql text=null;
113 begin
114 if (TG_OP = 'INSERT') then -- 3
115 insert into "dbContact".tbContName( -- 3.1
116 title
117 ,tcustom
118 ,prefix
119 ,tvalue
120 ,lastname
121 ,suffix
122 ,pid
123 ,uid -- 3.2
124 ) select
125 new.title
126 ,new.tcustom
127 ,new.prefix
128 ,new.tvalue
129 ,new.lastname
130 ,new.suffix
131 ,new.pid
132 ,new.uid -- 3.3
133 returning id into new.id;
134 return NEW;
135 elseif (TG_OP = 'UPDATE' ) then -- 4
136 sql := "dbSys".sqlSetValue( OLD.title, NEW.title, sql,'title' );
137 sql := "dbSys".sqlSetValue( OLD.tcustom, NEW.tcustom, sql,'tcustom' );
138 sql := "dbSys".sqlSetValue( OLD.prefix, NEW.prefix, sql,'prefix' );
139 sql := "dbSys".sqlSetValue( OLD.tvalue, NEW.tvalue, sql,'tvalue' );
140 sql := "dbSys".sqlSetValue( OLD.lastname, NEW.lastname, sql,'lastname' );
141 sql := "dbSys".sqlSetValue( OLD.suffix, NEW.suffix, sql,'suffix' );
142 sql := "dbSys".sqlSetValue( OLD.pid, NEW.pid, sql,'pid' );
143 sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql,'uid' ); -- 5.1
144 if sql is not null then
145 sql:= format('update "dbContact".tbContName %s where id = %s',sql, new.id); -- 5.2
146 execute sql;
147 end if;
148 return NEW;
149 elseif TG_OP = 'DELETE' then -- 5
150 delete from "dbContact".tbContName where id = old.id; -- 6
151 -- perform "dbSys".grpdelete( old.id, '"dbContact".tbContName' ); -- 6.1
152 return OLD;
153 end if;
154 end $x$ LANGUAGE plpgsql;
155
156
157 /*****************************
158 View registration
159 ******************************/
160 select "dbSys".viewRegistration( '"dbContact".tbContNameVW' -- 1
161 -- , pers := '"dbContact".tbContName' -- 2
162 -- , _delete_system_data := true
163 -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
164 -- ,_fnCheckPost := <fn> -- Field to check cannot be changed when refered by posting data source
165 -- ,_OrderNo_Updatable:= false -- 3
166 );
167
168
169
170 /*****************************************************************************************
171 View for "dbContact".tbContText
172
173 Author : Wutikrai Prnchai
174 Since : 2016-10-13
175
176 select * from "dbSys".CreateViewFunction( '"dbContact".tbContText' );
177 select "dbSys".tvCreateView ( varint( 'userid'), varint('accid'), '"dbContact".tbContText');
178 select * from "dbContact".tbContTextVW4;
179 ******************************************************************************************/
180
181
182
183 /***************************
184 Get data
185 ****************************/
186 drop function if exists "dbContact".tbContTextVW_UserData ( in UserID int , in AccID int ) cascade; -- 1
187 create or replace FUNCTION "dbContact".tbContTextVW_UserData ( in UserID int , in AccID int ) -- 2
188 returns table (
189 id int
190 ,title text
191 ,tcustom text
192 ,tvalue text
193 ,pid integer
194 ,uid integer -- 2.1
195 ) AS $x$
196 begin
197 return query
198 select
199 a.id
200 ,a.title
201 ,a.tcustom
202 ,a.tvalue
203 ,a.pid
204 ,a.uid -- 3
205 from
206 "dbContact".tbContText a -- 4
207 left join "dbContact".tpcontacttexttype a1 on (a1.field=a.title)
208 left join "dbContact".tbcontact a2 on (a2.id=a.pid)
209 left join "dbUserAcc".tbuser a3 on (a3.id=a.uid) -- 5
210 where
211 a2.uid in (select u.uid from "dbUserAcc".tbaccuser u where u.pid = accid) -- 6
212 ;
213 end $x$ LANGUAGE plpgsql;
214
215/***********************
216 Update data
217 ************************/
218 drop function if exists "dbContact".tbContTextVW_UpdData() cascade; -- 1
219 create or replace function "dbContact".tbContTextVW_UpdData() -- 2
220 returns trigger as $x$
221 declare sql text=null;
222 begin
223 if (TG_OP = 'INSERT') then -- 3
224 insert into "dbContact".tbContText( -- 3.1
225 title
226 ,tcustom
227 ,tvalue
228 ,pid
229 ,uid -- 3.2
230 ) select
231 new.title
232 ,new.tcustom
233 ,new.tvalue
234 ,new.pid
235 ,new.uid -- 3.3
236 returning id into new.id;
237 return NEW;
238 elseif (TG_OP = 'UPDATE' ) then -- 4
239 sql := "dbSys".sqlSetValue( OLD.title, NEW.title, sql,'title' );
240 sql := "dbSys".sqlSetValue( OLD.tcustom, NEW.tcustom, sql,'tcustom' );
241 sql := "dbSys".sqlSetValue( OLD.tvalue, NEW.tvalue, sql,'tvalue' );
242 sql := "dbSys".sqlSetValue( OLD.pid, NEW.pid, sql,'pid' );
243 sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql,'uid' ); -- 5.1
244 if sql is not null then
245 sql:= format('update "dbContact".tbContText %s where id = %s',sql, new.id); -- 5.2
246 execute sql;
247 end if;
248 return NEW;
249 elseif TG_OP = 'DELETE' then -- 5
250 delete from "dbContact".tbContText where id = old.id; -- 6
251 -- perform "dbSys".grpdelete( old.id, '"dbContact".tbContText' ); -- 6.1
252 return OLD;
253 end if;
254 end $x$ LANGUAGE plpgsql;
255
256 /*****************************
257 View registration
258 ******************************/
259 select "dbSys".viewRegistration( '"dbContact".tbContTextVW' -- 1
260 -- , pers := '"dbContact".tbContText' -- 2
261 -- , _delete_system_data := true
262 -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
263 -- ,_fnCheckPost := <fn> -- Field to check cannot be changed when refered by posting data source
264 -- ,_OrderNo_Updatable:= false -- 3
265 );