$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) ]
+ ]
]);
}
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);
}
$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();
$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;
$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);
// 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();
}
}
-?>
\ No newline at end of file
+?>