From b55a06210ac8551ac0d9a29ddfaad764f584849e Mon Sep 17 00:00:00 2001
From: Sebastian Reichel <sre@ring0.de>
Date: Sat, 23 Feb 2013 18:25:51 +0100
Subject: out all db related methods into a class

---
 invoice/dbhelper.py | 80 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 80 insertions(+)
 create mode 100644 invoice/dbhelper.py

(limited to 'invoice/dbhelper.py')

diff --git a/invoice/dbhelper.py b/invoice/dbhelper.py
new file mode 100644
index 0000000..b04030d
--- /dev/null
+++ b/invoice/dbhelper.py
@@ -0,0 +1,80 @@
+#!/usr/bin/env python3
+#-*- coding: utf-8 -*-
+
+import sqlite3
+
+class DB(object):
+	def __init__(self, dbfile='shop.db'):
+		self.__connection = sqlite3.connect(dbfile)
+	
+	def get_user_info(self, userid):
+		c = self.__connection.cursor()
+		c.execute("SELECT id, email, firstname, lastname, gender, street, plz, city FROM users WHERE id = ?;", (userid,))
+		row = c.fetchone()
+		c.close()
+
+		if row is None:
+			return None
+		else:
+			return {
+				"id": row[0],
+				"email": row[1],
+				"firstname": row[2],
+				"lastname": row[3],
+				"gender": row[4],
+				"street": row[5],
+				"plz": row[6],
+				"city": row[7]
+			}
+
+	def get_invoice_data(self, user, start=0, stop=0):
+		c = self.__connection.cursor()
+		startcondition = ""
+		stopcondition = ""
+
+		if start > 0:
+			startcondition = " AND timestamp >= %d" % start
+		if stop > 0:
+			stopcondition = " AND timestamp <= %d" % stop
+
+		c.execute("SELECT date(timestamp, 'unixepoch', 'localtime'), time(timestamp, 'unixepoch', 'localtime'), productname, price FROM invoice WHERE user = ?" + startcondition + stopcondition + " ORDER BY timestamp;", (user,))
+
+		result = []
+		for row in c:
+			result.append({
+				"date": row[0],
+				"time": row[1],
+				"product": row[2],
+				"price": row[3],
+			})
+
+		c.close()
+
+		return result
+
+	def get_invoice_amount(self, user, start=0, stop=0):
+		query = "SELECT SUM(price) FROM invoice WHERE user = ? AND timestamp >= ? AND timestamp <= ?";
+		amount = 0
+
+		c = self.__connection.cursor()
+		c.execute(query, (user, start, stop))
+
+		for row in c:
+			amount += row[0]
+
+		c.close()
+		return amount
+
+	def get_users_with_purchases(self, start, stop):
+		result = []
+
+		c = self.__connection.cursor()
+
+		c.execute("SELECT user FROM sales WHERE timestamp >= ? AND timestamp <= ? GROUP BY user ORDER BY user;", (start,stop))
+
+		for row in c:
+			result.append(row[0])
+
+		c.close()
+
+		return result
-- 
cgit v1.2.3