From ce1b833aec12f16c066c11c9b049f5c1270fd8ec Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Fri, 27 Jul 2012 02:38:27 +0200 Subject: fix sum calculation --- invoice/graph/graphs.py | 22 ++++++++++++++++++---- 1 file changed, 18 insertions(+), 4 deletions(-) (limited to 'invoice') diff --git a/invoice/graph/graphs.py b/invoice/graph/graphs.py index 5fdaee3..d1e8444 100755 --- a/invoice/graph/graphs.py +++ b/invoice/graph/graphs.py @@ -4,11 +4,18 @@ import cairoplot, datetime, sqlite3, time def TortendiagramUser(): data = {} + now = int(time.time()) + + query = "SELECT users.id, SUM(memberprice) FROM users, purchases purch, prices \ + WHERE users.id = purch.user AND purch.product = prices.product AND \ + purch.timestamp > ? AND purch.timestamp < ? AND prices.valid_from = \ + (SELECT valid_from FROM prices WHERE product = purch.product AND \ + valid_from < purch.timestamp ORDER BY valid_from DESC LIMIT 1) \ + GROUP BY users.id" connection = sqlite3.connect('shop.db') c = connection.cursor() - c.execute("SELECT users.id, SUM(prices.memberprice) FROM users, purchases, prices " + - "WHERE users.id = purchases.user AND purchases.product = prices.product GROUP BY users.id") + c.execute(query, (0, now)) for row in c: data["%d (%d.%d Euro)" %(row[0], row[1] / 100, row[1] % 100)] = row[1] c.close() @@ -18,11 +25,18 @@ def TortendiagramUser(): def BalkendiagramUserRanking(): data = {} names = [] + now = int(time.time()) + + query = "SELECT firstname, lastname, SUM(memberprice) FROM users, purchases purch, prices \ + WHERE users.id = purch.user AND purch.product = prices.product AND \ + purch.timestamp > ? AND purch.timestamp < ? AND prices.valid_from = \ + (SELECT valid_from FROM prices WHERE product = purch.product AND \ + valid_from < purch.timestamp ORDER BY valid_from DESC LIMIT 1) \ + GROUP BY users.id" connection = sqlite3.connect('shop.db') c = connection.cursor() - c.execute("SELECT users.firstname, users.lastname, SUM(prices.memberprice) FROM users, purchases, prices " + - "WHERE users.id = purchases.user AND purchases.product = prices.product GROUP BY users.id") + c.execute(query, (0, now)) for row in c: data["%s %s (%d.%d Euro)" % (row[0], row[1], row[2] / 100, row[2] % 100)] = row[2] c.close() -- cgit v1.2.3