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

class Mroster extends CI_Model {


	/*
	author: soumya hazra
	purpose: Add roster main table
	date: 24-9-2019
	*/
	public function insert_roster($data)
	{
		
		$this->db->insert('roster', $data);
		$insert_id = $this->db->insert_id();

   		return  $insert_id;
	}
	
	/*
	author: soumya hazra
	purpose: Add roster main table
	date: 24-9-2019
	*/
	public function delete_roster($week_no)
	{
		
		$this->db->where('week_no', $week_no);
		$this->db->delete('roster');

	}



	public function get_all_wk()
	{
		$result =  array();
		$return =  array();

		$this->db->select('week_name');
		$this->db->from('master_week');											
		$this->db->order_by('week_id','ASC');										
		$query = $this->db->get();
		$result = $query->result_array();

		return $result;
	}



	/*
	author: soumya hazra
	purpose: Add roster shift table
	date: 24-9-2019
	*/
	public function insert_shift($shift_data)
	{
		$this->db->insert('roster_shift_maping', $shift_data);
		$insert_id = $this->db->insert_id();

		//echo $insert_id;
   		return  $insert_id;
	}

	/*
	author: soumya hazra
	purpose: Add roster Driver table
	date: 24-9-2019
	*/
	public function insert_driver($driver_data){
		$this->db->insert('roster_driver_maping', $driver_data);
		$insert_id = $this->db->insert_id();

   		return  $insert_id;
	}

	/*
	author: soumya hazra
	purpose: Get roster Details
	date: 24-9-2019
	*/
	public function get_roster_details($week_no,$car_no=null)
	{
		$result =  array();
		$return =  array();

		$this->db->select('roster.car_id,master_car.registration_no');
		$this->db->from('roster');
		$this->db->join('master_car','master_car.car_id = roster.car_id');
		$this->db->where('roster.week_no',$week_no);
		if($car_no){
			$this->db->where('roster.car_id',$car_no);
		}
		else{										
			$this->db->group_by('roster.car_id');										
			$this->db->order_by('master_car.registration_no','ASC');
		}										
		$query = $this->db->get();
		$resultm = $query->result_array();


		
		

		foreach ($resultm as $car) {
			//echo $car['car_id'];

			$this->db->select('roster.*,master_car.car_id,master_car.registration_no');
			$this->db->from('roster');
			$this->db->join('master_car','master_car.car_id = roster.car_id');
			$this->db->where('roster.week_no',$week_no);										
			$this->db->where('roster.car_id',$car['car_id']);																						
			$this->db->order_by('master_car.registration_no','ASC');
			$this->db->order_by('roster.day_date','ASC');
			
			$query = $this->db->get();
			$result[$car['car_id']] = $query->result_array();
			
			// echo "<pre>";
			// print_r($result['car_id']);
			// die();
			$i = 0;
			foreach ($result[$car['car_id']] as $row) {
				
				$return[$car['car_id']][$i]['roster'] = $row;

					$this->db->select('*');
					$this->db->from('roster_shift_maping');
					$this->db->where('roster_id',$row['roster_id']);										
					$query = $this->db->get();
					$return[$car['car_id']][$i]['roster']['shift'] = $query->result_array();
					$j = 0;
					foreach ($return[$car['car_id']][$i]['roster']['shift'] as $roshift) {
						
						$this->db->select('rdm.*,ifnull(mn.network_name,"") network_name,CONCAT(up.first_name," ",ifnull(up.middle_name,"")," ",up.last_name) as full_name,mu.dc_no,up.mobile');
						$this->db->from('roster_driver_maping rdm');
						$this->db->join('roster r','r.roster_id = rdm.roster_id','inner');
						$this->db->join('master_car mc','mc.car_id = r.car_id');
						$this->db->join('master_network mn','mn.network_id = mc.network_id','left');
						$this->db->join('master_user mu','mu.user_id=rdm.driver_id','left');
						$this->db->join('user_profile up','up.user_id=rdm.driver_id','left');
						$this->db->where('roster_shift_id',$roshift['roster_shift_id']);										
						$query = $this->db->get();
						$return[$car['car_id']][$i]['roster']['shift'][$j]['driver'] = $query->row_array();
					$j++;
					}

					$i++;
			}
		}
		//  echo "<pre>";
		//  print_r($return);
		// // echo "</pre>";
		//  die();
		return $return;
		
	}




	/*
	author: soumya hazra
	purpose: Get roster Details
	date: 25-9-2019
	*/
	public function get_added_car($week_no)
	{

		$result =  array();
		$return =  array();

		$this->db->select('car_id');
		$this->db->from('roster');
		$this->db->where('week_no',$week_no);										
		$this->db->group_by('car_id');										
		$this->db->order_by('car_id','DESC');										
		$query = $this->db->get();
		$result= $query->result_array();

		return $result;
	}



	/*
	author: soumya hazra
	purpose: get Driver details
	date: 27-9-2019
	*/
	public function get_driver_data($user_id){
        $result=array();
        $query = "select mu.*,up.*, CONCAT(up.first_name,' ',ifnull(up.middle_name,''),' ',up.last_name) as full_name, CONCAT('Street:',up.flat_no,' ',up.street_no,' ',up.street_name,' Suburb:',up.suburb,' State:',up.state,' Pin:',up.pin) as address,date_format(up.dr_licence_expiry,'%d/%m/%Y') dr_licence_expiry,date_format(up.dr_dc_expiry,'%d/%m/%Y') dr_dc_expiry ,up.mobile,master_commission.commission_name from master_user mu
        inner join user_profile up  on mu.user_id=up.user_id left join master_commission on master_commission.commission_id = up.dr_commission_id
        where mu.user_id='".$user_id."'";
       	$query1 = $this->db->query($query);
       	$result=$query1->row_array();
        return $result;
    }


    /*
	author: soumya hazra
	purpose: search by date
	date: 27-9-2019
	*/
    public function get_roster_by_date($day_date)
    {
    	$result =  array();
		$return =  array();
		if($day_date){
			$day_date = date('Y-m-d', strtotime(str_replace('/', '-', $day_date)));
		}
		$this->db->select('roster.*,master_car.registration_no');
		$this->db->from('roster');
		$this->db->join('master_car','master_car.car_id = roster.car_id');
		$this->db->where('day_date',$day_date);										
		$this->db->order_by('master_car.registration_no','ASC');										
		$query = $this->db->get();
		$result = $query->result_array();
		$i = 0;
		foreach ($result as $row) {
			$return[$i] = $row;
			$this->db->select('*');
			$this->db->from('roster_shift_maping');
			$this->db->join('roster_driver_maping','roster_driver_maping.roster_shift_id = roster_shift_maping.roster_shift_id');
			$this->db->where('roster_shift_maping.roster_id',$row['roster_id']);	
			$query = $this->db->get();
			$return[$i]['shift'] = $query->result_array();

			$i++;
		}

		// echo "<pre>";
		// print_r($return);
		// echo "</pre>";
		// die();

		return $return;

    } 






     /*
	author: soumya hazra
	purpose: days used
	date: 27-9-2019
	*/
    public function days_used()
    {
    	$result =  array();
		$return =  array();

		$this->db->select('day_date');
		$this->db->from('roster');
		$this->db->group_by('day_date');			
		$query = $this->db->get();
		$result = $query->result_array();

		return $result;

    }

    /*
	author: soumya hazra
	purpose: delete day roster
	date: 27-9-2019
	*/
    public function delete_day_roster($roster_id)
    {
    	$this->db->where('roster_id', $roster_id);
		$this->db->delete('roster');

		$this->db->where('roster_id', $roster_id);
		$this->db->delete('roster_driver_maping');

		$this->db->where('roster_id', $roster_id);
		$this->db->delete('roster_shift_maping');
    }

    /*
	author: soumya hazra
	purpose: Update by date
	date: 27-9-2019
	*/
    public function update_day_roster($dayname,$week_no,$car_id,$day_date)
    {
    	;
    	$data = array(
    		'dayname' => $dayname,
    		'day_date' => $day_date,
    		'car_id' => $car_id,
			'week_no' => $week_no,
			'created_by'=>$this->session->userdata('user_data'),
    		'created_ts' => date('Y-m-d H:i:s'),
    	);

    	$this->db->insert('roster', $data);
		$roster_insert_id = $this->db->insert_id();	   		
		return $roster_insert_id;
    }

    /*
	author: soumya hazra
	purpose: Update by date Shift
	date: 27-9-2019
	*/
    public function update_day_roster_shift($roster_id,$shift_name)
    {
    	$data = array(
    		'roster_id' => $roster_id,
    		'shift_name' => $shift_name,
    		'created_ts' => date('Y-m-d H:i:s'),
    	);
    	$this->db->insert('roster_shift_maping', $data);
		$roster_shift_insert_id = $this->db->insert_id();	   		
		return $roster_shift_insert_id;
    }


    // /*
	// author: soumya hazra
	// purpose: Update by date Shift
	// date: 27-9-2019
	// */
    // public function day_wise_shift_update()
    // {

    // 	$this->db->set('shift_name', $shift_name);		
	// 	$this->db->set('roster_id', $roster_insert_id);		
	// 	$this->db->set('created_ts', date('Y-m-d H:i:s'));		
	// 	$this->db->insert('roster_shift_maping');
	// 	$shift_insert_id = $this->db->insert_id();


    // 	$this->db->set('roster_shift_id', $shift_insert_id);		
	// 	$this->db->set('driver_id', $user_id);		
	// 	$this->db->set('roster_id', $roster_insert_id);		
	// 	$this->db->set('created_ts', date('Y-m-d H:i:s'));		
	// 	$this->db->insert('roster_driver_maping');
	// 	$driver_insert_id = $this->db->insert_id();
	// }
	
	public function check_already_shift_started($roster_shift_id){

		$query="SELECT COUNT(*) as cnt 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
				WHERE rsm.roster_shift_id = '".$roster_shift_id."'";
		$run_query = $this->db->query($query);
		$result=$run_query->row_array();
	 	return $result;

	}


}