0

I have done extensive research into our orders and the many, many different columns that contain numbers between sales_flat_order and sales_flat_order_item. At first it was quite daunting, but here is what I have learned so far:

  1. Almost all number columns have a field name like grand_total and a corresponding field name of base_grand_total. Use the base_ tables if you don't allow for different currency, as those are in the currency unit you are expecting (in most cases $USD). If the default currency is the same as the selected currency, then grand_total and base_grand_total will always be the same. If the user used Canadian Dollars however, they would be different. If you set things up, you'd probably already know this.
  2. Magento seems to store all number values in 4 decimal places. However, it seems to do a poor job of rounding to 2 places values that should be only 2 places. For example, any subtotal value which appears as a line item on the order should be in two decimal places. The price per item could be 4 places, and the quantity could be 4 places, but the multiplier of them both, if presented or summed on an invoice, should be 2 places. So that when you add vertically, there is never a difference between the total and the subtotal items.
  3. The formula for the column summary appears to be (and this is ignoring the invoicing-type columns, or different amounts shipped vs. ordered etc. i.e. assuming everything is being sent and paid in full) as follows:

    o.base_grand_total =
      SUM(i.base_row_total_incl_tax) [for all items]
    + o.base_shipping_incl_tax 
    + o.fee_amount [there is no base_ column for this]
    - o.base_customer_credit_amount 
    + o.base_discount_amount [it's stored as a negative value]
    + IF(t.rate IS NOT NULL AND o.late_fee_amount > 0, ROUND(o.late_fee_amount * (1 + t.rate/100),4), 0.0000)
    

I have found with Magento that things are seldom if ever straightforward, so there is a catch. The tax rate needs to come from joining sales_flat_order_address and then tax_calculation_rate - Magento 1.4 doesn't have a field to record what the tax rate was at the time of the order.

My question (and yes I have a question) is:

  1. why might I still have discrepancies in the above math from time to time - values of between -.94 cents or +.75 cents? Even with just one item in the cart?
  2. Is this a known bug, and is it present in 2.0?

I appreciate any feedback on what I've written as well as answers to these questions, and will edit my work if suggestions are made.

Here is the query that I use:

-- now let's try and incorporate tax rate into the query
-- ugh.. now negative diffs are all over the place, but positive all are under a dollar :/
-- reason is because of tax_calculation rate, let's observe the values that are present
-- OK, removed duplicate rate entries and trying again..
-- SUCCESS! WE ARE DOWN TO diff RANGE OF BETWEEN .99 AND -6.74
-- note that LEFT(a.postcode,5) is necessary because some people actually use their zip + 4
SELECT
o.entity_id,
o.store_id,
-- DATE(o.created_at) AS date, o.state, o.status, o.increment_id,
o.base_grand_total as b_grand_total, SUM(base_row_total_incl_tax) AS b_row_total_incl_tax,
base_shipping_incl_tax as b_Ship_incl_tax, fee_amount, base_customer_credit_amount AS b_Cust_Cr_Amt, o.base_discount_amount AS b_Disc_Amt,
-- formula
o.base_grand_total
- SUM(i.base_row_total_incl_tax)
- o.base_shipping_incl_tax
- o.fee_amount
+ o.base_customer_credit_amount
- o.base_discount_amount
- IF(t.rate IS NOT NULL AND o.late_fee_amount > 0, ROUND(o.late_fee_amount * (1 + t.rate/100),4), 0.0000)
AS diff,

o.late_fee_amount AS late,
IF(t.rate IS NULL, CONCAT('(',LEFT(a.postcode,5),')'), t.rate) as tax_rate,
count(i.item_id) as items
FROM sales_flat_order o
JOIN sales_flat_order_item i ON o.entity_id=i.order_id AND i.base_row_total_incl_tax>0
LEFT JOIN sales_flat_order_address a ON o.entity_id=a.parent_id AND a.address_type='shipping'
LEFT JOIN tax_calculation_rate t ON a.region_id=t.tax_region_id AND LEFT(a.postcode,5)=t.tax_postcode AND a.country_id=t.tax_country_id

WHERE DATE(o.created_at) >='2015-08-01'
GROUP BY o.entity_id
HAVING diff <> 0
ORDER BY store_id, diff DESC
1

Magento 1 is known to have rounding issues. This happens mostly when you're using price rules, based on percentages. That and tax calculations make it possible to get a difference of 1 cent for quote items and/or the entire quote.

Most of the times, you can fix this issue by changing some tax settings in your installation. In other cases, you might need an actual fix in the code to get the result you want/expect. I know this Github extension that solves the issue for Magento 1.7 and Magento 1.8. In Magento 1.9 the issue should be fixed. This might also be the answer to your question, if the issue remains in Magento 2. Since the issue should be fixed in Magento 1.9, it would make sense that the issue is also fixed in Magento 2.

That being said, I'd try it out if I were you. Try to get the same situation in Magento 2.1 as you have now in your tested version and see if the result is as you'd expect it to be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.