<?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();
 
$releaseContractorId		= isset($_GET['id']) ? strip_tags($_GET['id']) : "";

  
 $whereCond = "";

 $whereCond .= " WHERE releases_checkbox='on' AND rc.Status = 'active' AND rc.id='$releaseContractorId' ";

    

 $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, rcd.recoup as recoup, rcd.retention as retention, rcd.electricity as electricity, rcd.water as water, rcd.others as others, rcd.net as net, rcd.check_no as check_no 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]['Block'];
	$arr1[3] = $main[$a]['Lot']; 
	$arr1[4] = $main[$a]['releases'];
	$arr1[5] = !empty($main[$a]['accomplishment_percentage']) ? number_format($main[$a]['accomplishment_percentage'],2)."%" : ""; 
	$arr1[6] = !empty($main[$a]['accomplishment_amount']) ? number_format($main[$a]['accomplishment_amount'],2) : "";
	$arr1[7] = !empty($main[$a]['rwht']) ? number_format($main[$a]['rwht'],2) : "";

	$arr1[8] = !empty($main[$a]['recoup']) ? number_format($main[$a]['recoup'],2) : "";
	$arr1[9] = !empty($main[$a]['retention']) ? number_format($main[$a]['retention'],2) : "";
	$arr1[10] = !empty($main[$a]['electricity']) ? number_format($main[$a]['electricity'],2) : "";
	$arr1[11] = !empty($main[$a]['water']) ? number_format($main[$a]['water'],2) : "";
	$arr1[12] = !empty($main[$a]['others']) ? number_format($main[$a]['others'],2) : "";
	$arr1[13] = !empty($main[$a]['net']) ? number_format($main[$a]['net'],2) : "";

	$arr1[14] = (($main[$a]['date_processed']=='1970-01-01') || ($main[$a]['date_processed']=='')) ? "" : date('m/d/Y', strtotime($main[$a]['date_processed']));

	$arr1[15] = $main[$a]['check_no'];

	$arr1[16] = ($main[$a]['releases']=='downpayment') ? $main[$a]['ReleasesStatus'] : "";
			 

	$arr2[] 	= $arr1;
}

$arr3['data'] = $arr2;
$contractor  = (isset($arr1[1])) ? trim(strtolower(str_replace(" ","_",$arr1[1]))) : "";
$fileName    = "releases_to_contractor_".$contractor.".xls";
$objPHPExcel = $objReader->load("templates/report_releases_to_contractor_individual.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])
											  ->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]);
											  
			$objPHPExcel->getActiveSheet()->getStyle('F'.$row)->getNumberFormat();											  
			$objPHPExcel->getActiveSheet()->getStyle('G'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('H'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('I'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('J'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('K'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('L'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('M'.$row)->getNumberFormat(); 
			$objPHPExcel->getActiveSheet()->getStyle('N'.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
 			
 			$objPHPExcel->getActiveSheet()->getStyle('E'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
 			$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)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
 			$objPHPExcel->getActiveSheet()->getStyle('J'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
 			$objPHPExcel->getActiveSheet()->getStyle('K'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
 			$objPHPExcel->getActiveSheet()->getStyle('L'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
 			$objPHPExcel->getActiveSheet()->getStyle('M'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);


 }  


 $objPHPExcel->getActiveSheet()->setCellValue('G2', "EWT");

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