REPORTFORM;MR_REP1.frf
TITLE;Summary Received Order
SQL;drop table if exists _exp1&
SQL;
SQL;create table _exp1
SQL;select 
SQL;ordhead.accid,xcomp,ordhead.docno,ordhead.datedoc,ordhead.taxtype,
SQL;xorder.invamt,
SQL;if(left(ordhead.taxtype,2)="01",xorder.amount_l * ordhead.vat /(100+ordhead.vat), xorder.invamt * ordhead.vat/100   )  as vat,
SQL;000000000.00 as xamount_l,
SQL;000000000.00 as wht,
SQL;000000000.00 as bkkchg,
SQL;000000000.00 as xtotal,
SQL;000000000.00 as cash,
SQL;000000000.00 as chq,
SQL;000000000.00 as tt,
SQL;000000000.00 as outstanding,
SQL;space(20) as chqno,
SQL;curdate() as chqdate,
SQL;space(20) as IV,
SQL;curdate() as IVdate,
SQL;000000000.00 as IV_INVAMT,
SQL;000000000.00 as IV_VATAMT,
SQL;000000000.00 as IVAMT,
SQL;000000000.00 as debtbal,
SQL;xorder.jobno,
SQL;xorder.line, 0 as xflag
SQL;from xorder inner join ordhead on xorder.docno = ordhead.docno
SQL;inner join armst on ordhead.accid = armst.accid
SQL;where xorder.invamt <> 0
SQL;and xorder.datedoc >= :m11 and xorder.datedoc <= :m12
SQL;and xorder.jobno >= :m21 and xorder.jobno <= :m22&
SQL;
SQL;alter table _exp1
SQL;  add index _exp1 (docno)&
SQL;alter table _exp1
SQL;  add index _exp1iv (iv)&
SQL;
SQL;update _exp1 set ivdate = '0000-00-00'&
SQL;
SQL;drop table if exists _exp2&
SQL;
SQL;create table _exp2
SQL;select docno from _exp1 group by 1&
SQL;
SQL;alter table _exp2
SQL;  add index _exp2 (docno)&
SQL;
SQL;insert into _exp1
SQL;select 
SQL;invcard.accid,xcomp,invcard.orddoc,ordhead.datedoc as datedoc,arcard.taxtype,
SQL;0.00,
SQL;0.00 as vat,
SQL;000000000.00 as xamount_l,
SQL;000000000.00 as wht,
SQL;000000000.00 as bkkchg,
SQL;000000000.00 as xtotal,
SQL;000000000.00 as cash,
SQL;000000000.00 as chq,
SQL;000000000.00 as tt,
SQL;000000000.00 as outstanding,
SQL;space(20) as chqno,
SQL;curdate() as chqdate,
SQL;invcard.docno as IV,
SQL;invcard.datedoc as IVdate,
SQL;invcard.invamt as IV_IVAMT,
SQL;if(left(arcard.taxtype,2)="01",invcard.amount_l * arcard.vat /(100+arcard.vat), invcard.invamt * arcard.vat/100   )  as IV_VATAMT,
SQL;000000000.00 as IVAMT,
SQL;000000000.00 as debtbal,
SQL;invcard.jobno,
SQL;invcard.ordline as xline, 1 as xflag
SQL;from invcard inner join arcard on invcard.docno = arcard.docno
SQL;inner join armst on invcard.accid = armst.accid
SQL;inner join _exp2 on invcard.orddoc = _exp2.docno
SQL;inner join ordhead on invcard.orddoc = ordhead.docno
SQL;where invcard.invamt <> 0&
SQL;
SQL;drop table if exists _exp4&
SQL;create table _exp4
SQL;select _exp1.iv from _exp1 
SQL;where trim(iv) <> ""
SQL;group by 1&
SQL;
SQL;drop table if exists _exp5&
SQL;create table _exp5
SQL;select arcard.docno,refno,arcard.RECAMT,arpayhd.AMOUNT,
SQL;arpayhd.CASHAMT, arpayhd.CHKAMT, arpayhd.TAXPAID, arpayhd.TRANAMT,
SQL;arpayhd.DISC,arpayhd.OTHERS,
SQL;000000000.00 as icashamt,
SQL;000000000.00 as ichkamt,
SQL;000000000.00 as itaxpaid,
SQL;000000000.00 as itranamt,
SQL;000000000.00 as idisc,
SQL;000000000.00 as iothers
SQL;from arcard inner join _exp4 on arcard.REFNO = _exp4.iv
SQL;inner join arpayhd on arcard.DOCNO = arpayhd.DOCNO
SQL;where left(trtype,2) = 'BR'
SQL;order by 1,2 &
SQL;
SQL;update _exp5 set 
SQL;icashamt = cashamt * recamt / amount,
SQL;ichkamt = chkamt * recamt / amount,
SQL;itaxpaid = taxpaid * recamt / amount,
SQL;itranamt = tranamt * recamt / amount,
SQL;idisc = disc * recamt / amount,
SQL;iothers = others * recamt / amount&
SQL;
SQL;drop table if exists _exp6&
SQL;create table _exp6
SQL;select refno,  
SQL;sum(icashamt) as cashamt,
SQL;sum(ichkamt) as chkamt,
SQL;sum(itaxpaid) as taxpaid,
SQL;sum(itranamt) as tranamt,
SQL;sum(idisc) as disc,
SQL;sum(iothers) as others
SQL;from _exp5
SQL;group by 1&
SQL;
SQL;
SQL;update _exp1 inner join _exp6 on _exp1.iv = _exp6.refno
SQL;set wht = _exp6.taxpaid,
SQL;bkkchg = disc+others,
SQL;xtotal = taxpaid+disc+others,
SQL;cash = cashamt,
SQL;chq = chkamt,
SQL;tt = tranamt,
SQL;outstanding = xamount_l - ( taxpaid+disc+others+cashamt+chkamt+tranamt)&
SQL;
SQL;update _exp1 set xamount_l = invamt+vat,
SQL;ivamt = iv_invamt+iv_vatamt,
SQL;outstanding = xamount_L,
SQL;debtbal = outstanding - debtbal&
SQL;
SQL;
SQL;drop table if exists _exp3&
SQL;
SQL;create table _exp3
SQL;select accid, xcomp,docno,datedoc,IV,IVDATE,JOBNO,LINE,
SQL;sum(invamt) as invamt, sum(vat) as vat, sum(xamount_L) as xamount_L,
SQL;sum(wht) as wht, sum(bkkchg) as bkkchg, sum(cash) as cash, sum(chq) as chq, sum(tt) as tt,
SQL;sum(outstanding) as outstanding,chqno,chqdate,sum(iv_invamt) as iv_invamt, sum(iv_vatamt) as vatamt, sum(ivamt)  as ivamt,
SQL;sum(debtbal) as debtbal
SQL;from _exp1
SQL;group by accid, xcomp,docno,datedoc,IV,IVDATE,JOBNO,LINE
SQL;&
SQL;
SQL;drop table if exists _exp7&
SQL;create table _exp7
SQL;select arcard.docno,_exp4.iv, bktr.CHKNO, bktr.DATECHK from 
SQL;arcard inner join _exp4 on arcard.REFNO = _exp4.iv
SQL;inner join bktr on arcard.DOCNO = bktr.DOCNO
SQL;where left(arcard.docno,2) = 'BR' and left(bktr.PAYTYPE,2) = '02'&
SQL;
SQL;alter table _exp7
SQL; add index _exp7 (iv)&
SQL;
SQL;update _exp1 set chqdate = '0000-00-00'&
SQL;
SQL;update _exp1 inner join _exp7 on _exp1.iv = _exp7.iv 
SQL;set _exp1.chqno = _exp7.chkno, _exp1.chqdate = _exp7.datechk&
SQL;
SQL;select * from _exp1 order by docno,line
CONDITION;µÑé§áµèÇÑ¹·Õè~XORDER.DATEDOC~Date~2~~
CONDITION;ÃËÑÊ Job~XORDER.JOBNO~Chr~2~SELECT JOBNO FROM XORDER~JOB
EXCELSQL;drop table if exists _exp1&
EXCELSQL;
EXCELSQL;create table _exp1
EXCELSQL;select 
EXCELSQL;ordhead.accid,xcomp,ordhead.docno,ordhead.datedoc,ordhead.taxtype,
EXCELSQL;xorder.invamt,
EXCELSQL;if(left(ordhead.taxtype,2)="01",xorder.amount_l * ordhead.vat /(100+ordhead.vat), xorder.invamt * ordhead.vat/100   )  as vat,
EXCELSQL;000000000.00 as xamount_l,
EXCELSQL;000000000.00 as wht,
EXCELSQL;000000000.00 as bkkchg,
EXCELSQL;000000000.00 as xtotal,
EXCELSQL;000000000.00 as cash,
EXCELSQL;000000000.00 as chq,
EXCELSQL;000000000.00 as tt,
EXCELSQL;000000000.00 as outstanding,
EXCELSQL;space(20) as chqno,
EXCELSQL;curdate() as chqdate,
EXCELSQL;space(20) as IV,
EXCELSQL;curdate() as IVdate,
EXCELSQL;000000000.00 as IV_INVAMT,
EXCELSQL;000000000.00 as IV_VATAMT,
EXCELSQL;000000000.00 as IVAMT,
EXCELSQL;000000000.00 as debtbal,
EXCELSQL;xorder.jobno,
EXCELSQL;xorder.line, 0 as xflag
EXCELSQL;from xorder inner join ordhead on xorder.docno = ordhead.docno
EXCELSQL;inner join armst on ordhead.accid = armst.accid
EXCELSQL;where xorder.invamt <> 0
EXCELSQL;and xorder.datedoc >= :m11 and xorder.datedoc <= :m12
EXCELSQL;and xorder.jobno >= :m21 and xorder.jobno <= :m22&
EXCELSQL;
EXCELSQL;alter table _exp1
EXCELSQL;  add index _exp1 (docno)&
EXCELSQL;alter table _exp1
EXCELSQL;  add index _exp1iv (iv)&
EXCELSQL;
EXCELSQL;update _exp1 set ivdate = '0000-00-00'&
EXCELSQL;
EXCELSQL;drop table if exists _exp2&
EXCELSQL;
EXCELSQL;create table _exp2
EXCELSQL;select docno from _exp1 group by 1&
EXCELSQL;
EXCELSQL;alter table _exp2
EXCELSQL;  add index _exp2 (docno)&
EXCELSQL;
EXCELSQL;insert into _exp1
EXCELSQL;select 
EXCELSQL;invcard.accid,xcomp,invcard.orddoc,ordhead.datedoc as datedoc,arcard.taxtype,
EXCELSQL;0.00,
EXCELSQL;0.00 as vat,
EXCELSQL;000000000.00 as xamount_l,
EXCELSQL;000000000.00 as wht,
EXCELSQL;000000000.00 as bkkchg,
EXCELSQL;000000000.00 as xtotal,
EXCELSQL;000000000.00 as cash,
EXCELSQL;000000000.00 as chq,
EXCELSQL;000000000.00 as tt,
EXCELSQL;000000000.00 as outstanding,
EXCELSQL;space(20) as chqno,
EXCELSQL;curdate() as chqdate,
EXCELSQL;invcard.docno as IV,
EXCELSQL;invcard.datedoc as IVdate,
EXCELSQL;invcard.invamt as IV_IVAMT,
EXCELSQL;if(left(arcard.taxtype,2)="01",invcard.amount_l * arcard.vat /(100+arcard.vat), invcard.invamt * arcard.vat/100   )  as IV_VATAMT,
EXCELSQL;000000000.00 as IVAMT,
EXCELSQL;000000000.00 as debtbal,
EXCELSQL;invcard.jobno,
EXCELSQL;invcard.ordline as xline, 1 as xflag
EXCELSQL;from invcard inner join arcard on invcard.docno = arcard.docno
EXCELSQL;inner join armst on invcard.accid = armst.accid
EXCELSQL;inner join _exp2 on invcard.orddoc = _exp2.docno
EXCELSQL;inner join ordhead on invcard.orddoc = ordhead.docno
EXCELSQL;where invcard.invamt <> 0&
EXCELSQL;
EXCELSQL;drop table if exists _exp4&
EXCELSQL;create table _exp4
EXCELSQL;select _exp1.iv from _exp1 
EXCELSQL;where trim(iv) <> ""
EXCELSQL;group by 1&
EXCELSQL;
EXCELSQL;drop table if exists _exp5&
EXCELSQL;create table _exp5
EXCELSQL;select arcard.docno,refno,arcard.RECAMT,arpayhd.AMOUNT,
EXCELSQL;arpayhd.CASHAMT, arpayhd.CHKAMT, arpayhd.TAXPAID, arpayhd.TRANAMT,
EXCELSQL;arpayhd.DISC,arpayhd.OTHERS,
EXCELSQL;000000000.00 as icashamt,
EXCELSQL;000000000.00 as ichkamt,
EXCELSQL;000000000.00 as itaxpaid,
EXCELSQL;000000000.00 as itranamt,
EXCELSQL;000000000.00 as idisc,
EXCELSQL;000000000.00 as iothers
EXCELSQL;from arcard inner join _exp4 on arcard.REFNO = _exp4.iv
EXCELSQL;inner join arpayhd on arcard.DOCNO = arpayhd.DOCNO
EXCELSQL;where left(trtype,2) = 'BR'
EXCELSQL;order by 1,2 &
EXCELSQL;
EXCELSQL;update _exp5 set 
EXCELSQL;icashamt = cashamt * recamt / amount,
EXCELSQL;ichkamt = chkamt * recamt / amount,
EXCELSQL;itaxpaid = taxpaid * recamt / amount,
EXCELSQL;itranamt = tranamt * recamt / amount,
EXCELSQL;idisc = disc * recamt / amount,
EXCELSQL;iothers = others * recamt / amount&
EXCELSQL;
EXCELSQL;drop table if exists _exp6&
EXCELSQL;create table _exp6
EXCELSQL;select refno,  
EXCELSQL;sum(icashamt) as cashamt,
EXCELSQL;sum(ichkamt) as chkamt,
EXCELSQL;sum(itaxpaid) as taxpaid,
EXCELSQL;sum(itranamt) as tranamt,
EXCELSQL;sum(idisc) as disc,
EXCELSQL;sum(iothers) as others
EXCELSQL;from _exp5
EXCELSQL;group by 1&
EXCELSQL;
EXCELSQL;
EXCELSQL;update _exp1 inner join _exp6 on _exp1.iv = _exp6.refno
EXCELSQL;set wht = _exp6.taxpaid,
EXCELSQL;bkkchg = disc+others,
EXCELSQL;xtotal = taxpaid+disc+others,
EXCELSQL;cash = cashamt,
EXCELSQL;chq = chkamt,
EXCELSQL;tt = tranamt,
EXCELSQL;outstanding = xamount_l - ( taxpaid+disc+others+cashamt+chkamt+tranamt)&
EXCELSQL;
EXCELSQL;update _exp1 set xamount_l = invamt+vat,
EXCELSQL;ivamt = iv_invamt+iv_vatamt,
EXCELSQL;outstanding = xamount_L,
EXCELSQL;debtbal = outstanding - debtbal&
EXCELSQL;
EXCELSQL;
EXCELSQL;drop table if exists _exp3&
EXCELSQL;
EXCELSQL;create table _exp3
EXCELSQL;select accid, xcomp,docno,datedoc,IV,IVDATE,JOBNO,LINE,
EXCELSQL;sum(invamt) as invamt, sum(vat) as vat, sum(xamount_L) as xamount_L,
EXCELSQL;sum(wht) as wht, sum(bkkchg) as bkkchg, sum(cash) as cash, sum(chq) as chq, sum(tt) as tt,
EXCELSQL;sum(outstanding) as outstanding,chqno,chqdate,sum(iv_invamt) as iv_invamt, sum(iv_vatamt) as vatamt, sum(ivamt)  as ivamt,
EXCELSQL;sum(debtbal) as debtbal
EXCELSQL;from _exp1
EXCELSQL;group by accid, xcomp,docno,datedoc,IV,IVDATE,JOBNO,LINE
EXCELSQL;&
EXCELSQL;
EXCELSQL;drop table if exists _exp7&
EXCELSQL;create table _exp7
EXCELSQL;select arcard.docno,_exp4.iv, bktr.CHKNO, bktr.DATECHK from 
EXCELSQL;arcard inner join _exp4 on arcard.REFNO = _exp4.iv
EXCELSQL;inner join bktr on arcard.DOCNO = bktr.DOCNO
EXCELSQL;where left(arcard.docno,2) = 'BR' and left(bktr.PAYTYPE,2) = '02'&
EXCELSQL;
EXCELSQL;alter table _exp7
EXCELSQL; add index _exp7 (iv)&
EXCELSQL;
EXCELSQL;update _exp1 set chqdate = '0000-00-00'&
EXCELSQL;
EXCELSQL;update _exp1 inner join _exp7 on _exp1.iv = _exp7.iv 
EXCELSQL;set _exp1.chqno = _exp7.chkno, _exp1.chqdate = _exp7.datechk&
EXCELSQL;
EXCELSQL;select * from _exp1 order by docno,line
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
