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

class jadwal_sidang_m extends CI_Model{
	function fetch_description($title,$data){
		$descrip = '<br><table style="vertical-align:top" cellspacing="0" cellpadding="1" border="1">';
		$descrip .= '<tr><th>Nama Kolom</th><th>Nilai</th></tr>';
		foreach ($data as $key => $value) {
			$descrip .= '<tr>';
			$descrip .= '<td>'.$key.'</td>';
			$descrip .= '<td>'.$value.'</td>';
			$descrip .= '</tr>';
		}
		$descrip .= '</table>';
		return $descrip;
	}

	function add_audittrail($action,$title,$descrip,$tablename,$namahalaman){
		try {
			$data = array(
				'datetime' => date("Y-m-d H:i:s"),
				'ipaddress' => $this->input->ip_address(),
				'username' => $this->session->userdata('username'),
				'tablename' => $tablename,
				'formname' => $namahalaman,
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
		}
	}

	function getDataPN(){//untuk template di data_persidangan_c.php
		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";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataBasSitaRevindicatoir($idperkara){//untuk template di data_persidangan_c.php
		try{
			if(!is_numeric($idperkara)){ return '';	}
			$sql = "SELECT pp.penetapan_majelis_hakim,ph2.nama AS nama_tergugat,ph2.alamat AS alamat_tergugat,
					ph1.nama AS nama_penggugat,ph1.alamat AS alamat_penggugat,p.nomor_perkara
					FROM perkara_penetapan AS pp
					LEFT JOIN perkara_pihak2 AS ph2
					ON pp.perkara_id=ph2.perkara_id
					LEFT JOIN perkara_pihak1 AS ph1
					ON pp.perkara_id=ph1.perkara_id
					LEFT JOIN perkara AS p
					ON pp.perkara_id=p.perkara_id
					WHERE pp.perkara_id=".$idperkara." GROUP BY pp.perkara_id;";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}

	function getTemplateAmar(){
		try {
			$this->db->select('id,nama');
			$this->db->where('aktif', 'Y');
			$res = $this->db->get('template_keterangan_saksi_m');
			return $res->result();
		} catch (Exception $e) {
			
		}
	}

	function getContentTemplateAmar($id){
		try {
			$this->db->where('m_id',$id);
			return $this->db->get('template_keterangan_saksi_d');
		} catch (Exception $e) {
			
		}
	}

	function get_data_jadwal($perkara_id){
		try {
			$qry="SELECT A.alur_perkara_id, B.* 
					FROM perkara A, perkara_jadwal_sidang B 
					WHERE B.perkara_id='".$perkara_id."' and B.perkara_id=A.perkara_id ORDER BY B.tanggal_sidang ASC";
			$query=$this->db->query($qry);
			return $query->result();
		} 
			catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function jumlah_hakim($perkara_id){
		try {
			$qry="SELECT COUNT(*) AS jml_hakim FROM perkara_hakim_pn WHERE perkara_id=".$perkara_id.";";
			$query=$this->db->query($qry);
			return $query->row()->jml_hakim;
		} 
			catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function data_saksi($perkara_id){
		try {
			$qry="SELECT * FROM perkara_pihak5 WHERE perkara_id=".$perkara_id.";";
			return $this->db->query($qry);
		} 
			catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function data_terdakwa($perkara_id){
		try {
			$qry="SELECT * FROM perkara_pihak2 WHERE perkara_id=".$perkara_id.";";
			return $this->db->query($qry);
		} 
			catch (Exception $e) {
			log_message('error', $e);
		}
	}
	#10-12-2016 V315
	function getInfoMediasi($perkara_id){
		try {
			$this->db->where('perkara_id',$perkara_id);
			$this->db->select('perkara_id');
			$this->db->from('perkara_mediasi');
			return $this->db->get()->num_rows()>0;
		} catch (Exception $e) {
			return '';
		}
	}

	function get_detil_jadwal($jadwal_id){
		if(empty($jadwal_id)) {
			return '';
		}
		try {
			$query=$this->db->query("SELECT A.* , 
										(SELECT COUNT(*) 
											FROM perkara_penetapan_hari_sidang B 
											WHERE B.jadwalsidang_id='".$jadwal_id."' AND B.jadwalsidang_id=A.id) AS phs
									FROM perkara_jadwal_sidang A 
									WHERE A.id='".$jadwal_id."';");
			return $query->result();
		}catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_ruangan_sidang(){
		$this->db->where('aktif','y');
		$query=$this->db->get('ruangan_sidang');
		return $query->result();
	}

	function get_id_jadwal_terakhir_all(){
		$query = $this->db->query('SELECT IFNULL(MAX(id)+1,1) AS nextid FROM perkara_jadwal_sidang');
		return $query->row()->nextid;
	}

	function get_urutan_jadwal_terakhir($id_perkara){
		$this->db->select_max('urutan');
		$this->db->where('perkara_id',$id_perkara);
		$result=$this->db->get('perkara_jadwal_sidang');
		return $result;
	}

	function get_id_jadwal_terakhir_satu_perkara($id_perkara){
		$this->db->select_max('id','idjadwalsidang','urutan');
		$this->db->where('perkara_id',$id_perkara);
		$result=$this->db->get('perkara_jadwal_sidang')->row();
		return $result->idjadwalsidang;
	}

	function update_jadwal_sidang($id_jadwal,$data_diupdate,$idperkara,$namahalaman){
		try {
			$namatabel="perkara_jadwal_sidang";
			$primarykey="id";
			$this->db->where($primarykey,$id_jadwal);
			$this->db->update($namatabel,$data_diupdate);
			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id_jadwal."]";
			$descrip = $this->fetch_description($title,$data_diupdate);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		}catch (Exception $e) {
			return FALSE;
		}
	}		

	function tunda_sidang($datainsert,$dataupdate,$id,$namahalaman){
		try {
			$namatabel="perkara_jadwal_sidang";
			$primarykey="id";
			$this->db->where('id',$id);
			$this->db->update('perkara_jadwal_sidang',$dataupdate);
			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id."]";
			$descrip = $this->fetch_description($title,$dataupdate);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);

			$nextid=$this->get_id_jadwal_terakhir_all();
			$datainsert['id']=$nextid;

			$namatabel="perkara_jadwal_sidang";
			$primarykey="id";
			$this->db->insert($namatabel, $datainsert);
			$title = "INSERT table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$datainsert['id']."]";
			$descrip = $this->fetch_description($title,$datainsert);
			$this->add_audittrail("INSERT",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}
	
	function update_ket_saksi($data_update,$id,$namahalaman){
		try {
			$namatabel="perkara_keterangan_saksi";
			$primarykey="id";
			$this->db->where($primarykey,$id);
			$this->db->update($namatabel,$data_update);
			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id."]";
			$descrip = $this->fetch_description($title,$dataupdate);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);			
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_pendapat_terdakwa($data_update,$id){
		try {
			$namatabel="perkara_pendapat_terdakwa";
			$primarykey="id";
			$this->db->where($primarykey,$id);
			$this->db->update($namatabel,$data_update);
			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id."]";
			$descrip = $this->fetch_description($title,$dataupdate);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);			
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_ket_terdakwa($data_update,$id,$namahalaman){
		try {
			$namatabel="perkara_keterangan_terdakwa";
			$primarykey="id";
			$this->db->where($primarykey,$id);
			$this->db->update($namatabel,$data_update);
			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id."]";
			$descrip = $this->fetch_description($title,$dataupdate);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);			
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function hapus_ket_saksi($id,$namahalaman){
		try {
			$namatabel="perkara_keterangan_saksi";
			$primarykey="id";
			$this->db->where($primarykey,$id);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function hapus_ket_saksi_noid($penanya_id,$saksi,$idperkara,$idjadwal,$namahalaman,$tanya=null,$jawab=null){
		try {
			$namatabel="perkara_keterangan_saksi";
			$primarykey1="penanya_id";
			$primarykey2="saksi_id";
			$primarykey3="perkara_id";
			$primarykey4="sidang_id";

			$this->db->where($primarykey1,$penanya_id);
			$this->db->where($primarykey2,$saksi);
			$this->db->where($primarykey3,$idperkara);
			$this->db->where($primarykey4,$idjadwal);
			if($tanya!=null)
				$primarykey5="pertanyaan";
				$this->db->where($primarykey5,$tanya);
			if($jawab!=null)
				$primarykey6="jawaban";
				$this->db->where($primarykey6,$jawab);
			$this->db->delete($namatabel);

			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$penanya_id." & ".$primarykey2."=".$saksi." & ".$primarykey3."=".$idperkara." & ".$primarykey4."=".$idjadwal."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}	

	function hapus_pend_terdakwa($id,$namahalaman){
		try {
			$namatabel="perkara_pendapat_terdakwa";
			$primarykey="id";
			$this->db->where($primarykey,$id);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function hapus_pend_terdakwa_noid($terdakwa,$saksi,$idperkara,$idjadwal,$pendapat,$namahalaman){
		try {
			$namatabel="perkara_pendapat_terdakwa";
			$primarykey1="pihak_id";
			$primarykey2="saksi_id";
			$primarykey3="perkara_id";
			$primarykey4="sidang_id";
			$primarykey5="pendapat";
			$this->db->where($primarykey1,$terdakwa);
			$this->db->where($primarykey2,$saksi);
			$this->db->where($primarykey3,$idperkara);
			$this->db->where($primarykey4,$idjadwal);
			$this->db->where($primarykey5,$pendapat);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$terdakwa." & ".$primarykey2."=".$saksi." & ".$primarykey3."=".$idperkara." & ".$primarykey4."=".$idjadwal." & ".$primarykey5."=".$pendapat."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function hapus_ket_terdakwa_noid($penanya_id,$terdakwa,$idperkara,$idjadwal,$namahalaman,$tanya,$jawab){
		try {
			$namatabel="perkara_keterangan_terdakwa";
			$primarykey1="penanya_id";
			$primarykey2="pihak_id";
			$primarykey3="perkara_id";
			$primarykey4="sidang_id";
			$primarykey5="pertanyaan";
			$primarykey6="jawaban";

			$this->db->where($primarykey1,$penanya_id);
			$this->db->where($primarykey2,$terdakwa);
			$this->db->where($primarykey3,$idperkara);
			$this->db->where($primarykey4,$idjadwal);
			$this->db->where($primarykey5,$tanya);
			$this->db->where($primarykey6,$jawab);
			$this->db->delete($namatabel);

			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$penanya_id." & ".$primarykey2."=".$terdakwa." & ".$primarykey3."=".$idperkara." & ".$primarykey4."=".$idjadwal." & ".$primarykey5."=".$tanya." & ".$primarykey6."=".$jawab."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function hapus_ket_terdakwa($id){
		try {
			$namatabel="perkara_keterangan_terdakwa";
			$primarykey="id";
			$this->db->where($primarykey,$id);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function insert_ket_saksi($datainsert,$namahalaman){
		try {
			$namatabel="perkara_keterangan_saksi";
			$primarykey1="perkara_id";
			$primarykey2="sidang_id";
			$primarykey3="penanya_id";
			$primarykey4="saksi_id";
			$this->db->insert($namatabel, $datainsert);
			$title = "INSERT table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$datainsert['perkara_id']." & ".$primarykey2."=".$datainsert['sidang_id']." & ".$primarykey3."=".$datainsert['penanya_id']." & ".$primarykey4."=".$datainsert['saksi_id']."]";
			$descrip = $this->fetch_description($title,$datainsert);
			$this->add_audittrail("INSERT",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function insert_pendapat_terdakwa($datainsert,$namahalaman){
		try {
			$namatabel="perkara_pendapat_terdakwa";
			$primarykey="id";
			$this->db->insert($namatabel, $datainsert);

			$title = "INSERT table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$datainsert['perkara_id']."]";
			$descrip = $this->fetch_description($title,$datainsert);
			$this->add_audittrail("INSERT",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function insert_ket_terdakwa($datainsert,$namahalaman){
		try {
			$namatabel="perkara_keterangan_terdakwa";
			$primarykey1="perkara_id";
			$primarykey2="sidang_id";
			$primarykey3="penanya_id";
			$primarykey4="pihak_id";
			
			$this->db->insert($namatabel, $datainsert);

			$title = "INSERT table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$datainsert['perkara_id']." & ".$primarykey2."=".$datainsert['sidang_id']." & ".$primarykey3."=".$datainsert['penanya_id']." & ".$primarykey4."=".$datainsert['pihak_id']."]";
			$descrip = $this->fetch_description($title,$datainsert);
			$this->add_audittrail("INSERT",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function insert_data_template_persidangan($datainsert,$namahalaman){
		try {
			$namatabel="perkara_data_template_sidang";
			$primarykey1="perkara_id";
			$primarykey2="sidang_id";
			$primarykey3="kolom_id";
			$this->db->insert('perkara_data_template_sidang', $datainsert);
			$title = "INSERT table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$datainsert['perkara_id']." & ".$primarykey2."=".$datainsert['sidang_id']." & ".$primarykey3."=".$datainsert['kolom_id']."]";
			$descrip = $this->fetch_description($title,$datainsert);
			$this->add_audittrail("INSERT",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data_template_s($data_update,$idperkara,$idjadwal,$kolom_id,$namahalaman){
		try {
			$namatabel="perkara_data_template_sidang";
			$primarykey1="perkara_id";
			$primarykey2="sidang_id";
			$primarykey3="kolom_id";
			$this->db->where($primarykey1,$idperkara);
			$this->db->where($primarykey2,$idjadwal);
			$this->db->where($primarykey3,$kolom_id);
			if($data_update->uraian==''){
				$this->db->delete($namatabel);
				$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$idperkara." & ".$primarykey2."=".$idjadwal." & ".$primarykey3."=".$kolom_id."]";
				$descrip = '';
				$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);			
			}else{
				$this->db->update($namatabel,$data_update);
				$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$idperkara." & ".$primarykey2."=".$idjadwal." & ".$primarykey3."=".$kolom_id."]";
				$descrip = $this->fetch_description($title,$dataupdate);
				$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);			
			}
			return TRUE;

		} catch (Exception $e) {
			return FALSE;
		}
	}

	function cek_data_template($idperkara,$idjadwal,$kolom_id){
		$qry="SELECT COUNT(*) AS jml FROM perkara_data_template_sidang WHERE perkara_id='".$idperkara."' and sidang_id='".$idjadwal."' and kolom_id='".$kolom_id."';";
		$hasil=$this->db->query($qry)->row()->jml;
		return $hasil;
	}

	function get_pp($idperkara){
		$qry="SELECT SUBSTRING_INDEX(panitera_pengganti_text,': ',-1) as pp FROM perkara_penetapan WHERE perkara_id=".$idperkara.";";
		$hasil=$this->db->query($qry)->row()->pp;
		return $hasil;
	}

	function show_data_template_s($idperkara,$idjadwal,$kolom_id){
		try {
			$query = $this->db->query("select uraian FROM perkara_data_template_sidang WHERE perkara_id='".$idperkara."' and sidang_id='".$idjadwal."' and kolom_id='".$kolom_id."';");
			return $query->row()->uraian;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function show_data_tnyjawab($idperkara,$idjadwal,$penanya_id,$saksi_id){
		try {
			$query = $this->db->query("select * from perkara_keterangan_saksi where perkara_id=".$idperkara." and sidang_id=".$idjadwal." and saksi_id=".$saksi_id." and penanya_id=".$penanya_id." order by penanya_id,saksi_id,id;");
			return $query;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function show_data_tnyjawab_terdakwa($idperkara,$idjadwal,$penanya_id,$pihak_id){
		try {
			$query = $this->db->query("select * from perkara_keterangan_terdakwa where perkara_id=".$idperkara." and sidang_id=".$idjadwal." and pihak_id=".$pihak_id." and penanya_id=".$penanya_id." order by penanya_id,pihak_id,id;");
			return $query;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function show_data_pendapat($idperkara,$idjadwal,$pihak_id,$saksi_id){
		try {
			$query = $this->db->query("select * from perkara_pendapat_terdakwa where perkara_id=".$idperkara." and sidang_id=".$idjadwal." and pihak_id=".$pihak_id." and saksi_id=".$saksi_id." order by pihak_id,saksi_id,id;");
			return $query;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function list_pihak_relaas($idperkara,$idjadwal){
		try {
			$query = $this->db->query(" SELECT a.pihak_id,a.nama,a.pihakke,a.ketpihak,b.tanggal_relaas,b.ket_temu,b.ket_hasil_relaas,b.doc_relaas FROM 
			(SELECT p1.pihak_id,p1.nama,1 AS pihakke,'pihak' AS ketpihak FROM perkara_pihak1 AS p1 WHERE perkara_id=".$idperkara." UNION
			SELECT p2.pihak_id,p2.nama,2 AS pihakke,'pihak' AS ketpihak FROM perkara_pihak2 AS p2 WHERE perkara_id=".$idperkara." UNION
			SELECT p3.pihak_id,p3.nama,3 AS pihakke, 'intervensi' AS ketpihak FROM perkara_pihak3 AS p3 WHERE perkara_id=".$idperkara." UNION 
			SELECT p4.pihak_id,p4.nama,4 AS pihakke, 'turut' AS ketpihak FROM perkara_pihak4 AS p4 WHERE perkara_id=".$idperkara." UNION 			
			SELECT pengacara_id,nama,pihak_ke AS pihhkke, 'pengacara' AS ketpihak FROM perkara_pengacara WHERE perkara_id=".$idperkara.") AS a
			LEFT JOIN (SELECT p1.pihak_id,p1.nama,1 AS pihakke,'pihak' AS ketpihak,tanggal_relaas,ket_temu,ket_hasil_relaas,doc_relaas FROM perkara_pihak1 AS p1 LEFT JOIN perkara_pelaksanaan_relaas AS pr ON p1.pihak_id=pr.pihak_id WHERE p1.perkara_id=".$idperkara." AND pr.sidang_id=".$idjadwal." UNION 
			SELECT p2.pihak_id,p2.nama,2 AS pihakke,'pihak' AS ketpihak,tanggal_relaas,ket_temu,ket_hasil_relaas,doc_relaas FROM perkara_pihak2 AS p2 LEFT JOIN perkara_pelaksanaan_relaas AS pr ON p2.pihak_id=pr.pihak_id WHERE p2.perkara_id=".$idperkara." AND pr.sidang_id=".$idjadwal." UNION 
			SELECT p3.pihak_id,p3.nama,3 AS pihakke,'intervensi' AS ketpihak,tanggal_relaas,ket_temu,ket_hasil_relaas,doc_relaas FROM perkara_pihak3 AS p3 LEFT JOIN perkara_pelaksanaan_relaas AS pr ON p3.pihak_id=pr.pihak_id WHERE p3.perkara_id=".$idperkara." AND pr.sidang_id=".$idjadwal." UNION
			SELECT p4.pihak_id,p4.nama,4 AS pihakke,'turut' AS ketpihak,tanggal_relaas,ket_temu,ket_hasil_relaas,doc_relaas FROM perkara_pihak4 AS p4 LEFT JOIN perkara_pelaksanaan_relaas AS pr ON p4.pihak_id=pr.pihak_id WHERE p4.perkara_id=".$idperkara." AND pr.sidang_id=".$idjadwal." UNION 			
			SELECT pengacara_id,nama,pihak_ke AS pihhkke, 'pengacara' AS ketpihak,tanggal_relaas,ket_temu,ket_hasil_relaas,doc_relaas FROM perkara_pengacara AS pp LEFT JOIN perkara_pelaksanaan_relaas AS pr ON pp.pengacara_id=pr.pihak_id WHERE pp.perkara_id=".$idperkara." AND pr.sidang_id=".$idjadwal.") AS b ON a.pihak_id=b.pihak_id;");		
			return $query;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function cek_hasil_relaas($idperkara,$idjadwal,$pihak_id){
		$qry="SELECT COUNT(*) AS jml FROM perkara_pelaksanaan_relaas WHERE perkara_id='".$idperkara."' and sidang_id='".$idjadwal."' and pihak_id='".$pihak_id."';";
		$hasil=$this->db->query($qry)->row()->jml;
		return $hasil;
	}

	function insert_hasil_relaas($datainsert,$namahalaman){
		try {
			$namatabel="perkara_pelaksanaan_relaas";
			$primarykey1="perkara_id";
			$primarykey2="sidang_id";
			$primarykey3="pihak_id";

			$this->db->insert($namatabel, $datainsert);

			$title = "INSERT table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$datainsert['perkara_id']." & ".$primarykey2."=".$datainsert['sidang_id']." & ".$primarykey3."=".$datainsert['pihak_id']."]";
			$descrip = $this->fetch_description($title,$datainsert);
			$this->add_audittrail("INSERT",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_hasil_relaas($data_update,$idperkara,$idjadwal,$pihakid,$namahalaman){
		try {
			$namatabel="perkara_pelaksanaan_relaas";
			$primarykey1="perkara_id";
			$primarykey2="sidang_id";
			$primarykey3="pihak_id";

			$this->db->where($primarykey1,$idperkara);
			$this->db->where($primarykey2,$idjadwal);
			$this->db->where($primarykey3,$pihakid);
			$this->db->update($namatabel,$data_update);

			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey1."=".$idperkara." & ".$primarykey2."=".$idjadwal." & ".$primarykey3."=".$pihakid."]";
			$descrip = $this->fetch_description($title,$dataupdate);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);			
			return TRUE;

		} catch (Exception $e) {
			return FALSE;
		}
	}

	function show_hasil_relaas($idperkara,$idjadwal,$kolom_id){
		try {
			$query = $this->db->query("select uraian FROM perkara_data_template_sidang WHERE perkara_id='".$idperkara."' and sidang_id='".$idjadwal."' and kolom_id='".$kolom_id."';");
			return $query->row()->uraian;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	##---
	function hapus_jadwal_sidang($id_jadwal_sidang,$namahalaman){
		try {
			$namatabel="perkara_jadwal_sidang";
			$primarykey="id";
			$this->db->where($primarykey,$id_jadwal_sidang);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id_jadwal_sidang."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function cari_jadwal_sidang_terakhir($perkara_id){
		$qry="SELECT * 
				FROM perkara_jadwal_sidang 
				WHERE tanggal_sidang = (SELECT MAX(tanggal_sidang) FROM perkara_jadwal_sidang WHERE perkara_id = $perkara_id) 
				AND perkara_id = $perkara_id;";
		$query=$this->db->query($qry);
		return $query;
	}

	function cek_apakah_verzet($idperkara){
		$qry="SELECT COUNT(*) AS jumlahdata FROM perkara_verzet WHERE perkara_id='$idperkara';";
		$hasil=$this->db->query($qry)->row()->jumlahdata;
		return $hasil;
	}

	function cek_jumlah_sidang_verzet($idperkara){
		$qry="SELECT COUNT(*) AS jumlahdata FROM perkara_jadwal_sidang WHERE perkara_id='$idperkara' AND verzet='Y';";
		$hasil=$this->db->query($qry)->row()->jumlahdata;
		return $hasil;
	}

	function cek_apakah_keberatan($idperkara){
		$qry="SELECT COUNT(*) AS jumlahdata FROM perkara_keberatan WHERE perkara_id='$idperkara';";
		$hasil=$this->db->query($qry)->row()->jumlahdata;
		return $hasil;
	}

	function cek_apakah_ikrar_talak($idperkara){
		$qry="SELECT COUNT(*) AS jumlahdata FROM perkara_ikrar_talak WHERE perkara_id='$idperkara';";
		$hasil=$this->db->query($qry)->row()->jumlahdata;
		return $hasil;
	}
	
	function cek_status_verzet_jadwalsidang_terakhir($idperkara){
		$qry="SELECT verzet FROM perkara_jadwal_sidang WHERE perkara_id='$idperkara' ORDER BY id DESC;";
		$hasil=$this->db->query($qry)->row()->verzet;
		return $hasil;
	}

	function get_jadwal_sidang_sebelum($idperkara){
		try {
			$qry="SELECT tanggal_sidang FROM perkara_jadwal_sidang 
					WHERE perkara_id='$idperkara' AND urutan=(SELECT MAX(IF(urutan=0,1,urutan))-1 FROM perkara_jadwal_sidang WHERE perkara_id='$idperkara');";
			$tgljsidangsebelum=$this->db->query($qry);
			$jumhasil=$tgljsidangsebelum->num_rows();
			if ($jumhasil>0){
				$tgljsidangsebelum=$tgljsidangsebelum->row()->tanggal_sidang;
				return $tgljsidangsebelum;
			}
		} catch (Exception $e) {
			return null;
		}
	}

	function getDataPenetapanSidang($idperkara){
		try{
			if(!is_numeric($idperkara)){ return '';	}
			$sql = "SELECT a.penetapan_hari_sidang, a.sidang_pertama, b.tanggal_sidang,  b.jam_sidang
					FROM perkara_penetapan AS a, perkara_jadwal_sidang AS b
					WHERE a.perkara_id=b.perkara_id AND b.urutan=1 AND a.perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e){ return false; }
	}

	function getDataPenetapanPanitera($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM 
						(SELECT perkara_id, panitera_id, tanggal_penetapan 
							FROM perkara_panitera_pn 
							WHERE perkara_id = $idperkara AND aktif='Y'
							GROUP BY panitera_id) AS ph
					LEFT JOIN
						(SELECT id, nama_gelar FROM panitera_pn) AS h
					ON ph.panitera_id = h.id";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getRefJenisTahanan(){
		try{
			$sql = "SELECT * 
					FROM jenis_tahanan";
			$query=$this->db->query($sql);
			return $query;
		}catch(Exception $e) { return false; }
	}

	function getDataPerkara($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT nomor_perkara,nomor_surat,tanggal_surat, tanggal_pendaftaran, dakwaan 
					FROM perkara 
					WHERE perkara_id =$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getJenisPerkara($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT nama 
					FROM jenis_perkara 
					WHERE id=(SELECT jenis_perkara_id 
								FROM perkara 
								WHERE perkara_id=$idperkara)";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPA(){
		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 ZonaWaktu FROM sys_config WHERE id = 75) AS T7";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getAllHakim(){
		try{
			$sql = "SELECT * FROM hakim_pn";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPihak($pihak_id){
		try{
			if(!is_numeric($pihak_id)) { return ''; }
			$sql = "SELECT * 
					FROM pihak 
					WHERE id=$pihak_id";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPanitera($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT DISTINCT perkara_id,urutan,panitera_id,panitera_nama,tanggal_penetapan 
					FROM perkara_panitera_pn 
					WHERE perkara_id=$idperkara";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataTerdakwa($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			 $sql="SELECT 		a.*
								,b.tempat_lahir
								,b.tanggal_lahir
								,b.jenis_kelamin
								,d.nama AS warga_negara
								,c.nama AS agama_nama
								,b.pekerjaan
							FROM perkara_pihak2 AS a
							LEFT JOIN pihak AS b
							ON b.id=a.pihak_id
							LEFT JOIN agama AS c
							ON c.id=b.agama_id
							LEFT JOIN negara AS d
							ON d.id=b.warga_negara_id
							
							WHERE a.perkara_id=$idperkara
							ORDER BY a.urutan";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPihakTerdakwa1($idperkara, $idpihak){
		try{
			if(!is_numeric($idperkara) OR !is_numeric($idpihak)) { return ''; }
			$sql = "SELECT 	a.pihak_id, a.urutan, 
						b.nama, b.alamat, b.tempat_lahir, b.tanggal_lahir, b.jenis_kelamin, a.pangkat, a.nrp, a.jabatan, a.kesatuan,
						c.`nama` AS agama_nama, d.`nama` AS warga_negara, b.pekerjaan 
					FROM 		perkara_pihak2 a
					LEFT JOIN pihak AS b ON a.pihak_id=b.id
					LEFT JOIN agama AS c ON  b.`agama_id`=c.`id`
					LEFT JOIN negara AS d ON b.`warga_negara_id`=d.`id`
					WHERE a.perkara_id = $idperkara AND a.pihak_id=$idpihak
					ORDER BY urutan ASC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPenahananTerdakwa($idperkara,$idpihak){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT 	pihak_id,
							jp.id AS idjns_penahanan,
							jp.nama AS oleh, 
							sp.nama AS status_penahanan, 
							jt.nama AS jenis_tahanan,
							pt.mulai AS sejak, 
							pt.sampai, 
							pt.lama_penahanan,
							pt.tanggal_surat as tglPenetapan,
							pt.id ,
							pt.tanggal_surat, pt.nomor_surat 
					FROM (SELECT * FROM penahanan_terdakwa WHERE perkara_id = $idperkara AND pihak_id = $idpihak 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 jp.id" ;
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}
	
	function getDataPenahanan($idpenahanan){
		try{
		$sql= "SELECT 	A.id,
						A.perkara_id,
						A.pihak_id,
						B.nama AS jenis_penahanan,
						C.nama AS status_penahanan,
						D.nama AS jenis_tahanan,
						A.tanggal_surat,
						A.nomor_surat,
						A.mulai,
						A.sampai,
						A.waktu_penahanan,
						A.lama_penahanan,
						A.rutan_id,
						E.nama
				FROM penahanan_terdakwa A 
				LEFT JOIN jenis_penahanan B ON A.jenis_penahanan_id=B.id 
				LEFT JOIN status_penahanan C ON A.status_penahanan_id=C.id
				LEFT JOIN jenis_tahanan D ON A.jenis_tahanan_id=D.id
				LEFT JOIN lembaga_pemasyarakatan AS E ON A.rutan_id = E.id
				WHERE A.id=$idpenahanan ";				
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataHakim($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM (SELECT perkara_id,urutan,jabatan_hakim_id, jabatan_hakim_nama, hakim_id, tanggal_penetapan 
							FROM perkara_hakim_pn 
							WHERE perkara_id = $idperkara AND aktif='Y'
							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 getDataPenetapanHakim($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * 
					FROM (SELECT perkara_id,urutan,jabatan_hakim_id, jabatan_hakim_nama, hakim_nama, hakim_id,tanggal_penetapan,aktif
							FROM perkara_hakim_pn 
							WHERE perkara_id = $idperkara AND aktif='Y'
							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 urutan ASC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataJurusita($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT B.nama_gelar,A.tanggal_penetapan FROM perkara_jurusita AS A LEFT JOIN jurusita AS B ON A.jurusita_id=B.id WHERE A.perkara_id='$idperkara'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataPP($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT B.nama_gelar,A.tanggal_penetapan FROM perkara_panitera_pn AS A LEFT JOIN panitera_pn AS B ON A.panitera_id=B.id WHERE A.perkara_id='$idperkara'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getAmar($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT amar_putusan FROM perkara AS 
			A LEFT JOIN perkara_putusan AS B ON A.perkara_id=B.perkara_id 
			WHERE A.perkara_id='$idperkara'";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getPenahananBefore($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM penahanan_terdakwa WHERE id = ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getKejaksaan($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT IF(a.alamat <> '',a.alamat, b.kejaksaan) AS kejaksaan  FROM 
					perkara_pihak1 AS a
					LEFT JOIN  pihak_jaksa AS b ON a.pihak_id=b.pihak_id
					WHERE a.perkara_id='".$idperkara."' ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function get_data_pihak_loop($idperkara,$jenispihak){
		try {
			if(!is_numeric($idperkara)){
				return '';
			}
			return $this->db->query('SELECT b.*,  a.*,c.nama AS kewarganegaraan, d.nama as agama,
											group_concat(e.pengacara_id) as pengacara_id 
											FROM pihak AS a
											LEFT JOIN
											(SELECT * FROM perkara_pihak'.$jenispihak.') AS b
											ON a.id=b.pihak_id
											LEFT JOIN
											(SELECT id,nama FROM negara) AS c
											ON a.warga_negara_id=c.id
											LEFT JOIN
											(SELECT id,nama FROM agama) AS d
											ON a.agama_id=d.id
											LEFT JOIN
											(SELECT pengacara_id, pihak_id, perkara_id FROM perkara_pengacara) AS e
											ON b.pihak_id=e.pihak_id and e.perkara_id=b.perkara_id
											WHERE b.perkara_id='.$idperkara.' GROUP BY b.id ORDER BY b.id ASC');
			} catch (Exception $e) {
				return '';
			}
	}

	function getTanggalPersidangan($idjadwal){
		try{
			if(!is_numeric($idjadwal)){
				return '';
		}
		return $this->db->query('SELECT * FROM perkara_jadwal_sidang WHERE id='.$idjadwal);
	}catch (Exception $e){
		}
	}

	function getTanggalPersidanganTKPertama($idperkara){
		try{
			if(!is_numeric($idperkara)){
				return '';
		}
		return $this->db->query("SELECT * FROM perkara_jadwal_sidang WHERE verzet='T' AND keberatan='T' AND ikrar_talak='T' AND perkara_id=".$idperkara);
	}catch (Exception $e){
		}
	}
	
	function getSidangTerakhir($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM perkara AS 
			A LEFT JOIN perkara_jadwal_sidang AS B ON A.perkara_id=B.perkara_id 
			WHERE A.perkara_id='$idperkara' ORDER BY B.urutan DESC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

    function getMediasiTerakhir($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM perkara AS A
			LEFT JOIN perkara_mediasi AS B ON A.perkara_id=B.perkara_id
			LEFT JOIN perkara_mediator AS C ON A.perkara_id=C.perkara_id
			WHERE A.perkara_id='$idperkara' ORDER BY B.mediasi_id DESC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getMeditor($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT * FROM perkara AS A
			LEFT JOIN perkara_mediator AS C ON A.perkara_id=C.perkara_id
			WHERE A.perkara_id='$idperkara' ORDER BY C.id DESC";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getDataSidangTunda($idperkara){
		try{
			if(!is_numeric($idperkara)) { return ''; }
			$sql = "SELECT id,urutan, tanggal_sidang
					FROM perkara_jadwal_sidang
					WHERE perkara_id =$idperkara AND verzet='T' AND keberatan='T' AND ikrar_talak='T' ";
			$query=$this->db->query($sql);
			return $query;	
		}catch(Exception $e) { return false; }
	}

	function getVerzet($idperkara){
		try {
			$result = $this->db->query('SELECT verzet FROM perkara_jadwal_sidang WHERE perkara_id ='.$idperkara);
			if($result->num_rows>0){
				return $result->row()->verzet;
			}else{
				return '';
			}
		} catch (Exception $e) {
			return '';
		}
	}
}
?>