]>
gitweb.pimeys.fr Git - scripts-20-100.git/blob - bde/stats/depenses.py
2 # -*- coding: utf-8 -*-
4 """Affiche les dépenses totales d'un compte par année + total"""
12 create_temp_table
= """
13 SELECT extract(year FROM date) AS annee,
16 CASE WHEN emetteur = %%(idbde)s THEN montant * quantite
19 )/100.0 AS numeric(10,2)) AS depenses,
21 CASE WHEN destinataire = %%(idbde)s THEN montant * quantite
24 )/100.0 AS numeric(10,2)) AS gains
27 WHERE valide AND %%(idbde)s in (emetteur, destinataire)
28 GROUP BY annee%(comma_month)s
32 select_month
= """extract(month FROM date) AS mois,"""
34 comma_month
= """, mois"""
36 display_all
= """ SELECT * FROM past
37 ORDER BY annee%(comma_month)s
41 display_sum
= """ SELECT sum(gains) AS gains, sum(depenses) AS depenses
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
)
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
}
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
)
64 cur
.execute(display_sum
)
65 tot
= cur
.fetchall()[0]
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
)
74 li
= [int(li
[0]), int(li
[1]), li
[2], li
[3]]
75 print "%s\t%s\t%s\t\t%s" % tuple(li
)
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
)
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.")
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
)