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

class Report_banding 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_banding IS NOT NULL AND LEFT(permohonan_banding,7) = '".$period."' THEN 1
						ELSE 0
						END
					) AS masuk,
					SUM(
					CASE
						WHEN pengiriman_berkas_banding IS NOT NULL AND LEFT(pengiriman_berkas_banding,7) = '".$period."' THEN 1
						ELSE 0
						END
					) AS kirim_berkas,
					SUM(
					CASE
						WHEN putusan_banding IS NOT NULL AND LEFT(putusan_banding,7) = '".$period."' THEN 1
						ELSE 0
						END
					) AS putusan,
					SUM(
					CASE
						WHEN putusan_banding IS NULL OR LEFT(putusan_banding,7) > '".$period."' AND LEFT(permohonan_banding,7) < '".$period."' THEN 1
						ELSE 0
						END
					) AS blm_putusan,
					SUM(
					CASE
						WHEN penerimaan_kembali_berkas_banding IS NOT NULL AND LEFT(penerimaan_kembali_berkas_banding,7) = '".$period."'  THEN 1
						ELSE 0
						END
					) AS penerimaan_berkas,
					SUM(
					CASE
						WHEN pemberitahuan_putusan_banding IS NOT NULL AND LEFT(pemberitahuan_putusan_banding,7) = '".$period."'  THEN 1
						ELSE 0
						END
					) AS pemberitahuan_putusan
					FROM v_perkara_banding
					WHERE alur_perkara_id IN (".$idalurperkara.") AND tanggal_cabut IS NULL
					AND LEFT(permohonan_banding,7) <= '".$period."';
				");
		} catch (Exception $e) {
			
		}
	}

	function get_lap_perkara($period,$idalurperkara){
		try {
			return $this->db->query("
				SELECT 
					tanggal_pendaftaran, perkara_id,alur_perkara_id,nomor_perkara_pn,nomor_perkara_banding,hakim1_banding
					,hakim2_banding,hakim3_banding,panitera_pengganti_banding,pemohon_banding,putusan_pn,
					pemberitahuan_putusan_pn,permohonan_banding,pemberitahuan_inzage,pelaksanaan_inzage
					,pengiriman_berkas_banding,penerimaan_kembali_berkas_banding,putusan_banding,pemberitahuan_putusan_banding
				FROM v_perkara_banding
				WHERE alur_perkara_id in (".$idalurperkara.") AND tanggal_cabut IS NULL AND
				((LEFT(permohonan_banding,7) < '".$period."' AND (putusan_banding IS NULL OR LEFT(putusan_banding,7)>='".$period."')
					OR LEFT(permohonan_banding,7)='".$period."')) 
				ORDER BY tanggal_pendaftaran, nomor_perkara_pn ASC");
		} catch (Exception $e) {
			
		}
	}
}