From 3fa30aa4d465c8a629c54c3bf491959ae80d6878 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Felix=20D=C3=B6rre?= Date: Fri, 17 Nov 2017 12:07:27 +0100 Subject: [PATCH] add: cancel running motion (with comment) --- motion.py | 47 +++++++++++++++++++++++++----------- schema.sql | 7 +++++- templates/motion.html | 11 +++++++-- templates/single_motion.html | 4 +++ 4 files changed, 52 insertions(+), 17 deletions(-) diff --git a/motion.py b/motion.py index b74438d..15431c7 100644 --- a/motion.py +++ b/motion.py @@ -35,19 +35,21 @@ def init_db(): @app.route("/") def main(): start=int(request.args.get("start", "-1")); - q = "SELECT *, motion.deadline > CURRENT_TIMESTAMP AS running FROM motion LEFT JOIN (SELECT motion_id, voter_id, "\ + q = "SELECT motion.*, votes.*, poser.email AS poser, canceler.email AS canceler, (motion.deadline > CURRENT_TIMESTAMP AND canceled is NULL) AS running FROM motion LEFT JOIN (SELECT motion_id, voter_id, "\ + "COUNT(CASE WHEN result='yes' THEN 'yes' ELSE NULL END) as yes, "\ + "COUNT(CASE WHEN result='no' THEN 'no' ELSE NULL END) as no, "\ + "COUNT(CASE WHEN result='abstain' THEN 'abstain' ELSE NULL END) as abstain "\ - + "FROM vote GROUP BY motion_id, voter_id) as votes ON votes.motion_id=motion.id " + + "FROM vote GROUP BY motion_id, voter_id) as votes ON votes.motion_id=motion.id "\ + + "LEFT JOIN voter poser ON poser.id = motion.posed_by "\ + + "LEFT JOIN voter canceler ON canceler.id = motion.canceled_by " prev=None if start == -1: - p = get_db().prepare(q + "ORDER BY id DESC LIMIT 11") + p = get_db().prepare(q + "ORDER BY motion.id DESC LIMIT 11") rv = p() else: - p = get_db().prepare(q + "WHERE id <= $1 ORDER BY id DESC LIMIT 11") + p = get_db().prepare(q + "WHERE motion.id <= $1 ORDER BY motion.id DESC LIMIT 11") rv = p(start) - rs = get_db().prepare("SELECT id FROM motion WHERE id > $1 ORDER BY id ASC LIMIT 10")(start) + rs = get_db().prepare("SELECT id FROM motion WHERE motion.id > $1 ORDER BY id ASC LIMIT 10")(start) if len(rs) == 10: prev = rs[9][0] else: @@ -59,16 +61,33 @@ def put_motion(): time = int(request.form.get("days", "3")); if time not in times: return "Error, invalid length" - p = get_db().prepare("INSERT INTO motion(\"name\", \"content\", \"deadline\") VALUES($1, $2, CURRENT_TIMESTAMP + $3 * interval '1 days')") - p(request.form.get("title", ""), request.form.get("content",""), time) + p = get_db().prepare("INSERT INTO motion(\"name\", \"content\", \"deadline\", \"posed_by\") VALUES($1, $2, CURRENT_TIMESTAMP + $3 * interval '1 days', $4)") + p(request.form.get("title", ""), request.form.get("content",""), time, voter) return redirect("/") voter=1 -@app.route("/motion/") -def show_motion(id): - p = get_db().prepare("SELECT motion.*, motion.deadline > CURRENT_TIMESTAMP AS running, vote.result FROM motion LEFT JOIN vote on vote.motion_id=motion.id AND vote.voter_id=$2 WHERE id=$1") - rv = p(id,voter) +def motion_edited(motion): + return redirect("/?start=" + str(motion) + "#motion-" + str(motion)) + +@app.route("/motion//cancel", methods=['POST']) +def cancel_motion(id): + if request.form.get("reason", "none") == "none": + return "Error, form requires reason" + rv = get_db().prepare("UPDATE motion SET canceled=CURRENT_TIMESTAMP, cancelation_reason=$1, canceled_by=$2 WHERE id=$3 AND canceled is NULL")(request.form.get("reason", ""), voter, id) + print(rv) + return motion_edited(id) + +@app.route("/motion/") +def show_motion(motion): + p = get_db().prepare("SELECT motion.*, poser.email AS poser, canceler.email AS canceler, (motion.deadline > CURRENT_TIMESTAMP AND canceled is NULL) AS running, vote.result FROM motion "\ + + "LEFT JOIN vote on vote.motion_id=motion.id AND vote.voter_id=$2 "\ + + "LEFT JOIN voter poser ON poser.id = motion.posed_by "\ + + "LEFT JOIN voter canceler ON canceler.id = motion.canceled_by " + + "WHERE motion.id=$1") + rv = p(motion,voter) + if len(rv) == 0: + return "Error, motion not found" # TODO 404 return render_template('single_motion.html', motion=rv[0]) @app.route("/motion//vote", methods=['POST']) @@ -82,10 +101,10 @@ def vote(motion): p = db.prepare("SELECT * FROM vote WHERE motion_id = $1 AND voter_id = $2") rv = p(motion, voter) if len(rv) == 0: - db.prepare("INSERT INTO vote (motion_id, voter_id, result) VALUES($1,$2,$3)")(motion,voter,v) + db.prepare("INSERT INTO vote(motion_id, voter_id, result) VALUES($1,$2,$3)")(motion,voter,v) else: db.prepare("UPDATE vote SET result=$3, entered=CURRENT_TIMESTAMP WHERE motion_id=$1 AND voter_id = $2")(motion,voter,v) - return redirect("/?start=" + str(motion) + "#motion-" + str(motion)) + return motion_edited(motion) -# TODO cancel running motion (with comment) # TODO authentication/user management +# TODO load config with flask mechanism diff --git a/schema.sql b/schema.sql index 6fa7a67..b3a6eaf 100644 --- a/schema.sql +++ b/schema.sql @@ -1,5 +1,5 @@ DROP TABLE IF EXISTS voter; -CREATE TABLE voter (id serial NOT NULL, name VARCHAR(10) NOT NULL, PRIMARY KEY(id)); +CREATE TABLE voter (id serial NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY(id)); DROP TABLE IF EXISTS motion; @@ -7,9 +7,14 @@ CREATE TABLE motion (id serial NOT NULL, name VARCHAR(250) NOT NULL, content text NOT NULL, posed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + posed_by int NOT NULL, deadline timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP + interval '3 days'), + canceled timestamp NULL DEFAULT NULL, + cancelation_reason text NULL DEFAULT NULL, + canceled_by int NULL DEFAULT NULL, PRIMARY KEY(id)); + DROP TABLE IF EXISTS vote; DROP TYPE IF EXISTS "vote_type"; CREATE TYPE "vote_type" AS ENUM ('yes', 'no', 'abstain'); diff --git a/templates/motion.html b/templates/motion.html index 6609d20..f0d28bd 100644 --- a/templates/motion.html +++ b/templates/motion.html @@ -1,10 +1,14 @@
# - {{motion.name}} ({{ 'Running' if motion.running else 'Finished' }}) + {{motion.name}} ({{ 'Running' if motion.running else ('Canceled' if motion.canceled != None else 'Finished') }})
-
Posed: {{motion.posed|timestamp}}
+
Posed: {{motion.posed|timestamp}} by {{motion.poser}}
+{%- if motion.canceled != None %} +
Canceled: {{motion.canceled|timestamp}} by {{motion.canceler}}
+{%- else %}
Votes until: {{motion.deadline|timestamp}}
+{%- endif %}

{{motion.content}}

@@ -14,6 +18,9 @@ {{vote|capitalize}} {{motion[vote]}}
{%- endfor %}

+{%- endif %} +{%- if motion.canceled != None %} +

Cancelation reason: {{motion.cancelation_reason}}

{%- endif %}
{%- block content %}{% endblock %} diff --git a/templates/single_motion.html b/templates/single_motion.html index 1078343..cf5c94e 100644 --- a/templates/single_motion.html +++ b/templates/single_motion.html @@ -10,5 +10,9 @@ Motion: {{motion.name}} {%- endfor %} +
+ + +
{%- endif %} {%- endblock %} -- 2.39.2