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

[Pak R. <pak...@gm...>]

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
>
>