Overview
Comment:running_sum via sqlite n'était pas fiable, faisons-le en PHP
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 199862d6c47a69a19aa775e4521af333604f2986
User & Date: bohwaz on 2014-11-06 00:45:12
Other Links: manifest | tags
Context
2014-11-22
17:51
Sous-requête qui n'était pas correctement formulée check-in: 9d2c3747e3 user: bohwaz tags: trunk
2014-11-06
00:45
running_sum via sqlite n'était pas fiable, faisons-le en PHP check-in: 199862d6c4 user: bohwaz tags: trunk
2014-10-22
13:38
unification recherche sql check-in: 0acf969b8d user: bohwaz tags: trunk
Changes

Modified src/include/class.compta_journal.php from [b035698dd5] to [04ec1a0048].

82
83
84
85
86
87
88
89
90
91

92
93
94
95
96
97

98



99
100
101
102
103
104
105
106
107
        }
        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.'

            ORDER BY date ASC;';

        // Obligatoire pour bien taper dans l'index de la date
        // sinon running_sum est appelé 2 fois et ça marche pas du coup
        // FIXME mettre ça ailleurs pour que ça soit appelé moins souvent
        $db->exec('ANALYZE compta_journal;');





        $db->resetRunningSum();
        return $db->simpleStatementFetch($query);
    }

    public function add($data)
    {
        $this->_checkFields($data);

        $db = DB::getInstance();







|
|
|
>


<
|
|
|
>
|
>
>
>
|
|







82
83
84
85
86
87
88
89
90
91
92
93
94

95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
        }
        else
        {
            $d = '-';
            $c = '';
        }

        $query = 'SELECT *, strftime(\'%s\', date) AS date, 
            (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.'
            ORDER BY date ASC;';


        $result = $db->simpleStatementFetch($query);
        $solde = 0.0;

        foreach ($result as &$row)
        {
            $solde += $row['solde'];
            $row['solde'] = $solde;
        }

        return $result;
    }

    public function add($data)
    {
        $this->_checkFields($data);

        $db = DB::getInstance();

Modified src/include/class.db.php from [1638255074] to [d57aaad273].

14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
    return $subject;
}

class DB extends \SQLite3
{
    static protected $_instance = null;

    protected $_running_sum = 0.0;

    protected $_transaction = 0;

    const NUM = \SQLITE3_NUM;
    const ASSOC = \SQLITE3_ASSOC;
    const BOTH = \SQLITE3_BOTH;

    static public function getInstance($create = false)







<
<







14
15
16
17
18
19
20


21
22
23
24
25
26
27
    return $subject;
}

class DB extends \SQLite3
{
    static protected $_instance = null;



    protected $_transaction = 0;

    const NUM = \SQLITE3_NUM;
    const ASSOC = \SQLITE3_ASSOC;
    const BOTH = \SQLITE3_BOTH;

    static public function getInstance($create = false)
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

        // Activer les contraintes des foreign keys
        $this->exec('PRAGMA foreign_keys = ON;');

        $this->createFunction('transliterate_to_ascii', ['Garradin\utils', 'transliterateToAscii']);
        $this->createFunction('base64', 'base64_encode');
        $this->createFunction('rank', [$this, 'sql_rank']);
        $this->createFunction('running_sum', [$this, 'sql_running_sum']);
    }

    public function sql_running_sum($data)
    {
        // Why is this function called two times for the first row?!
        // Dunno but here is a workaround
        if (is_null($this->_running_sum))
        {
            $this->_running_sum = 0.0;
            return $this->_running_sum;
        }

        $this->_running_sum += $data;
        return $this->_running_sum;
    }

    public function resetRunningSum()
    {
        $this->_running_sum = null;
    }

    public function sql_rank($aMatchInfo)
    {
        $iSize = 4; // byte size
        $iPhrase = (int) 0;                 // Current phrase //
        $score = (double)0.0;               // Value to return //







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







48
49
50
51
52
53
54




















55
56
57
58
59
60
61

        // Activer les contraintes des foreign keys
        $this->exec('PRAGMA foreign_keys = ON;');

        $this->createFunction('transliterate_to_ascii', ['Garradin\utils', 'transliterateToAscii']);
        $this->createFunction('base64', 'base64_encode');
        $this->createFunction('rank', [$this, 'sql_rank']);




















    }

    public function sql_rank($aMatchInfo)
    {
        $iSize = 4; // byte size
        $iPhrase = (int) 0;                 // Current phrase //
        $score = (double)0.0;               // Value to return //