Add comments

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

diff --git a/coffee_db.py b/coffee_db.py
index b7206fe20563b910ac065b363cb41a6a9294f79b..25551f90fed08e2a54dd1ef53f62c50fabbd2f99 100644 (file)
--- a/coffee_db.py
+++ b/coffee_db.py
@@ -25,6 +25,30 @@ 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 = 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,)):
@@ -33,18 +57,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()
@@ -56,7 +101,7 @@ def add_coffee(uid, flavor, time=None):
 
 def flavors():
     conn, c = open_db()
-    res = [row for row, in c.execute("select distinct name from flavors")]
+    res = list(c.execute("select distinct name, ord from flavors"))
     close_db(conn)
     return res
 
@@ -80,17 +125,18 @@ 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
         """, variables))
 
     close_db(conn)
@@ -113,7 +159,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,)))
@@ -121,14 +167,19 @@ def coffee_history(uid=None):
     close_db(conn)
     return res
 
-def coffee_count(uid=None, start=None, stop=None):
+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("id = ?")
+        clauses.append("ids.userid = ? and ids.active")
         args.append(uid)
 
     if start is not None:
@@ -137,13 +188,8 @@ def coffee_count(uid=None, start=None, stop=None):
     if stop is not None:
         clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
 
-    for count, in c.execute(
-            "select count(*) from coffees where " +
-            " and ".join(clauses)
-            , args):
-        res = count
-
-    if not res:
-        res = "0"
-
-    return res
+    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))