76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
|
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(REPLACE(montant * 100, '.0', '') 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
-- Revenus
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 AND c.type = 1);
-- Dépenses
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%');
-- 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 opérations, mais le nom a changé pour acc_transactions_users
INSERT INTO acc_transactions_users
|
>
|
|
|
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
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(REPLACE(montant * 100, '.0', '') 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
-- +Fix éventuels types qui ne correspondent pas à leur type… (@Fred C.) (... a.position = X)
-- Revenus
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 AND c.type = 1 AND a.position = 5);
-- Dépenses
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 opérations, mais le nom a changé pour acc_transactions_users
INSERT INTO acc_transactions_users
|