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

$rangeStart = isset($_REQUEST['range_start']) ?  $_REQUEST['range_start'] : date('Y-m-d', strtotime(date('01-m-Y')));
$rangeEnd 	= isset($_REQUEST['range_end'])   ? $_REQUEST['range_end']	  : date("Y-m-t", strtotime(date('Y-m-d')));

$tobedate 	= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "yes"; 
  
//$resultQuery = "SELECT Year(r.ReservationDate) as ryear, r.Broker as Broker, r.Id as Id,  Count(case when month(r.ReservationDate)=1 then Id end) As January, Count(case when month(r.ReservationDate)=2 then Id end) As February, Count(case when month(r.ReservationDate)=3 then Id end) As March, Count(case when month(r.ReservationDate)=4 then Id end) As April, Count(case when month(r.ReservationDate)=5 then Id end) As May,  Count(case when month(r.ReservationDate)=6 then Id end) As June, Count(case when month(r.ReservationDate)=7 then Id end) As July, Count(case when month(r.ReservationDate)=8 then Id end) As August, Count(case when month(r.ReservationDate)=9 then Id end) As September, Count(case when month(r.ReservationDate)=10 then Id end) As October, Count(case when month(r.ReservationDate)=11 then Id end) As November, Count(case when month(r.ReservationDate)=12 then Id end) As December, COUNT(*) AS YTD, SUM(bpm.TCP) as Total_TCP, SUM(bpm.Discount) as Total_Discount, SUM(bpm.TCP - bpm.Discount) as Net_TCP, r.Status, r.ReservationStatus as ReservationStatus FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id ";


