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

class Report_hakim_kasasi 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_rekap($idalurperkara,$year){
		try {
			
			return $this->db->query("
				SELECT hakim_id, hakim_nama AS nama,
				SUM(
				CASE
					WHEN (YEAR(putusan_kasasi) >= '".$year."-01' OR putusan_kasasi IS NULL) AND LEFT(permohonan_kasasi,7) < '".$year."-01' THEN 1
					ELSE 0
				END
				) AS beban,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-01' THEN 1
					ELSE 0
				END
				) AS masuk01,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-01' THEN 1
					ELSE 0
				END
				) AS putus01,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-02' THEN 1
					ELSE 0
				END
				) AS masuk02,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-02' THEN 1
					ELSE 0
				END
				) AS putus02,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-03' THEN 1
					ELSE 0
				END
				) AS masuk03,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-03' THEN 1
					ELSE 0
				END
				) AS putus03,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-04' THEN 1
					ELSE 0
				END
				) AS masuk04,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-04' THEN 1
					ELSE 0
				END
				) AS putus04,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-05' THEN 1
					ELSE 0
				END
				) AS masuk05,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-05' THEN 1
					ELSE 0
				END
				) AS putus05,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-06' THEN 1
					ELSE 0
				END
				) AS masuk06,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-06' THEN 1
					ELSE 0
				END
				) AS putus06,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-07' THEN 1
					ELSE 0
				END
				) AS masuk07,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-07' THEN 1
					ELSE 0
				END
				) AS putus07,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-08' THEN 1
					ELSE 0
				END
				) AS masuk08,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-08' THEN 1
					ELSE 0
				END
				) AS putus08,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-09' THEN 1
					ELSE 0
				END
				) AS masuk09,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-09' THEN 1
					ELSE 0
				END
				) AS putus09,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-10' THEN 1
					ELSE 0
				END
				) AS masuk10,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-10' THEN 1
					ELSE 0
				END
				) AS putus10,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-11' THEN 1
					ELSE 0
				END
				) AS masuk11,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-11' THEN 1
					ELSE 0
				END
				) AS putus11,
				SUM(
				CASE
					WHEN LEFT(permohonan_kasasi,7) = '".$year."-12' THEN 1
					ELSE 0
				END
				) AS masuk12,
				SUM(
				CASE
					WHEN LEFT(putusan_kasasi,7) = '".$year."-12' THEN 1
					ELSE 0
				END
				) AS putus12,
				SUM(
				CASE
					WHEN YEAR(permohonan_kasasi) = ".$year." THEN 1
					ELSE 0
				END
				) AS masuk_total,
				SUM(
				CASE
					WHEN YEAR(putusan_kasasi) = ".$year." THEN 1
					ELSE 0
				END
				) AS putus_total
				
				FROM perkara_hakim_kasasi AS ph,
				perkara_kasasi AS pa 
				WHERE ph.aktif='Y' 
				AND pa.alur_perkara_id IN (".$idalurperkara.") 
				AND YEAR(pa.permohonan_kasasi) <= ".$year."
				AND pa.perkara_id = ph.perkara_id
				GROUP BY hakim_id, hakim_nama ORDER BY hakim_nama;");
		} catch (Exception $e) {
			
		}
	}
}