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

[Phil D. <ph...@lo...>]

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