File: //var/www/html/qcr24/app/application/models/api/Mstaff.php
<?php
class Mstaff 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_vehicle(){
$query = $this->db->query('SELECT * FROM master_car WHERE car_type="Car" AND status= "0"');
$total_vehicle['total_car']=$query->num_rows();
$query = $this->db->query('SELECT * FROM master_car WHERE car_type="Van" AND status= "0"');
$total_vehicle['total_van']=$query->num_rows();
return $total_vehicle;
}
public function get_on_rentin_vehicle(){
$query = $this->db->query('SELECT * FROM rent_out_vehcile rov INNER JOIN master_car mc ON mc.car_id=rov.car_id WHERE rov.is_rent_in=0 AND mc.car_type="Car" AND mc.status= "0" GROUP BY rov.car_id');
$total_on_rentin_vehicle['total_car']=$query->num_rows();
$query = $this->db->query('SELECT * FROM rent_out_vehcile rov INNER JOIN master_car mc ON mc.car_id=rov.car_id WHERE rov.is_rent_in=0 AND mc.car_type="Van" AND mc.status= "0" GROUP BY rov.car_id');
$total_on_rentin_vehicle['total_van']=$query->num_rows();
return $total_on_rentin_vehicle;
}
public function get_pending_driver_validate(){
$query = $this->db->query('SELECT md.driver_id,md.first_name,ifnull(md.middle_name,"") as middle_name,md.last_name,md.flat_no,md.street_no,md.street_name,md.suburb,md.pin,md.email,md.mobile,ifnull(date_format(md.dob,"%d/%m/%Y"),"") as dob,md.licence_no,md.licence_image,ifnull(date_format(md.licence_expiry,"%d/%m/%Y"),"") as licence_expiry,md.licence_expiry_image,md.is_australian_licence,md.passport_no,md.passport_no_image,ifnull(date_format(md.passport_expiry,"%d/%m/%Y"),"") as passport_expiry,md.passport_expiry_image,md.utility_bill_id,md.utility_bill_image,md.bank_name,md.bsb,md.account_no,ifnull(md.no_of_at_fault_accidents,0) as no_of_at_fault_accidents,ifnull(md.no_of_not_at_fault_accidents,0) as no_of_not_at_fault_accidents,ifnull(md.profile_photo,"") as profile_photo,ifnull(md.admin_notes,"") as admin_notes,md.status,ifnull(md.created_by,"") as created_by,ifnull(date_format(md.created_ts,"%d/%m/%Y"),"") as created_ts,ifnull(md.updated_by,"") as updated_by,ifnull(date_format(md.updated_ts,"%d/%m/%Y"),"") as updated_ts FROM master_driver md WHERE md.status= "0" AND created_ts > now() - INTERVAL 15 day ORDER BY driver_id DESC');
$pending_driver_validate=$query->result_array();
return $pending_driver_validate;
}
public function get_available_vehicle(){
$query = $this->db->query('SELECT mc.car_id,mc.car_no,mc.make,mc.model,mc.year,mc.fuel_type,mc.service_kilometer,mc.transmission_service,mc.spark_plug_for_eg,date_format(mc.rego_expire_date,"%d/%m/%Y") as rego_expire_date,date_format(mc.insurance_expire_date,"%d/%m/%Y") as insurance_expire_date,mc.insurance_expire_pic,mc.car_pic,mc.is_hybrid,mc.status,mc.company_id,mcm.company_name,mcm.company_code,mc.car_type as vehicle_type,mc.total_odometer_reading
FROM master_car mc
INNER JOIN master_company mcm ON mcm.company_id=mc.company_id
WHERE mc.car_id NOT IN (SELECT rov.car_id FROM rent_out_vehcile rov WHERE rov.is_rent_in = 0)
ORDER BY mc.car_id DESC');
//echo $this->db->last_query();die;
$available_vehicle=$query->result_array();
return $available_vehicle;
}
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('md.*');
$this->db->where($condition);
$query=$this->db->get('master_driver md');
return $query->row_array();
}
public function checkCarRegistered($condition){
$this->db->select('mc.*');
$this->db->where($condition);
$query=$this->db->get('master_car mc');
//echo $this->db->last_query();die;
return $query->row_array();
}
public function checkDriverDetails($condition){
$this->db->select('md.*');
$this->db->where($condition);
$query=$this->db->get('master_driver md');
//echo $this->db->last_query();die;
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_car_list($search_text = null){
$this->db->select('mc.car_id,mc.car_no,mc.make,mc.model,mc.year,mc.fuel_type,mc.service_kilometer,mc.transmission_service,mc.spark_plug_for_eg,date_format(mc.rego_expire_date,"%d/%m/%Y") as rego_expire_date,date_format(mc.insurance_expire_date,"%d/%m/%Y") as insurance_expire_date,mc.insurance_expire_pic, mc.car_pic,mc.is_hybrid,mc.status,mc.company_id,mcm.company_name,mcm.company_code,mc.car_type as vehicle_type,mc.total_odometer_reading,ifnull(rov.car_id,"available") as on_rent');
$this->db->from('master_car mc');
$this->db->join('master_company mcm','mcm.company_id=mc.company_id','inner');
$this->db->join('rent_out_vehcile rov','rov.car_id=mc.car_id AND rov.is_rent_in=0','left');
$this->db->where('mc.status <>','2');
if($search_text){
$this->db->like('mc.car_no', $search_text);
$this->db->or_like('mc.make', $search_text);
$this->db->or_like('mc.model', $search_text);
$this->db->or_like('mc.year', $search_text);
$this->db->or_like('mc.fuel_type', $search_text);
$this->db->or_like('mcm.company_name', $search_text);
$this->db->or_like('mcm.company_code', $search_text);
}
$this->db->group_by('mc.car_id');
$this->db->order_by('mc.car_id','DESC');
$query=$this->db->get();
// print_r($this->db->last_query());die();
return $query->result_array();
}
public function edit_car($car_id){
$this->db->select('*');
$this->db->from('master_car');
$this->db->where('car_id',$car_id);
$query=$this->db->get();
return $query->row_array();
}
public function available_car_cnt($car_id){
$this->db->select('*');
$this->db->from('master_car mc');
$this->db->where(" NOT EXISTS (SELECT rov.car_id FROM rent_out_vehcile rov WHERE rov.car_id = mc.car_id AND rov.is_rent_in='0')", null, false);
$this->db->where('mc.car_id',$car_id);
$query=$this->db->get();
return $query->num_rows();
}
public function update_car($condition,$data){
$result=$this->db->update('master_car', $data, $condition);
return $result;
}
public function get_company_list(){
$this->db->select('mc.company_id,mc.company_name,mc.company_code');
$this->db->from('master_company mc');
$this->db->where('mc.status','0');
$this->db->order_by('mc.company_id','DESC');
$query=$this->db->get();
//print_r($this->db->last_query());die();
return $query->result_array();
}
public function get_driver_list(){
$this->db->select('md.driver_id,md.first_name,ifnull(md.middle_name,"") as middle_name,md.last_name,md.flat_no,md.street_no,md.street_name,md.suburb,md.pin,md.email,md.mobile,ifnull(date_format(md.dob,"%d/%m/%Y"),"") as dob,md.licence_no,md.licence_image,ifnull(date_format(md.licence_expiry,"%d/%m/%Y"),"") as licence_expiry,md.licence_expiry_image,md.is_australian_licence,md.passport_no,md.passport_no_image,ifnull(date_format(md.passport_expiry,"%d/%m/%Y"),"") as passport_expiry,md.passport_expiry_image,md.utility_bill_id,md.utility_bill_image,md.bank_name,md.bsb,md.account_no,ifnull(md.no_of_at_fault_accidents,0) as no_of_at_fault_accidents,ifnull(md.no_of_not_at_fault_accidents,0) as no_of_not_at_fault_accidents,ifnull(md.profile_photo,"") as profile_photo,ifnull(md.admin_notes,"") as admin_notes,md.status,ifnull(md.created_by,"") as created_by,md.created_ts,ifnull(md.updated_by,"") as updated_by,ifnull(md.updated_ts,"") as updated_ts');
$this->db->from('master_driver md');
$this->db->where('md.status <>','3');
$this->db->order_by('md.status','ASC');
$this->db->order_by('md.driver_id','DESC');
$query=$this->db->get();
//print_r($this->db->last_query());die();
return $query->result_array();
}
public function get_driver_bond_details($driver_id){
$this->db->select('bw.bond_amount,ifnull(date_format(bw.bond_date,"%d/%m/%Y"),"") as bond_date,bw.bond_payment_method,bw.bond_reference_no');
$this->db->from('bond_wallet bw');
$this->db->where('bw.bond_reference_type ','VALIDATE');
$this->db->order_by('bw.bond_reference_id',$driver_id);
$this->db->order_by('bw.driver_id',$driver_id);
$query=$this->db->get();
//print_r($this->db->last_query());die();
if($query){
return $query->row_array();
}
return array();
}
public function get_car_list_rent(){
$this->db->select('mc.car_id,mc.car_no,mc.make,mc.model,mc.year,mc.company_id,mcm.company_name,mcm.company_code');
$this->db->from('master_car mc');
$this->db->join('master_company mcm','mcm.company_id=mc.company_id','inner');
$this->db->where(" NOT EXISTS (SELECT rov.car_id FROM rent_out_vehcile rov WHERE rov.car_id = mc.car_id AND rov.is_rent_in='0')", null, false);
$this->db->where('mc.status','0');
$this->db->order_by('mc.car_no','ASC');
$this->db->group_by('mc.car_id');
$query=$this->db->get();
return $query->result_array();
}
public function get_driver_list_rent_in(){
$this->db->select('md.driver_id,rov.rent_out_id,md.first_name,md.middle_name,md.last_name');
$this->db->from('master_driver md');
$this->db->join('rent_out_vehcile rov','rov.driver_id=md.driver_id','inner');
$this->db->where('md.status','1');
$this->db->where('rov.is_rent_in','0');
$query=$this->db->get();
return $query->result_array();
}
public function get_driver_list_rent_out(){
$this->db->select('md.driver_id,md.first_name,md.middle_name,md.last_name');
$this->db->from('master_driver md');
$this->db->where('md.status','1');
$query=$this->db->get();
return $query->result_array();
}
public function get_user_details($user_id){
$this->db->select('ma.*,mr.role_name');
$this->db->from('master_admin ma');
$this->db->join('master_role mr','mr.role_id=ma.role_id','inner');
$this->db->where('ma.user_id',$user_id);
$query=$this->db->get();
return $query->row_array();
}
public function get_user($where=''){
$this->db->select('ma.*,mr.role_name');
$this->db->from('master_admin ma');
$this->db->join('master_role mr', 'mr.role_id = ma.role_id', 'inner');
$this->db->where('ma.status <>','2');
if(!empty($where)){
$this->db->where($where);
}
$query=$this->db->get();
return $query->result_array();
}
public function getRentoutDetails($where=''){
$this->db->select('rov.*,md.first_name,md.middle_name,md.last_name,mc.car_no');
$this->db->from('rent_out_vehcile rov');
$this->db->join('master_driver md','md.driver_id=rov.driver_id','inner');
$this->db->join('master_car mc','mc.car_id=rov.car_id','inner');
// $this->db->join('master_company mcom','mcom.company_id=rov.company_id','inner');
if(!empty($where)){
$this->db->where('rov.rent_out_id',$where);
$query=$this->db->get();
return $query->row_array();
}else{
$query=$this->db->get();
return $query->result_array();
}
}
public function getRentinDetails($where=''){
$this->db->select('riv.*,md.first_name,md.middle_name,md.last_name,DATE_FORMAT(rov.created_ts, "%d/%m/%Y") AS rent_out_date,rov.rent_out_no,rov.odometer_reading AS rent_out_odometer_reading,mc.car_no');
$this->db->from('rent_in_vehcile riv');
$this->db->join('rent_out_vehcile rov','rov.rent_out_id=riv.rent_out_id');
$this->db->join('master_driver md','md.driver_id=riv.driver_id');
$this->db->join('master_car mc','mc.car_id=rov.car_id');
if(!empty($where)){
$this->db->where('riv.rent_in_id',$where);
$query=$this->db->get();
return $query->row_array();
}else{
$query=$this->db->get();
return $query->result_array();
}
}
public function update_rent_out($condition,$data){
$result=$this->db->update('rent_out_vehcile', $data, $condition);
return $result;
}
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 ;
}
public function get_address_proof_list(){
$this->db->select('mub.utility_bill_id,mub.utility_bill_name');
$this->db->from('master_utility_bill mub');
$this->db->where('mub.status','0');
$this->db->order_by('mub.utility_bill_id','DESC');
$query=$this->db->get();
//print_r($this->db->last_query());die();
return $query->result_array();
}
}