summaryrefslogtreecommitdiffstats
path: root/invoice
diff options
context:
space:
mode:
authorSebastian Reichel <sre@ring0.de>2012-07-27 02:38:27 +0200
committerSebastian Reichel <sre@ring0.de>2012-07-27 02:38:27 +0200
commitce1b833aec12f16c066c11c9b049f5c1270fd8ec (patch)
treee97540150122f3a7c0c7d567c5c6cb715c157a6a /invoice
parent721b2a224469723f46e4577b9d75f92cf934fa6e (diff)
downloadserial-barcode-scanner-ce1b833aec12f16c066c11c9b049f5c1270fd8ec.tar.bz2
fix sum calculation
Diffstat (limited to 'invoice')
-rwxr-xr-xinvoice/graph/graphs.py22
1 files changed, 18 insertions, 4 deletions
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()