<?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();
$requestData	=	$_REQUEST; 
/*
$columns = array(
	0	=>	'Id',
	1	=>	'Id',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'FullName',
	5	=>	'ReservationDate',
	6	=>	'BuyerOrientationDate',
	7	=>	'Conducted',
	8	=>	'Financing',
	9	=>	'PagibigTakeOutDate',
	10	=>	'HomeownerOrientation',
	11	=>	'Inspection',
	12	=>	'ReInspection',
	13	=>	'LetterSent',
	14	=>	'HouseAcceptance',
	15	=>	'PermitToOccupy',
	16	=>	'PermitToOccupyNumber',
	17	=>	'PTOStatus',
	18	=>	'ReservationStatus'
);*/

$columns = array(
	0	=>	'Id',
	1	=>	'Id',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'FullName',
	5	=>	'ReservationDate',
	6	=>	'BuyerOrientationDate',
	7	=>	'Conducted',
	8	=>	'Financing',
	9	=>	'PagibigTakeOutDate',
	10	=>	'HomeownerOrientation',
	11	=>	'Inspection', 
	12	=>	'LetterSent',
	13	=>	'HouseAcceptance',
	14	=>	'PermitToOccupy',
	15	=>	'PermitToOccupyNumber',
	16	=>	'PTOStatus',
	17	=>	'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, r.Block, r.Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate, r.ReservationStatus, r.Project, r.Model, bm.BuyerOrientationDate, bm.Conducted, bm.PagibigTakeOutDate, bm.HomeownerOrientation, bm.Inspection, bm.ReInspection, bm.LetterSent, bm.HouseAcceptance, bm.PermitToOccupy, bm.PermitToOccupyNumber, bm.PTOStatus as PTOStatus, bpm.Financing as Financing  FROM reservation r LEFT JOIN buyer_status_monitoring bm ON bm.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 r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$ind."%' ";
		$sql.=" OR bm.PTOStatus LIKE '%".$ind."%' ";
		$sql.=" OR bpm.Financing LIKE '%".$ind."%' ";
		$sql.=" OR bm.PermitToOccupyNumber LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%'  ";
		$sql.=" OR UPPER(DATE_FORMAT(bm.PagibigTakeOutDate,'%m/%d/%Y')) LIKE '%".$ind."%'  ";
		$sql.=" OR UPPER(DATE_FORMAT(bm.Conducted,'%m/%d/%Y')) LIKE '%".$ind."%'  ";
		$sql.=" OR UPPER(DATE_FORMAT(bm.HomeownerOrientation,'%m/%d/%Y')) LIKE '%".$ind."%'  ";
		$sql.=" OR UPPER(DATE_FORMAT(bm.Inspection,'%m/%d/%Y')) LIKE '%".$ind."%'  ";
		$sql.=" OR UPPER(DATE_FORMAT(bm.ReInspection,'%m/%d/%Y')) LIKE '%".$ind."%'  ";   
		$sql.=" OR UPPER(DATE_FORMAT(bm.LetterSent,'%m/%d/%Y')) LIKE '%".$ind."%'  ";
		$sql.=" OR UPPER(DATE_FORMAT(bm.HouseAcceptance,'%m/%d/%Y')) LIKE '%".$ind."%'  ";
		$sql.=" OR UPPER(DATE_FORMAT(bm.PermitToOccupy,'%m/%d/%Y')) LIKE '%".$ind."%'  ";
		$sql.=" OR UPPER(DATE_FORMAT(bm.BuyerOrientationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ) ";
	}
}

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))."') ";
	}
}

$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();
			$nestedData[] = '<a href="index.php?pg=payment&type=buyer_status_monitoring&action=edit&id='.$result[$a]['Id'].'"><span class="btn btn-xs btn-warning glyphicon glyphicon-heart" title="Buyer Status Monitoring"></span></a>';
			$nestedData[] = $result[$a]['Id'];
			$nestedData[] = $result[$a]['Block'];
			$nestedData[] = $result[$a]['Lot'];
			//$nestedData[] = $result[$a]['Fullname'];

$nestedData[] = '<span style="word-wrap:break-word; word-break:break-all;">'.wordwrap($result[$a]['Fullname'],20,"<br>\n").'</span>';

			$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ReservationDate']));
			$nestedData[] = (($result[$a]['BuyerOrientationDate']=='1970-01-01') || ($result[$a]['BuyerOrientationDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['BuyerOrientationDate']));
			$nestedData[] = $result[$a]['Conducted'];
			$nestedData[] = $result[$a]['Financing'];
			$nestedData[] = (($result[$a]['PagibigTakeOutDate']=='1970-01-01') || ($result[$a]['PagibigTakeOutDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['PagibigTakeOutDate']));
			$nestedData[] = (($result[$a]['HomeownerOrientation']=='1970-01-01') || ($result[$a]['HomeownerOrientation']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['HomeownerOrientation']));
			$nestedData[] = (($result[$a]['Inspection']=='1970-01-01') || ($result[$a]['Inspection']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['Inspection']));
			//$nestedData[] = (($result[$a]['ReInspection']=='1970-01-01') || ($result[$a]['ReInspection']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ReInspection']));
			$nestedData[] = (($result[$a]['LetterSent']=='1970-01-01') || ($result[$a]['LetterSent']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['LetterSent']));
			$nestedData[] = (($result[$a]['HouseAcceptance']=='1970-01-01') || ($result[$a]['HouseAcceptance']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['HouseAcceptance']));
			$nestedData[] = (($result[$a]['PermitToOccupy']=='1970-01-01') || ($result[$a]['PermitToOccupy']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['PermitToOccupy']));  
			$nestedData[] = strtoupper($result[$a]['PermitToOccupyNumber']); 
			/*$selected1 = "";
			$selected2 = "";
			if($result[$a]['PTOStatus']=='Temporary PTO'){
				$selected2 = 'selected';
			}else if($result[$a]['PTOStatus']=='with PTO'){
				$selected1 = 'selected';
			}*/
			//$curId = strtoupper($result[$a]['Id']);
			
			$nestedData[] = $result[$a]['PTOStatus'];
			
			//$nestedData[] = '<select onchange="updatePTOStatus(this.value,'.$curId.');" name="ptostatus" id="ptostatus" class="form-control"><option></option><option '.$selected1.' value="with PTO">with PTO</option><option '.$selected2.' value="Temporary PTO">Temporary PTO</option></select>'; 
			$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();
?> 