Post by Sidecutter » Wed Aug 17, 2011 8:24 am

Retitling and rewriting this since I think I can better explain now what it is I am looking for.

Using 1.4.9.6

Shipping costs are not taxable, and for the purpose of reporting my sales for tax filing, they should not be included. Also, since they are not actually part of the sale, having them as part of the reported total sales makes no sense. However, Total Sales in the Dashboard and sales reports pages in admin includes the shipping.

How can I alter it to calculate total sales using the subtotal for the order, which is just the item costs, instead of the total, which would include shipping, taxes and fees?

Active Member

Posts

Joined
Tue Jan 18, 2011 6:58 am

Post by Qphoria » Thu Aug 18, 2011 3:26 am

In 1.4.x This is a lot harder than it seems.
The order record only stores the "final" total
The other totals are stored as text for display on the order.. but not for programmatic consumption

But in 1.5.x there a new "code" tag for the order total class code. So this can be done a bit easier.

1. EDIT: admin/model/sale/order.php

2. FIND:

Code: Select all

$query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0'");
3. REPLACE WITH:

Code: Select all

$query = $this->db->query("SELECT SUM(ot.value) AS total FROM `" . DB_PREFIX . "order_total` ot LEFT JOIN `" . DB_PREFIX . "order` o ON (ot.order_id = o.order_id) WHERE ot.code IN ('sub_total') AND o.order_status_id > '0'");
Tho keep in mind that will also exclude discounts and coupons or other order totals that are possibly taxable.
You could use another alternative to the above area where I have:
IN ('sub_total')
change to
NOT IN ('shipping', 'total')
if you only want to exclude shipping and total, since total includes shipping and sub_total.
or
NOT IN ('shipping','tax', 'total')
to get everything but exclude those 3 total types

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by Sidecutter » Thu Aug 18, 2011 7:27 am

*EDIT* Just to make sure this is clear - as my title says, it's the Total Sales being reported that I need to change, not the order totals. Where I need the different totals is in the Admin Dashboard (where it shows Total Sales and Total Sales This Year), and in the Reports > Sales page.

And yes, I only want it to exclude shipping, since tax would be paid on the actual total paid by the customer for the items, which would be affected by coupons or discounts.

Active Member

Posts

Joined
Tue Jan 18, 2011 6:58 am

Post by Qphoria » Thu Aug 18, 2011 12:03 pm

Yes.. The subtotal isn't stored in the "order" table, but it is stored in the "order_total" table linked to the "order" table. So this cross references the table to get just the subtotal value. Or if you want everything but shipping to be included you could use the alternatives

So an order:
Item: 10.00

Subtotal: 10.00
Shipping: 2.00
Tax: 4.00
Total: 16.00 <--- saved to the order table and used for Total orders.

I do the lookup on the initial subtotal value and use that for the calculations.
But that would also ignore Tax, which you may want, so I added the variations so you can include/exclude as much as you want.

Unfortunately, 1.4.x doesn't have this "code" column in the order_total table.. so there is no way to classify the type of total it was.. and this code won't work with 1.4.x

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by Sidecutter » Fri Aug 19, 2011 6:01 am

Sorry Q, but your last response has me confused.

Let me see if I have this:
I can't just modify Total Sales or reports, even though model/report/sale.php has code similar to what you showed I should change in model/sale/order.php? I have to modify how the orders display their totals, and then that will affect the Total Sales on the dashboard, and in the reports?

And the code you gave does not work in 1.4, so it's no good to me even though I said in the initial post I was using 1.4?

This seems like a problem if those are both correct. How else can I extract total sales, so that I can get an accurate number? It isn't feasible to sit there and go through every single order record and subtract shipping charges from the totals one by one, that would take hours, maybe days. Seems like a serious error in the design.

Active Member

Posts

Joined
Tue Jan 18, 2011 6:58 am

Post by Qphoria » Fri Aug 19, 2011 9:23 am

