Overview
Comment:List pending debts and credits from closed years, and alert about them
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable
Files: files | file ages | folders
SHA3-256: 6566b208d02a987dcdbf28ae5661ecab57f52fb45d29b407bff8022154ab38ad
User & Date: bohwaz on 2023-01-22 13:52:23
Other Links: manifest | tags
Context
2023-01-22
14:11
Check and alert if the same account is listed twice in a transaction check-in: 9f32cf302a user: bohwaz tags: trunk, stable
13:52
List pending debts and credits from closed years, and alert about them check-in: 6566b208d0 user: bohwaz tags: trunk, stable
12:51
Allow read-only users to do SQL queries, extend SQL queries for users to services tables check-in: be29a73150 user: bohwaz tags: trunk, stable
Changes

Modified src/include/lib/Garradin/Accounting/Transactions.php from [ea5b0a2b00] to [f94c739ea4].

95
96
97
98
99
100
101




















































102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
		return DB::getInstance()->count('acc_transactions_users', 'id_user = ?', $user_id);
	}

	static public function countForCreator(int $user_id): int
	{
		return DB::getInstance()->count('acc_transactions', 'id_creator = ?', $user_id);
	}





















































	static public function setProject(?int $id_project, ?array $transactions = null, ?array $lines = null)
	{
		$db = DB::getInstance();

		if (null !== $id_project && !$db->test(Project::TABLE, 'id = ?', $id_project)) {
			throw new \InvalidArgumentException('Invalid project ID');
		}

		if (isset($transactions, $lines) || ($transactions === null && $lines === null)) {
			throw new \BadMethodCallException('Only one of transactions or lines should be set');
		}

		$selection = array_map('intval', $transactions ?? $lines);
		$where = sprintf($transactions ? 'id_transaction IN (%s)' : 'id IN (%s)', implode(', ', $selection));

		return $db->exec(sprintf('UPDATE acc_transactions_lines SET id_project = %s WHERE %s;',
			(int)$id_project ?: 'NULL', $where));
	}

	static public function listByType(int $year_id, ?int $type)
	{
		$reverse = 1;

		$columns = Account::LIST_COLUMNS;
		unset($columns['line_label'], $columns['sum'], $columns['debit'], $columns['credit']);
		$columns['line_reference']['label'] = 'Réf. paiement';
		$columns['change']['select'] = sprintf('SUM(l.credit) * %d', $reverse);







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




















|







95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
		return DB::getInstance()->count('acc_transactions_users', 'id_user = ?', $user_id);
	}

	static public function countForCreator(int $user_id): int
	{
		return DB::getInstance()->count('acc_transactions', 'id_creator = ?', $user_id);
	}

	/**
	 * Returns a dynamic list of all waiting credit and debt transactions for closed years
	 */
	static public function listPendingCreditAndDebtForClosedYears(): DynamicList
	{
		$columns = Account::LIST_COLUMNS;

		unset($columns['line_label'], $columns['sum'], $columns['debit'], $columns['credit']);
		unset($columns['project_code'], $columns['id_project'], $columns['line_reference']);

		$columns['change']['select'] = 'SUM(l.credit)';
		$columns['change']['label'] = 'Montant';

		$columns = [
			'year_label' => [
				'select' => 'y.label',
				'label' => 'Exercice',
			],
			'type_label' => [
				'select' => 't.type',
				'label' => 'Type',
			]]
			+ $columns;

		$conditions = sprintf('y.closed = 1 AND t.status & %d AND t.type IN (%d, %d)',
			Transaction::STATUS_WAITING, Transaction::TYPE_CREDIT, Transaction::TYPE_DEBT);

		$tables = 'acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			INNER JOIN acc_years y ON y.id = t.id_year';

		$list = new DynamicList($columns, $tables, $conditions);
		$list->orderBy('date', true);
		$list->setCount('COUNT(DISTINCT t.id)');
		$list->groupBy('t.id');
		$list->setModifier(function (&$row) {
			$row->date = \DateTime::createFromFormat('!Y-m-d', $row->date);

			if (isset($row->type_label)) {
				$row->type_label = Transaction::TYPES_NAMES[(int)$row->type_label];
			}
		});

		$list->setExportCallback(function (&$row) {
			$row->change = Utils::money_format($row->change, '.', '', false);
		});

		$list->setTitle('Dettes et créances en attente');

		return $list;
	}

	static public function setProject(?int $id_project, ?array $transactions = null, ?array $lines = null)
	{
		$db = DB::getInstance();

		if (null !== $id_project && !$db->test(Project::TABLE, 'id = ?', $id_project)) {
			throw new \InvalidArgumentException('Invalid project ID');
		}

		if (isset($transactions, $lines) || ($transactions === null && $lines === null)) {
			throw new \BadMethodCallException('Only one of transactions or lines should be set');
		}

		$selection = array_map('intval', $transactions ?? $lines);
		$where = sprintf($transactions ? 'id_transaction IN (%s)' : 'id IN (%s)', implode(', ', $selection));

		return $db->exec(sprintf('UPDATE acc_transactions_lines SET id_project = %s WHERE %s;',
			(int)$id_project ?: 'NULL', $where));
	}

	static public function listByType(int $year_id, ?int $type): DynamicList
	{
		$reverse = 1;

		$columns = Account::LIST_COLUMNS;
		unset($columns['line_label'], $columns['sum'], $columns['debit'], $columns['credit']);
		$columns['line_reference']['label'] = 'Réf. paiement';
		$columns['change']['select'] = sprintf('SUM(l.credit) * %d', $reverse);

Modified src/templates/acc/accounts/index.tpl from [c5e32427e2] to [ba4c959060].

9
10
11
12
13
14
15




16
17
18
19
20
21
22


{if isset($_GET['chart_change'])}
<p class="block error">
	L'exercice sélectionné utilise un plan comptable différent, merci de sélectionner un autre compte.
</p>
{/if}





{include file="acc/_simple_help.tpl" link="../reports/trial_balance.php?year=%d"|args:$current_year.id type=null}

{if !empty($grouped_accounts)}
	<table class="list">
		<thead>
			<tr>







>
>
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


{if isset($_GET['chart_change'])}
<p class="block error">
	L'exercice sélectionné utilise un plan comptable différent, merci de sélectionner un autre compte.
</p>
{/if}

{if $pending_count}
	{include file="acc/transactions/_pending_message.tpl"}
{/if}

{include file="acc/_simple_help.tpl" link="../reports/trial_balance.php?year=%d"|args:$current_year.id type=null}

{if !empty($grouped_accounts)}
	<table class="list">
		<thead>
			<tr>

Modified src/templates/acc/accounts/simple.tpl from [3041b18c6c] to [9e7900910d].

11
12
13
14
15
16
17




18
19
20
21
22
23
24
	</aside>
	<ul>
		{foreach from=$types key="key" item="label"}
		<li{if $type == $key} class="current"{/if}><a href="?type={$key}">{$label}</a></li>
		{/foreach}
	</ul>
</nav>





{if !$list->count()}
	<p class="alert block">
		Aucune écriture à afficher.
	</p>
{else}
	<form method="post" action="{$admin_url}acc/transactions/actions.php">







>
>
>
>







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
	</aside>
	<ul>
		{foreach from=$types key="key" item="label"}
		<li{if $type == $key} class="current"{/if}><a href="?type={$key}">{$label}</a></li>
		{/foreach}
	</ul>
</nav>

{if $pending_count}
	{include file="acc/transactions/_pending_message.tpl"}
{/if}

{if !$list->count()}
	<p class="alert block">
		Aucune écriture à afficher.
	</p>
{else}
	<form method="post" action="{$admin_url}acc/transactions/actions.php">

Added src/templates/acc/transactions/_pending_message.tpl version [9d2d58c5c3].

















>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
<p class="alert block">
	{
		{Il y a une dette ou créance à régler dans les exercices clos.}
		{Il y a %n dettes ou créances à régler dans les exercices clos.}
		n=$pending_count
	}<br />
	{linkbutton href="!acc/transactions/pending.php" label="Voir les dettes et créances en attente" shape="menu"}
</p>

Added src/templates/acc/transactions/pending.tpl version [3033e884d5].



























































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
{include file="admin/_head.tpl" title="Dettes et créances non réglées sur les exercices clos" current="acc/simple"}

<nav class="tabs">
	<aside>
		{exportmenu href="?export="}
		{linkbutton shape="search" href="!acc/search.php" label="Recherche"}
	</aside>
</nav>

{if !$list->count()}
	<p class="alert block">
		Aucune écriture à afficher.
	</p>
{else}
	{include file="common/dynamic_list_head.tpl"}

		{foreach from=$list->iterate() item="line"}
			<tr>
				<td>{$line.year_label}</td>
				<td>{$line.type_label}</td>
				<td class="num">{link href="!acc/transactions/details.php?id=%d"|args:$line.id label="#%d"|args:$line.id}</td>
				<td>{$line.date|date_short}</td>
				<td class="money">{$line.change|abs|raw|money}</td>
				<td>{$line.reference}</td>
				<th>{$line.label}</th>
				<td class="actions">
					{if $line.type == Entities\Accounting\Transaction::TYPE_DEBT && ($line.status & Entities\Accounting\Transaction::STATUS_WAITING)}
						{linkbutton shape="check" label="Régler cette dette" href="!acc/transactions/payoff.php?for=%d"|args:$line.id}
					{elseif $line.type == Entities\Accounting\Transaction::TYPE_CREDIT && ($line.status & Entities\Accounting\Transaction::STATUS_WAITING)}
						{linkbutton shape="export" label="Régler cette créance" href="!acc/transactions/payoff.php?for=%d"|args:$line.id}
					{/if}

					{linkbutton href="!acc/transactions/details.php?id=%d"|args:$line.id label="Détails" shape="search"}
				</td>
			</tr>
		{/foreach}
		</tbody>
	</table>

	</form>

	{pagination url=$list->paginationURL() page=$list.page bypage=$list.per_page total=$list->count()}
{/if}

{include file="admin/_foot.tpl"}

Modified src/www/admin/acc/accounts/index.php from [f19ff030ea] to [ca40714bbd].

1
2
3
4

5
6
7
8
9
10




11
12
13
14
15
<?php
namespace Garradin;

use Garradin\Accounting\Reports;


require_once __DIR__ . '/../_inc.php';

if (!CURRENT_YEAR_ID) {
	Utils::redirect('!acc/years/?msg=OPEN');
}





$tpl->assign('chart_id', $current_year->id_chart);
$tpl->assign('grouped_accounts', Reports::getClosingSumsFavoriteAccounts(['year' => CURRENT_YEAR_ID]));

$tpl->display('acc/accounts/index.tpl');




>






>
>
>
>





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
namespace Garradin;

use Garradin\Accounting\Reports;
use Garradin\Accounting\Transactions;

require_once __DIR__ . '/../_inc.php';

if (!CURRENT_YEAR_ID) {
	Utils::redirect('!acc/years/?msg=OPEN');
}

$pending_count = Transactions::listPendingCreditAndDebtForClosedYears()->count();

$tpl->assign(compact('pending_count'));

$tpl->assign('chart_id', $current_year->id_chart);
$tpl->assign('grouped_accounts', Reports::getClosingSumsFavoriteAccounts(['year' => CURRENT_YEAR_ID]));

$tpl->display('acc/accounts/index.tpl');

Modified src/www/admin/acc/accounts/simple.php from [83f17410b3] to [d130795fe7].

30
31
32
33
34
35
36






37
38
39

$list = Transactions::listByType(CURRENT_YEAR_ID, $type == -1 ? null : $type);
$list->setTitle(sprintf('Suivi - %s', $types[$type]));
$list->loadFromQueryString();

$can_edit = $session->canAccess($session::SECTION_ACCOUNTING, $session::ACCESS_ADMIN) && !$year->closed;







$tpl->assign(compact('type', 'list', 'types', 'can_edit', 'year'));

$tpl->display('acc/accounts/simple.tpl');







>
>
>
>
>
>
|


30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

$list = Transactions::listByType(CURRENT_YEAR_ID, $type == -1 ? null : $type);
$list->setTitle(sprintf('Suivi - %s', $types[$type]));
$list->loadFromQueryString();

$can_edit = $session->canAccess($session::SECTION_ACCOUNTING, $session::ACCESS_ADMIN) && !$year->closed;

$pending_count = null;

if ($type == Transaction::TYPE_CREDIT || $type == Transaction::TYPE_DEBT) {
	$pending_count = Transactions::listPendingCreditAndDebtForClosedYears()->count();
}

$tpl->assign(compact('type', 'list', 'types', 'can_edit', 'year', 'pending_count'));

$tpl->display('acc/accounts/simple.tpl');

Added src/www/admin/acc/transactions/pending.php version [a795ae2cc4].





























>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
namespace Garradin;

use Garradin\Accounting\Transactions;
use Garradin\Entities\Accounting\Transaction;

require_once __DIR__ . '/../_inc.php';

$list = Transactions::listPendingCreditAndDebtForClosedYears();
$list->loadFromQueryString();

$tpl->assign(compact('list'));

$tpl->display('acc/transactions/pending.tpl');