Re: [WebERP-developers] SQL change proposal in DailySalesInquiry.php

[Phil D. <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...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers