File: //var/www/html/taxicamera/pmw_live_testing/application/models/admin/Mroster.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Mroster extends CI_Model {
public function __construct(){
parent::__construct();
// $ddate = date('Y-m-d');
$start_date = new DateTime();
$end_date = new DateTime();
$start_date_week = $start_date->modify('this week +0 days');
$end_date_week = $end_date->modify('this week +6 days');
$start_year = $start_date_week->format('Y');
$end_year = $end_date_week->format('Y');
$start_date = $start_date_week->format('Y-m-d');
$end_date = $end_date_week->format('Y-m-d');
$this->db_condition_date = " BETWEEN '".$start_date."' AND '".$end_date."'";
// echo $start_year;
// echo $end_year;die;
if($start_year != $end_year){
$this->db_condition = " BETWEEN '".$start_year."' AND '".$end_year."'";
} else{
$this->db_condition = "= '".$end_year."'";
}
}
/*
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->where("date_format(roster.day_date,'%Y') ".$this->db_condition." ");
$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;
}
public function get_newly_added_car($week_no,$car_no=null){
$query="SELECT mc.*, 1 as added_car
FROM master_car mc
INNER JOIN master_company mcom ON mcom.company_id = mc.company_id
INNER JOIN master_fuel mf ON mf.fuel_id = mc.fuel_id
INNER JOIN master_car_model mcm ON mcm.car_model_id = mc.car_model_id
INNER JOIN master_network mn ON mn.network_id = mc.network_id
WHERE mc.status = 1 AND NOT EXISTS(SELECT 1 FROM roster r1 WHERE r1.car_id = mc.car_id AND r1.week_no = '".$week_no."'
AND date_format(r1.day_date,'%Y') ".$this->db_condition.")";
if($car_no){
$query .=" AND mc.car_id ='".$car_no."'";
}
else{
$query .=" GROUP BY mc.car_id";
}
$query .=" ORDER BY mc.registration_no ASC";
$run_query = $this->db->query($query);
$result = $run_query->result_array();
return $result;
}
/*
author: soumya hazra
purpose: Get roster Details
date: 24-9-2019
*/
public function get_roster_details($week_no,$car_no=null,$year=null)
{
$result = array();
$return = array();
$query="SELECT roster.car_id,master_car.registration_no, 0 as added_car FROM roster
INNER JOIN master_car ON master_car.car_id = roster.car_id
WHERE roster.week_no = '".$week_no."'";
if($year){
$dto = new DateTime();
$dto->setISODate($year, $week_no);
$week_start = $dto->format('Y');
$date_start = $dto->format('Y-m-d');
$dto->modify('+6 days');
$week_end = $dto->format('Y');
$date_end = $dto->format('Y-m-d');
// echo $week_start;
// echo $week_end;
// die;
if($week_start != $week_end){
//$query .=" AND date_format(roster.day_date,'%Y') BETWEEN '".$date_start."' AND '".$week_end."'";
$query .=" AND roster.day_date BETWEEN '".$date_start."' AND '".$date_end."'";
} else {
$query .=" AND date_format(roster.day_date,'%Y') = '".$year."'";
}
}else{
$query .=" AND (date_format(roster.day_date,'%Y') ".$this->db_condition." )";
}
if($car_no){
$query .=" AND roster.car_id ='".$car_no."'";
}
else{
$query .=" GROUP BY roster.car_id";
}
$query .=" ORDER BY master_car.registration_no ASC";
//echo $query; die;
$run_query = $this->db->query($query);
$resultm = $run_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);
if($year){
$dto = new DateTime();
$dto->setISODate($year, $week_no);
$week_start = $dto->format('Y');
$date_start = $dto->format('Y-m-d');
$dto->modify('+6 days');
$week_end = $dto->format('Y');
$date_end = $dto->format('Y-m-d');
if($week_start != $week_end){
//$this->db->where('date_format(roster.day_date,"%Y") BETWEEN "'.$week_start.'" AND "'.$week_end.'"');
$this->db->where('roster.day_date BETWEEN "'.$date_start.'" AND "'.$date_end.'"');
} else {
$this->db->where("date_format(roster.day_date,'%Y') = '".$year."'");
}
}else{
$this->db->where('date_format(roster.day_date,"%Y") '.$this->db_condition.' ');
}
$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);
// die();
return $return;
}
public function get_roster_details_import($week_no,$car_no=null,$year=null)
{
$result = array();
$return = array();
$query="SELECT roster.car_id,master_car.registration_no, 0 as added_car FROM roster
INNER JOIN master_car ON master_car.car_id = roster.car_id
WHERE master_car.status=1 AND roster.week_no = '".$week_no."'";
if($year){
$dto = new DateTime();
$dto->setISODate($year, $week_no);
$week_start = $dto->format('Y');
$dto->modify('+6 days');
$week_end = $dto->format('Y');
if($week_start != $week_end){
$query .=" AND (date_format(roster.day_date,'%Y') BETWEEN '".$week_start."' AND '".$week_end."')";
} else {
$query .=" AND date_format(roster.day_date,'%Y') = '".$year."'";
}
} else {
$query .=" AND (date_format(roster.day_date,'%Y') ".$this->db_condition." )";
}
if($car_no){
$query .=" AND roster.car_id ='".$car_no."'";
}
else{
$query .=" GROUP BY roster.car_id";
}
$query .=" ORDER BY master_car.registration_no ASC";
//echo $query;die;
$run_query = $this->db->query($query);
$resultm = $run_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);
if($week_start != $week_end){
$this->db->where('date_format(roster.day_date,"%Y") BETWEEN "'.$week_start.'" AND "'.$week_end.'"');
} else {
$this->db->where('date_format(roster.day_date,"%Y") '.$this->db_condition.' ');
}
$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);
// 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->where("date_format(roster.day_date,'%Y') ".$this->db_condition." ");
$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->where('master_car.status',1);
$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(dsd.shift_id) 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;
}
public function check_already_exist_roster($day_date){
$query ="SELECT COUNT(r.roster_id) as cnt FROM roster r
INNER JOIN roster_shift_maping rsm ON rsm.roster_id = r.roster_id
INNER JOIN roster_driver_maping rdm ON rdm.roster_id = r.roster_id
WHERE r.day_date = '".$day_date."'";
//echo $query;die;
$run_query = $this->db->query($query);
$result=$run_query->row_array();
return $result;
}
// public function check_already_driver_exists($day_date,$driver_id,$car_id){
// $query ="SELECT COUNT(rdm.roster_driver_id) as cnt,COUNT(dsd.shift_id) as shift_cnt,COUNT(dsd.shift_payin_id) as shift_payin_cnt
// FROM roster r
// INNER JOIN roster_shift_maping rsm ON rsm.roster_id = r.roster_id
// INNER JOIN roster_driver_maping rdm ON rdm.roster_id = r.roster_id
// LEFT JOIN driver_shift_details dsd ON dsd.roster_driver_id = rdm.roster_driver_id
// LEFT JOIN driver_shift_payin dsp ON dsp.roster_driver_id = rdm.roster_driver_id
// WHERE r.day_date = STR_TO_DATE('".$day_date."','%Y-%m-%d') AND rdm.driver_id ='".$driver_id."' AND r.breakdown = 0";//AND r.car_id <> '".$car_id."'
// //echo $query;die;
// $run_query = $this->db->query($query);
// $result=$run_query->row_array();
// return $result;
// }
public function check_already_driver_shift_exists($day_date,$driver_id,$car_id){
$query ="SELECT COUNT(rdm.roster_driver_id) as cnt
FROM roster r
INNER JOIN roster_shift_maping rsm ON rsm.roster_id = r.roster_id
INNER JOIN roster_driver_maping rdm ON rdm.roster_id = r.roster_id
LEFT JOIN driver_shift_details dsd ON dsd.roster_driver_id = rdm.roster_driver_id
LEFT JOIN driver_shift_payin dsp ON dsp.roster_driver_id = rdm.roster_driver_id
WHERE r.day_date = STR_TO_DATE('".$day_date."','%Y-%m-%d') AND rdm.driver_id ='".$driver_id."' AND r.car_id <> '".$car_id."' AND r.breakdown = 0";//AND r.car_id <> '".$car_id."'
//echo $query;die;
$run_query = $this->db->query($query);
$result=$run_query->row_array();
return $result;
}
public function check_already_driver_shift_started($day_date,$driver_id){
$query ="SELECT COUNT(dsd.shift_id) as shift_cnt,COUNT(dsp.shift_payin_id) as shift_payin_cnt
FROM roster r
INNER JOIN roster_shift_maping rsm ON rsm.roster_id = r.roster_id
INNER JOIN roster_driver_maping rdm ON rdm.roster_id = r.roster_id
LEFT JOIN driver_shift_details dsd ON dsd.roster_driver_id = rdm.roster_driver_id
LEFT JOIN driver_shift_payin dsp ON dsp.roster_driver_id = rdm.roster_driver_id
WHERE r.day_date = STR_TO_DATE('".$day_date."','%Y-%m-%d') AND rdm.driver_id ='".$driver_id."' AND r.breakdown = 0";//AND r.car_id <> '".$car_id."'
//echo $query;die;
$run_query = $this->db->query($query);
$result=$run_query->row_array();
return $result;
}
public function delete_roster_main($day_date){
$query ="DELETE FROM roster WHERE day_date ='".$day_date."'";
$run_query = $this->db->query($query);
//$result=$run_query->row_array();
return 1;
}
public function delete_roster_shift_maping($day_date){
$query ="DELETE FROM roster_shift_maping
WHERE roster_id IN (SELECT r1.roster_id FROM roster r1 WHERE r1.day_date = '".$day_date."')";
$run_query = $this->db->query($query);
//$result=$run_query->row_array();
return 1;
}
}