-- --------------------------------------------------------
-- Server OS:                    Linux
-- HeidiSQL Version:             11.2.0.6213
-- --------------------------------------------------------


-- Dumping database structure for mutu
USE `mutu`;

-- Dumping structure for procedure mutu.laporanDetailDataiIndikator
DROP PROCEDURE IF EXISTS `laporanDetailDataiIndikator`;
DELIMITER //
CREATE PROCEDURE `laporanDetailDataiIndikator`(
	IN `PINDIKATOR` INT,
	IN `PRUANGAN` CHAR(20),
	IN `PTGL_AWAL` VARCHAR(50),
	IN `PTGL_AKHIR` VARCHAR(50)
)
BEGIN
	DECLARE VBULANT INT DEFAULT 0;
	DECLARE VCOUNT DECIMAL(10,2) DEFAULT 0;
	
	SELECT
		COUNT(*) INTO VBULANT
	FROM (
		SELECT 
			DATE_FORMAT(di.TANGGAL,'%Y-%m') GROUP_BULAN
		FROM master.tanggal di
		WHERE di.TANGGAL BETWEEN PTGL_AWAL AND PTGL_AKHIR
		GROUP BY  DATE_FORMAT(di.TANGGAL,'%Y-%m')
	)tgl;
	
	SELECT SUM(t.REALISASI) INTO VCOUNT
	FROM (
		SELECT 
				(SUM(di.NUM) / SUM(di.DENUM)) * 100 REALISASI
		FROM mutu.data_indikator di
		WHERE di.INDIKATOR = PINDIKATOR 
		AND di.RUANGAN LIKE CONCAT(PRUANGAN,'%')
		AND di.TANGGAL BETWEEN PTGL_AWAL AND PTGL_AKHIR
		AND di.`STATUS` != 0
		GROUP BY di.RUANGAN, DATE_FORMAT(di.TANGGAL,'%Y-%m')
	)t;
	
	SELECT
		i.ID ID_INDIKATOR,
		CONCAT(i.KODE,' - ', i.NAMA) DESK_INDIKATOR,
		tgl.GROUP_BULAN,
		ru.ID ID_RUANGAN,
		ru.DESKRIPSI DESK_RUANGAN,
		IF(dt_in.T_NUM IS NULL, 0, dt_in.T_NUM) T_NUM,
		IF(dt_in.T_DENUM IS NULL, 0, dt_in.T_DENUM) T_DENUM,	
		IF(dt_in.REALISASI IS NULL, 0, dt_in.REALISASI) REALISASI,
		IF(ROUND(VCOUNT / VBULANT,2) IS NULL, 0, ROUND(VCOUNT / VBULANT,2)) RATA_RATA,
		i.TARGET
	FROM (
		SELECT 
			DATE_FORMAT(di.TANGGAL,'%Y-%m') GROUP_BULAN
		FROM master.tanggal di
		WHERE di.TANGGAL BETWEEN PTGL_AWAL AND PTGL_AKHIR
		GROUP BY DATE_FORMAT(di.TANGGAL,'%Y-%m')
	)tgl 
	LEFT JOIN (
		SELECT 
			DATE_FORMAT(di.TANGGAL,'%Y-%m') GROUP_BULAN,
			di.RUANGAN,
			SUM(di.NUM) T_NUM,
			SUM(di.DENUM) T_DENUM,
			ROUND((SUM(di.NUM) / SUM(di.DENUM)) * 100, 2) REALISASI,
			di.INDIKATOR
		FROM mutu.data_indikator di
		WHERE di.INDIKATOR = PINDIKATOR 
		AND di.RUANGAN LIKE CONCAT(PRUANGAN,'%')
		AND di.TANGGAL BETWEEN PTGL_AWAL AND PTGL_AKHIR
		AND di.`STATUS` != 0
		GROUP BY di.RUANGAN, DATE_FORMAT(di.TANGGAL,'%Y-%m')
	) dt_in ON dt_in.GROUP_BULAN = tgl.GROUP_BULAN
	LEFT JOIN `master`.ruangan ru ON ru.ID = dt_in.RUANGAN
	,(SELECT 
			* 
		FROM mutu.indikator i WHERE i.ID = PINDIKATOR) i
	GROUP BY dt_in.RUANGAN, tgl.GROUP_BULAN; 

END//
DELIMITER ;

