- Coffee - list.iid.ciirc.cvut.cz

[PATCH coffee-flask] Fix data acquisition for flavor graph.
by Jaroslav Klapalek 14 Nov '18

14 Nov '18

Function 'coffee_flavors' wasn't doing what it was supposed to do. Upper bound was shifted by a day (so for a week graph it was count- ing 8 days) and also it didn't take into account 'start' variable. --- Oprava bugu, ktery spatne pocital kolacovy graf na uvodni obrazovce. coffee_db.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/coffee_db.py b/coffee_db.py index 8653f98..7e28f92 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -77,7 +77,7 @@ def coffee_flavors(uid=None, days=0, start=0): variables = list() if days is not None and days != 0: - query += " where date(time) between date('now', 'localtime', '-"+ str(days) +" days') and date('now', 'localtime', '-"+ str(start) +" days')" + 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 = ?" -- 2.7.4

1 0

[PATCH coffee-flask] Add support for multiple identifiers
by Jaroslav Klapalek 12 Oct '18

12 Oct '18

Users can register multiple identifiers for a single account. Also they can rename identifiers (each identifier has its own name) and unregister/remove them from the account. Required: Create new table in database CREATE TABLE "identifiers" ( `userid` varchar ( 24 ) NOT NULL, `id` varchar ( 24 ) NOT NULL UNIQUE, `name` varchar ( 24 ), FOREIGN KEY(`userid`) REFERENCES `users`(`id`) ) This table creates 1:N relation (users:identifiers), where 'userid' is marked as foreign key from 'users' table. There is no reason for changing 'users.id', but it could be done. Each coffee is now saved with 'identifiers.id' instead of 'users.id'. Because we were not using custom id in 'users' table, we do not have to change anything in currently recorded data/design of tables. On user creation / first login 'users.id' (or id of identifier) and id of identifier are added to 'identifiers' table. Selected changes: > app.py:login() >From now on we expect that received variable is 'iid' (id of identi- fier). At first this identifier is checked if it belongs to a user. If True: 'uid' (users.id) is found in database, Otherwise: 'iid' is registred as 'uid' for a new user. > app.py:user_XXX_identifier() Functions add/rename/remove modifies identifiers for current user. Note: 'session["uid"]' is used because client side (.js) does not know the difference between 'uid' and 'iid' (and there is no reason to change this). This solves a problem of invalid ids when trying to modify ID#1 of account ID#0 while logged by ID#2 in case we are assuming that id of identifier ID#0 is the same as 'users.id'. > app.py:coffee_add() Add consistency check to avoid situations when user without valid identifier is trying to register a coffee. > coffee_db.py Add functions to match new functions in app.py. Also modify current functions to use 'identifiers' table in SQL queries. > templates/main.js:addIdentifier() and addIdentifier_finish() Modifies UI for the user and changes 'updateRemote' variable. > templates/main.js:catchIdentifier() Used instead of 'hiddenUpdateRemote' during identifier registration. Otherwise it would be trying to login with the identifier. > templates/user.html Add table of identifiers for current user, which contains names, ids and buttons to rename/remove identifier from current account. --- Jedna se o prvni verzi podpory vice identifikatoru. Myslim si, ze serverova cast neni uplne spatne resena, klientska je na tom mozna o trochu hure. Je to docela testovano. Kazdopadne vsechny mozne pripominky/komentare na me klidne sypte :-). Obcas jsem nevedel, jak neco popsat, takze kdyby to bylo nejasne, ptejte se. app.py | 54 ++++++++++++++++++++++++++++++++++++++++------- coffee_db.py | 42 +++++++++++++++++++++++++++++++------ templates/main.js | 60 ++++++++++++++++++++++++++++++++++++++++++++++++++++- templates/user.html | 21 +++++++++++++++++++ 4 files changed, 162 insertions(+), 15 deletions(-) diff --git a/app.py b/app.py index d66eff1..8374966 100644 --- a/app.py +++ b/app.py @@ -27,19 +27,28 @@ def hello(): @app.route('/login', methods=["POST"]) -(a)app.route('/login/<uid>') -def login(uid=None): +(a)app.route('/login/<iid>') +def login(iid=None): if request.method == "POST": - uid = request.data.decode("utf-8") - if uid is not None: - db.add_user(uid) - session["uid"] = uid + iid = request.data.decode("utf-8") + if iid is not None: + uid = db.get_uid(iid) + + db.add_user_identifier(iid, iid, "Default") + session["iid"] = iid + + if uid is None: + session["uid"] = iid + db.add_user(iid) + else: + session["uid"] = uid return redirect(url_for('user')) @app.route('/logout') def logout(): session.pop('uid', None) + session.pop('iid', None) return redirect(url_for('user')) @@ -51,6 +60,8 @@ def user(): name=db.get_name(uid), flavors=db.flavors(), count=db.coffee_count(uid, 0), + identifiers=db.list_user_identifiers(uid), + iid=session["iid"], stamp=time.time() ) # TODO: Replace stamp parameter with proper cache control HTTP @@ -67,6 +78,31 @@ def user_rename(): return redirect(url_for('user')) +(a)app.route('/user/identifier/add', methods=["POST"]) +def user_add_identifier(): + if request.method == "POST": + json = request.json + db.add_user_identifier(session["uid"], json["id"], 'None') + return redirect(url_for('user')) + + +(a)app.route('/user/identifier/rename', methods=["POST"]) +def user_rename_identifier(): + if request.method == "POST": + json = request.json + db.rename_user_identifier(session["uid"], json["id"], json["name"]) + return redirect(url_for('user')) + + +(a)app.route('/user/identifier/remove', methods=["POST"]) +def user_remove_identifier(): + if request.method == "POST": + json = request.json + db.remove_user_identifier(session["uid"], json["id"]) + return logout() # force logout + #return redirect(url_for('user')) + + @app.route("/coffee/graph_flavors") def coffee_graph_flavors(): days = request.args.get('days', default = 0, type = int) @@ -160,8 +196,10 @@ def coffee_graph_history(): def coffee_add(): if request.method == "POST": json = request.json - print("User '%(uid)s' had '%(flavor)s' at %(time)s" % json) - db.add_coffee(json["uid"], json["flavor"], json["time"]) + # Check if identifier is valid for current user + if json["iid"] in [i[1] for i in db.list_user_identifiers(db.get_uid(json["iid"]))]: + print("User '%(iid)s' had '%(flavor)s' at %(time)s" % json) + db.add_coffee(json["iid"], json["flavor"], json["time"]) return redirect(url_for('user')) diff --git a/coffee_db.py b/coffee_db.py index 8653f98..6ea9dbc 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -25,6 +25,11 @@ def add_user(uid): c.execute("insert or ignore into users (id) values (?)", (uid,)) close_db(conn) +def add_user_identifier(uid, iid, name): + conn, c = open_db() + c.execute("insert or ignore into identifiers values (?, ?, ?)", (uid, iid, name, )) + 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 +38,43 @@ 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 = ? + """, (iid,))) + + 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 remove_user_identifier(uid, iid): + conn, c = open_db() + c.execute("delete from identifiers where userid = ? and id = ?", (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 = ? + """, (uid,))) + close_db(conn) + return res + def add_coffee(uid, flavor, time=None): conn, c = open_db() @@ -80,15 +110,15 @@ def coffee_flavors(uid=None, days=0, start=0): query += " where date(time) between date('now', 'localtime', '-"+ str(days) +" days') and date('now', 'localtime', '-"+ str(start) +" days')" if uid is not None: - query += " and id = ?" + query += " and ids.userid = ?" variables.append(uid) elif uid is not None: - query += " where id = ?" + query += " where ids.userid = ?" 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 """, variables)) @@ -113,7 +143,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 = ?) c on d = date(c.time) group by d, c.flavor """ , (uid,))) @@ -128,7 +158,7 @@ def coffee_count(uid=None, start=None, stop=None): clauses = [] if uid is not None: - clauses.append("id = ?") + clauses.append("ids.userid = ?") args.append(uid) if start is not None: @@ -138,7 +168,7 @@ def coffee_count(uid=None, start=None, stop=None): clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop)) for count, in c.execute( - "select count(*) from coffees where " + + "select count(*) from coffees co left join identifiers ids on co.id = ids.id where " + " and ".join(clauses) , args): res = count diff --git a/templates/main.js b/templates/main.js index 8135983..57d0ea3 100644 --- a/templates/main.js +++ b/templates/main.js @@ -160,6 +160,7 @@ function logout() { ajax("GET", "logout", "", "user"); id_user = undefined; timeToLogout = undefined; + updateRemote = hiddenUpdateRemote; } function countingTimeLogout(count_time) @@ -192,7 +193,7 @@ function addCoffee(flavor, time = new Date()) { var data = JSON.stringify({ time: time.toISOString(), flavor: flavor, - uid: id_user + iid: id_user }); if (id_user) { ajax("POST", "coffee/add", data, "user"); @@ -202,6 +203,63 @@ function addCoffee(flavor, time = new Date()) { } } +function addIdentifier() { + updateRemote = catchIdentifier; + document.getElementById("addIdentifier").disabled = true; + document.getElementById("labelIdentifier").style.visibility = "visible"; + document.getElementById("abortIdentifier").disabled = false; +} + +function addIdentifier_finish() { + updateRemote = hiddenUpdateRemote; + document.getElementById("addIdentifier").disabled = false; + document.getElementById("labelIdentifier").style.visibility = "hidden"; + document.getElementById("abortIdentifier").disabled = true; +} + +function catchIdentifier(json, time = new Date()) { + var msg = JSON.parse(json); + + var data = JSON.stringify({ + id: msg.uid, + uid: id_user + }); + + switch(msg.type) { + case "empty": + break; + case "rfid": + ajax("POST", "user/identifier/add", data, "user"); + break; + case "keys": + break; + case "ajax_failure": + ajax(msg.method, msg.route, msg.data, msg.id); + break; + } + addIdentifier_finish(); + sendLog(json); +} + +function renameIdentifier(i) { + var data = JSON.stringify({ + id: document.getElementById("identifier_" + i).innerText, + uid: id_user, + name: document.getElementById("identifier_name_" + i).value + }); + + ajax("POST", "user/identifier/rename", data, "user"); +} + +function removeIdentifier(id) { + var data = JSON.stringify({ + id: id, + uid: id_user + }); + + ajax("POST", "user/identifier/remove", data, "user"); +} + function sendLog(json) { ajax("POST", "log", json, "log"); } diff --git a/templates/user.html b/templates/user.html index 7e549b9..2d949b7 100644 --- a/templates/user.html +++ b/templates/user.html @@ -25,10 +25,31 @@ </p> <p> <form> + <label for="username">Username:</label> <input id="username" type="text" name="name"> <input type="button" value="rename" onclick="renameUser()"> </form> </p> + <form> + <table style="padding: 2px"> + <tr> + <td colspan="4" align="center"><b>Identifiers:</b></td> + </tr> + {% for id in identifiers %} + <tr> + <td><input type="text" id="identifier_name_{{ loop.index }}" value="{{ id[2] }}" /></td> + <td {% if iid == id[1] %} style="font-weight: bold" {% endif %}>#<span id="identifier_{{ loop.index }}">{{ id[1] }}</span></td> + <td><input type="button" value="rename" onclick="renameIdentifier({{ loop.index }})" /></td> + <td><input type="button" value="remove" onclick="removeIdentifier({{ id[1] }})" /></td> + </tr> + {% endfor %} + <tr> + <td><input type="button" id="addIdentifier" value="add identifier" onclick="addIdentifier()" /></td> + <td colspan="2" style="visibility: hidden" id="labelIdentifier"><b>Use your identifier.</b></td> + <td><input type="button" id="abortIdentifier" value="abort" disabled onclick="addIdentifier_finish()" /></td> + </tr> + </ul> + </form> {% else %} <p>Use your card/token to log in...</p> -- 2.7.4

