1 # -*- coding: utf-8 -*-
3 from sqlite3 import connect
5 class FileNotSetError(ValueError):
8 class ArgCountError(ValueError):
12 def __init__(self, dbpath=False):
14 self.con = connect(dbpath)
17 raise FileNotSetError("Database file must be set")
18 self.cur = self.con.cursor()
25 def getTopMateDrinkers(self):
26 """Return list of pairs of name, count for Mate drinkers."""
29 SELECT count(*), users.name FROM coffees
30 INNER JOIN users ON coffees.id = users.id
31 WHERE flavor = 'Club-Mate 0,5 l'
32 AND coffees.time BETWEEN
33 datetime('now', 'localtime', '-7 days') AND
34 datetime('now', 'localtime')
37 for (cnt, un) in self.cur.execute(que):
40 SELECT count(*), users.name FROM coffees
41 INNER JOIN users ON coffees.id = users.id
42 WHERE flavor = 'Club-Mate 0,33 l'
43 AND coffees.time BETWEEN
44 datetime('now', 'localtime', '-7 days') AND
45 datetime('now', 'localtime')
48 for (cnt, un) in self.cur.execute(que):
50 users[un] += cnt * 0.33
52 users[un] = cnt * 0.33
54 for (un, cnt) in users.items():
56 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
59 def getDrunkSum(self, *args, **kwargs):
60 """Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.
63 flavor -- The flavor of beverage.
64 dtf -- Date and time *from*.
65 dtt -- Date and time *to*.
67 if not ((len(args) == 3 and len(kwargs) == 0) or
68 (len(args) == 0 and len(kwargs) == 3)):
69 raise ArgCountError("3 arguments needed: flavor, from, and to")
75 flavor = kwargs["flavor"]
78 flavors = flavor.split(";")
80 SELECT count(*), users.name FROM coffees
81 INNER JOIN users ON coffees.id = users.id
85 que += "WHERE flavor = '{}'".format(f)
87 que += "OR flavor = '{}'".format(f)
89 AND coffees.time BETWEEN
90 datetime('{}', 'localtime') AND
91 datetime('{}', 'localtime')
95 for (cnt, un) in self.cur.execute(que):
96 drunk.append((un, cnt))
99 def getDrunkList(self, *args, **kwargs):
100 """Return dict of lists of drunken ``flavor`` from ``dtf`` to ``dtt``.
103 flavor -- The flavor of beverage.
104 dtf -- Date and time *from*.
105 dtt -- Date and time *to*.
107 if not ((len(args) == 3 and len(kwargs) == 0) or
108 (len(args) == 0 and len(kwargs) == 3)):
109 raise ArgCountError("3 arguments needed: flavor, from, and to")
115 flavor = kwargs["flavor"]
118 flavors = flavor.split(";")
123 SELECT count(*), users.name FROM coffees
124 INNER JOIN users ON coffees.id = users.id
126 AND coffees.time BETWEEN
127 datetime('{}', 'localtime') AND
128 datetime('{}', 'localtime')
130 """.format(f, dtf, dtt)
131 for (cnt, un) in self.cur.execute(que):
133 drunk[un] = [0 for j in range(i)]
134 drunk[un].append(cnt)