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

$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 reservation.Id as Id, reservation.Block as Block, reservation.Lot as Lot, CONCAT(reservation.LastName,', ',reservation.FirstName,' ', reservation.MiddleName) as Fullname, reservation.ReservationDate as ReservationDate, reservation.ReservationStatus as ReservationStatus, reservation.Project as Project, reservation.Model as Model FROM reservation LEFT JOIN buyer_payment_monitoring ON buyer_payment_monitoring.ReservationId=reservation.Id"; 

//$sql			=	$resultQuery."  WHERE reservation.Status = 'active' AND buyer_payment_monitoring.PaymentMode='pag-ibig' ";
$sql			=	$resultQuery."  WHERE reservation.Status = 'active' AND UPPER(buyer_payment_monitoring.Financing)='PAGIBIG' ";
if(!empty($requestData['search']['value']) ) { 
	$arr = explode(" ",$requestData['search']['value']);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);		
		$sql.=" AND ( reservation.Project LIKE '%".$ind."%' ";
		$sql.=" OR reservation.Model LIKE '%".$ind."%' ";
		$sql.=" OR reservation.Block LIKE '%".$ind."%' ";
		$sql.=" OR reservation.Lot LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(reservation.LastName,', ',reservation.FirstName,' ',reservation.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(reservation.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ) ";   
	}
}

if($tobedate=="yes"){
	if( !empty($rangeStart) && !empty($rangeEnd) ){
		$sql .= " AND reservation.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
	}else if( !empty($rangeStart)){
		$sql .= " AND reservation.ReservationDate >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
	}else if( !empty($rangeEnd)){
		$sql .= " AND reservation.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=postdated_pagibig_loan_amortization_details&rid='.$result[$a]['Id'].'"><span class="btn btn-xs btn-info glyphicon glyphicon-tag" title="Postdated Pag-ibig Loan Amortization"></span></a>';




			$nestedData[] = $result[$a]['Id'];
			$nestedData[] = $result[$a]['Block'];
			$nestedData[] = $result[$a]['Lot'];
			$nestedData[] = $result[$a]['Fullname']; 
			//$nestedData[] = date('m/d/Y', strtotime($result[$a]['ReservationDate']));
			//$nestedData[] = strtoupper($result[$a]['ReservationDate']);  
			$nestedData[] = (($result[$a]['ReservationDate']=='1970-01-01') || ($result[$a]['ReservationDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ReservationDate']));
			$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();