<?php 
class User_model extends CI_Model {
	
	function update_data() {
		
		$id = $this->input->post('userid');
			
		$data['username'] 		= trim($this->input->post('username'));
		$data['alias'] 			= $this->input->post('alias');
		$data['user_status'] 	= $this->input->post('user_status');
		$data['role_id'] 		= $this->input->post('role_id');
		$data['id_karyawan'] 	= $this->input->post('id_karyawan');
		
		$data['modified_date']	= date("Y-m-d H:i:s");
		$data['modified_by']	= $this->session->userdata('username');
		
		$this->db->where("userid",$id);
		$q = $this->db->update("adm_user",$data);
		return $q;	
		
	}
	
	function getDefaultPass() {
		$this->db->select("value_config");
		$this->db->from("adm_config");
		$this->db->where("name_config","PASSDEF");
		$val = $this->db->get()->row()->value_config;
		return $val;
	}
	
	function insert_data() {
				
		$data['username'] 		= trim($this->input->post('username'));
		$data['password'] 		= md5($this->getDefaultPass());		
		$data['alias'] 			= $this->input->post('alias');
		$data['user_status'] 	= $this->input->post('user_status');
		$data['role_id'] 		= $this->input->post('role_id');
		$data['id_karyawan'] 	= $this->input->post('id_karyawan');
	
		$data['created_date']	= date("Y-m-d H:i:s");
		$data['created_by']	= $this->session->userdata('username');
		$q = $this->db->insert("adm_user",$data);
		return $q;	
		
	}
	
	function delete_data() {
		
		$del = false;
		$id = $this->input->post('userid');
		if (!empty($id)) {
			$this->db->where("userid",$id);
			$del = $this->db->delete("adm_user");
			
		}
		
		return $del;
	}
	
	function get_value_edit($id) {
		$this->db->select("*");
		$this->db->from("adm_user");
		$this->db->where("userid",$id);
		$data = $this->db->get()->row();
		return $data;
	}
	
	function getRole() {
	
		$this->db->select("role_name,role_id");
		$this->db->from("adm_roles");
		$data = $this->db->get()->result_array();
		return $data;
		
	}
	
	function getKar() {
		
		$this->db->select("nama_karyawan,id");
		$this->db->from("ref_karyawan");
		$data = $this->db->get()->result_array();
		return $data;
		
	}
	
	function cekKaryawan($id_karyawan) {
		$this->db->select("userid");
		$this->db->from("adm_user");
		$this->db->where("id_karyawan",$id_karyawan);
		$num = $this->db->get()->num_rows();
		return $num;
	}
	
	function cekKaryawanUpdate($id_karyawan,$userid) {
		
		//get old role 
		$this->db->select("id_karyawan");
		$this->db->from("adm_user");
		$this->db->where("id_karyawan",$id_karyawan);
		$oldId = $this->db->get()->row()->id_karyawan;
		if ($oldId == $id_karyawan) {
			$num = 0;
		} else {
			
			$this->db->select("userid");
			$this->db->from("adm_user");
			$this->db->where("id_karyawan",$id_karyawan);
			$num = $this->db->get()->num_rows();
			
		}		
		return $num;
	}
	
	function cekUsername($username) {
		
		$this->db->select("userid");
		$this->db->from("adm_user");
		$this->db->where("upper(username)",strtoupper($username));
		$num = $this->db->get()->num_rows();
		return $num;
		
	}
	
	function cekUsernameUpdate($username,$userid) {
		
		//get old role 
		$this->db->select("username");
		$this->db->from("adm_user");
		$this->db->where("userid",$userid);
		$oldName = $this->db->get()->row()->username;
		if (strtoupper($oldName) == strtoupper($username)) {
			$num = 0;
		} else {
			
			$this->db->select("userid");
			$this->db->from("adm_user");
			$this->db->where("upper(username)",strtoupper($username));
			$num = $this->db->get()->num_rows();
			
		}		
		return $num;
	}
	
