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