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,))
30 for name, in c.execute("select name from users where id = ?",(uid,)):
37 def name_user(uid, name):
39 c.execute("update users set name = ? where id = ?", (name, uid))
44 for row in c.execute("select * from users"):
49 def add_coffee(uid, flavor, time=None):
52 c.execute("insert into coffees (id, flavor) values (?,?)", (uid,flavor))
54 c.execute("insert or ignore into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
59 res = list(c.execute("select distinct name, ord from flavors"))
63 def coffee_flavors(uid=None, days=0, start=0):
64 """Returns flavor statistics for team/user during selected period/since beginning.
66 days -- number of days for computation
67 start -- shift size from the current time
69 When 'days' is not given or 0 return statistics since the beginning.
71 e.g. (7, 7) returns statistics for 7 days, 7 days ago.
79 if days is not None and days != 0:
80 query += " where date(time) between date('now', 'localtime', '-"+ str(days+start-1) +" days') and date('now', 'localtime', '-"+ str(start) +" days')"
83 query += " and id = ?"
86 query += " where id = ?"
89 res = list(c.execute("""
90 select f.name, count(c.flavor) from flavors f
91 left join (select * from coffees
93 on f.name=c.flavor group by f.name
100 def coffee_history(uid=None):
104 res = list(c.execute("""
105 select strftime('%s', ds.d),count(c.flavor),c.flavor from
106 (select num,date('now', 'localtime', -num || ' days') as d from days) ds
108 (select time,flavor from coffees) c
109 on d = date(c.time) group by d, c.flavor
112 res = list(c.execute(
114 select strftime('%s', ds.d),count(c.flavor),c.flavor from
115 (select num,date('now', 'localtime', -num || ' days') as d from days) ds
117 (select date(time, 'localtime') as time,flavor from coffees where id = ?) c
118 on d = date(c.time) group by d, c.flavor
125 def coffee_count(uid=None, start=None, stop=None):
132 clauses.append("id = ?")
135 if start is not None:
136 clauses.append("date(time, 'localtime') >= date('now', 'localtime', '-%d days')" % int(start))
139 clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
141 for count, in c.execute(
142 "select count(*) from coffees where " +
143 " and ".join(clauses)