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

class Jurnal_hkk extends CI_Model{
	function isNomorPerkara($txt){
		$re1='(\\d+)';	# Integer Number 1
		$re2='(\\/)';	# Any Single Character 1
		$re3='.*?';	# Non-greedy match on filler
		$re4='(\\/)';	# Any Single Character 2
		$re5='((?:(?:[1]{1}\\d{1}\\d{1}\\d{1})|(?:[2]{1}\\d{3})))(?![\\d])';	# Year 1
		$re6='(\\/)';	# Any Single Character 3
		$re7='(PN)';	# Variable Name 1
		if ($c=preg_match_all ("/".$re1.$re2.$re3.$re4.$re5.$re6.$re7."/is", $txt, $matches)){
		      $int1=$matches[1][0];
		      $c1=$matches[2][0];
		      $c2=$matches[3][0];
		      $year1=$matches[4][0];
		      $c3=$matches[5][0];
		      $var1=$matches[6][0];
		      return 1;
		}else{
			return 0;
		}
	}

	function parseOrderby($col){
		if($col==1){
			return "tanggal_transaksi"; 
		}else if($col==2){
			return "jenis_biaya"; 
		}else if($col==3){
			return "uraian"; 
		}else if($col==4){
			return "jumlah"; 
		}
	}
	function getJurnalList($idalurperkara,$start_page=0,$key='',$col=1,$type='DESC'){
		$orderby = $this->parseOrderby($col);

		$where = "";
		if(!empty($idalurperkara)){
			$where = " AND p.alur_perkara_id IN (".$idalurperkara.") or pb.tahapan_id=99 ";
		}

		if(!empty($key)){
			$where .= ' AND (jb.nama LIKE "%'.$key.'%" OR pb.uraian LIKE "%'.$key.'%") ';
		}
		$sql = "SELECT 
					pb.id 						AS id,
					p.alur_perkara_id 			AS alur_perkara_id,
					pb.tanggal_transaksi		AS tanggal_transaksi,
					jb.nama 					AS jenis_biaya,
					pb.uraian 					AS uraian,
					pb.jumlah	 				AS jumlah,
					pb.keterangan 				AS keterangan
				FROM perkara_biaya AS pb 
				LEFT JOIN perkara AS p ON pb.perkara_id=p.perkara_id
				LEFT JOIN jenis_biaya AS jb ON pb.jenis_biaya_id = jb.id
				WHERE (pb.kategori_id=11 OR pb.kategori_id=13) ".$where."
				ORDER BY pb.tanggal_transaksi DESC, pb.diinput_tanggal DESC limit ".$start_page.",50;";
		try {
			return $this->db->query($sql);
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function getJurnalListCetak($idalurperkara='',$key=''){
		$where = "";
		if(!empty($idalurperkara)){
			$where = " AND p.alur_perkara_id IN (".$idalurperkara.") or pb.tahapan_id=99 ";
		}

		if(!empty($key)){
			$where .= ' AND (jb.nama LIKE "%'.$key.'%" OR pb.uraian LIKE "%'.$key.'%") ';
		}
		$sql = "SELECT 
					pb.id 						AS id,
					p.alur_perkara_id 			AS alur_perkara_id,
					pb.tanggal_transaksi		AS tanggal_transaksi,
					jb.nama 					AS jenis_biaya,
					pb.uraian 					AS uraian,
					pb.jumlah	 				AS jumlah,
					pb.keterangan 				AS keterangan
				FROM perkara_biaya AS pb 
				LEFT JOIN perkara AS p ON pb.perkara_id=p.perkara_id
				LEFT JOIN jenis_biaya AS jb ON pb.jenis_biaya_id = jb.id
				WHERE (pb.kategori_id=11 OR pb.kategori_id=13) ".$where."
				ORDER BY pb.tanggal_transaksi DESC, pb.diinput_tanggal DESC";
		try {
			return $this->db->query($sql);
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function fecth_data_num($idalurperkara,$key=''){

		$where = "";
	
			if(!empty($idalurperkara)){
				$where = " AND p.alur_perkara_id IN (".$idalurperkara.") ";
			}
			
			if(!empty($key)){
				$where .= ' AND (jb.nama LIKE "%'.$key.'%" OR pb.uraian LIKE "%'.$key.'%") ';
			}

		try {
			$query = $this->db->query("SELECT 
					count(pb.id) 						AS cnt
				FROM perkara_biaya AS pb 
				LEFT JOIN perkara AS p ON pb.perkara_id=p.perkara_id
				LEFT JOIN jenis_biaya AS jb ON pb.jenis_biaya_id = jb.id
				WHERE (pb.kategori_id=11 OR pb.kategori_id=13) ".$where.";");

			if($query->num_rows>0){
				foreach ($query->result() as $row) {
					return $row->cnt;
				}
	    		return $query->num_rows;
	    	}else{
	    		return 0;
	    	}
		} catch (Exception $e) {
			log_message('error', $e);
		}
	}

	function get_jenis_biaya(){
		try {
			$this->db->where("kategori_id",13);
			$this->db->where("tahapan_id",99);
			$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_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 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 edit_perkara_biaya($data_edit,$idbiayaperkara){
		
		try {
				$table = "perkara_biaya";
				$this->db->where('id', $idbiayaperkara);
				$this->db->update($table, $data_edit);

				$title = "Edit Data Baya <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');

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


	function get_data_edit_biaya($idbiayaperkara){
		try {
			$this->db->where("id",$idbiayaperkara);
			return $this->db->get('perkara_biaya');

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


}