REPORTFORM;OEIV2.frf
TITLE;รายงานตรวจสอบ ใบจอง,ใบส่งสินค้า ตาม Job (OE+IV) แสดงยอดทั้งเอกสาร
SQL;drop table if exists _OEIV&
SQL;
SQL;create table _OEIV
SQL;select xorder.DOCNO,xorder.DATEDOC,armst.XCOMP,
SQL;if(left(ordhead.TAXTYPE,2)<>'02',xorder.amount_l,xorder.amount_l 
SQL;+ (xorder.amount_l * ordhead.vat /100)) as amount_l, 
SQL;xorder.JOBNO, xorder.LINE,
SQL;space(15) as refno , 0 as refline,0000000000.00 as invamt, 1 as linea,CURDATE() AS DATEIV,
SQL;space(50) as jobdesc
SQL;from xorder inner join ordhead on xorder.DOCNO = ordhead.DOCNO
SQL;inner join armst on ordhead.ACCID = armst.ACCID
SQL;where ordhead.accid >= :m11 and ordhead.accid <= :m12
SQL;and xorder.jobno >= :m21 and xorder.jobno <= :m22
SQL;and xorder.datedoc >= :m31 and xorder.datedoc <= :m32
SQL;and ordhead.VOID = 0&
SQL;
SQL;drop table if exists _OEIV2&
SQL;create table _OEIV2
SQL;select docno from _OEIV group by docno&
SQL;
SQL;insert into _OEIV
SQL;select arcard.ordpono,invcard.DATEDOC,armst.XCOMP,
SQL;0.00 as amount_l, invcard.JOBNO, invcard.ORDLINE ,
SQL;invcard.DOCNO as refno , invcard.LINE,
SQL;if(left(arcard.TAXTYPE,2)<>'02',invcard.amount_l,invcard.amount_l 
SQL;+ (invcard.amount_l * arcard.vat /100)) as invamt,
SQL;2 as linea,invcard.datedoc as dateiv,
SQL;space(50) as jobdesc
SQL;from invcard inner join armst on invcard.ACCID = armst.ACCID
SQL;inner join arcard on invcard.docno = arcard.docno
SQL;inner join _OEIV2 on arcard.ordpono = _OEIV2.docno
SQL;
SQL;where invcard.jobno >= :m21 and invcard.jobno <= :m22
SQL;and invcard.datedoc >= :m31 and invcard.datedoc <= :m32
SQL;&
SQL;
SQL;update _OEIV inner join ordhead on _OEIV.docno = ordhead.docno
SQL;set _OEIV.datedoc = ordhead.datedoc&
SQL;
SQL;update _OEIV inner join jobmst on _OEIV.jobno = jobmst.jobno
SQL;set _OEIV.jobdesc = jobmst.jdesc&
SQL;
SQL;select * from _OEIV order by docno,line,refline
SQL;
CONDITION;ตั้งแต่รหัสลูกค้า~ORDHEAD.ACCID~Chr~2~SELECT ACCID FROM ARMST~AR
CONDITION;ตั้งแต่รหัส Job~XORDER.JOBNO~Chr~2~SELECT JOBNO FROM JOBMST~JOB
CONDITION;ตั้งแต่วันที่~XORDER.DATEDOC~Date~2~~
EXCELSQL;drop table if exists _OEIV&
EXCELSQL;
EXCELSQL;create table _OEIV
EXCELSQL;select xorder.DOCNO,xorder.DATEDOC,armst.XCOMP,
EXCELSQL;if(left(ordhead.TAXTYPE,2)<>'02',xorder.amount_l,xorder.amount_l 
EXCELSQL;+ (xorder.amount_l * ordhead.vat /100)) as amount_l, 
EXCELSQL;xorder.JOBNO, xorder.LINE,
EXCELSQL;space(15) as refno , 0 as refline,0000000000.00 as invamt, 1 as linea,CURDATE() AS DATEIV,
EXCELSQL;space(50) as jobdesc
EXCELSQL;from xorder inner join ordhead on xorder.DOCNO = ordhead.DOCNO
EXCELSQL;inner join armst on ordhead.ACCID = armst.ACCID
EXCELSQL;where ordhead.accid >= :m11 and ordhead.accid <= :m12
EXCELSQL;and xorder.jobno >= :m21 and xorder.jobno <= :m22
EXCELSQL;and xorder.datedoc >= :m31 and xorder.datedoc <= :m32
EXCELSQL;and ordhead.VOID = 0&
EXCELSQL;
EXCELSQL;drop table if exists _OEIV2&
EXCELSQL;create table _OEIV2
EXCELSQL;select docno from _OEIV group by docno&
EXCELSQL;
EXCELSQL;insert into _OEIV
EXCELSQL;select arcard.ordpono,invcard.DATEDOC,armst.XCOMP,
EXCELSQL;0.00 as amount_l, invcard.JOBNO, invcard.ORDLINE ,
EXCELSQL;invcard.DOCNO as refno , invcard.LINE,
EXCELSQL;if(left(arcard.TAXTYPE,2)<>'02',invcard.amount_l,invcard.amount_l 
EXCELSQL;+ (invcard.amount_l * arcard.vat /100)) as invamt,
EXCELSQL;2 as linea,invcard.datedoc as dateiv,
EXCELSQL;space(50) as jobdesc
EXCELSQL;from invcard inner join armst on invcard.ACCID = armst.ACCID
EXCELSQL;inner join arcard on invcard.docno = arcard.docno
EXCELSQL;inner join _OEIV2 on arcard.ordpono = _OEIV2.docno
EXCELSQL;
EXCELSQL;where invcard.jobno >= :m21 and invcard.jobno <= :m22
EXCELSQL;and invcard.datedoc >= :m31 and invcard.datedoc <= :m32
EXCELSQL;&
EXCELSQL;
EXCELSQL;update _OEIV inner join ordhead on _OEIV.docno = ordhead.docno
EXCELSQL;set _OEIV.datedoc = ordhead.datedoc&
EXCELSQL;
EXCELSQL;update _OEIV inner join jobmst on _OEIV.jobno = jobmst.jobno
EXCELSQL;set _OEIV.jobdesc = jobmst.jdesc&
EXCELSQL;
EXCELSQL;select * from _OEIV order by docno,line,refline
EXCELSQL;
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
