<?php
error_reporting(0);
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	=>	'ChequeDate',
	3	=>	'ChequeNo',
	4	=>	'Bank',
	5	=>	'Amount',
	6	=>	'Remarks',
	7	=>	'ChequeStatus',
	8	=>	'ReturnRemarks',
	9	=>	'ChequeProcessDate', 
	10	=>	'Model',
	11	=>	'Phase',
	12	=>	'Block',
	13	=>	'Lot',
	14	=>	'FullName'
); 

$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";
$mainQuery 		= "SELECT bcm.Id as Id, bcm.ChequeDate as ChequeDate, bcm.ChequeNo as ChequeNo, UPPER(bcm.Bank) as Bank, bcm.Amount as Amount, bcm.Remarks as Remarks, bcm.ChequeStatus as ChequeStatus,bcm.ChequeProcessDate as ChequeProcessDate, UPPER(r.Project) as Project, UPPER(r.Model) as Model, r.Phase as Phase, r.Block as Block, r.Lot as Lot, UPPER(CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName)) as FullName, bcm.ReservationId as ReservationId, bcm.ReturnRemarks as ReturnRemarks, r.Id as rid FROM buyer_cheque_monitoring bcm LEFT JOIN reservation r ON r.Id=bcm.ReservationId "; 
 
//$sql			=	$mainQuery."  WHERE (bcm.Bank)!='' AND bcm.Status = 'active' AND (bcm.ChequeStatus IS NULL OR (bcm.ChequeStatus!='Cleared' AND bcm.ChequeStatus!='Replacement' AND bcm.ChequeStatus!='Returned')OR bcm.ChequeStatus='Replaced')"; 
$sql			=	$mainQuery."  WHERE (bcm.Bank)!='' AND bcm.Status = 'active' AND bcm.ChequeStatus='No Check'"; 
if(!empty($requestData['search']['value']) ) { 
	$arr = explode(" ",$requestData['search']['value']);  
	foreach($arr as $ind){
		$ind = str_replace(',','',$ind);
		$sql.=" AND (r.Project LIKE '%".$ind."%' "; 
		$sql.=" OR r.Model LIKE '%".$ind."%' ";
		$sql.=" OR r.Phase LIKE '%".$ind."%' ";
		$sql.=" OR r.Block LIKE '%".$ind."%' ";
		$sql.=" OR r.Lot LIKE '%".$ind."%' "; 
		$sql.=" OR bcm.ChequeNo LIKE '%".$ind."%' ";
		$sql.=" OR bcm.Amount LIKE '%".$ind."%' ";
		$sql.=" OR bcm.Remarks LIKE '%".$ind."%' ";
		$sql.=" OR bcm.ChequeStatus LIKE '%".$ind."%' ";
		$sql.=" OR bcm.ReturnRemarks LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(bcm.Bank) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bcm.ChequeDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(bcm.ChequeProcessDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
		$sql.=" OR UPPER(CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName)) LIKE '%".htmlentities($ind)."%' )";
	}
} 
/*
if(!empty($rangeStart) || !empty($rangeEnd) ) { 
$rangestart = date('Y-m-d', strtotime($rangeStart));
$rangeend = date('Y-m-d', strtotime($rangeEnd));
//$sql .= " AND bcm.ChequeDate < '%".$rangestart."%' ";

   $sql .= " AND bcm.ChequeDate BETWEEN ('".$rangestart."') AND ('".$rangeend."')";

}*/

if($tobedate=="yes"){
	if( !empty($requestData['columns'][0]['search']['value']) && !empty($requestData['columns'][1]['search']['value']) ){
		$sql .= " AND bcm.ChequeDate 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 bcm.ChequeDate >= ('".date('Y-m-d', strtotime($requestData['columns'][0]['search']['value']))."') ";
	}else if( !empty($requestData['columns'][1]['search']['value'])){
		$sql .= " AND bcm.ChequeDate <= ('".date('Y-m-d', strtotime($requestData['columns'][1]['search']['value']))."') ";
	}


	if( !empty($rangeStart) && !empty($rangeEnd) ){
		$sql .= " AND bcm.ChequeDate BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
	}else if( !empty($rangeStart)){
		$sql .= " AND bcm.ChequeDate >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
	}else if( !empty($rangeEnd)){
		$sql .= " AND bcm.ChequeDate <= ('".date('Y-m-d', strtotime($rangeEnd))."') ";
	}
}
//&columns[0][search][value]=01/09/2016
 
//&columns[1][search][value]=01/16/2016 


$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']."   "; 
$mecon->query($sql);
$data = array();
$result = $mecon->resultset();
			 for($a=0; $a<count($result); $a++){
			 $nestedData=array(); 
				//$nestedData[] = '<span onclick="updatePDCStatus('.$result[$a]['ReservationId'].','.$result[$a]['Id'].')" class="btn btn-xs btn-info glyphicon glyphicon-time" title="Change Status of Postdated Checks Received for Equity"></span>';
				$nestedData[]	= "";
				$nestedData[]	= $a+1;
				$nestedData[] = (($result[$a]['ChequeDate']=='1970-01-01') || ($result[$a]['ChequeDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ChequeDate']));  
				$nestedData[]	= $result[$a]['ChequeNo']; 
				$nestedData[]	= $result[$a]['Bank']; 
				$nestedData[]	= (!empty($result[$a]['Amount']) || $result[$a]['Amount']!=NULL || $result[$a]['Amount']!="") ? number_format($result[$a]['Amount'],2) : $result[$a]['Amount']; 
				$nestedData[]	= $result[$a]['Remarks']; 
				$nestedData[]	= strtoupper($result[$a]['ChequeStatus']);
				$nestedData[]	= $result[$a]['ReturnRemarks'];  
				$nestedData[] = (($result[$a]['ChequeProcessDate']=='1970-01-01') || ($result[$a]['ChequeProcessDate']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['ChequeProcessDate'])); 
		//		$nestedData[]	= strtoupper($result[$a]['Project']); 
				$nestedData[]	= strtoupper($result[$a]['Model']); 
				$nestedData[]	= $result[$a]['Phase']; 
				$nestedData[]	= $result[$a]['Block'];
				$nestedData[]	= $result[$a]['Lot'];
				$reserveId 		= $result[$a]['rid'];
				$nestedData[]	= '<a href="index.php?pg=payment&type=postdated_checks_received&rid='.$reserveId.'">'.strtoupper($result[$a]['FullName']).'</a>';
				$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();