USE `laporan`;

-- Dumping structure for procedure laporan.LaporanBarangPerTransaksi
DELIMITER //
CREATE PROCEDURE `LaporanBarangPerTransaksi`(
	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,
	IN `PJENIS` TINYINT
)
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 
			PPK, NAMAINST, ALAMATINST, JENISLAPORAN, INSTALASI, KATEGORI, CARABAYARHEADER, BARANGHEADER,
		 	NAMAOBAT, KLP_PSEDIAAN, KODE_PSEDIA, KAT_BRG, GENERIK, FORMULARIUM, MERK, GOLONGAN,
		 	JENIS, JENISTRANSAKSI, TGLPELAYANAN, SUM(JUMLAH) JUMLAH, HRG_SATUAN, SUM(JUMLAH * HRG_SATUAN) TOTAL,
		 	NORM, NAMAPASIEN, DEPO, TAMBAH_ATAU_KURANG, GROUP_CONCAT(REF) REF, KET
		 	
		FROM 				
			(
				SELECT inst.PPK, inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST
						, CONCAT(''LAPORAN BARANG '',IF(jts.TAMBAH_ATAU_KURANG= ''+'',''MASUK'',''KELUAR''),'' PER TRANSAKSI'') JENISLAPORAN
						, master.getHeaderLaporan(''',RUANGAN,''') INSTALASI
						, master.getHeaderKategoriBarang(''',KATEGORI,''') KATEGORI
						, IF(',CARABAYAR,'=0,''Semua'',(SELECT ref.DESKRIPSI FROM master.referensi ref WHERE ref.ID=',CARABAYAR,' AND ref.JENIS=10)) CARABAYARHEADER
						, IF(',BARANG,'=0,''Semua'',(SELECT br.NAMA FROM inventory.barang br WHERE br.ID=',BARANG,')) BARANGHEADER
						, ib.ID , ib.NAMA NAMAOBAT, kp.NAMA KLP_PSEDIAAN, ib.KODE_PSEDIA, ik.NAMA KAT_BRG
						, CONCAT ( jts.DESKRIPSI, IF (jts.ID IN (53,54,60,61), CONCAT('' : '',IFNULL(tkr.DESKRIPSI, tkr1.DESKRIPSI)),'''')) JENISTRANSAKSI
						, IF(ib.JENIS_GENERIK=1,''GENERIK'',''NON GENERIK'') GENERIK, inventory.getGolonganPerBarang(ib.ID) GOLONGAN, fr.DESKRIPSI FORMULARIUM, mr.DESKRIPSI MERK, tsr.JENIS , DATE_FORMAT(tsr.TANGGAL,''%d-%m-%Y %H:%i:%s'') TGLPELAYANAN 
						, tsr.JUMLAH
						, IF(tsr.JENIS IN (''30'',''31'',''32''), inventory.getHargaJual(ib.ID, pd.HARGA_BARANG, pd.MARGIN, pd.PPN)
							,IF(tsr.JENIS IN (''33'',''35'',''64''),rt.TARIF
								, IF(tsr.JENIS IN (''51'',''67''), bp.HARGA_ESTIMASI
									, IF(tsr.JENIS IN (''21'',''22''), pbd.HARGA
										,inventory.getHargaPenerimaanTerakhirByTanggal(ib.ID, tsr.TANGGAL) 
											)))) HRG_SATUAN
						, LPAD(ps.NORM,8,''0'') NORM, pk.NOPEN, master.getNamaLengkap(ps.NORM) NAMAPASIEN
						, IF(tsr.JENIS IN (33,64), ra.DESKRIPSI, inventory.getKeteranganTransaksi(tsr.JENIS,tsr.REF)) RUANGASAL
						, pp.NOMOR, tsr.REF,jts.TAMBAH_ATAU_KURANG, r.DESKRIPSI DEPO 
						, IF(jts.ID IN (''21'',''22'',''24''),CONCAT(''No.FAKTUR : '', pb.FAKTUR),'''') KET
						
				FROM inventory.transaksi_stok_ruangan tsr
				     LEFT JOIN inventory.jenis_transaksi_stok jts ON tsr.JENIS=jts.ID
				     LEFT JOIN layanan.layanan_bon_sisa_farmasi lb ON lb.ID = tsr.REF AND tsr.JENIS=64
					  LEFT JOIN layanan.bon_sisa_farmasi bon ON bon.ID=lb.REF
					  LEFT JOIN layanan.retur_farmasi rf ON rf.ID=tsr.REF 
					  LEFT JOIN layanan.farmasi f ON f.ID = IFNULL(bon.REF,tsr.REF)
				     LEFT JOIN pendaftaran.kunjungan pk ON f.KUNJUNGAN=pk.NOMOR
				     LEFT JOIN layanan.order_resep o ON pk.REF=o.NOMOR
				     LEFT JOIN pendaftaran.kunjungan asal ON o.KUNJUNGAN=asal.NOMOR
				     LEFT JOIN master.ruangan ra ON asal.RUANGAN=ra.ID
				     LEFT JOIN pendaftaran.pendaftaran pp ON pk.NOPEN=pp.NOMOR
				     LEFT JOIN master.pasien ps ON pp.NORM=ps.NORM
				     LEFT JOIN pembayaran.rincian_tagihan rt ON f.ID=rt.REF_ID AND rt.JENIS=4
					  LEFT JOIN pendaftaran.penjamin pj ON pp.NOMOR=pj.NOPEN 
				     LEFT JOIN inventory.barang_produksi bp ON bp.ID = tsr.REF AND bp.`STATUS`!=0
				     LEFT JOIN inventory.penerimaan_barang_detil pbd ON pbd.ID = tsr.REF AND pbd.`STATUS`!=0
				     LEFT JOIN inventory.penerimaan_barang pb ON pb.ID=pbd.PENERIMAAN AND pbd.`STATUS`!=0
				     LEFT JOIN penjualan.penjualan_detil pd ON pd.ID = tsr.REF AND pd.`STATUS`!=0
				     LEFT JOIN inventory.transaksi_koreksi_detil tkd ON tkd.ID=tsr.REF AND tkd.`STATUS`!=0
				     LEFT JOIN inventory.transaksi_koreksi tk ON tk.ID=tkd.KOREKSI AND tk.`STATUS`!=0
				     LEFT JOIN `master`.referensi tkr ON tkr.ID= tk.ALASAN AND tkr.`STATUS`!=0 AND tkr.JENIS=900601
					  LEFT JOIN `master`.referensi tkr1 ON tkr1.ID= tk.ALASAN AND tkr1.`STATUS`!=0 AND tkr1.JENIS=900602
					, inventory.barang_ruangan br
					, master.ruangan r
					  LEFT JOIN master.referensi jk ON r.JENIS_KUNJUNGAN=jk.ID AND jk.JENIS=15
					, inventory.barang ib
					  LEFT JOIN inventory.kategori ik ON ib.KATEGORI=ik.ID
					  LEFT JOIN inventory.kelompok_psedia kp ON kp.ID=ib.KLP_PSEDIA AND kp.STATUS!=0
					  LEFT JOIN master.referensi fr ON ib.FORMULARIUM=fr.ID AND fr.JENIS=176 AND fr.STATUS!=0
					  LEFT JOIN master.referensi mr ON ib.MERK=mr.ID AND mr.JENIS=39 AND mr.STATUS!=0
					  ',IF(PENGGOLONGAN=0,'',CONCAT(' LEFT JOIN inventory.penggolongan_barang pb ON pb.BARANG=ib.ID AND pb.CHECKED=1')),'
					, (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
										FROM aplikasi.instansi ai
											, master.ppk mp
										WHERE ai.PPK=mp.ID) inst
				WHERE tsr.BARANG_RUANGAN=br.ID AND br.RUANGAN=r.ID AND r.JENIS=5 AND br.BARANG=ib.ID 
					 AND r.JENIS_KUNJUNGAN=',LAPORAN,' AND tsr.TANGGAL BETWEEN ''',TGLAWAL,''' AND ''',TGLAKHIR,'''
					 AND br.RUANGAN LIKE ''',vRUANGAN,'''
					 ',IF(PJENIS=1,'AND jts.TAMBAH_ATAU_KURANG= ''+'' ',' AND jts.TAMBAH_ATAU_KURANG=''-'''),'
					 ',IF(BARANG=0,'',CONCAT(' AND ib.ID=',BARANG)),'
					  ',IF(KATEGORI=0,'',CONCAT(' AND ik.ID LIKE ''',VKATEGORI,'''')),'
					  ',IF(JENISINVENTORY=0,'',CONCAT(' AND ik.ID LIKE ''',vJENISINVENTORY,'''')),'
					  ',IF(JENISKATEGORI=0,'',CONCAT(' AND ik.ID LIKE ''',vKATEGORI,'''')),'
					  ',IF(KATEGORIBARANG IN (0,10100),'',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 pb.PENGGOLONGAN=',PENGGOLONGAN)),'
				ORDER BY ib.ID, tsr.TANGGAL 
		) ab

	GROUP BY ID , JENIS, REF
						');

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

END//
DELIMITER ;