REPORTFORM;BILL4-1.frf
TITLE;รายงานใบวางบิล ตามวันนัดรับ
SQL;DROP TABLE IF EXISTS _BILL&
SQL;CREATE TABLE _BILL
SQL;SELECT BILLTR.LINE, BILLTR.BDOCNO, BILLTR.DOCNO, BILLTR.DATEDOC, BILLTR.DUEDATE, BILLHD.BILLDATE,
SQL;BILLTR.AMOUNT_L, BILLTR.ACCID, BILLHD.XREMARK, ARMST.XCOMP, BILLHD.DATEREC, BILLHD.DATERECD,ARCARD.NBAL,ARCARD.RECAMT,
SQL;SPACE(20) AS BRDOC,CURDATE() AS BRDATE,BILLHD.XSTATUS,JOBMST.JDESC
SQL;FROM BILLTR 
SQL;LEFT JOIN BILLHD ON BILLTR.BDOCNO = BILLHD.BDOCNO
SQL;LEFT JOIN ARMST ON BILLTR.ACCID = ARMST.ACCID
SQL;LEFT JOIN ARCARD ON BILLTR.DOCNO = ARCARD.DOCNO
SQL;LEFT JOIN JOBMST ON BILLHD.JOBNO = JOBMST.JOBNO
SQL;WHERE  BILLHD.DATEREC IS NOT NULL
SQL;AND BILLHD.DATERECD >= :M11 AND BILLHD.DATERECD <= :M12
SQL;ORDER BY BILLHD.DATERECD, BILLTR.DOCNO, BILLTR.LINE, BILLTR.ACCID&
SQL;
SQL;ALTER TABLE _BILL ADD INDEX _BILLDOCNO (DOCNO)&
SQL;
SQL;UPDATE _BILL SET BRDATE = '0000-00-00'&
SQL;
SQL;DROP TABLE IF EXISTS _BILL2&
SQL;CREATE TABLE _BILL2
SQL;SELECT ARCARD.REFNO,ARCARD.DOCNO,ARCARD.DATEDOC
SQL;FROM ARCARD INNER JOIN _BILL ON ARCARD.REFNO = _BILL.DOCNO
SQL;WHERE LEFT(ARCARD.DOCNO,2)="BR"&
SQL;
SQL;DROP TABLE IF EXISTS _BILL3&
SQL;CREATE TABLE _BILL3
SQL;SELECT REFNO,MAX(DOCNO) AS BRDOC,MAX(DATEDOC) AS BRDATE
SQL;FROM _BILL2 GROUP BY 1&
SQL;
SQL;ALTER TABLE _BILL2 ADD INDEX _BILL2DOCNO (REFNO)&
SQL;
SQL;UPDATE _BILL INNER JOIN _BILL3 ON _BILL.DOCNO = _BILL3.REFNO
SQL;SET _BILL.BRDOC = _BILL3.BRDOC, _BILL.BRDATE = _BILL3.BRDATE&
SQL;
SQL;SELECT * FROM _BILL
CONDITION;ตั้งแต่นัดรับ~BILLHD.DATERECD~Date~2~~
CONDITION;เงื่อนไข~~TextFile~~STKBAL4.TXT~
EXCELSQL;DROP TABLE IF EXISTS _BILL&
EXCELSQL;CREATE TABLE _BILL
EXCELSQL;SELECT BILLTR.LINE, BILLTR.BDOCNO, BILLTR.DOCNO, BILLTR.DATEDOC, BILLTR.DUEDATE, BILLHD.BILLDATE,
EXCELSQL;BILLTR.AMOUNT_L, BILLTR.ACCID, BILLHD.XREMARK, ARMST.XCOMP, BILLHD.DATEREC, BILLHD.DATERECD,ARCARD.NBAL,ARCARD.RECAMT,
EXCELSQL;SPACE(20) AS BRDOC,CURDATE() AS BRDATE,BILLHD.XSTATUS,JOBMST.JDESC
EXCELSQL;FROM BILLTR 
EXCELSQL;LEFT JOIN BILLHD ON BILLTR.BDOCNO = BILLHD.BDOCNO
EXCELSQL;LEFT JOIN ARMST ON BILLTR.ACCID = ARMST.ACCID
EXCELSQL;LEFT JOIN ARCARD ON BILLTR.DOCNO = ARCARD.DOCNO
EXCELSQL;LEFT JOIN JOBMST ON BILLHD.JOBNO = JOBMST.JOBNO
EXCELSQL;WHERE  BILLHD.DATEREC IS NOT NULL
EXCELSQL;AND BILLHD.DATERECD >= :M11 AND BILLHD.DATERECD <= :M12
EXCELSQL;ORDER BY BILLHD.DATERECD, BILLTR.DOCNO, BILLTR.LINE, BILLTR.ACCID&
EXCELSQL;
EXCELSQL;ALTER TABLE _BILL ADD INDEX _BILLDOCNO (DOCNO)&
EXCELSQL;
EXCELSQL;UPDATE _BILL SET BRDATE = '0000-00-00'&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _BILL2&
EXCELSQL;CREATE TABLE _BILL2
EXCELSQL;SELECT ARCARD.REFNO,ARCARD.DOCNO,ARCARD.DATEDOC
EXCELSQL;FROM ARCARD INNER JOIN _BILL ON ARCARD.REFNO = _BILL.DOCNO
EXCELSQL;WHERE LEFT(ARCARD.DOCNO,2)="BR"&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _BILL3&
EXCELSQL;CREATE TABLE _BILL3
EXCELSQL;SELECT REFNO,MAX(DOCNO) AS BRDOC,MAX(DATEDOC) AS BRDATE
EXCELSQL;FROM _BILL2 GROUP BY 1&
EXCELSQL;
EXCELSQL;ALTER TABLE _BILL2 ADD INDEX _BILL2DOCNO (REFNO)&
EXCELSQL;
EXCELSQL;UPDATE _BILL INNER JOIN _BILL3 ON _BILL.DOCNO = _BILL3.REFNO
EXCELSQL;SET _BILL.BRDOC = _BILL3.BRDOC, _BILL.BRDATE = _BILL3.BRDATE&
EXCELSQL;
EXCELSQL;SELECT * FROM _BILL
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
