1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
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
|