<?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();
$formClass		=	new formClass();
$requestData	=	$_REQUEST; 
$columns = array(
	0	=>	'Id',
	1	=>	'ReservationDate', 
	2	=>	'Block',
	3	=>	'Lot', 
	4	=>	'Id', 
	5	=>	'Id', 
	6	=>	'FullName',
	7	=>	'LoanableAmount', 
	8	=>	'ScheduleLoan', 
	9	=>	'ToBeDeliveredDate', 
	10	=>	'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.ReservationStatus as ReservationStatus, r.Id as Id, bpm.LoanableAmount as LoanableAmount, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.PricelistDate as PricelistDate, r.Phase as Phase, r.Project as Project, r.Model as Model, r.ReservationDate as ReservationDate, r.Project, r.Model, r.ScheduleLoan as ScheduleLoan, r.ToBeDeliveredDate as ToBeDeliveredDate, r.StatusUpdateDate as StatusUpdateDate FROM reservation r LEFT JOIN buyer_payment_monitoring bpm ON bpm.ReservationId=r.Id  "; 
 
$sql			=	$resultQuery."  WHERE r.Status = 'active' AND bpm.Financing='pagibig' ";
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 r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' "; 
		$sql.=" OR r.ScheduleLoan = '".strtolower(str_replace(" ","_",$requestData['search']['value']))."' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) 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))."') ";
		}
		
		
}
$mecon->query($sql);
$result			= $mecon->resultset(); 
$totalData		= count($result);
$totalFiltered	=	$totalData;
  
  
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."    ".$requestData['order'][0]['dir']."  ,cast(r.Block as unsigned) asc, cast(r.Lot as unsigned) asc  LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 

$detArr = array();
$data = array(); 
$mecon->query($sql);
$result			= $mecon->resultset(); 
		$selectBox = "";
		$hao = "";
		for($a=0; $a<count($result); $a++){
			$nestedData=array();  
			$hao = $a+1;
			if($result[$a]['ScheduleLoan']=='delivered'){  
			$nestedData[] = ' <span style="color:#ff0000" class="loan_heart glyphicon glyphicon-heart" id="loan_heart'.$result[$a]['Id'].'" >&nbsp;</span> '.$hao;
			 
			}else{
			$nestedData[] = ' <span class="loan_heart" id="loan_heart'.$result[$a]['Id'].'" >&nbsp;</span> '.$hao;
			}
			
			
			$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ReservationDate'])); 
			$nestedData[] = html_entity_decode(stripslashes($result[$a]['Block']));
			$nestedData[] = html_entity_decode(stripslashes($result[$a]['Lot'])); 
			//$nestedData[] = $result[$a]['LotSize'];
			$pricedate	= html_entity_decode(stripslashes($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'])); 
			
			$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);		
			$nestedData[] = $other['lot_size'];
			$nestedData[] = $other['floor_area'];
			$nestedData[] = strtoupper(html_entity_decode(stripslashes($result[$a]['Fullname'])));
			$nestedData[] = number_format($result[$a]['LoanableAmount'],2);    
			
			//$nestedData[] = '<input type="checkbox" id="schedule_loan_checklist'.$result[$a]['Id'].'" value="yes" name="schedule_loan_checklist[]" class="schedule_loan_checklist" '.$checked.' onclick="updateScheduleLoanChecklist('.$result[$a]['Id'].')" >';
			 
			if($result[$a]['ScheduleLoan']=="" && $result[$a]['ScheduleLoan']==NULL){
				$result[$a]['ScheduleLoan'] = "not_delivered";
				$delivered_checked = ($result[$a]['ScheduleLoan']=='delivered') ? "selected" : "";
				$tobe_deliver_checked = ($result[$a]['ScheduleLoan']=='to_be_delivered') ? "selected" : "";
				$not_deliver_checked = ($result[$a]['ScheduleLoan']=='not_delivered') ? "selected" : "";
			}else{
				$delivered_checked = ($result[$a]['ScheduleLoan']=='delivered') ? "selected" : "";
				$tobe_deliver_checked = ($result[$a]['ScheduleLoan']=='to_be_delivered') ? "selected" : "";
				$not_deliver_checked = ($result[$a]['ScheduleLoan']=='not_delivered') ? "selected" : "";
			}
			
			
			$selectBox = '<select id="schedule_loan_checklist'.$result[$a]['Id'].'" value="yes" name="schedule_loan_checklist[]" class="form-control schedule_loan_checklist" style="width:200px"  onchange="updateScheduleLoanChecklist('.$result[$a]['Id'].')" ><option value="">- Select One -</option><option value="not_delivered" '.$not_deliver_checked.'>Not Delivered</option><option value="to_be_delivered" '.$tobe_deliver_checked.'>To be Delivered</option><option value="delivered" '.$delivered_checked.'>Delivered</option></select>';
			$nestedData[] = $selectBox;
			
	if($result[$a]['ScheduleLoan']=="to_be_delivered" || $result[$a]['ScheduleLoan']=="delivered"){
			$tobe = ((date('m/d/Y', strtotime($result[$a]['ToBeDeliveredDate']))!='01/01/1970') && ($result[$a]['ToBeDeliveredDate']!='')) ? date('m/d/Y', strtotime($result[$a]['ToBeDeliveredDate'])) : "";
			$nestedData[] = '<div class="devdate'.$result[$a]['Id'].'">'.$tobe.'</div>';
	}else{
		$nestedData[] = '<span class="devdate'.$result[$a]['Id'].'">&nbsp;</span>'; 
	}
			
			
			$nestedData[] = strtoupper(html_entity_decode(stripslashes($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();
?>