REPORTFORM;TR5.frf
TITLE;เรียงตามรหัสสินค้า วันที่ แสดงต้นทุน
SQL;drop table if exists _invmove2&
SQL;
SQL;create table _invmove2
SQL;select curdate() as datedoc, space(10) as locid,concat('ยอดยกมา',space(20)) as docno,
SQL;0000000000.000 as Qin,0000000000.000 as Ain,0000000000.000 as Costin,
SQL;0000000000.000 as Qout,0000000000.000 as Aout,0000000000.000 as Costout,
SQL;invcard.id, invmst.xdesc,invmst.unit,0 as line, 0 as trline,
SQL;"00" AS TRTYPE
SQL;from invcard left join invmst on invcard.id=invmst.id
SQL;where datedoc < :m21 and invcard.id >= :m11 and invcard.id <= :m12
SQL;and not (invmst.XGROUP = "03" OR invmst.XGROUP = "04")
SQL;group by invcard.id, invmst.xdesc,invmst.unit&
SQL;
SQL;update _invmove2 set datedoc = :m21&
SQL;
SQL;insert into _invmove2
SQL;select datedoc,invcard.locid,docno,repbal,costrepbal,0.000 as costin,
SQL;0.000 as qout, 0.000 as Aout, 0.000 as costout,
SQL;invcard.id, invmst.xdesc,invmst.unit,1 as line, line as trline,
SQL;invcard.TRTYPE
SQL;from invcard left join invmst on invcard.id = invmst.id 
SQL;where trtype = "01" and left(docno,2) = "TR"
SQL;and datedoc >= :m21 and datedoc <= :m22 
SQL;and invcard.id >= :m11 and invcard.id <= :m12
SQL;and not (invmst.XGROUP = "03" OR invmst.XGROUP = "04")&
SQL;
SQL;insert into _invmove2
SQL;select datedoc,invcard.locid,docno,0.000 as qinl,0.000 as ain,0.000 as costin,
SQL;repbal * -1 as qout, truncate(abs(repbal) * cost,2) as Aout, 0.000 as costout,
SQL;invcard.id, invmst.xdesc,invmst.unit,1 as line, line as trline,
SQL;invcard.TRTYPE
SQL;from invcard left join invmst on invcard.id = invmst.id 
SQL;where trtype = "02"  and left(docno,2) = "TR"
SQL;and datedoc >= :m21 and datedoc <= :m22 
SQL;and invcard.id >= :m11 and invcard.id <= :m12
SQL;and not (invmst.XGROUP = "03" OR invmst.XGROUP = "04")&
SQL;
SQL;update _invmove2 set costin = ain/qin, costout = aout/qout&
SQL;
SQL;delete from _invmove2 where qin + qout = 0&
SQL; 
SQL;select * from _invmove2 order by id,datedoc,trtype,line
CONDITION;ตั้งแต่รหัสสินค้า~INVCARD.ID~Chr~2~SELECT ID FROM INVMST~INV
CONDITION;ตั้งแต่วันที่~INVCARD.DATEDOC~Date~2~~
CONDITION;เงื่อนไข~~TextFile~~INVMOVE_TR.TXT~
EXCELSQL;drop table if exists _invmove2&
EXCELSQL;
EXCELSQL;create table _invmove2
EXCELSQL;select curdate() as datedoc, space(10) as locid,concat('ยอดยกมา',space(20)) as docno,
EXCELSQL;0000000000.000 as Qin,0000000000.000 as Ain,0000000000.000 as Costin,
EXCELSQL;0000000000.000 as Qout,0000000000.000 as Aout,0000000000.000 as Costout,
EXCELSQL;SUM(REPBAL) as Qbal, SUM(COSTREPBAL) as Abal,0000000000.000 as Costbal,
EXCELSQL;SPACE(20) AS xrefno,invcard.id, invmst.xdesc,invmst.unit,0 as line, 0 as trline,
EXCELSQL;"00" AS TRTYPE
EXCELSQL;from invcard left join invmst on invcard.id=invmst.id
EXCELSQL;where datedoc < :m21 and invcard.id >= :m11 and invcard.id <= :m12
EXCELSQL;and not (invmst.XGROUP = "03" OR invmst.XGROUP = "04")
EXCELSQL;group by invcard.id, invmst.xdesc,invmst.unit&
EXCELSQL;
EXCELSQL;update _invmove2 set datedoc = :m21&
EXCELSQL;
EXCELSQL;insert into _invmove2
EXCELSQL;select datedoc,invcard.locid,docno,repbal,costrepbal,0.000 as costin,
EXCELSQL;0.000 as qout, 0.000 as Aout, 0.000 as costout,0.000 as qbal, 
EXCELSQL;0.000 as abal, 0.000 as costbal,'' as xrefno, invcard.id, invmst.xdesc,invmst.unit,1 as line, line as trline,
EXCELSQL;invcard.TRTYPE
EXCELSQL;from invcard left join invmst on invcard.id = invmst.id 
EXCELSQL;where trtype = "01" and left(docno,2) = "TR"
EXCELSQL;and datedoc >= :m21 and datedoc <= :m22 
EXCELSQL;and invcard.id >= :m11 and invcard.id <= :m12
EXCELSQL;and not (invmst.XGROUP = "03" OR invmst.XGROUP = "04")&
EXCELSQL;
EXCELSQL;insert into _invmove2
EXCELSQL;select datedoc,invcard.locid,docno,0.000 as qinl,0.000 as ain,0.000 as costin,
EXCELSQL;repbal * -1 as qout, truncate(abs(repbal) * cost,2) as Aout, 0.000 as costout,0.000 as qbal, 
EXCELSQL;0.000 as abal, 0.000 as costbal,'' as xrefno, invcard.id, invmst.xdesc,invmst.unit,1 as line, line as trline,
EXCELSQL;invcard.TRTYPE
EXCELSQL;from invcard left join invmst on invcard.id = invmst.id 
EXCELSQL;where trtype = "02"  and left(docno,2) = "TR"
EXCELSQL;and datedoc >= :m21 and datedoc <= :m22 
EXCELSQL;and invcard.id >= :m11 and invcard.id <= :m12
EXCELSQL;and not (invmst.XGROUP = "03" OR invmst.XGROUP = "04")&
EXCELSQL;
EXCELSQL;update _invmove2 set costin = ain/qin, costout = aout/qout, costbal = abal/qbal&
EXCELSQL;
EXCELSQL; 
EXCELSQL;select * from _invmove2 order by id,datedoc,trtype,line
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
