<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class biaya_perkara extends CI_Model {
	function parseOrderby($col){
		if($col==2){
			return "alur_perkara_id"; 
		}else if($col==3){
			return "kategori_nama";
		}else if($col==4){
			return "jenis_transaksi_text"; 
		}else if($col==5){
			return "jenis_biaya_nama"; 
		}else if($col==6){
			return "jumlah"; 
		}else if($col==7){
			return "keterangan";
		}else if($col==8){
			return "Aktif"; 
		}else{
			return "alur_perkara_id";
		}
	}

	function get_biaya_perkara_list($col=2,$asc_desc='ASC',$begin=0,$key=''){
		$orderby = $this->parseOrderby($col);
		$where = '';
		
		if(!empty($key)){
			$where .= ' AND (alur_perkara.nama LIKE "%'.$key.'%" OR tahapan_proses.nama LIKE "%'.$key.'%" OR kategori_biaya.nama LIKE "%'.$key.'%" OR jenis_biaya.nama LIKE "%'.$key.'%" OR biaya_perkara.jumlah LIKE "%'.$key.'%") ';
			$orderby = $this->parseOrderby($col);
		}
		try {
			return $this->db->query("SELECT SQL_CALC_FOUND_ROWS
									  biaya_perkara.alur_perkara_id AS alur_perkara_id,
									  biaya_perkara.jenis_biaya_id  AS jenis_biaya_id,
									  alur_perkara.nama             AS alur_perkara_nama,
									  tahapan_proses.nama	    AS nama_tahapan,
									  IF (jenis_biaya.jenis_transaksi=1,'Pemasukan','Pengeluaran') AS jenis_transaksi_text,
									  kategori_biaya.nama           AS kategori_nama,
									  jenis_biaya.jenis_transaksi   AS jenis_transaksi,
									  jenis_biaya.nama              AS jenis_biaya_nama,
									  biaya_perkara.jumlah          AS jumlah,
									  jenis_biaya.aktif             AS aktif,	
									  biaya_perkara.keterangan      AS keterangan,
									  jenis_biaya.tahapan_id        AS tahapan_id,
									  jenis_biaya.kategori_id       AS kategori_id,
									  kategori_biaya.urutan         AS kategori_urutan,
									  jenis_biaya.kode              AS jenis_biaya_kode,
									  tahapan_proses.id		    AS id_tahapan
									  
									  
									FROM ((((biaya_perkara
									     LEFT JOIN jenis_biaya
									       ON (((biaya_perkara.jenis_biaya_id = jenis_biaya.id))))
									    LEFT JOIN kategori_biaya
									      ON (((jenis_biaya.kategori_id = kategori_biaya.id))))
									   LEFT JOIN alur_perkara
									     ON (((biaya_perkara.alur_perkara_id = alur_perkara.id))))
									   LEFT JOIN tahapan_proses
									     ON (((tahapan_proses.id=jenis_biaya.tahapan_id))))
					WHERE tahapan_proses.jenis_pengadilan = 4 and biaya_perkara.alur_perkara_id IN (SELECT id FROM alur_perkara WHERE aktif = 'Y' AND jenis_pengadilan = ".$this->session->userdata('jenis_pengadilan').") ".$where." ORDER BY ".$orderby." ".$asc_desc." LIMIT ".$begin.",50;");
		} catch (Exception $e) {
			
		}
	}

	function get_biaya_perkara_info($enc_alur,$enc_biaya){
		if(!empty($enc_alur) AND !empty($enc_biaya)){
				try {
					return $this->db->query("SELECT SQL_CALC_FOUND_ROWS
									  biaya_perkara.alur_perkara_id AS alur_perkara_id,
									  biaya_perkara.jenis_biaya_id  AS jenis_biaya_id,
									  alur_perkara.nama             AS alur_perkara_nama,
									  tahapan_proses.nama	    AS nama_tahapan,
									  IF (jenis_biaya.jenis_transaksi=1,'Pemasukan','Pengeluaran') AS jenis_transaksi_text,
									  kategori_biaya.nama           AS kategori_nama,
									  jenis_biaya.jenis_transaksi   AS jenis_transaksi,
									  jenis_biaya.nama              AS jenis_biaya_nama,
									  biaya_perkara.jumlah          AS jumlah,
									  jenis_biaya.aktif             AS aktif,	
									  biaya_perkara.keterangan      AS keterangan,
									  jenis_biaya.tahapan_id        AS tahapan_id,
									  jenis_biaya.kategori_id       AS kategori_id,
									  kategori_biaya.urutan         AS kategori_urutan,
									  jenis_biaya.kode              AS jenis_biaya_kode,
									  tahapan_proses.id		    AS id_tahapan
									  
									  
									FROM ((((biaya_perkara
									     LEFT JOIN jenis_biaya
									       ON (((biaya_perkara.jenis_biaya_id = jenis_biaya.id))))
									    LEFT JOIN kategori_biaya
									      ON (((jenis_biaya.kategori_id = kategori_biaya.id))))
									   LEFT JOIN alur_perkara
									     ON (((biaya_perkara.alur_perkara_id = alur_perkara.id))))
									   LEFT JOIN tahapan_proses
									     ON (((tahapan_proses.id=jenis_biaya.tahapan_id))))
											WHERE biaya_perkara.alur_perkara_id = $enc_alur AND biaya_perkara.jenis_biaya_id = $enc_biaya");
											
				} catch (Exception $e) {
					return 0;
				}
			}
	}

	function get_klasifikasi_perkara(){
		try {
			return $this->db->query("SELECT id,nama FROM alur_perkara ORDER BY nama asc");
		} catch (Exception $e) {
			return;
		}
	}

	function get_tahapan_proses(){
		try {
			return $this->db->query("SELECT id,nama FROM tahapan_proses ORDER BY nama asc");
		} catch (Exception $e) {
			return;
		}
	}

	function get_kategori_biaya(){
		try {
			return $this->db->query("SELECT id,nama FROM kategori_biaya ORDER BY nama asc");
		} catch (Exception $e) {
			return;
		}
	}

	function get_jenis_biaya(){
		try {
			return $this->db->query("SELECT id,nama FROM jenis_biaya ORDER BY nama asc");
		} catch (Exception $e) {
			return;
		}
	}
	function fecth_data_num(){
		$query = $this->db->query("SELECT FOUND_ROWS() as cnt;");
		if($query->num_rows>0){
			foreach ($query->result() as $row) {
				return $row->cnt;
			}
    		return $query->num_rows;
    	}else{
    		return 0;
    	}
	}

	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){
		try {
			$data = array(
				'datetime' => date("Y-m-d H:i:s"),
				'ipaddress' => $this->input->ip_address(),
				'username' => $this->session->userdata('username'),
				'tablename' => 'jurusita',
				'formname' => 'Data Panitera',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}

	function add_data($data){
		try {
			$this->db->insert('biaya_perkara', $data);
			$title = "Tambah Biaya Perkara";
			$descrip = $this->fetch_description($title,$data);
//			$this->add_audittrail("INSERT",$title,$descrip);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

	function update_data($data,$aktif,$enc_biaya,$enc_alur){
		try {
			$this->db->where('jenis_biaya_id', $enc_biaya);
			$this->db->where('alur_perkara_id',$enc_alur);
			$this->db->update('biaya_perkara', $data);

			$this->db->where('id', $enc_biaya);
			$this->db->update('jenis_biaya', $aktif);

			$title = "Update Data Biaya Perkara";

			//$descrip = $this->fetch_description($title,$data);
			//$this->add_audittrail("UPDATE",$title,$descrip);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}

}
?>