<?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 ubl.Id AS Id, ubl.Block AS Block, ubl.Lot AS Lot, pal.TaxDecLandProcessed AS TaxDecLandProcessed, pal.TaxDecLandReleased AS TaxDecLandReleased, pal.TaxDecBuildingProcessed AS TaxDecBuildingProcessed, pal.TaxDecBuildingReleased AS TaxDecBuildingReleased,  pal.TaxReceiptLandProcessed AS TaxReceiptLandProcessed, pal.TaxReceiptLandReleased AS TaxReceiptLandReleased, pal.TaxReceiptBuildingProcessed AS TaxReceiptBuildingProcessed, pal.TaxReceiptBuildingReleased AS TaxReceiptBuildingReleased, pal.BuildingPermitProcessed AS BuildingPermitProcessed, pal.BuildingPermitReleased AS BuildingPermitReleased, pal.ElectricalPermitProcessed AS ElectricalPermitProcessed, pal.ElectricalPermitReleased AS ElectricalPermitReleased, pal.SanitaryPermitProcessed AS SanitaryPermitProcessed, pal.SanitaryPermitReleased AS SanitaryPermitReleased, pal.OccupancyPermitProcessed AS OccupancyPermitProcessed, pal.OccupancyPermitReleased AS OccupancyPermitReleased, ubl.TDLandNo AS TDLandNo, ubl.TDBldgNo AS TDBldgNo  FROM permits_and_licenses pal LEFT JOIN units_block_lot ubl ON ubl.UnitId=pal.UnitId WHERE pal.Block!='null' ";  

if(!empty($filterSearch)) { 
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql.=" AND ( ubl.Block LIKE '%".$ind."%' ";
		$sql.=" OR ubl.Lot LIKE '%".$ind."%' ";
		$sql.=" OR ubl.TDLandNo LIKE '%".$ind."%' ";
		$sql.=" OR ubl.TDBldgNo LIKE '%".$ind."%' ";
 		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxDecLandProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxDecLandReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxDecBuildingProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxDecBuildingReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxReceiptLandProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxReceiptLandReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxReceiptBuildingProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.TaxReceiptBuildingReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.BuildingPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.BuildingPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.ElectricalPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.ElectricalPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.SanitaryPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.SanitaryPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.OccupancyPermitProcessed,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(pal.OccupancyPermitReleased,'%m/%d/%Y')) LIKE '%".$ind."%' ";
 		$sql.="  ) "; 
	}
}
 
$sql .= "  ORDER BY cast(ubl.Block as unsigned) ,  cast(ubl.Lot as unsigned)  ";
$mecon->query($sql);
$main = $mecon->resultset(); 
$counter = count($main); 
$arr1 = array();
$arr2 = array();  
$arr3 = array();  

