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

class New_perdata extends CI_Model{
	
	function getJenisPerkara($idalurperkara){
		if(empty($idalurperkara)) return '';
		try {
			$this->db->where('id',$idalurperkara);
			$result = $this->db->get('alur_perkara');
			if($result->num_rows>0){
				return $result->row()->nama;
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getNomorUrutPerkara($idalurperkara,$year){
		if(empty($idalurperkara)) return '';
		if(empty($year)) return '';
		try {
			if($idalurperkara==1 OR $idalurperkara==7){
				$idalurperkara = '1,7';
			}
			$query = $this->db->query("SELECT ifnull(max(nomor_urut_perkara),0) + 1 AS nomor FROM perkara WHERE alur_perkara_id IN (".$idalurperkara.") AND year(tanggal_pendaftaran)='".$year."';");
			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->nomor;
				}
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getNomorUrutRegister($idalurperkara,$date){
		if(empty($idalurperkara)) return '';
		if(empty($date)) return '';
		try {
			if($idalurperkara==1 OR $idalurperkara==7){
				$idalurperkara = '1,7';
			}
			$query = $this->db->query("SELECT (ifnull(max(nomor_urut_register),0)) +1 AS nomor 
				FROM perkara WHERE alur_perkara_id IN (".$idalurperkara.") AND LEFT(tanggal_pendaftaran,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 getKlasifikasiPerkara($idalurperkara){
		if(empty($idalurperkara)) return '';
		if($idalurperkara<0) return '';
		try {
			$query = $this->db->query("SELECT alur_perkara_id, jenis_perkara_id AS id,nama_lengkap AS nama
				FROM jenis_alur_perkara AS j, jenis_perkara AS p
				WHERE j.jenis_perkara_id = p.id AND j.alur_perkara_id = ".$idalurperkara."
				ORDER BY nama_lengkap ASC");
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getBiayaPerkara($idalurperkara,$idtahapan){
		if(empty($idalurperkara)) return '';
		if($idtahapan<10) return '';
		try {
			$query = $this->db->query("SELECT id, kode,nama,b.jumlah 
				FROM biaya_perkara AS b, jenis_biaya AS j
				WHERE b.jenis_biaya_id = j.id AND b.alur_perkara_id = ".$idalurperkara." AND tahapan_id = ".$idtahapan." AND id IN (61,155);");
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	
	function getFormatPerkara($idjenisperkara){
		if(empty($idjenisperkara)) return '';
		try {
			$this->db->where('id',$idjenisperkara);
			$result = $this->db->get('jenis_perkara');
			if($result->num_rows>0){
				return $result->row()->format_nomor;
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getKodePerkara($idalurperkara){
		if(empty($idalurperkara)) return '';
		try {
			$this->db->where('id',$idalurperkara);
			$this->db->select('kode');
			$result = $this->db->get('alur_perkara');
			if($result->num_rows>0){
				return $result->row()->kode;
			}else{
				return '';
			}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function cekNomorUrut($date, $urutan){
		try {
			$this->db->select('perkara_id');
			$this->db->where('nomor_urut_register',$urutan);
			$this->db->where('LEFT(tanggal_pendaftaran,7)',substr($date, 0, -3));
			return $this->db->get('perkara');
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function cekNomorPerkara($no_manual){
		if(empty($no_manual)) return '';
		try {
			$this->db->select('perkara_id');
			$this->db->where('nomor_perkara',$no_manual);
			return $this->db->get('perkara');
		} 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 isNomorPerkaraExist($nomorPerkara){
		try {
			$this->db->where('nomor_perkara',$nomorPerkara);
			$this->db->select('nomor_perkara');
			$query = $this->db->get('perkara');
			if($query->num_rows>0){
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			log_message('error', $e);
			return -1;
		}
	}

	function isNomorUrutRegisterExists($tglPendaftaran,$idalurperkara,$noUrutReg){
		$tmp = explode("/", $tglPendaftaran);
		if(count($tmp)==3){
			$date = $tmp[2]."-".$tmp[1];
		}else{
			return -2;
		}
		try {
			if($idalurperkara==1 OR $idalurperkara==7){
				$idalurperkara = '1,7';
			}
			$query = $this->db->query("SELECT nomor_urut_register 
				FROM perkara 
				WHERE LEFT(tanggal_pendaftaran,7)='".$date."' 
				AND alur_perkara_id IN (".$idalurperkara.") AND nomor_urut_register='".$noUrutReg."';");
			if($query->num_rows>0){
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			log_message('error', $e);
			return -2;
		}
	}

	function getJenisPerkaraNama($id){
		if(empty($id)) return '';
		try {
			$this->db->where('id',$id);
			$this->db->select('kode,nama,nama_lengkap');
			return $this->db->get('jenis_perkara');
		} 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 Perkara Perdata',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}

	function add_data($data,$data_keuangan_perkara,$epayment=FALSE){
		try {
			$perkara_id = $this->db->query("SELECT IFNULL(max(perkara_id)+1,1) as perkara_id FROM perkara;")->row()->perkara_id;
			if($perkara_id>0){
				$data['perkara_id'] = $perkara_id;
				$noPerkara = $data['nomor_perkara'];
				$this->db->insert('perkara', $data);

				$title = "Tambah Data Perkara [Nomor Perkara=<b>".$data['nomor_perkara']."</b>]<br />Tambah table <b>Perkara</b> dari halaman <b>Jurnal Keuangan Perkara</b> dengan Primary Key [perkara_id=".$data['perkara_id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("INSERT",$title,$descrip,'perkara');
				$tanggal = $data['tanggal_pendaftaran'];
				foreach ($data_keuangan_perkara 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;
						$data['perkara_id'] = $perkara_id;
						$this->db->insert('perkara_biaya', $data);
					}
				}
				$title = "Tambah Data Keuangan Perkara [Nomor Perkara=<b>".$noPerkara."</b>]<br />Tambah table <b>Perkara_Biaya</b> dari halaman <b>Jurnal Keuangan Perkara</b> dengan Primary Key [perkara_id=".$data['perkara_id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("INSERT",$title,$descrip,'perkara_biaya');
				$this->db->simple_query("CALL perkara_proses_update(".$data['perkara_id'].", 10, 'Pendaftaran Perkara', 10, 'Pendaftaran Perkara', '".$tanggal."', '', '".$this->session->userdata('username')."', '".date('Y-m-d H:i:s')."');");
				$this->db->simple_query("CALL perkara_update_status(".$data['perkara_id'].");");				
				if($epayment!=FALSE){
					if($epayment['is_ecourt']==0){
						$this->db->simple_query("UPDATE `epayment_virtual_account` SET `perkara_id` = ".$perkara_id.", is_jurnal=1 WHERE `epayment_id` = ".$epayment['epayment_id']."; ");
					}
				}
				
				return TRUE;
			}else{
				return FALSE;
			}
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function getMinPermohonan($idperkara){
		try {
			$result = $this->db->query('SELECT min(permohonan_banding) AS tglpermohonan FROM perkara_banding_detil WHERE (status_pihak_id = 1 OR status_pihak_id = 2) AND  perkara_id = '.$idperkara.';');
			if($result->num_rows>0){
				return $result->row()->tglpermohonan;
			}else{
				return '';
			}
		} catch (Exception $e) {
			
		}
	}

	function isValidPerlawanan($nomorperkara){
		try {
			return $this->db->query("SELECT perkara_id, jenis_perkara_id, nomor_perkara, tanggal_putusan, putusan_verstek, alur_perkara_id,alur_perkara_nama,jenis_perkara_nama FROM v_perkara WHERE tanggal_putusan IS NOT NULL AND nomor_perkara = '".$nomorperkara."';");
		} catch (Exception $e) {
			log_message('error', $e);
			return '';
		}
	}
}