Overview
Comment:Re-implement missing "only non-reconciled transactions" option
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA1: 63cf52cc9a70669ceaf8bef317538494035f9580
User & Date: bohwaz on 2020-11-20 11:26:22
Other Links: branch diff | manifest | tags
Context
2020-11-20
11:34
Add reconciled sum column check-in: 69e18221d0 user: bohwaz tags: dev
11:26
Re-implement missing "only non-reconciled transactions" option check-in: 63cf52cc9a user: bohwaz tags: dev
11:25
Mention that analytical accounts can be deleted check-in: ed382034d7 user: bohwaz tags: dev
Changes

Modified src/include/lib/Garradin/Entities/Accounting/Account.php from [3b7ef1378b] to [043c8c9646].

215
216
217
218
219
220
221
222
223
224
225
226
227


228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252

253

254
255
256
257
258
259
260
	}

	static public function isReversed(int $type): bool
	{
		return in_array($type, [self::TYPE_BANK, self::TYPE_CASH, self::TYPE_OUTSTANDING, self::TYPE_EXPENSE, self::TYPE_THIRD_PARTY]);
	}

	public function getReconcileJournal(int $year_id, DateTimeInterface $start_date, DateTimeInterface $end_date)
	{
		if ($end_date < $start_date) {
			throw new ValidationException('La date de début ne peut être avant la date de fin.');
		}



		$db = DB::getInstance();
		$sql = 'SELECT l.debit, l.credit, t.id, t.date, t.reference, l.reference AS line_reference, t.label, l.label AS line_label, l.reconciled, l.id AS id_line
			FROM acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			WHERE l.id_account = ? AND t.id_year = ? AND t.date >= ? AND t.date <= ?
			ORDER BY t.date, t.id;';
		$rows = $db->iterate($sql, $this->id(), $year_id, $start_date->format('Y-m-d'), $end_date->format('Y-m-d'));

		$sum = $this->getSumAtDate($year_id, $start_date);

		$start_sum = false;

		foreach ($rows as $row) {
			if (!$start_sum) {
				yield (object) ['sum' => $sum, 'date' => $start_date];
				$start_sum = true;
			}

			$row->date = \DateTime::createFromFormat('Y-m-d', $row->date);
			$sum += ($row->credit - $row->debit);
			$row->running_sum = $sum;

			yield $row;
		}


		yield (object) ['sum' => $sum, 'date' => $end_date];

	}

	public function mergeReconcileJournalAndCSV(\Generator $journal, CSV_Custom $csv)
	{
		$lines = [];

		$csv = iterator_to_array($csv->iterate());







|





>
>




|

|


















>
|
>







215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
	}

	static public function isReversed(int $type): bool
	{
		return in_array($type, [self::TYPE_BANK, self::TYPE_CASH, self::TYPE_OUTSTANDING, self::TYPE_EXPENSE, self::TYPE_THIRD_PARTY]);
	}

	public function getReconcileJournal(int $year_id, DateTimeInterface $start_date, DateTimeInterface $end_date, bool $only_non_reconciled)
	{
		if ($end_date < $start_date) {
			throw new ValidationException('La date de début ne peut être avant la date de fin.');
		}

		$condition = $only_non_reconciled ? ' AND l.reconciled = 0' : '';

		$db = DB::getInstance();
		$sql = 'SELECT l.debit, l.credit, t.id, t.date, t.reference, l.reference AS line_reference, t.label, l.label AS line_label, l.reconciled, l.id AS id_line
			FROM acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			WHERE l.id_account = ? AND t.id_year = ? AND t.date >= ? AND t.date <= ? %s
			ORDER BY t.date, t.id;';
		$rows = $db->iterate(sprintf($sql, $condition), $this->id(), $year_id, $start_date->format('Y-m-d'), $end_date->format('Y-m-d'));

		$sum = $this->getSumAtDate($year_id, $start_date);

		$start_sum = false;

		foreach ($rows as $row) {
			if (!$start_sum) {
				yield (object) ['sum' => $sum, 'date' => $start_date];
				$start_sum = true;
			}

			$row->date = \DateTime::createFromFormat('Y-m-d', $row->date);
			$sum += ($row->credit - $row->debit);
			$row->running_sum = $sum;

			yield $row;
		}

		if (!$only_non_reconciled) {
			yield (object) ['sum' => $sum, 'date' => $end_date];
		}
	}

	public function mergeReconcileJournalAndCSV(\Generator $journal, CSV_Custom $csv)
	{
		$lines = [];

		$csv = iterator_to_array($csv->iterate());

Modified src/templates/acc/accounts/reconcile.tpl from [468467b95e] to [0862d327c1].

26
27
28
29
30
31
32

33
34
35
36
37
38
39
	<fieldset>
		<legend>Période de rapprochement</legend>
		<p>
			Du
			{input type="date" name="start" default=$start}
			au
			{input type="date" name="end" default=$end}

			<input type="hidden" name="id" value="{$account.id}" />
			<input type="submit" value="Afficher" />
		</p>
	</fieldset>
</form>

<p class="block help">







>







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
	<fieldset>
		<legend>Période de rapprochement</legend>
		<p>
			Du
			{input type="date" name="start" default=$start}
			au
			{input type="date" name="end" default=$end}
			<label>{input type="checkbox" name="only" value=1 default=$only} Seulement les écritures non rapprochées</label>
			<input type="hidden" name="id" value="{$account.id}" />
			<input type="submit" value="Afficher" />
		</p>
	</fieldset>
</form>

<p class="block help">

Modified src/www/admin/acc/accounts/reconcile.php from [40c1e90229] to [e28873a4a2].

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61

if (!$account) {
	throw new UserException("Le compte demandé n'existe pas.");
}

$start = new \DateTime('first day of this month');
$end = new \DateTime('last day of this month');


if (null !== qg('start') && null !== qg('end'))
{
	$start = \DateTime::createFromFormat('!d/m/Y', qg('start'));
	$end = \DateTime::createFromFormat('!d/m/Y', qg('end'));

	if (!$start || !$end) {
		$form->addError('La date donnée est invalide.');
	}
}

if ($start < $current_year->start_date || $start > $current_year->end_date
	|| $end < $current_year->start_date || $end > $current_year->end_date) {
	$start = clone $current_year->start_date;
	$end = clone $start;
	$end->modify('last day of this month');
}

$journal = $account->getReconcileJournal(CURRENT_YEAR_ID, $start, $end);

// Enregistrement des cases cochées
$form->runIf(f('save') || f('save_next'), function () use ($journal, $start, $end, $account) {
	Transactions::saveReconciled($journal, f('reconcile'));

	if (f('save')) {
		Utils::redirect(Utils::getSelfURL());
	}
	else {
		$start->modify('+1 month');
		$end->modify('+1 month');
		$url = sprintf('%sacc/accounts/reconcile.php?id=%s&debut=%s&fin=%s&sauf=%s',
			ADMIN_URL, $account->id(), $start->format('Y-m-d'), $end->format('Y-m-d'), (int) qg('sauf'));
		Utils::redirect($url);
	}
}, 'acc_reconcile_' . $account->id());

$prev = clone $start;
$next = clone $start;
$prev->modify('-1 month');







>


















|


|








|
|







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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

if (!$account) {
	throw new UserException("Le compte demandé n'existe pas.");
}

$start = new \DateTime('first day of this month');
$end = new \DateTime('last day of this month');
$only = (bool) qg('only');

if (null !== qg('start') && null !== qg('end'))
{
	$start = \DateTime::createFromFormat('!d/m/Y', qg('start'));
	$end = \DateTime::createFromFormat('!d/m/Y', qg('end'));

	if (!$start || !$end) {
		$form->addError('La date donnée est invalide.');
	}
}

if ($start < $current_year->start_date || $start > $current_year->end_date
	|| $end < $current_year->start_date || $end > $current_year->end_date) {
	$start = clone $current_year->start_date;
	$end = clone $start;
	$end->modify('last day of this month');
}

$journal = $account->getReconcileJournal(CURRENT_YEAR_ID, $start, $end, $only);

// Enregistrement des cases cochées
$form->runIf(f('save') || f('save_next'), function () use ($journal, $start, $end, $account, $only) {
	Transactions::saveReconciled($journal, f('reconcile'));

	if (f('save')) {
		Utils::redirect(Utils::getSelfURL());
	}
	else {
		$start->modify('+1 month');
		$end->modify('+1 month');
		$url = sprintf('%sacc/accounts/reconcile.php?id=%s&start=%s&end=%s&only=%d',
			ADMIN_URL, $account->id(), $start->format('Y-m-d'), $end->format('Y-m-d'), $only);
		Utils::redirect($url);
	}
}, 'acc_reconcile_' . $account->id());

$prev = clone $start;
$next = clone $start;
$prev->modify('-1 month');
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

95
96
97
}

