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

class Mshiftcron extends CI_Model {


    public function get_shift_number($roster_driver_id){
        $this->db->select('CONCAT(CASE WHEN rsm.shift_name = "D" THEN "DAY" WHEN rsm.shift_name = "N" THEN "NIGHT" ELSE "HUNGRY" END,"/",date_format(r.day_date,"%d%m%Y"),"/",mc.registration_no,"/",mu.dc_no) as shift_no');
		$this->db->from('roster_driver_maping rdm');
		$this->db->join('roster_shift_maping rsm','rsm.roster_shift_id=rdm.roster_shift_id','left');
		$this->db->join('roster r','r.roster_id=rsm.roster_id','left');
        $this->db->join('master_car mc','mc.car_id=r.car_id','inner');
        $this->db->join('master_user mu','mu.user_id=rdm.driver_id','inner');
        $this->db->join('user_profile up','up.user_id=mu.user_id','inner');
        $this->db->where('rdm.roster_driver_id',$roster_driver_id);
        $query=$this->db->get();
        //print_r($this->db->last_query());die();
		return $query->row_array();

    
    }

    public function get_ongoing_shift_details($driver_id,$week_no){
        
        $query="SELECT r.roster_id,r.week_no,rdm.roster_driver_id,dsd.*,r.car_id,mc.registration_no,mc.levy_applicable,rsm.shift_name,date_format(r.day_date,'%d/%m/%Y') as day_date,r.dayname 
                FROM driver_shift_details dsd
                INNER JOIN roster_driver_maping rdm ON rdm.roster_driver_id=dsd.roster_driver_id
                INNER JOIN roster_shift_maping rsm ON rsm.roster_shift_id=rdm.roster_shift_id
                INNER JOIN roster r ON r.roster_id=rdm.roster_id
                INNER JOIN master_car mc ON mc.car_id=r.car_id
                WHERE rdm.driver_id='".$driver_id."' AND r.week_no = '".$week_no."' AND date_format(r.day_date,'%Y') = '".date('Y')."' ORDER BY dsd.shift_id DESC LIMIT 1";
                //echo $query; die;
        $run_query=$this->db->query($query);
        $result=$run_query->row_array();
        return $result;
    }

    

    public function get_today_shift_details($driver_id){ 
        
        $query='SELECT r.roster_id,r.car_id,r.week_no,rdm.roster_driver_id,rsm.shift_name,date_format(r.day_date,"%d/%m/%Y") as day_date,r.dayname,mc.registration_no
		from roster_driver_maping rdm
		left join roster_shift_maping rsm on rsm.roster_shift_id=rdm.roster_shift_id
		left join roster r on r.roster_id=rsm.roster_id
		inner join master_car mc on mc.car_id=r.car_id
		where rdm.driver_id ='.$driver_id.'  
        AND r.day_date = "'.date('Y-m-d').'"
        
        AND NOT EXISTS (SELECT 1 FROM driver_shift_payin dsp WHERE dsp.roster_driver_id=rdm.roster_driver_id) ORDER BY rsm.shift_name ASC , rdm.roster_driver_id ASC LIMIT 1';
        //echo $query;die;
        $run_query=$this->db->query($query);
        //print_r($this->db->last_query());die();
		return $run_query->row_array(); 

    }
    
    public function get_today_double_shift($car_id,$day_date){  
        
        $query='SELECT DISTINCT rdm.driver_id as driver_id 
		from roster_driver_maping rdm
		left join roster_shift_maping rsm on rsm.roster_shift_id=rdm.roster_shift_id
		left join roster r on r.roster_id=rsm.roster_id
		inner join master_car mc on mc.car_id=r.car_id
		where r.car_id ='.$car_id.' 
        AND r.day_date = STR_TO_DATE("'.$day_date.'","%d/%m/%Y")';
        // $query;die;
        $run_query=$this->db->query($query);
        //print_r($this->db->last_query());die();
		return $run_query->result_array(); 

    }
    
    

}