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

class Report_rekapitulasi_mediasi_pihak 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
						name_id 
						,v.mediasi_pihak
						,v.mediasi_L
						,IF(IFNULL(v.mediasi_pihak,0)=0 OR IFNULL(v.mediasi_L,0)=0,0,CONCAT(ROUND(v.mediasi_L/v.mediasi_pihak*100),'%')) AS persen_mediasi_L
						,v.mediasi_P
						,IF(IFNULL(v.mediasi_pihak,0)=0 OR IFNULL(v.mediasi_P,0)=0,0,CONCAT(ROUND(v.mediasi_P/v.mediasi_pihak*100),'%')) AS persen_mediasi_P
						,v.mediasi_D
						,IF(IFNULL(v.mediasi_pihak,0)=0 OR IFNULL(v.mediasi_D,0)=0,0,CONCAT(ROUND(v.mediasi_D/v.mediasi_pihak*100),'%')) AS persen_mediasi_D
						,v.mediasi_ND
						,IF(IFNULL(v.mediasi_pihak,0)=0 OR IFNULL(v.mediasi_ND,0)=0,0,CONCAT(ROUND(v.mediasi_ND/v.mediasi_pihak*100),'%')) AS persen_mediasi_ND
						,v.mediasi_I
						,IF(IFNULL(v.mediasi_pihak,0)=0 OR IFNULL(v.mediasi_I,0)=0,0,CONCAT(ROUND(v.mediasi_I/v.mediasi_pihak*100),'%')) AS persen_mediasi_I
						,v.mediasi_Pem
						,IF(IFNULL(v.mediasi_pihak,0)=0 OR IFNULL(v.mediasi_Pem,0)=0,0,CONCAT(ROUND(v.mediasi_Pem/v.mediasi_pihak*100),'%')) AS persen_mediasi_Pem
						,v.mediasi_BUMN
						,IF(IFNULL(v.mediasi_pihak,0)=0 OR IFNULL(v.mediasi_BUMN,0)=0,0,CONCAT(ROUND(v.mediasi_BUMN/v.mediasi_pihak*100),'%')) AS persen_mediasi_BUMN
					FROM sys_month_name AS m
					LEFT JOIN 
					(SELECT 
						MONTH(m.dimulai_mediasi) AS bulan
						,COUNT(*) AS mediasi_pihak
						,SUM(p.`jenis_kelamin` = 'L' AND p.jenis_pihak_id = 1) AS mediasi_L
						,SUM(p.`jenis_kelamin` = 'P' AND p.jenis_pihak_id = 1) AS mediasi_P
						,SUM(p.`difabel` = 'Y' AND p.jenis_pihak_id = 1) AS mediasi_D
						,SUM(p.`difabel` = 'T' AND p.jenis_pihak_id = 1) AS mediasi_ND
						,SUM(p.jenis_pihak_id IN (1)) AS mediasi_I
						,SUM(p.jenis_pihak_id IN (2,4)) AS mediasi_Pem
						,SUM(p.jenis_pihak_id IN (3)) AS mediasi_BUMN
					FROM perkara_mediasi AS m
					JOIN perkara AS perk ON m.perkara_id = perk.`perkara_id`
					LEFT JOIN(
						SELECT 
							pp.perkara_id
							,pp.pihak_id
							,p.`jenis_kelamin`
							,p.difabel
							,jp.id AS jenis_pihak_id
						FROM (
							SELECT pihak_id, perkara_id FROM perkara_pihak1 UNION
							SELECT pihak_id, perkara_id FROM perkara_pihak2) AS pp
						LEFT JOIN pihak AS p ON pp.pihak_id = p.id
						LEFT JOIN (SELECT id, nama FROM jenis_pihak GROUP BY id) AS jp ON p.`jenis_pihak_id` = jp.id
					)AS p ON m.`perkara_id` = p.perkara_id
					WHERE dimulai_mediasi IS NOT NULL AND YEAR(dimulai_mediasi) = '".$tahun."' AND alur_perkara_id IN ('".$idalurperkara."')
					GROUP BY MONTH(m.dimulai_mediasi)) AS v ON v.bulan = m.`id`
					GROUP BY m.id
					ORDER BY m.id;
					";
			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) {
			
		}
	}
}