<?php
error_reporting(0);
if (substr_count($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip')) ob_start("ob_gzhandler"); else ob_start();
session_start();  
include_once('../config/minho.php');
include_once('../config/autoloadClass.php');
$autoload		=	new autoloadClass(); 
$mecon			=	new condbClass();
$mecon2			=	new condbClass();
$requestData	=	$_REQUEST; 
$columns = array( 
	0	=>	'FullName', 
	1	=>	'Block', 
	2	=>	'Lot',
	3	=>	'Releases',
	4	=>	'Broker',
	5	=>	'BrokerAccount', 
	6	=>	'ReleaseBrokerAmount', 
	7	=>	'Agent', 
	8	=>	'AgentAccount', 
	9	=>	'ReleaseAgentAmount', 
	10	=>	'ReleaseDate' 
);    

$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 Status = 'active' ";

if(!empty($requestData['search']['value']) ) {
	$arr = explode(" ",$requestData['search']['value']);  
	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($requestData['columns'][0]['search']['value']) && !empty($requestData['columns'][1]['search']['value']) ){
			$sql .= " AND ReleaseDate BETWEEN ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') AND ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."')";
		}else if( !empty($requestData['columns'][0]['search']['value'])){
			$sql .= " AND ReleaseDate >= ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') ";
		}else if( !empty($requestData['columns'][1]['search']['value'])){
			$sql .= " AND ReleaseDate <= ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."') ";
		}

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

$sql       .= " GROUP BY BrokerName, AgentName, FullName, Releases  "; 
 


$mecon->query($sql);
$result			= $mecon->resultset(); 
$totalData		= count($result);
$totalFiltered	=	$totalData;

//$sql	   .=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."   LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 
 
$sql .= " ORDER BY Broker, Agent, CAST(Releases AS UNSIGNED INTEGER)  ,cast(Block as unsigned) asc, cast(Lot as unsigned) asc  LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 
$data = array(); 
$mecon->query($sql);
$result			= $mecon->resultset(); 
  

for($a=0; $a<count($result); $a++){
				$nestedData=array();  
				$nestedData[] 	= strtoupper(stripslashes(html_entity_decode($result[$a]['FullName'])));  
				$nestedData[] 	= $result[$a]['Block']; 
				$nestedData[] 	= $result[$a]['Lot']; 
				$nestedData[] 	= $result[$a]['Releases']; 
				$nestedData[] 	= strtoupper(stripslashes(html_entity_decode($result[$a]['Broker'])));  
				$nestedData[] 	= strtoupper(stripslashes(html_entity_decode($result[$a]['BrokerAccount'])));  
				$nestedData[] 	= (!empty($result[$a]['ReleaseBrokerAmount'])) 	? number_format($result[$a]['ReleaseBrokerAmount'],2) : 0.00;  
				$nestedData[] 	= strtoupper(stripslashes(html_entity_decode($result[$a]['Agent'])));  
				$nestedData[] 	= strtoupper(stripslashes(html_entity_decode($result[$a]['AgentAccount'])));  
				$nestedData[] 	= (!empty($result[$a]['ReleaseAgentAmount'])) 	? number_format($result[$a]['ReleaseAgentAmount'],2) : 0.00; 

				$date_released  = date_create($result[$a]['ReleaseDate']);
				 
				$nestedData[] 	= ($result[$a]['ReleaseDate'] != '1970-01-01')  ? date_format($date_released,'m/d/Y')  : ''; 
				$data[] 		= $nestedData;
			} 
$json_data = array(
			"draw"            => intval( $requestData['draw'] ),
			"recordsTotal"    => intval( $totalData ),
			"recordsFiltered" => intval( $totalFiltered ),
			"data"            => $data
			);
echo json_encode($json_data);
exit();
ob_end_flush(); 