REPORTFORM;PRCO.frf
TITLE;รายงานเปรียบเทียบราคาขาย/ราคาซื้อ
SQL;drop table if exists _prco0&
SQL;create table _prco0
SQL;select unpr.ID, invmst.XDESC, unpr.UNIT, unpr.PRICE1,
SQL;unpr.PRICE2, unpr.PRICE3, unpr.PRICE4, 0000000000.00 as QTY, 0000000000.00 as XCOST
SQL;from unpr inner join invmst on unpr.ID = invmst.ID
SQL;where unpr.ID >= :M11 and unpr.ID <= :M12
SQL;order by unpr.ID, unpr.LINE&
SQL;
SQL;ALTER TABLE `_prco0`
SQL;	ADD INDEX `ID` (`ID`)&
SQL;
SQL;drop table if exists _prco1&
SQL;create table _prco1
SQL;select id, max(docno) as docno , max(datedoc) as datedoc 
SQL;from invcard  
SQL;where left(docno,2) = "RC" 
SQL;group by 1&
SQL;
SQL;ALTER TABLE `_prco1`
SQL;	ADD INDEX `DATEDOC` (`DATEDOC`),
SQL;	ADD INDEX `DOCNO` (`DOCNO`)&
SQL;
SQL;drop table if exists _prco2&
SQL;create table _prco2
SQL;select invcard.ID, invcard.PRICE as XCOST
SQL;from invcard inner join _prco1 on invcard.DOCNO = _prco1.docno and invcard.DATEDOC = _prco1.datedoc
SQL;inner join invmst on invcard.ID = invmst.ID
SQL;group by invcard.ID&
SQL;
SQL;ALTER TABLE `_prco2`
SQL;	ADD INDEX `ID` (`ID`)&
SQL;
SQL;drop table if exists _prco3&
SQL;create table _prco3
SQL;select invcard.ID, invmst.UNIT, sum(invcard.REPBAL) as QTY
SQL;from invcard inner join invmst on invcard.ID = invmst.ID
SQL;where invcard.DATEDOC <= :M22
SQL;group by id&
SQL;
SQL;ALTER TABLE `_prco3`
SQL;	ADD INDEX `ID` (`ID`)&
SQL;
SQL;update _prco0 inner join _prco2 on _prco0.ID = _prco2.ID 
SQL;set _prco0.XCOST = _prco2.XCOST&
SQL;
SQL;update _prco0 inner join _prco3 on _prco0.ID = _prco3.ID and _prco0.UNIT = _prco3.UNIT
SQL;set _prco0.QTY = _prco3.QTY&
SQL;
SQL;select * from _prco0
CONDITION;ตั้งแต่รหัสสินค้า~UNPR.ID~Chr~2~SELECT ID FROM INVMST~INV
CONDITION;สิ้นสุด ณ วันที่~INVCARD.DATEDOC~Date~1~<=~
EXCELSQL;drop table if exists _prco0&
EXCELSQL;create table _prco0
EXCELSQL;select unpr.ID, invmst.XDESC, unpr.UNIT, unpr.RATIO, unpr.PRICE1,
EXCELSQL;unpr.PRICE2, unpr.PRICE3, unpr.PRICE4, 0000000000.00 as QTY, 0000000000.00 as XCOST
EXCELSQL;from unpr inner join invmst on unpr.ID = invmst.ID
EXCELSQL;where unpr.ID >= :M11 and unpr.ID <= :M12
EXCELSQL;order by unpr.ID, unpr.LINE&
EXCELSQL;
EXCELSQL;drop table if exists _prco1&
EXCELSQL;create table _prco1
EXCELSQL;select id, max(docno) as docno , max(datedoc) as datedoc 
EXCELSQL;from invcard  
EXCELSQL;where left(docno,2) = "RC" 
EXCELSQL;group by 1&
EXCELSQL;
EXCELSQL;drop table if exists _prco2&
EXCELSQL;create table _prco2
EXCELSQL;select invcard.ID, invcard.PRICE as XCOST, invcard.UNIT
EXCELSQL;from invcard inner join _prco1 on invcard.DOCNO = _prco1.docno and invcard.DATEDOC = _prco1.datedoc
EXCELSQL;inner join invmst on invcard.ID = invmst.ID
EXCELSQL;group by invcard.ID&
EXCELSQL;
EXCELSQL;drop table if exists _prco3&
EXCELSQL;create table _prco3
EXCELSQL;select invcard.ID, invmst.UNIT, sum(invcard.REPBAL) as QTY
EXCELSQL;from invcard inner join invmst on invcard.ID = invmst.ID
EXCELSQL;where invcard.DATEDOC <= :M22
EXCELSQL;group by id&
EXCELSQL;
EXCELSQL;update _prco0 inner join _prco2 on _prco0.ID = _prco2.ID 
EXCELSQL;set _prco0.XCOST = if(_prco0.UNIT = _prco2.UNIT,_prco2.XCOST,_prco2.XCOST * _prco0.RATIO)&
EXCELSQL;
EXCELSQL;update _prco0 inner join _prco3 on _prco0.ID = _prco3.ID and _prco0.UNIT = _prco3.UNIT
EXCELSQL;set _prco0.QTY = _prco3.QTY&
EXCELSQL;
EXCELSQL;select 
EXCELSQL;ID as "รหัสสินค้า", 
EXCELSQL;XDESC as "ชื่อสินค้า",
EXCELSQL;UNIT as "หน่วยนับ",
EXCELSQL;QTY as "จำนวนคงเหลือ",
EXCELSQL;PRICE1 as "ราคาขาย 1",
EXCELSQL;XCOST as "ต้นขาย", 
EXCELSQL;(PRICE1 - XCOST) * 100 / XCOST as "กำไร_1 % ",
EXCELSQL;PRICE2 as "ราคาขาย 2",
EXCELSQL;XCOST as "ต้นขาย", 
EXCELSQL;(PRICE2 - XCOST) * 100 / XCOST as "กำไร_2 % ",
EXCELSQL;PRICE3 as "ราคาขาย 3",
EXCELSQL;XCOST as "ต้นขาย", 
EXCELSQL;(PRICE3 - XCOST) * 100 / XCOST as "กำไร_3 % ",
EXCELSQL;PRICE4 as "ราคาขาย 4",
EXCELSQL;XCOST as "ต้นขาย", 
EXCELSQL;(PRICE4 - XCOST) * 100 / XCOST as "กำไร_4 % "
EXCELSQL;from _prco0
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
