<?php
class EmployeeModel extends CI_Model
{
	function __construct() {
		parent::__construct();
		$this->load->database();
	}
       
	public function add_employee($data)
	{
		$this->db->insert('lkqou_vbizz_employee',$data);
		return true;
	}

	public function update_employee($id, $ownerid, $data)
	{
		$this->db->where(array('userid'=>$id, 'ownerid'=>$ownerid));
		$this->db->update('lkqou_vbizz_employee', $data);
		return true;
	}
	public function get_employee($id, $ownerid)
	{
        $sql = ' SELECT i.*, u.username as username, v.profile_pic as profile_pic FROM lkqou_vbizz_employee as i left join lkqou_users as u on i.userid=u.id left join lkqou_vbizz_users as v on i.userid=v.userid WHERE i.userid = '.$id.' and `i`.`ownerid`='.$ownerid;
		$query = $this
                ->db
                ->query($sql);
    
        
        if($query->num_rows()>0)
        {
            return $query->result(); 
        }
        else
        {
            return null;
        }
	}

	public function delete_employee($ids){
		$this->db->where_in('id', $ids);
		$this->db->delete('lkqou_vbizz_employee');
		return true;
	}


	function all_employees_count()
    {   
        $query = $this
                ->db
                ->get('lkqou_vbizz_employee');
    
        return $query->num_rows();  

    }
    
    function all_employees($limit,$start,$col,$dir)
    {   
        $sql ='SELECT i.*, d.name as department, p.title as designation FROM lkqou_vbizz_employee as i left join lkqou_vbizz_employee_dept as d on i.department=d.id left join lkqou_vbizz_employee_desg as p on i.designation=p.id '.
              ' Order by i.'.$col.' '.$dir.
			  ' Limit '.$limit.' Offset '.$start;
		$query = $this
                ->db
                ->query($sql);
    
        
        if($query->num_rows()>0)
        {
            return $query->result(); 
        }
        else
        {
            return null;
        }
        
    }
   
    function employees_search($limit,$start,$search_array,$col,$dir, $ownerid)
    {
        $where = 'Where i.ownerid='.$ownerid;

        if(isset($search_array['search'])){
            $where .= ' and (LOWER(i.name) like "%'.$search_array['search'].'%"'.
                      ' or LOWER(i.email) like "%'.$search_array['search'].'%"'.
                      ' or LOWER(i.empid) like "%'.$search_array['search'].'%"'.
                      ' or i.userid= "'.$search_array['search'].'"'.
                      ' or i.ctc="'.$search_array['search'].'")';
        }

        if(isset($search_array['filter_dept'])){
            $where .= ' and i.department ="'.$search_array['filter_dept'].'"';
        }
        if(isset($search_array['filter_desg'])){
            $where .= ' and i.designation ="'.$search_array['filter_desg'].'"';
        }
		$sql ='SELECT i.*, d.name as department, p.title as designation FROM lkqou_vbizz_employee as i left join lkqou_vbizz_employee_dept as d on i.department=d.id left join lkqou_vbizz_employee_desg as p on i.designation=p.id '.
              $where.' Order by i.'.$col.' '.$dir.
			  ' Limit '.$limit.' Offset '.$start;
		$query = $this
                ->db
                ->query($sql);
    
        
        if($query->num_rows()>0)
        {
            return $query->result(); 
        }
        else
        {
            return null;
        }
    }

    function employees_search_count($search_array, $ownerid)
    {
		$where = 'Where i.ownerid='.$ownerid;

        if(isset($search_array['search'])){
            $where .= ' and (LOWER(i.name) like "%'.$search_array['search'].'%"'.
                      ' or LOWER(i.email) like "%'.$search_array['search'].'%"'.
                      ' or LOWER(i.empid) like "%'.$search_array['search'].'%"'.
                      ' or i.userid= "'.$search_array['search'].'"'.
                      ' or i.ctc="'.$search_array['search'].'")';
        }

        if(isset($search_array['filter_dept'])){
            $where .= ' and i.department ="'.$search_array['filter_dept'].'"';
        }
        if(isset($search_array['filter_desg'])){
            $where .= ' and i.designation ="'.$search_array['filter_desg'].'"';
        }
		$sql ='SELECT i.*, d.name as department, p.title as designation FROM lkqou_vbizz_employee as i left join lkqou_vbizz_employee_dept as d on i.department=d.id left join lkqou_vbizz_employee_desg as p on i.designation=p.id '.
              $where;
		$query = $this
                ->db
                ->query($sql);
    
        return $query->num_rows();
    } 

	function dropdown_acounts()
	{
		$this->db->select('*');
		$this->db->from('lkqou_vbizz_employee');

		$query = $this->db->get();
        
        if($query->num_rows()>0)
        {
            return $query->result(); 
        }
        else
        {
            return null;
        }

	}

    function get_roles(){
        $sql = 'SELECT employee_group_id from lkqou_vbizz_configuration';
        $query = $this
                ->db
                ->query($sql);
        $employees = $query->row()->employee_group_id;

        $sql = 'SELECT a.*, COUNT(DISTINCT b.id) AS level' .
                ' FROM lkqou_usergroups AS a' .
                ' LEFT JOIN `lkqou_usergroups` AS b ON a.lft > b.lft AND a.rgt < b.rgt where a.parent_id='.$employees.' or a.id='.$employees.
                ' GROUP BY a.id' .
                ' ORDER BY a.lft ASC';
        $query = $this
                ->db
                ->query($sql);
        if($query->num_rows()>0)
        {
            return $query->result();  
        }
        else
        {
            return null;
        }
        
        
    }

	
	
}