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
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
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.
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.
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
e.g. proper order total = 281.82 , number of different products = 29. Customer Orders report = 8172.78 = 281.82*29
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.
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.
I posted a bug report on opencart github "customer orders report totals wrong #2709" to get an answer. danielkerr closed it.
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
to
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.
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') . "'";
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') . "'";
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.
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?
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?
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?
I replaced the code and tested it. but it seems issue still persist. Can anyone help me out please?
thanks & regards,
Smit
Unless you walk dream does not walk.
Who is online
Users browsing this forum: No registered users and 204 guests