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                         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;');
  34         }
  35
  36         public function recettes()
  37         {
  38                 return $this->_parType(Compta_Categories::RECETTES);
  39         }
  40
  41         public function depenses()
  42         {
  43                 return $this->_parType(Compta_Categories::DEPENSES);
  44         }
  45
  46         public function soldeCompte($compte, $augmente = 'debit', $diminue = 'credit')
  47         {
  48                 $db = DB::getInstance();
  49
  50                 if (strpos($compte, '%') !== false)
  51                 {
  52                         $compte = 'LIKE \''. $db->escapeString($compte) . '\'';
  53                 }
  54                 else
  55                 {
  56                         $compte = '= \''. $db->escapeString($compte) . '\'';
  57                 }
  58
  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)
  68                         ) AS solde
  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;');
  73
  74                 $c = 0;
  75                 foreach ($stats as $k=>$v)
  76                 {
  77                         $c += $v;
  78                         $stats[$k] = $c;
  79                 }
  80
  81                 return $stats;
  82         }
  83
  84         public function getStats($query)
  85         {
  86                 $db = DB::getInstance();
  87
  88                 $data = $db->simpleStatementFetchAssoc($query);
  89
  90                 $e = $db->querySingle('SELECT *, strftime(\'%s\', debut) AS debut,
  91                         strftime(\'%s\', fin) AS fin FROM compta_exercices WHERE cloture = 0;', true);
  92
  93                 $y = date('Y', $e['debut']);
  94                 $m = date('m', $e['debut']);
  95                 $max = date('Ym', $e['fin']);
  96
  97                 while ($y . $m <= $max)
  98                 {
  99                         if (!isset($data[$y . $m]))
 100                         {
 101                                 $data[$y . $m] = 0;
 102                         }
 103
 104                         if ($m == 12)
 105                         {
 106                                 $m = '01';
 107                                 $y++;
 108                         }
 109                         else
 110                         {
 111                                 $m++;
 112                                 $m = str_pad((int)$m, 2, '0', STR_PAD_LEFT);
 113                         }
 114                 }
 115
 116                 ksort($data);
 117
 118                 return $data;
 119         }
 120 }
 121
 122 ?>