<?  
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	=>	'Contractor', 
	1	=>	'Tin',
	2	=>	'Block',
	3	=>	'Lot',
	4	=>	'releases',
	5	=>	'accomplishment_percentage',
	6	=>	'accomplishment_amount',
	7	=>	'rwht',
	8	=>	'date_processed'  
);   

$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 rc.Id as Id, rc.DateIssued as DateIssued, rc.Contractor as Contractor, rc.Block as Block, rc.Lot as Lot, rc.Units as Units,rc.ContractPrice as ContractPrice, rc.ReleasesRemarks as ReleasesRemarks, rcd.releases as releases, rcd.accomplishment_amount as accomplishment_amount, rcd.accomplishment_percentage as accomplishment_percentage, rcd.wht as rwht, rcd.date_processed as date_processed, rc.ReleasesStatus as ReleasesStatus, rcd.releases_checkbox as releases_checkbox, (SELECT TIN FROM tin WHERE type='contractor' AND UPPER(CompanyName)=UPPER(rc.Contractor)) as Tin from releases_to_contractor rc LEFT JOIN releases_to_contractor_details rcd ON rcd.release_contractor_id=rc.Id "; 
   
$sql			=	$resultQuery."  WHERE releases_checkbox='on' AND 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 Lot LIKE '%".$ind."%' "; 
		$sql.=" OR Units LIKE '%".$ind."%' ";
		$sql.=" OR releases LIKE '%".$ind."%' "; 
		$sql.=" OR UPPER(DATE_FORMAT(date_processed,'%m/%d/%Y')) LIKE '%".$ind."%' ";  
		$sql.=" OR Contractor LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR ContractPrice LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR accomplishment_percentage LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR accomplishment_amount LIKE '%".htmlentities($ind)."%' ";
		$sql.=" OR ReleasesRemarks LIKE '%".$ind."%'  ) ";		 
	}
}
if($tobedate=="yes"){
	if( !empty($rangeStart) && !empty($rangeEnd) ){
		$sql .= " AND date_processed BETWEEN ('".date('Y-m-d', strtotime($rangeStart))."') AND ('".date('Y-m-d', strtotime($rangeEnd))."')";
	}else if( !empty($rangeStart)){
		$sql .= " AND date_processed >= ('".date('Y-m-d', strtotime($rangeStart))."') ";
	}else if( !empty($rangeEnd)){
		$sql .= " AND date_processed <= ('".date('Y-m-d', strtotime($rangeEnd))."') ";
	}

	//Nov1,2020 changes -> rename DateIssued db column to "Date PO issued" in label UI and DatePOIssued db column rename label to Completion Date
}
$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, rcd.releases_no ASC  LIMIT ".$requestData['start']." ,".$requestData['length']."   "; 
 
$data = array(); 
$mecon->query($sql);
$result			= $mecon->resultset();  

		for($a=0; $a<count($result); $a++){
			$nestedData	=	array(); 
			//$nestedData[] = $a+1;
			//$nestedData[] = ($result[$a]['releases']=='downpayment') ? $result[$a]['Contractor'] : "";
			//$nestedData[] = ($result[$a]['releases']=='downpayment') ? $result[$a]['Block'] : "";
			//$nestedData[] = ($result[$a]['releases']=='downpayment') ? $result[$a]['Lot'] : "";
			$nestedData[] = $result[$a]['Contractor'];
			$nestedData[] = $result[$a]['Tin'];
			$nestedData[] = $result[$a]['Block'];
			$nestedData[] = $result[$a]['Lot']; 
			$nestedData[] = $result[$a]['releases']; 
			$nestedData[] = !empty($result[$a]['accomplishment_percentage']) ? number_format($result[$a]['accomplishment_percentage'],2)."%" : "";
			$nestedData[] = !empty($result[$a]['accomplishment_amount']) ? number_format($result[$a]['accomplishment_amount'],2) : "";

			$nestedData[] = !empty($result[$a]['rwht']) ? number_format($result[$a]['rwht'],2) : "";

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

			//$nestedData[] = ($result[$a]['releases']=='downpayment') ? $result[$a]['ReleasesStatus'] : "";
			 
			$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();