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 (42.31 KiB) Viewed 5563 times
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?
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?
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') . "'";
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') . "'";
Code: Select all
$sql .= " GROUP BY o.customer_id ORDER BY total DESC";
Code: Select all
$sql .= " GROUP BY o.order_id";
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";
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!
Pottery Glaze shop
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 :
Call us at : (+91) -8765210318 , Skype: live:carlmorgan.cedcommerce , Email: support@cedcommerce.com
CedCommerece : Officail Opencart Partners
CedCommerce Official : www.cedcommerce.com
Opencart Services : www.cedcommerce/opencart-services.com
Sell On various Marketplace Integration : Opencart-Extensions
Magenative Mobile App/IOS Development : magenative.com
Webinar : Power up Your holiday season marketing with the Automation tool
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.
Pottery Glaze shop
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
Call us at : (+91) -8765210318 , Skype: live:carlmorgan.cedcommerce , Email: support@cedcommerce.com
CedCommerece : Officail Opencart Partners
CedCommerce Official : www.cedcommerce.com
Opencart Services : www.cedcommerce/opencart-services.com
Sell On various Marketplace Integration : Opencart-Extensions
Magenative Mobile App/IOS Development : magenative.com
Webinar : Power up Your holiday season marketing with the Automation tool
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>
Pottery Glaze shop
Users browsing this forum: No registered users and 2 guests