use inventory;
DROP PROCEDURE IF EXISTS `inventory`.`LaporanStokOpnameFifo`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `inventory`.`LaporanStokOpnameFifo`(
	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 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,'%');
	SET @VNILAIFIFO := null; 
	SET @sqlText = CONCAT('
		SELECT ID, KODE_PSEDIA, BARANG, PABRIK, satuan, PPK, NAMAINST, ALAMATINST, JENISLAPORAN, INSTALASI, KATEGORIBRG, CARABAYARHEADER, BARANGHEADER, DATE_FORMAT(IF(EXD IS NULL,''0000-00-00'',EXD),''%Y-%m-%d'') EXD, sisa_stok, FISIK, SELISIH, STOK_AWAL, BARANG_MASUK, BARANG_KELUAR 
		   , HARGA_SATUAN, INVENT, KLP_BRG, KAT_BRG, FIFOX 
		   , JSON_EXTRACT(FIFOX,''$.NILAI'') FIFO
	    , REPLACE(JSON_EXTRACT(FIFOX,''$.KET''),''"'','''') KETERANGANFIFO
	      /*	, (SELECT KET FROM JSON_TABLE(FIFOX,
                ''$[*]'' 
                COLUMNS(
                     KET VARCHAR(150) PATH ''$.KET'') 
            ) AS t) KETERANGANFIFO
            */
			FROM (
			SELECT 
				inst.PPK,
				inst.NAMA NAMAINST,
				inst.ALAMAT ALAMATINST,
				(CONCAT (''LAPORAN STOK OPNAME'' , UPPER(jk.DESKRIPSI))) JENISLAPORAN,
				(master.getHeaderLaporan(''',RUANGAN,''')) INSTALASI,
				(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,
				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,
				inventory.getMetodeFifoBarangStok(b.ID, SUM(IF (sd.MANUAL IS NULL, 0, sd.MANUAL)),  DATE_FORMAT(CONCAT(so.TANGGAL,'' '',so.TIME), ''%Y-%m-%d %H:%i:%s'')) FIFOX
				, 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
		          ',IF(PENGGOLONGAN=0,'',CONCAT(' LEFT JOIN inventory.penggolongan_barang pb ON pb.BARANG=b.ID')),'
		          
				,(	SELECT mp.NAMA, ai.PPK, 	mp.ALAMAT
						FROM aplikasi.instansi ai , master.ppk mp
					 	WHERE ai.PPK=mp.ID
				 ) inst
				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,'''
			  		',IF(BARANG=0,'',CONCAT(' AND b.ID=',BARANG)),'
			  		',IF(JENISINVENTORY=0,'',CONCAT(' AND k.ID LIKE ''',vJENISINVENTORY,'''')),'
			  		',IF(JENISKATEGORI=0,'',CONCAT(' AND k.ID LIKE ''',vKATEGORI,'''')),'
			  		',IF(KATEGORIBARANG=0,'',CONCAT(' AND k.ID LIKE ''',vKATEGORIBARANG,'''')),'
			  		',IF(JENISGENERIK=0,'',CONCAT(' AND b.JENIS_GENERIK=',JENISGENERIK)),'
					',IF(JENISFORMULARIUM=0,'',CONCAT(' AND b.FORMULARIUM=',JENISFORMULARIUM)),'
					',IF(PENGGOLONGAN=0,'',CONCAT(' AND pb.PENGGOLONGAN=',PENGGOLONGAN)),'			  		
			  		GROUP BY b.ID
			  		ORDER BY b.KATEGORI ASC
			  		) ab
			');
			

	PREPARE stmt FROM @sqlText;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

END//
DELIMITER ;