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



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

-- Dumping structure for procedure laporan.LaporanRekapPenerimaanBarangPerPbf
DROP PROCEDURE IF EXISTS `LaporanRekapPenerimaanBarangPerPbf`;
DELIMITER //
CREATE PROCEDURE `LaporanRekapPenerimaanBarangPerPbf`(
	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.NAMA NAMAINST, inst.ALAMAT ALAMATINST, py.NAMA NAMAREKANAN, py.ALAMAT ALAMATREKANAN, py.TELEPON TLPREKANAN, pb.FAKTUR NOFAKTUR, pb.TANGGAL TGLFAKTUR, pb.TANGGAL_DIBUAT
				  , ib.NAMA NAMABARANG, pbd.JUMLAH, s.NAMA NAMASATUAN, pbd.HARGA, ROUND(SUM(pbd.DISKON),2) DISKON, ROUND(SUM(((pbd.JUMLAH * pbd.HARGA) - pbd.DISKON)),2) TOTAL
				  , ROUND(SUM((pbd.JUMLAH * pbd.HARGA)),2) NILAI_JUMLAH
				  , ROUND(SUM(IF(pb.PPN=''Ya'',0, ((pbd.JUMLAH * pbd.HARGA) * 11 / 100))),2) NILAI_PPN
				  , ROUND(SUM((
				  		(pbd.JUMLAH * pbd.HARGA) +
				  			IF(pb.PPN=''Ya'',0, ((pbd.JUMLAH * pbd.HARGA) * 11 / 100)) -
							 pbd.DISKON
						)),2) NILAI_TOTAL
				  , master.getHeaderKategoriBarang(''',KATEGORI,''') KATEGORI
				  , master.getHeaderLaporan(''',RUANGAN,''') INSTALASI
				  , IF(',BARANG,'=0,''Semua'',(SELECT br.NAMA FROM inventory.barang br WHERE br.ID=',BARANG,')) BARANGHEADER
				  , pb.NO_SP, ra.NAMA ALOKASI_ANGGARAN
				FROM inventory.penerimaan_barang pb
				     LEFT JOIN inventory.penyedia py ON pb.REKANAN=py.ID
				     LEFT JOIN master.ruangan r ON pb.RUANGAN=r.ID
				     LEFT JOIN inventory.ref_anggaran ra ON ra.ID = pb.SUMBER_DANA
					, inventory.penerimaan_barang_detil pbd
					, inventory.barang ib
					  LEFT JOIN inventory.satuan s ON ib.SATUAN=s.ID
					  LEFT JOIN inventory.kategori ik ON ib.KATEGORI=ik.ID
					  LEFT JOIN inventory.penggolongan_barang gb ON gb.BARANG=ib.ID 
					, (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
								FROM aplikasi.instansi ai
									, master.ppk mp
								WHERE ai.PPK=mp.ID) inst
				WHERE pb.ID=pbd.PENERIMAAN AND pbd.BARANG=ib.ID AND pb.TANGGAL_DIBUAT BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
				  AND pb.RUANGAN LIKE ''',vRUANGAN,''' AND pb.STATUS=2 AND pbd.STATUS=2
				  ',IF(KATEGORI=0,'',CONCAT(' AND ik.ID LIKE ''',VKATEGORI,'''')),'
				  ',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 b.FORMULARIUM=',JENISFORMULARIUM)),'
				  ',IF(PENGGOLONGAN=0,'',CONCAT(' AND gb.PENGGOLONGAN=',PENGGOLONGAN)),'
				GROUP BY pb.REKANAN
			');

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

END//
DELIMITER ;