1 0

[PATCH coffee-flask] Fix tag in 'user' template
by Jaroslav Klapalek 11 Oct '18

11 Oct '18

--- templates/user.html | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/templates/user.html b/templates/user.html index 95ee95e..0277d09 100644 --- a/templates/user.html +++ b/templates/user.html @@ -28,7 +28,7 @@ <input id="username" type="text" name="name"> <input type="button" value="rename" onclick="renameUser()"> </form> - </p + </p> {% else %} Use your card/token to log in... {% endif %} -- 2.7.4

1 0

[PATCH coffee-flask v2] Add graphs with total coffee consumption on login screen
by Jaroslav Klapalek 11 Oct '18

11 Oct '18

--- Opravena verze, ktera je podle komentaru. Jedine, co zbyva je nahrada predavani 'stampu'. To bych nechal na jiny commit, protoze tento zpusob se pouziva i jinde. Pro tento zpusob funkce 'coffee_flavors' jsem se rozhodl proto, aby slo pripadne pocitat i jine useky v historii (tedy ne jen od ted). Pravdepodobne by ta funkce sla i vyresit jinak, ale ted me nenapada jak. app.py | 23 ++++++++++++++++++----- coffee_db.py | 47 ++++++++++++++++++++++++++++++++--------------- templates/user.html | 5 ++++- 3 files changed, 54 insertions(+), 21 deletions(-) diff --git a/app.py b/app.py index 807f614..c5fdc3c 100644 --- a/app.py +++ b/app.py @@ -53,7 +53,7 @@ def user(): count=db.coffee_count(uid, 0), stamp=time.time() ) - return render_template('user.html') + return render_template('user.html', stamp=time.time()) @app.route('/user/rename') @@ -67,18 +67,26 @@ def user_rename(): @app.route("/coffee/graph_flavors") def coffee_graph_flavors(): + days = request.args.get('days', default = 0, type = int) + start = request.args.get('start', default = 0, type = int) + b = BytesIO() if "uid" in session: uid = session["uid"] - flavors, counts = zip(*db.coffee_flavors(uid)) + flavors, counts = zip(*db.coffee_flavors(uid, days, start)) else: - flavors, counts = zip(*db.coffee_flavors()) + flavors, counts = zip(*db.coffee_flavors(None, days, start)) fig = plt.figure(figsize=(3, 3)) ax = fig.add_subplot(111) ax.set_aspect(1) ax.pie(counts, autopct=lambda p: '{:.0f}'.format(p * sum(counts)/100) if p != 0 else '') ax.legend(flavors, bbox_to_anchor=(1.0, 1.0)) - ax.set_title("Your taste") + + if "uid" in session: + ax.set_title("Your taste") + else: + ax.set_title("This week taste") + fig.savefig(b, format="svg", bbox_inches="tight") b.seek(0) return send_file(b, mimetype="image/svg+xml") @@ -133,7 +141,12 @@ def coffee_graph_history(): xdays[-2] = "YDA" ax.set_xticks(range(len(unix_days))) ax.set_xticklabels(xdays) - ax.set_title("Your week") + + if "uid" in session: + ax.set_title("Your week") + else: + ax.set_title("This week total") + ax.yaxis.set_major_locator(MaxNLocator(integer=True)) fig.savefig(b, format="svg", bbox_inches="tight") b.seek(0) diff --git a/coffee_db.py b/coffee_db.py index b708d03..8653f98 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -60,26 +60,42 @@ def 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) +" days') and date('now', 'localtime', '-"+ str(start) +" days')" + + if uid is not None: + query += " and id = ?" + variables.append(uid) + elif uid is not None: + query += " where id = ?" + variables.append(uid) + + res = list(c.execute(""" + select f.name, count(c.flavor) from flavors f + left join (select * from coffees + """+query+""") c + on f.name=c.flavor group by f.name + """, variables)) close_db(conn) return res - def coffee_history(uid=None): conn, c = open_db() @@ -87,7 +103,8 @@ def coffee_history(uid=None): res = list(c.execute(""" 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 coffees c + left join + (select time,flavor from coffees) c on d = date(c.time) group by d, c.flavor """)) else: diff --git a/templates/user.html b/templates/user.html index 95ee95e..255d997 100644 --- a/templates/user.html +++ b/templates/user.html @@ -30,5 +30,8 @@ </form> </p {% else %} - Use your card/token to log in... + <p>Use your card/token to log in...</p> + + <img src="{{ url_for('coffee_graph_history', _external=True, stamp=stamp) }}"> + <img src="{{ url_for('coffee_graph_flavors', _external=True, stamp=stamp, days=7) }}"> {% endif %} -- 2.7.4

