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

class Add_penahanan_m extends CI_Model{
	
	function getdatarutan($keyword) { 			
        $this->db->order_by('id_propinsi', 'ASC');
        $this->db->order_by('id_kabupaten', 'ASC');
        $this->db->like("nama", $keyword);
        return $this->db->get('lembaga_pemasyarakatan')->result_array();
    }
	
	function get_perkara_id($nomor_perkara){
		try {
			$this->db->select('perkara_id');
			$this->db->where('nomor_perkara', $nomor_perkara);
			$result = $this->db->get('perkara');
			if($result->num_rows>0){
				return $result->row()->perkara_id;
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_alur_perkara_id($perkara_id){
		try {
			$this->db->select('alur_perkara_id');
			$this->db->where('perkara_id', $perkara_id);
			$result = $this->db->get('perkara');
			if($result->num_rows>0){
				return $result->row()->alur_perkara_id;
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_nomor_perkara($perkara_id){
		try {
			$this->db->select('nomor_perkara');
			$this->db->where('perkara_id', $perkara_id);
			$result = $this->db->get('perkara');
			if($result->num_rows>0){
				return $result->row()->nomor_perkara;
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_nama_pihak($perkara_id){
		try {
			$this->db->select('perkara_id, alur_perkara_id, pihak_id, nama');
			$this->db->where('perkara_id', $perkara_id);
			$hasil = $this->db->get('v_penahanan');
			if($hasil->num_rows>0){
				return $hasil->result();
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_nama_pihak_awal($perkara_id){
		try {
			$this->db->select('pp.perkara_id, p.alur_perkara_id, pp.pihak_id, pp.nama');
			$this->db->from('perkara_pihak2 pp');
			$this->db->join('perkara p', 'pp.perkara_id=p.perkara_id', 'left');
			$this->db->where('pp.perkara_id', $perkara_id);
			return $this->db->get()->result();
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_data_pihak($pihak_id){
		try {
			$this->db->select("pp.perkara_id, pihak_id, pp.nama, pp.alamat, p0.tempat_lahir, p0.tanggal_lahir, YEAR(p.tanggal_pendaftaran) - YEAR(p0.tanggal_lahir) AS umur, 
	(CASE WHEN (YEAR(p.tanggal_pendaftaran) - YEAR(p0.tanggal_lahir))>18 THEN 'Y' ELSE 'T' END) AS dewasa, p0.jenis_kelamin, p0.status_kawin, 
	ag.nama AS agama_nama, p0.pekerjaan, tp.nama AS pendidikan, n.nama AS warga_negara, pp.pangkat, pp.nrp, pp.jabatan, pp.kesatuan");
			$this->db->from('perkara_pihak2 as pp');
			$this->db->join('pihak as p0', 'p0.id=pp.pihak_id', 'left');
			$this->db->join('perkara as p', 'p.perkara_id=pp.perkara_id', 'left');
			$this->db->join('negara as n', 'n.id=p0.warga_negara_id', 'left');
			$this->db->join('agama as ag', 'ag.id=p0.agama_id', 'left');
			$this->db->join('tingkat_pendidikan as tp', 'tp.id=p0.pendidikan_id', 'left');
			$this->db->where('pp.pihak_id', $pihak_id);
			return $this->db->get()->result();
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_dakwaan($perkara_id){
		try {
			$this->db->select('dakwaan');
			$this->db->where('perkara_id', $perkara_id);
			return $this->db->get('perkara')->result();
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_data_penahanan($perkara_id, $pihak_id){
		try {
			if (!is_numeric($perkara_id) || !is_numeric($pihak_id)) return '';
			$qry = ("SELECT jp.nama AS ditahan_oleh, sp.nama AS status_penahanan, jt.nama AS jenis_tahanan, pt.keterangan, 
								pt. id, pt.perkara_id, pt.pihak_id, pt.tanggal_surat, 
								pt.nomor_surat, pt.mulai, pt.sampai, pt.waktu_penahanan, pt.lama_penahanan, pt.jenis_penahanan_id, 
								pt.status_penahanan_id, pt.jenis_tahanan_id, pt.rutan_id, lp.nama AS rutan_nama, pt.pejabat_penahan
							FROM (SELECT * 
								FROM penahanan_terdakwa  
								WHERE perkara_id=".$perkara_id." AND pihak_id=".$pihak_id.") AS pt
								LEFT JOIN jenis_penahanan jp ON jp.id=pt.jenis_penahanan_id
								LEFT JOIN status_penahanan sp ON sp.id=pt.status_penahanan_id 
								LEFT JOIN jenis_tahanan jt ON jt.id=pt.jenis_tahanan_id
								LEFT JOIN lembaga_pemasyarakatan lp ON lp.id=pt.rutan_id
							ORDER BY jp.urutan;");
			$query = $this->db->query($qry);
			return $query->result();
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	//template bantar
	function get_data_template_bantar($perkara_id,$pihak_id,$penahanan_id){
		try {
			$qry = "SELECT * FROM perkara_data_template_penahanan where perkara_id=".$perkara_id." and pihak_id=".$pihak_id." and penahanan_id=".$penahanan_id;
			return $this->db->query($qry);
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function cek_bantar($perkara_id, $pihak_id,$penahanan_id){
		try {
			$qry = "select count(*) as ada from perkara_data_template_penahanan where perkara_id=".$perkara_id." and pihak_id=".$pihak_id." and penahanan_id=".$penahanan_id;
			$query=$this->db->query($qry);
			return $query->row()->ada;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function insert_data_bantar_template($datainsert){

		try {
			$this->db->insert('perkara_data_template_penahanan', $datainsert);

			$title = "INSERT Data Bantar <br /> INSERT table <b>perkara_data_template_penahanan</b> dari halaman <b>Input Data Bantar</b> dengan Primary Key [perkara_id=".$datainsert['perkara_id']."]";
			$descrip = $this->fetch_description($title,$datainsert);
			$this->add_audittrail("INSERT",$title,$descrip,'perkara_data_template_penahanan');
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data_bantar_template($data_update,$perkara_id,$pihak_id,$penahanan_id){

		try {
			$this->db->where('perkara_id',$perkara_id);
			$this->db->where('pihak_id',$pihak_id);
			$this->db->where('penahanan_id',$penahanan_id);
			$this->db->update('perkara_data_template_penahanan',$data_update);

			$title = "UPDATE Data Bantar Template <br /> UPDATE table <b>perkara_data_template_penahanan</b> dari halaman <b>EDIT DATA BANTAR TEMPLATE</b> dengan Primary Key [id=".$id."]";
			$descrip = $this->fetch_description($title,$dataupdate);
			$this->add_audittrail("UPDATE",$title,$descrip,'perkara_data_template_penahanan');			
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}	

	function hapus_data_bantar($id_template_bantar){
		try {
			$this->db->where('id',$id_template_bantar);
			$this->db->delete('perkara_data_template_penahanan');
			
			$title = "DELETE Data Template Bantar <br /> DELETE table <b>perkara_data_template_penahanan</b> dari halaman <b>EDIT DATA TEMPLATE BANTAR</b> dengan Primary Key [id=".$id_template_bantar."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,'perkara_data_template_penahanan');
			
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function get_data_penjamin($pihak_id,$tanggal_permohonan){
		try {
			$qry = "SELECT p.*,YEAR('".$tanggal_permohonan."') - YEAR(tanggal_lahir) as umur,n.nama as nama_negara FROM pihak as p
			left join negara as n on p.warga_negara_id=n.id
			where p.id=".$pihak_id;

			return $this->db->query($qry);
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	//--
	function get_jenis_penahanan(){
		try {
			$idnya = array('31', '32', '33', '34');
			$jenis_pengadilan=$this->db->query("SELECT value FROM sys_config WHERE id='83'")->row()->value;
			$this->db->select('id, nama, batas_waktu, batas_waktu_anak'); 
			$this->db->where_not_in('id', $idnya);
			$this->db->where('aktif','Y');
			$this->db->where('jenis_pengadilan',$jenis_pengadilan);
			$this->db->order_by('urutan');
			return $this->db->get('jenis_penahanan')->result();
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_jenis_pembebas(){
		try {
			$idnya = array('15', '16','22', '31', '25', '32', '33', '29', '34');
			$jenis_pengadilan=$this->db->query("SELECT value FROM sys_config WHERE id='83'")->row()->value;
			$this->db->select('id, nama, batas_waktu, batas_waktu_anak');
			$this->db->where_in('id', $idnya);			
			$this->db->where('aktif','Y');
			$this->db->where('jenis_pengadilan',$jenis_pengadilan);
			$this->db->order_by('id');
			return $this->db->get('jenis_penahanan')->result();
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_status_penahanan(){
		try {
			if($this->session->userdata('jenis_pengadilan')==1 OR $this->session->userdata('jenis_pengadilan')==4){
				$where = ' AND id NOT IN (5)';
			}else{
				$where = '';
			}
				$sql=('SELECT id,nama FROM status_penahanan WHERE aktif = "Y" '.$where.' ORDER BY id');
				$result = $this->db->query($sql)->result();
				return $result;
		} catch (Exception $e) {
			return '';
		}
	}

	function get_jenis_tahanan(){
		try {
			$jenis_pengadilan=$this->db->query("SELECT value FROM sys_config WHERE id='83'")->row()->value;
			if($jenis_pengadilan==1){
				$this->db->where('id <>','4');
			}
			$this->db->select('id, nama');
			$this->db->where('aktif','Y');
			$this->db->where('id <>','0');
			$this->db->order_by('id');
			return $this->db->get('jenis_tahanan')->result();
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function insert_penambahan_penahanan($data){
		try {
			if($this->db->insert('penahanan_terdakwa', $data)){
				$title = "Insert table <b>penahanan_terdakwa</b> dari halaman <b>Penambahan Penahanan Terdakwa</b> dengan Primary Key [perkara_id=".$data['perkara_id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("INSERT",$title,$descrip,'penahanan_terdakwa');
				return true;
			}else{
				return false;
			};
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function edit_penambahan_penahanan($data, $penahanan_id){
		try {
			$this->db->where('id', $penahanan_id);
			$this->db->update('penahanan_terdakwa', $data);
			$title = "Edit table <b>penahanan_terdakwa</b> dari halaman <b>Penambahan Penahanan Terdakwa</b> dengan Primary Key [perkara_id=".$data['perkara_id']."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("UPDATE",$title,$descrip,'penahanan_terdakwa');
		} catch (Exception $e) {
			log_message('error', $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/edit penahanan tersangka/terdakwa',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	function hapus_penahanan($idpenahanan){
		try{
			$this->db->where('id',$idpenahanan);
			$this->db->delete('penahanan_terdakwa');
			//hapus data template penahanan bila ada
			$this->db->query("DELETE FROM perkara_data_template_penahanan WHERE penahanan_id=".$idpenahanan);
			$title = "DELETE Data Penahanan <br /> DELETE table <b>penahanan_terdakwa</b> dari halaman <b>EDIT DATA PENAHANAN</b> dengan Primary Key [id=".$idpenahanan."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,'penahanan_terdakwa');
			
			return TRUE;
		}catch (Exception $e){
			log_message('error', $e);
		}
	}
}
/* End of file Add_penahanan_m.php */
/* Location: ./application/models/penahanan/Add_penahanan_m.php */