REPORTFORM;JOBDETAIL_1.frf
TITLE;Profit/Loss Job Detail Report
SQL;drop table if exists _Joblist&
SQL;create table _Joblist
SQL;select invcard.docno,id,xdesc,invcard.QUAN,invcard.UNIT,
SQL;if(left(arcard.taxtype,2)="01", invcard.price * 100 / ( 100+arcard.vat),invcard.PRICE )   as cost_price,
SQL;if(left(arcard.taxtype,2)="01", invcard.price * 100 / ( 100+arcard.vat),invcard.PRICE ) * invcard.quan as amount_l,
SQL;0 as lineA,1 as lineb,invcard.ACCID,invcard.JOBNO,1 AS MFLAG,invcard.datedoc,xcomp,"Invoice Section     " as subhead
SQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qpo,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;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(invcard.docno,2) = 'IV'
SQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
SQL;
SQL;insert into _joblist
SQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT,
SQL;1 as lineA,0 as lineb,invcard.ACCID,invcard.JOBNO,-1 AS MFLAG,datedoc,xcomp,"Cost By Receipt " as subhead
SQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
SQL;from invcard inner join apmst on invcard.ACCID = apmst.ACCID
SQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'RC'
SQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
SQL;
SQL;insert into _joblist
SQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT,
SQL;1 as lineA,0 as lineb,invcard.ACCID,invcard.JOBNO,1 AS MFLAG,datedoc,xcomp,"Cost By Receipt " as subhead
SQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
SQL;from invcard inner join apmst on invcard.ACCID = apmst.ACCID
SQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'RO'
SQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
SQL;
SQL;insert into _joblist
SQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT,
SQL;1 as lineA,1 as lineb,invcard.ACCID,invcard.JOBNO,-1 AS MFLAG,datedoc,xcomp,"Cost By Invioce " as subhead
SQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
SQL;from invcard inner join armst on invcard.ACCID = armst.ACCID
SQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'IV'
SQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
SQL;
SQL;insert into _joblist
SQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT,
SQL;1 as lineA,2 as lineb,invcard.ACCID,invcard.JOBNO,-1 AS MFLAG,datedoc,space(10) as xcomp,"Cost By Issue" as subhead
SQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
SQL;from invcard
SQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'IS'
SQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
SQL;
SQL;
SQL;insert into _joblist
SQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT * -1,
SQL;1 as lineA,2 as lineb,invcard.ACCID,invcard.JOBNO,-1 AS MFLAG,datedoc,space(10) as xcomp,"Cost By Issue" as subhead
SQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
SQL;from invcard
SQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'RS'
SQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
SQL;
SQL;
SQL;insert into _joblist
SQL;select aptrtail.docno,aptrtail.glid,glmst.GLDESC,1 as quan,"Unit" as UNIT,
SQL;aptrtail.INVAMT as cost_price,aptrtail.INVAMT,
SQL;1 as lineA,3 as lineb,aptrtail.accid,aptrtail.JOB,-1 AS MFLAG,datedoc,xcomp,"Expense/¤èÒãªé¨èÒÂ" as subhead
SQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
SQL;from aptrtail inner join glmst on aptrtail.glid = glmst.GLID
SQL;inner join apmst on aptrtail.accid = apmst.ACCID
SQL;inner join apcard on aptrtail.docno = apcard.DOCNO
SQL;where aptrtail.job >= :m11 and  aptrtail.job <= :m12
SQL;and apcard.datedoc >= :m21 and apcard.datedoc <= :m22&
SQL;
SQL;insert into _joblist
SQL;select po.docno,po.ID,po.XDESC,po.QUAN as quan,po.UNIT_SAL,
SQL;po.PRICE as cost_price,po.AMOUNT_L,
SQL;1 as lineA,4 as lineb,pohead.accid,po.JOBNO,-1 AS MFLAG,
SQL;po.datedoc,apmst.xcomp,"Cost By PO" as subhead
SQL;, space(20) as cus_po,po.REC as qrec,quan as qpo,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 po.jobno >= :m11 and  po.jobno <= :m12
SQL;and po.datedoc >= :m21 and po.datedoc <= :m22&
SQL;
SQL;update _joblist set quan = quan - qrec where lineb = 4&
SQL;update _joblist set amount_L = quan * cost_price where lineb = 4&
SQL;update _joblist inner join jobmst on _joblist.jobno = jobmst.jobno
SQL;set _joblist.jobdesc = jobmst.jdesc&
SQL;
SQL;update _joblist inner join arcard on _joblist.docno = arcard.docno
SQL;set _joblist.cus_po = arcard.cus_po
SQL;where _joblist.linea = 0&
SQL;
SQL;select * from _joblist order by jobno,linea,lineb,docno
SQL;
CONDITION;µÑé§áµèÃËÑÊ Job ~INVCARD.JOBNO~Chr~2~SELECT JOBNO FROM JOBMST~JOB
CONDITION;µÑé§áµèÇÑ¹·Õè~INVCARD.DATEDOC~Date~2~~
CONDITION;à§×èÍ¹ä¢~~TextFile~~JOBDETAIL_1.TXT~
EXCELSQL;drop table if exists _Joblist&
EXCELSQL;create table _Joblist
EXCELSQL;select invcard.docno,id,xdesc,invcard.QUAN,invcard.UNIT,
EXCELSQL;if(left(arcard.taxtype,2)="01", invcard.price * 100 / ( 100+arcard.vat),invcard.PRICE )   as cost_price,
EXCELSQL;if(left(arcard.taxtype,2)="01", invcard.price * 100 / ( 100+arcard.vat),invcard.PRICE ) * invcard.quan as amount_l,
EXCELSQL;0 as lineA,1 as lineb,invcard.ACCID,invcard.JOBNO,1 AS MFLAG,invcard.datedoc,xcomp,"Invoice Section     " as subhead
EXCELSQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qpo,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;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(invcard.docno,2) = 'IV'
EXCELSQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
EXCELSQL;
EXCELSQL;insert into _joblist
EXCELSQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT,
EXCELSQL;1 as lineA,0 as lineb,invcard.ACCID,invcard.JOBNO,-1 AS MFLAG,datedoc,xcomp,"Cost By Receipt " as subhead
EXCELSQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
EXCELSQL;from invcard inner join apmst on invcard.ACCID = apmst.ACCID
EXCELSQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'RC'
EXCELSQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
EXCELSQL;
EXCELSQL;insert into _joblist
EXCELSQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT,
EXCELSQL;1 as lineA,0 as lineb,invcard.ACCID,invcard.JOBNO,1 AS MFLAG,datedoc,xcomp,"Cost By Receipt " as subhead
EXCELSQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
EXCELSQL;from invcard inner join apmst on invcard.ACCID = apmst.ACCID
EXCELSQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'RO'
EXCELSQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
EXCELSQL;
EXCELSQL;insert into _joblist
EXCELSQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT,
EXCELSQL;1 as lineA,1 as lineb,invcard.ACCID,invcard.JOBNO,-1 AS MFLAG,datedoc,xcomp,"Cost By Invioce " as subhead
EXCELSQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
EXCELSQL;from invcard inner join armst on invcard.ACCID = armst.ACCID
EXCELSQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'IV'
EXCELSQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
EXCELSQL;
EXCELSQL;insert into _joblist
EXCELSQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT,
EXCELSQL;1 as lineA,2 as lineb,invcard.ACCID,invcard.JOBNO,-1 AS MFLAG,datedoc,space(10) as xcomp,"Cost By Issue" as subhead
EXCELSQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
EXCELSQL;from invcard
EXCELSQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'IS'
EXCELSQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
EXCELSQL;
EXCELSQL;
EXCELSQL;insert into _joblist
EXCELSQL;select docno,id,xdesc,invcard.QUAN,invcard.UNIT,invcard.COST,invcard.COSTAMT * -1,
EXCELSQL;1 as lineA,2 as lineb,invcard.ACCID,invcard.JOBNO,-1 AS MFLAG,datedoc,space(10) as xcomp,"Cost By Issue" as subhead
EXCELSQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
EXCELSQL;from invcard
EXCELSQL;where invcard.JOBNO >= :m11 and invcard.Jobno <= :m12 and left(docno,2) = 'RS'
EXCELSQL;and invcard.datedoc >= :m21 and invcard.datedoc <= :m22&
EXCELSQL;
EXCELSQL;
EXCELSQL;insert into _joblist
EXCELSQL;select aptrtail.docno,aptrtail.glid,glmst.GLDESC,1 as quan,"Unit" as UNIT,
EXCELSQL;aptrtail.INVAMT as cost_price,aptrtail.INVAMT,
EXCELSQL;1 as lineA,3 as lineb,aptrtail.accid,aptrtail.JOB,-1 AS MFLAG,datedoc,xcomp,"Expense/¤èÒãªé¨èÒÂ" as subhead
EXCELSQL;, space(20) as cus_po,0000000000.00 as qrec,0000000000.00 as qbal,space(50) as jobdesc
EXCELSQL;from aptrtail inner join glmst on aptrtail.glid = glmst.GLID
EXCELSQL;inner join apmst on aptrtail.accid = apmst.ACCID
EXCELSQL;inner join apcard on aptrtail.docno = apcard.DOCNO
EXCELSQL;where aptrtail.job >= :m11 and  aptrtail.job <= :m12
EXCELSQL;and apcard.datedoc >= :m21 and apcard.datedoc <= :m22&
EXCELSQL;
EXCELSQL;insert into _joblist
EXCELSQL;select po.docno,po.ID,po.XDESC,po.QUAN as quan,po.UNIT_SAL,
EXCELSQL;po.PRICE as cost_price,po.AMOUNT_L,
EXCELSQL;1 as lineA,4 as lineb,pohead.accid,po.JOBNO,-1 AS MFLAG,
EXCELSQL;po.datedoc,apmst.xcomp,"Cost By PO" as subhead
EXCELSQL;, space(20) as cus_po,po.REC as qrec,quan as qpo,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 po.jobno >= :m11 and  po.jobno <= :m12
EXCELSQL;and po.datedoc >= :m21 and po.datedoc <= :m22&
EXCELSQL;
EXCELSQL;update _joblist set quan = quan - qrec where lineb = 4&
EXCELSQL;update _joblist set amount_L = quan * cost_price where lineb = 4&
EXCELSQL;update _joblist inner join jobmst on _joblist.jobno = jobmst.jobno
EXCELSQL;set _joblist.jobdesc = jobmst.jdesc&
EXCELSQL;
EXCELSQL;update _joblist inner join arcard on _joblist.docno = arcard.docno
EXCELSQL;set _joblist.cus_po = arcard.cus_po
EXCELSQL;where _joblist.linea = 0&
EXCELSQL;
EXCELSQL;select * from _joblist order by jobno,linea,lineb,docno
EXCELSQL;
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
