68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
-- Recettes
UPDATE acc_transactions SET type = 1 WHERE id IN (SELECT id FROM compta_journal WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = 1));
-- Dépenses
UPDATE acc_transactions SET type = 2 WHERE id IN (SELECT id FROM compta_journal WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = -1));
-- 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(REPLACE(montant * 100, '.0', '') 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)
|
>
>
>
>
>
>
>
>
>
>
>
|
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
|
-- Recettes
UPDATE acc_transactions SET type = 1 WHERE id IN (SELECT id FROM compta_journal WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = 1));
-- Dépenses
UPDATE acc_transactions SET type = 2 WHERE id IN (SELECT id FROM compta_journal WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = -1));
-- Virements
UPDATE acc_transactions SET type = 3 WHERE id IN (SELECT id FROM compta_journal
WHERE (compte_credit IN ('530', '5112', '5115') OR compte_credit LIKE '512%')
AND (compte_debit IN ('530', '5112', '5115') OR compte_debit LIKE '512%'));
-- Dettes
UPDATE acc_transactions SET type = 4 WHERE id IN (SELECT id FROM compta_journal WHERE compte_debit LIKE '6%' AND compte_credit LIKE '4%');
-- Créances
UPDATE acc_transactions SET type = 5 WHERE id IN (SELECT id FROM compta_journal WHERE compte_credit LIKE '7%' AND compte_debit LIKE '4%');
-- 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(REPLACE(montant * 100, '.0', '') 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)
|