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

class Mservicedue extends CI_Model {
	var $table = 'job';	
    var $column_order = array('','car.car_no','job_service_date.service_date',null); //set column field database for datatable orderable
    var $column_search = array('car.car_no','job_service_date.service_date'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    var $order = array('job_service_date_id' => 'desc'); // default order 
    public function __construct() {
        parent::__construct();
    }
/*private function _get_datatables_query($start_date,$end_date,$car_id){      
        $this->db->select('*');
        $this->db->from($this->table);  
        $this->db->join('car', 'car.car_id = job_service_date.car_id', 'left');             
        $this->db->where("job_service_date.service_date>=",$start_date);
        $this->db->where("job_service_date.service_date<=",$end_date);  
        $this->db->where("job_service_date.car_id",$car_id);    
        
        $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){ 
//   $this->db->distinct();
//   $this->db->select('car_id');
//   $this->db->where("job_service_date.next_service_date>=",$start_date);
//   $this->db->where("job_service_date.next_service_date<=",$end_date);  
//   $query = $this->db->get('job_service_date');
//   $result_cars = $query->result_array();
//   foreach ($result_cars as $key => $item) {
    
//       $jobs = $this->mservicedue->getJobs($item['car_id'],$start_date,$end_date);
//       $services = $this->mservicedue->getServices($jobs);

// ########### Get transmission Id ###################
//         $this->db->select('*');     
//         $this->db->like('service_name', 'Transmission');
//         $query=$this->db->get('service');
//         $tservice_id = $query->row_array();
//         $transmiison_service_id = $tservice_id['service_id'];  
//  ########### Get ENGINE Id ###################       
//         $this->db->select('*');     
//         $this->db->like('service_name', 'Engine');
//         $query=$this->db->get('service');
//         $eservice_id = $query->row_array();
//         $engine_service_id = $eservice_id['service_id'];
// ########### Get BRAKE Id ###################
//         $this->db->select('*');     
//         $this->db->like('service_name', 'SPARK PLUG');
//         $query=$this->db->get('service'); 
//         $bservice_id = $query->row_array();
//         $brake_service_id = $bservice_id['service_id']; 
        

//       $arra=array($transmiison_service_id,$engine_service_id,$brake_service_id);
//       $arra2=array_intersect($arra, $services);
//       //var_dump($arra2); die;
//       if(sizeof($arra2) > 0){
//             $due_response[] = array($item['car_id']);
//               if (in_array($transmiison_service_id, $services)){
//                      array_push($due_response[$key],"Yes");
//                   }
//                   else{
//                          array_push($due_response[$key],"No");          
//                       }
//                 if (in_array($engine_service_id, $services)){
//                       array_push($due_response[$key],"Yes");
//                   }
//                   else{
//                       array_push($due_response[$key],"No");
//                   }
//                 if (in_array($brake_service_id, $services)){
//                       array_push($due_response[$key],"Yes");
//                   }
//                   else{
//                       array_push($due_response[$key],"No");
//                   }
//             }
//      }
//         return $due_response;
// ##############################################################
//     }
    
    public function get_datatables($start_date,$end_date){ 
  // $where_service_cond="()";    
   $this->db->distinct();
   $this->db->select('job_service_date.car_id');
   $this->db->where("job_service_date.next_service_date>=",$start_date);
   $this->db->where("job_service_date.next_service_date<=",$end_date);  
   $this->db->join('car', 'car.car_id = job_service_date.car_id');
   $this->db->order_by('car.car_no', 'ASC');
   $query = $this->db->get('job_service_date');
   $result_cars = $query->result_array();
   foreach ($result_cars as $key => $item) {
    
       $jobs = $this->mservicedue->getJobs($item['car_id'],$start_date,$end_date);
       $services = $this->mservicedue->getServices($jobs);

########### Get transmission Id ###################
        $this->db->select('*');     
        $this->db->like('service_name', 'Transmission');
        $query=$this->db->get('service');
        $tservice_id = $query->row_array();
        $transmiison_service_id = $tservice_id['service_id'];  
 ########### Get ENGINE Id ###################       
        $this->db->select('*');     
        $this->db->like('service_name', 'Engine');
        $query=$this->db->get('service');
        $eservice_id = $query->row_array();
        $engine_service_id = $eservice_id['service_id'];
########### Get BRAKE Id ###################
        $this->db->select('*');     
        $this->db->like('service_name', 'SPARK PLUG');
        $query=$this->db->get('service'); 
        $bservice_id = $query->row_array();
        $brake_service_id = $bservice_id['service_id']; 
        

       $arra=array($transmiison_service_id,$engine_service_id,$brake_service_id);
      // print_r($services); die;
       $arra2=array_intersect($arra, $services);
      // var_dump($arra2); die;
       if(sizeof($arra2) > 0){
            $due_response[] = array($item['car_id']);
               if (in_array($transmiison_service_id, $services)){
                     array_push($due_response[$key],"Yes");
                   }
                   else{
                         array_push($due_response[$key],"No");          
                       }
                if (in_array($engine_service_id, $services)){
                      array_push($due_response[$key],"Yes");
                   }
                   else{
                      array_push($due_response[$key],"No");
                   }
                if (in_array($brake_service_id, $services)){
                      array_push($due_response[$key],"Yes");
                   }
                   else{
                      array_push($due_response[$key],"No");
                   }
            }
     }
    
     for($i=0;$i<count($due_response);$i++)
     {
         if(count($due_response[$i])==4)
         {
            $responseArr_final[]= $due_response[$i]; 
         }
        
     }
        return $responseArr_final;
##############################################################
    }
  public function getJobs($car_id,$start_date,$end_date){
           $this->db->distinct();
           $this->db->select('job_id');
           $this->db->where("job_service_date.next_service_date>=",$start_date);
           $this->db->where("job_service_date.next_service_date<=",$end_date);  
           $this->db->where('car_id',$car_id);
           $query = $this->db->get('job_service_date');
           $result_jobs = $query->result_array();
            foreach ($result_jobs as $key => $item) {
                  $all_jobs = $all_jobs.$item['job_id'].",";
                }
                $all_jobs = rtrim($all_jobs, ',');
           return $all_jobs;
    }	
    public function getServices($jobs){
        $service_id_str='7,8,20';
           $this->db->distinct();
           $this->db->select('service_id');  
           $this->db->where_in('job_id',$jobs, FALSE);
           $this->db->where_in('service_id',$service_id_str, FALSE);
           $query = $this->db->get('job_services');
          // echo $this->db->last_query();die;
           $result_services = $query->result_array();
           foreach ($result_services as $value) {
             $all_services[] = $value['service_id'];
           }
           return $all_services;
    }







	public function count_filtered($start_date,$end_date,$car_id){
        $this->_get_datatables_query($start_date,$end_date,$car_id);
        $query = $this->db->get();
        return $query->num_rows();
    }
	
	public function count_all($start_date,$end_date,$car_id){
		$this->db->select('*');
        $this->db->from($this->table);	
		$this->db->join('car', 'car.car_id = job_service_date.car_id', 'left');				
		$this->db->where("job_service_date.service_date>=",$start_date);
		$this->db->where("job_service_date.service_date<=",$end_date);	
		$this->db->where("job_service_date.car_id",$car_id);			
        return $this->db->count_all_results();
    }
	
	public function get_details($job_id){
		$this->db->select('*');
        $this->db->from('job_services');
		$this->db->join('service', 'service.service_id = job_services.service_id', 'left');
        $this->db->where('job_id',$job_id);
        $query=$this->db->get();
        return $query->result_array();
    }
	
	public function get_user_slots($user_id){
		$this->db->from('user_time_slot');        
        $this->db->where('user_id',$user_id);
        $query=$this->db->get();
        return $query->result_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 add_payment($table,$data){
        $this->db->insert($table,$data);
        return $this->db->insert_id();
    }
	
	public function add_paypal_device_details($data){
		$this->db->insert('paypal_device_details',$data);
        return $this->db->insert_id();
	}
	
	public function add_work_type($data){
        $this->db->insert('caregiver_work_expertise',$data);
        return $this->db->insert_id();
    }
	
	public function delete($condition){
        $this->db->delete($this->table,$condition);
        return 1;
    }
	
	public function delete_cg_work_types($condition){
		  $this->db->delete('caregiver_work_expertise',$condition);
        return 1;
	}
	
	public function delete_paypal_device_details($condition){
		 $this->db->delete('paypal_device_details',$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_cities(){
		$this->db->select('*');
        $this->db->from('city');        
        $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 get_city_by_id($city_id)
	{
		$this->db->select('*');
        $this->db->from('city');
        $this->db->where('city_id',$city_id);		
        $query=$this->db->get();
        return $query->row_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 getRow($table,$condition){
        $this->db->where($condition);
        $query=$this->db->get($table);
        return $query->row_array();
    }  
	
	public function get_company_detail($condition){
		$this->db->select('*');
        $this->db->from('companys');
		$this->db->join('company_type', 'companys.company_type_id = company_type.company_type_id', 'left');
        $this->db->where($condition);
        $query=$this->db->get();
        return $query->row_array();
    }  
	
	public function get_work_type($admin_id){
		$this->db->select('*');
        $this->db->from('caregiver_work_expertise');
		$this->db->join('work_types', 'caregiver_work_expertise.work_type_id = work_types.work_type_id', 'left');
        $this->db->where("care_giver_id",$admin_id);
        $query=$this->db->get();
        return $query->result_array();
	}
	
	public function get_transaction_report($driver_id,$is_paid,$start_date,$end_date)
	{
		$this->db->select('driver_customer_ride.*,users.driver_dc');
        $this->db->from($this->table);	
		$this->db->join('users', 'users.user_id = driver_customer_ride.driver_id', 'left');		
		$this->db->where('is_paid',$is_paid);
		$this->db->where("DATE_FORMAT(date_time,'%Y-%m-%d')>=",$start_date);
		$this->db->where("DATE_FORMAT(date_time,'%Y-%m-%d')<=",$end_date);		
		if($driver_id != '')
		{
			$this->db->where('driver_id', $driver_id);
		}
		
		$query=$this->db->get();
        return $query->result_array();
	}
}