rptpaymentfix

CREATE TABLE etracs254_dangcaganbak.zzzzrptledger_payment_bak AS
SELECT
*
FROM etracs254_dangcaganbak.rptledger_payment
WHERE objid NOT IN (SELECT objid FROM etracs254_dangcagan.`rptledger_payment`);

INSERT INTO etracs254_dangcagan.rptpayment(
  objid,
  TYPE,
  refid,
  reftype,
  receiptid,
  receiptno,
  receiptdate,
  paidby_name,
  paidby_address,
  postedby,
  postedbytitle,
  dtposted,
  fromyear,
  fromqtr,
  toyear,
  toqtr,
  amount,
  collectingagency,
  voided
)
SELECT
  objid,
  TYPE,
  rptledgerid AS refid,
  'rptledger' AS reftype,
  receiptid,
  receiptno,
  receiptdate,
  paidby_name,
  paidby_address,
  postedby,
  postedbytitle,
  dtposted,
  fromyear,
  fromqtr,
  toyear,
  toqtr,
  amount,
  collectingagency,
  voided
FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak;




INSERT INTO etracs254_dangcagan.rptpayment_item(
  objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  revtype,
  revperiod,
  amount,
  interest,
  discount,
  partialled,
  priority
)
SELECT
  CONCAT(objid, '-basic') AS objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  'basic' AS revtype,
  revperiod,
  basic AS amount,
  basicint AS interest,
  basicdisc AS discount,
  partialled,
  10000 AS priority
FROM etracs254_dangcaganbak.rptledger_payment_item
WHERE parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
AND rptledgerfaasid IS NOT NULL
AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);





INSERT INTO etracs254_dangcagan.rptpayment_item(
  objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  revtype,
  revperiod,
  amount,
  interest,
  discount,
  partialled,
  priority
)
SELECT
  CONCAT(objid, '-sef') AS objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  'sef' AS revtype,
  revperiod,
  sef AS amount,
  sefint AS interest,
  sefdisc AS discount,
  partialled,
  10000 AS priority
FROM etracs254_dangcaganbak.rptledger_payment_item
WHERE parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
AND rptledgerfaasid IS NOT NULL
AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);


INSERT INTO etracs254_dangcagan.rptpayment_item(
  objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  revtype,
  revperiod,
  amount,
  interest,
  discount,
  partialled,
  priority
)
SELECT
  CONCAT(objid, '-sh') AS objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  'sh' AS revtype,
  revperiod,
  sh AS amount,
  shint AS interest,
  shdisc AS discount,
  partialled,
  100 AS priority
FROM etracs254_dangcaganbak.rptledger_payment_item
WHERE sh > 0
AND parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
AND rptledgerfaasid IS NOT NULL
AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);




INSERT INTO etracs254_dangcagan.rptpayment_item(
  objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  revtype,
  revperiod,
  amount,
  interest,
  discount,
  partialled,
  priority
)
SELECT
  CONCAT(objid, '-firecode') AS objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  'firecode' AS revtype,
  revperiod,
  firecode AS amount,
  0 AS interest,
  0 AS discount,
  partialled,
  50 AS priority
FROM etracs254_dangcaganbak.rptledger_payment_item
WHERE firecode > 0
AND parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
AND rptledgerfaasid IS NOT NULL
AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);



INSERT INTO etracs254_dangcagan.rptpayment_item(
  objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  revtype,
  revperiod,
  amount,
  interest,
  discount,
  partialled,
  priority
)
SELECT
  CONCAT(objid, '-basicidle') AS objid,
  parentid,
  rptledgerfaasid,
  YEAR,
  qtr,
  'basicidle' AS revtype,
  revperiod,
  basicidle AS amount,
  basicidleint AS interest,
  basicidledisc AS discount,
  partialled,
  200 AS priority
FROM etracs254_dangcaganbak.rptledger_payment_item
WHERE basicidle > 0
AND parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak)
AND rptledgerfaasid IS NOT NULL
AND rptledgerfaasid IN (SELECT objid FROM etracs254_dangcaganbak.rptledgerfaas);




UPDATE etracs254_dangcagan.rptpayment SET TYPE = 'online' WHERE TYPE = 'rptonline'
;
UPDATE etracs254_dangcagan.rptpayment SET TYPE = 'manual' WHERE TYPE = 'rptmanual'
;
UPDATE etracs254_dangcagan.rptpayment SET TYPE = 'compromise' WHERE TYPE = 'rptcompromise'
;

-- output ani i.butang sa notepad
SELECT * FROM etracs254_dangcagan.rptpayment WHERE objid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak);
SELECT * FROM etracs254_dangcagan.rptpayment_item WHERE parentid IN (SELECT objid FROM etracs254_dangcaganbak.zzzzrptledger_payment_bak);