Rename DB field identifiers.status to identifiers.active

[coffee/coffee-flask.git] / coffee_db.py

diff --git a/coffee_db.py b/coffee_db.py
index acfd398fefa0794ce0de91a24082e65fb9ecbfa4..072fe92f19e937bd30158c1b22db5013d8d7b0fc 100644 (file)
--- a/coffee_db.py
+++ b/coffee_db.py
@@ -25,6 +25,29 @@ def add_user(uid):
     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 = ?", (iid, 0, ))
+
+        # This is True when some rows were found before; delete old relation
+        if res.fetchone():
+            res = c.execute("delete from identifiers where id = ? and active = ?", (iid, 0, ))
+
+        # 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 = ? where userid = ? and id = ?", (0, 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,)):
@@ -33,18 +56,39 @@ def get_name(uid):
     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"):
         print(row)
     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()
@@ -80,15 +124,15 @@ def coffee_flavors(uid=None, days=0, start=0):
         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
@@ -114,7 +158,7 @@ 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,)))
@@ -129,7 +173,7 @@ def drink_count(uid=None, start=None, stop=None):
     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,5 +182,6 @@ 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 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))