Overview
Comment:Rapprochement: possibilité de ne pas lister les opérations déjà rapprochées (d'après un patch de @sebastien)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA1: cea3a6b0be205261fae9996b4190cdc04a838454
User & Date: bohwaz on 2017-09-21 07:28:53
Other Links: branch diff | manifest | tags
Context
2017-09-21
07:38
Ignorer les reports à nouveau dans les rapprochements check-in: 509dcc0bcf user: bohwaz tags: dev
07:28
Rapprochement: possibilité de ne pas lister les opérations déjà rapprochées (d'après un patch de @sebastien) check-in: cea3a6b0be user: bohwaz tags: dev
07:08
Créer une sauvegarde avant mise à jour check-in: d3c235bbd3 user: bohwaz tags: dev
Changes

Modified src/include/lib/Garradin/Compta/Rapprochement.php from [86bba5a596] to [7d3b707f78].

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
<?php

namespace Garradin\Compta;

use \Garradin\DB;
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->firstColumn('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->firstColumn($query, [
            '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.id_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->get($query, [
            'compte'    =>  $compte,
            'debut'     =>  $debut,
            'fin'       =>  $fin,
            'exercice'  =>  $exercice
        ]);

        foreach ($result as &$row)
        {
            $solde += $row->solde;
            $row->solde = $solde;
        }












|



<
<

|
|




<








|

>






<







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
<?php

namespace Garradin\Compta;

use \Garradin\DB;
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, $sauf_deja_rapprochees = false)
    {
        $db = DB::getInstance();



        $query = 'SELECT 
            COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_debit = :compte AND date < :date), 0)
            - COALESCE((SELECT SUM(montant) FROM compta_journal WHERE compte_credit = :compte AND date < :date), 0)';

        $solde_initial = $solde = $db->firstColumn($query, [
            'compte'    =>  $compte,
            'date'      =>  $debut,

        ]);

        $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.id_operation = j.id
            WHERE (compte_debit = :compte OR compte_credit = :compte)
                AND j.date >= :debut AND j.date <= :fin
                ' . ($sauf_deja_rapprochees ? 'AND r.id_operation IS NULL' : '') . '
            ORDER BY date ASC;';

        $result = $db->get($query, [
            'compte'    =>  $compte,
            'debut'     =>  $debut,
            'fin'       =>  $fin,

        ]);

        foreach ($result as &$row)
        {
            $solde += $row->solde;
            $row->solde = $solde;
        }

Modified src/templates/admin/compta/banques/rapprocher.tpl from [1c00ac659a] to [7ec3c5b39a].

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

<form method="get" action="{$self_url_no_qs}">
    {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}?id={$compte.id}&amp;debut={$prev|date_fr:'Y-m-01'}&amp;fin={$prev|date_fr:'Y-m-t'}">&larr; {$prev|date_fr:'F Y'}</a>
            | <a class="icn" href="{$self_url_no_qs}?id={$compte.id}&amp;debut={$next|date_fr:'Y-m-01'}&amp;fin={$next|date_fr:'Y-m-t'}">{$next|date_fr:'F Y'} &rarr;</a>
            </dd>
        </dl>
    </fieldset>
    {/if}
    <fieldset>
        <legend>Période de rapprochement</legend>
        <p>
            Du
            <span><input type="date" name="debut" id="f_debut" value="{form_field name='debut' default=$debut}" /></span>
            au
            <span><input type="date" name="fin" id="f_fin" value="{form_field name='fin' default=$fin}" /></span>

            <input type="hidden" name="id" value="{$compte.id}" />
            <input type="submit" value="Afficher" />
        </p>
    </fieldset>
</form>

{form_errors}







|
|








|

|
>







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

<form method="get" action="{$self_url_no_qs}">
    {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}?id={$compte.id}&amp;debut={$prev|date_fr:'Y-m-01'}&amp;fin={$prev|date_fr:'Y-m-t'}{if \Garradin\qg('sauf')}&amp;sauf=1{/if}">&larr; {$prev|date_fr:'F Y'}</a>
            | <a class="icn" href="{$self_url_no_qs}?id={$compte.id}&amp;debut={$next|date_fr:'Y-m-01'}&amp;fin={$next|date_fr:'Y-m-t'}{if \Garradin\qg('sauf')}&amp;sauf=1{/if}">{$next|date_fr:'F Y'} &rarr;</a>
            </dd>
        </dl>
    </fieldset>
    {/if}
    <fieldset>
        <legend>Période de rapprochement</legend>
        <p>
            Du
            <span><input type="date" name="debut" id="f_debut" value="{$debut}" /></span>
            au
            <span><input type="date" name="fin" id="f_fin" value="{$fin}" /></span>
            <label><input type="checkbox" name="sauf" value="1" {if \Garradin\qg('sauf')} checked="checked"{/if} /> Ne pas inclure les écritures déjà rapprochées</label>
            <input type="hidden" name="id" value="{$compte.id}" />
            <input type="submit" value="Afficher" />
        </p>
    </fieldset>
