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 5547 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

Post by francesco.bragagna » Sat May 05, 2018 7:12 am

Thank you!!


Posts

Joined
Sat May 05, 2018 7:10 am

Post by milo7 » Mon Nov 19, 2018 5:52 pm

For OC 2.3.0.2 - just first step by OCyvon2

Newbie

Posts

Joined
Wed Oct 04, 2017 11:09 pm

Post by masterross » Sat May 25, 2019 6:08 am

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!

Pottery Glaze shop


New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by cedcommerceteam » Mon May 27, 2019 10:24 pm

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 :

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


User avatar
Active Member

Posts

Joined
Wed Aug 09, 2017 9:02 pm

Post by masterross » Mon May 27, 2019 10:36 pm

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.

Pottery Glaze shop


New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by cedcommerceteam » Mon May 27, 2019 10:50 pm

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

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


User avatar
Active Member

Posts

Joined
Wed Aug 09, 2017 9:02 pm

Post by masterross » Tue May 28, 2019 3:18 am

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>

Pottery Glaze shop


New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm
Who is online

Users browsing this forum: No registered users and 28 guests