[SOLVED]Products Purchased Report Totals Incorrect?!

OC1.5.6 Clean install
Lots of vQmods
Some hardcoded changes for styling (possibly, may have redone them all as vQmods cannot remember)
Many CSS changes.


Just had my 4th sale since going live and thought I would check out the sales reports.

I have discovered that the Products Purchased report has the correct items, but incorrect prices...is that just me? It seems to be adding a small amount to the actual price in each case, varying from 14p on £8.3333 up to 56p on a £16.6583 net price.

The only threads I could find were really old 0.97RC and one from 1.5.1.3. and they really didn't seem to help.

Does anyone else have this problem? Does anyone know of a fix for the "total" calculation?

Stokey

No one else having this issue at all?

Damn...

The only vQmod which does anything at all to this file is "Admin Save and Continue" from ClearThinking, and that is not involved as all it does is try to add the save and continue button to the top of the page - have confirmed there is no difference when this mod is uninstalled and cache cleared etc.

So it seems to me that the databade query used in the model file for this report (admin/model/report/product.php) must have an error in the way it calculates the price from the database (or my database is corrupt in some way that only affects the price in this calculation, very unlikely!)...only I have no experience of SQL queries so I cannot investigate it further

This is the code, I think:
I do not follow the SUM, this seems to be the root of the issue. I would expect this to be broken up a little so the calculation is (op.total)+(op.total*op.tax/100) (I only have excel formula experience...) but it all runs together - how can it tell to do the multiplication before the addition?

Any ideas, gurus?

Or am I barking up the wrong tree entirely?

Cheers,

Stokey

I've not noticed but I really don't use the reporting in my own store. I'll have to keep an eye on it in my dev setup

Cheers, would appreciate it

I am going to just give up on this for the moment as I think as it is something I can live without for now...

Stokey

After speaking to the author of an extension which provides better reports, I think I can safely say that this is a bug in OC1.5.6 (and possibly earlier versions too!).

The calculation for the Products Purchased report is taking an incorrect field from the order_product table as shown below:

Example Product A is £15.99 including 20% VAT - net price is £13.3250 and VAT is £2.665

The sum in the report is doing this:

SUM(op.total + op.total * op.tax / 100) AS total where op.total is op.price * op.quantity

This is calling the op.tax field value and it should not be! This field is a value, not a tax rate, so it varies for every item!

So for the above example it calculates thus:

SUM(13.3250 + ((13.3250*2.665)/100)) = £13.68

It SHOULD be calculating like this:

SUM(13.3250 + ((13.3250*20)/100)) = £15.99

Can anyone tell me how to amend the calculation so it uses the Tax Rate instead of the oc.tax field value which is equal to oc.product*tax rate?

Pretty please?

As you can from the examples, I am used to Excel formulas, not PHP!

Stokey

Possible solution:

Open admin\model\report\product.php

Find:

public function getPurchased($data = array()) {
$sql = "SELECT op.name, op.model, SUM(op.quantity) AS quantity, SUM(op.total + op.total * op.tax / 100) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id)";

Change to:

public function getPurchased($data = array()) {
$sql = "SELECT op.name, op.model, SUM(op.quantity) AS quantity, SUM((op.total + 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)";

That fixed the report for me

Thanks to Mario for advice on changing the code!

EDIT: I have made a simple FREE vQmod to perform the fix for anyone who wants it - http://www.opencart.com/index.php?route ... n_id=14724

Your Vqmod file gives me very odd math, by like a factor of 4 for some products but fine for others. I don't know why but it does.

Mike

Any chance you could have sold 4 of those products in the period reported?

It should now sum the ex-vat price+the vat and then times that by the quantity in the order_product table. I have checked my database and that quantity is the one being passed to the payment gateway.

Real life sales have all been multiples of 1 so far though so I can't 100% confirm any errors.

This fix is included in the 1.5.6 branch over at GitHub and has been merged which I discovered after spending ages working out how to submit it myself(!) - if anyone else wants to double check my XML file for errors please feel free

Just had a thought...the table also seems to contain all the abandoned carts and so on...is is possible it is now adding those into the totals?

Will try and check this out tomorrow.

EDIT: It is not abandoned carts, just ran through one of the same items all the way up to PayPal and it has not changed anything in the totals.

How about dummy orders from before you went live when you were in Sandbox mode? Just a thought...

It was for an an item I sold 6 of. It seems odd. I still have your Vqmod in the xml folder (inactive) and if I get a chance (read: the time) I'll turn it back on and see what could possibly be the issue. (If I can figure it out).
Mike

Hmmm...that is odd

You can also check the order_product table in phpMyAdmin to see where it is pulling that data from (if you get time, that's the hard part!).

Cheers,

Stokey

ADD Creative is right, +1

vQmod changed accordingly ;-)

HI
I know this is an old post but thought it relevant. Ive always known that the reports are incorrect but i would really like the products purchased report to work to show me which products are best sellers. Totals are wrong, the above is a bit techy for me can anyone explain how i can amend total products purchased and quantity as these are wrong, can't even work out if sales price is also wrong thanks so much

Hi, please look at ADV Products Purchased Report
http://demo15x.opencartreports.com/admi ... _purchased

Funkywatch wrote:HI
I know this is an old post but thought it relevant. Ive always known that the reports are incorrect but i would really like the products purchased report to work to show me which products are best sellers. Totals are wrong, the above is a bit techy for me can anyone explain how i can amend total products purchased and quantity as these are wrong, can't even work out if sales price is also wrong thanks so much

Which version of OpenCart are you using? Is it the number of products that is wrong or the value of the sales?

HI

Thanks for response, as far i can can see its all totally wrong, I'm using 1.5.2.1 by the way on a custom design so not sure how i would upgrade. If i search for 1 month it brings up only 3 records which is wrong. If i just go into the report without any selections the first line says I've sold 8 of an item that i haven't sold any of at all.

I don't know of any issue with the number of products sold in the report being incorrect. This topic is about the value of the products sold. I'm not sure 1.5.2.1 had any issue with the value of products sold, so the fixes posted won't help you.

One thing to be aware of is the report shows all statuses by default. So it includes cancelled, refunded, etc. orders.

ok thanks for your help

Actually, I was wrong about 1.5.2.1 not having the value of products sold in the reports bug.

To fix you need to find the file admin/model/report/product.php on your server and find the line (should be line 40): Then change it to: Also found an old issue relating to the number of products which may affect 1.5.2.1. https://code.google.com/p/opencart/issues/detail?id=616

So in the same file, find on line 42 and line 78: And change both to: Try making the changes and see if they help.

Hello Here i can change this..

admin->model->report->product
find this :

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

GROUP BY op.model ORDER BY total DESC
Change to :

GROUP BY op.name ORDER BY total DESC.