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
  11 create_temp_table = """
  12  SELECT extract(year FROM date) AS annee,
  13        CAST(sum(
  14                 CASE WHEN emetteur = %(idbde)s THEN montant * quantite
  15                      ELSE 0
  16                 END
  17                )/100.0 AS numeric(10,2)) AS depenses,
  18        CAST(sum(
  19                 CASE WHEN destinataire = %(idbde)s THEN montant * quantite
  20                      ELSE 0
  21                 END
  22                )/100.0 AS numeric(10,2)) AS gains
  23  INTO past
  24  FROM transactions
  25  WHERE valide AND %(idbde)s in (emetteur, destinataire)
  26  GROUP BY annee
  27  ;
  28 """
  29
  30 display_all = """ SELECT * FROM past
  31  ORDER BY annee
  32  ;
  33 """
  34
  35 display_sum = """ SELECT sum(gains) AS gains, sum(depenses) AS depenses
  36  FROM past
  37  ;
  38 """
  39
  40 def get_cursor():
  41     """Ouvre une connexion à la bdd."""
  42     con = psycopg2.connect(database="note")
  43     con.set_client_encoding("utf-8")
  44     cur = con.cursor(cursor_factory = psycopg2.extras.DictCursor)
  45     return (con, cur)
  46
  47 def get_depenses(idbde):
  48     """Récupère toutes les dépenses/gains du compte ``idbde``"""
  49     con, cur = get_cursor()
  50     cur.execute(create_temp_table, {"idbde" : idbde})
  51     cur.execute(display_all)
  52     l = cur.fetchall()
  53     print "annee\tdepenses\tgains"
  54     for li in l:
  55         li = [int(li[0]), li[1], li[2]]
  56         print "%s\t%s\t\t%s" % tuple(li)
  57     cur.execute(display_sum)
  58     l = cur.fetchall()
  59     print "\nTotal :\n\tdepenses\tgains"
  60     print "\t%s\t\t%s" % tuple(l[0])
  61
  62 if __name__ == "__main__":
  63     idbde = int(sys.argv[1])
  64     get_depenses(idbde)