$resultQuery = "SELECT * FROM (
	
    SELECT '1st' as Releases, r.Status as Status, rb.FirstReleaseCheckbox as ReleaseCheckbox, rb.FirstReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.FirstReleaseAgentAmount as ReleaseAgentAmount, rb.FirstReleaseDate as ReleaseDate, 
	 
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount ,
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.FirstReleaseCheckbox='on'  
    
    UNION ALL 
    
    SELECT '2nd' as Releases, r.Status as Status, rb.SecondReleaseCheckbox as ReleaseCheckbox, rb.SecondReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.SecondReleaseAgentAmount as ReleaseAgentAmount, rb.SecondReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.SecondReleaseCheckbox='on'  
        
    UNION ALL 
    
    SELECT '3rd' as Releases, r.Status as Status, rb.ThirdReleaseCheckbox as ReleaseCheckbox, rb.ThirdReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.ThirdReleaseAgentAmount as ReleaseAgentAmount, rb.ThirdReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.ThirdReleaseCheckbox='on'  
        
    UNION ALL 
    
    SELECT '4th' as Releases, r.Status as Status, rb.FourthReleaseCheckbox as ReleaseCheckbox, rb.FourthReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.FourthReleaseAgentAmount as ReleaseAgentAmount, rb.FourthReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.FourthReleaseCheckbox='on'  
    
    UNION ALL 
    
    SELECT '5th' as Releases, r.Status as Status, rb.FifthReleaseCheckbox as ReleaseCheckbox, rb.FifthReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.FifthReleaseAgentAmount as ReleaseAgentAmount, rb.FifthReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.FifthReleaseCheckbox='on'  
    
    UNION ALL 
    
    SELECT '6th' as Releases, r.Status as Status, rb.SixReleaseCheckbox as ReleaseCheckbox, rb.SixReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.SixReleaseAgentAmount as ReleaseAgentAmount, rb.SixReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.SixReleaseCheckbox='on' 

    UNION ALL 
    
    SELECT '7th' as Releases, r.Status as Status, rb.SevenReleaseCheckbox as ReleaseCheckbox, rb.SevenReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.SevenReleaseAgentAmount as ReleaseAgentAmount, rb.SevenReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.SevenReleaseCheckbox='on'  
    
    UNION ALL 
    
    SELECT '8th' as Releases, r.Status as Status, rb.EightReleaseCheckbox as ReleaseCheckbox, rb.EightReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.EightReleaseAgentAmount as ReleaseAgentAmount, rb.EightReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.EightReleaseCheckbox='on'  

    UNION ALL 
    
    SELECT '9th' as Releases, r.Status as Status, rb.NineReleaseCheckbox as ReleaseCheckbox, rb.NineReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.NineReleaseAgentAmount as ReleaseAgentAmount, rb.NineReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.NineReleaseCheckbox='on'  

	UNION ALL 
    
    SELECT '10th' as Releases, r.Status as Status, rb.TenReleaseCheckbox as ReleaseCheckbox, rb.TenReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.TenReleaseAgentAmount as ReleaseAgentAmount, rb.TenReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.TenReleaseCheckbox='on'  

	UNION ALL 
    
    SELECT '11th' as Releases, r.Status as Status, rb.ElevenReleaseCheckbox as ReleaseCheckbox, rb.ElevenReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.ElevenReleaseAgentAmount as ReleaseAgentAmount, rb.ElevenReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.ElevenReleaseCheckbox='on'  
    
	UNION ALL 
    
    SELECT '12th' as Releases, r.Status as Status, rb.TwelveReleaseCheckbox as ReleaseCheckbox, rb.TwelveReleaseBrokerAmount as ReleaseBrokerAmount, 
    rb.TwelveReleaseAgentAmount as ReleaseAgentAmount, rb.TwelveReleaseDate as ReleaseDate, 
	
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as Broker,    
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Id=ba.Id AND brokeragent.Realty=ba.Realty AND brokeragent.Fullname=ba.Fullname AND brokeragent.BrokerOrAgent='broker') as BrokerAccount, 
    
    (SELECT Fullname FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as Agent,     
    (SELECT AccountNumber FROM brokeragent WHERE brokeragent.Realty=ba.Realty AND LOWER(brokeragent.Fullname)=LOWER(r.Agent) AND brokeragent.BrokerOrAgent='agent'  AND LOWER(brokeragent.Realty)=LOWER(r.Broker) LIMIT 1) as AgentAccount,
    
    r.Broker as BrokerName, r.Agent as AgentName, 
    
    CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName,  r.Block as Block, r.Lot as Lot 
	FROM reservation r 
 	LEFT JOIN partial_commission_releases rb ON rb.ReservationId=r.Id 
	LEFT JOIN brokeragent ba ON ba.Realty=r.Broker
	WHERE r.Status = 'active' AND rb.TwelveReleaseCheckbox='on'  
    
) reserve ";
  
   
//$sql			=	$resultQuery."   WHERE r.Status = 'active' AND bpm.TCP IS NOT NULL "; 
$sql			=	$resultQuery."   WHERE Status = 'active' ";
 
if(!empty($filterSearch) ) {
	$arr = explode(" ",$filterSearch);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		 
		$sql.=" AND ( YEAR(ReleaseDate) LIKE '%".$ind."%' ";  
		$sql.=" OR FullName LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR BrokerName LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR ReleaseBrokerAmount LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR ReleaseAgentAmount LIKE '%".htmlentities($ind)."%' "; 
		$sql.=" OR AgentAccount LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR BrokerAccount LIKE '%".htmlentities($ind)."%' "; 
		$sql.=" OR Broker LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Agent LIKE '%".htmlentities($ind)."%' "; 
		$sql.=" OR AgentName LIKE '%".htmlentities($ind)."%' ) ";   
	}
}

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

//$sql 	.= " GROUP BY Year(r.ReservationDate), r.Broker, r.ReservationStatus "; 
$sql       .= " GROUP BY BrokerName, AgentName, FullName, Releases  "; 
//$sql  	.= " ORDER BY r.Broker asc, r.ReservationStatus asc, Year(r.ReservationDate) desc "; 
$sql .= " ORDER BY Broker, Agent, CAST(Releases AS UNSIGNED INTEGER), 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]	= trim(strtoupper(stripslashes(html_entity_decode($main[$a]['FullName']))));
		$arr1[1]	= trim($main[$a]['Block']);
		$arr1[2]    = trim($main[$a]['Lot']);
		$arr1[3]    = trim($main[$a]['Releases']);
		$arr1[4]    = trim($main[$a]['Broker']);
		$arr1[5]    = trim($main[$a]['BrokerAccount']);
		$arr1[6]    = number_format(trim($main[$a]['ReleaseBrokerAmount']),2);  
		$arr1[7]    = trim($main[$a]['Agent']);
		$arr1[8]    = trim($main[$a]['AgentAccount']);
		$arr1[9]    =  number_format(trim($main[$a]['ReleaseAgentAmount']),2);  
		$arr1[10]   = trim($main[$a]['ReleaseDate']);  
   
   	$arr2[] 	= $arr1;
}

