<?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(rb.FullDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$whereCond.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ) ";   
	}
}

if($isfilterdate=="yes"){
	if( !empty($monthFrom) && !empty($monthTo) ){
		$whereCond .= " AND rb.FullDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
	}else if( !empty($monthFrom)){
		$whereCond .= " AND rb.FullDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
	}else if( !empty($monthTo)){
		$whereCond .= " AND rb.FullDate <= ('".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 "; */


//$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, DATE_FORMAT(rb.FullDate, '%m/%d/%Y') as FullDate, rb.FullCheck as FullCheck, rb.TotalAmount as TotalAmount, rb.TotalWHT as TotalWHT, (SELECT TIN FROM tin WHERE type='broker' AND UPPER(CompanyName)=UPPER(r.Broker)) as Tin 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(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc"; 

$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, DATE_FORMAT(rb.FullDate, '%m/%d/%Y') as FullDate, rb.FullCheck as FullCheck, rb.TotalAmount as TotalAmount, rb.TotalWHT as TotalWHT, (SELECT TIN FROM tin WHERE type='broker' AND UPPER(CompanyName)=UPPER(r.Broker)) as Tin FROM releases_to_broker_for_wht rb LEFT JOIN reservation r ON rb.ReservationId=r.Id WHERE r.Status='active' $whereCond ORDER BY UPPER(DATE_FORMAT(rb.FullDate,'%m/%d/%Y')) ASC, r.Broker ASC, Fullname ASC "; //25dec24


$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]['Tin'];
	$arr1[6] = $main[$a]['FullCheck']; 
	$arr1[7] = ($main[$a]['TotalAmount']!='') ? number_format($main[$a]['TotalAmount'],2) : '0.00';
	$arr1[8] = ($main[$a]['TotalWHT']!='') ? number_format($main[$a]['TotalWHT'],2) : '0.00';
	$arr1[9] = $main[$a]['FullDate'];
	
			 

	$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('G'.$row)->getNumberFormat();											  
			$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('I'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');	 	

			$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$objPHPExcel->getActiveSheet()->getStyle('C'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

 }  
$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'); 