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 create_temp_table = """
   7  SELECT * --, sum(gains), sum(depenses)
   8  INTO past
   9  FROM
  10    (SELECT extract(year FROM date) AS annee,
  11           CAST(sum(
  12                    CASE WHEN emetteur=$1 THEN montant*quantite
  13                         ELSE 0
  14                    END
  15                   )/100.0 AS numeric(10,2)) AS depenses,
  16           CAST(sum(
  17                    CASE WHEN destinataire=$1 THEN montant*quantite
  18                         ELSE 0
  19                    END
  20                   )/100.0 AS numeric(10,2)) AS gains
  21    FROM transactions
  22    WHERE valide AND $1 in (emetteur, destinataire)
  23    GROUP BY annee) AS pif
  24  ;
  25 """
  26
  27 display_all = """ SELECT * FROM past
  28  ;
  29 """
  30
  31 display_sum = """ SELECT sum(gains) AS gains, sum(depenses) AS depenses
  32  FROM past
  33  ;
  34 """