class FileNotSetError(ValueError):
pass
+class ArgCountError(ValueError):
+ pass
+
class Db:
def __init__(self, dbpath=False):
if dbpath:
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
+
+ def getDrunkSum(self, *args, **kwargs):
+ """Return list 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(";")
+ que = """
+ SELECT count(*), users.name FROM coffees
+ INNER JOIN users ON coffees.id = users.id
+ """
+ for f in flavors:
+ if f is flavors[0]:
+ que += "WHERE flavor = '{}'".format(f)
+ else:
+ que += "OR flavor = '{}'".format(f)
+ que += """
+ AND coffees.time BETWEEN
+ datetime('{}', 'localtime') AND
+ datetime('{}', 'localtime')
+ GROUP BY coffees.id
+ """.format(dtf, dtt)
+ drunk = []
+ 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 = {}
+ 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] = []
+ drunk[un].append(cnt)
+ return drunk