</form>

{form_errors}

Modified src/www/admin/compta/banques/rapprocher.php from [143cff1a1c] to [43c86f7162].

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
<?php
namespace Garradin;

require_once __DIR__ . '/../_inc.php';

$session->requireAccess('compta', Membres::DROIT_ECRITURE);

$banques = new Compta\Comptes_Bancaires;
$rapprochement = new Compta\Rapprochement;
$exercices = new Compta\Exercices;
$exercice = $exercices->getCurrent();

$compte = $banques->get(qg('id'));

if (!$compte)
{
    throw new UserException("Le compte demandé n'existe pas.");
}

$solde_initial = $solde_final = 0;

$debut = qg('debut');
$fin = qg('fin');

if ($debut && $fin)
{
    if (!Utils::checkDate($debut) || !Utils::checkDate($fin))
    {
        $form->addError('La date donnée est invalide.');
        $debut = $fin = false;
    }
    else if (strtotime($debut) < $exercice->debut)
    {
        $debut = date('Y-m-d', $exercice->debut);
    }
    else if (strtotime($fin) > $exercice->fin)
    {
        $fin = date('Y-m-d', $exercice->fin);
    }
}

if (!$debut || !$fin)
{
    $date = $exercice->fin;
    $debut = date('Y-m-01', $date);
    $fin = date('Y-m-t', $date);
}

$journal = $rapprochement->getJournal($compte->id, $debut, $fin, $solde_initial, $solde_final);

// Enregistrement des cases cochées
if (f('save') && $form->check('compta_rapprocher_' . $compte->id))
{
    try
    {
        $rapprochement->record($compte->id, $journal, f('rapprocher'), $user->id);
        Utils::redirect(Utils::getSelfURL());
    }
    catch (UserException $e)
    {
        $form->addError($e->getMessage());
    }
}

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));
}

$tpl->assign('compte', $compte);
$tpl->assign('debut', $debut);
$tpl->assign('fin', $fin);

$tpl->assign('journal', $journal);

$tpl->assign('solde_initial', $solde_initial);
$tpl->assign('solde_final', $solde_final);

$tpl->display('admin/compta/banques/rapprocher.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
<?php
namespace Garradin;

require_once __DIR__ . '/../_inc.php';

$session->requireAccess('compta', Membres::DROIT_ECRITURE);

$banques = new Compta\Comptes_Bancaires;
$rapprochement = new Compta\Rapprochement;



$compte = $banques->get(qg('id'));

if (!$compte)
{
    throw new UserException("Le compte demandé n'existe pas.");
}

$solde_initial = $solde_final = 0;

$debut = qg('debut');
$fin = qg('fin');

if ($debut && $fin)
{
    if (!Utils::checkDate($debut) || !Utils::checkDate($fin))
    {
        $form->addError('La date donnée est invalide.');
        $debut = $fin = false;
    }








}

if (!$debut || !$fin)
{

    $debut = date('Y-m-01');
    $fin = date('Y-m-t');
}

$journal = $rapprochement->getJournal($compte->id, $debut, $fin, $solde_initial, $solde_final, (bool) qg('sauf'));

// Enregistrement des cases cochées
if (f('save') && $form->check('compta_rapprocher_' . $compte->id))
{
    try
    {
        $rapprochement->record($compte->id, $journal, f('rapprocher'), $user->id);
        Utils::redirect(Utils::getSelfURL());
    }
    catch (UserException $e)
    {
        $form->addError($e->getMessage());
    }
}

if (substr($debut, 0, 7) == substr($fin, 0, 7))
{
    $tpl->assign('prev', Utils::modifyDate($debut, '-1 month', true));
    $tpl->assign('next', Utils::modifyDate($debut, '+1 month', true));
}

$tpl->assign('compte', $compte);
$tpl->assign('debut', $debut);
$tpl->assign('fin', $fin);

$tpl->assign('journal', $journal);

$tpl->assign('solde_initial', $solde_initial);
$tpl->assign('solde_final', $solde_final);

$tpl->display('admin/compta/banques/rapprocher.tpl');