DROP PROCEDURE IF EXISTS inventory.CetakMappingGroupBarang;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `inventory`.`CetakMappingGroupBarang`(
	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
	SET @sqlText = CONCAT('
			SELECT
				inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST
				, mg.BARANG ID_BARANG
				, b.NAMA NMBRG
				, mr.DESKRIPSI MERK
				, p.NAMA NMPENYEDIA 
				, mg.BARANG, mr.DESKRIPSI
				, kt.NAMA NMKATEGORI
				, g.ID ID_GROUP_BRG, g.DESKRIPSI NMGROUP, g.STOK_MIN, g.STOK_MAX
				, DATE_FORMAT(LOCALTIME,''%d-%m-%Y %H:%i:%s'') AS TGLCETAK
				, DATE_FORMAT(mg.INPUT_TIME,''%d-%m-%Y %H:%i:%s'') TGLINPUT, DATE_FORMAT(mg.UPDATE_TIME,''%d-%m-%Y %H:%i:%s'') TGLUPDATE
			FROM inventory.group_barang g
				LEFT JOIN inventory.kategori kt ON kt.ID = g.KATEGORI 
				, inventory.mapping_group_barang mg
				, inventory.barang b
				LEFT JOIN master.referensi mr ON b.MERK=mr.ID AND mr.JENIS=39
				LEFT JOIN inventory.penerimaan_barang_detil pbd ON pbd.REF_BRG=b.ID AND pbd.`STATUS`=2
				LEFT JOIN inventory.penerimaan_barang pb ON pb.ID=pbd.PENERIMAAN AND pb.`STATUS`=2
				LEFT JOIN inventory.penyedia p ON p.ID=pb.REKANAN AND p.`STATUS`!=0
				, (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
					FROM aplikasi.instansi ai
						, master.ppk mp
					WHERE ai.PPK=mp.ID) inst
			WHERE   
				b.ID = mg.BARANG AND mg.`GROUP` = g.ID 
				AND b.`STATUS`=1 AND mg.STATUS = 1 AND g.STATUS = 1
			ORDER BY g.NAMA, b.NAMA
			');

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