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

$autoload	=	new autoloadClass();
$mecon		=	new condbClass();
$mecon2		=	new condbClass();
$formClass		=	new formClass();
$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']) : "";
 
$resultQuery 		= "SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate as ReservationDate, r.Model as Model,r.Project as Project, r.Broker as Broker, r.Agent as Agent, bpm.Discount as Discount, r.Phase as Phase, r.PricelistDate as PricelistDate, bpm.NetEquity as PFNet, bpm.TermsOfPayment as TermsOfPayment, bpm.PaymentMode as PaymentMode, m.HouseConstruction as HouseConstruction FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id "; 
$sql			= $resultQuery; 
$mecon->query($sql);
$result			= $mecon->resultset(); 
$totalData		= count($result);
$totalFiltered	=	$totalData;

$sql			=	$resultQuery." WHERE r.Status='active' ";

if(!empty($filterSearch)) { 
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql.=" AND (r.Block LIKE '%".$ind."%' "; 
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName, r.MiddleName) LIKE '%".$ind."%' "; 
		$sql.=" OR r.Lot LIKE '%".$ind."%' "; 
		$sql.=" OR r.Model LIKE '%".$ind."%' "; 
		$sql.=" OR m.HouseConstruction LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ";  
		$sql.=" OR r.Broker LIKE '%".$ind."%' "; 
		$sql.=" OR r.Agent LIKE '%".$ind."%' "; 
		$sql.=" OR bpm.TermsOfPayment LIKE '%".$ind."%' "; 
		$sql.=" OR bpm.Discount LIKE '%".$ind."%' ";  
		$sql.=" OR bpm.NetEquity LIKE '%".str_replace(",","",$ind)."%' )";  
	}
}

if(!empty($monthFrom) && !empty($monthTo)){
		$sql .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
	}else if( !empty($monthFrom)){
		$sql .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
	}else if( !empty($monthTo)){
		$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
	} 
 
$mecon->query($sql);
$result = $mecon->resultset(); 
$totalFiltered = count($result);  


$mecon->query($sql);
$data = array();
$main = $mecon->resultset();


//Counter 
$sql2 = "SELECT r.Id as Id, COUNT(r.Model) as ModelCount, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName, r.MiddleName) as Fullname, r.ReservationDate as ReservationDate, r.Model as Model,r.Broker as Broker, r.Agent as Agent, bpm.Discount, r.Phase as Phase, r.Project as Project, r.PricelistDate as PricelistDate, bpm.NetEquity as PFNet, bpm.TermsOfPayment as TermsOfPayment, bpm.PaymentMode as PaymentMode, m.HouseConstruction as HouseConstruction FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id "; 
$sql2 .= " WHERE r.Status='active' "; 
if(!empty($filterSearch)) { 
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql2.=" AND (r.Id LIKE '%".$ind."%' ";
		$sql2.=" OR m.HouseConstruction LIKE '%".$ind."%' ";
		$sql2.=" OR r.Block LIKE '%".$ind."%' ";
		$sql2.=" OR CONCAT(r.LastName,', ',r.FirstName, r.MiddleName) LIKE '%".htmlentities($ind)."%' "; 
		$sql2.=" OR r.Lot LIKE '%".$ind."%' "; 
		$sql2.=" OR r.Model LIKE '%".$ind."%' "; 
		$sql2.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ";  
		$sql2.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql2.=" OR r.Broker LIKE '%".$ind."%' "; 
		$sql2.=" OR r.Agent LIKE '%".$ind."%' "; 
		$sql2.=" OR bpm.TermsOfPayment LIKE '%".$ind."%' "; 
		$sql2.=" OR bpm.Discount LIKE '%".$ind."%' ";  
		$sql2.=" OR bpm.NetEquity LIKE '%".str_replace(",","",$ind)."%' )"; 
		//$sql2.=" OR IF((SELECT bpm.TermsOfPayment FROM buyer_payment_monitoring bpm WHERE bpm.ReservationId=r.Id)=NULL OR (SELECT bpm.TermsOfPayment FROM buyer_payment_monitoring bpm WHERE bpm.ReservationId=r.Id)='0','cash',(SELECT bpm.TermsOfPayment FROM buyer_payment_monitoring bpm WHERE bpm.ReservationId=r.Id)) LIKE '%".$ind."%' )";
	}
}

if( !empty($monthFrom) && !empty($monthTo) ){
    $sql2 .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
}else if( !empty($monthFrom)){
    $sql2 .= " AND r.ReservationDate > ('".date('Y-m-d', strtotime($monthFrom))."') ";
}else if( !empty($monthTo)){
    $sql2 .= " AND r.ReservationDate > ('".date('Y-m-d', strtotime($monthTo))."') ";
}
$sql2 .= " GROUP BY Model";
$mecon2->query($sql2);
$data2 = array(); 
$mainCtr = array();
$mainCtr = $mecon2->resultset();

