|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace Application\Repository; |
|
6
|
|
|
|
|
7
|
|
|
use Application\Handler\ExportTransactionLinesHandler; |
|
8
|
|
|
use Application\Model\Account; |
|
9
|
|
|
use Application\Model\User; |
|
10
|
|
|
use Cake\Chronos\Date; |
|
11
|
|
|
use Doctrine\ORM\Query; |
|
12
|
|
|
use Ecodev\Felix\Api\Exception; |
|
13
|
|
|
use Ecodev\Felix\Repository\LimitedAccessSubQuery; |
|
14
|
|
|
use Money\Money; |
|
15
|
|
|
|
|
16
|
|
|
class TransactionLineRepository extends AbstractRepository implements ExportExcelInterface, LimitedAccessSubQuery |
|
17
|
|
|
{ |
|
18
|
|
|
/** |
|
19
|
|
|
* Returns pure SQL to get ID of all objects that are accessible to given user. |
|
20
|
|
|
* |
|
21
|
|
|
* @param null|User $user |
|
22
|
|
|
*/ |
|
23
|
14 |
|
public function getAccessibleSubQuery(?\Ecodev\Felix\Model\User $user): string |
|
24
|
|
|
{ |
|
25
|
14 |
|
if (!$user) { |
|
26
|
2 |
|
return '-1'; |
|
27
|
|
|
} |
|
28
|
|
|
|
|
29
|
12 |
|
if (in_array($user->getRole(), [User::ROLE_RESPONSIBLE, User::ROLE_ADMINISTRATOR], true)) { |
|
30
|
9 |
|
return $this->getAllIdsQuery(); |
|
31
|
|
|
} |
|
32
|
|
|
|
|
33
|
3 |
|
if ($user->getOwner()) { |
|
34
|
1 |
|
$id = $user->getOwner()->getId(); |
|
35
|
|
|
} else { |
|
36
|
2 |
|
$id = $user->getId(); |
|
37
|
|
|
} |
|
38
|
|
|
|
|
39
|
|
|
return 'SELECT transaction_line.id FROM transaction_line |
|
40
|
|
|
JOIN account ON transaction_line.debit_id = account.id OR transaction_line.credit_id = account.id |
|
41
|
3 |
|
WHERE account.owner_id = ' . $id; |
|
42
|
|
|
} |
|
43
|
|
|
|
|
44
|
|
|
/** |
|
45
|
|
|
* Generates an Excel spreadsheet with the query result |
|
46
|
|
|
* |
|
47
|
|
|
* @return string name of the temporary file |
|
48
|
|
|
*/ |
|
49
|
|
|
public function exportExcel(Query $query): string |
|
50
|
|
|
{ |
|
51
|
|
|
global $container; |
|
52
|
|
|
|
|
53
|
|
|
return ($container->get(ExportTransactionLinesHandler::class))->generate($query); |
|
54
|
|
|
} |
|
55
|
|
|
|
|
56
|
|
|
/** |
|
57
|
|
|
* Compute the total balance by credit or debit account and date range |
|
58
|
|
|
* |
|
59
|
|
|
* @param null|Account $debitAccount |
|
60
|
|
|
* @param null|Account $creditAccount |
|
61
|
|
|
* @param null|Date $dateStart the lines from this date, included |
|
62
|
|
|
* @param null|Date $dateEnd the line until this date, included |
|
63
|
|
|
* |
|
64
|
|
|
* @return Money |
|
65
|
|
|
*/ |
|
66
|
3 |
|
public function totalBalance($debitAccount, $creditAccount, $dateStart = null, $dateEnd = null) |
|
67
|
|
|
{ |
|
68
|
3 |
|
if ($debitAccount === null && $creditAccount === null) { |
|
69
|
|
|
throw new Exception('At least one debit or credit account is needed to compute the total balance'); |
|
70
|
|
|
} |
|
71
|
|
|
|
|
72
|
3 |
|
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder() |
|
73
|
3 |
|
->select('SUM(balance)') |
|
74
|
3 |
|
->from('transaction_line'); |
|
75
|
|
|
|
|
76
|
3 |
|
if ($debitAccount) { |
|
77
|
3 |
|
$qb->andWhere('debit_id = :debit') |
|
78
|
3 |
|
->setParameter('debit', $debitAccount->getId()); |
|
79
|
|
|
} |
|
80
|
|
|
|
|
81
|
3 |
|
if ($creditAccount) { |
|
82
|
3 |
|
$qb->andWhere('credit_id = :credit') |
|
83
|
3 |
|
->setParameter('credit', $creditAccount->getId()); |
|
84
|
|
|
} |
|
85
|
|
|
|
|
86
|
3 |
|
if ($dateStart) { |
|
87
|
1 |
|
$qb->andWhere('transaction_date >= :dateStart') |
|
88
|
1 |
|
->setParameter('dateStart', $dateStart); |
|
89
|
|
|
} |
|
90
|
|
|
|
|
91
|
3 |
|
if ($dateEnd) { |
|
92
|
3 |
|
$qb->andWhere('transaction_date <= :dateEnd') |
|
93
|
3 |
|
->setParameter('dateEnd', $dateEnd); |
|
94
|
|
|
} |
|
95
|
|
|
|
|
96
|
3 |
|
$result = $qb->execute(); |
|
97
|
|
|
|
|
98
|
3 |
|
return Money::CHF((int) $result->fetchColumn()); |
|
|
|
|
|
|
99
|
|
|
} |
|
100
|
|
|
|
|
101
|
4 |
|
public function importedIdExists(string $importedId): bool |
|
102
|
|
|
{ |
|
103
|
4 |
|
$connection = $this->getEntityManager()->getConnection(); |
|
104
|
4 |
|
$count = $connection->fetchOne('SELECT COUNT(*) > 0 FROM transaction_line WHERE imported_id = :importedId', ['importedId' => $importedId]); |
|
105
|
|
|
|
|
106
|
4 |
|
return (bool) $count; |
|
107
|
|
|
} |
|
108
|
|
|
} |
|
109
|
|
|
|
Ecodev /
my-ichtus
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.