REPORTFORM;V_5.frf
TITLE;รายงานค้างชำระตามเขตการขาย
SQL;DROP TABLE IF EXISTS _XARBAL&
SQL;
SQL;CREATE TABLE _XARBAL
SQL;SELECT arcard.AREA, area.ADESC, ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO, ARCARD.DATEDOC, ARCARD.DUEDATE,  
SQL;arcard.REPBAL + arcard.CASHPAY as xdeby,arcard.CASHPAY as xrec, 0000000000.00 as xBAL
SQL;FROM ARCARD 
SQL;INNER JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
SQL;left join area on arcard.AREA = area.AID
SQL;where arcard.AREA >= :M11 AND arcard.AREA <= :M12
SQL;and ARCARD.DATEDOC >= :M21 and ARCARD.DATEDOC <= :M22 
SQL;and left(trtype,2) = "AR"
SQL;GROUP BY ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO&
SQL;
SQL;ALTER TABLE `_xarbal`
SQL;	ADD INDEX `DOCNO` (`DOCNO`)&
SQL;
SQL;INSERT INTO _XARBAL
SQL;SELECT arcard.AREA, area.ADESC, ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO, ARCARD.DATEDOC, ARCARD.DUEDATE,  
SQL;repbal as xdeby,0000000000.00 as xrec, 0000000000.00 as xBAL
SQL;FROM ARCARD 
SQL;INNER JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
SQL;left join area on arcard.AREA = area.AID
SQL;where  left(ARCARD.trtype,2) = "DR"
SQL;and ( left(ARCARD.DISCTYPE,2) = "03")
SQL;and arcard.AREA >= :M11 AND arcard.AREA <= :M12
SQL;and ARCARD.DATEDOC >= :M21 and ARCARD.DATEDOC <= :M22
SQL;GROUP BY ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO&
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL2&
SQL;
SQL;CREATE TABLE _XARBAL2
SQL;SELECT REFNO ,
SQL;SUM(REPBAL * -1) AS XREC
SQL;FROM ARCARD
SQL;WHERE  ARCARD.DATEDOC >= :M21 and ARCARD.DATEDOC <= :M22
SQL;AND LEFT(TRTYPE,2) = 'BR'
SQL;GROUP BY REFNO&
SQL;
SQL;INSERT INTO _XARBAL2
SQL;SELECT REFNO ,
SQL;SUM(REPBAL * -1) AS XREC
SQL;FROM ARCARD
SQL;WHERE  ARCARD.DATEDOC >= :M21 and ARCARD.DATEDOC <= :M22 
SQL;and LEFT(TRTYPE,2) = 'DR' AND LEFT(DISCTYPE,2) = '01'
SQL;GROUP BY REFNO&
SQL;
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL3&
SQL;CREATE TABLE _XARBAL3
SQL;SELECT REFNO,SUM(XREC) AS XREC FROM _XARBAL2
SQL;GROUP BY REFNO&
SQL;
SQL;ALTER TABLE `_xarbal3`
SQL;	ADD INDEX `REFNO` (`REFNO`)&
SQL;
SQL;UPDATE _XARBAL INNER JOIN _XARBAL3 ON _XARBAL.DOCNO = _XARBAL3.REFNO
SQL;SET _XARBAL.XREC = _XARBAL.XREC + _XARBAL3.XREC&
SQL;
SQL;DROP TABLE _XARBAL2&
SQL;DROP TABLE _XARBAL3&
SQL;
SQL;UPDATE _XARBAL SET XBAL = XDEBY-XREC&
SQL;
SQL;SELECT * FROM _XARBAL ORDER BY ADESC,XCOMP,DOCNO,DATEDOC
CONDITION;ตั้งแต่ เส้นทาง~ARCARD.AREA~Chr~2~SELECT AID FROM AREA~AREA
CONDITION;ตั้งแต่วันที่~ARCARD.DATEDOC~Date~2~~
CONDITION;เงื่อนไข~~TextFile~~ARBAL.TXT~
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
