<?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 = "";

 $whereCond .= " WHERE releases_checkbox='on' AND Status = 'active' ";
if(!empty($filterSearch) ) { 
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		
		$ind = str_replace(',','',$ind);		
		$whereCond.=" AND (Block LIKE '%".$ind."%' "; 
		$whereCond.=" OR Lot LIKE '%".$ind."%' "; 
		$whereCond.=" OR Units LIKE '%".$ind."%' ";
		$whereCond.=" OR releases LIKE '%".$ind."%' "; 
		$whereCond.=" OR UPPER(DATE_FORMAT(date_processed,'%m/%d/%Y')) LIKE '%".$ind."%' ";  
		$whereCond.=" OR Contractor LIKE '%".htmlentities($ind)."%' ";
		$whereCond.=" OR ContractPrice LIKE '%".htmlentities($ind)."%' ";
		$whereCond.=" OR accomplishment_percentage LIKE '%".htmlentities($ind)."%' ";
		$whereCond.=" OR accomplishment_amount LIKE '%".htmlentities($ind)."%' ";
		$whereCond.=" OR ReleasesRemarks LIKE '%".$ind."%'  ) ";		 
	}
}

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

$sql 		= "SELECT rc.Id as Id, rc.DateIssued as DateIssued, rc.Contractor as Contractor, rc.Block as Block, rc.Lot as Lot, rc.Units as Units,rc.ContractPrice as ContractPrice, rc.ReleasesRemarks as ReleasesRemarks, rcd.releases as releases, rcd.accomplishment_amount as accomplishment_amount, rcd.accomplishment_percentage as accomplishment_percentage, rcd.wht as rwht, rcd.date_processed as date_processed, rc.ReleasesStatus as ReleasesStatus, rcd.releases_checkbox as releases_checkbox, (SELECT TIN FROM tin WHERE type='contractor' AND UPPER(CompanyName)=UPPER(rc.Contractor)) as Tin FROM  releases_to_contractor rc LEFT JOIN releases_to_contractor_details rcd ON rcd.release_contractor_id=rc.Id $whereCond ORDER BY rc.Contractor, cast(rc.Block as unsigned) asc, cast(rc.Lot as unsigned) asc, rcd.releases_no 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]['Contractor']; 
	$arr1[2] = $main[$a]['Tin']; 
	$arr1[3] = $main[$a]['Block'];
	$arr1[4] = $main[$a]['Lot']; 
	$arr1[5] = $main[$a]['releases'];
	$arr1[6] = !empty($main[$a]['accomplishment_percentage']) ? number_format($main[$a]['accomplishment_percentage'],2)."%" : ""; 
	$arr1[7] = !empty($main[$a]['accomplishment_amount']) ? number_format($main[$a]['accomplishment_amount'],2) : "";
	$arr1[8] = !empty($main[$a]['rwht']) ? number_format($main[$a]['rwht'],2) : "";
	$arr1[9] = (($main[$a]['date_processed']=='1970-01-01') || ($main[$a]['date_processed']=='')) ? "" : date('m/d/Y', strtotime($main[$a]['date_processed']));
	$arr1[10] = ($main[$a]['releases']=='downpayment') ? $main[$a]['ReleasesStatus'] : "";
			 

	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
$fileName    = "releases_to_contractor.xls";
$objPHPExcel = $objReader->load("templates/report_releases_to_contractor.xls"); 
$baseRow = 4; 
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]);
											  
			$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getNumberFormat();											  
			$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('F'.$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()->getStyle('I'.$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

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