Overview
Comment: | formattage rapprochement définitif |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
90f06b9e42016eacd3d9f7ae463bf8ae |
User & Date: | bohwaz on 2015-02-19 06:53:57 |
Other Links: | manifest | tags |
Context
2015-02-19
| ||
07:06 | Rapprochement fonctionnel check-in: 4bf13fe01e user: bohwaz tags: trunk | |
06:53 | formattage rapprochement définitif check-in: 90f06b9e42 user: bohwaz tags: trunk | |
06:53 | Permettre self_url sans query string, permettre de changer une date en timestamp check-in: ef1b8f1267 user: bohwaz tags: trunk | |
Changes
Modified src/include/data/schema.sql from [7deb0e0ac2] to [51479b06fb].
286
287
288
289
290
291
292
293
294
295
296
297
298
299
...
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
|
intitule TEXT NOT NULL, description TEXT, compte TEXT NOT NULL, -- Compte affecté par cette catégorie FOREIGN KEY(compte) REFERENCES compta_comptes(id) ); CREATE TABLE plugins ( id TEXT PRIMARY KEY, officiel INTEGER NOT NULL DEFAULT 0, nom TEXT NOT NULL, description TEXT, ................................................................................ ( signal TEXT NOT NULL, plugin TEXT NOT NULL REFERENCES plugins (id), callback TEXT NOT NULL, PRIMARY KEY (signal, plugin) ); CREATE TABLE compta_rapprochement -- Rapprochement entre compta et relevés de comptes ( operation INTEGER NOT NULL PRIMARY KEY REFERENCES compta_journal (id), date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, auteur INTEGER NOT NULL REFERENCES membres (id) ); CREATE TABLE fichiers -- Données sur les fichiers ( id INTEGER NOT NULL PRIMARY KEY, nom TEXT NOT NULL, -- nom de fichier (par exemple image1234.jpeg) type TEXT NOT NULL, -- Type MIME image INTEGER NOT NULL DEFAULT 0, -- 1 = image reconnue |
>
>
>
>
>
>
>
>
<
<
<
<
<
<
<
<
|
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
...
317
318
319
320
321
322
323
324
325
326
327
328
329
330
|
intitule TEXT NOT NULL, description TEXT, compte TEXT NOT NULL, -- Compte affecté par cette catégorie FOREIGN KEY(compte) REFERENCES compta_comptes(id) ); CREATE TABLE compta_rapprochement -- Rapprochement entre compta et relevés de comptes ( operation INTEGER NOT NULL PRIMARY KEY REFERENCES compta_journal (id), date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, auteur INTEGER NOT NULL REFERENCES membres (id) ); CREATE TABLE plugins ( id TEXT PRIMARY KEY, officiel INTEGER NOT NULL DEFAULT 0, nom TEXT NOT NULL, description TEXT, ................................................................................ ( signal TEXT NOT NULL, plugin TEXT NOT NULL REFERENCES plugins (id), callback TEXT NOT NULL, PRIMARY KEY (signal, plugin) ); CREATE TABLE fichiers -- Données sur les fichiers ( id INTEGER NOT NULL PRIMARY KEY, nom TEXT NOT NULL, -- nom de fichier (par exemple image1234.jpeg) type TEXT NOT NULL, -- Type MIME image INTEGER NOT NULL DEFAULT 0, -- 1 = image reconnue |
Modified src/include/lib/Garradin/Compta/Rapprochement.php from [426c8449ed] to [61b5bbecc5].
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
use \Garradin\Utils; use \Garradin\UserException; use \Garradin\Compta\Journal; use \Garradin\Compta\Comptes_Bancaires; class Rapprochement { public function getJournal($compte, $debut, $fin) { $db = DB::getInstance(); $exercice = $db->querySingle('SELECT id FROM compta_exercices WHERE cloture = 0 LIMIT 1;'); $query = ' SELECT j.*, strftime(\'%s\', j.date) AS date, (CASE WHEN j.compte_debit = :compte THEN j.montant ELSE -(j.montant) END) AS solde, r.date FROM compta_journal AS j LEFT JOIN compta_rapprochement AS r ON r.operation = j.id WHERE (compte_debit = :compte OR compte_credit = :compte) AND id_exercice = :exercice AND j.date >= :debut AND j.date <= :fin ORDER BY date ASC;'; $result = $db->simpleStatementFetch($query, DB::ASSOC, [ 'compte' => $compte, 'debut' => $debut, 'fin' => $fin, 'exercice' => $exercice ]); $solde = 0.0; foreach ($result as &$row) { $solde += $row['solde']; $row['solde'] = $solde; } return $result; } public function record($compte, $operations, $auteur) { if (!is_array($operations)) { throw new \UnexpectedValueException('$operations doit être un tableau.'); } $db = DB::getInstance(); $db->exec('BEGIN;'); $st = $db->prepare('INSERT OR REPLACE INTO compta_rapprochement (operation, auteur) VALUES (:operation, :auteur);'); $st->bindValue(':auteur', (int)$auteur, \SQLITE3_INTEGER); foreach ($operations as $row) { $st->bindValue(':operation', (int)$row, \SQLITE3_INTEGER); $st->execute(); } $db->exec('END;'); return true; } } |
| > > > > > > > > > > | | < > | | | | | | < < > > | | | | | | | | | | | | | | | | |
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
use \Garradin\Utils; use \Garradin\UserException; use \Garradin\Compta\Journal; use \Garradin\Compta\Comptes_Bancaires; class Rapprochement { public function getJournal($compte, $debut, $fin, &$solde_initial, &$solde_final) { $db = DB::getInstance(); $exercice = $db->querySingle('SELECT id FROM compta_exercices WHERE cloture = 0 LIMIT 1;'); $query = 'SELECT COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_debit = :compte AND id_exercice = :exercice AND date < :date), 0) - COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_credit = :compte AND id_exercice = :exercice AND date < :date), 0)'; $solde_initial = $solde = $db->simpleQuerySingle($query, false, [ 'compte' => $compte, 'date' => $debut, 'exercice' => $exercice ]); $query = ' SELECT j.*, strftime(\'%s\', j.date) AS date, (CASE WHEN j.compte_debit = :compte THEN j.montant ELSE -(j.montant) END) AS solde, r.date AS date_rapprochement FROM compta_journal AS j LEFT JOIN compta_rapprochement AS r ON r.operation = j.id WHERE (compte_debit = :compte OR compte_credit = :compte) AND id_exercice = :exercice AND j.date >= :debut AND j.date <= :fin ORDER BY date ASC;'; $result = $db->simpleStatementFetch($query, DB::ASSOC, [ 'compte' => $compte, 'debut' => $debut, 'fin' => $fin, 'exercice' => $exercice ]); foreach ($result as &$row) { $solde += $row['solde']; $row['solde'] = $solde; } $solde_final = $solde; return $result; } public function record($compte, $operations, $auteur) { if (!is_array($operations)) { throw new \UnexpectedValueException('$operations doit être un tableau.'); } $db = DB::getInstance(); $db->exec('BEGIN;'); $st = $db->prepare('INSERT OR REPLACE INTO compta_rapprochement (operation, auteur) VALUES (:operation, :auteur);'); $st->bindValue(':auteur', (int)$auteur, \SQLITE3_INTEGER); foreach ($operations as $row) { $st->bindValue(':operation', (int)$row, \SQLITE3_INTEGER); $st->execute(); } $db->exec('END;'); return true; } } |
Modified src/templates/admin/compta/banques/rapprocher.tpl from [d798562d82] to [8baf0ddb75].
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
{include file="admin/_head.tpl" title="Rapprochement — `$compte.id`" current="compta/banques" js=1} <ul class="actions"> <li><a href="{$www_url}admin/compta/banques/">Comptes bancaires</a></li> <li><a href="{$www_url}admin/compta/comptes/journal.php?id={Garradin\Compta\Comptes::CAISSE}&suivi">Journal de caisse</a></li> <li class="current"><a href="{$admin_url}compta/banques/rapprochement.php?id={$compte.id|escape}">Rapprochement</a></li> </ul> <form method="get" action="{$self_url|escape}"> <fieldset> <legend>Période de rapprochement</legend> <dl> <dt><label for="f_debut">Début</label></dt> <dd><input type="date" name="debut" id="f_debut" value="{form_field name='debut' default=$debut}" /></dd> <dt><label for="f_fin">Fin</label></dt> <dd><input type="date" name="fin" id="f_fin" value="{form_field name='fin' default=$fin}" /></dd> </dl> <p> <input type="hidden" name="id" value="{$compte.id|escape}" /> <input type="submit" value="Afficher" /> </p> </fieldset> </form> <table class="list"> <colgroup> <col width="3%" /> <col width="3%" /> <col width="12%" /> <col width="10%" /> <col width="12%" /> <col /> </colgroup> <thead> <tr> <td></td> <td></td> <td>Date</td> <td>Montant</td> <td>Solde cumulé</td> <th>Libellé</th> </tr> </thead> <tbody> {foreach from=$journal item="ligne"} <tr> <td class="num"><a href="{$admin_url}compta/operations/voir.php?id={$ligne.id|escape}">{$ligne.id|escape}</a></td> <td class="actions"> {if $user.droits.compta >= Garradin\Membres::DROIT_ADMIN} <a class="icn" href="{$admin_url}compta/operations/modifier.php?id={$ligne.id|escape}" title="Modifier cette opération">✎</a> {/if} </td> <td>{$ligne.date|date_fr:'d/m/Y'|escape}</td> <td>{if $ligne.compte_credit == $compte.id}-{else}+{/if}{$ligne.montant|html_money}</td> <td>{$ligne.solde|html_money}</td> <th>{$ligne.libelle|escape}</th> </tr> {/foreach} </tbody> <tfoot> <tr> <td colspan="3"></td> <th>Solde</th> <td colspan="2">{*{$solde|html_money} {$config.monnaie|escape}*}</td> </tr> </tfoot> </table> {include file="admin/_foot.tpl"} |
| | > > > > > > > > > > > < < > > | < > | < < > > > > > | | | | > | | | | | | | > | | | | | | | | | > > > > > | | > | | | | | | | | | | | | | | | | < | > | | | > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
{include file="admin/_head.tpl" title="Rapprochement — `$compte.id`" current="compta/banques" js=1} <ul class="actions"> <li><a href="{$www_url}admin/compta/banques/">Comptes bancaires</a></li> <li><a href="{$www_url}admin/compta/comptes/journal.php?id={Garradin\Compta\Comptes::CAISSE}&suivi">Journal de caisse</a></li> <li class="current"><a href="{$self_url_no_qs|escape}?id={$compte.id|escape}">Rapprochement</a></li> </ul> <form method="get" action="{$self_url_no_qs|escape}"> {if !empty($prev) && !empty($next)} <fieldset class="shortFormRight"> <legend>Rapprochement par mois</legend> <dl> <dd class="actions"> <a class="icn" href="{$self_url_no_qs|escape}?id={$compte.id|escape}&debut={$prev|date_fr:'Y-m-01'}&fin={$prev|date_fr:'Y-m-t'}">← {$prev|date_fr:'F Y'}</a> | <a class="icn" href="{$self_url_no_qs|escape}?id={$compte.id|escape}&debut={$next|date_fr:'Y-m-01'}&fin={$next|date_fr:'Y-m-t'}">{$next|date_fr:'F Y'} →</a> </dd> </dl> </fieldset> {/if} <fieldset> <legend>Période de rapprochement</legend> <p> Du <input type="date" name="debut" id="f_debut" value="{form_field name='debut' default=$debut}" /> au <input type="date" name="fin" id="f_fin" value="{form_field name='fin' default=$fin}" /> <input type="hidden" name="id" value="{$compte.id|escape}" /> <input type="submit" value="Afficher" /> </p> </fieldset> </form> {if $error} <p class="error">{$error|escape}</p> {/if} <form method="post" action="{$self_url|escape}"> <table class="list"> <colgroup> <col width="3%" /> <col width="3%" /> <col width="3%" /> <col width="12%" /> <col width="10%" /> <col width="12%" /> <col /> </colgroup> <thead> <tr> <td class="check"><input type="checkbox" title="Tout cocher / décocher" /></td> <td></td> <td></td> <td>Date</td> <td>Montant</td> <td>Solde cumulé</td> <th>Libellé</th> </tr> </thead> <tbody> <tr> <td colspan="5"></td> <td>{$solde_initial|html_money} {$config.monnaie|escape}</td> <th>Solde au {$debut|format_sqlite_date_to_french}</th> </tr> {foreach from=$journal item="ligne"} <tr> <td class="check"><input type="checkbox" name="rapprocher[{$ligne.id|escape}]" value="1" {if $ligne.date_rapprochement}checked="checked"{/if} /></td> <td class="num"><a href="{$admin_url}compta/operations/voir.php?id={$ligne.id|escape}">{$ligne.id|escape}</a></td> <td class="actions"> {if $user.droits.compta >= Garradin\Membres::DROIT_ADMIN} <a class="icn" href="{$admin_url}compta/operations/modifier.php?id={$ligne.id|escape}" title="Modifier cette opération">✎</a> {/if} </td> <td>{$ligne.date|date_fr:'d/m/Y'|escape}</td> <td>{if $ligne.compte_credit == $compte.id}-{else}+{/if}{$ligne.montant|html_money}</td> <td>{$ligne.solde|html_money}</td> <th>{$ligne.libelle|escape}</th> </tr> {/foreach} </tbody> <tfoot> <tr> <td colspan="5"></td> <td>{$solde_final|html_money} {$config.monnaie|escape}</td> <th>Solde au {$fin|format_sqlite_date_to_french}</th> </tr> </tfoot> </table> <p class="submit"> <input type="submit" name="save" value="Enregistrer" /> </p> </form> {include file="admin/_foot.tpl"} |
Modified src/www/admin/compta/banques/rapprocher.php from [84f2a86f78] to [c19f0013d0].
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
}
}
if (!$debut || !$fin)
{
$date = $exercice['fin'];
$debut = date('Y-m-01', $date);
$fin = date('Y-m-31', $date);
}
$tpl->assign('compte', $compte);
$tpl->assign('debut', $debut);
$tpl->assign('fin', $fin);
$tpl->assign('journal', $rapprochement->getJournal($compte['id'], $debut, $fin));
$tpl->assign('error', $error);
$tpl->display('admin/compta/banques/rapprocher.tpl');
|
| > > > > > > > > > | > > > |
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
} } if (!$debut || !$fin) { $date = $exercice['fin']; $debut = date('Y-m-01', $date); $fin = date('Y-m-t', $date); } if (substr($debut, 0, 7) == substr($fin, 0, 7)) { $tpl->assign('prev', Utils::modifyDate($debut, '-1 month', true)); $tpl->assign('next', Utils::modifyDate($fin, '+1 month', true)); } $solde_initial = $solde_final = 0; $tpl->assign('compte', $compte); $tpl->assign('debut', $debut); $tpl->assign('fin', $fin); $tpl->assign('journal', $rapprochement->getJournal($compte['id'], $debut, $fin, $solde_initial, $solde_final)); $tpl->assign('solde_initial', $solde_initial); $tpl->assign('solde_final', $solde_final); $tpl->assign('error', $error); $tpl->display('admin/compta/banques/rapprocher.tpl'); |