$counterModel 	= count($mainCtr); 
$arr1M 		= array();
$arr2M		= array();  
$arr3M 		= array();
for($a=0; $a<$counterModel; $a++){  
	$arr1M[0]	= number_format($mainCtr[$a]['ModelCount'],0);
	$arr1M[1]	= trim(html_entity_decode(stripslashes($mainCtr[$a]['Model'])));  
	$arr2M[] 	= $arr1M;
}
$arr3M['data'] = $arr2M;
/**************/


$counter 	= count($main); 
$arr1 		= array();
$arr2		= array();  
$arr3 		= array();
for($a=0; $a<$counter; $a++){  
	$arr1[0]	= '';
	$arr1[1]	= $a+1; 
	$arr1[2]	= (!empty($main[$a]['ReservationDate'])  && ($main[$a]['ReservationDate']!='1970-01-01') ) ? $main[$a]['ReservationDate'] : "";
	$arr1[3]	= trim(html_entity_decode(stripslashes($main[$a]['Fullname'])));
	$arr1[4]	= $main[$a]['Block'];
	$arr1[5]	= $main[$a]['Lot'];
	$arr1[6]	= trim(html_entity_decode(stripslashes($main[$a]['Model'])));
	
//	$arr1[7]	= $main[$a]['PricelistStatus'];
	
	
				$pricedate	= html_entity_decode(stripslashes($main[$a]['PricelistDate'])); 
				$phase		= html_entity_decode(stripslashes($main[$a]['Phase'])); 
				$block		= html_entity_decode(stripslashes($main[$a]['Block'])); 
				$lot		= html_entity_decode(stripslashes($main[$a]['Lot'])); 
				$project	= html_entity_decode(stripslashes($main[$a]['Project'])); 
				$models		= html_entity_decode(stripslashes($main[$a]['Model'])); 
				
				$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);		
				//$arr1[7] = $other['pricelist_status'];
			
	$arr1[7]	= trim(html_entity_decode(stripslashes($main[$a]['HouseConstruction'])));
	$arr1[8]	= (!empty($main[$a]['PFNet'])) ? number_format($main[$a]['PFNet'],2) : "";
	$arr1[9]	= number_format($main[$a]['Discount'],2);
	$arr1[10]	= ($main[$a]['PaymentMode']=="cash") ? "cash" : $main[$a]['TermsOfPayment'];
	$arr1[11]	= trim(html_entity_decode(stripslashes($main[$a]['Broker'])));
	$arr1[12]	= trim(html_entity_decode(stripslashes($main[$a]['Agent'])));
	$arr2[] 	= $arr1;
}
$arr3['data'] = $arr2;
$fileName    = "monthly_sales_reservation.xls";
$objPHPExcel = $objReader->load("templates/monthly_sales_reservation.xls"); 
$baseRowa = 6;
$modCtr = "";
for($b=0; $b<count($arr3M['data']); $b++){ 
$rowa = $baseRowa + $b + 1;
$objPHPExcel->getActiveSheet()->setCellValue('H'.$rowa, $arr3M['data'][$b][0])->setCellValue('G'.$rowa, $arr3M['data'][$b][1]);
$modCtr += number_format($arr3M['data'][$b][0],0);
}
$rowy = $baseRowa + count($arr3M['data'])+1;

$dataCtr = count($arr3M['data']);
  
$objPHPExcel->getActiveSheet()->setCellValue('G'.$rowy, "TOTAL")->setCellValue('H'.$rowy, $modCtr);

if(!empty($monthFrom) && !empty($monthTo)){
$moFrom = date('m/d/Y', strtotime($monthFrom));
$moTo = date('m/d/Y', strtotime($monthTo));
$rangeDate = $moFrom." to ".$moTo;
$objPHPExcel->getActiveSheet(0)->mergeCells('A'.$rowy.':C'.$rowy);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$rowy, $rangeDate);
}  

//$rowy2 = $baseRowa + count($arr3M['data'])+1;
$rowy2 = count($arr3M['data'])+1;
$objPHPExcel->getActiveSheet()->insertNewRowBefore(11,$rowy2);  
$baseRow = 11 + $rowy2 + 1;
//$baseRow = 13;
$row = "";
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]);
			$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
								  
 }

$currentUser 	= $_SESSION['castalia_lastname'].", ".$_SESSION['castalia_firstname'];
$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
$objWriter->save('php://output'); 