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

class Biaya_perkara_m extends CI_Model{
	
	function get_biaya_perkara($idperkara){
		if(empty($idperkara)) 
			{ 
				return '';
			}
		try {

			$this->db->select('pb.*,phk.nama');
			$this->db->join('pihak phk', 'pb.pihak_id=phk.id','left');
			$this->db->where('pb.perkara_id',$idperkara);
			$this->db->order_by("pb.tahapan_id", "ASC"); 
			$this->db->order_by("pb.tanggal_transaksi", "ASC");
			$this->db->order_by("pb.id", "ASC");
			$this->db->order_by("pb.jenis_transaksi", "DESC"); 
			return $this->db->get('perkara_biaya pb');#->result_array();

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_panjar_biaya($idperkara,$tahapan){
		if(empty($idperkara)) 
			{ 
				return '';
			}
		try {

			$query = $this->db->query("SELECT SUM(jumlah) AS jumlah FROM perkara_biaya WHERE perkara_id=".$idperkara." AND tahapan_id=".$tahapan." AND jenis_transaksi=1");
			return $query;

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_tgl_pendaftaran($idperkara,$tahapan){
		if(empty($idperkara)) 
			{ 
				return '';
			}
		try {
			if($tahapan==10){
				$query = $this->db->query("SELECT tanggal_pendaftaran as tgl_daftar FROM perkara WHERE perkara_id=".$idperkara);				
			}elseif($tahapan==20){
				$query = $this->db->query("SELECT permohonan_banding as tgl_daftar FROM perkara_banding WHERE perkara_id=".$idperkara);
			}elseif($tahapan==30){
				$query = $this->db->query("SELECT permohonan_kasasi as tgl_daftar FROM perkara_kasasi WHERE perkara_id=".$idperkara);				
			}elseif($tahapan==40){
				$query = $this->db->query("SELECT permohonan_pk as tgl_daftar FROM perkara_pk WHERE perkara_id=".$idperkara);								
			}elseif($tahapan==50){
				$query = $this->db->query("SELECT permohonan_eksekusi as tgl_daftar FROM perkara_eksekusi WHERE perkara_id=".$idperkara);												
			}

			return $query;

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function get_jurnal_biaya_perkara($idperkara,$tahapanidperkara){
		if(empty($idperkara)) 
			{ 
				return '';
			}
		try {


			$query = $this->db->query("SELECT pb.*,phk.nama, d.`nama_gelar` AS jurusita FROM perkara_biaya AS pb
									   LEFT JOIN pihak AS phk ON pb.`pihak_id`=phk.id 
									   LEFT JOIN `perkara_biaya_jurusita` AS c ON pb.`id` = c.`perkara_biaya_id`
									   LEFT JOIN jurusita AS d ON c.`jurusita_id` = d.`id`
									   WHERE perkara_id='".$idperkara."' AND tahapan_id='".$tahapanidperkara."'
									   ORDER BY tanggal_transaksi ASC,id ASC,jenis_transaksi DESC");
			return $query;
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getStatusBiaya($idperkara,$idtahapan){
		try {			
			$result = $this->db->query("SELECT id FROM perkara_biaya WHERE perkara_id=".$idperkara." AND jenis_transaksi=1 AND tahapan_id=".$idtahapan);
			if($result->num_rows>0){
				return $result->row()->id;
			}else{
				return 0;
			}
		} catch (Exception $e) {
			return '';
		}
	}

	function data_perkara($idperkara,$nomor_perkara=''){
		if(empty($idperkara)) 
			{ 
				return '';
			}
		try {
			if($nomor_perkara!=''){
				$this->db->like('nomor_perkara',$nomor_perkara);	
			}
			$this->db->where('perkara_id',$idperkara);
			return $this->db->get('perkara');

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function pihak_pembayar($idperkara){
		if(empty($idperkara)) 
			{ 
				return '';
			}
		try {

			$query = $this->db->query("SELECT id,perkara_id,pihak_id,jenis_pihak_id,nama,1 AS pihak_ke FROM perkara_pihak1 WHERE perkara_id='".$idperkara."'
									   UNION 
									   SELECT id,perkara_id,pihak_id,jenis_pihak_id,nama,2 AS pihak_ke FROM perkara_pihak2 WHERE perkara_id='".$idperkara."'
									   UNION 
									   SELECT id,perkara_id,pihak_id,jenis_pihak_id,nama,3 AS pihak_ke FROM perkara_pihak3 WHERE perkara_id='".$idperkara."'
									   UNION 
									   SELECT id,perkara_id,pihak_id,jenis_pihak_id,nama,4 AS pihak_ke FROM perkara_pihak4 WHERE perkara_id='".$idperkara."'");
			return $query;

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}	

	#didik23042016	
	function getBiayaPnbp($alur_perkara){
		
		try {

			$query = $this->db->query('SELECT bp.alur_perkara_id,bp.jenis_biaya_id,jb.tahapan_id,bp.jumlah FROM biaya_perkara AS bp 
								LEFT JOIN jenis_biaya AS jb ON jb.id=bp.jenis_biaya_id 
								WHERE alur_perkara_id='.$alur_perkara.' AND jenis_biaya_id IN (61,62,63,64,65);');
			return $query;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}	
	function getBiayaAtk($alur_perkara){
		
		try {

			$query = $this->db->query('SELECT bp.alur_perkara_id,bp.jenis_biaya_id,jb.tahapan_id,bp.jumlah FROM biaya_perkara AS bp
								LEFT JOIN jenis_biaya AS jb ON jb.id=bp.jenis_biaya_id 
								WHERE alur_perkara_id='.$alur_perkara.' AND jenis_biaya_id IN (155,170,185,200,215);');
			return $query;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	#end


	function get_kategori_biaya(){
		try {
			$this->db->where("aktif","Y");
			$this->db->order_by("id", "ASC"); 
			return $this->db->get('kategori_biaya');

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function get_jenis_biaya($tahapan){
		try {
			$this->db->where("tahapan_id",$tahapan);
			$this->db->where("aktif","Y");
			$this->db->order_by("id", "ASC"); 
			return $this->db->get('jenis_biaya');

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_jenis_biaya_all($tahapan,$alur_perkara){
		
		try {

			$query = $this->db->query('SELECT jb.*,bp.jumlah AS jmlbiaya FROM jenis_biaya AS jb LEFT JOIN biaya_perkara AS bp ON jb.id=bp.jenis_biaya_id
										WHERE jb.aktif="Y" AND  jb.tahapan_id='.$tahapan.' AND bp.alur_perkara_id='.$alur_perkara);
			return $query;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_jml_biaya($tahapan,$alur_perkara,$id_jenis_biaya){
		
		try {

			$query = $this->db->query('SELECT jb.*,bp.jumlah AS jmlbiaya FROM jenis_biaya AS jb LEFT JOIN biaya_perkara AS bp ON jb.id=bp.jenis_biaya_id
										WHERE jb.aktif="Y" AND  jb.tahapan_id='.$tahapan.' AND bp.alur_perkara_id='.$alur_perkara.' AND jb.id='.$id_jenis_biaya);
			return $query;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	

	function get_kategori_biaya_all($alur_perkara,$tahapan_id){
		
		try {

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

	function get_jurusita($perkara_id){
		try {
			$param = array(
					'perkara_id' => $perkara_id,
					'aktif' => 'Y',
				);
			return $this->db->get_where('perkara_jurusita', $param);
		} catch (Exception $e) {
			log_message('error', $e);	
		}
	}

	function get_detil_biaya($idperkara){
		try {
			$this->db->where("perkara_id",$idperkara);
			$this->db->group_by("id_pembiayaan"); 
			return $this->db->get('perkara_biaya');

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}
	
	function get_data_edit_biaya($idbiayaperkara){
		try {
			$this->db->select('*');
			$this->db->from('perkara_biaya');
			$this->db->join('perkara_biaya_jurusita', 'perkara_biaya.id = perkara_biaya_jurusita.perkara_biaya_id', 'left');
			$this->db->where("id",$idbiayaperkara);
			return $this->db->get();

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}		

	function get_detil_jenis_biaya($id_jenis_biaya){
		try {
			$this->db->where("id",$id_jenis_biaya);
			return $this->db->get('jenis_biaya');

		} catch (Exception $e) {
			log_message('error', $e);
		}
	}


	function get_maxid_perkara_biaya(){
		
		try {

			$query = $this->db->query('SELECT MAX(id)+1 AS maxid  FROM perkara_biaya');
			return $query;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function check_if_exist($perkara_id,$jenis_biaya_id){
		try {
			$sql="SELECT COUNT(*) AS hasil FROM perkara_biaya WHERE perkara_id=".$perkara_id." AND jenis_biaya_id=".$jenis_biaya_id;
			$query = $this->db->query($sql);
			return $query->result()[0]->hasil;
			
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	//allperkara by atria
	function manipulate_dbas($data){
		try {
			foreach ($data as $key => $value) {
				if($value->id=='' OR $value->id==null){
					$maxid=$this->get_maxid_perkara_biaya();
					foreach ($maxid->result() as $row) {
						$maxid_perkara_biaya=$row->maxid;
					}
					$value->id = $maxid_perkara_biaya;
					$value =  (array) $value;
					$this->add_perkara_biaya($value);
				}else{
					$idbiayaperkara = $value->id;
					$this->edit_perkara_biaya($value, $idbiayaperkara);
				}
			}
		} catch (Exception $e) {
			
		}
	}

	function add_perkara_biaya($data){
		
		try {
				$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['id']."]";
				$descrip = $this->fetch_description($title,$data);
				$this->add_audittrail("INSERT",$title,$descrip,'perkara_biaya');

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

	function set_jurusita($perkara_biaya_id, $jurusita_id){
		try {
			$data = array(
		        'perkara_biaya_id' 	=> $perkara_biaya_id,
		        'jurusita_id'  		=> $jurusita_id
			);
			$this->db->delete('perkara_biaya_jurusita', array('perkara_biaya_id' => $perkara_biaya_id));
			$kategori_id = $this->db->get_where('perkara_biaya', array('id' => $perkara_biaya_id))->result()[0]->kategori_id;
			if(intval($jurusita_id)>0 and in_array($kategori_id, array(4,5,6))){
				$this->db->replace('perkara_biaya_jurusita', $data);
			}

		} catch (Exception $e) {
			
		}
	}

	function edit_perkara_biaya($data_edit,$idbiayaperkara){
		
		try {
				$table = "perkara_biaya";
				$this->db->where('id', $idbiayaperkara);
				$this->db->update($table, $data_edit);

				$title = "Edit Data Biaya <br />Edit table <b>Perkara_Biaya</b> dari halaman <b>Biaya Perkara</b> dengan Primary Key [ID Biaya=".$idbiayaperkara."]";
				$descrip = $this->fetch_description($title,$data_edit);
				$this->add_audittrail("EDIT",$title,$descrip,'perkara_biaya');
				
				return TRUE;
		}
		catch (Exception $e) {
			return FALSE;
		}
	}

	function edit_perkara_biaya_ispanjar_61($data_edit_ispanjar,$perkara_id){
		
		try {
				$table = "perkara_biaya";
				$this->db->where('jenis_biaya_id', 61);
				$this->db->where('perkara_id', $perkara_id);
				$this->db->update($table, $data_edit_ispanjar);

				$title = "Edit Data Biaya <br />Edit table <b>Perkara_Biaya</b> dari halaman <b>Biaya Perkara</b> dengan Primary Key [ID Perkara=".$perkara_id."] dan Jenis Biaya=61";
				$descrip = $this->fetch_description($title,$data_edit_ispanjar);
				$this->add_audittrail("EDIT",$title,$descrip,'perkara_biaya');
				
				return TRUE;
		}
		catch (Exception $e) {
			return FALSE;
		}
	}


	function edit_perkara_biaya_ispanjar_155($data_edit_ispanjar,$perkara_id){
		
		try {
				$table = "perkara_biaya";
				$this->db->where('jenis_biaya_id', 155);
				$this->db->where('perkara_id', $perkara_id);
				$this->db->update($table, $data_edit_ispanjar);

				$title = "Edit Data Biaya <br />Edit table <b>Perkara_Biaya</b> dari halaman <b>Biaya Perkara</b> dengan Primary Key [ID Perkara=".$perkara_id."] dan Jenis Biaya=155";
				$descrip = $this->fetch_description($title,$data_edit_ispanjar);
				$this->add_audittrail("EDIT",$title,$descrip,'perkara_biaya');
				
				return TRUE;
		}
		catch (Exception $e) {
			return FALSE;
		}
	}
	function edit_pendaftaran_perkara($data_edit_tanggal_perkara,$idperkara){
		
		try {
				$table = "perkara";
				$this->db->where('perkara_id', $idperkara);
				$this->db->update($table, $data_edit_tanggal_perkara);

				$title = "Edit Tanggal Pendaftaran Perkara <br />Edit table <b>Perkara</b> dari halaman <b>Edit Jurnal Biaya </b> dengan Primary Key [ID Perkara=".$idperkara."]";
				$descrip = $this->fetch_description($title,$data_edit_tanggal_perkara);
				$this->add_audittrail("EDIT",$title,$descrip,'perkara_biaya');
				
				return TRUE;
		}
		catch (Exception $e) {
			return FALSE;
		}
	}


	function hapus_perkara_biaya($idbiayaperkara){
		try {
			$this->db->where('id',$idbiayaperkara);
			$this->db->delete('perkara_biaya');

			$title = "Hapus Biaya <br />Hapus table <b>Perkara_Biaya</b> dari halaman <b>Biaya Perkara</b> dengan Primary Key [ID Biaya=".$idbiayaperkara."]";
			$descrip = '';
			$this->add_audittrail("HAPUS",$title,$descrip,'perkara_biaya');
			
			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) {
			
		}
	}

	#didik22052016 edit by amin 20160730
	function cek_prodeo($perkara_id){
		try{
			$sql="SELECT id_pembiayaan FROM perkara_biaya WHERE perkara_id=$perkara_id ORDER BY urutan LIMIT 1";
			return $this->db->query($sql);
		}catch (Exception $e) {
			
		}
	}
	#ubah prodeo atau tidak
	function edit_kategori_prodeo($perkara_id,$id_pembiayaan){
		
		 return $this->db->query("UPDATE perkara_biaya SET id_pembiayaan=$id_pembiayaan where perkara_id=$perkara_id");
	}

	function edit_alur_perdata($perkara_id,$alur_menjadi){
		
		 return $this->db->query("UPDATE perkara SET alur_perkara_id=$alur_menjadi where perkara_id=$perkara_id");
	}
}
?>