
USE `laporan`;
DROP PROCEDURE IF EXISTS `LaporanPenerimaanBarangExternal`;
DELIMITER //
CREATE PROCEDURE `LaporanPenerimaanBarangExternal`(
	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.ID, ib.NAMA NAMABARANG, ib.ID REF_ID, ik.NAMA KTG, ib.ID
          , pbd.REF_BRG, bs.ID ID_BRG_SKA, bs.NAMA NM_BRG_SKA
			 , pbd.JUMLAH, s.NAMA NAMASATUAN, pbd.HARGA, pbd.DISKON, ((pbd.JUMLAH * pbd.HARGA) - pbd.DISKON) TOTAL
          , master.getHeaderKategoriBarang(''',KATEGORI,''') KATEGORI, inventory.getGolonganPerBarang(ib.ID) GOLONGAN
          , master.getHeaderLaporan(''',RUANGAN,''') INSTALASI
          , IF(',BARANG,'=0,''Semua'',(SELECT br.NAMA FROM inventory.barang br WHERE br.ID=',BARANG,')) BARANGHEADER
          , pb.NO_SP 
        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
         , inventory.penerimaan_barang_detil pbd
           LEFT JOIN inventory.barang_ska bs ON pbd.REF_BRG=bs.ID
         , inventory.barang ib
            LEFT JOIN inventory.satuan s ON ib.SATUAN=s.ID
            LEFT JOIN inventory.kategori ik ON ib.KATEGORI=ik.ID
            ',IF(PENGGOLONGAN=0,'',CONCAT(' LEFT JOIN inventory.penggolongan_barang gb ON gb.BARANG=ib.ID AND gb.CHECKED=1')),'
          , (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(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 pb.NO_SP, pb.FAKTUR, ib.ID, pbd.REF_BRG
      ');

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

END//
DELIMITER;