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
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}'"
41 f = f"WHERE flavor = '{b}'"
44 SELECT count(*), users.name FROM coffees
45 LEFT JOIN identifiers on coffees.id = identifiers.userid
46 LEFT JOIN users on identifiers.userid = users.id
48 AND coffees.time BETWEEN
51 GROUP BY identifiers.userid
57 def __init__(self, db_path):
58 self.con = connect(db_path)
59 self.cur = self.con.cursor()
65 def get_top_drinks(self):
68 for (cnt, dn) in self.cur.execute(q):
70 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
73 def get_top_mate_drinkers(self):
74 """Return list of pairs of name, count for Mate drinkers."""
76 que = Q("get_drinkers_of", "Club-Mate 0,5 l")
77 for (cnt, un) in self.cur.execute(que):
79 que = Q("get_drinkers_of", "Club-Mate 0,33 l")
80 for (cnt, un) in self.cur.execute(que):
82 users[un] += cnt * 0.33
84 users[un] = cnt * 0.33
86 for (un, cnt) in users.items():
88 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
91 def get_top_tea_drinkers(self):
92 """Return list of pairs of name, count for tea drinkers."""
93 q = Q("get_drinkers_of", "tea")
95 for (cnt, un) in self.cur.execute(q):
97 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
100 def get_drunk_sum(self, flavor, dtf, dtt):
101 """Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.
104 flavor -- The flavor of beverage.
105 dtf -- Date and time *from*.
106 dtt -- Date and time *to*.
108 que = Q("get_drinkers_of", flavor.split(";"), (dtf, dtt))
110 for (cnt, un) in self.cur.execute(que):
111 drunk.append((un, cnt))
114 def get_drunk_list(self, flavor, dtf, dtt):
115 """Return dict of lists of drunken ``flavor`` from ``dtf`` to ``dtt``.
118 flavor -- The flavor of beverage.
119 dtf -- Date and time *from*.
120 dtt -- Date and time *to*.
122 flavors = flavor.split(";")
126 que = Q("get_drinkers_of", f, (dtf, dtt))
127 for (cnt, un) in self.cur.execute(que):
129 drunk[un] = [0 for j in range(i)]
130 drunk[un].append(cnt)