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

class Report_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_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_stat_perkara($period,$idalurperkara){
		try {
			return $this->db->query("
				SELECT alur_perkara_id,
					SUM(
					CASE
						WHEN permohonan_kasasi IS NOT NULL AND LEFT(permohonan_kasasi,7) = '".$period."' THEN 1
						ELSE 0
						END
					) AS masuk,
					SUM(
					CASE
						WHEN pengiriman_berkas_kasasi IS NOT NULL AND LEFT(pengiriman_berkas_kasasi,7) = '".$period."' THEN 1
						ELSE 0
						END
					) AS kirim_berkas,
					SUM(
					CASE
						WHEN putusan_kasasi IS NOT NULL AND LEFT(putusan_kasasi,7) = '".$period."' THEN 1
						ELSE 0
						END
					) AS putusan,
					SUM(
					CASE
						WHEN putusan_kasasi IS NULL OR LEFT(putusan_kasasi,7) > '".$period."' AND LEFT(permohonan_kasasi,7) < '".$period."' THEN 1
						ELSE 0
						END
					) AS blm_putusan,
					SUM(
					CASE
						WHEN penerimaan_berkas_kasasi IS NOT NULL AND LEFT(penerimaan_berkas_kasasi,7) = '".$period."'  THEN 1
						ELSE 0
						END
					) AS penerimaan_berkas,
					SUM(
					CASE
						WHEN pemberitahuan_putusan_kasasi IS NOT NULL AND LEFT(pemberitahuan_putusan_kasasi,7) = '".$period."'  THEN 1
						ELSE 0
						END
					) AS pemberitahuan_putusan
					FROM v_perkara_kasasi
					WHERE alur_perkara_id IN (".$idalurperkara.")
					AND LEFT(permohonan_kasasi,7) <= '".$period."';
				");
		} catch (Exception $e) {
			
		}
	}

	function get_lap_perkara($period,$idalurperkara){
		try {
			return $this->db->query("
				SELECT 
					perkara_id,alur_perkara_id,nomor_perkara_pn,nomor_perkara_banding,nomor_perkara_kasasi
					,hakim1_kasasi,hakim2_kasasi,hakim3_kasasi,hakim4_kasasi,hakim5_kasasi,panitera_pengganti_kasasi
					,pemohon_kasasi,putusan_pn,putusan_banding,pemberitahuan_putusan_banding
					,permohonan_kasasi,penerimaan_memori_kasasi,pengiriman_berkas_kasasi,penerimaan_berkas_kasasi
					,putusan_kasasi,pemberitahuan_putusan_kasasi
				FROM v_perkara_kasasi
				WHERE alur_perkara_id in (".$idalurperkara.") AND 
				((LEFT(permohonan_kasasi,7) < '".$period."' AND (putusan_kasasi IS NULL OR LEFT(putusan_kasasi,7)>='".$period."')
					OR LEFT(permohonan_kasasi,7)='".$period."'))
				ORDER BY tanggal_pendaftaran, nomor_perkara_pn ASC");
		} catch (Exception $e) {
			
		}
	}
}