Overview
Comment: | Ajout types dépense/recette, renommer "reconcilied" vers "reconciled" (typo) |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | dev |
Files: | files | file ages | folders |
SHA1: |
21753844d4b793f12a1e6a11b33aec44 |
User & Date: | bohwaz on 2020-09-04 22:20:14 |
Other Links: | branch diff | manifest | tags |
Context
2020-09-04
| ||
22:20 | Liste simplifiée des comptes check-in: 4463ba110d user: bohwaz tags: dev | |
22:20 | Ajout types dépense/recette, renommer "reconcilied" vers "reconciled" (typo) check-in: 21753844d4 user: bohwaz tags: dev | |
2020-09-03
| ||
17:45 | Ajout comptes de type tiers check-in: c546c6f556 user: bohwaz tags: dev | |
Changes
Modified src/include/data/1.0.0_migration.sql from [eac19def50] to [057751f805].
︙ | ︙ | |||
16 17 18 19 20 21 22 | --.read plan_comptable_2020.sql -- Migration comptes de code comme identifiant à ID unique INSERT INTO acc_accounts (id, id_chart, code, label, position, user) SELECT NULL, 1, id, libelle, position, CASE WHEN plan_comptable = 1 THEN 0 ELSE 1 END FROM compta_comptes; -- Migrations projets vers comptes analytiques | | | | | | | | > > | | < | | > > | | > > | | > > > | 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 | --.read plan_comptable_2020.sql -- Migration comptes de code comme identifiant à ID unique INSERT INTO acc_accounts (id, id_chart, code, label, position, user) SELECT NULL, 1, id, libelle, position, CASE WHEN plan_comptable = 1 THEN 0 ELSE 1 END FROM compta_comptes; -- Migrations projets vers comptes analytiques INSERT INTO acc_accounts (id_chart, code, label, position, user, type, type_parent) VALUES (1, '99', 'Projets', 0, 1, 6, 1); INSERT INTO acc_accounts (id_chart, code, label, position, user, type) SELECT 1, '99' || substr('0000' || id, -4), libelle, 0, 1, 6 FROM compta_projets; -- Suppression des positions "actif ou passif" et "charge ou produit" UPDATE acc_accounts SET position = 0 WHERE position = 3 OR position = 12; -- Modification des valeurs de la position (qui n'est plus un champ binaire) UPDATE acc_accounts SET position = 3 WHERE position = 4; UPDATE acc_accounts SET position = 4 WHERE position = 8; -- Migration comptes bancaires UPDATE acc_accounts SET type = 3 WHERE code IN (SELECT id FROM compta_comptes_bancaires); -- Caisse UPDATE acc_accounts SET type = 4 WHERE code = '530'; -- Chèques et carte à encaisser UPDATE acc_accounts SET type = 5 WHERE code = '5112' OR code = '5113'; -- Bénévolat en nature UPDATE acc_accounts SET type = 7 WHERE code = '870'; -- FIXME: ajout parents des types -- Recopie des mouvements INSERT INTO acc_transactions (id, label, notes, reference, date, id_year) SELECT id, libelle, remarques, numero_piece, date, id_exercice FROM compta_journal; -- Création des lignes associées aux mouvements INSERT INTO acc_transactions_lines (id_transaction, id_account, debit, credit, reference, id_analytical) SELECT id, (SELECT id FROM acc_accounts WHERE code = compte_credit), 0, CAST(montant * 100 AS INT), numero_cheque, CASE WHEN id_projet IS NOT NULL THEN (SELECT id FROM acc_accounts WHERE code = '99' || substr('0000' || id_projet, -4)) ELSE NULL END FROM compta_journal; INSERT INTO acc_transactions_lines (id_transaction, id_account, debit, credit, reference, id_analytical) SELECT id, (SELECT id FROM acc_accounts WHERE code = compte_debit), CAST(montant * 100 AS INT), 0, numero_cheque, CASE WHEN id_projet IS NOT NULL THEN (SELECT id FROM acc_accounts WHERE code = '99' || substr('0000' || id_projet, -4)) ELSE NULL END FROM compta_journal; -- Recopie des descriptions de catégories dans la table des comptes, et mise des comptes en signets UPDATE acc_accounts SET type = 1, 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); UPDATE acc_accounts SET type = 2, 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); -- Recopie des opérations, mais le nom a changé pour "mouvements" INSERT INTO membres_mouvements SELECT * FROM membres_operations_old; -- FIXME: ajout d'entrées dans le le log utilisateur à partir de id_auteur |
︙ | ︙ | |||
79 80 81 82 83 84 85 | DROP TABLE compta_journal; DROP TABLE compta_categories; DROP TABLE compta_comptes; DROP TABLE compta_exercices; DROP TABLE membres_operations_old; -- Transfert des rapprochements | | | 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | DROP TABLE compta_journal; DROP TABLE compta_categories; DROP TABLE compta_comptes; DROP TABLE compta_exercices; DROP TABLE membres_operations_old; -- Transfert des rapprochements UPDATE acc_transactions_lines SET reconciled = 1 WHERE id_transaction IN (SELECT id_operation FROM compta_rapprochement); -- Suppression de la table rapprochements DROP TABLE compta_rapprochement; -- Suppression inutilisées DROP TABLE compta_projets; DROP TABLE compta_comptes_bancaires; DROP TABLE compta_moyens_paiement; |
Modified src/include/data/1.0.0_schema.sql from [37d671a7d2] to [e01cddf2b1].
︙ | ︙ | |||
199 200 201 202 203 204 205 206 207 208 209 210 211 212 | code TEXT NOT NULL, -- peut contenir des lettres, eg. 53A, 53B, etc. label TEXT NOT NULL, description TEXT NULL, position INTEGER NOT NULL, -- position actif/passif/charge/produit type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, favori, etc. user INTEGER NOT NULL DEFAULT 1 -- 1 = fait partie du plan comptable original, 0 = a été ajouté par l'utilisateur ); CREATE UNIQUE INDEX IF NOT EXISTS acc_accounts_codes ON acc_accounts (code, id_chart); CREATE TABLE IF NOT EXISTS acc_years -- Exercices | > | 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 | code TEXT NOT NULL, -- peut contenir des lettres, eg. 53A, 53B, etc. label TEXT NOT NULL, description TEXT NULL, position INTEGER NOT NULL, -- position actif/passif/charge/produit type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, favori, etc. type_parent INTEGER NOT NULL DEFAULT 0, -- Parent d'un type (eg. code = 53 aura comme type = banque et type_parent = 1 pour indiquer que c'est sous ce compte que doivent être créés les sous-comptes banque) user INTEGER NOT NULL DEFAULT 1 -- 1 = fait partie du plan comptable original, 0 = a été ajouté par l'utilisateur ); CREATE UNIQUE INDEX IF NOT EXISTS acc_accounts_codes ON acc_accounts (code, id_chart); CREATE TABLE IF NOT EXISTS acc_years -- Exercices |
︙ | ︙ | |||
235 236 237 238 239 240 241 | date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date), validated INTEGER NOT NULL DEFAULT 0, -- 1 = écriture validée, non modifiable hash TEXT NULL, prev_hash TEXT NULL, | | < | > > | 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 265 266 267 268 269 270 271 272 | date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date), validated INTEGER NOT NULL DEFAULT 0, -- 1 = écriture validée, non modifiable hash TEXT NULL, prev_hash TEXT NULL, id_year INTEGER NOT NULL REFERENCES acc_years(id) ); CREATE INDEX IF NOT EXISTS acc_transactions_year ON acc_transactions (id_year); CREATE INDEX IF NOT EXISTS acc_transactions_date ON acc_transactions (date); CREATE TABLE IF NOT EXISTS acc_transactions_lines -- Lignes d'écritures d'une opération ( id INTEGER PRIMARY KEY NOT NULL, id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE, id_account INTEGER NOT NULL REFERENCES acc_accounts (id), -- N° du compte dans le plan comptable credit INTEGER NOT NULL, debit INTEGER NOT NULL, reference TEXT NULL, -- Référence de paiement, eg. numéro de chèque label TEXT NULL, reconciled INTEGER NOT NULL DEFAULT 0, id_analytical INTEGER NULL REFERENCES acc_accounts(id) ON DELETE SET NULL, CONSTRAINT line_check1 CHECK ((credit * debit) = 0), CONSTRAINT line_check2 CHECK ((credit + debit) > 0) ); CREATE INDEX IF NOT EXISTS acc_transactions_lines_account ON acc_transactions_lines (id_account); |
︙ | ︙ |
Added src/include/lib/Garradin/Accounting/Accounts.php version [93c2de540c].
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | <?php namespace Garradin\Accounting; use Garradin\Entities\Accounting\Account; use Garradin\Utils; use Garradin\DB; use KD2\DB\EntityManager; class Accounts { protected $chart_id; protected $em; public function __construct(int $chart_id) { $this->chart_id = $chart_id; $this->em = EntityManager::getInstance(Account::class); } /** * Return common accounting accounts from current chart * (will not return analytical and volunteering accounts) */ public function listCommonTypes(): array { return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? AND type != 0 AND type NOT IN (?, ?) ORDER BY code COLLATE NOCASE;', $this->chart_id, Account::TYPE_ANALYTICAL, Account::TYPE_VOLUNTEERING); } /** * Return all accounts from current chart */ public function listAll(): array { return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? ORDER BY code COLLATE NOCASE;', $this->chart_id); } /** * Return only analytical accounts */ public function listAnalytical(): array { return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? AND type = ? ORDER BY code COLLATE NOCASE;', $this->chart_id, Account::TYPE_ANALYTICAL); } /** * Return only analytical accounts */ public function listVolunteering(): array { return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? AND type = ? ORDER BY code COLLATE NOCASE;', $this->chart_id, Account::TYPE_VOLUNTEERING); } /** * List common accounts, grouped by type * @return array */ public function listCommonGrouped(): array { $out = []; $query = $this->em->iterate('SELECT * FROM @TABLE WHERE id_chart = ? AND type != 0 AND type_parent = 0 ORDER BY type, code COLLATE NOCASE;', $this->chart_id); foreach ($query as $row) { $name = Account::TYPES_NAMES[$row->type]; if (!isset($out[$name])) { $out[$name] = []; } $out[$name][] = $row; } return $out; } public function getTypesParents(): array { return $this->em->DB->getAssoc($this->em->formatQuery('SELECT type, code FROM @TABLE WHERE type_parent = 1 ORDER BY type;')); } } |
Modified src/include/lib/Garradin/Entities/Accounting/Account.php from [55b3ff298e] to [3224d5aaad].
︙ | ︙ | |||
28 29 30 31 32 33 34 | 'Passif', 'Actif', 'Produit', 'Charge', ]; const TYPE_NONE = 0; | > > | | | | | < | < < > > < > > > > > > > | 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 91 92 93 94 95 96 97 | 'Passif', 'Actif', 'Produit', 'Charge', ]; const TYPE_NONE = 0; const TYPE_REVENUE = 1; const TYPE_EXPENSE = 2; const TYPE_BANK = 3; const TYPE_CASH = 4; /** * Outstanding transaction accounts (like cheque or card payments) */ const TYPE_OUTSTANDING = 5; const TYPE_ANALYTICAL = 6; const TYPE_VOLUNTEERING = 7; const TYPE_THIRD_PARTY = 8; const TYPES_NAMES = [ '', 'Recettes', 'Dépenses', 'Banque', 'Caisse', 'Attente d\'encaissement', 'Analytique', 'Bénévolat', 'Tiers', ]; protected $id; protected $id_chart; protected $code; protected $label; protected $description; protected $position; protected $type; /** * Parent of type (then type needs to be filled) * @var integer either 1 or 0 */ protected $type_parent; protected $user; protected $_types = [ 'id' => 'int', 'id_chart' => 'int', 'code' => 'string', 'label' => 'string', 'description' => '?string', 'position' => 'int', 'type' => 'int', 'type_parent' => 'int', 'user' => 'int', ]; protected $_validation_rules = [ 'id_chart' => 'required|integer|in_table:acc_charts,id', 'code' => 'required|string|alpha_num|max:10', 'label' => 'required|string|max:200', 'description' => 'string|max:2000', 'position' => 'required|integer', 'type' => 'required|integer', 'type_parent' => 'integer|min:0|max:1', 'user' => 'integer|min:0|max:1', ]; } |
Modified src/include/lib/Garradin/Entities/Accounting/Line.php from [3dc76458ae] to [b02c1d0334].
︙ | ︙ | |||
20 21 22 23 24 25 26 | protected $_types = [ 'id_transaction' => 'int', 'id_account' => 'int', 'credit' => 'int', 'debit' => 'int', 'reference' => '?string', 'label' => '?string', | | | | 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 | protected $_types = [ 'id_transaction' => 'int', 'id_account' => 'int', 'credit' => 'int', 'debit' => 'int', 'reference' => '?string', 'label' => '?string', 'reconciled' => 'int', ]; protected $_validation_rules = [ 'id_transaction' => 'required|integer|in_table:acc_transactions,id', 'id_account' => 'required|integer|in_table:acc_accounts,id', 'credit' => 'required|integer|min:0', 'debit' => 'required|integer|min:0', 'reference' => 'string|max:200', 'label' => 'string|max:200', 'reconciled' => 'int|min:0|max:1', ]; public function filterUserValue(string $key, $value, array $source) { $value = parent::filterUserValue($key, $value); if ($key == 'credit' || $key == 'debit') |
︙ | ︙ |