335fbe0f4d50765ee46b922a7e952eee3b0775f8
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 WHERE id_categorie IN (SELECT id FROM compta_categories WHERE type = '.$type.')
32 AND id_exercice = (SELECT id FROM compta_exercices WHERE cloture = 0)
33 GROUP BY strftime(\'%Y-%m\', date) ORDER BY date;');
36 public function recettes()
38 return $this->_parType(Compta_Categories
::RECETTES
);
41 public function depenses()
43 return $this->_parType(Compta_Categories
::DEPENSES
);
46 public function soldeCompte($compte, $augmente = 'debit', $diminue = 'credit')
48 $db = DB
::getInstance();
50 if (strpos($compte, '%') !== false)
52 $compte = 'LIKE \''. $db->escapeString($compte) . '\'';
56 $compte = '= \''. $db->escapeString($compte) . '\'';
59 $stats = $this->getStats('SELECT strftime(\'%Y%m\', date) AS date,
60 (COALESCE((SELECT SUM(montant) FROM compta_journal
61 WHERE compte_'.$augmente.' '.$compte.' AND id_exercice = cj.id_exercice
62 AND date >= strftime(\'%Y-%m-01\', cj.date)
63 AND date <= strftime(\'%Y-%m-31\', cj.date)), 0)
64 - COALESCE((SELECT SUM(montant) FROM compta_journal
65 WHERE compte_'.$diminue.' '.$compte.' AND id_exercice = cj.id_exercice
66 AND date >= strftime(\'%Y-%m-01\', cj.date)
67 AND date <= strftime(\'%Y-%m-31\', cj.date)), 0)
69 FROM compta_journal AS cj
70 WHERE (compte_debit '.$compte.' OR compte_credit '.$compte.')
71 AND id_exercice = (SELECT id FROM compta_exercices WHERE cloture = 0)
72 GROUP BY strftime(\'%Y-%m\', date) ORDER BY date;');
75 foreach ($stats as $k=>$v)
84 public function getStats($query)
86 $db = DB
::getInstance();
88 $data = $db->simpleStatementFetchAssoc($query);
90 $e = $db->querySingle('SELECT *, strftime(\'%s\', debut) AS debut,
91 strftime(\'%s\', fin) AS fin FROM compta_exercices WHERE cloture = 0;', true);
93 $y = date('Y', $e['debut']);
94 $m = date('m', $e['debut']);
95 $max = date('Ym', $e['fin']);
97 while ($y . $m <= $max)
99 if (!isset($data[$y . $m]))
112 $m = str_pad((int)$m, 2, '0', STR_PAD_LEFT
);