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



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

-- Dumping structure for procedure laporan.LaporanRekapKoreksiPerAlasan
DROP PROCEDURE IF EXISTS `LaporanRekapKoreksiPerAlasan`;
DELIMITER //
CREATE PROCEDURE `LaporanRekapKoreksiPerAlasan`(
	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 
		CONCAT(''REKAP LAPORAN TRANSAKSI KOREKSI PER ALASAN'') JENISLAPORAN, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST
		, master.getHeaderLaporan(''',RUANGAN,''') INSTALASI
		, if(t.JENIS=1,''MASUK'',''KELUAR'') JENIS, t.TANGGAL, t.KET
		, IF(t.JENIS = 1, ra1.DESKRIPSI, ra2.DESKRIPSI) NMALASAN
		, b.NAMA NMBARANG, d.TANGGAL, SUM(d.JUMLAH) JUMLAH
		, inventory.getHargaPenerimaanTerakhir(b.ID) HPT
		#, (inventory.getHargaPenerimaanTerakhir(b.ID)* (SUM(d.JUMLAH))) HRG_TOT
		FROM inventory.transaksi_koreksi t
		LEFT JOIN master.ruangan rg ON t.RUANGAN=rg.ID AND rg.JENIS=5
		LEFT JOIN master.referensi ra1 ON t.ALASAN = ra1.ID AND ra1.JENIS = 900601
		LEFT JOIN master.referensi ra2 ON t.ALASAN = ra2.ID AND ra2.JENIS = 900602
		, inventory.transaksi_koreksi_detil d
		, inventory.barang b
		LEFT JOIN inventory.kategori ik ON b.KATEGORI=ik.ID
		LEFT JOIN inventory.penggolongan_barang gb ON gb.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 = d.BARANG AND d.KOREKSI = t.ID AND t.`STATUS` = 2
		AND t.TANGGAL BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
		',IF(RUANGAN='0','',CONCAT(' AND t.RUANGAN LIKE ''',vRUANGAN,'''')),'
		',IF(KATEGORI=0,'',CONCAT(' AND ik.ID LIKE ''',vKATEGORI,'''')),'
		',IF(BARANG=0,'',CONCAT(' AND b.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)),'
		GROUP BY t.JENIS, t.ALASAN, b.ID
	');

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

END//
DELIMITER ;

