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

class diversi_m extends CI_Model{
	
	function get_next_id_diversi(){
		$query = $this->db->query('SELECT IFNULL(MAX(id)+1,1) AS nextid FROM perkara_diversi');
		return $query->row()->nextid;
		return $result;
	}

	function get_no_perkara($perkara_id){
		try {
			$query = $this->db->query("SELECT A.nomor_perkara, A.alur_perkara_id, A.perkara_id, A.pasal_dakwaan, MAX(B.tanggal_penetapan) AS tglpenhakim
										FROM perkara A, perkara_hakim_pn B
										WHERE A.perkara_id=B.perkara_id
										AND A.perkara_id='".$perkara_id."';");
			return $query->result();
		} catch (Exception $e) {
			return '';
		}
	}

	function get_data_diversi($perkara_id){
		try {
			$this->db->where("perkara_id", $perkara_id);
			return $this->db->get('perkara_diversi')->row(0);
		} catch (Exception $e) {
			return '';
		}
	}

	function get_data_terdakwa($perkara_id){
		try {
			$query=$this->db->query("SELECT 
                                        anak.id 
                                        ,anak.urutan
                                        ,anak.nama as nama_anak
                                        ,anak.alamat 
                                        ,anak.pihak_id
                                        ,(SELECT a.nama FROM jenis_penahanan AS a WHERE a.id=anak.ditahan_oleh_id) AS ditahan_oleh
                                        ,(SELECT b.nama FROM status_penahanan AS b WHERE b.id=anak.status_penahanan_id) AS status_penahanan 
                                        ,(SELECT c.nama FROM jenis_tahanan AS c WHERE c.id=anak.jenis_tahanan_id) AS jenis_tahanan
                                        ,ot.id_orang_tua
                                        ,p.nama as nama_orang_tua           
                                    FROM perkara_pihak2 AS anak
                                    	LEFT JOIN perkara_pihak_orang_tua AS ot ON ot.id_anak=anak.pihak_id
                                        LEFT JOIN pihak p ON ot.id_orang_tua=p.id
                                    WHERE anak.perkara_id=".$perkara_id." ORDER BY anak.urutan");
			return $query->result();
		} catch (Exception $e) {
			return '';
		}
	}

	function get_data_korban($perkara_id){
		try {
			$query=$this->db->query("SELECT pp2.id, pp2.urutan, pp2.perkara_id, pp2.pihak_id AS id_anak, pp2.nama AS nama_anak, pot.id_orang_tua, p.nama AS nama_orang_tua 
									FROM (SELECT * FROM perkara_pihak_korban WHERE perkara_id=".$perkara_id.") AS pp2
										LEFT JOIN perkara_pihak_orang_tua pot ON pp2.pihak_id=pot.id_anak AND pot.jenis_pihak_anak=1
										LEFT JOIN pihak p ON p.id=pot.id_orang_tua;");
			return $query->result();
		} catch (Exception $e) {
			return '';
		}
	}

	function get_data_pembimbing($perkara_id){
		try {
			$this->db->where("perkara_id", $perkara_id);
			return $this->db->get('perkara_pihak_pembimbing_masyarakat')->result();
		} catch (Exception $e) {
			return '';
		}
	}

	function get_data_peksos($perkara_id){
		try {
			$this->db->where("perkara_id", $perkara_id);
			return $this->db->get('perkara_pihak_pekerja_sosial_profesional')->result();
		} catch (Exception $e) {
			return '';
		}
	}

	function get_data_perwakilan_m($perkara_id){
		try {
			$this->db->where("perkara_id", $perkara_id);
			return $this->db->get('perkara_pihak_perwakilan_masyarakat')->result();
		} catch (Exception $e) {
			return '';
		}
	}

	function get_jadwal_diversi_terakhir($iddiversi){
		try {
			$qry="SELECT tanggal_musyawarah, dihadiri_oleh, jam_musyawarah, sampai_jam, tempat FROM perkara_jadwal_musyawarah_diversi 
					WHERE diversi_id='$iddiversi' AND urutan=(SELECT MAX(urutan) FROM perkara_jadwal_musyawarah_diversi WHERE diversi_id='$iddiversi');";			
			return $this->db->query($qry)->row(0);
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function get_diversi_register($iddiversi){
		try{
			$qry="SELECT CONCAT('<div style=\"text-align:center;display:table\">',A.no_diversi,'<br\>jo<br\>',A.no_perkara,'</div>') AS no_penetapan, A.id as iddiversiregister
					FROM diversi_register A, perkara_diversi B 
					WHERE A.id=B.diversi_register_id 
							AND B.id='$iddiversi';";
			return $this->db->query($qry)->row(0);
		}catch (Exception $e) {
			return FALSE;
		}
	}

	function get_data_barangbukti($id_perkara){
		try{
			$this->db->where('perkara_id',$id_perkara);
			return $this->db->get('perkara_barang_bukti');
		}catch(Exception $e){
			return FALSE;
		}
	}

	function get_uraian_barang_bukti($id_perkara){
		try{
			$qry="SELECT GROUP_CONCAT(jenis_barang_bukti,' ',tindakan_utk_pen_kesepakatan_diversi SEPARATOR ', ') AS uraian_barang_bukti
					FROM perkara_barang_bukti WHERE perkara_id=$id_perkara";
			return $this->db->query($qry)->row(0);
		}catch(Exception $e){
			return FALSE;
		}
	}

	function simpan_data_awal($perkara_id,$data_diversi,$tgl_musyawarah,$namahalaman){
		try {
			$namatabel="perkara_diversi";
			$primarykey="id";
			$this->db->insert('perkara_diversi', $data_diversi);
			$title = "INSERT table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [id=".$data_diversi->id."]";
			$descrip = $this->fetch_description($title,$data_diversi);
			$this->add_audittrail("INSERT",$title,$descrip,$namatabel,$namahalaman);

			return true;
		} catch (Exception $e) {
			return false;
		}
	}

	function edit_perkara_diversi($iddiversi,$data_diversi,$namahalaman){
		try{
			$namatabel="perkara_diversi";
			$primarykey="id";
			$this->db->where('id',$iddiversi);
			$this->db->update('perkara_diversi',$data_diversi);
			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$iddiversi."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		}catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data_barangbukti($id_bb,$dataupdate,$namahalaman){
		try{
			$namatabel="perkara_barang_bukti";
			$primarykey="id";
			$this->db->where($primarykey,$id_bb);
			$this->db->update($namatabel,$dataupdate);
			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id_bb."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		}catch(Exception $e){
			return FALSE;
		}
	}

	function hapus_diversi($id_perkara, $id_diversi,$namahalaman){
		try{
			$namatabel="perkara_pihak_pembimbing_masyarakat";
			$primarykey="perkara_id";
			$this->db->where($primarykey,$id_perkara);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Foreign Key [".$primarykey."=".$id_perkara."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			
			$namatabel="perkara_pihak_pekerja_sosial_profesional";
			$primarykey="perkara_id";
			$this->db->where($primarykey,$id_perkara);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Foreign Key [".$primarykey."=".$id_perkara."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);

			$namatabel="perkara_pihak_perwakilan_masyarakat";
			$primarykey="perkara_id";
			$this->db->where($primarykey,$id_perkara);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Foreign Key [".$primarykey."=".$id_perkara."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);

			$namatabel="perkara_jadwal_musyawarah_diversi";
			$primarykey="diversi_id";
			$this->db->where($primarykey,$id_diversi);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Foreign Key [".$primarykey."=".$id_perkara."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
					
			$dataresetbb=array(
				'tindakan_utk_pen_kesepakatan_diversi' => NULL
			);
			$namatabel="perkara_barang_bukti";
			$primarykey="perkara_id";
			$this->db->where('perkara_id',$id_perkara);
			$this->db->update('perkara_barang_bukti',$dataresetbb);
			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Foreign Key [".$primarykey."=".$id_perkara."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);

			$namatabel="diversi_register";
			$primarykey="perkara_id";
			$this->db->where($primarykey,$id_perkara);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Foreign Key [".$primarykey."=".$id_perkara."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			
			$namatabel="perkara_diversi";
			$primarykey="perkara_id";
			$this->db->where($primarykey,$id_perkara);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Foreign Key [".$primarykey."=".$id_perkara."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);

			return true;
		}catch(Exception $e){
			return FALSE;
		}
	}

	function add_diversi_register($data_diversi,$namahalaman){
		try{
			$namatabel="diversi_register";
			$primarykey="id";

			$this->db->insert('diversi_register',$data_diversi);

			$title = "INSERT table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$data_diversi->id."]";
			$descrip = $this->fetch_description($title,$data_diversi);
			$this->add_audittrail("INSERT",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		}catch (Exception $e){
			return FALSE;
		}
	}

	function update_diversi_register($iddiversiregister,$data_diversi,$namahalaman){
		try{
			$namatabel="diversi_register";
			$primarykey="id";

			$this->db->where($primarykey,$iddiversiregister);
			$this->db->update($namatabel,$data_diversi);

			$title = "UPDATE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$iddiversiregister."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("UPDATE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		}catch (Exception $e) {
			return FALSE;
		}
	}
	
	function hapus_diversi_register($id_diversi_register,$namahalaman){
		try{
			$namatabel="diversi_register";
			$primarykey="id";
			$this->db->where($primarykey,$id_diversi_register);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Primary Key [".$primarykey."=".$id_diversi_register."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			return TRUE;
		}catch(Exception $e){
			return FALSE;
		}
	}
	
	function hapus_jadwal_diversi($diversi_id,$namahalaman){
		try{
			$namatabel="perkara_jadwal_musyawarah_diversi";
			$primarykey="diversi_id";
			$this->db->where($primarykey,$diversi_id);
			$this->db->delete($namatabel);
			$title = "DELETE table <b>".$namatabel."</b> dari halaman <b>".$namahalaman."</b> dengan Foreign Key [".$primarykey."=".$diversi_id."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,$namatabel,$namahalaman);
			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,$namahalaman){
		try {
			$data = array(
				'datetime' => date("Y-m-d H:i:s"),
				'ipaddress' => $this->input->ip_address(),
				'username' => $this->session->userdata('username'),
				'tablename' => $tablename,
				'formname' => $namahalaman,
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}

}
?>