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

class Data_pengacara extends CI_Model{
	function getUrutanPengacara($pihakKe,$IDPerkara){
		if(!is_numeric($IDPerkara) AND $IDPerkara<0){
			return -2;
		}
		try {
			$query = $this->db->query("SELECT MAX(urutan) AS urutan FROM perkara_pengacara WHERE perkara_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 {
			return $this->db->query('SELECT alur_perkara_id FROM perkara WHERE perkara_id = '.$idperkara.';')->row()->alur_perkara_id;
		} 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 getPihakDiwakilkan($idperkarapengacara){
		try {
			if(!is_numeric($idperkarapengacara)){
				return '';
			}
			return $this->db->query("SELECT p.id AS pihak_id,p.nama 
				FROM pihak AS p, perkara_pengacara AS pp
				WHERE p.id = pp.pihak_id AND pp.id =".$idperkarapengacara);
		} catch (Exception $e) {
			
		}
	}

	function getDetilInfoPengacara($idPengacara,$idperkara){
		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,pekerjaan,st.nama AS status_kawin,
					n.nama AS warga_negara,t.nama AS pendidikan, a.nama AS agama,
					pp.aktif_mulai, pp.aktif_sampai,pp.aktif AS aktif,pp.nomor_kuasa,pp.tanggal_kuasa
					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
					LEFT JOIN perkara_pengacara AS pp
					ON  pp.pengacara_id = p.id
					WHERE p.id='.$idPengacara.' AND perkara_id = '.$idperkara.';');
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	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){
		#jng lupa update table perkara
		try {
			$table = "perkara_pengacara";

			# get pihak info
			$query = $this->getPihakInfo($data['pengacara_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;
			}
			
			$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 Pengacara Pihak [nama=<b>".$data['nama']."</b>]<br />Tambah table <b>".$table."</b> dari halaman <b>TAMBAH DATA PENGACARA 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_pengacara($id,$data){
		#jng lupa update table perkara
		try {

			# get pihak info
			$query = $this->getPihakInfo($data['pengacara_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;
			}
			$this->db->where('id', $id);
			$this->db->update('perkara_pengacara', $data);

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

	function update_data($data,$IDPerkara,$pihakKe){
		try {
			$functName = "GetListPengacaraPihak".$pihakKe."Text(".$IDPerkara.")";
			$colname = "pengacara_pihak".$pihakKe;
			$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($idpengacara,$pihakke,$idperkara){
		try {
			return $this->db->query("DELETE FROM perkara_pengacara WHERE perkara_id =".$idperkara." AND pengacara_id =".$idpengacara." AND pihak_ke =".$pihakke);
		} 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 isPengacaraAlreadyThere($idperkara,$idpengacara,$idpihak){
		try {
			$result = $this->db->query('SELECT count(*) AS cnt FROM perkara_pengacara WHERE perkara_id = '.$idperkara.' AND pengacara_id = '.$idpengacara.' AND pihak_id ='.$idpihak.';');
			if($result->num_rows>0){
				if ($result->row()->cnt>1){
					return TRUE;
				}else{
					return FALSE;
				}
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			return '';
		}
	}

	function isPengacaraAlreadyAssign($idperkara,$idpengacara,$pihakke){
		try {
			$result = $this->db->query('SELECT count(*) AS cnt FROM perkara_pengacara WHERE perkara_id = '.$idperkara.' AND pengacara_id = '.$idpengacara.' AND pihak_ke ='.$pihakke.';');
			if($result->num_rows>0){
				if ($result->row()->cnt>0){
					return TRUE;
				}else{
					return FALSE;
				}
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			return '';
		}
	}

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