Page 1 of 1
Customer Orders Report - Data all wrong
Posted: Wed Apr 01, 2015 7:28 am
by oscillate
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.
Re: Customer Orders Report - Data all wrong
Posted: Tue Sep 22, 2015 2:39 pm
by Swordfish2
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?
Re: Customer Orders Report - Data all wrong
Posted: Wed Nov 18, 2015 5:06 pm
by insomniak
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?
Re: Customer Orders Report - Data all wrong
Posted: Fri Sep 08, 2017 9:22 pm
by OCyvon2
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
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";
Re: Customer Orders Report - Data all wrong
Posted: Sat May 05, 2018 7:12 am
by francesco.bragagna
Thank you!!
Re: Customer Orders Report - Data all wrong
Posted: Mon Nov 19, 2018 5:52 pm
by milo7
For OC 2.3.0.2 - just first step by OCyvon2
Re: Customer Orders Report - Data all wrong
Posted: Sat May 25, 2019 6:08 am
by masterross
Hi,
I made changes in v 2.3 but it's not fixed completely.
In Reports/Products/Purchased the Total is til not correct.
For example, I have:
Product | Quantity | Total
Glaze1 | 4 | 2.70€
But the price per product is 1.35€ so the total should be 5.40€
Product | Quantity | Total
Glaze2 | 3 | 1.58€
But the price per product is 0.79€ so the total should be 2.37€
Product | Quantity | Total
Glaze3 | 5 | 0.79€
But the price per product is 0.79€ so the total should be 3.95€
and etc.
Any ideas?
Thanks!
Re: Customer Orders Report - Data all wrong
Posted: Sat May 25, 2019 6:27 am
by masterross
Re: Customer Orders Report - Data all wrong
Posted: Mon May 27, 2019 10:24 pm
by cedcommerceteam
Hi there,
O Cyvon2 you are right, making such changes in query can help them to get correct customer data, I really appreciate your effort, 1 more thing I would like to add instead of directly changing the queries please use vQmod or ocmod to edit your core file and you can check your other modification file for overriding core data for customer data printing.
Thanks & Regards :
Re: Customer Orders Report - Data all wrong
Posted: Mon May 27, 2019 10:36 pm
by masterross
Why do you want ocmod?
This is a bug and should be directly fixed. It's not a mod.
Also, you can make ocmod easily, you have all the data you need for.
Re: Customer Orders Report - Data all wrong
Posted: Mon May 27, 2019 10:50 pm
by cedcommerceteam
Hi @masterross,
It is good practice to not make changes in core files, though it is an issue we have to fix it by OcMod as it is default feature provided by Opencart to modify core feature
Thanks
Re: Customer Orders Report - Data all wrong
Posted: Tue May 28, 2019 3:18 am
by masterross
OK,
Here is the ocmod for OC 2.3.
Place the code in txt file and name it report_fix.ocmod.xml
Code: Select all
<?xml version="1.0" encoding="utf-8"?>
<modification>
<name>Fix Report Bug in OC 2.3</name>
<version>1.0</version>
<author>OCyvon2_and_others</author>
<code>report_fix</code>
<description>Fix Report Bug in OC 2.3</description>
<link>https://www.ceramic-glazes.com/</link>
<file path="admin/model/report/customer.php">
<operation>
<search><![CDATA[$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(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') . "'";]]></search>
<add position="replace"><![CDATA[$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') . "'";
]]></add>
</operation>
</file>
<file path="admin/model/report/product.php">
<operation>
<search><![CDATA[$sql = "SELECT op.name, op.model, SUM(op.quantity) AS quantity, SUM(op.price + (op.tax * op.quantity)) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id)";]]></search>
<add position="replace"><![CDATA[$sql = "SELECT op.name, op.model, SUM(op.quantity) AS quantity, SUM((op.price + op.tax) * op.quantity) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id)";)) {
]]></add>
</operation>
</file>
</modification>