USE `inventory`;
DROP FUNCTION IF EXISTS `getKategoriRumusPerencanaan`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `getKategoriRumusPerencanaan`(`PNILAI` DECIMAL(20,2)) RETURNS char(5) CHARSET latin1
    DETERMINISTIC
BEGIN
	DECLARE VKATEGORI, VKAT CHAR(5);
	
	SELECT r.KATEGORI INTO VKAT 
	FROM rumus_perencanaan r
	WHERE r.MIN_INDEX <= PNILAI AND r.MAX_INDEX >= PNILAI AND r.STATUS = 1 ORDER BY r.UPDATE_TIME DESC LIMIT 1;
	
	IF FOUND_ROWS() > 0 THEN
		SET VKATEGORI = VKAT;
	ELSE
		SET VKATEGORI = IF(PNILAI < 80, 'A', IF(PNILAI < 95, 'B','C'));
	END IF;
	
	RETURN VKATEGORI;
END//
DELIMITER ;