I have done extensive research into our orders and the many, many different columns that contain numbers between
sales_flat_order_item. At first it was quite daunting, but here is what I have learned so far:
- Almost all number columns have a field name like
grand_totaland 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
base_grand_totalwill 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.
- 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.
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:
- 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?
- 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