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

class Report_mediasi_klasifikasi_periode 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 {
			$n=12;
			$sql=" SELECT jp.nama";
				for ($i=1; $i <=$n ; $i++){$sql.=",v.m_".$i;}
				for ($i=1; $i <=$n ; $i++){$sql.=",v.a_".$i;}
				for ($i=1; $i <=$n ; $i++){$sql.=",v.s_".$i;}
				for ($i=1; $i <=$n ; $i++){$sql.=",v.c_".$i;}
				for ($i=1; $i <=$n ; $i++){$sql.=",v.t_".$i;}
				for ($i=1; $i <=$n ; $i++){$sql.=",v.d_".$i;}
  				$sql.=" 
  				,v.berhasil_akta
				,IF(IFNULL(v.perkara_mediasi,0)=0 OR IFNULL(v.berhasil_akta,0)=0,0,CONCAT(round(v.berhasil_akta/v.perkara_mediasi*100),'%')) AS persen_akta
				,v.berhasil_sebagian
				,IF(IFNULL(v.perkara_mediasi,0)=0 OR IFNULL(v.berhasil_sebagian,0)=0,0,CONCAT(round(v.berhasil_sebagian/v.perkara_mediasi*100),'%')) AS persen_sebagian
				,v.berhasil_cabut
				,IF(IFNULL(v.perkara_mediasi,0)=0 OR IFNULL(v.berhasil_cabut,0)=0,0,CONCAT(round(v.berhasil_cabut/v.perkara_mediasi*100),'%')) AS persen_cabut
				,v.tidak_berhasil
				,IF(IFNULL(v.perkara_mediasi,0)=0 OR IFNULL(v.tidak_berhasil,0)=0,0,CONCAT(round(v.tidak_berhasil/v.perkara_mediasi*100),'%')) AS persen_tidak_berhasil
				,v.tidak_dpt_dilaksanakan
				,IF(IFNULL(v.perkara_mediasi,0)=0 OR IFNULL(v.tidak_dpt_dilaksanakan,0)=0,0,CONCAT(round(v.tidak_dpt_dilaksanakan/v.perkara_mediasi*100),'%')) AS persen_tidak_dpt_dilaksanakan
  				FROM jenis_perkara AS jp
        		LEFT JOIN (SELECT p.jenis_perkara_id";
        		for ($i=1; $i <=$n ; $i++) { 
        			$sql.=",SUM(year(dimulai_mediasi)=".$tahun." and month(dimulai_mediasi)=".$i.") AS m_".$i;
        		}

        		for ($i=1; $i <=$n ; $i++) { 
        			$sql.=",SUM(year(keputusan_mediasi)=".$tahun." and month(keputusan_mediasi)=".$i." AND (hasil_mediasi='Y' OR hasil_mediasi = 'Y1') AND (tanggal_putusan IS NULL OR (tanggal_putusan IS NOT NULL AND status_putusan_id NOT IN (7,37,67)))) AS a_".$i;
        		} 

        		for ($i=1; $i <=$n ; $i++) { 
        			$sql.=",SUM(year(keputusan_mediasi)=".$tahun." and month(keputusan_mediasi)=".$i." AND hasil_mediasi='S') AS s_".$i;
        		} 

        		for ($i=1; $i <=$n ; $i++) { 
        			$sql.=",SUM(year(keputusan_mediasi)=".$tahun." and month(keputusan_mediasi)=".$i." AND (hasil_mediasi='Y' OR hasil_mediasi = 'Y2') AND (tanggal_putusan IS NULL OR (tanggal_putusan IS NOT NULL AND status_putusan_id IN (7,37,67)))) AS c_".$i;
        		} 

        		for ($i=1; $i <=$n ; $i++) { 
        			$sql.=",SUM(year(keputusan_mediasi)=".$tahun." and month(keputusan_mediasi)=".$i." AND hasil_mediasi='T') AS t_".$i;
        		} 

        		for ($i=1; $i <=$n ; $i++) { 
        			$sql.=",SUM(year(keputusan_mediasi)=".$tahun." and month(keputusan_mediasi)=".$i." AND hasil_mediasi='D') AS d_".$i;
        		} 
       		$sql.="
       				,SUM(year(dimulai_mediasi)='".$tahun."') AS perkara_mediasi       
       				,SUM(year(keputusan_mediasi)='".$tahun."' AND (hasil_mediasi='Y' OR hasil_mediasi = 'Y1') AND (tanggal_putusan IS NULL OR (tanggal_putusan IS NOT NULL AND status_putusan_id NOT IN (7,37,67)))) AS berhasil_akta
       				,SUM(year(keputusan_mediasi)='".$tahun."' AND hasil_mediasi='S') AS berhasil_sebagian       
       				,SUM(year(keputusan_mediasi)='".$tahun."' AND (hasil_mediasi='Y' OR hasil_mediasi = 'Y2') AND (tanggal_putusan IS NULL OR (tanggal_putusan IS NOT NULL AND status_putusan_id IN (7,37,67)))) AS berhasil_cabut
       				,SUM(year(keputusan_mediasi)='".$tahun."' AND hasil_mediasi='T') AS tidak_berhasil
       				,SUM(year(keputusan_mediasi)='".$tahun."' AND hasil_mediasi='D') AS tidak_dpt_dilaksanakan
       			FROM v_mediasi AS p WHERE p.alur_perkara_id ='".$idalurperkara."' GROUP BY p.jenis_perkara_id) AS v ON jp.id =  v.jenis_perkara_id
        		WHERE jp.aktif='Y'
        		AND jp.id IN (SELECT ja.jenis_perkara_id FROM jenis_alur_perkara AS ja WHERE ja.alur_perkara_id='".$idalurperkara."')
        		ORDER BY jp.lft";
        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 
									  jp.id,
									  jp.nama, 
									  vsum.* FROM jenis_perkara AS jp
									        LEFT JOIN (SELECT p.jenis_perkara_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_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_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_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_dicabut
									       ,SUM(LEFT(keputusan_mediasi,7)='".$period."' AND hasil_mediasi='T') AS tidak_berhasil
									       ,((SUM(LEFT(keputusan_mediasi,7)='".$period."' AND hasil_mediasi='T')/SUM(LEFT(dimulai_mediasi,7)='".$period."'))*100) AS persen_tdk_berhasil
									       ,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_mediasi AS p WHERE p.alur_perkara_id = '".$idalurperkara."' GROUP BY p.jenis_perkara_id) AS vsum
									        ON jp.id =  vsum.jenis_perkara_id

									        WHERE jp.aktif='Y'
									        AND jp.id IN (SELECT ja.jenis_perkara_id FROM jenis_alur_perkara AS ja WHERE ja.alur_perkara_id='".$idalurperkara."')
									        ORDER BY jp.lft;

				");
		} catch (Exception $e) {
			
		}
	}
}