1 # -*- coding: utf-8 -*-
3 from sqlite3 import connect
5 class FileNotSetError(ValueError):
8 class ArgCountError(ValueError):
12 def __init__(self, dbpath=False):
14 self.con = connect(dbpath)
17 raise FileNotSetError("Database file must be set")
18 self.cur = self.con.cursor()
25 def get_top_drinks(self):
26 """Return list of pairs of drink name and count."""
29 SELECT count(*), flavor
32 datetime('now', 'localtime', '-7 days')
33 AND datetime('now', 'localtime')
38 for (cnt, dn) in self.cur.execute(q):
40 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
43 def getTopMateDrinkers(self):
44 """Return list of pairs of name, count for Mate drinkers."""
48 SELECT count(*), users.name FROM coffees
49 LEFT JOIN identifiers on coffees.id = identifiers.id
50 LEFT JOIN users on identifiers.userid = users.id
51 WHERE flavor = 'Club-Mate 0,5 l'
52 AND coffees.time BETWEEN
53 datetime('now', 'localtime', '-7 days') AND
54 datetime('now', 'localtime')
55 GROUP BY identifiers.userid
58 for (cnt, un) in self.cur.execute(que):
62 SELECT count(*), users.name FROM coffees
63 LEFT JOIN identifiers on coffees.id = identifiers.id
64 LEFT JOIN users on identifiers.userid = users.id
65 WHERE flavor = 'Club-Mate 0,33 l'
66 AND coffees.time BETWEEN
67 datetime('now', 'localtime', '-7 days') AND
68 datetime('now', 'localtime')
69 GROUP BY identifiers.userid
72 for (cnt, un) in self.cur.execute(que):
74 users[un] += cnt * 0.33
76 users[un] = cnt * 0.33
78 for (un, cnt) in users.items():
80 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
83 def get_top_tea_drinkers(self):
84 """Return list of pairs of name, count for tea drinkers."""
87 SELECT count(*), users.name FROM coffees
88 LEFT JOIN identifiers on coffees.id = identifiers.id
89 LEFT JOIN users on identifiers.userid = users.id
91 AND coffees.time BETWEEN
92 datetime('now', 'localtime', '-7 days') AND
93 datetime('now', 'localtime')
94 GROUP BY identifiers.userid
98 for (cnt, un) in self.cur.execute(q):
100 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
103 def getDrunkSum(self, *args, **kwargs):
104 """Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.
107 flavor -- The flavor of beverage.
108 dtf -- Date and time *from*.
109 dtt -- Date and time *to*.
111 if not ((len(args) == 3 and len(kwargs) == 0) or
112 (len(args) == 0 and len(kwargs) == 3)):
113 raise ArgCountError("3 arguments needed: flavor, from, and to")
119 flavor = kwargs["flavor"]
122 flavors = flavor.split(";")
124 SELECT count(*), users.name FROM coffees
125 INNER JOIN users ON coffees.id = users.id
129 que += "WHERE flavor = '{}'".format(f)
131 que += "OR flavor = '{}'".format(f)
133 AND coffees.time BETWEEN
134 datetime('{}', 'localtime') AND
135 datetime('{}', 'localtime')
139 for (cnt, un) in self.cur.execute(que):
140 drunk.append((un, cnt))
143 def getDrunkList(self, *args, **kwargs):
144 """Return dict of lists of drunken ``flavor`` from ``dtf`` to ``dtt``.
147 flavor -- The flavor of beverage.
148 dtf -- Date and time *from*.
149 dtt -- Date and time *to*.
151 if not ((len(args) == 3 and len(kwargs) == 0) or
152 (len(args) == 0 and len(kwargs) == 3)):
153 raise ArgCountError("3 arguments needed: flavor, from, and to")
159 flavor = kwargs["flavor"]
162 flavors = flavor.split(";")
168 SELECT count(*), users.name FROM coffees
169 LEFT JOIN identifiers on coffees.id = identifiers.id
170 LEFT JOIN users on identifiers.userid = users.id
172 AND coffees.time BETWEEN
173 datetime('{}', 'localtime') AND
174 datetime('{}', 'localtime')
175 GROUP BY identifiers.userid
177 """.format(f, dtf, dtt)
178 for (cnt, un) in self.cur.execute(que):
180 drunk[un] = [0 for j in range(i)]
181 drunk[un].append(cnt)