File: //proc/self/root/home/cafsindia/ntc_cafsinfotech_in/application/controllers/Home.php
<?php
/**********************************************************
Filename: Home
Description: Chart view and Chart control logic developed, highchart integration based on role.
Author: Jaffer Sathik
Created on: 10-DEC-2018
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 get_vehicle_piechart(){
$this->db->select('prime_vehicle_category_id,vehicle_category');
$this->db->from('vehicle_category');
//$this->db->join('vehicle_category', 'cw_vehicle_category.prime_vehicle_category_id = vehicle_master.vehicle_category','inner');
//$this->db->join('vehicle_type', 'cw_vehicle_type.prime_vehicle_type_id = cw_vehicle_master.vehicle_type','inner');
$vehicle_category = $this->db->get()->result();
$vehicle_category_info = array();
$vehicle_type_info = array();
foreach ($vehicle_category as $value) {
$vehicle_categ['data'][]=array('name' => $value->vehicle_category,'y' => $value->prime_vehicle_category_id,'drilldown' => $value->vehicle_category);
$this->db->select('prime_vehicle_type_id,vehicle_type');
$this->db->from('vehicle_type');
$this->db->where('vehicle_category',$value->prime_vehicle_category_id);
$rlst_2 = $this->db->get()->result();
$vehicle_type_rlst = array();
$vehicle_type_rlst['name'] = $value->vehicle_category;
$vehicle_type_rlst['id'] = $value->vehicle_category;
foreach($rlst_2 as $values){
$vehicle_type_name = trim($values->vehicle_type);
$prime_vehicle_type_id = $values->prime_vehicle_type_id;
$vehicle_type_rlst['data'][]=array('name' => $vehicle_type_name,'y' => $prime_vehicle_type_id,'drilldown' => $vehicle_type_name);
array_push($vehicle_type_info,$vehicle_type_rlst);
$this->db->select('register_no,reg_no,year_model');
$this->db->from('cw_vehicle_master');
$this->db->where('vehicle_type',$value->prime_vehicle_type_id);
$rlst_3 = $this->db->get()->result();
$vehicle_master_rlst = array();
$vehicle_master_rlst['name'] = $vehicle_type_name;
$vehicle_master_rlst['id'] = $vehicle_type_name;
foreach($rlst_3 as $values){
$register_no = $values->register_no;
$year_model = $values->year_model;
$vehicle_master_rlst['data'][]=array($register_no,$year_model);
array_push($vehicle_type_info,$vehicle_master_rlst);
}
}
}
$vehicle_category_info[] = $vehicle_categ;
echo json_encode(array('series' => $vehicle_category_info, 'drill_list' => $vehicle_type_info, 'drill' => $vehicle_type_rlst),JSON_NUMERIC_CHECK);
}
//FUNCTION FOR CUSTOMER LOAD PIECHART
public function get_load_customer_piechart(){
$start_date = $this->input->post('from_date');
$end_date = $this->input->post('to_date');
$from_date = date("Y-m-d", strtotime($start_date));
$to_date = date("Y-m-d", strtotime($end_date));
$today = date("Y-m-d");
$load_customer_query='SELECT cw_ntc_load.load_customer_type,cw_load_customer_type.load_customer_type AS customer_name,COUNT(cw_ntc_load.load_customer_type)AS load_count FROM cw_ntc_load
INNER JOIN cw_load_customer_type ON cw_load_customer_type.prime_load_customer_type_id=cw_ntc_load.load_customer_type
WHERE cw_ntc_load.trans_status=1 AND cw_ntc_load.from_date BETWEEN "'.$from_date.'" AND "'.$to_date.'" GROUP BY cw_ntc_load.load_customer_type
';
$customer_data = $this->db->query("CALL sp_a_run ('SELECT','$load_customer_query')");
$customer_load_rslt = $customer_data->result();
$customer_data->next_result();
$rows = array();
$trip_info = array();
$i = 0;
if($customer_load_rslt){
foreach ($customer_load_rslt as $value){
$rslt['data'][]=array('name' => $value->customer_name,'y' => $value->load_count,'drilldown' => $value->customer_name);
$trip_sts_qry ='SELECT cw_ntc_load.load_customer_type,cw_ntc_load.trip_status,cw_load_customer_type.load_customer_type AS customer_name,COUNT( cw_ntc_load.trip_status) AS status_count,CASE WHEN cw_ntc_load.trip_status = 1 THEN "Schedule" WHEN cw_ntc_load.trip_status = 2 THEN "confirm" WHEN cw_ntc_load.trip_status = 3 THEN "onprogress" WHEN cw_ntc_load.trip_status =4 THEN "cancelled" WHEN cw_ntc_load.trip_status =5 THEN "completed" WHEN cw_ntc_load.trip_status =6 THEN "incompleted" END AS trip_status_name FROM cw_ntc_load INNER JOIN cw_load_customer_type ON cw_load_customer_type.prime_load_customer_type_id=cw_ntc_load.load_customer_type WHERE cw_ntc_load.trans_status=1 AND cw_ntc_load.from_date BETWEEN "'.$from_date.'" AND "'.$to_date.'" and cw_load_customer_type.load_customer_type = "'.$value->customer_name.'" GROUP BY cw_ntc_load.trip_status';
$trip_sts_data = $this->db->query("CALL sp_a_run ('SELECT','$trip_sts_qry') ");
$trip_sts_rslt = $trip_sts_data->result();
$trip_sts_data->next_result();
$cust_rlst_arr = array();
$cust_rlst_arr['name'] = $value->customer_name;
$cust_rlst_arr['id'] = $value->customer_name;
foreach($trip_sts_rslt as $values){
$sts_name =$values->trip_status_name;
$customer_name= $values->customer_name;
$trip_sts = $values->status_count;
$cust_type = $values->load_customer_type;
$cust_rlst_arr['data'][]=array($sts_name,$trip_sts);
array_push($trip_info,$cust_rlst_arr);
}
$i++;
}
array_push($rows,$rslt);
}
// else{
// $com_data_qry='SELECT DISTINCT cw_ntc_load.load_customer_type,cw_load_customer_type.load_customer_type as customer_name,0 as com_count FROM cw_ntc_load INNER JOIN cw_load_customer_type ON cw_load_customer_type.prime_load_customer_type_id=cw_ntc_load.load_customer_type WHERE cw_ntc_load.trans_status=1';
// $com_data = $this->db->query("CALL sp_a_run ('SELECT','$com_data_qry')");
// $comp_data_rslt = $com_data->result();
// $com_data->next_result();
// foreach ($comp_data_rslt as $value) {
// $cust_rlst_arr['data'][]=array('name' => $value->customer_name,'y' => $value->com_count,'drilldown' => $value->customer_name);
// $cust_rlst_arr['name'] = $value->customer_name;
// $cust_id = $value->com_count;
// $cust_rlst_arr['id'] = $value->customer_name;
// }
// array_push($rows,$cust_rlst_arr);
// }
echo json_encode(array('series' => $rows,'drill_list' => $trip_info),JSON_NUMERIC_CHECK);
}
public function get_dashboard_data(){
$from_date = date("Y-m-01");
$active_load_query='SELECT COUNT(*)AS active_load FROM cw_ntc_load WHERE trip_status=2 and from_date="'.$from_date.'" AND trans_status=1';
$dashboard_data = $this->db->query("CALL sp_a_run ('SELECT','$active_load_query')");
$active_load_rslt = $dashboard_data->result_array();
$dashboard_data->next_result();
$completed_trip_query='SELECT COUNT(*) AS completed_trips FROM cw_ntc_load WHERE trip_status not in(6,4) and from_date="'.$from_date.'" and trans_status=1';
$completed_data = $this->db->query("CALL sp_a_run ('SELECT','$completed_trip_query')");
$completed_load_rslt = $completed_data->result_array();
$completed_data->next_result();
$load_weight_qry='SELECT ifnull(SUM(weight),0)AS load_weight FROM cw_enquiry WHERE trans_status=1 and pickup_date="'.$from_date.'"';
$load_weight_data = $this->db->query("CALL sp_a_run ('SELECT','$load_weight_qry')");
$load_weight_rslt = $load_weight_data->result_array();
$load_weight_data->next_result();
$customer_load_qry='SELECT COUNT(*) AS customer_load FROM cw_ntc_load WHERE trip_status=5 AND trans_status=1 and from_date="'.$from_date.'"';
$customer_load_data = $this->db->query("CALL sp_a_run ('SELECT','$customer_load_qry')");
$customer_load_rslt = $customer_load_data->result_array();
$customer_load_data->next_result();
$diesel_qry = 'SELECT IFNULL(SUM(litre),0)AS diesel FROM cw_fuel_request WHERE trans_status=1 and date="'.$from_date.'"';
$diesel_data = $this->db->query("CALL sp_a_run ('SELECT','$diesel_qry')");
$diesel_rslt = $diesel_data->result_array();
$diesel_data->next_result();
$tot_vehicle_qry='SELECT DISTINCT COUNT(*)as vehicle_count FROM cw_vehicle_master WHERE trans_status=1';
$vehicle_data = $this->db->query("CALL sp_a_run ('SELECT','$tot_vehicle_qry')");
$vehicle_rslt = $vehicle_data->result_array();
$vehicle_data->next_result();
echo json_encode(array("success" => TRUE,'active_load' => $active_load_rslt,'completed_load'=>$completed_load_rslt,'weight_load'=>$load_weight_rslt,'customer_load'=>$customer_load_rslt,'diesel'=>$diesel_rslt,'vehicle'=>$vehicle_rslt));
}
public function table_search_data(){
$start_date = $this->input->post('from_date');
$end_date = $this->input->post('to_date');
$from_date = date("Y-m-d", strtotime($start_date));
$to_date = date("Y-m-d", strtotime($end_date));
$driver_license_qry = 'SELECT employee_name,license_expiry,employee_code FROM cw_employees WHERE trans_status=1 AND ROLE=4 AND license_expiry BETWEEN "'.$from_date.'" AND "'.$to_date.'"';
$driver_license_data = $this->db->query("CALL sp_a_run ('SELECT','$driver_license_qry')");
$driver_expiry_rslt = $driver_license_data->result_array();
$driver_license_data->next_result();
$driver_tr_line ='';
foreach($driver_expiry_rslt as $licese_value){
$emp_name = $licese_value['employee_name'];
$emp_code = $licese_value['employee_code'];
$expiry_date = $licese_value['license_expiry'];
$driver_tr_line .= "<tr><td style='text-align:center;'>".$emp_name."</td><td style='text-align:center;'>".$emp_code."</td><td style='text-align:center;'>".$expiry_date."</td></tr>";
}
if($driver_tr_line === ""){
$driver_tr_line = "<tr><td></td><td>No Data Available</td><td></td></tr>";
}
$licence_expiry_table="<h4 style='margin-left:20px;'>Employee License Expiry Information</h4><table class='table table-striped table-bordered' id='emp_license'><thead><tr><th style='text-align:center;'>Employee Name</th><th style='text-align:center;'>Employee Code</th><th style='text-align:center;'>License expiry date</th></tr></thead><tbody>".$driver_tr_line."</tbody></table>";
$vehicle_info_qry='SELECT cw_vehicle_master.register_no,cw_vehicle_master.purchase_date,cw_vehicle_master.vehicle_licence_expiry,cw_vehicle_master.year_model,
cw_vehicle_category.vehicle_category,cw_vehicle_type.vehicle_type FROM cw_vehicle_master
INNER JOIN cw_vehicle_category ON cw_vehicle_category.prime_vehicle_category_id = cw_vehicle_master.vehicle_category
INNER JOIN cw_vehicle_type ON cw_vehicle_type.prime_vehicle_type_id = cw_vehicle_master.vehicle_type WHERE cw_vehicle_master.trans_status=1 and cw_vehicle_master.vehicle_licence_expiry BETWEEN "'.$from_date.'" AND "'.$to_date.'"';
$vehicle_info = $this->db->query("CALL sp_a_run ('SELECT','$vehicle_info_qry')");
$vehicle_info_rslt = $vehicle_info->result_array();
$vehicle_info->next_result();
$vehicle_tr_line ="";
foreach($vehicle_info_rslt as $vehicle_info){
$reg_no = $vehicle_info['register_no'];
$purcase_date = $vehicle_info['purchase_date'];
$license_expiry = $vehicle_info['vehicle_licence_expiry'];
$model_year = $vehicle_info['year_model'];
$vehicle_category = $vehicle_info['vehicle_category'];
$vehicle_type = $vehicle_info['vehicle_type'];
$vehicle_tr_line .="<tr><td style='text-align:center;'>".$reg_no."</td><td style='text-align:center;'>".$vehicle_category."</td><td style='text-align:center;'>".$vehicle_type."</td><td style='text-align:center;'>".$model_year."</td><td style='text-align:center;'>".$purcase_date."</td><td style='text-align:center;'>".$license_expiry."</td></tr>";
}
if($vehicle_tr_line === ""){
$vehicle_tr_line = "<tr><td></td><td></td><td></td><td>No Data Available</td><td></td></tr>";
}
$vehicle_info_table ="<h4 style='margin-left:20px;'>Vehicle Information</h4><table class='table table-striped table-bordered' id='vehicle_info'><thead><tr><th style='text-align:center;'>Register Number</th><th style='text-align:center;'>Category</th><th style='text-align:center;'>Type</th><th style='text-align:center;'>Model year</th><th style='text-align:center;'>Purchase Date</th><th style='text-align:center;'>License Expiry</th></tr></thead><tbody>".$vehicle_tr_line."</tbody></table>";
echo json_encode(array("success" => TRUE,'driver_licence' => $licence_expiry_table,'Vehicle_info'=>$vehicle_info_table));
}
}
?>