<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Mediator extends CI_Model {
	function parseOrderby($col){
		if($col==1){
			return "status_mediator"; 
		}else if($col==2){
			return "nama"; 
		}else if($col==3){
			return "nama_gelar"; 
		}else if($col==4){
			return "profesi"; 
		}else if($col==5){
			return "aktif"; 
		}else{
			return "nama";
		}
	}

	function get_hakim_list(){
		try {
			return $this->db->query("SELECT id,nama,nama_gelar,nip,IF(aktif='T',0,1) AS status FROM hakim_pn ORDER BY nama ASC");
		} catch (Exception $e) {
			
		}
	}

	function get_pendidikan_list($id){
		if ($id!=NULL){
			$where=" WHERE id=".$id;
		}
		try {
			return $this->db->query("SELECT id,kode,nama FROM tingkat_pendidikan ".$where." ORDER BY kode ASC");
		} catch (Exception $e) {
			
		}
	}
	function get_mediator_list($col=3,$asc_desc='ASC',$begin=0,$key=''){
		$orderby = $this->parseOrderby($col);
		$where = '';
		if(!empty($key)){
			$where .= ' WHERE (nama LIKE "%'.$key.'%" OR nama_gelar LIKE "%'.$key.'%" OR profesi LIKE "%'.$key.'%"
				OR keterangan LIKE "%'.$key.'%") ';
		}
		try {
			return $this->db->query("SELECT SQL_CALC_FOUND_ROWS id, 
				CASE WHEN status_mediator='N' THEN 'Mediator Umum'
					WHEN status_mediator='H' THEN 'Hakim' 
					WHEN status_mediator='P' THEN 'Pegawai'
					END AS status_mediator_text, status_mediator, 
				nip, nama, nama_gelar, IF(aktif='T','Tidak','Aktif') AS aktif_text, aktif,profesi,keterangan
				FROM mediator ".$where." ORDER BY ".$orderby." ".$asc_desc." LIMIT ".$begin.",50;");
		} catch (Exception $e) {
			
		}
	}

	function fecth_data_num(){
		$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_mediator_info($id){
		if(empty($id)){
			return "";
		}
		try {
			return $this->db->query("SELECT id,	CASE WHEN status_mediator='N' THEN 1 
				WHEN status_mediator='H' THEN 2
				WHEN status_mediator='P' THEN 3
				END AS status_mediator, nip,kode, nama, nama_gelar,tempat_lahir,tgl_lahir,pendidikan_id,pendidikan,keahlian,tgl_sk_ketua,nomor_sk_ketua,foto,alamat,
				IF(aktif='T',0,1) AS aktif, hakim_id,keterangan,profesi, IF(bersertifikat='T',0,1) AS bersertifikat, no_sertifikasi, tgl_sertifikasi, lembaga_sertifikasi FROM mediator where id=".$id);
		} catch (Exception $e) {
			
		}
	}

	function get_hakim_info($id){
		if(empty($id)){
			return "";
		}
		try {
			$this->db->where('id',$id);
			$this->db->select('id,nip,nama, nama_gelar');
			return $this->db->get('hakim_pn');
		} catch (Exception $e) {
			
		}
	}

	

	function get_last_id(){
		try {
			$this->db->select_max('id');
			$query = $this->db->get('mediator');
			if ($query->num_rows() > 0){
				return $query->row();
			}else{
				return 0;
			}		
		} 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){
		try {
			$data = array(
				'datetime' => date("Y-m-d H:i:s"),
				'ipaddress' => $this->input->ip_address(),
				'username' => $this->session->userdata('username'),
				'tablename' => 'mediator',
				'formname' => 'Data Mediator',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}

	function add_data($data){
		try {
			$this->db->insert('mediator', $data);
			$title = "Tambah Mediator [Username=<b>".$data['nama']."</b>]<br />Tambah table <b>mediator</b> dari halaman <b>mediator</b> dengan Primary Key [id=".$data['id']."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("INSERT",$title,$descrip);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data($data,$id){
		try {
			$this->db->where('id', $id);
			$this->db->update('mediator', $data); 
			$title = "Edit Data Mediator [mediator=<b>".$data['nama']."</b>]<br />Edit table <b>mediator</b> dari halaman <b>mediator</b> dengan Primary Key [ID Mediator=".$id."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("UPDATE",$title,$descrip);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}


}
?>