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

class Info_perkara extends CI_Model{
	function getDataPN(){
		try{
			$sql = "SELECT * FROM 
					(SELECT value AS NamaPN FROM sys_config WHERE id = 62) AS T1,
					(SELECT value AS KetuaPNNama FROM sys_config WHERE id = 64) AS T2,
					(SELECT value AS AlamatPN FROM sys_config WHERE id = 63) AS T3,
					(SELECT value AS WakilKetuaPNNama FROM sys_config WHERE id = 66) AS T4,
					(SELECT value AS PansekPNNama FROM sys_config WHERE id = 68) AS T5,
					(SELECT value AS WaPanNama FROM sys_config WHERE id = 70) AS T6,
					(SELECT value AS IDPN FROM sys_config WHERE id = 81) AS T7,
					(SELECT value AS ZonaWaktu FROM sys_config WHERE id = 75) AS T8"; 
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getListPenutupan(){
		try {
			switch ($this->session->userdata('jenis_pengadilan')) {
				case 2:
					$id_penutup='-1';
				break;
				case 3:
					$id_penutup='-100';
				break;
				case 4:
					$id_penutup='-200';
				break;
			}
			
			$sql="select id, jenis_agenda_id, preview 
					from template_dokumen_agenda where jenis_agenda_id=".$id_penutup.";";
			return $this->db->query($sql);
		} catch (Exception $e) {
			return false;
		}
	}
	function getInfoPerkara($idperkara){
		try {
			
			return $this->db->query('SELECT a.*, (SELECT VALUE FROM sys_config WHERE id=62) as nama_pengadilan
				FROM v_perkara as a
				WHERE a.perkara_id='.$idperkara.';');
			
		} catch (Exception $e) {
			return '';
		}
	}
	function get_list_pihak($nama_table, $perkara_id, $tergabung_ke=0){
		try {
			
			$sql=('SELECT *
				FROM '.$nama_table.' 
				WHERE perkara_id='.$perkara_id.' ');
			if ($nama_table=='perkara_pihak5' && $tergabung_ke!=0){
				$sql.=" AND saksi_pihak_ke=".$tergabung_ke;
			}
			return $this->db->query($sql);
		} catch (Exception $e) {
			return '';
		}
	}
	function get_info_sidang($sidang_id, $idperkara){
		try {
			return $this->db->query('SELECT *
				FROM perkara_jadwal_sidang 
				WHERE id='.$sidang_id.' AND perkara_id='.$idperkara.';');
			
		} catch (Exception $e) {
			return '';
		}
	}
	function get_info_sidang_tundaan($sidang_id, $idperkara){
		try {
			$query=$this->db->query('SELECT *
				FROM perkara_jadwal_sidang 
				WHERE perkara_id='.$idperkara.' ORDER BY urutan;');
			if ($query->num_rows>1){
				$sidang_id_sebelumnya=0;
				$ketemu=false;
				$result=$query->result();
				foreach ($result as $key =>$row) {
					if ($sidang_id==$row->id){
						$ketemu=true;
						$info_sidang_tundaan=$result[$key+1];
						break;
					}
					$sidang_id_sebelumnya=$row->sidang_id;
				}

			}else{
				$info_sidang_tundaan=array();
			}
			return $info_sidang_tundaan;
			
		} catch (Exception $e) {
			return '';
		}
	}

	function get_jenis_agenda($jenis_agenda_id=''){
		try {
			$sql='SELECT *
				FROM template_dokumen_jenis_agenda WHERE varlist like "%'.$this->session->userdata('jenis_pengadilan').'%"';
			if ($jenis_agenda_id!=''){
				$sql.=' AND id='.$jenis_agenda_id;
			}
			return $this->db->query($sql);
			
		} catch (Exception $e) {
			return '';
		}
	}
	function get_agenda($jenis_agenda_id){
		try {
			
			return $this->db->query('SELECT *
				FROM template_dokumen_agenda 
				WHERE jenis_agenda_id='.$jenis_agenda_id.'
				ORDER BY urutan;');
			
		} catch (Exception $e) {
			return '';
		}
	}
	function get_preview($jenis_agenda_id, $where){
		try {
			
			return $this->db->query('SELECT konten_dokumen
				FROM template_dokumen_agenda 
				WHERE jenis_agenda_id='.$jenis_agenda_id.'
				AND ('.$where.') ORDER BY urutan;');
			
		} catch (Exception $e) {
			return '';
		}
	}

	function cetak_preview($jenis_agenda_id, $where){
		try {
			
			return $this->db->query('SELECT ja.nama, a.* 
				FROM template_dokumen_agenda a 
					LEFT JOIN template_dokumen_jenis_agenda ja ON a.jenis_agenda_id=ja.id 
				WHERE ('.$where.') ORDER BY a.jenis_agenda_id, urutan;');
			
		} catch (Exception $e) {
			return '';
		}
	}

	function add_data($data){
		try {
			$this->db->query('delete from template_perkara_bas where sidang_id='.$data[0]['sidang_id']);
			$this->db->insert_batch('template_perkara_bas', $data);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function add_penutup_bas($data){
		try {
			$this->db->query('delete from template_perkara_bas_penutup where sidang_id='.$data['sidang_id'].' AND perkara_id='.$data['perkara_id'].' AND agenda_id='.$data['agenda_id']);
			$this->db->replace('template_perkara_bas_penutup', $data);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function get_penutup_bas($perkara_id, $sidang_id){
		try {
			return $this->db->query('select perkara_id, sidang_id, agenda_id, redaksi from template_perkara_bas_penutup where perkara_id='.$perkara_id.' and sidang_id='.$sidang_id);
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function simpan_tanya_jawab($data, $perkara_id, $sidang_id){
		try {
			$this->db->query('delete from template_perkara_bas_detil_tanya_jawab where perkara_id='.$perkara_id.' AND sidang_id='.$sidang_id.';');
			$this->db->insert_batch('template_perkara_bas_detil_tanya_jawab', $data);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function get_info_pemeriksaan($perkara_id, $sidang_id, $jenis_agenda_id){
		try {
			if ($jenis_agenda_id==6){
				$table='perkara_pihak5';
			}elseif($jenis_agenda_id==7){
				$table='perkara_pihak2';
			}else{
				$table='perkara_pihak5';
			}
			return $this->db->query('SELECT DISTINCT  p.id, pp2.nama, pp2.alamat, p.tempat_lahir, p.tanggal_lahir, p.jenis_kelamin, p.agama_nama, p.status_kawin,
										tmp.penanya_t,
										tmp.pertanyaan_t,
										tmp.jawaban_t,
										tmp.kenal,
										tmp.tanggapan_terdakwa,
										tmp.urutan_sumber,
										tmp.urutan_penanya,
										tmp.urutan_pertanyaan
									FROM
										(SELECT * 
										FROM
										  template_perkara_bas_detil_tanya_jawab 
										WHERE perkara_id = '.$perkara_id.' AND sidang_id = '.$sidang_id.'
										  AND jenis_agenda_id = '.$jenis_agenda_id.' 
										) AS tmp
									  LEFT JOIN '.$table.' pp2 ON tmp.sumber_id=pp2.id
									  LEFT JOIN pihak p ON pp2.pihak_id=p.id
									  
									  ORDER BY urutan_sumber,
										  urutan_penanya,
										  urutan_pertanyaan');
		} catch (Exception $e) {
			return false;
		}
	}

	function get_info_pihak($perkara_id, $sidang_id, $jenis_agenda_id){
		try {
			if ($jenis_agenda_id==6){
				$table='perkara_pihak5';
			}elseif($jenis_agenda_id==7){
				$table='perkara_pihak2';
			}else{
				$table='perkara_pihak5';
			}
			return $this->db->query('SELECT DISTINCT  p.id, pp2.nama, pp2.alamat, p.tempat_lahir, p.tanggal_lahir, p.jenis_kelamin, p.agama_nama, p.status_kawin,
										group_concat(tmp.penanya_t separator "=+="),
										group_concat(tmp.pertanyaan_t separator "=+="),
										group_concat(tmp.jawaban_t separator "=+="),
										tmp.kenal,
										tmp.tanggapan_terdakwa,
										group_concat(tmp.urutan_sumber separator "=+="),
										group_concat(tmp.urutan_penanya separator "=+="),
										group_concat(tmp.urutan_pertanyaan separator "=+=") 
									FROM
										(SELECT * 
										FROM
										  template_perkara_bas_detil_tanya_jawab 
										WHERE perkara_id = '.$perkara_id.' AND sidang_id = '.$sidang_id.'
										  AND jenis_agenda_id = '.$jenis_agenda_id.' 
										) AS tmp
									  LEFT JOIN '.$table.' pp2 ON tmp.sumber_id=pp2.id
									  LEFT JOIN pihak p ON pp2.pihak_id=p.id 
									  group by p.id
									  ORDER BY urutan_sumber, urutan_penanya, urutan_pertanyaan');
		} catch (Exception $e) {
			return false;
		}
	}

	function get_data_bas($sidang_id){
		try {
			return  $this->db->query('SELECT sd.perkara_id, IF(da.type LIKE "%input_text%", 
											(CASE  pb.jenis_agenda_id
											   WHEN 2 THEN CONCAT("text_",pb.`agenda_id`,"||||",sd.uraian)
											   WHEN 3 THEN CONCAT("text_",pb.`agenda_id`,"||||",sd.uraian)
											   WHEN 10 THEN CONCAT("text_",pb.`agenda_id`,"||||",sd.uraian)
											   WHEN 11 THEN CONCAT("text_",pb.`agenda_id`,"||||",sd.uraian)
											   WHEN 12 THEN CONCAT("text_",pb.`agenda_id`,"||||",sd.uraian)
											   WHEN 14 THEN CONCAT("text_",pb.`agenda_id`,"||||",sd.uraian)
											END), "tidak ada") AS isian, da.type, pb.* 
										FROM template_perkara_bas pb 
											LEFT JOIN template_dokumen_agenda da ON pb.agenda_id=da.id
											LEFT JOIN perkara_jadwal_sidang_detil sd ON pb.perkara_id=sd.perkara_id AND sd.jenis_Agenda_id=pb.jenis_agenda_id
										WHERE pb.sidang_id='.$sidang_id.' ORDER BY pb.urutan');
		} catch (Exception $e) {
			return '';
		}
	}

	function get_data_tanya_jawab($sidang_id){
		try {
			$sql='select * from template_perkara_bas_detil_tanya_jawab 
				where sidang_id='.$sidang_id.' order by jenis_agenda_id, urutan_sumber, urutan_penanya, urutan_pertanyaan;';
			return $this->db->query($sql);
		} catch (Exception $e) {
			return false;
		}
	}

	function simpan_sidang_detil($data, $perkara_id, $sidang_id){
		try {
			$this->db->where('perkara_id',$perkara_id);
			$this->db->where('sidang_id',$sidang_id);
			$query = $this->db->get('perkara_jadwal_sidang_detil');
			if ($query->num_rows()>0){
				$this->db->where('perkara_id', $perkara_id);
				$this->db->where('sidang_id', $sidang_id);
				$this->db->delete('perkara_jadwal_sidang_detil'); 
			}
			$data_batch=array();
			foreach ($data as $key=>$value) {
				$data_batch[]=array('perkara_id'		=> $perkara_id,
									'sidang_id'			=> $sidang_id,
									'jenis_agenda_id'	=> $value['jenis_agenda_id'],
									'uraian'			=> $value['uraian'],
									'diinput_oleh'		=> $value['diinput_oleh'],
									'diinput_tanggal'	=> $value['diinput_tanggal']
									);
				
			}
			$this->db->insert_batch('perkara_jadwal_sidang_detil', $data_batch);	
			return true;
		} catch(Exception $e) {
			return false;
		}
	}

	function getDataTerdakwaMil($idperkara){
		try {
			if(!is_numeric($idperkara)){
					return '';
			}
				$sql="SELECT a.urutan AS urutan, 
							 a.pihak_id AS pihak_id, 
							 a.pangkat as pangkat_terdakwa,
							 a.nrp as nrp_terdakwa,
							 a.jabatan as jabatan_terdakwa,
							 a.kesatuan as kesatuan_terdakwa,
							 b.*
						   
							 FROM perkara_pihak2 AS a
							 LEFT JOIN pihak AS b ON a.pihak_id=b.id
							  WHERE a.perkara_id=$idperkara
							  ORDER BY a.urutan ASC";

			$query=$this->db->query($sql);
			return $query;
		} catch(Exception $e) {
			return false;
		}
	}

	function getDataPihakTerdakwa1Mil($perkara_id){
		try {
			if(!is_numeric($perkara_id) OR !is_numeric($idpihak)){
					return '';
			}
			$sql = "SELECT pihak_id, urutan,  nama, alamat, tempat_lahir, tanggal_lahir, umur, jenis_kelamin, warga_negara, agama_nama, pekerjaan  
					FROM perkara_pihak2 
					WHERE perkara_id = $perkara_id and pihak_id=$idpihak 
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) {
			return false;
		}
	}

	function getDataPenahananTerdakwaMil($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT jp.nama AS oleh, sp.nama AS status_penahanan, jt.nama AS jenis_tahanan, 
					 	pt.jenis_penahanan_id,
  						pt.tanggal_surat,
  						pt.nomor_surat,
  						pt.mulai AS sejak, pt.sampai, pt.lama_penahanan, pt.pihak_id, pt.pejabat_penahan, sp.id as status_penahanan_id
					FROM (SELECT * 
							FROM penahanan_terdakwa 
							WHERE perkara_id=$idperkara  
							ORDER BY jenis_penahanan_id, mulai ASC) pt 
					LEFT JOIN jenis_penahanan jp ON pt.jenis_penahanan_id = jp.id
					LEFT JOIN status_penahanan sp ON pt.status_penahanan_id = sp.id
					LEFT JOIN jenis_tahanan jt ON pt.jenis_tahanan_id = jt.id ORDER BY sejak, pt.jenis_penahanan_id";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataHakimMil($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM (SELECT perkara_id, urutan, jabatan_hakim_id, jabatan_hakim_nama, hakim_id, tanggal_penetapan, nomor_sk_penetapan 
							FROM perkara_hakim_pn 
							WHERE perkara_id = $idperkara 
							GROUP BY hakim_id) AS ph
					LEFT JOIN (SELECT id, nama_gelar 
								FROM hakim_pn) AS h
					ON ph.hakim_id = h.id
					ORDER BY jabatan_hakim_id ASC";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getSusunanMajelisMil($perkara_id){
		try{
			if(!is_numeric($perkara_id)) { return ''; }
			$sql = "SELECT  
						ph.* , h.*, pp.nama_gelar AS nama_pp, pp.pangkat AS pangkat_pp, pp.nip AS nip_pp, 
						pj.nip AS nrp_om, om.nama AS nama_om, om.alamat AS alamatp_om, pj.pangkat AS pangkat_om, pj.jabatan AS jabatan_om,
						p.*
					FROM (SELECT perkara_id, urutan AS urutan_hakim,
							jabatan_hakim_id, jabatan_hakim_nama, hakim_id,
							tanggal_penetapan AS penetapan_majelis_hakim, nomor_sk_penetapan 
						FROM perkara_hakim_pn 
						WHERE tahapan_id=10 AND aktif='Y' AND perkara_id = ".$perkara_id."
						GROUP BY hakim_id) AS ph 
					LEFT JOIN  (SELECT id, nama_gelar AS nama_hakim,
							nip AS nip_hakim, pangkat AS pangkat_hakim, jabatan AS jabatan_hakim
						FROM hakim_pn) AS h ON ph.hakim_id = h.id 
					LEFT JOIN panitera_pn AS pp ON pp.id IN (SELECT panitera_id FROM perkara_panitera_pn WHERE perkara_id=".$perkara_id." AND aktif='Y')
					LEFT JOIN perkara_pihak1 AS om ON om.perkara_id=".$perkara_id."
					LEFT JOIN pihak_jaksa pj ON pj.pihak_id=om.pihak_id
					LEFT JOIN (SELECT b.nomor_induk AS nrp_p, a.perkara_id, a.nama AS nama_p, a.alamat AS alamat_p, b.pangkat AS pangkat_p ,b.jabatan AS jabatan_p,  b.kesatuan AS kesatuan_p
						FROM perkara_pengacara AS a
						LEFT JOIN pihak_pengacara AS b ON b.pihak_id=a.pengacara_id
						WHERE a.perkara_id=".$perkara_id.") AS p ON p.perkara_id=".$perkara_id."
					ORDER BY ph.jabatan_hakim_id ASC";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}
}