USE `laporan`;

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

	

	SET @jmlsisa= ROUND(0.00,2);
	SET @stokakhir=null;
	
	DROP TEMPORARY TABLE IF EXISTS tmp1_saldoawal;
	
	CREATE TEMPORARY TABLE tmp1_saldoawal (id INT, jml DECIMAL(60,2), harga DECIMAL(60,2)) ENGINE = MEMORY;
	INSERT INTO tmp1_saldoawal
	SELECT BARANG, SUM(jmlsisa) jmlsisa, HARGA
		FROM (	
				SELECT ab.TANGGAL, ab.BARANG, ab.JUMLAH, ab.HARGA
						, @stokakhir:=(IF(@stokakhir is null,@stokakhir:=ROUND(PSTOK,2),@stokakhir) - @jmlsisa) stokakhir
						, @jmlsisa:= IF(ab.JUMLAH > @stokakhir OR JENIS=2,@stokakhir, IF(YEAR(ab.TANGGAL)< '2021',@stokakhir,ab.JUMLAH)) jmlsisa
					 
					FROM (
								(
									SELECT 1 JENIS, 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 <= DATE_SUB(PTANGGAL, INTERVAL 1 MONTH) AND pb.`STATUS`=2
									ORDER BY pb.TANGGAL DESC
								)
									UNION ALL
								(
									SELECT 1 JENIS, 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 1 JENIS,  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 <= DATE_SUB(PTANGGAL, INTERVAL 1 MONTH)
									ORDER BY h.TANGGAL DESC
								)
							) ab ORDER BY ab.TANGGAL DESC
				) cd
	WHERE cd.jmlsisa!=0
	GROUP BY BARANG, HARGA ;

 # SELECT * FROM tmp1;
	
END//
DELIMITER ;