<?php

// Get Contract Project Information	
$query = "SELECT *, SUM(q.invAmount) AS invTotal, GROUP_CONCAT(CAST(q.pmtID AS CHAR) SEPARATOR ',') AS pmts FROM (SELECT p.projectID AS pID, p.projectName AS pName, c.clientName AS cName, pmt.paymentAmount AS invAmount, pmt.invoiceDate AS invDate, pmt.paymentID AS pmtID, pmt.paymentStatus AS invStat, pmt.ppInv, pmt.ppPaid, cr.* FROM projects p LEFT JOIN clients c ON (p.clientID = c.clientID) LEFT JOIN payments pmt ON (p.projectID = pmt.projectID) LEFT JOIN clientRates cr ON (p.currentRateID = cr.rateID) WHERE p.projectDisp=1 AND p.projectType=1 AND (p.projectStatus=1 OR p.projectStatus=2) AND c.subClientOf=". $pclientID;

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

// Updated 2015-07-21 JSC - To fix Project Comission reporting bug - report wqas not including project payment unless invoice date was in current month regardless of patment date
/*if (!empty($dateFrom))
	$query .= ' AND pmt.paymentDate>="' . $dateFrom . '"';
if (!empty($dateTo))
	$query .= ' AND pmt.paymentDate<="' . $dateTo . '"';*/
if ((!empty($dateFrom)) || (!empty($dateTo))) {	
	$drInvoice = '';
	$drPayment = '';  	
	if (!empty($dateFrom)) {
		$drInvoice .= 'pmt.invoiceDate>="' . $dateFrom . '"';
		$drPayment .= 'pmt.paymentDate>="' . $dateFrom . '"';
	}
	if (!empty($dateTo)) {
		$drInvoice .=  (($drInvoice!='') ? ' AND ' : '') . 'pmt.invoiceDate<="' . $dateTo . '"';
		$drPayment .=  (($drPayment!='') ? ' AND ' : '') . 'pmt.paymentDate<="' . $dateTo . '"';
	}
	$query .= ' AND (' . ((($drInvoice!='') && ($drPayment!='')) ? '(' : '') . (($drInvoice!='') ? $drInvoice : '') . ((($drInvoice!='') && ($drPayment!='')) ? ') OR (' : '') . (($drPayment!='') ? $drPayment : '') . ((($drInvoice!='') && ($drPayment!='')) ? ')' : '') .')';
}
if ($prevUnpaid == 1) {	
	if ((!empty($dateFrom)) && (in_array('invNo',$pmtStatArray)))
		$query .= ' OR (pmt.invoiceDate<"' . $dateFrom . '" AND pmt.ppInv=0 AND c.subClientOf='. $pclientID .')';
	elseif ((!empty($dateFrom)) && (in_array('PayNo',$pmtStatArray)))
		$query .= ' OR (pmt.invoiceDate<"' . $dateFrom . '" AND pmt.ppPaid=0 AND c.subClientOf='. $pclientID .')';	
	else
		$query .= ' OR (pmt.invoiceDate<"' . $dateFrom . '" AND pmt.ppPaid=0 AND c.subClientOf='. $pclientID .')';	
}
// Updated 2015-07-21 JSC - To fix Project Comission reporting bug - report wqas not including project payment unless invoice date was in current month regardless of patment date
//$query .= ') q WHERE q.invStat=1 GROUP BY q.pID ORDER BY q.cName, q.pName';
$query .= ') q GROUP BY q.pID ORDER BY q.cName, q.pName';
//echo $query;
$result = $con->query($query);

?>