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,)):
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()
- print(uid, flavor, time)
if time is None:
c.execute("insert into coffees (id, flavor) values (?,?)", (uid,flavor))
else:
- c.execute("insert into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
+ c.execute("insert or ignore into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
close_db(conn)
-
-def list_coffees(uid=None):
- c = conn.cursor()
- if uid is None:
- for row in c.execute("select id, time, flavor from coffees"):
- print(row)
- else:
- for row in c.execute("select time, flavor from coffees where id = ?", (uid,)):
- print(row)
-
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
-def coffee_flavors(uid=None):
+def coffee_flavors(uid=None, days=0, start=0):
+ """Returns flavor statistics for team/user during selected period/since beginning.
+
+ days -- number of days for computation
+ start -- shift size from the current time
+
+ When 'days' is not given or 0 return statistics since the beginning.
+
+ e.g. (7, 7) returns statistics for 7 days, 7 days ago.
+ """
+
conn, c = open_db()
- if uid is None:
- res = list(c.execute("""
- select f.name, count(c.flavor) from flavors f left join
- (select * from coffees) c
- on f.name=c.flavor group by f.name
- """))
- else:
- res = list(c.execute("""
- select f.name, count(c.flavor) from flavors f left join
- (select * from coffees where id = ?) c
- on f.name=c.flavor group by f.name
- """, (uid,)))
+ query = ""
+ 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')"
+
+ if uid is not None:
+ query += " and ids.userid = ? and ids.active"
+ variables.append(uid)
+ elif uid is not None:
+ 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 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)
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.flavor),c.flavor from
- (select num,date('now',-num || ' days') as d from days) ds
- left join coffees c
+ 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 time,flavor from coffees) c
on d = date(c.time) group by d, c.flavor
"""))
else:
res = list(c.execute(
"""
- select strftime('%d', ds.d),count(c.flavor),c.flavor from
- (select num,date('now',-num || ' days') as d from days) ds
+ 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 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,)))
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:
- clauses.append("date(time) >= date('now', '-%d days')" % int(start))
+ clauses.append("date(time, 'localtime') >= date('now', 'localtime', '-%d days')" % int(start))
if stop is not None:
- clauses.append("date(time) <= date('now', '-%d days')" % int(stop))
-
- for count, in c.execute(
- "select count(*) from coffees where " +
- " and ".join(clauses)
- , args):
- res = count
+ clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
- 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))
projects / coffee / coffee-flask.git / blobdiff