Page 1 of 1
[OC2.0] Customer Orders Report
Posted: Fri Dec 12, 2014 4:11 pm
by beipink
Has anyone noticed any problem with the Customer Orders Report.
It appears the numbers are much larger than what they suppose to be.
To run the report; go to -> Reports/Customers/Orders
Be warned, this is slow and it might run out of time or give errors. I would suggest to run it offline.
thank you
Re: [OC2.0] Customer Orders Report
Posted: Mon Jan 05, 2015 8:51 am
by onlinemarketer
Yes mine are much higher
Also Reports>products>purchased are also randomly must higher. Some 1.5x higher like its usd and other 7 times higher.
On this report and also on the dashboard it's showing the amount it would have been in USD.
Re: [OC2.0] Customer Orders Report
Posted: Fri Jan 16, 2015 6:32 am
by gahinton
I am having this same problem. It is showing things like customers who have only made 1 order look like they have made 4.
Re: [OC2.0] Customer Orders Report
Posted: Wed Feb 18, 2015 11:48 pm
by devlinhall
My order totals are too high also. The totals seem to be taking the proper invoice total and then multiplying it by the number of different products within that order.
e.g. proper order total = 281.82 , number of different products = 29. Customer Orders report = 8172.78 = 281.82*29
Re: [OC2.0] Customer Orders Report
Posted: Sat Feb 21, 2015 11:52 pm
by devlinhall
Within admin/controller/report/customer.php - public function getOrders()
I think the "left join" in the sql to find the product quantities is resulting in the order total rows being duplicated to accommodate the quantity value for each unique product on an order, resulting in the number of orders and their order totals to be skewed.
Re: [OC2.0] Customer Orders Report
Posted: Wed Feb 25, 2015 5:55 am
by devlinhall
I posted a bug report on opencart github "customer orders report totals wrong #2709" to get an answer. danielkerr closed it.
Re: [OC2.0] Customer Orders Report
Posted: Thu Feb 26, 2015 3:05 am
by BrettMW
Hello All,
I have had a look and the problem is that the SQL query duplicates the order row for each different product, resulting in the total being wrong (as suggested by devlinhall)
You can fix this by changing the getOrders() function in admin/model/report/customer.php
Change the line
Code: Select all
$sql = "SELECT c.customer_id, CONCAT(c.firstname, ' ', c.lastname) AS customer, c.email, cgd.name AS customer_group, c.status, COUNT(o.order_id) AS orders, SUM(op.quantity) AS products, SUM(o.total) AS `total` FROM `" . DB_PREFIX . "order` o LEFT JOIN `" . DB_PREFIX . "order_product` op ON (o.order_id = op.order_id)LEFT JOIN `" . DB_PREFIX . "customer` c ON (o.customer_id = c.customer_id) LEFT JOIN `" . DB_PREFIX . "customer_group_description` cgd ON (c.customer_group_id = cgd.customer_group_id) WHERE o.customer_id > 0 AND cgd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
to
Code: Select all
$sql = "SELECT c.customer_id, CONCAT(c.firstname, ' ', c.lastname) AS customer, c.email, cgd.name AS customer_group, c.status, COUNT(DISTINCT o.order_id) AS orders, SUM(op.quantity) AS products, SUM(op.total) AS `total` FROM `" . DB_PREFIX . "order` o LEFT JOIN `" . DB_PREFIX . "order_product` op ON (o.order_id = op.order_id)LEFT JOIN `" . DB_PREFIX . "customer` c ON (o.customer_id = c.customer_id) LEFT JOIN `" . DB_PREFIX . "customer_group_description` cgd ON (c.customer_group_id = cgd.customer_group_id) WHERE o.customer_id > 0 AND cgd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
This changes it so that it only counts unique orders (so the order count will be fixed), and it sums the order product totals rather than the order totals. Note that the total won't include shipping/tax with this fix.
Re: [OC2.0] Customer Orders Report
Posted: Thu Feb 26, 2015 5:49 am
by devlinhall
Nice to see someone looking into the cause of this problem BrettMW. It would certainly be a simpler fix to keep the single sql command but I think any order totals need to represent the 'final' order total after taxes/shipping/coupons etc. I am assuming that the only way to properly fix this is to split the product quantities into a separate sql command, meaning more code changes outside of the sql.
Re: [OC2.0] Customer Orders Report
Posted: Wed Nov 18, 2015 5:12 pm
by insomniak
I see this bug still exists in my install of 2.0.3.1, I will attempt the fix now as posted above and see if it helps.
Re: [OC2.0] Customer Orders Report
Posted: Wed Nov 18, 2015 5:37 pm
by insomniak
I've just edited the code and it seems that the customer reports are now more realistic, however for me at least there is still some discrepencies with the totals coming up slightly higher on the reports than they should be.
The actual order totals are coming up as correct for the small sample of customers I tested, so all in all a good fix.
Can anyone please post a fix for the products purchased report?
Re: [OC2.0] Customer Orders Report
Posted: Fri Nov 20, 2015 8:15 am
by aljawaid
is it possible to search for all that code to make the fix in a vqmod?
Re: [OC2.0] Customer Orders Report
Posted: Tue Apr 05, 2016 10:45 pm
by Qphoria
aljawaid wrote:is it possible to search for all that code to make the fix in a vqmod?
Don't do bug fixes with vqmod because you'll forget about it later and it can cause adverse results when the bug is actually fixed in the core. Just replace the code.. its only a single line.
Re: [OC2.0] Customer Orders Report
Posted: Wed Jan 31, 2018 5:03 am
by smit
it seems it is a very old topic, but since i'm behind the same issues with OC 2.1.0.1.
I replaced the code and tested it. but it seems issue still persist. Can anyone help me out please?