File: //home/cafsindia/allyindian_com/sbltt/application/views/DashboardAdmin.php
<?php
//Box-1
$enquiry=$this->db->query("SELECT count(*) as count FROM sblt_enquiry WHERE status = '1' and created_date like '$cur_month%'");
$enquiry_result = $enquiry->result_array();
$enquiry_count = $enquiry_result[0]['count'];
//Box-2
$enquiry_sts=$this->db->query("SELECT count(*) as count FROM sblt_enquiry WHERE enquiry_sts = '1' and status = '1' and created_date like '$cur_month%'");
$enquiry_sts_result = $enquiry_sts->result_array();
$enquiry_sts_count = $enquiry_sts_result[0]['count'];
$enquiry_hot=$this->db->query("SELECT count(*) as count FROM sblt_enquiry WHERE prospect = '1' and status = '1' and created_date like '$cur_month%'");
$enquiry_hot_result = $enquiry_hot->result_array();
$enquiry_hot_count = $enquiry_hot_result[0]['count'];
//Box-3
$booking_confirm=$this->db->query("SELECT count(*) as count FROM sblt_booking WHERE booking_sts = '2' and status = '1' and from_date like '$cur_month%'");
$booking_confirm_result = $booking_confirm->result_array();
$booking_confirm_count = $booking_confirm_result[0]['count'];
//travel date
$booking_travel=$this->db->query("SELECT count(*) as count FROM sblt_booking WHERE booking_sts = '2' and status = '1' and created_date like '$last_month%' and from_date like '$cur_month%'");
$booking_travel_result = $booking_travel->result_array();
$booking_travel_count = $booking_travel_result[0]['count'];
//Box-4
$last_booking_confirm=$this->db->query("SELECT count(*) as count FROM sblt_booking WHERE booking_sts = '2' and status = '1' and from_date like '$last_month%'");
$last_booking_confirm_result = $last_booking_confirm->result_array();
$last_booking_confirm_count = $last_booking_confirm_result[0]['count'];
//Box-6
$total_customer=$this->db->query("SELECT count(*) as count FROM sblt_customers WHERE cust_sts = 'Active'");
$total_customer_result = $total_customer->result_array();
$total_customer_count = $total_customer_result[0]['count'];
//Enquiry
$today_crdate = $this->db->query("SELECT count(*) as count,enquiry_sts FROM sblt_enquiry WHERE status = '1' and created_date like '$today_date%' GROUP by enquiry_sts");
$today_crdate_rslt = $today_crdate->result_array();
$today_crdate_follow = 0;
$today_crdate_confirm = 0;
$today_crdate_cancel = 0;
foreach ($today_crdate_rslt as $key => $value) {
$count = $value['count'];
$enquiry_sts = $value['enquiry_sts'];
if($enquiry_sts === "1"){
$today_crdate_follow = $count;
}else
if($enquiry_sts === "2"){
$today_crdate_confirm = $count;
}else
if($enquiry_sts === "3"){
$today_crdate_cancel = $count;
}
}
$month_crdate = $this->db->query("SELECT count(*) as count,enquiry_sts FROM sblt_enquiry WHERE status = '1' and created_date like '$cur_month%' GROUP by enquiry_sts");
$month_crdate_rslt = $month_crdate->result_array();
$month_crdate_follow = 0;
$month_crdate_confirm = 0;
$month_crdate_cancel = 0;
foreach ($month_crdate_rslt as $key => $value) {
$count = $value['count'];
$enquiry_sts = $value['enquiry_sts'];
if($enquiry_sts === "1"){
$month_crdate_follow = $count;
}else
if($enquiry_sts === "2"){
$month_crdate_confirm = $count;
}else
if($enquiry_sts === "3"){
$month_crdate_cancel = $count;
}
}
$year_crdate = $this->db->query("SELECT count(*) as count,enquiry_sts FROM sblt_enquiry WHERE status = '1' and created_date like '$cur_year%' GROUP by enquiry_sts");
$year_crdate_rslt = $year_crdate->result_array();
$year_crdate_follow = 0;
$year_crdate_confirm = 0;
$year_crdate_cancel = 0;
foreach ($year_crdate_rslt as $key => $value) {
$count = $value['count'];
$enquiry_sts = $value['enquiry_sts'];
if($enquiry_sts === "1"){
$year_crdate_follow = $count;
}else
if($enquiry_sts === "2"){
$year_crdate_confirm = $count;
}else
if($enquiry_sts === "3"){
$year_crdate_cancel = $count;
}
}
//Booking count by created date
//Today
$cr_today_booking = $this->db->query("SELECT count(*) as count FROM sblt_booking WHERE status = '1' and created_date like '$today_date%'");
$cr_today_booking_result = $cr_today_booking->result_array();
$cr_today_booking_count = $cr_today_booking_result[0]['count'];
//Month
$cr_month_booking = $this->db->query("SELECT count(*) as count FROM sblt_booking WHERE status = '1' and created_date like '$cur_month%'");
$cr_month_booking_result = $cr_month_booking->result_array();
$cr_month_booking_count = $cr_month_booking_result[0]['count'];
//Year
$cr_year_booking = $this->db->query("SELECT count(*) as count FROM sblt_booking WHERE status = '1' and created_date like '$cur_year%'");
$cr_year_booking_result = $cr_year_booking->result_array();
$cr_year_booking_count = $cr_year_booking_result[0]['count'];
//Booking
$today_crdate_book = $this->db->query("SELECT count(*) as count,booking_sts FROM sblt_booking WHERE status = '1' and from_date like '$today_date%' GROUP by booking_sts");
$today_crdate_book_rslt = $today_crdate_book->result_array();
$today_crdate_book_follow = 0;
$today_crdate_book_confirm = 0;
$today_crdate_book_cancel = 0;
foreach ($today_crdate_book_rslt as $key => $value) {
$count = $value['count'];
$booking_sts = $value['booking_sts'];
if($booking_sts === "1"){
$today_crdate_book_follow = $count;
}else
if($booking_sts === "2"){
$today_crdate_book_confirm = $count;
}else
if($booking_sts === "3"){
$today_crdate_book_cancel = $count;
}
}
$month_crdate_book = $this->db->query("SELECT count(*) as count,booking_sts FROM sblt_booking WHERE status = '1' and from_date like '$cur_month%' GROUP by booking_sts");
$month_crdate_book_rslt = $month_crdate_book->result_array();
$month_crdate_book_follow = 0;
$month_crdate_book_confirm = 0;
$month_crdate_book_cancel = 0;
foreach ($month_crdate_book_rslt as $key => $value) {
$count = $value['count'];
$booking_sts = $value['booking_sts'];
if($booking_sts === "1"){
$month_crdate_book_follow = $count;
}else
if($booking_sts === "2"){
$month_crdate_book_confirm = $count;
}else
if($booking_sts === "3"){
$month_crdate_book_cancel = $count;
}
}
$year_crdate_book = $this->db->query("SELECT count(*) as count,booking_sts FROM sblt_booking WHERE status = '1' and from_date like '$cur_year%' GROUP by booking_sts");
$year_crdate_book_rslt = $year_crdate_book->result_array();
$year_crdate_book_follow = 0;
$year_crdate_book_confirm = 0;
$year_crdate_book_cancel = 0;
foreach ($year_crdate_book_rslt as $key => $value) {
$count = $value['count'];
$booking_sts = $value['booking_sts'];
if($booking_sts === "1"){
$year_crdate_book_follow = $count;
}else
if($booking_sts === "2"){
$year_crdate_book_confirm = $count;
}else
if($booking_sts === "3"){
$year_crdate_book_cancel = $count;
}
}
// Maintenance vehicle details date included 21-07-18
$maintenance_details = $this->db->query("SELECT maintenance_type,vehicle_no,maintenance_date,delivery_date,service_center FROM sblt_maintenance INNER JOIN sblt_vehicle on sblt_vehicle.vehicle_id = sblt_maintenance.vehicle_id LEFT JOIN sblt_main_service on sblt_main_service.maintenance_id = sblt_maintenance.maintenance_id WHERE sblt_maintenance.status = '1'");
$maintenance_details_rslt = $maintenance_details->result_array();
$maintenance_name ="";
$service_center_name ="";
foreach ($maintenance_details_rslt as $key => $value) {
$maintenance_type = $value['maintenance_type'];
$vehicle_no = $value['vehicle_no'];
$maintenance_date = $value['maintenance_date'];
$delivery_date = $value['delivery_date'];
$service_center = $value['service_center'];
$mainten_date = date("d-m-Y",strtotime($maintenance_date));
if(!$delivery_date){
$delivery_date = "-";
}
if($maintenance_type === "1"){
$maintenance_name = "General Service";
}else
if($maintenance_type === "2"){
$maintenance_name = "Break Down";
}
if($service_center === "1"){
$service_center_name = "SBLTT";
}
$tr_line1 .= "<tr>
<td>$vehicle_no</td>
<td>$maintenance_name</td>
<td>$mainten_date</td>
<td>$delivery_date</td>
<td>$service_center_name</td>
</tr>";
}
//Driver licence date
$date_now = date("Y-m");
$todays_date = date("Y-m-d");
$todays_date = new DateTime('now');
$todays_date->modify('+6 month');
$todays_date = $todays_date->format('Y-m-d');
$today = date("Y-m-d");
$today = new DateTime('now');
$today->modify('-6 month');
$today = $today->format('Y-m-d');
$driver_details = $this->db->query("SELECT license_expiry,first_name,last_name FROM sblt_employees INNER JOIN sblt_people on sblt_people.person_id = sblt_employees.person_id WHERE role = '7' and deleted = '0' and STR_TO_DATE(license_expiry, '%d-%m-%Y') BETWEEN '$today' and '$todays_date' order by STR_TO_DATE(license_expiry, '%d-%m-%Y') ASC");
$driver_details_rslt = $driver_details->result_array();
foreach ($driver_details_rslt as $key => $value) {
$first_name = $value['first_name'];
$last_name = $value['last_name'];
$license_date = $value['license_expiry'];
$license_expiry = date("Y-m",strtotime($license_date));
$cls = "";
if(strtotime($license_expiry) < strtotime($date_now)){
$cls = "style='color:red;'";
}
$tr_line .= "<tr $cls>
<td>$first_name $last_name</td>
<td>$license_date</td>
</tr>";
}
//Vehicle Assign Report for operation and operation_line model
$vehicle_total = $this->db->query("SELECT count(*) as count FROM `sblt_operation` where from_date like '$cur_month%' ");
$vehicle_total_rslt = $vehicle_total->result_array();
$vehicle_total_count = $vehicle_total_rslt[0]['count'];
$vehicle_assign = $this->db->query("SELECT COUNT(DISTINCT sblt_operation.operation_id) as count FROM `sblt_operation` INNER join sblt_operation_line on sblt_operation_line.operation_id = sblt_operation.operation_id where from_date like '$cur_month%' and sblt_operation.operation_sts != '3'");
$vehicle_assign_rslt = $vehicle_assign->result_array();
$vehicle_assign_count = $vehicle_assign_rslt[0]['count'];
$vehicle_pending = $this->db->query("SELECT DISTINCT(COUNT(sblt_operation.operation_id)) as count FROM `sblt_operation` left join sblt_operation_line on sblt_operation_line.operation_id = sblt_operation.operation_id where op_line_id IS NULL and from_date like '$cur_month%' and operation_sts != '3'");
$vehicle_pending_rslt = $vehicle_pending->result_array();
$vehicle_pending_count = $vehicle_pending_rslt[0]['count'];
$vehicle_cancel = $this->db->query("SELECT DISTINCT(COUNT(sblt_operation.operation_id)) as count FROM `sblt_operation` where operation_sts = '3' and from_date like '$cur_month%'");
$vehicle_cancel_rslt = $vehicle_cancel->result_array();
$vehicle_cancel_count = $vehicle_cancel_rslt[0]['count'];
//vehicel track record --find vehicle id
$vehile_track_all = $this->db->query("SELECT vehicle_id,vehicle_no,tracking_link,owned_by FROM sblt_vehicle WHERE status = '1' and owned_by='1'");
$vehile_track_all_rslt = $vehile_track_all->result_array();
$veh_tr_line="";
foreach ($vehile_track_all_rslt as $key => $value) {
$vehicle_no = $value['vehicle_no'];
$tracking_link = $value['tracking_link'];
$vehicle_id = $value['vehicle_id'];
if(($tracking_link !== "") && (strtoupper($tracking_link) !== "HTTP") && ($tracking_link !== "-")){
$url = "http://gpsvts.net/mobile/getSelectedVehicleLocation?vehicleId=".urlencode($tracking_link)."&userId=manisblt";
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
$track_info = curl_exec($ch);
curl_close($ch);
$vehicle_place = json_decode($track_info);
$speed = $vehicle_place->speed;
$overspeed = $vehicle_place->isOverSpeed;
$place = $vehicle_place->address;
$lastseen = $vehicle_place->lastSeen;
//Qr code generation--MRJ--07AUG2018
$url = $this->lang->line('url');
$encode_vehicle= base64_encode($vehicle_id);
$cht = "qr";
$chs = "100x100";
$chl = $url.'/travel_feedback.php?feed_id='.$encode_vehicle;
$choe = "UTF-8";
$qrcode = 'https://chart.googleapis.com/chart?cht='. $cht . '&chs=' . $chs . '&chl=' . $chl . '&choe=' . $choe;
$qr_img = "<img id='final_qr' src='$qrcode'/>";
$veh_tr_line .= "<tr><td>$vehicle_no</td><td style='white-space: nowrap'>$lastseen</td><td >$speed</td><td >$overspeed</td><td style='text-align:left;'>$place</td><td>$qr_img<br/><a class='button' href='./dashboard/download.php?url=$chl'><i class='fa fa-download'></i></a></td></tr>";
}
}
echo "<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>Admin Dashboard</h3>
<div class='row mg0 mgb15'>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Enquiry'>
<div class='dashbox orange'>
<table>
<tr>
<td class='pd10'><i class='fa fa-heartbeat fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Total Enquiry</h5> <span class='bold'>$enquiry_count</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Followup / Hot'>
<div class='dashbox red'>
<table>
<tr>
<td class='pd10'><i class='fa fa-users fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Followup / Hot</h5> <span class='bold'> $enquiry_sts_count/$enquiry_hot_count</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Booking Confirmed / Travel this Month'>
<div class='dashbox blue'>
<table>
<tr>
<td class='pd04'><i class='fa fa-ticket fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Booking Confirmed / TTM</h5> <span class='bold'>$booking_confirm_count/$booking_travel_count</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Last Month Booking / Travel this Month'>
<div class='dashbox violet'>
<table>
<tr>
<td class='pd10'><i class='fa fa-thermometer-half fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Last Month Booking / TTM</h5> <span class='bold'> $last_booking_confirm_count/$booking_travel_count</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Days to go'>
<div class='dashbox teal'>
<table>
<tr>
<td class='pd10'><i class='fa fa-hourglass-half fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Days</h5> <span class='bold'> $DaysRemaining</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Customer'>
<div class='dashbox yellow'>
<table>
<tr>
<td class='pd10'><i class='fa fa-user fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Total Customer</h5> <span class='bold'> $total_customer_count</span></td>
</tr>
</table>
</div>
</div>
</div>
</div>
<div class='row mg0'>
<div class='col-md-6'>
<div class='row mg0'>
<div class='col-md-12 pdl0'>
<h5>Ticket Enquiry Report</h5>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Enquiry Status</th>
<th>Follow up</th>
<th>Confirmed</th>
<th>Cancelled</th>
</tr>
</thead>
<tbody>
<tr>
<td >Today</td>
<td class='cursor' onclick=get_enquire_follow('$today_date');>$today_crdate_follow</td>
<td class='cursor' onclick=get_enquire_confirm('$today_date');>$today_crdate_confirm</td>
<td class='cursor' onclick=get_enquire_cancel('$today_date');>$today_crdate_cancel</td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_enquire_follow('$cur_month');>$month_crdate_follow</td>
<td class='cursor' onclick=get_enquire_confirm('$cur_month');>$month_crdate_confirm</td>
<td class='cursor' onclick=get_enquire_cancel('$cur_month');>$month_crdate_cancel</td>
</tr>
<tr>
<td >Year</td>
<td class='cursor' onclick=get_enquire_follow('$cur_year');>$year_crdate_follow</td>
<td class='cursor' onclick=get_enquire_confirm('$cur_year');>$year_crdate_confirm</td>
<td class='cursor' onclick=get_enquire_cancel('$cur_year');>$year_crdate_cancel</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class='col-md-6'>
<div class='row mg0'>
<div class='col-md-12 pdl0'>
<h5>Ticket Booking Report</h5>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Booking Status</th>
<th>Created</th>
<th>Follow up</th>
<th>Confirmed</th>
<th>Cancelled</th>
</tr>
</thead>
<tbody>
<tr>
<td >Today</td>
<td class='cursor' onclick=get_booking_created('$today_date');>$cr_today_booking_count</td>
<td class='cursor' onclick=get_booking_follow('$today_date');>$today_crdate_book_follow</td>
<td class='cursor' onclick=get_booking_confirm('$today_date');>$today_crdate_book_confirm</td>
<td class='cursor' onclick=get_booking_cancel('$today_date');>$today_crdate_book_cancel</td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_booking_created('$cur_month');>$cr_month_booking_count</td>
<td class='cursor' onclick=get_booking_follow('$cur_month');>$month_crdate_book_follow</td>
<td class='cursor' onclick=get_booking_confirm('$cur_month');>$month_crdate_book_confirm</td>
<td class='cursor' onclick=get_booking_cancel('$cur_month');>$month_crdate_book_cancel</td>
</tr>
<tr>
<td >Year</td>
<td class='cursor' onclick=get_booking_created('$cur_year');>$cr_year_booking_count</td>
<td class='cursor' onclick=get_booking_follow('$cur_year');>$year_crdate_book_follow</td>
<td class='cursor' onclick=get_booking_confirm('$cur_year');>$year_crdate_book_confirm</td>
<td class='cursor' onclick=get_booking_cancel('$cur_year');>$year_crdate_book_cancel</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class='col-md-6'>
<div class='row mg0'>
<div class='col-md-12 pdl0'>
<h5>Vehicle Maintenance Report</h5>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Vehicle Name</th>
<th>Maintenance Type</th>
<th>Service Date</th>
<th>Delivery Date</th>
<th>Service Center Name</th>
</tr>
</thead>
<tbody>
$tr_line1
</tbody>
</table>
</div>
</div>
</div>
<div class='col-md-6'>
<div class='row mg0'>
<div class='col-md-12 pdl0'>
<h5>Vehicle Assign Report</h5>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Total</th>
<th>Assigned</th>
<th>Pending</th>
<th>Cancelled</th>
</tr>
</thead>
<tbody>
<tr>
<td class='left cursor' onclick=get_vehicle_total_info('$cur_month');>$vehicle_total_count</td>
<td class='cursor' onclick=get_vehicle_assign('$cur_month');>$vehicle_assign_count</td>
<td class='cursor' onclick=get_vehicle_pending('$cur_month');>$vehicle_pending_count</td>
<td class='cursor' onclick=get_vehicle_cancel('$cur_month');>$vehicle_cancel_count</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
</div>
<div class='row mg0'>
<div class='col-md-8'>
<div class='row mg0'>
<div class='col-md-12 pdl0' style='height: 320px; overflow: auto;'>
<h5>Vehicle Track Record</h5>
<table id='veh_track' class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Vehicle Name</th>
<th>Last Seen</th>
<th>Speed</th>
<th>Over Speed</th>
<th style='width:50px'>Place</th>
<th>QR Code</th>
</tr>
</thead>
<tbody id='tbody'>
$veh_tr_line
</tbody>
</table>
</div>
</div>
</div>
<div class='col-md-4'>
<div class='row mg0'>
<div class='col-md-12 pdl0' style='height: 320px; overflow: auto;'>
<h5>Driver License Report</h5>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Driver Name</th>
<th>License Expire Date</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>
</div>
</div>
</div>
</div>";
?>