<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Report_rekapitulasi_mediasi_perkara extends CI_Model{
	
	function get_info_pn(){
		try {
			$this->db->select('*');
			$this->db->where('id >=', 61); 
			return $this->db->get('sys_config');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	function getJenisPerkara($idalurperkara){
		if(empty($idalurperkara)) return '';
		try {
			$this->db->where('id',$idalurperkara);
			return $this->db->get('alur_perkara')->row()->nama;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_tahun($idalurperkara){
		try {
			return $this->db->query('SELECT YEAR(min(tanggal_pendaftaran)) AS years FROM perkara WHERE alur_perkara_id IN ('.$idalurperkara.');');
		} catch (Exception $e) {
			
		}
	}

	function get_rekap($idalurperkara,$tahun){
		try {
			$sql="SELECT 
						p.nomor_perkara,
						p.`jenis_perkara_nama`,
						DATE_FORMAT(m.`penetapan_penunjukan_mediator`,'%d-%m-%Y') as penetapan_penunjukan_mediator,
						DATE_FORMAT(j.`tanggal_mediasi`,'%d-%m-%Y') as tanggal_mediasi,
						if(m.`tgl_laporan_mediator` is null,'-',DATE_FORMAT(m.`tgl_laporan_mediator`,'%d-%m-%Y')) as tgl_laporan_mediator,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=1,1,'-') AS a1,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=2,1,'-') AS a2,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=3,1,'-') AS a3,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=4,1,'-') AS a4,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=5,1,'-') AS a5,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=6,1,'-') AS a6,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=7,1,'-') AS a7,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=8,1,'-') AS a8,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=9,1,'-') AS a9,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=10,1,'-') AS a10,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=11,1,'-') AS a11,
						IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))) && MONTH(keputusan_mediasi)=12,1,'-') AS a12,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=1,1,'-') AS s1,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=2,1,'-') AS s2,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=3,1,'-') AS s3,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=4,1,'-') AS s4,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=5,1,'-') AS s5,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=6,1,'-') AS s6,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=7,1,'-') AS s7,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=8,1,'-') AS s8,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=9,1,'-') AS s9,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=10,1,'-') AS s10,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=11,1,'-') AS s11,
						IF(hasil_mediasi = 'S' && MONTH(keputusan_mediasi)=12,1,'-') AS s12,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=1,1,'-') AS c1,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=2,1,'-') AS c2,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=3,1,'-') AS c3,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=4,1,'-') AS c4,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=5,1,'-') AS c5,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=6,1,'-') AS c6,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=7,1,'-') AS c7,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=8,1,'-') AS c8,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=9,1,'-') AS c9,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=10,1,'-') AS c10,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=11,1,'-') AS c11,
						IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))) && MONTH(keputusan_mediasi)=12,1,'-') AS c12,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=1,1,'-') AS t1,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=2,1,'-') AS t2,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=3,1,'-') AS t3,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=4,1,'-') AS t4,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=5,1,'-') AS t5,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=6,1,'-') AS t6,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=7,1,'-') AS t7,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=8,1,'-') AS t8,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=9,1,'-') AS t9,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=10,1,'-') AS t10,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=11,1,'-') AS t11,
						IF(hasil_mediasi = 'T' && MONTH(keputusan_mediasi)=12,1,'-') AS t12,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=1,1,'-') AS d1,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=2,1,'-') AS d2,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=3,1,'-') AS d3,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=4,1,'-') AS d4,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=5,1,'-') AS d5,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=6,1,'-') AS d6,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=7,1,'-') AS d7,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=8,1,'-') AS d8,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=9,1,'-') AS d9,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=10,1,'-') AS d10,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=11,1,'-') AS d11,
						IF(hasil_mediasi = 'D' && MONTH(keputusan_mediasi)=12,1,'-') AS d12,
						@a:=IF((hasil_mediasi = 'Y1' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id NOT IN (7,37,67)))),1,'-') AS a,
						CONCAT(round(@a/1*100),'%') AS p_a,
						@s:=IF(hasil_mediasi = 'S',1,'-') AS s,
						CONCAT(round(@s/1*100),'%') AS p_s,
						@c:=IF((hasil_mediasi = 'Y2' OR hasil_mediasi ='Y') AND ((put.tanggal_putusan IS NULL OR (put.tanggal_putusan IS NOT NULL AND put.status_putusan_id IN (7,37,67)))),1,'-') AS c,
						CONCAT(round(@c/1*100),'%') AS p_c,
						@t:=IF(hasil_mediasi = 'T',1,'-') AS t,
						CONCAT(round(@t/1*100),'%') AS p_t,
						@d:=IF(hasil_mediasi = 'D',1,'-') AS d,
						CONCAT(round(@d/1*100),'%') AS p_d
					FROM perkara_mediasi AS m
					JOIN perkara AS p ON m.`perkara_id` = p.`perkara_id` and alur_perkara_id = $idalurperkara
					LEFT JOIN perkara_putusan AS put ON p.perkara_id = put.perkara_id
					LEFT JOIN perkara_jadwal_mediasi AS j ON m.`mediasi_id` = j.`mediasi_id` AND j.`urutan` = 1
					WHERE m.tahapan_id = 10 AND YEAR(m.`penetapan_penunjukan_mediator`) =$tahun";
		return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}

	function get_stat_perkara($period,$idalurperkara){
		try {
			return $this->db->query("
				SELECT alur_perkara_id,alur_perkara_nama,
				SUM(
					CASE 
						WHEN (tanggal_putusan IS NULL OR LEFT(tanggal_putusan,7)>='".$period."') AND LEFT(tanggal_pendaftaran,7) < '".$period."' THEN 1
						ELSE 0
						END
					) AS sisa,
				SUM(
					CASE 
						WHEN (tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7)<'".$period."') AND LEFT(tanggal_pendaftaran,7) < '".$period."' AND 
								(tanggal_minutasi IS NULL OR LEFT(tanggal_minutasi,7)>='".$period."') THEN 1
						ELSE 0
						END
					) AS putus_blm_minut,
				SUM(
					CASE 
						WHEN (keputusan_mediasi IS NOT NULL AND LEFT(keputusan_mediasi,7)<'".$period."') AND LEFT(tanggal_pendaftaran,7) < '".$period."' AND 
								(tanggal_minutasi IS NULL OR LEFT(tanggal_minutasi,7)>='".$period."') AND hasil_mediasi='Y' THEN 1
						ELSE 0
						END
					) AS med_blm_minut,
				SUM(
					CASE 
						WHEN LEFT(tanggal_pendaftaran,7) = '".$period."' THEN 1
						ELSE 0
						END
					) AS masuk,
				SUM(
					CASE 
						WHEN (penetapan_majelis_hakim IS NULL OR LEFT(penetapan_majelis_hakim,7)>'".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS blm_majelis,
				SUM(
					CASE 
						WHEN (tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7)='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS putus,
				SUM(
					CASE 
						WHEN (tanggal_minutasi IS NOT NULL AND LEFT(tanggal_minutasi,7)='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS minut,
				SUM(
					CASE 
						WHEN (tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7)<='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' AND 
								(tanggal_minutasi IS NULL OR LEFT(tanggal_minutasi,7)>'".$period."') THEN 1
						WHEN (LEFT(mediasi_berhasil,7)<='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' AND 
								(tanggal_minutasi IS NULL OR LEFT(tanggal_minutasi,7)>='".$period."') THEN 1
						ELSE 0
						END
					) AS blm_minut,
				SUM(
					CASE 
						WHEN (tanggal_cabut IS NOT NULL AND LEFT(tanggal_cabut,7)='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS cabut,
				SUM(
					CASE 
						WHEN (tanggal_gugur IS NOT NULL AND LEFT(tanggal_gugur,7)='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS gugur,
				SUM(
					CASE 
						WHEN LEFT(tanggal_pendaftaran,7) <= '".$period."' AND hasil_mediasi='Y' AND status_mediator='H' AND LEFT(mediasi_berhasil,7) = '".$period."'  THEN 1
						ELSE 0
						END
					) AS mediator_hakim_berhasil,
					SUM(
					CASE 
						WHEN LEFT(tanggal_pendaftaran,7) <= '".$period."' AND hasil_mediasi='N' AND status_mediator='H' AND LEFT(mediasi_berhasil,7) = '".$period."'  THEN 1
						ELSE 0
						END
					) AS mediator_hakim_gagal
				FROM v_perkara
				WHERE alur_perkara_id IN (".$idalurperkara.")
				AND LEFT(tanggal_pendaftaran,7) <='".$period."';
				");
		} catch (Exception $e) {
			
		}
	}

	function get_stat_perkara_bln($period,$idalurperkara){
		try {
			return $this->db->query("
				SELECT jenis_perkara_id,jenis_perkara_nama,
				SUM(
					CASE 
						WHEN (tanggal_putusan IS NULL OR LEFT(tanggal_putusan,7)>='".$period."') AND LEFT(tanggal_pendaftaran,7) < '".$period."' THEN 1
						ELSE 0
						END
					) AS sisa,
				SUM(
					CASE 
						WHEN (tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7)<'".$period."') AND LEFT(tanggal_pendaftaran,7) < '".$period."' AND 
								(tanggal_minutasi IS NULL OR LEFT(tanggal_minutasi,7)>='".$period."') THEN 1
						ELSE 0
						END
					) AS putus_blm_minut,
				SUM(
					CASE 
						WHEN (keputusan_mediasi IS NOT NULL AND LEFT(keputusan_mediasi,7)<'".$period."') AND LEFT(tanggal_pendaftaran,7) < '".$period."' AND 
								(tanggal_minutasi IS NULL OR LEFT(tanggal_minutasi,7)>='".$period."') AND hasil_mediasi='Y' THEN 1
						ELSE 0
						END
					) AS med_blm_minut,
				SUM(
					CASE 
						WHEN LEFT(tanggal_pendaftaran,7) = '".$period."' THEN 1
						ELSE 0
						END
					) AS masuk,
				SUM(
					CASE 
						WHEN (penetapan_majelis_hakim IS NULL OR LEFT(penetapan_majelis_hakim,7)>'".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS blm_majelis,
				SUM(
					CASE 
						WHEN (tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7)='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS putus,
				SUM(
					CASE 
						WHEN (tanggal_minutasi IS NOT NULL AND LEFT(tanggal_minutasi,7)='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS minut,
				SUM(
					CASE 
						WHEN (tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7)<='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' AND 
								(tanggal_minutasi IS NULL OR LEFT(tanggal_minutasi,7)>'".$period."') THEN 1
						WHEN (LEFT(mediasi_berhasil,7)<='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' AND 
								(tanggal_minutasi IS NULL OR LEFT(tanggal_minutasi,7)>='".$period."') THEN 1
						ELSE 0
						END
					) AS blm_minut,
				SUM(
					CASE 
						WHEN (tanggal_cabut IS NOT NULL AND LEFT(tanggal_cabut,7)='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS cabut,
				SUM(
					CASE 
						WHEN (tanggal_gugur IS NOT NULL AND LEFT(tanggal_gugur,7)='".$period."') AND LEFT(tanggal_pendaftaran,7) <= '".$period."' THEN 1
						ELSE 0
						END
					) AS gugur,
				SUM(
					CASE 
						WHEN LEFT(tanggal_pendaftaran,7) <= '".$period."' AND hasil_mediasi='Y' AND LEFT(mediasi_berhasil,7) = '".$period."'  THEN 1
						ELSE 0
						END
					) AS mediasi_berhasil,
					SUM(
					CASE 
						WHEN LEFT(tanggal_pendaftaran,7) <= '".$period."' AND hasil_mediasi='N' AND LEFT(mediasi_berhasil,7) = '".$period."'  THEN 1
						ELSE 0
						END
					) AS mediasi_gagal
				FROM v_perkara
				WHERE alur_perkara_id IN (".$idalurperkara.")
				AND LEFT(tanggal_pendaftaran,7) <='".$period."'
				GROUP BY jenis_perkara_id;");
		} catch (Exception $e) {
			
		}
	}
	function get_lap_perkara($period,$idalurperkara){
		try {
			return $this->db->query("SELECT 
						  mp.id,
						  mp.nama_gelar, 
						  vsum.* 
						  FROM mediator AS mp
						        LEFT JOIN (SELECT p.mediator_id
						       
						       ,SUM(LEFT(dimulai_mediasi,7) = '".$period."') AS perkara_mediasi
						       ,SUM(LEFT(keputusan_mediasi,7) = '".$period."' AND hasil_mediasi='Y' AND tanggal_cabut IS NULL) AS berhasil_mediasi_akta
						       ,((SUM(LEFT(keputusan_mediasi,7) = '".$period."' AND hasil_mediasi='Y' AND tanggal_cabut IS NULL)/SUM(LEFT(dimulai_mediasi,7) = '".$period."'))*100) AS persen_akta
						       ,SUM(LEFT(keputusan_mediasi,7) = '".$period."' AND hasil_mediasi='S') AS berhasil_mediasi_sebagian
						       ,((SUM(LEFT(keputusan_mediasi,7) = '".$period."' AND hasil_mediasi='S')/SUM(LEFT(dimulai_mediasi,7) = '".$period."'))*100) AS persen_sebagian
						       ,SUM(LEFT(keputusan_mediasi,7)='".$period."' AND hasil_mediasi='Y' AND status_putusan_id=7 AND tanggal_cabut IS NOT NULL) AS berhasil_mediasi_cabut
						       ,((SUM(LEFT(keputusan_mediasi,7)='".$period."' AND hasil_mediasi='Y' AND status_putusan_id=7 AND tanggal_cabut IS NOT NULL))/SUM(LEFT(dimulai_mediasi,7)='".$period."'))*100 AS persen_cabut       
						       ,SUM(LEFT(keputusan_mediasi,7) = '".$period."' AND hasil_mediasi='T') AS tidak_berhasil_mediasi
						       ,((SUM(LEFT(keputusan_mediasi,7) = '".$period."' AND hasil_mediasi='T')/SUM(LEFT(dimulai_mediasi,7) = '".$period."'))*100) AS persen_tdk_berhasil_mediasi
						       ,SUM(LEFT(keputusan_mediasi,7) = '".$period."' AND hasil_mediasi='D') AS tidak_dpt_dilaksanakan
						       ,((SUM(LEFT(keputusan_mediasi,7) = '".$period."' AND hasil_mediasi='D')/SUM(LEFT(dimulai_mediasi,7) = '".$period."'))*100) AS persen_tdk_laksana
						       
						       FROM 
						       v_perkara AS p where alur_perkara_id='".$idalurperkara."' GROUP BY p.mediator_id) AS vsum
						       ON mp.id =  vsum.mediator_id

						        WHERE mp.aktif='Y'
						        ORDER BY mp.id;
						");
		} catch (Exception $e) {
			
		}
	}
}