Recalculate shipment taxes in Magento

shipments-vat-fix.sql

-- Configuration

SET @tax_percent = 21;
SET @tax_code = 'CZ DPH 21%';
SET @tax_title = 'CZ DPH 21%';

-- Functions

SET @tax_coef = ROUND(@tax_percent / (100 + @tax_percent), 4);

DROP FUNCTION IF EXISTS tax_amount;
DROP FUNCTION IF EXISTS tax_base;

CREATE FUNCTION tax_amount (base DECIMAL(12,4))
RETURNS DECIMAL(12,4) DETERMINISTIC
RETURN ROUND(base * @tax_coef, 2);

CREATE FUNCTION tax_base (base DECIMAL(12,4))
RETURNS DECIMAL(12,4) DETERMINISTIC
RETURN ROUND(base - tax_amount(base), 2);

-- Data changes

UPDATE sales_flat_creditmemo
SET
    shipping_tax_amount = tax_amount(shipping_incl_tax),
    tax_amount = tax_amount + shipping_tax_amount,
    shipping_amount = tax_base(shipping_incl_tax),
    base_shipping_tax_amount = tax_amount(base_shipping_incl_tax),
    base_tax_amount = base_tax_amount + base_shipping_tax_amount,
    base_shipping_amount = tax_base(base_shipping_incl_tax)
WHERE shipping_tax_amount = 0 AND shipping_amount <> 0;

UPDATE sales_flat_invoice
SET
    shipping_tax_amount = tax_amount(shipping_incl_tax),
    tax_amount = tax_amount + shipping_tax_amount,
    shipping_amount = tax_base(shipping_incl_tax),
    base_shipping_tax_amount = tax_amount(base_shipping_incl_tax),
    base_tax_amount = base_tax_amount + base_shipping_tax_amount,
    base_shipping_amount = tax_base(base_shipping_incl_tax)
WHERE shipping_tax_amount = 0 AND shipping_amount <> 0;

UPDATE sales_flat_quote_address
SET
    shipping_tax_amount = tax_amount(shipping_incl_tax),
    tax_amount = tax_amount + shipping_tax_amount,
    shipping_amount = tax_base(shipping_incl_tax),
    base_shipping_tax_amount = tax_amount(base_shipping_incl_tax),
    base_tax_amount = base_tax_amount + base_shipping_tax_amount,
    base_shipping_amount = tax_base(base_shipping_incl_tax)
WHERE shipping_tax_amount = 0 AND shipping_amount <> 0;

UPDATE sales_flat_order_payment
SET
    shipping_captured = CASE WHEN shipping_captured IS NOT NULL THEN tax_base(shipping_captured) ELSE NULL END,
    base_shipping_captured = CASE WHEN base_shipping_captured IS NOT NULL THEN tax_base(base_shipping_captured) ELSE NULL END,
    shipping_amount = CASE WHEN shipping_amount IS NOT NULL THEN tax_base(shipping_amount) ELSE NULL END,
    base_shipping_amount = CASE WHEN base_shipping_amount IS NOT NULL THEN tax_base(base_shipping_amount) ELSE NULL END,
    shipping_refunded = CASE WHEN shipping_refunded IS NOT NULL THEN tax_base(shipping_refunded) ELSE NULL END,
    base_shipping_refunded = CASE WHEN base_shipping_refunded IS NOT NULL THEN tax_base(base_shipping_refunded) ELSE NULL END
WHERE parent_id IN (
    SELECT entity_id
    FROM sales_flat_order
    WHERE shipping_tax_amount = 0 AND shipping_amount <> 0
);

UPDATE sales_order_tax t
JOIN sales_flat_order o ON t.order_id = o.entity_id
SET
    t.amount = t.amount + tax_amount(o.shipping_incl_tax),
    t.base_amount = t.base_amount + (@base_tax_amt := tax_amount(o.base_shipping_incl_tax)),
    t.base_real_amount = t.base_real_amount + @base_tax_amt
WHERE
    o.shipping_tax_amount = 0 AND
    o.shipping_amount <> 0 AND
    t.percent = @tax_percent;

