REPORTFORM;PORC2.frf
TITLE;รายงานตรวจสอบ ใบสั่งซื้อ,ใบรับสินค้า ตาม Job (PO+RC) แสดงยอดทั้งเอกสาร
SQL;drop table if exists _PORC&
SQL;
SQL;create table _PORC
SQL;select po.DOCNO,po.DATEDOC,apmst.XCOMP,
SQL;if(left(pohead.TAXTYPE,2)<>'02',po.amount_l,po.amount_l 
SQL;+ (po.amount_l * pohead.vat /100)) as amount_l, 
SQL;po.JOBNO, po.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 po inner join pohead on po.DOCNO = pohead.DOCNO
SQL;inner join apmst on pohead.ACCID = apmst.ACCID
SQL;where pohead.VOID = 0 and pohead.accid >= :m11 and pohead.accid <= :m12
SQL;and po.jobno >= :m21 and po.jobno <= :m22
SQL;and po.datedoc >= :m31 and po.datedoc <= :m32&
SQL;
SQL;drop table if exists _PORC2&
SQL;create table _PORC2
SQL;select docno from _PORC group by docno&
SQL;
SQL;insert into _PORC
SQL;select invcard.orddoc,invcard.DATEDOC,apmst.XCOMP,
SQL;0.00 as amount_l, invcard.JOBNO, invcard.ORDLINE ,
SQL;invcard.DOCNO as refno , invcard.LINE,
SQL;if(left(apcard.TAXTYPE,2)<>'02',invcard.amount_l,invcard.amount_l 
SQL;+ (invcard.amount_l * apcard.vat /100)) as invamt, 
SQL;2 as linea,invcard.datedoc as dateiv,
SQL;space(50) as jobdesc
SQL;from invcard inner join apmst on invcard.ACCID = apmst.ACCID
SQL;inner join apcard on invcard.docno = apcard.docno
SQL;inner join _PORC2 on invcard.orddoc = _PORC2.docno
SQL;where invcard.jobno >= :m21 and invcard.jobno <= :m22
SQL;and invcard.datedoc >= :m31 and invcard.datedoc <= :m32&
SQL;
SQL;
SQL;update _PORC inner join pohead on _PORC.docno = pohead.docno
SQL;set _PORC.datedoc = pohead.datedoc&
SQL;
SQL;update _PORC inner join jobmst on _PORC.jobno = jobmst.jobno
SQL;set _PORC.jobdesc = jobmst.jdesc&
SQL;
SQL;select * from _PORC order by docno,line,refline
SQL;
CONDITION;ตั้งแต่รหัสผู้จำหน่าย~POHEAD.ACCID~Chr~2~SELECT ACCID FROM APMST~AP
CONDITION;ตั้งแต่รหัส Job~XORDER.JOBNO~Chr~2~SELECT JOBNO FROM JOBMST~JOB
CONDITION;ตั้งแต่วันที่~XORDER.DATEDOC~Date~2~~
EXCELSQL;drop table if exists _PORC&
EXCELSQL;
EXCELSQL;create table _PORC
EXCELSQL;select po.DOCNO,po.DATEDOC,apmst.XCOMP,
EXCELSQL;po.amount_l as amount_l, po.JOBNO, po.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 po inner join pohead on po.DOCNO = pohead.DOCNO
EXCELSQL;inner join apmst on pohead.ACCID = apmst.ACCID
EXCELSQL;where pohead.VOID = 0 and pohead.accid >= :m11 and pohead.accid <= :m12
EXCELSQL;and po.jobno >= :m21 and po.jobno <= :m22
EXCELSQL;and po.datedoc >= :m31 and po.datedoc <= :m32&
EXCELSQL;
EXCELSQL;drop table if exists _PORC2&
EXCELSQL;create table _PORC2
EXCELSQL;select docno from _PORC group by docno&
EXCELSQL;
EXCELSQL;insert into _PORC
EXCELSQL;select apcard.ordpono,invcard.DATEDOC,apmst.XCOMP,
EXCELSQL;0.00 as amount_l, invcard.JOBNO, invcard.ORDLINE ,
EXCELSQL;invcard.DOCNO as refno , invcard.LINE,invcard.amount_l as invamt, 2 as linea,invcard.datedoc as dateiv,
EXCELSQL;space(50) as jobdesc
EXCELSQL;from invcard inner join apmst on invcard.ACCID = apmst.ACCID
EXCELSQL;inner join apcard on invcard.docno = apcard.docno
EXCELSQL;inner join _PORC2 on apcard.ordpono = _PORC2.docno
EXCELSQL;where invcard.jobno >= :m21 and invcard.jobno <= :m22
EXCELSQL;and invcard.datedoc >= :m31 and invcard.datedoc <= :m32&
EXCELSQL;
EXCELSQL;
EXCELSQL;update _PORC inner join pohead on _PORC.docno = pohead.docno
EXCELSQL;set _PORC.datedoc = pohead.datedoc&
EXCELSQL;
EXCELSQL;update _PORC inner join jobmst on _PORC.jobno = jobmst.jobno
EXCELSQL;set _PORC.jobdesc = jobmst.jdesc&
EXCELSQL;
EXCELSQL;select * from _PORC order by docno,line,refline
EXCELSQL;
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
