7 protected function _getCurrentExercice()
9 $db = DB
::getInstance();
10 $id = $db->querySingle('SELECT id FROM compta_exercices WHERE cloture = 0 LIMIT 1;');
14 throw new UserException('Aucun exercice en cours.');
20 public function checkExercice()
22 return $this->_getCurrentExercice();
25 protected function _checkOpenExercice($id)
30 $db = DB
::getInstance();
31 $id = $db->simpleQuerySingle('SELECT id FROM compta_exercices
32 WHERE cloture = 0 AND id = ? LIMIT 1;', false, (int)$id);
40 public function getSolde($id_compte, $inclure_sous_comptes = false)
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)) . '\'';
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)';
52 return $db->querySingle('SELECT ' . $solde . ';');
55 public function getJournalCompte($compte, $inclure_sous_comptes = false)
57 $db = DB
::getInstance();
59 $exercice = $this->_getCurrentExercice();
60 $compte = $inclure_sous_comptes
61 ?
'LIKE \'' . $db->escapeString(trim($compte)) . '%\''
62 : '= \'' . $db->escapeString(trim($compte)) . '\'';
64 $query = 'SELECT *, strftime(\'%s\', date) AS date, running_sum(compta_flux.montant) AS solde
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.'
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;');
75 $db->resetRunningSum();
76 return $db->simpleStatementFetch($query);
79 public function add($data)
81 $this->_checkFields($data);
83 $db = DB
::getInstance();
85 $data['id_exercice'] = $this->_getCurrentExercice();
87 $fluxs = $data['fluxs'];
88 unset($data['fluxs']);
90 $db->simpleInsert('compta_journal', $data);
91 $id = $db->lastInsertRowId();
93 foreach ($fluxs as $flux)
95 $db->simpleInsert('compta_flux', $flux +
['id_journal' => $id]);
97 $data['fluxs'] = $fluxs;
102 public function edit($id, $data)
104 $db = DB
::getInstance();
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)))
109 throw new UserException('Cette opération fait partie d\'un exercice qui a été clôturé.');
112 $this->_checkFields($data);
114 $fluxs = $data['fluxs'];
115 unset($data['fluxs']);
117 $db->simpleUpdate('compta_journal', $data, 'id = \''.trim($id).'\'');
119 $db->simpleExec('DELETE FROM compta_flux WHERE id_journal = ?;', (int)$id);
120 foreach ($fluxs as $flux)
122 $db->simpleInsert('compta_flux', $flux +
['id_journal' => (int)$id]);
124 $data['fluxs'] = $fluxs;
129 public function delete($id)
131 $db = DB
::getInstance();
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)))
136 throw new UserException('Cette opération fait partie d\'un exercice qui a été clôturé.');
139 $db->simpleExec('DELETE FROM membres_operations WHERE id_operation = ?;', (int)$id);
140 $db->simpleExec('DELETE FROM compta_journal WHERE id = ?;', (int)$id);
145 public function get($id)
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]);
154 public function countForMember($id)
156 $db = DB
::getInstance();
157 return $db->simpleQuerySingle('SELECT COUNT(*)
158 FROM compta_journal WHERE id_auteur = ?;', false, (int)$id);
161 public function listForMember($id, $exercice)
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]);
171 protected function _checkFields(&$data)
173 $db = DB
::getInstance();
175 if (empty($data['libelle']) ||
!trim($data['libelle']))
177 throw new UserException('Le libellé ne peut rester vide.');
180 $data['libelle'] = trim($data['libelle']);
182 if (!empty($data['moyen_paiement'])
183 && !$db->simpleQuerySingle('SELECT 1 FROM compta_moyens_paiement WHERE code = ?;', false, $data['moyen_paiement']))
185 throw new UserException('Moyen de paiement invalide.');
188 if (empty($data['date']) ||
!utils
::checkDate($data['date']))
190 throw new UserException('Date vide ou invalide.');
193 if (!$db->simpleQuerySingle('SELECT 1 FROM compta_exercices WHERE cloture = 0
194 AND debut <= :date AND fin >= :date;', false, ['date' => $data['date']]))
196 throw new UserException('La date ne correspond pas à l\'exercice en cours.');
199 if (empty($data['moyen_paiement']))
201 $data['moyen_paiement'] = null;
202 $data['numero_cheque'] = null;
206 $data['moyen_paiement'] = strtoupper($data['moyen_paiement']);
208 if ($data['moyen_paiement'] != 'CH')
210 $data['numero_cheque'] = null;
213 if (!$db->simpleQuerySingle('SELECT 1 FROM compta_moyens_paiement WHERE code = ? LIMIT 1;',
214 false, $data['moyen_paiement']))
216 throw new UserException('Moyen de paiement invalide.');
221 foreach (['remarques', 'numero_piece', 'numero_cheque'] as $champ)
223 if (empty($data[$champ]) ||
!trim($data[$champ]))
229 $data[$champ] = trim($data[$champ]);
234 foreach ($data['fluxs'] as $flux)
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'])))
240 throw new UserException('Compte débité inconnu.');
242 $flux['compte'] = is_null($flux['compte']) ?
null : strtoupper(trim($flux['compte']));
244 $flux['montant'] = str_replace(',', '.', $flux['montant']);
245 $flux['montant'] = (float)$flux['montant'];
247 $sum_montant = round($sum_montant +
$flux['montant'], 2);
250 if ($sum_montant != 0)
252 throw new UserException('Somme non-nulle des montants des flux: '.print_r($data, true));
255 if (isset($data['id_categorie']))
257 if (!$db->simpleQuerySingle('SELECT 1 FROM compta_categories WHERE id = ?;', false, (int)$data['id_categorie']))
259 throw new UserException('Catégorie inconnue.');
262 $data['id_categorie'] = (int)$data['id_categorie'];
266 $data['id_categorie'] = NULL;
269 if (isset($data['id_auteur']))
271 $data['id_auteur'] = (int)$data['id_auteur'];
277 public function getListForCategory($type = null, $cat = null)
279 $db = DB
::getInstance();
280 $exercice = $this->_getCurrentExercice();
282 $query = 'SELECT compta_journal.*, strftime(\'%s\', compta_journal.date) AS date ';
284 if (is_null($cat) && !is_null($type))
286 $query.= ', compta_categories.intitule AS categorie
287 FROM compta_journal LEFT JOIN compta_categories
288 ON compta_journal.id_categorie = compta_categories.id ';
292 $query.= ' FROM compta_journal ';
299 $query .= 'id_categorie = ' . (int)$cat;
301 elseif (is_null($type) && is_null($cat))
303 $query .= 'id_categorie IS NULL';
307 $query.= 'id_categorie IN (SELECT id FROM compta_categories WHERE type = '.(int)$type.')';
310 $query .= ' AND id_exercice = ' . (int)$exercice;
311 $query .= ' ORDER BY date;';
313 return $db->simpleStatementFetch($query);
316 public function searchSQL($query)
318 $db = DB
::getInstance();
320 if (!preg_match('/LIMIT\s+/', $query))
322 $query = preg_replace('/;?\s*$/', '', $query);
323 $query .= ' LIMIT 100';
326 $st = $db->prepare($query);
328 if (!$st->readOnly())
330 throw new UserException('Seules les requêtes en lecture sont autorisées.');
333 $res = $st->execute();
336 while ($row = $res->fetchArray(SQLITE3_ASSOC
))
344 public function schemaSQL()
346 $db = DB
::getInstance();
349 'journal' => $db->querySingle('SELECT sql FROM sqlite_master WHERE type = \'table\' AND name = \'compta_journal\';'),