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: |
555019068fd0f39c672f6a76e03b5f82 |
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 | $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 | | | 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 | <?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 { | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | > | 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 | && empty($row->date) && empty($row->notes) && empty($row->reference) ); // New transaction, save previous one if (null !== $transaction && $has_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 | 'debit' => $row->amount, 'id_account' => $debit_account, ]); $transaction->addLine($l1); $transaction->addLine($l2); | | > | 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 | $line = new Line; $line->importForm($data); $transaction->addLine($line); } } 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 | { $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); } | | > > > > > > > > > > > > > > > > > | | | 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 | unset($params['type']); Utils::redirect(Utils::getSelfURI($params)); } if ($type && $type_name) { $columns = Export::COLUMNS[$type]; | < < < < < | 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'); |