TransactionRepository::getSqlToComputeBalance() - Code Metrics - Inspection of "Don't push "Transaction Datatrans" down when remar..." - Ecodev/my-ichtus - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( a056d8...a2923e )

by Adrien

created 2025-03-03 08:08 UTC

TransactionRepository::getSqlToComputeBalance() A

↳ Parent: TransactionRepository

Complexity

Conditions	3
Paths	4

Size

Total Lines	15
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	9
CRAP Score	3

Importance

Changes

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

<?php

declare(strict_types=1);

namespace Application\Repository;

use Application\Api\Helper;
use Application\Model\Account;
use Application\Model\Transaction;
use Application\Model\TransactionLine;
use Application\Model\User;
use Ecodev\Felix\Api\Exception;
use Ecodev\Felix\Repository\LimitedAccessSubQuery;
use Ecodev\Felix\Utility;
use LogicException;

/**
 * @extends AbstractRepository<Transaction>
 */
class TransactionRepository extends AbstractRepository implements LimitedAccessSubQuery
{
    /**
     * Returns pure SQL to get ID of all objects that are accessible to given user.
     *
     * @param null|User $user
     */
    public function getAccessibleSubQuery(?\Ecodev\Felix\Model\User $user): string
    {
        if (!$user) {
            return '-1';
        }

        if (in_array($user->getRole(), [User::ROLE_ACCOUNTING_VERIFICATOR, User::ROLE_RESPONSIBLE, User::ROLE_ADMINISTRATOR], true)) {
            return '';
        }

        if ($user->getOwner()) {
            $id = $user->getOwner()->getId();
        } else {
            $id = $user->getId();
        }

        return 'SELECT transaction.id FROM transaction
              JOIN transaction_line ON transaction.id = transaction_line.transaction_id
              JOIN account ON transaction_line.debit_id = account.id OR transaction_line.credit_id = account.id 
              WHERE account.owner_id = ' . $id;
    }

    public function hydrateLinesAndFlush(Transaction $transaction, array $lines): void
    {
        if (!$lines) {
            throw new Exception('A Transaction must have at least one TransactionLine');
        }

        // Destroy all previously existing TransactionLine
        foreach ($transaction->getTransactionLines() as $line) {
            $this->getEntityManager()->remove($line);
        }
        $transaction->getTransactionLines()->clear();

        $accounts = [];
        foreach ($lines as $line) {
            $transactionLine = new TransactionLine();
            Helper::hydrate($transactionLine, $line);
            if (!$transactionLine->getCredit() && !$transactionLine->getDebit()) {
                throw new Exception('Cannot create a TransactionLine without any account');
            }
            $accounts[] = $transactionLine->getCredit();
            $accounts[] = $transactionLine->getDebit();

            $transactionLine->setTransaction($transaction);
            $transactionLine->setTransactionDate($transaction->getTransactionDate());
            $this->getEntityManager()->persist($transactionLine);
        }

        $this->getEntityManager()->persist($transaction);
        $this->flushWithFastTransactionLineTriggers();

        // Be sure to refresh the new account balance that were computed by DB triggers
        $accounts = array_filter(Utility::unique($accounts));
        foreach ($accounts as $account) {
            $this->getEntityManager()->refresh($account);
        }
    }

    /**
     * This is a replacement for `_em()->flush();` for when you are flushing a lot of transaction lines.
     *
     * It does the exact same thing as `_em()->flush();`, except it will disable transaction line
     * triggers temporarily and execute the de-duplicated stocked procedures at the very end. So we
     * avoid re-computing the same thing over and over.
     */
    public function flushWithFastTransactionLineTriggers(): void
    {
        $transactions = [];
        $accounts = [];

        $unitOfWork = $this->getEntityManager()->getUnitOfWork();
        $unitOfWork->computeChangeSets();

        $inserted = $unitOfWork->getScheduledEntityInsertions();
        $updated = $unitOfWork->getScheduledEntityUpdates();
        $deleted = $unitOfWork->getScheduledEntityDeletions();

        // Remember the IDs before deleting them
        foreach ($deleted as $object) {
            if ($object instanceof TransactionLine) {
                $this->gatherTransactionLine($transactions, $accounts, $object);
            } elseif ($object instanceof Transaction) {
                $this->gatherDeletedTransaction($accounts, $object);
            } else {
                $this->throwNotAllowed($object);
            }
        }

        $this->getEntityManager()->getConnection()->executeStatement('SET @disable_triggers_for_mass_transaction_line = true;');
        $this->getEntityManager()->flush();

        // Get the (possibly new) IDs possibly affected by this flush
        foreach ([...$inserted, ...$updated] as $object) {
            if ($object instanceof TransactionLine) {
                $this->gatherTransactionLine($transactions, $accounts, $object);
            } elseif (!in_array(get_class($object), [Transaction::class, Account::class], true)) {
                $this->throwNotAllowed($object);
            }
        }

        // Keep everything in a single string to save very precious time in a single DB round trip
        $sql = $this->getSqlToComputeBalance($transactions, $accounts)
            . 'SET @disable_triggers_for_mass_transaction_line = false;';

        // Compute balance for all objects that may have been affected
        $this->getEntityManager()->getConnection()->executeStatement($sql);
    }

