SQL change proposal in DailySalesInquiry.php

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

SQL change proposal in DailySalesInquiry.php

Pak Ricard
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL change proposal in DailySalesInquiry.php

phildaintree
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers


------------------------------------------------------------------------------

_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
If anyone is wondering about the persistently nasty comments made by Tim Schofield and wants the full story please see: http://timschofield.blogspot.com/ Hell hath no fury like a woman (or Tim) scorned
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL change proposal in DailySalesInquiry.php

Pak Ricard
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 <[hidden email]>:
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers


------------------------------------------------------------------------------

_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers



------------------------------------------------------------------------------

_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL change proposal in DailySalesInquiry.php

phildaintree
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 <[hidden email]>:
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers


------------------------------------------------------------------------------

_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers




------------------------------------------------------------------------------


_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers


------------------------------------------------------------------------------

_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
If anyone is wondering about the persistently nasty comments made by Tim Schofield and wants the full story please see: http://timschofield.blogspot.com/ Hell hath no fury like a woman (or Tim) scorned
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL change proposal in DailySalesInquiry.php

Pak Ricard
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 <[hidden email]>:
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 <[hidden email]>:
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers


------------------------------------------------------------------------------

_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers




------------------------------------------------------------------------------


_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers


------------------------------------------------------------------------------

_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers



------------------------------------------------------------------------------

_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
Loading...