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 get_top_tea_drinkers(self):
60 """Return list of pairs of name, count for tea drinkers."""
63 SELECT count(*), users.name FROM coffees
64 INNER JOIN users ON coffees.id = users.id
66 AND coffees.time BETWEEN
67 datetime('now', 'localtime', '-7 days') AND
68 datetime('now', 'localtime')
73 for (cnt, un) in self.cur.execute(q):
75 top.sort(key=lambda x: (x[1], x[0]), reverse=True)
78 def getDrunkSum(self, *args, **kwargs):
79 """Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.
82 flavor -- The flavor of beverage.
83 dtf -- Date and time *from*.
84 dtt -- Date and time *to*.
86 if not ((len(args) == 3 and len(kwargs) == 0) or
87 (len(args) == 0 and len(kwargs) == 3)):
88 raise ArgCountError("3 arguments needed: flavor, from, and to")
94 flavor = kwargs["flavor"]
97 flavors = flavor.split(";")
99 SELECT count(*), users.name FROM coffees
100 INNER JOIN users ON coffees.id = users.id
104 que += "WHERE flavor = '{}'".format(f)
106 que += "OR flavor = '{}'".format(f)
108 AND coffees.time BETWEEN
109 datetime('{}', 'localtime') AND
110 datetime('{}', 'localtime')
114 for (cnt, un) in self.cur.execute(que):
115 drunk.append((un, cnt))
118 def getDrunkList(self, *args, **kwargs):
119 """Return dict of lists of drunken ``flavor`` from ``dtf`` to ``dtt``.
122 flavor -- The flavor of beverage.
123 dtf -- Date and time *from*.
124 dtt -- Date and time *to*.
126 if not ((len(args) == 3 and len(kwargs) == 0) or
127 (len(args) == 0 and len(kwargs) == 3)):
128 raise ArgCountError("3 arguments needed: flavor, from, and to")
134 flavor = kwargs["flavor"]
137 flavors = flavor.split(";")
142 SELECT count(*), users.name FROM coffees
143 INNER JOIN users ON coffees.id = users.id
145 AND coffees.time BETWEEN
146 datetime('{}', 'localtime') AND
147 datetime('{}', 'localtime')
149 """.format(f, dtf, dtt)
150 for (cnt, un) in self.cur.execute(que):
152 drunk[un] = [0 for j in range(i)]
153 drunk[un].append(cnt)