<?   
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();
$requestData	=	$_REQUEST; 
$columns = array(
	0	=>	'Agent', 
	1	=>	'Broker',
	2	=>	'ReservationStatus',
	3	=>	'ryear', 
	4	=>	'January',
	5	=>	'February',
	6	=>	'March',
	7	=>	'April', 
	8	=>	'May', 
	9	=>	'June', 
	10	=>	'July', 
	11	=>	'August', 
	12	=>	'September', 
	13	=>	'October', 
	14	=>	'November', 
	15	=>	'December',
	16	=>	'YTD',
	17	=>	'Net_TCP' 
);    

$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 r.Agent as Agent,
  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, r.Status, r.ReservationStatus as ReservationStatus, 
  SUM(bpm.TCP) as Total_TCP, SUM(bpm.Discount) as Total_Discount, SUM(bpm.TCP - bpm.Discount) as Net_TCP 
FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id ";

$sql			=	$resultQuery."   WHERE r.Status = 'active' AND bpm.TCP IS NOT NULL ";
if(!empty($requestData['search']['value']) ) {
	$arr = explode(" ",$requestData['search']['value']);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql.=" AND ( YEAR(r.ReservationDate) LIKE '%".$ind."%' ";  
		$sql.=" OR (SELECT COUNT(*) FROM reservation) LIKE '%".$ind."%' ";
		$sql.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
		$sql.=" OR r.Agent LIKE '%".htmlentities($ind)."%'  ";   
		$sql.=" OR r.Broker LIKE '%".htmlentities($ind)."%' ) ";   
	}
}
 
if($tobedate=="yes"){ 
		if( !empty($requestData['columns'][0]['search']['value']) && !empty($requestData['columns'][1]['search']['value']) ){
			$sql .= " AND r.ReservationDate 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 r.ReservationDate >= ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') ";
		}else if( !empty($requestData['columns'][1]['search']['value'])){
			$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."') ";
		}

		if( !empty($rangeStart) && !empty($rangeEnd) ){
			$sql .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
		}else if( !empty($rangeStart)){
			$sql .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
		}else if( !empty($rangeEnd)){
			$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($rangeEnd))."') ";
		}
} 
$sql       .= " GROUP BY Year(r.ReservationDate), r.Agent, r.Broker, r.ReservationStatus "; 
  
$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 r.Broker asc, r.Agent asc, r.ReservationStatus asc, Year(r.ReservationDate) desc  LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 

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

 
 
$data = array(); 
			 for($a=0; $a<count($result); $a++){
				$nestedData=array(); 
				//$nestedData[] = $a+1; 

				
				$agentName 		= strtoupper(stripslashes(html_entity_decode($result[$a]['Agent'])));
				$brokerName 	= strtoupper(stripslashes(html_entity_decode($result[$a]['Broker']))); 
				$nestedData[] 	= wordwrap($agentName,25,"<br>\n");
				$nestedData[]	= wordwrap($brokerName,25,"<br>\n");
				$nestedData[] 	= strtoupper(stripslashes(html_entity_decode($result[$a]['ReservationStatus']))); 
				$nestedData[] 	= stripslashes(html_entity_decode($result[$a]['ryear'])); 
				$nestedData[] 	= $result[$a]['January']; 
				$nestedData[] 	= $result[$a]['February']; 
				$nestedData[] 	= $result[$a]['March']; 
				$nestedData[] 	= $result[$a]['April']; 
				$nestedData[] 	= $result[$a]['May']; 
				$nestedData[] 	= $result[$a]['June']; 
				$nestedData[] 	= $result[$a]['July']; 
				$nestedData[] 	= $result[$a]['August']; 
				$nestedData[] 	= $result[$a]['September']; 
				$nestedData[] 	= $result[$a]['October']; 
				$nestedData[] 	= $result[$a]['November'];
				$nestedData[] 	= $result[$a]['December'];
				$nestedData[] 	= $result[$a]['YTD'];
				$nestedData[] 	= number_format($result[$a]['Net_TCP'],2);
				$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(); 