Incorrect Totals value in Customer Orders List

Quote

Post by ozbod » Sat Dec 12, 2015 6:33 pm

Hi,

running Open Cart 2.0.1.1 with the default theme only.

Problem is in Reports > Customers > Orders

If you look at the attached image and see the Totals column, these figures are all wrong, you will also see figures in red, these are the correct values.

What I have been able to figure out so far is if you divide the figure in the totals column by the figure in the No.Orders column you arrive at the correct value in all cases apart from when a customer has made more than one order and then I can find no correlation.

In Dashboard > Orders, the Total figure is correct, so the logic is going haywire somewhere.

Has anyone else experienced this at all?

Many thanks

Dave

Attachments

customer-orders-totals.jpg

customer-orders-totals.jpg (39.86 KiB) Viewed 958 times

ozbod
Newbie
Posts
16
Joined
Thu Jun 04, 2015 3:09 pm
Top

Re: Incorrect Totals value in Customer Orders List

Quote

Post by daniGo » Sun Dec 13, 2015 1:05 am

Try change this in admin/model/report/customer.php

Code: Select all

public function getOrders($data = array()) {
	$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') . "'";

	if (!empty($data['filter_order_status_id'])) {
		$sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
	} else {
		$sql .= " AND o.order_status_id > '0'";
	}

	if (!empty($data['filter_date_start'])) {
		$sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
	}

	if (!empty($data['filter_date_end'])) {
		$sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
	}

	$sql .= " GROUP BY o.order_id";

	if (isset($data['start']) || isset($data['limit'])) {
		if ($data['start'] < 0) {
			$data['start'] = 0;
		}

		if ($data['limit'] < 1) {
			$data['limit'] = 20;
		}

		$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
	}

        $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";

	$query = $this->db->query($sql);

	return $query->rows;
}
in

Code: Select all

	
public function getOrders($data = array()) {
	$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') . "'";

	if (!empty($data['filter_order_status_id'])) {
		$sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
	} else {
		$sql .= " AND o.order_status_id > '0'";
	}

	if (!empty($data['filter_date_start'])) {
		$sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
	}

	if (!empty($data['filter_date_end'])) {
		$sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
	}

	$sql .= " GROUP BY o.order_id";

        $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";

	if (isset($data['start']) || isset($data['limit'])) {
		if ($data['start'] < 0) {
			$data['start'] = 0;
		}

		if ($data['limit'] < 1) {
			$data['limit'] = 20;
		}

		$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
	}

	$query = $this->db->query($sql);

	return $query->rows;
}

http://www.gombac.si

daniGo
Active Member
Posts
161
Joined
Wed Mar 20, 2013 4:49 pm
Location - Slovenia
Top

Re: Incorrect Totals value in Customer Orders List

Quote

Post by ozbod » Sun Dec 13, 2015 2:40 am

Hi daniGo,

thanks for you input, I have compared your first chunk of code with what I have in customer.php and there are some differences, please see the screenshot attached:

On line 114 you have in your code:

$sql .= " GROUP BY o.order_id";

in my file (arrowed) it's different

$sql .= " GROUP BY o.customer_id Order By total DESC";

Also you have this code:

$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";

that would be around line 127, I don;t have this code at all.

So because of these differences I have thought it best to report back to you before i make any changes

Best regards

Dave

Attachments

customer-php.jpg

customer-php.jpg (122.76 KiB) Viewed 930 times

ozbod
Newbie
Posts
16
Joined
Thu Jun 04, 2015 3:09 pm
Top

Re: Incorrect Totals value in Customer Orders List

Quote

Post by daniGo » Sun Dec 13, 2015 3:03 am

I'm now download Open Cart 2.0.1.1 from Download page and the code for getOrders is

