REPORTFORM;AGE5_N.frf
TITLE;งบอายุลูกหนี้ ตามกลุ่มพนักงานขาย
SQL;drop table if exists _age&
SQL;
SQL;create table _age
SQL;select arcard.saleid, salemst.sname, arcard.accid, armst.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,armst.TERM,SPACE(100) AS XDESC
SQL;from arcard inner join armst on arcard.ACCID = armst.ACCID
SQL;inner join salemst on arcard.saleid = salemst.sid
SQL;where left(arcard.TRTYPE,2) = 'AR'
SQL;and arcard.saleid >= :M11 and arcard.saleid <= :M12
SQL;and datedoc <= :M21
SQL;and repbal <> 0
SQL;and arcard.void = '0'&
SQL;
SQL;ALTER TABLE `_age`
SQL;	ADD INDEX `ACCID` (`ACCID`),
SQL;             ADD INDEX `DOCNO` (`DOCNO`)&
SQL;
SQL;drop table if exists _age2&
SQL;create table _age2
SQL;select refno, sum(abs(repbal)) as rec
SQL;from arcard 
SQL;where left(arcard.TRTYPE,2) <> 'AR'
SQL;and datedoc <= :M21
SQL;and arcard.void = '0'
SQL;group by refno&
SQL;
SQL;ALTER TABLE `_age2`
SQL;	ADD INDEX `REFNO` (`REFNO`)&
SQL;
SQL;update _age set Mtoday = :M21&
SQL;
SQL;update _age inner join _age2 on _age.docno = _age2.refno
SQL;set _age.Rec = _age2.rec&
SQL;
SQL;update _age set nbal = amt - rec&
SQL;
SQL;delete from _age where nbal = 0&
SQL;
SQL;update _age set Nday = (((year(Mtoday) - 1900) * 365) + 
SQL;DAYOFYEAR(Mtoday))  - (((YEAR(DUEDATE) - 1900) * 365)
SQL; + DAYOFYEAR(DUEDATE))&
SQL;
SQL;update _age set d30 = nbal where Nday >= 1 and Nday <= 30&
SQL;update _age set d60 = nbal where Nday > 30 and Nday <= 60&
SQL;update _age set d90 = nbal where Nday > 60 and Nday <= 90&
SQL;update _age set d90_2 = nbal where Nday > 90 &
SQL;update _age set undue = nbal where Nday <= 0 &
SQL;
SQL;update _age left join invcard on _age.docno = invcard.docno
SQL;set _age.xdesc = invcard.xdesc
SQL;where invcard.line = 1&
SQL;
SQL;update _age SET XTOTAL = UNDUE + D30 + D60 + D90 + D90_2&
SQL;
SQL;select * from _age order by saleid,accid
CONDITION;รหัสพนักงาน~ARCARD.SALEID~Chr~2~SELECT SALEID FROM ARCARD~SALE
CONDITION;สิ้นสุด ณ วันที่~ARCARD.DATEDOC~Date~1~~
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
