Post by stokeyblokey » Thu Nov 21, 2013 7:14 am

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
Last edited by stokeyblokey on Fri Nov 22, 2013 1:20 am, edited 1 time in total.

Stokey


User avatar
Active Member

Posts

Joined
Sat Aug 31, 2013 10:19 pm

Post by stokeyblokey » Thu Nov 21, 2013 9:31 pm

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:

Code: Select all

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

Stokey


User avatar
Active Member

Posts

Joined
Sat Aug 31, 2013 10:19 pm

Post by Qphoria » Thu Nov 21, 2013 10:25 pm

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

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by stokeyblokey » Thu Nov 21, 2013 11:33 pm

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

Stokey


User avatar
Active Member

Posts

Joined
Sat Aug 31, 2013 10:19 pm

Post by stokeyblokey » Fri Nov 22, 2013 12:23 am

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

Stokey


User avatar
Active Member

Posts

Joined
Sat Aug 31, 2013 10:19 pm

Post by stokeyblokey » Fri Nov 22, 2013 1:19 am

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 ;D

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

Stokey


User avatar
Active Member

Posts

Joined
Sat Aug 31, 2013 10:19 pm

Post by Cue4cheap » Fri Nov 22, 2013 6:28 am

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

cue4cheap not cheap quality


Active Member

Posts

Joined
Fri Sep 20, 2013 4:45 am

Post by stokeyblokey » Fri Nov 22, 2013 8:01 am

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 :)

Stokey


User avatar
Active Member

Posts

Joined
Sat Aug 31, 2013 10:19 pm

Post by stokeyblokey » Fri Nov 22, 2013 8:12 am

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

Stokey


User avatar
Active Member

Posts

Joined
Sat Aug 31, 2013 10:19 pm

Post by Cue4cheap » Fri Nov 22, 2013 12:05 pm

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

cue4cheap not cheap quality


Active Member

Posts

Joined
Fri Sep 20, 2013 4:45 am

Post by stokeyblokey » Fri Nov 22, 2013 4:18 pm

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

Stokey


User avatar
Active Member

Posts

Joined
Sat Aug 31, 2013 10:19 pm

Post by stokeyblokey » Sat Nov 23, 2013 4:58 pm

ADD Creative is right, +1

vQmod changed accordingly ;-)

Stokey


User avatar
Active Member

Posts

Joined
Sat Aug 31, 2013 10:19 pm

Post by Funkywatch » Wed Oct 15, 2014 7:52 am

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

Kids accessories gifts shop
http://www.gigglemonsters.co.uk


User avatar
New member

Posts

Joined
Mon Mar 25, 2013 4:05 am
Location - Lincolnshire, UK

Post by cmerry » Wed Oct 15, 2014 3:24 pm

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


User avatar
Active Member

Posts

Joined
Sat Jul 17, 2010 8:42 pm

Post by ADD Creative » Wed Oct 15, 2014 4:53 pm

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?

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by Funkywatch » Wed Oct 15, 2014 6:47 pm

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.
:)

Kids accessories gifts shop
http://www.gigglemonsters.co.uk


User avatar
New member

Posts

Joined
Mon Mar 25, 2013 4:05 am
Location - Lincolnshire, UK

Post by ADD Creative » Wed Oct 15, 2014 8:08 pm

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.

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by Funkywatch » Mon Oct 20, 2014 2:35 am

ok thanks for your help

Kids accessories gifts shop
http://www.gigglemonsters.co.uk


User avatar
New member

Posts

Joined
Mon Mar 25, 2013 4:05 am
Location - Lincolnshire, UK

Post by ADD Creative » Mon Oct 20, 2014 4:57 am

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):

Code: Select all

$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)";
Then change it to:

Code: Select all

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

Code: Select all

if (!is_null($data['filter_order_status_id'])) {
And change both to:

Code: Select all

if (!empty($data['filter_order_status_id'])) {
Try making the changes and see if they help.

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by viralm » Wed Jan 21, 2015 3:29 pm

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.

:)

Newbie

Posts

Joined
Fri Oct 31, 2014 2:07 pm
Who is online

Users browsing this forum: Google [Bot] and 16 guests