· 9 years ago · Jan 13, 2017, 04:08 PM
1/****************************************************************
2
3 Wutirkai Pornchai
4 14-09-2014
5
6*****************************************************************/
7/******************************************
8View Procedures
9*******************************************/
10 -- * Data selected for underlying user
11drop function if exists "dbLegal".tbLGStepProItemDocTypeVW_UserData ( in UserID int , in AccID int ) cascade;
12create or replace FUNCTION "dbLegal".tbLGStepProItemDocTypeVW_UserData ( in UserID int , in AccID int )
13returns table
14( id int
15 ,Title text
16 ,DocID int
17 ,DocTypeID int-- null references "dbDoc".tbdoctype(ID) , -- unsigned null comment 'Document type id',
18 ,DocType text
19 ,Description text -- null, --- comment 'Describ the doc type is for specific purpose',
20 ,QtyOrg smallint -- null, -- tinyint unsigned null comment 'number of original',
21 ,QtyCopy smallint -- null, -- tinyint unsigned null comment 'number of copy',
22 ,Required boolean -- null default 'N', --enum ('N','Y') default 'N' comment 'Is required or not',
23 -- PID int unsigned not null comment 'Parent ID' ,
24 ,PID int -- not null references "dbLegal".tbLGStepProItem(ID) on delete cascade on update cascade -- unsigned not null comment 'Parent ID' ,
25 ,OrderNo smallint ,idgrp int ,fqty smallint
26 ,uid int
27 ,EachDefd boolean
28 ,lgstepproid int
29 ,docid_template int
30 ,title_template text
31
32)
33 AS $$
34
35 select
36 a.id
37 ,case when a2.title is not null then a2.title else c.title end
38 ,a.docid
39 ,a.DocTypeID --int-- null references "dbDoc".tbdoctype(ID) , -- unsigned null comment 'Document type id',
40 ,c.title DocType
41 ,a.Description -- text -- null, --- comment 'Describ the doc type is for specific purpose',
42 ,a.QtyOrg -- smallint -- null, -- tinyint unsigned null comment 'number of original',
43 ,a.QtyCopy -- smallint -- null, -- tinyint unsigned null comment 'number of copy',
44 ,a.Required -- boolean -- null default 'N', --enum ('N','Y') default 'N' comment 'Is required or not',
45
46 -- PID int unsigned not null comment 'Parent ID' ,
47 ,a.PID -- int -- not null references "dbLegal".tbLGStepProItem(ID) on delete cascade on update cascade -- unsigned not null comment 'Parent ID' ,
48 ,a.orderno ,a.idgrp ,a.fqty
49 ,a.uid
50 ,a.EachDefd
51 ,a.lgstepproid
52 ,a.docid_template
53 ,a3.title title_template
54 from "dbLegal".tbLGStepProItemDocType a
55
56 left join "dbLegal".tbLGStepProItem b on ( b.id = a.pid )
57 left join "dbDoc".tbdoctype c on (c.id = a.doctypeid )
58
59 left join "dbSys".grpGetData( tbl:='"dbLegal".tbLGStepProItemDocType',_pid := b.id ) a1 on (a1.id = a.id )
60 left join "dbDoc".tbdoc a2 on a2.id = a.docid
61 left join "dbDoc".tbdoc a3 on a3.id = a.docid_template
62
63 where
64 a.uid in (select z.uid from "dbUserAcc".tbaccuser z where z.pid = accid )
65 order by a1.path
66 ;
67
68$$ LANGUAGE sql;
69
70/*
71create or replace function "dbLegal".tbLGStepProItemDocTypeVW_MoveIDToOrderNo(
72 in newid int, in newOrderNo smallint , in newpid int , in newidgrp int , in tbl text
73 , out _OrderNo smallint
74)
75 as $$
76declare tar record; sql text;
77begin
78
79 --* Get exist record of existing order no
80 sql := format(
81 'select id, orderno from %s where orderno = %s and pid = %s and idgrp %s'
82 , tbl
83 , neworderno
84 , newpid
85 ,case
86 when newidgrp is not null then ' = '||newidgrp
87 else ' is null '
88 end
89 );
90 execute sql into tar;
91
92
93
94 -- raise notice '73 orderno =%,idgrp=%, tarid=%, tar.orderno = %', neworderno ,newidgrp , tar.id ,tar.orderno;
95
96 --*
97 if (tar.id is not null) then
98 perform "dbSys".grpMove_ID_TO_ID( newid , tar.id ,tbl );
99 _OrderNo := newOrderno;
100 end if;
101
102end $$ language plpgsql; */
103
104-- * How to update underlying tables whose data is projected to this view
105drop function if exists "dbLegal".tbLGStepProItemDocTypeVW_UpdData() cascade;
106create or replace function "dbLegal".tbLGStepProItemDocTypeVW_UpdData()
107returns trigger as $$
108declare Grp varchar; GrpID int; Change boolean; sql text; lastid int;
109 rec record;
110begin
111 if (TG_OP = 'INSERT') then
112 new.Required := if(new.Required is null, false, new.Required );
113
114 insert into "dbLegal".tbLGStepProItemDocType (
115 DocTypeID -- int-- null references "dbDoc".tbdoctype(ID) , -- unsigned null comment 'Document type id',
116 ,Description -- text -- null, --- comment 'Describ the doc type is for specific purpose',
117 ,QtyOrg -- smallint -- null, -- tinyint unsigned null comment 'number of original',
118 ,QtyCopy -- smallint -- null, -- tinyint unsigned null comment 'number of copy',
119 ,Required -- boolean -- null default 'N', --enum ('N','Y') default 'N' comment 'Is required or not',
120
121 -- PID int unsigned not null comment 'Parent ID' ,
122 ,PID -- int -- not null references "dbLegal".tbLGStepProItem(ID) on delete cascade on update cascade -- unsigned not null comment 'Parent ID' ,
123 -- ,orderno
124 ,uid
125 ,EachDefd
126 ) select
127 new.DocTypeID -- int-- null references "dbDoc".tbdoctype(ID) , -- unsigned null comment 'Document type id',
128 ,new.Description -- text -- null, --- comment 'Describ the doc type is for specific purpose',
129 ,new.QtyOrg -- smallint -- null, -- tinyint unsigned null comment 'number of original',
130 ,new.QtyCopy -- smallint -- null, -- tinyint unsigned null comment 'number of copy',
131 ,new.Required -- boolean -- null default 'N', --enum ('N','Y') default 'N' comment 'Is required or not',
132
133 -- PID int unsigned not null comment 'Parent ID' ,
134 ,new.PID -- int -- not null references "dbLegal".tbLGStepProItem(ID) on delete cascade on update cascade -- unsigned not null comment 'Parent ID' ,
135 -- ,new.orderno
136 ,new.uid
137 ,new.EachDefd
138 returning id into new.id;
139
140 --** order
141 /*
142 if new.orderno is not null then
143 select _orderno from "dbLegal".tbLGStepProItemDocTypeVW_MoveIDToOrderNo(
144 new.id, new.orderno, new.pid, new.idgrp , '"dbLegal".tbLGStepProItemDocType'
145 ) into new.orderNo;
146 end if; */
147
148
149 return NEW ;
150 elseif (TG_OP = 'UPDATE') then
151 sql := null;
152 sql := sqlSetValue( OLD.DocTypeID, NEW.DocTypeID, sql, 'DocTypeID' );
153 sql := sqlSetValue( OLD.Description, NEW.Description, sql, 'Description' );
154
155 sql := sqlSetValue( OLD.QtyOrg, NEW.QtyOrg, sql, 'QtyOrg' );
156 sql := sqlSetValue( OLD.QtyCopy, NEW.QtyCopy, sql, 'QtyCopy' );
157 sql := sqlSetValue( OLD.Required, NEW.Required, sql, 'Required' );
158
159 sql := sqlSetValue( OLD.PID, NEW.PID, sql, 'PID' );
160 sql := sqlSetValue( OLD.EachDefd, NEW.EachDefd, sql, 'EachDefd' );
161
162 -- sql := sqlSetValue( OLD.orderno, NEW.orderno, sql, 'orderno' );
163 sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql, 'uid' );
164 if sql is not null then
165 sql := 'update "dbLegal".tbLGStepProItemDocType ' || sql || ' where id = ' || new.id;
166 execute sql ;
167 end if;
168
169 return NEW;
170 elseif TG_OP = 'DELETE' then
171 -- delete from "dbLegal".tbLGStepProItemDocType where id = old.id; -- record id of this decendant
172 perform "dbSys".grpDelete (old.id , '"dbLegal".tbLGStepProItemDocType' );
173 return OLD;
174
175 end if;
176end ; $$ LANGUAGE plpgsql;
177
178/************************************************************
179 Registration : inheritance
180*************************************************************/
181-- select "dbSys".tblRegistration_Inherit( '"dbLegal".tbLGStepProItemDocTypeVW','"dbBase".tbLGStepProItemDocTypeVW', false, true, true );
182
183/*****************************
184 View registration
185******************************/
186select "dbSys".viewRegistration( '"dbLegal".tbLGStepProItemDocTypeVW'
187 , _delete_system_data := false
188 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
189 , _OrderNo_Updatable := true
190
191);