<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Reminder_perkara extends CI_Model {
	function parseOrderby($col){
		if($col==1){
			return "a.nama"; 
		}else if($col==2){
			return "tahapan_nama"; 
		}else if($col==3){
			return "proses_nama"; 
		}else if($col==4){
			return "batas_waktu"; 
		}else if($col==5){
			return "satuan"; 
		}else if($col==6){
			return "b.nama"; 
		}else if($col==7){
			return "p.aktif"; 
		}else if($col==8){
			return "reminder"; 
		}else{
			return "a.nama";
		}
	}

	function get_reminder_list($col=1,$asc_desc='ASC',$begin=0,$key='',$tahapan='',$proses='',$jenis_perkara=''){
		$orderby = $this->parseOrderby($col);
		$where = ' ';
		if(!empty($tahapan)){
			$where .= ' WHERE tahapan_id ='.$tahapan.' ';
		}

		if(!empty($proses)){
			if($where != ' '){
				$where .= ' AND ';
			}else{
				$where = ' WHERE ';
			}
			$where .= ' proses_id ='.$proses.' ';
		}

		if(!empty($jenis_perkara)){
			if($where != ' '){
				$where .= ' AND ';
			}else{
				$where = ' WHERE ';
			}
			$where .= ' alur_perkara_id ='.$jenis_perkara.' ';
		}

		if(!empty($key)){
			if($where != ' '){
				$where .= ' AND ';
			}else{
				$where = ' WHERE ';
			}
			$where .= ' (tahapan_nama LIKE "%'.$key.'%" OR proses_nama LIKE "%'.$key.'%" 
				OR a.nama LIKE "%'.$key.'%"
				OR b.nama LIKE "%'.$key.'%"
				OR batas_waktu LIKE "%'.$key.'%") ';
		}
		try {
			return $this->db->query("SELECT SQL_CALC_FOUND_ROWS p.id, 
				tahapan_nama, proses_nama, batas_waktu, IF(p.aktif='T','Tidak','Aktif') AS aktif_text, 
				IF(satuan=1,'Hari Kerja', IF(satuan=2,'Hari Kalender', IF(satuan=3,'Bulan', '')) ) AS  satuan,
				reminder,dari_proses_id,a.nama AS jenis_perkara,b.nama AS proses,p.aktif
				FROM proses_alur_perkara AS p
				LEFT JOIN 
				alur_perkara AS a
				ON p.alur_perkara_id = a.id
				LEFT JOIN
				(SELECT id, nama FROM proses) AS b
				ON b.id = p.dari_proses_id ".$where." ORDER BY ".$orderby." ".$asc_desc." LIMIT ".$begin.",50;");
		} catch (Exception $e) {
			
		}
	}

	function get_list_tahapan(){
		try {
			$this->db->select('id, nama');
			$this->db->order_by('nama');
			return $this->db->get('tahapan_proses');
		} catch (Exception $e) {
			
		}
	}

	function get_list_perkara(){
		try {
			$this->db->select('id, nama');
			$this->db->order_by('nama');
			return $this->db->get('alur_perkara');
		} catch (Exception $e) {
			
		}
	}

	function get_list_proses(){
		try {
			$this->db->select('id, nama');
			$this->db->order_by('nama');
			return $this->db->get('proses');
		} catch (Exception $e) {
			
		}
	}

	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 get_reminder_info($id){
		if(empty($id)){
			return "";
		}
		try {
			return $this->db->query("SELECT SQL_CALC_FOUND_ROWS p.id, 
				tahapan_nama, proses_nama, batas_waktu, p.aktif, 
				satuan,reminder,dari_proses_id,a.nama AS jenis_perkara
				FROM proses_alur_perkara AS p
				LEFT JOIN 
				alur_perkara AS a
				ON p.alur_perkara_id = a.id
				WHERE p.id=".$id);
		} catch (Exception $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){
		try {
			$data = array(
				'datetime' => date("Y-m-d H:i:s"),
				'ipaddress' => $this->input->ip_address(),
				'username' => $this->session->userdata('username'),
				'tablename' => 'proses_alur_perkara',
				'formname' => 'Data Reminder Perkara',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}

	function update_data($data,$id){
		try {
			$this->db->where('id', $id);
			$this->db->update('proses_alur_perkara', $data); 
			$title = "Edit Data Reminder Perkara [Reminder Perkara=<b>".$id."</b>]<br />Edit table <b>proses_alur_perkara</b> dari halaman <b>Reminder Perkara</b> dengan Primary Key [ID Reminder Perkara=".$id."]";
			$descrip = $this->fetch_description($title,$data);
			$this->add_audittrail("UPDATE",$title,$descrip);
			return TRUE;
		} catch (Exception $e) {
			return FALSE;
		}
	}


}
?>