7 protected function _parRepartitionCategorie($type)
9 $db = DB
::getInstance();
10 return $db->simpleStatementFetch('SELECT COUNT(*) AS nb, id_categorie
12 WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = ?)
13 AND id_exercice = (SELECT id FROM compta_exercices WHERE cloture = 0)
14 GROUP BY id_categorie ORDER BY nb DESC;', SQLITE3_ASSOC
, $type);
17 public function repartitionRecettes()
19 return $this->_parRepartitionCategorie(Compta_Categories
::RECETTES
);
22 public function repartitionDepenses()
24 return $this->_parRepartitionCategorie(Compta_Categories
::DEPENSES
);
27 protected function _parType($type)
29 return $this->getStats('SELECT strftime(\'%Y%m\', date) AS date,
30 SUM(montant) FROM compta_journal
31 LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal
32 WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = '.$type.')
33 AND id_exercice = (SELECT id FROM compta_exercices WHERE cloture = 0)
34 GROUP BY strftime(\'%Y-%m\', date) ORDER BY date;');
37 public function recettes()
39 return $this->_parType(Compta_Categories
::RECETTES
);
42 public function depenses()
44 return $this->_parType(Compta_Categories
::DEPENSES
);
47 public function soldeCompte($compte)
49 $db = DB
::getInstance();
51 if (strpos($compte, '%') !== false)
53 $compte = 'LIKE \''. $db->escapeString($compte) . '\'';
57 $compte = '= \''. $db->escapeString($compte) . '\'';
60 $stats = $this->getStats('SELECT strftime(\'%Y%m\', date) AS date,
61 (COALESCE((SELECT SUM(montant) FROM compta_journal
62 LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal
63 WHERE compte '.$compte.' AND id_exercice = cj.id_exercice
64 AND date >= strftime(\'%Y-%m-01\', cj.date)
65 AND date <= strftime(\'%Y-%m-31\', cj.date)), 0)
67 FROM compta_journal AS cj
68 WHERE (compte '.$compte.')
69 AND id_exercice = (SELECT id FROM compta_exercices WHERE cloture = 0)
70 GROUP BY strftime(\'%Y-%m\', date) ORDER BY date;');
73 foreach ($stats as $k=>$v)
82 public function getStats($query)
84 $db = DB
::getInstance();
86 $data = $db->simpleStatementFetchAssoc($query);
88 $e = $db->querySingle('SELECT *, strftime(\'%s\', debut) AS debut,
89 strftime(\'%s\', fin) AS fin FROM compta_exercices WHERE cloture = 0;', true);
91 $y = date('Y', $e['debut']);
92 $m = date('m', $e['debut']);
93 $max = date('Ym', $e['fin']);
95 while ($y . $m <= $max)
97 if (!isset($data[$y . $m]))
110 $m = str_pad((int)$m, 2, '0', STR_PAD_LEFT
);