$arr3['data'] 	= $arr2;
 
$fileName    	= "report_partial_commission_releases_schedule.xls";
$objPHPExcel 	= $objReader->load("templates/report_partial_commission_releases_schedule.xls"); 
$baseRow 		= 3;
  



$objPHPExcel->getActiveSheet()->setCellValue('L1', '');
$objPHPExcel->getActiveSheet()->setCellValue('M1', '');
$objPHPExcel->getActiveSheet()->setCellValue('N1', '');
$objPHPExcel->getActiveSheet()->setCellValue('O1', '');
$objPHPExcel->getActiveSheet()->setCellValue('P1', '');
$objPHPExcel->getActiveSheet()->setCellValue('Q1', '');





 //$objPHPExcel->getActiveSheet()->mergeCells('P1:Q1');
 //$objPHPExcel->getActiveSheet()->setCellValue('P3', '#');
 //$objPHPExcel->getActiveSheet()->setCellValue('Q3', 'Amount');
 //$objPHPExcel->getActiveSheet()->getStyle("P3:Q3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
 
 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][0])
											  ->setCellValue('B'.$row, $arr3['data'][$a][1])
											  ->setCellValue('C'.$row, $arr3['data'][$a][2])
											  ->setCellValue('D'.$row, $arr3['data'][$a][3])
											  ->setCellValue('E'.$row, $arr3['data'][$a][4])
											  ->setCellValue('F'.$row, $arr3['data'][$a][5])
											  ->setCellValue('G'.$row, $arr3['data'][$a][6])
											  ->setCellValue('H'.$row, $arr3['data'][$a][7])
											  ->setCellValue('I'.$row, $arr3['data'][$a][8])
											  ->setCellValue('J'.$row, $arr3['data'][$a][9])
											  ->setCellValue('K'.$row, $arr3['data'][$a][10]); 
		
		$objPHPExcel->getActiveSheet()->getStyle('K'.$row)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
}  


$objPHPExcel->getActiveSheet()->getStyle("C3:Q10000")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
 
//$objPHPExcel->getActiveSheet()->getStyle("P3:Q3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
//$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
//$tcounter = $counter + 3;
//$objPHPExcel->getActiveSheet()->getStyle('A3:Q'.$tcounter)->applyFromArray($styleArray);


//labels
//$objPHPExcel->getActiveSheet()->mergeCells('A1:A2');
/*
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'BUYER');

//$objPHPExcel->getActiveSheet()->mergeCells('B1:B2');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'BLOCK');

//$objPHPExcel->getActiveSheet()->mergeCells('C1:C2');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'LOT');

//$objPHPExcel->getActiveSheet()->mergeCells('D1:D2');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'RELEASES');

//$objPHPExcel->getActiveSheet()->mergeCells('E1:G1');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'BROKER');
$objPHPExcel->getActiveSheet()->setCellValue('E2', 'NAME');
$objPHPExcel->getActiveSheet()->setCellValue('F2', 'ACCOUNT NO.');
$objPHPExcel->getActiveSheet()->setCellValue('G2', 'AMOUNT');

//$objPHPExcel->getActiveSheet()->mergeCells('H1:J1');
$objPHPExcel->getActiveSheet()->setCellValue('H1', 'AGENT');
$objPHPExcel->getActiveSheet()->setCellValue('H2', 'NAME');
$objPHPExcel->getActiveSheet()->setCellValue('I2', 'ACCOUNT NO.');
$objPHPExcel->getActiveSheet()->setCellValue('J2', 'AMOUNT');

$objPHPExcel->getActiveSheet()->setCellValue('K1', 'DATE RELEASED');
*/

$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

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