Overview
Comment:Implement import of linked users in each transaction
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable
Files: files | file ages | folders
SHA3-256: 555019068fd0f39c672f6a76e03b5f8235b5ee6c2c9fa2d9ab75b6d6ade8017b
User & Date: bohwaz on 2022-11-29 15:37:43
Other Links: manifest | tags
Context
2022-11-29
16:56
Fix error on import when translation table is not set check-in: d9144c3afe user: bohwaz tags: trunk, stable
15:37
Implement import of linked users in each transaction check-in: 555019068f user: bohwaz tags: trunk, stable
14:26
Fix possibly undefined variable check-in: a20501839b user: bohwaz tags: trunk, stable
Changes

Modified src/include/lib/Garradin/Accounting/Export.php from [25d46a7282] to [0e5101bc13].

228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
		$res = DB::getInstance()->iterate($sql, $year_id);

		$previous_id = null;

		foreach ($res as $row) {
			if ($type == self::GROUPED && $previous_id === $row->id) {
				// Remove transaction data to differentiate lines and transactions
				$row->id = $row->type = $row->status = $row->label = $row->date = $row->notes = $row->reference = null;
			}
			else {
				$row->type = Transaction::TYPES_NAMES[$row->type];

				if (property_exists($row, 'status')) {
					$status = [];








|







228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
		$res = DB::getInstance()->iterate($sql, $year_id);

		$previous_id = null;

		foreach ($res as $row) {
			if ($type == self::GROUPED && $previous_id === $row->id) {
				// Remove transaction data to differentiate lines and transactions
				$row->id = $row->type = $row->status = $row->label = $row->date = $row->notes = $row->reference = $row->linked_users = null;
			}
			else {
				$row->type = Transaction::TYPES_NAMES[$row->type];

				if (property_exists($row, 'status')) {
					$status = [];

Modified src/include/lib/Garradin/Accounting/Import.php from [9d866da3fa] to [f0f4b93023].

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
48
49
50
51
<?php

namespace Garradin\Accounting;

use Garradin\Entities\Accounting\Line;
use Garradin\Entities\Accounting\Transaction;
use Garradin\Entities\Accounting\Year;
use Garradin\CSV_Custom;

use Garradin\DB;
use Garradin\UserException;

class Import
{
	static protected function saveImportedTransaction(Transaction $transaction, bool $dry_run = false, array &$report = null): void
	{

































		if ($transaction->countLines() > 2) {
			$transaction->type = Transaction::TYPE_ADVANCED;
		}
		// Try to magically find out what kind of transaction this is
		elseif (!isset($transaction->type)) {
			$transaction->type = $transaction->findTypeFromAccounts();
		}

		if (!$dry_run) {
			if ($transaction->isModified()) {
				$transaction->save();
			}




		}
		else {
			$transaction->selfCheck();
		}

		if (null !== $report) {
			if (!$transaction->isModified()) {
				$target = 'unchanged';
			}
			elseif ($transaction->exists()) {
				$target = 'modified';
			}
			else {
				$target = 'created';
			}

			$report[$target][] = $transaction->asJournalArray();

		}
	}

	/**
	 * Imports a CSV file of transactions in a year
	 * @param  string     $type    Type of CSV format
	 * @param  Year       $year    Target year where transactions should be updated or created








>





|

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












>
>
>
>






|









|
>







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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
<?php

namespace Garradin\Accounting;

use Garradin\Entities\Accounting\Line;
use Garradin\Entities\Accounting\Transaction;
use Garradin\Entities\Accounting\Year;
use Garradin\CSV_Custom;
use Garradin\Config;
use Garradin\DB;
use Garradin\UserException;

class Import
{
	static protected function saveImportedTransaction(Transaction $transaction, ?array $linked_users, bool $dry_run = false, array &$report = null): void
	{
		static $users = [];
		$found_users = null;

		// Associate users
		if (is_array($linked_users) && count($linked_users)) {
			$found_users = array_intersect_key($users, array_flip($linked_users));

			foreach ($linked_users as $name) {
				if (!array_key_exists($name, $users)) {
					continue;
				}

				$found_users[$name] = $users[$name];
			}

			if (count($found_users) != count($linked_users)) {
				$id_field = Config::getInstance()->champ_identite;
				$db = DB::getInstance();
				$sql = sprintf('SELECT %s AS name, id FROM membres WHERE %s;', $db->quoteIdentifier($id_field), $db->where($id_field, $linked_users));

				foreach ($db->iterate($sql) as $row) {
					$found_users[$row->name] = $row->id;
					$users[$row->name] = $row->id;
				}

				$missing = array_diff_key($users, array_flip($linked_users));

				foreach ($missing as $key => $v) {
					$users[$key] = null;
				}
			}
		}

		if ($transaction->countLines() > 2) {
			$transaction->type = Transaction::TYPE_ADVANCED;
		}
		// Try to magically find out what kind of transaction this is
		elseif (!isset($transaction->type)) {
			$transaction->type = $transaction->findTypeFromAccounts();
		}

		if (!$dry_run) {
			if ($transaction->isModified()) {
				$transaction->save();
			}

			if (null !== $found_users) {
				$transaction->updateLinkedUsers($found_users);
			}
		}
		else {
			$transaction->selfCheck();
		}

		if (null !== $report) {
			if (!$transaction->isModified() && (null === $found_users || !$transaction->checkLinkedUsersChange($found_users))) {
				$target = 'unchanged';
			}
			elseif ($transaction->exists()) {
				$target = 'modified';
			}
			else {
				$target = 'created';
			}

			$report[$target][] = $transaction->asJournalArray()
				+ ['linked_users' => null !== $found_users ? implode(', ', array_keys($found_users)) : null];
		}
	}

	/**
	 * Imports a CSV file of transactions in a year
	 * @param  string     $type    Type of CSV format
	 * @param  Year       $year    Target year where transactions should be updated or created
75
76
77
78
79
80
81

82
83
84
85
86
87
88
		}

		$db = DB::getInstance();
		$db->begin();

		$accounts = $year->accounts();
		$transaction = null;

		$types = array_flip(Transaction::TYPES_NAMES);

		if ($o->return_report) {
			$report = ['created' => [], 'modified' => [], 'unchanged' => []];
		}
		else {
			$report = null;







>







114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
		}

		$db = DB::getInstance();
		$db->begin();

		$accounts = $year->accounts();
		$transaction = null;
		$linked_users = null;
		$types = array_flip(Transaction::TYPES_NAMES);

		if ($o->return_report) {
			$report = ['created' => [], 'modified' => [], 'unchanged' => []];
		}
		else {
			$report = null;
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
						&& empty($row->date)
						&& empty($row->notes)
						&& empty($row->reference)
					);

					// New transaction, save previous one
					if (null !== $transaction && $has_transaction) {
						self::saveImportedTransaction($transaction, $dry_run, $report);
						$transaction = null;

					}

					if (!$has_transaction && null === $transaction) {
						throw new UserException('cette ligne n\'est reliée à aucune écriture');
					}
				}
				else {
					if (!empty($row->id) && $row->id != $current_id) {
						if (null !== $transaction) {
							self::saveImportedTransaction($transaction, $dry_run, $report);
							$transaction = null;

						}

						$current_id = (int) $row->id;
					}
				}

				// Find or create transaction







|

>









|

>







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
						&& empty($row->date)
						&& empty($row->notes)
						&& empty($row->reference)
					);

					// New transaction, save previous one
					if (null !== $transaction && $has_transaction) {
						self::saveImportedTransaction($transaction, $linked_users, $dry_run, $report);
						$transaction = null;
						$linked_users = null;
					}

					if (!$has_transaction && null === $transaction) {
						throw new UserException('cette ligne n\'est reliée à aucune écriture');
					}
				}
				else {
					if (!empty($row->id) && $row->id != $current_id) {
						if (null !== $transaction) {
							self::saveImportedTransaction($transaction, $linked_users, $dry_run, $report);
							$transaction = null;
							$linked_users = null;
						}

						$current_id = (int) $row->id;
					}
				}

				// Find or create transaction
176
177
178
179
180
181
182




183
184
185
186
187
188
189
							if (in_array($v, $status_list)) {
								$status |= $k;
							}
						}

						$transaction->status = $status;
					}




				}

				$data = [];

				if (!empty($row->project)) {
					$id_project = Projects::getIdFromCodeOrLabel($row->project);








>
>
>
>







218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
							if (in_array($v, $status_list)) {
								$status |= $k;
							}
						}

						$transaction->status = $status;
					}

					if (isset($row->linked_users)) {
						$linked_users = array_map('trim', explode(',', $row->linked_users));
					}
				}

				$data = [];

				if (!empty($row->project)) {
					$id_project = Projects::getIdFromCodeOrLabel($row->project);

226
227
228
229
230
231
232
233
234

235
236
237
238
239
240
241
						'debit'      => $row->amount,
						'id_account' => $debit_account,
					]);

					$transaction->addLine($l1);
					$transaction->addLine($l2);

					self::saveImportedTransaction($transaction, $dry_run, $report);
					$transaction = null;

				}
				else {
					$id_account = $accounts->getIdFromCode($row->account);

					if (!$id_account) {
						throw new UserException(sprintf('le compte "%s" n\'existe pas dans le plan comptable', $row->account));
					}







|

>







272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
						'debit'      => $row->amount,
						'id_account' => $debit_account,
					]);

					$transaction->addLine($l1);
					$transaction->addLine($l2);

					self::saveImportedTransaction($transaction, $linked_users, $dry_run, $report);
					$transaction = null;
					$linked_users = null;
				}
				else {
					$id_account = $accounts->getIdFromCode($row->account);

					if (!$id_account) {
						throw new UserException(sprintf('le compte "%s" n\'existe pas dans le plan comptable', $row->account));
					}
252
253
254
255
256
257
258
259
260

261
262
263
264
265
266
267
					$line = new Line;
					$line->importForm($data);
					$transaction->addLine($line);
				}
			}

			if (null !== $transaction) {
				self::saveImportedTransaction($transaction, $dry_run, $report);
				$transaction = null;

			}
		}
		catch (UserException $e) {
			$db->rollback();
			$e->setMessage(sprintf('Erreur sur la ligne %d : %s', $l - 1, $e->getMessage()));

			if (null !== $transaction) {







|

>







299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
					$line = new Line;
					$line->importForm($data);
					$transaction->addLine($line);
				}
			}

			if (null !== $transaction) {
				self::saveImportedTransaction($transaction, $linked_users, $dry_run, $report);
				$transaction = null;
				$linked_users = null;
			}
		}
		catch (UserException $e) {
			$db->rollback();
			$e->setMessage(sprintf('Erreur sur la ligne %d : %s', $l - 1, $e->getMessage()));

			if (null !== $transaction) {

Modified src/include/lib/Garradin/Entities/Accounting/Transaction.php from [8871deaa91] to [7a0d34d5c9].

893
894
895
896
897
898
899
900
901
902




903
904
905
906
907
908
909
910
911
912
913
914
915













916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
	{
		$db = EntityManager::getInstance(self::class)->DB();

		return $db->preparedQuery('REPLACE INTO acc_transactions_users (id_transaction, id_user, id_service_user) VALUES (?, ?, ?);',
			$this->id(), $user_id, $service_id);
	}

	public function updateLinkedUsers(array $users)
	{
		$db = EntityManager::getInstance(self::class)->DB();





		$db->begin();

		$sql = sprintf('DELETE FROM acc_transactions_users WHERE id_transaction = ? AND %s AND id_service_user IS NULL;', $db->where('id_user', 'NOT IN', $users));
		$db->preparedQuery($sql, $this->id());

		foreach ($users as $id) {
			$db->preparedQuery('INSERT OR IGNORE INTO acc_transactions_users (id_transaction, id_user) VALUES (?, ?);', $this->id(), $id);
		}

		$db->commit();
	}














	public function listLinkedUsers()
	{
		$db = EntityManager::getInstance(self::class)->DB();
		$identity_column = Config::getInstance()->get('champ_identite');
		$sql = sprintf('SELECT m.id, m.%s AS identity, l.id_service_user FROM membres m INNER JOIN acc_transactions_users l ON l.id_user = m.id WHERE l.id_transaction = ?;', $identity_column);
		return $db->get($sql, $this->id());
	}

	public function listLinkedUsersAssoc()
	{
		$db = EntityManager::getInstance(self::class)->DB();
		$identity_column = Config::getInstance()->get('champ_identite');
		$sql = sprintf('SELECT m.id, m.%s AS identity, l.id_service_user
			FROM membres m
			INNER JOIN acc_transactions_users l ON l.id_user = m.id
			WHERE l.id_transaction = ? AND l.id_service_user IS NULL;', $identity_column);







|


>
>
>
>













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







|







893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
	{
		$db = EntityManager::getInstance(self::class)->DB();

		return $db->preparedQuery('REPLACE INTO acc_transactions_users (id_transaction, id_user, id_service_user) VALUES (?, ?, ?);',
			$this->id(), $user_id, $service_id);
	}

	public function updateLinkedUsers(array $users): void
	{
		$db = EntityManager::getInstance(self::class)->DB();

		if (!$this->checkLinkedUsersChange($users)) {
			return;
		}

		$db->begin();

		$sql = sprintf('DELETE FROM acc_transactions_users WHERE id_transaction = ? AND %s AND id_service_user IS NULL;', $db->where('id_user', 'NOT IN', $users));
		$db->preparedQuery($sql, $this->id());

		foreach ($users as $id) {
			$db->preparedQuery('INSERT OR IGNORE INTO acc_transactions_users (id_transaction, id_user) VALUES (?, ?);', $this->id(), $id);
		}

		$db->commit();
	}

	public function checkLinkedUsersChange(array $users): bool
	{
		$existing = $this->listLinkedUsersAssoc();
		ksort($users);
		ksort($existing);

		if ($users === $existing) {
			return false;
		}

		return true;
	}

	public function listLinkedUsers(): array
	{
		$db = EntityManager::getInstance(self::class)->DB();
		$identity_column = Config::getInstance()->get('champ_identite');
		$sql = sprintf('SELECT m.id, m.%s AS identity, l.id_service_user FROM membres m INNER JOIN acc_transactions_users l ON l.id_user = m.id WHERE l.id_transaction = ?;', $identity_column);
		return $db->get($sql, $this->id());
	}

	public function listLinkedUsersAssoc(): array
	{
		$db = EntityManager::getInstance(self::class)->DB();
		$identity_column = Config::getInstance()->get('champ_identite');
		$sql = sprintf('SELECT m.id, m.%s AS identity, l.id_service_user
			FROM membres m
			INNER JOIN acc_transactions_users l ON l.id_user = m.id
			WHERE l.id_transaction = ? AND l.id_service_user IS NULL;', $identity_column);

Modified src/templates/acc/reports/_journal.tpl from [b7e93bd479] to [997c31a255].

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
<table class="list multi">
	<thead>
		<tr>
			<td class="num">N°</td>
			<td>Pièce comptable</td>
			<td>Date</td>
			<th>Libellé</th>

			<td>Comptes</td>
			<td class="money">Débit</td>
			<td class="money">Crédit</td>
			<td>Libellé ligne</td>
			<td>Réf. ligne</td>
			{if !empty($action)}<td></td>{/if}
		</tr>
	</thead>
	{foreach from=$journal item="transaction"}
	<tbody>
		<tr>
			<td rowspan="{$transaction.lines|count}" class="num">{if $transaction.id}<a href="{$admin_url}acc/transactions/details.php?id={$transaction.id}">#{$transaction.id}</a>{/if}</td>
			<td rowspan="{$transaction.lines|count}">{$transaction.reference}</td>
			<td rowspan="{$transaction.lines|count}">{$transaction.date|date_short}</td>
			<th rowspan="{$transaction.lines|count}">{$transaction.label}</th>

		{foreach from=$transaction.lines key="k" item="line"}
			<td>{$line.account_code} - {$line.account_label}</td>
			<td class="money">{$line.debit|raw|money}</td>
			<td class="money">{$line.credit|raw|money}</td>
			<td>{$line.label}</td>
			<td>{$line.reference}</td>
			{if !empty($action) && $k == 0}







>















>







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
<table class="list multi">
	<thead>
		<tr>
			<td class="num">N°</td>
			<td>Pièce comptable</td>
			<td>Date</td>
			<th>Libellé</th>
			{if !empty($with_linked_users)}<td>Membres liés</td>{/if}
			<td>Comptes</td>
			<td class="money">Débit</td>
			<td class="money">Crédit</td>
			<td>Libellé ligne</td>
			<td>Réf. ligne</td>
			{if !empty($action)}<td></td>{/if}
		</tr>
	</thead>
	{foreach from=$journal item="transaction"}
	<tbody>
		<tr>
			<td rowspan="{$transaction.lines|count}" class="num">{if $transaction.id}<a href="{$admin_url}acc/transactions/details.php?id={$transaction.id}">#{$transaction.id}</a>{/if}</td>
			<td rowspan="{$transaction.lines|count}">{$transaction.reference}</td>
			<td rowspan="{$transaction.lines|count}">{$transaction.date|date_short}</td>
			<th rowspan="{$transaction.lines|count}">{$transaction.label}</th>
			{if !empty($with_linked_users)}<td rowspan="{$transaction.lines|count}">{$transaction.linked_users}</td>{/if}
		{foreach from=$transaction.lines key="k" item="line"}
			<td>{$line.account_code} - {$line.account_label}</td>
			<td class="money">{$line.debit|raw|money}</td>
			<td class="money">{$line.credit|raw|money}</td>
			<td>{$line.label}</td>
			<td>{$line.reference}</td>
			{if !empty($action) && $k == 0}

Modified src/www/admin/acc/years/import.php from [184319b35e] to [0bba8e1c43].

42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
	unset($params['type']);
	Utils::redirect(Utils::getSelfURI($params));
}

if ($type && $type_name) {
	$columns = Export::COLUMNS[$type];

	// Remove linked users from import
	if ($found = array_search('linked_users', $columns)) {
		unset($columns[$found]);
	}

	// Remove NULLs
	$columns = array_filter($columns);
	$columns_table = $columns = array_flip($columns);

	if ($type == Export::FEC) {
		// Fill with labels
		$columns_table = array_intersect_key(array_flip(Export::COLUMNS_FULL), $columns);







<
<
<
<
<







42
43
44
45
46
47
48





49
50
51
52
53
54
55
	unset($params['type']);
	Utils::redirect(Utils::getSelfURI($params));
}

if ($type && $type_name) {
	$columns = Export::COLUMNS[$type];






	// Remove NULLs
	$columns = array_filter($columns);
	$columns_table = $columns = array_flip($columns);

	if ($type == Export::FEC) {
		// Fill with labels
		$columns_table = array_intersect_key(array_flip(Export::COLUMNS_FULL), $columns);
108
109
110
111
112
113
114

115
116
117
	],
	Export::FEC => [
		'label' => 'FEC (Fichier des Écritures Comptables)',
		'help' => 'Format standard de l\'administration française.',
	],
];


$tpl->assign(compact('csv', 'year', 'csrf_key', 'examples', 'type', 'type_name', 'ignore_ids', 'types', 'report'));

$tpl->display('acc/years/import.tpl');







>



103
104
105
106
107
108
109
110
111
112
113
	],
	Export::FEC => [
		'label' => 'FEC (Fichier des Écritures Comptables)',
		'help' => 'Format standard de l\'administration française.',
	],
];

$tpl->assign('with_linked_users', in_array('linked_users', $csv->getTranslationTable()));
$tpl->assign(compact('csv', 'year', 'csrf_key', 'examples', 'type', 'type_name', 'ignore_ids', 'types', 'report'));

$tpl->display('acc/years/import.tpl');