<?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";
 //DateIssued => date po issued as of Nov 7, 2020
//DatePOIssued => completion date as of Nov 7, 2020
$resultQuery 		= "SELECT Id, DateIssued, DatePOIssued, Contractor, Block, Lot, Units,ContractPrice, ReleasesRemarks from releases_to_contractor"; 
$sql			=	$resultQuery."  WHERE Status = 'active' ";  

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 Units LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(DateIssued,'%m/%d/%Y')) LIKE '%".$ind."%' ";  
		$sql.=" OR Contractor LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR ContractPrice LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR ReleasesRemarks LIKE '%".$ind."%'  ) ";	
	}
} 

if($isfilterdate=="yes"){
	if( !empty($monthFrom) && !empty($monthTo) ){
		$sql .= " AND DateIssued BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
	}else if( !empty($monthFrom)){
		$sql .= " AND DateIssued >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
	}else if( !empty($monthTo)){
		$sql .= " AND DateIssued <= ('".date('Y-m-d', strtotime($monthTo))."') ";
	}
}

$sql .= " ORDER BY 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]['DateIssued']=='1970-01-01') || ($main[$a]['DateIssued']=='')) ? "" : date('m/d/Y', strtotime($main[$a]['DateIssued']));
	$arr1[2]	= (($main[$a]['DatePOIssued']=='1970-01-01') || ($main[$a]['DatePOIssued']=='')) ? "" : date('m/d/Y', strtotime($main[$a]['DatePOIssued']));
	$arr1[3]	= $main[$a]['Contractor']; 
	$arr1[4]	= $main[$a]['Block'];
	$arr1[5]	= $main[$a]['Lot'];
	$arr1[6]	= $main[$a]['Units'];  
	$arr1[7]	= !empty($main[$a]['ContractPrice']) ? number_format($main[$a]['ContractPrice'],2) : "";
	$arr1[8]	= $main[$a]['ReleasesRemarks'];
   	$arr2[] 	= $arr1;
}
$arr3['data'] = $arr2;
 
 
$fileName    = "summary_po_to_contractor.xls";
$objPHPExcel = $objReader->load("templates/report_summary_po_to_contractor.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]); 


		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
		$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
		 
 }  
$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'); 