· 8 years ago · Feb 09, 2018, 02:18 AM
1drop table if exists cashreceiptitem_rpt_noledger;
2drop table if exists cashreceiptitem_rpt;
3drop table if exists rptledger_payment_share;
4drop table if exists rptledger_payment_item;
5drop table if exists rptledger_payment;
6
7
8CREATE TABLE `rptledger_payment` (
9 `objid` varchar(100) NOT NULL,
10 `rptledgerid` varchar(50) NOT NULL,
11 `type` varchar(20) NOT NULL,
12 `receiptid` varchar(50) NULL,
13 `receiptno` varchar(50) NOT NULL,
14 `receiptdate` date NOT NULL,
15 `paidby_name` longtext NOT NULL,
16 `paidby_address` varchar(150) NOT NULL,
17 `postedby` varchar(100) NOT NULL,
18 `postedbytitle` varchar(50) NOT NULL,
19 `dtposted` datetime NOT NULL,
20 `fromyear` int(11) NOT NULL,
21 `fromqtr` int(11) NOT NULL,
22 `toyear` int(11) NOT NULL,
23 `toqtr` int(11) NOT NULL,
24 `amount` decimal(12,2) NOT NULL,
25 `collectingagency` varchar(50) DEFAULT NULL,
26 `voided` int(11) NOT NULL,
27 PRIMARY KEY (`objid`)
28) ENGINE=InnoDB DEFAULT CHARSET=utf8;
29
30
31create index `fk_rptledger_payment_rptledger` on rptledger_payment(`rptledgerid`) using btree;
32create index `fk_rptledger_payment_cashreceipt` on rptledger_payment(`receiptid`) using btree;
33create index `ix_receiptno` on rptledger_payment(`receiptno`) using btree;
34
35alter table rptledger_payment
36add constraint `fk_rptledger_payment_cashreceipt` foreign key (`receiptid`) references `cashreceipt` (`objid`);
37
38alter table rptledger_payment
39add constraint `fk_rptledger_payment_rptledger` foreign key (`rptledgerid`) references `rptledger` (`objid`);
40
41
42CREATE TABLE `rptledger_payment_item` (
43 `objid` varchar(50) NOT NULL,
44 `parentid` varchar(100) NOT NULL,
45 `rptledgerfaasid` varchar(50) DEFAULT NULL,
46 `rptledgeritemid` varchar(50) DEFAULT NULL,
47 `rptledgeritemqtrlyid` varchar(50) DEFAULT NULL,
48 `year` int(11) NOT NULL,
49 `qtr` int(11) NOT NULL,
50 `basic` decimal(16,2) NOT NULL,
51 `basicint` decimal(16,2) NOT NULL,
52 `basicdisc` decimal(16,2) NOT NULL,
53 `basicidle` decimal(16,2) NOT NULL,
54 `basicidledisc` decimal(16,2) DEFAULT NULL,
55 `basicidleint` decimal(16,2) DEFAULT NULL,
56 `sef` decimal(16,2) NOT NULL,
57 `sefint` decimal(16,2) NOT NULL,
58 `sefdisc` decimal(16,2) NOT NULL,
59 `firecode` decimal(10,2) DEFAULT NULL,
60 `sh` decimal(16,2) NOT NULL,
61 `shint` decimal(16,2) NOT NULL,
62 `shdisc` decimal(16,2) NOT NULL,
63 `total` decimal(16,2) DEFAULT NULL,
64 `revperiod` varchar(25) DEFAULT NULL,
65 `partialled` int(11) NOT NULL,
66 PRIMARY KEY (`objid`)
67) ENGINE=InnoDB DEFAULT CHARSET=utf8;
68
69create index `FK_rptledger_payment_item_parentid` on rptledger_payment_item(`parentid`) using btree;
70create index `FK_rptledger_payment_item_rptledgerfaasid` on rptledger_payment_item(`rptledgerfaasid`) using btree;
71create index `ix_rptledgeritemid` on rptledger_payment_item(`rptledgeritemid`) using btree;
72create index `ix_rptledgeritemqtrlyid` on rptledger_payment_item(`rptledgeritemqtrlyid`) using btree;
73
74
75alter table rptledger_payment_item
76 add constraint `fk_rptledger_payment_item_parentid`
77 foreign key (`parentid`) references `rptledger_payment` (`objid`);
78alter table rptledger_payment_item
79 add constraint `fk_rptledger_payment_item_rptledgerfaasid`
80 foreign key (`rptledgerfaasid`) references `rptledgerfaas` (`objid`);
81
82
83create table rptledger_payment_share (
84 objid varchar(50) not null,
85 parentid varchar(100) null,
86 revperiod varchar(25) not null,
87 revtype varchar(25) not null,
88 item_objid varchar(50) not null,
89 amount decimal(16,4) not null,
90 sharetype varchar(25) not null,
91 discount decimal(16,4) null,
92 primary key (objid)
93) engine=innodb charset=utf8;
94
95alter table rptledger_payment_share
96 add constraint FK_rptledger_payment_share_parentid foreign key (parentid)
97 references rptledger_payment(objid);
98
99alter table rptledger_payment_share
100 add constraint FK_rptledger_payment_share_itemaccount foreign key (item_objid)
101 references itemaccount(objid);
102
103create index FK_parentid on rptledger_payment_share(parentid) using btree;
104create index FK_item_objid on rptledger_payment_share(item_objid) using btree;
105
106
107SET foreign_key_checks = 0;
108insert into rptledger_payment(
109 objid,
110 rptledgerid,
111 type,
112 receiptid,
113 receiptno,
114 receiptdate,
115 paidby_name,
116 paidby_address,
117 postedby,
118 postedbytitle,
119 dtposted,
120 fromyear,
121 fromqtr,
122 toyear,
123 toqtr,
124 amount,
125 collectingagency,
126 voided
127)
128select
129 x.objid,
130 x.rptledgerid,
131 x.type,
132 x.receiptid,
133 x.receiptno,
134 x.receiptdate,
135 x.paidby_name,
136 x.paidby_address,
137 x.postedby,
138 x.postedbytitle,
139 x.dtposted,
140 x.fromyear,
141 (select min(qtr) from cashreceiptitem_rpt_online
142 where rptledgerid = x.rptledgerid and rptreceiptid = x.receiptid and year = x.fromyear) as fromqtr,
143 x.toyear,
144 (select max(qtr) from cashreceiptitem_rpt_online
145 where rptledgerid = x.rptledgerid and rptreceiptid = x.receiptid and year = x.toyear) as toqtr,
146 x.amount,
147 x.collectingagency,
148 0 as voided
149from (
150 select
151 concat(cro.rptledgerid, '-', cr.objid) as objid,
152 cro.rptledgerid,
153 cr.txntype as type,
154 cr.objid as receiptid,
155 c.receiptno as receiptno,
156 c.receiptdate as receiptdate,
157 c.paidby as paidby_name,
158 c.paidbyaddress as paidby_address,
159 c.collector_name as postedby,
160 c.collector_title as postedbytitle,
161 c.txndate as dtposted,
162 min(cro.year) as fromyear,
163 max(cro.year) as toyear,
164 sum(
165 cro.basic + cro.basicint - cro.basicdisc + cro.sef + cro.sefint - cro.sefdisc + cro.firecode +
166 cro.basicidle + cro.basicidleint - cro.basicidledisc
167 ) as amount,
168 null as collectingagency
169 from cashreceipt_rpt cr
170 inner join cashreceipt c on cr.objid = c.objid
171 inner join cashreceiptitem_rpt_online cro on c.objid = cro.rptreceiptid
172 left join cashreceipt_void cv on c.objid = cv.receiptid
173 where cv.objid is null
174 and cro.rptledgerid is not null
175 group by
176 cr.objid,
177 cro.rptledgerid,
178 cr.txntype,
179 c.receiptno,
180 c.receiptdate,
181 c.paidby,
182 c.paidbyaddress,
183 c.collector_name,
184 c.collector_title,
185 c.txndate
186)x;
187SET foreign_key_checks = 1;
188
189SET foreign_key_checks = 0;
190insert into rptledger_payment_item(
191 objid,
192 parentid,
193 rptledgerfaasid,
194 rptledgeritemid,
195 rptledgeritemqtrlyid,
196 year,
197 qtr,
198 basic,
199 basicint,
200 basicdisc,
201 basicidle,
202 basicidledisc,
203 basicidleint,
204 sef,
205 sefint,
206 sefdisc,
207 firecode,
208 sh,
209 shint,
210 shdisc,
211 total,
212 revperiod,
213 partialled
214)
215select
216 cro.objid,
217 concat(cro.rptledgerid, '-', cro.rptreceiptid) as parentid,
218 cro.rptledgerfaasid,
219 cro.rptledgeritemid,
220 cro.rptledgeritemqtrlyid,
221 cro.year,
222 cro.qtr,
223 cro.basic,
224 cro.basicint,
225 cro.basicdisc,
226 cro.basicidle,
227 cro.basicidledisc,
228 cro.basicidleint,
229 cro.sef,
230 cro.sefint,
231 cro.sefdisc,
232 cro.firecode,
233 0 as sh,
234 0 as shint,
235 0 as shdisc,
236 cro.total,
237 cro.revperiod,
238 cro.partialled
239from cashreceipt_rpt cr
240inner join cashreceipt c on cr.objid = c.objid
241inner join cashreceiptitem_rpt_online cro on c.objid = cro.rptreceiptid
242left join cashreceipt_void cv on c.objid = cv.receiptid
243where cv.objid is null
244 and cro.rptledgerid is not null ;
245SET FOREIGN_KEY_CHECKS = 1;
246
247SET foreign_key_checks = 0;
248insert into rptledger_payment_share(
249 objid,
250 parentid,
251 revperiod,
252 revtype,
253 item_objid,
254 amount,
255 sharetype,
256 discount
257)
258select
259 cra.objid,
260 concat(cra.rptledgerid, '-', cra.rptreceiptid) as parentid,
261 cra.revperiod,
262 cra.revtype,
263 cra.item_objid,
264 cra.amount,
265 cra.sharetype,
266 cra.discount
267from cashreceipt_rpt cr
268inner join cashreceipt c on cr.objid = c.objid
269inner join cashreceiptitem_rpt_account cra on c.objid = cra.rptreceiptid
270left join cashreceipt_void cv on c.objid = cv.receiptid
271where cv.objid is null
272 and cra.rptledgerid is not null ;
273SET foreign_key_checks = 1;
274
275insert into rptledger_payment(
276 objid,
277 rptledgerid,
278 type,
279 receiptid,
280 receiptno,
281 receiptdate,
282 paidby_name,
283 paidby_address,
284 postedby,
285 postedbytitle,
286 dtposted,
287 fromyear,
288 fromqtr,
289 toyear,
290 toqtr,
291 amount,
292 collectingagency,
293 voided
294)
295select
296 objid,
297 rptledgerid,
298 type,
299 null as receiptid,
300 refno as receiptno,
301 refdate,
302 paidby_name,
303 paidby_address,
304 postedby,
305 postedbytitle,
306 dtposted,
307 fromyear,
308 fromqtr,
309 toyear,
310 toqtr,
311 (basic + basicint - basicdisc + sef + sefint - sefdisc + basicidle + firecode) as amount,
312 collectingagency,
313 0 as voided
314from rptledger_credit;
315
316
317
318alter table rptledgeritem
319 add sh decimal(16,2),
320 add shdisc decimal(16,2),
321 add shpaid decimal(16,2),
322 add shint decimal(16,2);
323
324update rptledgeritem set
325 sh = 0, shdisc=0, shpaid = 0, shint = 0
326where sh is null ;
327
328
329alter table rptledgeritem_qtrly
330 add sh decimal(16,2),
331 add shdisc decimal(16,2),
332 add shpaid decimal(16,2),
333 add shint decimal(16,2);
334
335update rptledgeritem_qtrly set
336 sh = 0, shdisc = 0, shpaid = 0, shint = 0
337where sh is null ;
338
339
340
341alter table rptledger_compromise_item add sh decimal(16,2);
342alter table rptledger_compromise_item add shpaid decimal(16,2);
343alter table rptledger_compromise_item add shint decimal(16,2);
344alter table rptledger_compromise_item add shintpaid decimal(16,2);
345
346update rptledger_compromise_item set
347 sh = 0, shpaid = 0, shint = 0, shintpaid = 0
348where sh is null ;
349
350
351alter table rptledger_compromise_item_credit add sh decimal(16,2);
352alter table rptledger_compromise_item_credit add shint decimal(16,2);
353
354update rptledger_compromise_item_credit set
355 sh = 0, shint = 0
356where sh is null ;
357
358
359update province_taxaccount_mapping set basicprioracct_objid = null, basicpriorintacct_objid = null;
360update municipality_taxaccount_mapping set basicprioracct_objid = null, basicpriorintacct_objid = null;
361update brgy_taxaccount_mapping set basicprioracct_objid = null, basicpriorintacct_objid = null;
362
363update province_taxaccount_mapping set sefprioracct_objid = null, sefpriorintacct_objid = null;
364update municipality_taxaccount_mapping set sefprioracct_objid = null, sefpriorintacct_objid = null;
365update province_taxaccount_mapping set sefprioracct_objid = null, sefpriorintacct_objid = null;
366update municipality_taxaccount_mapping set sefprioracct_objid = null, sefpriorintacct_objid = null;