tax calculation problem with coupons and multiple products

This requires more testing because I'm not entirely sure what is triggering this bug, but it is repeatable and here is the scenario:

Canadian store with GST zone (all of Canada) and PST zone (just BC) and HST zone (Atlantic provinces).

Store is v 1.3.6. Mods are many, so I ask that someone with an fresh site try it to confirm (I will when I get back from vacation)

Issued a coupon code with a product restriction so it applied to only one item.

Customer (from Quebec, so only GST applies) ordered that item as well as another item, and used the coupon code.

Shipping applied correctly.

Taxes calculated very odd (too low) and I can't figure out why.

BEFORE entering the coupon code, I get this:

Item 1: $221.00
Item 2: $35.00
Shipping: $26.91
Tax (6%): $16.97
Total: $299.88

And that is correct! But after entering the coupon code, I get:

Item 1: $221.00
Item 2: $35.00
Shipping: $26.91
Coupon: -$132.60
Tax (6%): $7.75 <-- Incorrect! Should be $9.02
Total: $158.06 <-- should be $159.33

The tax (and therefor the total) are completely wrong!

It only seems to happen when a coupon is applied, which is restricted to one product, and another product is ordered at the same time. However, I have not thoroughly tested other scenarios to see if it is also a problem with a different set of circumstances.

I have other orders with coupon codes restricted to a certain product... but in each of those cases the customer only ordered that item and nothing else. This is the first time I've had this situation, but I can repeat it.

• Steven

Steven,

I too am looking closely at the coupon code as it is very different from what I am currently using and I am also getting strange results. Do you mind testing what I am using for ot_coupon.php here and advising if you get correct totals when using this version?

Thanks,
Jeff

Interesting! Yes, it seems to have solved it... almost.

Now I get this:

Item 1: $221.00
Item 2: $35.00
Shipping: $26.91
Coupon: -$132.60
Tax (6%): $9.01 <-- Incorrect! Should be $9.02
Total: $159.32 <-- should be $159.33

Very close, but it is rounding incorrectly. You can confirm this yourself... (221+35+26.91-132.6) * .06 = 9.0186 which should round up not down.

warning
Your code seems to have introduced another bug... if the customer doesn't finish checking out and manages to find themselves back on the payment page, they have the option to enter REMOVE into the coupon box to remove it. The original code this worked, but with yours it does remove the coupon but presents the user with a blank page.

Of course, I really doubt many people ever use this during a normal transaction. The only reason I know the original file works and yours doesn't is because I'm monkeying around going back and forth... still, it should get fixed.

Hope that helps! Thanks for your efforts on this.

• Steven

Hmm... possibly the rounding thing is correct now that I look at it again. I'm not sure how zen does its calculations... but if it seperately calculates tax on shipping vs. everything else (which it should) then the result is 7.4040 + 1.6146 = 9.0146 which would round down. If it does seperate them, it does it in the background because the taxes are all together when presented to the customer. At least it is on mine.

• Steven

Steven,

Yes it does them separately so again yes I do feel the totals are correct in your above scenario. I will have to look into the REMOVE feature and figure out when that was added and fix it. I have been using "my" version of ot_copon.php for a long time without updating as I have had problems getting correct totals with previous versions 1.3.02-1.3.7 in certain coupon cases so I probably have never updated the file contents to support a REMOVE feature. I will look into it and let you know.

Thanks for the feedback,
Jeff

Steven,

Comparing the two files I can see no difference that would make the REMOVE functionality fail IMHO. Can you please verify this for certain, that it is not something else causing the problem?

Thanks,
Jeff

I can confirm the REMOVE functionality fails Jeff.

It works if I use my ot_coupon version (= a slightly modified 1.3.6 version), but when I use yours I get:

Warning: Cannot modify header information - headers already sent by (output started at /home/domains/beterelektro.nl/public_html/includes/modules/order_total/ot_coupon.php:1) in /home/domains/beterelektro.nl/public_html/includes/functions/functions_general.php on line 44

The tax seems to be calculated correctly though. I only tried absolute discounts on a shop showing prices including tax.

But when I apply a freeshipping coupon it substracts (the coupon also shows a value with tax applied twice to it on checkout) the shipping tax twice form the order total.

Posted to early.

The headers already sent is caused by a couple of spaces at the top of the file:

     <?php
/**
 * @package orderTotal
 * @copyright Copyright 2003-2006 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: ot_coupon.php 4562 2006-09-19 19:11:08Z wilt $
 */
