REPORTFORM;ARCARD.frf
TITLE;ความเคลื่อนไหว ลูกหนี้แสดงรายละเอียด
SQL;DROP TABLE IF EXISTS _XARBAL&
SQL;
SQL;CREATE TABLE _XARBAL
SQL;SELECT ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO, ARCARD.DATEDOC, ARCARD.REFNO,
SQL;SPACE(50) AS XDESC, 
SQL;SPACE(10) AS SACCID,  
SQL;0000000000.00 as XBEG, 
SQL;IF(LEFT(TRTYPE,2)="AR",REPBAL,0) as xdeby,
SQL;IF(LEFT(TRTYPE,2)="BR",ABS(REPBAL),0) as xREC,
SQL;IF(LEFT(TRTYPE,2)="DR",ABS(REPBAL),0) as xDISC,
SQL;IF(LEFT(TRTYPE,2)="AR",1,IF(LEFT(TRTYPE,2)="BR",2,3)) AS LINE, 1 AS LINE2
SQL;FROM ARCARD INNER JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
SQL;where ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
SQL;and ARCARD.DATEDOC >= :M21 AND ARCARD.DATEDOC <= :M22
SQL;GROUP BY ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO,
SQL;ARCARD.DATEDOC, ARCARD.REFNO&
SQL;
SQL;ALTER TABLE _xarbal
SQL;	ADD INDEX `ACCID` (`ACCID`),
SQL;	ADD INDEX `DOCNO` (`DOCNO`)&
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL2&
SQL;
SQL;CREATE TABLE _XARBAL2
SQL;SELECT ARCARD.ACCID,
SQL;SUM(REPBAL) AS XBEG FROM ARCARD
SQL;WHERE  ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
SQL;AND ARCARD.DATEDOC < :M21
SQL;GROUP BY ARCARD.ACCID&
SQL;
SQL;ALTER TABLE _xarbal2
SQL;	ADD INDEX `ACCID` (`ACCID`)&
SQL;
SQL;INSERT INTO _XARBAL 
SQL;SELECT _XARBAL2.ACCID,XCOMP,"ยอดยกมา" as DOCNO,:M21 AS DATEDOC,SPACE(1) AS REFNO, SPACE(1) AS XDESC,SPACE(10) AS SACCID,
SQL;XBEG,0 AS XDEBY, 0 AS XREC, 0 AS XDISC,0 AS LINE, 0 AS LINE2
SQL;FROM _XARBAL2 INNER JOIN ARMST ON _XARBAL2.ACCID = ARMST.ACCID&
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL2&
SQL;
SQL;DELETE FROM _XARBAL 
SQL;WHERE XBEG+XDEBY+XREC+XDISC = 0&
SQL;
SQL;UPDATE _XARBAL SET REFNO = "" WHERE LEFT(DOCNO,2) = "IV"&
SQL;
SQL;UPDATE _XARBAL INNER JOIN INVCARD ON _XARBAL.DOCNO = INVCARD.DOCNO
SQL;SET _XARBAL.XDESC = INVCARD.XDESC
SQL;WHERE (LEFT(_XARBAL.DOCNO,2) = 'IV' OR LEFT(_XARBAL.DOCNO,2) = 'RI') 
SQL;AND INVCARD.LINE = 1&
SQL;
SQL;UPDATE _XARBAL INNER JOIN GLTRHD ON _XARBAL.DOCNO = GLTRHD.DOCNO
SQL;SET _XARBAL.XDESC = LEFT(GLTRHD.XREMLINE,50)
SQL;WHERE LEFT(_XARBAL.DOCNO,2) = 'BR'&
SQL;
SQL;
SQL;SELECT * FROM _XARBAL ORDER BY ACCID,LINE2,DATEDOC,LINE,DOCNO
CONDITION;ตั้งแต่รหัสลูกค้า~ARCARD.ACCID~Chr~2~SELECT ACCID FROM ARCARD~AR
CONDITION;ตั้งแต่วันที่~ARCARD.DATEDOC~Date~2~~
EXCELSQL;DROP TABLE IF EXISTS _XARBAL&
EXCELSQL;
EXCELSQL;CREATE TABLE _XARBAL
EXCELSQL;SELECT ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO, ARCARD.DATEDOC, ARCARD.REFNO,
EXCELSQL;SPACE(50) AS XDESC, 
EXCELSQL;SPACE(10) AS SACCID,  
EXCELSQL;0000000000.00 as XBEG, 
EXCELSQL;IF(LEFT(TRTYPE,2)="AR",REPBAL,0) as xdeby,
EXCELSQL;IF(LEFT(TRTYPE,2)="BR",ABS(REPBAL),0) as xREC,
EXCELSQL;IF(LEFT(TRTYPE,2)="DR",ABS(REPBAL),0) as xDISC,
EXCELSQL;IF(LEFT(TRTYPE,2)="AR",1,IF(LEFT(TRTYPE,2)="BR",2,3)) AS LINE, 1 AS LINE2
EXCELSQL;FROM ARCARD INNER JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
EXCELSQL;where ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
EXCELSQL;and ARCARD.DATEDOC >= :M21 AND ARCARD.DATEDOC <= :M22
EXCELSQL;GROUP BY ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO,
EXCELSQL;ARCARD.DATEDOC, ARCARD.REFNO&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL2&
EXCELSQL;
EXCELSQL;CREATE TABLE _XARBAL2
EXCELSQL;SELECT ARCARD.ACCID,SPACE(10) AS SACCID,ARMST.XCOMP,
EXCELSQL;SUM(REPBAL) AS XBEG FROM ARCARD
EXCELSQL;INNER JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
EXCELSQL;WHERE  ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
EXCELSQL;AND ARCARD.DATEDOC < :M21
EXCELSQL;GROUP BY ARCARD.ACCID,ARMST.XCOMP&
EXCELSQL;
EXCELSQL;INSERT INTO _XARBAL 
EXCELSQL;SELECT ACCID,XCOMP,"ยอดยกมา" as DOCNO,:M21 AS DATEDOC,SPACE(1) AS REFNO, SPACE(1) AS XDESC,SPACE(10) AS SACCID,
EXCELSQL;XBEG,0 AS XDEBY, 0 AS XREC, 0 AS XDISC,0 AS LINE, 0 AS LINE2
EXCELSQL;FROM _XARBAL2&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL2&
EXCELSQL;
EXCELSQL;DELETE FROM _XARBAL 
EXCELSQL;WHERE XBEG+XDEBY+XREC+XDISC = 0&
EXCELSQL;
EXCELSQL;UPDATE _XARBAL SET REFNO = "" WHERE LEFT(DOCNO,2) = "IV"&
EXCELSQL;
EXCELSQL;UPDATE _XARBAL INNER JOIN INVCARD ON _XARBAL.DOCNO = INVCARD.DOCNO
EXCELSQL;SET _XARBAL.XDESC = INVCARD.XDESC
EXCELSQL;WHERE LEFT(_XARBAL.DOCNO,2) = 'IV' AND INVCARD.LINE = 1&
EXCELSQL;
EXCELSQL;UPDATE _XARBAL INNER JOIN ARPAYHD ON _XARBAL.DOCNO = ARPAYHD.DOCNO
EXCELSQL;SET _XARBAL.XDESC = ARPAYHD.XREMARK&
EXCELSQL;
EXCELSQL;
EXCELSQL;SELECT * FROM _XARBAL ORDER BY ACCID,LINE2,DATEDOC,LINE,DOCNO
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
