<?php

include('../inc/queries/sumQuery_Recurring.php');

if ($result->num_rows>0) { 
	// Section header	
	$objPHPExcel->getActiveSheet()->getStyle('A'. $excelRow .':F'. $excelRow .'')->applyFromArray( $style_header );
	$objPHPExcel->getActiveSheet()->setCellValue('A'. $excelRow .'', 'Ongoing Fees');
	$objPHPExcel->getActiveSheet()->setCellValue('B'. $excelRow .'', '');
	$objPHPExcel->getActiveSheet()->setCellValue('C'. $excelRow .'', 'Fee Date');
	$objPHPExcel->getActiveSheet()->setCellValue('D'. $excelRow .'', $amtInv);
	$objPHPExcel->getActiveSheet()->setCellValue('E'. $excelRow .'', $comishRate);
	$objPHPExcel->getActiveSheet()->setCellValue('F'. $excelRow .'', $comDue);
	$objPHPExcel->getActiveSheet()->getStyle('B'. $excelRow .':F'. $excelRow .'')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	$invTotal = 0; 
	$comissionTotal = 0;
	
	while ($row=$result->fetch_array(MYSQLI_ASSOC)) {
		$excelRow = $excelRow + 1; // Move to Next Excel Row
		// Fee Date
		$feeDate = ''; 
		$invDate = ''; 
		if ($row['ogfpmtID'] != NULL) {
			$feeDate = Date::convert($row['invDate'], 'Y-m-d', 'M d, Y');
			$idDate = $row['invDate'];
		}
		elseif ((!empty($dateFrom)) && (!empty($dateTo))) {  
			if (($row['feeNextRec']>=$dateFrom) && ($row['feeNextRec']<=$dateTo)) {
				$feeDate = Date::convert($row['feeNextRec'], 'Y-m-d', 'M d, Y'); 
				$invDate = $row['feeNextRec'];
				$idDate = $row['feeNextRec'];
			}
			elseif (($row['feeLastRec']>=$dateFrom) && ($row['feeLastRec']<=$dateTo)) {
				$feeDate = Date::convert($row['feeLastRec'], 'Y-m-d', 'M d, Y'); 
				$invDate = $row['feeLastRec'];
				$idDate = $row['feeLastRec'];
			}
		}
		else {
			$feeDate = Date::convert($row['feeNextRec'], 'Y-m-d', 'M d, Y');
			$invDate = $row['feeNextRec'];
			$idDate = $row['feeNextRec'];
		}
		
		$itemID = $row['pID'] .'_' . $idDate;
		
		// Get Current Fee paid Values
		if ($row['ogfpmtID'] != NULL)
			$currentValues = $row['ogfpmtID'] .','. $row['ogfeeID'] .','. $row['invDate'] .','. $row['pmtDate'] .','. $row['ppInvDate'] .','. $row['ppInv'] .','. $row['ppPaidDate'] .','. $row['ppPaid']; 
		else 
			$currentValues = '0,'. $row['pID'] .','. $invDate .',0000-00-00,0,0000-00-00,0';	 						
		$comission = number_format($row['billAmount']*$comishPercent, 2,'.','');
		
		// Details
		$feeDetails = ''; 
		if ($row['feeDetails']) { $feeDetails .= $row['feeDetails']; }
		if ($row['feeDomain']) { $feeDetails .= ' - '. $row['feeDomain']; } 	
		if (!empty($feeDetails)) { $feeDetails = ' '. $feeDetails; }
		
		$objPHPExcel->getActiveSheet()->setCellValue('A'. $excelRow .'', $row['clientName'] . ' - ' . $row['feeDesc'] .' ('. $row['feeRecDesc'] .') '. $feeDetails);
		$objPHPExcel->getActiveSheet()->setCellValue('B'. $excelRow .'', '');
		$objPHPExcel->getActiveSheet()->setCellValue('C'. $excelRow .'', $feeDate);
		$objPHPExcel->getActiveSheet()->getCell('D'. $excelRow .'')->setValueExplicit(number_format($row['billAmount'],2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
		$objPHPExcel->getActiveSheet()->getStyle('D'. $excelRow .'')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
		$objPHPExcel->getActiveSheet()->getCell('E'. $excelRow .'')->setValueExplicit(number_format($comishPercent,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
		$objPHPExcel->getActiveSheet()->getStyle('E'. $excelRow .'')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
		$objPHPExcel->getActiveSheet()->getCell('F'. $excelRow .'')->setValueExplicit(number_format($comission,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
		$objPHPExcel->getActiveSheet()->getStyle('F'. $excelRow .'')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
		$invTotal = $invTotal+$row['billAmount'];
		$comissionTotal = $comissionTotal+$comission;
	}
	$excelRow = $excelRow + 1; // Move to Next Excel Row
	$objPHPExcel->getActiveSheet()->getStyle('A'. $excelRow .'')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
	$objPHPExcel->getActiveSheet()->setCellValue('A'. $excelRow .'', 'Totals: ');
	$objPHPExcel->getActiveSheet()->setCellValue('B'. $excelRow .'', '');
	$objPHPExcel->getActiveSheet()->setCellValue('C'. $excelRow .'', '');
	$objPHPExcel->getActiveSheet()->getCell('D'. $excelRow .'')->setValueExplicit(number_format($invTotal, 2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
	$objPHPExcel->getActiveSheet()->getStyle('D'. $excelRow .'')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
	$objPHPExcel->getActiveSheet()->getCell('E'. $excelRow .'')->setValueExplicit(number_format($comishPercent,2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
	$objPHPExcel->getActiveSheet()->getStyle('E'. $excelRow .'')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
	$objPHPExcel->getActiveSheet()->getCell('F'. $excelRow .'')->setValueExplicit(number_format($comissionTotal, 2,'.',''), PHPExcel_Cell_DataType::TYPE_NUMERIC);
	$objPHPExcel->getActiveSheet()->getStyle('F'. $excelRow .'')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
	$objPHPExcel->getActiveSheet()->getStyle('A'. $excelRow .':F'. $excelRow .'')->applyFromArray( $style_totals );
	//$objPHPExcel->getActiveSheet()->getStyle('F'. $excelRow .'')->getBold()->setFormatCode(PHPExcel_Style_Font::Bold);

	$repTotalInv = $repTotalInv+$invTotal;
	$repTotalComish = $repTotalComish+$comissionTotal;
	$excelRow = $excelRow + 3; // Move to Next Excel Row
}

?>