1p rounding issue on Ucommerce products/discounts

We've been hitting an issue over the past few years where the product's price is `1p` out in certain circumstances e.g. £2 off 2 £30 items -it should equal £56.00 but often results in £55.99.

The fix is surprisingly (annoyingly) simple -you need to alter the precision of the amount off award columns in Ucommerce, which are set to 2dp:

 

Changing these to `decimal(18,6)` means Ucommerce calculates everything with sufficient precision to result in correct rounding.

You can do that like this:

ALTER TABLE [dbo].[uCommerce_AmountOffUnitAward] ALTER COLUMN AmountOff decimal(18,6);
ALTER TABLE [dbo].[uCommerce_AmountOffOrderLinesAward] ALTER COLUMN AmountOff decimal(18,6);
ALTER TABLE [dbo].[uCommerce_AmountOffOrderTotalAward] ALTER COLUMN AmountOff decimal(18,6);

Sometimes you will need to update the `NumberOfDigitsPrecision` setting in  `www/umbraco/Ucommerce/Configuration/Settings/Settings.config` and change it to `5`.

The Math

VAT in the UK is 20% and Ucommerce requires all prices to be input excluding VAT.

£30.00 exc VAT: £25.00 (this is what’s entered in Ucommrece)
£2.00 exc VAT: £1.66667 (but we can only enter £1.67 in Ucommerce due to the precision)

The desired/expected total when ordering 2 is: £56.00.

When calculating it out at 2dp:

Unit Price: 25.00 – 1.67 = 23.33
Line Total: 23.33 * 2 = 46.66
Order Total: 46.66 * 1.2 = 55.992

That then rounds down to £55.99

If you calculate it to 5dp:
Unit Price: 25.00 – 1.66667 = 23.33333
Line Total: 23.33 * 2 = 46.666666
Order Total: 46.66 * 1.2 = 55.99992

That then rounds to £56.00