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

class Report_banding_pid 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 LEFT(permohonan_banding,7) <= '".$period."';
				");
		} catch (Exception $e) {
			
		}
	}

	function get_lap_perkara($period,$idalurperkara){
		try {
			return $this->db->query("SELECT 
									p.perkara_id
									,p.alur_perkara_id
									,p.nomor_perkara_pn
									,p.nomor_perkara_banding
									,REPLACE(UPPER(p.majelis_hakim_banding),',','<br />') AS majelis_hakim_banding  
									,UPPER(p.panitera_pengganti_banding) AS panitera_pengganti_banding
									,p.pemohon_banding
									,p.putusan_pn
									,p.permohonan_banding
									,(SELECT IF(d.status_pihak_id IS NULL ,p.pemberitahuan_permohonan_banding, MIN(d.pemberitahuan_permohonan_banding)) 
										FROM perkara_banding_detil AS d WHERE d.perkara_id=p.perkara_id 
									    AND d.pihak_asal=2 ) AS pemberitahuan_permohonan_banding_terdakwa
									,(SELECT IF(d.status_pihak_id IS NULL ,p.pemberitahuan_permohonan_banding, MIN(d.pemberitahuan_permohonan_banding)) 
										FROM perkara_banding_detil AS d WHERE d.perkara_id=p.perkara_id 
									    AND d.pihak_asal=1 ) AS pemberitahuan_permohonan_banding_jaksa
									,p.pemberitahuan_inzage
									,p.pelaksanaan_inzage
									,p.pengiriman_berkas_banding
									,p.penerimaan_kembali_berkas_banding
									,p.putusan_banding
									,(SELECT IF(d.status_pihak_id IS NULL ,p.pemberitahuan_putusan_banding, MIN(d.pemberitahuan_putusan_banding)) 
										FROM perkara_banding_detil AS d WHERE d.perkara_id=p.perkara_id 
									    AND d.pihak_asal=2 ) AS pemberitahuan_putusan_banding_terdakwa
									,(SELECT IF(d.status_pihak_id IS NULL ,p.pemberitahuan_putusan_banding, MIN(d.pemberitahuan_putusan_banding)) 
										FROM perkara_banding_detil AS d WHERE d.perkara_id=p.perkara_id 
									    AND d.pihak_asal=1 ) AS pemberitahuan_putusan_banding_jaksa
									FROM 
									  perkara_banding AS p
									WHERE 
									p.alur_perkara_id IN (".$idalurperkara.") AND 
									( ( LEFT(p.permohonan_banding,7) < '".$period."' AND ( p.putusan_banding IS NULL OR LEFT(p.putusan_banding,7)>='".$period."')) OR 
									(LEFT(p.permohonan_banding,7) = '".$period."') 
									) 
									ORDER BY p.permohonan_banding;

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