"""Affiche les dépenses totales d'un compte par année + total"""
-import psycopg2
-import psycopg2.extras
-
import sys
import argparse
+import base
+
create_temp_table = """
SELECT extract(year FROM date) AS annee,
%(select_month)s
INTO past
FROM transactions
WHERE valide AND %%(idbde)s in (emetteur, destinataire)
- GROUP BY annee%(groupby_month)s
+ GROUP BY annee%(comma_month)s
;
"""
select_month = """extract(month FROM date) AS mois,"""
-groupby_month = """, month"""
+comma_month = """, mois"""
display_all = """ SELECT * FROM past
- ORDER BY annee
+ ORDER BY annee%(comma_month)s
;
"""
;
"""
-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" : "", "groupby_month" : ""}
+ con, cur = base.getcursor()
+ modifiers = {"select_month" : "", "comma_month" : ""}
params = {"idbde" : idbde}
if bymonth:
- params["select_month"] = select_month
- params["groupby_month"] = groupby_month
+ modifiers["select_month"] = select_month
+ modifiers["comma_month"] = comma_month
cur.execute(create_temp_table % modifiers, params)
- cur.execute(display_all)
+ 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):
+def display_depenses(idbde, all, tot, bymonth=False):
"""Pretty-printing des résultats."""
- print "Compte %s:\nannee\tdepenses\tgains" % (idbde,)
+ month = "\tmois" if bymonth else ""
+ print "Compte %s:\nannee%s\tgains\t\tdépenses" % (idbde, month)
for li in all:
- li = [int(li[0]), li[1], li[2]]
- print "%s\t%s\t\t%s" % tuple(li)
- print "\nTotal :\n\tdepenses\tgains"
+ 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\t\tdépenses"
print "\t%s\t\t%s\n" % tuple(tot)
if __name__ == "__main__":
args = parser.parse_args()
for idbde in args.idbdes:
- all, tot = get_depenses(idbde)
- display_depenses(idbde, all, tot)
+ all, tot = get_depenses(idbde, bymonth=args.months)
+ display_depenses(idbde, all, tot, bymonth=args.months)