Problem:
Currently the prices associated with orders are rounded to three decimal places. The problem is that the payment systems round to two decimal places, which presents some inconsistencies between payment totals and order totals when you perform custom reports.

Suggested solution:
Round the order total, using the same method used in the payment system. This will prevent accounting inconsistencies.

Files: 
CommentFileSizeAuthor
#85 test-order3.jpg27.92 KBbreezeweb
#82 test_order.jpg12.56 KBbreezeweb
#69 decimal-scale-479784.patch1.71 KBpebosi
FAILED: [[SimpleTest]]: [MySQL] 1,416 pass(es), 2 fail(s), and 0 exception(es).
[ View ]
#59 uc_order_rounding.patch5.05 KBergophobe
FAILED: [[SimpleTest]]: [MySQL] Invalid patch format in uc_order_rounding.patch.
[ View ]
#59 uc_order_rounding_uc_2.4_rev_0.1.zip83.18 KBergophobe
#60 order_settings_overview.JPG36.35 KBergophobe
#60 order_settings_collapsed.JPG53.36 KBergophobe
#60 order_settings_expanded.JPG75.74 KBergophobe
#35 479784_5_decimal_places.patch14.93 KBIsland Usurper
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 479784_5_decimal_places.patch.
[ View ]
#30 example_for_hanoii.jpg30.16 KBxibun
#22 479784_order_total_preview_with_db_roudning.patch701 byteshanoii
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 479784_order_total_preview_with_db_roudning.patch.
[ View ]
#20 479784_order_total_preview_without_rounding.patch573 byteshanoii
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 479784_order_total_preview_without_rounding.patch.
[ View ]
#17 ubercart rounding problem.xls565 KBxibun
#4 479784.order_total_rounding.2.x.patch816 bytescha0s
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 479784.order_total_rounding.2.x.patch.
[ View ]
#1 uc_order_total.patch862 bytesmikejoconnor
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch uc_order_total.patch.
[ View ]

Comments

Status:Active» Needs review
StatusFileSize
new862 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch uc_order_total.patch.
[ View ]

Here's my first pass at a fix.

Status:Needs review» Needs work

uc_order_get_total() is used to save the value of $order->total, so it doesn't need to be used again in uc_order_history().

Also, uc_order_get_total() has two return statements, so we probably want to use round() on both of them. Or maybe we should round the product prices and line item values before adding them in. I'm not sure which way actually makes more sense.

Issue tags:+ubercamp sprint

StatusFileSize
new816 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 479784.order_total_rounding.2.x.patch.
[ View ]

To me, it makes sense to put the precision loss off until the last minute. This makes sure we have the most information possible to make the final rounding decision.

Also, I made the logic in uc_order_get_total() better, so there's no return duplication, and I think we should be using the store precision to do the rounding, instead of '2'.

Status:Needs work» Needs review

