Hi there,
I help run a store shipping physical products from the UK to customers all over the world. We've been operating in some version of OpenCart since around 2012, and have recently migrated to v3.0.3.2. The base currency is GBP, but prices are displayed in the user's local currency and we take payments in the user's local currency - about 25 currencies in total. We also charge VAT and shipping where appropriate.
We've run into an odd problem since the migration, that I don't recall encountering before:
OC seems to be doing all its calculation for multiples of unit prices on line items in the base currency, then converting that to the foreign currency.
I've attached an example.
24.45 * 3 is 73.35, not 73.34
58.68 * 2 is 117.36 not 117.35
And then the subtotal should be 190.69 not 190.70
This only seems to happen on foreign currency transactions, so it looks to me like everything gets individually calculated in the base currency and then converted, rather than converted at the unit price, then calculated in the foreign currency - which seems a really strange way to go about it.
Has anyone figured out a way to address this?
OC does all calculations in the default/shop currency and does the conversion when formatting which causes rounding issues in some cases.
That is a problem but that is also the case in version 2 and probably 1.
That is a problem but that is also the case in version 2 and probably 1.
Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.
It's only really a problem because we sync our sales to Xero, and Xero spits back an error if the calcs are wrong.
We've had to introduce an adjustment line item to deal with it, which we didn't previously.
I suspect our previous developer who created a bespoke Xero integration re-did the calcs in the foreign currency before submitting.
So no plugins that fix this?
We've had to introduce an adjustment line item to deal with it, which we didn't previously.
I suspect our previous developer who created a bespoke Xero integration re-did the calcs in the foreign currency before submitting.
So no plugins that fix this?
Talking about single cents, what a World, we live in ... 
https://www.opencart.com/index.php?rout ... arch=round
https://www.opencart.com/index.php?rout ... arch=round
My Github OC Site: https://github.com/IP-CAM
5'600 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.
well, no.
These mixing of conversions and formatting is everywhere so to fix it system-wide you would have to change all $this->currency->format statements along with doing the convert before any calculation on prices, things like in the cart:
instead of
$total = $this->currency->format($unit_price * $product['quantity'], $this->session->data['currency']);
you would have something like
// convert unit price
$conv_unit_price = $this->currency->convert($unit_price, YOUR_SHOP_CURRENCY, $this->session->data['currency']);
// format converted unit price * quantity without conversion
$total = $this->currency->format($conv_unit_price*$product['quantity'], $this->session->data['currency'],1);
but that is all over the place, even the fetching of prices from the database is done multiple times so it can be done but it's a lot of work and most of it would be in testing everything.
These mixing of conversions and formatting is everywhere so to fix it system-wide you would have to change all $this->currency->format statements along with doing the convert before any calculation on prices, things like in the cart:
instead of
$total = $this->currency->format($unit_price * $product['quantity'], $this->session->data['currency']);
you would have something like
// convert unit price
$conv_unit_price = $this->currency->convert($unit_price, YOUR_SHOP_CURRENCY, $this->session->data['currency']);
// format converted unit price * quantity without conversion
$total = $this->currency->format($conv_unit_price*$product['quantity'], $this->session->data['currency'],1);
but that is all over the place, even the fetching of prices from the database is done multiple times so it can be done but it's a lot of work and most of it would be in testing everything.
Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.
It's not about the single cents, it's about the opportunity cost lost in fixing it.IP_CAM wrote: ↑Tue Feb 25, 2020 9:10 amTalking about single cents, what a World, we live in ...
https://www.opencart.com/index.php?rout ... arch=round
We're working on near-complete automaton of the business owner's processes. His strengths are in marketing and product development. Why should he waste his time sorting out dodgy account reconciliations for rounding that shouldn't occur in the first place?
or you could make sure that the Xero integration adopts/adheres/takes into account to the way OC does it.
Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.
Yeah, we've basically had to do that - we've got a line item just for rounding. It's a messy way to do it, and not great accounting practice.
Xero anticipates all sorts of possible rounding issues, but not one where the individual line items don't add up
Well, in core OC you could make it "easy" by changing the format function to only format without conversion (as it should, it is called format and not possibly_convert_and_format) and change all functions which fetch prices from the database to return those prices already converted, all prices, not just the product price.
That would mean that all calculations would be made in the current currency (the one in the session) and the formatting would do just that, format in the current currency. (you would have to check all queries though as there are controllers which run their own queries bypassing the model functions)
But, that introduces problems with extensions, any extension you have installed or will install in the future will expect the OC functions to return the prices in the default currency and do their own conversions which would make for some bad results.
So it is more a matter of convention and OC chose the wrong one.
That would mean that all calculations would be made in the current currency (the one in the session) and the formatting would do just that, format in the current currency. (you would have to check all queries though as there are controllers which run their own queries bypassing the model functions)
But, that introduces problems with extensions, any extension you have installed or will install in the future will expect the OC functions to return the prices in the default currency and do their own conversions which would make for some bad results.
So it is more a matter of convention and OC chose the wrong one.
Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.
Try these from the Russian Opencart forum:
round or ceil in calculations
round or ceil in calculations
Attachments
Suppliers Module - XML, CSV, XLS Product Feed Import and Update
Rich Snippets | Facebook Open Graph Meta Tags | WebP Images
not sure what those are for, I think this is exchange rate related, not price rounding, I experimented by rounding the exchange rate to (decimal places of the currency + 1) before doing the conversion.
Have not been able to check if that covers all instances though.
Have not been able to check if that covers all instances though.
Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces
“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.
Making the calculation after currency conversion was something I experimented on a while back.
My examples and comments on some of the issue I found are below, in case anyone is interested.
https://github.com/copona/copona/commit ... t-25190143
https://github.com/copona/copona/commit ... t-27435401
My examples and comments on some of the issue I found are below, in case anyone is interested.
https://github.com/copona/copona/commit ... t-25190143
https://github.com/copona/copona/commit ... t-27435401
Two different functions would be clearer. As it is, possibly_convert_and_possibly_format seems a more appropriate name.letxobnav wrote: ↑Tue Feb 25, 2020 11:15 amWell, in core OC you could make it "easy" by changing the format function to only format without conversion (as it should, it is called format and not possibly_convert_and_format) and change all functions which fetch prices from the database to return those prices already converted, all prices, not just the product price.
Who is online
Users browsing this forum: No registered users and 4 guests
