OpenCart Community

Posted: **Fri Aug 27, 2010 11:01 pm**

I hope Daniel will decide to add this, or some variation of this, as a built in feature!

This way we can see if our product sales are on the decline, or on the incline, and what products are doing the best this month, etc!

Code is below, attached is a screenshot!

Download - 5.9 KiB

Detailed Reports.zip Downloaded 429 times

Download - 5.9 KiB

stats.jpg (69.56 KiB) Viewed 8216 times

Posted: **Thu Sep 02, 2010 4:18 pm**

Hi 12oclocker,

I think your added criteria is a good idea. However, the underlying Products Purchased report on which it is based is incorrect. I posted so in 'Bugs' but it was ignored.

Order_product.tax contains tax RATE so SUM(op.total + op.tax) should be SUM(op.total + op.total*op.tax/100) so Totals are all wrong (as was pointed out by my client). Also, many shops don't use model and those that do often duplicate it for different products so grouping needs to be by product_id.

I was going to provide an alternative report as a free extension and probably add criteria but, since your addition is free, it would be better incorporated into yours otherwise there will be three different versions of the same trivial report.

The changes I made to the original admin/model/report/purchased.php which now reports correctly for me were:

Change:

Code: Select all

$query = $this->db->query("SELECT op.name, op.model, SUM(op.quantity) AS quantity, SUM(op.total + op.tax) AS total FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_product op ON (op.order_id = o.order_id) WHERE o.order_status_id > '0' GROUP BY model ORDER BY total DESC LIMIT " . (int)$start . "," . (int)$limit);

To:

Code: Select all

$query = $this->db->query("SELECT op.name, op.model, SUM(op.quantity) AS quantity, SUM(op.total+op.total*op.tax/100) AS total FROM `" . DB_PREFIX . "order` o INNER JOIN " . DB_PREFIX . "order_product op ON (op.order_id = o.order_id) WHERE o.order_status_id > 0 GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$start . "," . (int)$limit);

Change:

Code: Select all

$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` GROUP BY model");

To:

Code: Select all

$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` GROUP BY product_id");

(You also have some blocks of code commented out which would be best deleted if they are now redundant because they always make me very nervous

)

Posted: **Thu Sep 02, 2010 9:58 pm**

mystifier wrote: I posted so in 'Bugs' but it was ignored.

hardly, it has mention in the main bug thread
http://forum.opencart.com/viewtopic.php ... 293#p94363

Posted: **Thu Sep 02, 2010 10:48 pm**

Sorry I wasn't there when I looked.

ALLEGED BUG ?! Q, It was reported to me by my client because the results are totally false and caused a minor panic.

Posted: **Thu Sep 02, 2010 11:22 pm**

mystifier wrote:Sorry I wasn't there when I looked.

ALLEGED BUG ?! Q, It was reported to me by my client because the results are totally false and caused a minor panic.

Alleged until it can be confirmed by others. You are the first to report it since inception and that code has never changed so I'd like a second opinion. On my test store I just checked. In 5 orders, I bought 2 macbooks ($2000 each), 4 canon cameras ($100 ea), 3 ipods ($150 ea)

On my Product Purchased Report I see:

Macbook 2 4000
canon 4 400
Ipod 3 450

So far I see no issue.

Then I try your first sql from the bug report and I get:

Code: Select all

name 	model 	  price 	Qty 	Orders 	Total 	order_status_id
Canon EOS 5D 	Product 3 	  100.0000 	4 	2 	400.0000 	1
iPod Classic 	product 20   150.0000 	3 	2 	430.0000 	1

Macbook is missing from your query for some reason. And the math on ipod classic doesn't even add up.

Then I tried your final sql fix and got this:

Code: Select all

iPod Classic 	product 20 	3 	431.500000000000
Canon EOS 5D 	Product 3 	4 	400.000000000000

Again macbook is AWOL and the math is still off

Posted: **Fri Sep 03, 2010 12:50 am**

Bizarre! All I can say is that 'they' complained that the results were nonsense and they now seem happy after much cross-checking.

The standard report calculates SUM(op.total + op.tax) which simply added £17.50 to each order rather than 17.5% of it.

I acknowledge that 'model' is not unique and often duplicated in each category but this field is not a unique key and there are no guide-lines on its intended use (they didn't even particularly want it).

Posted: **Fri Sep 03, 2010 2:33 am**

I do agree with the grouping by model. That is not a forced unique field. op.product_id is probably best, especially if you renamed another product to have the same model. More testing is needed

Posted: **Thu Sep 09, 2010 8:07 pm**

Regardless; there is no Purchased.tpl in the download ?!

Posted: **Thu Sep 09, 2010 9:27 pm**

mystifier wrote:Regardless; there is no Purchased.tpl in the download ?!

No? First, I didn't add your change for the tax. Second, it is only a change in the model file.

Posted: **Thu Sep 09, 2010 10:11 pm**

I was referring to 12oclocker's download in the first post. The TPL file is not included in the zip.

Posted: **Sun Sep 19, 2010 10:58 pm**

your right, I forgot to include it, I fixed the file and reuploaded it, you should be able to use it now without any problems

Posted: **Sun Oct 17, 2010 8:56 pm**

Shouldn't the SQL where clause on the order status be this:-

Code: Select all

WHERE o.order_status_id IN (1,2,3,5)

instead of this:-

Code: Select all

WHERE o.order_status_id > '0'

(admin/model/report/purchased.php)

The latter will included cancelled, refunded etc orders right - or have I misunderstood?

just my €0.02c
Rich

Posted: **Mon Oct 25, 2010 12:16 pm**

probably yes, I just copied that from another part in open cart, so if it's wrong there, it's wrong somewhere else in open cart as well.

Posted: **Fri Jan 28, 2011 6:04 am**

I tried it, great one!

but when installed, they have no translations text,the site is in english and french, either, no text!

and my client asked if we could separate options ? have MORE details ? i'll check for the options...

Posted: **Fri Aug 19, 2011 5:22 am**

HI, I guess this might be the suitable place to post my question?

I am using OC 1.5.0.5, and would like to modify the Report > Product Purchased to show the product option, eg size, colour and etc.

I have purchased a 1 new extensions, and it only works for OC 1.5.1. I was wandering could anyone point me in the right direction to mod this Product Purchased with Option?

Any help would be much appreciated. Thanks

Posted: **Mon Oct 31, 2011 5:44 am**

Not free, but this extension show the product options:
Advanced Products Purchased Report for OCv1.5.x
http://forum.opencart.com/viewtopic.php?f=124&t=44419

OpenCart Community

Detailed Product Purchased Report! (please add to open cart)

Detailed Product Purchased Report! (please add to open cart)

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c

Re: Detailed Product Purchased Report! (please add to open c