USE `inventory`;
-- --------------------------------------------------------
-- Versi server:                 8.0.11 - MySQL Community Server - GPL
-- OS Server:                    Win64
-- HeidiSQL Versi:               9.2.0.4947
-- --------------------------------------------------------


-- Dumping structure for procedure inventory.CetakStokRealGroupBarang
DROP PROCEDURE IF EXISTS `CetakStokRealGroupBarang`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `CetakStokRealGroupBarang`(IN `PTANGGAL` DATETIME)
BEGIN
	DECLARE VTANGGAL DATETIME;
	
	SET vTANGGAL = DATE_FORMAT(PTANGGAL,'%d-%m-%Y %H:%i:%s');
	SET @sqlText = CONCAT('
			SELECT
				inst.NAMA NAMAINST, inst.ALAMAT ALAMATINST
				, g.ID, g.DESKRIPSI NMGROUP, kt.NAMA NMKATEGORI, g.STOK_MIN, g.STOK_MAX, SUM(br.STOK) STOK_RS, ''',vTANGGAL,''' AS TGLCETAK
			FROM inventory.group_barang g
				LEFT JOIN inventory.kategori kt ON kt.ID = g.KATEGORI
				, inventory.mapping_group_barang mg
				, inventory.barang b
				, inventory.barang_ruangan br
				, (SELECT mp.NAMA, ai.PPK, mp.ALAMAT
					FROM aplikasi.instansi ai
						, master.ppk mp
					WHERE ai.PPK=mp.ID) inst
			WHERE br.BARANG = b.ID AND b.ID = mg.BARANG AND mg.`GROUP` = g.ID AND mg.STATUS = 1 AND g.STATUS = 1
			GROUP BY g.ID
			ORDER BY g.DESKRIPSI
			');

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