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