Post by oscillate » Wed Apr 01, 2015 7:28 am

Reports > Customers > Orders
Version: 2.0.1.1

Entering a date range, or not, the results are still wrong for every line item.

As per the attachment, you can see that apparently Dorothy has made 50 orders, 142 products worth $144k.
In reality, she has only placed 3 orders totalling $2667.

Attachments

screenshot1.jpg

screenshot1.jpg (42.31 KiB) Viewed 712 times


Newbie

Posts

Joined
Wed Apr 01, 2015 7:09 am

Post by Swordfish2 » Tue Sep 22, 2015 2:39 pm

I am having the same problem.
How did this data get messed up?
I have installed one plugin which shows customer order as a tab in the customer data section and it seems to be showing fine.

This wrong data is because of the failed, incomplete orders from the customers?

Newbie

Posts

Joined
Mon Sep 09, 2013 1:39 pm

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

This is also happening to me on 2.0.3.1

Also the data in reports -> products -> purchased is also wrong with the figures much higher than they should be, plus the actual calculations it performs are wrong.

Example it says I have sold 226 of one product and the total for this is £3,210.64, however the item price is £5.99 so;
£5.99 x 226 = £1353.74

I would like to know if this is a bug or if anyone knows how to correct it?

New member

Posts

Joined
Wed Feb 13, 2013 8:27 am

Post by OCyvon2 » Fri Sep 08, 2017 9:22 pm

For version 2.0.3.1
go to admin/model/report/customer.php
change line 98

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') . "'";
into

Code: Select all

$sql = "SELECT c.customer_id, CONCAT(c.firstname, ' ', c.lastname) AS customer, c.email, cgd.name AS customer_group, c.status, o.order_id, SUM(op.quantity) as products, SUM(DISTINCT 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') . "'";
and change line 114

Code: Select all

$sql .= " GROUP BY o.customer_id ORDER BY total DESC";
into

Code: Select all

$sql .= " GROUP BY o.order_id";
add below line 126

Code: Select all

$sql = "SELECT t.customer_id, t.customer, t.email, t.customer_group, t.status, COUNT(t.order_id) AS orders, SUM(t.products) AS products, SUM(t.total) AS total FROM (" . $sql . ") AS t GROUP BY t.customer_id ORDER BY total DESC";

OpenCartstore
Gebruikersgids (admin handleiding)


User avatar
Active Member

Posts

Joined
Sun Jan 31, 2010 8:00 pm
Location - Zaandam, The Netherlands
Who is online

Users browsing this forum: No registered users and 5 guests