    /**
     * @param list<int> $transactions
     * @param list<int> $accounts
     */
    private function gatherTransactionLine(array &$transactions, array &$accounts, TransactionLine $object): void
    {
        $transactions[] = $object->getTransaction()->getId();
        $accounts[] = $object->getDebit()?->getId();
        $accounts[] = $object->getCredit()?->getId();
    }

    /**
     * @param list<int> $accounts
     */
    private function gatherDeletedTransaction(array &$accounts, Transaction $object): void
    {
        $accountIds = $this->getEntityManager()->getConnection()->fetchFirstColumn(
            <<<SQL
                SELECT debit_id FROM transaction_line WHERE transaction_id = :transaction AND debit_id IS NOT NULL 
                UNION
                SELECT credit_id FROM transaction_line WHERE transaction_id = :transaction AND credit_id IS NOT NULL
                SQL,
            [
                'transaction' => $object->getId(),
            ],
        );

        array_push($accounts, ...$accountIds);
    }

    private function throwNotAllowed(object $object): never
    {
        // If you read this code because you saw this exception thrown in production,
        // then you must review which object was trying to be inserted/updated/deleted.
        // Then, super-triple-check that that object does not have triggers, or other
        // mechanisms, that would be broken/not ran by this method. If you are
        // really-really-really sure that the DB content stays consistent, then you can
        // allowlist the object here.
        throw new LogicException('flushWithFastTransactionLineTriggers() must not be used with ' . get_class($object));
    }

    /**
     * @param list<int> $transactions
     * @param list<int> $accounts
     */
    private function getSqlToComputeBalance(array $transactions, array $accounts): string
    {
        $sql = '';

        $transactions = array_filter(array_unique($transactions));
        foreach ($transactions as $transaction) {
            $sql .= "CALL update_transaction_balance($transaction);" . PHP_EOL;
        }

        $accounts = array_filter(array_unique($accounts));
        foreach ($accounts as $account) {
            $sql .= "CALL update_account_balance($account);" . PHP_EOL;
        }

        return $sql;
    }
}


