Application\Service\Exporter\AccountingReport

Complexity

Total Complexity

53

Size/Duplication

Total Lines	406
Duplicated Lines	0 %

Test Coverage

Coverage

96.41%

Importance

Changes

0

12 Methods

Rating	Name	Duplication	Size	Complexity
A	getProfitOrLoss()	0	8	1
D	processAccounts()	0	30	20
A	sumBalance()	0	11	2
A	getHeaders()	0	15	1
A	getTitleForFilename()	0	3	1
A	applyExtraFormatting()	0	11	3
A	__construct()	0	10	1
A	writeTitle()	0	26	1
A	writeFooter()	0	57	1
F	writeData()	0	161	18
A	setDate()	0	3	1
A	cellsToSum()	0	11	3

<?php

declare(strict_types=1);

namespace Application\Service\Exporter;

use Application\DBAL\Types\AccountTypeType;
use Application\Model\Account;
use Cake\Chronos\Date;
use Ecodev\Felix\Format;
use Money\Money;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class AccountingReport extends AbstractExcel
{
    private array $accountingConfig;

    private Date $date;

    private array $assets = [];

    private array $liabilities = [];

    private array $expenses = [];

    private array $revenues = [];

    private static array $balanceFormat = [
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => [
                'argb' => 'FFDDDDDD',
            ],
        ],
        'numberFormat' => [
            'formatCode' => NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1, // eg. 12'345.67
        ],
    ];

    private static array $columnWidth = [
        'accountCode' => 11,
        'accountName' => 35,
        'balance' => 12,
    ];

    public function __construct(string $hostname, array $accountingConfig)
    {
        parent::__construct($hostname);

        $this->date = Date::today();
        $this->accountingConfig = $accountingConfig;

        $this->sheet->setTitle('Bilan + PP');
        $this->zebra = false;
        $this->autoFilter = false;
    }

    protected function getTitleForFilename(): string
    {
        return sprintf('compta_rapport_%s', $this->date->format('Y-m-d'));
    }

    public function setDate(Date $date): void
    {
        $this->date = $date;
    }

    protected function writeTitle(): void
    {
        $this->column = 1;
        $this->sheet->mergeCellsByColumnAndRow($this->column, $this->row, $this->column + 14, $this->row);
        $this->write(
            sprintf($this->hostname . ': rapport comptable au %s', $this->date->format('d.m.Y')),
            self::$titleFormat, self::$centerFormat
        );
        $this->sheet->getRowDimension($this->row)->setRowHeight(35);
        ++$this->row;

        $this->column = 1;
        $this->sheet->mergeCellsByColumnAndRow($this->column, $this->row, $this->column + 6, $this->row);
        $this->write(
            sprintf('Bilan'),
            self::$titleFormat, self::$centerFormat
        );
        $this->column = 9;
        $this->sheet->mergeCellsByColumnAndRow($this->column, $this->row, $this->column + 6, $this->row);
        $this->write(
            sprintf('Résultat'),
            self::$titleFormat, self::$centerFormat
        );

        $this->sheet->getRowDimension($this->row)->setRowHeight(35);
        ++$this->row;
    }

    private function processAccounts(array $accounts, int $depth): void
    {
        foreach ($accounts as $account) {
            $balance = $account->getBalanceAtDate($this->date);
            if ($this->accountingConfig['report']['showAccountsWithZeroBalance'] === false && $depth > 1 && $balance->isZero()) {
                continue;
            }
            if ($account->getType() === AccountTypeType::EQUITY) {
                // Don't show special accounts since it's an interim statement, their balance will be computed manually
                continue;
            }
            $data = [
                'code' => $account->getCode(),
                'name' => Format::truncate($account->getName(), 55),
                'depth' => $depth,
                'balance' => $balance,
                'account' => $account,
            ];
            if ($account->getType() === AccountTypeType::ASSET || ($account->getType() === AccountTypeType::GROUP && mb_substr((string) $account->getCode(), 0, 1) === '1')) {
                $this->assets[] = $data;
            } elseif ($account->getType() === AccountTypeType::LIABILITY || ($account->getType() === AccountTypeType::GROUP && mb_substr((string) $account->getCode(), 0, 1) === '2')) {
                $this->liabilities[] = $data;
            } elseif ($account->getType() === AccountTypeType::REVENUE || ($account->getType() === AccountTypeType::GROUP && mb_substr((string) $account->getCode(), 0, 1) === '3')) {
                $this->revenues[] = $data;
            } elseif ($account->getType() === AccountTypeType::EXPENSE || ($account->getType() === AccountTypeType::GROUP && in_array(mb_substr((string) $account->getCode(), 0, 1), ['4', '5', '6'], true))) {
                $this->expenses[] = $data;
            }
            if ($account->getType() === AccountTypeType::GROUP && $depth <= $this->accountingConfig['report']['maxAccountDepth']) {
                $children = $account->getChildren()->toArray();
                $this->processAccounts($children, $depth + 1);
            }
        }
    }

    /**
     * @param Account[] $accounts
     */
    protected function writeData(array $accounts): void
    {
        $this->processAccounts($accounts, 1);

        $this->sheet->setShowGridlines(false);

        $profitOrLoss = $this->getProfitOrLoss();

        if ($profitOrLoss->isNegative()) {
            // A loss is written at the end of the Revenues and Assets columns
            $data = [
                'code' => '',
                'name' => 'Résultat intermédiaire (perte)',
                'depth' => 1,
                'balance' => $profitOrLoss->absolute(),
                'account' => null,
                'format' => ['font' => ['color' => ['argb' => Color::COLOR_RED]]],
            ];
            $this->revenues[] = $data;
            $this->assets[] = $data;
        } else {
            // A profit is written at the end of the Expenses and Liabilities columns
            $data = [
                'code' => '',
                'name' => 'Résultat intermédiaire (bénéfice)',
                'depth' => 1,
                'balance' => $profitOrLoss,
                'account' => null,
                'format' => ['font' => ['color' => ['argb' => Color::COLOR_DARKGREEN]]],
            ];
            $this->expenses[] = $data;
            $this->liabilities[] = $data;
        }

        // Assets
        $this->column = $initialColumn = 1;
        $initialRow = $this->row;
        $firstLine = true;
        $this->lastDataRow = $this->row;
        foreach ($this->assets as $index => $data) {
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountCode']);
            }
            $format = ['font' => ['bold' => $data['depth'] <= 2]];
            $this->write(
                str_repeat('  ', $data['depth'] - 1) . $data['code'],
                ['alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'indent' => 1]],
                $format
            );
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountName']);
            }
            $this->write($data['name'], ['alignment' => ['wrapText' => true]], $format, $data['format'] ?? []);
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['balance']);
            }
            // Store the coordinate of the cell to later compute totals
            $this->assets[$index]['cell'] = $this->sheet->getCellByColumnAndRow($this->column, $this->row)->getCoordinate();
            $this->write($data['balance'], self::$balanceFormat);

            $firstLine = false;
            ++$this->row;
            $this->column = $initialColumn;

            $this->lastDataRow = max($this->lastDataRow, $this->row);
        }

        // Liabilities
        $this->row = $initialRow;
        $this->column = $initialColumn = $initialColumn + 4;
        $firstLine = true;
        foreach ($this->liabilities as $index => $data) {
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['balance']);
            }
            // Store the coordinate of the cell to later compute totals
            $this->liabilities[$index]['cell'] = $this->sheet->getCellByColumnAndRow($this->column, $this->row)->getCoordinate();
            $this->write($data['balance'], self::$balanceFormat);
            $format = ['font' => ['bold' => $data['depth'] <= 2]];
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountCode']);
            }
            $this->write(
                str_repeat('  ', $data['depth'] - 1) . $data['code'],
                ['alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'indent' => 1]],
                $format
            );
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountName']);
            }
            $this->write($data['name'], ['alignment' => ['wrapText' => true]], $format, $data['format'] ?? []);
            $firstLine = false;
            ++$this->row;
            $this->column = $initialColumn;

            $this->lastDataRow = max($this->lastDataRow, $this->row);
        }

        // Expenses
        $this->row = $initialRow;
        $this->column = $initialColumn = $initialColumn + 4;
        $firstLine = true;
        foreach ($this->expenses as $index => $data) {
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountCode']);
            }
            $format = ['font' => ['bold' => $data['depth'] === 1]];
            $this->write(
                str_repeat('  ', $data['depth'] - 1) . $data['code'],
                ['alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'indent' => 1]],
                $format
            );
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountName']);
            }
            $this->write($data['name'], ['alignment' => ['wrapText' => true]], $format, $data['format'] ?? []);
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['balance']);
            }
            // Store the coordinate of the cell to later compute totals
            $this->expenses[$index]['cell'] = $this->sheet->getCellByColumnAndRow($this->column, $this->row)->getCoordinate();
            $this->write($data['balance'], self::$balanceFormat);
            $firstLine = false;
            ++$this->row;
            $this->column = $initialColumn;

            $this->lastDataRow = max($this->lastDataRow, $this->row);
        }

        // Revenues
        $this->row = $initialRow;
        $this->column = $initialColumn = $initialColumn + 4;
        $firstLine = true;
        foreach ($this->revenues as $index => $data) {
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['balance']);
            }
            // Store the coordinate of the cell to later compute totals
            $this->revenues[$index]['cell'] = $this->sheet->getCellByColumnAndRow($this->column, $this->row)->getCoordinate();
            $this->write($data['balance'], self::$balanceFormat);
            $format = ['font' => ['bold' => $data['depth'] === 1]];
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountCode']);
            }
            $this->write(
                str_repeat('  ', $data['depth'] - 1) . $data['code'],
                ['alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'indent' => 1]],
                $format
            );
            if ($firstLine) {
                $this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountName']);
            }
            $this->write($data['name'], ['alignment' => ['wrapText' => true]], $format, $data['format'] ?? []);
            $firstLine = false;
            ++$this->row;
            $this->column = $initialColumn;

            $this->lastDataRow = max($this->lastDataRow, $this->row);
        }

        $this->applyExtraFormatting();
    }

    private function getProfitOrLoss(): Money
    {
        // Sum the profit and loss root accounts
        $totalRevenues = $this->sumBalance($this->revenues);

        $totalExpenses = $this->sumBalance($this->expenses);

        return $totalRevenues->subtract($totalExpenses);
    }

    protected function getHeaders(): array
    {
        $headers = [];

        $headers[] = ['label' => 'Actifs', 'formats' => [self::$headerFormat, self::$centerFormat], 'colspan' => 3];
        $headers[] = ['label' => '', 'width' => 3, 'formats' => []]; // margin
        $headers[] = ['label' => 'Passifs', 'formats' => [self::$headerFormat, self::$centerFormat], 'colspan' => 3];

        $headers[] = ['label' => '', 'width' => 5, 'formats' => []]; // margin

        $headers[] = ['label' => 'Charges', 'formats' => [self::$headerFormat, self::$centerFormat], 'colspan' => 3];
        $headers[] = ['label' => '', 'width' => 3, 'formats' => []]; // margin
        $headers[] = ['label' => 'Profits', 'formats' => [self::$headerFormat, self::$centerFormat], 'colspan' => 3];

        return $headers;
    }

    protected function writeFooter(): void
    {
        $initialColumn = $this->column;

        /** BALANCE SHEET */

        /** Assets */
        // Account.code
        $this->write('');
        // Account.name
        $this->write('');
        // Account.balance
        $cellsToSum = $this->cellsToSum($this->assets);
        $this->write('=SUM(' . implode(',', $cellsToSum) . ')', self::$balanceFormat, self::$totalFormat);

        // Margin
        $this->write('');

        /** Liabilities */
        // Account.balance
        $cellsToSum = $this->cellsToSum($this->liabilities);
        $this->write('=SUM(' . implode(',', $cellsToSum) . ')', self::$balanceFormat, self::$totalFormat);

        // Account.code
        $this->write('');
        // Account.name
        $this->write('');

        // Margin
        $this->write('');

        /** INCOME STATEMENT */

        /** Expenses */
        // Account.code
        $this->write('');
        // Account.name
        $this->write('');
        // Account.balance
        $cellsToSum = $this->cellsToSum($this->expenses);
        $this->write('=SUM(' . implode(',', $cellsToSum) . ')', self::$balanceFormat, self::$totalFormat);

        // Margin
        $this->write('');

        /** Revenues ** */
        // Account.balance
        $cellsToSum = $this->cellsToSum($this->revenues);
        $this->write('=SUM(' . implode(',', $cellsToSum) . ')', self::$balanceFormat, self::$totalFormat);
        // Account.code
        $this->write('');
        // Account.name
        $this->write('');

        // Apply style
        $range = Coordinate::stringFromColumnIndex($initialColumn) . $this->row . ':' . Coordinate::stringFromColumnIndex($this->column - 1) . $this->row;
        $this->sheet->getStyle($range)->applyFromArray(self::$totalFormat);
    }

    private function applyExtraFormatting(): void
    {
        // Format balance numbers
        foreach ([3, 5, 11, 13] as $colIndex) {
            $range = Coordinate::stringFromColumnIndex($colIndex) . 4 . ':' . Coordinate::stringFromColumnIndex($colIndex) . $this->lastDataRow;
            $this->sheet->getStyle($range)->applyFromArray(self::$balanceFormat);
        }

        // Increase row height since account names can wrap on multiple lines
        for ($r = 4; $r <= $this->lastDataRow; ++$r) {
            $this->sheet->getRowDimension($r)->setRowHeight(30);
        }
    }

    protected function cellsToSum(array $data): array
    {
        $cells = array_reduce($data, function (array $carry, $data) {
            if ($data['depth'] === 1 && isset($data['cell'])) {
                $carry[] = $data['cell'];
            }

            return $carry;
        }, []);

        return $cells;
    }

    private function sumBalance(array $data): Money
    {
        $sum = array_reduce($data, function (Money $carry, $data) {
            if ($data['depth'] === 1) {
                return $carry->add($data['balance']);
            }

            return $carry;
        }, Money::CHF(0));

        return $sum;
    }
}