include/class.compta_stats.php

   1 <?php
   2
   3 namespace Garradin;
   4
   5 class Compta_Stats
   6 {
   7         protected function _parRepartitionCategorie($type)
   8         {
   9                 $db = DB::getInstance();
  10                 return $db->simpleStatementFetch('SELECT COUNT(*) AS nb, id_categorie
  11                         FROM compta_journal
  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);
  15         }
  16
  17         public function repartitionRecettes()
  18         {
  19                 return $this->_parRepartitionCategorie(Compta_Categories::RECETTES);
  20         }
  21
  22         public function repartitionDepenses()
  23         {
  24                 return $this->_parRepartitionCategorie(Compta_Categories::DEPENSES);
  25         }
  26
  27         protected function _parType($type)
  28         {
  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;');
  35         }
  36
  37         public function recettes()
  38         {
  39                 return $this->_parType(Compta_Categories::RECETTES);
  40         }
  41
  42         public function depenses()
  43         {
  44                 return $this->_parType(Compta_Categories::DEPENSES);
  45         }
  46
  47         public function soldeCompte($compte)
  48         {
  49                 $db = DB::getInstance();
  50
  51                 if (strpos($compte, '%') !== false)
  52                 {
  53                         $compte = 'LIKE \''. $db->escapeString($compte) . '\'';
  54                 }
  55                 else
  56                 {
  57                         $compte = '= \''. $db->escapeString($compte) . '\'';
  58                 }
  59
  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)
  66                         ) AS solde
  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;');
  71
  72                 $c = 0;
  73                 foreach ($stats as $k=>$v)
  74                 {
  75                         $c += $v;
  76                         $stats[$k] = $c;
  77                 }
  78
  79                 return $stats;
  80         }
  81
  82         public function getStats($query)
  83         {
  84                 $db = DB::getInstance();
  85
  86                 $data = $db->simpleStatementFetchAssoc($query);
  87
  88                 $e = $db->querySingle('SELECT *, strftime(\'%s\', debut) AS debut,
  89                         strftime(\'%s\', fin) AS fin FROM compta_exercices WHERE cloture = 0;', true);
  90
  91                 $y = date('Y', $e['debut']);
  92                 $m = date('m', $e['debut']);
  93                 $max = date('Ym', $e['fin']);
  94
  95                 while ($y . $m <= $max)
  96                 {
  97                         if (!isset($data[$y . $m]))
  98                         {
  99                                 $data[$y . $m] = 0;
 100                         }
 101
 102                         if ($m == 12)
 103                         {
 104                                 $m = '01';
 105                                 $y++;
 106                         }
 107                         else
 108                         {
 109                                 $m++;
 110                                 $m = str_pad((int)$m, 2, '0', STR_PAD_LEFT);
 111                         }
 112                 }
 113
 114                 ksort($data);
 115
 116                 return $data;
 117         }
 118 }
 119
 120 ?>