1		<?php
2
3		declare(strict_types=1);
4
5		namespace Application\Repository;
6
7		use Application\Api\Helper;
8		use Application\Model\Account;
9		use Application\Model\Transaction;
10		use Application\Model\TransactionLine;
11		use Application\Model\User;
12		use Ecodev\Felix\Api\Exception;
13		use Ecodev\Felix\Repository\LimitedAccessSubQuery;
14		use Ecodev\Felix\Utility;
15		use LogicException;
16
17		/**
18		* @extends AbstractRepository<Transaction>
19		*/
20		class TransactionRepository extends AbstractRepository implements LimitedAccessSubQuery
21		{
22		/**
23		* Returns pure SQL to get ID of all objects that are accessible to given user.
24		*
25		* @param null\|User $user
26		*/
27	24	public function getAccessibleSubQuery(?\Ecodev\Felix\Model\User $user): string
28		{
29	24	if (!$user) {
30	2	return '-1';
31		}
32
33	22	if (in_array($user->getRole(), [User::ROLE_ACCOUNTING_VERIFICATOR, User::ROLE_RESPONSIBLE, User::ROLE_ADMINISTRATOR], true)) {
34	14	return '';
35		}
36
37	8	if ($user->getOwner()) {
38	6	$id = $user->getOwner()->getId();
39		} else {
40	2	$id = $user->getId();
41		}
42
43	8	return 'SELECT transaction.id FROM transaction
44		JOIN transaction_line ON transaction.id = transaction_line.transaction_id
45		JOIN account ON transaction_line.debit_id = account.id OR transaction_line.credit_id = account.id
46	8	WHERE account.owner_id = ' . $id;
47		}
48
49	8	public function hydrateLinesAndFlush(Transaction $transaction, array $lines): void
50		{
51	8	if (!$lines) {
52	1	throw new Exception('A Transaction must have at least one TransactionLine');
53		}
54
55		// Destroy all previously existing TransactionLine
56	7	foreach ($transaction->getTransactionLines() as $line) {
57	3	$this->getEntityManager()->remove($line);
58		}
59	7	$transaction->getTransactionLines()->clear();
60
61	7	$accounts = [];
62	7	foreach ($lines as $line) {
63	7	$transactionLine = new TransactionLine();
64	7	Helper::hydrate($transactionLine, $line);
65	7	if (!$transactionLine->getCredit() && !$transactionLine->getDebit()) {
66	2	throw new Exception('Cannot create a TransactionLine without any account');
67		}
68	5	$accounts[] = $transactionLine->getCredit();
69	5	$accounts[] = $transactionLine->getDebit();
70
71	5	$transactionLine->setTransaction($transaction);
72	5	$transactionLine->setTransactionDate($transaction->getTransactionDate());
73	5	$this->getEntityManager()->persist($transactionLine);
74		}
75
76	5	$this->getEntityManager()->persist($transaction);
77	5	$this->flushWithFastTransactionLineTriggers();
78
79		// Be sure to refresh the new account balance that were computed by DB triggers
80	4	$accounts = array_filter(Utility::unique($accounts));
81	4	foreach ($accounts as $account) {
82	4	$this->getEntityManager()->refresh($account);
83		}
84		}
85
86		/**
87		* This is a replacement for `_em()->flush();` for when you are flushing a lot of transaction lines.
88		*
89		* It does the exact same thing as `_em()->flush();`, except it will disable transaction line
90		* triggers temporarily and execute the de-duplicated stocked procedures at the very end. So we
91		* avoid re-computing the same thing over and over.
92		*/
93	8	public function flushWithFastTransactionLineTriggers(): void
94		{
95	8	$transactions = [];
96	8	$accounts = [];
97
98	8	$unitOfWork = $this->getEntityManager()->getUnitOfWork();
99	8	$unitOfWork->computeChangeSets();
100
101	8	$inserted = $unitOfWork->getScheduledEntityInsertions();
102	8	$updated = $unitOfWork->getScheduledEntityUpdates();
103	8	$deleted = $unitOfWork->getScheduledEntityDeletions();
104
105		// Remember the IDs before deleting them
106	8	foreach ($deleted as $object) {
107	3	if ($object instanceof TransactionLine) {
108	2	$this->gatherTransactionLine($transactions, $accounts, $object);
109	1	} elseif ($object instanceof Transaction) {
110	1	$this->gatherDeletedTransaction($accounts, $object);
111		} else {
112		$this->throwNotAllowed($object);
113		}
114		}
115
116	8	$this->getEntityManager()->getConnection()->executeStatement('SET @disable_triggers_for_mass_transaction_line = true;');
117	8	$this->getEntityManager()->flush();
118
119		// Get the (possibly new) IDs possibly affected by this flush
120	7	foreach ([...$inserted, ...$updated] as $object) {
121	6	if ($object instanceof TransactionLine) {
122	6	$this->gatherTransactionLine($transactions, $accounts, $object);
123	5	} elseif (!in_array(get_class($object), [Transaction::class, Account::class], true)) {
124		$this->throwNotAllowed($object);
125		}
126		}
127
128		// Keep everything in a single string to save very precious time in a single DB round trip
129	7	$sql = $this->getSqlToComputeBalance($transactions, $accounts)
130	7	. 'SET @disable_triggers_for_mass_transaction_line = false;';
131
132		// Compute balance for all objects that may have been affected
133	7	$this->getEntityManager()->getConnection()->executeStatement($sql);
134		}
135
136		/**
137		* @param list<int> $transactions
138		* @param list<int> $accounts
139		*/
140	6	private function gatherTransactionLine(array &$transactions, array &$accounts, TransactionLine $object): void
141		{
142	6	$transactions[] = $object->getTransaction()->getId();
143	6	$accounts[] = $object->getDebit()?->getId();
144	6	$accounts[] = $object->getCredit()?->getId();
145		}
146
147		/**
148		* @param list<int> $accounts
149		*/
150	1	private function gatherDeletedTransaction(array &$accounts, Transaction $object): void
151		{
152	1	$accountIds = $this->getEntityManager()->getConnection()->fetchFirstColumn(
153	1	<<<SQL
154		SELECT debit_id FROM transaction_line WHERE transaction_id = :transaction AND debit_id IS NOT NULL
155		UNION
156		SELECT credit_id FROM transaction_line WHERE transaction_id = :transaction AND credit_id IS NOT NULL
157	1	SQL,
158	1	[
159	1	'transaction' => $object->getId(),
160	1	],
161	1	);
162
163	1	array_push($accounts, ...$accountIds);
164		}
165
166		private function throwNotAllowed(object $object): never
167		{
168		// If you read this code because you saw this exception thrown in production,
169		// then you must review which object was trying to be inserted/updated/deleted.
170		// Then, super-triple-check that that object does not have triggers, or other
171		// mechanisms, that would be broken/not ran by this method. If you are
172		// really-really-really sure that the DB content stays consistent, then you can
173		// allowlist the object here.
174		throw new LogicException('flushWithFastTransactionLineTriggers() must not be used with ' . get_class($object));
175		}
176
177		/**
178		* @param list<int> $transactions
179		* @param list<int> $accounts
180		*/
181	7	private function getSqlToComputeBalance(array $transactions, array $accounts): string
182		{
183	7	$sql = '';
184
185	7	$transactions = array_filter(array_unique($transactions));
186	7	foreach ($transactions as $transaction) {
187	6	$sql .= "CALL update_transaction_balance($transaction);" . PHP_EOL;
188		}
189
190	7	$accounts = array_filter(array_unique($accounts));
191	7	foreach ($accounts as $account) {
192	7	$sql .= "CALL update_account_balance($account);" . PHP_EOL;
193		}
194
195	7	return $sql;
196		}
197		}
198

Ecodev / my-ichtus

Push — master ( a056d8...a2923e )

TransactionRepository::getSqlToComputeBalance() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like