1 0

[PATCH coffee-flask 1/2] Add graphs with total coffee consumption on login screen
by Jaroslav Klapalek 10 Oct '18

10 Oct '18

Fixed version that takes into account timezone related issue, where coffees made close to midnight are counted for wrong day. --- app.py | 37 +++++++++++++++++++++++++++++++++++-- coffee_db.py | 25 ++++++++++++++++++++++++- templates/user.html | 5 ++++- 3 files changed, 63 insertions(+), 4 deletions(-) diff --git a/app.py b/app.py index 807f614..a597ad6 100644 --- a/app.py +++ b/app.py @@ -78,7 +78,35 @@ def coffee_graph_flavors(): ax.set_aspect(1) ax.pie(counts, autopct=lambda p: '{:.0f}'.format(p * sum(counts)/100) if p != 0 else '') ax.legend(flavors, bbox_to_anchor=(1.0, 1.0)) - ax.set_title("Your taste") + + if "uid" in session: + ax.set_title("Your taste") + else: + ax.set_title("Team's taste") + + fig.savefig(b, format="svg", bbox_inches="tight") + b.seek(0) + return send_file(b, mimetype="image/svg+xml") + +(a)app.route("/coffee/graph_flavors_history") +def coffee_graph_flavors_history(): + b = BytesIO() + if "uid" in session: + uid = session["uid"] + flavors, counts = zip(*db.coffee_flavors_history(uid)) + else: + flavors, counts = zip(*db.coffee_flavors_history()) + fig = plt.figure(figsize=(3, 3)) + ax = fig.add_subplot(111) + ax.set_aspect(1) + ax.pie(counts, autopct=lambda p: '{:.0f}'.format(p * sum(counts)/100) if p != 0 else '') + ax.legend(flavors, bbox_to_anchor=(1.0, 1.0)) + + if "uid" in session: + ax.set_title("Your taste") + else: + ax.set_title("This week taste") + fig.savefig(b, format="svg", bbox_inches="tight") b.seek(0) return send_file(b, mimetype="image/svg+xml") @@ -133,7 +161,12 @@ def coffee_graph_history(): xdays[-2] = "YDA" ax.set_xticks(range(len(unix_days))) ax.set_xticklabels(xdays) - ax.set_title("Your week") + + if "uid" in session: + ax.set_title("Your week") + else: + ax.set_title("This week total") + ax.yaxis.set_major_locator(MaxNLocator(integer=True)) fig.savefig(b, format="svg", bbox_inches="tight") b.seek(0) diff --git a/coffee_db.py b/coffee_db.py index b708d03..ee84c90 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -79,6 +79,28 @@ def coffee_flavors(uid=None): close_db(conn) return res +def coffee_flavors_history(uid=None): + conn, c = open_db() + + if uid is None: + res = list(c.execute(""" + select f.name, count(c.flavor) from + (select num,date('now', 'localtime', -num || ' days') as d from days) ds + left join flavors f + left join (select * from coffees) c + on f.name=c.flavor and d = date(c.time) group by f.name + """)) + else: + res = list(c.execute(""" + select f.name, count(c.flavor) from + (select num,date('now', 'localtime', -num || ' days') as d from days) ds + left join flavors f + left join (select * from coffees where id = ?) c + on f.name=c.flavor and d = date(c.time) group by f.name + """, (uid,))) + + close_db(conn) + return res def coffee_history(uid=None): conn, c = open_db() @@ -87,7 +109,8 @@ def coffee_history(uid=None): res = list(c.execute(""" 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 coffees c + left join + (select time,flavor from coffees) c on d = date(c.time) group by d, c.flavor """)) else: diff --git a/templates/user.html b/templates/user.html index 95ee95e..51a41f6 100644 --- a/templates/user.html +++ b/templates/user.html @@ -30,5 +30,8 @@ </form> </p {% else %} - Use your card/token to log in... + <img src="{{ url_for('coffee_graph_history', _external=True, stamp=stamp) }}"> + <img src="{{ url_for('coffee_graph_flavors_history', _external=True, stamp=stamp) }}"> + + <p>Use your card/token to log in...</p> {% endif %} -- 2.7.4

