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

class List_kurator extends CI_Model{
	
	function parseOrderby($col){
		if($col==1){
			return "nama"; 
		}else if($col==2){
			return "alamat"; 
		}else if($col==3){
			return "tanggal_ijin"; 
		}else if($col==4){
			return "nomor_ijin"; 
		}else if($col==5){
			return "tanggal_penyumpahan"; 
		}else if($col==6){
			return "tanggal_pendaftaran_pn"; 
		}else{
			return "id";
		}
	}

	function getListKurator($col=1,$type='DESC',$begin=0,$key='',$column=''){
		try {
			$orderby = $this->parseOrderby($col);
			$where = ' WHERE id IN (SELECT pihak_id FROM kurator) ';
			$column = $this->nativesession->get_flash_session('column');
			$whereLike = '';
			if(!empty($key)){
					$whereLike .= 'WHERE (p.nama LIKE "%'.$key.'%" OR alamat LIKE "%'.$key.'%" OR 
					tanggal_ijin LIKE "%'.$key.'%" OR nomor_ijin LIKE "%'.$key.'%" OR
					tanggal_penyumpahan LIKE "%'.$key.'%" OR tanggal_pendaftaran_pn LIKE "%'.$key.'%")'; 
			}
			return $this->db->query("SELECT SQL_CALC_FOUND_ROWS p.id,p.nama,alamat,tanggal_ijin,nomor_ijin,
				tanggal_penyumpahan,tanggal_pendaftaran_pn 
				FROM (SELECT * FROM pihak ".$where." ORDER BY ".$orderby." ".$type." LIMIT ".$begin.",30) as p
				LEFT JOIN
				kurator as k
				ON k.pihak_id = p.id ".$whereLike.";");
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	function getListCountry(){
		try {
			return $this->db->query('SELECT id,nama FROM negara;');
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	function getListAgama(){
		try {
			return $this->db->query('SELECT id,nama FROM agama WHERE aktif="Y" ORDER BY nama;');
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	function getListPendidikan(){
		try {
			return $this->db->query('SELECT id,kode AS nama FROM tingkat_pendidikan WHERE aktif="Y" ORDER BY nama;');
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	function fecth_data_num(){
		try {
			$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;
	    	}
		} catch (Exception $e) {
			
		}
	}

	function getInfoPihak($idPihak){
		try {
			return $this->db->query('SELECT p.id,jp.nama AS jenis_pihak,ji.nama AS jenis_indentitas_text,
					tanggal_ijin,nomor_ijin,tanggal_penyumpahan,tanggal_pendaftaran_pn,alamat_kantor,
					telepon_kantor,fax_kantor,mulai_ijin,sampai_ijin,
					jenis_pihak_id,warga_negara_id,agama_id,status_kawin AS status_kawin_id,pendidikan_id,
					p.keterangan,nomor_indentitas,p.nama,tempat_lahir,tanggal_lahir,jenis_kelamin,jenis_indentitas,
					golongan_darah,alamat,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
					LEFT JOIN kurator as k
					ON k.pihak_id = p.id
					WHERE p.id='.$idPihak.';');
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	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 Referensi Pihak',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}

	function add_data($data,$dataKurator){
		try {
			$pihakID = $this->db->query("SELECT IFNULL(max(id)+1,1) as id FROM pihak;")->row()->id;
			if($pihakID>0){
				$data['id'] = $pihakID;
				$dataKurator['pihak_id'] = $pihakID;
				$this->db->insert('pihak', $data);
				$title = "Tambah Data Pihak [nama=<b>".$data['nama']."</b>]<br />Tambah table <b>Pihak</b> dari halaman <b>TAMBAH REFERENSI DATA PIHAK</b> dengan Primary Key [id=".$data['id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("INSERT",$title,$descrip,'pihak');

				
				$id = $this->db->query("SELECT IFNULL(max(id)+1,1) as id FROM kurator")->row()->id;
				if($id>0){
					$dataKurator['id'] = $id;
					$this->db->insert('kurator', $dataKurator);

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

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

	function add_data_ortu($data){
		try {
			$this->db->REPLACE('perkara_pihak_orang_tua', $data);
			$title = "Tambah Data Orangtuan [id anak=<b>".$data['id_anak']."</b>]<br />Tambah table <b>perkara_pihak_orang_tua</b> dari halaman <b>TAMBAH DATA ORANG TUA</b> dengan Primary Key [id=".$data['perkara_id']."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("INSERT",$title,$descrip,'perkara_pihak_orang_tua');
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data($data,$dataKurator,$pihakID){
		try {
			if($pihakID>0){
				$data['id'] = $pihakID;
				$this->db->where('id', $pihakID);
				$this->db->update('pihak', $data); 
				$title = "Ubah Data Pihak [nama=<b>".$data['nama']."</b>]<br />Update table <b>Pihak</b> dari halaman <b>TAMBAH REFERENSI DATA KURATOR</b> dengan Primary Key [id=".$data['id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("UPDATE",$title,$descrip,'pihak');

				$this->db->where('pihak_id', $pihakID);
				$this->db->update('kurator', $dataKurator); 
				$title = "Ubah Data Kurator [nama=<b>".$dataKurator['nama']."</b>]<br />Update table <b>Kurator</b> dari halaman <b>TAMBAH REFERENSI DATA KURATOR</b> dengan Primary Key [id=".$data['id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("UPDATE",$title,$descrip,'kurator');

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

}