```When I remove those the REMOVE coupon works fine again :-)

Any ideas about the doubled freeshipping tax? (of course I could add a line to prevent the tax being applied twice, but i would rather know the cause of it, if possible).

I believe the double tax on shipping is due to my commenting out line 45 in the file.

// if ($od_amount['type'] == 'S') $order->info['shipping_cost'] = 0;

If you remove the comments from in front of this line I believe the calculation for the free shipping coupon will be correct. Please try this and let me know?

Thanks for pointing out the spaces Paul!

Jeff

PS: I have updated the posted file with this change.

Hi Jeff,

I already tried uncommenting that line, but to my suprise it seemed to make no difference at all (I will give it another shot though, maybe I made an error?).

I added an (ugly!) hack which fixes the freeshipping coupon amount, but the tax is slightly wrong then (while it's correct with other coupon types now, thanks for that! :-) ).

The hack is that I added the second line in the code below:

                    $od_amount['tax'] -= ($order->info['shipping_cost'] * $tax_rate)/100;
                    $od_amount['total'] += $od_amount['tax']; // !hack :: trying to fix double taxing with freeshipper coupons
                    $od_amount[$tax_desc]  += ($order->info['shipping_cost'] * $tax_rate)/100;
```Which is within the "// JTD:04/20/06 - added support to subtract coupon tax from tax total when shipping includes tax" part.

Also, while we are on the subject of the coupon code I have a pet peeve in regard to the implementation of the coupon popup help functionality. In the current implementation there is in place the following code:

      $this->output[] = array('title' => $this->title . ': ' . '<a href="javascript:couponpopupWindow(\'' . zen_href_link(FILENAME_POPUP_COUPON_HELP, 'cID=' . $_SESSION['cc_id']) . '\')">' . $this->coupon_code . '</a> :',
                              'text' => '-' . $currencies->format($od_amount['total']),
                              'value' => $od_amount['total']);

This actually causes the anchor href and javascript html to be written out to the order_totals table record of the database! If you later attempt to view/edit the coupon field data using edit_orders or super_orders the html embedded in this order record really screws up the edit order output. As a result we have edited this code in our production store as described below to eliminate writing html out to the order_total table records, which I personally think is a really bad idea anyway.

      $this->output[] = array('title' => $this->title . ': ' . $this->coupon_code . ':',
                              'text' => '-' . $currencies->format($od_amount['total']),
                              'value' => $od_amount['total']);

I would really like to see the elimination of html being written out to database records in future versions of the coupon code!

Thanks,
Jeff

Hi Paul,

We don't charge tax on shipping here in the states nor do we use the show prices with tax configuration. The code I added to support these is unfortunately pretty much untested as I added it a while back to help a fellow from Germany who was having similar issues as you are now. Let me set up a test store with these configurations and I will see if I can help get the totals correct.

Just to confirm your configuration, you are using "show prices with tax" and "tax on shipping", correct?

Let me know?

Thanks,
Jeff

The code now works in v1.3.7 so that the links are active and functional on the orders and the My Account history ...

This may even have been fixed in v1.3.6 ... but I haven't had enough coffee yet to recall this ... :cool:

Hi Ajeh,

My peeve is not that it doesn't work... but that IMHO it is a bad idea to write html out to the order_total table records, as this makes any external database processor or editor fail since it will likely not expect to find html in ot records.

Thanks,
Jeff

JeffD:

Just to confirm your configuration, you are using "show prices with tax" and "tax on shipping", correct?Exactly.

I already wondered why you added the fixes for including tax shops..

If you are indeed willing to take a closer look at it I will be very gratefull :-)

If you look in the orders_total table you have a TEXT field of exactly what was contained and you have the class field for the Order Total Class example: ot_coupon used ...

How is that any more or less helpful than a shipping module that adds text title:

United States Postal Service (1 x 6.00lbs) (Bound Printed Material):

vs the class field with ot_coupon and where the text title is:

Discount Coupon: <a href="javascript:couponpopupWindow('http://www.my_domain.com/my_catalog/index.php?main_page=popup_coupon_help&cID=1&')">10OFFALL</a> :

What is it that would make having that stripped more helpful to you or how can you utilize it better?

Linda,

The issues are order editors such as edit_orders or super_orders read this ot data back in from the database to allow order editing... for example, adding a coupon to an order which previously did not have a coupon (we have to do this dozens of times a week) or changing the coupon ID on an order that previously had a different coupon. Both EO and SO assume that directly after "Discount Coupon:" in the order totals table will be the coupon ID when in fact now there is a long html sequence before the code. This screws folks who install EO or SO and then attempt to edit existing orders which have a coupon (and have this extra html data), not to mention the fact that it makes querying for report data considerably more difficult since now you have to parse an html sequence in order to locate the coupon ID used on the order. I encourage you to install EO or SO and then attempt to edit a coupon order, or write an ot table query to return coupon codes used in the past month and I think you will quickly see how detrimental this extra data really is.