3 4

[PATCH coffee-flask] Add example for using hiscores functions
by Jaroslav Klapalek 26 Sep '18

26 Sep '18

--- app.py | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) diff --git a/app.py b/app.py index 807f614..d970dcb 100644 --- a/app.py +++ b/app.py @@ -157,6 +157,39 @@ def coffee_count(): return str(db.coffee_count(session.get("uid"), start, stop)) +(a)app.route("/coffee/hiscore") +(a)app.route("/coffee/hiscore/<relweek>") +def coffee_hiscore(relweek=0): + try: + int(relweek) + except: + return "" + + relweek = int(relweek) + + hiscore = db.coffee_weekly_hiscores(week=relweek, limit=5) + + ret = "<style> table.hiscore { counter-reset: count; width: 4in; white-space: nowrap; table-layout: fixed } table.hiscore td { width: 100%; white-space: nowrap; overflow: hidden; text-overflow: ellipsis; } .ol:before { counter-increment: count; content: counter(count) '.' }</style>" + + if hiscore: + ret += "<table class=\"hiscore\"><tr><td colspan=\"2\"><b>" + if relweek == 0: + ret += "This week hiscore:" + elif relweek == 1: + ret += "Last week hiscore:" + else: + ret += "Weekly hiscore:" + ret += "</b></td></tr>" + for (u, c) in hiscore: + ret += "<tr><td><span class=\"ol\"></span> %s</td><td>%d</td></tr>" % (u, c) + ret += "</table>" + else: + ret += "<b>Hiscore for this week is empty!</b>" + + #return render_template("hiscore.html", hiscore_data=ret) + return ret + + @app.route('/js') def js(): response = make_response(render_template('main.js')) -- 2.7.4

