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

class Report_lipa9 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 get_info_ketua(){
		try {
			$this->db->select('*');
			$this->db->where('id >=', 64); 
			return $this->db->get('sys_config');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_info_nipketua(){
		try {
			$this->db->select('*');
			$this->db->where('id >=', 65); 
			return $this->db->get('sys_config');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_info_panses(){
		try {
			$this->db->select('*');
			$this->db->where('id >=', 68); 
			return $this->db->get('sys_config');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_info_nippanses(){
		try {
			$this->db->select('*');
			$this->db->where('id >=', 69); 
			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 {
			return $this->db->query('SELECT tanggal_pendaftaran, 
				left(tanggal_pendaftaran,7) AS tgl,
				tahun_pendaftaran,
				bulan_pendaftaran, 
				LEFT(tanggal_putusan,7) AS putusan, 
				LEFT(penetapan_majelis_hakim,7) AS majelis,
				hasil_mediasi,LEFT(tanggal_cabut,7) AS cabut,
				LEFT(keputusan_mediasi,7) AS mediasi, 
				LEFT(tanggal_gugur,7) AS gugur,LEFT(tanggal_minutasi,7) AS minutasi
				FROM v_perkara 
				WHERE alur_perkara_id IN ('.$idalurperkara.') AND tahun_pendaftaran<='.$tahun.';');
		} 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 jenis_perkara_id,
										jenis_perkara_nama,
										sum(
											CASE 
												WHEN jenis_perkara_id = 341 AND (tanggal_putusan IS NULL OR LEFT(tanggal_putusan,7) >='".$period."') AND LEFT(tanggal_pendaftaran,7)  < '".$period."' THEN 1
												ELSE 0
											END 
										) sisa_poligami,
										sum(
											CASE 
												WHEN jenis_perkara_id = 341 AND LEFT(tanggal_pendaftaran,7)  = '".$period."' THEN 1
												ELSE 0
											END 
										) masuk_poligami,
										sum(
											CASE 
												WHEN jenis_perkara_id = 341 AND (tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7) ='".$period."')  THEN 1
												ELSE 0
											END 
										) putus_poligami,
										sum(
											CASE 
												WHEN jenis_perkara_id = 346 AND (tanggal_putusan IS NULL OR LEFT(tanggal_putusan,7) >='".$period."') AND LEFT(tanggal_pendaftaran,7)  < '".$period."' THEN 1
												ELSE 0
											END 
										) sisa_talak,
										sum(
											CASE 
												WHEN jenis_perkara_id = 346 AND LEFT(tanggal_pendaftaran,7)  = '".$period."' THEN 1
												ELSE 0
											END 
										) masuk_talak,
										sum(
											CASE 
												WHEN jenis_perkara_id = 346 AND (tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7) ='".$period."')  THEN 1
												ELSE 0
											END 
										) putus_talak,
										sum(
											CASE 
												WHEN jenis_perkara_id = 347 AND (tanggal_putusan IS NULL OR LEFT(tanggal_putusan,7) >='".$period."') AND LEFT(tanggal_pendaftaran,7)  < '".$period."' THEN 1
												ELSE 0
											END 
										) sisa_gugat,
										sum(
											CASE 
												WHEN jenis_perkara_id = 347 AND LEFT(tanggal_pendaftaran,7)  = '".$period."' THEN 1
												ELSE 0
											END 
										) masuk_gugat,
										sum(
											CASE 
												WHEN jenis_perkara_id = 347 AND tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7) ='".$period."'  THEN 1
												ELSE 0
											END 
										) putus_gugat
										FROM v_perkara vpk LEFT JOIN (
														SELECT *  
														FROM (SELECT p1.perkara_id,p1.pihak_id,p1.nama,p.pekerjaan,izin.status AS statusizin,'1' AS status_pihak 
																	FROM perkara_pihak1 AS p1
																		LEFT JOIN  pihak AS p
																			ON p1.pihak_id = p.id
																		LEFT JOIN perkara_izin_cerai AS izin
																			ON izin.perkara_id = p1.perkara_id AND p1.pihak_id = izin.pihak_id
																			) AS pihak
																	UNION
																(SELECT p2.perkara_id,p2.pihak_id,p2.nama,p.pekerjaan,izin.status AS statusizin,'2' AS status_pihak 
																	FROM perkara_pihak2 AS p2
																		LEFT JOIN  pihak AS p
																			ON p2.pihak_id = p.id
																		LEFT JOIN perkara_izin_cerai AS izin
																			ON izin.perkara_id = p2.perkara_id AND p2.pihak_id = izin.pihak_id) 
																) AS pihaks
                                                                              ON vpk.perkara_id=pihaks.perkara_id
										
										WHERE jenis_perkara_id IN (341,347,346) AND pekerjaan IN ('Tentara Nasional Indonesia','Pegawai Negeri Sipil','Pegawai BUMN/BUMD','POLRI')
										AND LEFT(tanggal_pendaftaran,7) <='".$period."';");
		} catch (Exception $e) {
			
		}
	}

function get_rekap_izin_cerai($period){
		try {
			return $this->db->query("
SELECT 
sum(
	CASE
		WHEN pekerjaan IN ('Tentara Nasional Indonesia','Pegawai Negeri Sipil','Pegawai BUMN/BUMD','POLRI')  AND statusizin=1 AND status_pihak=1 THEN 1
		ELSE 0
	END
) AS pemohonizin,
sum(
	CASE
		WHEN pekerjaan IN ('Tentara Nasional Indonesia','Pegawai Negeri Sipil','Pegawai BUMN/BUMD','POLRI')  AND (statusizin=0 OR statusizin IS NULL OR statusizin='')  AND status_pihak=1 THEN 1
		ELSE 0
	END
) AS pemohontidakizin,
sum(
	CASE
		WHEN pekerjaan IN ('Tentara Nasional Indonesia','Pegawai Negeri Sipil','Pegawai BUMN/BUMD','POLRI')  AND statusizin=1 AND status_pihak=2 THEN 1
		ELSE 0
	END
) AS termohonizin,
sum(
	CASE
		WHEN pekerjaan IN ('Tentara Nasional Indonesia','Pegawai Negeri Sipil','Pegawai BUMN/BUMD','POLRI')  AND (statusizin=0 OR statusizin IS NULL OR statusizin='')  AND status_pihak=2 THEN 1
		ELSE 0
	END
) AS termohontidakizin

FROM (
SELECT *  
FROM (SELECT p1.perkara_id,p1.pihak_id,p1.nama,p.pekerjaan,izin.status AS statusizin,'1' AS status_pihak 
			FROM perkara_pihak1 AS p1
				LEFT JOIN  pihak AS p
					ON p1.pihak_id = p.id
				LEFT JOIN perkara_izin_cerai AS izin
					ON izin.perkara_id = p1.perkara_id AND p1.pihak_id = izin.pihak_id
					) AS pihak
			UNION
		(SELECT p2.perkara_id,p2.pihak_id,p2.nama,p.pekerjaan,izin.status AS statusizin,'2' AS status_pihak 
			FROM perkara_pihak2 AS p2
				LEFT JOIN  pihak AS p
					ON p2.pihak_id = p.id
				LEFT JOIN perkara_izin_cerai AS izin
					ON izin.perkara_id = p2.perkara_id AND p2.pihak_id = izin.pihak_id) 
		) AS pi1
		LEFT JOIN v_perkara AS v
		ON pi1.perkara_id = v.perkara_id
		WHERE jenis_perkara_id IN (341,347,346) AND tanggal_putusan IS NOT NULL AND LEFT(tanggal_putusan,7) ='".$period."'; ");
		} catch (Exception $e) {
			
		}
	}
}