I'm currently writing unit tests for a stored procedure that calculates the amount of taxes and the tax rate based on the country/province the customer lives in.
Somehow, MySQL considers that "14.975" is different than "14.975".
If I try taxRate < 14.975
or taxRate = 14.975
, it does not enter the condition; but taxRate <> 14.975
and taxRate > 14.975
does.
I have the feeling I'm missing something obvious, but I just can't figure it out.
My test code:
DECLARE totalTax DECIMAL(20,6) DEFAULT 0;
DECLARE taxRate DECIMAL(10,3) DEFAULT 0;
SET @roundMode = 0;
SET @roundPrecision = 2;
/* caculate TPS only*/
CALL calculateTotalTaxOnAmount (200, 4, NULL, totalTax, taxRate);
IF (totalTax <> 10) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'INVALID_TAX_AMOUNT_CANADA';
ELSEIF (taxRate <> 5) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'INVALID_TAX_RATE_CANADA';
END IF;
/* caculate TPS and TVQ */
CALL calculateTotalTaxOnAmount (100, 4, 87, totalTax, taxRate);
IF (totalTax <> 14.98) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'INVALID_TAX_AMOUNT_CANADA_QUEBEC';
ELSEIF (taxRate <> 14.975) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'INVALID_TAX_RATE_CANADA_QUEBEC';
END IF;
My function:
CREATE PROCEDURE calculateTotalTaxOnAmount (
IN amount DECIMAL(20,6),
IN countryID INT(10),
IN stateID INT(10),
OUT totalTax DECIMAL(20,6),
OUT totalTaxRate DECIMAL(10,3)
)
BEGIN
DECLARE break BOOLEAN DEFAULT FALSE;
DECLARE taxBehavior INT(10);
DECLARE taxRate DECIMAL(10,3);
DECLARE TaxCursor CURSOR FOR
SELECT (t.`rate` / 100),
tr.`behavior`
FROM `ps_tax_rule` tr
JOIN `ps_tax` t
ON tr.`id_tax` = t.`id_tax`
WHERE tr.`id_country` = countryID
AND tr.`id_state` IN (0, stateID)
AND t.`active` = 1
ORDER BY tr.`id_state`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET break = TRUE;
SET totalTax = 0;
SET totalTaxRate = 0;
OPEN TaxCursor;
tax_loop: LOOP
FETCH TaxCursor INTO
taxRate,
taxBehavior;
IF (break) THEN
LEAVE tax_loop;
END IF;
IF (taxBehavior = 2) THEN /* cumulative tax */
SET totalTax = totalTax + (amount + totalTax) * taxRate;
ELSE /* standalone tax */
SET totalTax = totalTax + (amount * taxRate);
END IF;
END LOOP tax_loop;
CLOSE TaxCursor;
IF (totalTax <> 0) THEN
SET totalTaxRate = totalTax * 100 / amount;
SET totalTax = roundMoney(totalTax);
END IF;
END //
Note: If you want to test it locally, replace roundMoney(totalTax)
by round(totalTax, 2)