	function get_list_data() {
	
		$param = $this->input->post(array('page', 'rows', 'sort', 'order', 'filterRules'));
        $offset = intval(($param['page'] - 1) * $param['rows']);
		
		// clause filter, array not null
        $cond = '';
        if (count($param['filterRules']) > 0) {
            $filter = json_decode($param['filterRules']);
            $loop = 0;
            foreach ($filter as $json) {
                // convert to array
                $rule = get_object_vars($json);
                // declare variable
                $field = $rule['field'];
                $opt = $rule['op'];
                $value = $rule['value'];
                if ($loop == 0) {
                    // user where
                    if (!empty($value)) {
                        if ($opt == 'contains') {
                            $cond .= " and ($field like '%$value%')";
                        } else if ($opt == 'greater') {
                            $cond .= " and $field > '$value'";
                        } else if ($opt == 'less') {
                            $cond .= " and $field < '$value'";
                        } else if ($opt == 'notequal') {
                            $cond .= " and $field != '$value'";
                        } else if ($opt == 'equal') {
                            $cond .= "and $field = '$value'";
                        }
                        $loop++; // flag where
                    }
                } else {
                    // user and
                    if (!empty($value)) {
                        if ($opt == 'contains') {
                            $cond .= " and ($field like '%$value%')";
                        } else if ($opt == 'greater') {
                            $cond .= " and $field > '$value'";
                        } else if ($opt == 'less') {
                            $cond .= " and $field < '$value'";
                        } else if ($opt == 'notequal') {
                            $cond .= " and $field != '$value'";
                        } else if ($opt == 'equal') {
                            $cond .= " and $field = '$value'";
                        }
                    }
                }
            }
        }
		
		$response = array();
		//$table = 'adm_user'; 
		 
        if (empty($param['sort'])) {
            if (empty($cond)) {
                $sql = "select 
							a.userid,
							a.username,
							a.alias,
							a.user_status,
							b.role_name,
							c.nama_karyawan,
							d.jabatan,
							a.modified_date,
							a.modified_by,
							a.created_by,
							a.created_date
						from adm_user a,
							 adm_roles b,
							 ref_karyawan c,
							 ref_jabatan d
						where a.role_id = b.role_id
						 and  a.id_karyawan = c.id
						 and  c.jabatan = d.jabatan
						order by a.userid limit ?, ?";
                $sqlcount = "
					select 
							a.userid
						from adm_user a,
							 adm_roles b,
							 ref_karyawan c,
							 ref_jabatan d
						where a.role_id = b.role_id
						 and  a.id_karyawan = c.id
						 and  c.jabatan = d.jabatan
						order by a.userid
				";
            } else {
                $sql = "				 
	`				selectselect 
						a.userid,
						a.username,
						a.alias,
						a.user_status,
						b.role_name,
						c.nama_karyawan,
						d.jabatan,
						a.modified_date,
						a.modified_by,
						a.created_by,
						a.created_date
					from adm_user a,
						 adm_roles b,
						 ref_karyawan c,
						 ref_jabatan d
					where a.role_id = b.role_id
					 and  a.id_karyawan = c.id
					 and  c.jabatan = d.jabatan
	  	             $cond  
					 order by a.userid limit ?, ?";
                $sqlcount = "
					selectselect 
						a.userid
					from adm_user a,
						 adm_roles b,
						 ref_karyawan c,
						 ref_jabatan d
					where a.role_id = b.role_id
					 and  a.id_karyawan = c.id
					 and  c.jabatan = d.jabatan
	  	             $cond
				";
            }
            $result_array = $this->db->query($sql, array($offset, intval($param['rows'])));
            $response['total'] = $this->db->query($sqlcount)->num_rows();
            $response['rows'] = $result_array->result();
        } else {
            $sort = $param['sort'];
            $order = $param['order'];
            if (empty($cond)) {
                $sql = "
					select 
							a.userid,
							a.username,
							a.alias,
							a.user_status,
							b.role_name,
							c.nama_karyawan,
							d.jabatan,
							a.modified_date,
							a.modified_by,
							a.created_by,
							a.created_date
						from adm_user a,
							 adm_roles b,
							 ref_karyawan c,
							 ref_jabatan d
						where a.role_id = b.role_id
						 and  a.id_karyawan = c.id
						 and  c.jabatan = d.jabatan
					order by $sort $order limit ?, ?";
                $sqlcount = "select 
							a.userid
						from adm_user a,
							 adm_roles b,
							 ref_karyawan c,
							 ref_jabatan d
						where a.role_id = b.role_id
						 and  a.id_karyawan = c.id
						 and  c.jabatan = d.jabatan";
            } else {
                $sql = "
					select 
							a.userid,
							a.username,
							a.alias,
							a.user_status,
							b.role_name,
							c.nama_karyawan,
							d.jabatan,
							a.modified_date,
							a.modified_by,
							a.created_by,
							a.created_date
						from adm_user a,
							 adm_roles b,
							 ref_karyawan c,
							 ref_jabatan d
						where a.role_id = b.role_id
						 and  a.id_karyawan = c.id
						 and  c.jabatan = d.jabatan
						$cond order by $sort $order limit ?, ?";
                $sqlcount = "
					select 
							a.userid,
							a.username,
							a.alias,
							a.user_status,
							b.role_name,
							c.nama_karyawan,
							d.jabatan,
							a.modified_date,
							a.modified_by,
							a.created_by,
							a.created_date
						from adm_user a,
							 adm_roles b,
							 ref_karyawan c,
							 ref_jabatan d
						where a.role_id = b.role_id
						 and  a.id_karyawan = c.id
						 and  c.jabatan = d.jabatan
						 $cond";
            }
            $result_array = $this->db->query($sql, array($offset, intval($param['rows'])));
            $response['total'] = $this->db->query($sqlcount)->num_rows();
            $response['rows'] = $result_array->result();
        }
        return $response;
	}
	
}