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

class Mediasi_m extends CI_Model{
	
	function get_data_mediasi_all($perkara_id){
		if(empty($perkara_id)){ 
				return '';
		}
		try{
			return $this->db->query("SELECT mediasi.* FROM perkara_mediasi AS mediasi
											WHERE mediasi.perkara_id = $perkara_id
											ORDER BY mediasi.mediasi_id ASC");
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_data_mediasi($perkara_id){
		if(empty($perkara_id)){ 
				return '';
		}
		try{
			return $this->db->query("SELECT mediasi.* FROM perkara_mediasi AS mediasi
											WHERE mediasi.perkara_id = $perkara_id AND mediasi.tahapan_id=10
											ORDER BY mediasi.mediasi_id ASC");
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

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


	function get_mediator1($nama,$begin,$status_mediator){
		try {
			if($status_mediator=='H'){
				if($nama!=''){
					$where .= 'WHERE nama LIKE "%'.$nama.'%"';
				}else{
					$where .= '';
				}
			}else{
				if($nama!=''){
					$where .= 'WHERE nama LIKE "%'.$nama.'%" AND status_mediator ="N"';	
				}else{
					$where .= 'WHERE status_mediator ="N"';	
				}
			}
			
			return $this->db->query("SELECT id,nama_gelar AS nama,no_sertifikasi,status_mediator FROM mediator $where ORDER BY id ASC LIMIT ".$begin.",20;");
		}catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function search_data($nama){
		try {
			$where .= 'WHERE nama LIKE "%'.$nama.'%"';
			return $this->db->query("SELECT id,nama,no_sertifikasi,status_mediator 
				  					 FROM mediator $where ORDER BY id ASC;");
		} catch (Exception $e) {
			log_message('error',$e);
		}
	}

	function total_mediator(){
		try {
			return $this->db->query("SELECT COUNT(*) AS total FROM mediator");
		} catch (Exception $e) {
			log_message('error',$e);
		}
	}

	function get_date_tanggal($perkara_id){
		if(empty($perkara_id)){ 
				return '';
		}

		try {
			return $this->db->query("SELECT DISTINCT diinput_tanggal FROM perkara_mediator WHERE perkara_id=$perkara_id")->result();
		} catch (Exception $e) {
			log_message('error', $e);	
		}
	}

	function get_data_dukung_mediasi($mediasi_id){
		if(empty($mediasi_id)){
			return '';
		}
		if(!intval($mediasi_id)){
			show_404();
		}

		try {
			$this->db->where('mediasi_id',$mediasi_id);
			$this->db->order_by('mediasi_id','ASC');
			return $this->db->get('perkara_data_dukung_mediasi');
		} catch (Exception $e) {
			log_message('error',$e);
		}
	}

	function get_perkara_mediator(){
		try {
			$query = $this->db->query('SELECT MAX(id) AS maxid FROM perkara_mediator');
			return $query->row()->maxid;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_nomor_perkara($idperkara){
	try {
			return $this->db->query("select perkara_id,alur_perkara_id,nomor_perkara,tanggal_pendaftaran from perkara where perkara_id = ".$idperkara);
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_maxid_mediasi(){
		try {
			$query = $this->db->query('SELECT IFNULL(MAX(mediasi_id)+1,1) AS maxid FROM perkara_mediasi');
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_v_mediasi($id_mediasi){
		try {			
			return $this->db->query("SELECT 
									A.mediasi_id,
									A.penetapan_penunjukan_mediator,
									A.nomor_sk_penetapan_mediator,
									A.dimulai_mediasi,
									A.keputusan_mediasi,
									A.tgl_kesepakatan_perdamaian,
									A.isi_kesepakatan_perdamaian,
									A.akta_perdamaian,
									A.isi_akta_perdamaian,
									A.isi_akta_perdamaian_dok,
									A.catatan_mediasi,
									A.tgl_laporan_mediator,
									A.hasil_mediasi,
									B.hakim_id,
									B.status_mediator,
									C.uraian,
									C.identifier
									FROM perkara_mediasi AS A
										LEFT JOIN mediator AS B 
											ON A.mediator_id = B.id
										LEFT JOIN perkara_data_dukung_mediasi AS C 
											ON A.mediasi_id = C.mediasi_id
									WHERE A.mediasi_id=".$id_mediasi);
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_data_penetapan($idperkara){
		if(empty($idperkara)){ 
				return '';
			}
		try {
			$this->db->select('penetapan_hari_sidang,sidang_pertama,nomor_perkara');
			$this->db->from('perkara_penetapan');
			$this->db->join('perkara', 'perkara_penetapan.perkara_id = perkara.perkara_id');
			$this->db->where('perkara_penetapan.perkara_id',$idperkara);
			return $this->db->get();
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


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

	function get_mediator_edit($perkara_id){
		if(intval($perkara_id)<0){
			show_404();
		}

		try {
			return $this->db->query("SELECT 
									mediator_id,
									status_mediator,
									nama_mediator
									FROM perkara_mediator WHERE perkara_id=$perkara_id");
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_detil_mediator($idmediator){
		if(empty($idmediator)){ 
			return '';
		}
		try {
			$this->db->where('id',$idmediator);
			return $this->db->get('mediator');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getSidangPertama($idperkara){
		try {
			$result = $this->db->query('SELECT sidang_pertama FROM perkara_penetapan WHERE perkara_id ='.$idperkara);
			if($result->num_rows>0){
				return $result->row()->sidang_pertama;;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function get_nama_dok($mediasi_id){
		if(empty($mediasi_id)){
				return '';
		}
		try{
			$this->db->where('mediasi_id',$mediasi_id);
			$query=$this->db->get('perkara_mediasi');
			if ($query->num_rows()>0){
				return $query->row()->isi_akta_perdamaian_dok;
			}
		}catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_mediasi_sebelum($id_perkara){
		if(empty($id_perkara)){
				return '';
		}
		try{
			$qry="SELECT keputusan_mediasi,hasil_mediasi FROM perkara_mediasi WHERE perkara_id='$id_perkara'
					AND mediasi_id=(SELECT MAX(mediasi_id) FROM perkara_mediasi WHERE perkara_id='$id_perkara');";
			$hasil=$this->db->query($qry);
			return $hasil;
		}catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_jadwal_mediasi_kedua($id_mediasi){
		if(empty($id_mediasi)){
				return '';
		}
		try{
			$qry="SELECT tanggal_mediasi FROM perkara_jadwal_mediasi WHERE mediasi_id='$id_mediasi' AND urutan='2'";
			$hasil=$this->db->query($qry);
			if ($hasil->num_rows()>0){
			return $hasil->row()->tanggal_mediasi;
			}
		}catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function cek_data_dukung_mediasi($idperkara,$mediasi_id){
		if(!intval($idperkara)||$idperkara==null){
			show_404();
			exit();
		}
		
		try{
			return $this->db->query("select 
									id AS id,
									perkara_id AS perkara_id,
									mediasi_id AS mediasi_id,
									identifier AS identifier 
									from perkara_data_dukung_mediasi
									where perkara_id=$idperkara AND identifier='#pilih_mediator#'");
		}catch(Exception $e){
			log_message('error',$e);
		}
	}
	
	function update_data_dukung_mediasi($data_dukung_mediasi,$mediasi_id,$idperkara){
		if(empty($data_dukung_mediasi)){
			return '';
		}
		if(!intval($mediasi_id)){
			return '';
		}

		try {
			$this->db->where('mediasi_id',$mediasi_id);
			$this->db->where('perkara_id',$idperkara);
			$this->db->where('identifier',$data_dukung_mediasi['identifier']);
			$this->db->update('perkara_data_dukung_mediasi',$data_dukung_mediasi);
			return TRUE;
		} catch (Exception $e) {
			log_message('error',$e);
		}
	}

	function update_mediator_text($data_edit,$perkara_id){
		try {
			$table = "perkara_mediasi";
			$this->db->where('perkara_id', $perkara_id);
			$this->db->update($table, $data_edit);
		} catch (Exception $e) {
			return false;
		}
	}

	function add_data_dukung_mediasi($data_dukung_mediasi){
		if(empty($data_dukung_mediasi)){
			return '';
		}
		try {
			$this->db->insert('perkara_data_dukung_mediasi', $data_dukung_mediasi);
			$title = "Tambah Data Dukung Mediasi <br />Tambah table <b>perkara_data_dukung_mediasi</b> dari halaman <b>Mediasi</b> dengan Primary Key [mediasi_id=".$data_dukung_mediasi['mediasi_id']."]";
			$descrip = $this->fetch_description($title,$data_add);
			$this->add_audittrail("INSERT",$title,$descrip,'perkara_mediasi');
			return TRUE;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function add_mediasi($data_add){
		if(empty($data_add)){
				return '';
		}
		try {
			$this->db->insert('perkara_mediasi', $data_add);
			$title = "Tambah Data Mediasi <br />Tambah table <b>Perkara_Mediasi</b> dari halaman <b>Mediasi</b> dengan Primary Key [mediasi_id=".$data_add['mediasi_id']."]";
			$descrip = $this->fetch_description($title,$data_add);
			$this->add_audittrail("INSERT",$title,$descrip,'perkara_mediasi');
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function isAlreadyMediasi($idperkara){
		if(empty($idperkara)){
				return '';
		}
		try {
			$result = $this->db->query('SELECT COUNT(*) as jml FROM perkara_mediasi WHERE perkara_id ='.$idperkara);
			if($result->num_rows>0){
				if($result->row()->jml>0){
					return TRUE;
				}else{
					return FALSE;
				}
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}
	
	function hapus_mediasi($id_mediasi,$perkara_id){
		if(empty($id_mediasi)){
				return '';
		}
		try {
			$this->db->where('mediasi_id',$id_mediasi);
			$this->db->delete('perkara_mediasi');

			$this->db->where('mediasi_id',$id_mediasi);
			$this->db->delete('perkara_jadwal_mediasi');

			$this->db->where('perkara_id',$perkara_id);
			$this->db->delete('perkara_mediator');
			
			$title = "DELETE Data Mediasi <br /> DELETE table <b>perkara_mediasi dan perkara_jadwal_mediasi</b> dari halaman <b>EDIT DATA MEDIASI</b> dengan Primary Key [id=".$id_mediasi."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,'perkara_penuntutan');

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

	function hapus_hasil_mediasi($id_mediasi,$perkara_id,$hasil_mediasi){
		if(empty($id_mediasi)){
				return '';
		}
		try {
			$hapus_hasil = array(
				'keputusan_mediasi'				=> NULL,
				'hasil_mediasi' 				=> NULL,
				'tgl_kesepakatan_perdamaian'	=> NULL,
				'isi_kesepakatan_perdamaian'	=> NULL,
				'akta_perdamaian'				=> NULL,
				'isi_akta_perdamaian'			=> NULL,
				'isi_akta_perdamaian_dok'		=> '',
				'catatan_mediasi'				=> '',
				'tgl_laporan_mediator' 			=> NULL
				);

			$this->db->where('mediasi_id',$id_mediasi);
			$this->db->where('perkara_id',$perkara_id);
			$this->db->update('perkara_mediasi',$hapus_hasil);

			if($hasil_mediasi=='Y'||$hasil_mediasi=='Y1'||$hasil_mediasi=='Y2'){
				$this->db->query("DELETE FROM perkara_proses WHERE proses_id IN (130,180)");
			}elseif($hasil_mediasi=='S'){
				$this->db->query("DELETE FROM perkara_proses WHERE proses_id IN (130,181)");
			}elseif($hasil_mediasi=='T'){
				$this->db->query("DELETE FROM perkara_proses WHERE proses_id IN (130,150)");
			}elseif($hasil_mediasi=='D'){
				$this->db->query("DELETE FROM perkara_proses WHERE proses_id IN (130,151)");
			}
			
			$title = "DELETE Hasil Mediasi <br /> DELETE table <b>perkara_mediasi dan perkara_mediasi</b> dari halaman <b>EDIT HASIL MEDIASI</b> dengan Primary Key [id=".$id_mediasi."]";
			$descrip = '';
			$this->add_audittrail("DELETE",$title,$descrip,'perkara_mediasi');

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

	function edit_mediasi($data_edit,$mediasi_id){
		if(empty($data_edit)&&empty($mediasi_id)){
				return '';
		}
		try {
			$table = "perkara_mediasi";
			$this->db->where('mediasi_id', $mediasi_id);
			$this->db->update($table, $data_edit);

			if (!empty($data_edit['dimulai_mediasi'])){
				$data_edit2['tanggal_mediasi']=$data_edit['dimulai_mediasi'];
				$this->db->where('mediasi_id',$mediasi_id);
				$this->db->where('urutan','1');
				$this->db->update('perkara_jadwal_mediasi',$data_edit2);
			}

			$title = "Edit Data Mediasi <br />Edit table <b>Perkara_Mediasi</b> dari halaman <b>Mediasi</b> dengan Primary Key [Mediasi_id=".$mediasi_id."]";
			$descrip = $this->fetch_description($title,$data_edit);
			$this->add_audittrail("EDIT",$title,$descrip,'perkara_mediasi');
			return TRUE;
		}catch (Exception $e){
			return FALSE;
		}
	}

	function add_perkara_mediator($data_add1){
		if(empty($data_add1)){
				return '';
		}
		try {
			$this->db->insert('perkara_mediator', $data_add1);
			return TRUE;
		} catch (Exception $e){
			return FALSE;
		}
	}

	function delete_update($perkara_id){
		try {
			$this->db->where('perkara_id',$perkara_id);
			$this->db->delete('perkara_mediator');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function edit_p_mediator($data_edit1,$idmediator){
		if(empty($data_edit1)&&empty($idmediator)){
				return '';
		}
		try {
			$this->db->where('mediator_id', $idmediator);
			$this->db->update('perkara_mediator', $data_edit1);
			return TRUE;
		}catch (Exception $e){
			return FALSE;
		}
	}

	function get_jadwal_mediasi_terakhir($idmediasi){
		if(empty($idmediasi)){
				return '';
		}
		try {
			$qry="SELECT tanggal_mediasi FROM perkara_jadwal_mediasi 
					WHERE mediasi_id='$idmediasi' AND urutan=(SELECT MAX(urutan) FROM perkara_jadwal_mediasi WHERE mediasi_id='$idmediasi');";			
			$tgljmediasiterakhir=$this->db->query($qry);
			$jumhasil=$tgljmediasiterakhir->num_rows();
			if ($jumhasil>0){
				$tgljmediasiterakhir=$tgljmediasiterakhir->row()->tanggal_mediasi;
				return $tgljmediasiterakhir;
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function get_majelis_untuk_mediator($idperkara){
		if(empty($idperkara)){
				return '';
		}
		try {
			$qry="	SELECT hakim_id AS id,hakim_nama AS nama,jabatan_hakim_nama AS status_mediator 
					FROM perkara_hakim_pn 
					WHERE perkara_id=$idperkara AND aktif='Y' ORDER BY urutan ASC";
			$meditornya=$this->db->query($qry);
			return $meditornya;
		}catch (Exception $e) {
			return FALSE;
		}
	}
	
	function get_maxid_mediator(){
		try {
			$qry=$this->db->select_max('id');
			$maxid=$this->db->get('mediator')->row()->id;
			return $maxid;
		}catch (Exception $e) {
			return FALSE;
		}
	}
	
	function cek_hakim_di_mediator($idhakim){
		if(empty($idhakim)){
				return '';
		}
		try {
			$this->db->where('hakim_id',$idhakim);
			$ada=count($this->db->get('mediator')->result());
			
			if($ada==0){
				$this->db->where('id',$idhakim);
				$rowhakim=$this->db->get('hakim_pn')->row();
				
				$idmediator=$this->get_maxid_mediator() + 1;
				$diinput_oleh=$this->session->userdata('username');
				$diinput_tanggal=date("Y-m-d h:i:s",time());
				
				$data_add = array(
					'id' => $idmediator,
					'status_mediator' => 'H',
					'hakim_id' => $idhakim,
					'nama' => $rowhakim->nama,
					'nama_gelar' => $rowhakim->nama_gelar,
					'profesi' => 'Hakim',						
					'bersertifikat' => 'T',
					'aktif' => 'Y',
					'diinput_oleh' => $this->session->userdata('username'),
					'diinput_tanggal' => date("Y-m-d h:i:s",time())
				);

				$this->db->insert('mediator',$data_add);
			}
			$this->db->where('hakim_id',$idhakim);
			$idmediator=$this->db->get('mediator')->row()->id;

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

	function get_urutan_perkaraproses_mediasi($idperkara,$idproses){
		if(empty($idperkara)&&empty($idproses)){
				return '';
		}
		try {
			$qry="SELECT MAX(urutan) AS urutan FROM perkara_proses WHERE perkara_id=$idperkara AND proses_id=$idproses;";
			$urutan=$this->db->query($qry)->row()->urutan;
			return $urutan;
		}catch (Exception $e) {
			return FALSE;
		}
	}

	function updateproses($idperkara,$idproses,$tanggal,$urutan){
		try {
			$alur_perkara_id = $this->tanggalhelper->getIDAlurPerkara($idperkara);
			
			$cekproses = $this->db->query("SELECT tahapan_id,tahapan_nama,proses_id,proses_nama FROM perkara_proses WHERE perkara_id = ".$idperkara." ORDER BY proses_id DESC LIMIT 1 ;");			
			if($cekproses->num_rows>0){
				foreach ($cekproses->result() as $row) {					
					$update_status['tahapan_terakhir_id']	=$row->tahapan_id;
					$update_status['tahapan_terakhir_text']	=$row->tahapan_nama;
					$update_status['proses_terakhir_id']	=$row->proses_id;
					$update_status['proses_terakhir_text']	=$row->proses_nama;
				}				
				$this->db->where('perkara_id',$idperkara);
				$this->db->update('perkara',$update_status);
			}
			$hasil=$this->db->query("SELECT * FROM proses_alur_perkara WHERE alur_perkara_id=".$alur_perkara_id.' AND proses_id='.$idproses);

			$data_update['tahapan_terakhir_id']=$hasil->row()->tahapan_id;
			$data_update['tahapan_terakhir_text']=$hasil->row()->tahapan_nama;
			$data_update['proses_terakhir_id']=$hasil->row()->proses_id;
			$data_update['proses_terakhir_text']=$hasil->row()->proses_nama;

			$data_insert['perkara_id']=$idperkara;
			$data_insert['tahapan_id']=$data_update['tahapan_terakhir_id'];
			$data_insert['tahapan_nama']=$data_update['tahapan_terakhir_text'];
			$data_insert['proses_id']=$data_update['proses_terakhir_id'];
			$data_insert['proses_nama']=$data_update['proses_terakhir_text'];
			$data_insert['tanggal']=$tanggal;
			$data_insert['urutan']=$urutan;
			$data_insert['diinput_oleh']=$this->session->userdata('username');
			$data_insert['diinput_tanggal']=date('Y-m-d H:i:s');

			$this->db->insert('perkara_proses',$data_insert);			

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

	function cancelproses($idperkara,$idproses,$urutan){
		try
		{
			$this->db->where('proses_id',$idproses);
			$this->db->where('perkara_id',$idperkara);
			$this->db->where('urutan',$urutan);
			$this->db->delete('perkara_proses');

			$qry="SELECT tahapan_id,tahapan_nama,proses_id,proses_nama FROM perkara_proses WHERE perkara_id='$idperkara' AND proses_id=(SELECT MAX(proses_id) FROM perkara_proses WHERE perkara_id='$idperkara' AND urutan='$urutan');";
			$hasil=$this->db->query($qry);
			$jumhasil=count($hasil->result());
			if ($jumhasil==0){
				$qry="SELECT tahapan_id,tahapan_nama,proses_id,proses_nama FROM perkara_proses WHERE perkara_id='$idperkara' AND proses_id=(SELECT MAX(proses_id) FROM perkara_proses WHERE perkara_id='$idperkara');";
				$hasil=$this->db->query($qry);
			}

			$data_update['tahapan_terakhir_id']=$hasil->row()->tahapan_id;
			$data_update['tahapan_terakhir_text']=$hasil->row()->tahapan_nama;
			$data_update['proses_terakhir_id']=$hasil->row()->proses_id;
			$data_update['proses_terakhir_text']=$hasil->row()->proses_nama;

			$this->db->where('perkara_id',$idperkara);
			$this->db->update('perkara',$data_update);
		} 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 Mediasi',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		}catch (Exception $e) {
		}
	}
}
?>
