REPORTFORM;AR_3.frf
TITLE;รายงานยอดหนี้คงเหลือเรียงตามพนักงานขาย
SQL;DROP TABLE IF EXISTS _XARBAL&
SQL;
SQL;CREATE TABLE _XARBAL
SQL;SELECT arcard.docno,arcard.SALEID, salemst.SNAME, ARCARD.ACCID, ARMST.XCOMP,  0000000000.00 as xBEG,
SQL;REPBAL as xdeby,0000000000.00 as xrec,0000000000.00 as xdisc, 0000000000.00 as xBAL
SQL;FROM arcard LEFT JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
SQL;LEFT join salemst on arcard.SALEID = salemst.SID
SQL;where arcard.SALEID >= :M11 AND arcard.SALEID <= :M12
SQL;and arcard.datedoc >= :m21 and arcard.datedoc <= :m22
SQL;and left(trtype,2) = "AR"&
SQL;
SQL;INSERT INTO _XARBAL
SQL;SELECT arcard.docno,arcard.SALEID, salemst.SNAME, ARCARD.ACCID, ARMST.XCOMP,  REPBAL as xBEG,
SQL;0000000000.00 as xdeby,0000000000.00 as xrec,0000000000.00 as xdisc, 0000000000.00 as xBAL
SQL;FROM arcard LEFT JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
SQL;LEFT join salemst on arcard.SALEID = salemst.SID
SQL;where arcard.SALEID >= :M11 AND arcard.SALEID <= :M12
SQL;and arcard.datedoc <= :m21
SQL;and left(trtype,2) = "AR"&
SQL;
SQL;ALTER TABLE `_xarbal`
SQL;	ADD INDEX `DOCNO` (`DOCNO`)&
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL0&
SQL;CREATE TABLE _XARBAL0
SQL;select refno,SUM(repbal) as xdisc from arcard where left(trtype,2) = "DR"
SQL;and datedoc < :m21
SQL;group by refno&
SQL;ALTER TABLE `_xarbal0`
SQL;	ADD INDEX `DOCNO` (`refno`)&
SQL;update _xarbal inner join _xarbal0 on _xarbal.docno = _xarbal0.refno
SQL;set _xarbal.xbeg = _xarbal.xbeg + _xarbal0.xdisc&
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL0&
SQL;CREATE TABLE _XARBAL0
SQL;select refno,sum(repbal) as xrec from arcard where left(trtype,2) = "BR"
SQL;and datedoc <  :m21
SQL;group by refno&
SQL;ALTER TABLE `_xarbal0`
SQL;	ADD INDEX `DOCNO` (`refno`)&
SQL;update _xarbal inner join _xarbal0 on _xarbal.docno = _xarbal0.refno
SQL;set _xarbal.xbeg = _xarbal.xbeg+ _xarbal0.xrec&
SQL;
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL0&
SQL;CREATE TABLE _XARBAL0
SQL;select refno,SUM(repbal) as xdisc from arcard where left(trtype,2) = "DR"
SQL;and datedoc >= :m21 and datedoc <= :m22
SQL;group by refno&
SQL;ALTER TABLE `_xarbal0`
SQL;	ADD INDEX `DOCNO` (`refno`)&
SQL;update _xarbal inner join _xarbal0 on _xarbal.docno = _xarbal0.refno
SQL;set _xarbal.xdisc = _xarbal0.xdisc&
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL0&
SQL;CREATE TABLE _XARBAL0
SQL;select refno,SUM(repbal) as xrec from arcard where left(trtype,2) = "BR"
SQL;and datedoc >= :m21 and datedoc <= :m22
SQL;group by refno&
SQL;ALTER TABLE `_xarbal0`
SQL;	ADD INDEX `DOCNO` (`refno`)&
SQL;update _xarbal inner join _xarbal0 on _xarbal.docno = _xarbal0.refno
SQL;set _xarbal.xrec = _xarbal0.xrec&
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL0&
SQL;
SQL;CREATE TABLE _XARBAL0
SQL;SELECT SALEID, SNAME, ACCID, XCOMP,  sum(xBEG) as xbeg,
SQL;sum(xdeby) as xdeby,sum(xrec) as xrec,sum(xdisc) as xdisc, sum(xbal) as xBAL
SQL;from _xarbal
SQL;group by SALEID, SNAME, ACCID, XCOMP&
SQL;
SQL;update _xarbal0 set xbal = xbeg+xdeby+xrec+xdisc&
SQL;update _xarbal0 set xrec = xrec * -1, xdisc = xdisc * -1&
SQL;
SQL;delete from _xarbal0 where xbeg+xdeby+xrec+xbal = 0&
SQL;
SQL;select * from _xarbal0
SQL;order by saleid,accid
CONDITION;รหัสพนักงาน~ARCARD.SALEID~Chr~2~SELECT SALEID FROM ARCARD~SALE
CONDITION;ตั้งแต่วันที่~ARCARD.DATEDOC~Date~2~~
CONDITION;เงื่อนไข~~TextFile~~ARBAL.TXT~
EXCELSQL;DROP TABLE IF EXISTS _XARBAL&
EXCELSQL;
EXCELSQL;CREATE TABLE _XARBAL
EXCELSQL;SELECT arcard.docno,arcard.SALEID, salemst.SNAME, ARCARD.ACCID, ARMST.XCOMP,  0000000000.00 as xBEG,
EXCELSQL;REPBAL as xdeby,0000000000.00 as xrec,0000000000.00 as xdisc, 0000000000.00 as xBAL
EXCELSQL;FROM arcard LEFT JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
EXCELSQL;LEFT join salemst on arcard.SALEID = salemst.SID
EXCELSQL;where arcard.SALEID >= :M11 AND arcard.SALEID <= :M12
EXCELSQL;and arcard.datedoc >= :m21 and arcard.datedoc <= :m22
EXCELSQL;and left(trtype,2) = "AR"&
EXCELSQL;
EXCELSQL;INSERT INTO _XARBAL
EXCELSQL;SELECT arcard.docno,arcard.SALEID, salemst.SNAME, ARCARD.ACCID, ARMST.XCOMP,  REPBAL as xBEG,
EXCELSQL;0000000000.00 as xdeby,0000000000.00 as xrec,0000000000.00 as xdisc, 0000000000.00 as xBAL
EXCELSQL;FROM arcard LEFT JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
EXCELSQL;LEFT join salemst on arcard.SALEID = salemst.SID
EXCELSQL;where arcard.SALEID >= :M11 AND arcard.SALEID <= :M12
EXCELSQL;and arcard.datedoc <= :m21
EXCELSQL;and left(trtype,2) = "AR"&
EXCELSQL;
EXCELSQL;ALTER TABLE `_xarbal`
EXCELSQL;	ADD INDEX `DOCNO` (`DOCNO`)&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL0&
EXCELSQL;CREATE TABLE _XARBAL0
EXCELSQL;select refno,repbal as xdisc from arcard where left(trtype,2) = "DR"
EXCELSQL;and datedoc <= :m22&
EXCELSQL;ALTER TABLE `_xarbal0`
EXCELSQL;	ADD INDEX `DOCNO` (`refno`)&
EXCELSQL;update _xarbal inner join _xarbal0 on _xarbal.docno = _xarbal0.refno
EXCELSQL;set _xarbal.xdisc = _xarbal0.xdisc&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL0&
EXCELSQL;CREATE TABLE _XARBAL0
EXCELSQL;select refno,repbal as xrec from arcard where left(trtype,2) = "BR"
EXCELSQL;and datedoc <= :m22&
EXCELSQL;ALTER TABLE `_xarbal0`
EXCELSQL;	ADD INDEX `DOCNO` (`refno`)&
EXCELSQL;update _xarbal inner join _xarbal0 on _xarbal.docno = _xarbal0.refno
EXCELSQL;set _xarbal.xrec = _xarbal0.xrec&
EXCELSQL;
EXCELSQL;update _xarbal set xbeg = xbeg + xrec + xdisc
EXCELSQL;where xbeg <> 0&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL0&
EXCELSQL;
EXCELSQL;CREATE TABLE _XARBAL0
EXCELSQL;SELECT SALEID, SNAME, ACCID, XCOMP,  sum(xBEG) as xbeg,
EXCELSQL;sum(xdeby) as xdeby,sum(xrec) as xrec,sum(xdisc) as xdisc, sum(xbal) as xBAL
EXCELSQL;from _xarbal
EXCELSQL;group by SALEID, SNAME, ACCID, XCOMP&
EXCELSQL;
EXCELSQL;update _xarbal0 set xbal = xbeg+xdeby+xrec+xdisc&
EXCELSQL;update _xarbal0 set xrec = xrec * -1, xdisc = xdisc * -1&
EXCELSQL;
EXCELSQL;delete from _xarbal0 where xbeg+xdeby+xrec+xbal = 0&
EXCELSQL;
EXCELSQL;select * from _xarbal0
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
