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

class Data_pihak_diversi extends CI_Model{
	function getUrutanPihak($pihakKe,$IDPerkara, $tergabung_ke_pihak=0){
		if(is_numeric($pihakKe) AND $pihakKe>0){
			if(!is_numeric($pihakKe)){
				return FALSE;
			}
			
			if($pihakKe==6){
				$ekor = "_korban";
			}elseif ($pihakKe==7){
				$ekor="_penyidik";
			}elseif ($pihakKe==8){
				$ekor="_pembimbing_masyarakat";
			}elseif ($pihakKe==13){
				$ekor="5";
			}elseif ($pihakKe==20 || $pihakke==24){
				$ekor="diversi_anak_orang_tua";
			}elseif ($pihakKe==21){
				$ekor="diversi_penuntut";
			}elseif ($pihakKe==22){
				$ekor="diversi_penyidik";
			}elseif ($pihakKe==23){
				$ekor="diversi_korban_orang_tua";
			}elseif ($pihakKe==26){
				$ekor="diversi_pembimbing_kemasyarakatan";
			}
			else{
				$ekor = $pihakKe;
			}
			$table = $ekor;
		}else{
			return -1;
		}

		if(!is_numeric($IDPerkara) AND $IDPerkara<0){
			return -2;
		}
		try {
			$query = $this->db->query("SELECT MAX(urutan) AS urutan FROM ".$table." WHERE diversi_register_id =".$IDPerkara);	
			
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->urutan+1;
				}
	    	}else{
	    		return 1;
	    	}
		} catch (Exception $e) {
			return -3;
		}
	}

	function getAlurPerkaraId($idperkara){
		try {
			$result = $this->db->query('SELECT alur_perkara_id FROM perkara WHERE perkara_id = '.$idperkara.';');
			if($result->num_rows>0){
				return $result->row()->alur_perkara_id;
			}else{
				return '';
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function getIDPihak($idperkara,$id,$pihakKe){
		try {
			if(!is_numeric($pihakKe)){
				return FALSE;
			}
			if($pihakKe==6){
				$table = "perkara_pihak_korban";
			}elseif($pihakKe==13){
				$table = "perkara_pihak5";
			}else{
				$table = "perkara_pihak".$pihakKe;
			}
			$result = $this->db->query('SELECT pihak_id FROM '.$table.' WHERE perkara_id = '.$idperkara.' AND id = '.$id.';');
			if($result->num_rows>0){
				return $result->row()->pihak_id;
			}else{
				return 0;
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function isPihakberpekara($idpihak,$idperkara){
		try {
			return $this->db->query('SELECT pihak_anak
				FROM diversi_anak_orang_tua 
				WHERE pihak_anak = '.$idpihak.' AND diversi_register_id = '.$idperkara.';');
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function isAnakberperkara($idpihak,$idperkara){
		try {
			return $this->db->query('SELECT pihak_id, nama, status_perkara 
				FROM v_pihak_perkara 
				WHERE pihak_id = '.$idpihak.' AND perkara_id = '.$idperkara.';');
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function getPihakInfo($idpihak){
		try {
			return $this->db->query('SELECT id,nama,alamat,jenis_pihak_id FROM pihak WHERE id = '.$idpihak);
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function getJenisPihak($idpihak){
		try {
			$result = $this->db->query('SELECT jenis_pihak_id FROM pihak WHERE id = '.$idpihak);
			if($result->num_rows>0){
				return $result->row()->jenis_pihak_id;
			}else{
				return '';
			}
		} catch (Exception $e) {
			return '';
		}
	}

	function getInfoIntervensi($idpihak,$idperkara){
		try {
			return $this->db->query('SELECT id,pihak_id,tergabung_ke_pihak,jenis_intervensi_id,
				jenis_intervensi_nama, tanggal_izin_intervensi FROM perkara_pihak3 WHERE pihak_id = '.$idpihak.' AND perkara_id = '.$idperkara);
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function getInfoMiliter($idpihak,$idperkara,$pihakke){
		try {
			if ($pihakke==13){
				$tabelnya="5";
			}
			else
			{
				$tabelnya=$pihakke;
			}
			return $this->db->query('SELECT id, pihak_id, pangkat, nrp, jabatan, kesatuan FROM perkara_pihak'.$tabelnya.' WHERE pihak_id = '.$idpihak.' AND perkara_id = '.$idperkara);
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function getDetilInfoPihak($idPihak){
		try {
			return $this->db->query('SELECT p.id,jp.nama AS jenis_pihak,ji.nama AS jenis_indentitas,
					p.keterangan,nomor_indentitas,p.nama,tempat_lahir,tanggal_lahir,jenis_kelamin,
					golongan_darah,p.alamat,p.pekerjaan,st.nama AS status_kawin,n.nama AS warga_negara,t.nama AS pendidikan, a.nama AS agama
					FROM pihak AS p
					LEFT JOIN 
						negara AS n
					ON p.warga_negara_id = n.id
					LEFT JOIN 
						agama AS a
					ON p.agama_id = a.id
					LEFT JOIN
						tingkat_pendidikan AS t
					ON p.pendidikan_id = t.id
					LEFT JOIN 
						status_perkawinan AS st
					ON p.status_kawin = st.id
					LEFT JOIN 
						jenis_pihak AS jp
					ON p.jenis_pihak_id = jp.id
					LEFT JOIN 
						jenis_identitas AS ji
					ON p.jenis_indentitas = ji.id
					WHERE p.id='.$idPihak.';');
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}
	function getDetilInfoPihakTerdakwa($idPihak){
		try {
			return $this->db->query('SELECT p.id,jp.nama AS jenis_pihak,ji.nama AS jenis_indentitas,
					pp.jabatan,pp.pangkat,pp.nrp,pp.kesatuan,
					p.keterangan,nomor_indentitas,p.nama,tempat_lahir,tanggal_lahir,jenis_kelamin,
					golongan_darah,p.alamat,p.pekerjaan,st.nama AS status_kawin,n.nama AS warga_negara,t.nama AS pendidikan, a.nama AS agama
					FROM pihak AS p
					LEFT JOIN 
						perkara_pihak2 AS pp
					ON p.id = pp.pihak_id
					LEFT JOIN 
						negara AS n
					ON p.warga_negara_id = n.id
					LEFT JOIN 
						agama AS a
					ON p.agama_id = a.id
					LEFT JOIN
						tingkat_pendidikan AS t
					ON p.pendidikan_id = t.id
					LEFT JOIN 
						status_perkawinan AS st
					ON p.status_kawin = st.id
					LEFT JOIN 
						jenis_pihak AS jp
					ON p.jenis_pihak_id = jp.id
					LEFT JOIN 
						jenis_identitas AS ji
					ON p.jenis_indentitas = ji.id
					WHERE p.id='.$idPihak.';');
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}
	
	function getInfoPembawaSaksi($idperkara,$idpihak){
		try{
			$qry="SELECT saksi_pihak_ke FROM perkara_pihak5 where perkara_id=$idperkara AND pihak_id=$idpihak";
			$hasil=$this->db->query($qry)->row()->saksi_pihak_ke;
			return $hasil;
		} catch (Exception $e) {
			return '';
		}
	}
	#23042017
	function getJenisSaksi($idperkara,$idpihak){
		try{
			$qry="SELECT jenis_saksi FROM perkara_pihak5 where perkara_id=$idperkara AND pihak_id=$idpihak";
			$hasil=$this->db->query($qry)->row()->jenis_saksi;
			return $hasil;
		} catch (Exception $e) {
			return '';
		}
	}
	#end

	function getInfoParaPihak($idperkara,$pihakKe){
		try {
			$this->db->where('perkara_id',$idperkara);
			$this->db->select('id,pihak_id,nama,urutan');
			$this->db->order_by('urutan', "ASC");
			if(empty($pihakKe) OR $pihakKe<0 OR !is_numeric($pihakKe)){
				return '';
			}
			return $this->db->get('perkara_pihak'.$pihakKe);
		} catch (Exception $e) {
			return '';
		}
	}

	function add_data($data,$pihakKe){
		#jng lupa update table perkara
		try {
			if(!is_numeric($pihakKe)){
				return FALSE;
			}
			if($pihakKe==6){
				$table = "perkara_pihak_korban";
			}elseif($pihakKe==13){
				$table = "perkara_pihak5";
			}elseif($pihakKe==20  || $pihakke==24){
				$table = "diversi_anak_orang_tua";
			}elseif($pihakKe==21){
				$table = "diversi_penuntut";
			}elseif ($pihakKe==22){
				$table="diversi_penyidik";
			}elseif($pihakKe==23){
				$table = "diversi_korban_orang_tua";
			}elseif($pihakKe==26){
				$table = "diversi_pembimbing_kemasyarakatan";
			}else{
				$table = "perkara_pihak".$pihakKe;
			}
			

			# get pihak info
/*			$query = $this->getPihakInfo($data['pihak_id']);
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					$data['nama'] = $row->nama;
					$data['alamat'] = $row->alamat;
					$data['jenis_pihak_id'] = $row->jenis_pihak_id;
				}
			}else{
				return FALSE;
			}*/
			# get id from perkara_pihak..
			$id = $this->db->query("SELECT IFNULL(max(id)+1,1) as id FROM ".$table)->row()->id;
			if($id>0){
				$data['id'] = $id;
				$this->db->insert($table, $data);

				# insert audittrail
				$title = "Tambah Pihak Diversi [nama=<b>".$data['nama']."</b>]<br />Tambah table <b>".$table."</b> dari halaman <b>TAMBAH DATA PIHAK</b> dengan Primary Key [id=".$data['id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("INSERT",$title,$descrip,$table);
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}


	function updt_data_pihak($id,$data,$pihakKe){
		#jng lupa update table perkara
		try {
			if(!is_numeric($pihakKe)){
				return FALSE;
			}
			if($pihakKe==6){
				$table = "perkara_pihak_korban";
			}elseif($pihakKe==13){
				$table = "perkara_pihak5";
			}elseif($pihakKe==20  || $pihakke==24){
				$table = "diversi_anak_orang_tua";
			}elseif($pihakKe==21){
				$table = "diversi_penuntut";
			}elseif ($pihakKe==22){
				$table="diversi_penyidik";
			}elseif($pihakKe==23){
				$table = "diversi_korban_orang_tua";
			}elseif($pihakKe==26){
				$table = "diversi_pembimbing_kemasyarakatan";
			}else{
				$table = "perkara_pihak".$pihakKe;
			}

/*			$isDuplicate = $this->db->query("SELECT count(id) AS cnt FROM ".$table." WHERE perkara_id = ".$data['perkara_id']." AND pihak_id = ".$data['pihak_id']." AND urutan <>".$data['urutan'])->row()->cnt;
			if(intval($isDuplicate)!=0){
				return 5;
			}
*/			# get pihak info
/*			$query = $this->getPihakInfo($data['pihak_id']);
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					$data['nama'] = $row->nama;
					$data['alamat'] = $row->alamat;
					$data['jenis_pihak_id'] = $row->jenis_pihak_id;
				}
			}else{
				return FALSE;
			}*/
			# get id from perkara_pihak1
			$this->db->where('id', $id);
			$this->db->update($table, $data);

			# insert audittrail
			$title = "UPDATE Pihak Diversi [nama=<b>".$data['nama']."</b>]<br />UPDATE table <b>".$table."</b> dari halaman <b>UPDATE DATA PIHAK DIVERSI</b> dengan Primary Key [id=".$id."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("UPDATE",$title,$descrip,$table);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data($data,$IDPerkara,$pihakKe){
		try {
			if($pihakKe>4){
				return TRUE;
			}
			$functName = "GetListPihak".$pihakKe."Text(".$IDPerkara.")";
			$colname = "pihak".$pihakKe."_text";
			$listPihak = $this->db->query("SELECT ".$functName." AS pihak")->row()->pihak;
			$data[$colname] = $listPihak;
			$this->db->where('perkara_id', $IDPerkara);
			$this->db->update('perkara', $data);
			$result = $this->update_data_parapihak($data,$IDPerkara);
			if($result){
				return TRUE; 
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
		}
	}

	function del_data($id,$pihakKe){
		try {
			if(!is_numeric($pihakKe)){
				return FALSE;
			}
			if($pihakKe==6){
				$table = "perkara_pihak_korban";
			}elseif($pihakKe==13){
				$table = "perkara_pihak5";
			}elseif($pihakKe==20  || $pihakke==24){
				$table = "diversi_anak_orang_tua";
			}elseif($pihakKe==21){
				$table = "diversi_penuntut";
			}elseif ($pihakKe==22){
				$table="diversi_penyidik";
			}elseif($pihakKe==23){
				$table = "diversi_korban_orang_tua";
			}elseif($pihakKe==26){
				$table = "diversi_pembimbing_kemasyarakatan";
			}else{
				$table = "perkara_pihak".$pihakKe;
			}
			$this->db->query("DELETE FROM ".$table." WHERE id =".$id);
			$title = "Delete Data Pihak Diversi <br />dengan id [id=".$id."] Oleh = ".$this->session->userdata('username');
			$this->add_audittrail("DELETE",$title,'DELETE DATA',$table);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}


	function del_data_pengacara($id,$pihakKe,$idperkara){
		try {
			if(!is_numeric($pihakKe)){
				return FALSE;
			}
			$this->db->query("DELETE FROM perkara_pengacara WHERE pihak_id =".$id." AND pihak_ke = ".$pihakKe." AND perkara_id = ".$idperkara);
			//$title = "Delete Data Pihak <br />dengan id [id=".$id."] Oleh = ".$this->session->userdata('username');
			//$this->add_audittrail("DELETE",$title,'DELETE DATA',$table);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function add_data_ortu_diversi($idortu,$iddiversi,$idanak){
		try {

			$this->db->query("UPDATE diversi_anak_orang_tua set pihak_orang_tua=".$idortu." where diversi_register_id=".$iddiversi." and pihak_anak=".$idanak);
			//$title = "Delete Data Pihak <br />dengan id [id=".$id."] Oleh = ".$this->session->userdata('username');
			//$this->add_audittrail("DELETE",$title,'DELETE DATA',$table);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function add_data_ortu_diversi_korban($idortu,$iddiversi,$idanak){
		try {

			$this->db->query("UPDATE diversi_korban_orang_tua set pihak_orang_tua=".$idortu." where diversi_register_id=".$iddiversi." and pihak_anak=".$idanak);
			//$title = "Delete Data Pihak <br />dengan id [id=".$id."] Oleh = ".$this->session->userdata('username');
			//$this->add_audittrail("DELETE",$title,'DELETE DATA',$table);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}
	function update_data_parapihak($data,$IDPerkara){
		try {
			$parapihak = $this->db->query("SELECT GetListParaPihakText(".$IDPerkara.") AS parapihak")->row()->parapihak;
			$data['para_pihak'] = $parapihak;
			$this->db->where('perkara_id', $IDPerkara);
			$this->db->update('perkara', $data);
			return TRUE; 
		} catch (Exception $e) {
			
		}
	}

	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){
		try {
			$data = array(
				'datetime' => date("Y-m-d H:i:s"),
				'ipaddress' => $this->input->ip_address(),
				'username' => $this->session->userdata('username'),
				'tablename' => $tablename,
				'formname' => 'Add Pihak',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}

	function getInfoPihakIntervensi($idperkara,$idPihak){
		try {
			return $this->db->query('SELECT pihak_id, nama, isi_petitum FROM perkara_pihak3 WHERE perkara_id = '.$idperkara.' AND pihak_id = '.$idPihak);
		} catch (Exception $e) {
			
		}
	}

	function update_petitum($idperkara,$idpihak,$data){
		try {
			$this->db->where('perkara_id', $idperkara);
			$this->db->where('pihak_id', $idpihak);
			$this->db->update('perkara_pihak3', $data);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function isPihakAlreadyThere($idperkara,$idpihak){
		try {
			$this->db->where('perkara_id',$idperkara);
			$this->db->where('id',$idpihak);
			$result = $this->db->get('diversi_anak_orang_tua');
			if($result->num_rows>0)
				return TRUE;
			else
				return FALSE;
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}
}