/**
* @param $sheet
* @param $ria
* @param $clients
* @param $year
* @param $quarter
* @return mixed
*/
protected function billTable($sheet, $ria, $clients, $year, $quarter)
{
$index = 9;
// Set table head background
$sheet->getStyle("A{$index}:K{$index}")->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => '8EB4E3')));
$totals = "";
$totalCell = "K{$index}";
foreach ($clients as $client) {
// Get client accounts
$clientAccounts = $this->summaryInformationManager->getAccountsInformationByClient($client, $year, $quarter);
foreach ($clientAccounts as $account) {
$index++;
$clientAccount = $this->em->getRepository('WealthbotClientBundle:ClientAccount')->find($account['id']);
$billItem = $this->em->getRepository('WealthbotClientBundle:BillItem')->getByAccountAndPeriod($clientAccount, $year, $quarter);
$sheet->setCellValue("A{$index}", $client->getLastName());
$sheet->setCellValue("B{$index}", $client->getFirstName());
$sheet->setCellValue("C{$index}", SystemAccount::getTypeName($account['type']));
$sheet->setCellValue("D{$index}", $account['number']);
$sheet->setCellValue("E{$index}", $client->getFeeShedule());
$sheet->setCellValue("F{$index}", Profile::getPaymentMethodName($client->getPaymentMethod()));
$sheet->setCellValue("G{$index}", $account['averageAccountValue']);
$sheet->setCellValue("H{$index}", $account['daysInPortfolio']);
$sheet->setCellValue("I{$index}", empty($billItem) ? 0 : $billItem->getAdminFee());
$sheet->setCellValue("J{$index}", empty($billItem) ? 0 : $billItem->getRiaFee());
$sheet->setCellValue("K{$index}", "=SUM(I{$index}:J{$index})");
// Set currency USD format
$sheet->getStyle("G{$index}")->applyFromArray(array('numberformat' => array('code' => \PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD)));
$sheet->getStyle("I{$index}:K{$index}")->applyFromArray(array('numberformat' => array('code' => \PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD)));
$font = $sheet->getStyle("A{$index}:K{$index}")->getFont();
$font->setName('Arial')->setSize(10);
// Save total cell
$totals .= "K{$index},";
// Set table body background
$sheet->getStyle("A{$index}:K{$index}")->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'DCE6F2')));
}
}
// Calculate total
$sheet->setCellValue($totalCell, "=SUM(" . substr($totals, 0, -1) . ")");
$sheet->getStyle($totalCell)->getFont()->setBold(true);
$sheet->getStyle($totalCell)->applyFromArray(array('numberformat' => array('code' => \PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD)));
return $sheet;
}
