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

class OrangTua_Wali_Diversi extends CI_Model{
	function getInfoPerkaraAnak($idperkara){
		try {
			return $this->db->query("SELECT 
										dao.id 				AS id,
										dao.urutan			AS urutan,
										dao.diversi_register_id 	AS perkara_id,
										p.nama 				AS nama,
										dao.pihak_anak 			AS pihak_id,
										p.alamat 			AS alamat,
										dao.pihak_orang_tua 		AS id_orang_tua,
										po.nama				AS wali
									FROM diversi_anak_orang_tua AS dao
									LEFT JOIN pihak AS p ON dao.pihak_anak=p.id
									LEFT JOIN pihak AS po ON po.id=dao.pihak_orang_tua
									WHERE diversi_register_id=".$idperkara." ORDER BY dao.urutan ASC;");
		} catch (Exception $e) {
			
		}
	}

	function getInfoKorbanAnak($idperkara){
		try {
			return $this->db->query("SELECT 
										dao.id 				AS id,
										dao.urutan			AS urutan,
										dao.diversi_register_id 	AS perkara_id,
										p.nama 				AS nama,
										dao.pihak_anak 			AS pihak_id,
										p.alamat 			AS alamat,
										dao.pihak_orang_tua 		AS id_orang_tua,
										po.nama				AS wali
									FROM diversi_korban_orang_tua AS dao
									LEFT JOIN pihak AS p ON dao.pihak_anak=p.id
									LEFT JOIN pihak AS po ON po.id=dao.pihak_orang_tua
									WHERE diversi_register_id=".$idperkara." ORDER BY dao.urutan ASC;");
		} catch (Exception $e) {
			
		}
	}

	function getInfoAnakSaksi($idperkara){
		try {
			return $this->db->query("SELECT pp.id AS id, pp.urutan, pp.perkara_id, pp.nama,pp.pihak_id AS pihak_id, pp.alamat,ortu.id_orang_tua,p.nama AS wali
					FROM perkara_pihak5 AS pp
					LEFT JOIN 
					(SELECT * FROM perkara_pihak_orang_tua  WHERE jenis_pihak_anak = 2) AS ortu
					ON pp.pihak_id = ortu.id_anak AND pp.perkara_id = ortu.perkara_id
					LEFT JOIN
					pihak AS p
					ON p.id = ortu.id_orang_tua
					WHERE pp.perkara_id=".$idperkara." ORDER BY pp.urutan ASC;");
		} catch (Exception $e) {
			
		}
	}

	function getDataPihakOrtu($idperkara,$idanak){
		try {
			return $this->db->query("SELECT * FROM perkara_pihak_orang_tua
						WHERE perkara_id=$idperkara AND id_anak=$idanak;");
		} 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,
					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,telepon,email
					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.' GROUP BY p.id');
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}

	function getNamaOrtu($idperkara){
		try {
			return $this->db->query("SELECT p.nama,t.id_anak FROM pihak AS p
						LEFT JOIN perkara_pihak_orang_tua AS t
						ON p.id=t.id_orang_tua
						WHERE t.perkara_id=$idperkara AND t.jenis_pihak_anak=2;");
		} catch (Exception $e) {
			
		}
	}

	function del_data($idperkara,$idanak){
		try {
			$this->db->query("UPDATE diversi_anak_orang_tua SET pihak_orang_tua=NULL WHERE diversi_register_id =".$idperkara." AND pihak_anak = ".$idanak);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function del_data_korban($idperkara,$idanak){
		try {
			$this->db->query("UPDATE diversi_korban_orang_tua SET pihak_orang_tua=NULL WHERE diversi_register_id =".$idperkara." AND pihak_anak = ".$idanak);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	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,$data_ortu){
		try {
			$pihakID = $this->db->query("SELECT IFNULL(max(id)+1,1) as id FROM pihak;")->row()->id;
			if($pihakID>0){
				$data['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');

				$this->db->REPLACE('perkara_pihak_orang_tua', $data_ortu);
				$title = "Tambah Data Orangtuan [id anak=<b>".$data_ortu['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_ortu['perkara_id']."]";
				$descrip = $this->fetch_description($title,$data_ortu);
				$this->add_audittrail("INSERT",$title,$descrip,'perkara_pihak_orang_tua');
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data($data,$idortu,$data_ortu){
		try {
			if($idortu>0){
				$data['id'] = $idortu;
				$this->db->where('id', $idortu);
				$this->db->update('pihak', $data); 
				$title = "Ubah Data Pihak [nama=<b>".$data['nama']."</b>]<br />Update table <b>Pihak</b> dari halaman <b>TAMBAH ORANG TUA/WALI ANAK SAKSI</b> dengan Primary Key [id=".$idortu."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("UPDATE",$title,$descrip,'pihak');

				$this->db->REPLACE('perkara_pihak_orang_tua', $data_ortu);
				$title = "Edit Data Orang Tua/Wali [id anak=<b>".$data_ortu['id_anak']."</b>]<br />Edit table <b>perkara_pihak_orang_tua</b> dari halaman <b>TAMBAH ORANG TUA/WALI ANAK SAKSI</b> dengan Primary Key [id=".$data_ortu['perkara_id']."]";
				$descrip = $this->fetch_description($title,$data_ortu);
				$this->add_audittrail("UPDATE",$title,$descrip,'perkara_pihak_orang_tua');
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}	

}