Don't over complicate it.
In 1.4.x, OpenCart saved all the itemized totals blindly with no ability to trace which total is which.
That's just how it is and that is why you can't do what you want in 1.4.x... at least not without putting that order total "class code" system in place first.

That was fixed in 1.5.x and easily done in 1.5.x using my example above.

You have 2 options if you really want this..
1. Stick with 1.4.x and hire a developer to backport the full featureset.
2. Upgrade to 1.5.x at some point and make the small code change above.

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by Sidecutter » Fri Aug 19, 2011 10:49 am

This is why I was confused, because the code you were telling me to replace is present in 1.4.x, and telling me how seemed strange if it didn't work for the version I said I was using. And a 1.5 upgrade is not happening anytime soon, not with the money and time spent on getting everything right for 1.4.9. I can't afford to spend hundreds more, and weeks or months more time, to have developers upgrade everything for 1.5.

I still think having this be the way it handles Total Sales is bad, if no alternative total is provided by default to show just the pure total of product sales. I'm not aware of anyplace where the number as given now would be the correct one to give for tax purposes. The proper number seems like key information to have the cart hand over easily for store owners. You may want to address it in future versions of 1.4.9 (if any) and 1.5.

Regardless, I found a reports extension that shows totals both with tax and shipping included and without them (the tiny images, even when enlarged, make it a pain to see what an extension does, BTW), and it has been around for a while, so it must be doable. Apparantly I'll need to shell out for that if I want to be able to file my taxes.

Active Member

Posts

Joined
Tue Jan 18, 2011 6:58 am

Post by Qphoria » Fri Aug 19, 2011 11:51 am

Sidecutter wrote: I still think having this be the way it handles Total Sales is bad, if no alternative total is provided by default to show just the pure total of product sales.
Did you like the way Windows 3.1 handled things? No.. that is why they made Windows 95. You are arguing the point that we already addressed. 1.4.x had a problem... 1.5.x fixed it.

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by Sidecutter » Fri Sep 16, 2011 11:11 am

Qphoria wrote:
Sidecutter wrote: I still think having this be the way it handles Total Sales is bad, if no alternative total is provided by default to show just the pure total of product sales.
Did you like the way Windows 3.1 handled things? No.. that is why they made Windows 95. You are arguing the point that we already addressed. 1.4.x had a problem... 1.5.x fixed it.
1.5x may have fixed it for 1.5.x, but upgrading to 1.5.x is simply not realistic for everyone. If you sunk hundreds of dollars and untold hours into developing everything for 1.4.9.x starting from when 1.5.x was still not even an alpha, custom mods, and the extensions and mods that are publicly available that suited your needs, it may not be feasible to upgrade to 1.5.x. Would I like to? Yes. Is it feasible? Not even. Doing all of that over again simply isn't possible for many small businesses, financially. Especially bad when one person has to do everything from top to bottom and still develop or oversee any work to be done on upgrading.

But that's why I'm arguing the point. Because I don't believe that it really was addressed. This is the kind of major flaw that merits some backtracking to fix at least one version back, in my opinion.

Active Member

Posts

Joined
Tue Jan 18, 2011 6:58 am

Post by kohtut » Mon Oct 31, 2011 5:20 pm

Hello Qphoria,

I would like to change each order total to subtotal.
May I know, where should I need to edit?

Thank you

Newbie

Posts

Joined
Mon Sep 05, 2011 12:11 am

Post by dwcar49us » Sun May 06, 2012 1:16 pm

Sidecutter wrote:
Regardless, I found a reports extension that shows totals both with tax and shipping included and without them (the tiny images, even when enlarged, make it a pain to see what an extension does, BTW), and it has been around for a while, so it must be doable. Apparantly I'll need to shell out for that if I want to be able to file my taxes.
What was the report mod you found, I have a similar problem.

Thanks

Newbie

Posts

Joined
Mon Apr 23, 2012 3:55 am
Who is online

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