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

class Mjobnotification extends CI_Model {
	var $table = 'job';	
    var $column_order = array('job.job_id','car.car_no','job.service','job.service_date',null); //set column field database for datatable orderable
    var $column_search = array('job.job_id','car.car_no','job.service','job.service_date'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    //var $order = array('job.job_id' => 'desc'); // default order 
	
	 var $column_order_reject = array('assign_job.assign_job_id','name','assign_job.remarks',null); //set column field database for datatable orderable
    var $column_search_reject = array('assign_job.assign_job_id','CONCAT(admins.first_name," ",admins.last_name)','assign_job.remarks'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    //var $order = array('job.job_id' => 'desc'); // default order 
    public function __construct() {
        parent::__construct();
    }
	private function _get_datatables_query(){
		$this->db->select('job.job_id,car.car_no,job.service,job.service_date,job.job_status');
        $this->db->from($this->table);		
		$this->db->join('car','car.car_id=job.car_id','left');	
		//$date = date('Y-m-d');
		$date = '2018-11-19';
		$where = "next_service_date='".$date."' OR next_service_date_reminder_before_seven='".$date."' OR next_service_date_before_three='".$date."'";			
		$this->db->where($where);				
		 
		
        $i = 0;
        foreach ($this->column_search as $item){ // loop column		
            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_reject($job_id){		
		$this->db->select('assign_job.assign_job_id,CONCAT(admins.first_name," ",admins.last_name) name,assign_job.remarks');
        $this->db->from("assign_job");		
		$this->db->join('admins','admins.admin_id=assign_job.mechanic_id','left');	
		$this->db->where('job_id',$job_id);
		 
		
        $i = 0;
        foreach ($this->column_search_reject as $item){ // loop column		
            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_reject) - 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_reject[$_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(){
		$this->_get_datatables_query();
        if($_POST['length'] != -1)
        $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        return $query->result();
    }
	
	public function get_datatables_reject($job_id){
		$this->_get_datatables_query_reject($job_id);
        if($_POST['length'] != -1)
        $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        return $query->result();
	}
	
	public function count_filtered(){
		$this->_get_datatables_query();		
        $query = $this->db->get();
        return $query->num_rows();
    }
	
	public function count_filtered_reject($job_id){
		$this->_get_datatables_query_reject($job_id);		
        $query = $this->db->get();
        return $query->num_rows();
    }
	
	public function count_all(){
		$this->db->select('job.job_id,car.car_no,CONCAT(admins.first_name," ",admins.last_name) name,job.service,job.service_date');
        $this->db->from($this->table);		
		$this->db->join('car','car.car_id=job.car_id','left');
		//$date = date('Y-m-d');
		$date = '2018-11-19';
		$where = "next_service_date='".$date."' OR next_service_date_reminder_before_seven='".$date."' OR next_service_date_before_three='".$date."'";			
		$this->db->where($where);
		
        return $this->db->count_all_results();
    }
	
	public function count_all_reject($job_id){
		$this->db->select('CONCAT(admins.first_name," ",admins.last_name) name,assign_job.remarks');
        $this->db->from("assign_job");		
		$this->db->join('admins','admins.admin_id=assign_job.mechanic_id','left');	
		$this->db->where('job_id',$job_id);		
		
        return $this->db->count_all_results();
    }
	
	public function get_details($job_id){
		$this->db->select('job.job_id,car.car_no,job.service,job.service_date,job.job_status');
        $this->db->from($this->table);		
		$this->db->join('car','car.car_id=job.car_id','left');			
        $this->db->where('job.job_id',$job_id);
        $query=$this->db->get();
        return $query->row_array();
    }
	
	public function update($condition,$data){
		$this->db->where($condition);
		$this->db->update($this->table,$data);
		return 1;
	}
	public function add($data){
        $this->db->insert($this->table,$data);
        return $this->db->insert_id();
    }
	
	public function delete($condition){
        $this->db->delete($this->table,$condition);
        return 1;
    }
	
	public function active($condition,$data){
        $this->db->where($condition);
        $this->db->update($this->table,$data);
        return 1;
    }
	
	public function get_parent_category(){
		$this->db->select('*');
        $this->db->from('categories'); 	
        $this->db->where('parent_id=0');
        $query=$this->db->get();
        return $query->result_array();
	}
	
	public function get_client_list(){
		$this->db->select('*');
		$this->db->from('client');
		$this->db->group_by('client_name'); 
		$query=$this->db->get();
        return $query->result_array();
	}
	
	public function get_city_list(){
		$this->db->select('*');
		$this->db->from('city');
		$query=$this->db->get();
        return $query->result_array();		
	}
	
	public function get_client_address($client_name){
		$this->db->select('*');
		$this->db->from('client');
		$this->db->where('client_name',$client_name);
		$this->db->group_by('client_address');			
		$query=$this->db->get();
        return $query->result_array();
	}
	
	public function get_client_phone($client_name){
		$this->db->select('*');
		$this->db->from('job');
		$this->db->where('client_name',$client_name);
		$this->db->group_by('client_phoneno');			
		$query=$this->db->get();
        return $query->result_array();
	}
	
	public function get_sub_category($category_id){		
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('parent_id',$category_id);		
        $query=$this->db->get();
        return $query->result_array();
    }
	
	public function get_sub_category_by_category_id($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 get_parent_category_by_parent_id($parent_id){
		$this->db->select('*');
        $this->db->from('categories');
        $this->db->where('category_id',$parent_id);		
        $query=$this->db->get();
        return $query->row_array();
	}
	
	public function get_sub_categories_by_parent_id($parent_id){
		$this->db->select('*');
        $this->db->from('categories');
        $this->db->where('parent_id',$parent_id);		
        $query=$this->db->get();
        return $query->result_array();
	}
	
	public function get_category_by_id($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 deviceList($cat_id,$city_id){
        $this->db->select('*');
        $this->db->from('devices');
		$this->db->join('users','users.user_id=devices.user_id','left');	
		$this->db->where('users.cat_id',$cat_id);
		$this->db->where('users.city_id',$city_id);
        $query=$this->db->get();
        return $query->result_array(); 
    }
	
	 public function getRow($table,$condition){
        $this->db->where($condition);
        $query=$this->db->get($table);
        return $query->row_array();
    }
	
	 public function getRowA($table,$condition){
        $this->db->where($condition);
		$this->db->order_by('assign_job_id', 'DESC');
		$this->db->limit(1);
        $query=$this->db->get($table);
        return $query->row_array();
    }
	
	public function getJobInformationForclient($client_name,$client_address)
	{
		$this->db->select('*');
        $this->db->from($this->table);
		$this->db->join('categories','job.cat_id=categories.category_id','left');
		$this->db->where('client_name',$client_name);
		$this->db->where('job_address',$client_address);			
		$this->db->where('is_applied',0);		
		$query=$this->db->get();
        return $query->result_array(); 
	}
	
	public function getRows($table,$condition,$order_col=null,$order_type=null){
        $this->db->where($condition);
		
		if(isset($limit)){
          $this->db->limit($limit);      
        }
        if(!empty($order_col) && !empty($order_type)){
            $this->db->order_by($order_col,$order_type);
        }
        $query=$this->db->get($table);
        return $query->result_array();
    }
	
	public function getAsssignJob($job_id)
	{
		$this->db->select('CONCAT(admins.first_name," ",admins.last_name) name,assign_job.assign_status,assign_job.remarks');
        $this->db->from("assign_job");		
		$this->db->join('admins','admins.admin_id=assign_job.mechanic_id','left');	
		$this->db->where('job_id',$job_id);		
		$query=$this->db->get();
        return $query->result_array();
	}
	
	 public function insert($table,$data){
        $this->db->insert($table,$data);
        return $this->db->insert_id();
    }
}