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: 21753844d4b793f12a1e6a11b33aec44b7547198
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
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
..
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
--.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)
	VALUES (1, '99', 'Projets', 0, 1, 0);

INSERT INTO acc_accounts (id_chart, code, label, position, user, type)
	SELECT 1, '99' || substr('0000' || id, -4), libelle, 0, 1, 4 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 = 1 WHERE code IN (SELECT id FROM compta_comptes_bancaires);

-- Caisse
UPDATE acc_accounts SET type = 2 WHERE code = '530';

-- Chèques et carte à encaisser
UPDATE acc_accounts SET type = 3 WHERE code = '5112' OR code = '5113';

-- Bénévolat en nature
UPDATE acc_accounts SET type = 5 WHERE code = '870';



-- Recopie des mouvements
INSERT INTO acc_transactions (id, label, notes, reference, date, id_year, id_analytical)
	SELECT id, libelle, remarques, numero_piece, date, id_exercice,
	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;

-- Création des lignes associées aux mouvements
INSERT INTO acc_transactions_lines (id_transaction, id_account, debit, credit, payment_reference)
	SELECT id, (SELECT id FROM acc_accounts WHERE code = compte_credit), 0, CAST(montant * 100 AS INT), numero_cheque FROM compta_journal;



INSERT INTO acc_transactions_lines (id_transaction, id_account, debit, credit, payment_reference)
	SELECT id, (SELECT id FROM acc_accounts WHERE code = compte_debit), CAST(montant * 100 AS INT), 0, numero_cheque 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 = 6, 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);




-- 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

................................................................................
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 reconcilied = 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;







|
|


|









|


|


|


|

>
>

|
|
<



|
|
>
>

|
|
>
>


|
|
>
>
>







 







|








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
..
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
--.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

................................................................................
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
...
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
...
256
257
258
259
260
261
262
263


264
265
266
267
268
269
270
    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
................................................................................
    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),
    id_analytical INTEGER NULL REFERENCES acc_accounts(id) ON DELETE SET NULL
);

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
................................................................................

    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,

    reconcilied INTEGER NOT NULL DEFAULT 0,



    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);








>







 







|
<







 







|
>
>







199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
...
236
237
238
239
240
241
242
243

244
245
246
247
248
249
250
...
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
    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
................................................................................
    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
................................................................................

    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


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
		'Passif',
		'Actif',
		'Produit',
		'Charge',
	];

	const TYPE_NONE = 0;


	const TYPE_BANK = 1;
	const TYPE_CASH = 2;

	/**
	 * Outstanding transaction accounts (like cheque or card payments)
	 */
	const TYPE_OUTSTANDING = 3;

	const TYPE_ANALYTICAL = 4;
	const TYPE_VOLUNTEERING = 5;

	const TYPE_THIRD_PARTY = 6;

	const TYPE_BOOKMARK = 7;

	const TYPES_NAMES = [
		'',


		'Banque',
		'Caisse',
		'Attente d\'encaissement',
		'Analytique',
		'Bénévolat',
		'Tiers',
		'Favori',
	];

	protected $id;
	protected $id_chart;
	protected $code;
	protected $label;
	protected $description;
	protected $position;
	protected $type;





	protected $user;

	protected $_types = [
		'id'          => 'int',
		'id_chart'    => 'int',
		'code'        => 'string',
		'label'       => 'string',
		'description' => '?string',
		'position'    => 'int',
		'type'        => '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',

		'user'        => 'integer|min:0|max:1',
	];
}







>
>
|
|




|

|
|
<
|
<
<



>
>






<









>
>
>
>
>










>










>



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
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',
		'reconcilied'    => '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',
		'reconcilied'    => 'int|min:0|max:1',
	];

	public function filterUserValue(string $key, $value, array $source)
	{
		$value = parent::filterUserValue($key, $value);

		if ($key == 'credit' || $key == 'debit')







|









|







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')