$self_uri = Utils::getSelfURI(false);

if (null !== $prev) {
	$prev = [
		'date' => $prev,
		'url' => sprintf($self_uri . '?id=%d&start=%s&end=%s&sauf=%d', $account->id, $prev->format('01/m/Y'), $prev->format('t/m/Y'), qg('sauf')),
	];
}

if (null !== $next) {
	$next = [
		'date' => $next,
		'url' => sprintf($self_uri . '?id=%d&start=%s&end=%s&sauf=%d', $account->id, $next->format('01/m/Y'), $next->format('t/m/Y'), qg('sauf')),
	];
}

$tpl->assign(compact(
	'account',
	'start',
	'end',
	'prev',
	'next',
	'journal'

));

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







|






|









|
>



71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
}

$self_uri = Utils::getSelfURI(false);

if (null !== $prev) {
	$prev = [
		'date' => $prev,
		'url' => sprintf($self_uri . '?id=%d&start=%s&end=%s&only=%d', $account->id, $prev->format('01/m/Y'), $prev->format('t/m/Y'), $only),
	];
}

if (null !== $next) {
	$next = [
		'date' => $next,
		'url' => sprintf($self_uri . '?id=%d&start=%s&end=%s&only=%d', $account->id, $next->format('01/m/Y'), $next->format('t/m/Y'), $only),
	];
}

$tpl->assign(compact(
	'account',
	'start',
	'end',
	'prev',
	'next',
	'journal',
	'only'
));

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