1 0

[PATCH coffee-flask] Add functions for retrieving hiscores from the database
by Jaroslav Klapalek 26 Sep '18

26 Sep '18

--- coffee_db.py | 34 ++++++++++++++++++++++++++++++++++ 1 file changed, 34 insertions(+) diff --git a/coffee_db.py b/coffee_db.py index b708d03..7957920 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -1,5 +1,6 @@ import sqlite3 import os +import datetime dbdir = os.path.dirname(__file__) dbdef = os.path.join(dbdir, "coffee_db.sql") @@ -130,3 +131,36 @@ def coffee_count(uid=None, start=None, stop=None): res = "0" return res + +def coffee_hiscores(date_start, date_end, limit=-1): + """Shows total coffee consumption between dates per user, ordered desc. + + Note: That means, the top 'consumer' will be first. + Note: Setting limit to different number will show only top 'limit' users. + """ + conn, c = open_db() + + res = list(c.execute( + """ + select users.name, count(coffees.id) from coffees + left join users on coffees.id = users.id + where date(time, 'localtime') between date(?) and date(?) + group by coffees.id order by count(coffees.id) desc limit ? + """ + , (date_start, date_end, limit,))) + + close_db(conn) + return res + +def coffee_weekly_hiscores(week=0, limit=-1): + """Shows total coffee consumption in selected week per user. + + Note: Week is relative, so 0 is this week (starting from Monday). + Note: Using local time. + """ + today = datetime.datetime.today().date() + + monday = today - datetime.timedelta(days=today.weekday()) + datetime.timedelta(weeks=week) + sunday = monday + datetime.timedelta(days=6) + + return coffee_hiscores(monday, sunday, limit) -- 2.7.4

