<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

require_once("Secure_Controller.php");

class Br_report extends Secure_Controller
{
	public function __construct()
	{
		parent::__construct('br_report');
		$this->load->model('Br_report_model');
	}

	public function index()
	{

		if(!$this->Appconfig->isAppvalid()){
			redirect('config');
		}
		 $data['table_headers'] = $this->xss_clean(get_Br_report_table_headers());

		$this->load->view('br_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->Br_report_model->search($search, $filters, $limit, $offset, $sort, $order);
        $total_rows = $this->Br_report_model->get_found_rows($search, $filters);
        $data_rows = array();
        foreach($all_leads->result() as $leads){
            $data_rows[] = get_Br_report_data_row($leads, $this);
        }
        echo json_encode(array('total' => $total_rows, 'rows' => $data_rows));
	}	
	public function excel($start_date,$end_date){
		
	//$filters = array('start_date' => $start_date, 'end_date' => $end_date);
	$this->load->library('excel');
	$obj = new Excel();
	// Add new sheet
	 $objWorkSheet = $obj->createSheet(0); //Setting index when creating
	
	//Write cells
	 $objWorkSheet->setCellValue('A1', 'S.No')
	 ->setCellValue('B1', 'Tour No')
	 ->setCellValue('C1', 'Name')
	 ->setCellValue('D1', 'Billing Name')
	 ->setCellValue('E1', 'Mobile')
	 ->setCellValue('F1', 'Alter Mobile')
	 ->setCellValue('G1', 'Email')
	 ->setCellValue('H1', 'From Date')
	 ->setCellValue('I1', 'To Date')
	 ->setCellValue('J1', 'Orgin')
	 ->setCellValue('K1', 'Destination')
	 ->setCellValue('L1', 'Trip Info')
	 ->setCellValue('M1', 'Trip Days')
	 ->setCellValue('N1', 'Trip Type')
	 ->setCellValue('O1', 'Average Km')
	 ->setCellValue('P1', 'Vehicle Type')
	 ->setCellValue('Q1', 'No Of Vehicles')
	 ->setCellValue('R1', 'Reporting Address')
	 ->setCellValue('S1', 'Reporting Time')
	 ->setCellValue('T1', 'Booking Amount')
	 ->setCellValue('U1', 'Advance Amount')
	 ->setCellValue('V1', 'Discount Amount')
	 ->setCellValue('W1', 'Balance Amount')
	 ->setCellValue('X1', 'Including GST')
	 ->setCellValue('Y1', 'Payment Type')
	 ->setCellValue('Z1', 'Customer Type')
	 ->setCellValue('AA1', 'Custumer Status')
	 ->setCellValue('AB1', 'Tol')
	 ->setCellValue('AC1', 'Parking')
	 ->setCellValue('AD1', 'Driver Batta')
	 ->setCellValue('AE1', 'State Tax')
	 ->setCellValue('AF1', 'Referred By')
	 ->setCellValue('AG1', 'Booking Status')
	 ->setCellValue('AH1', 'Including Food/Accommodation')
	 ->setCellValue('AI1', 'Permit Type')
	 ->setCellValue('AJ1', 'Remarks');

		 // set database value
		$data = $this->Br_report_model->get_excel_data($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']));
			$tour_no        = $value['tour_no'];
			$booking_id     = $value['booking_id'];
			$balance_amount = $this->Br_report_model->get_total_trip_amount($booking_id);
			$name           = $value['customer_name'];
			$mob            = $value['phone_number'];
			$alt_number     = $value['alt_number'];
			$cust_email     = $value['cust_email'];
			$includ_gst     = $value['includ_gst'];
			$cust_sts       = $value['cust_sts'];
			$trip_days      = $value['trip_days'];
			$bus_count      = $value['bus_count'];
			$report_address = $value['report_address'];
			$billing_name   = $value['billing_name'];
			$first_name     = $value['first_name'];
			$avg_km         = $value['avg_km'];
			$trip_type_name = $value['trip_type_name'];
			$veh_type       = $value['veh_type'];
			$toll           = $value['toll'];
			$parking        = $value['parking'];
			$driver_batta   = $value['driver_batta'];
			$state_tax      = $value['state_tax'];
			$type           = $value['booking_type_name'];
			$frm_city_name  = $value['frm_city_name'];
			$to_city_name   = $value['to_city_name'];
			$reporting_time = $value['reporting_time'];
			$trip_details   = $value['trip_details'];
			$booking_sts    = $value['booking_sts'];
			$booking_amount = $value['booking_amount'];			
			$prospect       = $value['prospect'];
			$advance_amount = $value['advance_amount'];
			$discount_amount= $value['discount_amount'];
			$payment_type   = $value['payment_type'];
			$operation_type = $value['operation_type'];
			$catering       = $value['catering'];
			$tp_info        = $value['tp_info'];
			$remark         = $value['remark'];
$advance_received = $this->Br_report_model->get_bal_log_amount($booking_id);
$advance_paid     = $advance_received->bal_log_amt;

$advance_amount   = $advance_paid + $advance_amount;
			//$advance_amount = $balance + $advance_amount;
			//$balance_amount = $booking_amount - $advance_amount;
			$ncd = date('d-m-Y', strtotime($value['ncd']));
			if($booking_sts === "1"){
				$booking_sts = "Follow Up";
			}else
			if($booking_sts === "2"){
				$booking_sts = "Confirm";
			}else
			if($booking_sts === "3"){
				$booking_sts = "Cancelled";
			}else
			if($booking_sts === "4"){
				$booking_sts = "Completed";
			}else
			if($booking_sts === "5"){
				$booking_sts = "On Progress";
			}
			
			if($operation_type === "1"){
				$operation_type = "Vehicle with driver";
			}else
			if($operation_type === "2"){
				$operation_type = "Only Driver";
			}
			
			if($includ_gst === "1"){
				$includ_gst = "Yes";
			}else{
				$includ_gst = "No";
			}
			
			if($toll === "1"){
				$toll = "Yes";
			}else{
				$toll = "No";
			}
			
			if($parking === "1"){
				$parking = "Yes";
			}else{
				$parking = "No";
			}
			
			if($driver_batta === "1"){
				$driver_batta = "Yes";
			}else{
				$driver_batta = "No";
			}
			if($state_tax === "1"){
				$state_tax = "Yes";
			}else{
				$state_tax = "No";
			}
			

		$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, $alt_number)
		->setCellValue('G'.$i, $cust_email)
		->setCellValue('H'.$i, $from_date)
		->setCellValue('I'.$i, $to_date)
		->setCellValue('J'.$i, $frm_city_name)
		->setCellValue('K'.$i, $to_city_name)
		->setCellValue('L'.$i, $trip_details)
		->setCellValue('M'.$i, $trip_days)
		->setCellValue('N'.$i, $trip_type_name)
		->setCellValue('O'.$i, $avg_km)
		->setCellValue('P'.$i, $veh_type)
		->setCellValue('Q'.$i, $bus_count)
		->setCellValue('R'.$i, $report_address)
		->setCellValue('S'.$i, $reporting_time)
		->setCellValue('T'.$i, $booking_amount)
		->setCellValue('U'.$i, $advance_amount)
		->setCellValue('V'.$i, $discount_amount)
		->setCellValue('W'.$i, $balance_amount)
		->setCellValue('X'.$i, $includ_gst)
		->setCellValue('Y'.$i, $payment_type)
		->setCellValue('Z'.$i, $type)
		->setCellValue('AA'.$i, $cust_sts)
		->setCellValue('AB'.$i, $toll)
		->setCellValue('AC'.$i, $parking)
		->setCellValue('AD'.$i, $driver_batta)
		->setCellValue('AE'.$i, $state_tax)
		->setCellValue('AF'.$i, $first_name)
		->setCellValue('AG'.$i, $booking_sts)
		->setCellValue('AH'.$i, $catering)
		->setCellValue('AI'.$i, $tp_info)
		->setCellValue('AJ'.$i, $remark);
		$i++;           
		$k++;           
		}
	
		  // Rename sheet
		 $objWorkSheet->setTitle("Detailed");
		 $obj->setActiveSheetIndex(0);

		 $date = date("Y-m-d H:i:s");
		 
		 $filename="Booking 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');
	}


}
?>