<?  
//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; 
$user_type = isset($_SESSION['castalia_user_type']) ? strip_tags(stripslashes(trim($_SESSION['castalia_user_type']))) : "";
$columns = array(
	0	=>	'Id',
	1	=>	'Id',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'FullName',
	5	=>	'rReservationDate',
	6	=>	'Financing',
	7	=>	'Broker',
	8	=>	'Agent',  
	9	=>	'EmployeeType',  
	10	=>	'Spouse',
	11	=>	'Address1',
	12	=>	'Address2',
	13	=>	'HomeTel',
	14	=>	'OfficeTel', 
	15	=>	'CellphoneNo',  
	16	=>	'EmployerName',  
	17	=>	'Position',  
	18	=>	'GrossIncome',   
	19	=>	'TinNo',   
	20	=>	'BuyerProfile',    
	21	=>	'ReservationStatus'
);  

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

$mainQuery 		= "SELECT r.Id as Id, r.Project as Project, r.Model as Model, r.ReservationDate as rReservationDate, r.Phase as Phase, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, r.Agent as Agent, r.Spouse as Spouse, r.HomeTel as HomeTel, r.CellphoneNo as CellphoneNo, r.SpouseContactNo as SpouseContactNo, r.Address1 as Address1, r.Address2 as Address2, r.EmployerName as EmployerName, r.EmployeeType as EmployeeType, r.GrossIncome as GrossIncome, r.DiscountPromo as DiscountPromo, r.Position as Position, r.BuyerProfile as BuyerProfile, r.SpouseIncome as SpouseIncome, r.TinNo as TinNo, r.OfficeTel as OfficeTel, r.AggregateIncome as AggregateIncome, r.NoCoBorrower as NoCoBorrower, r.ReservationStatus as ReservationStatus, r.Status as rStatus, r.EmployeeType as EmployeeType, bsf.TakeOut as TakeOut, bpm.Financing as Financing FROM  reservation r LEFT JOIN buyer_status_folder_pagibig bsf ON bsf.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id"; 
//$sql			= $mainQuery; 

$sql			=	$mainQuery."  WHERE r.Status = 'active' ";
$tobe 			= isset($_REQUEST['tobe_stat'])   ? $_REQUEST['tobe_stat']	  : "yes";
$tobeno 		= isset($_REQUEST['tobe_stat_no'])   ? $_REQUEST['tobe_stat_no']	  : "yes";
$tobedate 		= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "yes";
if($tobe=="yes" && $tobeno=="no"){
		$sql.=" AND (TakeOut!='1970-01-01' OR TakeOut!=NULL)"; 
}else if($tobe=="no" && $tobeno=="yes"){
		$sql.=" AND (TakeOut='1970-01-01' OR TakeOut IS NULL)"; 
}
if(!empty($requestData['search']['value']) ) { 
	$arr = explode(" ",$requestData['search']['value']);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql.=" AND (Block LIKE '".$ind."%' ";
		$sql.=" OR CONCAT(LastName,', ',FirstName,' ',MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '".$ind."%' "; 
		$sql.=" OR Broker LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR EmployeeType LIKE '%".$ind."%' ";
		$sql.=" OR Agent LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Model LIKE '%".$ind."%' ";  
		$sql.=" OR Spouse LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Project LIKE '%".$ind."%' ";
		$sql.=" OR EmployerName LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Position LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Address1 LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Address2 LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR HomeTel LIKE '".$ind."%' ";
		$sql.=" OR OfficeTel LIKE '".$ind."%' "; 
		$sql.=" OR CellphoneNo LIKE '".$ind."%' ";
		$sql.=" OR GrossIncome LIKE '".$ind."%' ";
		$sql.=" OR TinNo LIKE '".$ind."%' ";
		$sql.=" OR BuyerProfile LIKE '".$ind."%' "; 
		$sql.=" OR ReservationStatus  LIKE '".$ind."%' ";
		$sql.=" OR Lot LIKE '".$ind."%' )"; 
	}
}

