REPORTFORM;VATS3N.frf
TITLE;รายงานภาษีขาย
SQL;drop table if exists _vatsalejob&
SQL;
SQL;create table _vatsalejob
SQL;select docno,datedoc,arcard.accid,invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
SQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0000000000.00 as vattotal
SQL;from arcard inner join armst on arcard.ACCID = armst.ACCID
SQL;where (left(trtype,2) = 'AR' or left(trtype,2) = 'DR') and left(docno,2) <> "AI" and 
SQL;vat <> 0 AND left(vattype,2) = '01' and left(taxtype,2) <> '03' and VOID <> 1
SQL;and datedoc >= :m11 and datedoc <= :m12&
SQL;
SQL;insert into _vatsalejob
SQL;select docno,datedoc,downrec.accid,invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
SQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,0
SQL;from downrec inner join armst on downrec.ACCID = armst.ACCID
SQL;where vatamt <> 0
SQL;and datedoc >= :m11 and datedoc <= :m12&
SQL;
SQL;insert into _vatsalejob
SQL;select docno,datedoc,"",invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
SQL;XTAXNO,TAXSITETYPE,TAXSITE,mCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,0
SQL;from vatgl 
SQL;where vatamt <> 0 and vatxtype = "S"
SQL;and datedoc >= :m11 and datedoc <= :m12&
SQL;
SQL;insert into _vatsalejob
SQL;select docno,datedoc,arcard.accid,000000000.00 as invamt,000000000.00 as vatamt,invamt as invamt2,vatamt as vatamt2,
SQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0
SQL;from arcard inner join armst on arcard.ACCID = armst.ACCID
SQL;where (left(trtype,2) = 'AR' or left(trtype,2) = 'DR') and left(docno,2) <> "AI" and 
SQL;vatamt = 0 and left(arcard.TAXTYPE,2)="03" and arcard.vat = 7 and void = 0 
SQL;and datedoc >= :m11 and datedoc <= :m12&
SQL;
SQL;insert into _vatsalejob
SQL;select arpayhd.docno,arpayhd.datedoc,arpayhd.accid,invamt as invamt,vatamt as vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
SQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0
SQL;from arpayhd inner join armst on arpayhd.ACCID = armst.ACCID
SQL;where arpayhd.vatamt <> 0 and left(arpayhd.VATTYPE,2)="02"
SQL;and datedoc >= :m11 and datedoc <= :m12&
SQL;
SQL;insert into _vatsalejob
SQL;select docno,datedoc,arcard.accid,000000000.00 as invamt,000000000.00 as vatamt,invamt as invamt2,vatamt as vatamt2,
SQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0
SQL;from arcard inner join armst on arcard.ACCID = armst.ACCID
SQL;where (left(trtype,2) = 'AR' or left(trtype,2) = 'DR') and left(docno,2) <> "AI" and 
SQL;vatamt = 0 and arcard.vat = 7 and void = 1
SQL;and datedoc >= :m11 and datedoc <= :m12&
SQL;
SQL;update _vatsalejob set xsite1 = 'X' where left(taxsitetype,2) = '01'&
SQL;update _vatsalejob set xsite2 = taxsite where left(taxsitetype,2) <>  '01'&
SQL;update _vatsalejob set invamt = invamt * -1, vatamt = vatamt * -1 ,invamt2 =  invamt2 * -1, vatamt2 = vatamt2 * -1 where 
SQL;( left(docno,2)='RI' OR left(docno,2)='DR')&
SQL;update  _vatsalejob set xcomp = '** เอกสารยกเลิก **', invamt = 0, vatamt =0, invamt2 = 0, vatamt2 =-0 where
SQL;void = 1&
SQL;update _vatsalejob set vattotal = invamt+vatamt+invamt2+vatamt2&
SQL;
SQL;select * from _vatsalejob order by datedoc, docno
CONDITION;ตั้งแต่วันที่~DATEDOC~Date~2~~
EXCELSQL;drop table if exists _vatsalejob&
EXCELSQL;
EXCELSQL;create table _vatsalejob
EXCELSQL;select docno,datedoc,arcard.accid,invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
EXCELSQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0000000000.00 as vattotal
EXCELSQL;from arcard inner join armst on arcard.ACCID = armst.ACCID
EXCELSQL;where (left(trtype,2) = 'AR' or left(trtype,2) = 'DR') and left(docno,2) <> "AI" and 
EXCELSQL;vat <> 0 AND left(vattype,2) = '01' and left(taxtype,2) <> '03' and VOID <> 1
EXCELSQL;and datedoc >= :m11 and datedoc <= :m12&
EXCELSQL;
EXCELSQL;insert into _vatsalejob
EXCELSQL;select docno,datedoc,downrec.accid,invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
EXCELSQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,0
EXCELSQL;from downrec inner join armst on downrec.ACCID = armst.ACCID
EXCELSQL;where vatamt <> 0
EXCELSQL;and datedoc >= :m11 and datedoc <= :m12&
EXCELSQL;
EXCELSQL;insert into _vatsalejob
EXCELSQL;select docno,datedoc,"",invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
EXCELSQL;XTAXNO,TAXSITETYPE,TAXSITE,mCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,0
EXCELSQL;from vatgl 
EXCELSQL;where vatamt <> 0 and vatxtype = "S"
EXCELSQL;and datedoc >= :m11 and datedoc <= :m12&
EXCELSQL;
EXCELSQL;insert into _vatsalejob
EXCELSQL;select docno,datedoc,arcard.accid,000000000.00 as invamt,000000000.00 as vatamt,invamt as invamt2,vatamt as vatamt2,
EXCELSQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0
EXCELSQL;from arcard inner join armst on arcard.ACCID = armst.ACCID
EXCELSQL;where (left(trtype,2) = 'AR' or left(trtype,2) = 'DR') and left(docno,2) <> "AI" and 
EXCELSQL;vatamt = 0 and left(arcard.TAXTYPE,2)="03" and arcard.vat = 7 and void = 0 
EXCELSQL;and datedoc >= :m11 and datedoc <= :m12&
EXCELSQL;
EXCELSQL;insert into _vatsalejob
EXCELSQL;select arpayhd.docno,arpayhd.datedoc,arpayhd.accid,invamt as invamt,vatamt as vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
EXCELSQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0
EXCELSQL;from arpayhd inner join armst on arpayhd.ACCID = armst.ACCID
EXCELSQL;where arpayhd.vatamt <> 0 and left(arpayhd.VATTYPE,2)="02"
EXCELSQL;and datedoc >= :m11 and datedoc <= :m12&
EXCELSQL;
EXCELSQL;insert into _vatsalejob
EXCELSQL;select docno,datedoc,arcard.accid,000000000.00 as invamt,000000000.00 as vatamt,invamt as invamt2,vatamt as vatamt2,
EXCELSQL;armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0
EXCELSQL;from arcard inner join armst on arcard.ACCID = armst.ACCID
EXCELSQL;where (left(trtype,2) = 'AR' or left(trtype,2) = 'DR') and left(docno,2) <> "AI" and 
EXCELSQL;vatamt = 0 and arcard.vat = 7 and void = 1
EXCELSQL;and datedoc >= :m11 and datedoc <= :m12&
EXCELSQL;
EXCELSQL;update _vatsalejob set xsite1 = 'X' where left(taxsitetype,2) = '01'&
EXCELSQL;update _vatsalejob set xsite2 = taxsite where left(taxsitetype,2) <>  '01'&
EXCELSQL;update _vatsalejob set invamt = invamt * -1, vatamt = vatamt * -1 ,invamt2 =  invamt2 * -1, vatamt2 = vatamt2 * -1 where 
EXCELSQL;( left(docno,2)='RI' OR left(docno,2)='DR')&
EXCELSQL;update  _vatsalejob set xcomp = '** เอกสารยกเลิก **', invamt = 0, vatamt =0, invamt2 = 0, vatamt2 =-0 where
EXCELSQL;void = 1&
EXCELSQL;update _vatsalejob set vattotal = invamt+vatamt+invamt2+vatamt2&
EXCELSQL;
EXCELSQL;select * from _vatsalejob order by datedoc, docno
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
