Overview
Comment: | Fix migration from 0.9.8 when service duration was zero instead of NULL, and when some services were not added back |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk | stable |
Files: | files | file ages | folders |
SHA3-256: |
a22b11d69ca8ac3f89e92a9d56504d36 |
User & Date: | bohwaz on 2021-06-29 01:51:49 |
Other Links: | manifest | tags |
Context
2021-07-16
| ||
16:23 | Add year label to reports title check-in: 92074c0b13 user: bohwaz tags: trunk, stable | |
2021-06-29
| ||
01:51 | Fix migration from 0.9.8 when service duration was zero instead of NULL, and when some services were not added back check-in: a22b11d69c user: bohwaz tags: trunk, stable | |
2021-06-28
| ||
13:32 | Fix upgrade from 0.9.8 versions check-in: 6f3d72d02c user: bohwaz tags: trunk, stable | |
Changes
Modified src/include/data/1.0.0_migration.sql from [218d7afd21] to [931bc31a61].
︙ | ︙ | |||
106 107 108 109 110 111 112 | UPDATE acc_accounts SET type = 5, description = (SELECT description FROM compta_categories WHERE compte = acc_accounts.code) WHERE id IN (SELECT a.id FROM acc_accounts a INNER JOIN compta_categories c ON c.compte = a.code AND c.type = -1 AND c.compte NOT LIKE '4%' AND a.position = 4); -- Tiers UPDATE acc_accounts SET type = 4, description = (SELECT description FROM compta_categories WHERE compte = acc_accounts.code) WHERE id IN (SELECT a.id FROM acc_accounts a INNER JOIN compta_categories c ON c.compte = a.code AND c.type = -1 AND c.compte LIKE '4%'); | < < < < > | 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 | UPDATE acc_accounts SET type = 5, description = (SELECT description FROM compta_categories WHERE compte = acc_accounts.code) WHERE id IN (SELECT a.id FROM acc_accounts a INNER JOIN compta_categories c ON c.compte = a.code AND c.type = -1 AND c.compte NOT LIKE '4%' AND a.position = 4); -- Tiers UPDATE acc_accounts SET type = 4, description = (SELECT description FROM compta_categories WHERE compte = acc_accounts.code) WHERE id IN (SELECT a.id FROM acc_accounts a INNER JOIN compta_categories c ON c.compte = a.code AND c.type = -1 AND c.compte LIKE '4%'); -- Recopie des exercices, mais la date de fin ne peut être nulle INSERT INTO acc_years (id, label, start_date, end_date, closed, id_chart) SELECT id, libelle, debut, CASE WHEN fin IS NULL THEN date(debut, '+1 year') ELSE fin END, cloture, 1 FROM compta_exercices; -- Recopie des catégories, on supprime la colonne id_cotisation_obligatoire INSERT INTO membres_categories SELECT id, nom, droit_wiki, droit_membres, droit_compta, droit_inscription, droit_connexion, droit_config, cacher FROM membres_categories_old; DROP TABLE membres_categories_old; -- Transfert des rapprochements UPDATE acc_transactions_lines SET reconciled = 1 WHERE id_transaction IN (SELECT id_operation FROM compta_rapprochement); --------- MIGRATION COTISATIONS ---------- -- A edge-case where the end date is after the start date, let's fix it… UPDATE cotisations SET fin = debut WHERE fin < debut; UPDATE cotisations SET duree = NULL WHERE duree = 0; INSERT INTO services SELECT id, intitule, description, duree, debut, fin FROM cotisations; INSERT INTO services_fees (id, label, amount, id_service, id_account, id_year) SELECT id, intitule, CASE WHEN montant IS NOT NULL THEN CAST(montant*100 AS integer) ELSE NULL END, id, (SELECT id FROM acc_accounts WHERE code = (SELECT compte FROM compta_categories WHERE id = id_categorie_compta)), (SELECT MAX(id) FROM acc_years WHERE closed = 0) |
︙ | ︙ | |||
155 156 157 158 159 160 161 162 163 164 165 166 167 168 | INSERT INTO services_reminders SELECT * FROM rappels; INSERT INTO services_reminders_sent SELECT id, id_membre, id_cotisation, CASE WHEN id_rappel IS NULL THEN (SELECT MAX(id) FROM rappels) ELSE id_rappel END, date FROM rappels_envoyes WHERE id_rappel IS NOT NULL GROUP BY id_membre, id_cotisation, id_rappel; DROP TABLE cotisations; DROP TABLE cotisations_membres; DROP TABLE rappels; DROP TABLE rappels_envoyes; -- Suppression inutilisées | > > > > > > | 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 | INSERT INTO services_reminders SELECT * FROM rappels; INSERT INTO services_reminders_sent SELECT id, id_membre, id_cotisation, CASE WHEN id_rappel IS NULL THEN (SELECT MAX(id) FROM rappels) ELSE id_rappel END, date FROM rappels_envoyes WHERE id_rappel IS NOT NULL GROUP BY id_membre, id_cotisation, id_rappel; -- Recopie des opérations par membre, mais le nom a changé pour acc_transactions_users, et il faut valider l'existence du membre ET du service INSERT INTO acc_transactions_users SELECT a.* FROM membres_operations_old a INNER JOIN membres b ON b.id = a.id_membre INNER JOIN services_users c ON c.id = a.id_cotisation; DROP TABLE cotisations; DROP TABLE cotisations_membres; DROP TABLE rappels; DROP TABLE rappels_envoyes; -- Suppression inutilisées |
︙ | ︙ |