// getting records as per search parameters
/*
if( !empty($requestData['columns'][2]['search']['value']) ){   //block
    $sql .= " AND Block LIKE '".$requestData['columns'][2]['search']['value']."%' ";
}
if( !empty($requestData['columns'][3]['search']['value']) ){   //lot
    $sql .= " AND Lot LIKE '".$requestData['columns'][3]['search']['value']."%' ";
}
if( !empty($requestData['columns'][4]['search']['value']) ){   //fullname
    $sql .= " AND CONCAT(LastName,', ',FirstName,' ',MiddleName) LIKE '%".htmlentities($requestData['columns'][4]['search']['value'])."%' ";
}
if( !empty($requestData['columns'][5]['search']['value']) ){   //reservation date
    $sql .= " AND UPPER(DATE_FORMAT(ReservationDate,'%m/%d/%Y')) LIKE '%".htmlentities($requestData['columns'][5]['search']['value'])."%' "; 
}
*/
if($tobedate=="yes"){
	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 r.Id";
$mecon->query($sql);
$result			= $mecon->resultset(); 
$totalData		= count($result);
$totalFiltered	=	$totalData;

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


			 for($a=0; $a<count($result); $a++){
			 $nestedData=array();
				
				 
				 
				 
				 $buttonOptions = '<a href="index.php?pg=reservation&type=checklist_of_requirements&action=edit&id='.$result[$a]['Id'].'"><span class="btn btn-xs btn-info glyphicon glyphicon-ok" title="Checklist Requirements"></span></a>&nbsp;';
		
		//ADMIN ONLY OR LIAISON	 
		if($user_type=="admin" || $user_type=="liaison"): 		
				 $buttonOptions .= '<a href="index.php?pg=reservation&type=monitoring_folders&action=edit&id='.$result[$a]['Id'].'"><span class="btn btn-xs btn-success glyphicon glyphicon-folder-open" title="Monitoring of Folders to be Delivered"></span></a>&nbsp;';
		endif;		 

				 $buttonOptions .= '<a href="index.php?pg=reservation&type=reservation&action=edit&id='.$result[$a]['Id'].'"><span class="btn btn-xs btn-primary glyphicon glyphicon-pencil" title="Update Reservation"></span></a>&nbsp;';

		//ADMIN ONLY
		if($user_type=="admin"): 				 
				 $buttonOptions .= '<span class="btn btn-xs btn-danger glyphicon glyphicon-trash" title="Delete Reservation" onclick="deleteTransaction('.$result[$a]['Id'].')" ></span>';
		endif;		 

				if($result[$a]['ReservationStatus']=='cancelled'){
					$buttonOptions .='&nbsp;<span class="btn btn-xs btn-warning glyphicon glyphicon-share" title="Activate Cancelled Reservation" onclick="undoCancelReservation('.$result[$a]['Id'].')"></span>'; 
				}else{
					$buttonOptions .='&nbsp;<span class="btn btn-xs btn-warning glyphicon glyphicon-remove" title="Cancel Reservation" onclick="cancelReservation('.$result[$a]['Id'].')"></span>';
				}				
		
		if($user_type!="assistant_accountant"):
					$nestedData[]	= $buttonOptions; 
		else:
					$nestedData[]	= "";
		endif;
		
		
				//$nestedData[]	= $a+1; 
				//$hao = $a+1;

				$hao = $result[$a]['Id'];

			if(($result[$a]['TakeOut']!='1970-01-01') && ($result[$a]['TakeOut']!='') && ($result[$a]['TakeOut']!=NULL)){  
			$nestedData[] = ' <span style="color:#ff0000" class="loan_heart2 glyphicon glyphicon-home" id="loan_heart2'.$result[$a]['Id'].'" >&nbsp;</span> '.$hao;
			 
			}else{
			$nestedData[] = ' <span class="loan_heart2" id="loan_heart2'.$result[$a]['Id'].'" >&nbsp;</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '.$hao;
			}
				
				
				$nestedData[]	= trim($result[$a]['Block']);
				$nestedData[]	= intval(trim($result[$a]['Lot'])); 

				$fullname = strtoupper(trim(html_entity_decode(stripslashes($result[$a]['FullName']))));
			 
				$nestedData[] = '<a href="index.php?pg=reservation&type=reservation&action=view&id='.$result[$a]['Id'].'">'.wordwrap($fullname,25,"<br>\n").'</a>';
				  
				$nestedData[] = (($result[$a]['rReservationDate']=='1970-01-01') || ($result[$a]['rReservationDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['rReservationDate'])); 
				$nestedData[] =  stripslashes($result[$a]['Financing']);

				$brokerName = stripslashes($result[$a]['Broker']); 
				$nestedData[] =  wordwrap($brokerName,25,"<br>\n");

				$agentName =  stripslashes($result[$a]['Agent']); 
				$nestedData[] =  wordwrap($agentName,25,"<br>\n");

				$nestedData[] =  stripslashes($result[$a]['EmployeeType']); 

				$spouse =  stripslashes($result[$a]['Spouse']); 
				$nestedData[] =  wordwrap($spouse,25,"<br>\n");
 				
 				$address =  stripslashes($result[$a]['Address1']); 
				$nestedData[] =  wordwrap($address,50,"<br>\n");
 				
 				$email =  $result[$a]['Address2']; 
				$nestedData[] =  wordwrap($email,200,"<br>\n");

				$nestedData[] =  $result[$a]['HomeTel'];
				$nestedData[] =  $result[$a]['OfficeTel'];
				$nestedData[] =  $result[$a]['CellphoneNo'];
				
				$employerName =  $result[$a]['EmployerName']; 
				$nestedData[] =  wordwrap($employerName,25,"<br>\n");
 				
 				$position	  =  $result[$a]['Position'];
				$nestedData[] =  wordwrap($position,25,"<br>\n");	

				$nestedData[] =  number_format($result[$a]['GrossIncome'],2);  
				$nestedData[] =  $result[$a]['TinNo'];

				$buyerProfile =  $result[$a]['BuyerProfile']; 
				$nestedData[] =  wordwrap($buyerProfile,25,"<br>\n"); 

				$nestedData[] =  strtoupper($result[$a]['ReservationStatus']);  
				 
				$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();