1 from sqlite3 import connect
4 def Q(q, b="", t=["now", "-7 days"]):
5 """Return db query identified by `q`.
7 :param q: Identifier of query.
8 :param b: Optionally, specify the beverage(s).
9 :param t: Optionally, specify the time range.
11 assert isinstance(t, tuple) or isinstance(t, list)
14 dtf = f"datetime('now', 'localtime', '{t[1]}')"
15 dtt = "datetime('now', 'localtime')"
17 dtf = f"datetime('now', 'localtime', '{t[0]}')"
18 dtt = "datetime('now', 'localtime')"
20 dtf = f"datetime('{t[0]}', 'localtime')"
21 dtt = f"datetime('{t[1]}', 'localtime')"
25 SELECT count(*), flavor
27 WHERE (coffees.time BETWEEN
33 elif q == "get_drinkers_of":
34 if isinstance(b, tuple) or isinstance(b, list):
36 f = f"WHERE (flavor = '{b[0]}'"
38 f += f" OR flavor = '{i}'"
42 f = f"WHERE flavor = '{b}'"
45 SELECT count(*), users.name FROM coffees
46 INNER JOIN identifiers on coffees.id = identifiers.id
47 INNER JOIN users on identifiers.userid = users.id
49 AND (coffees.time BETWEEN
58 def __init__(self, db_path):
59 self.con = connect(db_path)
60 self.cur = self.con.cursor()
66 def get_top_drinks(self):
69 for (cnt, dn) in self.cur.execute(q):
71 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
74 def get_top_mate_drinkers(self):
75 """Return list of pairs of name, count for Mate drinkers."""
77 que = Q("get_drinkers_of", "Club-Mate 0,5 l")
78 for (cnt, un) in self.cur.execute(que):
80 que = Q("get_drinkers_of", "Club-Mate 0,33 l")
81 for (cnt, un) in self.cur.execute(que):
83 users[un] += cnt * 0.33
85 users[un] = cnt * 0.33
87 for (un, cnt) in users.items():
89 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
92 def get_top_tea_drinkers(self):
93 """Return list of pairs of name, count for tea drinkers."""
94 q = Q("get_drinkers_of", "tea")
96 for (cnt, un) in self.cur.execute(q):
98 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
101 def get_drunk_sum(self, flavor, dtf, dtt):
102 """Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.
105 flavor -- The flavor of beverage.
106 dtf -- Date and time *from*.
107 dtt -- Date and time *to*.
109 que = Q("get_drinkers_of", flavor.split(";"), (dtf, dtt))
111 for (cnt, un) in self.cur.execute(que):
112 drunk.append((un, cnt))
115 def get_drunk_list(self, flavor, dtf, dtt):
116 """Return dict of lists of drunken ``flavor`` from ``dtf`` to ``dtt``.
119 flavor -- The flavor of beverage.
120 dtf -- Date and time *from*.
121 dtt -- Date and time *to*.
123 flavors = flavor.split(";")
127 que = Q("get_drinkers_of", f, (dtf, dtt))
128 for (cnt, un) in self.cur.execute(que):
130 drunk[un] = [0 for j in range(i)]
131 drunk[un].append(cnt)