Lately each new revision of ZC that we upgrade to we have to remember to edit ot_coupon.php to remove this extra data and in cases where we have forgotten we end up having to go back into the database later and hand edit dozens of database records containing the extra data, otherwise coupon orders can't be edited using these important contributions. It really gets to be a pain, trust me...

Thanks for listening!
Jeff

So kind of like when I added all those fields to make things easier for Super Orders I should of looked at a few more eh? :lamo:

Actually we are aware that there are quite a few fields that need cleaning up for the orders tables group so that you have both text and live info so things can be added as well as removed ...

A lot of things get cleaned up with each release but we do not always know everything you feel that you need ... so the better answer on the Discount Coupon is two fields so you have text as well as real coupon coded ... you would know it was a ot_coupon from the class field and then could pop the code from something like a redeem field but that would have to be smart if we do grow to multiple Discount Coupons so you can get them all ... :sleepy:

But yes, I can see what a royal pain that might be for you ...

Hey Jeff,

I got it working for freeshipping now too. :cool:

Or at least working in my case, which is a shop using/showing prices including tax, for products as well as shipping.

I am not sure if it will work correctly for everyone, but it does in my case (also did some tests on coupon value, order total and tax for percentage and fixed discounts).

Since the shipping cost already includes tax (in my case), I fixed your fix to calculate the included tax (before it calculated the tax as if the shipping cost was ex tax):

            // JTD:04/20/06 - added support to subtract coupon tax from tax total when shipping includes tax
             if (DISPLAY_PRICE_WITH_TAX == 'true') {
                $tax_rate = zen_get_tax_rate($this->tax_class, $tax_address['country_id'], $tax_address['zone_id']);
                $tax_desc = zen_get_tax_description($this->tax_class, $tax_address['country_id'], $tax_address['zone_id']);
                if ($tax_rate > 0) {
                  
                    // shipping tax if shipping_cost is excluding tax (possible here?)
                    //$shipping_tax = ($order->info['shipping_cost'] * $tax_rate)/100;
                    
                    // paulm shipping tax if shipping_cost is including tax:
                    $shipping_tax = $order->info['shipping_cost'] - ((100 * $order->info['shipping_cost'])/ (100 + $tax_rate));
                    
                    $od_amount['tax'] -= $shipping_tax;
                    $od_amount[$tax_desc]  += $shipping_tax; 
                             
                    // paulm hack to fix double taxing with freeshipping coupons
                    $od_amount['total'] += $od_amount['tax'];
                  }
                }              
            // JTD:04/20/06 - end of addition for support to subtract coupon tax from tax total when shipping includes tax

Hi Paul,

Well I tried you changes but at least on my store it produces incorrect results for tax, coupon amount and order total when a free shipping coupon is used. I suppose it is possible that I have the store I am testing on set up improperly since I usually don't use the "product price includes tax" or "tax on shipping" options so let me review my configuration and test data with you.

I only have one tax class "Taxable Goods" which is 7.25% "Texas State Sales tax" so I am using this class for both product tax and shipping tax. Here is the Modules->Order Total->Discount Coupon configuration.

Discount Coupon

This module is installed
true

Sort Order
280

Include Shipping
true

Include Tax
true

Re-calculate Tax
Standard

Tax Class
Taxable Goods

I do not have the free shipping module installed as I don't believe it is needed to redeem a free shipping coupon but perhaps I am wrong about this or somehow inclusion of this module changes the calculations?

Here is the pertinent info in Configuration->My Store

Display Prices with Tax true
Basis of Product Tax Shipping
Basis of Shipping Tax Shipping

To the best of my knowledge all other configuration is default.

When I use my version of the code I get:

Sub-Total: $10.71
Discount Coupon: FSHIP : -$6.96
Texas State Sales Tax: $0.72
United States Postal Service (1 x 1.00lbs) (Priority Mail (2 - 3 days)): $6.49
Total:$10.71

Notice that everything is correct except the discount coupon amount which appears as $6.96 rather than $6.49, however the order total, and tax are correct as this is a $9.99 retail product price.

When I attempt to use your code I get:

Sub-Total:$10.71
Discount Coupon: FSHIP : -$6.05
Texas State Sales Tax: $0.32
United States Postal Service (1 x 1.00lbs) (Priority Mail (2 - 3 days)): $6.49
Total:$11.15

The only correct amount in the above calculation is the Sub-Total. All other values are wrong. The coupon amount indicates the non-taxed value of the shipping, yet under shipping charges the $6.49 value is correct i.e., $6.05 + $0.44 tax = $6.49 and the slaes tax amount is off by $0.40 and the total by $0.44?

Is it possible you have some different configuration settings than I am using?

Thanks,
Jeff