"""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
+
+ SELECT count(*), users.name FROM coffees
+ LEFT JOIN identifiers on coffees.id = identifiers.id
+ LEFT JOIN users on identifiers.userid = users.id
+ WHERE flavor = 'Club-Mate 0,5 l'
+ AND coffees.time BETWEEN
+ datetime('now', 'localtime', '-7 days') AND
+ datetime('now', 'localtime')
+ GROUP BY identifiers.userid
+
"""
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
+
+ SELECT count(*), users.name FROM coffees
+ LEFT JOIN identifiers on coffees.id = identifiers.id
+ LEFT JOIN users on identifiers.userid = users.id
+ WHERE flavor = 'Club-Mate 0,33 l'
+ AND coffees.time BETWEEN
+ datetime('now', 'localtime', '-7 days') AND
+ datetime('now', 'localtime')
+ GROUP BY identifiers.userid
+
"""
for (cnt, un) in self.cur.execute(que):
if un in users:
q = """
SELECT count(*), users.name FROM coffees
- INNER JOIN users ON coffees.id = users.id
+ LEFT JOIN identifiers on coffees.id = identifiers.id
+ LEFT JOIN users on identifiers.userid = users.id
WHERE flavor = 'tea'
AND coffees.time BETWEEN
datetime('now', 'localtime', '-7 days') AND
datetime('now', 'localtime')
- GROUP BY coffees.id
+ GROUP BY identifiers.userid
"""
top = []
i = 0
for f in flavors:
que = """
- SELECT count(*), users.name FROM coffees
- INNER JOIN users ON coffees.id = users.id
- WHERE flavor = '{}'
- AND coffees.time BETWEEN
- datetime('{}', 'localtime') AND
- datetime('{}', 'localtime')
- GROUP BY coffees.id
+
+ SELECT count(*), users.name FROM coffees
+ LEFT JOIN identifiers on coffees.id = identifiers.id
+ LEFT JOIN users on identifiers.userid = users.id
+ WHERE flavor = '{}'
+ AND coffees.time BETWEEN
+ datetime('{}', 'localtime') AND
+ datetime('{}', 'localtime')
+ GROUP BY identifiers.userid
+
""".format(f, dtf, dtt)
for (cnt, un) in self.cur.execute(que):
if not un in drunk: