<?
// Get Contract Project Information	
//$query = 'SELECT *, ogf.feeID AS pID, ogf.feeDesc AS feeDetails, fpd.ogfpmtID AS paidID, (ogf.feeAmt * fr.feeRecCalc) as billAmount FROM ongoingFees ogf LEFT JOIN ongoingFeesPaid fpd on (fpd.ogfeeID = ogf.feeId) LEFT JOIN clients c ON (ogf.clientID = c.clientID) LEFT JOIN feeDesc fd ON (ogf.feeDescID = fd.feeDescID) LEFT JOIN feeRec AS fr ON (ogf.feeRecID = fr.feeRecID) WHERE ogf.feeActive=1 AND c.subClientOf='. $pclientID;

$query = 'SELECT *, ogf.feeID AS pID, ogf.feeDesc AS feeDetails, fpd.ogfpmtID AS paidID, (ogf.feeAmt * fr.feeRecCalc) as billAmount, cr.compPercent AS feeRate FROM ongoingFees ogf LEFT JOIN ongoingFeesPaid fpd on (fpd.ogfeeID = ogf.feeId) LEFT JOIN clients c ON (ogf.clientID = c.clientID) LEFT JOIN feeDesc fd ON (ogf.feeDescID = fd.feeDescID) LEFT JOIN feeRec AS fr ON (ogf.feeRecID = fr.feeRecID) LEFT JOIN clientRates cr ON (cr.rateID = ogf.currentRateID) WHERE ogf.feeActive=1 AND c.subClientOf='. $pclientID;

if (!empty($pmtStatArray)) {
	if (in_array('invYes',$pmtStatArray))
		$query .= ' AND fpd.ppInv=1';
	elseif (in_array('invNo',$pmtStatArray))
		$query .= ' AND fpd.ppInv=0';
	elseif (in_array('payYes',$pmtStatArray))
		$query .= ' AND fpd.ppPaid=1';
	elseif (in_array('payNo',$pmtStatArray))
		$query .= ' AND fpd.ppPaid=0';
}

$rangeNext = '';
$rangeLast = '';
$pmtNext = '';
$pmtLast = '';
$ogfInv = '';
if (!empty($dateFrom)) {
	$rangeNext .= 'ogf.feeNextRec>="' . $dateFrom . '"';
	$rangeLast .= 'ogf.feeLastRec>="' . $dateFrom . '"';
	$pmtNext .= 'ogf.feeNextRec>="' . $dateFrom . '"';
	$pmtLast .= 'ogf.feeLastRec>="' . $dateFrom . '"';
	$ogfInv .= 'fpd.invDate>="' . $dateFrom . '"';
}
if ((!empty($dateFrom)) && (!empty($dateTo)))  {
	$rangeNext .= ' AND ';
	$rangeLast .= ' AND ';
	$pmtNext .= ' AND ';
	$pmtLast .= ' AND ';
	$ogfInv .= ' AND ';
}
if (!empty($dateTo)) {
	$rangeNext .= 'ogf.feeNextRec<="' . $dateTo . '"';
	$rangeLast .= 'ogf.feeLastRec<="' . $dateTo . '"';
	$pmtNext .= 'ogf.feeNextRec<="' . $dateTo . '"';
	$pmtLast .= 'ogf.feeLastRec<="' . $dateTo . '"';
	$ogfInv .= 'fpd.invDate<="' . $dateTo . '"';
}

if ((!empty($rangeNext)) && (!empty($rangeLast))) {
	//$query .= ' AND (  ( ('. $rangeNext .') OR ('. $rangeLast .') ) AND ('. $ogfInv .') )'; 
	// 20161202 - Removed Next & Last Received because it was excluding some ongoing fees from reports.  Changed fees to be based on invoice dates instead - should be more accurate
	$query .= ' AND ('. $ogfInv .')';
}
elseif (!empty($rangeNext))
	$query .= ' AND '. $rangeNext;
elseif (!empty($rangeLast))
	$query .= ' AND '. $rangeLast;


/*if ( (!empty($dateFrom)) && (!empty($dateTo)) ) {
	$query .= ' AND ((pt.timeDate>="' . $dateFrom . '" AND pt.timeDate<="' . $dateTo . '")';*/
	if ($prevUnpaid == 1) {
		if ((!empty($dateFrom)) && (in_array('invNo',$pmtStatArray)))
			$query .= ' OR (ogf.feeLastRec>"' . $dateFrom . '" AND fpd.ppInv=0)';
		elseif ((!empty($dateFrom)) && (in_array('PayNo',$pmtStatArray)))
			$query .= ' OR (fpd.invDate>"' . $dateFrom . '" AND fpd.ppPaid=0)';
		else
			$query .= ' OR (fpd.invDate<"' . $dateFrom . '" AND fpd.ppPaid=0 AND c.subClientOf='. $pclientID .')';
	/*}	
	$query .= ')';*/
}

$query .= ' ORDER BY c.clientName ASC, fpd.invDate DESC, ogf.feeNextRec DESC, ogf.feeLastRec DESC';
//echo '<p>'. $query .'</p>'; 
//echo '<br>ROOT: '. $ROOT;
$result = $con->query($query);
?>