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,)):
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()
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
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,)))
clauses = []
if uid is not None:
- clauses.append("id = ?")
+ clauses.append("ids.userid = ? and ids.active")
args.append(uid)
if start is not 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))