<?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	=>	'ReservationDate',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'FullName', 
	5	=>	'DateRFDeposited',
	6	=>	'StartOfEquity',
	7	=>	'Financing',
	8	=>	'TCP',
	9	=>	'Discount',
	10	=>	'LoanableAmount',
	11	=>	'NetEquity', 
	12	=>	'MonthlyEquity',
	13	=>	'TermsOfPayment', 
	14	=>	'LackingPDC',
	15	=>	'PDCStatus',
	16	=>	'RemarksOnEquity',
	17	=>	'ReservationStatus'
	/*,
	17	=>	'TakeOutDate',
	18	=>	'TakeOutAmount',
	19	=>	'MRI',
	20	=>	'DifferenceApprovedVSLoanable',
	21	=>	'Adjustment',
	22	=>	'PagibigLoanableAmount',
	23	=>	'Misc',
	24	=>	'MoveInMRI',
	25	=>	'MoveInOthers',
	26	=>	'RefillingFee',
	27	=>	'PagIbigUpgrading',
	28	=>	'PagIbigLoanUpdate',
	29	=>	'CollectedDate',
	30	=>	'DepositedDate',
	31	=>	'MoveInAmount', 
	32	=>	'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, bpm.Financing as Financing 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 bpm.Financing 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[] = html_entity_decode(stripslashes($result[$a]['Fullname']));
$nestedData[] = '<span style="word-wrap:break-word; word-break:break-all;">'.wordwrap($result[$a]['Fullname'],15,"<br>\n").'</span>';	
	
	$nestedData[] = (($result[$a]['DateRFDeposited']=='1970-01-01') || ($result[$a]['DateRFDeposited']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['DateRFDeposited']));
	 
	$nestedData[] = (!empty($result[$a]['StartOfEquity'])) ? date('m/d/Y', strtotime($result[$a]['StartOfEquity'])) : ""; 
	$nestedData[] = html_entity_decode(stripslashes($result[$a]['Financing'])); 
	$nestedData[] = number_format($result[$a]['TCP'],2); 
	$nestedData[] = number_format($result[$a]['Discount'],2); 
	$nestedData[] = number_format($result[$a]['LoanableAmount'],2); 
	$nestedData[] = number_format($result[$a]['NetEquity'],2);
	$nestedData[] = number_format($result[$a]['MonthlyEquity'],2); 
	$nestedData[] = ($result[$a]['PaymentMode']=="cash") ? "cash" : $result[$a]['TermsOfPayment'];
	
	$nestedData[] = $result[$a]['LackingPDC']; 
	$nestedData[] = strtoupper(str_replace("_"," ",$result[$a]['PDCStatus']));    
	//$nestedData[] = $result[$a]['RemarksOnEquity']; 

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

	$nestedData[] = strtoupper(html_entity_decode(stripslashes($result[$a]['ReservationStatus'])));
	/*$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); 

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

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

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