The patch in #4 did not help with the issue I'm seeing.
My shopping cart shows a total of 39,00€, but the order total preview on the checkout page shows 39,01€. (I'm using uc_vat to calculate the tax)

In some cases it will be necessary to perform calculations on individual line items - like multicurrency, discounts and taxes. It would be nice if rounding could be done per line item, then sum the rounded lines, so order total errors like above would be prevented. As an example - right now I can't show price per line item in foreign currency because it results in small errors like above. Same goes for discounts - you can't show discounted price per item without exposing rounding errors. No good solution at this point, just observations.

For the EU further rounding like you suggest would be a show stopper. The only solution for us are 5 decimals like at least European ERPs do and no rounding except what uc_price correctly does.

So if this is still an issue for you I vote for UC to go back to 2 decimals for all prices and uc_vat alters the tables to what we need.

Please no rounding per line item!

Again, I don't claim to heave a good solution. I wish I had the experience to say "this his how it should be done". I only know that I quickly ran into problems with the current implementation when a client doing international sales wanted to see discounts and non-default currencies per line item. The order total did not add up and I found no clean way to fix it. Anyone else?

Al01,

Oddly enough, this issue was brought up during hte development of the DrupalCon Paris site. Due to how rounding currently operates, there was no way to get the proper price. Furthermore, the majority of payment gateways do not support billing any more precise than two decimal places. Maybe this needs to be integrated with the currency setting, I definitely see a valid reason for supporting 3 decimal places for product prices, but is it really useful for an order total?

Mike, the order total of course should have 2 decimals, this is ok. My response was on #7 where criznach suggested rounding per line item.

3 decimals are enough for the EU to display any price incl. VAT, but it is not enough to avoid rounding issues like has e.g. #6.

I tried to explain what happens here, there are also numerical examples where the issue occurs still with 4 decimals. European ERP systems work internal with 5 decimals and there is no alternative for us.

If you have troubles with 5 decimals I really suggest core to go back to 2 decimals as three is anyway not enough for us. This would avoid your troubles and we alter the DB through uc_vat.

I understand the benefits of extra precision, and forgive me if I'm inexperienced in this area. How do you suggest handling display of intermediate calculations like per item totals, discounts, or taxes in currencies that only use 2 decimal places? Customers expect these totals to add up exactly to the order total. This is something that I expect to work with in the future and am curious how it's typically done.

We need to increasing the number of decimals for price in the database as pointed out by Al01 in #8.

Did my own research on the topic and came to the same conclusion as him: http://drupal.org/node/493322#comment-1948928.
(@Al01: Sorry, it's not that I didn't believe you, I actually overlooked your findings yesterday.)

Just chiming in to say I agree fully with Al01 and xibun on this issue. uc_vat users are reporting rounding errors in line items so things don't add up correctly or there ends up being an 0.01 discrepancy, yet uc_vat does no rounding of its own - the problem seems to lie somewhere in Ubercart core.

However I am not sure what the best approach would be for a fix for this, as criznach says this must happen fairly often in accounting where you end up with sub-0.01 values being summed and rounded, yet I have no idea what the official way of dealing with this is - and it wouldn't surprise me if it varied between countries/line item types/tax rules/etc.

there are two things which should be rather easy to do and where I'm 100% sure they are necessary:
- store price information with 5 decimal places [see post #11 Al01]
- put the precision loss off until the last minute (if not done already) [see post #4 cha0s]

this would make the rounding problem disappear for most tax rates.

exception being countries with 20% VAT rate - there we will need to implement some special rounding rules on top. I have contacted the authorities in Austria a week ago but they haven't responded yet. I suggest to open a new issue for those special cases.

Another issue I have recently been told about is a Swiss client who needs tax line items to be rounded to the nearest 0.05, as there is no 0.01 CHF coin, the smallest denomination is 0.05 CHF. However, I suspect this could be handled as a special case by uc_vat? Perhaps the Austrian rules could be handled in the same way?

StatusFileSize
new565 KB

attached a spreadsheet so you can experience the effect of having more or less decimal places in the database yourself.

note: to keep the file small I limited it to prices from 0.01 to 10.00 - but you can easily extend it to over 650.00 on your local machine.

Issue tags:+Release blocker

StatusFileSize
new573 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 479784_order_total_preview_without_rounding.patch.
[ View ]

I got to this issue because of the same error of #6 (Order total preview displaying a difference of 0.01). I found other places where that kind of difference appear, but in those places I concluded it was a none issue. Adding more precision to the database can be a good thing and it might definitely be a requirement for certain stores, but it makes no sense if rounding occur at the code in a lot of places.

One question might be, is rounding really necessary in calculations or just for displaying issues?

I don't think adding a rounding where there was no rounding, as the patches submitted in this issue queue, is the way to go. Rounding should occur only for display or, if necessary at the last minute like cha0s said on #4. I don't think rounding should not happen on intermediate calculations although there might be some rounding if values are stored on the db.

I found, however, a rounding that was occurring on a place which I think it shouldn't, which was leading to this wrong difference in the Order total preview place. Attached is a fix that removes a rounding and seems to not affect anything else and actually fixes the difference.

I want to show an example of what I think are rounding non-issues on display and also, showing the problem of the order total preview. I have a product which a db value of 10.435

I am using uc_vat module, so with 5x of those items I get the following Order total preview (not patched):

Subtotal: £52.18   (10.435*5 =52.175)
VAT: £7.83 (10.435*5*0.15 = 7.82625)
Order total: £60.01 (52.18+7.83)

and with the patch:

Subtotal: £52.18   (10.435*5 =52.175)
VAT: £7.83 (10.435*5*0.15 = 7.82625)
Order total: £60.00 (52.175+7.82625 = 60.00125)

In parenthesis I included what was being calculated. I think second is good, although there is a difference if you just look each rounded value independently. Order total is not really the sum of the rounded subtotal and rounded VAT, but there's nothing you can do about it if you want to display 2dp along the site.

#4 was also good to know there was an store precision setting which I really wasn't aware.. just got there to look at it and I found you can only select up two decimal points, is there a reason for that?

Also, I just grepped ubercart (rc3) and I found little places where round were used at all (which is good).. maybe look at those might be a good idea as well.

I found them in uc_reports.module and uc_reports.admin.inc (both with a hard coded 2dp), uc_quote.js (those I think are harmless round()), uc_authorizenet.module (which I think you have to round it to 2dp, so all Ok there as well) and uc_store.module (only used in the encryption algorithm so those are OK).

StatusFileSize
new701 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 479784_order_total_preview_with_db_roudning.patch.
[ View ]

I take a few things from #20 back. Although I still think that no rounding is better than some, because eventually things will be rounded when stored on the db, an equivalent rounding should be done in some parts of the code, in particular where my first patch removed the rounding.

The new patch I am uploading removes the rounding when calculating the order total for the preview but eventually, I rounded the $grand_total to 3 dp.

<?php
    $grand_total
= round($grand_total, 3);
?>

Why to 3dp, because that's the db precision as is what the value will be rounded when stored on the db and pass along other parts of the checkout process (payment for instance).

As an example, I have an order total, with no rounding, of 62.9947. With my first patch the order preview would have displayed 62.99, but when stored in the db the value would be rounded to 62.995. Most payment gateways (SagePay for instance) request values with 2dp, so the rounded total order that will be charged would be: round(62.995, 2) = 63.00 (because the value is retrieved from the order db) which would be different from what the order total preview would have given in the first place.

With the new patch, both the order preview total and what's actually being charged on the CC (either 3dp or 2dp) will be the same. I think that if the precision is increased, the round on my new patch would have to be upgraded.

I know these rounding issues are a bit tricky and I am sorry if I add more confusion to it.

Attached is the patch for review.

Worth saying this still fixes the error on #6.

I don't know if this works in all situations. I modified the price formatter so that it shows the unrounded price as well. Using uc_vat with a 6% tax, I got the following line items:

Subtotal: $48.97 (48.972)
U.S.P.S. Parcel Post: $5.19 (5.194)
Subtotal excluding taxes: $51.10 (51.1)
KY Sales Tax: $3.07 (3.066)
Order total: $54.17 (54.166)

(The original product price is $46.20, and the original shipping cost would be $4.90.)
48.97 + 5.19 != 54.17
Rounding these numbers to 3dp before or after adding them doesn't really help anything when you have to display them with 2dp.

Status:Needs review» Needs work

No, it doesn't help for the display, but it does help for the actual calculation. I can't use your example to show how it would fail leaving the round(x,2) because your unrounded values are below the x.xx5 threshold that would make the round(x,2) to round up.

As I stated in #20:

Order total is not really the sum of the rounded subtotal and rounded VAT, but there's nothing you can do about it if you want to display 2dp along the site.

Personally, I still think it's impossible to display middle calculations values in 2dp and expect them to be coherent with the total if the total was calculated using full precision (or db precision for that matters).

The bug I mentioned in #20 and #22 which is related to #6 (I think) is not that much of a display error but a difference between what the store shows to the customer and what it's actually charged on the credit card (or displayed later on).

If you leave the round(x,2) as it is, the order preview total will show the total calculated from the rounded values, while in the rest of the ubercart code, there's no rounding at all, and the value that finally is stored in the db, and eventually used to charge the CC might have a .01 difference from what the site told the customer.

By adding the round(x,3) I am 'emulating' the round that the db will make to the stored value (which is the value that will be eventually used all along in the payment process/invoicing/reporting/etc.

Good example, I see your point.

I'm not sure if I can speak for 100% of Europeans - but I'm sure the following is true for most shops over here:
we have to indicate the prices incl. VAT to the consumer. therefore we set the final price and then figure out what share we are supposed to give to the government. in other words our subtotal including VAT and our shipping costs including VAT will be nice numbers such as 48.95000 and 5.20000 and therefore sum up nicely. that's what the XLS is based on in post #17 - it also can result in odd numbers (for subtotal excl. VAT and VAT itself), but the correct rounding of those is fixed when having 5 digits in the database.

does it fix all cases? NO. unfortunately not. as pointed out in post #15 there are exceptions in some countries. but I think we should go forward and improve UC step by step.

strong vote for 5 digits for all prices in the database.

So, in my example where we see the Subtotal + shipping incl. VAT and Subtotal excl. VAT + VAT add up to different numbers, which number should be the displayed Order Total? It sounds like there will always be cases where they will be different, so we have to decide the direction in which to err. Which price should be sent to the payment processor? $54.16 or $54.17?

Does this mean that we should mark the Subtotal excluding taxes as an estimate? Or maybe it and the tax line? "Accurate to one cent" (or .05 CHF if someone works that out).

(This finite precision is the exact reason I decided not to be a scientist.... *sigh*)

I can only speak from personal experiences with my clients.

I am actually working with one who is very picky in details and he hasn't complain (as of yet) about the difference in the display, he has, however, complain about the order total (which is the figure most of us look at) not being the same as the one appearing on the invoice, or charged to the CC.

I don't see a way to fix the display difference, however, in terms of what should be sent to the payment processor, I think it should be how it is, the stored db value of the unrounded calculated total.

What to display? If the order total is 54.166 I am fine with displaying 54.17, as that's what most payment processors will charge (if requested the values to be rounded to 2dp (2 decimal points). The important thing is that value comes from the proper calculation, which I think is right at the moment.

There seems to be two different things related to Rounding in this issue.

1. One appears to be a difference in the Order total preview that comes from the rounding in uc_payment_get_totals() which my patch tries to overcome. On the second version of my patch I am rounding to the db precision (I am wondering if this can be taken from the schema? probably) so to emulates what will happen to the order total when it gets to the db.

@xibun:
2. Db precision issue, which, in my opinion is not an issue. I saw the spreadsheet xibun uploaded but I think there's a mistake there. The delta column which is looked for a 0.01 difference is not in sync with what ubercart does, because ubercart does not round the excl VAT value and then apply the VAT, but rather apply the VAT to the db stored value and then round it, which is basically to look for the delta on colulmn M (rather than R) and that delta is always 0 with 3 decimals.

I believe that even #2 is originated from #1. Al01 on #11 refers to the problem on #6 which is addressed by my patch. Even more, if you follow Al01 to the uc_vat issue comment (http://drupal.org/node/493322#comment-1753220), it seems that he's experiencing a problem similar to #6 as well. And there, I found one more thing, he mentioned the following :

16.7647 displays as 16.76, while 16.765 as 16.77

He's actually right, although I am not sure if that difference will ever create a problem. I can't actually think of an use case where that problem is solved.

However, there's an universal fact which is that the more precision you have, the more accurate results will be, and I don't think increasing the precision of the db to 5 decimals will cause any problem, and if Al01 is right and it's the standard for EU ERPs, it's something that worth doing.

Hope this helps :)

@hanoii:
maybe we can discuss this in IRC in case the following is not clear. but I believe you're mistaken, not me.

The rounding used in the spreadsheet is to emulate the database precision and not suggesting that Ubercart is doing any rounding at that point.

When a price is saved to the database it needs to be rounded to the precision of the database (in case it's truncated we have another source of imprecision we need to eliminate). So when I take the db stored value and apply VAT as you suggest then I will have an internal error as in column M because I base myself on a value with x digits (currently x=3) - but as we then only display 2 digits to the customer the internal values will be rounded to those 2 digits which ends up giving us a delta as in column R for the displayed order details. so yes, R is the column relevant to the problem I try to solve.

I'm aware that we have several problems at hand, so I'm not suggesting that increasing the db precision to 5 digits will solve all of them. So I'm not going against your suggestions, I just say we also need to increase db precision.

Finally Al01 is pointing at the same problem as me (and you confirm his finding). I just increased the sample space and give an exploration tool so that everyone can convince himself that we need to increase the db precision and also can "play" with it to convince himself what precision will be the right one. Why did I do this? Because I didn't see any progress after the facts were clearly presented for one month. (Al01 on July 20th: "The only solution for us are 5 decimals".)

StatusFileSize
new30.16 KB

@hanoii:
here another price example. let's use this as a discussion basis. please tell me where I'm wrong and why the db precision of 5 digits doesn't help.

note: for this example it doesn't matter if we take the VAT value directly from the db or calculate it fresh based on the price excl VAT from the db.

@xibun: I'll try to be around on IRC later today.

Just to be clear on something, I am not against increasing the db decimals, on the contrary, I think is better, however, what I tried to explain, is that finding an usecase where that really helps wasn't easy.

And good you sent out that shorter sample, as it kind of explain what I tried to say before (even though I certainly can be mistaken :) ).

In your JPG, the rounded excl VAT is the value that has a 0.01 difference, while the rounded incl VAT is 9.99 which eventually, is the value that's most needed, right? It is my understanding that you need to display inc.vat values along the site, even more, inc.vat value was used the generate the stored excl.vat value.

So even if you display somewhere the rounded excl.VAT, it will just be a display adjustments, while calculation is performed using full decimals, the end result will probably be accurate enough. It is true, though, that you may end up loosing precision in the third decimal.

Something I want to quote from your last post:

but as we then only display 2 digits to the customer the internal values will be rounded to those 2 digits which ends up giving us a delta as in column R for the displayed order details. so yes, R is the column relevant to the problem I try to solve.

This is the part in which I thought things differently and why I point you to the column M rather than column R. The internal values are not rounded to 2 digits, except on the order preview total because of a round() which my patch tries to solve, but on the rest of ubercart (and I checked), the only rounding occurs on display, not on calculations, so it's not the rounded excl.VAT value that will be used to generate the inc.vat value for display (as you are doing in column R) but the db value (as you are doing in column M).

Increasing the number of digits stored in the DB won't solve all problems, but it would help with a few of them. I think there is no reason not to simply fix this number to 5.

I think I agree w/ demm, though I'm not sure that would solve all the problems people are pointing out above. For reference, lemme link to this thread from Freshbooks... http://community.freshbooks.com/forums/viewtopic.php?id=5084

One quote tells me we should do this:

"SAP is the (de facto) financial industry standard and they use NUMBER(16,5) for storing currency fields in the database."

I agree and I was about to submit a new patch with an update trying to fix the problem I explained on #20 and #22 and I noticed uc_payment_get_totals() have been just (yesterday according to CVS) completely reworked in a different way, and I don't see a problem in there any more :s.

So I have no more patches :).

Status:Needs work» Needs review
StatusFileSize
new14.93 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 479784_5_decimal_places.patch.
[ View ]

OK, I've got one then. Changes all of the currency field schemas to precision 16, scale 5. I've also checked that the order total is the same in the checkout preview, the checkout review, the order pages, and the invoice.

I took out some of the database updates that were basically the same as this one since it doesn't make sense to change a column twice in a row.

Can we say that this is enough to consider this issue fixed?

any time you save amount a(ex 1.00001) to the db, and charge amount b(ex 1.00), you are going to have inaccuracies. It's the whole "at some point fractions of a cent add up to a cent" issue. There is not really an easy way around it. Setting it to 5 decimals simply hides the root issue a bit further, but at some point, enough .00001 will create an accounting error.

In this case, weighing the changes required to make a system that doesn't suffer from this issue, vs the changes I think we should make under RC, I think that this solution is acceptable for now.

So yes, I think this is enough to consider the issue fixed for uc2.

I'll try to apply this and test it soon, but I have looked over the patch, and it seems logical.

Status:Needs review» Needs work

So, I applied this to the Livetest and the updates worked out alright. I'm a little concerned about the time it could take tables with thousands of rows to ALTER the columns, but I guess I don't know any way around it.

I think I have one problem with this patch: we're now storing numbers up to 5 decimal places, but our decimal places setting in the store currency display settings only lets you specify up to 2. It seems it should go all the way to 5, eh? Also, I thought we were going to split that into two different settings, one for price displays and one for price values on forms.

fwiw, I did confirm that the number of decimal places setting is working properly, it's just not differentiating between entry forms and the product / checkout pages. If I'm not mistaken, part of the goal here is to enable decimal values that when multiplied by a VAT amount will come out as a round number, meaning we might display all prices w/ 2 decimal points but need to enter 3 - 5 decimal points for the calculation to work.

subscribing

@rszrama: Island Usurper found a nice solution for the decimal points in entry forms here: http://drupal.org/node/368884#comment-2045176. In my mind that made an adjustable value for the forms unnecessary - but I might be wrong. It probably would be good to leave a note/description below the "store display" setting (something like "Note: for reason of accurate calculation the database stores all prices with a precision of 5 decimals, all entry forms allow you to enter prices up to that precision.").

Ahh, I see - so I can in fact enter a number with greater precision on the node form and it would be rounded down? In that case, perhaps all that needs to be done is for that function to be fixed to 5 decimal places instead of 6?

When I made that patch in #368884, I made the maximum number of decimal places 5 because I figured the patch here would be made soon. So the admin, who cares about what is stored in the database, will see what he expects, and the customer who is used to seeing only 2 decimal places on prices will see what he expects.

examples: stored in database / displayed in edit forms
17.00000 / 17.00
17,40000 / 17,40
17,43000 / 17,43
17,43500 / 17,435
17,43490 / 17,4349
17,43488 / 17,43488

in case you have set the 5 decimal of db precision as a constant somewhere you could indeed use the same constant here. in reality as shown by above examples the value 6 will not hurt (unless after data entry a 6 decimal price stays in memory and will only "loose" the last digit at the next load from the db - this could be confusing).

Status:Needs work» Needs review

Sounds great, then. I haven't had any other problems running the patch... prices appear fine all over the front end. Anyone else wanna weigh in?

Status:Needs review» Fixed

I think it's good. (I'm not biased, am I?)

Committed.

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Version:6.x-2.x-dev» 6.x-2.4
Status:Closed (fixed)» Active

This is still a problem with Ubercart 2.4 as detailed here: #900640: Pricing doesn't add up correctly - rounding issue? (marked as dupe now)

In essence, the problem that I'm seeing is this - many items are carried out to three decimal places and when you add then round, you get different totals. So it looks like this

Subtotal: 100.00
Shipping: 5.00 (but actually 4.996)
Taxes: 5.00 (but actually 4.996)

Total: 109.99 (because actually 109.992)

So it seems like each line item should get rounded, and then the total should be the sum of the rounded line items. Otherwise there are display problems.

Status:Active» Closed (fixed)

I don't think this issue should be re-opened.

You are right, there is a display issue, but there's no really easy way around that. If you read through this issue, this have been discussed in some way. Basically all the mathematics behind the scene are done using all decimals (which from an accountant point of view, is correct). However, because the checkout screen display partial calculations and it rounds it to two decimal points, then you might end up with a possible 0.01 difference.

Rounding up the partial results is not a proper approach, because it doesn't match the real price. It can be a decision you might want to do in your site, but I don't think it should be a global decision.

Another option might be to change the display to show more decimals.

Perhaps this needs to be handled on a module by module basis, rather than in Ubercart core, because this is not just a display problem, but given that some people need more precision and others need less, it may not be possible to have a core solution.

Much of the discussion above turns around VAT, which apparently needs to be calculated with great precision and so more precision seems to lead to more accuracy. Since I don't know how VAT works, I found that hard to follow.

My scenario has nothing to do with VAT. In my case applying that level of precision leads to an accumulation of error and incorrect order totals. It's not just that they are displayed incorrectly, it's that they literally are incorrect - the customer is not charged the right amount.

Here's the example I gave above with different commentary to hopefully show how and where this is causing a problem. I'm using a hypothetical tax rate of 3.996% just for the example. My actual tax rate is 8.25% and results in similar problems, but not on an even $100 purchase.

Subtotal: 100.00 (this is what I charge the customer for the products)
Shipping: 6.00 (this is what the post office charges me, but Ubercart stores this as 5.996)
Taxes: 4.00 (this is what I pay to the state, but Ubercart stores this as 3.996)

Total: 109.99 (=100+5.996+3.996, but the customer *should* pay 110.00 = 100+4+6).

So now I've sold $100.00 in goods, $4.00 in taxes and $6.00 in shipping. I will bring this into Quickbooks, my accounting software, exactly broken down like that and it will expect me to have $110.00 in revenue.

But the customer is being charged $109.99. I now have a $0.01 discrepancy between the amount of money taken in, and what my accounting software thinks I have taken in.

Rounding line items then adding the rounded numbers to get the total would fix this, but I gather create issues in other scenarios where people have different tax rules, for example.

Perhaps the solution is that individual modules that contribute to price need to stipulate their precision. For example, if the USPS module and the tax module both rounded to two digits before passing those numbers on, then there would be no problem for me at the end.

I can't buy postage in amounts that are precise to three decimal places. I can't pay taxes in amounts that are precise to three decimal places.

So if services/charges that can't in the real world be used in increments other than currency increments, were always rounded to currency increments, then the problem would disappear and my accounting would reconcile.

Why is it that your shipping is stored as 5.996?

But I see your point, in paying tax up to 2dp, however, let me ask you something (and bear with me that I am not an accountant).

Say you have a lot of purchased with TAX up to 3dp, once you sum all that you will end up with some number with 3dp which you eventually will have to round up to 2dp. Now I get that the tax is paid with 2dp, as most things are paid like that, rounding actually occurs when you send the final amount to the order total. But the less rounding you do on middle terms the better accuracy you will have.

Say you sold 10 times that product that costs 100.

You would pay 39.96 in tax, instead of 40 if you have rounded it to 4 on each purchase. I do see that eventually you might end up with one cents rounding up in your tax, but it shouldn't cause you that much of a deal I guess. And why not enter the full decimal amounts on quickbooks?

Not sure if this is just a bit too specific, but worth keeping this discussion on this issue in case anyone else seems to think the same way as you.

EDIT: And although I haven't followed your proposed solution properly, it does seem like quite a bit of rework in ubercart. If you feel like providing a patch there will be certainly some reviews or trials in here.

Ah.. okay... I understand the communication problem here: I am using the words "line item" incorrectly. My apologies. "subtotals" would have been better terminology.

You're right, *line items* should get rounded as late as possible. If I round up for each item and I have 10 items, as you say, I'm again accumulating error with each and every product. So now I see your point better.

By "line item" what I really meant was each subtotal line of the order summary (subtotal, taxes, shipping). Sorry for the confusion.

You ask why I have 5.996 for shipping. I have it because this is the value returned from the USPS shipping module when it taps into the USPS API. I can't imagine why in the world this would be the case since the USPS does not sell anything at this price, but there it is.

To me, this is an error USPS side or there's something fundamental I do not know about postal charges, but in all my life I've never seen a half cent postage increment. But to get the cart contents to add up correctly, the USPS module need to round this before passing it on and never pass on the 3dp value, since the store will never charge such a value.

This is why I say this is something that module developers need to be aware of and probably should not be handled in core, because there are cases where people need precision and cases like mine where increased precision leads to decreased accuracy.

Thanks for your insight though. Now I see why this is not to be fixed globally in uc_orders, but in the modules that pass info to uc_order.

So I think you're right to close this issue, and any problems like the ones I'm having should get opened for the component in question, not here.

There wasn't necessarily a problem in terminology, the accumulation of error goes as well for subtotals. If you subtotal is the example you gave and you have 10 different purchases you are accumulating error there as well.

If you subtotal is the example you gave and you have 10 different purchases you are accumulating error there as well.

No, actually, I'm not and that's my point. The problem is that the *rounded* value of the subtotal is the *true* value. That is the value it costs me and it is the value that the customer is charged. There is no error there. Those numbers are correct because I can only charge costs to two decimal places.

This is analagous to a calculation that can only be done using integers, but I'm doing it using a float.

So when I do subsequent calculations using more than 2 decimal points, that additional precision results in an accumulation of error.

Think of the costs in pennies. I cannot, in the USA, charge less than one penny.

So if the subtotals are

100 pennies
100 pennies
200 pennies

The customer should get billed 400 pennies and my accounting software should record a transaction for 400 pennies. To record anything else is innacurate.

But if I have subtotals totals that display as above, but because I'm carrying over too many decimal places, I end up with the following underlying values.

99.8 pennies
99.8 pennies
199.8 pennies

The problem is, these underlying values are precise but the are not accurate. When I add up these precise, but ultimately inaccurate values, I get 399.4 pennies, which gets rounded to 399 pennies, which is just plain wrong.

In the latter scenario, error is accumulating because of excess precision being carried through too long in the process. In the former scenario (rounding values for subtotals and then adding subtotals) error is NOT accumulating because the subtotals are exact and accurate as stated.

Status:Closed (fixed)» Active

Reopening this due to the above, I think it warrants further discussion. I understand that in your example the 399.4 is wrong and should be charged at 400, so this should be fixed in Ubercart, but we cannot just assume all prices and line items should be treated this way.

In the EU, VAT (tax) must be rounded to the nearest 0.1 or 0.5 pennies per item depending on how you carry out your calculation, then rounded again for the final result: see http://customs.hmrc.gov.uk/channelsPortalWebApp/channelsPortalWebApp.por...

I am still none the wiser as to what the proper fix for all this is.

Thanks longwave! I didn't want to reopen yet again since I'm relatively new to Ubercart, and this has obviously been discussed at length, so thanks for reopening.

Unfortunately, I think this needs to be rolled out on a per-component basis, so each module that returns a price would have to decide what level of precision to apply and would have rounding rules, but maybe even that wouldn't work. For example, I have no idea if there are some jurisdictions where taxes always have to be rounded down.

Is there anyway to retrieve all possible line items in the subtotal section? If so, it would be possible to have an admin screen that let the users set precision for each of those. The problem is that some modules, like shipping, might have dozens of different items.

What about this for an idea - a binary option to create a total based on numbers in the DB or based on the numbers listed as line item totals? Is that even possible? Would that work for people who are using VAT?

Google Checkout has a comprehensive explanation of how they handle rounding: http://code.google.com/apis/checkout/developer/Google_Checkout_HTML_API_...

Should Ubercart adopt the same methods?

That looks excellent!

Google is just using the modes (UP, DOWN, HALF_UP, etc), from the Java spec, but they are duplicated in PHP, so that functionality is there in the PHP round() function. So that part is relatively simple. I suppose the question would be how many modes should get exposed to the end user and how (i.e. have them choose the actual rounding method, or choose a locale and apply the appropriate rounding).

The rules (per item, per line, total) are of course something that would need to be put into the Ubercart logic.

I think to be truly international, it would need one more mode, which is to round to the smallest unit of currency. So in Switzerland, you would need to round to increments of 5 centimes.

Anyway, the rounding details are the easy part - it's the order of operations that's more complicated - i.e. round the totals, items, line items, or subtotals.

For the US scenario, you want to figure tax on the total and then round. I'm not sure what works best elsewhere

Unfortunately rounding modes were only introduced in PHP 5.3.0, so we'd need to reimplement backwards compatible versions of these, even for Drupal 7 :(

Having said that it looks like this is a good reference point, now all we need is someone with enough time to attempt an implementation!

StatusFileSize
new83.18 KB
new5.05 KB
FAILED: [[SimpleTest]]: [MySQL] Invalid patch format in uc_order_rounding.patch.
[ View ]

longwave - thanks so much for your help. The example of how Google is doing it was what I really needed to get my head going. I hope I can finally offer a reasonable, if not yet complete, solution.

I have it working on my site on both the checkout and checkout/review pages and I have tested a couple of transactions. So far so good, but I have only shipping and CA state taxes. This definitely needs testing from someone who is using VAT to see if it works.

A couple of things to note.

The basic principle is as established by cha0s - round as late as possible.

Patch is versus 2.4, not dev... sorry. Not sure if there are any significant differences. I include both a patch, which hopefully works okay, and a zip file of uc_order based on Ubercart 2.4

Changed files
- uc_order.admin.inc
- uc_order.line_item.inc

So far it has two settings as drupal variables.

Rounding MODE - uc_order_rounding_mode
This is the same as the Google and PHP and Java "mode" concept and uses the values of the php round() function as it exists in php >= 5.3.0. This would allow testing for PHP version and using the native function instead of the ugly switch statement I have. That might be more efficient.

Rounding RULE -uc_order_rounding_rule
This takes the values 'total' or 'subtotals'. Google has PER_LINE and PER_ITEM, which would round each line for a product in the cart and each item. This seems excessive and it also requires modify uc_order_get_total() which is left alone for the time being (and thus the uc_order.module file is also left alone), making the patch considerably simpler. I'm not certain it answers everyone's issues though.

It also does not answer the Swiss problem of rounding to the min currency unit. Does Ubercart even have a variable for this? If so, it would be a simple matter to get the variable. If not, it would be fairly trivial to add a text field to this patch and let the user enter the number, having it default to 0.01 I suppose.

Also, one comment about the uc_order_round() function - yes the code is super bloated in the switch statement, but it allows a very simple correspondance between the switch statement and the standard PHP rounding modes.

On the admin end, this adds a fieldset between the Admin Settings and the Customer settings. It could perhaps go in the Admin settings I guess. Anyway, it currently defaults to collapsed since it's sort of an advanced setting.

Also, on the Order Settings overview page, it uses the select field value for the summary and doesn't do a callback to get nice human readable summary. Honestly, I don't know the UC extensions to the FAPI well enough and wasn't sure how to get the summaries in nice language, but it seems like a minor detail at this point.

What this still doesn't solve
If you apply high precision to the line items and only round at the end, your totals may still display wrong on the order review page, meaning they won't necessarily add. I don't think it's actually possible to do that without actually displaying more precision. In this case, it's simple math - you display the line items to the limits of your currency, but you carry more precision through to the end. You would need to show values with the added precision so that when the user adds them, they tally.

If you want to solve that problem, you need to change the way prices display, not the way they are calculated. If you do that, and then select 'total' for your rounding rule, the final math will be right and the display will be correct (I think).

Status:Active» Needs review
StatusFileSize
new75.74 KB
new53.36 KB
new36.35 KB

Some screenshots of the admin area using Acquia Prosper

Subscribe

So... there are only 61 posts in this thread... I don't suppose anyone would actually try this patch and report back. Until it gets tested by the community, it can't be marked RTBC and the maintainers can't commit it to CVS (or, assuming it fails in testing, I can't fix it, since it works fine for me).

