Overview
Comment:Implement list of transactions linked to a service
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA1: 54b9d03a1cefda1087138906d76a40a9c3ae8804
User & Date: bohwaz on 2020-11-08 02:11:08
Other Links: branch diff | manifest | tags
Context
2020-11-08
03:24
Improve login form messages check-in: cb81b4f743 user: bohwaz tags: dev
02:11
Implement list of transactions linked to a service check-in: 54b9d03a1c user: bohwaz tags: dev
01:41
Implement partial service fee payments check-in: 80bb461020 user: bohwaz tags: dev
Changes

Modified src/include/lib/Garradin/Accounting/Reports.php from [db2104d9f5] to [61beb22b4d].

33
34
35
36
37
38
39




40
41
42
43
44
45
46
		if (!empty($criterias['user'])) {
			$where[] = sprintf('t.id IN (SELECT id_transaction FROM acc_transactions_users WHERE id_user = %d)', $criterias['user']);
		}

		if (!empty($criterias['creator'])) {
			$where[] = sprintf('t.id_creator = %d', $criterias['creator']);
		}





		if (!count($where)) {
			throw new \LogicException('Unknown criteria');
		}

		return implode(' AND ', $where);
	}







>
>
>
>







33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
		if (!empty($criterias['user'])) {
			$where[] = sprintf('t.id IN (SELECT id_transaction FROM acc_transactions_users WHERE id_user = %d)', $criterias['user']);
		}

		if (!empty($criterias['creator'])) {
			$where[] = sprintf('t.id_creator = %d', $criterias['creator']);
		}

		if (!empty($criterias['service_user'])) {
			$where[] = sprintf('t.id IN (SELECT tu.id_transaction FROM acc_transactions_users tu WHERE id_service_user = %d)', $criterias['service_user']);
		}

		if (!count($where)) {
			throw new \LogicException('Unknown criteria');
		}

		return implode(' AND ', $where);
	}
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204

