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

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

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