From: Vincent Le Gallic <legallic@crans.org>
Date: Sat, 14 Feb 2015 17:09:31 +0000 (+0100)
Subject: [depenses.py] Working, now
X-Git-Url: http://gitweb.pimeys.fr/?p=scripts-20-100.git;a=commitdiff_plain;h=6bf6fbfd7b6713da88f9beddaa111ebcd0bbac09

[depenses.py] Working, now
---

diff --git a/bde/stats/depenses.py b/bde/stats/depenses.py
index 4f6c936..787a668 100755
--- a/bde/stats/depenses.py
+++ b/bde/stats/depenses.py
@@ -3,28 +3,34 @@
 
 """Affiche les dépenses totales d'un compte par année + total"""
 
+import psycopg2
+import psycopg2.extras
+
+import sys
+
 create_temp_table = """
- SELECT * --, sum(gains), sum(depenses)
+ SELECT *
  INTO past
  FROM
    (SELECT extract(year FROM date) AS annee,
           CAST(sum(
-                   CASE WHEN emetteur=$1 THEN montant*quantite
+                   CASE WHEN emetteur = %(idbde)s THEN montant * quantite
                         ELSE 0
                    END
                   )/100.0 AS numeric(10,2)) AS depenses,
           CAST(sum(
-                   CASE WHEN destinataire=$1 THEN montant*quantite
+                   CASE WHEN destinataire = %(idbde)s THEN montant*quantite
                         ELSE 0
                    END
                   )/100.0 AS numeric(10,2)) AS gains
    FROM transactions
-   WHERE valide AND $1 in (emetteur, destinataire)
+   WHERE valide AND %(idbde)s in (emetteur, destinataire)
    GROUP BY annee) AS pif
  ;
 """
 
 display_all = """ SELECT * FROM past
+ ORDER BY annee
  ;
 """
 
@@ -32,3 +38,29 @@ display_sum = """ SELECT sum(gains) AS gains, sum(depenses) AS depenses
  FROM past
  ;
 """
+
+def get_cursor():
+    """Ouvre une connexion à la bdd."""
+    con = psycopg2.connect(database="note")
+    con.set_client_encoding("utf-8")
+    cur = con.cursor(cursor_factory = psycopg2.extras.DictCursor)
+    return (con, cur)
+
+def get_depenses(idbde):
+    """Récupère toutes les dépenses/gains du compte ``idbde``"""
+    con, cur = get_cursor()
+    cur.execute(create_temp_table, {"idbde" : idbde})
+    cur.execute(display_all)
+    l = cur.fetchall()
+    print "annee\tdepenses\tgains"
+    for li in l:
+        li = [int(li[0]), li[1], li[2]]
+        print "%s\t%s\t\t%s" % tuple(li)
+    cur.execute(display_sum)
+    l = cur.fetchall()
+    print "\nTotal :\n\tdepenses\tgains"
+    print "\t%s\t\t%s" % tuple(l[0])
+
+if __name__ == "__main__":
+    idbde = int(sys.argv[1])
+    get_depenses(idbde)