AccountingReport::writeSum() - Code Metrics - Inspection of "Support exports without any rows to sum #8637" - Ecodev/my-ichtus - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 367538...e6ff4f )

by Adrien

created 2022-01-18 10:32 UTC

AccountingReport::writeSum() A

↳ Parent: AccountingReport

Complexity

Conditions	2
Paths	2

Size

Total Lines	5
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	2

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	3
c	1
b	0
f	0
dl	0
loc	5
ccs	4
cts	4
cp	1
rs	10
cc	2
nc	2
nop	1
crap	2

<?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(
            'Bilan',
            self::$titleFormat,
            self::$centerFormat
        );
        $this->column = 9;
        $this->sheet->mergeCellsByColumnAndRow($this->column, $this->row, $this->column + 6, $this->row);
        $this->write(
            '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'] && $account->getCode() !== $this->accountingConfig['customerDepositsAccountCode']) {
                $children = $account->getChildren()->toArray();
                $this->processAccounts($children, $depth + 1);
            }
        }
    }

    /**
     * @param Account[] $items
     */
    protected function writeData(array $items): void
    {
        $this->processAccounts($items, 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
        $this->writeSum($this->assets);

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

        // Liabilities
        // Account.balance
        $this->writeSum($this->liabilities);

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

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

        // INCOME STATEMENT

        // Expenses
        // Account.code
        $this->write('');
        // Account.name
        $this->write('');
        // Account.balance
        $this->writeSum($this->expenses);

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

        // Revenues
        // Account.balance
        $this->writeSum($this->revenues);
        // 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);
        }
    }

    private function writeSum(array $data): void
    {
        $cellsToSum = $this->cellsToSum($data);
        $sum = $cellsToSum ? '=SUM(' . implode(',', $cellsToSum) . ')' : '';
        $this->write($sum, self::$balanceFormat, self::$totalFormat);
    }

    private function cellsToSum(array $data): array
    {
        $cells = array_reduce($data, function (array $carry, $data) {
            if (isset($data['cell']) && ($data['depth'] === 1 || (int) mb_substr((string) $data['code'], 0, 1) > 6)) {
                $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 || (int) mb_substr((string) $data['code'], 0, 1) > 6) {
                return $carry->add($data['balance']);
            }

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

        return $sum;
    }
}


1		<?php
2
3		declare(strict_types=1);
4
5		namespace Application\Service\Exporter;
6
7		use Application\DBAL\Types\AccountTypeType;
8		use Application\Model\Account;
9		use Cake\Chronos\Date;
10		use Ecodev\Felix\Format;
11		use Money\Money;
12		use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
13		use PhpOffice\PhpSpreadsheet\Style\Alignment;
14		use PhpOffice\PhpSpreadsheet\Style\Color;
15		use PhpOffice\PhpSpreadsheet\Style\Fill;
16		use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
17
18		class AccountingReport extends AbstractExcel
19		{
20		private array $accountingConfig;
21
22		private Date $date;
23
24		private array $assets = [];
25
26		private array $liabilities = [];
27
28		private array $expenses = [];
29
30		private array $revenues = [];
31
32		private static array $balanceFormat = [
33		'fill' => [
34		'fillType' => Fill::FILL_SOLID,
35		'startColor' => [
36		'argb' => 'FFDDDDDD',
37		],
38		],
39		'numberFormat' => [
40		'formatCode' => NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1, // eg. 12'345.67
41		],
42		];
43
44		private static array $columnWidth = [
45		'accountCode' => 11,
46		'accountName' => 35,
47		'balance' => 12,
48		];
49
50	1	public function __construct(string $hostname, array $accountingConfig)
51		{
52	1	parent::__construct($hostname);
53
54	1	$this->date = Date::today();
55	1	$this->accountingConfig = $accountingConfig;
56
57	1	$this->sheet->setTitle('Bilan + PP');
58	1	$this->zebra = false;
59	1	$this->autoFilter = false;
60	1	}
61
62	1	protected function getTitleForFilename(): string
63		{
64	1	return sprintf('compta_rapport_%s', $this->date->format('Y-m-d'));
65		}
66
67	1	public function setDate(Date $date): void
68		{
69	1	$this->date = $date;
70	1	}
71
72	1	protected function writeTitle(): void
73		{
74	1	$this->column = 1;
75	1	$this->sheet->mergeCellsByColumnAndRow($this->column, $this->row, $this->column + 14, $this->row);
76	1	$this->write(
77	1	sprintf($this->hostname . ': rapport comptable au %s', $this->date->format('d.m.Y')),
78	1	self::$titleFormat,
79	1	self::$centerFormat
80		);
81	1	$this->sheet->getRowDimension($this->row)->setRowHeight(35);
82	1	++$this->row;
83
84	1	$this->column = 1;
85	1	$this->sheet->mergeCellsByColumnAndRow($this->column, $this->row, $this->column + 6, $this->row);
86	1	$this->write(
87	1	'Bilan',
88	1	self::$titleFormat,
89	1	self::$centerFormat
90		);
91	1	$this->column = 9;
92	1	$this->sheet->mergeCellsByColumnAndRow($this->column, $this->row, $this->column + 6, $this->row);
93	1	$this->write(
94	1	'Résultat',
95	1	self::$titleFormat,
96	1	self::$centerFormat
97		);
98
99	1	$this->sheet->getRowDimension($this->row)->setRowHeight(35);
100	1	++$this->row;
101	1	}
102
103	1	private function processAccounts(array $accounts, int $depth): void
104		{
105	1	foreach ($accounts as $account) {
106	1	$balance = $account->getBalanceAtDate($this->date);
107	1	if ($this->accountingConfig['report']['showAccountsWithZeroBalance'] === false && $depth > 1 && $balance->isZero()) {
108		continue;
109		}
110	1	if ($account->getType() === AccountTypeType::EQUITY) {
111		// Don't show special accounts since it's an interim statement, their balance will be computed manually
112	1	continue;
113		}
114		$data = [
115	1	'code' => $account->getCode(),
116	1	'name' => Format::truncate($account->getName(), 55),
117	1	'depth' => $depth,
118	1	'balance' => $balance,
119	1	'account' => $account,
120		];
121	1	if ($account->getType() === AccountTypeType::ASSET \|\| ($account->getType() === AccountTypeType::GROUP && mb_substr((string) $account->getCode(), 0, 1) === '1')) {
122	1	$this->assets[] = $data;
123	1	} elseif ($account->getType() === AccountTypeType::LIABILITY \|\| ($account->getType() === AccountTypeType::GROUP && mb_substr((string) $account->getCode(), 0, 1) === '2')) {
124	1	$this->liabilities[] = $data;
125	1	} elseif ($account->getType() === AccountTypeType::REVENUE \|\| ($account->getType() === AccountTypeType::GROUP && mb_substr((string) $account->getCode(), 0, 1) === '3')) {
126	1	$this->revenues[] = $data;
127	1	} elseif ($account->getType() === AccountTypeType::EXPENSE \|\| ($account->getType() === AccountTypeType::GROUP && in_array(mb_substr((string) $account->getCode(), 0, 1), ['4', '5', '6'], true))) {
128	1	$this->expenses[] = $data;
129		}
130	1	if ($account->getType() === AccountTypeType::GROUP && $depth <= $this->accountingConfig['report']['maxAccountDepth'] && $account->getCode() !== $this->accountingConfig['customerDepositsAccountCode']) {
131	1	$children = $account->getChildren()->toArray();
132	1	$this->processAccounts($children, $depth + 1);
133		}
134		}
135	1	}
136
137		/**
138		* @param Account[] $items
139		*/
140	1	protected function writeData(array $items): void
141		{
142	1	$this->processAccounts($items, 1);
143
144	1	$this->sheet->setShowGridlines(false);
145
146	1	$profitOrLoss = $this->getProfitOrLoss();
147
148	1	if ($profitOrLoss->isNegative()) {
149		// A loss is written at the end of the Revenues and Assets columns
150		$data = [
151		'code' => '',
152		'name' => 'Résultat intermédiaire (perte)',
153		'depth' => 1,
154		'balance' => $profitOrLoss->absolute(),
155		'account' => null,
156		'format' => ['font' => ['color' => ['argb' => Color::COLOR_RED]]],
157		];
158		$this->revenues[] = $data;
159		$this->assets[] = $data;
160		} else {
161		// A profit is written at the end of the Expenses and Liabilities columns
162		$data = [
163	1	'code' => '',
164	1	'name' => 'Résultat intermédiaire (bénéfice)',
165	1	'depth' => 1,
166	1	'balance' => $profitOrLoss,
167		'account' => null,
168	1	'format' => ['font' => ['color' => ['argb' => Color::COLOR_DARKGREEN]]],
169		];
170	1	$this->expenses[] = $data;
171	1	$this->liabilities[] = $data;
172		}
173
174		// Assets
175	1	$this->column = $initialColumn = 1;
176	1	$initialRow = $this->row;
177	1	$firstLine = true;
178	1	$this->lastDataRow = $this->row;
179	1	foreach ($this->assets as $index => $data) {
180	1	if ($firstLine) {
181	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountCode']);
182		}
183	1	$format = ['font' => ['bold' => $data['depth'] <= 2]];
184	1	$this->write(
185	1	str_repeat(' ', $data['depth'] - 1) . $data['code'],
186	1	['alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'indent' => 1]],
187		$format
188		);
189	1	if ($firstLine) {
190	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountName']);
191		}
192	1	$this->write($data['name'], ['alignment' => ['wrapText' => true]], $format, $data['format'] ?? []);
193	1	if ($firstLine) {
194	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['balance']);
195		}
196		// Store the coordinate of the cell to later compute totals
197	1	$this->assets[$index]['cell'] = $this->sheet->getCellByColumnAndRow($this->column, $this->row)->getCoordinate();
198	1	$this->write($data['balance'], self::$balanceFormat);
199
200	1	$firstLine = false;
201	1	++$this->row;
202	1	$this->column = $initialColumn;
203
204	1	$this->lastDataRow = max($this->lastDataRow, $this->row);
205		}
206
207		// Liabilities
208	1	$this->row = $initialRow;
209	1	$this->column = $initialColumn = $initialColumn + 4;
210	1	$firstLine = true;
211	1	foreach ($this->liabilities as $index => $data) {
212	1	if ($firstLine) {
213	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['balance']);
214		}
215		// Store the coordinate of the cell to later compute totals
216	1	$this->liabilities[$index]['cell'] = $this->sheet->getCellByColumnAndRow($this->column, $this->row)->getCoordinate();
217	1	$this->write($data['balance'], self::$balanceFormat);
218	1	$format = ['font' => ['bold' => $data['depth'] <= 2]];
219	1	if ($firstLine) {
220	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountCode']);
221		}
222	1	$this->write(
223	1	str_repeat(' ', $data['depth'] - 1) . $data['code'],
224	1	['alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'indent' => 1]],
225		$format
226		);
227	1	if ($firstLine) {
228	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountName']);
229		}
230	1	$this->write($data['name'], ['alignment' => ['wrapText' => true]], $format, $data['format'] ?? []);
231	1	$firstLine = false;
232	1	++$this->row;
233	1	$this->column = $initialColumn;
234
235	1	$this->lastDataRow = max($this->lastDataRow, $this->row);
236		}
237
238		// Expenses
239	1	$this->row = $initialRow;
240	1	$this->column = $initialColumn = $initialColumn + 4;
241	1	$firstLine = true;
242	1	foreach ($this->expenses as $index => $data) {
243	1	if ($firstLine) {
244	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountCode']);
245		}
246	1	$format = ['font' => ['bold' => $data['depth'] === 1]];
247	1	$this->write(
248	1	str_repeat(' ', $data['depth'] - 1) . $data['code'],
249	1	['alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'indent' => 1]],
250		$format
251		);
252	1	if ($firstLine) {
253	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountName']);
254		}
255	1	$this->write($data['name'], ['alignment' => ['wrapText' => true]], $format, $data['format'] ?? []);
256	1	if ($firstLine) {
257	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['balance']);
258		}
259		// Store the coordinate of the cell to later compute totals
260	1	$this->expenses[$index]['cell'] = $this->sheet->getCellByColumnAndRow($this->column, $this->row)->getCoordinate();
261	1	$this->write($data['balance'], self::$balanceFormat);
262	1	$firstLine = false;
263	1	++$this->row;
264	1	$this->column = $initialColumn;
265
266	1	$this->lastDataRow = max($this->lastDataRow, $this->row);
267		}
268
269		// Revenues
270	1	$this->row = $initialRow;
271	1	$this->column = $initialColumn = $initialColumn + 4;
272	1	$firstLine = true;
273	1	foreach ($this->revenues as $index => $data) {
274	1	if ($firstLine) {
275	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['balance']);
276		}
277		// Store the coordinate of the cell to later compute totals
278	1	$this->revenues[$index]['cell'] = $this->sheet->getCellByColumnAndRow($this->column, $this->row)->getCoordinate();
279	1	$this->write($data['balance'], self::$balanceFormat);
280	1	$format = ['font' => ['bold' => $data['depth'] === 1]];
281	1	if ($firstLine) {
282	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountCode']);
283		}
284	1	$this->write(
285	1	str_repeat(' ', $data['depth'] - 1) . $data['code'],
286	1	['alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'indent' => 1]],
287		$format
288		);
289	1	if ($firstLine) {
290	1	$this->sheet->getColumnDimensionByColumn($this->column)->setWidth(self::$columnWidth['accountName']);
291		}
292	1	$this->write($data['name'], ['alignment' => ['wrapText' => true]], $format, $data['format'] ?? []);
293	1	$firstLine = false;
294	1	++$this->row;
295	1	$this->column = $initialColumn;
296
297	1	$this->lastDataRow = max($this->lastDataRow, $this->row);
298		}
299
300	1	$this->applyExtraFormatting();
301	1	}
302
303	1	private function getProfitOrLoss(): Money
304		{
305		// Sum the profit and loss root accounts
306	1	$totalRevenues = $this->sumBalance($this->revenues);
307
308	1	$totalExpenses = $this->sumBalance($this->expenses);
309
310	1	return $totalRevenues->subtract($totalExpenses);
311		}
312
313	1	protected function getHeaders(): array
314		{
315	1	$headers = [];
316
317	1	$headers[] = ['label' => 'Actifs', 'formats' => [self::$headerFormat, self::$centerFormat], 'colspan' => 3];
318	1	$headers[] = ['label' => '', 'width' => 3, 'formats' => []]; // margin
319	1	$headers[] = ['label' => 'Passifs', 'formats' => [self::$headerFormat, self::$centerFormat], 'colspan' => 3];
320
321	1	$headers[] = ['label' => '', 'width' => 5, 'formats' => []]; // margin
322
323	1	$headers[] = ['label' => 'Charges', 'formats' => [self::$headerFormat, self::$centerFormat], 'colspan' => 3];
324	1	$headers[] = ['label' => '', 'width' => 3, 'formats' => []]; // margin
325	1	$headers[] = ['label' => 'Profits', 'formats' => [self::$headerFormat, self::$centerFormat], 'colspan' => 3];
326
327	1	return $headers;
328		}
329
330	1	protected function writeFooter(): void
331		{
332	1	$initialColumn = $this->column;
333
334		// BALANCE SHEET
335
336		// Assets
337		// Account.code
338	1	$this->write('');
339		// Account.name
340	1	$this->write('');
341		// Account.balance
342	1	$this->writeSum($this->assets);
343
344		// Margin
345	1	$this->write('');
346
347		// Liabilities
348		// Account.balance
349	1	$this->writeSum($this->liabilities);
350
351		// Account.code
352	1	$this->write('');
353		// Account.name
354	1	$this->write('');
355
356		// Margin
357	1	$this->write('');
358
359		// INCOME STATEMENT
360
361		// Expenses
362		// Account.code
363	1	$this->write('');
364		// Account.name
365	1	$this->write('');
366		// Account.balance
367	1	$this->writeSum($this->expenses);
368
369		// Margin
370	1	$this->write('');
371
372		// Revenues
373		// Account.balance
374	1	$this->writeSum($this->revenues);
375		// Account.code
376	1	$this->write('');
377		// Account.name
378	1	$this->write('');
379
380		// Apply style
381	1	$range = Coordinate::stringFromColumnIndex($initialColumn) . $this->row . ':' . Coordinate::stringFromColumnIndex($this->column - 1) . $this->row;
382	1	$this->sheet->getStyle($range)->applyFromArray(self::$totalFormat);
383	1	}
384
385	1	private function applyExtraFormatting(): void
386		{
387		// Format balance numbers
388	1	foreach ([3, 5, 11, 13] as $colIndex) {
389	1	$range = Coordinate::stringFromColumnIndex($colIndex) . 4 . ':' . Coordinate::stringFromColumnIndex($colIndex) . $this->lastDataRow;
390	1	$this->sheet->getStyle($range)->applyFromArray(self::$balanceFormat);
391		}
392
393		// Increase row height since account names can wrap on multiple lines
394	1	for ($r = 4; $r <= $this->lastDataRow; ++$r) {
395	1	$this->sheet->getRowDimension($r)->setRowHeight(30);
396		}
397	1	}
398
399	1	private function writeSum(array $data): void
400		{
401	1	$cellsToSum = $this->cellsToSum($data);
402	1	$sum = $cellsToSum ? '=SUM(' . implode(',', $cellsToSum) . ')' : '';
403	1	$this->write($sum, self::$balanceFormat, self::$totalFormat);
404	1	}
405
406	1	private function cellsToSum(array $data): array
407		{
408	1	$cells = array_reduce($data, function (array $carry, $data) {
409	1	if (isset($data['cell']) && ($data['depth'] === 1 \|\| (int) mb_substr((string) $data['code'], 0, 1) > 6)) {
410	1	$carry[] = $data['cell'];
411		}
412
413	1	return $carry;
414	1	}, []);
415
416	1	return $cells;
417		}
418
419	1	private function sumBalance(array $data): Money
420		{
421	1	$sum = array_reduce($data, function (Money $carry, $data) {
422	1	if ($data['depth'] === 1 \|\| (int) mb_substr((string) $data['code'], 0, 1) > 6) {
423	1	return $carry->add($data['balance']);
424		}
425
426	1	return $carry;
427	1	}, Money::CHF(0));
428
429	1	return $sum;
430		}
431		}
432

Ecodev / my-ichtus

Push — master ( 367538...e6ff4f )

AccountingReport::writeSum() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like