USE `inventory`;

DROP FUNCTION IF EXISTS `inventory`.`getHargaPenerimaanTerakhirBySo`;
DELIMITER //

CREATE DEFINER=`root`@`localhost` FUNCTION `inventory`.`getHargaPenerimaanTerakhirBySo`(
	`PBARANG` BIGINT,
	`PSO` INT
) RETURNS decimal(20,2)
    DETERMINISTIC
BEGIN
	DECLARE VHRG DECIMAL(20,2);
	DECLARE VTANGGAL DATETIME;
	
	SET VTANGGAL = LOCALTIME;
	
	SELECT DATE_FORMAT(CONCAT(s.TANGGAL,' ',s.TIME),'%Y-%m-%d %H:%i:%s') INTO VTANGGAL FROM inventory.stok_opname s WHERE s.ID = PSO;
	
	SELECT pbd.HARGA INTO VHRG 
	FROM inventory.penerimaan_barang_detil pbd
		, inventory.penerimaan_barang pb
	WHERE pbd.BARANG=PBARANG AND pbd.PENERIMAAN=pb.ID AND pbd.JUMLAH > 0 AND pbd.HARGA > 0
	  AND pb.TANGGAL_DIBUAT < VTANGGAL
	ORDER BY pb.TANGGAL_DIBUAT DESC LIMIT 1;
	
	IF VHRG > 0 THEN
		RETURN VHRG;
	ELSE
		SELECT d.HARGA INTO VHRG
		FROM inventory.hibah h
			, inventory.hibah_detil d
		WHERE d.HIBAH = h.ID AND d.BARANG = PBARANG and h.`STATUS` = 2 AND h.JENIS = 1 and h.TANGGAL <= VTANGGAL
		ORDER BY h.TANGGAL DESC LIMIT 1;
		IF VHRG > 0 THEN
			RETURN VHRG;
		ELSE
			SELECT hms.HARGA INTO VHRG
				FROM inventory.hrg_migrasi_sistem hms WHERE hms.BARANG = PBARANG ORDER BY hms.ID DESC LIMIT 1;
			IF VHRG > 0  THEN
				RETURN VHRG;
			ELSE
				SELECT h.HARGA INTO VHRG
					FROM inventory.harga_estimasi_barang h WHERE h.BARANG=PBARANG AND h.`STATUS` = 1 ORDER BY h.UPDATE_TIME DESC LIMIT 1;
				IF VHRG > 0 THEN
					RETURN VHRG;
				ELSE
					RETURN 0;
				END IF;
			END IF;
		END IF;
	END IF;
END//
DELIMITER ;