<?  
//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; 
$user_type = isset($_SESSION['danvilles_user_type']) ? strip_tags(stripslashes(trim($_SESSION['danvilles_user_type']))) : "";
$columns = array(
	0	=>	'Id',
	1	=>	'FullName',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'TakeOut',
	5	=>	'conversions',
	6	=>	'ReleasedPagibig',
	7	=>	'BirDateSubmitted',
	8	=>	'BirDateReleased', 
	9	=>	'BirEcar',
	10	=>	'RegistryDateSubmitted',
	11	=>	'RegistryDateReleased',
	12	=>	'RegistryNewTCTNo',
	13	=>	'CityHallDateSubmitted',
	14	=>	'CityHallDateReleased',
	15	=>	'CityHallNewTaxDecLand',
	16	=>	'CityHallNewTaxDecBldg',
	17	=>	'Remarks'
);  

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

$tobe 			= isset($_REQUEST['tobe_stat'])   ? $_REQUEST['tobe_stat']	  : "yes";
$tobeno 		= isset($_REQUEST['tobe_stat_no'])   ? $_REQUEST['tobe_stat_no']	  : "yes";
$tobedate 		= isset($_REQUEST['tobe_stat_date'])   ? $_REQUEST['tobe_stat_date']	  : "yes";

$mainQuery 		= "SELECT r.Id as Id, r.Project as Project, r.Model as Model, r.ReservationDate as ReservationDate, r.Phase as Phase, r.Block as Block, r.Lot as Lot, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, r.Agent as Agent, r.Spouse as Spouse, r.HomeTel as HomeTel, r.CellphoneNo as CellphoneNo, r.SpouseContactNo as SpouseContactNo, r.Address1 as Address1, r.Address2 as Address2, r.EmployerName as EmployerName, r.EmployeeType as EmployeeType, r.GrossIncome as GrossIncome, r.DiscountPromo as DiscountPromo, r.Position as Position, r.BuyerProfile as BuyerProfile, r.SpouseIncome as SpouseIncome, r.TinNo as TinNo, r.OfficeTel as OfficeTel, r.AggregateIncome as AggregateIncome, r.NoCoBorrower as NoCoBorrower, r.ReservationStatus as ReservationStatus, r.Status as rStatus, r.EmployeeType as EmployeeType, bsf.TakeOut as TakeOut, con.ReleasedPagibig as ReleasedPagibig, con.ProcessedDate as ProcessedDate, con.ReleasedDate as ReleasedDate, DATE_ADD(bsf.TakeOut, INTERVAL 18 MONTH) as conversions, con.BirDateSubmitted as BirDateSubmitted, con.BirDateReleased as BirDateReleased, con.BirEcar as BirEcar, con.RegistryDateSubmitted as RegistryDateSubmitted,con.RegistryDateReleased as RegistryDateReleased, con.RegistryNewTCTNo as RegistryNewTCTNo, con.CityHallDateSubmitted as CityHallDateSubmitted, con.CityHallDateReleased as CityHallDateReleased, con.CityHallNewTaxDecLand as CityHallNewTaxDecLand, con.CityHallNewTaxDecBldg as CityHallNewTaxDecBldg, con.Remarks as Remarks FROM  reservation r LEFT JOIN buyer_status_folder_pagibig bsf ON bsf.ReservationId=r.Id LEFT JOIN conversion con ON con.ReservationId=r.Id"; 
 

//$sql			=	$mainQuery."  WHERE r.Status = 'active' AND r.ReservationStatus='active'  AND (con.ReleasedPagibig!='1970-01-01' OR con.ReleasedPagibig!=NULL) ";
 
$sql			=	$mainQuery."  WHERE r.Status = 'active' AND r.ReservationStatus='active'  AND (bsf.TakeOut!='1970-01-01' OR bsf.TakeOut!=NULL) ";

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 CONCAT(LastName,', ',FirstName,' ',MiddleName) LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(con.ReleasedPagibig,'%m/%d/%Y')) LIKE '".$ind."%' "; 

		$sql.=" OR UPPER(DATE_FORMAT(con.BirDateSubmitted,'%m/%d/%Y')) LIKE '".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(con.BirDateReleased,'%m/%d/%Y')) LIKE '".$ind."%' "; 
		$sql.=" OR con.BirEcar LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(con.RegistryDateSubmitted,'%m/%d/%Y')) LIKE '".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(con.RegistryDateReleased,'%m/%d/%Y')) LIKE '".$ind."%' "; 
		$sql.=" OR con.RegistryNewTCTNo LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR UPPER(DATE_FORMAT(con.CityHallDateSubmitted,'%m/%d/%Y')) LIKE '".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(con.CityHallDateReleased,'%m/%d/%Y')) LIKE '".$ind."%' "; 
		$sql.=" OR con.CityHallNewTaxDecLand LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR con.CityHallNewTaxDecBldg LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR con.Remarks LIKE '%".htmlentities($ind)."%' ";
   
		$sql.=" OR Broker LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR EmployeeType LIKE '%".$ind."%' ";
		$sql.=" OR Agent LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Model LIKE '%".$ind."%' ";  
		$sql.=" OR Spouse LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Project LIKE '%".$ind."%' ";
		$sql.=" OR EmployerName LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Position LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Address1 LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR Address2 LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR HomeTel LIKE '".$ind."%' ";
		$sql.=" OR OfficeTel LIKE '".$ind."%' "; 
		$sql.=" OR CellphoneNo LIKE '".$ind."%' ";
		$sql.=" OR GrossIncome LIKE '".$ind."%' ";
		$sql.=" OR TinNo LIKE '".$ind."%' ";
		$sql.=" OR BuyerProfile LIKE '".$ind."%' "; 
		$sql.=" OR ReservationStatus  LIKE '".$ind."%' ";
		$sql.=" OR Lot LIKE '".$ind."%' )"; 
	}
}
 
if($tobedate=="yes"){
	if( !empty($rangeStart) && !empty($rangeEnd) ){
		$sql .= " AND con.ReleasedPagibig BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
	}else if( !empty($rangeStart)){
		$sql .= " AND con.ReleasedPagibig >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
	}else if( !empty($rangeEnd)){
		$sql .= " AND con.ReleasedPagibig <= ('".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']." LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 

$sql.=" ORDER BY cast(Block as unsigned)   ".$requestData['order'][0]['dir'].", 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();
				$hao = $a+1;
 				$nestedData[]	= trim($result[$a]['Id']);
				$nestedData[] 	= strtoupper(trim(html_entity_decode(stripslashes($result[$a]['FullName']))));
				$nestedData[]	= trim($result[$a]['Block']);
				$nestedData[]	= trim($result[$a]['Lot']); 
				  
				$nestedData[] = (($result[$a]['TakeOut']=='1970-01-01') || ($result[$a]['TakeOut']=='')) ? "" : date('m/d/Y', strtotime($result[$a]['TakeOut'])); 

				 
    			$takeoutDate 	= $result[$a]['TakeOut'];
    			//$conversionDate = date('m/d/Y', strtotime($takeoutDate. ' + 18 month'));  
    			$conversionDate = date('m/d/Y', strtotime($result[$a]['conversions']));
				$nestedData[]	= '<input value="'.$conversionDate.'"  class="form-control text-right datepicker2" type="text" readonly >';

				$releasedPagibigDate = date('m/d/Y', strtotime($result[$a]['ReleasedPagibig']));
				$releasedPagibigDate = ($releasedPagibigDate=='01/01/1970') ? "" : $releasedPagibigDate;
				
				//$nestedData[]	= $releasedPagibigDate; //24SEPT23
				$nestedData[]	= '<input value="'.$releasedPagibigDate.'" placeholder="mm/dd/yyyy" class="form-control text-right datepicker2" type="text" maxlength="10" name="released_pagibig_$a" onchange="updateConversion(this.value,\'release pagibig\','.$result[$a]['Id'].')">'; 
 				

 				//BIR Date Submitted
				$BirDateSubmitted 	= date('m/d/Y', strtotime($result[$a]['BirDateSubmitted']));
				$BirDateSubmitted 	= ($BirDateSubmitted=='01/01/1970') ? "" : $BirDateSubmitted; 
				$nestedData[]		= '<input value="'.$BirDateSubmitted.'" placeholder="mm/dd/yyyy" class="form-control text-right datepicker2" type="text" maxlength="10" name="bir_date_submitted_$a" onchange="updateConversion(this.value,\'bir_date_submitted\','.$result[$a]['Id'].')">'; 
 
 				//BIR Date Released
				$BirDateReleased 	= date('m/d/Y', strtotime($result[$a]['BirDateReleased']));
				$BirDateReleased 	= ($BirDateReleased=='01/01/1970') ? "" : $BirDateReleased; 
				$nestedData[]		= '<input value="'.$BirDateReleased.'" placeholder="mm/dd/yyyy" class="form-control text-right datepicker2" type="text" maxlength="10" name="bir_date_released_$a" onchange="updateConversion(this.value,\'bir_date_released\','.$result[$a]['Id'].')">'; 

				//BIR ECAR
				$BirEcar 		= $result[$a]['BirEcar']; 
				$nestedData[]	= '<input value="'.$BirEcar.'" placeholder="" class="form-control text-left" type="text" maxlength="13" name="bir_ecar_$a" onchange="updateConversion(this.value,\'bir_ecar\','.$result[$a]['Id'].')">'; 

				//Registry Date Released
				$RegistryDateReleased 		= date('m/d/Y', strtotime($result[$a]['RegistryDateReleased']));
				$RegistryDateReleased 		= ($RegistryDateReleased=='01/01/1970') ? "" : $RegistryDateReleased; 
				$nestedData[]				= '<input value="'.$RegistryDateReleased.'" placeholder="mm/dd/yyyy" class="form-control text-right datepicker2" type="text" maxlength="10" name="registry_date_released_$a" onchange="updateConversion(this.value,\'registry_date_released\','.$result[$a]['Id'].')">'; 

				//Registry Date Submitted
				$RegistryDateSubmitted 		= date('m/d/Y', strtotime($result[$a]['RegistryDateSubmitted']));
				$RegistryDateSubmitted 		= ($RegistryDateSubmitted=='01/01/1970') ? "" : $RegistryDateSubmitted; 
				$nestedData[]				= '<input value="'.$RegistryDateSubmitted.'" placeholder="mm/dd/yyyy" class="form-control text-right datepicker2" type="text" maxlength="10" name="registry_date_submitted_$a" onchange="updateConversion(this.value,\'registry_date_submitted\','.$result[$a]['Id'].')">';

				//Registry New TCT No. 
				$RegistryNewTCTNo 			= $result[$a]['RegistryNewTCTNo']; 
				$nestedData[]				= '<input value="'.$RegistryNewTCTNo.'" placeholder="" class="form-control text-left" type="text" maxlength="15" name="registry_new_tct_no_$a" onchange="updateConversion(this.value,\'registry_new_tct_no\','.$result[$a]['Id'].')">'; 

				//City Hall Date Submitted
				$CityHallDateSubmitted 		= date('m/d/Y', strtotime($result[$a]['CityHallDateSubmitted']));
				$CityHallDateSubmitted 		= ($CityHallDateSubmitted=='01/01/1970') ? "" : $CityHallDateSubmitted; 
				$nestedData[]				= '<input value="'.$CityHallDateSubmitted.'" placeholder="mm/dd/yyyy" class="form-control text-right datepicker2" type="text" maxlength="10" name="city_hall_date_submitted_$a" onchange="updateConversion(this.value,\'city_hall_date_submitted\','.$result[$a]['Id'].')">';

				//City Hall Date Released
				$CityHallDateReleased 		= date('m/d/Y', strtotime($result[$a]['CityHallDateReleased']));
				$CityHallDateReleased 		= ($CityHallDateReleased=='01/01/1970') ? "" : $CityHallDateReleased; 
				$nestedData[]				= '<input value="'.$CityHallDateReleased.'" placeholder="mm/dd/yyyy" class="form-control text-right datepicker2" type="text" maxlength="10" name="city_hall_date_released_$a" onchange="updateConversion(this.value,\'city_hall_date_released\','.$result[$a]['Id'].')">';

				//City Hall New Tax Dec Land
				$CityHallNewTaxDecLand	= $result[$a]['CityHallNewTaxDecLand']; 
				$nestedData[]			= '<input value="'.$CityHallNewTaxDecLand.'" placeholder="" class="form-control text-left" type="text" maxlength="8" name="city_hall_new_tax_dec_land_$a" onchange="updateConversion(this.value,\'city_hall_new_tax_dec_land\','.$result[$a]['Id'].')">'; 

				//City Hall New Tax Dec Bldg
				$CityHallNewTaxDecBldg	= $result[$a]['CityHallNewTaxDecBldg']; 
				$nestedData[]			= '<input value="'.$CityHallNewTaxDecBldg.'" placeholder="" class="form-control text-left" type="text" maxlength="8" name="city_hall_new_tax_dec_bldg_$a" onchange="updateConversion(this.value,\'city_hall_new_tax_dec_bldg\','.$result[$a]['Id'].')">'; 


				//Remarks
				$Remarks				= $result[$a]['Remarks']; 
				$nestedData[]			= '<input value="'.$Remarks.'" placeholder="" class="form-control text-left" type="text" maxlength="8" name="remarks_$a" onchange="updateConversion(this.value,\'remarks\','.$result[$a]['Id'].')">'; 


 				/*$processedDate 	= date('m/d/Y', strtotime($result[$a]['ProcessedDate']));
 				$processedDate 	= ($processedDate=='01/01/1970') ? "" : $processedDate;
				$nestedData[]	= '<input value="'.$processedDate.'"  placeholder="mm/dd/yyyy" class="form-control text-right datepicker2" type="text" name="processed_$a"  maxlength="10" onchange="updateConversion(this.value,\'processed\','.$result[$a]['Id'].')">'; 
			
			
				$releasedDate 	= date('m/d/Y', strtotime($result[$a]['ReleasedDate']));
				$releasedDate 	= ($releasedDate=='01/01/1970') ? "" : $releasedDate;
				$nestedData[]	= '<input value="'.$releasedDate.'"  placeholder="mm/dd/yyyy" class="form-control text-right datepicker2" type="text" name="released_$a"  maxlength="10" onchange="updateConversion(this.value,\'released\','.$result[$a]['Id'].')">';*/
				//$nestedData[]	= trim($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(); 