Comment: | Make project code optional |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | accounts_refactor |
Files: | files | file ages | folders |
SHA3-256: |
e65607c51e34b2ae6121344c63983e9b |
User & Date: | bohwaz on 2022-11-05 18:56:33 |
Other Links: | branch diff | manifest | tags |
2022-11-05
| ||
19:18 | Change to 1.2.0 as DB schema changes are major check-in: 7b53c797b3 user: bohwaz tags: accounts_refactor | |
18:56 | Make project code optional check-in: e65607c51e user: bohwaz tags: accounts_refactor | |
16:52 | Pre-select asset or liability, to get a sensible default check-in: 91144d5c10 user: bohwaz tags: accounts_refactor | |
Modified src/include/lib/Garradin/Accounting/Export.php from [3f68d2b228] to [25d46a7282].
︙ | ︙ | |||
156 157 158 159 160 161 162 | if (self::SIMPLE == $type) { $sql = 'SELECT t.id, t.type, t.status, t.label, t.date, t.notes, t.reference, l1.reference AS p_reference, a1.code AS debit_account, a2.code AS credit_account, l1.debit AS amount, | | | 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 | if (self::SIMPLE == $type) { $sql = 'SELECT t.id, t.type, t.status, t.label, t.date, t.notes, t.reference, l1.reference AS p_reference, a1.code AS debit_account, a2.code AS credit_account, l1.debit AS amount, IFNULL(p.code, p.label) AS project, GROUP_CONCAT(u.%s) AS linked_users FROM acc_transactions t INNER JOIN acc_transactions_lines l1 ON l1.id_transaction = t.id AND l1.debit != 0 INNER JOIN acc_transactions_lines l2 ON l2.id_transaction = t.id AND l2.credit != 0 INNER JOIN acc_accounts a1 ON a1.id = l1.id_account INNER JOIN acc_accounts a2 ON a2.id = l2.id_account LEFT JOIN acc_projects p ON p.id = l1.id_project |
︙ | ︙ | |||
203 204 205 206 207 208 209 | GROUP BY t.id, l.id ORDER BY t.date, t.id, l.id;'; } elseif (self::FULL == $type || self::GROUPED == $type) { $sql = 'SELECT t.id, t.type, t.status, t.label, t.date, t.notes, t.reference, a.code AS account, a.label AS account_label, l.debit AS debit, l.credit AS credit, l.reference AS line_reference, l.label AS line_label, l.reconciled, | | | | 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 | GROUP BY t.id, l.id ORDER BY t.date, t.id, l.id;'; } elseif (self::FULL == $type || self::GROUPED == $type) { $sql = 'SELECT t.id, t.type, t.status, t.label, t.date, t.notes, t.reference, a.code AS account, a.label AS account_label, l.debit AS debit, l.credit AS credit, l.reference AS line_reference, l.label AS line_label, l.reconciled, IFNULL(p.code, p.label) AS project, GROUP_CONCAT(u.%s) AS linked_users FROM acc_transactions t INNER JOIN acc_transactions_lines l ON l.id_transaction = t.id INNER JOIN acc_accounts a ON a.id = l.id_account LEFT JOIN acc_projects p ON p.id = l.id_project LEFT JOIN acc_transactions_users tu ON tu.id_transaction = t.id LEFT JOIN membres u ON u.id = tu.id_user WHERE t.id_year = ? GROUP BY t.id, l.id ORDER BY t.date, t.id, l.id;'; $sql = sprintf($sql, $id_field); |
︙ | ︙ |
Modified src/include/lib/Garradin/Accounting/Import.php from [4ea5db04e0] to [9d866da3fa].
︙ | ︙ | |||
181 182 183 184 185 186 187 | $transaction->status = $status; } } $data = []; if (!empty($row->project)) { | | | 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 | $transaction->status = $status; } } $data = []; if (!empty($row->project)) { $id_project = Projects::getIdFromCodeOrLabel($row->project); if (!$id_project) { throw new UserException(sprintf('le projet analytique "%s" n\'existe pas', $row->project)); } $data['id_project'] = $id_project; } |
︙ | ︙ |
Modified src/include/lib/Garradin/Accounting/Projects.php from [0f5607b129] to [4cac127cae].
︙ | ︙ | |||
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | class Projects { static public function get(int $id): ?Project { return EntityManager::findOneById(Project::class, $id); } static public function count(): int { return DB::getInstance()->count(Project::TABLE); } static public function listAssoc(): array { $em = EntityManager::getInstance(Project::class); | > > > > > | | | | | | | 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 | class Projects { static public function get(int $id): ?Project { return EntityManager::findOneById(Project::class, $id); } static public function getIdFromCodeOrLabel(string $str): ?int { return DB::getInstance()->firstColumn('SELECT id FROM acc_projects WHERE code = ? OR label = ?;', $str, $str) ?: null; } static public function count(): int { return DB::getInstance()->count(Project::TABLE); } static public function listAssoc(): array { $em = EntityManager::getInstance(Project::class); $sql = $em->formatQuery('SELECT id, CASE WHEN code IS NOT NULL THEN code || \' — \' || label ELSE label END FROM @TABLE WHERE archived = 0 ORDER BY code COLLATE NOCASE, label COLLATE U_NOCASE;'); return $em->DB()->getAssoc($sql); } static public function listAssocWithEmpty(): array { return ['' => '-- Aucun'] + self::listAssoc(); } /** * Return account balances per year or per project * @param bool $by_year If true will return projects grouped by year, if false it will return years grouped by project */ static public function getBalances(bool $by_year = false): \Generator { $join = $by_year ? 'INNER' : 'LEFT'; $sql = 'SELECT p.label AS project_label, p.description AS project_description, p.id AS id_project, p.code AS project_code, p.archived, p.id AS project_id, y.id AS id_year, y.label AS year_label, y.start_date, y.end_date, SUM(l.credit - l.debit) AS sum, SUM(l.credit) AS credit, SUM(l.debit) AS debit, 0 AS total, (SELECT SUM(l2.credit - l2.debit) FROM acc_transactions_lines l2 INNER JOIN acc_transactions t2 ON t2.id = l2.id_transaction INNER JOIN acc_accounts a2 ON a2.id = l2.id_account WHERE a2.position = %d AND l2.id_project = l.id_project AND t2.id_year = t.id_year) * -1 AS sum_expense, (SELECT SUM(l2.credit - l2.debit) FROM acc_transactions_lines l2 INNER JOIN acc_transactions t2 ON t2.id = l2.id_transaction INNER JOIN acc_accounts a2 ON a2.id = l2.id_account WHERE a2.position = %d AND l2.id_project = l.id_project AND t2.id_year = t.id_year) AS sum_revenue FROM acc_projects p %s JOIN acc_transactions_lines l ON p.id = l.id_project %3$s JOIN acc_transactions t ON t.id = l.id_transaction %3$s JOIN acc_years y ON y.id = t.id_year GROUP BY %s ORDER BY p.archived, %s;'; $order = 'p.code COLLATE NOCASE, p.label COLLATE U_NOCASE'; if ($by_year) { $group = 'y.id, p.id'; $order = 'y.start_date DESC, ' . $order; } else { $group = 'p.id, y.id'; $order = $order . ', y.id'; } $sql = sprintf($sql, Account::EXPENSE, Account::REVENUE, $join, $group, $order); $current = null; |
︙ | ︙ | |||
88 89 90 91 92 93 94 | $out->{$s} = $current->{$s}; } return $out; }; foreach (DB::getInstance()->iterate($sql) as $row) { | | | | | | | 93 94 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 | $out->{$s} = $current->{$s}; } return $out; }; foreach (DB::getInstance()->iterate($sql) as $row) { $id = $by_year ? $row->id_year : $row->project_id; if (null !== $current && $current->selector !== $id) { if (count($current->items)) { $current->items[] = $total($current, $by_year); } yield $current; $current = null; } if (null === $current) { $current = (object) [ 'selector' => $id, 'id' => $by_year ? $row->id_year : $row->id_project, 'label' => $by_year ? $row->year_label : ($row->project_code ? $row->project_code . ' — ' : '') . $row->project_label, 'description' => !$by_year ? $row->project_description : null, 'archived' => !$by_year ? $row->archived : 0, 'items' => [], ]; foreach ($sums as $s) { $current->$s = 0; } } if (null === $row->sum) { continue; } $row->label = !$by_year ? $row->year_label : ($row->project_code ? $row->project_code . ' — ' : '') . $row->project_label; $current->items[] = $row; foreach ($sums as $s) { $current->$s += $row->$s; } } if ($current === null) { return; } if (count($current->items)) { $current->items[] = $total($current, $by_year); } yield $current; } } |
Modified src/include/lib/Garradin/Accounting/Transactions.php from [c25dd99019] to [1dee9d435c].
︙ | ︙ | |||
122 123 124 125 126 127 128 | $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); $columns['change']['label'] = 'Montant'; | | | 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 | $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); $columns['change']['label'] = 'Montant'; $columns['project_code']['select'] = 'GROUP_CONCAT(IFNULL(b.code, SUBSTR(b.label, 1, 10) || \'…\'), \',\')'; $columns['id_project']['select'] = 'GROUP_CONCAT(l.id_project, \',\')'; if (!$type) { $columns = ['type_label' => [ 'select' => 't.type', 'label' => 'Type d\'écriture', ]] |
︙ | ︙ |
Modified src/include/lib/Garradin/Entities/Accounting/Account.php from [1caf979290] to [2fd9aca932].
︙ | ︙ | |||
221 222 223 224 225 226 227 | 'label' => 'Réf. ligne', 'select' => 'l.reference', ], 'id_project' => [ 'select' => 'l.id_project', ], 'project_code' => [ | | | 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 | 'label' => 'Réf. ligne', 'select' => 'l.reference', ], 'id_project' => [ 'select' => 'l.id_project', ], 'project_code' => [ 'select' => 'IFNULL(p.code, SUBSTR(p.label, 1, 10) || \'…\')', ], 'projects' => [ 'label' => 'Projet', 'select' => null, ], 'status' => [ 'select' => 't.status', |
︙ | ︙ |
Modified src/include/lib/Garradin/Entities/Accounting/Project.php from [8fed002baa] to [7fb20ef642].
︙ | ︙ | |||
9 10 11 12 13 14 15 | * Analytical projects */ class Project extends Entity { const TABLE = 'acc_projects'; protected ?int $id; | | > | | | > > > > > > > > > > < < < < < < < < | 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 | * Analytical projects */ class Project extends Entity { const TABLE = 'acc_projects'; protected ?int $id; protected ?string $code; protected string $label; protected ?string $description; protected bool $archived = false; protected $_position = []; public function selfCheck(): void { if (null !== $this->code) { $this->assert(trim($this->code) !== '', 'Le numéro de projet est invalide.'); $this->assert(strlen($this->code) <= 100, 'Le numéro de projet est trop long.'); $this->assert(preg_match('/^[A-Z0-9_]+$/', $this->code), 'Le numéro de projet ne peut comporter que des lettres majuscules et des chiffres.'); $db = DB::getInstance(); if ($this->exists()) { $this->assert(!$db->test(self::TABLE, 'code = ? AND id != ?', $this->code, $this->id()), 'Ce code est déjà utilisé par un autre projet.'); } else { $this->assert(!$db->test(self::TABLE, 'code = ?', $this->code), 'Ce code est déjà utilisé par un autre projet.'); } } $this->assert(trim($this->label) !== '', 'L\'intitulé de projet ne peut rester vide.'); $this->assert(strlen($this->label) <= 200, 'L\'intitulé de compte ne peut faire plus de 200 caractères.'); if (null !== $this->description) { $this->assert(trim($this->description) !== '', 'L\'intitulé de projet est invalide.'); $this->assert(strlen($this->description) <= 2000, 'L\'intitulé de compte ne peut faire plus de 2000 caractères.'); } parent::selfCheck(); } public function importForm(?array $source = null) { if (null === $source) { |
︙ | ︙ |
Modified src/include/migrations/1.1/32.sql from [a7eb3b4440] to [d4341c3278].
︙ | ︙ | |||
25 26 27 28 29 30 31 | SET id_project = (SELECT b.id FROM acc_projects AS b INNER JOIN acc_accounts_old c ON c.code = b.code WHERE c.id = a.id_project) WHERE id_project IS NOT NULL; -- Remove first 99 and 9 from code (added in 1.1.30) UPDATE acc_projects SET code = CASE WHEN SUBSTR(code, 1, 2) = '99' AND LENGTH(code) > 2 THEN SUBSTR(code, 3) ELSE code END; UPDATE acc_projects SET code = CASE WHEN SUBSTR(code, 1, 1) = '9' AND LENGTH(code) > 1 THEN SUBSTR(code, 2) ELSE code END; | | < | 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | SET id_project = (SELECT b.id FROM acc_projects AS b INNER JOIN acc_accounts_old c ON c.code = b.code WHERE c.id = a.id_project) WHERE id_project IS NOT NULL; -- Remove first 99 and 9 from code (added in 1.1.30) UPDATE acc_projects SET code = CASE WHEN SUBSTR(code, 1, 2) = '99' AND LENGTH(code) > 2 THEN SUBSTR(code, 3) ELSE code END; UPDATE acc_projects SET code = CASE WHEN SUBSTR(code, 1, 1) = '9' AND LENGTH(code) > 1 THEN SUBSTR(code, 2) ELSE code END; UPDATE acc_transactions_lines SET id_project = NULL WHERE id_project NOT IN (SELECT id FROM acc_projects); INSERT INTO acc_accounts SELECT *, CASE WHEN type > 0 AND type <= 8 THEN 1 ELSE 0 END FROM acc_accounts_old; -- Delete old analytical accounts DELETE FROM acc_accounts AS a WHERE type = 7 OR (id_chart IN (SELECT id FROM acc_charts WHERE country = 'FR') AND type != 7 |
︙ | ︙ |
Modified src/templates/acc/charts/accounts/_account_form.tpl from [776700b635] to [6a136bee7d].
︙ | ︙ | |||
11 12 13 14 15 16 17 | {input type="radio" label="Bilan : actif" name="position" value=Entities\Accounting\Account::ASSET source=$account help="ce que possède l'association : stocks, locaux, soldes bancaires, etc."} {input type="radio" label="Bilan : passif" name="position" value=Entities\Accounting\Account::LIABILITY source=$account help="ce que l'association doit : dettes, provisions, réserves, etc."} {input type="radio" label="Bilan : actif ou passif" name="position" value=Entities\Accounting\Account::ASSET_OR_LIABILITY source=$account help="le compte sera placé à l'actif si son solde est débiteur, ou au passif s'il est créditeur"} {input type="radio" label="Résultat : charge" name="position" value=Entities\Accounting\Account::EXPENSE source=$account help="dépenses"} {input type="radio" label="Résultat : produit" name="position" value=Entities\Accounting\Account::REVENUE source=$account help="recettes"} {elseif $account->canSetAssetOrLiabilityPosition()} <dt><label for="f_position_0">Position au bilan</label> <b>(obligatoire)</b></dt> | | > > | | < | 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | {input type="radio" label="Bilan : actif" name="position" value=Entities\Accounting\Account::ASSET source=$account help="ce que possède l'association : stocks, locaux, soldes bancaires, etc."} {input type="radio" label="Bilan : passif" name="position" value=Entities\Accounting\Account::LIABILITY source=$account help="ce que l'association doit : dettes, provisions, réserves, etc."} {input type="radio" label="Bilan : actif ou passif" name="position" value=Entities\Accounting\Account::ASSET_OR_LIABILITY source=$account help="le compte sera placé à l'actif si son solde est débiteur, ou au passif s'il est créditeur"} {input type="radio" label="Résultat : charge" name="position" value=Entities\Accounting\Account::EXPENSE source=$account help="dépenses"} {input type="radio" label="Résultat : produit" name="position" value=Entities\Accounting\Account::REVENUE source=$account help="recettes"} {elseif $account->canSetAssetOrLiabilityPosition()} <dt><label for="f_position_0">Position au bilan</label> <b>(obligatoire)</b></dt> <dd class="help">La position permet d'indiquer dans quelle partie du bilan doit figurer le compte.<br /> En cas de doute, sélectionner <em>« Actif ou passif »</em>.</dd> {input type="radio" label="Actif ou passif" name="position" value=Entities\Accounting\Account::ASSET_OR_LIABILITY source=$account help="le compte sera automatiquement placé à l'actif si son solde est débiteur, ou au passif si le solde est créditeur"} {input type="radio" label="Actif" name="position" value=Entities\Accounting\Account::ASSET source=$account help="ce que possède l'association : stocks, locaux, soldes bancaires, etc."} {input type="radio" label="Passif" name="position" value=Entities\Accounting\Account::LIABILITY source=$account help="ce que l'association doit : dettes, provisions, réserves, etc."} {elseif $account->exists()} <dt>Position du compte</dt> <dd> {if $account.position == $account::EXPENSE || $account.position == $account::REVENUE}Au compte de résultat{else}Au bilan{/if} — {$account->position_name()} </dd> |
︙ | ︙ |
Modified src/templates/acc/projects/edit.tpl from [2f87196e30] to [7f41e25ace].
1 2 3 4 5 6 7 8 9 10 11 | {include file="admin/_head.tpl" title="Projet" current="acc/years"} {include file="./_nav.tpl" current=null} {form_errors} <form method="post" action="" data-focus="1"> <fieldset> <legend>{if $project->exists()}Modifier un projet{else}Créer un projet{/if}</legend> <dl> {input type="text" required=true name="label" label="Libellé du projet" source=$project} | | | | 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 | {include file="admin/_head.tpl" title="Projet" current="acc/years"} {include file="./_nav.tpl" current=null} {form_errors} <form method="post" action="" data-focus="1"> <fieldset> <legend>{if $project->exists()}Modifier un projet{else}Créer un projet{/if}</legend> <dl> {input type="text" required=true name="label" label="Libellé du projet" source=$project} {input type="text" name="code" label="Code du projet" source=$project help="Utile pour retrouver le projet rapidement. Ne peut contenir que des chiffres et des lettres majuscules." pattern="[0-9A-Z_]+"} {input type="textarea" name="description" label="Description du projet" source=$project} <dt>Archivage</dt> {input type="checkbox" name="archived" label="Archiver ce projet" value=1 source=$project help="Si coché, ce projet ne sera plus proposé dans la sélection de projets lors de la saisie d'une écriture."} </dl> </fieldset> <p class="submit"> {csrf_field key=$csrf_key} {button type="submit" name="save" label="Enregistrer" shape="right" class="main"} </p> </form> {include file="admin/_foot.tpl"} |
Modified src/templates/acc/projects/index.tpl from [abe82b5498] to [682d2deb12].
1 2 3 4 5 6 7 8 9 10 | {include file="admin/_head.tpl" title="Projets" current="acc/years"} {include file="./_nav.tpl" current=$current} <div class="year-header"> <h2>{$config.nom_asso} — Projets</h2> {if $projects_count} <p class="noprint"> {if $by_year} | | | < < < < < | | 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 | {include file="admin/_head.tpl" title="Projets" current="acc/years"} {include file="./_nav.tpl" current=$current} <div class="year-header"> <h2>{$config.nom_asso} — Projets</h2> {if $projects_count} <p class="noprint"> {if $by_year} {linkbutton href="./" label="Grouper par projet" shape="left"} {else} {linkbutton href="?by_year=1" label="Grouper par exercice" shape="right"} {/if} </p> <p class="noprint print-btn"> <button onclick="window.print(); return false;" class="icn-btn" data-icon="⎙">Imprimer</button> {linkbutton shape="download" href="%s?by_year=%d&_pdf"|args:$self_url_no_qs,$by_year label="Télécharger en PDF"} </p> {/if} </div> {if $projects_count} |
︙ | ︙ |
Modified src/templates/acc/reports/_header.tpl from [b78a78fe5a] to [bebaa864d9].
︙ | ︙ | |||
23 24 25 26 27 28 29 | <li{if $sub_current != 'simple'} class="current"{/if}>{link href="?%s&simple=0"|args:$criterias_query_no_compare label="Vue comptable"}</li> </ul> {/if} </nav> <h2>{$config.nom_asso} — {$title}</h2> {if isset($project)} | | | 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | <li{if $sub_current != 'simple'} class="current"{/if}>{link href="?%s&simple=0"|args:$criterias_query_no_compare label="Vue comptable"}</li> </ul> {/if} </nav> <h2>{$config.nom_asso} — {$title}</h2> {if isset($project)} <h3>Projet : {if $project.code}{$project.code} — {/if}{$project.label}{if $project.archived} <em>(archivé)</em>{/if}</h3> {/if} {if isset($year)} <p>Exercice : {$year.label} ({if $year.closed}clôturé{else}en cours{/if}, du {$year.start_date|date_short} au {$year.end_date|date_short}, généré le {$close_date|date_short})</p> {/if} {if !empty($allow_compare) && !empty($other_years) && empty($criterias['project'])} |
︙ | ︙ |