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

class M_sidang_list extends CI_Model{
	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_list_sidang($idalurperkara='',$col=1,$type='DESC',$begin=0,$key='',$date=''){
		$orderby = 'tanggal_sidang';
		$where = '';
		if($idalurperkara>0){
			$where = "AND alur_perkara_id IN (".$idalurperkara.")";
		}else{
			$where = "";
		}

		if(!empty($key)){
			$where_like = ' WHERE (panitera_pengganti_text LIKE "%'.$key.'%" OR majelis_hakim_nama LIKE "%'.$key.'%" OR nomor_perkara LIKE "%'.$key.'%" OR tanggal_sidang LIKE "%'.$key.'%" OR agenda LIKE "%'.$key.'%" OR para_pihak LIKE "%'.$key.'%") ';
		}else{
			$where_like =" ";
			if($idalurperkara>0){
				$where = " WHERE alur_perkara_id IN (".$idalurperkara.") ";
			}
		}

		if(empty($date) AND empty($key))
			$where .= " ";
		elseif(!empty($date))
			$where .= " AND tanggal_sidang = '".$date."'";
		try {
			$query = $this->db->query("SELECT SQL_CALC_FOUND_ROWS id, alur_perkara_id, perkara_id, 
				tanggal_sidang,sidang_keliling, jam_sidang, agenda, 
				ruangan, ditunda, nomor_perkara, pihak1_text, pihak2_text, para_pihak, 
				pihak_dipublikasikan, majelis_hakim_nama, 
				panitera_pengganti_text, keterangan, nomor_tilang 
				FROM v_jadwal_sidang ".$where_like." ".$where." 
				ORDER BY ".$orderby." ".$type." LIMIT ".$begin.",50");
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_list_sidang_tun($idalurperkara='',$col=1,$type='DESC',$begin=0,$key='',$date=''){
		$orderby = 'sid_per.`tanggal_sidang`';
		$where = '';
		
		
		if($idalurperkara>0){
			$where = "AND alur_perkara_id IN (".$idalurperkara.")";
		}else{
			$where = "";
		}

		if(!empty($key)){
			$where_like = ' ON (panitera_pengganti_text LIKE "%'.$key.'%" OR majelis_hakim_nama LIKE "%'.$key.'%" OR nomor_perkara LIKE "%'.$key.'%" OR tanggal_sidang LIKE "%'.$key.'%" OR agenda LIKE "%'.$key.'%" OR para_pihak LIKE "%'.$key.'%") ';
		}else{
			$where_like =" ";
			if($idalurperkara>0){
				$where = " WHERE alur_perkara_id IN (".$idalurperkara.") ";
			}
		}

		if(empty($date) AND empty($key))
			$where .= " ";			
		elseif(!empty($date))
			$where .= " AND tanggal_sidang = '".$date."'";			

		try {
			$query = $this->db->query("SELECT SQL_CALC_FOUND_ROWS * FROM
										(SELECT 
										id,perkara_id, tanggal_sidang, CONVERT(agenda USING utf8) AS agenda, ruangan
										FROM v_jadwal_sidang 										
										UNION
										SELECT id, perkara_id,tanggal_musyawarah,agenda_musyawarah, 'Ruang Pemeriksaan Persiapan' AS ruangan
										FROM perkara_persiapan_proses ) sid_per 
										LEFT JOIN perkara ON perkara.`perkara_id`=sid_per.`perkara_id`										
										LEFT JOIN perkara_penetapan ON perkara_penetapan.`perkara_id`=sid_per.`perkara_id`
										".$where_like." ".$where." 
										ORDER BY ".$orderby." ".$type." LIMIT ".$begin.",50");
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getSidangListCetak($idalurperkara='',$key='',$date=''){
		$where = '';
		$today = date('Y-m-d');
		
		if($idalurperkara>0){
			$where = "AND alur_perkara_id IN (".$idalurperkara.")";
		}else{
			$where = "";
		}

		if(!empty($key)){
			$where_like = ' WHERE (panitera_pengganti_text LIKE "%'.$key.'%" OR majelis_hakim_nama LIKE "%'.$key.'%" OR nomor_perkara LIKE "%'.$key.'%" OR tanggal_sidang LIKE "%'.$key.'%" OR agenda LIKE "%'.$key.'%" OR para_pihak LIKE "%'.$key.'%") ';
		}else{
			$where_like =" ";
			if($idalurperkara>0){
				$where = " WHERE alur_perkara_id IN (".$idalurperkara.")";
			}
		}
		if(empty($date) AND empty($key))
			$where .= " AND tanggal_sidang = '".$today."'";
		elseif(!empty($date))
			$where .= " AND tanggal_sidang = '".$date."'";
		try {
			$query = $this->db->query("SELECT SQL_CALC_FOUND_ROWS id, alur_perkara_id, perkara_id, tanggal_sidang,sidang_keliling, jam_sidang, agenda, ruangan, ditunda, nomor_perkara, pihak1_text, pihak2_text, para_pihak, pihak_dipublikasikan, majelis_hakim_nama, panitera_pengganti_text, keterangan, nomor_tilang FROM v_jadwal_sidang ".$where_like." ".$where." ORDER BY tanggal_sidang DESC");
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}	


	function fecth_data_num($idalurperkara,$key){
		$query = $this->db->query("SELECT FOUND_ROWS() as cnt;");
		if($query->num_rows>0){
			foreach ($query->result() as $row) {
				return $row->cnt;
			}
    		return $query->num_rows;
    	}else{
    		return 0;
    	}
    }

    function get_list_sidang_detil($id_jadwal_sidang){
    	try{
    		$query = $this->db->query("SELECT * FROM v_jadwal_sidang WHERE id='$id_jadwal_sidang'");
    		return $query;
    	} catch (Exception $e) {
			log_message('error', $e);
		}
    }

    function parseOrderby($col){
		if($col==1){
			return "nomor_perkara"; 
		}else if($col==2){
			return "tanggal_sidang"; 
		}else if($col==3){
			return "sidang_keliling"; 
		}else if($col==4){
			return "ruangan"; 
		}else if($col==5){
			return "nomor_perkara"; 
		}else if($col==6){
			return "agenda"; 
		}else if($col==7){
			return "pihak1_text"; 
		}else if($col==8){
			return "pihak2_text"; 
		}else if($col==9){
			return "majelis_hakim_nama"; 
		}else if($col==10){
			return "panitera_pengganti_text"; 
		}else if($col==11){
			return "keterangan"; 
		}else{
			return "tanggal_sidang";
		}
	}

    function getJadwalSidangListSearchDetail($idalurperkara='',$col=2,$type='DESC',$begin=0,$key=''){
		$orderby = $this->parseOrderby($col);
		$where = '';
		

		if($idalurperkara>0){
			$where = "AND alur_perkara_id IN (".$idalurperkara.")";
		}else{
			$where = "";
		}

		if(!empty($key)){
			$where_like = ' WHERE '.$key.' ';
		}else{
			$where_like =" ";
			if($idalurperkara>0){
				$where = " WHERE alur_perkara_id IN (".$idalurperkara.")";
			}
		}

		try {
			$query = $this->db->query("SELECT SQL_CALC_FOUND_ROWS 
					id, alur_perkara_id, perkara_id, tanggal_sidang,sidang_keliling, jam_sidang, agenda, ruangan, ditunda, nomor_perkara, pihak1_text, pihak2_text, para_pihak, pihak_dipublikasikan, majelis_hakim_nama, panitera_pengganti_text, keterangan, nomor_tilang FROM v_jadwal_sidang ".$where_like." ".$where." ORDER BY ".$orderby." ".$type." LIMIT ".$begin.",50");
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function getDataPetitumDakwaan($idperkara){
		try {
			return $this->db->query('SELECT perkara_id, pasal_dakwaan, IF(alur_perkara_id<100,petitum,dakwaan) AS petitum_dakwaan FROM perkara WHERE perkara_id = '.$idperkara);
		} catch (Exception $e) {
			
		}
	}
	
	function getDataPenahanan($idperkara){
		try {
			return $this->db->query("SELECT max(mulai),p.nama, t.* 
					FROM v_penahanan_terdakwa AS t 
					LEFT JOIN pihak AS p
					ON p.id = t.pihak_id
					WHERE perkara_id = ".$idperkara."
					GROUP BY pihak_id;");
		} catch (Exception $e) {
			
		}
	}

	function blangko_bas_lanjutan_agama(){
		try{
			$sql = "SELECT kode,nama FROM template_dokumen WHERE alur_perkara_id='15' AND tahapan_id='14'  AND proses_id='200' AND (nomor BETWEEN '101' AND '200' ) ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	function get_list_ruang_sidang(){
    	try{
    		$query = $this->db->query("SELECT id,nama AS nama_ruang FROM ruangan_sidang WHERE aktif='Y'");
    		return $query;
    	} catch (Exception $e) {
			log_message('error', $e);
		}
    }
	function get_list_jadwalsidang($tanggal,$druang){
		$where .= "where D.tanggal_sidang = '".$tanggal."' AND D.ruangan_id = '".$druang."'";
		try {
			
			$query = $this->db->query("SELECT 
										A.nomor_perkara,
										B.hakim_nama,
										C.majelis_hakim_kode,
										C.majelis_hakim_nama,
										C.panitera_pengganti_text,
										D.tanggal_sidang,
										D.agenda,
										D.ruangan
										FROM perkara A
										LEFT JOIN perkara_hakim_pn B ON A.perkara_id=B.perkara_id
										LEFT JOIN perkara_penetapan C ON A.perkara_id=C.perkara_id
										LEFT JOIN perkara_jadwal_sidang D ON A.perkara_id=D.perkara_id
										".$where."
										GROUP BY A.perkara_id");
			return $query;
		} catch (Exception $e) {
			return FALSE;
		}
	}
	function get_konfigurasi($id){
		try {
			
			$query = $this->db->query("SELECT id,value FROM sys_config WHERE id=".$id."");
			foreach ($query->result() as $row) {
				return $row->value;
			}
			return $query;
		} catch (Exception $e) {
			return FALSE;
		}
	}
	function get_list_majelis(){
    	try{
    		$query = $this->db->query("SELECT a.id,	a.nip,a.nama_gelar
										FROM hakim_pn a
										LEFT JOIN perkara_hakim_pn b ON a.id=b.hakim_id
										WHERE a.aktif='Y' AND jabatan_hakim_id='1' GROUP BY a.id");
    		return $query;
    	} catch (Exception $e) {
			log_message('error', $e);
		}
    }
	function get_list_jadwalmajelis($tanggal,$IDMajelis){
		$where .= "where D.tanggal_sidang = '".$tanggal."' AND B.hakim_id = $IDMajelis";
		try {
			
			$query = $this->db->query("SELECT 
										A.nomor_perkara,
										B.hakim_nama,
										C.majelis_hakim_kode,
										C.majelis_hakim_nama,
										C.panitera_pengganti_text,
										D.tanggal_sidang,
										D.agenda,
										D.ruangan
										FROM perkara A
										LEFT JOIN perkara_hakim_pn B ON A.perkara_id=B.perkara_id
										LEFT JOIN perkara_penetapan C ON A.perkara_id=C.perkara_id
										LEFT JOIN perkara_jadwal_sidang D ON A.perkara_id=D.perkara_id
										".$where."
										GROUP BY A.perkara_id");
			return $query;
		} catch (Exception $e) {
			return FALSE;
		}
	}
}
?>