X-Git-Url: http://rtime.felk.cvut.cz/gitweb/coffee/coffee-flask.git/blobdiff_plain/c38692694b0b480020d4b8eacdcc690572f78c13..HEAD:/coffee_db.py

diff --git a/coffee_db.py b/coffee_db.py
index 5c41d51..810ed8e 100644
--- a/coffee_db.py
+++ b/coffee_db.py
@@ -1,30 +1,36 @@
 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):
@@ -32,59 +38,67 @@ def add_user_identifier(uid, iid, name):
 
         # 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
@@ -93,19 +107,30 @@ def list_user_identifiers(uid):
 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
@@ -121,13 +146,13 @@ 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 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("""
@@ -141,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()
 
@@ -158,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 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:
@@ -182,6 +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 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