Menu
▾
▴
web-erp-developers
|
From: Pak R. <pak...@gm...> - 2015-07-23 01:24:59
|
Hi all: I've getting wrong results with DailySalesInquiry.php, as in an employee entered the same sales order twice (by error) and we cancelled it later on (by deleteting its items), in the DailySalesInquiry it was still showed as twice the sales. I changed the SQL to read salesorderdetails instead of stockmoves strating at line 96 $sql = "SELECT orddate AS trandate, SUM(unitprice*(1-discountpercent)* (qtyinvoiced)) as salesvalue, SUM(CASE WHEN mbflag='A' THEN 0 ELSE ((materialcost+labourcost+overheadcost) * qtyinvoiced) END) as cost FROM salesorders INNER JOIN salesorderdetails ON salesorders.orderno=salesorderdetails.orderno INNER JOIN stockmaster ON stockmaster.stockid=salesorderdetails.stkcode WHERE orddate>='" . $StartDateSQL . "' AND orddate<='" . $EndDateSQL . "'"; $sql .= " GROUP BY salesorders.orddate ORDER BY salesorders.orddate"; BTW, why did we check the sales via a table related to stock movements? Not sure if this change will seem reasonable to the community, so before I upload to SVN would like your votes on this. Regards, Ricard |
|
From: Phil D. <ph...@lo...> - 2015-07-23 06:23:50
|
Well the stock movements should capture all sales and all the other inquiries use this table - could it be because the sale was an assembly item and we need to simply exclude the components that are hidden stock movements. The stock movements are used to print invoices so it is definitely possible to use them - the other snag with salesorderdetails is that it cannot capture credit notes. Did you do a credit note for the duplicated sale? Phil Phil Daintree Logic Works Ltd - +64 (0)275 567890 http://www.logicworks.co.nz On 23/07/15 12:57, Pak Ricard wrote: > Hi all: > > I've getting wrong results with DailySalesInquiry.php, as in an > employee entered the same sales order twice (by error) and we > cancelled it later on (by deleteting its items), in the > DailySalesInquiry it was still showed as twice the sales. > > I changed the SQL to read salesorderdetails instead of stockmoves > strating at line 96 > > $sql = "SELECT orddate AS trandate, > SUM(unitprice*(1-discountpercent)* (qtyinvoiced)) as salesvalue, > SUM(CASE WHEN mbflag='A' THEN 0 ELSE > ((materialcost+labourcost+overheadcost) * qtyinvoiced) END) as cost > FROM salesorders > INNER JOIN salesorderdetails > ON salesorders.orderno=salesorderdetails.orderno > INNER JOIN stockmaster > ON stockmaster.stockid=salesorderdetails.stkcode > WHERE orddate>='" . $StartDateSQL . "' > AND orddate<='" . $EndDateSQL . "'"; > > $sql .= " GROUP BY salesorders.orddate ORDER BY salesorders.orddate"; > > BTW, why did we check the sales via a table related to stock movements? > > Not sure if this change will seem reasonable to the community, so > before I upload to SVN would like your votes on this. > > > Regards, > Ricard > > > ------------------------------------------------------------------------------ > > > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers |
|
From: Pak R. <pak...@gm...> - 2015-07-23 06:28:57
|
Hi Phil: Understood. For the duplicated sales order, we just deleted it (deleted the items in the second one), we did not a credit note. Regards, Ricard 2015-07-23 14:23 GMT+08:00 Phil Daintree <ph...@lo...>: > Well the stock movements should capture all sales and all the other > inquiries use this table - could it be because the sale was an assembly > item and we need to simply exclude the components that are hidden stock > movements. The stock movements are used to print invoices so it is > definitely possible to use them - the other snag with salesorderdetails is > that it cannot capture credit notes. Did you do a credit note for the > duplicated sale? > > Phil > > Phil Daintree > Logic Works Ltd - +64 (0)275 567890http://www.logicworks.co.nz > > On 23/07/15 12:57, Pak Ricard wrote: > > Hi all: > > I've getting wrong results with DailySalesInquiry.php, as in an employee > entered the same sales order twice (by error) and we cancelled it later on > (by deleteting its items), in the DailySalesInquiry it was still showed as > twice the sales. > > I changed the SQL to read salesorderdetails instead of stockmoves > strating at line 96 > > $sql = "SELECT orddate AS trandate, > SUM(unitprice*(1-discountpercent)* (qtyinvoiced)) as salesvalue, > SUM(CASE WHEN mbflag='A' THEN 0 ELSE > ((materialcost+labourcost+overheadcost) * qtyinvoiced) END) as cost > FROM salesorders > INNER JOIN salesorderdetails > ON salesorders.orderno=salesorderdetails.orderno > INNER JOIN stockmaster > ON stockmaster.stockid=salesorderdetails.stkcode > WHERE orddate>='" . $StartDateSQL . "' > AND orddate<='" . $EndDateSQL . "'"; > > $sql .= " GROUP BY salesorders.orddate ORDER BY salesorders.orddate"; > > BTW, why did we check the sales via a table related to stock movements? > > Not sure if this change will seem reasonable to the community, so before > I upload to SVN would like your votes on this. > > > Regards, > Ricard > > > ------------------------------------------------------------------------------ > > > > _______________________________________________ > Web-erp-developers mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > > > > ------------------------------------------------------------------------------ > > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > |
|
From: Phil D. <ph...@lo...> - 2015-07-23 08:06:05
|
So it had been invoice twice? Phil Phil Daintree Logic Works Ltd - +64 (0)275 567890 http://www.logicworks.co.nz On 23/07/15 18:27, Pak Ricard wrote: > Hi Phil: > > Understood. > > For the duplicated sales order, we just deleted it (deleted the items > in the second one), we did not a credit note. > > > Regards, > Ricard > > 2015-07-23 14:23 GMT+08:00 Phil Daintree <ph...@lo... > <mailto:ph...@lo...>>: > > Well the stock movements should capture all sales and all the > other inquiries use this table - could it be because the sale was > an assembly item and we need to simply exclude the components that > are hidden stock movements. The stock movements are used to print > invoices so it is definitely possible to use them - the other snag > with salesorderdetails is that it cannot capture credit notes. Did > you do a credit note for the duplicated sale? > > Phil > > Phil Daintree > Logic Works Ltd - +64 (0)275 567890 > http://www.logicworks.co.nz > > On 23/07/15 12:57, Pak Ricard wrote: >> Hi all: >> >> I've getting wrong results with DailySalesInquiry.php, as in an >> employee entered the same sales order twice (by error) and we >> cancelled it later on (by deleteting its items), in the >> DailySalesInquiry it was still showed as twice the sales. >> >> I changed the SQL to read salesorderdetails instead of stockmoves >> strating at line 96 >> >> $sql = "SELECT orddate AS trandate, >> SUM(unitprice*(1-discountpercent)* (qtyinvoiced)) as salesvalue, >> SUM(CASE WHEN mbflag='A' THEN 0 ELSE >> ((materialcost+labourcost+overheadcost) * qtyinvoiced) END) as cost >> FROM salesorders >> INNER JOIN salesorderdetails >> ON salesorders.orderno=salesorderdetails.orderno >> INNER JOIN stockmaster >> ON stockmaster.stockid=salesorderdetails.stkcode >> WHERE orddate>='" . $StartDateSQL . "' >> AND orddate<='" . $EndDateSQL . "'"; >> >> $sql .= " GROUP BY salesorders.orddate ORDER BY salesorders.orddate"; >> >> BTW, why did we check the sales via a table related to stock >> movements? >> >> Not sure if this change will seem reasonable to the community, so >> before I upload to SVN would like your votes on this. >> >> >> Regards, >> Ricard >> >> >> ------------------------------------------------------------------------------ >> >> >> _______________________________________________ >> Web-erp-developers mailing list >> Web...@li... <mailto:Web...@li...> >> https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > > ------------------------------------------------------------------------------ > > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > <mailto:Web...@li...> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > > > > ------------------------------------------------------------------------------ > > > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers |
|
From: Pak R. <pak...@gm...> - 2015-07-23 09:27:13
|
Sorry Phil: I was wrong. We did the credit note and also had to delete the duplicated sales order as it showed as outstanding. I can't understand then why the DailySalesInquiry was showing the data as duplicated. Will dig in it and let you know. Regards, Ricard 2015-07-23 16:05 GMT+08:00 Phil Daintree <ph...@lo...>: > So it had been invoice twice? > > Phil > > Phil Daintree > Logic Works Ltd - +64 (0)275 567890http://www.logicworks.co.nz > > On 23/07/15 18:27, Pak Ricard wrote: > > Hi Phil: > > Understood. > > For the duplicated sales order, we just deleted it (deleted the items in > the second one), we did not a credit note. > > > Regards, > Ricard > > 2015-07-23 14:23 GMT+08:00 Phil Daintree <ph...@lo...>: > >> Well the stock movements should capture all sales and all the other >> inquiries use this table - could it be because the sale was an assembly >> item and we need to simply exclude the components that are hidden stock >> movements. The stock movements are used to print invoices so it is >> definitely possible to use them - the other snag with salesorderdetails is >> that it cannot capture credit notes. Did you do a credit note for the >> duplicated sale? >> >> Phil >> >> Phil Daintree >> Logic Works Ltd - +64 (0)275 567890http://www.logicworks.co.nz >> >> On 23/07/15 12:57, Pak Ricard wrote: >> >> Hi all: >> >> I've getting wrong results with DailySalesInquiry.php, as in an >> employee entered the same sales order twice (by error) and we cancelled it >> later on (by deleteting its items), in the DailySalesInquiry it was still >> showed as twice the sales. >> >> I changed the SQL to read salesorderdetails instead of stockmoves >> strating at line 96 >> >> $sql = "SELECT orddate AS trandate, >> SUM(unitprice*(1-discountpercent)* (qtyinvoiced)) as salesvalue, >> SUM(CASE WHEN mbflag='A' THEN 0 ELSE >> ((materialcost+labourcost+overheadcost) * qtyinvoiced) END) as cost >> FROM salesorders >> INNER JOIN salesorderdetails >> ON salesorders.orderno=salesorderdetails.orderno >> INNER JOIN stockmaster >> ON stockmaster.stockid=salesorderdetails.stkcode >> WHERE orddate>='" . $StartDateSQL . "' >> AND orddate<='" . $EndDateSQL . "'"; >> >> $sql .= " GROUP BY salesorders.orddate ORDER BY salesorders.orddate"; >> >> BTW, why did we check the sales via a table related to stock movements? >> >> Not sure if this change will seem reasonable to the community, so >> before I upload to SVN would like your votes on this. >> >> >> Regards, >> Ricard >> >> >> ------------------------------------------------------------------------------ >> >> >> >> _______________________________________________ >> Web-erp-developers mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/web-erp-developers >> >> >> >> >> ------------------------------------------------------------------------------ >> >> _______________________________________________ >> Web-erp-developers mailing list >> Web...@li... >> https://lists.sourceforge.net/lists/listinfo/web-erp-developers >> >> > > > ------------------------------------------------------------------------------ > > > > _______________________________________________ > Web-erp-developers mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > > > > ------------------------------------------------------------------------------ > > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X