subscribe. I think this will *hopefully* solve a problem a user had with Linkpoint #1000364: Keeps getting "checkout error contact administrator"

bkosborne - have you tested the patch? Nothing will happen without testing.

subscribing

Ahh sorry bout that ergo, I'll try and get this patch on one of my sites this week for testing.

Status:Needs review» Needs work

The last submitted patch, uc_order_rounding.patch, failed testing.

Version:6.x-2.4» 6.x-2.x-dev

As you can see from the testbot log, the patch has DOS line endings instead of Unix line endings as required for Drupal code. I'm still working on trying to get the testbot functioning properly in this queue, but that's a start ... I changed the version to 6.x-2.x-dev because once the patch is re-written in the correct format the testbot is going to fail again - the testbot currently has problems dealing with fixed-point releases.

@ergophobe: Can you re-roll the patch against 6.x-2.x-dev with the corrected line endings? Hopefully we can get this resolved and some new tests written to specifically check for rounding issues so it won't crop up again.

StatusFileSize
new1.71 KB
FAILED: [[SimpleTest]]: [MySQL] 1,416 pass(es), 2 fail(s), and 0 exception(es).
[ View ]

Created another patch to raise precision value for mysql decimal fields in uc_prodcuts to 10.

@pebosi: What is the reasoning behind your patch?