205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
		}

		return $out;
	}

	/**
	 * Return list of favorite accounts (accounts with a type), grouped by type, with their current sum
	 * @param  int    $chart_id
	 * @param  int    $year_id
	 * @return \Generator list of accounts grouped by type
	 */
	static public function getClosingSumsFavoriteAccounts(int $chart_id, int $year_id, bool $include_all = false): \Generator
	{
		if ($include_all) {
			// List all accounts, including those with no amount
			$sql = sprintf('SELECT a.id, a.code, a.label, a.description, a.type,
				(SELECT SUM(l.credit) - SUM(l.debit) FROM %s l INNER JOIN %s t ON t.id = l.id_transaction WHERE l.id_account = a.id AND t.id_year = %d) AS sum
				FROM %s a
				WHERE a.id_chart = %d AND a.type != 0
				GROUP BY a.id
				ORDER BY a.type, a.code COLLATE NOCASE;',
				Line::TABLE, Transaction::TABLE, $year_id, Account::TABLE, $chart_id);

		}
		else {
			$sql = sprintf('SELECT a.id, a.code, a.label, a.description, a.type,
				SUM(l.credit) - SUM(l.debit) AS sum
				FROM %s a
				INNER JOIN %s t ON t.id = l.id_transaction
				INNER JOIN %s l ON a.id = l.id_account
				WHERE t.id_year = %d AND a.type != 0
				GROUP BY l.id_account
				ORDER BY a.type, a.code COLLATE NOCASE;', Account::TABLE, Transaction::TABLE, Line::TABLE, $year_id);
		}

		$group = null;

		foreach (DB::getInstance()->iterate($sql) as $row) {
			if (null !== $group && $row->type !== $group->type) {
				yield $group;
				$group = null;







<
<


|

<
<
<
<
<
<
<
<
<
>
|
<





|

|
<







187
188
189
190
191
192
193


194
195
196
197









198
199

200
201
202
203
204
205
206
207

208
209
210
211
212
213
214
		}

		return $out;
	}

	/**
	 * Return list of favorite accounts (accounts with a type), grouped by type, with their current sum


	 * @return \Generator list of accounts grouped by type
	 */
	static public function getClosingSumsFavoriteAccounts(array $criterias): \Generator
	{









		$where = self::getWhereClause($criterias);


			$sql = sprintf('SELECT a.id, a.code, a.label, a.description, a.type,
				SUM(l.credit) - SUM(l.debit) AS sum
				FROM %s a
				INNER JOIN %s t ON t.id = l.id_transaction
				INNER JOIN %s l ON a.id = l.id_account
			WHERE a.type != 0 AND %s
				GROUP BY l.id_account
			ORDER BY a.type, a.code COLLATE NOCASE;', Account::TABLE, Transaction::TABLE, Line::TABLE, $where);


		$group = null;

		foreach (DB::getInstance()->iterate($sql) as $row) {
			if (null !== $group && $row->type !== $group->type) {
				yield $group;
				$group = null;
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
		}

		if (null !== $group) {
			yield $group;
		}
	}

	static public function getClosingSums(int $year_id): array
	{
		// Find sums, link them to accounts
		$sql = sprintf('SELECT l.id_account, SUM(l.credit) - SUM(l.debit)
			FROM %s l
			INNER JOIN %s t ON t.id = l.id_transaction
			WHERE t.id_year = %d GROUP BY l.id_account;', Line::TABLE, Transaction::TABLE, $year_id);
		return DB::getInstance()->getAssoc($sql);
	}

	/**
	 * Grand livre
	 */
	static public function getGeneralLedger(array $criterias): \Generator
	{
		$where = self::getWhereClause($criterias);








<
<
<
<
<
<
<
<
<
<







229
230
231
232
233
234
235










236
237
238
239
240
241
242
		}

		if (null !== $group) {
			yield $group;
		}
	}











	/**
	 * Grand livre
	 */
	static public function getGeneralLedger(array $criterias): \Generator
	{
		$where = self::getWhereClause($criterias);

Modified src/include/lib/Garradin/Accounting/Years.php from [849724b7dc] to [50248f541d].

20
21
22
23
24
25
26





27
28
29
30
31
32
33
	}

	static public function listOpen()
	{
		$em = EntityManager::getInstance(Year::class);
		return $em->all('SELECT * FROM @TABLE WHERE closed = 0 ORDER BY end_date;');
	}






	static public function listClosed()
	{
		$em = EntityManager::getInstance(Year::class);
		return $em->all('SELECT * FROM @TABLE WHERE closed = 1 ORDER BY end_date;');
	}








>
>
>
>
>







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
	}

	static public function listOpen()
	{
		$em = EntityManager::getInstance(Year::class);
		return $em->all('SELECT * FROM @TABLE WHERE closed = 0 ORDER BY end_date;');
	}

	static public function listAssoc()
	{
		return DB::getInstance()->getAssoc('SELECT id, label FROM acc_years ORDER BY end_date;');
	}

	static public function listClosed()
	{
		$em = EntityManager::getInstance(Year::class);
		return $em->all('SELECT * FROM @TABLE WHERE closed = 1 ORDER BY end_date;');
	}

Added src/templates/acc/transactions/service_user.tpl version [5cc486b106].

































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
{include file="admin/_head.tpl" title="Écritures liées à une inscription" current="acc/accounts"}

<p>
	{linkbutton href="membres/fiche.php?id=%d"|args:$user_id label="Retour à la fiche membre" shape="user"}
</p>

{include file="acc/reports/_journal.tpl"}

<h2 class="ruler">Solde des comptes</h2>

<table class="list">
	<thead>
		<tr>
			<td>Numéro</td>
			<th>Compte</th>
			<td class="money">Solde débiteur</td>
			<td class="money">Solde créditeur</td>
		</tr>
	</thead>
	<tbody>
	{foreach from=$balance item="account"}
		<tr>
			<td class="num"><a href="{$admin_url}acc/accounts/journal.php?id={$account.id}">{$account.code}</a></td>
			<th>{$account.label}</th>
			<td class="money">{if $account.sum < 0}{$account.sum|raw|html_money}{/if}</td>
			<td class="money">{if $account.sum > 0}{$account.sum|raw|html_money}{/if}</td>
		</tr>
	{/foreach}
	</tbody>
</table>

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

Modified src/templates/acc/transactions/user.tpl from [a8bb45e52a] to [d3806dea1b].

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
{include file="admin/_head.tpl" title="Écritures liées à %s"|args:$transaction_user.identite current="acc/accounts"}

<p>
	{linkbutton href="membres/fiche.php?id=%d"|args:$transaction_user.id label="Retour à la fiche membre" shape="user"}
</p>

{include file="acc/reports/_journal.tpl"}

<h2 class="ruler">Solde des comptes</h2>














<p class="block alert">Cette liste représente le solde des comptes uniquement pour les écritures liées à un membre, et ce sur tous les exercices réunis.</p>

<table class="list">
	<thead>
		<tr>
			<td>Numéro</td>
			<th>Compte</th>
			<td class="money">Solde</td>

		</tr>
	</thead>
	<tbody>
	{foreach from=$balance item="account"}
		<tr>
			<td class="num"><a href="{$admin_url}acc/accounts/journal.php?id={$account.id}">{$account.code}</a></td>
			<th>{$account.label}</th>
			<td class="money">{$account.sum|raw|html_money:false}</td>

		</tr>
	{/foreach}
	</tbody>
</table>

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










>
>
>
>
>
>
>
>
>
>
>
>
>
|






|
>







|
>






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
46
47
{include file="admin/_head.tpl" title="Écritures liées à %s"|args:$transaction_user.identite current="acc/accounts"}

<p>
	{linkbutton href="membres/fiche.php?id=%d"|args:$transaction_user.id label="Retour à la fiche membre" shape="user"}
</p>

{include file="acc/reports/_journal.tpl"}

<h2 class="ruler">Solde des comptes</h2>

<form method="get" action="{$self_url_no_qs}">
	<fieldset>
		<legend>Exercice</legend>
		<dl>
			{input type="select" name="year" options=$years onchange="this.form.submit();" default=$year}
		</dl>
		<input type="hidden" name="id" value="{$transaction_user.id}" />
		<noscript>
			<input type="submit" value="OK" />
		</noscript>
	</fieldset>
</form>

<p class="block help">Cette liste représente le solde des comptes uniquement pour les écritures liées à ce membre.</p>

<table class="list">
	<thead>
		<tr>
			<td>Numéro</td>
			<th>Compte</th>
			<td class="money">Solde débiteur</td>
			<td class="money">Solde créditeur</td>
		</tr>
	</thead>
	<tbody>
	{foreach from=$balance item="account"}
		<tr>
			<td class="num"><a href="{$admin_url}acc/accounts/journal.php?id={$account.id}">{$account.code}</a></td>
			<th>{$account.label}</th>
			<td class="money">{if $account.sum < 0}{$account.sum|raw|html_money}{/if}</td>
			<td class="money">{if $account.sum > 0}{$account.sum|raw|html_money}{/if}</td>
		</tr>
	{/foreach}
	</tbody>
</table>

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

Modified src/templates/services/user.tpl from [87355b82f5] to [842c9e2d18].

38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
					{if $row.paid}
						{linkbutton shape="reset" label="Marquer comme non payé" href="services/user.php?id=%d&su_id=%d&paid=0"|args:$user.id,$row.id}
					{else}
						{linkbutton shape="check" label="Marquer comme payé" href="services/user.php?id=%d&su_id=%d&paid=1"|args:$user.id,$row.id}
					{/if}
				{/if}
				{if $session->canAccess('compta', Membres::DROIT_ACCES) && $row.id_account}
					{linkbutton shape="menu" label="Liste des écritures" href="acc/transactions/service_user.php?id=%d"|args:$row.id}
				{/if}
				{if $session->canAccess('compta', Membres::DROIT_ECRITURE) && $row.id_account}
					{linkbutton shape="plus" label="Nouveau règlement" href="services/payment.php?id=%d"|args:$row.id}
				{/if}
			</td>
		</tr>
	{/foreach}







|







38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
					{if $row.paid}
						{linkbutton shape="reset" label="Marquer comme non payé" href="services/user.php?id=%d&su_id=%d&paid=0"|args:$user.id,$row.id}
					{else}
						{linkbutton shape="check" label="Marquer comme payé" href="services/user.php?id=%d&su_id=%d&paid=1"|args:$user.id,$row.id}
					{/if}
				{/if}
				{if $session->canAccess('compta', Membres::DROIT_ACCES) && $row.id_account}
					{linkbutton shape="menu" label="Liste des écritures" href="acc/transactions/service_user.php?id=%d&user=%d"|args:$row.id,$user.id}
				{/if}
				{if $session->canAccess('compta', Membres::DROIT_ECRITURE) && $row.id_account}
					{linkbutton shape="plus" label="Nouveau règlement" href="services/payment.php?id=%d"|args:$row.id}
				{/if}
			</td>
		</tr>
	{/foreach}

Modified src/www/admin/acc/accounts/index.php from [e63b9dcb8c] to [c826784eaa].

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(ADMIN_URL . 'acc/years/?msg=OPEN');
}

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

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












|


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(ADMIN_URL . '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');

Added src/www/admin/acc/transactions/service_user.php version [0473be53b7].



































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

use Garradin\Accounting\Reports;
use Garradin\Accounting\Years;

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

$session->requireAccess('compta', Membres::DROIT_ACCES);

$criterias = ['service_user' => (int)qg('id')];

$tpl->assign('balance', Reports::getClosingSumsWithAccounts($criterias));
$tpl->assign('journal', Reports::getJournal($criterias));
$tpl->assign('user_id', qg('user'));

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

Modified src/www/admin/acc/transactions/user.php from [7d6ef4bfc0] to [698de7ba96].

1
2
3
4

5
6
7
8
9
10
11
12
13
14
15




16
17
18
19

20
21
22
<?php
namespace Garradin;

use Garradin\Accounting\Reports;


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

$session->requireAccess('compta', Membres::DROIT_ACCES);

$u = (new Membres)->get((int)qg('id'));

if (!$u) {
	throw new UserException('Ce membre n\'existe pas');
}





$criterias = ['user' => $u->id];

$tpl->assign('balance', Reports::getClosingSumsWithAccounts($criterias));
$tpl->assign('journal', Reports::getJournal($criterias));

$tpl->assign('transaction_user', $u);

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




>











>
>
>
>


|

>



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
<?php
namespace Garradin;

use Garradin\Accounting\Reports;
use Garradin\Accounting\Years;

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

$session->requireAccess('compta', Membres::DROIT_ACCES);

$u = (new Membres)->get((int)qg('id'));

if (!$u) {
	throw new UserException('Ce membre n\'existe pas');
}

$years = Years::listAssoc();
end($years);
$year = (int)qg('year') ?: key($years);

$criterias = ['user' => $u->id];

$tpl->assign('balance', Reports::getClosingSumsWithAccounts($criterias + ['year' => $year]));
$tpl->assign('journal', Reports::getJournal($criterias));
$tpl->assign(compact('years', 'year'));
$tpl->assign('transaction_user', $u);

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