<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Register_diversi extends CI_Model {
	function parseOrderby($col){
		if($col==2){
			return "tgl_permohonan_diversi"; 
		}else if($col==3){
			return "pihak_anak";
		}else{
			return "tgl_permohonan_diversi";
		}
	}

	function get_register_diversi_list($col=2,$asc_desc='DESC',$begin=0,$key=''){
		$orderby = $this->parseOrderby($col);
		$where = '';
		
		if(!empty($key)){
			//$where .= ' AND (alur_perkara.nama LIKE "%'.$key.'%" OR tahapan_proses.nama LIKE "%'.$key.'%" OR kategori_biaya.nama LIKE "%'.$key.'%" OR jenis_biaya.nama LIKE "%'.$key.'%" OR biaya_perkara.jumlah LIKE "%'.$key.'%") ';
			$orderby = $this->parseOrderby($col);
		}
		try {
			return $this->db->query("SELECT SQL_CALC_FOUND_ROWS 
										dr.id 						AS id,
										dr.no_urut 					AS no_urut,
										dr.no_diversi 				AS no_diversi,
									        DATE_FORMAT(dr.tgl_permohonan_diversi,'%Y-%m-%d')	AS tgl_permohonan_diversi,
										dr.no_reg_penyidik			AS no_reg_penyidik,
										dr.no_reg_penuntut_umum		AS no_reg_penuntut,
										dr.no_perkara 				AS no_perkara,
										dr.perkara_id,
										dr.pemohon_diversi 			AS pemohon_diversi,
										IF(dr.pemohon_diversi=3,(SELECT hakim_nama FROM perkara_hakim_pn WHERE tanggal_penetapan IN (SELECT MAX(tanggal_penetapan) FROM perkara_hakim_pn WHERE perkara_id=83399 GROUP BY perkara_id) AND perkara_id=dr.perkara_id AND (jabatan_hakim_nama='Hakim Tunggal' OR jabatan_hakim_nama='Hakim Ketua')),'') AS nama_hakim,
										pp.nama				AS nama_penyidik,
										pu.nama 			AS nama_penuntut,
										CONCAT('',IF(dr.pemohon_diversi=3,pan_hkm.pihak_anak_hkm,pan.pihak_nama_anak)) 			AS nama_anak,
										dr.pasal_sangkaan_dakwaan 	AS pasal_sangkaan_dakwaan,
										DATE_FORMAT(dr.tgl_kesepakatan_diversi,'%Y-%m-%d') 	AS tgl_kesepakatan,
										dr.isi_kesepakatan_diversi	AS isi_kesepakatan,	
										DATE_FORMAT(dr.tanggal_penetapan_kpn,'%Y-%m-%d') 	AS tanggal_penetapan_kpn,
										dr.amar 			AS amar,
										DATE_FORMAT(dr.tgl_pengiriman_penetapan_kpn,'%Y-%m-%d') AS tgl_pengiriman,
										DATE_FORMAT(dr.tgl_pelaksanaan_isi_diversi,'%Y-%m-%d') 	AS tgl_pelaksanaan
											
									FROM diversi_register AS dr
									LEFT JOIN (SELECT diversi_register_id,pihak_id,p.nama FROM diversi_penyidik AS dpa LEFT JOIN pihak AS p ON dpa.`pihak_id`=p.id GROUP BY diversi_register_id ) AS pp ON pp.`diversi_register_id`=dr.id
									LEFT JOIN (SELECT diversi_register_id,pihak_id,p.nama FROM diversi_penuntut AS dpa LEFT JOIN pihak AS p ON dpa.`pihak_id`=p.id GROUP BY diversi_register_id ) AS pu ON pu.`diversi_register_id`=dr.id

									LEFT JOIN (SELECT 
											dan.diversi_register_id,
											GROUP_CONCAT(CONCAT_WS(',',CONCAT((@cnto := @cnto + 1),'. ',p.nama)) SEPARATOR '<br>') AS pihak_nama_anak 
										FROM diversi_anak_orang_tua AS dan
										LEFT JOIN pihak AS p ON dan.pihak_anak=p.id
										CROSS JOIN (SELECT @cnto := 0) AS dum_nomer
										GROUP BY diversi_register_id
										) AS pan ON dr.id=pan.diversi_register_id

									LEFT JOIN pihak AS ph ON ph.id=dr.pihak_penasihat_hukum	
									LEFT JOIN pihak AS pk ON pk.id=dr.pihak_pk_bapas

									LEFT JOIN (SELECT 
											ph2.perkara_id,
											GROUP_CONCAT(CONCAT_WS(',',CONCAT(ph2.urutan,'. ',p.nama)) SEPARATOR '<br>') AS pihak_anak_hkm 
										FROM perkara_pihak2 AS ph2
										LEFT JOIN pihak AS p ON ph2.pihak_id=p.id	
										GROUP BY perkara_id
										) AS pan_hkm ON dr.perkara_id=pan_hkm.perkara_id
									
									 ".$where." ORDER BY ".$orderby." ".$asc_desc." LIMIT ".$begin.",50;");
											} catch (Exception $e) {
												
											}


/*			return $this->db->query("SELECT SQL_CALC_FOUND_ROWS 
									dr.id 						AS id,
									dr.no_urut 					AS no_urut,
									dr.no_diversi 				AS no_diversi,
								        DATE_FORMAT(dr.tgl_permohonan_diversi,'%Y-%m-%d')	AS tgl_permohonan_diversi,
									dr.no_reg_penyidik			AS no_reg_penyidik,
									dr.no_reg_penuntut_umum		AS no_reg_penuntut,
									dr.no_perkara 				AS no_perkara,
									dr.perkara_id,
									dr.pemohon_diversi 			AS pemohon_diversi,
									IF(dr.pemohon_diversi=3,(SELECT hakim_nama FROM perkara_hakim_pn WHERE tanggal_penetapan IN (SELECT MAX(tanggal_penetapan) FROM perkara_hakim_pn WHERE perkara_id=83399 GROUP BY perkara_id) AND perkara_id=dr.perkara_id AND (jabatan_hakim_nama='Hakim Tunggal' OR jabatan_hakim_nama='Hakim Ketua')),'') AS nama_hakim,
									pp.nama				AS nama_penyidik,
									pu.nama 			AS nama_penuntut,
									pa.pihak_anak			AS pihak_anak,
									CONCAT('<strong>a. Anak : </strong><br>',IF(dr.pemohon_diversi=3,pan_hkm.pihak_anak_hkm,pan.pihak_nama_anak)) 			AS nama_anak,
									CONCAT('<strong>b. Orang Tua/Wali : </strong><br>',IF(dr.pemohon_diversi=3,ppoan_hkm.pihak_orang_tua_hkm,po.pihak_orang_tua))	AS pihak_orang_tua,
									CONCAT('<strong>c. ',IF(dr.pemohon_diversi=3,ppcr_hkm.pihak_pengacara_hkm,ph.nama),'</strong>') 			AS penasihat_hukum,
									CONCAT('<strong>d. ',IF(dr.pemohon_diversi=3,ppbm_hkm.pihak_pbm_hkm,pk.nama),'</strong>') 			AS pk_bapas,
									pko.pihak_korban 		AS pihak_korban,
									pkot.pihak_korban_orang_tua 	AS pihak_korban_orang_tua,
									dr.pasal_sangkaan_dakwaan 	AS pasal_sangkaan_dakwaan,
									DATE_FORMAT(dr.tgl_kesepakatan_diversi,'%Y-%m-%d') 	AS tgl_kesepakatan,
									dr.isi_kesepakatan_diversi	AS isi_kesepakatan,	
									DATE_FORMAT(dr.tanggal_penetapan_kpn,'%Y-%m-%d') 	AS tanggal_penetapan_kpn,
									dr.amar 			AS amar,
									DATE_FORMAT(dr.tgl_pengiriman_penetapan_kpn,'%Y-%m-%d') AS tgl_pengiriman,
									DATE_FORMAT(dr.tgl_pelaksanaan_isi_diversi,'%Y-%m-%d') 	AS tgl_pelaksanaan
										
								FROM diversi_register AS dr
								LEFT JOIN pihak AS pp ON dr.pihak_penyidik=pp.id
								LEFT JOIN pihak AS pu ON dr.pihak_penuntut=pu.id
								LEFT JOIN (SELECT 
										dan.diversi_register_id,
										GROUP_CONCAT(CONCAT_WS(',',CONCAT((@cnto := @cnto + 1),'. ',p.nama)) SEPARATOR '<br>') AS pihak_nama_anak 
									FROM diversi_anak_orang_tua AS dan
									LEFT JOIN pihak AS p ON dan.pihak_anak=p.id
									CROSS JOIN (SELECT @cnto := 0) AS dum_nomer
									GROUP BY diversi_register_id
									) AS pan ON dr.id=pan.diversi_register_id

								LEFT JOIN (SELECT 
										dao.diversi_register_id,
										GROUP_CONCAT(CONCAT_WS(',',CONCAT((@cnt := @cnt + 1),'. ',p.nama),p.tempat_lahir,DATE_FORMAT(p.tanggal_lahir,'%d-%m-%Y'),IF(p.jenis_kelamin='L','Laki-laki','Perempuan'),
										p.warga_negara,p.alamat,p.agama_nama,p.pekerjaan,p.pendidikan) SEPARATOR '<br>') AS pihak_anak 
									FROM diversi_anak_orang_tua AS dao
									LEFT JOIN pihak AS p ON dao.pihak_anak=p.id
									CROSS JOIN (SELECT @cnt := 0) AS dum_nomer
									GROUP BY diversi_register_id
									) AS pa ON dr.id=pa.diversi_register_id
								LEFT JOIN (SELECT 
										dao.diversi_register_id,
										GROUP_CONCAT(CONCAT_WS(',',CONCAT((@cntoaa := @cntoaa + 1),'. ',p.nama)) SEPARATOR '<br>') AS pihak_orang_tua 
									FROM diversi_anak_orang_tua AS dao
									LEFT JOIN pihak AS p ON dao.pihak_orang_tua=p.id
									CROSS JOIN (SELECT @cntoaa := 0) AS dum_nomer
									GROUP BY diversi_register_id
									) AS po ON dr.id=po.diversi_register_id
								LEFT JOIN pihak AS ph ON ph.id=dr.pihak_penasihat_hukum	
								LEFT JOIN pihak AS pk ON pk.id=dr.pihak_pk_bapas
								LEFT JOIN (SELECT 
										dk.diversi_register_id,
										GROUP_CONCAT(CONCAT_WS(',',CONCAT((@cntk := @cntk + 1),'. ',p.nama),p.tempat_lahir,DATE_FORMAT(p.tanggal_lahir,'%d-%m-%Y'),IF(p.jenis_kelamin='L','Laki-laki','Perempuan'),
										p.warga_negara,p.alamat,p.agama_nama,p.pekerjaan,p.pendidikan) SEPARATOR '<br>') AS pihak_korban
									FROM diversi_korban_orang_tua AS dk
									LEFT JOIN pihak AS p ON dk.pihak_anak=p.id
									CROSS JOIN (SELECT @cntk := 0) AS dum_nomer
									GROUP BY diversi_register_id
									) AS pko ON dr.id=pko.diversi_register_id
								LEFT JOIN (SELECT 
										dko.diversi_register_id,
										GROUP_CONCAT(CONCAT_WS(',',CONCAT((@cntko := @cntko + 1),'. ',p.nama)) SEPARATOR '<br>') AS pihak_korban_orang_tua
									FROM diversi_korban_orang_tua AS dko
									LEFT JOIN pihak AS p ON dko.pihak_orang_tua=p.id
									CROSS JOIN (SELECT @cntko := 0) AS dum_nomer
									GROUP BY diversi_register_id
									) AS pkot ON dr.id=pkot.diversi_register_id

								LEFT JOIN (SELECT 
										ph2.perkara_id,
										GROUP_CONCAT(CONCAT_WS(',',CONCAT(ph2.urutan,'. ',p.nama)) SEPARATOR '<br>') AS pihak_anak_hkm 
									FROM perkara_pihak2 AS ph2
									LEFT JOIN pihak AS p ON ph2.pihak_id=p.id	
									GROUP BY perkara_id
									) AS pan_hkm ON dr.perkara_id=pan_hkm.perkara_id
									
								LEFT JOIN (SELECT 
										ph2.perkara_id,
										GROUP_CONCAT(CONCAT_WS(',',CONCAT(ph2.urutan,'. ',ppoa.nama)) SEPARATOR '<br>') AS pihak_orang_tua_hkm 
									FROM perkara_pihak2 AS ph2
									LEFT JOIN perkara_pihak_orang_tua AS ppo ON ph2.pihak_id=ppo.id_anak AND ph2.`perkara_id`=ppo.`perkara_id`
									LEFT JOIN pihak AS ppoa ON ppo.`id_orang_tua`=ppoa.`id`
									GROUP BY perkara_id
									) AS ppoan_hkm ON dr.perkara_id=ppoan_hkm.perkara_id

								LEFT JOIN (SELECT 
										ph2.perkara_id,
										GROUP_CONCAT(CONCAT_WS(',',CONCAT(ppcr.urutan,'. ',ppoa.nama)) SEPARATOR '<br>') AS pihak_pengacara_hkm 
									FROM perkara_pihak2 AS ph2
									LEFT JOIN perkara_pengacara AS ppcr ON ph2.pihak_id=ppcr.pihak_id AND ph2.`perkara_id`=ppcr.`perkara_id`
									LEFT JOIN pihak AS ppoa ON ppcr.`pengacara_id`=ppoa.`id`
									GROUP BY perkara_id
									) AS ppcr_hkm ON dr.perkara_id=ppcr_hkm.perkara_id

								LEFT JOIN (SELECT 
										ph2.perkara_id,
										GROUP_CONCAT(CONCAT_WS(',',CONCAT(ppcr.urutan,'. ',ppoa.nama)) SEPARATOR '<br>') AS pihak_pbm_hkm 
									FROM perkara_pihak2 AS ph2
									LEFT JOIN perkara_pihak_pembimbing_masyarakat AS ppcr ON ph2.`perkara_id`=ppcr.`perkara_id`
									LEFT JOIN pihak AS ppoa ON ppcr.`pihak_id`=ppoa.`id`
									GROUP BY perkara_id
									) AS ppbm_hkm ON dr.perkara_id=ppbm_hkm.perkara_id
									 ".$where." ORDER BY ".$orderby." ".$asc_desc." LIMIT ".$begin.",50;");
											} catch (Exception $e) {
												
											}*/

	}


	function get_last_nomor_diversi(){
		try {
			return $this->db->query("SELECT MAX(CAST(SUBSTRING_INDEX(no_diversi,'/',1) AS UNSIGNED)) as no_diversi FROM diversi_register WHERE LEFT(tgl_permohonan_diversi,4)=YEAR(CURDATE())")->row()->no_diversi;
		} catch (Exception $e) {
			return;
		}
	}

	function get_info_sppa($no_perkara){
		try {
			return $this->db->query("SELECT perkara_id,jenis_perkara_nama,pihak2_text AS nama_anak,tanggal_pendaftaran,tahapan_terakhir_text FROM perkara WHERE UPPER(nomor_perkara)=UPPER('".$no_perkara."');");
		} catch (Exception $e) {
			return;
		}
	}

	function cek_no_diversi($no_diversi,$tahun_nomor){
		try {
			return $this->db->query("SELECT count(no_diversi) as ada from diversi_register where left(tgl_permohonan_diversi,4)='".$tahun_nomor."' and no_diversi = '".$no_diversi."';")->row()->ada;
		} catch (Exception $e) {
			return;
		}
	}

	function cek_no_urut_diversi($no_urut,$tahun_nomor){
		try {
			return $this->db->query("SELECT count(no_urut) as ada from diversi_register where left(tgl_permohonan_diversi,4)='".$tahun_nomor."' and no_urut = '".$no_urut."';")->row()->ada;
		} catch (Exception $e) {
			return;
		}
	}

	function getKodePN(){
		try {
			$this->db->where('id',61);
			$this->db->select('value');
			$result = $this->db->get('sys_config');
			if($result->num_rows>0){
				return $result->row()->value;
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function last_id_diversi(){
		try {
			return $this->db->query("SELECT IF(COUNT(*)=0,COUNT(id)+1,MAX(id)+1) as id_diversi from diversi_register")->row()->id_diversi;
		} catch (Exception $e) {
			return;
		}
	}

	function get_detil_diversi($diversi_id){
		try {
			return $this->db->query("SELECT * from diversi_register where id=".$diversi_id);
		} catch (Exception $e) {
			return;
		}
	}

	function pihak_anak_orang_tua($diversi_id){
		try {
			return $this->db->query("SELECT 
										dpa.id,
										dpa.urutan,
										dpa.pihak_anak,
										p.nama 		AS nama_anak,
										p.alamat 	AS alamat_anak,
										p.tanggal_lahir AS tanggal_lahir,
										p.tempat_lahir  AS tempat_lahir,
										CONCAT(TIMESTAMPDIFF(YEAR,p.tanggal_lahir,dr.tgl_permohonan_diversi),' ' ,'Tahun') AS umur,
										p.jenis_kelamin,
										p.agama_nama,
										p.pekerjaan,
										p.warga_negara,
										po.nama 	AS orang_tua,
										po.alamat 	AS alamat_orang_tua
									FROM diversi_anak_orang_tua AS dpa
									LEFT JOIN pihak AS p ON dpa.pihak_anak=p.id
									LEFT JOIN pihak AS po ON dpa.pihak_orang_tua=po.id
									LEFT JOIN diversi_register AS dr ON dpa.diversi_register_id=dr.id
									WHERE diversi_register_id=".$diversi_id);
		} catch (Exception $e) {
			return;
		}
	}

	//dipakai template
	function anak_berhadapan_dgn_hukum($perkara_id){
		try {
			return $this->db->query("SELECT 
										dpa.id,dpa.urutan,
										p.nama 		AS nama_anak,
										p.alamat 	AS alamat_anak,
										p.tanggal_lahir AS tanggal_lahir,
										p.tempat_lahir  AS tempat_lahir,
										CONCAT(TIMESTAMPDIFF(YEAR,p.tanggal_lahir,pk.tanggal_pendaftaran),' ' ,'Tahun') AS umur,
										p.jenis_kelamin,
										p.agama_nama,
										p.pekerjaan,
										p.warga_negara										
									FROM perkara_pihak2 AS dpa
									LEFT JOIN pihak AS p ON dpa.pihak_id=p.id
									LEFT JOIN perkara AS pk ON dpa.perkara_id=pk.perkara_id								
									WHERE dpa.perkara_id=".$perkara_id);
		} catch (Exception $e) {
			return;
		}
	}
	
	//end	

	function pihak_anak_orang_tua_hakim($perkara_id){
		try {
			return $this->db->query("SELECT
										p2.perkara_id 		AS perkara_id,
										p2.urutan		AS urutan,
										p2.pihak_id 		AS pihak_id,
										p2.nama			AS nama_anak,
										p2.alamat		AS alamat_anak,
										p.nama 			AS orang_tua
										
									FROM perkara_pihak2 AS p2
									LEFT JOIN perkara_pihak_orang_tua AS poa ON p2.pihak_id=poa.id_anak
									LEFT JOIN pihak AS p ON p.id=poa.id_orang_tua
									WHERE p2.perkara_id=".$perkara_id." ORDER BY urutan");
		} catch (Exception $e) {
			return;
		}
	}	

	function pihak_korban_orang_tua($diversi_id){
		try {
			return $this->db->query("SELECT 
										dpa.id,
										dpa.urutan,
										dpa.pihak_anak,
										p.nama 		AS nama_anak,
										p.alamat 	AS alamat_anak,
										p.tanggal_lahir AS tanggal_lahir,
										po.nama 	AS orang_tua,
										po.alamat 	AS alamat_orang_tua
									FROM diversi_korban_orang_tua AS dpa
									LEFT JOIN pihak AS p ON dpa.pihak_anak=p.id
									LEFT JOIN pihak AS po ON dpa.pihak_orang_tua=po.id
									WHERE diversi_register_id=".$diversi_id);
		} catch (Exception $e) {
			return;
		}
	}	

	function pihak_korban_orang_tua_hakim($perkara_id){
		try {
			return $this->db->query("SELECT
										p2.perkara_id 		AS perkara_id,
										p2.urutan		AS urutan,
										p2.pihak_id 		AS pihak_id,
										p2.nama			AS nama_anak,
										p2.alamat		AS alamat_anak,
										p.nama 			AS orang_tua
										
									FROM perkara_pihak_korban AS p2
									LEFT JOIN perkara_pihak_orang_tua AS poa ON p2.pihak_id=poa.id_anak
									LEFT JOIN pihak AS p ON p.id=poa.id_orang_tua
									WHERE p2.perkara_id=".$perkara_id." ORDER BY urutan");
		} catch (Exception $e) {
			return;
		}
	}		

	function pihak_penuntut($diversi_id){
		try {
			return $this->db->query("SELECT 
										dp.id,
										dp.urutan,
										dp.pihak_id,
										p.nama 		AS nama_penuntut,
										p.alamat 	AS alamat_penuntut,
										p.tanggal_lahir AS tanggal_lahir
									FROM diversi_penuntut AS dp
									LEFT JOIN pihak AS p ON dp.pihak_id=p.id
									WHERE diversi_register_id=".$diversi_id);
		} catch (Exception $e) {
			return;
		}
	}	

	function pihak_penuntut_hakim($perkara_id){
		try {
			return $this->db->query("SELECT
										perkara_id 		AS perkara_id,
										urutan 			AS urutan,
										pihak_id 		AS pihak_id,
										nama 			AS nama_penuntut,
										alamat 			AS alamat_penuntut
									FROM perkara_pihak1 WHERE perkara_id=".$perkara_id." ORDER BY urutan");
		} catch (Exception $e) {
			return;
		}
	}	

	function berita_acara_hakim($perkara_id){
		try {
			return $this->db->query("SELECT 
										pd.perkara_id,
										pd.diversi_register_id,
										p.nomor_perkara,
										pj.tanggal_musyawarah
									FROM perkara_diversi AS pd
									LEFT JOIN perkara AS p ON pd.`perkara_id`= p.`perkara_id`
									LEFT JOIN (SELECT 
											diversi_id,
											MAX(tanggal_musyawarah) AS tanggal_musyawarah 
										FROM perkara_jadwal_musyawarah_diversi 
										GROUP BY diversi_id) AS pj ON pd.`diversi_register_id`=pj.diversi_id
									WHERE pd.perkara_id=".$perkara_id);
		} catch (Exception $e) {
			return;
		}
	}	

	function pihak_penyidik($diversi_id){
		try {
			return $this->db->query("SELECT 
										dp.id,
										dp.urutan,
										dp.pihak_id,
										p.nama 		AS nama_penyidik,
										p.alamat 	AS alamat_penyidik,
										p.tanggal_lahir AS tanggal_lahir
									FROM diversi_penyidik AS dp
									LEFT JOIN pihak AS p ON dp.pihak_id=p.id
									WHERE diversi_register_id=".$diversi_id);
		} catch (Exception $e) {
			return;
		}
	}	

	function pihak_pkbapas($diversi_id){
		try {
			return $this->db->query("SELECT 
										dp.id,
										dp.urutan,
										dp.pihak_id,
										p.nama 		AS nama_pkbapas,
										p.alamat 	AS alamat_pkbapas,
										p.tanggal_lahir AS tanggal_lahir
									FROM diversi_pembimbing_kemasyarakatan AS dp
									LEFT JOIN pihak AS p ON dp.pihak_id=p.id
									WHERE diversi_register_id=".$diversi_id);
		} catch (Exception $e) {
			return;
		}
	}

	function pihak_pkbapas_hakim($perkara_id){
		try {
			return $this->db->query("SELECT
										p2.perkara_id 		AS perkara_id,
										p2.urutan		AS urutan,
										p2.pihak_id 		AS pihak_id,
										p2.nama			AS nama_pkbapas,
										p2.alamat		AS alamat_pkbapas

									FROM perkara_pihak_pembimbing_masyarakat AS p2
									LEFT JOIN pihak AS p ON p.id=p2.pihak_id
									WHERE p2.perkara_id=".$perkara_id." ORDER BY urutan");
		} catch (Exception $e) {
			return;
		}
	}
	function pihak_penasihat_hukum($diversi_id){
		try {
			return $this->db->query("SELECT 
										dph.id,
										dph.urutan,
										dph.pengacara_id,
										dph.anak_id,
										pp.nama 	AS nama_pengacara,
										p.nama 		AS nama_anak,
										pp.alamat 	AS alamat_pengacara
									FROM diversi_penasihat_hukum AS dph
									LEFT JOIN pihak AS pp ON pp.id=dph.`pengacara_id`
									LEFT JOIN pihak AS p ON dph.anak_id=p.id
									WHERE diversi_register_id=".$diversi_id);
		} catch (Exception $e) {
			return;
		}
	}	

	function pihak_penasihat_hukum_hakim($perkara_id){
		try {
			return $this->db->query("SELECT
										p2.perkara_id 	AS perkara_id,
										p2.urutan		AS urutan,
										p2.pihak_id 	AS pihak_id,
										p2.nama			AS nama_pengacara,
										p2.alamat		AS alamat_pengacara,
										p.nama 			AS nama_anak
										
									FROM perkara_pengacara AS p2
									LEFT JOIN pihak AS p ON p.id=p2.pihak_id
									WHERE p2.perkara_id=".$perkara_id." ORDER BY urutan");
		} catch (Exception $e) {
			return;
		}
	}

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

	function add_diversi($data){
		try {
			$this->db->insert('diversi_register', $data);
			$title = "Tambah Biaya Perkara";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("INSERT",$title,$descrip);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_diversi($data,$id){
		try {
			$this->db->where('id', $id);
			$this->db->update('diversi_register', $data);

			$title = "Update Register Diversi";

			//$descrip = $this->fetch_description($title,$data);
			//$this->add_audittrail("UPDATE",$title,$descrip);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data($data,$aktif,$enc_biaya,$enc_alur){
		try {
			$this->db->where('jenis_biaya_id', $enc_biaya);
			$this->db->where('alur_perkara_id',$enc_alur);
			$this->db->update('biaya_perkara', $data);

			$this->db->where('id', $enc_biaya);
			$this->db->update('jenis_biaya', $aktif);

			$title = "Update Data Biaya Perkara";

			//$descrip = $this->fetch_description($title,$data);
			//$this->add_audittrail("UPDATE",$title,$descrip);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

}
?>