USE `laporan`;

-- Dumping structure for procedure laporan.LaporanTransaksiPenerimaanResidu
DROP PROCEDURE IF EXISTS `LaporanTransaksiPenerimaanResidu`;
DELIMITER //
CREATE PROCEDURE `LaporanTransaksiPenerimaanResidu`(
	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 vRUANGAN = CONCAT(RUANGAN,'%');
   SET vKATEGORI = CONCAT(KATEGORI,'%');
   SET vJENISINVENTORY = CONCAT(JENISINVENTORY,'%');
   SET vKATEGORIBARANG = CONCAT(KATEGORIBARANG,'%');  
	SET @sqlText = CONCAT('
	SELECT inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST
	, CONCAT(''LAPORAN TRANSAKSI PENERIMAAN RESIDU'') JENISLAPORAN
	, IFNULL(master.getHeaderKategoriBarang(IF(''',KATEGORIBARANG,'''!=''0'',''',KATEGORIBARANG,''',IF(''',KATEGORI,'''!=''0'',''',KATEGORI,''',''',JENISINVENTORY,'''))),''Semua'') KATEGORI
	, IF(',BARANG,'=0,''Semua'',(SELECT br.NAMA FROM inventory.barang br WHERE br.ID=',BARANG,')) BARANGHEADER
	, (master.getHeaderLaporan(''',RUANGAN,''')) INSTALASI
	, r.TANGGAL,r.ASAL 
	, ruang.DESKRIPSI UNIT_ASAL
	, r.RUANGAN
	, r2.DESKRIPSI NM_RUANGAN
	, CONCAT( b.NAMA,'' | '', IFNULL(k3.NAMA,''-''), '' | '',IFNULL(ref.DESKRIPSI,''-''),'' | '',IFNULL(k.NAMA,''-'')) NM_OBAT
	, rd.QTY,@HPT:=inventory.getHargaPenerimaanTerakhir(rd.BARANG) HPT, (@HPT*rd.QTY) TOTAL
	, r.OLEH, master.getNamaLengkapPegawai(mp.NIP) USER
	, r.KETERANGAN 
	FROM 
		inventory.penerimaan_barang_residu r
		LEFT JOIN master.ruangan ruang ON ruang.ID=r.ASAL
		LEFT JOIN master.ruangan r2 ON r2.ID=r.RUANGAN
		LEFT JOIN aplikasi.pengguna mp ON mp.ID=r.OLEH
		, inventory.penerimaan_barang_residu_detil rd
		, inventory.barang b 
		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
		LEFT JOIN master.referensi ref ON ref.ID=b.MERK AND ref.JENIS=39
		',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 r.ID=rd.PENERIMAAN AND r.STATUS=2 AND b.ID=rd.BARANG 
				AND r2.JENIS_KUNJUNGAN=',LAPORAN,'
				AND r.TANGGAL BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
				AND r.RUANGAN LIKE ''',vRUANGAN,'''
				',IF(KATEGORI=0,'',CONCAT(' AND k.ID LIKE ''',vKATEGORI,'''')),'
				',IF(BARANG=0,'',CONCAT(' AND b.ID=',BARANG)),'
				',IF(JENISKATEGORI=0,'',CONCAT(' AND k.ID LIKE ''',vKATEGORI,'''')),'
				',IF(JENISINVENTORY=0,'',CONCAT(' AND k.ID LIKE ''',vJENISINVENTORY,'''')),'
				',IF(KATEGORIBARANG IN (0,10100),'',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)),'
	ORDER BY r.TANGGAL DESC ,ruang.DESKRIPSI DESC
	');

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

END//
DELIMITER ;