for($a=0; $a<$counter; $a++){  
	
	$arr1[0] 	= (!empty($main[$a]['Id'])) 		? $main[$a]['Id'] 		: ""; 
	$arr1[1] 	= (!empty($main[$a]['Block']))  	? $main[$a]['Block']  	: ""; 
	$arr1[2] 	= (!empty($main[$a]['Lot'])) 		? $main[$a]['Lot'] 		: ""; 


	$arr1[3] = (!empty($main[$a]['TaxDecLandProcessed']) && $main[$a]['TaxDecLandProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['TaxDecLandProcessed'])) 	 : ""; 
	$arr1[4] = (!empty($main[$a]['TaxDecLandReleased']) && $main[$a]['TaxDecLandReleased']!='1970-01-01')  ? date('m/d/Y',strtotime($main[$a]['TaxDecLandReleased']))  			: ""; 

	$arr1[5] = (!empty($main[$a]['TDLandNo'])) ? $main[$a]['TDLandNo'] : ""; 

	 
	$arr1[6] = (!empty($main[$a]['TaxDecBuildingProcessed']) && $main[$a]['TaxDecBuildingProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['TaxDecBuildingProcessed'])) : ""; 
	$arr1[7] = (!empty($main[$a]['TaxDecBuildingReleased']) && $main[$a]['TaxDecBuildingReleased']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['TaxDecBuildingReleased']))  : ""; 

	$arr1[8] = (!empty($main[$a]['TDBldgNo'])) ? $main[$a]['TDBldgNo'] : ""; 



	$arr1[9] = (!empty($main[$a]['TaxReceiptLandProcessed']) && $main[$a]['TaxReceiptLandProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['TaxReceiptLandProcessed'])) : ""; 
	$arr1[10] = ($main[$a]['TaxReceiptLandReleased']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['TaxReceiptLandReleased'])) : ""; 
	$arr1[11] = ($main[$a]['TaxReceiptBuildingProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['TaxReceiptBuildingProcessed'])) : ""; 
	$arr1[12] = ($main[$a]['TaxReceiptBuildingReleased']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['TaxReceiptBuildingReleased'])) : ""; 
	$arr1[13] = ($main[$a]['BuildingPermitProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['BuildingPermitProcessed'])) : ""; 

	$arr1[14] = (!empty($main[$a]['BuildingPermitReleased']) 	&& $main[$a]['BuildingPermitReleased']!='1970-01-01') 	? date('m/d/Y',strtotime($main[$a]['BuildingPermitReleased'])) 		  	: ""; 
	$arr1[15] = (!empty($main[$a]['ElectricalPermitProcessed']) && $main[$a]['ElectricalPermitProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['ElectricalPermitProcessed'])) 		 : "";
	$arr1[16] = (!empty($main[$a]['ElectricalPermitReleased']) 	&& $main[$a]['ElectricalPermitReleased']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['ElectricalPermitReleased'])) 		: ""; 
	$arr1[17] = (!empty($main[$a]['SanitaryPermitProcessed']) 	&& $main[$a]['SanitaryPermitProcessed']!='1970-01-01') 	? date('m/d/Y',strtotime($main[$a]['SanitaryPermitProcessed'])) 		: ""; 
	$arr1[18] = (!empty($main[$a]['SanitaryPermitReleased']) 	&& $main[$a]['SanitaryPermitReleased']!='1970-01-01') 	? date('m/d/Y',strtotime($main[$a]['SanitaryPermitReleased'])) 		  	: ""; 
	$arr1[19] = (!empty($main[$a]['OccupancyPermitProcessed']) 	&& $main[$a]['OccupancyPermitProcessed']!='1970-01-01') ? date('m/d/Y',strtotime($main[$a]['OccupancyPermitProcessed'])) 		: ""; 
	$arr1[20] = (!empty($main[$a]['OccupancyPermitReleased']) 	&& $main[$a]['OccupancyPermitReleased']!='1970-01-01') 	? date('m/d/Y',strtotime($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 = 3;
  
 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])
											  ->setCellValue('S'.$row, $arr3['data'][$a][19])
											  ->setCellValue('T'.$row, $arr3['data'][$a][20]); 


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

	 
   	 	$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':'.'T'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
   	 	//$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

 }


/*
//labels
$objPHPExcel->getActiveSheet()->mergeCells('A1:A2');
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'BLOCK');

$objPHPExcel->getActiveSheet()->mergeCells('B1:B2');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'LOT'); 

$objPHPExcel->getActiveSheet()->mergeCells('C1:E1');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'TAX DEC LAND');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 'DATE PROCESSED');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'DATE RELEASED');
$objPHPExcel->getActiveSheet()->setCellValue('E2', 'NUMBER');

$objPHPExcel->getActiveSheet()->mergeCells('F1:H1');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'TAX DEC BUILDING');
$objPHPExcel->getActiveSheet()->setCellValue('F2', 'DATE PROCESSED');
$objPHPExcel->getActiveSheet()->setCellValue('G2', 'DATE RELEASED');
$objPHPExcel->getActiveSheet()->setCellValue('H2', 'NUMBER');


$objPHPExcel->getActiveSheet()->mergeCells('I1:J1');
$objPHPExcel->getActiveSheet()->setCellValue('I1', 'TAX RECEIPT LAND');
$objPHPExcel->getActiveSheet()->setCellValue('I2', 'DATE PROCESSED');
$objPHPExcel->getActiveSheet()->setCellValue('J2', 'DATE RELEASED'); 

$objPHPExcel->getActiveSheet()->mergeCells('K1:L1');
$objPHPExcel->getActiveSheet()->setCellValue('K1', 'TAX RECEIPT BUILDING');
$objPHPExcel->getActiveSheet()->setCellValue('K2', 'DATE PROCESSED');
$objPHPExcel->getActiveSheet()->setCellValue('L2', 'DATE RELEASED'); 

$objPHPExcel->getActiveSheet()->mergeCells('M1:N1');
$objPHPExcel->getActiveSheet()->setCellValue('M1', 'BUILDING PERMIT');
$objPHPExcel->getActiveSheet()->setCellValue('M2', 'DATE PROCESSED');
$objPHPExcel->getActiveSheet()->setCellValue('N2', 'DATE RELEASED'); 
  
$objPHPExcel->getActiveSheet()->mergeCells('O1:P1');
$objPHPExcel->getActiveSheet()->setCellValue('O1', 'ELECTRICAL PERMIT');
$objPHPExcel->getActiveSheet()->setCellValue('O2', 'DATE PROCESSED');
$objPHPExcel->getActiveSheet()->setCellValue('P2', 'DATE RELEASED');

$objPHPExcel->getActiveSheet()->mergeCells('Q1:R1');
$objPHPExcel->getActiveSheet()->setCellValue('Q1', 'SANITARY PERMIT');
$objPHPExcel->getActiveSheet()->setCellValue('Q2', 'DATE PROCESSED');
$objPHPExcel->getActiveSheet()->setCellValue('R2', 'DATE RELEASED'); 
  
$objPHPExcel->getActiveSheet()->mergeCells('S1:T1');
$objPHPExcel->getActiveSheet()->setCellValue('S1', 'OCCUPANCY PERMIT');
$objPHPExcel->getActiveSheet()->setCellValue('S2', 'DATE PROCESSED');
$objPHPExcel->getActiveSheet()->setCellValue('T2', 'DATE RELEASED'); 
  
*/

  
$objPHPExcel->getActiveSheet()->removeRow($baseRow,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'); 