USE inventory;

DROP PROCEDURE IF EXISTS inventory.executeHargaBarangByPenerimaan;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `inventory`.`executeHargaBarangByPenerimaan`(IN `PLIMIT` INT)
BEGIN
	DECLARE VBARANG, VPPN, VOLEH, VSTATUS, VREF_PENERIMAAN INT;
	DECLARE VHARGA_BELI, VHARGA_JUAL DECIMAL(60,2);
	DECLARE VMASA_BERLAKU, VTANGGAL DATETIME;
	DECLARE RECALC_NOT_FOUND TINYINT DEFAULT FALSE;
	
	DECLARE CR_RECALC CURSOR FOR
		SELECT BARANG, HARGA_BELI, HARGA_JUAL, PPN, MASA_BERLAKU, TANGGAL, OLEH, STATUS, REF_PENERIMAAN
		FROM inventory.harga_barang_log_penerimaan WHERE STATUS = 1 ORDER BY ID ASC LIMIT PLIMIT;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET RECALC_NOT_FOUND = TRUE;
	
	OPEN CR_RECALC;
	EOF: LOOP
		FETCH CR_RECALC INTO VBARANG, VHARGA_BELI, VHARGA_JUAL, VPPN, VMASA_BERLAKU, VTANGGAL, VOLEH, VREF_PENERIMAAN;
		
		IF RECALC_NOT_FOUND THEN
			LEAVE EOF;
		END IF;
		
		IF EXISTS(SELECT 1
		  FROM inventory.harga_barang
		 WHERE BARANG = VBARANG AND STATUS = 1) THEN	
			UPDATE inventory.harga_barang SET STATUS = 0 WHERE BARANG = VBARANG AND STATUS = 1;
		END IF;
	
		
		IF NOT EXISTS(SELECT 1
		  FROM inventory.harga_barang
		 WHERE REF_PENERIMAAN = VREF_PENERIMAAN) THEN	
			INSERT INTO inventory.harga_barang (BARANG, HARGA_BELI, HARGA_JUAL, PPN, MASA_BERLAKU, TANGGAL, OLEH, STATUS, REF_PENERIMAAN, IS_UPDATE_BY_CORE) 
			VALUES (VBARANG, VHARGA_BELI, VHARGA_JUAL, VPPN, VMASA_BERLAKU, VTANGGAL, VOLEH, 1, VREF_PENERIMAAN, 1);
		END IF;
		
	END LOOP;
	CLOSE CR_RECALC;
END//
DELIMITER ;