[PATCH event-logging 1/2] Add event tables to the database
--- 1/2 Úprava databáze coffee_db.sql | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) diff --git a/coffee_db.sql b/coffee_db.sql index 51f83aa..5248d4c 100644 --- a/coffee_db.sql +++ b/coffee_db.sql @@ -38,6 +38,32 @@ insert or ignore into days values (0),(1),(2),(3),(4),(5),(6) ; +create table if not exists event_types ( + id integer primary key, + name varchar(255) not null, -- name of the event + status varchar(32) not null, -- `status` x days ago (required when `display`=1) + action varchar(32) not null, -- label of button to register event + display integer default 1, -- 1 for showing the latest occurence on the main page + trigger integer references event_types(id) default NULL + -- When set, registering this event will also count for `trigger` event +); + +insert or ignore into event_types values + (0, "coffee pack", "opened", "open", 1, NULL), + (1, "coffee pack", "", "open last", 0, 0), + (2, "coffee machine", "cleaned", "clean", 1, NULL), + (3, "milk container", "cleaned", "clean", 1, NULL), + (4, "milk container", "pill cleaned", "pill clean", 0, 3) -- e.g. pill cleaning implies cleaning of the container +; + +create table if not exists events ( + id integer primary key, + event_id integer references event_types(id), + user_id varchar(24) references users(id), + time datetime default current_timestamp, + UNIQUE (event_id, time, user_id) +); + CREATE TABLE if not exists identifiers ( `userid` varchar ( 24 ) NOT NULL, `id` varchar ( 24 ) PRIMARY KEY NOT NULL, -- 2.7.4
--- 2/2 Podpora zobrazení a logování app.py | 14 +++++++-- coffee_db.py | 58 +++++++++++++++++++++++++++++++++++ templates/main.js | 31 ++++++++++++++++++- templates/user.html | 87 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 187 insertions(+), 3 deletions(-) diff --git a/app.py b/app.py index 17d2a7c..71dfe0c 100644 --- a/app.py +++ b/app.py @@ -67,11 +67,14 @@ def user(): counts=counts, identifiers=db.list_user_identifiers(uid), iid=session["iid"], - stamp=time.time() + stamp=time.time(), + events=db.event_list_last(display=False) ) # TODO: Replace stamp parameter with proper cache control HTTP # headers in response - return render_template('user.html', stamp=time.time()) + return render_template('user.html', stamp=time.time(), + events=db.event_list_last() + ) @app.route('/user/rename') @@ -209,6 +212,13 @@ def coffee_add(): db.add_coffee(session["iid"], json["flavor"], json["time"]) return redirect(url_for('user')) +@app.route("/event/add", methods=["POST"]) +def event_add(): + if request.method == "POST": + json = request.json + print("User '%(uid)s' registered event ID %(event_id) at %(time)s" % json) + db.add_event(json["uid"], json["event_id"], json["time"]) + return redirect(url_for('user')) # TODO: Remove me - unused @app.route("/coffee/count") diff --git a/coffee_db.py b/coffee_db.py index 25551f9..ad424c4 100644 --- a/coffee_db.py +++ b/coffee_db.py @@ -99,6 +99,17 @@ def add_coffee(uid, flavor, time=None): c.execute("insert or ignore into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time)) close_db(conn) + +def add_event(uid, event_id, time=None): + conn, c = open_db() + if time is None: + c.execute("insert into events (user_id, event_id) values (?,?)", (uid, event_id)) + else: + c.execute("insert or ignore into events (user_id, event_id, time) values (?,?,?)", (uid, event_id, time)) + close_db(conn) + + + def flavors(): conn, c = open_db() res = list(c.execute("select distinct name, ord from flavors")) @@ -193,3 +204,50 @@ def drink_count(uid=None, start=None, stop=None): "left join identifiers ids on co.id = ids.id where " + " and ".join(clauses) + " group by fl.type " "order by fl.ord asc", args)) + +def event_list_last(display = True): + """List last occurences for all events in `event_types`. + + Arguments: + display -- when True, only events with `display=1` are returned + """ + + conn, c = open_db() + + events = list(c.execute(""" + select name, status, action, id, strftime('%s', 'now', 'localtime') - strftime('%s', time, 'localtime') + from (select * from event_types e + left join event_types e2 on e2.trigger=e.id + left join events on event_id in (e.id, e2.id) + """ + ("where e.display = 1" if display else "") + """ order by time asc) + group by id order by name asc + """)) + close_db(conn) + res = list() + last_event = "" + for event in events: + if event[0] != last_event: + res.append(list()) + res[-1].append(event[0]) + last_event = event[0] + + t = "" + if event[-1] is None: + t = "never" + else: + if event[-1] < 60: + t = "%d secs ago" % (event[-1]) + elif event[-1] < 3600: + t = "%d mins ago" % (event[-1]/60) + elif event[-1] < 86400: + t = "%d hrs ago" % (event[-1]/3600) + else: + t = "%d days ago" % (event[-1]/86400) + #res[event[0]].append((event[1], t)) + r = dict() + r["status"] = event[1] + r["action"] = event[2] + r["id"] = event[3] + r["time"] = t + res[-1].append(r) + return res diff --git a/templates/main.js b/templates/main.js index 417dc40..5650b94 100644 --- a/templates/main.js +++ b/templates/main.js @@ -29,6 +29,9 @@ function replayOfflineQueue() { } var flavorChosen; +var eventName; +var eventStatus; +var eventAction; // Central function to update UI elements. To ensure that the UI is // consistent, other code should only change state variables and then @@ -48,8 +51,14 @@ function updateUI() if (id_user !== undefined) { document.getElementById("nextStep").innerHTML = "Now select a beverage on the coffee machine…"; - } else { + } else if (flavorChosen !== undefined) { document.getElementById("nextStep").innerHTML = "Enjoy your " + flavorChosen + "!"; + // clean the coffee machine makes it cleaned + // ^ Action ^ Name ^ Status + } else if (eventStatus !== undefined && eventStatus !== "") { + document.getElementById("nextStep").innerHTML = "You have " + eventStatus + " the " + eventName + ". Thanks!"; + } else { + document.getElementById("nextStep").innerHTML = "Event '" + eventAction + "' reported for the " + eventName + ". Thanks!"; } if (timeToLogout !== undefined) @@ -171,6 +180,7 @@ function logout() { id_user = undefined; timeToLogout = undefined; identifier_registration = false; + window.scrollTo(0, 0); // Scroll up } function countingTimeLogout(count_time) @@ -212,6 +222,25 @@ function addCoffee(flavor, time = new Date()) { } } + +function addEvent(event_id, name, status, action, time = new Date()) { + var data = JSON.stringify({ + time: time.toISOString(), + event_id: event_id, + uid: id_user + }); + if (id_user) { + ajax("POST", "event/add", data, "user"); + id_user = undefined; + eventName = name; + eventStatus = status; + eventAction = action; + countingTimeLogout(10); //mean 10 seconds + window.scrollTo(0, 0); // Scroll up + } +} + + function addIdentifier_start() { identifier_registration = true; document.getElementById("addIdentifier").disabled = true; diff --git a/templates/user.html b/templates/user.html index 5ad7b90..9dd4bc4 100644 --- a/templates/user.html +++ b/templates/user.html @@ -1,3 +1,27 @@ +<style> +table.events { + margin: 0.8em; + margin-bottom: 1.5em; + padding: 2px; + border-spacing: 2em 0; + text-align: center; +} + +td.events-list { + border: 1px solid black; + padding: 0.5em; +} + +td.events-name { + border-bottom: 1px dashed black; +} + +td.events-options { + border-top: 0; + line-height: 125%; +} +</style> + {% if name %} <form style="position: absolute; right: 15%; width: 15%; height: 15%;"> <button type="button" id="logout_button" onclick="logout()" style="width: 100%; height: 100%;">logout</button> @@ -71,9 +95,72 @@ </tr> </table> </form> + {% if events %} + <br /> + <form> + <table class="events"> + <tr> + <td colspan="{{ events|length }}" align="center"><b>Events:</b></td> + </tr> + <tr> + {% for event in events %} + <td class="events-list events-name"> + <b>{{ event[0] }}</b> + </td> + {% endfor %} + </tr> + <tr> + {% for event in events %} + <td class="events-list events-options"> + {% for details in event %} + {% if not loop.first %} + <input type="button" value="{{ details['action'] }}" onclick="addEvent('{{ details['id'] }}', '{{ event[0] }}', '{{ details['status'] }}', '{{ details['action'] }}')" /> ( {{ details['time'] }} ) + {% if not loop.last %} + <br /> + {% endif %} + {% endif %} + {% endfor %} + </td> + {% endfor %} + </tr> + </table> + </form> + {% endif %} {% else %} <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) }}"> + + {% if events %} + <form> + <table class="events"> + <tr> + {% for event in events %} + <td class="events-list events-name"> + <b>{{ event[0] }}</b> + </td> + {% endfor %} + </tr> + <tr> + {% for event in events %} + <td class="events-list events-options"> + {% for details in event %} + {% if not loop.first %} + {% if details['time'] == "never" %} + {{ details['time'] }} {{ details['status'] }} + {% else %} + {{ details['status'] }} {{ details['time'] }} + {% endif %} + {% if not loop.last %} + <br /> + {% endif %} + {% endif %} + {% endfor %} + </td> + {% endfor %} + </tr> + </table> + </form> + {% endif %} {% endif %} -- 2.7.4
On Wed, Jul 29 2020, Jaroslav Klapalek wrote:
Podle mě to není jméno události, ale objekt, se kterým se pracovalo. Nebyl by "object" lepší název?
+ status varchar(32) not null, -- `status` x days ago (required when `display`=1)
Status se mi taky nelíbí. Co třeba verb_past? Do komentáře bych napsal: Used when display=1 in sentence "`verb_pase` X days ago".
+ action varchar(32) not null, -- label of button to register event
Proč to nenazvat rovnou button_label?
Když tak o tom přemýšlím, tak tabulka event_types z velké části slouží k tomu, aby definovala user interface. A pokud vím, tak většinou se lidi snaží "user interface" a "business logiku :-)" od sebe co nevíc oddělit. Nevyšel by pak ten kód jednodušší? Místo netriviálních SQL dotazů by tam byl jednodušeji pochopitelný kód v pythonu? Tabulka event_types by obsahovala jen id a name a s databázi bys interagoval jen pomocí event_add(name) a event_list_last() a překlad do lidského jazyka bys dělal buď pythonem nebo v html templatu. Co o tom soudíš? Na zbytek se případně podívám zítra. -M.
participants (2)
-
Jaroslav Klapalek -
Michal Sojka