3 3

[PATCH coffee-flask] Add graphs with total coffee consumption on login screen
by Jaroslav Klapalek 14 Sep '18

14 Sep '18

--- app.py | 37 +++++++++++++++++++++++++++++++++++-- coffee_db.py | 25 ++++++++++++++++++++++++- templates/user.html | 5 ++++- 3 files changed, 63 insertions(+), 4 deletions(-) diff --git a/app.py b/app.py index 807f614..a597ad6 100644 --- a/app.py +++ b/app.py @@ -78,7 +78,35 @@ def coffee_graph_flavors(): ax.set_aspect(1) ax.pie(counts, autopct=lambda p: '{:.0f}'.format(p * sum(counts)/100) if p != 0 else '') ax.legend(flavors, bbox_to_anchor=(1.0, 1.0)) - ax.set_title("Your taste") + + if "uid" in session: + ax.set_title("Your taste") + else: + ax.set_title("Team's taste") + + fig.savefig(b, format="svg", bbox_inches="tight") + b.seek(0) + return send_file(b, mimetype="image/svg+xml") + +(a)app.route("/coffee/graph_flavors_history") +def coffee_graph_flavors_history(): + b = BytesIO() + if "uid" in session: + uid = session["uid"] + flavors, counts = zip(*db.coffee_flavors_history(uid)) + else: + flavors, counts = zip(*db.coffee_flavors_history()) + fig = plt.figure(figsize=(3, 3)) + ax = fig.add_subplot(111) + ax.set_aspect(1) + ax.pie(counts, autopct=lambda p: '{:.0f}'.format(p * sum(counts)/100) if p != 0 else '') + ax.legend(flavors, bbox_to_anchor=(1.0, 1.0)) + + if "uid" in session: + ax.set_title("Your taste") + else: + ax.set_title("This week taste") + fig.savefig(b, format="svg", bbox_inches="tight") b.seek(0) return send_file(b, mimetype="image/svg+xml") @@ -133,7 +161,12 @@ def coffee_graph_history(): xdays[-2] = "YDA" ax.set_xticks(range(len(unix_days))) ax.set_xticklabels(xdays) - ax.set_title("Your week") + + if "uid" in session: + ax.set_title("Your week") + else: + ax.set_title("This week total") + ax.yaxis.set_major_locator(MaxNLocator(integer=True)) fig.savefig(b, format="svg", bbox_inches="tight") b.seek(0) diff --git a/coffee_db.py b/coffee_db.py index 8c18e63..be87c13 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -89,6 +89,28 @@ def coffee_flavors(uid=None): close_db(conn) return res +def coffee_flavors_history(uid=None): + conn, c = open_db() + + if uid is None: + res = list(c.execute(""" + select f.name, count(c.flavor) from + (select num,date('now',-num || ' days') as d from days) ds + left join flavors f + left join (select * from coffees) c + on f.name=c.flavor and d = date(c.time) group by f.name + """)) + else: + res = list(c.execute(""" + select f.name, count(c.flavor) from + (select num,date('now',-num || ' days') as d from days) ds + left join flavors f + left join (select * from coffees where id = ?) c + on f.name=c.flavor and d = date(c.time) group by f.name + """, (uid,))) + + close_db(conn) + return res def coffee_history(uid=None): conn, c = open_db() @@ -97,7 +119,8 @@ def coffee_history(uid=None): res = list(c.execute(""" select strftime('%s', ds.d),count(c.flavor),c.flavor from (select num,date('now',-num || ' days') as d from days) ds - left join coffees c + left join + (select time,flavor from coffees) c on d = date(c.time) group by d, c.flavor """)) else: diff --git a/templates/user.html b/templates/user.html index 95ee95e..51a41f6 100644 --- a/templates/user.html +++ b/templates/user.html @@ -30,5 +30,8 @@ </form> </p {% else %} - Use your card/token to log in... + <img src="{{ url_for('coffee_graph_history', _external=True, stamp=stamp) }}"> + <img src="{{ url_for('coffee_graph_flavors_history', _external=True, stamp=stamp) }}"> + + <p>Use your card/token to log in...</p> {% endif %} -- 2.7.4