I had the problem that paypal was sending one cent more than order total was.

Product price incl. tax was 34.50
Real product price excl. tax should be 28.9915966
Ubercart product price excl. tax was rounded to 28.99160 in db field.

So paypal calculates the tax with 28.99160 and results in 34.500004 rounded to 34.51

Ubercart works with 5 digits everywhere. (The following is without looking at the code...) the interface to PayPal should also work with 5 digits and therefore send the amount of 34.50000. Or even use the precision used for displaying prices to the customer which is preset to 2 digits and therefore send 34.50.

Paypal rounds 34.500004 to 34.51? That's about wrong from every arithmetic point of view.

that was also my first thought :) - but it seems PayPal is rounding up to the complete cent (?), that's why my suggestion.. (from experience with big companies we won't be able to change the interface on PayPal's side)

I guess his problem is because he's sending the excluding vat rate to paypal and have them calculate the tax.

I would, however, consider increasing the precision even more if that accommodate even one more use case, not sure about the impact on performance or large decimal numbers in mysql (or postgre for that matter).

subscribing..... Also hunting a solution to PayPal rounding up to $34.51, I mostly find this with my product kits.
Thanks.

Status:Needs work» Needs review
Issue tags:-Release blocker, -ubercamp sprint

#59: uc_order_rounding.patch queued for re-testing.

