REPORTFORM;VATJOBP.frf
TITLE;รายงานภาษีซื้อ ตาม JOB
SQL;drop table if exists _vatpurjob&
SQL;
SQL;create table _vatpurjob
SQL;select apcard.docno,apcard.TAXNO as doctaxno, apcard.datedoc,apcard.accid,invamt,
SQL;vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
SQL;apmst.TAXNO , apmst.TAXSITETYPE, apmst.TAXSITE, apmst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,void,apcard.jobno
SQL;from apcard inner join apmst on apcard.ACCID = apmst.ACCID
SQL;where (left(trtype,3) = 'AP1' or left(trtype,2) = 'DP') and left(docno,2) <> "AI" and 
SQL;vatamt <> 0  
SQL;and apcard.datedoc >= :m11 and apcard.datedoc <= :m12
SQL;and apcard.jobno >= :m21 and apcard.jobno <= :m22
SQL;&
SQL;
SQL;insert into _vatpurjob
SQL;select docno,downpay.TAXNO as doctaxno ,datedoc,downpay.accid,invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
SQL;apmst.TAXNO, apmst.TAXSITETYPE, apmst.TAXSITE, apmst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,downpay.jobno
SQL;from downpay inner join apmst on downpay.ACCID = apmst.ACCID
SQL;where vatamt <> 0 
SQL;and downpay.datedoc >= :m11 and downpay.datedoc <= :m12
SQL;and downpay.jobno >= :m21 and downpay.jobno <= :m22
SQL;&
SQL;
SQL;insert into _vatpurjob
SQL;select docno, vatgl.taxno as doctaxno, 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,vatgl.jobno
SQL;from vatgl 
SQL;where vatamt <> 0 and vatxtype = "P"
SQL;and vatgl.datedoc >= :m11 and vatgl.datedoc <= :m12
SQL;and vatgl.jobno >= :m21 and vatgl.jobno <= :m22
SQL;
SQL;&
SQL;
SQL;insert into _vatpurjob
SQL;select docno,apcard.TAXNO as doctaxno, datedoc,apcard.accid,000000000.00 as invamt,000000000.00 as vatamt,invamt as invamt2,vatamt as vatamt2,
SQL;apmst.TAXNO, apmst.TAXSITETYPE, apmst.TAXSITE, apmst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,void,apcard.jobno
SQL;from apcard inner join apmst on apcard.ACCID = apmst.ACCID
SQL;where (left(trtype,3) = 'AP1' or left(trtype,2) = 'DP') and left(docno,2) <> "AI" and 
SQL;vatamt = 0 and left(apcard.TAXTYPE,2)="03" and apcard.vat = 7 and void = 0
SQL;and apcard.datedoc >= :m11 and apcard.datedoc <= :m12
SQL;and apcard.jobno >= :m21 and apcard.jobno <= :m22
SQL;&
SQL;
SQL;insert into _vatpurjob
SQL;select aptrtail.docno ,aptrtail.TAXNO as doctaxno, aptrtail.DATEINV ,aptrtail.accid, aptrtail.INVAMT,
SQL;aptrtail.VATAMT,000000000.00 as invamt2,000000000.00 as vatamt2,
SQL;apmst.TAXNO , apmst.TAXSITETYPE, apmst.TAXSITE, apmst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,apcard.void,aptrtail.job
SQL;from aptrtail inner join apmst on aptrtail.ACCID = apmst.ACCID
SQL;inner join apcard on aptrtail.docno = apcard.DOCNO
SQL;where aptrtail.vatamt <> 0 and apcard.VOID = 0
SQL;and aptrtail.dateinv >= :m11 and aptrtail.dateinv <= :m12
SQL;and aptrtail.job >= :m21 and aptrtail.job <= :m22
SQL;&
SQL;
SQL;insert into _vatpurjob
SQL;select exptail.docno ,exptail.TAXNO as doctaxno, exptail.VATDATE , "", exptail.INVAMT,
SQL;exptail.VATAMT,000000000.00 as invamt2,000000000.00 as vatamt2,
SQL;exptail.XTAXNO, exptail.TAXSITETYPE, exptail.TAXSITE, exptail.XREMARK,
SQL;SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,exptail.jobno
SQL;from exptail 
SQL;where exptail.vatamt <> 0 
SQL;and exptail.vatdate >= :m11 and exptail.vatdate <= :m12
SQL;and exptail.jobno >= :m21 and exptail.jobno <= :m22
SQL;&
SQL;
SQL;
SQL;update _vatpurjob set xsite1 = 'X' where left(taxsitetype,2) = '01'&
SQL;update _vatpurjob set xsite2 = taxsite where left(taxsitetype,2) <>  '01'&
SQL;update _vatpurjob set invamt = invamt * -1, vatamt = vatamt * -1 ,invamt2 =  invamt2 * -1, vatamt2 = vatamt2 * -1 where 
SQL;( left(docno,2)='RO' OR left(docno,2)='DP')&
SQL;
SQL;select * from _vatpurjob order by datedoc , docno
CONDITION;ตั้งแต่วันที่~DATEDOC~Date~2~~
CONDITION;ตั้งแต่รหัส Job~JOBNO~Chr~2~SELECT JOBNO FROM JOBMST~JOB
EXCELSQL;drop table if exists _vatpurjob&
EXCELSQL;
EXCELSQL;create table _vatpurjob
EXCELSQL;select apcard.docno,apcard.TAXNO as doctaxno, apcard.datedoc,apcard.accid,invamt,
EXCELSQL;vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
EXCELSQL;apmst.TAXNO , apmst.TAXSITETYPE, apmst.TAXSITE, apmst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,void,apcard.jobno
EXCELSQL;from apcard inner join apmst on apcard.ACCID = apmst.ACCID
EXCELSQL;where (left(trtype,3) = 'AP1' or left(trtype,2) = 'DP') and left(docno,2) <> "AI" and 
EXCELSQL;vatamt <> 0  
EXCELSQL;and apcard.datedoc >= :m11 and apcard.datedoc <= :m12
EXCELSQL;and apcard.jobno >= :m21 and apcard.jobno <= :m22
EXCELSQL;&
EXCELSQL;
EXCELSQL;insert into _vatpurjob
EXCELSQL;select docno,downpay.TAXNO as doctaxno ,datedoc,downpay.accid,invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
EXCELSQL;apmst.TAXNO, apmst.TAXSITETYPE, apmst.TAXSITE, apmst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,downpay.jobno
EXCELSQL;from downpay inner join apmst on downpay.ACCID = apmst.ACCID
EXCELSQL;where vatamt <> 0 
EXCELSQL;and downpay.datedoc >= :m11 and downpay.datedoc <= :m12
EXCELSQL;and downpay.jobno >= :m21 and downpay.jobno <= :m22
EXCELSQL;&
EXCELSQL;
EXCELSQL;insert into _vatpurjob
EXCELSQL;select docno, vatgl.taxno as doctaxno, 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,vatgl.jobno
EXCELSQL;from vatgl 
EXCELSQL;where vatamt <> 0 and vatxtype = "P"
EXCELSQL;and vatgl.datedoc >= :m11 and vatgl.datedoc <= :m12
EXCELSQL;and vatgl.jobno >= :m21 and vatgl.jobno <= :m22
EXCELSQL;
EXCELSQL;&
EXCELSQL;
EXCELSQL;insert into _vatpurjob
EXCELSQL;select docno,apcard.TAXNO as doctaxno, datedoc,apcard.accid,000000000.00 as invamt,000000000.00 as vatamt,invamt as invamt2,vatamt as vatamt2,
EXCELSQL;apmst.TAXNO, apmst.TAXSITETYPE, apmst.TAXSITE, apmst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,void,apcard.jobno
EXCELSQL;from apcard inner join apmst on apcard.ACCID = apmst.ACCID
EXCELSQL;where (left(trtype,3) = 'AP1' or left(trtype,2) = 'DP') and left(docno,2) <> "AI" and 
EXCELSQL;vatamt = 0 and left(apcard.TAXTYPE,2)="03" and apcard.vat = 7 and void = 0
EXCELSQL;and apcard.datedoc >= :m11 and apcard.datedoc <= :m12
EXCELSQL;and apcard.jobno >= :m21 and apcard.jobno <= :m22
EXCELSQL;&
EXCELSQL;
EXCELSQL;insert into _vatpurjob
EXCELSQL;select aptrtail.docno ,aptrtail.TAXNO as doctaxno, aptrtail.DATEINV ,aptrtail.accid, aptrtail.INVAMT,
EXCELSQL;aptrtail.VATAMT,000000000.00 as invamt2,000000000.00 as vatamt2,
EXCELSQL;apmst.TAXNO , apmst.TAXSITETYPE, apmst.TAXSITE, apmst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,apcard.void,aptrtail.job
EXCELSQL;from aptrtail inner join apmst on aptrtail.ACCID = apmst.ACCID
EXCELSQL;inner join apcard on aptrtail.docno = apcard.DOCNO
EXCELSQL;where aptrtail.vatamt <> 0 and apcard.VOID = 0
EXCELSQL;and aptrtail.dateinv >= :m11 and aptrtail.dateinv <= :m12
EXCELSQL;and aptrtail.job >= :m21 and aptrtail.job <= :m22
EXCELSQL;&
EXCELSQL;
EXCELSQL;insert into _vatpurjob
EXCELSQL;select exptail.docno ,exptail.TAXNO as doctaxno, exptail.VATDATE , "", exptail.INVAMT,
EXCELSQL;exptail.VATAMT,000000000.00 as invamt2,000000000.00 as vatamt2,
EXCELSQL;exptail.XTAXNO, exptail.TAXSITETYPE, exptail.TAXSITE, exptail.XREMARK,
EXCELSQL;SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,exptail.jobno
EXCELSQL;from exptail 
EXCELSQL;where exptail.vatamt <> 0 
EXCELSQL;and exptail.vatdate >= :m11 and exptail.vatdate <= :m12
EXCELSQL;and exptail.jobno >= :m21 and exptail.jobno <= :m22
EXCELSQL;&
EXCELSQL;
EXCELSQL;
EXCELSQL;update _vatpurjob set xsite1 = 'X' where left(taxsitetype,2) = '01'&
EXCELSQL;update _vatpurjob set xsite2 = taxsite where left(taxsitetype,2) <>  '01'&
EXCELSQL;update _vatpurjob set invamt = invamt * -1, vatamt = vatamt * -1 ,invamt2 =  invamt2 * -1, vatamt2 = vatamt2 * -1 where 
EXCELSQL;( left(docno,2)='RO' OR left(docno,2)='DP')&
EXCELSQL;
EXCELSQL;select * from _vatpurjob order by datedoc , docno
