X-Git-Url: http://rtime.felk.cvut.cz/gitweb/coffee/coffee-flask.git/blobdiff_plain/2c347298e32811685734f6beabde0031cb1a5396..HEAD:/coffee_db.py diff --git a/coffee_db.py b/coffee_db.py index 328d603..810ed8e 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -1,44 +1,93 @@ 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): + conn, c = open_db() + + # 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 active = 0", (iid, )) + + # This is True when some rows were found before; delete old relation + if res.fetchone(): + 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 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 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"): @@ -46,22 +95,42 @@ def list_users(): close_db(conn) +def list_user_identifiers(uid): + conn, c = open_db() + res = list(c.execute(""" + 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 @@ -77,18 +146,18 @@ def coffee_flavors(uid=None, days=0, start=0): 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 id = ?" + query += " and ids.userid = ? and ids.active" variables.append(uid) elif uid is not None: - query += " where id = ?" + query += " where ids.userid = ? and ids.active" variables.append(uid) res = list(c.execute(""" select f.name, count(c.flavor) from flavors f - left join (select * from coffees + left join (select * from coffees co left join identifiers ids on co.id=ids.id """+query+""") c on f.name=c.flavor group by f.name order by f.ord asc @@ -97,6 +166,7 @@ def coffee_flavors(uid=None, days=0, start=0): close_db(conn) return res + def coffee_history(uid=None): conn, c = open_db() @@ -114,22 +184,28 @@ def coffee_history(uid=None): 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 where id = ?) 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_count(stop=0) + [('coffee', 7066), ('Club-Mate', 497), ('tea', 1)] + """ conn, c = open_db() args = [] clauses = [] if uid is not None: - clauses.append("id = ?") + clauses.append("ids.userid = ? and ids.active") args.append(uid) if start is not None: @@ -138,4 +214,27 @@ def drink_count(uid=None, start=None, stop=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 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