4 dbdir = os.path.dirname(__file__)
5 dbdef = os.path.join(dbdir, "coffee_db.sql")
6 dbfile = os.path.join(dbdir, "coffee.db")
9 conn = sqlite3.connect(dbfile)
19 with open(dbdef, "r") as f:
20 c.executescript(f.read())
25 c.execute("insert or ignore into users (id) values (?)", (uid,))
28 def add_user_identifier(uid, iid, name):
29 # Check if this identifier is not currently associated with different account
33 # Try to remove old relation of identifier
34 # As 'delete or ignore' does not exist, workaround is used
35 res = c.execute("select * from identifiers where id = ? and active = 0", (iid, ))
37 # This is True when some rows were found before; delete old relation
39 print("Deleting uid:%s id:%s" % (uid, iid))
40 res = c.execute("delete from identifiers where id = ? and active = 0", (iid, ))
43 res = c.execute("insert into identifiers (userid, id, name) values (?, ?, ?)", (uid, iid, name, ))
47 def disable_user_identifier(uid, iid):
49 c.execute("update identifiers set active = 0 where userid = ? and id = ?", (uid, iid, ))
54 for name, in c.execute("select name from users where id = ?",(uid,)):
62 res = list(c.execute("""
63 select userid from identifiers where id = ? and active
67 return res[0][0] if len(res) > 0 else None
69 def name_user(uid, name):
71 c.execute("update users set name = ? where id = ?", (name, uid))
74 def rename_user_identifier(uid, iid, name):
76 c.execute("update identifiers set name = ? where userid = ? and id = ?", (name, uid, iid, ))
81 for row in c.execute("select * from users"):
85 def list_user_identifiers(uid):
87 res = list(c.execute("""
88 select * from identifiers where userid = ? and active
94 def add_coffee(uid, flavor, time=None):
97 c.execute("insert into coffees (id, flavor) values (?,?)", (uid,flavor))
99 c.execute("insert or ignore into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
104 res = list(c.execute("select distinct name, ord from flavors"))
108 def coffee_flavors(uid=None, days=0, start=0):
109 """Returns flavor statistics for team/user during selected period/since beginning.
111 days -- number of days for computation
112 start -- shift size from the current time
114 When 'days' is not given or 0 return statistics since the beginning.
116 e.g. (7, 7) returns statistics for 7 days, 7 days ago.
124 if days is not None and days != 0:
125 query += " where date(time) between date('now', 'localtime', '-"+ str(days+start-1) +" days') and date('now', 'localtime', '-"+ str(start) +" days')"
128 query += " and ids.userid = ? and ids.active"
129 variables.append(uid)
130 elif uid is not None:
131 query += " where ids.userid = ? and ids.active"
132 variables.append(uid)
134 res = list(c.execute("""
135 select f.name, count(c.flavor) from flavors f
136 left join (select * from coffees co left join identifiers ids on co.id=ids.id
138 on f.name=c.flavor group by f.name
145 def coffee_history(uid=None):
149 res = list(c.execute("""
150 select strftime('%s', ds.d),count(c.flavor),c.flavor from
151 (select num,date('now', 'localtime', -num || ' days') as d from days) ds
153 (select time,flavor from coffees) c
154 on d = date(c.time) group by d, c.flavor
157 res = list(c.execute(
159 select strftime('%s', ds.d),count(c.flavor),c.flavor from
160 (select num,date('now', 'localtime', -num || ' days') as d from days) ds
162 (select date(time, 'localtime') as time,flavor from coffees co left join identifiers ids on co.id = ids.id where ids.userid = ? and ids.active) c
163 on d = date(c.time) group by d, c.flavor
170 def drink_count(uid=None, start=None, stop=None):
177 clauses.append("ids.userid = ? and ids.active")
180 if start is not None:
181 clauses.append("date(time, 'localtime') >= date('now', 'localtime', '-%d days')" % int(start))
184 clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
186 return list(c.execute("select fl.type, count(*) from coffees co "
187 "left join flavors fl on co.flavor = fl.name "
188 "left join identifiers ids on co.id = ids.id where "
189 + " and ".join(clauses) + " group by fl.type "
190 "order by fl.ord asc", args))