-- --------------------------------------------------------
-- Server OS:                    Linux
-- HeidiSQL Version:             12.5.0.6677
-- --------------------------------------------------------



-- Dumping database structure for laporan
USE `laporan`;

-- Dumping structure for procedure laporan.LaporanPermintaanBarangInternal
DROP PROCEDURE IF EXISTS `LaporanPermintaanBarangInternal`;
DELIMITER //
CREATE PROCEDURE `LaporanPermintaanBarangInternal`(
	IN `TGLAWAL` DATE,
	IN `TGLAKHIR` DATE,
	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 vRUANGAN = CONCAT(RUANGAN,'%');
   SET vKATEGORI = CONCAT(KATEGORI,'%');
   SET vJENISINVENTORY = CONCAT(JENISINVENTORY,'%');
   SET vKATEGORIBARANG = CONCAT(KATEGORIBARANG,'%');

	SET @sqlText = CONCAT('
				SELECT 
							p.NOMOR, 
							p.OLEH,
							p.ASAL, 
							p.TUJUAN, 
							ra.DESKRIPSI AS RUANGAN_ASAL, 
							rt.DESKRIPSI AS RUANGAN_TUJUAN, 
							p.TANGGAL,
							pd.JUMLAH JUMLAH_PENGIRIMAN,
							pr_d.BARANG, 
							pr_d.JUMLAH JUMLAH_PERMINTAAN,
							br.NAMA, brk.NAMA BARANG_KIRIM,
							inventory.getHargaPenerimaanTerakhir(br.ID) HPT
							#, (inventory.getHargaPenerimaanTerakhir(br.ID)* (pr_d.JUMLAH)) HRG_TOT,
							, usr.NAMA AS PETUGAS,
							inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST,
							master.getHeaderKategoriBarang(''',KATEGORI,''') KATEGORI,
							master.getHeaderLaporan(''',RUANGAN,''') INSTALASI,
							IF(',BARANG,'=0,''Semua'',(SELECT br.NAMA FROM inventory.barang br WHERE br.ID=',BARANG,')) BARANGHEADER
					FROM 
					inventory.permintaan p
					LEFT JOIN aplikasi.pengguna usr ON usr.ID = p.OLEH
					LEFT JOIN master.ruangan ra ON ra.ID = p.ASAL
					LEFT JOIN master.ruangan rt ON rt.ID = p.TUJUAN
					, inventory.permintaan_detil pr_d  
					, inventory.barang br  
					LEFT JOIN inventory.kategori ik ON br.KATEGORI=ik.ID
					LEFT JOIN inventory.penggolongan_barang gb ON gb.BARANG=br.ID
					, inventory.pengiriman_detil pd
					LEFT JOIN inventory.barang brk ON brk.ID = pd.BARANG
					, (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
						FROM aplikasi.instansi ai
						, master.ppk mp
						WHERE ai.PPK=mp.ID) inst
						WHERE pr_d.PERMINTAAN = p.NOMOR AND pd.PERMINTAAN_BARANG_DETIL = pr_d.ID AND pd.`STATUS` = 2 AND br.ID = pr_d.BARANG AND
						p.TANGGAL BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
						  ',IF(RUANGAN=0,'',CONCAT(' AND p.TUJUAN LIKE ''',vRUANGAN,'''')),'
						  ',IF(KATEGORI=0,'',CONCAT(' AND ik.ID LIKE ''',VKATEGORI,'''')),'
						  ',IF(BARANG=0,'',CONCAT(' AND br.ID=',BARANG)),'
						  ',IF(JENISKATEGORI=0,'',CONCAT(' AND ik.ID LIKE ''',vKATEGORI,'''')),'
						  ',IF(JENISINVENTORY=0,'',CONCAT(' AND ik.ID LIKE ''',vJENISINVENTORY,'''')),'
						  ',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 gb.PENGGOLONGAN=',PENGGOLONGAN)),'
						ORDER BY p.ASAL, p.TUJUAN, p.TANGGAL, br.NAMA
				
			');

	
	PREPARE stmt FROM @sqlText;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
END//
DELIMITER ;