1 0

[PATCH coffee-flask 1/2] Fix incorrect counting of coffees made during time close to midnight
by Jaroslav Klapalek 14 Sep '18

14 Sep '18

Datetime in database is stored in UTC timezone. Currently we are in UTC +2 timezone (GMT +1). Because of time shifts fixing the time cannot be done in a static way. Dynamic solution is using paramater 'localtime' in SQL query function date(). Therefore it is required for the target device to have properly set timezone. TODO: Suggested solution is to use 'type converters' for python/sqlite3. --- coffee_db.py | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) diff --git a/coffee_db.py b/coffee_db.py index 8c18e63..d3f659c 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -58,10 +58,10 @@ def add_coffee(uid, flavor, time=None): def list_coffees(uid=None): c = conn.cursor() if uid is None: - for row in c.execute("select id, time, flavor from coffees"): + for row in c.execute("select id, date(time, 'localtime'), flavor from coffees"): print(row) else: - for row in c.execute("select time, flavor from coffees where id = ?", (uid,)): + for row in c.execute("select date(time, 'localtime'), flavor from coffees where id = ?", (uid,)): print(row) def flavors(): @@ -96,7 +96,7 @@ def coffee_history(uid=None): if uid is None: res = list(c.execute(""" select strftime('%s', ds.d),count(c.flavor),c.flavor from - (select num,date('now',-num || ' days') as d from days) ds + (select num,date('now', 'localtime', -num || ' days') as d from days) ds left join coffees c on d = date(c.time) group by d, c.flavor """)) @@ -104,9 +104,9 @@ def coffee_history(uid=None): res = list(c.execute( """ select strftime('%s', ds.d),count(c.flavor),c.flavor from - (select num,date('now',-num || ' days') as d from days) ds + (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 where id = ?) c on d = date(c.time) group by d, c.flavor """ , (uid,))) @@ -125,10 +125,10 @@ def coffee_count(uid=None, start=None, stop=None): 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)) + clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop)) for count, in c.execute( "select count(*) from coffees where " + -- 2.7.4

1 1

Otázky k terminálu
by Michal Sojka 08 Sep '18

08 Sep '18

Milý pane Matějáku, váš terminál ke kávovaru nám výborně slouží, ale je tam ještě pár drobností, které bychom potřebovali vyřešit: - Vypínání podsvícení. Implementovali jsme zhasínání framebufferu, ale podsvícení displeje je pořád zapnuté. Nevíte z hlavy, jestli/jak se dá podsvícení ovládat? - Citlivost touchscreenu. Občas se stává, že displej detekuje doteky i když se ho nikdo nedotýká. Projevuje se to například tak, že se html stránka pořád scroluje, nebo se pořád objevuje a mizí kontextové menu. Netušíte, jestli se touchscreen dá nějak kalibrovat, aby se to nedělo? Díky -Michal Sojka

2 1

2024

2023

2022

2021

2020

2019

2018

Coffee