Post by RonLens » Tue Dec 23, 2014 3:59 am

The SQL-query in model/reports/sale

Code: Select all

public function getOrders($data = array()) {
		$sql = "SELECT MIN(o.date_added) AS date_start, MAX(o.date_added) AS date_end, COUNT(*) AS `orders`, (SELECT SUM(op.quantity) FROM `" . DB_PREFIX . "order_product` op WHERE op.order_id = o.order_id GROUP BY op.order_id) AS products, (SELECT SUM(ot.value) FROM `" . DB_PREFIX . "order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'tax' GROUP BY ot.order_id) AS tax, SUM(o.total) AS `total` FROM `" . DB_PREFIX . "order` o";

		if (!empty($data['filter_order_status_id'])) {
			$sql .= " WHERE o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
		} else {
			$sql .= " WHERE 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']) . "'";
		}

		if (!empty($data['filter_group'])) {
			$group = $data['filter_group'];
		} else {
			$group = 'week';
		}

		switch($group) {
			case 'day';
				$sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added), DAY(o.date_added)";
				break;
			default:
			case 'week':
				$sql .= " GROUP BY YEAR(o.date_added), WEEK(o.date_added)";
				break;
			case 'month':
				$sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added)";
				break;
			case 'year':
				$sql .= " GROUP BY YEAR(o.date_added)";
				break;
		}

		$sql .= " ORDER BY o.date_added 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;
	}
gives a wrong result concerning the tax sum and products sum.
The SQL-query from OC 1.5.6 works fine but takes about 100 seconds with nearly 20.000 orders.

Newbie

Posts

Joined
Wed Dec 10, 2014 3:37 am
Location - Germany

Post by mannanan » Sat Jan 10, 2015 5:10 pm

So far, using Opencart 2.0.1.1, I have found the following bugs in Reports:

1. Reports - Sales - Orders:

Gives a bad product quantity when you have multiple orders in the same date. For example I have 2 orders on same date, the column No. of Orders will show 2, but the No. of Products will show only the Quantity for one order. I tried changing the SQL query to no extent - I am not the best with SQL :). The query is located in the file admin/model/report/sale.php, and this part is the function thats handling the calculation, I bolded the part handling the quantity of Products, although I am not sure if thats the only part that is not good, maybe its something else in this function.

Code: Select all

public function getOrders($data = array()) {
		$sql = "SELECT MIN(o.date_added) AS date_start, MAX(o.date_added) AS date_end, COUNT(*) AS `orders`, [b](SELECT SUM(op.quantity) FROM `" . DB_PREFIX . "order_product` op WHERE op.order_id = o.order_id GROUP BY op.order_id) AS products[/b], (SELECT SUM(ot.value) FROM `" . DB_PREFIX . "order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'tax' GROUP BY ot.order_id) AS tax, SUM(o.total) AS `total` FROM `" . DB_PREFIX . "order` o";

		if (!empty($data['filter_order_status_id'])) {
			$sql .= " WHERE o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
		} else {
			$sql .= " WHERE 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']) . "'";
		}

		if (!empty($data['filter_group'])) {
			$group = $data['filter_group'];
		} else {
			$group = 'week';
		}

		switch($group) {
			case 'day';
				$sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added), DAY(o.date_added)";
				break;
			default:
			case 'week':
				$sql .= " GROUP BY YEAR(o.date_added), WEEK(o.date_added)";
				break;
			case 'month':
				$sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added)";
				break;
			case 'year':
				$sql .= " GROUP BY YEAR(o.date_added)";
				break;
		}

		$sql .= " ORDER BY o.date_added 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;
	}
2. Next we have Reports - Product - Viewed and Purchased. Both had errors in either SQL or PHP and gave wrong results. I can't even remember what exactly I did there is help via Google for these I think.

3. Next is Reports - Customers - Orders

This one shows wrong data in columns No. of orders and Total. The figures are completely wrong. The file handling this part is admin/model/report/customer.php. The function handling the calculation is below, I bolded the query which is responsible for the figures. Again, as I previously said, the part of the query I put in bold is probably not the only one causing the issue, it could be the entire query is wrong.

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, [b]COUNT(o.order_id) AS orders[/b], SUM(op.quantity) AS products, [b]SUM(o.total) AS `total`[/b] 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;
	}
I hope that someone else found a solution to these problems because the queries produce wrong data and it is difficult to use the reports for any viable calculation. Please help!

Newbie

Posts

Joined
Sat Jan 10, 2015 4:57 pm

Post by allexdaemon » Tue May 26, 2015 12:54 am

hello. i have the same problem. if al thouse numbers on the reports are true .. i should be rich :)).
HOW CAN WEE FIX THAT ? ..

Newbie

Posts

Joined
Sat Mar 21, 2015 6:30 pm

User avatar
Guru Member

Posts

Joined
Wed Nov 16, 2011 11:53 am
Location - Singapore

Post by allexdaemon » Thu Jun 04, 2015 8:23 pm

The Opencat 2.0.3.0 just come out and the problem is still there.
CUSTOMER ORDER REPORT shows wrong numbers.
I made a test .. i placed 3 diferent orders .. the total value of the 3 order is 1,3 k.
When i open the customer order report. it shows me 7 orders , 7 products , and 4,3 k in money.
So ... this is a big problem i think. Because i have to know how much is every customer spendig so i can give him a voucher or a present . but in this case u can not know .. Am i the only one who has this problem ?

Newbie

Posts

Joined
Sat Mar 21, 2015 6:30 pm

Post by IP_CAM » Thu Jun 04, 2015 8:53 pm

allexdaemon wrote:.. Am i the only one who has this problem ?
just about, many others did not decide, so far, possibly, to participate as Testers
in a Test-Environment, like the one, you seemengly use. At least those, expecting
a fully developed Production-Source, before relying on it, to make a living... ;)

Somehow, I don't even feel sorry, It's just the risk, one takes, to be part of the bandwaggon.
just thinking..., no offense! ::)
Ernie
bigmax.ch/os/

Please don't send me OC Forum Personal Messages, just contact: jti@jacob.ch
---
OC 1.5.6.5 LIGHT Test Site: http://www.bigmax.ch/shop/
OC 1.5.6.5 V-PRO Test Site: http://www.jacob.ch/shop/
My Github OC Site: https://github.com/IP-CAM
2'450+ FREE OC Extensions from the World's largest Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland
Who is online

Users browsing this forum: No registered users and 6 guests