-- --------------------------------------------------------
-- Server OS:                    Linux
-- HeidiSQL Version:             12.0.0.6468
-- --------------------------------------------------------


USE `laundry`;

-- Dumping structure for procedure laundry.CetakDistribusiLaundry
DELIMITER //
CREATE PROCEDURE `CetakDistribusiLaundry`(
	IN `PNOMOR` CHAR(21),
	IN `JENIS` TINYINT






)
BEGIN
	
	/*JENIS 1: Infeksi , 2: Non Infeksi, 3: Distribusi*/
		

	SET @sqlText = CONCAT('
		SELECT inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST, DATE_FORMAT(dl.TANGGAL,''%d-%m-%Y %H:%i:%s'') TANGGAL, pnm.DESKRIPSI RUANGTERIMA, asl.DESKRIPSI RUANGASAL
		, br.NAMA, IF(pdl.KONDISI_BARANG=1,''Infeksi'',''Non Infeksi'') JENISBARANG
		, SUM(pdl.JUMLAH) JUMLAHPENERIMAAN, SUM(pbl.BERAT) BERATPENERIMAAN
		, SUM(pdl.JUMLAH-ddl.JUMLAH) JUMLAHDISTRIBUSI
		, SUM(ddl.JUMLAH) TIDAKLAYAK
	   , master.getNamaLengkapPegawai(usd.NIP) PENDISTRIBUSI
	   , master.getNamaLengkapPegawai(usp.NIP) PENERIMA
	   , dl.NOMOR
	FROM laundry.penerimaan_linen pl
		  LEFT JOIN laundry.penerimaan_detil_linen pdl ON pl.NOMOR=pdl.PENERIMAAN_LINEN AND pdl.`STATUS`!=0
		  LEFT JOIN laundry.penerimaan_berat_linen pbl ON pl.NOMOR=pbl.PENERIMAAN_LINEN AND pbl.`STATUS`!=0 AND pdl.KONDISI_BARANG=pbl.KONDISI_BARANG
		  LEFT JOIN inventory.barang br ON pdl.BARANG=br.ID
		  LEFT JOIN distribusi_linen dl ON pl.NOMOR=dl.PENERIMAAN_LINEN AND dl.`STATUS`=2
		  LEFT JOIN master.ruangan pnm ON dl.PENERIMA=pnm.ID
		  LEFT JOIN master.ruangan asl ON dl.ASAL=asl.ID
		  LEFT JOIN distribusi_detil_linen ddl ON dl.NOMOR=ddl.DISTRIBUSI_LINEN AND ddl.`STATUS`=2 AND ddl.PENERIMAAN_DETIL_LINEN=pdl.ID
		  LEFT JOIN aplikasi.pengguna usd ON dl.OLEH=usd.ID AND usd.`STATUS`!=0
        LEFT JOIN aplikasi.pengguna usp ON pl.OLEH=usp.ID
        , (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
					FROM aplikasi.instansi ai
						, master.ppk mp
					WHERE ai.PPK=mp.ID) inst
	WHERE pl.`STATUS`!=0 AND dl.NOMOR=''',PNOMOR,'''
	',IF(JENIS=4,'',IF(JENIS=1,CONCAT(' AND pdl.KONDISI_BARANG=1'),IF(JENIS=2,CONCAT(' AND pdl.KONDISI_BARANG=2'),CONCAT(' AND pdl.KONDISI_BARANG IN (1,2) AND dl.NOMOR IS NOT NULL')))),'
	',IF(JENIS=4,' ',CONCAT(' GROUP BY pdl.BARANG')),'
	');
	

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

-- Dumping structure for procedure laundry.CetakKegiatanLaundry
DELIMITER //
CREATE PROCEDURE `CetakKegiatanLaundry`(
	IN `PNOMOR` CHAR(21),
	IN `JENIS` TINYINT





)
BEGIN
	
	/*JENIS 1: Infeksi , 2: Non Infeksi, 3: Distribusi*/
		

	SET @sqlText = CONCAT('
		SELECT inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST, dl.TANGGAL, pnm.DESKRIPSI RUANGPENERIMA, asl.DESKRIPSI RUANGASAL
		, br.NAMA, IF(pdl.KONDISI_BARANG=1,''Infeksi'',''Non Infeksi'') JENISBARANG
		, SUM(pdl.JUMLAH) JUMLAHPENERIMAAN, SUM(pbl.BERAT) BERATPENERIMAAN
		, SUM(pdl.JUMLAH-ddl.JUMLAH) JUMLAHDISTRIBUSI
		, SUM(ddl.JUMLAH) TIDAKLAYAK
	   , master.getNamaLengkapPegawai(usd.NIP) PENDISTRIBUSI
	   , master.getNamaLengkapPegawai(usp.NIP) PENERIMA
	FROM laundry.penerimaan_linen pl
		  LEFT JOIN laundry.penerimaan_detil_linen pdl ON pl.NOMOR=pdl.PENERIMAAN_LINEN AND pdl.`STATUS`!=0
		  LEFT JOIN laundry.penerimaan_berat_linen pbl ON pl.NOMOR=pbl.PENERIMAAN_LINEN AND pbl.`STATUS`!=0 AND pdl.KONDISI_BARANG=pbl.KONDISI_BARANG
		  LEFT JOIN inventory.barang br ON pdl.BARANG=br.ID
		  LEFT JOIN distribusi_linen dl ON pl.NOMOR=dl.PENERIMAAN_LINEN AND dl.`STATUS`!=0
		  LEFT JOIN master.ruangan pnm ON dl.PENERIMA=pnm.ID
		  LEFT JOIN master.ruangan asl ON dl.ASAL=asl.ID
		  LEFT JOIN distribusi_detil_linen ddl ON dl.NOMOR=ddl.DISTRIBUSI_LINEN AND ddl.`STATUS`!=0 AND ddl.PENERIMAAN_DETIL_LINEN=pdl.ID
		  LEFT JOIN aplikasi.pengguna usd ON dl.OLEH=usd.ID AND usd.`STATUS`!=0
        LEFT JOIN aplikasi.pengguna usp ON pl.OLEH=usp.ID
        , (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
					FROM aplikasi.instansi ai
						, master.ppk mp
					WHERE ai.PPK=mp.ID) inst
	WHERE pl.`STATUS`!=0 AND dl.NOMOR=''',PNOMOR,'''
	',IF(JENIS=4,'',IF(JENIS=1,CONCAT(' AND pdl.KONDISI_BARANG=1'),IF(JENIS=2,CONCAT(' AND pdl.KONDISI_BARANG=2'),CONCAT(' AND pdl.KONDISI_BARANG IN (1,2) AND dl.NOMOR IS NOT NULL')))),'
	',IF(JENIS=4,' ',CONCAT(' GROUP BY pdl.BARANG')),'
	');
	

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

-- Dumping structure for procedure laundry.CetakPenerimaanLaundry
DELIMITER //
CREATE PROCEDURE `CetakPenerimaanLaundry`(
	IN `PNOMOR` CHAR(21),
	IN `JENIS` TINYINT
)
BEGIN
	
	/*JENIS 1: Infeksi , 2: Non Infeksi, 3: Distribusi*/
		

	SET @sqlText = CONCAT('
		SELECT inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST
		, br.NAMA BARANG, IF(pdl.KONDISI_BARANG=1,''Infeksi'',''Non Infeksi'') JENISBARANG
		, SUM(pdl.JUMLAH) JUMLAHPENERIMAAN, SUM(pbl.BERAT) BERATPENERIMAAN
		, ra.DESKRIPSI RUANGASAL
		, rp.DESKRIPSI RUANGTERIMA
		, master.getNamaLengkapPegawai(usp.NIP) PENERIMA
		, pl.NOMOR
		, DATE_FORMAT(pl.TANGGAL,''%d-%m-%Y %H:%i:%s'') TANGGAL
	FROM laundry.penerimaan_linen pl
		  LEFT JOIN laundry.penerimaan_detil_linen pdl ON pl.NOMOR=pdl.PENERIMAAN_LINEN AND pdl.`STATUS`!=0
		  LEFT JOIN laundry.penerimaan_berat_linen pbl ON pl.NOMOR=pbl.PENERIMAAN_LINEN AND pbl.`STATUS`!=0 AND pdl.KONDISI_BARANG=pbl.KONDISI_BARANG
		  LEFT JOIN inventory.barang br ON pdl.BARANG=br.ID
		  LEFT JOIN master.ruangan ra ON pl.ASAL=ra.ID
	     LEFT JOIN master.ruangan rp ON pl.PENERIMA=rp.ID
		  LEFT JOIN aplikasi.pengguna usp ON pl.OLEH=usp.ID
	        , (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
					FROM aplikasi.instansi ai
						, master.ppk mp
					WHERE ai.PPK=mp.ID) inst
	WHERE pl.`STATUS`!=0 AND pl.NOMOR=''',PNOMOR,'''
	',IF(JENIS=4,'',IF(JENIS=1,CONCAT(' AND pdl.KONDISI_BARANG=1'),IF(JENIS=2,CONCAT(' AND pdl.KONDISI_BARANG=2'),CONCAT(' AND pdl.KONDISI_BARANG IN (1,2) AND pl.NOMOR IS NOT NULL')))),'
	',IF(JENIS=4,' ',CONCAT(' GROUP BY pdl.BARANG')),'
	
	');
	

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

-- Dumping structure for procedure laundry.LaporanDistribusiLaundry
DELIMITER //
CREATE PROCEDURE `LaporanDistribusiLaundry`(
	IN `TGLAWAL` DATETIME,
	IN `TGLAKHIR` DATETIME,
	IN `RUANGAN` CHAR(10),
	IN `PEGAWAI` VARCHAR(30),
	IN `BARANG` INT




















,
	IN `JENIS` TINYINT





















)
BEGIN
	
	
	
	DECLARE vRUANGAN VARCHAR(11);
      
   SET vRUANGAN = CONCAT(RUANGAN,'%');
	
	SET @sqlText = CONCAT('
	SELECT inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST, dl.TANGGAL, pnm.DESKRIPSI RUANGPENERIMA, asl.DESKRIPSI RUANGASAL, br.NAMA, IF(pdl.KONDISI_BARANG=1,''Infeksi'',''Non Infeksi'') JENISBARANG
		, SUM(pdl.JUMLAH) JUMLAHPENERIMAAN, SUM(pbl.BERAT) BERATPENERIMAAN
		, SUM(pdl.JUMLAH-ddl.JUMLAH) JUMLAHDISTRIBUSI
		, '''' BERATDISTRIBUSI
		, master.getHeaderLaporan(''',RUANGAN,''') INSTALASI
		, IF(''',PEGAWAI,'''=''0'' OR ''',PEGAWAI,'''='''',''Semua'', master.getNamaLengkapPegawai(us.NIP)) PEGAWAIHEADER
		, IF(',BARANG,'=0,''Semua'', br.NAMA) BARANGHEADER
		, CONCAT(''LAPORAN'',IF(',JENIS,'=1,'' RINCIAN '','' REKAP ''),''PENDISTRIBUSIAN LINEN'') JENISLAPORAN
	FROM laundry.penerimaan_linen pl
		  LEFT JOIN laundry.penerimaan_detil_linen pdl ON pl.NOMOR=pdl.PENERIMAAN_LINEN AND pdl.`STATUS`!=0
		  LEFT JOIN laundry.penerimaan_berat_linen pbl ON pl.NOMOR=pbl.PENERIMAAN_LINEN AND pbl.`STATUS`!=0 AND pdl.KONDISI_BARANG=pbl.KONDISI_BARANG
		  LEFT JOIN inventory.barang br ON pdl.BARANG=br.ID
		  LEFT JOIN distribusi_linen dl ON pl.NOMOR=dl.PENERIMAAN_LINEN AND dl.`STATUS`=2
		  LEFT JOIN master.ruangan pnm ON dl.PENERIMA=pnm.ID
		  LEFT JOIN master.ruangan asl ON dl.ASAL=asl.ID
		  LEFT JOIN distribusi_detil_linen ddl ON dl.NOMOR=ddl.DISTRIBUSI_LINEN AND ddl.`STATUS`=2 AND ddl.PENERIMAAN_DETIL_LINEN=pdl.ID
		  LEFT JOIN aplikasi.pengguna us ON dl.OLEH=us.ID AND us.`STATUS`!=0
        LEFT JOIN master.pegawai mp ON us.NIP=mp.NIP AND mp.`STATUS`!=0
        , (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
					FROM aplikasi.instansi ai
						, master.ppk mp
					WHERE ai.PPK=mp.ID) inst
	WHERE pl.`STATUS`!=0 AND dl.TANGGAL BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
		AND dl.PENERIMA LIKE ''',vRUANGAN,''' AND dl.NOMOR IS NOT NULL
			',IF(PEGAWAI='0' OR PEGAWAI='','',CONCAT(' AND mp.NIP=''',PEGAWAI,'''')),'
		',IF(BARANG=0,'',CONCAT(' AND br.ID=',BARANG)),'
	',IF(JENIS=1,CONCAT(' GROUP BY DATE(dl.TANGGAL), asl.ID, pdl.KONDISI_BARANG, pdl.BARANG'),CONCAT(' GROUP BY asl.ID, pdl.KONDISI_BARANG,pdl.BARANG')),'
	',IF(JENIS=1,CONCAT(' ORDER BY dl.TANGGAL, asl.DESKRIPSI'),CONCAT(' ORDER BY asl.DESKRIPSI')),' 
	');
	

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

-- Dumping structure for procedure laundry.LaporanKegiatanPegawai
DELIMITER //
CREATE PROCEDURE `LaporanKegiatanPegawai`(
	IN `TGLAWAL` DATETIME,
	IN `TGLAKHIR` DATETIME,
	IN `RUANGAN` CHAR(10),
	IN `PEGAWAI` VARCHAR(30),
	IN `BARANG` INT





)
BEGIN
	DECLARE vRUANGAN VARCHAR(11);
      
   SET vRUANGAN = CONCAT(RUANGAN,'%');
	
	SET @sqlText = CONCAT('
	SELECT inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST, master.getNamaLengkapPegawai(us.NIP) PEGAWAI, r.DESKRIPSI RUANGAN
		, SUM(IF(dk.REF=1, dk.PENCUCIAN_INFEKSI,0)) PENCUCIAN_INFEKSI_LBR
		, SUM(IF(dk.REF=2, dk.PENCUCIAN_INFEKSI,0)) PENCUCIAN_INFEKSI_KG
		, SUM(IF(dk.REF=1, dk.PENCUCIAN_NONINFEKSI,0)) PENCUCIAN_NONINFEKSI_LBR
		, SUM(IF(dk.REF=2, dk.PENCUCIAN_NONINFEKSI,0)) PENCUCIAN_NONINFEKSI_KG
		, SUM(IF(dk.REF=1, dk.PERENDAMAN,0)) PERENDAMAN_LBR
		, SUM(IF(dk.REF=2, dk.PERENDAMAN,0)) PERENDAMAN_KG
		, SUM(IF(dk.REF=1, dk.PENGERINGAN,0)) PENGERINGAN_LBR
		, SUM(IF(dk.REF=2, dk.PENGERINGAN,0)) PENGERINGAN_KG
		, SUM(IF(dk.REF=1, dk.PENYETRIKAAN,0)) PENYETRIKAAN_LBR
		, SUM(IF(dk.REF=2, dk.PENYETRIKAAN,0)) PENYETRIKAAN_KG
		, SUM(IF(dk.REF=1, dk.PELIPATAN,0)) PELIPATAN_LBR
		, SUM(IF(dk.REF=2, dk.PELIPATAN,0)) PELIPATAN_KG
		, SUM(IF(dk.REF=1, dk.PENYIMPANAN,0)) PENYIMPANAN_LBR
		, SUM(IF(dk.REF=2, dk.PENYIMPANAN,0)) PENYIMPANAN_KG
		, SUM(IF(dk.REF=1, dk.PENJAHITAN,0)) PENJAHITAN_LBR
		, SUM(IF(dk.REF=2, dk.PENJAHITAN,0)) PENJAHITAN_KG
		, master.getHeaderLaporan(''',RUANGAN,''') INSTALASI
		, IF(''',PEGAWAI,'''=''0'' OR ''',PEGAWAI,'''='''',''Semua'', master.getNamaLengkapPegawai(us.NIP)) PEGAWAIHEADER
	  , CONCAT(''LAPORAN KEGIATAN PEGAWAI'') JENISLAPORAN
	FROM laundry.kegiatan k
	      LEFT JOIN aplikasi.pengguna us ON k.OLEH=us.ID AND us.`STATUS`!=0
	      LEFT JOIN master.ruangan r ON k.RUANGAN=r.ID
	      LEFT JOIN master.pegawai mp ON us.NIP=mp.NIP AND mp.`STATUS`!=0
		, laundry.detil_kegiatan dk
		, (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
					FROM aplikasi.instansi ai
						, master.ppk mp
					WHERE ai.PPK=mp.ID) inst
	WHERE k.`STATUS`=2 AND k.NOMOR=dk.KEGIATAN 
	   AND k.TANGGAL BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
		AND k.RUANGAN LIKE ''',vRUANGAN,'''
			',IF(PEGAWAI='0' OR PEGAWAI='','',CONCAT(' AND mp.NIP=''',PEGAWAI,'''')),'
	GROUP BY k.OLEH
	ORDER BY PEGAWAI
	');
	

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

-- Dumping structure for procedure laundry.LaporanKegiatanPegawaiPertransaksi
DELIMITER //
CREATE PROCEDURE `LaporanKegiatanPegawaiPertransaksi`(
	IN `TGLAWAL` DATETIME,
	IN `TGLAKHIR` DATETIME,
	IN `RUANGAN` CHAR(10),
	IN `PEGAWAI` VARCHAR(30),
	IN `BARANG` INT







)
BEGIN
	DECLARE vRUANGAN VARCHAR(11);
      
   SET vRUANGAN = CONCAT(RUANGAN,'%');
	
	SET @sqlText = CONCAT('
	SELECT inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST, master.getNamaLengkapPegawai(us.NIP) PEGAWAI, k.TANGGAL, r.DESKRIPSI RUANGAN
		, SUM(IF(dk.REF=1, dk.PENCUCIAN_INFEKSI,0)) PENCUCIAN_INFEKSI_LBR
		, SUM(IF(dk.REF=2, dk.PENCUCIAN_INFEKSI,0)) PENCUCIAN_INFEKSI_KG
		, SUM(IF(dk.REF=1, dk.PENCUCIAN_NONINFEKSI,0)) PENCUCIAN_NONINFEKSI_LBR
		, SUM(IF(dk.REF=2, dk.PENCUCIAN_NONINFEKSI,0)) PENCUCIAN_NONINFEKSI_KG
		, SUM(IF(dk.REF=1, dk.PERENDAMAN,0)) PERENDAMAN_LBR
		, SUM(IF(dk.REF=2, dk.PERENDAMAN,0)) PERENDAMAN_KG
		, SUM(IF(dk.REF=1, dk.PENGERINGAN,0)) PENGERINGAN_LBR
		, SUM(IF(dk.REF=2, dk.PENGERINGAN,0)) PENGERINGAN_KG
		, SUM(IF(dk.REF=1, dk.PENYETRIKAAN,0)) PENYETRIKAAN_LBR
		, SUM(IF(dk.REF=2, dk.PENYETRIKAAN,0)) PENYETRIKAAN_KG
		, SUM(IF(dk.REF=1, dk.PELIPATAN,0)) PELIPATAN_LBR
		, SUM(IF(dk.REF=2, dk.PELIPATAN,0)) PELIPATAN_KG
		, SUM(IF(dk.REF=1, dk.PENYIMPANAN,0)) PENYIMPANAN_LBR
		, SUM(IF(dk.REF=2, dk.PENYIMPANAN,0)) PENYIMPANAN_KG
		, SUM(IF(dk.REF=1, dk.PENJAHITAN,0)) PENJAHITAN_LBR
		, SUM(IF(dk.REF=2, dk.PENJAHITAN,0)) PENJAHITAN_KG
		, master.getHeaderLaporan(''',RUANGAN,''') INSTALASI
		, IF(''',PEGAWAI,'''=''0'' OR ''',PEGAWAI,'''='''',''Semua'', master.getNamaLengkapPegawai(us.NIP)) PEGAWAIHEADER
	  , CONCAT(''LAPORAN KEGIATAN PEGAWAI'') JENISLAPORAN
	FROM laundry.kegiatan k
	      LEFT JOIN aplikasi.pengguna us ON k.OLEH=us.ID AND us.`STATUS`!=0
	      LEFT JOIN master.ruangan r ON k.RUANGAN=r.ID
	      LEFT JOIN master.pegawai mp ON us.NIP=mp.NIP AND mp.`STATUS`!=0
		, laundry.detil_kegiatan dk
		, (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
					FROM aplikasi.instansi ai
						, master.ppk mp
					WHERE ai.PPK=mp.ID) inst
	WHERE k.`STATUS`=2 AND k.NOMOR=dk.KEGIATAN 
	   AND k.TANGGAL BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
		AND k.RUANGAN LIKE ''',vRUANGAN,'''
			',IF(PEGAWAI='0' OR PEGAWAI='','',CONCAT(' AND mp.NIP=''',PEGAWAI,'''')),'
	GROUP BY k.OLEH, k.NOMOR
	ORDER BY PEGAWAI, k.TANGGAL
	');
	

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

-- Dumping structure for procedure laundry.LaporanPenerimaanLaundry
DELIMITER //
CREATE PROCEDURE `LaporanPenerimaanLaundry`(
	IN `TGLAWAL` DATETIME,
	IN `TGLAKHIR` DATETIME,
	IN `RUANGAN` CHAR(10),
	IN `PEGAWAI` VARCHAR(30),
	IN `BARANG` INT,
	IN `JENIS` TINYINT








)
BEGIN
	
	
		
	DECLARE vRUANGAN VARCHAR(11);
      
   SET vRUANGAN = CONCAT(RUANGAN,'%');
	
	SET @sqlText = CONCAT('
	SELECT inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST, pl.TANGGAL, pnm.DESKRIPSI RUANGPENERIMA, asl.DESKRIPSI RUANGASAL, br.NAMA, IF(pdl.KONDISI_BARANG=1,''Infeksi'',''Non Infeksi'') JENISBARANG
		, SUM(pdl.JUMLAH) JUMLAHPENERIMAAN, SUM(pbl.BERAT) BERATPENERIMAAN
		, master.getHeaderLaporan(''',RUANGAN,''') INSTALASI
		, IF(''',PEGAWAI,'''=''0'' OR ''',PEGAWAI,'''='''',''Semua'', master.getNamaLengkapPegawai(us.NIP)) PEGAWAIHEADER
		, IF(',BARANG,'=0,''Semua'', br.NAMA) BARANGHEADER
		, CONCAT(''LAPORAN'',IF(',JENIS,'=1,'' RINCIAN '','' REKAP ''),''PENERIMAAN LINEN'') JENISLAPORAN, pnm.ID
	FROM laundry.penerimaan_linen pl
		  LEFT JOIN laundry.penerimaan_detil_linen pdl ON pl.NOMOR=pdl.PENERIMAAN_LINEN AND pdl.`STATUS`!=0
		  LEFT JOIN laundry.penerimaan_berat_linen pbl ON pl.NOMOR=pbl.PENERIMAAN_LINEN AND pbl.`STATUS`!=0 AND pdl.KONDISI_BARANG=pbl.KONDISI_BARANG
		  LEFT JOIN inventory.barang br ON pdl.BARANG=br.ID
		  LEFT JOIN master.ruangan pnm ON pl.PENERIMA=pnm.ID
		  LEFT JOIN master.ruangan asl ON pl.ASAL=asl.ID
		  LEFT JOIN aplikasi.pengguna us ON pl.OLEH=us.ID AND us.`STATUS`!=0
        LEFT JOIN master.pegawai mp ON us.NIP=mp.NIP AND mp.`STATUS`!=0
        , (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
					FROM aplikasi.instansi ai
						, master.ppk mp
					WHERE ai.PPK=mp.ID) inst
	WHERE pl.`STATUS`!=0 AND pl.TANGGAL BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
		AND pl.PENERIMA LIKE ''',vRUANGAN,'''
		',IF(PEGAWAI='0' OR PEGAWAI='','',CONCAT(' AND mp.NIP=''',PEGAWAI,'''')),'
		',IF(BARANG=0,'',CONCAT(' AND br.ID=',BARANG)),'
	',IF(JENIS=1,CONCAT(' GROUP BY DATE(pl.TANGGAL), asl.ID, pdl.KONDISI_BARANG, pdl.BARANG'),CONCAT(' GROUP BY asl.ID, pdl.KONDISI_BARANG, pdl.BARANG')),'
	',IF(JENIS=1,CONCAT(' ORDER BY pl.TANGGAL, asl.DESKRIPSI'),CONCAT(' ORDER BY asl.DESKRIPSI')),' 
	');
	

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

