<?php

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

class Users_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

//GET USERS 
    function getusers() {
        $page = $this->input->post('page');
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        
        //Default values
        if (!$sidx)
            $sidx = 'id';
        if (!$sord)
            $sord = 'asc';

        if (!$page)
            $page = 1;
        if (!$limit)
            $limit = 25;
        $search = $this->input->post('_search');
        if($search=='true')
        {
                $searchField = $this->input->post('searchField');
                $searchOper = $this->input->post('searchOper');
                $searchString = $this->input->post('searchString');
                $oper='';
                if($searchField =="ROLE"){
                    if($searchOper=='cn')
                    $searchQry = 'ROLES.NAME LIKE "%' . $searchString . '%"';
                else if($searchOper=='eq')
                    $searchQry = 'ROLES.NAME = "' . $searchString .'"';

                $this->db->where($searchQry); 
                }
                else{
                if($searchOper=='cn')
                    $searchQry = 'PEOPLE.'.$searchField . ' LIKE "%' . $searchString . '%"';
                else if($searchOper=='eq')
                    $searchQry = 'PEOPLE.'.$searchField . ' = "' . $searchString .'"';

                $this->db->where($searchQry);
                }}
        
        //Total Returned Rows Count
        $this->db->from('PEOPLE');
        $this->db->join("ROLES", "ROLES.ID  = PEOPLE.ROLE");
        $count = $this->db->count_all_results();

        //Total Pages
        if ($count > 0 && $limit > 0) {
            $total_pages = ceil($count / $limit);
        } else {
            $total_pages = 0;
        }

        //Check requested page
        if ($page > $total_pages)
            $page = $total_pages;

        //Set start record number
        $start = $limit * $page - $limit;

        //Format data for jqGrid output
        $data['page'] = "" . $page . "";
        $data['total'] = "" . $total_pages . "";
        $data['records'] = "" . $count . "";
        
        //Fetch data
        $this->db->select("PEOPLE.ID,PEOPLE.NAME,PEOPLE.APPPASSWORD AS PASSWORD,PEOPLE.APPPASSWORD AS CONFIRMPASSWORD,PEOPLE.CARD,ROLES.NAME AS ROLE");
        $this->db->join("ROLES", "ROLES.ID  = PEOPLE.ROLE");
        $this->db->where("ROLES.ID !=",98);
        $this->db->order_by($sidx, $sord);
        
        
        $this->db->limit($limit, $start);
        if($search=='true') $this->db->where($searchQry);
        $rows = $this->db->get("PEOPLE")->result_array();

        //Format data
        $i = 0;
        foreach ($rows as $row) {
            $data['rows'][$i]['id'] = $i + 1;
            $a = $i + 1;
            $data['rows'][$i]['cell'] = array($row['ID'], $row['NAME'], $row['PASSWORD'], $row['CONFIRMPASSWORD'],$row['CARD'], $row['ROLE']);
            $i++;
        }
        return json_encode($data);
    }

    //GET USER ROLES
    function getrols() {
        $page = $this->input->post('page');
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
        
        //Default values
        if (!$sidx)
            $sidx = 'id';
        if (!$sord)
            $sord = 'asc';
        if (!$page)
            $page = 1;
        if (!$limit)
            $limit = 25;
        $search = $this->input->post('_search');
                if($search=='true')
                {
                $searchField = $this->input->post('searchField');
                $searchOper = $this->input->post('searchOper');
                $searchString = $this->input->post('searchString');
                $oper='';
                if($searchOper=='cn')
                    $searchQry = 'ROLES.'.$searchField . ' LIKE "%' . $searchString . '%"';
                else if($searchOper=='eq')
                    $searchQry = 'ROLES.'.$searchField . ' = "' . $searchString .'"';

                $this->db->where($searchQry);
                }
        //Total Returned Rows Count
        $this->db->from('ROLES');
        $count = $this->db->count_all_results();

        //Total Pages
        if ($count > 0 && $limit > 0) {
            $total_pages = ceil($count / $limit);
        } else {
            $total_pages = 0;
        }

        //Check requested page
        if ($page > $total_pages)
            $page = $total_pages;

        //Set start record number
        $start = $limit * $page - $limit;

        //Format data for jqGrid output
        $data['page'] = "" . $page . "";
        $data['total'] = "" . $total_pages . "";
        $data['records'] = "" . $count . "";
        
        //Fetch data
        $this->db->select("ID, NAME,PERMISSIONS");
        $where='ID <> "-1" AND ID <> "0"';
        $this->db->where($where);
        $this->db->order_by($sidx, $sord);
        //Total Pages
        if ($count > 0 && $limit > 0) {
            $total_pages = ceil($count / $limit);
            $start = $limit * $page - $limit;
        } else {
            $total_pages = 0;
            $start=0;
        }
        $this->db->limit($limit, $start);
        if($search=='true') $this->db->where($searchQry);
        $rows = $this->db->get("ROLES")->result_array();
        
        //Format data
        $i = 0;
        foreach ($rows as $row) {
            $data['rows'][$i]['id'] = $i + 1;
            $a = $i + 1;
            $data['rows'][$i]['cell'] = array($row['ID'], $row['NAME'],$row['PERMISSIONS']);
            $i++;
        }
        return json_encode($data);
    }

    public function getRoles() {
        return $this->db->get('ROLES')->result();
    }

    public function getRole($id) {
        $this->db->where('ID', $id);
        return $this->db->get('ROLES')->result();
    }

    function getUserID($name) {
        $this->db->where('NAME', $name);
        $q = $this->db->get('PEOPLE')->result();
        return $q[0]->ID;
    }

    public function roleName($id) {
        $this->db->where('ID', $id);
        $q = $this->db->get('ROLES')->result();
        return $q[0]->NAME;
    }

    function validateLogin($user, $pass) {
        $this->db->where('NAME', $user);
        $this->db->where('APPPASSWORD', 'sha1:' . strtoupper(sha1($pass)));
        $q = $this->db->get('PEOPLE');
//         echo  $this->db->last_query();exit;
        return ($q->num_rows() > 0) ? true : false;
    }
    
    function getLogindetails($user, $pass) {
        $this->db->select('*');
        $this->db->where('NAME', $user);
        $this->db->where('APPPASSWORD', 'sha1:' . strtoupper(sha1($pass)));
        $q = $this->db->get('PEOPLE')->result();
        return $q[0];
    }
    
      function validateLoginType($user, $pass) {
        $this->db->where('NAME', $user);
        $this->db->where('APPPASSWORD', 'sha1:' . strtoupper(sha1($pass)));
        $q = $this->db->get('PEOPLE');
        return ($q->num_rows > 0) ? true : false;
    }

    function getpermissions($user) {
        $this->db->select('ROLES.PERMISSIONS');
        $this->db->where('PEOPLE.NAME', $user);
        $this->db->join('ROLES', 'PEOPLE.ROLE = ROLES.ID');
        $q = $this->db->get('PEOPLE')->result();;
        return $q[0]->PERMISSIONS;
    }
    
    public function getoldpassword($id) {
        $this->db->like('ID', $id);
        $q = $this->db->get('PEOPLE')->result();;
        return $q[0]->APPPASSWORD;
    }
      
    function getidbyrole($name) {
        $this->db->where('NAME', $name);
        $q = $this->db->get('ROLES')->result();
        return $q[0]->ID;
    }
      
    function getrolesautocomplete($term) {
        $this->db->select('ID, NAME');
         $where = "NAME LIKE '". $term . "%'";
         $this->db->where($where);
        return $this->db->get("ROLES")->result();
    }
    function getroleidautocomplete($term) {
        $this->db->select('ID');
        $where = "ID LIKE '". $term . "%'";
        $this->db->where($where);
        return $this->db->get("ROLES")->result();
    }
    
    function add($form_data){
        $this->db->insert('PEOPLE', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
     
     function update($id, $form_data){
        $this->db->where('ID', $id);
        $this->db->update('PEOPLE', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
     
     function delete($id) {
        return ($this->db->delete('PEOPLE', array('ID' => $id))) ? true : false;
     }

     function addrole($form_data){
        $this->db->insert('ROLEfS', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
  
     function updaterole($id, $form_data){
        $this->db->where('ID', $id);
        $this->db->update('ROLfES', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
		return FALSE;
     }
     
     function deleterole($id) {
        return ($this->db->delete('ROLES', array('ID' => $id))) ? true : false;
     }
     
     public function changepassword($id, $password) {
        $this->db->where('ID', $id);
        $this->db->set('APPPASSWORD', 'sha1:' . strtoupper(sha1($password)));
        return ($this->db->update('PEOPLE')) ? true : false;
     }
     
    function getusersautocomplete($term) {
        $this->db->select('ID, NAME');
        $where = "NAME LIKE '". $term . "%'";
        $this->db->where($where);
        return $this->db->get("PEOPLE")->result();
    }
    function getcardautocomplete($term) {
        $this->db->select('ID, CARD');
        $where = "CARD LIKE '". $term . "%'";
        $this->db->where($where);
        return $this->db->get("PEOPLE")->result();
    }
     function delete1($id) {
        return ($this->db->delete('ROLES', array('ID' => $id))) ? true : false;
     }
      function add1($form_data){
        $this->db->insert('ROLES', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
        function update1($id, $form_data){
        $this->db->where('ID', $id);
        $this->db->update('ROLES', $form_data);
		if ($this->db->affected_rows() == '1')
		{
			return TRUE;
		}
        return FALSE;
     }
     
      function getPermission($userName) {
       
        $this->db->select('R.PERMISSIONS');
         $this->db->join('PEOPLE AS P', 'R.ID = P.ROLE', 'LEFT');
        $this->db->where('P.NAME', $userName);
        $q = $this->db->get('ROLES AS R');
        return $q->result_array();
   
    }
}

