if self.con:
self.con.close()
+ def get_top_drinks(self):
+ """Return list of pairs of drink name and count."""
+ q = """
+
+ SELECT count(*), flavor
+ FROM coffees
+ WHERE time BETWEEN
+ datetime('now', 'localtime', '-7 days')
+ AND datetime('now', 'localtime')
+ GROUP BY flavor
+
+ """
+ top = []
+ for (cnt, dn) in self.cur.execute(q):
+ top.append((dn, cnt))
+ top.sort(key=lambda x: (x[1], x[0]), reverse=True)
+ return top
+
def getTopMateDrinkers(self):
"""Return list of pairs of name, count for Mate drinkers."""
users = {}
top.sort(key=lambda x: (x[1], x[0]), reverse=True)
return top
+ def get_top_tea_drinkers(self):
+ """Return list of pairs of name, count for tea drinkers."""
+ q = """
+
+ SELECT count(*), users.name FROM coffees
+ INNER JOIN users ON coffees.id = users.id
+ WHERE flavor = 'tea'
+ AND coffees.time BETWEEN
+ datetime('now', 'localtime', '-7 days') AND
+ datetime('now', 'localtime')
+ GROUP BY coffees.id
+
+ """
+ top = []
+ for (cnt, un) in self.cur.execute(q):
+ top.append((un, cnt))
+ top.sort(key=lambda x: (x[1], x[0]), reverse=True)
+ return top
+
def getDrunkSum(self, *args, **kwargs):
"""Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.
for (cnt, un) in self.cur.execute(que):
drunk.append((un, cnt))
return drunk
+
+ def getDrunkList(self, *args, **kwargs):
+ """Return dict of lists of drunken ``flavor`` from ``dtf`` to ``dtt``.
+
+ Keyword arguments:
+ flavor -- The flavor of beverage.
+ dtf -- Date and time *from*.
+ dtt -- Date and time *to*.
+ """
+ if not ((len(args) == 3 and len(kwargs) == 0) or
+ (len(args) == 0 and len(kwargs) == 3)):
+ raise ArgCountError("3 arguments needed: flavor, from, and to")
+ if args:
+ flavor = args[0]
+ dtf = args[1]
+ dtt = args[2]
+ elif kwargs:
+ flavor = kwargs["flavor"]
+ dtf = kwargs["dtf"]
+ dtt = kwargs["dtt"]
+ flavors = flavor.split(";")
+ drunk = {}
+ 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
+ """.format(f, dtf, dtt)
+ for (cnt, un) in self.cur.execute(que):
+ if not un in drunk:
+ drunk[un] = [0 for j in range(i)]
+ drunk[un].append(cnt)
+ i += 1
+ return drunk