Ajout du support des écritures ventilées.

[garradin.git] / include / class.compta_exercices.php

diff --git a/include/class.compta_exercices.php b/include/class.compta_exercices.php
index 02edb14..e115a35 100644 (file)
--- 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
+?>