<?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']) : "";
$filterSearch = isset($_GET['filter']) ? str_replace("'"," ",$_GET['filter']) : "";
$isfilterdate = isset($_GET['isfilterdate']) ? strip_tags($_GET['isfilterdate']) : "yes";
 
$sql = "SELECT Id, Block, Lot, TaxDecLandProcessed,  TaxDecLandReleased, TaxDecBuildingProcessed, TaxDecBuildingReleased,  TaxReceiptLandProcessed, TaxReceiptLandReleased,  TaxReceiptBuildingProcessed, TaxReceiptBuildingReleased, BuildingPermitProcessed, BuildingPermitReleased, ElectricalPermitProcessed, ElectricalPermitReleased, SanitaryPermitProcessed, SanitaryPermitReleased,  OccupancyPermitProcessed,  OccupancyPermitReleased FROM permits_and_licenses  
WHERE Block!='null' ";  

if(!empty($filterSearch)) { 
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql.=" AND ( Block LIKE '%".$ind."%' ";
		$sql.=" OR Lot LIKE '%".$ind."%' ";
 		$sql.=" OR UPPER(DATE_FORMAT(TaxDecLandProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(TaxDecLandReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(TaxDecBuildingProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(TaxDecBuildingReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(TaxReceiptLandProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(TaxReceiptLandReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(TaxReceiptBuildingProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(TaxReceiptBuildingReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(BuildingPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(BuildingPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(ElectricalPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(ElectricalPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(SanitaryPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(SanitaryPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(OccupancyPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(OccupancyPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
 		$sql.="  ) ";   
	}
}
/*
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);
$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]['Block'];
	$arr1[2]	= $main[$a]['Lot']; 
	 
	$arr1[3]	= (!empty($main[$a]['TaxDecLandProcessed']) && ($main[$a]['TaxDecLandProcessed']!='1970-01-01')) ? $main[$a]['TaxDecLandProcessed'] : "";
	$arr1[4]	= (!empty($main[$a]['TaxDecLandReleased']) && ($main[$a]['TaxDecLandReleased']!='1970-01-01')) ? $main[$a]['TaxDecLandReleased'] : ""; 
	$arr1[5]	= (!empty($main[$a]['TaxDecBuildingProcessed']) && ($main[$a]['TaxDecBuildingProcessed']!='1970-01-01')) ? $main[$a]['TaxDecBuildingProcessed'] : "";
	$arr1[6]	= (!empty($main[$a]['TaxDecBuildingReleased']) && ($main[$a]['TaxDecBuildingReleased']!='1970-01-01')) ? $main[$a]['TaxDecBuildingReleased'] : "";
	$arr1[7]	= (!empty($main[$a]['TaxReceiptLandProcessed']) && ($main[$a]['TaxReceiptLandProcessed']!='1970-01-01')) ? $main[$a]['TaxReceiptLandProcessed'] : "";
$arr1[8]	= (!empty($main[$a]['TaxReceiptLandReleased']) && ($main[$a]['TaxReceiptLandReleased']!='1970-01-01')) ? $main[$a]['TaxReceiptLandReleased'] : "";
$arr1[9]	= (!empty($main[$a]['TaxReceiptBuildingProcessed']) && ($main[$a]['TaxReceiptBuildingProcessed']!='1970-01-01')) ? $main[$a]['TaxReceiptBuildingProcessed'] : "";
$arr1[10]	= (!empty($main[$a]['TaxReceiptBuildingReleased']) && ($main[$a]['TaxReceiptBuildingReleased']!='1970-01-01')) ? $main[$a]['TaxReceiptBuildingReleased'] : "";
$arr1[11]	= (!empty($main[$a]['BuildingPermitProcessed']) && ($main[$a]['BuildingPermitProcessed']!='1970-01-01')) ? $main[$a]['BuildingPermitProcessed'] : "";
$arr1[12]	= (!empty($main[$a]['BuildingPermitReleased']) && ($main[$a]['BuildingPermitReleased']!='1970-01-01')) ? $main[$a]['BuildingPermitReleased'] : "";
$arr1[13]	= (!empty($main[$a]['ElectricalPermitProcessed']) && ($main[$a]['ElectricalPermitProcessed']!='1970-01-01')) ? $main[$a]['ElectricalPermitProcessed'] : "";
 $arr1[14]	= (!empty($main[$a]['ElectricalPermitReleased']) && ($main[$a]['ElectricalPermitReleased']!='1970-01-01')) ? $main[$a]['ElectricalPermitReleased'] : "";
 $arr1[15]	= (!empty($main[$a]['SanitaryPermitProcessed']) && ($main[$a]['SanitaryPermitProcessed']!='1970-01-01')) ? $main[$a]['SanitaryPermitProcessed'] : "";
 $arr1[16]	= (!empty($main[$a]['SanitaryPermitReleased']) && ($main[$a]['SanitaryPermitReleased']!='1970-01-01')) ? $main[$a]['SanitaryPermitReleased'] : "";
 $arr1[17]	= (!empty($main[$a]['OccupancyPermitProcessed']) && ($main[$a]['OccupancyPermitProcessed']!='1970-01-01')) ? $main[$a]['OccupancyPermitProcessed'] : "";
 $arr1[18]	= (!empty($main[$a]['OccupancyPermitReleased']) && ($main[$a]['OccupancyPermitReleased']!='1970-01-01')) ? $main[$a]['OccupancyPermitReleased'] : "";
     




	$arr2[] 	= $arr1;
}
$arr3['data'] = $arr2;
 
 
$fileName    = "summary_status_of_permits.xls";
$objPHPExcel = $objReader->load("templates/report_summary_status_of_permits.xls"); 

$baseRow = 2;
  
 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][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])
											  ->setCellValue('J'.$row, $arr3['data'][$a][10])
											  ->setCellValue('K'.$row, $arr3['data'][$a][11])
											  ->setCellValue('L'.$row, $arr3['data'][$a][12])
											  ->setCellValue('M'.$row, $arr3['data'][$a][13])
											  ->setCellValue('N'.$row, $arr3['data'][$a][14])
											  ->setCellValue('O'.$row, $arr3['data'][$a][15])
											  ->setCellValue('P'.$row, $arr3['data'][$a][16])
											  ->setCellValue('Q'.$row, $arr3['data'][$a][17])
											  ->setCellValue('R'.$row, $arr3['data'][$a][18]); 


		$objPHPExcel->getActiveSheet()->getStyle('C'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');	 
		$objPHPExcel->getActiveSheet()->getStyle('D'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
		$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
		$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('I'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('J'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('K'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('L'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('M'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('N'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('O'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('P'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('Q'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy'); 
		$objPHPExcel->getActiveSheet()->getStyle('R'.$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'); 