<?  
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; 
$formClass		=	new formClass();
$columns = array(
	0	=>	'Id',
	1	=>	'ReservationDate',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'Id',
	5	=>	'Id',
	6	=>	'FullName',
	7	=>	'LoanableAmount',
	8	=>	'MonitoringFolderDetails',
	9	=>	'MonitoringFolderDetails',
	10	=>	'MonitoringFolderDetails',
	11	=>	'MonitoringFolderDetails',
	12	=>	'MonitoringFolderDetails',
	13	=>	'MonitoringFolderDetails',
	14	=>	'MonitoringFolderDetails',
	15	=>	'MonitoringFolderDetails',
	16	=>	'MonitoringFolderDetails', 
	17	=>	'MonitoringFolderDetails', 
	18	=>	'MonitoringFolderDetails', 
	19	=>	'MonitoringFolderDetails', 
	20	=>	'MonitoringFolderDetails', 
	21	=>	'MonitoringFolderDetails', 
	22	=>	'MonitoringFolderDetails', 
	23	=>	'HouseConstruction',
	24	=>	'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')));
$tobedate 	= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "yes";
   
$resultQuery = "SELECT r.Id as Id, bpm.LoanableAmount as LoanableAmount, r.Phase as Phase, r.PricelistDate as PricelistDate, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname,  r.ReservationDate, m.HouseConstruction as HouseConstruction, m.MonitoringFolderDetails as MonitoringFolderDetails, r.Model, r.Project, r.ReservationStatus as ReservationStatus FROM reservation r LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id   ";

$sql			=	$resultQuery."  WHERE r.Status = 'active' ";
if(!empty($requestData['search']['value']) ) { 
	$arr = explode(" ",$requestData['search']['value']);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);		
		$sql.=" AND (r.Block LIKE '%".$ind."%' ";
		$sql.=" OR r.Lot LIKE '%".$ind."%' ";
		$sql.=" OR m.MonitoringFolderDetails LIKE '%".$ind."%' "; 
		$sql.=" OR m.HouseConstruction LIKE '%".$ind."%' "; 
		$sql.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
		$sql.=" OR r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$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))."') ";
		}
}

$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']."   "; 
 
$data = array(); 
$mecon->query($sql);
$result			= $mecon->resultset(); 

$detArr = array(); 
				$req_completed 		= "";	
				$signed_loan_docs 	= "";
				$sworn_cert 		= "";
				$tax_dec_land 		= "";
				$tax_dec_building 	= ""; 
				$tax_land_receipt 	= ""; 
				$tax_building_receipt 	= ""; 
				$building_permit 	= ""; 
				$electrical_permit 	= ""; 
				$sanitary_permit 	= ""; 
				$occupancy_permit 	= ""; 
				$road 	= ""; 
				$sidewalk_and_gutter 	= ""; 
				$meralco 	= ""; 
				$water 	= "";  
				
		for($a=0; $a<count($result); $a++){
			$nestedData=array(); 
			$nestedData[] = $a + 1;
			$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='') || ($result[$a]['ReservationDate']==NULL)) ? "" : date('m/d/Y', strtotime($result[$a]['ReservationDate']));
			$nestedData[] = $result[$a]['Block'];
			$nestedData[] = $result[$a]['Lot']; 
			//$nestedData[] = $result[$a]['LotSize'];  
			$pricedate	= (($result[$a]['PricelistDate']=='1970-01-01') || ($result[$a]['PricelistDate']=='') || ($result[$a]['PricelistDate']==NULL)) ? "" : date('Y-m-d', strtotime($result[$a]['PricelistDate']));
			$phase		= html_entity_decode(stripslashes($result[$a]['Phase'])); 
			$block		= html_entity_decode(stripslashes($result[$a]['Block'])); 
			$lot		= html_entity_decode(stripslashes($result[$a]['Lot'])); 
			$project	= html_entity_decode(stripslashes($result[$a]['Project'])); 
			$models		= html_entity_decode(stripslashes($result[$a]['Model'])); 
			if($pricedate!='1970-01-01' || $pricedate!='' || $pricedate!=NULL){
			$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);		
			$nestedData[] = $other['lot_size'];
			$nestedData[] = $other['floor_area'];
			//$nestedData[] = "";
			}else{
			$nestedData[] = "";
			$nestedData[] = "";
			}
			
			
			$nestedData[] = html_entity_decode($result[$a]['Fullname']);
			$nestedData[] = (!empty($result[$a]['LoanableAmount']) || $result[$a]['LoanableAmount']!=NULL)	? number_format($result[$a]['LoanableAmount'],2) : "";
			//$detArr = json_decode(str_replace('""','',$result[$a]['MonitoringFolderDetails']));
			$detArr = json_decode(str_replace('""','',$result[$a]['MonitoringFolderDetails']),true);
			 if(count($detArr)!=0){ 
					foreach($detArr as $key => $detInd){
						if($detInd=="req_completed"){	$req_completed = "OK";	} 
						if($detInd=="signed_loan_docs"){	$signed_loan_docs = "OK";	 } 
						if($detInd=="sworn_cert"){	 $sworn_cert = "OK";	 } 
						if($detInd=="tax_dec_land"){ $tax_dec_land = "OK";	 } 
						if($detInd=="tax_dec_building"){ $tax_dec_building = "OK";	 } 
						if($detInd=="tax_land_receipt"){ $tax_land_receipt = "OK";	 } 
						if($detInd=="tax_building_receipt"){ $tax_building_receipt = "OK";	 } 

						if($detInd=="building_permit"){	$building_permit = "OK"; } 
						if($detInd=="electrical_permit"){	$electrical_permit = "OK";	 } 
						if($detInd=="sanitary_permit"){	$sanitary_permit = "OK"; }  
						if($detInd=="occupancy_permit"){	$occupancy_permit = "OK";	 } 

						if($detInd=="road"){	$road = "OK";	 } 
						if($detInd=="sidewalk_and_gutter"){	$sidewalk_and_gutter = "OK";	 } 
						if($detInd=="meralco"){	$meralco = "OK";	 } 
						if($detInd=="water"){	$water = "OK";	 }
					}
 
						$nestedData[] = $req_completed; 
						$nestedData[] = $signed_loan_docs; 
						$nestedData[] = $sworn_cert; 
						$nestedData[] = $tax_dec_land; 
						$nestedData[] = $tax_dec_building; 
						$nestedData[] = $tax_land_receipt; 
						$nestedData[] = $tax_building_receipt; 
						$nestedData[] = $building_permit; 
						$nestedData[] = $electrical_permit; 
						$nestedData[] = $sanitary_permit; 
						$nestedData[] = $occupancy_permit; 
						$nestedData[] = $road; 
						$nestedData[] = $sidewalk_and_gutter; 
						$nestedData[] = $meralco; 
						$nestedData[] = $water;  
			 }else{
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
				$nestedData[] = "";
			 } 
			 $nestedData[] = strtoupper($result[$a]['HouseConstruction']);
			 $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();