File: //home/cafsindia/allyindian_com/sbltt/application/controllers/Op_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once("Secure_Controller.php");
class Op_report extends Secure_Controller{
public function __construct(){
parent::__construct('op_report');
$this->load->model('Op_report_model');
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$data['table_headers'] = $this->xss_clean(get_Op_report_table_headers());
$this->load->view('op_report/manage', $data);
}
/*
Returns Supplier table data rows. This will be called with AJAX.
*/
public function search(){
$search = $this->input->get('search');
$limit = $this->input->get('limit');
$offset = $this->input->get('offset');
$sort = $this->input->get('sort');
$order = $this->input->get('order');
$filters = array('start_date' => $this->input->get('start_date'),
'end_date' => $this->input->get('end_date'));
$all_leads = $this->Op_report_model->search($search, $filters, $limit, $offset, $sort, $order);
$total_rows = $this->Op_report_model->get_found_rows($search, $filters);
$data_rows = array();
foreach($all_leads->result() as $leads){
$data_rows[] = get_Op_report_data_row($leads, $this);
}
echo json_encode(array('total' => $total_rows, 'rows' => $data_rows));
}
//excel generation for operation report
public function excel($start_date,$end_date){
$this->load->library('excel');
$obj = new Excel();
// Add new sheet
$objWorkSheet = $obj->createSheet(0); //Setting index when creating
$obj->setActiveSheetIndex(0); //First sheet open in excel sheet
//Write cells head column name
$objWorkSheet->setCellValue('A1', 'Si.No')
->setCellValue('B1', 'Tour No')
->setCellValue('C1', 'Customer Name')
->setCellValue('D1', 'Billing Name')
->setCellValue('E1', 'Customer Mobile')
->setCellValue('F1', 'From Date')
->setCellValue('G1', 'To Date')
->setCellValue('H1', 'Trip Days')
->setCellValue('I1', 'Bus Count')
->setCellValue('J1', 'Trip Type')
->setCellValue('K1', 'Origin')
->setCellValue('L1', 'Destination')
->setCellValue('M1', 'Reporting Time')
->setCellValue('N1', 'Vehicle Type')
->setCellValue('O1', 'Source Of vehicle')
->setCellValue('P1', 'Vehicle No')
->setCellValue('Q1', 'Driver Name')
->setCellValue('R1', 'Driver Mob No')
->setCellValue('S1', 'Starting Km')
->setCellValue('T1', 'Closing Km')
->setCellValue('U1', 'Total Km')
->setCellValue('V1', 'Avg KM / Coverage Km')
->setCellValue('W1', 'Extra Km')
->setCellValue('X1', 'Per Km')
->setCellValue('Y1', 'Total Extra Km Amount')
->setCellValue('Z1', 'Booking Amount')
->setCellValue('AA1', 'Receipt')
->setCellValue('AB1', 'Discount Amount')
->setCellValue('AC1', 'Balance Amount')
->setCellValue('AD1', 'Hire Charges')
->setCellValue('AE1', 'Tp info')
->setCellValue('AF1', 'Driver Suspense')
->setCellValue('AG1', 'Add Suspense')
->setCellValue('AH1', 'Total Suspense')
->setCellValue('AI1', 'Toll')
->setCellValue('AJ1', 'Parking')
->setCellValue('AK1', 'Kickbacks')
->setCellValue('AL1', 'Rto')
->setCellValue('AM1', 'Driver Adv')
->setCellValue('AN1', 'Cleaner Adv')
->setCellValue('AO1', 'Others')
->setCellValue('AP1', 'Balance Received')
->setCellValue('AQ1', 'Diesel Exp')
->setCellValue('AR1', 'Balance Amount')
->setCellValue('AS1', 'Trip Information')
->setCellValue('AT1', 'Remarks')
->setCellValue('AU1', 'Ref By')
->setCellValue('AV1', 'Customer Type')
->setCellValue('AW1', 'Ops Type')
->setCellValue('AX1', 'Status');
// set database value
$data = $this->Op_report_model->excel_operation_type_report($start_date,$end_date);
//print_r($data); die;
$i=2;
$k=1;
foreach ($data as $key => $value) {
$serial = $k;
$from_date = date('d-m-Y', strtotime($value['from_date']));
$to_date = date('d-m-Y', strtotime($value['to_date']));
$name = $value['customer_name'];
$mob = $value['phone_number'];
$origin = $value['frm_city_name'];
$destination = $value['to_city_name'];
$trip_days = $value['trip_days'];
$bus_count = $value['bus_count'];
$tour_no = $value['tour_no'];
$report_address = $value['report_address'];
$billing_name = $value['billing_name'];
$avg_km = $value['avg_km'];
$trip_type_name = $value['trip_type_name'];
$veh_type = $value['veh_type'];
$type = $value['booking_type_name'];
$trip_details = $value['trip_details'];
$operation_type = $value['operation_type'];
$booking_amount = $value['booking_amount'];
$advance_amount = $value['advance_amount'];
$booking_id = $value['booking_id'];
$operation_id = $value['operation_id'];
$vehicle_id = $value['vehicle_id'];
//$balance_amount = $value['balance_amount'];
$discount_amount = $value['discount_amount'];
$payment_type = $value['payment_type'];
$op_suspence_amt = $value['op_suspence_amt'];
$hire_charges = $value['hire_charges'];
$op_driver_name = $value['op_driver_name'];
$vehicle_no = $value['vehicle_no'];
$operation_type = $value['operation_type'];
$operation_sts = $value['operation_sts'];
$first_name = $value['first_name'];
$tp_info = $value['tp_info'];
$extra_km = $value['extra_km'];
$extra_amt = $value['extra_amt'];
$op_extra_km = $value['op_extra_km'];
$op_extra_amt = $value['op_extra_amt'];
$reporting_time = $value['reporting_time'];
$vendor_name = $value['vendor_name'];
$op_driver_number = $value['op_driver_number'];
$trip_start_km = $value['trip_start_km'];
$trip_end_km = $value['trip_end_km'];
$op_extra_pre_km = $value['op_extra_pre_km'];
$cust_type = $value['cust_type'];
$trip_end_km = $value['trip_end_km'];
$toll = $value['toll'];
$parking = $value['parking'];
$driver_batta = $value['driver_batta'];
$remarks = $value['remarks'];
$diesel_amt = $value['diesel_amt'];
$rto = $value['rto'];
$trip_details = $value['trip_details'];
$reporting_time = date("G:i", strtotime($reporting_time)).' '.'Hrs';
$balance_amount = $this->Op_report_model->get_total_trip_amount($booking_id);
$toll_amt = $this->Op_report_model->get_suspence_by_vech($operation_id,$vehicle_id,'Toll');
$parking_amt = $this->Op_report_model->get_suspence_by_vech($operation_id,$vehicle_id,'Parking');
$kickbacks_amt = $this->Op_report_model->get_suspence_by_vech($operation_id,$vehicle_id,'Kickbacks');
$rto_amt = $this->Op_report_model->get_suspence_by_vech($operation_id,$vehicle_id,'RTO');
$driver_adv_amt = $this->Op_report_model->get_suspence_by_vech($operation_id,$vehicle_id,'Driver');
$cleaner_adv_amt = $this->Op_report_model->get_suspence_by_vech($operation_id,$vehicle_id,'Cleaner');
$balance_received = $this->Op_report_model->get_suspence_by_vech($operation_id,$vehicle_id,'Balance_Received');
$other_amt = $this->Op_report_model->get_suspence_by_vech($operation_id,$vehicle_id,'Others');
$driver_sus_amt = $this->Op_report_model->get_driver_by_vech($operation_id,$vehicle_id);
/*
if($toll === "1"){
$toll_amt = $this->Op_report_model->get_suspence_amount_mode($booking_id,'Toll');
}
if($parking === "1"){
$parking_amt = $this->Op_report_model->get_suspence_amount_mode($booking_id,'Parking');
}
if($driver_batta === "1"){
$driver_batta_amt = $this->Op_report_model->get_suspence_amount_mode($booking_id,'driver_batta');
}
if($state_tax === "1"){
$state_tax_amt = $this->Op_report_model->get_suspence_amount_mode($booking_id,'TAX');
}
$kickbacks_amt = $this->Op_report_model->get_suspence_amount_mode($booking_id,'Kickbacks');
$kickbacks_amt = $this->get_suspence_amount_mode($booking_id,'Kickbacks');
$driver_sup_amt = $this->get_suspence_amount_mode($booking_id,'Driver');
$cleaner_sup_amt = $this->get_suspence_amount_mode($booking_id,'Cleaner');
$bal_received_amt = $this->get_suspence_amount_mode($booking_id,'Balance_Received');
$rto_amt = $this->get_suspence_amount_mode($booking_id,'RTO');
*/
if($operation_sts === "1"){
$operation_sts = "Follow Up";
}else
if($operation_sts === "2"){
$operation_sts = "Confirm";
}else
if($operation_sts === "3"){
$operation_sts = "Cancelled";
}else
if($operation_sts === "4"){
$operation_sts = "Completed";
}else
if($operation_sts === "5"){
$operation_sts = "On progress";
}
if($operation_type === "1"){
$operation_type = "Vehicle with Driver";
}else
if($operation_type === "2"){
$operation_type = "Driver Only";
}
if($cust_type === "1"){
$cust_type = "Aggregate";
}else
if($cust_type === "2"){
$cust_type = "Corporate";
}else
if($cust_type === "3"){
$cust_type = "Individual";
}else
if($cust_type === "4"){
$cust_type = "Package";
}else
if($cust_type === "5"){
$cust_type = "Travel Agents";
}
$total_extra_km = $op_extra_km * $op_extra_pre_km;
//Total KM Calculation
$total_km = $trip_end_km - $trip_start_km;
//hire charge include all charge stay,catering,booking and extra km amount
$total_hire_charge = $booking_amount + $catering_amount + $stay_tot_amt + $extra_amt;
//gst calculation
$gst_amount=($total_hire_charge*$tax_precentage)/100;
//gst+total charge
$total_inc_gst_amount = $total_hire_charge + $gst_amount;
//receipt amount
$bal = $this->Op_report_model->get_bal_log_amount($booking_id);
$advance_amt = $bal->bal_log_amt;
$receipt = $advance_amount+$advance_amt;
//Total Suspense
$total_susbance_amt = $op_suspence_amt + $driver_sus_amt;
//balance total trip
$total_driver_amt = $toll_amt + $parking_amt + $kickbacks_amt + $rto_amt + $driver_adv_amt + $cleaner_adv_amt + $balance_received + $other_amt;
$total_trip_bal = $total_susbance_amt - $total_driver_amt;
$objWorkSheet->setCellValue('A'.$i, $serial)
->setCellValue('B'.$i, $tour_no)
->setCellValue('C'.$i, $name)
->setCellValue('D'.$i, $billing_name)
->setCellValue('E'.$i, $mob)
->setCellValue('F'.$i, $from_date)
->setCellValue('G'.$i, $to_date)
->setCellValue('H'.$i, $trip_days)
->setCellValue('I'.$i, $bus_count)
->setCellValue('J'.$i, $trip_type_name)
->setCellValue('K'.$i, $origin)
->setCellValue('L'.$i, $destination)
->setCellValue('M'.$i, $reporting_time)
->setCellValue('N'.$i, $veh_type)
->setCellValue('O'.$i, $vendor_name)
->setCellValue('P'.$i, $vehicle_no)
->setCellValue('Q'.$i, $op_driver_name)
->setCellValue('R'.$i, $op_driver_number)
->setCellValue('S'.$i, $trip_start_km)
->setCellValue('T'.$i, $trip_end_km)
->setCellValue('U'.$i, $total_km)
->setCellValue('V'.$i, $avg_km)
->setCellValue('W'.$i, $op_extra_km)
->setCellValue('X'.$i, $op_extra_pre_km)
->setCellValue('Y'.$i, $total_extra_km)
->setCellValue('Z'.$i, $booking_amount)
->setCellValue('AA'.$i, $receipt)
->setCellValue('AB'.$i, $discount_amount)
->setCellValue('AC'.$i, $balance_amount)
->setCellValue('AD'.$i, $total_hire_charge)
->setCellValue('AE'.$i, $tp_info)
->setCellValue('AF'.$i, $op_suspence_amt)
->setCellValue('AG'.$i, $driver_sus_amt)
->setCellValue('AH'.$i, $total_susbance_amt)
->setCellValue('AI'.$i, $toll_amt)
->setCellValue('AJ'.$i, $parking_amt)
->setCellValue('AK'.$i, $kickbacks_amt)
->setCellValue('AL'.$i, $rto_amt)
->setCellValue('AM'.$i, $driver_adv_amt)
->setCellValue('AN'.$i, $cleaner_adv_amt)
->setCellValue('AO'.$i, $other_amt)
->setCellValue('AP'.$i, $balance_received)
->setCellValue('AQ'.$i, $diesel_amt)
->setCellValue('AR'.$i, $total_trip_bal)
->setCellValue('AS'.$i, $trip_details)
->setCellValue('AT'.$i, $remarks)
->setCellValue('AU'.$i, $first_name)
->setCellValue('AV'.$i, $cust_type)
->setCellValue('AW'.$i, $operation_type)
->setCellValue('AX'.$i, $operation_sts);
$k++;
$i++;
}
// Rename worksheet name
$objWorkSheet->setTitle("Detailed");
$filename="Operation Report_".date('d-m-Y').".xls"; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($obj, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
}
}
?>