Post by fido-x » Sat Jan 24, 2009 12:19 am

In admin/controller/report_purchased.php (line 28), the database query --

Code: Select all

$results = $database->getRows("select name, model, sum(quantity) as quantity, sum((total + tax) * quantity) as total from order_product group by model order by total desc");
multiplies the quantity twice returning an incorrect value.

Where the query says --

Code: Select all

sum((total + tax) * quantity) as total from order_product
The "total" field in the order_product table already contains the value of unit price multiplied by the quantity purchased, subsequently the end value returned by the query is equal to "((unit price times quantity) plus tax) times quantity)".

Changing the query to --

Code: Select all

$results = $database->getRows("select name, model, sum(quantity) as quantity, sum(total) as total from order_product group by model order by total desc");
returns a correct value if tax is included in the unit price (like the GST in Australia) or if no tax is applied.

Since there are a variety of ways that tax can be applied (eg. fixed amount, percentage, applied to the product, applied to the total order), it makes it difficult to suggest a fix. But it does beg the question, is it necessary to include taxation in this particular report?

Fido-X.

Image
Modules for OpenCart 2.3.0.2
Homepage Module [Free - since OpenCart 0.7.7]
Multistore Extensions
Store Manager Multi-Vendor/Multi-Store management tool

If you're not living on the edge ... you're taking up too much space!


User avatar
Expert Member

Posts

Joined
Sat Jun 28, 2008 1:09 am
Location - Tasmania, Australia

Post by Qphoria » Sat Jan 24, 2009 12:54 am

Ah wow.. great catch! Yea i see i have sold 4 x $10 products and it totals $80!

And perhaps you are right about the tax thing as well, as this is at the product level, it doesn't include coupons, tax, etc. So it should likely only calculate product total (which includes discounts) * qty. The full order total is handled on the sales report.
Last edited by Qphoria on Sat Jan 24, 2009 1:06 am, edited 1 time in total.

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am
Who is online

Users browsing this forum: No registered users and 2 guests