/** * @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; }