Post by beipink » Fri Dec 12, 2014 4:11 pm

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

Active Member

Posts

Joined
Tue Mar 20, 2012 7:43 pm

Post by onlinemarketer » Mon Jan 05, 2015 8:51 am

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.

Active Member

Posts

Joined
Sat May 14, 2011 8:21 pm

Post by gahinton » Fri Jan 16, 2015 6:32 am

I am having this same problem. It is showing things like customers who have only made 1 order look like they have made 4.

Newbie

Posts

Joined
Wed Jan 07, 2015 9:31 am

Post by devlinhall » Wed Feb 18, 2015 11:48 pm

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

Newbie

Posts

Joined
Mon Feb 09, 2015 2:05 am

Post by devlinhall » Sat Feb 21, 2015 11:52 pm

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.

Newbie

Posts

Joined
Mon Feb 09, 2015 2:05 am

Post by devlinhall » Wed Feb 25, 2015 5:55 am

I posted a bug report on opencart github "customer orders report totals wrong #2709" to get an answer. danielkerr closed it.

Newbie

Posts

Joined
Mon Feb 09, 2015 2:05 am

Post by BrettMW » Thu Feb 26, 2015 3:05 am

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.

Website | Follow us on LinkedIn


User avatar
New member

Posts

Joined
Thu Feb 19, 2015 10:03 am


Post by devlinhall » Thu Feb 26, 2015 5:49 am

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.

Newbie

Posts

Joined
Mon Feb 09, 2015 2:05 am

Post by insomniak » Wed Nov 18, 2015 5:12 pm

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.

New member

Posts

Joined
Wed Feb 13, 2013 8:27 am

Post by insomniak » Wed Nov 18, 2015 5:37 pm

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?

New member

Posts

Joined
Wed Feb 13, 2013 8:27 am

Post by aljawaid » Fri Nov 20, 2015 8:15 am

is it possible to search for all that code to make the fix in a vqmod?

Total e-commerce newbie bravely testing OC v2.0.3.1 before rolling it live...getting there slowly, somehow. Maybe not. I dunno.


Active Member

Posts

Joined
Fri Oct 10, 2014 10:33 pm
Location - UK

Post by Qphoria » Tue Apr 05, 2016 10:45 pm

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.

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by smit » Wed Jan 31, 2018 5:03 am

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?

thanks & regards,
Smit
Unless you walk dream does not walk.


Newbie

Posts

Joined
Wed Jan 31, 2018 4:34 am
Who is online

Users browsing this forum: No registered users and 167 guests