X-Git-Url: https://git.cyclocoop.org/?p=garradin.git;a=blobdiff_plain;f=include%2Fclass.compta_exercices.php;h=e115a35f445408d206f569730514f62c007ebaf5;hp=02edb1477f8852015b894e0e27a484983fb01e4c;hb=f7ee0ae730f47bfef173c394fa4db71a22dece61;hpb=1ab3343a95ed3ef4958d91dfbf49372dce8a092e diff --git a/include/class.compta_exercices.php b/include/class.compta_exercices.php index 02edb14..e115a35 100644 --- a/include/class.compta_exercices.php +++ b/include/class.compta_exercices.php @@ -204,9 +204,10 @@ class Compta_Exercices $journal->add([ 'libelle' => 'Résultat de l\'exercice précédent', 'date' => $date, - 'montant' => abs($resultat), - 'compte_debit' => $resultat < 0 ? 129 : NULL, - 'compte_credit' => $resultat > 0 ? 120 : NULL, + 'fluxs' => + [ [ 'compte' => $resultat > 0 ? 129 : 120 + , 'montant' => abs($resultat) ] + ] ]); } @@ -283,8 +284,11 @@ class Compta_Exercices public function getJournal($exercice) { $db = DB::getInstance(); - $query = 'SELECT *, strftime(\'%s\', date) AS date FROM compta_journal - WHERE id_exercice = '.(int)$exercice.' ORDER BY date, id;'; + $query = 'SELECT *, strftime(\'%s\', date) AS date, -montant AS montant_oppose + FROM compta_flux + LEFT JOIN compta_journal ON compta_journal.id = compta_flux.id_journal + WHERE compta_journal.id_exercice = '.(int)$exercice.' + ORDER BY date, id;'; return $db->simpleStatementFetch($query); } @@ -293,12 +297,9 @@ class Compta_Exercices $db = DB::getInstance(); $livre = ['classes' => [], 'debit' => 0.0, 'credit' => 0.0]; - $res = $db->prepare('SELECT compte FROM - (SELECT compte_debit AS compte FROM compta_journal - WHERE id_exercice = '.(int)$exercice.' GROUP BY compte_debit - UNION - SELECT compte_credit AS compte FROM compta_journal - WHERE id_exercice = '.(int)$exercice.' GROUP BY compte_credit) + $res = $db->prepare('SELECT compte FROM compta_flux + LEFT JOIN compta_journal ON compta_journal.id = compta_flux.id_journal + WHERE id_exercice = '.(int)$exercice.' ORDER BY base64(compte) COLLATE BINARY ASC;' )->execute(); @@ -328,19 +329,20 @@ class Compta_Exercices $livre['classes'][$classe][$parent]['comptes'][$compte] = ['debit' => 0.0, 'credit' => 0.0, 'journal' => []]; $livre['classes'][$classe][$parent]['comptes'][$compte]['journal'] = $db->simpleStatementFetch( - 'SELECT *, strftime(\'%s\', date) AS date FROM ( - SELECT * FROM compta_journal WHERE compte_debit = :compte AND id_exercice = '.(int)$exercice.' - UNION - SELECT * FROM compta_journal WHERE compte_credit = :compte AND id_exercice = '.(int)$exercice.' - ) - ORDER BY date, numero_piece, id;', SQLITE3_ASSOC, ['compte' => $compte]); + 'SELECT *, strftime(\'%s\', date) AS date FROM compta_journal + LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal + WHERE compte = :compte AND id_exercice = '.(int)$exercice.' + ORDER BY date, numero_piece, id;', SQLITE3_ASSOC, ['compte' => $compte]); $debit = (float) $db->simpleQuerySingle( - 'SELECT SUM(montant) FROM compta_journal WHERE compte_debit = ? AND id_exercice = '.(int)$exercice.';', + 'SELECT SUM(montant) FROM compta_journal + LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal + WHERE compte = ? AND montant > 0 AND id_exercice = '.(int)$exercice.';', false, $compte); - $credit = (float) $db->simpleQuerySingle( - 'SELECT SUM(montant) FROM compta_journal WHERE compte_credit = ? AND id_exercice = '.(int)$exercice.';', + 'SELECT -SUM(montant) FROM compta_journal + LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal + WHERE compte = ? AND montant < 0 AND id_exercice = '.(int)$exercice.';', false, $compte); $livre['classes'][$classe][$parent]['comptes'][$compte]['debit'] = $debit; @@ -368,19 +370,25 @@ class Compta_Exercices $res = $db->prepare('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 = '.(int)$exercice.' GROUP BY compte_debit + (SELECT compte, SUM(montant) AS debit, 0 AS credit + FROM compta_journal + LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal + WHERE montant > 0 AND id_exercice = '.(int)$exercice.' GROUP BY compte UNION - SELECT compte_credit AS compte, 0 AS debit, SUM(montant) AS credit - FROM compta_journal WHERE id_exercice = '.(int)$exercice.' GROUP BY compte_credit) + SELECT compte, 0 AS debit, -SUM(montant) AS credit + FROM compta_journal + LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal + WHERE montant < 0 AND id_exercice = '.(int)$exercice.' GROUP BY compte) WHERE compte LIKE \'6%\' OR compte LIKE \'7%\' GROUP BY compte ORDER BY base64(compte) COLLATE BINARY ASC;' )->execute(); + while ($row = $res->fetchArray(SQLITE3_NUM)) { list($compte, $debit, $credit) = $row; + print_r([$compte, $debit, $credit]); $classe = substr($compte, 0, 1); $parent = substr($compte, 0, 2); @@ -466,11 +474,15 @@ class Compta_Exercices // soit fait au niveau du SQL, mais pour le moment ça marche $res = $db->prepare('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 = '.(int)$exercice.' GROUP BY compte_debit + (SELECT compte, SUM(montant) AS debit, NULL AS credit + FROM compta_journal + LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal + WHERE montant > 0 AND id_exercice = '.(int)$exercice.' GROUP BY compte UNION - SELECT compte_credit AS compte, NULL AS debit, SUM(montant) AS credit - FROM compta_journal WHERE id_exercice = '.(int)$exercice.' GROUP BY compte_credit) + SELECT compte, NULL AS debit, SUM(montant) AS credit + FROM compta_journal + LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal + WHERE montant < 0 AND id_exercice = '.(int)$exercice.' GROUP BY compte) WHERE compte IN (SELECT id FROM compta_comptes WHERE position IN ('.implode(', ', $include).')) ORDER BY base64(compte) COLLATE BINARY ASC;' )->execute(); @@ -566,4 +578,4 @@ class Compta_Exercices } } -?> \ No newline at end of file +?>