<?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']) ? str_replace("'"," ",$_GET['filter']) : "";
//$filterSearch	= isset($_GET['filter']) ? strip_tags($_GET['filter']) : "";
$isfilterdate = isset($_GET['isfilterdate']) ? strip_tags($_GET['isfilterdate']) : "yes";
  
 $whereCond = "";
if(!empty($filterSearch) ) { 
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);		
		$whereCond.=" AND ( r.Project LIKE '%".$ind."%' ";
		$whereCond.=" OR r.Model LIKE '%".$ind."%' ";
		$whereCond.=" OR r.Block LIKE '%".$ind."%' ";
		$whereCond.=" OR r.Lot LIKE '%".$ind."%' ";
		$whereCond.=" OR r.Broker LIKE '%".$ind."%' ";
		$whereCond.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$whereCond.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ) ";   
	}
}

if($isfilterdate=="yes"){
	if( !empty($monthFrom) && !empty($monthTo) ){
		$whereCond .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
	}else if( !empty($monthFrom)){
		$whereCond .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
	}else if( !empty($monthTo)){
		$whereCond .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
	}
}
/*
$sql 		= "(SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Broker as Broker, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.ReservationStatus, r.Project, r.Model,
rb.FirstReleaseAmount as ReleaseAmount, rb.FirstReleaseType as ReleaseType, rb.FirstReleaseWHT as ReleaseWHT, rb.FirstReleaseDate as ReleaseDate, rb.ReleasesStatus as ReleasesStatus FROM reservation r LEFT JOIN releases_to_broker_for_wht rb ON rb.ReservationId=r.Id WHERE rb.FirstReleaseCheckbox='on' AND r.Status='active' $whereCond)
UNION
(SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Broker as Broker,  CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.ReservationStatus, r.Project, r.Model, rb.SecondReleaseAmount as ReleaseAmount, rb.SecondReleaseType as ReleaseType, rb.SecondReleaseWHT as ReleaseWHT, rb.SecondReleaseDate as ReleaseDate, rb.ReleasesStatus as ReleasesStatus FROM reservation r LEFT JOIN releases_to_broker_for_wht rb ON rb.ReservationId=r.Id WHERE rb.SecondReleaseCheckbox='on' AND r.Status='active' $whereCond)
UNION
(SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Broker as Broker,  CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.ReservationStatus, r.Project, r.Model, rb.ThirdReleaseAmount as ReleaseAmount, rb.ThirdReleaseType as ReleaseType, rb.ThirdReleaseWHT as ReleaseWHT, rb.ThirdReleaseDate as ReleaseDate, rb.ReleasesStatus as ReleasesStatus FROM reservation r LEFT JOIN releases_to_broker_for_wht rb ON rb.ReservationId=r.Id WHERE rb.ThirdReleaseCheckbox='on' AND r.Status='active' $whereCond)
ORDER BY Broker,Id,ReleaseType
"; */


$sql 		= "(SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Broker as Broker, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, 
r.ReservationDate, r.ReservationStatus, r.Project, r.Model, 

IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='on', 'Full', IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='', 'Partial', IF(rb.FullCheckbox='on', 'Full', ''))) as ReleaseType,  

IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='on', rb.TotalAmount, IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='', rb.PartialAmount, IF(rb.FullCheckbox='on', rb.TotalAmount, ''))) as ReleaseAmount,  

IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='on', rb.TotalWHT, IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='', rb.PartialWHT, IF(rb.FullCheckbox='on', rb.TotalWHT, ''))) as ReleaseWHT,  


IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='on', DATE_FORMAT(rb.TotalDate, '%m/%d/%Y'), IF(rb.Balancecheckbox='on' AND rb.FullCheckbox='', DATE_FORMAT(rb.PartialDate, '%m/%d/%Y'), IF(rb.FullCheckbox='on', DATE_FORMAT(rb.TotalDate, '%m/%d/%Y'), ''))) as ReleaseDate, 

rb.ReleasesStatus as ReleasesStatus FROM releases_to_broker_for_wht rb  LEFT JOIN reservation r ON rb.ReservationId=r.Id WHERE r.Status='active' $whereCond)
ORDER BY r.Broker, cast(Block as unsigned) asc, cast(Lot as unsigned) asc "; 
 

$mecon->query($sql); 

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

for($a=0; $a<$counter; $a++){  
	$arr1[0] = $a+1; 
	$arr1[1] = $main[$a]['Fullname']; 
	$arr1[2] = $main[$a]['Block'];
	$arr1[3] = $main[$a]['Lot'];
	$arr1[4] = $main[$a]['Broker']; 
	$arr1[5] = $main[$a]['ReleaseType'];
	$arr1[6] = ($main[$a]['ReleaseAmount']!='') ? number_format($main[$a]['ReleaseAmount'],2) : '0.00';
	$arr1[7] = ($main[$a]['ReleaseWHT']!='') ? number_format($main[$a]['ReleaseWHT'],2) : '0.00';
	$arr1[8] = $main[$a]['ReleaseDate'];
	$arr1[9] = $main[$a]['ReleasesStatus']; 
			 

	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
$fileName    = "releases_to_broker_for_wht.xls";
$objPHPExcel = $objReader->load("templates/report_releases_to_broker_for_wht.xls"); 
$baseRow = 4; 
for($a=0; $a<count($arr3['data']); $a++){ 
	$row = $baseRow + $a;
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1); 
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $arr3['data'][$a][1])
											  ->setCellValue('B'.$row, $arr3['data'][$a][2])
											  ->setCellValue('C'.$row, $arr3['data'][$a][3])
											  ->setCellValue('D'.$row, $arr3['data'][$a][4])
											  ->setCellValue('E'.$row, $arr3['data'][$a][5])
											  ->setCellValue('F'.$row, $arr3['data'][$a][6])
											  ->setCellValue('G'.$row, $arr3['data'][$a][7])
											  ->setCellValue('H'.$row, $arr3['data'][$a][8])
											  ->setCellValue('I'.$row, $arr3['data'][$a][9]);
											  
			$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getNumberFormat();											  
			$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');	 										  
 }  
$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
$objWriter->save('php://output'); 