import sqlite3
import os
+from datetime import datetime
dbdir = os.path.dirname(__file__)
dbdef = os.path.join(dbdir, "coffee_db.sql")
dbfile = os.path.join(dbdir, "coffee.db")
+
def open_db():
conn = sqlite3.connect(dbfile)
c = conn.cursor()
return conn, c
+
def close_db(conn):
conn.commit()
conn.close()
+
def init_db():
conn, c = open_db()
with open(dbdef, "r") as f:
c.executescript(f.read())
close_db(conn)
+
def add_user(uid):
conn, c = open_db()
c.execute("insert or ignore into users (id) values (?)", (uid,))
close_db(conn)
+
def add_user_identifier(uid, iid, name):
# Check if this identifier is not currently associated with different account
if not get_uid(iid):
# Try to remove old relation of identifier
# As 'delete or ignore' does not exist, workaround is used
- res = c.execute("select * from identifiers where id = ? and status = ?", (iid, 0, ))
+ res = c.execute("select * from identifiers where id = ? and active = 0", (iid, ))
# This is True when some rows were found before; delete old relation
if res.fetchone():
- res = c.execute("delete from identifiers where id = ? and status = ?", (iid, 0, ))
+ print("Deleting uid:%s id:%s" % (uid, iid))
+ res = c.execute("delete from identifiers where id = ? and active = 0", (iid, ))
# Add new relation
res = c.execute("insert into identifiers (userid, id, name) values (?, ?, ?)", (uid, iid, name, ))
close_db(conn)
+
def disable_user_identifier(uid, iid):
conn, c = open_db()
- c.execute("update identifiers set status = ? where userid = ? and id = ?", (0, uid, iid, ))
+ c.execute("update identifiers set active = 0 where userid = ? and id = ?", (uid, iid, ))
close_db(conn)
+
def get_name(uid):
conn, c = open_db()
- for name, in c.execute("select name from users where id = ?",(uid,)):
+ for name, in c.execute("select name from users where id = ?", (uid,)):
close_db(conn)
return name
close_db(conn)
return None
+
def get_uid(iid):
conn, c = open_db()
res = list(c.execute("""
- select userid from identifiers where id = ? and status
+ select userid from identifiers where id = ? and active
""", (iid,)))
close_db(conn)
return res[0][0] if len(res) > 0 else None
+
def name_user(uid, name):
conn, c = open_db()
c.execute("update users set name = ? where id = ?", (name, uid))
close_db(conn)
+
def rename_user_identifier(uid, iid, name):
conn, c = open_db()
c.execute("update identifiers set name = ? where userid = ? and id = ?", (name, uid, iid, ))
close_db(conn)
+
def list_users():
conn, c = open_db()
for row in c.execute("select * from users"):
print(row)
close_db(conn)
+
def list_user_identifiers(uid):
conn, c = open_db()
res = list(c.execute("""
- select * from identifiers where userid = ? and status
+ select * from identifiers where userid = ? and active
""", (uid,)))
close_db(conn)
return res
def add_coffee(uid, flavor, time=None):
conn, c = open_db()
if time is None:
- c.execute("insert into coffees (id, flavor) values (?,?)", (uid,flavor))
+ c.execute("insert into coffees (id, flavor) values (?,?)", (uid, flavor))
else:
c.execute("insert or ignore into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
close_db(conn)
+
+def add_event(uid, event_name, time):
+ conn, c = open_db()
+ c.execute("""insert into events (user_id, event_type, time)
+ values (?, (SELECT id FROM event_types WHERE name = ?), ?)""",
+ (uid, event_name, time))
+ close_db(conn)
+
+
def flavors():
conn, c = open_db()
res = list(c.execute("select distinct name, ord from flavors"))
close_db(conn)
return res
+
def coffee_flavors(uid=None, days=0, start=0):
- """Returns flavor statistics for team/user during selected period/since beginning.
+ """Returns flavor statistics for team/user during selected
+ period/since beginning.
days -- number of days for computation
start -- shift size from the current time
variables = list()
if days is not None and days != 0:
- query += " where date(time) between date('now', 'localtime', '-"+ str(days+start-1) +" days') and date('now', 'localtime', '-"+ str(start) +" days')"
+ query += " where date(time) between date('now', 'localtime', '-" + str(days+start-1) + " days') and date('now', 'localtime', '-" + str(start) + " days')"
if uid is not None:
- query += " and ids.userid = ? and ids.status"
+ query += " and ids.userid = ? and ids.active"
variables.append(uid)
elif uid is not None:
- query += " where ids.userid = ? and ids.status"
+ query += " where ids.userid = ? and ids.active"
variables.append(uid)
res = list(c.execute("""
close_db(conn)
return res
+
def coffee_history(uid=None):
conn, c = open_db()
select strftime('%s', ds.d),count(c.flavor),c.flavor from
(select num,date('now', 'localtime', -num || ' days') as d from days) ds
left join
- (select date(time, 'localtime') as time,flavor from coffees co left join identifiers ids on co.id = ids.id where ids.userid = ? and ids.status) c
+ (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
on d = date(c.time) group by d, c.flavor
- """
- , (uid,)))
+ """,
+ (uid,)))
close_db(conn)
return res
+
def drink_count(uid=None, start=None, stop=None):
+ """Return a list of tuples ('<drink type>', <count>).
+
+ >>> drink_count(stop=0)
+ [('coffee', 7066), ('Club-Mate', 497), ('tea', 1)]
+ """
conn, c = open_db()
args = []
clauses = []
if uid is not None:
- clauses.append("ids.userid = ? and ids.status")
+ clauses.append("ids.userid = ? and ids.active")
args.append(uid)
if start is not None:
if stop is not None:
clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
- return dict(c.execute("select case when flavor like 'Club%' then 'Club-Mate' else 'Coffee' end as drink, count(*) "
- "from coffees co left join identifiers ids on co.id = ids.id where "
- + " and ".join(clauses) + " group by drink", args))
+ return list(c.execute("select fl.type, count(*) from coffees co "
+ "left join flavors fl on co.flavor = fl.name "
+ "left join identifiers ids on co.id = ids.id where "
+ + " and ".join(clauses) + " group by fl.type "
+ "order by fl.ord asc", args))
+
+def sqlite_timestring_to_datetime(timestring):
+ if timestring[-1] == 'Z': # Convert Zulu time zone to datetime compatible format
+ timestring = timestring[0:-1] + '+00:00'
+ return datetime.fromisoformat(timestring)
+
+def last_events():
+ """Return mapping with event names as keys and SQLite time string of
+ the last event as values.
+ """
+ conn, c = open_db()
+ res = {}
+ for name, time in c.execute("""select name, MAX(time)
+ from events as e left join event_types as et on e.event_type = et.id
+ group by name"""):
+ res[name] = sqlite_timestring_to_datetime(time)
+
+ close_db(conn)
+ return res
projects / coffee / coffee-flask.git / blobdiff