USE `laporan`;

DROP PROCEDURE IF EXISTS `laporan`.`getStokFifoMasuk`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `laporan`.`getStokFifoMasuk`(
	IN `PIDOBAT` INT,
	IN `PSTOK` DECIMAL(15,3),
	IN `PNILAI_MAX` DECIMAL(15,3),
	IN `PTANGGAL` DATETIME
)
BEGIN

	

	SET @jmlsisa1= ROUND(0.00,2);
	SET @stokakhir1=null;
	SET @jmlsisa= ROUND(0.00,2);
	SET @stokakhir=null;
	
	DROP TEMPORARY TABLE IF EXISTS tmp1_masuk;

	CREATE TEMPORARY TABLE tmp1_masuk (id INT, jml DECIMAL(60,2), harga DECIMAL(60,2)) ENGINE = MEMORY;
	INSERT INTO tmp1_masuk
	SELECT BARANG, SUM(jmlsisa) jmlsisa, HARGA
						FROM (

					SELECT BARANG, HARGA 
						, @stokakhir:=(IF(@stokakhir is null,@stokakhir:=ROUND(PSTOK,2),@stokakhir) - @jmlsisa) stokakhir
						, @jmlsisa:= IF(jmlsisa > @stokakhir,@stokakhir, IF(YEAR(TANGGAL)< '2021',@stokakhir,jmlsisa)) jmlsisa
					FROM (
					SELECT BARANG, SUM(jmlsisa) jmlsisa, HARGA, TANGGAL
						FROM (	
								SELECT ab.TANGGAL, ab.BARANG, ab.JUMLAH, ab.HARGA
										, @stokakhir1:=(IF(@stokakhir1 is null,@stokakhir1:=ROUND(PNILAI_MAX,2),@stokakhir1) - @jmlsisa1) stokakhir
										, @jmlsisa1:= IF(ab.JUMLAH > @stokakhir1,@stokakhir1, IF(YEAR(ab.TANGGAL)< '2021',@stokakhir1,ab.JUMLAH)) jmlsisa
									 
									FROM (
												(
													SELECT pb.TANGGAL, pbd.BARANG, pbd.JUMLAH, pbd.HARGA
													  FROM inventory.penerimaan_barang_detil pbd
														  , inventory.penerimaan_barang pb
													 WHERE pbd.BARANG=PIDOBAT AND pbd.PENERIMAAN=pb.ID AND pbd.`STATUS` = 2 AND pbd.BONUS=0 AND pb.TANGGAL <= PTANGGAL AND pb.`STATUS`=2
													ORDER BY pb.TANGGAL DESC
												)
													UNION ALL
												(
													SELECT DATE_ADD('2022-12-01 00:00:00', INTERVAL ID MINUTE) TANGGAL, pgd.BARANG, pgd.JUMLAH, pgd.HARGA
														HARGA
													FROM inventory.hrg_migrasi_sistem pgd
													WHERE pgd.BARANG=PIDOBAT AND pgd.JUMLAH > 0
												)
													UNION ALL
												(
													SELECT h.TANGGAL, d.BARANG, d.QTY JUMLAH, d.HARGA
													FROM inventory.hibah h
														, inventory.hibah_detil d
													WHERE d.HIBAH = h.ID AND d.BARANG = PIDOBAT and h.`STATUS` = 2 AND h.JENIS = 1 and h.TANGGAL <= PTANGGAL
													ORDER BY h.TANGGAL DESC
												)
											) ab ORDER BY ab.TANGGAL DESC
								) cd
					WHERE cd.jmlsisa!=0
					GROUP BY BARANG, HARGA 
					) ef 
					ORDER BY TANGGAL DESC
				) gh
		WHERE jmlsisa!=0
	GROUP BY BARANG, HARGA;

	
END//
DELIMITER ;