Code: Select all

	public function getOrders($data = array()) {
		$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') . "'";

		if (!empty($data['filter_order_status_id'])) {
			$sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
		} else {
			$sql .= " AND o.order_status_id > '0'";
		}

		if (!empty($data['filter_date_start'])) {
			$sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}

		$sql .= " GROUP BY o.order_id";

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

        $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";

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalOrders($data = array()) {
		$sql = "SELECT COUNT(DISTINCT o.customer_id) AS total FROM `" . DB_PREFIX . "order` o WHERE o.customer_id > '0'";

		if (!empty($data['filter_order_status_id'])) {
			$sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
		} else {
			$sql .= " AND o.order_status_id > '0'";
		}

		if (!empty($data['filter_date_start'])) {
			$sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}

		$query = $this->db->query($sql);

		return $query->row['total'];
	}
I don't know which version you have installed.

http://www.gombac.si

daniGo
Active Member
Posts
161
Joined
Wed Mar 20, 2013 4:49 pm
Location - Slovenia
Top

Re: Incorrect Totals value in Customer Orders List

Quote

Post by ozbod » Sun Dec 13, 2015 3:17 am

I will check this but am certain its 2.0.1.1. I wonder if this code could have been changed by a different theme I used initially.
ozbod
Newbie
Posts
16
Joined
Thu Jun 04, 2015 3:09 pm
Top

Re: Incorrect Totals value in Customer Orders List

Quote

Post by daniGo » Sun Dec 13, 2015 3:23 am

Try download Open Cart 2.0.1.1 and compare your file with the downloaded.

http://www.gombac.si

daniGo
Active Member
Posts
161
Joined
Wed Mar 20, 2013 4:49 pm
Location - Slovenia
Top

Re: Incorrect Totals value in Customer Orders List

Quote

Post by ozbod » Sun Dec 13, 2015 7:02 am

Hi,
just did as you suggested and downloaded 2.0.1.1 and extracted it on my pc and this is what I have in that file:

public function getOrders($data = array()) {
$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') . "'";

if (!empty($data['filter_order_status_id'])) {
$sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
} else {
$sql .= " AND o.order_status_id > '0'";
}

if (!empty($data['filter_date_start'])) {
$sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
}

if (!empty($data['filter_date_end'])) {
$sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
}

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

if (isset($data['start']) || isset($data['limit'])) {
if ($data['start'] < 0) {
$data['start'] = 0;
}

if ($data['limit'] < 1) {
$data['limit'] = 20;
}

$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
}
ozbod
Newbie
Posts
16
Joined
Thu Jun 04, 2015 3:09 pm
Top

Re: Incorrect Totals value in Customer Orders List

Quote

Post by daniGo » Sun Dec 13, 2015 8:29 am

I'm sorry, you are right. I messed up with OC version. I think you have 2.1.0.1.

Try replace this

Code: Select all

	public function getOrders($data = array()) {
		$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') . "'";

		if (!empty($data['filter_order_status_id'])) {
			$sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
		} else {
			$sql .= " AND o.order_status_id > '0'";
		}

		if (!empty($data['filter_date_start'])) {
			$sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}

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

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}
with this

Code: Select all

	public function getOrders($data = array()) {
		$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') . "'";

		if (!empty($data['filter_order_status_id'])) {
			$sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
		} else {
			$sql .= " AND o.order_status_id > '0'";
		}

		if (!empty($data['filter_date_start'])) {
			$sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}

		$sql .= " GROUP BY o.order_id";

        $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";

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}
For me works fine, and I receive the correct values.

http://www.gombac.si

daniGo
Active Member
Posts
161
Joined
Wed Mar 20, 2013 4:49 pm
Location - Slovenia
Top

Re: Incorrect Totals value in Customer Orders List

Quote

Post by ozbod » Sun Dec 13, 2015 7:15 pm

Hey daniGo

how can I thank you enough for this and sticking with me along the way.

the fix works perfectly and I have fixed three sites, 2 live and one in development.

I did it first on a dev site to test it and when it was fine, backed up the other sites first and then applied the fix.

So I guess this was a bug in version 2.0.1.1 then all along.

Cheers and thanks again

Dave
ozbod
Newbie
Posts
16
Joined
Thu Jun 04, 2015 3:09 pm
Top
Post Reply
Return to “General Support”
Who is online

Users browsing this forum: hoatinh and 36 guests