J'ai vraiment du mal avec gains/dépenses…

[scripts-20-100.git] / bde / stats / depenses.py

diff --git a/bde/stats/depenses.py b/bde/stats/depenses.py
index 4f6c936982a0af42c7ce9c79ac07d30eabc87759..d790975aab122ea228b895fe4e12726b3bad8fd1 100755 (executable)
--- a/bde/stats/depenses.py
+++ b/bde/stats/depenses.py
@@ -3,28 +3,38 @@
 
 """Affiche les dépenses totales d'un compte par année + total"""
 
+import psycopg2
+import psycopg2.extras
+
+import sys
+import argparse
+
 create_temp_table = """
- SELECT * --, sum(gains), sum(depenses)
+ SELECT extract(year FROM date) AS annee,
+       %(select_month)s
+       CAST(sum(
+                CASE WHEN emetteur = %%(idbde)s THEN montant * quantite
+                     ELSE 0
+                END
+               )/100.0 AS numeric(10,2)) AS depenses,
+       CAST(sum(
+                CASE WHEN destinataire = %%(idbde)s THEN montant * quantite
+                     ELSE 0
+                END
+               )/100.0 AS numeric(10,2)) AS gains
  INTO past
- FROM
-   (SELECT extract(year FROM date) AS annee,
-          CAST(sum(
-                   CASE WHEN emetteur=$1 THEN montant*quantite
-                        ELSE 0
-                   END
-                  )/100.0 AS numeric(10,2)) AS depenses,
-          CAST(sum(
-                   CASE WHEN destinataire=$1 THEN montant*quantite
-                        ELSE 0
-                   END
-                  )/100.0 AS numeric(10,2)) AS gains
-   FROM transactions
-   WHERE valide AND $1 in (emetteur, destinataire)
-   GROUP BY annee) AS pif
+ FROM transactions
+ WHERE valide AND %%(idbde)s in (emetteur, destinataire)
+ GROUP BY annee%(comma_month)s
  ;
 """
 
+select_month = """extract(month FROM date) AS mois,"""
+
+comma_month = """, mois"""
+
 display_all = """ SELECT * FROM past
+ ORDER BY annee%(comma_month)s
  ;
 """
 
@@ -32,3 +42,49 @@ 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, bymonth=False):
+    """Récupère toutes les dépenses/gains du compte ``idbde``"""
+    con, cur = get_cursor()
+    modifiers = {"select_month" : "", "comma_month" : ""}
+    params = {"idbde" : idbde}
+    if bymonth:
+        modifiers["select_month"] = select_month
+        modifiers["comma_month"] = comma_month
+    cur.execute(create_temp_table % modifiers, params)
+    cur.execute(display_all % modifiers)
+    all = cur.fetchall()
+    cur.execute(display_sum)
+    tot = cur.fetchall()[0]
+    return (all, tot)
+
+def display_depenses(idbde, all, tot, bymonth=False):
+    """Pretty-printing des résultats."""
+    month = "\tmois" if bymonth else ""
+    print "Compte %s:\nannee%s\tgains\tdépenses" % (idbde, month)
+    for li in all:
+        if bymonth:
+            li = [int(li[0]), int(li[1]), li[2], li[3]]
+            print "%s\t%s\t%s\t\t%s" % tuple(li)
+        else:
+            li = [int(li[0]), li[1], li[2]]
+            print "%s\t%s\t\t%s" % tuple(li)
+    print "\nTotal :\n\tgains\tdépenses"
+    print "\t%s\t\t%s\n" % tuple(tot)
+
+if __name__ == "__main__":
+    parser = argparse.ArgumentParser(description=__doc__)
+    parser.add_argument("-m", "--months", help="Faire le calcul par mois", action="store_true")
+    parser.add_argument('idbdes', type=int, nargs=argparse.ONE_OR_MORE, help="Les idbde dont on veut connaître les dépenses.")
+    
+    args = parser.parse_args()
+    for idbde in args.idbdes:
+        all, tot = get_depenses(idbde, bymonth=args.months)
+        display_depenses(idbde, all, tot, bymonth=args.months)