Viewing file:  ProfitLossExport.php (10.1 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Illuminate\Support\Collection;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
class ProfitLossExport implements FromArray, WithEvents, WithHeadings, WithStyles, WithColumnWidths, WithCustomStartCell
{
/**
* @return \Illuminate\Support\Collection
*/
public function __construct($data , $startDate, $endDate, $companyName)
{
$formattedData = [];
$totalIncome = 0; $totalCosts = 0; $totalExpense =0;
foreach ($data as $category)
{
if($category['Type'] == 'Income' || $category['Type'] == 'Costs of Goods Sold')
{
$formattedData[] = [
'Account Name' => '',
'Account No' => '',
'Total' => ''
];
$formattedData[] = [
'Account Name' => $category['Type'],
'Account No' => '',
'Total' => ''
];
foreach($category['account'] as $accounts)
{
foreach($accounts as $account)
{
if($account['netAmount'] > 0)
{
$netAmount = $account['netAmount'];
}
else
{
$netAmount = -$account['netAmount'];
}
if ($account['account'] == 'parent' || $account['account'] == 'parentTotal')
{
$formattedData[] = [
'Account Name' => ' ' . $account['account_name'],
'Account No' => $account['account_code'],
'Total' => $netAmount
];
}
elseif (!preg_match('/\btotal\b/i', $account['account_name']) || $account['account'] == 'subAccount')
{
$formattedData[] = [
'Account Name' => ' ' . $account['account_name'],
'Account No' => $account['account_code'],
'Total' => $netAmount
];
}
else
{
$formattedData[] = [
'Account Name' => $account['account_name'],
'Account No' => $account['account_code'],
'Total' => $netAmount
];
}
if($account['account_name'] == 'Total Income')
{
$totalIncome = $netAmount;
}
if($account['account_name'] == 'Total Costs of Goods Sold')
{
$totalCosts = $netAmount;
}
}
}
}
}
$grossProfit = $totalIncome - $totalCosts;
$formattedData[] = [
'Account Name' => 'Gross Profit',
'Account No' => '',
'Total' => $grossProfit
];
foreach ($data as $category)
{
if($category['Type'] == 'Expenses')
{
$formattedData[] = [
'Account Name' => '',
'Account No' => '',
'Total' => ''
];
$formattedData[] = [
'Account Name' => $category['Type'],
'Account No' => '',
'Total' => ''
];
foreach($category['account'] as $accounts)
{
foreach($accounts as $account)
{
if($account['netAmount'] > 0)
{
$netAmount = $account['netAmount'];
}
else
{
$netAmount = -$account['netAmount'];
}
if ($account['account'] == 'parent' || $account['account'] == 'parentTotal')
{
$formattedData[] = [
'Account Name' => ' ' . $account['account_name'],
'Account No' => $account['account_code'],
'Total' => $netAmount
];
}
elseif (!preg_match('/\btotal\b/i', $account['account_name']) || $account['account'] == 'subAccount')
{
$formattedData[] = [
'Account Name' => ' ' . $account['account_name'],
'Account No' => $account['account_code'],
'Total' => $netAmount
];
}
else
{
$formattedData[] = [
'Account Name' => $account['account_name'],
'Account No' => $account['account_code'],
'Total' => $netAmount
];
}
}
}
$formattedData[] = [
'Account Name' => 'Net Profit/Loss',
'Account No' => '',
'Total' => $grossProfit - $totalExpense
];
}
}
$this->data = $formattedData;
$this->companyName = $companyName;
$this->startDate = $startDate;
$this->endDate = $endDate;
}
public function startCell(): string
{
return 'A5';
}
public function columnWidths(): array
{
return [
'A' => 30,
'B' => 15,
'C' => 15,
];
}
public function styles(Worksheet $sheet)
{
$sheet->getStyle('A5')->getFont()->setBold(true);
$sheet->getStyle('B5')->getFont()->setBold(true);
$sheet->getStyle('C5')->getFont()->setBold(true);
$sheet->getStyle('D5')->getFont()->setBold(true);
$sheet->getStyle('E5')->getFont()->setBold(true);
$sheet->getStyle('F5')->getFont()->setBold(true);
}
public function array(): array
{
return $this->data ;
}
public function registerEvents(): array
{
return [
BeforeWriting::class => function (BeforeWriting $event) {
},
AfterSheet::class => function (AfterSheet $event) {
$event->sheet->getDelegate()->mergeCells('A1:F1');
$event->sheet->getDelegate()->mergeCells('A2:F2');
$event->sheet->getDelegate()->mergeCells('A3:F3');
$event->sheet->getDelegate()->setCellValue('A1', 'Profit & Loss - ' . $this->companyName)->getStyle('A1')->getFont()->setBold(true);
$event->sheet->getDelegate()->setCellValue('A2', 'Print Out Date : ' . date('Y-m-d H:i'));
$event->sheet->getDelegate()->setCellValue('A3', 'Date : ' . $this->startDate . ' - ' . $this->endDate);
$event->sheet->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$event->sheet->getStyle('A2')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$event->sheet->getStyle('A3')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$event->sheet->getDelegate()->getStyle('A')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$event->sheet->getDelegate()->getStyle('B')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$data = $this->data;
foreach ($data as $index => $row) {
if (isset($row['Account Name']) && ($row['Account Name'] == 'Total Costs of Goods Sold' || $row['Account Name'] == 'Total Income' || $row['Account Name'] == 'Income'
|| $row['Account Name'] == 'Costs of Goods Sold' || $row['Account Name'] == 'Expenses' || $row['Account Name'] == 'Total Expenses')) {
$rowIndex = $index + 6; // Adjust for 1-based indexing and header row
$event->sheet->getStyle('A' . $rowIndex . ':C' . $rowIndex)
->applyFromArray([
'font' => [
'bold' => true,
],
]);
}
elseif(isset($row['Account Name']) && ($row['Account Name'] == 'Gross Profit' || $row['Account Name'] == 'Net Profit/Loss'))
{
$rowIndex = $index + 6; // Adjust for 1-based indexing and header row
$event->sheet->getStyle('A' . $rowIndex . ':C' . $rowIndex)
->applyFromArray([
'font' => [
'bold' => true,
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
],
]);
$event->sheet->mergeCells('A' . $rowIndex . ':B' . $rowIndex);
}
}
},
];
}
public function headings(): array
{
return [
"Account",
"Account No",
"Total",
];
}
}
|