Symfony Profiler

<?php
namespace App\Controller\ProjectManagement;
use App\Repository\DepartmentRepository;
use App\Repository\ProjectRepository;
use App\Repository\TimeSpentRepository;
use App\Repository\TaskRepository;
use App\Repository\UserRepository;
use App\Repository\VendorRepository;
use App\Service\CurrencyService;
use App\Entity\Client;
use App\Repository\ClientRepository;
use App\Form\ProjectManagement\ClientType;
use App\Repository\InvoiceRepository;
use App\Repository\PurchaseOrderRepository;
use App\Repository\SalesOrderInvoiceRepository;
use App\Repository\SalesOrderRepository;
use App\Repository\VendorInvoiceRepository;
use App\Repository\XeroContactRepository;
use App\Service\GoogleDriveService;
use App\Service\UserService;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\RedirectResponse;
use Symfony\Contracts\Translation\TranslatorInterface;
class InvoiceViewerController extends AbstractController
{
/**
* @param ClientRepository $clientRepository
* @param ProjectRepository $projectRepository
* @param DepartmentRepository $departmentRepository
* @param TaskRepository $taskRepository
* @param Request $request
* @return Response
*/
#[Route(path: '/invoice-viewer', name: 'invoice_viewer')]
public function invoiceViewer(ProjectRepository $projectRepository, ClientRepository $clientRepository, TaskRepository $taskRepository, DepartmentRepository $departmentRepository, Request $request, TranslatorInterface $translator): Response
{
$user = $this->getUser();
if (count(array_diff(['ROLE_FINANCE', 'ROLE_HR', 'ROLE_MANAGEMENT'], $user->getRoles())) == 0) {
throw new \Exception($translator->trans('messages.security.forbidden'));
};
$page = intval($request->query->get('page'));
$limit = intval($request->query->get('limit'));
if ($limit === 0) {
$limit = 20;
}
$keyword = $request->get('keyword');
$order = $request->get('order');
$orderBy = $request->get('orderBy');
$clientsList = $clientRepository->findByPage($page, $limit, $keyword,$order, $orderBy);
$dataClient = $clientRepository->findAll();
$clients = $clientRepository->findByPage(0, 9999, "", "ASC", "");
$confirmedProjects = $projectRepository->findByPage(0, 9999, "", 'ASC', "", "", "", "CONFIRMED", 0, null, 0, null, null, null, false);
return $this->render('private/invoice-viewer/invoice-viewer.html.twig', [
'clientList' => $clientsList,
'dataClient' => $dataClient,
'clients' => $clients,
'confirmedProjects' => $confirmedProjects,
]);
}
/**
* @param ClientRepository $clientRepository
* @param ProjectRepository $projectRepository
* @param DepartmentRepository $departmentRepository
* @param TaskRepository $taskRepository
* @param Request $request
* @return Response
*/
#[Route(path: '/settings/finance', name: 'settings_finance')]
public function settingsFinance(ProjectRepository $projectRepository, ClientRepository $clientRepository, TaskRepository $taskRepository, DepartmentRepository $departmentRepository, Request $request, TranslatorInterface $translator): Response
{
$user = $this->getUser();
if (count(array_diff(['ROLE_FINANCE', 'ROLE_HR', 'ROLE_MANAGEMENT'], $user->getRoles())) == 0) {
throw new \Exception($translator->trans('messages.security.forbidden'));
};
$page = intval($request->query->get('page'));
$limit = intval($request->query->get('limit'));
if ($limit === 0) {
$limit = 20;
}
$keyword = $request->get('keyword');
$order = $request->get('order');
$orderBy = $request->get('orderBy');
$clientsList = $clientRepository->findByPage($page, $limit, $keyword,$order, $orderBy);
$dataClient = $clientRepository->findAll();
$clients = $clientRepository->findByPage(0, 9999, "", "ASC", "");
$confirmedProjects = $projectRepository->findByPage(0, 9999, "", 'ASC', "", "", "", "CONFIRMED", 0, null, 0, null, null, null, false);
return $this->render('private/invoice-viewer/invoice-viewer-admin.html.twig', [
'clientList' => $clientsList,
'dataClient' => $dataClient,
'clients' => $clients,
'confirmedProjects' => $confirmedProjects,
]);
}
/**
* @param SalesOrderRepository $salesOrderRepository
* @param Request $request
* @return Response
*/
#[Route(path: '/invoice-viewer/export/so/{_target}', defaults: ['_target' => ''], name: 'export_invoice_so')]
public function exportSO(string $_target, SalesOrderRepository $salesOrderRepository, XeroContactRepository $xeroContactRepository, Request $request, UserService $userService, GoogleDriveService $googleDriveService): Response
{
$nonlinked = $request->get('nonlinked');
$keyword = $request->get('keyword');
$order = $request->get('order');
$client = $request->get('client');
$status = $request->get('status');
$orderBy = $request->get('orderBy');
$start = $request->get('start');
$end = $request->get('end');
$project = $request->get('project');
$waitForPage = $request->query->get('waitForPage');
$waitForPage === null ? $result['waitForPage'] = 'false' : $result['waitForPage'] = $waitForPage;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'SO Number');
$sheet->setCellValue('B1', 'Xero Client');
$sheet->setCellValue('C1', 'Amount');
$sheet->setCellValue('D1', 'Allocated');
$sheet->setCellValue('E1', 'Amount Left');
$sheet->setCellValue('F1', 'Invoice');
$sheet->setCellValue('G1', 'INV Amount');
$sheet->setCellValue('H1', 'CN Number');
$sheet->setCellValue('I1', 'PO Number');
$sheet->setCellValue('J1', 'Client');
$sheet->setCellValue('K1', 'Project');
$sheet->setCellValue('L1', 'SO PIC');
$sheet->setCellValue('M1', 'Issue Date');
$sheet->setCellValue('N1', 'SO Status');
$sheet->getDefaultColumnDimension()->setWidth(25);
$sheet->getStyle("A1:". $sheet->getHighestColumn() ."1")->getFont()->setBold(true);
$sheet->getStyle("A1:". $sheet->getHighestColumn() ."1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$sheet->getStyle("A1:". $sheet->getHighestColumn() ."1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$sheet->getStyle("A2:". $sheet->getHighestColumn() ."2")->getFont()->setBold(false);
$sheet->getStyle("A2:". $sheet->getHighestColumn() ."2")->getAlignment()->setWrapText(true);
$sheet->getStyle("A2:". $sheet->getHighestColumn() ."2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$salesOrdersList = $salesOrderRepository->findByPage(0, 9999, $keyword, $order, $orderBy, $project, $nonlinked, $client, $status, $start, $end);
$filename = 'Sales-Orders_' . $status .'_'. date('Y-m-d') . '.xlsx';
$row = $sheet->getHighestRow();
foreach($salesOrdersList as $salesOrder){
$initiateRow = $row;
$sheet->setCellValue('A'.$row, $salesOrder->getSalesOrderNo());
$xeroContact = $xeroContactRepository->findOneBy(['xeroContactId' => $salesOrder->getXeroContactId()]);
$sheet->setCellValue('B'.$row, $xeroContact ? $xeroContact->getName() : '-');
$sheet->setCellValue('C'.$row, $salesOrder->getSubTotalUsd());
$sheet->setCellValue('D'.$row, $salesOrder->getProjectSalesOrderTotalAmountUsedUsd());
$sheet->setCellValue('E'.$row, $salesOrder->getSubTotalUsd() - $salesOrder->getProjectSalesOrderTotalAmountUsedUsd());
$sheet->setCellValue('L'.$row, $salesOrder->getCreator());
$sheet->setCellValue('M'.$row, $salesOrder->getQuoteDate() ? $salesOrder->getQuoteDate()->format("d/m/Y") : '-');
$sheet->setCellValue('N'.$row, $salesOrder->getXeroStatus());
$invoices = $salesOrder->getSalesOrderInvoices();
/*
$soInvoices = [];
foreach ($invoices as $soInvoice){
$invoice = $soInvoice->getInvoice();
array_push($soInvoices, $invoice->getInvoiceNo());
}
$sheet->setCellValue('F'.$row,implode(', ', $soInvoices));
*/
$invoiceRow = $row;
if (count($invoices) > 0){
foreach($salesOrder->getSalesOrderInvoices() as $invoice){
$sheet->setCellValue('F'.$invoiceRow,$invoice->getInvoice()->getInvoiceNo());
$sheet->setCellValue('G'.$invoiceRow,$invoice->getAmountUsd());
$creditNote = $invoice->getInvoice()->getXeroCreditNoteAllocation();
$sheet->setCellValue('H'.$invoiceRow, $creditNote ? $creditNote->getXeroCreditNote()->getCreditNoteNo() : '-');
$invoiceRow++;
}
}else{
$sheet->setCellValue('F'.$invoiceRow, "-");
$sheet->setCellValue('G'.$invoiceRow, "-");
}
$purchases = $salesOrder->getSalesOrderPurchaseOrders();
$purchasesRow = $row;
if(count($purchases) > 0){
foreach($purchases as $po){
$sheet->setCellValue('I'.$purchasesRow, $po->getPurchaseOrder()->getPurchaseOrderNo());
$purchasesRow++;
}
}else{
$sheet->setCellValue('I'.$purchasesRow, "-");
}
$projectSORow = $row;
$projectSalesOrders = $salesOrder->getProjectSalesOrders();
if(count($projectSalesOrders) > 0){
foreach($projectSalesOrders as $pso){
$sheet->setCellValue('J'.$projectSORow, $pso->getProject()->getClient()->getName());
$sheet->setCellValue('K'.$projectSORow, $pso->getProject()->getName());
$sheet->getCell('K' . $projectSORow)->getHyperlink()->setUrl($baseurl . '/project-management/project/view/' . $pso->getProject()->getId());
$sheet->getStyle('K' . $projectSORow)->getFont()->setUnderline(true);
$projectSORow++;
}
}else{
$sheet->setCellValue('J'.$projectSORow, "-");
$sheet->setCellValue('K'.$projectSORow, "-");
}
$allRow = [$projectSORow, $purchasesRow, $invoiceRow];
$maxRow = max($allRow);
$row = $maxRow == $row ? $row + 1 : $maxRow;
/*
$PurchaseOrders = [];
foreach ($purchases as $purchase){
$po = $purchase->getPurchaseOrder();
array_push($PurchaseOrders, $po->getPurchaseOrderNo());
}
$sheet->setCellValue('G'.$row, implode(', ', $PurchaseOrders));
*/
if(count($invoices) > 0 || count($purchases) > 0 || count($projectSalesOrders) > 0){
$sheet->mergeCells('A'.$initiateRow.':A'.($row - 1));
$sheet->mergeCells('B'.$initiateRow.':B'.($row - 1));
$sheet->mergeCells('C'.$initiateRow.':C'.($row - 1));
$sheet->mergeCells('D'.$initiateRow.':D'.($row - 1));
$sheet->mergeCells('E'.$initiateRow.':E'.($row - 1));
$sheet->mergeCells('K'.$initiateRow.':K'.($row - 1));
$sheet->mergeCells('L'.$initiateRow.':L'.($row - 1));
$sheet->mergeCells('M'.$initiateRow.':M'.($row - 1));
$sheet->mergeCells('N'.$initiateRow.':N'.($row - 1));
$sheet->getStyle('A'.$initiateRow.':A'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('B'.$initiateRow.':B'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('C'.$initiateRow.':C'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('D'.$initiateRow.':D'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('E'.$initiateRow.':E'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('K'.$initiateRow.':K'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('L'.$initiateRow.':L'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('M'.$initiateRow.':M'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('N'.$initiateRow.':N'.($row - 1))->getAlignment()->setVertical('center');
if(count($projectSalesOrders) == 1){
$sheet->mergeCells('J'.$initiateRow.':J'.($row - 1));
$sheet->mergeCells('K'.$initiateRow.':K'.($row - 1));
$sheet->getStyle('J'.$initiateRow.':J'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('K'.$initiateRow.':K'.($row - 1))->getAlignment()->setVertical('center');
}
}
}
$sheet->setAutoFilter('A1:'. $sheet->getHighestColumn() . $sheet->getHighestRow());
// $sheet->getStyle('C2:E' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
// $sheet->getStyle('F2:F' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$sheet->getStyle('C2:E' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$sheet->getStyle('F2:F' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
// Set the content-type:
if($_target == 'google'){
$gsheetURL = $googleDriveService->uploadToGoogleDrive($filename);
if($gsheetURL){
unlink($filename);
return new RedirectResponse($gsheetURL, 302);
}
}else{
$response = new Response();
$response->headers->set('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', sprintf('attachment; filename="%s"', $filename));
$response->setContent(file_get_contents($filename));
$response->setStatusCode(\Symfony\Component\HttpFoundation\Response::HTTP_OK);
$response->headers->set('Content-Transfer-Encoding', 'binary');
$response->headers->set('Pragma', 'no-cache');
$response->headers->set('Expires', '0');
unlink($filename);
return $response;
exit;
}
}
/**
* @param PurchaseOrderRepository $purchaseOrderRepository
* @param Request $request
* @return Response
*/
#[Route(path: '/invoice-viewer/export/po/{_target}', defaults: ['_target' => ''], name: 'export_invoice_po')]
public function exportPO(string $_target, PurchaseOrderRepository $purchaseOrderRepository, XeroContactRepository $xeroContactRepository, Request $request, GoogleDriveService $googleDriveService): Response
{
$keyword = $request->get('keyword');
$order = $request->get('order');
$client = $request->get('client');
$status = $request->get('status');
$orderBy = $request->get('orderBy');
$start = $request->get('start');
$end = $request->get('end');
$waitForPage = $request->query->get('waitForPage');
$waitForPage === null ? $result['waitForPage'] = 'false' : $result['waitForPage'] = $waitForPage;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'PO Number');
$sheet->setCellValue('B1', 'Amount');
$sheet->setCellValue('C1', 'Client');
$sheet->setCellValue('D1', 'Project');
$sheet->setCellValue('E1', 'Amount Allocated');
$sheet->setCellValue('F1', 'Total Allocated');
$sheet->setCellValue('G1', 'Amount Left');
$sheet->setCellValue('H1', 'PO Date');
$sheet->setCellValue('I1', 'Cancel Note');
$lastCol = $sheet->getHighestColumn();
$sheet->getDefaultColumnDimension()->setWidth(25);
$sheet->getStyle("A1:".$lastCol."1")->getFont()->setBold(true);
$sheet->getStyle("A1:".$lastCol."1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$sheet->getStyle("A1:".$lastCol."1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$sheet->getStyle("A2:".$lastCol."2")->getFont()->setBold(false);
$sheet->getStyle("A2:".$lastCol."2")->getAlignment()->setWrapText(true);
$sheet->getStyle("A2:".$lastCol."2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$purchaseOrderList = $purchaseOrderRepository->findByPage(0, 99999, $keyword, $order, $orderBy, $status, $client, $start, $end);
$filename = 'Purchase-Orders_'. date('Y-m-d') . '.xlsx';
$row = $sheet->getHighestRow();
foreach($purchaseOrderList as $purchaseOrder){
$sheet->setCellValue('A'.$row, 'PO-'.$purchaseOrder->getPurchaseOrderNo());
$sheet->setCellValue('B'.$row, $purchaseOrder->getAmountUsd());
$sheet->setCellValue('C'.$row, $purchaseOrder->getClient() ? $purchaseOrder->getClient()->getName() : '-');
$sheet->setCellValue('F'.$row, $purchaseOrder->getTotalAllocatedUsd());
$sheet->setCellValue('G'.$row, $purchaseOrder->getAmountLeftUsd());
$sheet->setCellValue('H'.$row, $purchaseOrder->getDate()->format("d-M-Y"));
$sheet->setCellValue('I'.$row, $purchaseOrder->getCancelNote());
if ($purchaseOrder->getCancelNote()) {
$sheet->getStyle('A'.$row.':I'.$row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF00'); // Yellow color
}
$salesOrderPurchaseOrders = $purchaseOrder->getSalesOrderPurchaseOrders()->toArray();
// dd($salesOrderPurchaseOrders);
$mergeRow = $row;
if(count($salesOrderPurchaseOrders) > 0){
foreach($salesOrderPurchaseOrders as $salesOrderPurchaseOrder){
$sheet->setCellValue('D'.$mergeRow, $salesOrderPurchaseOrder->getProject() ? $salesOrderPurchaseOrder->getProject()->getName() : '-');
$sheet->setCellValue('E'.$mergeRow, $salesOrderPurchaseOrder->getAmountUsd());
$mergeRow = count($salesOrderPurchaseOrders) > 1 ? $mergeRow+1 : $mergeRow;
}
if(count($salesOrderPurchaseOrders) > 1 && $mergeRow > $row){
$sheet->mergeCells("A$row:A".($mergeRow - 1));
$sheet->mergeCells("B$row:B".($mergeRow - 1));
$sheet->mergeCells("C$row:C".($mergeRow - 1));
$sheet->mergeCells("F$row:F".($mergeRow - 1));
$sheet->mergeCells("G$row:G".($mergeRow - 1));
$sheet->mergeCells("H$row:H".($mergeRow - 1));
$sheet->mergeCells("I$row:I".($mergeRow - 1));
$sheet->getStyle("A$row:A".($mergeRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle("B$row:B".($mergeRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle("C$row:C".($mergeRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle("F$row:F".($mergeRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle("G$row:G".($mergeRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle("H$row:H".($mergeRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle("I$row:I".($mergeRow - 1))->getAlignment()->setVertical('center');
}
}else{
$sheet->setCellValue('D'.$row, '-');
$sheet->setCellValue('E'.$row, '-');
}
$row = $mergeRow == $row ? $row+1 : $mergeRow;
}
$sheet->setAutoFilter('A1:'.$sheet->getHighestColumn(). $sheet->getHighestRow());
$sheet->getStyle('B2:B' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$sheet->getStyle('E2:G' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
// Set the content-type:
if($_target == 'google'){
$gsheetURL = $googleDriveService->uploadToGoogleDrive($filename);
if($gsheetURL){
unlink($filename);
return new RedirectResponse($gsheetURL, 302);
}
}else{
$response = new Response();
$response->headers->set('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', sprintf('attachment; filename="%s"', $filename));
$response->setContent(file_get_contents($filename));
$response->setStatusCode(\Symfony\Component\HttpFoundation\Response::HTTP_OK);
$response->headers->set('Content-Transfer-Encoding', 'binary');
$response->headers->set('Pragma', 'no-cache');
$response->headers->set('Expires', '0');
unlink($filename);
return $response;
exit;
}
}
/**
* @param InvoiceRepository $invoiceRepository
* @param Request $request
* @return Response
*/
#[Route(path: '/invoice-viewer/export/client/{_target}', defaults: ['_target' => ''], name: 'export_invoice_client')]
public function exportClientInvoice(string $_target, InvoiceRepository $invoiceRepository, XeroContactRepository $xeroContactRepository, Request $request, UserService $userService, GoogleDriveService $googleDriveService): Response
{
$keyword = $request->get('keyword');
$order = $request->get('order');
$client = $request->get('client');
$status = $request->get('status');
$orderBy = $request->get('orderBy');
$start = $request->get('start');
$end = $request->get('end');
$waitForPage = $request->query->get('waitForPage');
$waitForPage === null ? $result['waitForPage'] = 'false' : $result['waitForPage'] = $waitForPage;
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Invoice');
$sheet->setCellValue('B1', 'Xero Client');
$sheet->setCellValue('C1', 'Currency');
$sheet->setCellValue('D1', 'Amount');
$sheet->setCellValue('E1', 'Amount Left');
$sheet->setCellValue('F1', 'Allocated');
$sheet->setCellValue('G1', 'SO Number');
$sheet->setCellValue('H1', 'PO Number');
$sheet->setCellValue('I1', 'CN Number');
$sheet->setCellValue('J1', 'Client');
$sheet->setCellValue('K1', 'Project');
$sheet->setCellValue('L1', 'INV PIC');
$sheet->setCellValue('M1', 'Due');
$sheet->setCellValue('N1', 'Status');
$sheet->getDefaultColumnDimension()->setWidth(25);
$sheet->getStyle("A1:". $sheet->getHighestColumn() ."1")->getFont()->setBold(true);
$sheet->getStyle("A1:". $sheet->getHighestColumn() ."1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$sheet->getStyle("A1:". $sheet->getHighestColumn() ."1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$sheet->getStyle("A2:". $sheet->getHighestColumn() ."2")->getFont()->setBold(false);
$sheet->getStyle("A2:". $sheet->getHighestColumn() ."2")->getAlignment()->setWrapText(true);
$sheet->getStyle("A2:". $sheet->getHighestColumn() ."2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$invoicesList = $invoiceRepository->findByPage(0, 99999, $keyword, $order, $orderBy, $status, $client, $start, $end);
// dd($invoicesList);
$filename = 'Invoices_' .$status.'_'. date('Y-m-d') . '.xlsx';
$row = $sheet->getHighestRow();
foreach($invoicesList as $invoice){
$sheet->setCellValue('A'.$row, $invoice->getInvoiceNo());
$xeroContact = $xeroContactRepository->findOneBy(['xeroContactId' => $invoice->getXeroContactId()]);
//$invoice->xeroContactName = $xeroContact ? $xeroContact->getName() : null;
$sheet->setCellValue('B'.$row, $xeroContact ? $xeroContact->getName() : '-');
$sheet->setCellValue('C'.$row, $invoice->getCurrency ()->getIso());
$sheet->setCellValue('D'.$row, $invoice->getSubTotalUsd() ?: '-');
$creditNote = $invoice->getXeroCreditNoteAllocation();
$sheet->setCellValue('I'.$row, $creditNote ? $creditNote->getXeroCreditNote()->getCreditNoteNo() : '-');
$sheet->setCellValue('L'.$row, $invoice->getCreator());
$sheet->setCellValue('M'.$row, $invoice->getDueDate() ? $invoice->getDueDate()->format('d M Y') : "");
$sheet->setCellValue('N'.$row, $invoice->getXeroStatus() == 'AUTHORISED'? 'NOT PAID' : $invoice->getXeroStatus());
$soInvoices = $invoice->getSalesOrderInvoices();
// Amount Left
$amountLeft = '-';
if(count($soInvoices) > 0){
$amountLeft = $invoice->getSubTotalUsd() - $invoice->getTotalAmountUsedUsd();
}
$sheet->setCellValue('E'.$row, $amountLeft);
// Allocated
$allocatedRow = $row;
if(count($soInvoices) > 0){
foreach($soInvoices as $soInvoice){
$sheet->setCellValue('F'.$row, $soInvoice->getAmountUsd() ?: '-');
$sheet->setCellValue('G'.$row, $soInvoice->getSalesOrder()->getSalesOrderNo());
$sheet->setCellValue('J'.$row, $soInvoice->getProject() ? $soInvoice->getProject()->getClient()->getName() : '-');
$sheet->setCellValue('K'.$row, $soInvoice->getProject() ? $soInvoice->getProject()->fullName() : '-');
$sheet->getCell('K' . $row)->getHyperlink()->setUrl($baseurl . '/project-management/project/view/' . $soInvoice->getProject()->getId());
$sheet->getStyle('K' . $row)->getFont()->setUnderline(true);
// $allocatedRow++;
$purchases = $soInvoice->getSalesOrder()->getSalesOrderPurchaseOrders();
if(count($purchases) > 0){
foreach($purchases as $purchase){
$sheet->setCellValue('H'.$row, $purchase->getPurchaseOrder()->getPurchaseOrderNo());
$row++;
}
}else{
$sheet->setCellValue('H'.$row,'-');
$row++;
}
// $row++;
}
}else{
$sheet->setCellValue('F'.$row,'-');
$sheet->setCellValue('G'.$row,'-');
$row++;
}
// Client
// PO Number
/*
$salesOrderInvoices = $invoice->getSalesOrderInvoices();
$salesOrderNums = [];
$PONums = [];
foreach ($salesOrderInvoices as $salesOrderInvoice){
$salesOrder = $salesOrderInvoice->getSalesOrder();
array_push($salesOrderNums, $salesOrder->getSalesOrderNo());
$salesOrderPurchaseOrders = null;
if($salesOrder){
$salesOrderPurchaseOrders = $salesOrder->getSalesOrderPurchaseOrders();
}
foreach ($salesOrderPurchaseOrders as $salesOrderPurchaseOrder) {
$poNum = $salesOrderPurchaseOrder->getPurchaseOrder() ? $salesOrderPurchaseOrder->getPurchaseOrder()->getPurchaseOrderNo() : "";
array_push($PONums, $poNum);
}
}
*/
// $row++;
}
$sheet->setAutoFilter('A1:'. $sheet->getHighestColumn() . $sheet->getHighestRow());
// $sheet->getStyle('D2:F' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$sheet->getStyle('D2:F' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
// Set the content-type:
if($_target == 'google'){
$gsheetURL = $googleDriveService->uploadToGoogleDrive($filename);
if($gsheetURL){
unlink($filename);
return new RedirectResponse($gsheetURL, 302);
}
}else{
$response = new Response();
$response->headers->set('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', sprintf('attachment; filename="%s"', $filename));
$response->setContent(file_get_contents($filename));
$response->setStatusCode(\Symfony\Component\HttpFoundation\Response::HTTP_OK);
$response->headers->set('Content-Transfer-Encoding', 'binary');
$response->headers->set('Pragma', 'no-cache');
$response->headers->set('Expires', '0');
unlink($filename);
return $response;
exit;
}
}
/**
* @param InvoiceRepository $invoiceRepository
* @param Request $request
* @return Response
*/
#[Route(path: '/invoice-viewer/report/client/overdue/{_target}', defaults: ['_target' => ''], name: 'report_invoice_client_overdue')]
public function reportInvoiceClientOverdue(string $_target, InvoiceRepository $invoiceRepository, Request $request, SalesOrderInvoiceRepository $salesOrderInvoiceRepository, GoogleDriveService $googleDriveService): Response
{
$keyword = $request->get('keyword');
$order = $request->get('order');
$client = $request->get('client');
$status = $request->get('status');
$orderBy = $request->get('orderBy');
$start = $request->get('start');
$end = $request->get('end');
$waitForPage = $request->query->get('waitForPage');
$waitForPage === null ? $result['waitForPage'] = 'false' : $result['waitForPage'] = $waitForPage;
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
if (strpos($start, '/') !== false) {
$startDate = str_replace('/', '-', $start);
$startDate = date("Y-m-d", strtotime($startDate));
$startDateRange = date("d-M-Y", strtotime($startDate));
}
if (strpos($end, '/') !== false) {
$endDate = str_replace('/', '-', $end);
$endDate = date("Y-m-d", strtotime($endDate));
$endDateRange = date("d-M-Y", strtotime($endDate));
}
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet()->setTitle('All');
$sheet->setCellValue('A1', 'Today\'s Date');
$sheet->setCellValue('B1', date("d-M-Y"));
$sheet->setCellValue('A2', 'From');
$sheet->setCellValue('B2', $startDateRange);
$sheet->setCellValue('A3', 'To');
$sheet->setCellValue('B3', $endDateRange);
$sheet->setCellValue('A5', 'Client');
$sheet->setCellValue('B5', 'Project');
$sheet->setCellValue('C5', 'Client PIC Name');
$sheet->setCellValue('D5', 'Client PIC Email');
$sheet->setCellValue('E5', 'PIC Name');
$sheet->setCellValue('F5', 'PIC Email');
$sheet->setCellValue('G5', 'Invoice');
$sheet->setCellValue('H5', 'Amount without GST INV Currency');
$sheet->setCellValue('I5', 'Amount with GST INV Currency');
$sheet->setCellValue('J5', 'Currency');
$sheet->setCellValue('K5', 'Amount without GST USD');
$sheet->setCellValue('L5', 'Amount with GST USD');
$sheet->setCellValue('M5', 'Invoice Date');
$sheet->setCellValue('N5', 'Due Date');
$sheet->setCellValue('O5', 'Status');
$sheet->setCellValue('P5', 'Overdue');
$lastCol = 'P';
$sheet->getDefaultColumnDimension()->setWidth(25);
$sheet->getColumnDimension('A')->setWidth(40);
$sheet->getColumnDimension('B')->setWidth(40);
$sheet->getColumnDimension('D')->setWidth(38);
$sheet->getColumnDimension('E')->setWidth(40);
$sheet->getColumnDimension('F')->setWidth(40);
$sheet->getColumnDimension('O')->setWidth(15);
$sheet->getStyle("A1:B3")->getFont()->setBold(true);
$sheet->getStyle("A5:".$lastCol."5")->getFont()->setBold(true);
$sheet->getStyle("A5:".$lastCol."5")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$sheet->getStyle("A5:".$lastCol."5")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$sheet->getStyle("A6:".$lastCol."6")->getFont()->setBold(false);
$sheet->getStyle("A6:".$lastCol."6")->getAlignment()->setWrapText(true);
$sheet->getStyle("A6:".$lastCol."6")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$invoicesList = $salesOrderInvoiceRepository->findByFilter($keyword, $order, $orderBy, $status, $client, $startDate, $endDate);
// $invoicesList = $invoiceRepository->findByFilter($keyword, $order, $orderBy, $status, $client, $startDate, $endDate);
$filename = 'Overdue_Invoice'.'_'. date('Y-m-d') . '.xlsx';
$row = $sheet->getHighestRow();
foreach($invoicesList as $soInvoice){
$clientName = $soInvoice->getProject()->getClient()->getName();
$projectName = $soInvoice->getProject()->fullName();
$clientPic = $soInvoice->getProject()->getClientPersonInCharge();
$projectPic = $soInvoice->getProject()->getPersonInCharge();
$invoice = $soInvoice->getInvoice();
$amountWoGST = $soInvoice->getAmount();
$totalAmountWoGST = $invoice->getSubTotal();
$totalAmountWoGSTUsd = $invoice->getSubTotalUsd();
$totalAmountwGST = $invoice->getTotal();
$totalAmountwGSTUsd = $invoice->getTotalUsd();
$sheet->setCellValue('A'.$row, $clientName);
$sheet->setCellValue('B'.$row, $projectName);
$sheet->setCellValue('C'.$row, $clientPic ? $clientPic->getName() : '-');
$sheet->setCellValue('D'.$row, $clientPic ? $clientPic->getEmail() : '-');
$sheet->setCellValue('E'.$row, $projectPic ? $projectPic->getPersonalInfo()->getFullName() : '-');
$sheet->setCellValue('F'.$row, $projectPic ? $projectPic->getEmail() : '-');
$sheet->setCellValue('G'.$row, $invoice->getInvoiceNo());
$sheet->setCellValue('H'.$row, $amountWoGST);
$amountRatio = $amountWoGST / $totalAmountWoGST;
$amountWoGSTUsd = $amountRatio * $totalAmountWoGSTUsd;
$amountwGST = $amountRatio * $totalAmountwGST;
$amountwGSTUsd = $amountRatio * $totalAmountwGSTUsd;
$sheet->setCellValue('I'.$row, $amountwGST);
$sheet->setCellValue('J'.$row, $invoice->getCurrency()->getIso());
$sheet->setCellValue('K'.$row, $amountWoGSTUsd);
$sheet->setCellValue('L'.$row, $amountwGSTUsd);
$sheet->setCellValue('M'.$row, $invoice->getInvoiceDate()->format('d-M-Y'));
$sheet->setCellValue('N'.$row, $invoice->getDueDate()->format('d-M-Y'));
$sheet->setCellValue('O'.$row, $invoice->getCustomXeroStatus());
$overDueDays = date_diff($invoice->getDueDate(), new \DateTime());
$sheet->setCellValue('P'.$row, $overDueDays->invert == 0 ? $overDueDays->days.' Days' : '-');
$color = 'FF0000';
$fontColor = 'FFFFFF';
if($overDueDays->invert == 0){
$sheet->getStyle('P'.$row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB($color);
$sheet->getStyle('P'.$row)->getFont()->getColor()->setRGB($fontColor);
}
$row++;
}
$sheet->setAutoFilter('A5:'.$lastCol.$sheet->getHighestRow());
// $sheet->getStyle('H5:I' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
// $sheet->getStyle('K5:L' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$sheet->getStyle('H5:I' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$sheet->getStyle('K5:L' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$overdueSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Overdue');
$spreadsheet->addSheet($overdueSheet, 1);
$overdueSheet->getDefaultColumnDimension()->setWidth(30);
$overdueSheet->setCellValue('A1', 'Today\'s Date');
$overdueSheet->setCellValue('B1', date("d-M-Y"));
$overdueSheet->setCellValue('A2', 'From');
$overdueSheet->setCellValue('B2', $startDateRange);
$overdueSheet->setCellValue('A3', 'To');
$overdueSheet->setCellValue('B3', $endDateRange);
$overdueSheet->setCellValue('A5', 'Client');
$overdueSheet->setCellValue('B5', 'Total Due (INV Currency with GST)');
$overdueSheet->setCellValue('C5', 'Total Due (USD Currency with GST)');
$overdueSheet->setCellValue('D5', 'Total Overdue (INV Currency with GST)');
$overdueSheet->setCellValue('E5', 'Total Overdue (USD Currency with GST)');
$lastCol2 = 'E';
$overdueSheet->getDefaultColumnDimension()->setWidth(25);
$overdueSheet->getStyle("A1:B3")->getFont()->setBold(true);
$overdueSheet->getStyle("A5:".$lastCol2."5")->getFont()->setBold(true);
$overdueSheet->getStyle("A5:".$lastCol2."5")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$overdueSheet->getStyle("A5:".$lastCol2."5")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$overdueSheet->getStyle("A6:".$lastCol2."6")->getFont()->setBold(false);
$overdueSheet->getStyle("A6:".$lastCol2."6")->getAlignment()->setWrapText(true);
$overdueSheet->getStyle("A6:".$lastCol2."6")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
/*
$overdueList = $salesOrderInvoiceRepository->findByFilterGroupByProject($keyword, $order, $orderBy, $status, $client, $startDate, $endDate);
$row = $overdueSheet->getHighestRow();
foreach($overdueList as $soInvoice){
$overdueSheet->setCellValue('A'.$row, $soInvoice['clientName']);
$overdueSheet->setCellValue('B'.$row, $soInvoice['totalDue']);
$overdueSheet->setCellValue('C'.$row, '');
$overdueSheet->setCellValue('D'.$row, $soInvoice['totalOverdue']);
$overdueSheet->setCellValue('E'.$row, '');
$row++;
}
$overdueSheet->setAutoFilter('A5:'.$lastCol2.$sheet->getHighestRow());
$overdueSheet->getStyle('B5:E' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
*/
// $overdueList = $salesOrderInvoiceRepository->findByFilterGroupByProject($keyword, $order, $orderBy, $status, $client, $startDate, $endDate);
$overdueList = $invoiceRepository->findByTest($keyword, $order, $orderBy, $status, $client, $startDate, $endDate);
$newArray = [];
$previousInvoiceNo = [];
foreach ($overdueList as $item) {
$clientName = $item['clientName'];
$invoiceNo = $item['invoiceNo'];
// if ($invoiceNo !== $previousInvoiceNo) {
if (!in_array($invoiceNo, $previousInvoiceNo)) {
if (!isset($newArray[$clientName])) {
$newArray[$clientName] = $item;
} else {
$newArray[$clientName]['totalDue'] += (float)$item['totalDue'];
$newArray[$clientName]['totalDueUsd'] += (float)$item['totalDueUsd'];
$newArray[$clientName]['totalOverdue'] += (float)$item['totalOverdue'];
$newArray[$clientName]['totalOverdueUsd'] += (float)$item['totalOverdueUsd'];
}
}
$previousInvoiceNo[] = $invoiceNo;
}
$newArray = array_values($newArray);
$row = $overdueSheet->getHighestRow();
foreach($newArray as $soInvoice){
$overdueSheet->setCellValue('A'.$row, $soInvoice['clientName']);
$overdueSheet->setCellValue('B'.$row, $soInvoice['totalDue']);
$overdueSheet->setCellValue('C'.$row, $soInvoice['totalDueUsd']);
$overdueSheet->setCellValue('D'.$row, $soInvoice['totalOverdue']);
$overdueSheet->setCellValue('E'.$row, $soInvoice['totalOverdueUsd']);
$row++;
}
$overdueSheet->setAutoFilter('A5:'.$lastCol2.$sheet->getHighestRow());
// $overdueSheet->getStyle('B5:E' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$sheet->getStyle('B5:E' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$spreadsheet->setActiveSheetIndex(0);
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
// Set the content-type:
if($_target == 'google'){
$gsheetURL = $googleDriveService->uploadToGoogleDrive($filename);
if($gsheetURL){
unlink($filename);
return new RedirectResponse($gsheetURL, 302);
}
}else{
$response = new Response();
$response->headers->set('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', sprintf('attachment; filename="%s"', $filename));
$response->setContent(file_get_contents($filename));
$response->setStatusCode(\Symfony\Component\HttpFoundation\Response::HTTP_OK);
$response->headers->set('Content-Transfer-Encoding', 'binary');
$response->headers->set('Pragma', 'no-cache');
$response->headers->set('Expires', '0');
unlink($filename);
return $response;
exit;
}
}
/**
* @param Request $request
* @param InvoiceRepository $invoiceRepository
* @param XeroContactRepository $xeroContactRepository
* @param VendorInvoiceRepository $vendorInvoiceRepository
* @return Response
*/
#[Route(path: '/invoice-viewer/export/vendor/{_target}', defaults: ['_target' => ''], name: 'export_invoice_vendor')]
public function exportVendorInvoice(string $_target, Request $request, InvoiceRepository $invoiceRepository, XeroContactRepository $xeroContactRepository, VendorInvoiceRepository $vendorInvoiceRepository, GoogleDriveService $googleDriveService): Response
{
$keyword = $request->get('keyword');
$order = $request->get('order');
$client = $request->get('client');
$status = $request->get('status');
$orderBy = $request->get('orderBy');
$vendor = $request->get('vendor');
$blotter = "IMPORTED";
$vendorQuotationPlanning = $request->get('vendorQuotationPlanning');
$start = $request->get('start');
$end = $request->get('end');
$waitForPage = $request->query->get('waitForPage');
$waitForPage === null ? $result['waitForPage'] = 'false' : $result['waitForPage'] = $waitForPage;
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet()->setTitle('Vendor Invoices');
$sheet->setCellValue('A1', 'Invoice');
$sheet->setCellValue('B1', 'Vendor Name');
$sheet->setCellValue('C1', 'Client');
$sheet->setCellValue('D1', 'Project');
$sheet->setCellValue('E1', 'Vendor Qoute');
$sheet->setCellValue('F1', 'Amount');
$sheet->setCellValue('G1', 'Allocated');
$sheet->setCellValue('H1', 'Amount Left');
$sheet->setCellValue('I1', 'Date');
$sheet->setCellValue('J1', 'Due Date');
$sheet->setCellValue('K1', 'Client INV');
$sheet->setCellValue('L1', 'Client INV Issue Date');
$sheet->setCellValue('M1', 'Total Client INV Amount');
$sheet->setCellValue('N1', 'Total Billed 3rd Party');
$sheet->setCellValue('O1', 'Status');
$sheet->setCellValue('P1', 'Financial Year');
$lastCol = $sheet->getHighestColumn();
$sheet->getDefaultColumnDimension()->setWidth(25);
$sheet->getStyle("A1:".$lastCol."1")->getFont()->setBold(true);
$sheet->getStyle("A1:".$lastCol."1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$sheet->getStyle("A1:".$lastCol."1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$sheet->getStyle("A2:".$lastCol."2")->getFont()->setBold(false);
$sheet->getStyle("A2:".$lastCol."2")->getAlignment()->setWrapText(true);
$sheet->getStyle("A2:".$lastCol."2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$vendorInvoicesList = $vendorInvoiceRepository->findByPage(0, 99999, $keyword, $order, $orderBy, $vendorQuotationPlanning, $blotter, $client, $vendor, $status, $start, $end);
$filename = 'Vendor-Invoices_' .$status.'_'.$client.'_'. date('Y-m-d') . '.xlsx';
try {
$row = $sheet->getHighestRow();
foreach ($vendorInvoicesList as $vendorInvoice) {
$vendorQuotationPlanningVendorInvoices = $vendorInvoice->getVendorQuotationPlanningVendorInvoices();
$vqpviLength = count($vendorQuotationPlanningVendorInvoices);
$xeroContact = $xeroContactRepository->findOneBy(['xeroContactId' => $vendorInvoice->getXeroContactId()]);
$parentRow = $row;
$sheet->setCellValue('A' . $row, $vendorInvoice->getVendorInvoiceNo() ?: '-');
$sheet->setCellValue('F' . $row, $vendorInvoice->getSubTotalUsd());
$sheet->setCellValue('H' . $row, $vendorInvoice->getAmountLeftUsd());
$sheet->setCellValue('I' . $row, $vendorInvoice->getInvoiceDate()->format("d M Y"));
$sheet->setCellValue('J' . $row, $vendorInvoice->getDueDate()->format("d M Y"));
$sheet->setCellValue('P' . $row, $vendorInvoice->getFinancialYear());
$clientInvoices = [];
// if(!empty($vendorInvoice->getInvoiceVendorInvoices()->toArray())){
// foreach($vendorInvoice->getInvoiceVendorInvoices()->toArray() as $invVendorI){
// $clientInv = $invVendorI->getInvoice()->getInvoiceNo();
// array_push($clientInvoices, $clientInv);
// }
// }
// $sheet->setCellValue('K'. $row, $clientInvoices ? implode(';', $clientInvoices) : '-');
$sheet->setCellValue('O' . $row, $vendorInvoice->getXeroStatus() == 'AUTHORISED'? 'NOT PAID' : $vendorInvoice->getXeroStatus());
$vendorName = '';
$amount = '';
foreach ($vendorQuotationPlanningVendorInvoices as $vqpi) {
$vendorQuotationPlanning = $vqpi->getVendorQuotationPlanning();
if($vqpi){
$vendorName = $vqpi->getVendorQuotationPlanning() ? $vqpi->getVendorQuotationPlanning()->getVendor()->getName() : "-";
}elseif($vendorInvoice->getXeroContactId() == null){
$vendorName = $vendorInvoice->getVendor()->getName();
}else{
$vendorName = $xeroContact ? $xeroContact->getName() : null;
$vendorName = "[".$vendorName."]";
}
$sheet->setCellValue('C' . $row, $vendorQuotationPlanning ? $vendorQuotationPlanning->getProject()->getClient()->getName() : "-");
$sheet->setCellValue('D' . $row, $vendorQuotationPlanning ? $vendorQuotationPlanning->getProject()->fullName() : "-");
if($vendorQuotationPlanning){
$sheet->getCell('D' . $row)->getHyperlink()->setUrl($baseurl . '/project-management/project/view/' . $vendorQuotationPlanning->getProject()->getId());
} else {
$sheet->getCell('D' . $row, '-');
}
$sheet->getStyle('D' . $row)->getFont()->setUnderline(true);
$amount = $vqpi->getAmountUsd();
$sheet->setCellValue('G' . $row, $amount);
$sheet->setCellValue('K'. $row, $vqpi->getInvoice() ? $vqpi->getInvoice()->getInvoiceNo() : '-');
$sheet->setCellValue('L'. $row, $vqpi->getInvoice() && $vqpi->getInvoice()->getInvoiceDate() ? $vqpi->getInvoice()->getInvoiceDate()->format('d M Y') : '-');
$sheet->setCellValue('M'. $row, $vqpi->getInvoice() ? $vqpi->getInvoice()->getSubTotalUsd() : '-');
$sheet->setCellValue('N'. $row, $vqpi->getInvoice() ? $vqpi->getInvoice()->getBilled3rdPartyAmountUsd() : '-');
if($vqpi->getVendorQuotationPlanning()){
$vqRow = $row;
$vendorQuotations = $vqpi->getVendorQuotationPlanning()->getVendorQuotations() ?? null;
if(count($vendorQuotations) > 0){
foreach($vendorQuotations as $vendorQoute){
$sheet->setCellValue('E' . $row, $vendorQoute->getAmountUsd());
$row++;
}
$allocatedRow = $row;
$sheet->mergeCells('C'.$vqRow.':C'.($allocatedRow - 1));
$sheet->mergeCells('D'.$vqRow.':D'.($allocatedRow - 1));
$sheet->mergeCells('G'.$vqRow.':G'.($allocatedRow - 1));
$sheet->mergeCells('K'.$vqRow.':K'.($allocatedRow - 1));
$sheet->mergeCells('L'.$vqRow.':L'.($allocatedRow - 1));
$sheet->mergeCells('M'.$vqRow.':M'.($allocatedRow - 1));
$sheet->mergeCells('N'.$vqRow.':N'.($allocatedRow - 1));
$sheet->getStyle('C'.$vqRow.':C'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('D'.$vqRow.':D'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('G'.$vqRow.':G'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('K'.$vqRow.':K'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('L'.$vqRow.':L'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('M'.$vqRow.':M'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('N'.$vqRow.':N'.($allocatedRow - 1))->getAlignment()->setVertical('center');
}else{
$sheet->setCellValue('E' . $row, "-");
$row++;
}
} else {
$sheet->setCellValue('E' . $row, "-");
$row++;
}
}
$sheet->setCellValue('B' . $parentRow, $vendorName);
if($vqpviLength == 0){
$vendorName = '';
if($vendorInvoice->getXeroContactId()){
$vendorName = $xeroContact ? $xeroContact->getName() : null;
$vendorName = "[".$vendorName."]";
$sheet->setCellValue('B' . $row, $vendorName);
}else{
$sheet->setCellValue('B' . $row, $vendorInvoice->getVendor()->getName());
}
$sheet->setCellValue('C' . $row, '-');
$sheet->setCellValue('D' . $row, '-');
$sheet->setCellValue('G' . $row, '-');
$sheet->setCellValue('K' . $row, '-');
$sheet->setCellValue('L' . $row, '-');
$sheet->setCellValue('M' . $row, '-');
$sheet->setCellValue('N' . $row, '-');
}
if($vqpviLength > 0){
$sheet->mergeCells('A'.$parentRow.':A'.($row - 1));
$sheet->mergeCells('B'.$parentRow.':B'.($row - 1));
$sheet->mergeCells('F'.$parentRow.':F'.($row - 1));
$sheet->mergeCells('H'.$parentRow.':H'.($row - 1));
$sheet->mergeCells('I'.$parentRow.':I'.($row - 1));
$sheet->mergeCells('J'.$parentRow.':J'.($row - 1));
// $sheet->mergeCells('K'.$parentRow.':K'.($row - 1));
$sheet->mergeCells('O'.$parentRow.':O'.($row - 1));
$sheet->mergeCells('P'.$parentRow.':P'.($row - 1));
$sheet->getStyle('A'.$parentRow.':A'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('B'.$parentRow.':B'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('F'.$parentRow.':F'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('H'.$parentRow.':H'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('I'.$parentRow.':I'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('J'.$parentRow.':J'.($row - 1))->getAlignment()->setVertical('center');
// $sheet->getStyle('K'.$parentRow.':K'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('O'.$parentRow.':O'.($row - 1))->getAlignment()->setVertical('center');
$sheet->getStyle('P'.$parentRow.':P'.($row - 1))->getAlignment()->setVertical('center');
}
if($vqpviLength == 0){
$row++;
}
}
} catch (\Exception $e) {
dd($e->getMessage());
}
$sheet->setAutoFilter('A1:'. $sheet->getHighestColumn() . $sheet->getHighestRow());
// $sheet->getStyle('E2:H' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$sheet->getStyle('E2:H' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$unallocatedSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Amount Left Invoices');
$spreadsheet->addSheet($unallocatedSheet, 1);
$unallocatedSheet->setCellValue('A1', 'Invoice');
$unallocatedSheet->setCellValue('B1', 'Vendor Name');
$unallocatedSheet->setCellValue('C1', 'Client');
$unallocatedSheet->setCellValue('D1', 'Project');
$unallocatedSheet->setCellValue('E1', 'Vendor Qoute');
$unallocatedSheet->setCellValue('F1', 'Amount');
$unallocatedSheet->setCellValue('G1', 'Allocated');
$unallocatedSheet->setCellValue('H1', 'Amount Left');
$unallocatedSheet->setCellValue('I1', 'Date');
$unallocatedSheet->setCellValue('J1', 'Due Date');
$unallocatedSheet->setCellValue('K1', 'Client INV');
$unallocatedSheet->setCellValue('L1', 'Client INV Issue Date');
$unallocatedSheet->setCellValue('M1', 'Total Client INV Amount');
$unallocatedSheet->setCellValue('N1', 'Total Billed 3rd Party');
$unallocatedSheet->setCellValue('O1', 'Status');
$unallocatedSheet->setCellValue('P1', 'Financial Year');
$lastColx = $unallocatedSheet->getHighestColumn();
$unallocatedSheet->getDefaultColumnDimension()->setWidth(25);
$unallocatedSheet->getStyle("A1:".$lastColx."1")->getFont()->setBold(true);
$unallocatedSheet->getStyle("A1:".$lastColx."1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$unallocatedSheet->getStyle("A1:".$lastColx."1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$unallocatedSheet->getStyle("A2:".$lastColx."2")->getFont()->setBold(false);
$unallocatedSheet->getStyle("A2:".$lastColx."2")->getAlignment()->setWrapText(true);
$unallocatedSheet->getStyle("A2:".$lastColx."2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$vendorInvoicesListUnallocated = $vendorInvoiceRepository->findByPage(0, 99999, $keyword, $order, $orderBy, $vendorQuotationPlanning, $blotter, $client, $vendor, 'Amount Left', $start, $end);
try {
$row = $unallocatedSheet->getHighestRow();
foreach ($vendorInvoicesListUnallocated as $vendorInvoice) {
$vendorQuotationPlanningVendorInvoices = $vendorInvoice->getVendorQuotationPlanningVendorInvoices();
$vqpviLength = count($vendorQuotationPlanningVendorInvoices);
$xeroContact = $xeroContactRepository->findOneBy(['xeroContactId' => $vendorInvoice->getXeroContactId()]);
$parentRow = $row;
$unallocatedSheet->setCellValue('A' . $row, $vendorInvoice->getVendorInvoiceNo() ?: '-');
$unallocatedSheet->setCellValue('F' . $row, $vendorInvoice->getSubTotalUsd());
$unallocatedSheet->setCellValue('H' . $row, $vendorInvoice->getAmountLeftUsd());
$unallocatedSheet->setCellValue('I' . $row, $vendorInvoice->getInvoiceDate()->format("d M Y"));
$unallocatedSheet->setCellValue('J' . $row, $vendorInvoice->getDueDate()->format("d M Y"));
$unallocatedSheet->setCellValue('P' . $row, $vendorInvoice->getFinancialYear());
$clientInvoices = [];
// if(!empty($vendorInvoice->getInvoiceVendorInvoices()->toArray())){
// foreach($vendorInvoice->getInvoiceVendorInvoices()->toArray() as $invVendorI){
// $clientInv = $invVendorI->getInvoice()->getInvoiceNo();
// array_push($clientInvoices, $clientInv);
// }
// }
// $unallocatedSheet->setCellValue('K'. $row, $clientInvoices ? implode(';', $clientInvoices) : '-');
$unallocatedSheet->setCellValue('O' . $row, $vendorInvoice->getXeroStatus() == 'AUTHORISED'? 'NOT PAID' : $vendorInvoice->getXeroStatus());
$vendorName = '';
$amount = '';
foreach ($vendorQuotationPlanningVendorInvoices as $vqpi) {
$vendorQuotationPlanning = $vqpi->getVendorQuotationPlanning();
if($vqpi){
$vendorName = $vqpi->getVendorQuotationPlanning() ? $vqpi->getVendorQuotationPlanning()->getVendor()->getName() : "-";
}elseif($vendorInvoice->getXeroContactId() == null){
$vendorName = $vendorInvoice->getVendor()->getName();
}else{
$vendorName = $xeroContact ? $xeroContact->getName() : null;
$vendorName = "[".$vendorName."]";
}
$unallocatedSheet->setCellValue('C' . $row, $vendorQuotationPlanning ? $vendorQuotationPlanning->getProject()->getClient()->getName() : "-");
$unallocatedSheet->setCellValue('D' . $row, $vendorQuotationPlanning ? $vendorQuotationPlanning->getProject()->fullName() : "-");
if($vendorQuotationPlanning){
$unallocatedSheet->getCell('D' . $row)->getHyperlink()->setUrl($baseurl . '/project-management/project/view/' . $vendorQuotationPlanning->getProject()->getId());
} else {
$unallocatedSheet->getCell('D' . $row, '-');
}
$unallocatedSheet->getStyle('D' . $row)->getFont()->setUnderline(true);
$amount = $vqpi->getAmountUsd();
$unallocatedSheet->setCellValue('G' . $row, $amount);
$unallocatedSheet->setCellValue('K'. $row, $vqpi->getInvoice() ? $vqpi->getInvoice()->getInvoiceNo() : '-');
$unallocatedSheet->setCellValue('L'. $row, $vqpi->getInvoice() && $vqpi->getInvoice()->getInvoiceDate() ? $vqpi->getInvoice()->getInvoiceDate()->format('d M Y') : '-');
$unallocatedSheet->setCellValue('M'. $row, $vqpi->getInvoice() ? $vqpi->getInvoice()->getSubTotalUsd() : '-');
$unallocatedSheet->setCellValue('N'. $row,$vqpi->getInvoice() ? $vqpi->getInvoice()->getBilled3rdPartyAmountUsd() : '-');
if($vqpi->getVendorQuotationPlanning()){
$vqRow = $row;
$vendorQuotations = $vqpi->getVendorQuotationPlanning()->getVendorQuotations() ?? null;
if(count($vendorQuotations) > 0){
foreach($vendorQuotations as $vendorQoute){
$unallocatedSheet->setCellValue('E' . $row, $vendorQoute->getAmountUsd());
$row++;
}
$allocatedRow = $row;
$unallocatedSheet->mergeCells('C'.$vqRow.':C'.($allocatedRow - 1));
$unallocatedSheet->mergeCells('D'.$vqRow.':D'.($allocatedRow - 1));
$unallocatedSheet->mergeCells('G'.$vqRow.':G'.($allocatedRow - 1));
$unallocatedSheet->mergeCells('K'.$vqRow.':K'.($allocatedRow - 1));
$unallocatedSheet->mergeCells('L'.$vqRow.':L'.($allocatedRow - 1));
$unallocatedSheet->mergeCells('M'.$vqRow.':M'.($allocatedRow - 1));
$unallocatedSheet->mergeCells('N'.$vqRow.':N'.($allocatedRow - 1));
$unallocatedSheet->getStyle('C'.$vqRow.':C'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('D'.$vqRow.':D'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('G'.$vqRow.':G'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('K'.$vqRow.':K'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('L'.$vqRow.':L'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('M'.$vqRow.':M'.($allocatedRow - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('N'.$vqRow.':N'.($allocatedRow - 1))->getAlignment()->setVertical('center');
}else{
$unallocatedSheet->setCellValue('E' . $row, "-");
$row++;
}
} else {
$unallocatedSheet->setCellValue('E' . $row, "-");
$row++;
}
}
$unallocatedSheet->setCellValue('B' . $parentRow, $vendorName);
if($vqpviLength == 0){
$vendorName = '';
if($vendorInvoice->getXeroContactId()){
$vendorName = $xeroContact ? $xeroContact->getName() : null;
$vendorName = "[".$vendorName."]";
$unallocatedSheet->setCellValue('B' . $row, $vendorName);
}else{
$unallocatedSheet->setCellValue('B' . $row, $vendorInvoice->getVendor()->getName());
}
$unallocatedSheet->setCellValue('C' . $row, '-');
$unallocatedSheet->setCellValue('D' . $row, '-');
$unallocatedSheet->setCellValue('G' . $row, '-');
$unallocatedSheet->setCellValue('K' . $row, '-');
$unallocatedSheet->setCellValue('L' . $row, '-');
$unallocatedSheet->setCellValue('M' . $row, '-');
$unallocatedSheet->setCellValue('N' . $row, '-');
}
if($vqpviLength > 0){
$unallocatedSheet->mergeCells('A'.$parentRow.':A'.($row - 1));
$unallocatedSheet->mergeCells('B'.$parentRow.':B'.($row - 1));
$unallocatedSheet->mergeCells('F'.$parentRow.':F'.($row - 1));
$unallocatedSheet->mergeCells('H'.$parentRow.':H'.($row - 1));
$unallocatedSheet->mergeCells('I'.$parentRow.':I'.($row - 1));
$unallocatedSheet->mergeCells('J'.$parentRow.':J'.($row - 1));
// $unallocatedSheet->mergeCells('K'.$parentRow.':K'.($row - 1));
$unallocatedSheet->mergeCells('O'.$parentRow.':O'.($row - 1));
$unallocatedSheet->mergeCells('P'.$parentRow.':P'.($row - 1));
$unallocatedSheet->getStyle('A'.$parentRow.':A'.($row - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('B'.$parentRow.':B'.($row - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('F'.$parentRow.':F'.($row - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('H'.$parentRow.':H'.($row - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('I'.$parentRow.':I'.($row - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('J'.$parentRow.':J'.($row - 1))->getAlignment()->setVertical('center');
// $unallocatedSheet->getStyle('K'.$parentRow.':K'.($row - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('O'.$parentRow.':O'.($row - 1))->getAlignment()->setVertical('center');
$unallocatedSheet->getStyle('P'.$parentRow.':P'.($row - 1))->getAlignment()->setVertical('center');
}
if($vqpviLength == 0){
$row++;
}
}
} catch (\Exception $e) {
dd($e->getMessage());
}
$unallocatedSheet->setAutoFilter('A1:'. $unallocatedSheet->getHighestColumn() . $unallocatedSheet->getHighestRow());
// $sheet->getStyle('E2:H' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$unallocatedSheet->getStyle('E2:H' . $unallocatedSheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
if($_target == 'google'){
$gsheetURL = $googleDriveService->uploadToGoogleDrive($filename);
if($gsheetURL){
unlink($filename);
return new RedirectResponse($gsheetURL, 302);
}
}else{
$response = new Response();
$response->headers->set('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', sprintf('attachment; filename="%s"', $filename));
$response->setContent(file_get_contents($filename));
$response->setStatusCode(\Symfony\Component\HttpFoundation\Response::HTTP_OK);
$response->headers->set('Content-Transfer-Encoding', 'binary');
$response->headers->set('Pragma', 'no-cache');
$response->headers->set('Expires', '0');
unlink($filename);
return $response;
exit;
}
}
}

src/Controller/ProjectManagement/InvoiceViewerController.php line 44