Hi Peter
I've attached a patch file which should address the issues for your problem
calculation. You will need to download the latest revision of Ofbiz (not
opentaps) and apply the patch. Make sure you do this before running "ant
run-install" as there is a small change to the data model.
Regards
Scott
On 29/04/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Hi,
Well that is indeed what needs to be done because, currently the fact of
the
matter is that the figure is not worked out correctly.
See David's response.
Thanks & Regards,
Peter
-----Original Message-----
From: Scott Gray [mailto:[EMAIL PROTECTED]
Sent: 28 April 2007 05:56
To: [email protected]
Subject: Re: Will Pay US$500 for a Solution to a basic tax calculation
issue
From looking at the code, the tax is always calculated and rounded to 2
decimal places for each order (and invoice) line, so there is no way to
get
a grand total of $91.04 regardless of what settings you place in
arithmetic.properties.
Your only solution would be to refactor quite of lot of code to support
calculating tax on the final taxable subtotal.
Regards
Scott
On 28/04/07, Chris Howe <[EMAIL PROTECTED]> wrote:
>
> For those interested in Peter's challenge, IIRC, his total was ending
> up at 91.03 or 91.05, depending on the arithmetic rules. he is
> expecting 91.04. If someone could verify the following behavior, you
> may claim his prize :) . (I don't have time at the moment to actually
> dig for this occurrence, but if someone wanted to share, I wouldn't
> complain...i wouldn't complain if they didn't want to share either ;-)
> )
>
> Since math is rather straight forward (assuming this is all
> bigdecimal), there are only a few scenarios that can exist to give
> these numbers.
>
> ::91.03::
> Tax line item
> round lineItemTax to two significant digits
> Add each lineItemTax
>
> ::91.05::
> Tax line item
> round lineItemTax to three significant digits
> add lineItemTax to line Item
> round lineItemTotal to two significant digits
> sum lineItemTotals
>
>
> Applying the rules from 91.05 to two significant digits, you will get
> the 91.03 as well.
>
> The expected summation is as follows:
> Tax line item
> round lineItemTax to x significant digits.
> sum all lineItemTax
> add lineItemTaxTotal to lineItemTotal
> round to two significant digits (cents)
> This will produce the expected 91.04
>
>
> --- [EMAIL PROTECTED] wrote:
>
> > Hi,
> >
> >
> > Ofbiz must be capable of calculating tax or vat correctly (the term
> > vat is
> > used in the UK to describe tax on goods, tax at a rate of 17.5%).
> >
> > TO BE CLEAR;
> > THE CALCULATION OF TAX IS THE PROBLEM.
> > THE CALCULATION OF PRODUCTS IS CORRECT.
> >
> > 'arithmetic.properties' is the file I have been working with.
> > Essentially
> > there are three parameters I have looked at that control the rounding
> > and
> > length of decimals used with regards to calculation of tax or vat.
> >
> > salestax.calc.decimals = 3
> > salestax.final.decimals = 2
> > salestax.rounding = ROUND_HALF_UP
> >
> > It is safe to say that I have tried every combination of 'Big
> > Decimal'
> > rounding field types and 'decimals' length (for
> > salestax.calc.decimals and
> > salestax.final.decimals) without any success!
> >
> > I have of course been stopping and restarting the server after each
> > change
> > to reload the file at startup. Even tried some crazy wild figures
> > just to
> > ensure that I was getting a different figure, to ensure the process
> > works.
> >
> > Therefore, there must be other files that also have some bearing on
> > this
> > calculation. Does anyone know what other files maybe relevant?
> >
> >
> > VERY HAPPY TO PAY SOMEONE 'US$500' for a solution at this point. On
> > condition that the solution actually works and produces the figures
> > below,
> > only one payment to the first working model will be paid;
> >
> > -------------------------------------------------
> > price tax product+tax
> > -------------------------------------------------
> > Product1 35.74 6.2545 41.995
> > Product2 35.74 6.2545 41.995
> > -------------------------------------------------
> > Product Total 71.48
> > Shipping 6.00 1.050 7.050
> > Tax (17.5%) 13.56 13.559
> > -------------------------------------------------
> > Grand Total 91.04 91.039
> > -------------------------------------------------
> >
> >
> > Let's see if someone can prove to me that Ofbiz is capable of
> > calculating
> > vat correctly. Had this issue open is various threads for around a
> > month.
> >
> >
> >
> > Thanks & Regards,
> >
> > Peter
> >
> >
> > -----Original Message-----
> > From: Scott Gray [mailto:[EMAIL PROTECTED]
> > Sent: 18 April 2007 10:50
> > To: [email protected]
> > Subject: Re: Doesn't calculate tax correctly...Inaccurate Calculation
> > of
> > Order Total
> >
> > Hi Peter
> >
> > Did you try what I suggested?
> >
> > >Change this line:
> > >salestax.calc.decimals = 10
> > >and see if that gives you the results you are expecting.
> >
> > Regards
> > Scott
> >
> > On 18/04/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > >
> > > Hi,
> > >
> > > Actually 91.04 is what i need as a result, but i can't seem to get
> > Ofbiz
> > > to reproduce this result. Technically right now Ofbiz is not
> > calculating
> > it
> > > correctly. I have tried most permatations of the big decimal class
> > and a
> > > varying the digit length and mostly get 91.03 or 91.05.
> > >
> > > Surely, Ofbiz must be able to calculate the tax (at 17.5%)
> > correctly, it
> > > is such a basic requirement.
> > >
> > > I must be overlooking something. Anyone, can you help.
> > >
> > >
> > > Thanks & Regards,
> > >
> > > Peter.
> > >
> > >
> > > - original message -
> > > Subject: Re: The Return of...Inaccurate Calculation of Order
> > > From: "BJ Freeman" <[EMAIL PROTECTED]>
> > > Date: 18/04/2007 02:19
> > >
> > > if I read this right
> > > Grand Total 91.04 91.039
> > > so if you round to two places on 91.039 you get 91.04
> > > which means they equal.
> > >
> > > [EMAIL PROTECTED] sent the following on 4/17/2007 4:00 PM:
> > > > Hi,
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > There is something very odd about the way Ofbiz calculates tax
> > (in the
> > > order
> > > > manager).
> > > >
> > > >
> > > >
> > > > Using this model from Excel;
> > > >
> > > > US$ price tax p+tax
> > > >
> > > > Product1 35.74 6.2545 41.995
> > > >
> > > > Product2 35.74 6.2545 41.995
> > > >
> > > > Shipping 6.00 1.050 7.050
> > > >
> > > > Total 77.48 (3 digit)
> > > >
> > > > Tax 13.56 13.559
> > > >
> > > > -------------------------------------------------
> > > >
> > > > Grand Total 91.04 91.039
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Method1 gives 91.05
> > > >
> > > > Method2 gives 91.03
> > > >
> > > >
> > > >
> > > > Tried each of the different big-decimal rounding scheme, not give
> > the
> > > value
> > > > I was expecting (91.04). I get either 91.03 or 91.05
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >>From the arithmetic.properties file;
> > > >
> > > >
> > > >
> > > > METHOD1
> > > >
> > > > # Most companies would want their sales tax calculations ALWAYS
> > to round
> > > up
> > > > (ie, 100.081 becomes 100.09)
> > > >
> > > > # This could be ROUND_CEILING or ROUND_UP. (The difference is
> > that
> > > > ROUND_CEILING rounds towards positive infinity,
> > > >
> > > > # ROUND_UP away from zero. So, for 1.13, both ROUND_UP and
> > > ROUND_CEILING
> > > > will round to 1.2, but for -1.13,
> > > >
> > > > # ROUND_UP gives you -1.2 and ROUND_CEILING -1.1.)
> > > >
> > > > salestax.calc.decimals = 2
> > > >
> > > > salestax.final.decimals = 2
> > > >
> > > > salestax.rounding = ROUND_HALF_UP
> >
> === message truncated ===
>
>
Index: applications/accounting/src/org/ofbiz/accounting/invoice/InvoiceServices.java
===================================================================
--- applications/accounting/src/org/ofbiz/accounting/invoice/InvoiceServices.java (revision 532965)
+++ applications/accounting/src/org/ofbiz/accounting/invoice/InvoiceServices.java (working copy)
@@ -104,7 +104,7 @@
private static int decimals = UtilNumber.getBigDecimalScale("invoice.decimals");
private static int rounding = UtilNumber.getBigDecimalRoundingMode("invoice.rounding");
private static int taxDecimals = UtilNumber.getBigDecimalScale("salestax.calc.decimals");
- private static int taxRounding = UtilNumber.getBigDecimalScale("salestax.rounding");
+ private static int taxRounding = UtilNumber.getBigDecimalRoundingMode("salestax.rounding");
public static final int taxCalcScale = UtilNumber.getBigDecimalScale("salestax.calc.decimals");
private static final int INVOICE_ITEM_SEQUENCE_ID_DIGITS = 5; // this is the number of digits used for invoiceItemSeqId: 00001, 00002...
@@ -510,7 +510,12 @@
// set decimals = 100 means we don't round this intermediate value, which is very important
amount = adj.getBigDecimal("amount").divide(originalOrderItem.getBigDecimal("quantity"), 100, rounding);
amount = amount.multiply(billingQuantity);
- amount = amount.setScale(decimals, rounding);
+ // Tax needs to be rounded differently from other order adjustments
+ if (adj.getString("orderAdjustmentTypeId").equals("SALES_TAX")) {
+ amount = amount.setScale(taxDecimals, taxRounding);
+ } else {
+ amount = amount.setScale(decimals, rounding);
+ }
}
else if (adj.get("sourcePercentage") != null) {
// pro-rate the amount
@@ -566,7 +571,7 @@
}
// this adjustment amount
- BigDecimal thisAdjAmount = new BigDecimal(amount.doubleValue()).setScale(decimals, rounding);
+ BigDecimal thisAdjAmount = new BigDecimal(amount.doubleValue());
// adjustments only apply to totals when they are not tax or shipping adjustments
if (!"SALES_TAX".equals(adj.getString("orderAdjustmentTypeId")) &&
Index: applications/accounting/src/org/ofbiz/accounting/invoice/InvoiceWorker.java
===================================================================
--- applications/accounting/src/org/ofbiz/accounting/invoice/InvoiceWorker.java (revision 532965)
+++ applications/accounting/src/org/ofbiz/accounting/invoice/InvoiceWorker.java (working copy)
@@ -45,6 +45,8 @@
private static BigDecimal ZERO = new BigDecimal("0");
private static int decimals = UtilNumber.getBigDecimalScale("invoice.decimals");
private static int rounding = UtilNumber.getBigDecimalRoundingMode("invoice.rounding");
+ private static int taxDecimals = UtilNumber.getBigDecimalScale("salestax.calc.decimals");
+ private static int taxRounding = UtilNumber.getBigDecimalRoundingMode("salestax.rounding");
/**
* Method to return the total amount of an invoice
@@ -134,6 +136,7 @@
public static BigDecimal getInvoiceTotalBd(GenericValue invoice) {
BigDecimal invoiceTotal = ZERO;
+ BigDecimal invoiceTaxTotal = ZERO;
List invoiceItems = null;
try {
invoiceItems = invoice.getRelated("InvoiceItem");
@@ -150,10 +153,14 @@
amount = ZERO;
if (quantity == null)
quantity = new BigDecimal("1");
- invoiceTotal = invoiceTotal.add( amount.multiply(quantity)).setScale(decimals,rounding);
+ if ("ITM_SALES_TAX".equals(invoiceItem.get("invoiceItemTypeId"))) {
+ invoiceTaxTotal = invoiceTaxTotal.add( amount.multiply(quantity)).setScale(taxDecimals, taxRounding);
+ } else {
+ invoiceTotal = invoiceTotal.add( amount.multiply(quantity)).setScale(decimals,rounding);
+ }
}
}
- return invoiceTotal;
+ return invoiceTotal.add(invoiceTaxTotal).setScale(decimals, rounding);
}
/**
Index: applications/accounting/entitydef/entitymodel.xml
===================================================================
--- applications/accounting/entitydef/entitymodel.xml (revision 532965)
+++ applications/accounting/entitydef/entitymodel.xml (working copy)
@@ -1031,7 +1031,7 @@
<field name="uomId" type="id"></field>
<field name="taxableFlag" type="indicator"></field>
<field name="quantity" type="floating-point"></field>
- <field name="amount" type="currency-amount"></field>
+ <field name="amount" type="currency-precise"></field>
<field name="description" type="description"></field>
<field name="taxAuthPartyId" type="id-ne"/>
<field name="taxAuthGeoId" type="id-ne"/>
Index: applications/order/src/org/ofbiz/order/order/OrderReadHelper.java
===================================================================
--- applications/order/src/org/ofbiz/order/order/OrderReadHelper.java (revision 532965)
+++ applications/order/src/org/ofbiz/order/order/OrderReadHelper.java (working copy)
@@ -2612,7 +2612,7 @@
Iterator itemIter = UtilMisc.toIterator(orderItems);
while (itemIter != null && itemIter.hasNext()) {
- result = result.add(getOrderItemTotalBd((GenericValue) itemIter.next(), adjustments)).setScale(scale, rounding);
+ result = result.add(getOrderItemTotalBd((GenericValue) itemIter.next(), adjustments));
}
return result.setScale(scale, rounding);
}
@@ -2624,7 +2624,7 @@
public static BigDecimal getOrderItemTotalBd(GenericValue orderItem, List adjustments) {
// add tax and shipping to subtotal
- return getOrderItemSubTotalBd(orderItem, adjustments).add(getOrderItemAdjustmentsTotalBd(orderItem, adjustments, false, true, true)).setScale(scale, rounding);
+ return getOrderItemSubTotalBd(orderItem, adjustments).add(getOrderItemAdjustmentsTotalBd(orderItem, adjustments, false, true, true));
}
/** @deprecated */
@@ -2675,7 +2675,7 @@
Iterator itemIter = UtilMisc.toIterator(orderItems);
while (itemIter != null && itemIter.hasNext()) {
- result = result.add(getOrderItemAdjustmentsTotalBd((GenericValue) itemIter.next(), adjustments, includeOther, includeTax, includeShipping)).setScale(scale, rounding);
+ result = result.add(getOrderItemAdjustmentsTotalBd((GenericValue) itemIter.next(), adjustments, includeOther, includeTax, includeShipping));
}
return result;
}
@@ -2728,7 +2728,7 @@
while (adjIt.hasNext()) {
GenericValue orderAdjustment = (GenericValue) adjIt.next();
- adjTotal = adjTotal.add(OrderReadHelper.calcItemAdjustmentBd(orderAdjustment, quantity, unitPrice)).setScale(scale, rounding);
+ adjTotal = adjTotal.add(OrderReadHelper.calcItemAdjustmentBd(orderAdjustment, quantity, unitPrice));
}
}
return adjTotal;
@@ -2773,7 +2773,7 @@
adjustment = adjustment.add(setScaleByType("SALES_TAX".equals(itemAdjustment.get("orderAdjustmentTypeId")), itemAdjustment.getBigDecimal("sourcePercentage").multiply(quantity).multiply(unitPrice).multiply(percentage)));
}
if (Debug.verboseOn()) Debug.logVerbose("calcItemAdjustment: " + itemAdjustment + ", quantity=" + quantity + ", unitPrice=" + unitPrice + ", adjustment=" + adjustment, module);
- return adjustment.setScale(scale, rounding);
+ return adjustment;
}
public static BigDecimal calcItemAdjustmentRecurringBd(GenericValue itemAdjustment, BigDecimal quantity, BigDecimal unitPrice) {
Index: applications/order/webapp/ordermgr/entry/order/orderitems.ftl
===================================================================
--- applications/order/webapp/ordermgr/entry/order/orderitems.ftl (revision 532965)
+++ applications/order/webapp/ordermgr/entry/order/orderitems.ftl (working copy)
@@ -115,7 +115,7 @@
<td> </td>
<td> </td>
<td align="right">
- <div class="tabletext" style="font-size: xx-small;"><@ofbizCurrency amount=localOrderReadHelper.getOrderItemAdjustmentTotal(orderItem, orderItemAdjustment) isoCode=currencyUomId/></div>
+ <div class="tabletext" style="font-size: xx-small;"><@ofbizCurrency amount=localOrderReadHelper.getOrderItemAdjustmentTotal(orderItem, orderItemAdjustment) isoCode=currencyUomId maxDecimals=10/></div>
</td>
<td> </td>
<td> </td>
@@ -130,28 +130,28 @@
<tr><td colspan="8"><hr class="sepbar"/></td></tr>
<tr>
<td align="right" colspan="4"><div class="tabletext"><b>${uiLabelMap.OrderSubTotal}</b></div></td>
- <td align="right" nowrap><div class="tabletext"> <#if orderSubTotal?exists><@ofbizCurrency amount=orderSubTotal isoCode=currencyUomId/></#if></div></td>
+ <td align="right" nowrap><div class="tabletext"> <#if orderSubTotal?exists><@ofbizCurrency amount=orderSubTotal isoCode=currencyUomId maxDecimals=10/></#if></div></td>
</tr>
<#list headerAdjustmentsToShow?if_exists as orderHeaderAdjustment>
<tr>
<td align="right" colspan="4"><div class="tabletext"><b>${localOrderReadHelper.getAdjustmentType(orderHeaderAdjustment)}</b></div></td>
- <td align="right" nowrap><div class="tabletext"><@ofbizCurrency amount=localOrderReadHelper.getOrderAdjustmentTotal(orderHeaderAdjustment) isoCode=currencyUomId/></div></td>
+ <td align="right" nowrap><div class="tabletext"><@ofbizCurrency amount=localOrderReadHelper.getOrderAdjustmentTotal(orderHeaderAdjustment) isoCode=currencyUomId maxDecimals=10/></div></td>
</tr>
</#list>
<tr>
<td align="right" colspan="4"><div class="tabletext"><b>${uiLabelMap.FacilityShippingAndHandling}</b></div></td>
- <td align="right" nowrap><div class="tabletext"><#if orderShippingTotal?exists><@ofbizCurrency amount=orderShippingTotal isoCode=currencyUomId/></#if></div></td>
+ <td align="right" nowrap><div class="tabletext"><#if orderShippingTotal?exists><@ofbizCurrency amount=orderShippingTotal isoCode=currencyUomId maxDecimals=10/></#if></div></td>
</tr>
<tr>
<td align="right" colspan="4"><div class="tabletext"><b>${uiLabelMap.OrderSalesTax}</b></div></td>
- <td align="right" nowrap><div class="tabletext"><#if orderTaxTotal?exists><@ofbizCurrency amount=orderTaxTotal isoCode=currencyUomId/></#if></div></td>
+ <td align="right" nowrap><div class="tabletext"><#if orderTaxTotal?exists><@ofbizCurrency amount=orderTaxTotal isoCode=currencyUomId maxDecimals=10/></#if></div></td>
</tr>
<tr><td colspan=2></td><td colspan="8"><hr class="sepbar"/></td></tr>
<tr>
<td align="right" colspan="4"><div class="tabletext"><b>${uiLabelMap.OrderGrandTotal}</b></div></td>
<td align="right" nowrap>
- <div class="tabletext"><#if orderGrandTotal?exists><@ofbizCurrency amount=orderGrandTotal isoCode=currencyUomId/></#if></div>
+ <div class="tabletext"><#if orderGrandTotal?exists><@ofbizCurrency amount=orderGrandTotal isoCode=currencyUomId maxDecimals=10/></#if></div>
</td>
</tr>
</table>
Index: framework/widget/src/org/ofbiz/widget/form/ModelFormField.java
===================================================================
--- framework/widget/src/org/ofbiz/widget/form/ModelFormField.java (revision 532965)
+++ framework/widget/src/org/ofbiz/widget/form/ModelFormField.java (working copy)
@@ -1742,7 +1742,7 @@
}
try {
Double parsedRetVal = (Double) ObjectType.simpleTypeConvert(retVal, "Double", null, locale, false);
- retVal = UtilFormatOut.formatCurrency(parsedRetVal, isoCode, locale);
+ retVal = UtilFormatOut.formatCurrency(parsedRetVal, isoCode, locale, 10);
} catch (GeneralException e) {
String errMsg = "Error formatting currency value [" + retVal + "]: " + e.toString();
Debug.logError(e, errMsg, module);
Index: framework/webapp/src/org/ofbiz/webapp/ftl/OfbizCurrencyTransform.java
===================================================================
--- framework/webapp/src/org/ofbiz/webapp/ftl/OfbizCurrencyTransform.java (revision 532965)
+++ framework/webapp/src/org/ofbiz/webapp/ftl/OfbizCurrencyTransform.java (working copy)
@@ -36,6 +36,7 @@
import org.ofbiz.base.util.Debug;
import org.ofbiz.base.util.UtilFormatOut;
import org.ofbiz.base.util.UtilHttp;
+import org.ofbiz.base.util.UtilValidate;
/**
* OfbizCurrencyTransform - Freemarker Transform for content links
@@ -96,6 +97,12 @@
final Double amount = OfbizCurrencyTransform.getAmount(args, "amount");
final String isoCode = OfbizCurrencyTransform.getArg(args, "isoCode");
final String locale = OfbizCurrencyTransform.getArg(args, "locale");
+ String maxDecimalsStr = OfbizCurrencyTransform.getArg(args, "maxDecimals");
+ int maxDecimalsTemp = -1;
+ if (UtilValidate.isNotEmpty(maxDecimalsStr) && UtilValidate.isInteger(maxDecimalsStr)){
+ maxDecimalsTemp = new Integer(maxDecimalsStr).intValue();
+ }
+ final int maxDecimals = maxDecimalsTemp;
return new Writer(out) {
public void write(char cbuf[], int off, int len) {
@@ -115,9 +122,9 @@
BeanModel req = (BeanModel) env.getVariable("request");
if (req != null) {
HttpServletRequest request = (HttpServletRequest) req.getWrappedObject();
- out.write(UtilFormatOut.formatCurrency(amount, isoCode, UtilHttp.getLocale(request)));
+ out.write(UtilFormatOut.formatCurrency(amount, isoCode, UtilHttp.getLocale(request), maxDecimals));
} else {
- out.write(UtilFormatOut.formatCurrency(amount, isoCode, env.getLocale()));
+ out.write(UtilFormatOut.formatCurrency(amount, isoCode, env.getLocale(), maxDecimals));
}
} else {
out.write(UtilFormatOut.formatCurrency(amount.doubleValue(), isoCode, new Locale(locale)));
