REPORTFORM;AGEAP_1.frf
TITLE;รายงานงบอายุเจ้าหนี้ แบบสรุป
SQL;drop table if exists _ageAP&
SQL;
SQL;create table _ageAP
SQL;select apcard.accid,apmst.XCOMP, docno,datedoc,duedate,repbal as Amt,000000000.00 as Rec,
SQL;000000000.00 as Nbal,Curdate() AS Mtoday,000 as Nday,000000000.00 as D30,
SQL;000000000.00 as D60,000000000.00 as D90,000000000.00 as D90_2,000000000.00 as undue,
SQL;000000000.00 as xtotal,apmst.TERM,SPACE(100) AS XDESC
SQL;from apcard inner join apmst 
SQL;on apcard.ACCID = apmst.ACCID
SQL;where left(apcard.TRTYPE,2) = 'AP'
SQL;and datedoc >= :M11 and datedoc <= :M12
SQL;and apcard.accid >= :M21 and apcard.accid <= :M22
SQL;and repbal <> 0&
SQL;
SQL;drop table if exists _ageAP2&
SQL;create table _ageAP2
SQL;select refno, sum(abs(repbal)) as rec
SQL;from apcard inner join apmst 
SQL;on apcard.ACCID = apmst.ACCID
SQL;where left(apcard.TRTYPE,2) <> 'AP'
SQL;and datedoc >= :M11 and datedoc <= :M12
SQL;and apcard.accid >= :M21 and apcard.accid <= :M22
SQL;group by refno&
SQL;
SQL;update _ageAP set Mtoday = :M31&
SQL;
SQL;ALTER TABLE `_ageap` ADD INDEX `DOCNO` (`DOCNO`)&
SQL;ALTER TABLE `_ageap2` ADD INDEX `refno` (`refno`)&
SQL;
SQL;
SQL;update _ageAP inner join _ageAP2 on _ageAP.docno = _ageAP2.refno
SQL;set _ageAP.Rec = _ageAP2.rec&
SQL;
SQL;drop table if exists _ageAP2&
SQL;
SQL;update _ageAP set nbal = amt - rec&
SQL;
SQL;delete from _ageAP where nbal = 0&
SQL;
SQL;update _ageAP set Nday = (((year(Mtoday) - 1900) * 365) + 
SQL;DAYOFYEAR(Mtoday))  - (((YEAR(DUEDATE) - 1900) * 365)
SQL; + DAYOFYEAR(DUEDATE))&
SQL;
SQL;update _ageAP set d30 = nbal where Nday >= 1 and Nday <= 30&
SQL;update _ageAP set d60 = nbal where Nday > 30 and Nday <= 60&
SQL;update _ageAP set d90 = nbal where Nday > 60 and Nday <= 90&
SQL;update _ageAP set d90_2 = nbal where Nday > 90 &
SQL;update _ageAP set undue = nbal where Nday <= 0 &
SQL;
SQL;update _ageAP left join invcard on _ageAP.docno = invcard.docno
SQL;set _ageAP.xdesc = invcard.xdesc
SQL;where invcard.line = 1&
SQL;
SQL;update _ageAP SET XTOTAL = UNDUE + D30 + D60 + D90 + D90_2&
SQL;
SQL;select  ACCID,XCOMP,TERM,SUM(UNDUE) AS UNDUE,
SQL;SUM(D30) AS X30,
SQL;SUM(D60) AS X60,
SQL;SUM(D90) AS X90,
SQL;SUM(D90_2) AS X91,
SQL;SUM(XTOTAL) AS XTOTAL  
SQL;from _ageAP
SQL;GROUP BY ACCID,XCOMP,TERM
SQL;ORDER BY ACCID
CONDITION;ตั้งแต่วันที่~APCARD.DATEDOC~Date~2~~
CONDITION;ตั้งแต่รหัสเจ้าหนี้~APCARD.ACCID~Chr~2~SELECT ACCID FROM APCARD~AP
CONDITION;สิ้นสุด ณ วันที่~APCARD.DATEDOC~Date~1~~
EXCELSQL;drop table if exists _ageAP&
EXCELSQL;
EXCELSQL;create table _ageAP
EXCELSQL;select apcard.accid,apmst.XCOMP, docno,datedoc,duedate,repbal as Amt,000000000.00 as Rec,
EXCELSQL;000000000.00 as Nbal,Curdate() AS Mtoday,000 as Nday,000000000.00 as D30,
EXCELSQL;000000000.00 as D60,000000000.00 as D90,000000000.00 as D90_2,000000000.00 as undue,
EXCELSQL;000000000.00 as xtotal,apmst.TERM,SPACE(100) AS XDESC
EXCELSQL;from apcard inner join apmst 
EXCELSQL;on apcard.ACCID = apmst.ACCID
EXCELSQL;where left(apcard.TRTYPE,2) = 'AP'
EXCELSQL;and datedoc >= :M11 and datedoc <= :M12
EXCELSQL;and apcard.accid >= :M21 and apcard.accid <= :M22
EXCELSQL;and repbal <> 0&
EXCELSQL;
EXCELSQL;drop table if exists _ageAP2&
EXCELSQL;create table _ageAP2
EXCELSQL;select refno, sum(abs(repbal)) as rec
EXCELSQL;from apcard inner join apmst 
EXCELSQL;on apcard.ACCID = apmst.ACCID
EXCELSQL;where left(apcard.TRTYPE,2) <> 'AP'
EXCELSQL;and datedoc >= :M11 and datedoc <= :M12
EXCELSQL;and apcard.accid >= :M21 and apcard.accid <= :M22
EXCELSQL;group by refno&
EXCELSQL;
EXCELSQL;update _ageAP set Mtoday = :M31&
EXCELSQL;
EXCELSQL;update _ageAP inner join _ageAP2 on _ageAP.docno = _ageAP2.refno
EXCELSQL;set _ageAP.Rec = _ageAP2.rec&
EXCELSQL;
EXCELSQL;drop table if exists _ageAP2&
EXCELSQL;
EXCELSQL;update _ageAP set nbal = amt - rec&
EXCELSQL;
EXCELSQL;delete from _ageAP where nbal = 0&
EXCELSQL;
EXCELSQL;update _ageAP set Nday = (((year(Mtoday) - 1900) * 365) + 
EXCELSQL;DAYOFYEAR(Mtoday))  - (((YEAR(DUEDATE) - 1900) * 365)
EXCELSQL; + DAYOFYEAR(DUEDATE))&
EXCELSQL;
EXCELSQL;update _ageAP set d30 = nbal where Nday >= 1 and Nday <= 30&
EXCELSQL;update _ageAP set d60 = nbal where Nday > 30 and Nday <= 60&
EXCELSQL;update _ageAP set d90 = nbal where Nday > 60 and Nday <= 90&
EXCELSQL;update _ageAP set d90_2 = nbal where Nday > 90 &
EXCELSQL;update _ageAP set undue = nbal where Nday <= 0 &
EXCELSQL;
EXCELSQL;update _ageAP left join invcard on _ageAP.docno = invcard.docno
EXCELSQL;set _ageAP.xdesc = invcard.xdesc
EXCELSQL;where invcard.line = 1&
EXCELSQL;
EXCELSQL;update _ageAP SET XTOTAL = UNDUE + D30 + D60 + D90 + D90_2&
EXCELSQL;
EXCELSQL;select  ACCID,XCOMP,TERM,SUM(UNDUE) AS UNDUE,
EXCELSQL;SUM(D30) AS X30,
EXCELSQL;SUM(D60) AS X60,
EXCELSQL;SUM(D90) AS X90,
EXCELSQL;SUM(D90_2) AS X91,
EXCELSQL;SUM(XTOTAL) AS XTOTAL  
EXCELSQL;from _ageAP
EXCELSQL;GROUP BY ACCID,XCOMP,TERM
EXCELSQL;ORDER BY ACCID
