<?php error_reporting(0);
set_time_limit(0);
if (substr_count($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip')) ob_start("ob_gzhandler"); else ob_start();
session_start();
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Manila');
if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser'); 
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php'; 
$objReader = PHPExcel_IOFactory::createReader('Excel5');
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
include_once('../config/minho.php');
include_once('../config/autoloadClass.php');

$autoload	=	new autoloadClass();
$mecon		=	new condbClass();
$main		=	array();
$main2		=	array();
$main3		=	array();
$main4		=	array();

$monthFrom = isset($_GET['month_from']) ? strip_tags($_GET['month_from']) : "";
$monthTo = isset($_GET['month_to']) ? strip_tags($_GET['month_to']) : "";
$filterSearch = isset($_GET['filter']) ? strip_tags($_GET['filter']) : "";

$rangeStart = isset($_REQUEST['range_start']) ?  $_REQUEST['range_start'] : date('Y-m-d', strtotime(date('01-m-Y')));
$rangeEnd 	= isset($_REQUEST['range_end'])   ? $_REQUEST['range_end']	  : date("Y-m-t", strtotime(date('Y-m-d')));

$tobedate 	= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "yes"; 
  
$resultQuery = "SELECT Year(r.ReservationDate) as ryear, r.Broker as Broker, r.Id as Id,  Count(case when month(r.ReservationDate)=1 then Id end) As January, Count(case when month(r.ReservationDate)=2 then Id end) As February, Count(case when month(r.ReservationDate)=3 then Id end) As March, Count(case when month(r.ReservationDate)=4 then Id end) As April, Count(case when month(r.ReservationDate)=5 then Id end) As May,  Count(case when month(r.ReservationDate)=6 then Id end) As June, Count(case when month(r.ReservationDate)=7 then Id end) As July, Count(case when month(r.ReservationDate)=8 then Id end) As August, Count(case when month(r.ReservationDate)=9 then Id end) As September, Count(case when month(r.ReservationDate)=10 then Id end) As October, Count(case when month(r.ReservationDate)=11 then Id end) As November, Count(case when month(r.ReservationDate)=12 then Id end) As December, COUNT(*) AS YTD, SUM(bpm.TCP) as Total_TCP, SUM(bpm.Discount) as Total_Discount, SUM(bpm.TCP - bpm.Discount) as Net_TCP, r.Status, r.ReservationStatus as ReservationStatus FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id ";
   
$sql			=	$resultQuery."   WHERE r.Status = 'active' AND bpm.TCP IS NOT NULL "; 

 
if(!empty($filterSearch) ) {
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql.=" AND ( YEAR(r.ReservationDate) LIKE '%".$ind."%' ";  
		$sql.=" OR (SELECT COUNT(*) FROM reservation) LIKE '%".$ind."%' ";
		$sql.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
		$sql.=" OR r.Broker LIKE '%".htmlentities($ind)."%' ) ";   
	}
}

if($tobedate=="yes"){
		if( !empty($monthFrom) && !empty($monthTo) ){
			$sql .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
		}else if( !empty($monthFrom)){
			$sql .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
		}else if( !empty($monthTo)){
			$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
		}
 
} 

$sql 	.= " GROUP BY Year(r.ReservationDate), r.Broker, r.ReservationStatus "; 
$sql  	.= " ORDER BY r.Broker asc, r.ReservationStatus asc, Year(r.ReservationDate) desc "; 

$mecon->query($sql);
$main = $mecon->resultset(); 
$counter = count($main); 
$arr1 = array();
$arr2 = array();  
$arr3 = array();  

for($a=0; $a<$counter; $a++){   
		$arr1[0]	= trim(strtoupper(stripslashes(html_entity_decode($main[$a]['Broker']))));
		$arr1[1]	= trim(strtoupper(stripslashes(html_entity_decode($main[$a]['ReservationStatus'])))); 
		$arr1[2]    = stripslashes(html_entity_decode($main[$a]['ryear']));
		$arr1[3]    = trim($main[$a]['January']);
		$arr1[4]    = trim($main[$a]['February']);
		$arr1[5]    = trim($main[$a]['March']);
		$arr1[6]    = trim($main[$a]['April']);
		$arr1[7]    = trim($main[$a]['May']);
		$arr1[8]    = trim($main[$a]['June']);
		$arr1[9]    = trim($main[$a]['July']);
		$arr1[10]    = trim($main[$a]['August']);
		$arr1[11]    = trim($main[$a]['September']);
		$arr1[12]    = trim($main[$a]['October']);
		$arr1[13]    = trim($main[$a]['November']);
		$arr1[14]    = trim($main[$a]['December']);
		$arr1[15]    = trim($main[$a]['YTD']);
		$arr1[16]    = (isset($main[$a]['Net_TCP'])) ? number_format(trim($main[$a]['Net_TCP']),2) : 0.00;
   
   	$arr2[] 	= $arr1;
}
$arr3['data'] = $arr2;
 
 
$fileName    = "sales_report_per_broker.xls";
$objPHPExcel = $objReader->load("templates/sales_report_per_broker.xls"); 
$baseRow = 3;
  
 $objPHPExcel->getActiveSheet()->mergeCells('P1:Q1');
 $objPHPExcel->getActiveSheet()->setCellValue('P3', '#');
 $objPHPExcel->getActiveSheet()->setCellValue('Q3', 'Amount');
 $objPHPExcel->getActiveSheet()->getStyle("P3:Q3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
 
 for($a=0; $a<count($arr3['data']); $a++){ 
	$row = $baseRow + $a + 1;
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1); 
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $arr3['data'][$a][0])
											  ->setCellValue('B'.$row, $arr3['data'][$a][1])
											  ->setCellValue('C'.$row, $arr3['data'][$a][2])
											  ->setCellValue('D'.$row, $arr3['data'][$a][3])
											  ->setCellValue('E'.$row, $arr3['data'][$a][4])
											  ->setCellValue('F'.$row, $arr3['data'][$a][5])
											  ->setCellValue('G'.$row, $arr3['data'][$a][6])
											  ->setCellValue('H'.$row, $arr3['data'][$a][7])
											  ->setCellValue('I'.$row, $arr3['data'][$a][8])
											  ->setCellValue('J'.$row, $arr3['data'][$a][9])
											  ->setCellValue('K'.$row, $arr3['data'][$a][10])
											  ->setCellValue('L'.$row, $arr3['data'][$a][11])
											  ->setCellValue('M'.$row, $arr3['data'][$a][12])
											  ->setCellValue('N'.$row, $arr3['data'][$a][13])
											  ->setCellValue('O'.$row, $arr3['data'][$a][14])
											  ->setCellValue('P'.$row, $arr3['data'][$a][15])
											  ->setCellValue('Q'.$row, $arr3['data'][$a][16]); 
}  


 $objPHPExcel->getActiveSheet()->getStyle("C3:Q10000")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
 
 $objPHPExcel->getActiveSheet()->getStyle("P3:Q3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
$tcounter = $counter + 3;
$objPHPExcel->getActiveSheet()->getStyle('A3:Q'.$tcounter)->applyFromArray($styleArray);


$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$fileName.'"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
//header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
//header ('Pragma: public'); // HTTP/1.0

header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1.
header("Pragma: no-cache"); // HTTP 1.0.
header("Expires: 0"); // Proxies.


$objWriter->save('php://output'); 