ExportAccountingReportHandler::writeTitle() - Code Metrics - Inspection of "Accounting report Excel export #7836" - Ecodev/my-ichtus - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Push — master ( 4b335d...ccf9db )

by Sylvain

created 2021-01-08 18:24 UTC

ExportAccountingReportHandler::writeTitle() A

↳ Parent: ExportAccountingReportHandler

Complexity

Conditions	1
Paths	1

Size

Total Lines	26
Code Lines	19

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	20
CRAP Score	1

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	19
c	1
b	0
f	0
dl	0
loc	26
ccs	20
cts	20
cp	1
rs	9.6333
cc	1
nc	1
nop	1
crap	1

<?php

declare(strict_types=1);

namespace Application\Handler;

use Application\DBAL\Types\AccountTypeType;
use Application\Model\Account;
use ApplicationTest\Traits\TestWithTransactionAndUser;
use Cake\Chronos\Date;
use Doctrine\ORM\Query;
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;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportAccountingReportHandler extends AbstractExcel
{
    use TestWithTransactionAndUser;

    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,
    ];

    private int $lastDataRow;

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

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

        $sheet = $this->workbook->getActiveSheet();
        $sheet->setTitle('Bilan + PP');
        $sheet->getSheetView()->setZoomScale(70);
        $sheet->getDefaultRowDimension()->setRowHeight(20);
    }

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

    public function getDate(): Date
    {
        return $this->date;
    }

    /**
     * The model class name
     */
    protected function getModelClass(): string
    {
        return Account::class;
    }

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

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

        $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(Worksheet $sheet, array $accounts): void
    {
        $this->processAccounts($accounts, 1);

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

            $firstLine = false;
            ++$this->row;
            $this->column = $initialColumn;
            if ($this->row > $this->lastDataRow) {
                $this->lastDataRow = $this->row;
            }
        }

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

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

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

    protected function getProfitOrLoss(): Money
    {
        // Sum the profit and loss root accounts
        $totalRevenues = array_reduce($this->revenues, function (Money $carry, $data) {
            if ($data['depth'] === 1) {
                return $carry->add($data['balance']);
            }

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

        $totalExpenses = array_reduce($this->expenses, function (Money $carry, $data) {
            if ($data['depth'] === 1) {
                return $carry->add($data['balance']);
            }

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

        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;
    }

    /**
     * @param Account[] $items
     */
    protected function writeFooter(Worksheet $sheet, array $items): void
    {
        $initialColumn = $this->column;

        /** BALANCE SHEET */

        /** Assets */
        // Account.code
        $this->write($sheet, '');
        // Account.name
        $this->write($sheet, '');
        // Account.balance
        $cellsToSum = array_reduce($this->assets, function (array $carry, $data) {
            if ($data['depth'] === 1 && isset($data['cell'])) {
                $carry[] = $data['cell'];
            }

            return $carry;
        }, []);
        $this->write($sheet, '=SUM(' . implode(',', $cellsToSum) . ')', self::$balanceFormat, self::$totalFormat);

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

        /** Liabilities */
        // Account.balance
        $cellsToSum = array_reduce($this->liabilities, function (array $carry, $data) {
            if ($data['depth'] === 1 && isset($data['cell'])) {
                $carry[] = $data['cell'];
            }

            return $carry;
        }, []);
        $this->write($sheet, '=SUM(' . implode(',', $cellsToSum) . ')', self::$balanceFormat, self::$totalFormat);

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

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

        /** INCOME STATEMENT */

        /** Expenses */
        // Account.code
        $this->write($sheet, '');
        // Account.name
        $this->write($sheet, '');
        // Account.balance
        $cellsToSum = array_reduce($this->expenses, function (array $carry, $data) {
            if ($data['depth'] === 1 && isset($data['cell'])) {
                $carry[] = $data['cell'];
            }

            return $carry;
        }, []);
        $this->write($sheet, '=SUM(' . implode(',', $cellsToSum) . ')', self::$balanceFormat, self::$totalFormat);

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

        /** Revenues ** */
        // Account.balance
        $cellsToSum = array_reduce($this->revenues, function (array $carry, $data) {
            if ($data['depth'] === 1 && isset($data['cell'])) {
                $carry[] = $data['cell'];
            }

            return $carry;
        }, []);
        $this->write($sheet, '=SUM(' . implode(',', $cellsToSum) . ')', self::$balanceFormat, self::$totalFormat);
        // Account.code
        $this->write($sheet, '');
        // Account.name
        $this->write($sheet, '');

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

    public function generate(Query $query): string
    {
        $this->outputFileName = sprintf('%s_compta_rapport_%s.xlsx', $this->hostname, $this->date->format('Y-m-d'));
        $accounts = $query->getResult();
        $this->workbook->getDefaultStyle()->applyFromArray(self::$defaultFormat);
        $sheet = $this->workbook->getActiveSheet();
        $this->row = 1;
        $this->column = 1;
        $this->writeTitle($sheet);
        $this->column = 1;
        $this->writeHeaders($sheet, $accounts);
        ++$this->row;
        $this->column = 1;
        $this->writeData($sheet, $accounts);

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

        // Increase row height since account names can wrap on multiple lines
        $rows = $sheet->getRowIterator(4, $this->lastDataRow);

        for ($r = 4; $r <= $this->lastDataRow; ++$r) {
            $sheet->getRowDimension($r)->setRowHeight(30);
        }

        $this->column = 1;
        $this->row = $this->lastDataRow + 1;
        $this->column = 1;
        $this->writeFooter($sheet, $accounts);

        $writer = new Xlsx($this->workbook);

        $tmpFile = bin2hex(random_bytes(16));
        !is_dir($this->tmpDir) && mkdir($this->tmpDir);
        $writer->save($this->tmpDir . '/' . $tmpFile);

        return 'https://' . $this->hostname . '/export/' . $this->routeName . '/' . $tmpFile . '/' . $this->outputFileName;
    }
}


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

Ecodev / my-ichtus

Push — master ( 4b335d...ccf9db )

ExportAccountingReportHandler::writeTitle() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like