? 'LIKE \'' . $db->escapeString(trim($id_compte)) . '%\''
: '= \'' . $db->escapeString(trim($id_compte)) . '\'';
- $debit = 'COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_debit '.$compte.' AND id_exercice = '.(int)$exercice.'), 0)';
- $credit = 'COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_credit '.$compte.' AND id_exercice = '.(int)$exercice.'), 0)';
+ $solde = 'COALESCE((SELECT SUM(montant) FROM compta_journal
+ LEFT JOIN compta_flux ON compta_journal.id = compta_flux.id_journal
+ WHERE compte '.$compte.' AND id_exercice = '.(int)$exercice.'), 0)';
- // L'actif augmente au débit, le passif au crédit
- $position = $db->simpleQuerySingle('SELECT position FROM compta_comptes WHERE id = ?;', false, $id_compte);
-
- if (($position & Compta_Comptes::ACTIF) || ($position & Compta_Comptes::CHARGE))
- {
- $query = $debit . ' - ' . $credit;
- }
- else
- {
- $query = $credit . ' - ' . $debit;
- }
-
- return $db->querySingle('SELECT ' . $query . ';');
+ return $db->querySingle('SELECT ' . $solde . ';');
}
public function getJournalCompte($compte, $inclure_sous_comptes = false)
{
$db = DB::getInstance();
- $position = $db->simpleQuerySingle('SELECT position FROM compta_comptes WHERE id = ?;', false, $compte);
-
$exercice = $this->_getCurrentExercice();
$compte = $inclure_sous_comptes
? 'LIKE \'' . $db->escapeString(trim($compte)) . '%\''
: '= \'' . $db->escapeString(trim($compte)) . '\'';
- // L'actif et les charges augmentent au débit, le passif et les produits au crédit
- if (($position & Compta_Comptes::ACTIF) || ($position & Compta_Comptes::CHARGE))
- {
- $d = '';
- $c = '-';
- }
- else
- {
- $d = '-';
- $c = '';
- }
-
- $query = 'SELECT *, strftime(\'%s\', date) AS date,
- running_sum(CASE WHEN compte_debit '.$compte.' THEN '.$d.'montant ELSE '.$c.'montant END) AS solde
- FROM compta_journal WHERE (compte_debit '.$compte.' OR compte_credit '.$compte.') AND id_exercice = '.(int)$exercice.'
+ $query = 'SELECT *, strftime(\'%s\', date) AS date, running_sum(compta_flux.montant) AS solde
+ FROM compta_flux
+ LEFT JOIN compta_journal ON compta_journal.id = compta_flux.id_journal
+ WHERE compta_flux.compte '.$compte.' AND compta_journal.id_exercice = '.(int)$exercice.'
ORDER BY date ASC;';
// Obligatoire pour bien taper dans l'index de la date
$data['id_exercice'] = $this->_getCurrentExercice();
+ $fluxs = $data['fluxs'];
+ unset($data['fluxs']);
+
$db->simpleInsert('compta_journal', $data);
$id = $db->lastInsertRowId();
+ foreach ($fluxs as $flux)
+ {
+ $db->simpleInsert('compta_flux', $flux + ['id_journal' => $id]);
+ }
+ $data['fluxs'] = $fluxs;
+
return $id;
}
$this->_checkFields($data);
- $db->simpleUpdate('compta_journal', $data,
- 'id = \''.trim($id).'\'');
+ $fluxs = $data['fluxs'];
+ unset($data['fluxs']);
+
+ $db->simpleUpdate('compta_journal', $data, 'id = \''.trim($id).'\'');
+
+ $db->simpleExec('DELETE FROM compta_flux WHERE id_journal = ?;', (int)$id);
+ foreach ($fluxs as $flux)
+ {
+ $db->simpleInsert('compta_flux', $flux + ['id_journal' => (int)$id]);
+ }
+ $data['fluxs'] = $fluxs;
return true;
}
public function get($id)
{
$db = DB::getInstance();
- return $db->simpleQuerySingle('SELECT *, strftime(\'%s\', date) AS date FROM compta_journal WHERE id = ?;', true, $id);
+ $journal = $db->simpleQuerySingle('SELECT *, strftime(\'%s\', date) AS date FROM compta_journal WHERE id = ?;', true, $id);
+ $fluxs = $db->simpleStatementFetch('SELECT * FROM compta_flux WHERE id_journal = ?
+ ORDER BY base64(compte) COLLATE BINARY ASC;', true, $id);
+ return ($journal + ['fluxs' => $fluxs]);
}
public function countForMember($id)
public function listForMember($id, $exercice)
{
$db = DB::getInstance();
- return $db->simpleStatementFetch('SELECT * FROM compta_journal
+ $journal = $db->simpleStatementFetch('SELECT * FROM compta_journal
WHERE id_auteur = ? AND id_exercice = ?;', \SQLITE3_ASSOC, (int)$id, (int)$exercice);
+ $fluxs = $db->simpleStatementFetch('SELECT * FROM compta_flux WHERE id_journal = ?
+ ORDER BY base64(compte) COLLATE BINARY ASC;', true, $journal['id']);
+ return ($journal + ['fluxs' => $fluxs]);
}
protected function _checkFields(&$data)
}
}
- $data['montant'] = str_replace(',', '.', $data['montant']);
- $data['montant'] = (float)$data['montant'];
-
- if ($data['montant'] <= 0)
- {
- throw new UserException('Le montant ne peut être égal ou inférieur à zéro.');
- }
foreach (['remarques', 'numero_piece', 'numero_cheque'] as $champ)
{
}
}
- if (!array_key_exists('compte_debit', $data) ||
- (!is_null($data['compte_debit']) &&
- !$db->simpleQuerySingle('SELECT 1 FROM compta_comptes WHERE id = ?;', false, $data['compte_debit'])))
+ $sum_montant = 0;
+ foreach ($data['fluxs'] as $flux)
{
- throw new UserException('Compte débité inconnu.');
+ if (!array_key_exists('compte', $flux) ||
+ (!is_null($flux['compte']) &&
+ !$db->simpleQuerySingle('SELECT 1 FROM compta_comptes WHERE id = ?;', false, $flux['compte'])))
+ {
+ throw new UserException('Compte débité inconnu.');
+ }
+ $flux['compte'] = is_null($flux['compte']) ? null : strtoupper(trim($flux['compte']));
+
+ $flux['montant'] = str_replace(',', '.', $flux['montant']);
+ $flux['montant'] = (float)$flux['montant'];
+
+ $sum_montant = round($sum_montant + $flux['montant'], 2);
}
- if (!array_key_exists('compte_credit', $data) ||
- (!is_null($data['compte_credit']) &&
- !$db->simpleQuerySingle('SELECT 1 FROM compta_comptes WHERE id = ?;', false, $data['compte_credit'])))
- {
- throw new UserException('Compte crédité inconnu.');
- }
-
- $data['compte_credit'] = is_null($data['compte_credit']) ? null : strtoupper(trim($data['compte_credit']));
- $data['compte_debit'] = is_null($data['compte_debit']) ? null : strtoupper(trim($data['compte_debit']));
-
- if ($data['compte_credit'] == $data['compte_debit'])
+ if ($sum_montant != 0)
{
- throw new UserException('Compte crédité identique au compte débité.');
+ throw new UserException('Somme non-nulle des montants des flux: '.print_r($data, true));
}
if (isset($data['id_categorie']))
}
}
-?>
\ No newline at end of file
+?>
dépôts / garradin.git / blobdiff