INSERT INTO sales_order_tax (order_id, code, title, percent, amount, priority, position, base_amount, process, base_real_amount, hidden)
SELECT
    o.entity_id,
    @tax_code,
    @tax_title,
    CAST(@tax_percent AS DECIMAL(12,4)),
    tax_amount(o.shipping_incl_tax),
    0,
    0,
    (@base_tax_amt := tax_amount(o.base_shipping_incl_tax)),
    1, -- Not sure what this means, didn't work wih 0 though
    @base_tax_amt,
    0
FROM sales_flat_order o
LEFT JOIN sales_order_tax t
ON t.order_id = o.entity_id AND t.percent = @tax_percent
WHERE
    o.shipping_tax_amount = 0 AND
    o.shipping_amount <> 0 AND
    t.order_id IS NULL;

UPDATE sales_flat_order
SET
    shipping_tax_amount = tax_amount(shipping_incl_tax),
    tax_amount = tax_amount + shipping_tax_amount,
    shipping_amount = tax_base(shipping_incl_tax),
    base_shipping_tax_amount = tax_amount(base_shipping_incl_tax),
    base_tax_amount = base_tax_amount + base_shipping_tax_amount,
    base_shipping_amount = tax_base(base_shipping_incl_tax),
    tax_canceled = CASE WHEN shipping_canceled IS NOT NULL AND tax_canceled IS NOT NULL THEN tax_canceled + tax_amount(shipping_canceled) ELSE tax_canceled END,
    base_tax_canceled = CASE WHEN base_shipping_canceled IS NOT NULL AND base_tax_canceled IS NOT NULL THEN base_tax_canceled + tax_amount(base_shipping_canceled) ELSE base_tax_canceled END,
    tax_invoiced = CASE WHEN shipping_invoiced IS NOT NULL AND tax_invoiced IS NOT NULL THEN tax_invoiced + tax_amount(shipping_invoiced) ELSE tax_invoiced END,
    base_tax_invoiced = CASE WHEN base_shipping_invoiced IS NOT NULL AND base_tax_invoiced IS NOT NULL THEN base_tax_invoiced + tax_amount(base_shipping_invoiced) ELSE base_tax_invoiced END,
    shipping_canceled = CASE WHEN shipping_canceled IS NOT NULL THEN tax_base(shipping_canceled) ELSE NULL END,
    base_shipping_canceled = CASE WHEN base_shipping_canceled IS NOT NULL THEN tax_base(base_shipping_canceled) ELSE NULL END,
    shipping_invoiced = CASE WHEN shipping_invoiced IS NOT NULL THEN tax_base(shipping_invoiced) ELSE NULL END,
    base_shipping_invoiced = CASE WHEN base_shipping_invoiced IS NOT NULL THEN tax_base(base_shipping_invoiced) ELSE NULL END,
    shipping_tax_refunded = CASE WHEN shipping_refunded IS NOT NULL THEN tax_amount(shipping_refunded) ELSE NULL END,
    base_shipping_tax_refunded = CASE WHEN base_shipping_refunded IS NOT NULL THEN tax_amount(base_shipping_refunded) ELSE NULL END,
    tax_refunded = CASE WHEN shipping_tax_refunded IS NOT NULL AND tax_refunded IS NOT NULL THEN tax_refunded + shipping_tax_refunded ELSE tax_refunded END,
    base_tax_refunded = CASE WHEN base_shipping_tax_refunded IS NOT NULL AND base_tax_refunded IS NOT NULL THEN base_tax_refunded + base_shipping_tax_refunded ELSE base_tax_refunded END,
    shipping_refunded = CASE WHEN shipping_refunded IS NOT NULL THEN tax_base(shipping_refunded) ELSE NULL END,
    base_shipping_refunded = CASE WHEN base_shipping_refunded IS NOT NULL THEN tax_base(base_shipping_refunded) ELSE NULL END
WHERE shipping_tax_amount = 0 AND shipping_amount <> 0;

-- Cleanup

DROP FUNCTION tax_amount;
DROP FUNCTION tax_base;