def __del__(self):
if self.con:
self.con.close()
+
+ def getTopMateDrinkers(self):
+ """Return list of pairs of name, count for Mate drinkers."""
+ users = {}
+ que = """
+ SELECT count(*), users.name FROM coffees
+ INNER JOIN users ON coffees.id = users.id
+ WHERE flavor = 'Club-Mate 0,5 l'
+ AND coffees.time BETWEEN
+ datetime('now', 'localtime', '-7 days') AND
+ datetime('now', 'localtime')
+ GROUP BY coffees.id
+ """
+ for (cnt, un) in self.cur.execute(que):
+ users[un] = cnt * 0.5
+ que = """
+ SELECT count(*), users.name FROM coffees
+ INNER JOIN users ON coffees.id = users.id
+ WHERE flavor = 'Club-Mate 0,33 l'
+ AND coffees.time BETWEEN
+ datetime('now', 'localtime', '-7 days') AND
+ datetime('now', 'localtime')
+ GROUP BY coffees.id
+ """
+ for (cnt, un) in self.cur.execute(que):
+ if un in users:
+ users[un] += cnt * 0.33
+ else:
+ users[un] = cnt * 0.33
+ top = []
+ for (un, cnt) in users.items():
+ top.append((un, cnt))
+ top.sort(key=lambda x: (x[1], x[0]), reverse=True)
+ return top