REPORTFORM;IVC5.frf
TITLE;รายงานมูลค่าสินค้า ตามเลขที่ผังบัญชี
SQL;drop table if exists _xcost4&
SQL;create table _xcost4 
SQL;SELECT invmst.xsgroup AS XGROUP,space(30) AS DOCNO,space(30) AS REFNO,invcard.DATEDOC,
SQL;invcard.id AS XID,invmst.xdesc AS XDESC,
SQL;invmst.unit AS XUNIT1,SUM(invcard.REPBAL) AS BEG,SUM(invcard.COSTREPBAL) AS BAL,
SQL;"00" AS XTRTYPE,0000000.0000 AS XIN,000000.0000 AS XCIN,space(20) AS LOCID,space(60) AS LOCDESC,
SQL;space(30) AS SHELF,0 AS NOMOVE,00 AS LINE,0000000.0000 AS XRC,
SQL;0000000.0000 AS XIV, 000000.0000 AS XRI, 0000000.0000 AS XRO,
SQL;0000000.0000 AS XAJ1, 0000000.0000 AS XAJ2,
SQL;0000000.0000 AS XCOUT, 0000000.0000 As OUT, space(70) aS XREMARK,
SQL;space(20) AS XIDTYPE, space(60) AS XIDTDESC,
SQL;0000000.0000 AS REPBAL, 0000000.0000 AS COSTREPBAL,
SQL;000000.0000 AS COST, "00" AS XTRTYPE2
SQL;FROM invcard LEFT JOIN invmst ON invcard.ID = invmst.ID
SQL;WHERE invcard.DATEDOC < :m21 and
SQL;(invmst.INVGLID >= :m11 and invmst.INVGLID <= :m12 ) 
SQL;GROUP BY invcard.ID, invmst.XDESC
SQL;ORDER BY invcard.ID&
SQL;
SQL;insert into _xcost4
SQL;SELECT invmst.xsgroup,invcard.docno,invcard.orddoc,invcard.datedoc,
SQL;invcard.id,invmst.xdesc,invmst.unit,0,0,invcard.TRTYPE,0,0,
SQL;invcard.locid,loca.locdesc,invshelf.shelf,1,
SQL;invcard.line,0,0,0,0,0,0,0,0,"",
SQL;invmst.XIDTYPE, idtype.idtdesc,
SQL;invcard.repbal,invcard.costrepbal,
SQL;invcard.cost,invcard.trtype2
SQL;FROM invcard LEFT JOIN invmst ON invcard.ID = invmst.ID
SQL;LEFT JOIN loca ON invcard.LOCID = loca.LOCID
SQL;LEFT JOIN idtype ON invmst.XIDTYPE = idtype.IDTID
SQL;LEFT JOIN invshelf ON invcard.id = invshelf.id and invcard.locid = invshelf.locid
SQL;WHERE (invcard.DATEDOC >= :m21 AND invcard.DATEDOC <= :m22 )
SQL;AND (invmst.INVGLID >= :m11 AND invmst.INVGLID <= :m12 )
SQL;ORDER BY invcard.ID&
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการซื้อสินค้า",XIN = REPBAL, XCIN = COSTREPBAL, XRC = XIN
SQL;WHERE LEFT(DOCNO,2)="RC"&
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการขายสินค้า",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1, XIV = OUT
SQL;WHERE LEFT(DOCNO,2)="IV"&
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการรับคืนสินค้า",XIN = REPBAL, XCIN = COSTREPBAL,XRI = XIN
SQL;WHERE LEFT(DOCNO,2)="RI"&
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการส่งคืนสินค้า",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1, XRO = OUT
SQL;WHERE LEFT(DOCNO,2)="RO"&
SQL;  
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการปรับปรุงลดสินค้า",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1,XAJ2 = OUT
SQL;WHERE (LEFT(XTRTYPE,2)="02" AND LEFT(DOCNO,2) = "AJ")&
SQL;  
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการปรับปรุงเพิ่มสินค้า",XIN = REPBAL, XCIN = COSTREPBAL,XAJ1 = XIN
SQL;WHERE (LEFT(XTRTYPE,2)="01" AND LEFT(DOCNO,2) = "AJ")&
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการเบิกวัตถุดิบไปผลิต",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1,XAJ2 = OUT
SQL;WHERE LEFT(DOCNO,2)="IS"&
SQL;
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการรับคืนวัตถุดิบจากการผลิต",XIN = REPBAL, XCIN = COSTREPBAL,XAJ1 = XIN
SQL;WHERE LEFT(DOCNO,2)="RS"&
SQL;
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการรับสินค้าจากการผลิต",XIN = REPBAL, XCIN = COSTREPBAL,XAJ1 = XIN
SQL;WHERE LEFT(DOCNO,2)="RF"&
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการโอนย้ายสินค้าระหว่างคลัง",XIN = REPBAL, XCIN = COSTREPBAL, XAJ1 = XIN
SQL;WHERE LEFT(DOCNO,2)="TR" AND XTRTYPE = "01"&
SQL;
SQL;UPDATE _xcost4
SQL;SET XREMARK = "รายการโอนย้ายสินค้าระหว่างคลัง",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1, XAJ2 = OUT
SQL;WHERE LEFT(DOCNO,2)="TR" AND XTRTYPE <> "01"&
SQL;
SQL;UPDATE _xcost4 INNER JOIN INVMST ON _xcost4.XID = INVMST.ID
SQL;SET _xcost4.LOCID = INVMST.INVGLID&
SQL;
SQL;UPDATE _xcost4 INNER JOIN GLMST ON _xcost4.LOCID = GLMST.GLID
SQL;SET _xcost4.LOCDESC = GLMST.GLDESC&
SQL;
SQL;UPDATE _xcost4 INNER JOIN INVMST ON _xcost4.XID = INVMST.ID
SQL;SET _xcost4.SHELF = "_DEL_" WHERE (INVMST.XGROUP = "03" OR INVMST.XGROUP = "04")&
SQL;  
SQL;DELETE FROM  _xcost4 WHERE SHELF = "_DEL_"&
SQL;  
SQL;update _xcost4 
SQL;set DOCNO = "ยอดยกมา",
SQL;DATEDOC = :m21
SQL;where XTRTYPE = "00"&
SQL;
SQL;SELECT XID, XDESC,SUM(TRUNCATE(BAL,4)) AS CBEG, 
SQL;SUM(TRUNCATE(XCIN,4)) AS COSTIN,
SQL;SUM(TRUNCATE(XCOUT,4)) AS COSTOUT,LOCID, LOCDESC
SQL;FROM _xcost4
SQL;GROUP BY XID,XDESC,LOCID,LOCDESC
SQL;HAVING SUM(BAL) <> 0 OR SUM(XCIN) <> 0 OR SUM(XCOUT) <> 0 
SQL;ORDER BY LOCID,XID,XDESC
CONDITION;ตั้งแต่รหัสผังบัญชี~INVMST.INVGLID~Chr~2~SELECT GLID FROM GLMST~GL
CONDITION;ตั้งแต่วันที่~INVCARD.DATEDOC~Date~2~~
EXCELSQL;drop table if exists _xcost4&
EXCELSQL;create table _xcost4 
EXCELSQL;SELECT invmst.xsgroup AS XGROUP,space(30) AS DOCNO,space(30) AS REFNO,invcard.DATEDOC,
EXCELSQL;invcard.id AS XID,invmst.xdesc AS XDESC,
EXCELSQL;invmst.unit AS XUNIT1,SUM(invcard.REPBAL) AS BEG,SUM(invcard.COSTREPBAL) AS BAL,
EXCELSQL;"00" AS XTRTYPE,0000000.0000 AS XIN,000000.0000 AS XCIN,space(20) AS LOCID,space(60) AS LOCDESC,
EXCELSQL;space(30) AS SHELF,0 AS NOMOVE,00 AS LINE,0000000.0000 AS XRC,
EXCELSQL;0000000.0000 AS XIV, 000000.0000 AS XRI, 0000000.0000 AS XRO,
EXCELSQL;0000000.0000 AS XAJ1, 0000000.0000 AS XAJ2,
EXCELSQL;0000000.0000 AS XCOUT, 0000000.0000 As OUT, space(70) aS XREMARK,
EXCELSQL;space(20) AS XIDTYPE, space(60) AS XIDTDESC,
EXCELSQL;0000000.0000 AS REPBAL, 0000000.0000 AS COSTREPBAL,
EXCELSQL;000000.0000 AS COST, "00" AS XTRTYPE2
EXCELSQL;FROM invcard LEFT JOIN invmst ON invcard.ID = invmst.ID
EXCELSQL;WHERE invcard.DATEDOC < :m21 and
EXCELSQL;(invmst.INVGLID >= :m11 and invmst.INVGLID <= :m12 ) 
EXCELSQL;GROUP BY invcard.ID, invmst.XDESC
EXCELSQL;ORDER BY invcard.ID&
EXCELSQL;
EXCELSQL;insert into _xcost4
EXCELSQL;SELECT invmst.xsgroup,invcard.docno,invcard.orddoc,invcard.datedoc,
EXCELSQL;invcard.id,invmst.xdesc,invmst.unit,0,0,invcard.TRTYPE,0,0,
EXCELSQL;invcard.locid,loca.locdesc,invshelf.shelf,1,
EXCELSQL;invcard.line,0,0,0,0,0,0,0,0,"",
EXCELSQL;invmst.XIDTYPE, idtype.idtdesc,
EXCELSQL;invcard.repbal,invcard.costrepbal,
EXCELSQL;invcard.cost,invcard.trtype2
EXCELSQL;FROM invcard LEFT JOIN invmst ON invcard.ID = invmst.ID
EXCELSQL;LEFT JOIN loca ON invcard.LOCID = loca.LOCID
EXCELSQL;LEFT JOIN idtype ON invmst.XIDTYPE = idtype.IDTID
EXCELSQL;LEFT JOIN invshelf ON invcard.id = invshelf.id and invcard.locid = invshelf.locid
EXCELSQL;WHERE (invcard.DATEDOC >= :m21 AND invcard.DATEDOC <= :m22 )
EXCELSQL;AND (invmst.INVGLID >= :m11 AND invmst.INVGLID <= :m12 )
EXCELSQL;ORDER BY invcard.ID&
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการซื้อสินค้า",XIN = REPBAL, XCIN = COSTREPBAL, XRC = XIN
EXCELSQL;WHERE LEFT(DOCNO,2)="RC"&
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการขายสินค้า",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1, XIV = OUT
EXCELSQL;WHERE LEFT(DOCNO,2)="IV"&
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการรับคืนสินค้า",XIN = REPBAL, XCIN = COSTREPBAL,XRI = XIN
EXCELSQL;WHERE LEFT(DOCNO,2)="RI"&
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการส่งคืนสินค้า",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1, XRO = OUT
EXCELSQL;WHERE LEFT(DOCNO,2)="RO"&
EXCELSQL;  
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการปรับปรุงลดสินค้า",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1,XAJ2 = OUT
EXCELSQL;WHERE (LEFT(XTRTYPE,2)="02" AND LEFT(DOCNO,2) = "AJ")&
EXCELSQL;  
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการปรับปรุงเพิ่มสินค้า",XIN = REPBAL, XCIN = COSTREPBAL,XAJ1 = XIN
EXCELSQL;WHERE (LEFT(XTRTYPE,2)="01" AND LEFT(DOCNO,2) = "AJ")&
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการเบิกวัตถุดิบไปผลิต",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1,XAJ2 = OUT
EXCELSQL;WHERE LEFT(DOCNO,2)="IS"&
EXCELSQL;
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการรับคืนวัตถุดิบจากการผลิต",XIN = REPBAL, XCIN = COSTREPBAL,XAJ1 = XIN
EXCELSQL;WHERE LEFT(DOCNO,2)="RS"&
EXCELSQL;
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการรับสินค้าจากการผลิต",XIN = REPBAL, XCIN = COSTREPBAL,XAJ1 = XIN
EXCELSQL;WHERE LEFT(DOCNO,2)="RF"&
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการโอนย้ายสินค้าระหว่างคลัง",XIN = REPBAL, XCIN = COSTREPBAL, XAJ1 = XIN
EXCELSQL;WHERE LEFT(DOCNO,2)="TR" AND XTRTYPE = "01"&
EXCELSQL;
EXCELSQL;UPDATE _xcost4
EXCELSQL;SET XREMARK = "รายการโอนย้ายสินค้าระหว่างคลัง",OUT = REPBAL * -1, XCOUT = COSTREPBAL * -1, XAJ2 = OUT
EXCELSQL;WHERE LEFT(DOCNO,2)="TR" AND XTRTYPE <> "01"&
EXCELSQL;
EXCELSQL;UPDATE _xcost4 INNER JOIN INVMST ON _xcost4.XID = INVMST.ID
EXCELSQL;SET _xcost4.LOCID = INVMST.INVGLID&
EXCELSQL;
EXCELSQL;UPDATE _xcost4 INNER JOIN GLMST ON _xcost4.LOCID = GLMST.GLID
EXCELSQL;SET _xcost4.LOCDESC = GLMST.GLDESC&
EXCELSQL;
EXCELSQL;UPDATE _xcost4 INNER JOIN INVMST ON _xcost4.XID = INVMST.ID
EXCELSQL;SET _xcost4.SHELF = "_DEL_" WHERE (INVMST.XGROUP = "03" OR INVMST.XGROUP = "04")&
EXCELSQL;  
EXCELSQL;DELETE FROM  _xcost4 WHERE SHELF = "_DEL_"&
EXCELSQL;  
EXCELSQL;update _xcost4 
EXCELSQL;set DOCNO = "ยอดยกมา",
EXCELSQL;DATEDOC = :m21
EXCELSQL;where XTRTYPE = "00"&
EXCELSQL;
EXCELSQL;SELECT XID AS "รหัสสินค้า", XDESC AS "รายละเอียด",SUM(TRUNCATE(BAL,4)) AS "มูลค่ายกมา", 
EXCELSQL;SUM(TRUNCATE(XCIN,4)) AS "มูลค่ารับ",
EXCELSQL;SUM(TRUNCATE(XCOUT,4)) AS "มูลค่าออก",
EXCELSQL;(SUM(TRUNCATE(BAL,4))+SUM(TRUNCATE(XCIN,4))-SUM(TRUNCATE(XCOUT,4))) AS "มูลค่าสุทธิ",
EXCELSQL;LOCID AS "รหัสบัญชี" , LOCDESC AS "ชื่อบัญชี"
EXCELSQL;FROM _xcost4
EXCELSQL;GROUP BY XID,XDESC,LOCID,LOCDESC
EXCELSQL;HAVING SUM(BAL) <> 0 OR SUM(XCIN) <> 0 OR SUM(XCOUT) <> 0 
EXCELSQL;ORDER BY LOCID,XID,XDESC
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
