<?php error_reporting(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');
$currentUser 	= $_SESSION['castalia_username'];
$timeStamp	= date('Y-m-d H:i:s');
$autoload	=	new autoloadClass();
$mecon		=	new condbClass();
$utility	=	new utilityClass();
$main		=	array();
$main2		=	array();
$main3		=	array();
$main4		=	array();
$pricelistId = (isset($_GET['id'])) ? strip_tags(trim($_GET['id'])) : "";
$mecon->query("SELECT Id, Project, Model, ReservationFee, Remarks, PricelistDate, PricelistDetails, (SELECT PricelistTemplate FROM model WHERE Model=pricelist.Model LIMIT 1) as PricelistTemplate, (SELECT ReservationFee FROM model WHERE Model=pricelist.Model LIMIT 1) as ReservationFee, KeyCode FROM pricelist WHERE Status = :status AND Id=:pricelistId ORDER BY PricelistDate DESC, Id");
$mecon->bind(':status', 'active');
$mecon->bind(':pricelistId', $pricelistId);
$main = $mecon->resultset(); 
$counter = count($main); 
$arr1 = array();
$arr2 = array();  

$arr1['id']					= $main[0]['Id']; 
$arr1['notes']				= html_entity_decode($main[0]['Remarks']);
$arr1['code']				= $main[0]['KeyCode']; 
$arr1['template']			= $main[0]['PricelistTemplate']; 
$arr1['pricedetails']		= $main[0]['PricelistDetails']; 
$arr1['reservation_fee']	= $main[0]['ReservationFee']; 
$arr1['project']			= strtoupper(trim(html_entity_decode(stripslashes($main[0]['Project']))));
$arr1['model']				= strtoupper(trim(html_entity_decode(stripslashes($main[0]['Model']))));
$arr1['pricelist_date']		= date('F d, Y', strtotime($main[0]['PricelistDate'])); 
$arr1['pricelist_filedate']	= date('mdY', strtotime($main[0]['PricelistDate'])); 
$arr2[] 					= $arr1; 
$data = $arr2;
$fileName    = "pricelist_".strtolower($arr2[0]['model']).strtolower($arr2[0]['code'])."_".$arr2[0]['pricelist_filedate'].".xls";
//STANDARD INFORMATION
if($arr2[0]['template']=="template2"){
	$objPHPExcel = $objReader->load("templates/pricelist2.xls");
	foreach($data as $r => $dataRow){
		$objPHPExcel->setActiveSheetIndex(0) 
				->setCellValue('A5', "As Of ".$dataRow['pricelist_date'])
				->setCellValue('J6', " MISC (net of RF - ".$dataRow['reservation_fee'].") 10 months "); 
	}
}else{
	$objPHPExcel = $objReader->load("templates/pricelist1.xls"); 
	foreach($data as $r => $dataRow){
		$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('A1', $dataRow['project'])
				->setCellValue('A2', "PRICELIST ( ".$dataRow['model'].$dataRow['code']." ) 2-STOREY")
				->setCellValue('A5', "As Of ".$dataRow['pricelist_date'])
				->setCellValue('I6', "PF (Net of RF - ".$dataRow['reservation_fee']); 
	}
}

//GETTING DETAILS
$dbDetailsArr 		= array();
$preDetailsArr 		= array();
$rfoDetailsArr 		= array();
$ongoingDetailsArr 	= array();
$upcomingDetailsArr = array();
$preMainArr			= array();
$rfoMainArr 		= array();
$ongoingMainArr 	= array();
$upcomingMainArr 	= array();
$dbDetailsArr 		= json_decode($arr2[0]['pricedetails']);
$ctr 				= count($dbDetailsArr->details);
for($a=0; $a<$ctr; $a++){
	if($dbDetailsArr->details[$a]->pricelist_status=="PRE-SELLING"){
		$preDetailsArr['block']			=	$dbDetailsArr->details[$a]->block;
		$preDetailsArr['lot']				=	$dbDetailsArr->details[$a]->lot;
		$preDetailsArr['lot_size']			=	$dbDetailsArr->details[$a]->lot_size;
		$preDetailsArr['lot_type']			=	$dbDetailsArr->details[$a]->lot_type;
		$preDetailsArr['floor_area']		=	$dbDetailsArr->details[$a]->floor_area;
		//$preDetailsArr['selling_price']		=	$dbDetailsArr->details[$a]->selling_price;
		//$preDetailsArr['taxes']				=	$dbDetailsArr->details[$a]->taxes;
		$preDetailsArr['contract_price']	=	$dbDetailsArr->details[$a]->contract_price;
		$preDetailsArr['loanable_amount']	=	$dbDetailsArr->details[$a]->loanable_amount;
		$preDetailsArr['pf_net']			=	$dbDetailsArr->details[$a]->pf_net;
		$preDetailsArr['pf_payable']		=	$dbDetailsArr->details[$a]->pf_payable;
		$preDetailsArr['required_gross']	=	$dbDetailsArr->details[$a]->required_gross;
		$preDetailsArr['amortization']		=	$dbDetailsArr->details[$a]->amortization;
		$preMainArr[]	=	$preDetailsArr;
	}
	if($dbDetailsArr->details[$a]->pricelist_status=="RFO"){
		$rfoDetailsArr['block']			=	$dbDetailsArr->details[$a]->block;
		$rfoDetailsArr['lot']				=	$dbDetailsArr->details[$a]->lot;
		$rfoDetailsArr['lot_size']			=	$dbDetailsArr->details[$a]->lot_size;
		$rfoDetailsArr['lot_type']			=	$dbDetailsArr->details[$a]->lot_type;
		$rfoDetailsArr['floor_area']		=	$dbDetailsArr->details[$a]->floor_area;
		//$rfoDetailsArr['selling_price']		=	$dbDetailsArr->details[$a]->selling_price;
		//$rfoDetailsArr['taxes']				=	$dbDetailsArr->details[$a]->taxes;
		$rfoDetailsArr['contract_price']	=	$dbDetailsArr->details[$a]->contract_price;
		$rfoDetailsArr['loanable_amount']	=	$dbDetailsArr->details[$a]->loanable_amount;
		$rfoDetailsArr['pf_net']			=	$dbDetailsArr->details[$a]->pf_net;
		$rfoDetailsArr['pf_payable']		=	$dbDetailsArr->details[$a]->pf_payable;
		$rfoDetailsArr['required_gross']	=	$dbDetailsArr->details[$a]->required_gross;
		$rfoDetailsArr['amortization']		=	$dbDetailsArr->details[$a]->amortization;
		$rfoMainArr[]	=	$rfoDetailsArr;
	}
	if($dbDetailsArr->details[$a]->pricelist_status=="ON-GOING"){
		$ongoingDetailsArr['block']				=	$dbDetailsArr->details[$a]->block;
		$ongoingDetailsArr['lot']				=	$dbDetailsArr->details[$a]->lot;
		$ongoingDetailsArr['lot_size']			=	$dbDetailsArr->details[$a]->lot_size;
		$ongoingDetailsArr['lot_type']			=	$dbDetailsArr->details[$a]->lot_type;
		$ongoingDetailsArr['floor_area']		=	$dbDetailsArr->details[$a]->floor_area;
		//$ongoingDetailsArr['selling_price']		=	$dbDetailsArr->details[$a]->selling_price;
		//$ongoingDetailsArr['taxes']				=	$dbDetailsArr->details[$a]->taxes;
		$ongoingDetailsArr['contract_price']	=	$dbDetailsArr->details[$a]->contract_price;
		$ongoingDetailsArr['loanable_amount']	=	$dbDetailsArr->details[$a]->loanable_amount;
		$ongoingDetailsArr['pf_net']			=	$dbDetailsArr->details[$a]->pf_net;
		$ongoingDetailsArr['pf_payable']		=	$dbDetailsArr->details[$a]->pf_payable;
		$ongoingDetailsArr['required_gross']	=	$dbDetailsArr->details[$a]->required_gross;
		$ongoingDetailsArr['amortization']		=	$dbDetailsArr->details[$a]->amortization;
		$ongoingMainArr[]	=	$ongoingDetailsArr;
	}
	if($dbDetailsArr->details[$a]->pricelist_status=="UP-COMING"){
		$upcomingDetailsArr['block']			=	$dbDetailsArr->details[$a]->block;
		$upcomingDetailsArr['lot']				=	$dbDetailsArr->details[$a]->lot;
		$upcomingDetailsArr['lot_size']			=	$dbDetailsArr->details[$a]->lot_size;
		$upcomingDetailsArr['lot_type']			=	$dbDetailsArr->details[$a]->lot_type;
		$upcomingDetailsArr['floor_area']		=	$dbDetailsArr->details[$a]->floor_area;
		//$upcomingDetailsArr['selling_price']	=	$dbDetailsArr->details[$a]->selling_price;
		//$upcomingDetailsArr['taxes']			=	$dbDetailsArr->details[$a]->taxes;
		$upcomingDetailsArr['contract_price']	=	$dbDetailsArr->details[$a]->contract_price;
		$upcomingDetailsArr['loanable_amount']	=	$dbDetailsArr->details[$a]->loanable_amount;
		$upcomingDetailsArr['pf_net']			=	$dbDetailsArr->details[$a]->pf_net;
		$upcomingDetailsArr['pf_payable']		=	$dbDetailsArr->details[$a]->pf_payable;
		$upcomingDetailsArr['required_gross']	=	$dbDetailsArr->details[$a]->required_gross;
		$upcomingDetailsArr['amortization']		=	$dbDetailsArr->details[$a]->amortization;
		$upcomingMainArr[]	=	$upcomingDetailsArr;
	}
}

if($arr2[0]['template']=="template2"){
$baseRow = 7;
		if(count($ongoingMainArr)!=0){
		$data3 = $ongoingMainArr;
		//$objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRow,1); 
		//$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$baseRow.':'.'L'.$baseRow);
		//$objPHPExcel->getActiveSheet()->getStyle('A'.$baseRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		//$objPHPExcel->getActiveSheet()->setCellValue('A'.$baseRow, "ON-GOING CONSTRUCTION ( PF payable in 10 months) ");
			foreach($data3 as $r => $dataRow){
				$row = $baseRow + $r + 1;
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1); 
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $r+1)
											  ->setCellValue('B'.$row, $dataRow['block'])
											  ->setCellValue('C'.$row, $dataRow['lot'])
											  ->setCellValue('D'.$row, $dataRow['lot_size'])
											  ->setCellValue('E'.$row, $dataRow['floor_area']) 
			//								  ->setCellValue('F'.$row, $dataRow['selling_price'])
				//							  ->setCellValue('G'.$row, $dataRow['taxes'])
											  ->setCellValue('F'.$row, $dataRow['contract_price'])
											  ->setCellValue('G'.$row, $dataRow['loanable_amount'])
											  ->setCellValue('H'.$row, $dataRow['pf_payable'])
											  ->setCellValue('I'.$row, $dataRow['required_gross'])
											  ->setCellValue('J'.$row, $dataRow['amortization']);
			}
		} 
}else{
$baseRow = 8;
		if(count($upcomingMainArr)!=0){
		$data4 = $upcomingMainArr;
		$objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRow,1); 
		$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$baseRow.':'.'L'.$baseRow);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$baseRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$baseRow, "UP-COMING CONSTRUCTION");
			foreach($data4 as $r => $dataRow){
				$row = $baseRow + $r + 1;
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1); 
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $r+1)
											  ->setCellValue('B'.$row, $dataRow['block'])
											  ->setCellValue('C'.$row, $dataRow['lot'])
											  ->setCellValue('D'.$row, $dataRow['lot_size'])
											  ->setCellValue('E'.$row, $dataRow['lot_type']) 
											  ->setCellValue('F'.$row, $dataRow['floor_area'])
											  ->setCellValue('G'.$row, $dataRow['contract_price'])
											  ->setCellValue('H'.$row, $dataRow['loanable_amount'])
											  ->setCellValue('I'.$row, $dataRow['pf_net'])
											  ->setCellValue('J'.$row, $dataRow['pf_payable'])
											  ->setCellValue('K'.$row, $dataRow['required_gross'])
											  ->setCellValue('L'.$row, $dataRow['amortization']);
			}
		}

		if(count($ongoingMainArr)!=0){
		$data3 = $ongoingMainArr;
		$objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRow,1); 
		$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$baseRow.':'.'L'.$baseRow);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$baseRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$baseRow, "ON-GOING CONSTRUCTION");
			foreach($data3 as $r => $dataRow){
				$row = $baseRow + $r + 1;
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1); 
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $r+1)
											  ->setCellValue('B'.$row, $dataRow['block'])
											  ->setCellValue('C'.$row, $dataRow['lot'])
											  ->setCellValue('D'.$row, $dataRow['lot_size'])
											  ->setCellValue('E'.$row, $dataRow['lot_type']) 
											  ->setCellValue('F'.$row, $dataRow['floor_area'])
											  ->setCellValue('G'.$row, $dataRow['contract_price'])
											  ->setCellValue('H'.$row, $dataRow['loanable_amount'])
											  ->setCellValue('I'.$row, $dataRow['pf_net'])
											  ->setCellValue('J'.$row, $dataRow['pf_payable'])
											  ->setCellValue('K'.$row, $dataRow['required_gross'])
											  ->setCellValue('L'.$row, $dataRow['amortization']);
			}
		}
		 

		if(count($rfoMainArr)!=0){
		$data2 = $rfoMainArr;
		$objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRow,1); 
		$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$baseRow.':'.'L'.$baseRow);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$baseRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		  
			$objPHPExcel->getActiveSheet()->setCellValue('A'.$baseRow, "RFO UNITS");
			foreach($data2 as $r => $dataRow){
				$row = $baseRow + $r + 1;
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1); 
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $r+1)
											  ->setCellValue('B'.$row, $dataRow['block'])
											  ->setCellValue('C'.$row, $dataRow['lot'])
											  ->setCellValue('D'.$row, $dataRow['lot_size'])
											  ->setCellValue('E'.$row, $dataRow['lot_type']) 
											  ->setCellValue('F'.$row, $dataRow['floor_area'])
											  ->setCellValue('G'.$row, $dataRow['contract_price'])
											  ->setCellValue('H'.$row, $dataRow['loanable_amount'])
											  ->setCellValue('I'.$row, $dataRow['pf_net'])
											  ->setCellValue('J'.$row, $dataRow['pf_payable'])
											  ->setCellValue('K'.$row, $dataRow['required_gross'])
											  ->setCellValue('L'.$row, $dataRow['amortization']);
			}
		}
		
		
		if(count($preMainArr)!=0){
		$data2 = $preMainArr;
		$objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRow,1); 
		$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$baseRow.':'.'L'.$baseRow);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$baseRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		  
			$objPHPExcel->getActiveSheet()->setCellValue('A'.$baseRow, "PRE-SELLING");
			foreach($data2 as $r => $dataRow){
				$row = $baseRow + $r + 1;
				$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1); 
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $r+1)
											  ->setCellValue('B'.$row, $dataRow['block'])
											  ->setCellValue('C'.$row, $dataRow['lot'])
											  ->setCellValue('D'.$row, $dataRow['lot_size'])
											  ->setCellValue('E'.$row, $dataRow['lot_type']) 
											  ->setCellValue('F'.$row, $dataRow['floor_area'])
											  ->setCellValue('G'.$row, $dataRow['contract_price'])
											  ->setCellValue('H'.$row, $dataRow['loanable_amount'])
											  ->setCellValue('I'.$row, $dataRow['pf_net'])
											  ->setCellValue('J'.$row, $dataRow['pf_payable'])
											  ->setCellValue('K'.$row, $dataRow['required_gross'])
											  ->setCellValue('L'.$row, $dataRow['amortization']);
			}
		}
} 
$low = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
$row2 = $low + 4;
$objPHPExcel->getActiveSheet()->insertNewRowBefore($row2,1); 
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$row2.':'.'L'.$row2);
$objPHPExcel->getActiveSheet()->getRowDimension($row2)->setRowHeight(150);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row2, $arr1['notes']);
	
$actArr['UserType']			= "accountant,assistant_accountant";
$actArr['Remarks']			= "Downloaded Pricelist : ".$arr1['pricelist_date'];
$actArr['Action']			= "download";
$actArr['ActionType']		= "pricelist";
$actArr['ActionContent']	= json_encode($arr1);
$actArr['ActionBy'] 		= $currentUser;	
$actArr['ActionDate']		= $timeStamp;
$actArr['TransId']		= $pricelistId;
$utility->encodeActivityLog($actArr);

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