File: /home/cafsindia/login_cafsindia_com/application_bk10feb2023/controllers/Home.php
<?php
/**********************************************************
Filename: Home
Description: Chart view and Chart control logic developed, highchart integration based on role.
Author: s vasanth kumar
Created on: 12-SEP-2019
Reviewed by: Udhayakumar Anandhan (REVIEW PENDING)
Reviewed on:
Approved by:
Approved on:
-------------------------------------------------------
Modification Details:
Modification Date:
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once("Secure_Controller.php");
class Home extends Secure_Controller {
public function __construct(){
parent::__construct();
$this->logged_id = $this->session->userdata('logged_id');
$this->logged_role = $this->session->userdata('logged_role');
}
public function logout(){
$this->session->sess_destroy();
redirect('login');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->load->view('home',$data);
}
public function collect_data(){
$start_date = str_replace("/","-",$this->input->post('start_date'));
$end_date = str_replace("/","-",$this->input->post('end_date'));
$from_date = date('Y-m-d',strtotime($start_date));
$to_date = date('Y-m-d',strtotime($end_date));
$booking_info = $this->booking_info($from_date,$to_date);
$process_info = $this->process_info($from_date,$to_date);
$dl_info = $this->dl_info($from_date,$to_date);
$vehicle_expiry_info = $this->vehicle_expiry_info($from_date,$to_date);
echo json_encode(array('booking_info'=>$booking_info,'process_info'=>$process_info,'dl_info'=>$dl_info,
'vehicle_expiry_info'=>$vehicle_expiry_info));
}
//FUNCTION PROVIDES BOTH COUNT AND RESULT ROWS OF BOOKING
public function booking_master($process_mode,$sts,$from_date,$to_date){
if($process_mode === "COUNT"){
$this->db->select('IFNULL(count(*),0) as booking_count');
}
$this->db->from('booking');
$this->db->where('trans_status',1);
if((int)$sts > 0){
$this->db->where('booking_sts',$sts);
}
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
if($process_mode === "COUNT"){
$booking_info = $this->db->get()->row();
return $booking_info->booking_count;
}else{
$booking_info = $this->db->get()->result();
$trip_mode_list = array(1=> 'Single',2=> 'Multi');
$booking_sts_list = array(1=>"Follow Up",2=>"Confirm",3=>"Cancelled",4=>"On Progress",5=>"Completed");
$booking_tr_line = "";
foreach($booking_info as $booking){
$trip_mode = $trip_mode_list[$booking->trip_mode];
$tour_no = $booking->tour_no;
$from_date = date('d-m-Y',strtotime($booking->from_date));
$to_date = date('d-m-Y',strtotime($booking->to_date));
$origin = $booking->origin;
$destination = $booking->destination;
$avg_km = $booking->avg_km;
$total_tolls = $booking->total_tolls;
$route_information = $booking->route_information;
$vehicle_count = $booking->vehicle_count;
$trip_amount = $booking->trip_amount;
$balance_amount = $booking->balance_amount;
$booking_sts = $booking_sts_list[$booking->booking_sts];
$booking_tr_line .= "<tr>
<td>$trip_mode</td>
<td>$tour_no</td>
<td>$from_date</td>
<td>$to_date</td>
<td>$origin</td>
<td>$destination</td>
<td>$avg_km</td>
<td>$total_tolls</td>
<td>$route_information</td>
<td>$vehicle_count</td>
<td>$trip_amount</td>
<td>$balance_amount</td>
<td>$booking_sts</td>
</tr>";
}
$booking_tr_line .="<tfoot>
<tr style='font-weight: bold; color: red;'>
<td colspan='10' style='text-align:right'>Total</td>
<td></td>
<td></td>
<td></td>
</tr>
</tfoot>";
if($booking_tr_line === ""){
$booking_tr_line = "<tr><td align='center' colspan='13'>No Records Found</td></tr>";
}
return $booking_tr_line;
}
}
//BOOKING COUNT BASED ON STATUS
public function booking_info($from_date,$to_date){
//TOTAL BOOKING COUNT
$total_booking = $this->booking_master("COUNT",0,$from_date,$to_date);
$follow_up = $this->booking_master("COUNT",1,$from_date,$to_date);
$confirm = $this->booking_master("COUNT",2,$from_date,$to_date);
$cancelled = $this->booking_master("COUNT",3,$from_date,$to_date);
$on_progress = $this->booking_master("COUNT",4,$from_date,$to_date);
$completed = $this->booking_master("COUNT",5,$from_date,$to_date);
$send_data = json_encode(array('from_date'=>$from_date,'to_date'=>$to_date));
$view_btn = "<button type='button' class='btn btn-edit btn-xs btn_action' onclick=view_model('booking_info_view','$send_data');><i class='fa fa-pencil-square-o fa-large' aria-hidden='true'></i> View</button>";
$table_content = "<h5 class='tab_head' style='margin:10px 0px;'>Total Booking</h5>
<table class= 'table table-bordered' style='background-color: #ffffff; text-align: center;'>
<thead>
<tr>
<th>Total Booking</th>
<th>Follow Up</th>
<th>Confirm</th>
<th>Onprogress</th>
<th>Completed</th>
<th>Cancelled</th>
<th>View</th>
</tr>
</thead>
<tbody>
<tr>
<td>$total_booking</td>
<td>$follow_up</td>
<td>$confirm</td>
<td>$on_progress</td>
<td>$completed</td>
<td>$cancelled</td>
<td>$view_btn</td>
</tr>
</tbody>
</table>";
return $table_content;
}
//BOOKING INFORMATION AGAINS BOOKING COUNT
public function booking_info_view(){
$send_data = json_decode($this->input->post('send_data'));
$from_date = $send_data->from_date;
$to_date = $send_data->to_date;
//booking_master($process_mode,$join,$sts,$from_date,$to_date) -->FORMAT OF BOOKING MASTER
$followup_table = $this->booking_master("INFO",1,$from_date,$to_date);
$confirm_table = $this->booking_master("INFO",2,$from_date,$to_date);
$completed_table = $this->booking_master("INFO",5,$from_date,$to_date);
$onprogress_table = $this->booking_master("INFO",4,$from_date,$to_date);
$cancelled_table = $this->booking_master("INFO",3,$from_date,$to_date);
$table_data = "<table class= 'table table-bordered table-striped' id='booking'>
<thead>
<tr>
<th>Trip Mode</th>
<th>Tour No</th>
<th>From Date</th>
<th>TO Date</th>
<th>Origin</th>
<th>Destination</th>
<th>Avg.KM</th>
<th>Total Toll</th>
<th>Route</th>
<th>Vehicle</th>
<th>Total Amount</th>
<th>Balance</th>
<th>Booking Status</th>
</tr>
</thead>
<tbody>
@TRCONTENT
</tbody>
</table>";
$followup_table = str_replace("@TRCONTENT",$followup_table,$table_data);
$confirm_table = str_replace("@TRCONTENT",$confirm_table,$table_data);
$completed_table = str_replace("@TRCONTENT",$completed_table,$table_data);
$onprogress_table = str_replace("@TRCONTENT",$onprogress_table,$table_data);
$cancelled_table = str_replace("@TRCONTENT",$cancelled_table,$table_data);
$append_data = "<ul class='nav nav-tabs' role='tablist'>
<li role='presentation' class='active'><a href='#follow_up_tab' data-toggle='tab'>Follow Up</a></li>
<li role='presentation'><a href='#confirm_tab' data-toggle='tab'>Confirm</a></li>
<li role='presentation'><a href='#onprogress_tab' data-toggle='tab'>Onprogress</a></li>
<li role='presentation'><a href='#completed_tab' data-toggle='tab'>Completed</a></li>
<li role='presentation'><a href='#cancelled_tab' data-toggle='tab'>Cancelled</a></li>
</ul>
<div class='tab-content' style='padding:8px'>
<div role='tabpanel' class='tab-pane active' id='follow_up_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Followup Booking</h5>
$followup_table
</div>
<div role='tabpanel' class='tab-pane' id='confirm_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Confirm Booking</h5>
$confirm_table
</div>
<div role='tabpanel' class='tab-pane' id='onprogress_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Onprogress Booking</h5>$onprogress_table
</div>
<div role='tabpanel' class='tab-pane' id='completed_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Completed Booking</h5>
$completed_table
</div>
<div role='tabpanel' class='tab-pane' id='cancelled_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Cancelled Booking</h5>
$cancelled_table
</div>
</div>";
echo json_encode(array('append_head'=>"Booking Detail Information",'append_data'=>$append_data));
}
//TOTAL TRIP COUNT STATUS
public function process_info($from_date,$to_date){
//TRIP INFORMATION
$this->db->from('booking');
$this->db->join('booking_line', 'booking_line.prime_booking_id = booking.prime_booking_id','inner');
$this->db->where('booking.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$this->db->group_by('booking_line.prime_booking_id,booking_line.preferred_vehicle');
$trip_info = $this->db->get();
$trip_count = $trip_info->num_rows();
//OPERATION INFORMATION
$this->db->select('IFNULL(count(*),0) as assign_count');
$this->db->from('operation');
$this->db->join('operation_line', 'operation_line.prime_operation_id = operation.prime_operation_id','inner');
$this->db->where('operation_line.assign_vehicle !=',0);
$this->db->where('operation.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$assign_trip = $this->db->get()->row();
$assign_count = $assign_trip->assign_count;
$this->db->select('IFNULL(count(*),0) as unassign_count');
$this->db->from('operation');
$this->db->join('operation_line', 'operation_line.prime_operation_id = operation.prime_operation_id','inner');
$this->db->where('operation_line.assign_vehicle',0);
$this->db->where('operation.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$unassign_trip = $this->db->get()->row();
$unassign_count = $unassign_trip->unassign_count;
//ACCOUNTS INFORMATION
$this->db->select('IFNULL(count(*),0) as completed');
$this->db->from('accounts');
$this->db->join('accounts_line', 'accounts_line.prime_operation_id = accounts.prime_operation_id','inner');
$this->db->where('accounts.accounts_sts',5);
$this->db->where('accounts.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$completed_trip = $this->db->get()->row();
$completed = $completed_trip->completed;
$this->db->select('IFNULL(count(*),0) as in_completed');
$this->db->from('accounts');
$this->db->join('accounts_line', 'accounts_line.prime_operation_id = accounts.prime_operation_id','inner');
$this->db->where('accounts.accounts_sts != ',5);
$this->db->where('accounts.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$in_completed_trip = $this->db->get()->row();
$in_completed = $in_completed_trip->in_completed;
$send_data = json_encode(array('from_date'=>$from_date,'to_date'=>$to_date));
$view_btn = "<button type='button' class='btn btn-edit btn-xs btn_action' onclick=view_model('process_info_view','$send_data');><i class='fa fa-pencil-square-o fa-large' aria-hidden='true'></i> View</button>";
$table_content = "<h5 class='tab_head' style='margin:10px 0px;'>Process Infomration</h5>
<table class= 'table table-bordered' style='background-color: #ffffff; text-align: center;'>
<thead>
<tr>
<th rowspan='2' style='padding: 2px; text-align: center;'>Total Trip</th>
<th colspan='2' style='padding: 2px; text-align: center;'>Operation</th>
<th colspan='2' style='padding: 2px; text-align: center;'>Accounts</th>
<th rowspan='2' style='padding: 2px; text-align: center;'>View</th>
</tr>
<tr>
<th style='padding: 2px; text-align: center;'>Un Assigned</th>
<th style='padding: 2px; text-align: center;'>Assigned</th>
<th style='padding: 2px; text-align: center;'>In Completed</th>
<th style='padding: 2px; text-align: center;'>Completed</th>
</tr>
</thead>
<tbody>
<tr>
<td>$trip_count</td>
<td>$unassign_count</td>
<td>$assign_count</td>
<td>$in_completed</td>
<td>$completed</td>
<td>$view_btn</td>
</tr>
</tbody>
</table>";
return $table_content;
}
public function process_info_view(){
$send_data = json_decode($this->input->post('send_data'));
$from_date = $send_data->from_date;
$to_date = $send_data->to_date;
//TRIP INFORMATION
$this->db->select('trip_mode,tour_no,from_date,to_date,origin,destination,avg_km,trip_amount,balance_amount,booking_sts,vehicle_type.vehicle_type,vehicle.vehicle_number');
$this->db->from('booking');
$this->db->join('booking_line', 'booking_line.prime_booking_id = booking.prime_booking_id','inner');
$this->db->join('vehicle_type','vehicle_type.prime_vehicle_type_id = booking_line.vehicle_type','inner');
$this->db->join('vehicle','vehicle.prime_vehicle_id = booking_line.preferred_vehicle','inner');
$this->db->where('booking.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$this->db->group_by('booking_line.prime_booking_id,booking_line.preferred_vehicle');
$trip_info = $this->db->get();
$trip_result = $trip_info->result();
$trip_mode_list = array(1=> 'Single',2=> 'Multi');
$trip_sts_list = array(1=>"Follow Up",2=>"Confirm",3=>"Cancelled",4=>"On Progress",5=>"Completed");
$trip_tr_line = "";
foreach($trip_result as $total_booking){
$trip_mode = $trip_mode_list[$total_booking->trip_mode];
$trip_tour_no = $total_booking->tour_no;
$trip_from_date = date('d-m-Y',strtotime($total_booking->from_date));
$trip_to_date = date('d-m-Y',strtotime($total_booking->to_date));
$trip_origin = $total_booking->origin;
$trip_destination = $total_booking->destination;
$trip_avg_km = $total_booking->avg_km;
$trip_vehicle_type = $total_booking->vehicle_type;
$trip_vehicle_number = $total_booking->vehicle_number;
$trip_trip_amount = $total_booking->trip_amount;
$trip_balance_amount = $total_booking->balance_amount;
$trip_booking_sts = $trip_sts_list[$total_booking->booking_sts];
$trip_tr_line .= "<tr>
<td>$trip_mode</td>
<td>$trip_tour_no</td>
<td>$trip_from_date</td>
<td>$trip_to_date</td>
<td>$trip_origin</td>
<td>$trip_destination</td>
<td>$trip_avg_km</td>
<td>$trip_vehicle_type</td>
<td>$trip_vehicle_number</td>
<td>$trip_trip_amount</td>
<td>$trip_balance_amount</td>
<td>$trip_booking_sts</td>
</tr>";
}
$trip_tr_line .="<tfoot>
<tr style='font-weight: bold; color: red;'>
<td colspan='9' style='text-align:right'>Total</td>
<td></td>
<td></td>
<td></td>
</tr>
</tfoot>";
if($trip_tr_line === ""){
$trip_tr_line = "<tr><td align='center' colspan='13'>No Records Found</td></tr>";
}
$table_data_trip_info = "<table class= 'table table-bordered table-striped' id='total_trip'>
<thead>
<tr>
<th>Trip Mode</th>
<th>Tour No</th>
<th>From Date</th>
<th>TO Date</th>
<th>Origin</th>
<th>Destination</th>
<th>Avg.KM</th>
<th>Vehicle Type</th>
<th>Preferred Vehicle</th>
<th>Total Amount</th>
<th>Balance</th>
<th>Booking Status</th>
</tr>
</thead>
<tbody>
$trip_tr_line
</tbody>
</table>";
//OPERATION INFORMATION ASSIGNED VEHICLE
$this->db->select('tour_no,from_date,to_date,origin,destination,avg_km,route_information,trip_amount,operation_line.preferred_vehicle,balance_amount,booking_sts,vehicle_type.vehicle_type as vehicle,assign.vehicle_number as assined_vehicle,preferred.vehicle_number as preferred_vehicle,operation_sts');
$this->db->from('operation');
$this->db->join('operation_line', 'operation_line.prime_operation_id = operation.prime_operation_id','inner');
$this->db->join('vehicle_type', 'operation_line.vehicle_type = vehicle_type.prime_vehicle_type_id','inner');
$this->db->join('vehicle as preferred', 'operation_line.preferred_vehicle = preferred.prime_vehicle_id','inner');
$this->db->join('vehicle as assign', 'operation_line.assign_vehicle = assign.prime_vehicle_id','left');
$this->db->where('operation_line.assign_vehicle !=',0);
$this->db->where('operation.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$assign_trip = $this->db->get();
$assign_result = $assign_trip->result();
$assign_sts_list = array(1=>"Follow Up",2=>"Confirm",3=>"Cancelled",4=>"On Progress",5=>"Completed");
$assign_tr_line = "";
foreach($assign_result as $assigned){
$assign_tour_no = $assigned->tour_no;
$assign_from_date = date('d-m-Y',strtotime($assigned->from_date));
$assign_to_date = date('d-m-Y',strtotime($assigned->to_date));
$assign_origin = $assigned->origin;
$assign_destination = $assigned->destination;
$assign_avg_km = $assigned->avg_km;
$assign_vehicle_type = $assigned->vehicle;
$assign_preferred_vehicle = $assigned->preferred_vehicle;
$assign_vehicle_number = $assigned->assined_vehicle;
$assign_trip_amount = $assigned->trip_amount;
$assign_balance_amount = $assigned->balance_amount;
$assign_booking_sts = $assign_sts_list[$assigned->booking_sts];
$assign_operation_sts = $assign_sts_list[$assigned->operation_sts];
$assign_tr_line .= "<tr>
<td>$assign_tour_no</td>
<td>$assign_from_date</td>
<td>$assign_to_date</td>
<td>$assign_origin</td>
<td>$assign_destination</td>
<td>$assign_avg_km</td>
<td>$assign_vehicle_type</td>
<td>$assign_preferred_vehicle</td>
<td>$assign_vehicle_number</td>
<td>$assign_trip_amount</td>
<td>$assign_balance_amount</td>
<td>$assign_booking_sts</td>
<td>$assign_operation_sts</td>
</tr>";
}
$assign_tr_line .= "<tfoot>
<tr style='font-weight: bold; color: red;'>
<td colspan='9' style='text-align:right'>Total</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tfoot>";
if($assign_tr_line === ""){
$assign_tr_line = "<tr><td align='center' colspan='13'>No Records Found</td></tr>";
}
$table_data_operation_assigned = "<table class= 'table table-bordered table-striped' id='assigned_trip'>
<thead>
<tr>
<th>Tour No</th>
<th>From Date</th>
<th>TO Date</th>
<th>Origin</th>
<th>Destination</th>
<th>Avg.KM</th>
<th>Vehicle Type</th>
<th>Preferred Number</th>
<th>Assigned Number</th>
<th>Total Amount</th>
<th>Balance</th>
<th>Booking Status</th>
<th>Operation Status</th>
</tr>
</thead>
<tbody>
$assign_tr_line
</tbody>
</table>";
//OPERATION INFORMATION UNASSIGNED VEHICLE
$this->db->select('tour_no,from_date,to_date,origin,destination,avg_km,route_information,trip_amount,balance_amount,booking_sts,vehicle_type.vehicle_type as vehicle,vehicle.vehicle_number,operation_sts');
$this->db->from('operation');
$this->db->join('operation_line', 'operation_line.prime_operation_id = operation.prime_operation_id','inner');
$this->db->join('vehicle_type','vehicle_type.prime_vehicle_type_id = operation_line.vehicle_type','inner');
$this->db->join('vehicle','vehicle.prime_vehicle_id = operation_line.preferred_vehicle','inner');
$this->db->where('operation_line.assign_vehicle',0);
$this->db->where('operation.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$unassign_trip = $this->db->get();
$unassign_result = $unassign_trip->result();
$unassign_sts_list = array(1=>"Follow Up",2=>"Confirm",3=>"Cancelled",4=>"On Progress",5=>"Completed");
$unassign_tr_line = "";
foreach($unassign_result as $unassigned){
$unassign_tour_no = $unassigned->tour_no;
$unassign_from_date = date('d-m-Y',strtotime($unassigned->from_date));
$unassign_to_date = date('d-m-Y',strtotime($unassigned->to_date));
$unassign_origin = $unassigned->origin;
$unassign_destination = $unassigned->destination;
$unassign_avg_km = $unassigned->avg_km;
$unassign_vehicle_type = $unassigned->vehicle;
$unassign_vehicle_number = $unassigned->vehicle_number;
$unassign_trip_amount = $unassigned->trip_amount;
$unassign_balance_amount = $unassigned->balance_amount;
$unassign_booking_sts = $unassign_sts_list[$unassigned->booking_sts];
$unassign_operation_sts = $unassign_sts_list[$unassigned->operation_sts];
$unassign_tr_line .= "<tr>
<td>$unassign_tour_no</td>
<td>$unassign_from_date</td>
<td>$unassign_to_date</td>
<td>$unassign_origin</td>
<td>$unassign_destination</td>
<td>$unassign_avg_km</td>
<td>$unassign_vehicle_type</td>
<td>$unassign_vehicle_number</td>
<td>$unassign_trip_amount</td>
<td>$unassign_balance_amount</td>
<td>$unassign_booking_sts</td>
<td>$unassign_operation_sts</td>
</tr>";
}
$unassign_tr_line .="<tfoot>
<tr style='font-weight: bold; color: red;'>
<td colspan='9' style='text-align:right'>Total</td>
<td></td>
<td></td>
<td></td>
</tr>
</tfoot>";
if($unassign_tr_line === ""){
$unassign_tr_line = "<tr><td align='center' colspan='13'>No Records Found</td></tr>";
}
$table_data_operation_unassigned = "<table class= 'table table-bordered table-striped' id='unassigned_trip'>
<thead>
<tr>
<th>Tour No</th>
<th>From Date</th>
<th>TO Date</th>
<th>Origin</th>
<th>Destination</th>
<th>Avg.KM</th>
<th>Vehicle Type</th>
<th>Prefered Number</th>
<th>Total Amount</th>
<th>Balance</th>
<th>Booking Status</th>
<th>Operation Status</th>
</tr>
</thead>
<tbody>
$unassign_tr_line
</tbody>
</table>";
//ACCOUNTS INFORMATION COMPLETED
$this->db->from('accounts');
$this->db->join('accounts_line', 'accounts_line.prime_operation_id = accounts.prime_operation_id','inner');
$this->db->where('accounts.accounts_sts',5);
$this->db->where('accounts.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$completed_trip = $this->db->get();
$completed_result = $completed_trip->result();
$completed_sts_list = array(1=>"Follow Up",2=>"Confirm",3=>"Cancelled",4=>"On Progress",5=>"Completed");
$completed_tr_line = "";
foreach($completed_result as $completed){
$complete_tour_no = $completed->tour_no;
$complete_from_date = date('d-m-Y',strtotime($completed->from_date));
$complete_to_date = date('d-m-Y',strtotime($completed->to_date));
$complete_origin = $completed->origin;
$complete_destination = $completed->destination;
$complete_avg_km = $completed->avg_km;
$complete_trip_amount = $completed->trip_amount;
$complete_balance_amount = $completed->balance_amount;
$complete_booking_sts = $completed_sts_list[$completed->booking_sts];
$complete_operation_sts = $completed_sts_list[$completed->operation_sts];
$completed_tr_line .= "<tr>
<td>$complete_tour_no</td>
<td>$complete_from_date</td>
<td>$complete_to_date</td>
<td>$complete_origin</td>
<td>$complete_destination</td>
<td>$complete_avg_km</td>
<td>$complete_trip_amount</td>
<td>$complete_balance_amount</td>
<td>$complete_booking_sts</td>
<td>$complete_operation_sts</td>
</tr>";
}
$completed_tr_line .= "<tfoot>
<tr style='font-weight: bold; color: red;'>
<td colspan='6' style='text-align:right'>Total</td>
<td></td>
<td></td>
<td></td>
</tr>
</tfoot>";
if($completed_tr_line === ""){
$completed_tr_line = "<tr><td align='center' colspan='13'>No Records Found</td></tr>";
}
//ACCOUNTS INFORMATION INCOMPLETED
$this->db->from('accounts');
$this->db->join('accounts_line', 'accounts_line.prime_operation_id = accounts.prime_operation_id','inner');
$this->db->where('accounts.accounts_sts != ',5);
$this->db->where('accounts.trans_status',1);
$this->db->group_start();
$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($from_date).' AND '.$this->db->escape($to_date));
$this->db->group_end();
$in_completed_trip = $this->db->get();
$in_completed_result = $in_completed_trip->result();
$in_completed_sts_list = array(1=>"Follow Up",2=>"Confirm",3=>"Cancelled",4=>"On Progress",5=>"Completed");
$in_completed_tr_line = "";
foreach($in_completed_result as $in_completed){
$in_complete_tour_no = $in_completed->tour_no;
$in_complete_from_date = date('d-m-Y',strtotime($in_completed->from_date));
$in_complete_to_date = date('d-m-Y',strtotime($in_completed->to_date));
$in_complete_origin = $in_completed->origin;
$in_complete_destination = $in_completed->destination;
$in_complete_avg_km = $in_completed->avg_km;
$in_complete_trip_amount = $in_completed->trip_amount;
$in_complete_balance_amount = $in_completed->balance_amount;
$in_complete_booking_sts = $in_completed_sts_list[$in_completed->booking_sts];
$in_complete_operation_sts = $in_completed_sts_list[$in_completed->operation_sts];
$in_completed_tr_line .= "<tr>
<td>$in_complete_tour_no</td>
<td>$in_complete_from_date</td>
<td>$in_complete_to_date</td>
<td>$in_complete_origin</td>
<td>$in_complete_destination</td>
<td>$in_complete_avg_km</td>
<td>$in_complete_trip_amount</td>
<td>$in_complete_balance_amount</td>
<td>$in_complete_booking_sts</td>
<td>$in_complete_operation_sts</td>
</tr>";
}
$in_completed_tr_line .= "<tfoot>
<tr style='font-weight: bold; color: red;'>
<td colspan='6' style='text-align:right'>Total</td>
<td></td>
<td></td>
<td></td>
</tr>
</tfoot>";
if($in_completed_tr_line === ""){
$in_completed_tr_line = "<tr><td align='center' colspan='13'>No Records Found</td></tr>";
}
$table_data_accounts = "<table class= 'table table-bordered table-striped' id='incompleted_trip'>
<thead>
<tr>
<th>Tour No</th>
<th>From Date</th>
<th>TO Date</th>
<th>Origin</th>
<th>Destination</th>
<th>Avg.KM</th>
<th>Total Amount</th>
<th>Balance</th>
<th>Booking Status</th>
<th>Operation Status</th>
</tr>
</thead>
<tbody>
@TRCONTENT
</tbody>
</table>";
$completed_tr_line = str_replace("@TRCONTENT",$completed_tr_line,$table_data_accounts);
$in_completed_tr_line = str_replace("@TRCONTENT",$in_completed_tr_line,$table_data_accounts);
$append_data = "<ul class='nav nav-tabs' role='tablist'>
<li role='presentation' class='active'><a href='#total_trip_tab' data-toggle='tab'>Total Trip</a></li>
<li role='presentation'><a href='#un_assigned_tab' data-toggle='tab'>Un Assigned</a></li>
<li role='presentation'><a href='#assigned_tab' data-toggle='tab'>Assigned</a></li>
<li role='presentation'><a href='#in_completed_tab' data-toggle='tab'>In Completed </a></li>
<li role='presentation'><a href='#completed_tab' data-toggle='tab'>Completed </a></li>
</ul>
<div class='tab-content' style='padding:8px'>
<div role='tabpanel' class='tab-pane active' id='total_trip_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Total Trip</h5>
$table_data_trip_info
</div>
<div role='tabpanel' class='tab-pane' id='un_assigned_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Un Assigned</h5>
$table_data_operation_unassigned
</div>
<div role='tabpanel' class='tab-pane' id='assigned_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Assigned</h5>
$table_data_operation_assigned
</div>
<div role='tabpanel' class='tab-pane' id='in_completed_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Incompleted</h5>
$in_completed_tr_line
</div>
<div role='tabpanel' class='tab-pane' id='completed_tab'>
<h5 class='tab_head' style='margin:10px 0px;'>Completed</h5>
$completed_tr_line
</div>
</div>";
echo json_encode(array('append_head'=>"Process Detail Information",'append_data'=>$append_data));
}
//DRIVER LICENCE EXPIRY BASED ON 60 DAYS
public function dl_info($from_date,$to_date){
$this->db->select('employee_name,license_expiry_date');
$this->db->from('employees');
$this->db->where('employees.trans_status', 1);
$this->db->where('employees.role', 7);
$this->db->group_start();
$from_date = $this->db->escape($from_date);
$to_date = $this->db->escape($to_date);
$this->db->where("DATE_FORMAT(license_expiry_date, '%Y-%m-%d') <= $from_date");
$this->db->or_where("DATE_FORMAT(license_expiry_date, '%Y-%m-%d') <= DATE_ADD($to_date, INTERVAL 60 DAY)");
$this->db->group_end();
$dl_expiry_data = $this->db->get();
$dl_expiry_rslt = $dl_expiry_data->result();
$dl_expiry_tr_line = "";
foreach($dl_expiry_rslt as $dl_expiry){
$employee_name = $dl_expiry->employee_name;
$dl_style = "";
$license_expiry_diff = (int)days_difference($dl_expiry->license_expiry_date);
if($license_expiry_diff <= 30){
$dl_style = "style='color:red;'";
}else
if(($license_expiry_diff > 30) && ($license_expiry_diff <= 60)){
$dl_style = "style='color:#1dd8b3;'";
}
$dl_expiry_tr_line .= "<tr>
<td>$employee_name</td>
<td><span $dl_style>".date('d-m-Y',strtotime($dl_expiry->license_expiry_date))."</td></span>
</tr>";
}
if($dl_expiry_tr_line === ""){
$dl_expiry_tr_line = "<tr><td align='center' colspan='2'>No Records Found</td></tr>";
}
$table_data = "<h5 class='tab_head' style='margin:10px 0px;'>DL Expiry<sup>(+60 Days)</sup> </h5>
<div style='max-height:350px; overflow: auto;'>
<table class= 'table table-bordered table-striped'>
<thead>
<tr>
<th>Employee Name</th>
<th>Licence Expiry Date</th>
</tr>
</thead>
<tbody>
$dl_expiry_tr_line
</tbody>
</table>
</div>";
return $table_data;
}
//VEHICLE EXPIRY BASED ON 60 DAYS
public function vehicle_expiry_info($from_date,$to_date){
$this->db->select('vehicle_number,fc_date,permit_valid_date,insurance_valid_date,pollution_valid_date,tp_date,tax_date');
$this->db->from('vehicle');
$this->db->where('vehicle.trans_status', 1);
$from_date = $this->db->escape($from_date);
$to_date = $this->db->escape($to_date);
$this->db->group_start();
$this->db->where("DATE_FORMAT(fc_date, '%Y-%m-%d') <= $from_date");
$this->db->or_where("DATE_FORMAT(fc_date, '%Y-%m-%d') <= DATE_ADD($to_date, INTERVAL 60 DAY)");
$this->db->or_where("DATE_FORMAT(permit_valid_date, '%Y-%m-%d') <= $from_date");
$this->db->or_where("DATE_FORMAT(permit_valid_date, '%Y-%m-%d') <= DATE_ADD($to_date, INTERVAL 60 DAY)");
$this->db->or_where("DATE_FORMAT(insurance_valid_date, '%Y-%m-%d') <= $from_date");
$this->db->or_where("DATE_FORMAT(insurance_valid_date, '%Y-%m-%d') <= DATE_ADD($to_date, INTERVAL 60 DAY)");
$this->db->or_where("DATE_FORMAT(pollution_valid_date, '%Y-%m-%d') <= $from_date");
$this->db->or_where("DATE_FORMAT(pollution_valid_date, '%Y-%m-%d') <= DATE_ADD($to_date, INTERVAL 60 DAY)");
$this->db->or_where("DATE_FORMAT(tp_date, '%Y-%m-%d') <= $from_date");
$this->db->or_where("DATE_FORMAT(tp_date, '%Y-%m-%d') <= DATE_ADD($to_date, INTERVAL 60 DAY)");
$this->db->or_where("DATE_FORMAT(tax_date, '%Y-%m-%d') <= $from_date");
$this->db->or_where("DATE_FORMAT(tax_date, '%Y-%m-%d') <= DATE_ADD($to_date, INTERVAL 60 DAY)");
$this->db->group_end();
$vehicle_data = $this->db->get();
$vehicle_rslt = $vehicle_data->result();
$vehicle_expiry_tr_line = "";
foreach($vehicle_rslt as $vehicle_expiry){
$vehicle_number = $vehicle_expiry->vehicle_number;
$fc_style = "";
$fc_diff = (int)days_difference($vehicle_expiry->fc_date);
if($fc_diff <= 30){ $fc_style = "style='color:red;'"; }else
if(($fc_diff > 30) && ($fc_diff <= 60)){ $fc_style = "style='color:#1dd8b3;'"; }
$permit_style = "";
$permit_diff = (int)days_difference($vehicle_expiry->permit_valid_date);
if($permit_diff <= 30){ $permit_style = "style='color:red;'"; }else
if(($permit_diff > 30) && ($permit_diff <= 60)){ $permit_style = "style='color:#1dd8b3;'"; }
$insurance_style = "";
$insurance_diff = (int)days_difference($vehicle_expiry->insurance_valid_date);
if($insurance_diff <= 30){ $insurance_style = "style='color:red;'"; }else
if(($insurance_diff > 30) && ($insurance_diff <= 60)){ $insurance_style = "style='color:#1dd8b3;'"; }
$pollution_style = "";
$pollution_diff = (int)days_difference($vehicle_expiry->pollution_valid_date);
if($pollution_diff <= 30){ $pollution_style = "style='color:red;'"; }else
if(($pollution_diff > 30) && ($pollution_diff <= 60)){ $pollution_style = "style='color:#1dd8b3;'"; }
$tp_style = "";
$tp_diff = (int)days_difference($vehicle_expiry->tp_date);
if($tp_diff <= 30){ $tp_style = "style='color:red;'"; }else
if(($tp_diff > 30) && ($tp_diff <= 60)){ $tp_style = "style='color:#1dd8b3;'"; }
$tax_style = "";
$tax_diff = (int)days_difference($vehicle_expiry->tax_date);
if($tax_diff <= 30){ $tax_style = "style='color:red;'"; }else
if(($tax_diff > 30) && ($tax_diff <= 60)){ $tax_style = "style='color:#1dd8b3;'"; }
$vehicle_expiry_tr_line .= "<tr>
<td>$vehicle_number</td>
<td><span $fc_style>".date('d-m-Y',strtotime($vehicle_expiry->fc_date))."</td></span>
<td><span $permit_style>".date('d-m-Y',strtotime($vehicle_expiry->permit_valid_date))."</td></span>
<td><span $insurance_style>".date('d-m-Y',strtotime($vehicle_expiry->insurance_valid_date))."</td></span>
<td><span $pollution_style>".date('d-m-Y',strtotime($vehicle_expiry->pollution_valid_date))."</td></span>
<td><span $tp_style>".date('d-m-Y',strtotime($vehicle_expiry->tp_date))."</td></span>
<td><span $tax_style>".date('d-m-Y',strtotime($vehicle_expiry->tax_date))."</td></span>
</tr>";
}
if($vehicle_expiry_tr_line === ""){
$vehicle_expiry_tr_line = "<tr><td align='center' colspan='7'>No Records Found</td></tr>";
}
$table_data = "<h5 class='tab_head' style='margin:10px 0px;'>Vehicle Expiry<sup>(+60 Days)</sup> </h5>
<div style='max-height:350px; overflow: auto;'>
<table class= 'table table-bordered table-striped'>
<thead>
<tr'>
<th>Vehicle Number</th>
<th>FC Date</th>
<th>Permit Date</th>
<th>Insurance Date</th>
<th>pollution Date</th>
<th>TP Date</th>
<th>Tax Date</th>
</tr>
</thead>
<tbody'>
$vehicle_expiry_tr_line
</tbody>
</table>
</div>";
return $table_data;
}
// Date Difference Function
function days_difference($check_date){
$begin_date = new DateTime();
$begin_date = $begin_date->format("Y-m-d");
$end_date = new DateTime($check_date);
$end_date = $end_date->format("Y-m-d");
$date_parts1 = explode("-", $begin_date);
$date_parts2 = explode("-", $end_date);
$start_date = gregoriantojd($date_parts1[1], $date_parts1[2], $date_parts1[0]);
$end_date = gregoriantojd($date_parts2[1], $date_parts2[2], $date_parts2[0]);
$diff = abs($end_date - $start_date);
$d1 = new DateTime();
$d2 = new DateTime($check_date);
if($d1 > $d2){
$diff = "-".$diff;
}
return $diff;
}
}
?>