[bde] transactions : ajout du solde après transaction

soldes cumulés croissants avec OVER
Attention au special case emetteur == destinataire…

diff --git a/bde/transactions.sh b/bde/transactions.sh
index f2140855795be12680b07142fe29c177d646d6e0..088ec5cfc3af1654666430a56ade238425a9e0ef 100755 (executable)
--- a/bde/transactions.sh
+++ b/bde/transactions.sh
@@ -5,7 +5,9 @@
 export LESS='-S'
 
 psql note -c "
 export LESS='-S'
 
 psql note -c "

-   SELECT t.id,t.date,t.quantite,t.montant,t.description,t.valide,t.emetteur,t.destinataire,
+   SELECT t.id, t.date,
+          sum((CASE WHEN $1 = emetteur THEN (CASE WHEN $1 = destinataire THEN 0 ELSE -1 END) ELSE 1 END)*montant*quantite) OVER (ORDER BY date ASC) AS soldeapres,
+          t.quantite, t.montant, t.description, t.valide, t.emetteur, t.destinataire,

           adhe.pseudo AS emetteur, adhd.pseudo AS destinataire
    FROM transactions AS t, comptes AS adhe, comptes AS adhd
    WHERE $1 in (emetteur, destinataire) AND t.emetteur=adhe.idbde AND t.destinataire=adhd.idbde AND valide
           adhe.pseudo AS emetteur, adhd.pseudo AS destinataire
    FROM transactions AS t, comptes AS adhe, comptes AS adhd
    WHERE $1 in (emetteur, destinataire) AND t.emetteur=adhe.idbde AND t.destinataire=adhd.idbde AND valide