USE `laporan`;
DROP PROCEDURE IF EXISTS `LaporanPerencanaanBarang`;
DELIMITER //
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `LaporanPerencanaanBarang`(IN `TGLAWAL` DATETIME, IN `TGLAKHIR` DATETIME, IN `RUANGAN` INT, 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 @sqlText = CONCAT('
		SELECT 
			inst.NAMA NAMAINST,inst.ALAMAT ALAMATINST,
			(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
			, mr.TANGGAL, ib.ID, ib.NAMA OBAT, ik.NAMA KTG
			, SUM(bon.SISA) SISA,  mrd.SISA_STOK, mrd.HARGA_BELI
			, mrd.JUMLAH JML_RENCANA
			, inventory.getHargaPenerimaanTerakhirByTanggal(ib.ID, MAX(mr.TANGGAL)) HPT
		FROM 
			inventory.material_request mr
			, inventory.material_request_detil mrd
			LEFT JOIN layanan.bon_sisa_farmasi bon ON bon.FARMASI=mrd.BARANG
			, inventory.barang ib 
			LEFT JOIN inventory.kategori ik ON ib.KATEGORI=ik.ID AND ik.JENIS = 3
			LEFT JOIN inventory.penggolongan_barang pb ON pb.BARANG=ib.ID
			,(	SELECT mp.NAMA, ai.PPK, 	mp.ALAMAT
				FROM aplikasi.instansi ai , master.ppk mp
				WHERE ai.PPK=mp.ID
			) inst
		WHERE mr.NO_MR=mrd.NO_MR AND mrd.BARANG=ib.ID
				AND mr.TANGGAL BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
			  	AND mr.UNIT LIKE ''',vRUANGAN,'''
			  	',IF(BARANG=0,'',CONCAT(' AND ib.ID=',BARANG)),'
			  	',IF(JENISINVENTORY=0,'',CONCAT(' AND ik.ID LIKE ''',vJENISINVENTORY,'''')),'
			  	',IF(JENISKATEGORI=0,'',CONCAT(' AND ik.ID LIKE ''',vKATEGORI,'''')),'
			  	',IF(KATEGORIBARANG=0,'',CONCAT(' AND ik.ID LIKE ''',vKATEGORIBARANG,'''')),'
			  	',IF(JENISGENERIK=0,'',CONCAT(' AND ib.JENIS_GENERIK=',JENISGENERIK)),'
				',IF(JENISFORMULARIUM=0,'',CONCAT(' AND ib.FORMULARIUM=',JENISFORMULARIUM)),'
				',IF(PENGGOLONGAN=0,'',CONCAT(' AND pb.PENGGOLONGAN=',PENGGOLONGAN)),'
		GROUP BY mrd.BARANG
		ORDER BY ib.NAMA ASC
	');

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

	
END//
DELIMITER ;