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

class New_eksekusi extends CI_Model{

	function getNomorUrutRegisterEks($idalurperkara,$date){
		if(empty($idalurperkara)) return '';
		if(empty($date)) return '';
		try {
			if($idalurperkara==1 OR $idalurperkara==7 or $idalurperkara==15){
				$idalurperkara = '1,7,15';
			}
			$query = $this->db->query("SELECT (ifnull(max(nomor_urut_perkara_eksekusi),0)) +1 AS nomor 
				FROM perkara_eksekusi WHERE alur_perkara_id IN (".$idalurperkara.") AND LEFT(permohonan_eksekusi,7) = '".$date."';");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->nomor;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getNomorUrutRegisterEksHt($idalurperkara,$date){
		if(empty($idalurperkara)) return '';
		if(empty($date)) return '';
		try {
			if($idalurperkara==1 OR $idalurperkara==7 or $idalurperkara==15){
				$idalurperkara = '1,7,15';
			}
			$query = $this->db->query("SELECT (ifnull(max(nomor_urut_perkara_eksekusi),0)) +1 AS nomor 
				FROM perkara_eksekusi_ht WHERE alur_perkara_id = 0 AND LEFT(permohonan_eksekusi,7) = '".$date."';");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->nomor;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function getNomorPerkaraEks($idalurperkara,$date){
		if(empty($idalurperkara)) return '';
		if(empty($date)) return '';
		try {
			if($idalurperkara==1 OR $idalurperkara==7 or $idalurperkara==15){
				$idalurperkara = '1,7,15';
			}
			$query = $this->db->query("SELECT IFNULL(MAX(SUBSTRING_INDEX(nomor_register_eksekusi,'/',1)),0)+1 AS nomor 
				FROM perkara_eksekusi WHERE alur_perkara_id IN (".$idalurperkara.") AND RIGHT(SUBSTRING_INDEX(nomor_register_eksekusi,'/',3),4)= '".$date."';");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->nomor;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function getNomorPerkaraEksHt($date){		
		if(empty($date)) return '';
		try {
			
			$query = $this->db->query("SELECT IFNULL(MAX(SUBSTRING_INDEX(eksekusi_nomor_perkara,'/',1)),0)+1 AS nomor 
				FROM perkara_eksekusi_ht WHERE alur_perkara_id = 0 AND RIGHT(SUBSTRING_INDEX(eksekusi_nomor_perkara,'/',3),4)= '".$date."';");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->nomor;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function cek_nomor_urut_eks($no_urut,$tgl_permohonan_eksekusi){
		if(empty($no_urut)) return '';
		if(empty($tgl_permohonan_eksekusi)) return '';
		try {

			$query = $this->db->query("SELECT COUNT(nomor_urut_perkara_eksekusi) AS a FROM perkara_eksekusi WHERE nomor_urut_perkara_eksekusi=".$no_urut." AND LEFT(permohonan_eksekusi,7)='".$tgl_permohonan_eksekusi."';");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->a;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function cek_nomor_urut_eksht($no_urut,$tgl_permohonan_eksekusi){
		if(empty($no_urut)) return '';
		if(empty($tgl_permohonan_eksekusi)) return '';
		try {

			$query = $this->db->query("SELECT COUNT(nomor_urut_perkara_eksekusi) AS a FROM perkara_eksekusi_ht WHERE nomor_urut_perkara_eksekusi=".$no_urut." AND LEFT(permohonan_eksekusi,7)='".$tgl_permohonan_eksekusi."';");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->a;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function cek_nomor_perkara_eks($nomor_perkara){
		if(empty($nomor_perkara)) return '';		
		try {

			$query = $this->db->query("SELECT COUNT(nomor_register_eksekusi) AS a FROM perkara_eksekusi WHERE nomor_register_eksekusi='".$nomor_perkara."'");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->a;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function cek_nomor_perkara_eksht($nomor_perkara){
		if(empty($nomor_perkara)) return '';		
		try {

			$query = $this->db->query("SELECT COUNT(eksekusi_nomor_perkara) AS a FROM perkara_eksekusi_ht WHERE eksekusi_nomor_perkara='".$nomor_perkara."'");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->a;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}



	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 getPerkara($nomor_perkara){
		try{
				$this->db->where('nomor_perkara',$nomor_perkara);
				$query = $this->db->get('perkara');
				return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}

	function getPerkaraEksekusi($perkara_id){
		try{
			$query = $this->db->query("SELECT count(*) as ada_eksekusi from perkara_eksekusi where perkara_id=$perkara_id;");
			return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}


	function getNomorPerkara($perkara_id){
		try{
				$this->db->where('perkara_id',$perkara_id);
				$query = $this->db->get('perkara');
				return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}

	function getPerkaraBHT($perkara_id){
		try{
			$this->db->select('tanggal_bht');
			$this->db->where('perkara_id',$perkara_id);
			$query = $this->db->get('perkara_putusan');
			return $query;
		} catch(Exception $e){
			log_message('error',$e);
		}
	}

	function data_putusan_pn($perkara_id){
		try{
				$this->db->where('perkara_id', $perkara_id);
				$query = $this->db->get('perkara_putusan');
				return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}	

	function data_banding($perkara_id){
		try{
				$this->db->where('perkara_id', $perkara_id);
				$query = $this->db->get('perkara_banding');
				return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}

	function data_kasasi($perkara_id){
		try{
				$this->db->where('perkara_id', $perkara_id);
				$query = $this->db->get('perkara_kasasi');
				return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}

	function data_pk($perkara_id){
		try{
				$this->db->where('perkara_id', $perkara_id);
				$query = $this->db->get('perkara_pk');
				return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}


	function data_para_pemohon($perkara_id){
		try{
			$query = $this->db->query("SELECT GROUP_CONCAT(pihak_id) as para_pemohon FROM perkara_eksekusi_detil WHERE status_pihak_id=1 AND perkara_id=$perkara_id;");
			return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}


	function data_penggugat($perkara_id){
		try{
				$this->db->where('perkara_id', $perkara_id);
				$query = $this->db->get('perkara_pihak1');
				return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}

	function data_tergugat($perkara_id){
		try{
				$this->db->where('perkara_id', $perkara_id);
				$query = $this->db->get('perkara_pihak2');
				return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}

	function get_biaya_pendaftaran(){
		
		try {

			$query = $this->db->query('SELECT 
						jb.id	 		AS id,
						jb.tahapan_id 		AS tahapan_id,
						jb.kategori_id  	AS kategori_id,
						jb.nama 		AS nama,
						bp.jumlah 		AS jumlah
					FROM jenis_biaya AS jb
					LEFT JOIN biaya_perkara AS bp ON jb.id=bp.jenis_biaya_id
					WHERE jb.tahapan_id=50 AND jb.id=65 GROUP BY jb.id;');
			return $query;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_biaya_atk(){
		
		try {

			$query = $this->db->query('SELECT 
						jb.id	 		AS id,
						jb.tahapan_id 		AS tahapan_id,
						jb.kategori_id  	AS kategori_id,
						jb.nama 		AS nama,
						bp.jumlah 		AS jumlah
					FROM jenis_biaya AS jb
					LEFT JOIN biaya_perkara AS bp ON jb.id=bp.jenis_biaya_id
					WHERE jb.tahapan_id=50 AND jb.id=215 GROUP BY jb.id;');
			return $query;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function get_jurusita(){
		
		try {

			$query = $this->db->query("SELECT * FROM jurusita WHERE aktif='Y';");
			return $query;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}	

	function add_perkara_eksekusi($data){
		
		try {
				$this->db->insert('perkara_eksekusi', $data);

				$data_update_proses = array(
					'tahapan_terakhir_id'=>50,
					'tahapan_terakhir_text'=>'Eksekusi',
					'proses_terakhir_id'=>600,
					'proses_terakhir_text'=>'Permohonan Eksekusi',
				);
				//update tabel perkara
				$this->db->where('perkara_id', $data['perkara_id']);
				$this->db->update('perkara', $data_update_proses);

				//insert tabel perkara_proses
				$data_perkara_proses = array(
					'perkara_id'=>$data['perkara_id'],
					'tahapan_id'=>'50',
					'tahapan_nama'=>'Eksekusi',
					'proses_id'=>'600',
					'proses_nama'=>'Permohonan Eksekusi',
					'tanggal'=>$data['permohonan_eksekusi'],
					'diinput_oleh'=>$this->session->userdata('username'),
					'diinput_tanggal'=>date('Y-m-d H:i:s')
				);
				$this->db->insert('perkara_proses', $data_perkara_proses);
				$title = "Tambah Data Perkara Eksekusi <br />Tambah table <b>perkara_eksekusi</b> dari halaman <b>Tambah Data Eksekusi</b> dengan Primary Key [ID=".$data['perkara_id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("INSERT",$title,$descrip,'perkara_eksekusi');
				return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}	

	function add_perkara_eksekusi_detil($datapihak){
		
		try {
			foreach ($datapihak as $data) {
				$this->db->insert('perkara_eksekusi_detil', $data);
			}
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}


	function add_perkara_biaya($data_biaya){
		
		try {
				foreach ($data_biaya as $data) {
					$id = $this->db->query("SELECT IFNULL(max(id)+1,1) as id FROM perkara_biaya;")->row()->id;
					if($id>0){
						$data['id'] = $id;
						$this->db->insert('perkara_biaya', $data);
					}
				}
				$title = "Tambah Data Biaya <br />Tambah table <b>Perkara_Biaya</b> dari halaman <b>Tambah Biaya</b> dengan Primary Key [ID=".$data['perkara_id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("INSERT",$title,$descrip,'perkara_biaya');

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

	function update_perkara_eksekusi($data,$idperkara){
		
		try {
				$table = "perkara_eksekusi";
				$this->db->where('perkara_id', $idperkara);
				$this->db->update($table, $data);

				$title = "Edit Data Baya <br />Edit table <b>perkara_eksekusi</b> dari halaman <b>Edit Permohonan Eksekusi</b> dengan Primary Key [ID Perkara=".$idperkara."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("EDIT",$title,$descrip,'perkara_eksekusi');

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

	function dele_perkara_eksekusi_detil($idperkara){
		
		try {
				$table = "perkara_eksekusi_detil";
				$this->db->where('perkara_id', $idperkara);
				$this->db->delete($table); 

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


	function get_detil_eksekusi($perkara_id){
		try{
			$query = $this->db->query("SELECT
				pe.perkara_id				AS perkara_id,
				pe.alur_perkara_id 			AS alur_perkara_id,
				pe.nomor_perkara_pn 		AS nomor_perkara_pn,
				pe.putusan_pn 				AS putusan_pn,
				pe.nomor_perkara_banding	AS nomor_perkara_banding,
				pe.putusan_banding 			AS putusan_banding,
				pe.nomor_perkara_kasasi 	AS nomor_perkara_kasasi,
				pe.putusan_kasasi 			AS putusan_kasasi,
				pe.nomor_perkara_pk 		AS nomor_perkara_pk,
				pe.putusan_pk 				AS putusan_pk,
				pe.permohonan_eksekusi 		AS permohonan_eksekusi,
				pe.eksekusi_putusan 		AS eksekusi_putusan_id,
				pe.nomor_register_eksekusi	AS nomor_register_eksekusi,
				pe.eksekusi_nomor_perkara 	AS eksekusi_nomor_perkara,
				pe.eksekusi_amar_putusan 	AS eksekusi_amar_putusan,
				pe.penetapan_teguran_eksekusi as penetapan_teguran_eksekusi,
				pe.nomor_penetapan_teguran_eksekusi as nomor_penetapan_teguran_eksekusi,
				pe.pelaksanaan_teguran_eksekusi as pelaksanaan_teguran_eksekusi,
				pe.penetapan_sita_eksekusi as penetapan_sita_eksekusi,
				pe.nomor_penetapan_sita_eksekusi as nomor_penetapan_sita_eksekusi,
				pe.pelaksanaan_sita_eksekusi as pelaksanaan_sita_eksekusi,
				pe.jurusita_id 			AS jurusita_id,
				pe.jurusita_nama 			AS jurusita_nama,
				pe.penetapan_perintah_eksekusi_lelang as penetapan_perintah_eksekusi_lelang,
				pe.pelaksanaan_eksekusi_lelang as pelaksanaan_eksekusi_lelang,
				pe.penyerahan_hasil_lelang as penyerahan_hasil_lelang,
				pe.penetapan_perintah_eksekusi_rill as penetapan_perintah_eksekusi_rill,
				pe.pelaksanaan_eksekusi_rill as pelaksanaan_eksekusi_rill,
				pe.penetapan_noneksekusi as penetapan_noneksekusi,
				pe.alasan_eksekusi as alasan_eksekusi,
				pe.catatan_eksekusi as catatan_eksekusi,
				pe.diterima_permohonan 							as diterima_permohonan,
				pe.panggilan_parapihak 							as panggilan_parapihak,
				pe.penetapan_ketua 								as penetapan_ketua,
				pe.sk_objek_tidak_punya_kekuatan_hukum 			as sk_objek_tidak_punya_kekuatan_hukum,
				pe.surat_tergugat_objek_non_executable 			as surat_tergugat_objek_non_executable,
				pe.panggilan_pihak_non_executable 				as panggilan_pihak_non_executable,
				pe.upaya_kesepakatan_kompensasi 				as upaya_kesepakatan_kompensasi,
				pe.penetapan_ketua_kompensasi 					as penetapan_ketua_kompensasi,
				pe.upaya_hukum_kma 								as upaya_hukum_kma,
				pe.penetapan_kma_kompensasi 					as penetapan_kma_kompensasi,
				pe.uangpaksa_putusan_hakim 						as uangpaksa_putusan_hakim,
				pe.uangpaksa_penetapan_ketua 					as uangpaksa_penetapan_ketua,
				pe.surat_ketua_tergugat_uangpaksa 				as surat_ketua_tergugat_uangpaksa,
				pe.surat_peringatan_uangpaksa 					as surat_peringatan_uangpaksa,
				pe.perintah_ketua_saksi_administratif 			as perintah_ketua_saksi_administratif,
				pe.sanksi_administratif_dari_pejabat 			as sanksi_administratif_dari_pejabat,
				pe.pengumuman_ketua_panitera_js 				as pengumuman_ketua_panitera_js,
				pe.pengumuman_media 							as pengumuman_media,
				pe.surat_presiden 								as surat_presiden,
				pe.surat_lembaga_perwakilan_rakyat 				as surat_lembaga_perwakilan_rakyat,
				pe.tanggal_cabut_eks 							as tanggal_cabut_eks,
				ped.pemohon_id	 			AS pemohon_id,
				ped.pemohon_nama 			AS pemohon_eksekusi,
				ped.pemohon_pekerjaan 		AS pekerjaan_pemohon,
				ped.pemohon_alamat 			AS alamat_pemohon,
				ped.pemohon_tanggal_surat  	AS tanggal_surat_kuasa,
				ped.pemohon_nomor_surat 	AS nomor_surat_kuasa,
				GROUP_CONCAT(ped.pihak_nama) 	AS pihak_dimohonkan
				
			FROM perkara_eksekusi AS pe
			LEFT JOIN perkara_eksekusi_detil AS ped ON pe.perkara_id=ped.perkara_id 
			WHERE pe.perkara_id=$perkara_id AND ped.pemohon_id <>0 GROUP BY ped.pemohon_id;");
			return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}

	function get_pihak_eksekusi($perkara_id){
		try{
			$query = $this->db->query("SELECT 
			ped.id 					AS id,
			ped.perkara_id 			AS perkara_id,
			ped.status_pihak_id 	AS status_pihak_id,
			ped.status_pihak_text 	AS status_pihak_text,
			ped.pihak_asal 			AS pihak_asal,
			ped.pihak_asal_text 	AS pihak_asal_text,
			ped.pihak_id 			AS pihak_id,
			ped.pihak_nama 			AS pihak_nama,
			ph.alamat 				AS alamat
		FROM perkara_eksekusi_detil AS ped
		LEFT JOIN pihak AS ph ON ph.id=ped.pihak_id
		WHERE ped.perkara_id=$perkara_id ORDER BY ped.status_pihak_id;");
			return $query;
			} catch (Exception $e) {
            	log_message('error', $e);
		}
	}



	function edit_perkara_eksekusi($data,$idperkara){
		
		try {
				$table = "perkara_eksekusi";
				$this->db->where('perkara_id', $idperkara);
				$this->db->update($table, $data);

				$title = "Edit Data Perkara Eksekusi <br />Edit table <b>perkara_eksekusi</b> dari halaman <b>Edit Permohonan Eksekusi</b> dengan Primary Key [ID Perkara=".$idperkara."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("EDIT",$title,$descrip,'perkara_eksekusi');

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

	function hapus_perkara_eks($idperkara){
		try {
			$this->db->where('perkara_id', $idperkara);
			$this->db->delete('perkara_eksekusi');

			$this->db->where('perkara_id', $idperkara);
			$this->db->where('tahapan_id', 50);
			$this->db->delete('perkara_biaya');
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function deletePerkaraProses($idperkara,$idproses){
		try {
			$this->db->simple_query("CALL perkara_proses_delete(".$idperkara.", '".$idproses."');");
			$this->db->simple_query("CALL perkara_update_status(".$idperkara.");");
			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 Mediasi',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}


}

?>