When replaying offline queue, use the original event time rather than now()

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

diff --git a/coffee_db.py b/coffee_db.py
index 97082ea80aab39068756bb1b45cd3e04f00cb2df..0abb5a4fe68a0010ea5cb7d12ad97cc3062bf434 100644 (file)
--- a/coffee_db.py
+++ b/coffee_db.py
@@ -1,7 +1,9 @@
 import sqlite3
+import os
 
-dbdef = "coffee_db.sql"
-dbfile = "coffee.db"
+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)
@@ -46,7 +48,6 @@ def list_users():
 
 def add_coffee(uid, flavor, time=None):
     conn, c = open_db()
-    print(uid, flavor, time)
     if time is None:
         c.execute("insert into coffees (id, flavor) values (?,?)", (uid,flavor))
     else:
@@ -88,24 +89,25 @@ def coffee_flavors(uid=None):
     close_db(conn)
     return res
 
+
 def coffee_history(uid=None):
     conn, c = open_db()
 
     if uid is None:
         res = list(c.execute("""
-            select strftime('%d', ds.d),count(c.time) from
+            select strftime('%d', ds.d),count(c.flavor),c.flavor from
             (select num,date('now',-num || ' days') as d from days) ds
             left join coffees c
-            on d = date(c.time) group by d
+            on d = date(c.time) group by d, c.flavor
             """))
     else:
         res = list(c.execute(
             """
-            select strftime('%d', ds.d),count(c.time) from
+            select strftime('%d', ds.d),count(c.flavor),c.flavor from
             (select num,date('now',-num || ' days') as d from days) ds
             left join
-            (select time from coffees where id = ? ) c
-            on d = date(c.time) group by d
+            (select time,flavor from coffees where id = ?) c
+            on d = date(c.time) group by d, c.flavor
             """
             , (uid,)))
 
@@ -116,28 +118,21 @@ def coffee_count(uid=None, start=None, stop=None):
     conn, c = open_db()
 
     args = []
+    clauses = []
 
     if uid is not None:
-        uid_q = "id = ?"
+        clauses.append("id = ?")
         args.append(uid)
-    else:
-        uid_q = "true"
 
     if start is not None:
-        start_q = "date(time) >= date('now', -? || ' days')"
-        args.append(start)
-    else:
-        start_q = "true"
+        clauses.append("date(time) >= date('now', '-%d days')" % int(start))
 
     if stop is not None:
-        stop_q = "date(time) <= date('now', -? || ' days')"
-        args.append(stop)
-    else:
-        stop_q = "true"
+        clauses.append("date(time) <= date('now', '-%d days')" % int(stop))
 
     for count, in c.execute(
             "select count(*) from coffees where " +
-            " and ".join([uid_q, start_q, stop_q])
+            " and ".join(clauses)
             , args):
         res = count