include/class.compta_journal.php

   1 <?php
   2
   3 namespace Garradin;
   4
   5 class Compta_Journal
   6 {
   7     protected function _getCurrentExercice()
   8     {
   9         $db = DB::getInstance();
  10         $id = $db->querySingle('SELECT id FROM compta_exercices WHERE cloture = 0 LIMIT 1;');
  11
  12         if (!$id)
  13         {
  14             throw new UserException('Aucun exercice en cours.');
  15         }
  16
  17         return $id;
  18     }
  19
  20     public function checkExercice()
  21     {
  22         return $this->_getCurrentExercice();
  23     }
  24
  25     protected function _checkOpenExercice($id)
  26     {
  27         if (is_null($id))
  28             return true;
  29
  30         $db = DB::getInstance();
  31         $id = $db->simpleQuerySingle('SELECT id FROM compta_exercices
  32             WHERE cloture = 0 AND id = ? LIMIT 1;', false, (int)$id);
  33
  34         if ($id)
  35             return true;
  36
  37         return false;
  38     }
  39
  40     public function getSolde($id_compte, $inclure_sous_comptes = false)
  41     {
  42         $db = DB::getInstance();
  43         $exercice = $this->_getCurrentExercice();
  44         $compte = $inclure_sous_comptes
  45             ? 'LIKE \'' . $db->escapeString(trim($id_compte)) . '%\''
  46             : '= \'' . $db->escapeString(trim($id_compte)) . '\'';
  47
  48         $solde = 'COALESCE((SELECT SUM(montant) FROM compta_journal
  49                    LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal
  50                    WHERE compte '.$compte.' AND id_exercice = '.(int)$exercice.'), 0)';
  51
  52         return $db->querySingle('SELECT ' . $solde . ';');
  53     }
  54
  55     public function getJournalCompte($compte, $inclure_sous_comptes = false)
  56     {
  57         $db = DB::getInstance();
  58
  59         $exercice = $this->_getCurrentExercice();
  60         $compte = $inclure_sous_comptes
  61             ? 'LIKE \'' . $db->escapeString(trim($compte)) . '%\''
  62             : '= \'' . $db->escapeString(trim($compte)) . '\'';
  63
  64         $query = 'SELECT *, strftime(\'%s\', date) AS date, running_sum(compta_flux.montant) AS solde
  65             FROM compta_flux
  66             LEFT JOIN compta_journal ON compta_journal.id = compta_flux.id_journal
  67             WHERE compta_flux.compte '.$compte.' AND compta_journal.id_exercice = '.(int)$exercice.'
  68             ORDER BY date ASC;';
  69
  70         // Obligatoire pour bien taper dans l'index de la date
  71         // sinon running_sum est appelé 2 fois et ça marche pas du coup
  72         // FIXME mettre ça ailleurs pour que ça soit appelé moins souvent
  73         $db->exec('ANALYZE compta_journal;');
  74
  75         $db->resetRunningSum();
  76         return $db->simpleStatementFetch($query);
  77     }
  78
  79     public function add($data)
  80     {
  81         $this->_checkFields($data);
  82
  83         $db = DB::getInstance();
  84
  85         $data['id_exercice'] = $this->_getCurrentExercice();
  86
  87         $fluxs = $data['fluxs'];
  88         unset($data['fluxs']);
  89
  90         $db->simpleInsert('compta_journal', $data);
  91         $id = $db->lastInsertRowId();
  92
  93         foreach ($fluxs as $flux)
  94         {
  95           $db->simpleInsert('compta_flux', $flux + ['id_journal' => $id]);
  96         }
  97         $data['fluxs'] = $fluxs;
  98
  99         return $id;
 100     }
 101
 102     public function edit($id, $data)
 103     {
 104         $db = DB::getInstance();
 105
 106         // Vérification que l'on peut éditer cette opération
 107         if (!$this->_checkOpenExercice($db->simpleQuerySingle('SELECT id_exercice FROM compta_journal WHERE id = ?;', false, $id)))
 108         {
 109             throw new UserException('Cette opération fait partie d\'un exercice qui a été clôturé.');
 110         }
 111
 112         $this->_checkFields($data);
 113
 114         $fluxs = $data['fluxs'];
 115         unset($data['fluxs']);
 116
 117         $db->simpleUpdate('compta_journal', $data, 'id = \''.trim($id).'\'');
 118
 119         $db->simpleExec('DELETE FROM compta_flux WHERE id_journal = ?;', (int)$id);
 120         foreach ($fluxs as $flux)
 121         {
 122           $db->simpleInsert('compta_flux', $flux + ['id_journal' => (int)$id]);
 123         }
 124         $data['fluxs'] = $fluxs;
 125
 126         return true;
 127     }
 128
 129     public function delete($id)
 130     {
 131         $db = DB::getInstance();
 132
 133         // Vérification que l'on peut éditer cette opération
 134         if (!$this->_checkOpenExercice($db->simpleQuerySingle('SELECT id_exercice FROM compta_journal WHERE id = ?;', false, $id)))
 135         {
 136             throw new UserException('Cette opération fait partie d\'un exercice qui a été clôturé.');
 137         }
 138
 139         $db->simpleExec('DELETE FROM membres_operations WHERE id_operation = ?;', (int)$id);
 140         $db->simpleExec('DELETE FROM compta_journal WHERE id = ?;', (int)$id);
 141
 142         return true;
 143     }
 144
 145     public function get($id)
 146     {
 147         $db = DB::getInstance();
 148         $journal = $db->simpleQuerySingle('SELECT *, strftime(\'%s\', date) AS date FROM compta_journal WHERE id = ?;', true, $id);
 149         $fluxs = $db->simpleStatementFetch('SELECT * FROM compta_flux WHERE id_journal = ?
 150             ORDER BY base64(compte) COLLATE BINARY ASC;', true, $id);
 151         return ($journal + ['fluxs' => $fluxs]);
 152     }
 153
 154     public function countForMember($id)
 155     {
 156         $db = DB::getInstance();
 157         return $db->simpleQuerySingle('SELECT COUNT(*)
 158             FROM compta_journal WHERE id_auteur = ?;', false, (int)$id);
 159     }
 160
 161     public function listForMember($id, $exercice)
 162     {
 163         $db = DB::getInstance();
 164         $journal = $db->simpleStatementFetch('SELECT * FROM compta_journal
 165             WHERE id_auteur = ? AND id_exercice = ?;', \SQLITE3_ASSOC, (int)$id, (int)$exercice);
 166         $fluxs = $db->simpleStatementFetch('SELECT * FROM compta_flux WHERE id_journal = ?
 167             ORDER BY base64(compte) COLLATE BINARY ASC;', true, $journal['id']);
 168         return ($journal + ['fluxs' => $fluxs]);
 169     }
 170
 171     protected function _checkFields(&$data)
 172     {
 173         $db = DB::getInstance();
 174
 175         if (empty($data['libelle']) || !trim($data['libelle']))
 176         {
 177             throw new UserException('Le libellé ne peut rester vide.');
 178         }
 179
 180         $data['libelle'] = trim($data['libelle']);
 181
 182         if (!empty($data['moyen_paiement'])
 183             && !$db->simpleQuerySingle('SELECT 1 FROM compta_moyens_paiement WHERE code = ?;', false, $data['moyen_paiement']))
 184         {
 185             throw new UserException('Moyen de paiement invalide.');
 186         }
 187
 188         if (empty($data['date']) || !utils::checkDate($data['date']))
 189         {
 190             throw new UserException('Date vide ou invalide.');
 191         }
 192
 193         if (!$db->simpleQuerySingle('SELECT 1 FROM compta_exercices WHERE cloture = 0
 194             AND debut <= :date AND fin >= :date;', false, ['date' => $data['date']]))
 195         {
 196             throw new UserException('La date ne correspond pas à l\'exercice en cours.');
 197         }
 198
 199         if (empty($data['moyen_paiement']))
 200         {
 201             $data['moyen_paiement'] = null;
 202             $data['numero_cheque'] = null;
 203         }
 204         else
 205         {
 206             $data['moyen_paiement'] = strtoupper($data['moyen_paiement']);
 207
 208             if ($data['moyen_paiement'] != 'CH')
 209             {
 210                 $data['numero_cheque'] = null;
 211             }
 212
 213             if (!$db->simpleQuerySingle('SELECT 1 FROM compta_moyens_paiement WHERE code = ? LIMIT 1;',
 214                 false, $data['moyen_paiement']))
 215             {
 216                 throw new UserException('Moyen de paiement invalide.');
 217             }
 218         }
 219
 220
 221         foreach (['remarques', 'numero_piece', 'numero_cheque'] as $champ)
 222         {
 223             if (empty($data[$champ]) || !trim($data[$champ]))
 224             {
 225                 $data[$champ] = '';
 226             }
 227             else
 228             {
 229                 $data[$champ] = trim($data[$champ]);
 230             }
 231         }
 232
 233         $sum_montant = 0;
 234         foreach ($data['fluxs'] as $flux)
 235         {
 236           if (!array_key_exists('compte', $flux) ||
 237               (!is_null($flux['compte']) &&
 238                   !$db->simpleQuerySingle('SELECT 1 FROM compta_comptes WHERE id = ?;', false, $flux['compte'])))
 239           {
 240               throw new UserException('Compte débité inconnu.');
 241           }
 242           $flux['compte'] = is_null($flux['compte']) ? null : strtoupper(trim($flux['compte']));
 243
 244           $flux['montant'] = str_replace(',', '.', $flux['montant']);
 245           $flux['montant'] = (float)$flux['montant'];
 246
 247           $sum_montant = round($sum_montant + $flux['montant'], 2);
 248         }
 249
 250         if ($sum_montant != 0)
 251         {
 252             throw new UserException('Somme non-nulle des montants des flux: '.print_r($data, true));
 253         }
 254
 255         if (isset($data['id_categorie']))
 256         {
 257             if (!$db->simpleQuerySingle('SELECT 1 FROM compta_categories WHERE id = ?;', false, (int)$data['id_categorie']))
 258             {
 259                 throw new UserException('Catégorie inconnue.');
 260             }
 261
 262             $data['id_categorie'] = (int)$data['id_categorie'];
 263         }
 264         else
 265         {
 266             $data['id_categorie'] = NULL;
 267         }
 268
 269         if (isset($data['id_auteur']))
 270         {
 271             $data['id_auteur'] = (int)$data['id_auteur'];
 272         }
 273
 274         return true;
 275     }
 276
 277     public function getListForCategory($type = null, $cat = null)
 278     {
 279         $db = DB::getInstance();
 280         $exercice = $this->_getCurrentExercice();
 281
 282         $query = 'SELECT compta_journal.*, strftime(\'%s\', compta_journal.date) AS date ';
 283
 284         if (is_null($cat) && !is_null($type))
 285         {
 286             $query.= ', compta_categories.intitule AS categorie
 287                 FROM compta_journal LEFT JOIN compta_categories
 288                 ON compta_journal.id_categorie = compta_categories.id ';
 289         }
 290         else
 291         {
 292             $query.= ' FROM compta_journal ';
 293         }
 294
 295         $query .= ' WHERE ';
 296
 297         if (!is_null($cat))
 298         {
 299             $query .= 'id_categorie = ' . (int)$cat;
 300         }
 301         elseif (is_null($type) && is_null($cat))
 302         {
 303             $query .= 'id_categorie IS NULL';
 304         }
 305         else
 306         {
 307             $query.= 'id_categorie IN (SELECT id FROM compta_categories WHERE type = '.(int)$type.')';
 308         }
 309
 310         $query .= ' AND id_exercice = ' . (int)$exercice;
 311         $query .= ' ORDER BY date;';
 312
 313         return $db->simpleStatementFetch($query);
 314     }
 315
 316     public function searchSQL($query)
 317     {
 318         $db = DB::getInstance();
 319
 320         if (!preg_match('/LIMIT\s+/', $query))
 321         {
 322             $query = preg_replace('/;?\s*$/', '', $query);
 323             $query .= ' LIMIT 100';
 324         }
 325
 326         $st = $db->prepare($query);
 327
 328         if (!$st->readOnly())
 329         {
 330             throw new UserException('Seules les requêtes en lecture sont autorisées.');
 331         }
 332
 333         $res = $st->execute();
 334         $out = [];
 335
 336         while ($row = $res->fetchArray(SQLITE3_ASSOC))
 337         {
 338             $out[] = $row;
 339         }
 340
 341         return $out;
 342     }
 343
 344     public function schemaSQL()
 345     {
 346         $db = DB::getInstance();
 347
 348         $tables = [
 349             'journal'   =>  $db->querySingle('SELECT sql FROM sqlite_master WHERE type = \'table\' AND name = \'compta_journal\';'),
 350         ];
 351
 352         return $tables;
 353     }
 354 }
 355
 356 ?>