File: //var/www/html/taxicamera/pmw_live_testing/application/models/Mapi.php
<?php
class Mapi extends CI_Model {
function __construct(){
parent::__construct();
}
public function insert($table,$data){
$this->db->insert($table,$data);
return $this->db->insert_id();
}
public function batch_insert($table,$data){
$this->db->insert_batch($table,$data);
return 1;
}
public function getDetails($table,$condition){
$this->db->where($condition);
$query=$this->db->get($table);
return $query->result_array();
}
public function get_details($product_id){
$this->db->select('*');
$this->db->from('product');
$this->db->where('product_id',$product_id);
$query=$this->db->get();
return $query->row_array();
}
public function get_all_rates(){
$this->db->select('*');
$this->db->from('rate');
$this->db->where('rate_id',1);
$query=$this->db->get();
return $query->row_array();
}
public function getRow($table,$condition){
$this->db->where($condition);
$query=$this->db->get($table);
return $query->row_array();
}
public function getCount($table,$condition){
$this->db->where($condition);
$query=$this->db->get($table);
return $query->num_rows();
}
public function checkUser($condition){
$this->db->select('users.*,case when users.profile_image ="" then concat("'.base_url("uploads/no_image.png").'") else concat("'.base_url("uploads/user/").'",users.profile_image) end as file_path,islands.island_id,islands.islands_name,currency.currency_id,currency.currency_title');
$this->db->join('islands', 'islands.island_id = users.country', 'inner');
$this->db->join('currency', 'currency.currency_id = islands.currency_id', 'inner');
$this->db->where($condition);
$query=$this->db->get('users');
return $query->row_array();
}
public function checkUserRegistered($condition){
$this->db->select('mu.*');
$this->db->where($condition);
$query=$this->db->get('master_user mu');
return $query->row_array();
}
public function update($table,$condition,$data){
$this->db->where($condition);
$this->db->update($table,$data);
//echo $this->db->last_query(); die();
return $this->db->affected_rows();
}
public function getRows($table,$condition,$order_col=null,$order_type=null){
$this->db->where($condition);
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 delete($table,$condition){
$this->db->where($condition);
$this->db->delete($table);
return true;
}
public function joinQuery($data,$condition = null,$return_type){
if(array_key_exists('select',$data) && $data['select'] != ""){
$this->db->select($data['select']);
}else{
$this->db->select('*');
}
$this->db->from($data['first_table']);
if(array_key_exists('second_table',$data) && array_key_exists('dependency1',$data) && array_key_exists('join_type1',$data)){
if($data['second_table'] != "" && $data['dependency1'] != "" && $data['join_type1'] != ""){
$this->db->join($data['second_table'],$data['dependency1'],$data['join_type1']);
}
}
if(array_key_exists('third_table',$data) && array_key_exists('dependency2',$data) && array_key_exists('join_type2',$data)){
if($data['third_table'] != "" && $data['dependency2'] != "" && $data['join_type2'] != ""){
$this->db->join($data['third_table'],$data['dependency2'],$data['join_type2']);
}
}
if(array_key_exists('forth_table',$data) && array_key_exists('dependency3',$data) && array_key_exists('join_type3',$data)){
if($data['forth_table'] != "" && $data['dependency3'] != "" && $data['join_type3'] != ""){
$this->db->join($data['forth_table'],$data['dependency3'],$data['join_type3']);
}
}
if(array_key_exists('fifth_table',$data) && array_key_exists('dependency4',$data) && array_key_exists('join_type4',$data)){
if($data['fifth_table'] != "" && $data['dependency4'] != "" && $data['join_type4'] != ""){
$this->db->join($data['fifth_table'],$data['dependency4'],$data['join_type4']);
}
}
$this->db->where($condition);
$query = $this->db->get();
//echo $this->db->last_query(); die();
if($query->num_rows() > 0){
if($return_type == 'result'){
return $query->result_array();
}elseif($return_type == 'row'){
return $query->row_array();
}
}else{
return false;
}
}
public function getTokenDetailsByDeviceToken($ap){
$condition=array('token_owner'=>$ap['device_token']);
$this->db->where($condition);
$this->db->order_by('token_id','desc');
$this->db->limit(1);
$query=$this->db->get('api_token');
//print_r($this->db->last_query());die();
return $query->row_array();
}
public function getTokenDetailsByTokenkey($token_key){
$condition=array('token_key'=>$token_key);
$this->db->where($condition);
$this->db->order_by('token_id','desc');
$this->db->limit(1);
$query=$this->db->get('api_token');
//print_r($this->db->last_query());die();
return $query->row_array();
}
public function getDeviceToken($table,$ids,$select){
$this->db->select($select);
$this->db->where_in('admin_id',$ids);
$query=$this->db->get($table);
//print_r($this->db->last_query());die();
return $query->result_array();
}
public function email_check($email,$user_id){
$this->db->select('*');
$this->db->from('users');
$this->db->where('email',$email);
$this->db->where_not_in('user_id', $user_id);
$query=$this->db->get();
return $query->row_array();
}
public function get_payin_list($driver_id){
$this->db->select('p.payin_id as transaction_id,p.shift_id,dsd.shift_no,date_format(p.created_ts,"%d/%m/%Y") as created_ts,ifnull(p.settlement_amt,p.total_payin_payout) as total_payin_payout,mc.registration_no as car_no');
$this->db->from('payin p');
$this->db->join('driver_shift_details dsd','dsd.shift_id=p.shift_id','inner');
$this->db->join('roster_driver_maping rdm','rdm.roster_driver_id=dsd.roster_driver_id','inner');
$this->db->join('roster r','r.roster_id=rdm.roster_id','inner');
$this->db->join('master_car mc','mc.car_id=r.car_id','inner');
$this->db->where('p.driver_id',$driver_id);
$this->db->group_by('p.payin_id');
$this->db->order_by('p.payin_id','DESC');
$query=$this->db->get();
//print_r($this->db->last_query());die();
return $query->result_array();
}
public function get_payin_details($payin_id){
$driver_settlement_data = $this->mcommon->getRow('driver_settlement',array('payin_id'=>$payin_id));
if(empty($driver_settlement_data)){
$this->db->select('p.payin_id as transaction_id,p.shift_id,dsd.shift_no,date_format(p.created_ts,"%d/%m/%Y") as created_ts,p.total_payin_payout,
,p.km_traveled,p.metered_fares,p.bond_charges,p.subtotal,p.levy,p.no_of_whl,TRUNCATE(p.no_of_hiring_start,0) as no_of_hiring_start,TRUNCATE(p.no_of_hiring_end,0) as no_of_hiring_end,p.total_extra_start, p.total_extra_end,TRUNCATE(p.paid_km_start,0) as paid_km_start,TRUNCATE(p.paid_km_end,0) as paid_km_end, TRUNCATE(p.total_km_start,0) as total_km_start,TRUNCATE(p.total_km_end,0) as total_km_end,TRUNCATE(p.extra_start,0) as extra_start,TRUNCATE(p.extra_end,0) as extra_end,TRUNCATE(p.speedo_reading_start,0) as speedo_reading_start,TRUNCATE(p.speedo_reading_end,0) as speedo_reading_end,
up.dr_driver_type as driver_type,mc.registration_no as car_no,CASE WHEN p.payin_reference_doc IS NOT NULL THEN CONCAT("/public/upload_image/payin_images/",p.payin_reference_doc) ELSE "" END as payin_reference_doc,
CASE WHEN p.payin_lifetime_total IS NOT NULL THEN CONCAT("/public/upload_image/payin_images/",p.payin_lifetime_total) ELSE "" END as payin_lifetime_total,
(SELECT ifnull(SUM(pd.value),"0") FROM payin_dockets pd WHERE pd.payin_id= "'.$payin_id.'") as docket_total_value,
(SELECT ifnull(SUM(pe.value),"0")FROM payin_expenses pe WHERE pe.payin_id= "'.$payin_id.'") as expense_total_value,
(SELECT gtn_share * p.no_of_whl FROM master_lifting_fee ORDER BY lifting_fee_id DESC LIMIT 1) as total_lifting_fees');
$this->db->from('payin p');
$this->db->join('driver_shift_details dsd','dsd.shift_id=p.shift_id','inner');
$this->db->join('roster_driver_maping rdm','rdm.roster_driver_id=dsd.roster_driver_id','inner');
$this->db->join('user_profile up','up.user_id=rdm.driver_id','inner');
$this->db->join('roster r','r.roster_id=rdm.roster_id','inner');
$this->db->join('master_car mc','mc.car_id=r.car_id','inner');
$this->db->where('p.payin_id',$payin_id);
$query=$this->db->get();
//print_r($this->db->last_query());die();
$result = $query->row_array();
} else {
$this->db->select('p.payin_id as transaction_id,p.shift_id,dsd.shift_no,date_format(p.created_on,"%d/%m/%Y") as created_ts,p.amount_due as total_payin_payout,
,(p.speedo_reading_end - p.speedo_reading_start) as km_traveled,(p.total_extra_end - p.total_extra_start) as metered_fares,p.bond_installment,p.shift_levy as levy,payin.no_of_whl,TRUNCATE(p.no_of_hiring_start,0) as no_of_hiring_start,TRUNCATE(p.no_of_hiring_end,0) as no_of_hiring_end,p.total_extra_start, p.total_extra_end,TRUNCATE(p.paid_km_start,0) as paid_km_start,TRUNCATE(p.paid_km_end,0) as paid_km_end, TRUNCATE(p.total_km_start,0) as total_km_start,TRUNCATE(p.total_km_end,0) as total_km_end,TRUNCATE(p.extra_start,0) as extra_start,TRUNCATE(p.extra_end,0) as extra_end,TRUNCATE(p.speedo_reading_start,0) as speedo_reading_start,TRUNCATE(p.speedo_reading_end,0) as speedo_reading_end,
up.dr_driver_type as driver_type,mc.registration_no as car_no,CASE WHEN payin.payin_reference_doc IS NOT NULL THEN CONCAT("/public/upload_image/payin_images/",payin.payin_reference_doc) ELSE "" END as payin_reference_doc,
CASE WHEN payin.payin_lifetime_total IS NOT NULL THEN CONCAT("/public/upload_image/payin_images/",payin.payin_lifetime_total) ELSE "" END as payin_lifetime_total,
(SELECT ifnull(SUM(pd.docket_settlement_amt),"0") FROM dr_settlement_docket pd WHERE pd.dr_settlement_id= "'.$driver_settlement_data['dr_settlement_id'].'") as docket_total_value,
(SELECT ifnull(SUM(pe.expense_settlement_amt),"0")FROM dr_settlement_expense pe WHERE pe.dr_settlement_id= "'.$driver_settlement_data['dr_settlement_id'].'") as expense_total_value,
(SELECT gtn_share * payin.no_of_whl FROM master_lifting_fee ORDER BY lifting_fee_id DESC LIMIT 1) as total_lifting_fees');
$this->db->from('driver_settlement p');
$this->db->join('payin','payin.payin_id=p.payin_id','inner');
$this->db->join('driver_shift_details dsd','dsd.shift_id=p.shift_id','inner');
$this->db->join('roster_driver_maping rdm','rdm.roster_driver_id=dsd.roster_driver_id','inner');
$this->db->join('user_profile up','up.user_id=rdm.driver_id','inner');
$this->db->join('roster r','r.roster_id=rdm.roster_id','inner');
$this->db->join('master_car mc','mc.car_id=r.car_id','inner');
$this->db->where('p.payin_id',$payin_id);
$query=$this->db->get();
$row = $query->row_array();
$result = array();
$result = $row;
$result ['subtotal'] = number_format(($result['total_payin_payout'] + $result['expense_total_value']),2,'.','');
}
return $result;
}
public function get_payin_docket_details($payin_id){
$driver_settlement_data = $this->mcommon->getRow('driver_settlement',array('payin_id'=>$payin_id));
if(empty($driver_settlement_data)){
$this->db->select('pd.value,CASE WHEN pd.doc_link IS NOT NULL THEN CONCAT("/public/upload_image/payin_images/",pd.doc_link) ELSE "" END as doc_link,md.docket_name');
$this->db->from('payin_dockets pd');
$this->db->join('master_docket md','md.docket_id=pd.docket_id','inner');
$this->db->where('pd.payin_id',$payin_id);
} else {
$this->db->select('dsd.docket_settlement_amt as value,CASE WHEN pd.doc_link IS NOT NULL THEN CONCAT("/public/upload_image/payin_images/",pd.doc_link) ELSE "" END as doc_link,md.docket_name');
$this->db->from('dr_settlement_docket dsd');
$this->db->join('payin_dockets pd','pd.payin_dockets_id = dsd.payin_docket_id','inner');
$this->db->join('master_docket md','md.docket_id=pd.docket_id','inner');
$this->db->where('dsd.dr_settlement_id',$driver_settlement_data['dr_settlement_id']);
}
$query=$this->db->get();
return $query->result_array();
}
public function get_payin_expense_details($payin_id){
$driver_settlement_data = $this->mcommon->getRow('driver_settlement',array('payin_id'=>$payin_id));
if(empty($driver_settlement_data)){
$this->db->select('pe.value,CASE WHEN pe.doc_link IS NOT NULL THEN CONCAT("/public/upload_image/payin_images/",pe.doc_link) ELSE "" END as doc_link,me.expense_name');
$this->db->from('payin_expenses pe');
$this->db->join('master_expense me','me.expense_id=pe.expense_id','inner');
$this->db->where('pe.payin_id',$payin_id);
} else {
$this->db->select('dse.expense_settlement_amt as value,CASE WHEN pe.doc_link IS NOT NULL THEN CONCAT("/public/upload_image/payin_images/",pe.doc_link) ELSE "" END as doc_link,me.expense_name');
$this->db->from('dr_settlement_expense dse');
$this->db->join('payin_expenses pe','pe.payin_expense_id = dse.payin_expense_id','inner');
$this->db->join('master_expense me','me.expense_id=pe.expense_id','inner');
$this->db->where('dse.dr_settlement_id',$driver_settlement_data['dr_settlement_id']);
}
$query=$this->db->get();
return $query->result_array();
}
public function get_roster_list($driver_id){
$this->db->select('r.roster_id,CASE WHEN rsm.shift_name = "D" THEN "DAY (4am to 4pm)" WHEN rsm.shift_name = "N" THEN "NIGHT (4pm to 4am)" ELSE "HUNGRY (4am to 4am)" END as shift_name,date_format(r.day_date,"%d/%m/%Y") as day_date,r.dayname,mc.registration_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->where('rdm.driver_id',$driver_id);
$this->db->where('r.day_date >=',date('Y-m-d'));
$this->db->order_by('r.day_date','ASC');
$query=$this->db->get();
//print_r($this->db->last_query());die();
return $query->result_array();
}
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,r.week_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_levy_report($driver_id,$start_date,$end_date){
$this->db->select('p.payin_id,dsd.shift_no,date_format(p.created_ts,"%d/%m/%Y") as payin_date,(p.no_of_hiring_end - p.no_of_hiring_start) as no_of_run,ifnull(p.levy,"0.00") as levy');
$this->db->from('payin p');
$this->db->join('driver_shift_details dsd','dsd.shift_id=p.shift_id','inner');
$this->db->where('p.driver_id',$driver_id);
$this->db->where('p.levy >',0);
if(!empty($start_date) && !empty($end_date)){
$this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") >=',$start_date);
$this->db->where('str_to_date(date_format(p.created_ts,"%d/%m/%Y"),"%d/%m/%Y") <=',$end_date);
}
$this->db->order_by('p.payin_id','DESC');
$query=$this->db->get();
//print_r($this->db->last_query());die();
return $query->result_array();
}
public function check_inspection_status($car_id){
$query="SELECT CASE WHEN DATEDIFF(NOW(),created_ts) > 7 THEN 0 ELSE 1 END as inspection_status FROM inspection WHERE car_id='".$car_id."' ORDER BY inspection_id DESC LIMIT 1";
//echo $query;die;
$run_query=$this->db->query($query);
$result=$run_query->row_array();
return $result;
}
public function get_previous_payin_data($car_id){
$query="SELECT p.payin_id,CASE WHEN ds.total_extra_end IS NOT NULL THEN ds.total_extra_end ELSE p.total_extra_end END as total_extra_end,
CASE WHEN ds.paid_km_end IS NOT NULL THEN TRUNCATE(ds.paid_km_end,0) ELSE TRUNCATE(p.paid_km_end,0) END as paid_km_end,
CASE WHEN ds.total_km_end IS NOT NULL THEN TRUNCATE(ds.total_km_end,0) ELSE TRUNCATE(p.total_km_end,0) END as total_km_end,
CASE WHEN ds.no_of_hiring_end IS NOT NULL THEN TRUNCATE(ds.no_of_hiring_end,0) ELSE TRUNCATE(p.no_of_hiring_end,0) END as no_of_hiring_end,
CASE WHEN ds.extra_end IS NOT NULL THEN TRUNCATE(ds.extra_end,0) ELSE TRUNCATE(p.extra_end,0) END as extra_end,
CASE WHEN ds.speedo_reading_end IS NOT NULL THEN TRUNCATE(ds.speedo_reading_end,0) ELSE TRUNCATE(p.speedo_reading_end,0) END as speedo_reading_end
FROM payin p
LEFT JOIN driver_settlement ds ON ds.payin_id=p.payin_id
INNER JOIN driver_shift_details dsd ON dsd.shift_id=p.shift_id
INNER JOIN roster_driver_maping rdm ON rdm.roster_driver_id=dsd.roster_driver_id
INNER JOIN roster r ON r.roster_id=rdm.roster_id
WHERE r.car_id='".$car_id."' ORDER BY CASE WHEN ds.payin_id IS NOT NULL THEN ds.payin_id ELSE p.payin_id END DESC LIMIT 1";
//echo $query;die;
$run_query=$this->db->query($query);
$result=$run_query->row_array();
return $result;
}
public function get_ongoing_shift_details($driver_id){
$query="SELECT r.roster_id,rdm.roster_driver_id,dsd.*,r.car_id,mc.registration_no,mc.levy_applicable,CASE WHEN rsm.shift_name = 'D' THEN 'DAY (4am to 4pm)' WHEN rsm.shift_name = 'N' THEN 'NIGHT (4pm to 4am)' ELSE 'HUNGRY (4am to 4am)' END as 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 dsd.shift_status = 1 ORDER BY dsd.shift_id ASC 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,rdm.roster_driver_id,CASE WHEN rsm.shift_name = "D" THEN "DAY (4am to 4pm)" WHEN rsm.shift_name = "N" THEN "NIGHT (4pm to 4am)" ELSE "HUNGRY (4am to 4am)" END as 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 check_today_shift_availibility_after_end_shift($driver_id){
$query='SELECT r.roster_id,r.car_id,rdm.roster_driver_id,CASE WHEN rsm.shift_name = "D" THEN "DAY (4am to 4pm)" WHEN rsm.shift_name = "N" THEN "NIGHT (4pm to 4am)" ELSE "HUNGRY (4am to 4am)" END as 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 r.car_id = (SELECT r1.car_id FROM roster r1 INNER JOIN roster_driver_maping rdm1 ON rdm1.roster_id = r1.roster_id WHERE r1.day_date = "'.date('Y-m-d', strtotime(date('Y-m-d') .' -1 day')).'" AND rdm1.driver_id ="'.$driver_id.'" )
AND NOT EXISTS (SELECT 1 FROM driver_shift_details dsd WHERE dsd.roster_driver_id=rdm.roster_driver_id)';
// echo $query;die;
$run_query=$this->db->query($query);
//print_r($this->db->last_query());die();
return $run_query->row_array();
}
public function insert_driver_shift_payin($start_time,$end_time,$driver_id,$shift_id){
// echo $start_time.'$'.$end_time;
/* $chk_qry='SELECT MAX(rdm.roster_driver_id),rsm.shift_name
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
where rdm.driver_id ="'.$driver_id.'" AND rdm.roster_driver_id <> (SELECT MAX(rdm1.roster_driver_id) FROM roster_driver_maping rdm1 left join roster_shift_maping rsm1 on rsm1.roster_shift_id=rdm1.roster_shift_id
left join roster r1 on r1.roster_id=rsm1.roster_id) ORDER BY r.roster_id DESC LIMIT 1';*/
// $chk_qry ='SELECT rsm.shift_name
// 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
// where rdm.driver_id ="'.$driver_id.'" AND NOT EXISTS (SELECT 1 FROM driver_shift_details dsd WHERE dsd.roster_driver_id=rdm.roster_driver_id AND dsd.shift_status=2) ORDER BY r.roster_id DESC LIMIT 1';
// //echo $chk_qry;die;
// $run_chk_qry=$this->db->query($chk_qry);
// $result_chk_qry=$run_chk_qry->row_array();
// if($result_chk_qry){
// if($result_chk_qry['shift_name'] == 'H'){
// $check_end_time = $end_time;
// }else{
// $check_end_time = date('Y-m-d', strtotime($end_time));
// }
// }
$start_date_time = new DateTime($start_time);
$start_time_week = $start_date_time->format("W");
$end_date_time = new DateTime($end_time);
$end_time_week = $end_date_time->format("W");
//echo $start_time_week .'#'. $end_time_week;
$start_time_next_sunday = date('Y-m-d', strtotime('next sunday'.$start_time));
//$start_time_next_thursday = date('Y-m-d', strtotime('next thursday '.$start_time));
if($end_time_week >= ($start_time_week+1)){
if($end_time != $start_time_next_sunday){
$end_time = $start_time_next_sunday;
}
}
$get_car_id_query='SELECT r.roster_id,r.car_id,rdm.roster_driver_id
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=rsm.roster_id
where dsd.shift_id ='.$shift_id.' ORDER BY rdm.roster_driver_id DESC LIMIT 1';
//echo $query;die;
$get_car_id_run_query=$this->db->query($get_car_id_query);
//print_r($this->db->last_query());die();
$get_car_id_result = $get_car_id_run_query->row_array();
$query='INSERT INTO driver_shift_payin(shift_id,roster_driver_id,created_by,created_ts)
SELECT "'.$shift_id.'",rdm.roster_driver_id, "'.$driver_id.'","'.date("Y-m-d H:i:s").'"
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.car_id = "'.$get_car_id_result['car_id'].'" AND r.breakdown <> 1
AND r.day_date BETWEEN str_to_date(date_format("'.$start_time.'","%Y-%m-%d"),"%Y-%m-%d") AND
str_to_date("'.$end_time.'","%Y-%m-%d")
AND NOT EXISTS (SELECT 1 FROM driver_shift_details dsd WHERE dsd.roster_driver_id=rdm.roster_driver_id AND dsd.shift_status=2)';
//echo $query;die;
$run_query=$this->db->query($query);
return true;
}
public function check_service_notification($shift_id,$payin_id){
$query="SELECT check_service_notification('".$shift_id."','".$payin_id."') as notification_flag";
$run_query=$this->db->query($query);
$result=$run_query->row_array();
return $result;
}
public function get_cms_page_list(){
$base_url=base_url('cms/');
$query="SELECT cms_id, page_name, CONCAT('".$base_url."',cms_slug) as cms_link FROM cms WHERE status=1 ORDER BY cms_id ASC LIMIT 3";
//echo $query;die;
$run_query=$this->db->query($query);
$result=$run_query->result_array();
return $result;
}
public function get_total_payout_amount($driver_id){
$query="SELECT SUM(final_due_amt) as tot_final_settlement_amt FROM payin WHERE driver_id='".$driver_id."'";
$run_query=$this->db->query($query);
$result=$run_query->row_array();
return $result;
}
public function get_bond_accumulated_amount($driver_id){
$query="SELECT (ifnull(bond_amt,0) - ifnull(bond_accumulated_amt,0)) as tot_bond_accumulated_amt FROM master_bond WHERE driver_id=".$driver_id."";
$run_query=$this->db->query($query);
$result=$run_query->row_array();
return $result;
}
public function get_driver_shift_details(){
}
public function GetAccidentData($cond_accident_installment){
$result = array();
$total_accident_installment = 0.00;
$this->db->select('master_accident.*,accident_charge.*');
$this->db->from('master_accident');
$this->db->join('accident_charge', 'master_accident.accident_id = accident_charge.accident_id', 'left');
$this->db->where($cond_accident_installment);
$query = $this->db->get();
//echo $this->db->last_query(); die();
$result = $query->result_array();
return $result ;
}
}