bde/stats/depenses.py

   1 #!/usr/bin/env python
   2 # -*- coding: utf-8 -*-
   3
   4 """Affiche les dépenses totales d'un compte par année + total"""
   5
   6 import psycopg2
   7 import psycopg2.extras
   8
   9 import sys
  10 import argparse
  11
  12 create_temp_table = """
  13  SELECT extract(year FROM date) AS annee,
  14        %(select_month)s
  15        CAST(sum(
  16                 CASE WHEN emetteur = %%(idbde)s THEN montant * quantite
  17                      ELSE 0
  18                 END
  19                )/100.0 AS numeric(10,2)) AS depenses,
  20        CAST(sum(
  21                 CASE WHEN destinataire = %%(idbde)s THEN montant * quantite
  22                      ELSE 0
  23                 END
  24                )/100.0 AS numeric(10,2)) AS gains
  25  INTO past
  26  FROM transactions
  27  WHERE valide AND %%(idbde)s in (emetteur, destinataire)
  28  GROUP BY annee%(comma_month)s
  29  ;
  30 """
  31
  32 select_month = """extract(month FROM date) AS mois,"""
  33
  34 comma_month = """, mois"""
  35
  36 display_all = """ SELECT * FROM past
  37  ORDER BY annee%(comma_month)s
  38  ;
  39 """
  40
  41 display_sum = """ SELECT sum(gains) AS gains, sum(depenses) AS depenses
  42  FROM past
  43  ;
  44 """
  45
  46 def get_cursor():
  47     """Ouvre une connexion à la bdd."""
  48     con = psycopg2.connect(database="note")
  49     con.set_client_encoding("utf-8")
  50     cur = con.cursor(cursor_factory = psycopg2.extras.DictCursor)
  51     return (con, cur)
  52
  53 def get_depenses(idbde, bymonth=False):
  54     """Récupère toutes les dépenses/gains du compte ``idbde``"""
  55     con, cur = get_cursor()
  56     modifiers = {"select_month" : "", "comma_month" : ""}
  57     params = {"idbde" : idbde}
  58     if bymonth:
  59         modifiers["select_month"] = select_month
  60         modifiers["comma_month"] = comma_month
  61     cur.execute(create_temp_table % modifiers, params)
  62     cur.execute(display_all % modifiers)
  63     all = cur.fetchall()
  64     cur.execute(display_sum)
  65     tot = cur.fetchall()[0]
  66     return (all, tot)
  67
  68 def display_depenses(idbde, all, tot, bymonth=False):
  69     """Pretty-printing des résultats."""
  70     month = "\tmois" if bymonth else ""
  71     print "Compte %s:\nannee%s\tgains\tdépenses" % (idbde, month)
  72     for li in all:
  73         if bymonth:
  74             li = [int(li[0]), int(li[1]), li[2], li[3]]
  75             print "%s\t%s\t%s\t\t%s" % tuple(li)
  76         else:
  77             li = [int(li[0]), li[1], li[2]]
  78             print "%s\t%s\t\t%s" % tuple(li)
  79     print "\nTotal :\n\tgains\tdépenses"
  80     print "\t%s\t\t%s\n" % tuple(tot)
  81
  82 if __name__ == "__main__":
  83     parser = argparse.ArgumentParser(description=__doc__)
  84     parser.add_argument("-m", "--months", help="Faire le calcul par mois", action="store_true")
  85     parser.add_argument('idbdes', type=int, nargs=argparse.ONE_OR_MORE, help="Les idbde dont on veut connaître les dépenses.")
  86
  87     args = parser.parse_args()
  88     for idbde in args.idbdes:
  89         all, tot = get_depenses(idbde, bymonth=args.months)
  90         display_depenses(idbde, all, tot, bymonth=args.months)