use inventory;
DROP PROCEDURE IF EXISTS `inventory`.`LaporanStokOpnameFifoDetail`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `inventory`.`LaporanStokOpnameFifoDetail`(
	IN `TGLAWAL` DATETIME,
	IN `TGLAKHIR` DATETIME,
	IN `RUANGAN` CHAR(10),
	IN `LAPORAN` INT,
	IN `CARABAYAR` INT,
	IN `KATEGORI` INT,
	IN `BARANG` INT,
	IN `JENISINVENTORY` INT,
	IN `JENISKATEGORI` INT,
	IN `KATEGORIBARANG` INT,
	IN `JENISGENERIK` TINYINT,
	IN `JENISFORMULARIUM` INT,
	IN `PENGGOLONGAN` INT

)
BEGIN

   
   DECLARE counter INT DEFAULT 0;
	DECLARE vRUANGAN VARCHAR(11);
	DECLARE vKATEGORI VARCHAR(11);
	DECLARE vJENISINVENTORY VARCHAR(11);
	DECLARE vKATEGORIBARANG VARCHAR(11);	
	
	SET RUANGAN = IF(RUANGAN = '0', '', RUANGAN); 
   SET vRUANGAN = CONCAT(RUANGAN,'%');
   SET vKATEGORI = CONCAT(KATEGORI,'%');
   SET vJENISINVENTORY = CONCAT(JENISINVENTORY,'%');
   SET vKATEGORIBARANG = CONCAT(KATEGORIBARANG,'%');
   
   DROP TEMPORARY TABLE IF EXISTS tmp2;
   DROP TEMPORARY TABLE IF EXISTS tmp3;
  
	CREATE TEMPORARY TABLE tmp2 (id INT, jml DECIMAL(60,2), idx BIGINT, harga DECIMAL(60,2), tanggal DATETIME) ENGINE = MEMORY;
	CREATE TEMPORARY TABLE tmp3 (
		ID INT,
		KODE_PSEDIA VARCHAR(20),
		BARANG VARCHAR(250),
		PABRIK VARCHAR(250),
		SATUAN VARCHAR(50),
		EXD VARCHAR(10),
		SISA_STOK DECIMAL(60,2),
		FISIK DECIMAL(60,2),
		SELISIH DECIMAL(60,2),
		TANGGAL DATETIME,
		STOK_AWAL DECIMAL(60,2),
		BARANG_MASUK DECIMAL(60,2),
		BARANG_KELUAR DECIMAL(60,2),
		HARGA_SATUAN DECIMAL(60,2),
		INVENT VARCHAR(100),
		KLP_BRG VARCHAR(100),
		KAT_BRG VARCHAR(100)
	) 
	ENGINE = MEMORY;
	
	BEGIN 
		DECLARE VID INT;
		DECLARE VKODE_PSEDIA VARCHAR(20);
		DECLARE VBARANG VARCHAR(250);
		DECLARE VPABRIK VARCHAR(250);
		DECLARE VSATUAN VARCHAR(50);
		DECLARE VEXD VARCHAR(10);
		DECLARE VSISA_STOK DECIMAL(60,2);
		DECLARE VFISIK DECIMAL(60,2);
		DECLARE VSELISIH DECIMAL(60,2);
		DECLARE VTANGGAL DATETIME;
		DECLARE VSTOK_AWAL DECIMAL(60,2);
		DECLARE VBARANG_MASUK DECIMAL(60,2);
		DECLARE VBARANG_KELUAR DECIMAL(60,2);
		DECLARE VHARGA_SATUAN DECIMAL(60,2);
		DECLARE VINVENT VARCHAR(100);
		DECLARE VKLP_BRG VARCHAR(100);
		DECLARE VKAT_BRG VARCHAR(100);
		DECLARE EXEC_NOT_FOUND TINYINT DEFAULT FALSE;		
		DECLARE CR_EXEC CURSOR FOR
	
		SELECT b.ID, b.KODE_PSEDIA, b.NAMA BARANG, r.DESKRIPSI PABRIK, s.DESKRIPSI satuan, MIN(sd.EXD) EXD,
				SUM(IF (sd.SISTEM IS NULL, 0, sd.SISTEM)) sisa_stok,
				SUM(IF (sd.MANUAL IS NULL, 0, sd.MANUAL)) FISIK,
				(SUM(IF (sd.SISTEM IS NULL, 0, sd.SISTEM)) - SUM(IF (sd.MANUAL IS NULL, 0, sd.MANUAL))) SELISIH,
				DATE_FORMAT(CONCAT(so.TANGGAL,' ',so.TIME), '%Y-%m-%d %H:%i:%s') TANGGAL
				, SUM(sd.AWAL) STOK_AWAL ,SUM(sd.BARANG_MASUK) BARANG_MASUK, SUM(sd.BARANG_KELUAR) BARANG_KELUAR
				, inventory.getHargaPenerimaanTerakhir(b.ID) HARGA_SATUAN
				, k3.NAMA INVENT, k2.NAMA KLP_BRG, k.NAMA KAT_BRG
			FROM inventory.stok_opname so
				  LEFT JOIN master.ruangan ru ON so.RUANGAN=ru.ID
				  LEFT JOIN master.referensi jk ON ru.JENIS_KUNJUNGAN=jk.ID AND jk.JENIS=15
				, inventory.stok_opname_detil sd
				, inventory.barang_ruangan br
				, inventory.barang b 
	           LEFT JOIN master.referensi r ON r.ID = b.MERK AND r.JENIS = 39
	           LEFT JOIN inventory.satuan s ON s.ID = b.SATUAN
	           LEFT JOIN inventory.kategori k ON k.ID = b.KATEGORI AND k.JENIS = 3
	           LEFT JOIN inventory.kategori k2 ON k2.ID = LEFT(k.ID,3) AND k2.JENIS = 2
	           LEFT JOIN inventory.kategori k3 ON k3.ID = LEFT(k2.ID,1) AND k3.JENIS = 1
	    	WHERE b.ID = br.BARANG AND br.ID = sd.BARANG_RUANGAN AND sd.STOK_OPNAME = so.ID									
			  AND so.STATUS='Final'
			  AND sd.STATUS=2
	  		  AND so.TANGGAL BETWEEN TGLAWAL AND TGLAKHIR
	  	  	  AND so.RUANGAN LIKE vRUANGAN
	  	  	  AND IF(BARANG=0,b.ID!=0,b.ID=BARANG)
	  	  	  AND IF(JENISINVENTORY=0,b.KATEGORI!=0,b.KATEGORI LIKE vJENISINVENTORY)
	  		GROUP BY b.ID
	  		ORDER BY b.KATEGORI ASC;
			  		
	   DECLARE CONTINUE HANDLER FOR NOT FOUND SET EXEC_NOT_FOUND = TRUE;
						
		OPEN CR_EXEC;
						
		EXIT_EXEC: LOOP
			FETCH CR_EXEC INTO VID, VKODE_PSEDIA, VBARANG, VPABRIK, VSATUAN, VEXD, VSISA_STOK, VFISIK, VSELISIH, VTANGGAL, VSTOK_AWAL, VBARANG_MASUK, VBARANG_KELUAR, VHARGA_SATUAN, VINVENT, VKLP_BRG, VKAT_BRG;
							
			IF EXEC_NOT_FOUND THEN
				LEAVE EXIT_EXEC;
			END IF;
			
			 INSERT INTO tmp3(ID, KODE_PSEDIA,BARANG,PABRIK,SATUAN,EXD,SISA_STOK,FISIK,SELISIH,TANGGAL,STOK_AWAL,BARANG_MASUK,BARANG_KELUAR,HARGA_SATUAN,INVENT,KLP_BRG,KAT_BRG) 
			 VALUES(VID, VKODE_PSEDIA, VBARANG, VPABRIK, VSATUAN, VEXD, VSISA_STOK, VFISIK, VSELISIH, VTANGGAL, VSTOK_AWAL, VBARANG_MASUK, VBARANG_KELUAR, VHARGA_SATUAN, VINVENT, VKLP_BRG, VKAT_BRG);
			 
			 CALL laporan.getStokFifo(VID, VFISIK, VTANGGAL);
			 INSERT INTO inventory.tmp2 SELECT * FROM laporan.tmp1;
			 
			 
		END LOOP;
		
		CLOSE CR_EXEC;
	END;
 
 	SELECT mp.NAMA NAMAINST, mp.ALAMAT ALAMATINST, ai.PPK, w.DESKRIPSI KOTA, mp.TELEPON, mp.FAX, ai.EMAIL
		   INTO @NAMAINST, @ALAMATINST, @PPK, @KOTA, @TELEPON, @FAX, @EMAIL
			FROM aplikasi.instansi ai
				, master.ppk mp
				, master.wilayah w
			WHERE ai.PPK=mp.ID AND mp.WILAYAH=w.ID;
			
  SELECT a.*, IFNULL(b.id, a.id) idb, IFNULL(b.jml, 0) jml, IFNULL(b.harga, 0) harga, (IFNULL(b.jml, 0) * IFNULL(b.harga, 0)) TOTAL
 		, @PPK PPK, @NAMAINST NAMAINST, @ALAMATINST ALAMATINST
 	   , (master.getHeaderLaporan(RUANGAN)) INSTALASI
		, IF(KATEGORI=0,'Semua',(master.getHeaderKategoriBarang(KATEGORI))) KATEGORIBRG
		, IF(CARABAYAR=0,'Semua',(SELECT ref.DESKRIPSI FROM master.referensi ref WHERE ref.ID=CARABAYAR AND ref.JENIS=10)) CARABAYARHEADER
		, IF(BARANG=0,'Semua',(SELECT br.NAMA FROM inventory.barang br WHERE br.ID=BARANG)) BARANGHEADER
		, 'LAPORAN STOK OPNAME' JENISLAPORAN
 	FROM inventory.tmp3 a
	 	  LEFT JOIN inventory.tmp2 b ON a.id=b.id
	ORDER BY KAT_BRG,  ID ASC
	;	
END//
DELIMITER ;