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