REPORTFORM;VATS12.frf
TITLE;ภาษีซื้อแบบที่ 2 (ระบุตามงวดภาษี)
SQL;drop table if exists _VATAPCARD&
SQL;
SQL;create table  _VATAPCARD
SQL;select apcard.DATEDOC, apcard.DOCNO, IF(apcard.vatexp="Y",0,apcard.VATAMT) as VATAMT, 
SQL;IF(apcard.vatexp="Y",0,apcard.INVAMT) AS INVAMT, apmst.XCOMP, 
SQL;apmst.taxno as xtaxno,apmst.taxsitetype,apmst.taxsite,
SQL;apcard.ACCID,apcard.TAXNO, apcard.REFNO, left(apcard.TRTYPE,2) as TRTYPE ,apcard.vatexp
SQL;,apcard.void, IF(apcard.vatexp="Y",apcard.INVAMT,0) AS XINVAMT1,
SQL;IF(apcard.vatexp="Y",apcard.VATAMT,0) AS XINVAMT2,
SQL;apmst.xcomp as xdesc,space(20) as xsite1,space(20) as xsite2
SQL;FROM apcard LEFT JOIN apmst ON apcard.ACCID = apmst.ACCID
SQL;where apcard.vatperiod = :m11
SQL; AND VATADD <> "Y"
SQL;AND ((LEFT(apcard.TRTYPE,3) = "AP1") OR (LEFT(apcard.TRTYPE,2) = "DP")) AND (LEFT(apcard.VATTYPE,2) = "01" AND apcard.VAT <> 0) 
SQL;AND LEFT(apcard.DOCNO,2) <> "AO" ORDER BY apcard.DATEDOC, apcard.DOCNO&
SQL;
SQL;UPDATE _VATAPCARD SET XINVAMT1 = XINVAMT1 * -1, XINVAMT2 = XINVAMT2 * -1,
SQL;VATAMT = VATAMT * -1, INVAMT = INVAMT * -1
SQL;WHERE _VATAPCARD.TRTYPE <> "AP"&
SQL;
SQL;INSERT INTO _VATAPCARD (DATEDOC,DOCNO,VATAMT,INVAMT,XCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
SQL;ACCID,TAXNO,REFNO,VOID,VATEXP,XINVAMT1,XINVAMT2,TRTYPE)
SQL;SELECT downpay.DATEDOC, downpay.DOCNO, downpay.VATAMT, downpay.INVAMT, apmst.XCOMP, apmst.taxno as xtaxno,apmst.taxsitetype,apmst.taxsite,
SQL;downpay.ACCID,downpay.TAXNO,downpay.docno,0,"",0,0,"AP"
SQL;FROM downpay LEFT JOIN apmst ON downpay.ACCID = apmst.ACCID
SQL;where downpay.VATPERIOD = :m11 &
SQL;
SQL;INSERT INTO _VATAPCARD (DATEDOC,DOCNO,VATAMT,INVAMT,XCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
SQL;ACCID,TAXNO,REFNO,VOID,VATEXP,XINVAMT1,XINVAMT2,TRTYPE)
SQL;SELECT APTRTAIL.DATEINV, APTRTAIL.DOCNO, APTRTAIL.VATAMT,APTRTAIL.INVAMT,XCOMP,apmst.taxno as xtaxno,apmst.taxsitetype,apmst.taxsite,
SQL;APTRTAIL.ACCID,APTRTAIL.TAXNO,APTRTAIL.DOCNO,APCARD.VOID,APCARD.VATEXP,0,0,APCARD.TRTYPE
SQL;FROM APTRTAIL LEFT JOIN apmst ON APTRTAIL.ACCID = apmst.ACCID
SQL;LEFT JOIN APCARD ON APTRTAIL.DOCNO = APCARD.DOCNO
SQL;WHERE  apcard.vatperiod = :m11
SQL; AND VATADD <> "Y"
SQL;AND (LEFT(APCARD.VATTYPE,2) = "01" AND APTRTAIL.VATAMT <> 0) &
SQL;
SQL;
SQL;
SQL;INSERT INTO _VATAPCARD (DATEDOC,DOCNO,VATAMT,INVAMT,XCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
SQL;ACCID,TAXNO,REFNO,VOID,VATEXP,XINVAMT1,XINVAMT2,TRTYPE,XSITE1,XSITE2)
SQL;SELECT EXPTAIL.VATDATE, EXPTAIL.DOCNO, EXPTAIL.VATAMT, EXPTAIL.INVAMT, EXPTAIL.XREMARK,XTAXNO,EXPTAIL.TAXSITETYPE,EXPTAIL.TAXSITE,
SQL;"",EXPTAIL.TAXNO,EXPTAIL.DOCNO,0, CASHPAY.VATEXP,0,0,"AP",
SQL;IF(LEFT(EXPTAIL.TAXSITETYPE,2)="01","X",""),IF(LEFT(EXPTAIL.TAXSITETYPE,2) = "01","",EXPTAIL.TAXSITE)
SQL;FROM EXPTAIL LEFT JOIN CASHPAY ON EXPTAIL.DOCNO = CASHPAY.DOCNO
SQL;WHERE EXPTAIL.VATPERIOD = :m11 
SQL; AND CASHPAY.VATADD <> "Y"
SQL;AND EXPTAIL.VATAMT <> 0&
SQL;
SQL;INSERT INTO _VATAPCARD (DATEDOC,DOCNO,VATAMT,INVAMT,XCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
SQL;ACCID,TAXNO,REFNO,VOID,VATEXP,XINVAMT1,XINVAMT2,TRTYPE)
SQL;SELECT DATEDOC, DOCNO, VATAMT, INVAMT, MCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
SQL;"",TAXNO,DOCNO,0,"",0,0,"AP"
SQL;FROM VATGL  WHERE VATXTYPE = "P"
SQL;AND  vatperiod = :m11 
SQL; AND VATADD <> "Y"&
SQL;
SQL;INSERT INTO _VATAPCARD
SQL;SELECT apcard.DATEDOC, apcard.DOCNO, sum(apcard.VATAMT) AS VATAMT, 
SQL;sum(apcard.INVAMT) AS INVAMT, apmst.XCOMP, apmst.taxno as xtaxno,
SQL;apmst.taxsitetype,apmst.taxsite,
SQL;apcard.ACCID,apcard.TAXNO, apcard.REFNO, left(apcard.TRTYPE,2) as TRTYPE ,
SQL;apcard.vatexp,apcard.void, IF(apcard.vatexp="Y",apcard.INVAMT,0) AS XINVAMT1,
SQL;IF(apcard.vatexp="Y",apcard.VATAMT,0) AS XINVAMT2,
SQL;apmst.xcomp as xdesc,space(20) as xsite1,space(20) as xsite2
SQL;FROM apcard LEFT JOIN apmst ON apcard.ACCID = apmst.ACCID
SQL;LEFT JOIN appayhd on apcard.DOCNO = appayhd.DOCNO
SQL;WHERE appayhd.VATPERIOD = :m11 
SQL; and  left(apcard.VATTYPE,2) = "02" and left(apcard.docno,2) = "BP"
SQL;GROUP BY apcard.DOCNO, apcard.TAXNO&
SQL;
SQL;UPDATE _VATAPCARD SET XCOMP = "*** เอกสารยกเลิก ***",VATAMT = 0,INVAMT = 0,XINVAMT1 = 0, XINVAMT2 = 0 WHERE VOID = 1&
SQL;UPDATE _VATAPCARD SET XDESC = XCOMP&
SQL;UPDATE _VATAPCARD SET XSITE1 = "X" WHERE LEFT(TAXSITETYPE,2) ="01"&
SQL;UPDATE _VATAPCARD SET XSITE2 = TAXSITE WHERE LEFT(TAXSITETYPE,2)  <> "01"&
SQL;DELETE FROM _VATAPCARD WHERE VATEXP = "Y"&
SQL;
SQL;delete from _vatapcard where trim(taxno) = ""&
SQL;delete from _vatapcard where isnull(taxno)&
SQL;
SQL;select datedoc, taxno, xdesc, xtaxno, xsite1, xsite2,docno,
SQL;sum(invamt) as invamt, sum(vatamt) as vatamt,sum(xinvamt1) as xinvamt1,sum(xinvamt2) as xinvamt2
SQL;from _vatapcard
SQL;GROUP BY  datedoc, taxno, xdesc, xtaxno, xsite1, xsite2,docno
SQL;ORDER BY DATEDOC,TAXNO,DOCNO
CONDITION;งวดภาษี~APCARD.VATPERIOD~Chr~1~select concat(if(month(now()) < 10, concat('0',month(now())) ,month(now())  ),'/',year(now())+543) as vatperiod from config ~
EXCELSQL;drop table if exists _VATAPCARD&
EXCELSQL;
EXCELSQL;create table  _VATAPCARD
EXCELSQL;select apcard.DATEDOC, apcard.DOCNO, IF(apcard.vatexp="Y",0,apcard.VATAMT) as VATAMT, 
EXCELSQL;IF(apcard.vatexp="Y",0,apcard.INVAMT) AS INVAMT, apmst.XCOMP, 
EXCELSQL;apmst.taxno as xtaxno,apmst.taxsitetype,apmst.taxsite,
EXCELSQL;apcard.ACCID,apcard.TAXNO, apcard.REFNO, left(apcard.TRTYPE,2) as TRTYPE ,apcard.vatexp
EXCELSQL;,apcard.void, IF(apcard.vatexp="Y",apcard.INVAMT,0) AS XINVAMT1,
EXCELSQL;IF(apcard.vatexp="Y",apcard.VATAMT,0) AS XINVAMT2,
EXCELSQL;apmst.xcomp as xdesc,space(20) as xsite1,space(20) as xsite2
EXCELSQL;FROM apcard LEFT JOIN apmst ON apcard.ACCID = apmst.ACCID
EXCELSQL;where apcard.vatperiod = :m11
EXCELSQL; AND VATADD <> "Y"
EXCELSQL;AND ((LEFT(apcard.TRTYPE,3) = "AP1") OR (LEFT(apcard.TRTYPE,2) = "DP")) AND (LEFT(apcard.VATTYPE,2) = "01" AND apcard.VAT <> 0) 
EXCELSQL;AND LEFT(apcard.DOCNO,2) <> "AO" ORDER BY apcard.DATEDOC, apcard.DOCNO&
EXCELSQL;
EXCELSQL;UPDATE _VATAPCARD SET XINVAMT1 = XINVAMT1 * -1, XINVAMT2 = XINVAMT2 * -1,
EXCELSQL;VATAMT = VATAMT * -1, INVAMT = INVAMT * -1
EXCELSQL;WHERE _VATAPCARD.TRTYPE <> "AP"&
EXCELSQL;
EXCELSQL;INSERT INTO _VATAPCARD (DATEDOC,DOCNO,VATAMT,INVAMT,XCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
EXCELSQL;ACCID,TAXNO,REFNO,VOID,VATEXP,XINVAMT1,XINVAMT2,TRTYPE)
EXCELSQL;SELECT downpay.DATEDOC, downpay.DOCNO, downpay.VATAMT, downpay.INVAMT, apmst.XCOMP, apmst.taxno as xtaxno,apmst.taxsitetype,apmst.taxsite,
EXCELSQL;downpay.ACCID,downpay.TAXNO,downpay.docno,0,"",0,0,"AP"
EXCELSQL;FROM downpay LEFT JOIN apmst ON downpay.ACCID = apmst.ACCID
EXCELSQL;where downpay.VATPERIOD = :m11 &
EXCELSQL;
EXCELSQL;INSERT INTO _VATAPCARD (DATEDOC,DOCNO,VATAMT,INVAMT,XCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
EXCELSQL;ACCID,TAXNO,REFNO,VOID,VATEXP,XINVAMT1,XINVAMT2,TRTYPE)
EXCELSQL;SELECT APTRTAIL.DATEINV, APTRTAIL.DOCNO, APTRTAIL.VATAMT,APTRTAIL.INVAMT,XCOMP,apmst.taxno as xtaxno,apmst.taxsitetype,apmst.taxsite,
EXCELSQL;APTRTAIL.ACCID,APTRTAIL.TAXNO,APTRTAIL.DOCNO,APCARD.VOID,APCARD.VATEXP,0,0,APCARD.TRTYPE
EXCELSQL;FROM APTRTAIL LEFT JOIN apmst ON APTRTAIL.ACCID = apmst.ACCID
EXCELSQL;LEFT JOIN APCARD ON APTRTAIL.DOCNO = APCARD.DOCNO
EXCELSQL;WHERE  apcard.vatperiod = :m11
EXCELSQL; AND VATADD <> "Y"
EXCELSQL;AND (LEFT(APCARD.VATTYPE,2) = "01" AND APTRTAIL.VATAMT <> 0) &
EXCELSQL;
EXCELSQL;
EXCELSQL;
EXCELSQL;INSERT INTO _VATAPCARD (DATEDOC,DOCNO,VATAMT,INVAMT,XCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
EXCELSQL;ACCID,TAXNO,REFNO,VOID,VATEXP,XINVAMT1,XINVAMT2,TRTYPE,XSITE1,XSITE2)
EXCELSQL;SELECT EXPTAIL.VATDATE, EXPTAIL.DOCNO, EXPTAIL.VATAMT, EXPTAIL.INVAMT, EXPTAIL.XREMARK,XTAXNO,EXPTAIL.TAXSITETYPE,EXPTAIL.TAXSITE,
EXCELSQL;"",EXPTAIL.TAXNO,EXPTAIL.DOCNO,0, CASHPAY.VATEXP,0,0,"AP",
EXCELSQL;IF(LEFT(EXPTAIL.TAXSITETYPE,2)="01","X",""),IF(LEFT(EXPTAIL.TAXSITETYPE,2) = "01","",EXPTAIL.TAXSITE)
EXCELSQL;FROM EXPTAIL LEFT JOIN CASHPAY ON EXPTAIL.DOCNO = CASHPAY.DOCNO
EXCELSQL;WHERE EXPTAIL.VATPERIOD = :m11 
EXCELSQL; AND CASHPAY.VATADD <> "Y"
EXCELSQL;AND EXPTAIL.VATAMT <> 0&
EXCELSQL;
EXCELSQL;INSERT INTO _VATAPCARD (DATEDOC,DOCNO,VATAMT,INVAMT,XCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
EXCELSQL;ACCID,TAXNO,REFNO,VOID,VATEXP,XINVAMT1,XINVAMT2,TRTYPE)
EXCELSQL;SELECT DATEDOC, DOCNO, VATAMT, INVAMT, MCOMP,XTAXNO,TAXSITETYPE,TAXSITE,
EXCELSQL;"",TAXNO,DOCNO,0,"",0,0,"AP"
EXCELSQL;FROM VATGL  WHERE VATXTYPE = "P"
EXCELSQL;AND  vatperiod = :m11 
EXCELSQL; AND VATADD <> "Y"&
EXCELSQL;
EXCELSQL;INSERT INTO _VATAPCARD
EXCELSQL;SELECT apcard.DATEDOC, apcard.DOCNO, sum(apcard.VATAMT) AS VATAMT, 
EXCELSQL;sum(apcard.INVAMT) AS INVAMT, apmst.XCOMP, apmst.taxno as xtaxno,
EXCELSQL;apmst.taxsitetype,apmst.taxsite,
EXCELSQL;apcard.ACCID,apcard.TAXNO, apcard.REFNO, left(apcard.TRTYPE,2) as TRTYPE ,
EXCELSQL;apcard.vatexp,apcard.void, IF(apcard.vatexp="Y",apcard.INVAMT,0) AS XINVAMT1,
EXCELSQL;IF(apcard.vatexp="Y",apcard.VATAMT,0) AS XINVAMT2,
EXCELSQL;apmst.xcomp as xdesc,space(20) as xsite1,space(20) as xsite2
EXCELSQL;FROM apcard LEFT JOIN apmst ON apcard.ACCID = apmst.ACCID
EXCELSQL;LEFT JOIN appayhd on apcard.DOCNO = appayhd.DOCNO
EXCELSQL;WHERE appayhd.VATPERIOD = :m11 
EXCELSQL; and  left(apcard.VATTYPE,2) = "02" and left(apcard.docno,2) = "BP"
EXCELSQL;GROUP BY apcard.DOCNO, apcard.TAXNO&
EXCELSQL;
EXCELSQL;UPDATE _VATAPCARD SET XCOMP = "*** เอกสารยกเลิก ***",VATAMT = 0,INVAMT = 0,XINVAMT1 = 0, XINVAMT2 = 0 WHERE VOID = 1&
EXCELSQL;UPDATE _VATAPCARD SET XDESC = XCOMP&
EXCELSQL;UPDATE _VATAPCARD SET XSITE1 = "X" WHERE LEFT(TAXSITETYPE,2) ="01"&
EXCELSQL;UPDATE _VATAPCARD SET XSITE2 = TAXSITE WHERE LEFT(TAXSITETYPE,2)  <> "01"&
EXCELSQL;DELETE FROM _VATAPCARD WHERE VATEXP = "Y"&
EXCELSQL;
EXCELSQL;delete from _vatapcard where trim(taxno) = ""&
EXCELSQL;delete from _vatapcard where isnull(taxno)&
EXCELSQL;
EXCELSQL;select datedoc as "วันที่", 
EXCELSQL;taxno as "ใบกำกับ", 
EXCELSQL;docno as "เอกสารใหม่",
EXCELSQL;xdesc as "รายละเอียด",
EXCELSQL;sum(invamt)  as "มูลค่าสินค้า/บริการ", 
EXCELSQL;sum(vatamt)   as "ภาษี"
EXCELSQL;from _vatapcard
EXCELSQL;GROUP BY  datedoc, taxno, xdesc, xtaxno, xsite1, xsite2,docno
EXCELSQL;ORDER BY DATEDOC,TAXNO,DOCNO
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
