<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Muserjob extends CI_Model {
	var $table = 'users_job';	
   var $column_order = array('job.job_id','categories.category_name','job.job_address','location','job.client_name','job_date','job.job_start_time','job.job_end_time','name',null); //set column field database for datatable orderable
   var $column_search = array('job.job_id','categories.category_name','job.job_address','city.name','job.client_name','DATE_FORMAT(job.job_date, "%d/%m/%Y")','job.job_start_time','job.job_end_time','CONCAT(users.first_name," ",users.last_name)'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    var $order = array('users_job.users_job_id' => 'desc'); // default order 
    public function __construct() {
        parent::__construct();
    }
	
	private function _get_datatables_query(){
		$this->db->select('*,CONCAT(first_name," ",last_name) name,DATE_FORMAT(job.job_date, "%d/%m/%Y") job_date,city.name location');
        $this->db->from($this->table);
		$this->db->join('job', 'job.job_id = users_job.job_id', 'left');
		$this->db->join('users', 'users.user_id = users_job.user_id', 'left');		
		$this->db->join('categories', 'categories.category_id = job.cat_id', 'left');
		$this->db->join('city', 'city.city_id = job.city_id', 'left');
		
        $i = 0;
        foreach ($this->column_search as $item){ // loop column 
			//echo $_POST['search']['value'];exit;
            if($_POST['search']['value']){ // if datatable send POST for search                 
                if($i===0){ // first loop
                    $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
                    $this->db->like($item, $_POST['search']['value']);
                }
                else{
                    $this->db->or_like($item, $_POST['search']['value']);
                }
                if(count($this->column_search) - 1 == $i) //last loop
                $this->db->group_end(); //close bracket
            }
            $i++;
        }
         
        if(isset($_POST['order'])){ // here order processing
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        }else if(isset($this->order)){
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }
	
	private function _get_datatables_query_with_date($start_date,$end_date){
		$this->db->select('*,CONCAT(first_name," ",last_name) name,DATE_FORMAT(job.job_date, "%d/%m/%Y") job_date');
        $this->db->from($this->table);
		$this->db->join('job', 'job.job_id = users_job.job_id', 'left');
		$this->db->join('users', 'users.user_id = users_job.user_id', 'left');
		$this->db->join('categories', 'categories.category_id = job.cat_id', 'left');
		$this->db->where('DATE_FORMAT(job.job_date, "%d/%m/%Y") >=', $start_date);
		$this->db->where('DATE_FORMAT(job.job_date, "%d/%m/%Y") <=', $end_date);
        $i = 0;
        foreach ($this->column_search as $item){ // loop column 
			//echo $_POST['search']['value'];exit;
            if($_POST['search']['value']){ // if datatable send POST for search                 
                if($i===0){ // first loop
                    $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
                    $this->db->like($item, $_POST['search']['value']);
                }
                else{
                    $this->db->or_like($item, $_POST['search']['value']);
                }
                if(count($this->column_search) - 1 == $i) //last loop
                $this->db->group_end(); //close bracket
            }
            $i++;
        }
         
        if(isset($_POST['order'])){ // here order processing
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        }else if(isset($this->order)){
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
	}
	
	public function get_datatables($start_date,$end_date){
       if($start_date == '' && $end_date == '')
		{
			 $this->_get_datatables_query();
		}else{
			$this->_get_datatables_query_with_date($start_date,$end_date);
		}    
        if($_POST['length'] != -1)
        $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
		
		
        return $query->result();
    }
	
	public function count_filtered($start_date,$end_date){
		if($start_date == '' && $end_date == '')
		{
			 $this->_get_datatables_query();
		}else{
			$this->_get_datatables_query_with_date($start_date,$end_date);
		}        
        $query = $this->db->get();
        return $query->num_rows();
    }
	
	public function count_all(){
        $this->db->from($this->table);		
        return $this->db->count_all_results();
    }
	
	public function get_details($users_job_id){
		$this->db->select('*,DATE_FORMAT(job.job_date, "%d/%m/%Y") job_date');
        $this->db->from($this->table);
		$this->db->join('job', 'job.job_id = users_job.job_id', 'left');
		$this->db->join('users', 'users.user_id = users_job.user_id', 'left');
		$this->db->join('categories', 'categories.category_id = job.cat_id', 'left');		
        $this->db->where('users_job.users_job_id',$users_job_id);
        $query=$this->db->get();
        return $query->row_array();
    }
	
	public function get_sub_category($category_id)
	{
		$this->db->select('*');
        $this->db->from('categories');
		$this->db->where('category_id',$category_id);
        $query=$this->db->get();
        return $query->row_array();
	}
	
	public function delete($condition){
        $this->db->delete($this->table,$condition);
        return 1;
    }
}