<?php 
set_time_limit(0);
error_reporting(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');
$showCanCtr = "";
$showPastCtr = "";
$showLackCtr = "";
$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']) 			: "";
$filterSearch = isset($_GET['filter']) ? str_replace("'"," ",$_GET['filter']) : "";

$isfilterdate = isset($_GET['isfilterdate']) ? strip_tags($_GET['isfilterdate']) : "yes";
$fileName		=	"cancelled_sales_reservation.xls";
$objPHPExcel	=	$objReader->load("templates/report_cancelled_sales.xls"); 

$marr			=	array();
$carr			=	array();
$darr			=	array();
 
		//LOOP PER SHEET
		$prevAssetName = NULL;
		$objPHPExcel->removeSheetByIndex(0);
		$objPHPExcel->removeSheetByIndex(1); 
		$objPHPExcel->removeSheetByIndex(0);
  
$cancelArr = array(); 
$cancelArr	= getCancelBroker($monthFrom,$monthTo,$filterSearch,$isfilterdate); 
$ctrCancel	= count($cancelArr);
$marr = $cancelArr;

 foreach($marr as $mIndex => $mVal){
	 
	if ($mVal !== $prevAssetName) {
				$newsheet = $objPHPExcel->createSheet();
				$newsheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
				$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LEGAL);

				$newsheet->setTitle($mVal);
				$prevAssetName = $mVal; 
				$modelFilter = $mVal; 
				$projectName = $_SESSION['castalia_project'];
				$newsheet->setCellValue('A1',$projectName);
				$newsheet->setCellValue('A2',$mVal);
				$newsheet->setCellValue('A3',"SUMMARY REPORT OF BUYER/S WITH DEFICIENCIES ");
				
				$styleArray = array('font' => array('bold' => true));
						$newsheet->getStyle('A1:A3')->applyFromArray($styleArray);
						unset($styleArray);   
				//CANCEL ACCOUNTS
				$showCancelArr = array();
				if($ctrCancel!=0){					
					$showCancelArr = showCancelDetails($mVal,$monthFrom,$monthTo,$filterSearch,$isfilterdate); 
					$showCanCtr = count($showCancelArr);
					if($showCanCtr!=0){
						$newsheet->setCellValue('A4',"ACCOUNT DUE CANCELLATION");
						$newsheet->setCellValue('A5',"AS OF ");
						$newsheet->setCellValue('A6'," ");
						$newsheet->setCellValue('A7',"#");
						$newsheet->setCellValue('B7',"RESERVATION DATE"); 
						$newsheet->setCellValue('C7',"BLOCK");
						$newsheet->setCellValue('D7',"LOT"); 
						$newsheet->setCellValue('E7',"FULLNAME");
						$newsheet->setCellValue('F7',"PHASE"); 
						$newsheet->setCellValue('G7',"CONSTRUCTION STATUS UPON RESERVATION");
						

							$objPHPExcel->getActiveSheet()->getStyle('A7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);	
							$objPHPExcel->getActiveSheet()->getStyle('B7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);	
							$objPHPExcel->getActiveSheet()->getStyle('C7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);	
							$objPHPExcel->getActiveSheet()->getStyle('D7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);	
							$objPHPExcel->getActiveSheet()->getStyle('E7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);	
							$objPHPExcel->getActiveSheet()->getStyle('F7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);	
							$objPHPExcel->getActiveSheet()->getStyle('G7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						 
						
						$newsheet->getColumnDimension('A')->setWidth(5); 
						$newsheet->getColumnDimension('C')->setWidth(12); 
						$newsheet->getColumnDimension('E')->setWidth(30); 
						$newsheet->getColumnDimension('B')->setWidth(30); 
						$newsheet->getColumnDimension('F')->setWidth(25); 
						$newsheet->getColumnDimension('G')->setWidth(20); 
						$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
						$newsheet->getStyle('A3:G7')->applyFromArray($styleArray);
						unset($styleArray);   
						$b = 8;	
						for($a=0; $a<count($showCancelArr); $a++){ 
							$newsheet->setCellValue('A'.$b,$showCancelArr[$a][0]);
							$newsheet->setCellValue('B'.$b,$showCancelArr[$a][1]);
							$newsheet->setCellValue('C'.$b,$showCancelArr[$a][3]);
							$newsheet->setCellValue('D'.$b,$showCancelArr[$a][4]);
							$newsheet->setCellValue('E'.$b,$showCancelArr[$a][5]);
							$newsheet->setCellValue('F'.$b,strtoupper($showCancelArr[$a][2]));
							$newsheet->setCellValue('G'.$b,$showCancelArr[$a][6]);
							$objPHPExcel->getActiveSheet()->getStyle('B'.$b)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
							$objPHPExcel->getActiveSheet()->getStyle('A'.$b)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('B'.$b)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('C'.$b)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('D'.$b)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('E'.$b)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('F'.$b)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('G'.$b)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							
							$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
						$newsheet->getStyle('A'.$b.':G'.$b.'')->applyFromArray($styleArray);
						unset($styleArray);   
						
							$b++;
							
						}
					} 
				}
				
   
		}  
}			
   
 
$currentUser 	= $_SESSION['castalia_lastname'].", ".$_SESSION['castalia_firstname']; 
$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'); 

  

function getCancelBroker($monthFrom, $monthTo, $filterSearch, $isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass();	
	$sql = "SELECT r.Broker as Broker, r.Id as Id, r.ReservationDate, r.ConstructionStatusUponReservation as ConstructionStatusUponReservation FROM reservation r WHERE  r.Status='active' AND r.ReservationStatus='cancelled' ";
	  
	if(!empty($filterSearch)) { 
		$arr = explode(" ",$filterSearch);  
		foreach($arr as $ind){
			$ind = str_replace(',','',$ind);
			$sql.=" AND (r.Broker LIKE '%".htmlentities($ind)."%' ";
			$sql.=" OR r.ConstructionStatusUponReservation LIKE '%".$ind."%' ";
			$sql.=" OR r.Block LIKE '%".$ind."%' ";
			$sql.=" OR r.Lot LIKE '%".$ind."%' )";
		}
	}
	
if($isfilterdate=="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 (r.Broker) ";

	 $sql .= " ORDER BY r.ReservationDate desc, cast(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc";
	 
	$mecon->query($sql);
	$result3 = $mecon->resultset(); 
	$cancelArr	=	array(); 			
	for($a=0; $a<count($result3); $a++){ 
		$cancelArr[] = $result3[$a]['Broker']; 
	} 
	return $cancelArr;
}

function showCancelDetails($broker,$monthFrom,$monthTo,$filterSearch,$isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass();	
	$formClass			=	new formClass();	
	$sql = "SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Model as Model, r.Project as Project, r.ReservationDate as ReservationDate, r.PricelistDate as PricelistDate, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, r.Phase as Phase, r.ConstructionStatusUponReservation as ConstructionStatusUponReservation  FROM reservation r  WHERE  r.Status='active' AND r.ReservationStatus='cancelled' AND r.Broker LIKE '%".$broker."%' ";
	 
 
	
	if(!empty($filterSearch)) { 
		$arr = explode(" ",$filterSearch);  
		foreach($arr as $ind){
			$ind = str_replace(',','',$ind);
			$sql.=" AND (r.Model LIKE '%".$ind."%' "; 
			$sql.=" OR r.ConstructionStatusUponReservation LIKE '%".$ind."%' ";
			$sql.=" OR r.Block LIKE '%".$ind."%' ";
			$sql.=" OR r.Lot LIKE '%".$ind."%' ";
			$sql.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
			$sql.=" OR r.Broker LIKE '%".htmlentities($ind)."%' ) ";
		}
	}
	
if($isfilterdate=="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))."') ";
	} 
}	 
	$mecon->query($sql);
	$result3 = $mecon->resultset();  
	$dataCancel = array();
	for($a=0; $a<count($result3); $a++){ 
	$cancelArr	=	array();	
		$cancelArr[] = $a+1; 
		$cancelArr[] = $result3[$a]['ReservationDate']; 
		$cancelArr[] = $result3[$a]['Phase']; 
		$cancelArr[] = $result3[$a]['Block']; 
		$cancelArr[] = $result3[$a]['Lot']; 
		$cancelArr[] = strtoupper(html_entity_decode(stripslashes($result3[$a]['FullName']))); 
		//$cancelArr[] = $result3[$a]['PricelistStatus'];    
		
			$pricedate	= html_entity_decode(stripslashes($result3[$a]['PricelistDate'])); 
			$phase		= html_entity_decode(stripslashes($result3[$a]['Phase'])); 
			$block		= html_entity_decode(stripslashes($result3[$a]['Block'])); 
			$lot		= html_entity_decode(stripslashes($result3[$a]['Lot'])); 
			$project	= html_entity_decode(stripslashes($result3[$a]['Project'])); 
			$models		= html_entity_decode(stripslashes($result3[$a]['Model'])); 
			
			$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);
			
			//$cancelArr[] = $other['pricelist_status'];
		
		//$cancelArr[] = (!empty($result3[$a]['HouseConstruction']) || $result3[$a]['HouseConstruction']=NULL) ? $result3[$a]['HouseConstruction'] : $other['pricelist_status'];
		$cancelArr[] = (!empty($result3[$a]['ConstructionStatusUponReservation']) || $result3[$a]['ConstructionStatusUponReservation']=NULL) ? $result3[$a]['ConstructionStatusUponReservation'] : "";
		
		$dataCancel[] = $cancelArr; 
	} 
	return $dataCancel;
}  