Overview
Comment:formattage rapprochement définitif
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 90f06b9e42016eacd3d9f7ae463bf8aeed740ec1
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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}&amp;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}&amp;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}&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|escape}?id={$compte.id|escape}&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
            <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');

Fossil 2.10 [9d9ef82234] 2019-10-04 21:41:13
This page was generated in about 0.013s