REPORTFORM;MOVEMENT_BYSALE.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,datedoc
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,datedoc
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;ALTER TABLE `_xarbal`
SQL;	ADD INDEX `accid` (`accid`)&
SQL;ALTER TABLE `_xarbal`
SQL;	ADD INDEX `saleid` (`saleid`)&
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
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 accid,xcomp,saleid,sname,sum(xbeg) as xbeg,curdate() as datedoc,space(20) as docno,space(20) as refno,
SQL;0000000000.00 as xdebt,0000000000.00 as xrec,0 as line
SQL;from _xarbal 
SQL;where xbeg <> 0
SQL;group by accid,xcomp,saleid,sname&
SQL;
SQL;update _xarbal0 set docno = "ยอดยกมา"&
SQL;update _xarbal0 set datedoc = :m21&
SQL;
SQL;insert into _XARBAL0
SQL;select accid,xcomp,saleid,sname,0,datedoc,docno,"" as refno,xdeby,0 as xrec,1 as line
SQL;from _xarbal where xdeby <> 0&
SQL;
SQL;drop table if exists _XARBAL2&
SQL;create table  _XARBAL2
SQL;select arcard.accid,armst.xcomp,space(10) as saleid,space(10) sname,0,datedoc,docno,refno,0 as xdeby,repbal * -1 as xrec,2 as line
SQL;from arcard inner join armst on arcard.accid = armst.accid
SQL;where (left(trtype,2) = "BR" OR left(trtype,2)="DR") and 
SQL;datedoc >= :m21 and datedoc <= :m22&
SQL;
SQL;update _xarbal2 inner join arcard on _xarbal2.refno = arcard.docno
SQL;set 
SQL;_xarbal2.saleid = arcard.saleid&
SQL;
SQL;delete from _xarbal2 where not (saleid >= :m11 and saleid <= :m12 )&
SQL;
SQL;update  _xarbal2 LEFT join salemst on _xarbal2.SALEID = salemst.SID
SQL;set _xarbal2.sname = salemst.sname&
SQL;
SQL;insert into _xarbal0
SQL;select * from _xarbal2&
SQL;
SQL;ALTER TABLE _xarbal0
SQL;	ADD COLUMN DUEDATE DATE NOT NULL DEFAULT '0'&
SQL;update _xarbal0 inner join arcard on _xarbal0.docno = arcard.docno
SQL;set _xarbal0.duedate = arcard.duedate
SQL;where _xarbal0.line = 1&
SQL;
SQL;select * from _xarbal0 order by saleid,accid,datedoc,line
CONDITION;รหัสพนักงาน~ARCARD.SALEID~Chr~2~SELECT SALEID FROM ARCARD~SALE
CONDITION;ตั้งแต่วันที่~ARCARD.DATEDOC~Date~2~~
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,datedoc
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,datedoc
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;ALTER TABLE `_xarbal`
EXCELSQL;	ADD INDEX `accid` (`accid`)&
EXCELSQL;ALTER TABLE `_xarbal`
EXCELSQL;	ADD INDEX `saleid` (`saleid`)&
EXCELSQL;
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL0&
EXCELSQL;CREATE TABLE _XARBAL0
EXCELSQL;select refno,SUM(repbal) as xdisc from arcard where left(trtype,2) = "DR"
EXCELSQL;and datedoc < :m21
EXCELSQL;group by refno&
EXCELSQL;ALTER TABLE `_xarbal0`
EXCELSQL;	ADD INDEX `DOCNO` (`refno`)&
EXCELSQL;update _xarbal inner join _xarbal0 on _xarbal.docno = _xarbal0.refno
EXCELSQL;set _xarbal.xbeg = _xarbal.xbeg + _xarbal0.xdisc&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL0&
EXCELSQL;CREATE TABLE _XARBAL0
EXCELSQL;select refno,sum(repbal) as xrec from arcard where left(trtype,2) = "BR"
EXCELSQL;and datedoc <  :m21
EXCELSQL;group by refno&
EXCELSQL;ALTER TABLE `_xarbal0`
EXCELSQL;	ADD INDEX `DOCNO` (`refno`)&
EXCELSQL;update _xarbal inner join _xarbal0 on _xarbal.docno = _xarbal0.refno
EXCELSQL;set _xarbal.xbeg = _xarbal.xbeg+ _xarbal0.xrec&
EXCELSQL;
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL0&
EXCELSQL;CREATE TABLE _XARBAL0
EXCELSQL;select accid,xcomp,saleid,sname,sum(xbeg) as xbeg,curdate() as datedoc,space(20) as docno,space(20) as refno,
EXCELSQL;0000000000.00 as xdebt,0000000000.00 as xrec,0 as line
EXCELSQL;from _xarbal 
EXCELSQL;where xbeg <> 0
EXCELSQL;group by accid,xcomp,saleid,sname&
EXCELSQL;
EXCELSQL;update _xarbal0 set docno = "ยอดยกมา"&
EXCELSQL;update _xarbal0 set datedoc = :m21&
EXCELSQL;
EXCELSQL;insert into _XARBAL0
EXCELSQL;select accid,xcomp,saleid,sname,0,datedoc,docno,"" as refno,xdeby,0 as xrec,1 as line
EXCELSQL;from _xarbal where xdeby <> 0&
EXCELSQL;
EXCELSQL;drop table if exists _XARBAL2&
EXCELSQL;create table  _XARBAL2
EXCELSQL;select arcard.accid,armst.xcomp,space(10) as saleid,space(10) sname,0,datedoc,docno,refno,0 as xdeby,repbal * -1 as xrec,2 as line
EXCELSQL;from arcard inner join armst on arcard.accid = armst.accid
EXCELSQL;where (left(trtype,2) = "BR" OR left(trtype,2)="DR") and 
EXCELSQL;datedoc >= :m21 and datedoc <= :m22&
EXCELSQL;
EXCELSQL;update _xarbal2 inner join arcard on _xarbal2.refno = arcard.docno
EXCELSQL;set 
EXCELSQL;_xarbal2.saleid = arcard.saleid&
EXCELSQL;
EXCELSQL;delete from _xarbal2 where not (saleid >= :m11 and saleid <= :m12 )&
EXCELSQL;
EXCELSQL;update  _xarbal2 LEFT join salemst on _xarbal2.SALEID = salemst.SID
EXCELSQL;set _xarbal2.sname = salemst.sname&
EXCELSQL;
EXCELSQL;insert into _xarbal0
EXCELSQL;select * from _xarbal2&
EXCELSQL;
EXCELSQL;ALTER TABLE _xarbal0
EXCELSQL;	ADD COLUMN DUEDATE DATE NOT NULL DEFAULT '0'&
EXCELSQL;update _xarbal0 inner join arcard on _xarbal0.docno = arcard.docno
EXCELSQL;set _xarbal0.duedate = arcard.duedate
EXCELSQL;where _xarbal0.line = 1&
EXCELSQL;
EXCELSQL;select * from _xarbal0 order by saleid,accid,datedoc,line
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