Status:Needs review» Needs work
Issue tags:+Release blocker, +ubercamp sprint

The last submitted patch, decimal-scale-479784.patch, failed testing.

subscribing!

Version:6.x-2.x-dev» 7.x-3.x-dev

#1525996: Rounding errors and inconsistent totals has a detailed example for 7.x-3.x, bumping this to 7.x-3.x and closing that as a duplicate.

Priority:Normal» Major
StatusFileSize
new12.56 KB

This issue is still open and hasn't seen any review in some time. I wanted to see if there had been any progress on this or a recommended patch.

Is there not a simple way to round the line items to two digits? I realize that VAT and other scenarios need more precision but perhaps this needs to be a setting.

The attached image is a perfect example: all of the line items end in either a 0 or 5, and yet at the end we've lost a cent along the way. Bookkeepers hate this sort of thing.

Screenshot

I don't even understand how you get the numbers in that screenshot, if you can give details of how the taxes are calculated from the subtotals given perhaps I can explain and we can work towards fixing this.

EDIT: my screenshot was incorrect, see post below for updated post...

StatusFileSize
new27.92 KB

longwave,
Thanks for your reply. Some products are tax exempt, some have both, as per the tax regulations here. Only one of the two taxes applies to shipping. See attached screenshot clarifying below.

