1 files changed, 141 insertions, 0 deletions
diff --git a/invoice/graph/graphs.py b/invoice/graph/graphs.py
new file mode 100755
index 0000000..051a455
--- /dev/null
+++ b/invoice/graph/graphs.py
@@ -0,0 +1,141 @@
+#!/usr/bin/python
+# -*- coding: utf-8 -*-
+import cairoplot, datetime, sqlite3, time
+
+def TortendiagramUser():
+	data = {}
+
+	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")
+	for row in c:
+		data["%d (%d.%d Euro)" %(row[0], row[1] / 100, row[1] % 100)] = row[1]
+	c.close()
+
+	cairoplot.pie_plot("tortendiagram", data, 640, 480)
+
+def TortendiagramUserRanking():
+	data = {}
+	names = []
+
+	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")
+	for row in c:
+		data["%s %s (%d.%d Euro)" % (row[0], row[1], row[2] / 100, row[2] % 100)] = row[2]
+	c.close()
+
+	count=0
+	sorted_data = []
+	for key, value in sorted(data.iteritems(), key=lambda (k,v): (v,k), reverse=True):
+		sorted_data.append(value)
+		names.append(key)
+		count+=1
+		if count >= 10:
+			break
+
+	cairoplot.horizontal_bar_plot("ranking", sorted_data, 640, 480, y_labels = names, rounded_corners = True, grid = True)
+
+def TortendiagramProduct():
+	data = {}
+
+	connection = sqlite3.connect('shop.db')
+	c = connection.cursor()
+	c.execute("SELECT products.name, SUM(1) FROM products, purchases " +
+			  "WHERE products.id = purchases.product GROUP BY products.id")
+	for row in c:
+		data[row[0]] = row[1]
+	c.close()
+
+	cairoplot.pie_plot("tortendiagram2", data, 640, 480)
+
+def Lagerbestand(category):
+	data = {}
+	translation = {}
+
+	day = 24 * 60 * 60
+	now = int(time.time())
+	
+	dates = []
+	dt = datetime.datetime.fromtimestamp(now)
+	dates.append("%04d-%02d-%02d" % (dt.year, dt.month, dt.day))
+
+	colors = [
+		"black",
+		"red",
+		"green",
+		"blue",
+		"orange",
+		(117/255.0, 255/255.0, 20/255.0),
+		(216/255.0, 20/255.0, 255/255.0),
+		(204/255.0, 153/255.0, 0/255.0),
+		(0/255.0, 204/255.0, 255/255.0),
+		(153/255.0, 77/255.0, 0/255.0),
+		(128/255.0, 0/255.0, 128/255.0),
+		(204/255.0, 0/255.0, 0/255.0),
+		(0/255.0, 0/255.0, 102/255.0),
+		"yellow",
+	]
+
+	connection = sqlite3.connect('shop.db')
+	c = connection.cursor()
+	query = ""
+
+	if category == "getraenke":
+		query = "name LIKE '%Mate%' OR name LIKE '%Coca Cola%' OR name LIKE '%Vilsa%' OR name = 'Fanta' OR name = 'Sprite'"
+	elif category == "haribo":
+		query = "name LIKE '%Haribo%'"
+	elif category == "riegel":
+		query = "name LIKE '%KitKat%' OR name = 'Lion' OR name LIKE '%Snickers%' OR name = 'Mars' OR name = 'Twix' OR name = 'Duplo'"
+	elif category == "other":
+		query = "name LIKE '%Gouda%' OR name LIKE '%Chipsfrisch%' OR name LIKE '%Sesamsticks%'"
+	elif category == "schoko":
+		query = "name = 'Ü-Ei' OR name LIKE '%Tender%' OR name = 'Knoppers' OR name LIKE '%m&m%'"
+	else:
+		return
+
+	c.execute("SELECT name, amount, id FROM products WHERE (%s) AND amount > 0" % query);
+
+	for row in c:
+		data[row[0]] = [int(row[1])]
+		translation[row[2]] = row[0]
+	
+	current = now
+	currentid = 1
+	while current > (now - 21 * day):
+		for k, v in data.iteritems():
+			data[k].append(v[-1])
+
+		dt = datetime.datetime.fromtimestamp(current - day)
+		dates.append("%04d-%02d-%02d" % (dt.year, dt.month, dt.day))
+
+		c.execute("SELECT name, SUM(restock.amount) FROM products, restock WHERE products.id = restock.product AND timestamp > ? AND timestamp < ? GROUP BY name", (current - day, current));
+		for row in c:
+			if row[0] in data:
+				data[row[0]][currentid] -= row[1]
+		c.execute("SELECT name, SUM(1) FROM products, purchases WHERE products.id = purchases.product AND timestamp > ? AND timestamp < ? GROUP BY name", (current - day, current));
+		for row in c:
+			if row[0] in data:
+				data[row[0]][currentid] += row[1]
+
+		current -= day
+		currentid += 1
+	
+	for k, v in data.iteritems():
+		data[k].reverse()
+	dates.reverse()
+
+	c.close()
+	cairoplot.dot_line_plot("lagerbestand_%s" % category, data, 640, 480, series_colors = colors, x_labels = dates, y_title = "Anzahl", axis=True, grid=True, series_legend = True)
+
+
+data = [ "getraenke", "haribo", "riegel", "other", "schoko" ]
+
+TortendiagramUser()
+TortendiagramProduct()
+TortendiagramUserRanking()
+
+for x in data:
+	Lagerbestand(x)