︙ | | | ︙ | |
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
|
}
unset($comptes);
$this->solderResultat($old_id, $date);
// Récupérer chacun des comptes de bilan et leurs soldes (uniquement les classes 1 à 5)
$statement = $db->query('SELECT compta_comptes.id AS compte, compta_comptes.position AS position,
ROUND(COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_debit = compta_comptes.id AND id_exercice = :id), 0), 2)
- ROUND(COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_credit = compta_comptes.id AND id_exercice = :id), 0), 2) AS solde
FROM compta_comptes
INNER JOIN compta_journal ON compta_comptes.id = compta_journal.compte_debit
OR compta_comptes.id = compta_journal.compte_credit
WHERE id_exercice = :id AND solde != 0 AND CAST(substr(compta_comptes.id, 1, 1) AS INTEGER) <= 5
GROUP BY compta_comptes.id;', ['id' => $old_id]);
|
|
|
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
|
}
unset($comptes);
$this->solderResultat($old_id, $date);
// Récupérer chacun des comptes de bilan et leurs soldes (uniquement les classes 1 à 5)
$statement = $db->preparedQuery('SELECT compta_comptes.id AS compte, compta_comptes.position AS position,
ROUND(COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_debit = compta_comptes.id AND id_exercice = :id), 0), 2)
- ROUND(COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_credit = compta_comptes.id AND id_exercice = :id), 0), 2) AS solde
FROM compta_comptes
INNER JOIN compta_journal ON compta_comptes.id = compta_journal.compte_debit
OR compta_comptes.id = compta_journal.compte_credit
WHERE id_exercice = :id AND solde != 0 AND CAST(substr(compta_comptes.id, 1, 1) AS INTEGER) <= 5
GROUP BY compta_comptes.id;', ['id' => $old_id]);
|
︙ | | | ︙ | |
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
|
}
public function getGrandLivre($exercice)
{
$db = DB::getInstance();
$livre = ['classes' => [], 'debit' => 0.0, 'credit' => 0.0];
$res = $db->query('SELECT compte FROM
(SELECT compte_debit AS compte FROM compta_journal
WHERE id_exercice = :exercice GROUP BY compte_debit
UNION
SELECT compte_credit AS compte FROM compta_journal
WHERE id_exercice = :exercice GROUP BY compte_credit)
ORDER BY compte ASC;', ['exercice' => (int) $exercice]);
|
|
|
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
|
}
public function getGrandLivre($exercice)
{
$db = DB::getInstance();
$livre = ['classes' => [], 'debit' => 0.0, 'credit' => 0.0];
$res = $db->preparedQuery('SELECT compte FROM
(SELECT compte_debit AS compte FROM compta_journal
WHERE id_exercice = :exercice GROUP BY compte_debit
UNION
SELECT compte_credit AS compte FROM compta_journal
WHERE id_exercice = :exercice GROUP BY compte_credit)
ORDER BY compte ASC;', ['exercice' => (int) $exercice]);
|
︙ | | | ︙ | |
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
|
{
$db = DB::getInstance();
$charges = ['comptes' => [], 'total' => 0.0];
$produits = ['comptes' => [], 'total' => 0.0];
$resultat = 0.0;
$res = $db->query('SELECT compte, SUM(debit), SUM(credit)
FROM
(SELECT compte_debit AS compte, SUM(montant) AS debit, 0 AS credit
FROM compta_journal WHERE id_exercice = :exercice GROUP BY compte_debit
UNION
SELECT compte_credit AS compte, 0 AS debit, SUM(montant) AS credit
FROM compta_journal WHERE id_exercice = :exercice GROUP BY compte_credit)
WHERE compte LIKE \'6%\' OR compte LIKE \'7%\'
|
|
|
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
|
{
$db = DB::getInstance();
$charges = ['comptes' => [], 'total' => 0.0];
$produits = ['comptes' => [], 'total' => 0.0];
$resultat = 0.0;
$res = $db->preparedQuery('SELECT compte, SUM(debit), SUM(credit)
FROM
(SELECT compte_debit AS compte, SUM(montant) AS debit, 0 AS credit
FROM compta_journal WHERE id_exercice = :exercice GROUP BY compte_debit
UNION
SELECT compte_credit AS compte, 0 AS debit, SUM(montant) AS credit
FROM compta_journal WHERE id_exercice = :exercice GROUP BY compte_credit)
WHERE compte LIKE \'6%\' OR compte LIKE \'7%\'
|
︙ | | | ︙ | |
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
|
];
$passif['total'] = $resultat['resultat'];
}
// Y'a sûrement moyen d'améliorer tout ça pour que le maximum de travail
// soit fait au niveau du SQL, mais pour le moment ça marche
$res = $db->query('SELECT compte, debit, credit, (SELECT position FROM compta_comptes WHERE id = compte) AS position
FROM
(SELECT compte_debit AS compte, SUM(montant) AS debit, NULL AS credit
FROM compta_journal WHERE id_exercice = :exercice GROUP BY compte_debit
UNION
SELECT compte_credit AS compte, NULL AS debit, SUM(montant) AS credit
FROM compta_journal WHERE id_exercice = :exercice GROUP BY compte_credit)
WHERE compte IN (SELECT id FROM compta_comptes WHERE position IN ('.implode(', ', $include).'))
|
|
|
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
|
];
$passif['total'] = $resultat['resultat'];
}
// Y'a sûrement moyen d'améliorer tout ça pour que le maximum de travail
// soit fait au niveau du SQL, mais pour le moment ça marche
$res = $db->preparedQuery('SELECT compte, debit, credit, (SELECT position FROM compta_comptes WHERE id = compte) AS position
FROM
(SELECT compte_debit AS compte, SUM(montant) AS debit, NULL AS credit
FROM compta_journal WHERE id_exercice = :exercice GROUP BY compte_debit
UNION
SELECT compte_credit AS compte, NULL AS debit, SUM(montant) AS credit
FROM compta_journal WHERE id_exercice = :exercice GROUP BY compte_credit)
WHERE compte IN (SELECT id FROM compta_comptes WHERE position IN ('.implode(', ', $include).'))
|
︙ | | | ︙ | |