"""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
;
"""
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\tdepenses\tgains" % (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\tdepenses\tgains"
+ 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)