<?  
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	=>	'ReservationDate',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'FullName', 
	5	=>	'TakeOutDate',
	6 	=>	'TakeOutAmount',
	7	=>	'MRI',
	8	=>	'DifferenceApprovedVSLoanable',
	9	=>	'Adjustment',
	10	=>	'PagibigLoanableAmount',
	11	=>	'Misc',
	12	=>	'MoveInMRI',
	13	=>	'MoveInOthers',
	14	=>	'RefillingFee',
	15	=>	'PagIbigUpgrading',
	//16	=>	'PagIbigLoanUpdate', //21JUN25
	16	=>	'MoveInAmount',  //21JUN25
	17	=>	'CollectedDate', //21JUN25
	18	=>	'DepositedDate', //21JUN25
	
	19	=>	'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, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ', r.MiddleName) as Fullname, r.ReservationDate as ReservationDate, r.ReservationStatus as ReservationStatus, bpm.DateRFDeposited as DateRFDeposited,bpm.StartOfEquity as StartOfEquity,bpm.TCP as TCP,bpm.LoanableAmount as LoanableAmount,bpm.NetEquity as NetEquity,bpm.Discount as Discount,bpm.PDCSubmitted as  PDCSubmitted, bpm.LackingPDC as LackingPDC, bpm.PDCStatus as PDCStatus, bpm.TermsOfPayment as TermsOfPayment,bpm.MonthlyEquity as MonthlyEquity,bpm.RemarksOnEquity as RemarksOnEquity,bpm.PaymentsMade as PaymentsMade,bpm.TakeOutAmount as TakeOutAmount,bpm.MRI as MRI,bpm.DifferenceApprovedVSLoanable as DifferenceApprovedVSLoanable,bpm.Misc as Misc,bpm.Adjustment as Adjustment,bpm.AdjustmentDate as AdjustmentDate,bpm.PagibigLoanableAmount as PagibigLoanableAmount,bpm.MoveInMRI as MoveInMRI,bpm.MoveInOthers as MoveInOthers,bpm.RefillingFee as RefillingFee,bpm.PagIbigUpgrading as PagIbigUpgrading,bpm.PagIbigLoanUpdate as PagIbigLoanUpdate,bpm.CollectedDate as CollectedDate,bpm.DepositedDate as DepositedDate,bpm.MoveInAmount as MoveInAmount,bpm.TakeOutDate as TakeOutDate, r.Project, r.Model, bpm.PaymentMode as PaymentMode FROM reservation r 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 (Block LIKE '%".$ind."%' "; 
		$sql.=" OR r.Project LIKE '%".$ind."%' ";
		$sql.=" OR r.Model LIKE '%".$ind."%' ";
		$sql.=" OR r.Lot LIKE '%".$ind."%' ";
		$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bpm.DateRFDeposited,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bpm.StartOfEquity,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bpm.TakeOutDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR bpm.TCP LIKE '%".$ind."%' ";
		$sql.=" OR bpm.Discount LIKE '%".$ind."%' ";
		$sql.=" OR bpm.LoanableAmount LIKE '%".$ind."%' ";
		$sql.=" OR bpm.NetEquity LIKE '%".$ind."%' ";
		$sql.=" OR bpm.TermsOfPayment LIKE '%".$ind."%' ";
		$sql.=" OR bpm.MonthlyEquity LIKE '%".$ind."%' ";
		$sql.=" OR bpm.LackingPDC LIKE '%".$ind."%' ";
		$sql.=" OR bpm.PDCStatus LIKE '%".strtolower(str_replace(" ","_",$ind))."%' ";
		$sql.=" OR bpm.RemarksOnEquity LIKE '%".$ind."%' ";
		$sql.=" OR bpm.TakeOutAmount LIKE '%".$ind."%' ";
		$sql.=" OR bpm.MRI LIKE '%".$ind."%' ";
		$sql.=" OR bpm.DifferenceApprovedVSLoanable LIKE '%".$ind."%' ";
		$sql.=" OR bpm.Adjustment LIKE '%".$ind."%' ";
		$sql.=" OR bpm.PagibigLoanableAmount LIKE '%".$ind."%' ";
		$sql.=" OR bpm.Misc LIKE '%".$ind."%' ";
		$sql.=" OR bpm.MoveInMRI LIKE '%".$ind."%' ";
		$sql.=" OR bpm.MoveInOthers LIKE '%".$ind."%' ";
		$sql.=" OR bpm.RefillingFee LIKE '%".$ind."%' ";
		$sql.=" OR bpm.PagIbigUpgrading LIKE '%".$ind."%' ";
		$sql.=" OR bpm.PagIbigLoanUpdate LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(bpm.CollectedDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bpm.DepositedDate,'%m/%d/%Y')) LIKE '%".$ind."%' "; 
		$sql.=" OR bpm.MoveInAmount LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%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']."   "; 

$mecon->query($sql);
$data = array();
$result = $mecon->resultset();  
 
		for($a=0; $a<count($result); $a++){
			$nestedData	=	array();
			$nestedData[] = $result[$a]['Id'];
			$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[] = '<span style="word-wrap:break-word; word-break:break-all;">'.wordwrap($result[$a]['Lot'],5,"<br>\n").'</span>';	
	 
			//$nestedData[] = html_entity_decode(stripslashes($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]['TakeOutDate']=='1970-01-01') || ($result[$a]['TakeOutDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['TakeOutDate']));
	$nestedData[] = number_format($result[$a]['TakeOutAmount'],2); 
	$nestedData[] = number_format($result[$a]['MRI'],2); 
	$nestedData[] = number_format($result[$a]['DifferenceApprovedVSLoanable'],2); 
	$nestedData[] = number_format($result[$a]['Adjustment'],2);  
	$nestedData[] = number_format($result[$a]['PagibigLoanableAmount'],2);
	$nestedData[] = number_format($result[$a]['Misc'],2); 
	$nestedData[] = number_format($result[$a]['MoveInMRI'],2); 
	$nestedData[] = number_format($result[$a]['MoveInOthers'],2); 
	$nestedData[] = number_format($result[$a]['RefillingFee'],2); 
	$nestedData[] = number_format($result[$a]['PagIbigUpgrading'],2); 
	//$nestedData[] = number_format($result[$a]['PagIbigLoanUpdate'],2); //21JUN25

	$nestedData[] = number_format($result[$a]['MoveInAmount'],2);  //21JUN25

	$nestedData[] = (($result[$a]['CollectedDate']=='1970-01-01') || ($result[$a]['CollectedDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['CollectedDate'])); //21JUN25

	$nestedData[] = (($result[$a]['DepositedDate']=='1970-01-01') || ($result[$a]['DepositedDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['DepositedDate'])); //21JUN25
	

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