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."""
47 SELECT count(*), users.name FROM coffees
48 INNER JOIN users ON coffees.id = users.id
49 WHERE flavor = 'Club-Mate 0,5 l'
50 AND coffees.time BETWEEN
51 datetime('now', 'localtime', '-7 days') AND
52 datetime('now', 'localtime')
55 for (cnt, un) in self.cur.execute(que):
58 SELECT count(*), users.name FROM coffees
59 INNER JOIN users ON coffees.id = users.id
60 WHERE flavor = 'Club-Mate 0,33 l'
61 AND coffees.time BETWEEN
62 datetime('now', 'localtime', '-7 days') AND
63 datetime('now', 'localtime')
66 for (cnt, un) in self.cur.execute(que):
68 users[un] += cnt * 0.33
70 users[un] = cnt * 0.33
72 for (un, cnt) in users.items():
74 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
77 def get_top_tea_drinkers(self):
78 """Return list of pairs of name, count for tea drinkers."""
81 SELECT count(*), users.name FROM coffees
82 INNER JOIN users ON coffees.id = users.id
84 AND coffees.time BETWEEN
85 datetime('now', 'localtime', '-7 days') AND
86 datetime('now', 'localtime')
91 for (cnt, un) in self.cur.execute(q):
93 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
96 def getDrunkSum(self, *args, **kwargs):
97 """Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.
100 flavor -- The flavor of beverage.
101 dtf -- Date and time *from*.
102 dtt -- Date and time *to*.
104 if not ((len(args) == 3 and len(kwargs) == 0) or
105 (len(args) == 0 and len(kwargs) == 3)):
106 raise ArgCountError("3 arguments needed: flavor, from, and to")
112 flavor = kwargs["flavor"]
115 flavors = flavor.split(";")
117 SELECT count(*), users.name FROM coffees
118 INNER JOIN users ON coffees.id = users.id
122 que += "WHERE flavor = '{}'".format(f)
124 que += "OR flavor = '{}'".format(f)
126 AND coffees.time BETWEEN
127 datetime('{}', 'localtime') AND
128 datetime('{}', 'localtime')
132 for (cnt, un) in self.cur.execute(que):
133 drunk.append((un, cnt))
136 def getDrunkList(self, *args, **kwargs):
137 """Return dict of lists of drunken ``flavor`` from ``dtf`` to ``dtt``.
140 flavor -- The flavor of beverage.
141 dtf -- Date and time *from*.
142 dtt -- Date and time *to*.
144 if not ((len(args) == 3 and len(kwargs) == 0) or
145 (len(args) == 0 and len(kwargs) == 3)):
146 raise ArgCountError("3 arguments needed: flavor, from, and to")
152 flavor = kwargs["flavor"]
155 flavors = flavor.split(";")
160 SELECT count(*), users.name FROM coffees
161 INNER JOIN users ON coffees.id = users.id
163 AND coffees.time BETWEEN
164 datetime('{}', 'localtime') AND
165 datetime('{}', 'localtime')
167 """.format(f, dtf, dtt)
168 for (cnt, un) in self.cur.execute(que):
170 drunk[un] = [0 for j in range(i)]
171 drunk[un].append(cnt)