unitConverter.sql

DELIMITER $$

DROP FUNCTION IF EXISTS `unit_conversion`$$

CREATE FUNCTION `unit_conversion`(value_to_convert DECIMAL(10,2), precission INT, au_lbl_1 INT, au_lbl_2 INT)
 RETURNS VARCHAR(50) CHARSET utf8 COLLATE utf8_unicode_ci
    DETERMINISTIC
BEGIN 
DECLARE level1 VARCHAR(10);
DECLARE level2 VARCHAR(10);
DECLARE level3 VARCHAR(10);
DECLARE rem VARCHAR(10);
DECLARE formated_unit VARCHAR(50);

IF au_lbl_1 IS NOT NULL AND au_lbl_1 > 0 THEN
    SET level3 =  FLOOR(value_to_convert % au_lbl_1);
    SET rem = FLOOR(value_to_convert /au_lbl_1);
    SET formated_unit = CONCAT(rem, '-', level3);
ELSE
    IF precission IS NOT NULL THEN
	RETURN ROUND(value_to_convert,precission);
    ELSE
	RETURN value_to_convert;
END IF;
END IF;

IF au_lbl_2 IS NOT NULL AND au_lbl_2 > 0 THEN
    SET level2 = rem % au_lbl_2;
    SET level1 = FLOOR(rem / au_lbl_2);
    SET formated_unit = CONCAT(level1, "-", level2, "-", level3);
END IF; 

RETURN formated_unit;
END$$

DELIMITER ;