When I do the math manually with a calculator, I see that both taxes are being rounded up as they should (5% is $2.145 originally, 7% is $2.4465) , but the final order Total is off a cent because Ubercart must be using more than two decimal places when it adds to add the taxes into the total.

Screenshot

Do you know what the correct tax rounding rules are for your situation? Should the $2.145 become $2.14 or $2.15? Should the $2.4465 become $2.44 or $2.45? I suspect the answer here may be different for different countries, so we might have to provide configuration for this.

I checked, and for both taxes in this case (and I suspect for all Canadian and US taxes) standard rounding rules apply; .000-.004 rounds down to the nearest cent, and .005 and up rounds upwards.

Issue summary:View changes

Hi,

I've been searching and reading for the last couple of days on a way to solve this problem to no avail. We are in Australia and have a small online store. Being in Australia we need to include the GST tax of 10% on top of the products sell price.

For example if we would like to sell a product for $13 including GST - the price will be listed as $11.82. Simple enough as 10% of $11.82 is (with 2 decimal places) $1.18 bumping the subtotal in the catalogue to $13. This is all working fine.

The problem arises when adding that item to the cart multiple times. For example a customer wants to buy 3x worth of that 13 dollar product, the subtotal cost ticks over to $39.01. Now I believe (I could be wrong) this is a result of of the 5 decimal places being stored in the database in uc_order_line_items, because, 10% of 13 is actually 1.182 (1.18200 in the DB) which in turn would make the total cost of the product 13.00200. Now if we multiply that amount by 3x the subtotal of the order is 39.00600 - then with rounding to 2 decimal places applied (which I'm guessing is done by UC) it ticks the price over to 39.01.

Ideally, it would be good if we could round that .01 down to .00. Unfortunately I have no idea how to achieve this. I've read this issue queue multiple times and I think the problems similar but different. Someone suggested using schema to change the DB values to only be 2 places though I think this may have been for a different version of UC and Drupal.

Any help is greatly appreciated even if it's just throwing up a few ideas that I can try.

Thanks

@Jedd Casella

I've encountered the same problem in Australia.

To fix this issue try storing the GST-exclusive values to the full five decimal places.

If you store the product sell price to five decimal places it will look like 11.81812 in the database. Multiplying the value by 3 and adding GST you get 38.99980, which will correctly round to $39.00 (nearest cent).

The only problem I have is when I send the order to PayPal. They use their own rounding and arithmetic, which replicates the issue on their end. I believe if you send the subtotal of the order as a single line item to PayPal it will fix the issue. In any case, it stems from PayPal's inability to accept currency values that exceed a precision of two.

Hopefully this helps you out.

Strainy

Thanks Strainy.

I managed to sort it out without the 5 decimal place excluding the GST value.

You can create a custom module that should do it nicely to avoid losing changes on updates. Essentially this is solved by rounding the $total in function uc_order_get_total in the uc_order.mod i.e. return round($total, 1); - Then doing the same to the functions in uc_cart.mod. They were function uc_cart_block_view, function uc_cart_view_form, function uc_cart_view_table. For the uc_cart_checkout.inc you'll have to hook function theme_uc_cart_review_table($variables) for the subtotal and the total.

The client uses paypal aswell and it's been working a charm for nearly 2 months. Probably not suited to anyone using a tax other than the Australian GST though.

Hope this helps anyone else out there.