From e8ce8490e76a649ac5ff19f9abd723a2745e666c Mon Sep 17 00:00:00 2001 From: Vincent Le Gallic Date: Sat, 14 Feb 2015 17:21:05 +0100 Subject: [PATCH] [depenses.py] WIP --- bde/stats/depenses.py | 35 ++++++++++++++++++++++++++++++++--- 1 file changed, 32 insertions(+), 3 deletions(-) diff --git a/bde/stats/depenses.py b/bde/stats/depenses.py index d429c99..4f6c936 100755 --- a/bde/stats/depenses.py +++ b/bde/stats/depenses.py @@ -1,5 +1,34 @@ -#!/bin/bash +#!/usr/bin/env python +# -*- coding: utf-8 -*- -# Affiche les dépenses totales d'un compte +"""Affiche les dépenses totales d'un compte par année + total""" -psql bde -c "SELECT sum(montant*quantite) FROM transactions WHERE valide AND emetteur=$1;" +create_temp_table = """ + SELECT * --, sum(gains), sum(depenses) + INTO past + FROM + (SELECT extract(year FROM date) AS annee, + CAST(sum( + CASE WHEN emetteur=$1 THEN montant*quantite + ELSE 0 + END + )/100.0 AS numeric(10,2)) AS depenses, + CAST(sum( + CASE WHEN destinataire=$1 THEN montant*quantite + ELSE 0 + END + )/100.0 AS numeric(10,2)) AS gains + FROM transactions + WHERE valide AND $1 in (emetteur, destinataire) + GROUP BY annee) AS pif + ; +""" + +display_all = """ SELECT * FROM past + ; +""" + +display_sum = """ SELECT sum(gains) AS gains, sum(depenses) AS depenses + FROM past + ; +""" -- 2.39.2