<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Secure_Controller.php");
class Suspense_report  extends Secure_Controller{ 
	public function __construct(){
		parent::__construct('suspense_report');
		$this->load->model('Suspense_report_model');
	}
	public function index(){
		if(!$this->Appconfig->isAppvalid()){
			redirect('config');
		}
		$data['table_headers'] = $this->xss_clean(get_suspense_report_headers());
		$this->load->view('suspense_report/manage',$data);
	}
	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'));
		
		$trip_wise=$this->Suspense_report_model->search($search,$filters,$limit,$offset,$sort,$order);
		$TotalRows=$this->Suspense_report_model->get_found_rows($search,$filters);
		$DataRows=array();

		foreach ($trip_wise->result() as $person){	
			$DataRows[]=get_suspense_report_datarows($person,$this);
		}
		$DataRows=$this->xss_clean($DataRows);
		echo json_encode(array('total'=>$TotalRows,'rows'=>$DataRows));
	}
		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', 'Date')
		 ->setCellValue('C1', 'Vehicle No')
		 ->setCellValue('D1', 'Party Name')
		 ->setCellValue('E1', 'Destination')
		 ->setCellValue('F1', 'Driver Name')
		 ->setCellValue('G1', 'From Date')
		 ->setCellValue('H1', 'To Date')
		 ->setCellValue('I1', 'Days')
		 ->setCellValue('J1', 'Suspenses')
		 ->setCellValue('K1', 'Expenses')
		 ->setCellValue('L1', 'Cash Settlement')
		 ->setCellValue('M1', 'Balance')
		 ->setCellValue('N1', 'Tour No');

		 // set database value
		$data = $this->Suspense_report_model->get_excel_data($start_date,$end_date);
		$i=2;
		$j=1;
		foreach ($data as $key => $value) {
			$serial    = $j;
			$from_date  = date('d-m-Y', strtotime($value['from_date']));
			$to_date  = date('d-m-Y', strtotime($value['to_date']));
			$operation_id     = $value['operation_id'];
			$amt_receive_info = $this->Suspense_report_model->get_received_amt($operation_id);
			$amt_settled = $amt_receive_info->suspence_amt;
			$name     = $value['customer_name'];
			$tour_no      = $value['tour_no'];
			$vehicle_no      = $value['vehicle_no'];
			$op_driver_name      = $value['op_driver_name'];
			$tot_amt      = $value['tot_amt'];
			$expense_amt      = $value['expense_amt'];
			$trip_days      = $value['trip_days'];
			$suspence_amt      = $value['suspence_amt'];
			$first_name      = $value['first_name'];
			$to_city_name = $value['to_city_name'];
			$driver_suspence_date = date('d-m-Y', strtotime($value['driver_suspence_date']));
			$bal_amt     = (int)$tot_amt - (int)$expense_amt;
			$bal_amt     = (int)$bal_amt - (int)$amt_settled;
			//$driver_name = $person->first_name;
			if(!$first_name){
				$first_name = "Other Driver";
			}
			$tot_sum_amt      += $tot_amt;
			$tot_sum_exp      += $expense_amt;
			$tot_sum_bal      += $bal_amt;
			$tot_amt_settled  += $amt_settled;
		$objWorkSheet->setCellValue('A'.$i, $serial)
		->setCellValue('B'.$i, $driver_suspence_date)
		->setCellValue('C'.$i, $vehicle_no)
		->setCellValue('D'.$i, $name)
		->setCellValue('E'.$i, $to_city_name)
		->setCellValue('F'.$i, $first_name)
		->setCellValue('G'.$i, $from_date)
		->setCellValue('H'.$i, $to_date)
		->setCellValue('I'.$i, $trip_days)
		->setCellValue('J'.$i, $tot_amt)
		->setCellValue('K'.$i, $expense_amt)
		->setCellValue('L'.$i, $amt_settled)
		->setCellValue('M'.$i, $bal_amt)
		->setCellValue('N'.$i, $tour_no);
			$i++;
			$j++;
		
		}
		$objWorkSheet->setCellValue('J'.$i, $tot_sum_amt);
		$objWorkSheet->setCellValue('K'.$i, $tot_sum_exp);
		$objWorkSheet->setCellValue('L'.$i, $tot_amt_settled);
		$objWorkSheet->setCellValue('M'.$i, $tot_sum_bal);
		  // Rename sheet
		 $objWorkSheet->setTitle("Detailed");
		 $obj->setActiveSheetIndex(0);

		 $filename="Suspense 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');
	}
}
?>