<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Users extends CI_Model {
	function parseOrderby($col){
		if($col==1){
			return "fullname"; 
		}else if($col==2){
			return "username"; 
		}else if($col==3){
			return "email"; 
		}else if($col==4){
			return "group_name"; 
		}else{
			return "fullname";
		}
	}

	function get_user_list($col=1,$asc_desc='ASC',$begin=0,$key=''){
		$orderby = $this->parseOrderby($col);
		$where = '';
		if(!empty($key)){
			$where .= ' AND (fullname LIKE "%'.$key.'%" 
				OR username LIKE "%'.$key.'%" 
				OR email LIKE "%'.$key.'%" 
				OR group_name LIKE "%'.$key.'%") ';
		}
		try {
			return $this->db->query("SELECT SQL_CALC_FOUND_ROWS userid, userid, fullname, username, email, 
				group_name AS alternative_email,IF(block=1,'Ya','Tidak') AS block,
				IF(hakim_id IS NOT NULL ,concat('Hakim : ',hakim_nama),
				IF(panitera_id IS NOT NULL, concat('Panitera: ',`panitera_nama`),
				IF(jurusita_id IS NOT NULL, concat ('Jurusita: ',jurusita_nama),' ') ) ) AS keterangan
				FROM v_users WHERE group_id >".$this->session->userdata('idgroup')." ".$where." ORDER BY ".$orderby." ".$asc_desc." LIMIT ".$begin.",50;");
		} 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_user_info($userid){
		if(empty($userid)){
			return "";
		}
		try {
			return $this->db->query("SELECT userid, fullname, username, email, 
				alternative_email,block,
				hakim_id,
				panitera_id,
				jurusita_id,user_expired,group_id
				FROM v_users WHERE userid = ".$userid." AND group_id >=".$this->session->userdata('idgroup').";");
		} catch (Exception $e) {
			
		}
	}

	function get_group_list(){
		try {
			$sql = "SELECT groupid, getTreeName(LEVEL-2,NAME) AS NAME FROM sys_groups WHERE enable=1 AND lft > 2 AND rgt <= 1000 ORDER BY lft;";
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}

	function get_list_hakim(){
		try {
			$sql = "SELECT id,nama_gelar FROM hakim_pn WHERE aktif='Y' ORDER BY nama ASC;";
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}

	function get_list_panitera(){
		try {
			$sql = "SELECT id,nama_gelar FROM panitera_pn WHERE aktif='Y' ORDER BY nama ASC;";
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}

	function get_list_jurusita(){
		try {
			$sql = "SELECT id,nama_gelar FROM jurusita WHERE aktif='Y' ORDER BY nama ASC;";
			return $this->db->query($sql);
		} catch (Exception $e) {
			
		}
	}

	function get_last_id(){
		try {
			$this->db->select_max('userid');
			$query = $this->db->get('sys_users');
			if ($query->num_rows() > 0){
				return $query->row();
			}else{
				return 0;
			}		
		} 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' => 'sys_users',
				'formname' => 'Data User',
				'action' => $action,
				'title' => $title,
				'description' => $descrip
			);
			$this->db->insert('sys_audittrail', $data);
		} catch (Exception $e) {
			
		}
	}

	function add_data($data_user,$data_group,$data_optional){
		try {
			$this->db->insert('sys_users', $data_user);
			$this->db->insert('sys_user_group', $data_group);
			if(!empty($data_optional) AND !empty($data_optional['table'])){
				$tables = array('user_panitera', 'user_hakim', 'user_jurusita');
				$this->db->where('userid', $data_optional['userid']);
				$this->db->delete($tables);
				$this->db->query('REPLACE INTO '.$data_optional['table'].' VALUES ('.$data_optional['userid'].','.$data_optional['col_value'].');');
			}
			$title = "Tambah User [Username=<b>".$data_user['username']."</b>]<br />Tambah table <b>sys_users</b> dari halaman <b>users_edit</b> dengan Primary Key [userid=".$data_user['userid']."]";
			$descrip = $this->fetch_description($title,$data_user);
			$this->add_audittrail("INSERT",$title,$descrip);
			return 1;
		} catch (Exception $e) {
			return 0;
		}
	}

	function update_data($data_user,$data_group,$userid,$data_optional){
		try {
			$this->db->where('userid', $userid);
			$this->db->update('sys_users', $data_user);
			$this->db->where('userid', $userid);
			$this->db->delete('sys_user_group');
			$this->db->insert('sys_user_group', $data_group);
			if(!empty($data_optional)){
				$tables = array('user_panitera', 'user_hakim', 'user_jurusita');
				$this->db->where('userid', $data_optional['userid']);
				$this->db->delete($tables);
				if(!empty($data_optional['table'])){
					$this->db->query('REPLACE INTO '.$data_optional['table'].' VALUES ('.$data_optional['userid'].','.$data_optional['col_value'].');');
				}
			}
			$title = "Edit User [Username=<b>".$data_user['username']."</b>]<br />Edit table <b>sys_users</b> dari halaman <b>users_edit</b> dengan Primary Key [userid=".$userid."]";
			$descrip = $this->fetch_description($title,$data_user);
			$this->add_audittrail("UPDATE",$title,$descrip);
			return 1;
		} catch (Exception $e) {
			return 0;
		}
	}

	function update_profile($data_user,$userid){
		try {
			$this->db->where('userid', $userid);
			$this->db->update('sys_users', $data_user);
			$title = "Edit User [Username=<b>".$this->session->userdata('username')."</b>]<br />Edit table <b>sys_users</b> dari halaman <b>users_edit</b> dengan Primary Key [userid=".$userid."]";
			$descrip = $this->fetch_description($title,$data_user);
			$this->add_audittrail("UPDATE",$title,$descrip);
			return 1;
		} catch (Exception $e) {
			return 0;
		}
	}
}
?>