owl.py 55.6 KB
Newer Older
1
from flask import Flask, render_template, request, make_response, g, request_tearing_down, redirect, url_for, session, send_file
2
from flask.helpers import flash
Martin Mareš's avatar
Martin Mareš committed
3
4
5
import psycopg2
import psycopg2.extras
import sys
Martin Mareš's avatar
Posts    
Martin Mareš committed
6
import os
Martin Mareš's avatar
Martin Mareš committed
7
8
from cas import CASClient
import logging
Martin Mareš's avatar
Martin Mareš committed
9
from flask_wtf import FlaskForm
Martin Mareš's avatar
Posts    
Martin Mareš committed
10
import flask_wtf.file
Martin Mareš's avatar
Martin Mareš committed
11
import wtforms
Martin Mareš's avatar
Posts    
Martin Mareš committed
12
import wtforms.validators as validators
Martin Mareš's avatar
Martin Mareš committed
13
import wtforms.fields.html5 as wtforms_html5
Martin Mareš's avatar
Posts    
Martin Mareš committed
14
import secrets
Martin Mareš's avatar
Martin Mareš committed
15
import re
Martin Mareš's avatar
Martin Mareš committed
16
17
18
from dataclasses import dataclass
from decimal import Decimal
import datetime
Martin Mareš's avatar
Martin Mareš committed
19
import dateutil.tz
Martin Mareš's avatar
Martin Mareš committed
20
from http import HTTPStatus
21
22
23
24
import click
import email.message
import email.headerregistry
import textwrap
Martin Mareš's avatar
Martin Mareš committed
25
import subprocess
26
from json.encoder import JSONEncoder
Martin Mareš's avatar
Martin Mareš committed
27
from collections import defaultdict
Martin Mareš's avatar
Martin Mareš committed
28
29
30
import urllib.parse
from html import escape
from markupsafe import Markup
31
32
import csv
import io
Martin Mareš's avatar
Martin Mareš committed
33
34
35
36
37

### Flask app object ###

app = Flask(__name__)
app.config.from_pyfile('config.py')
38
app.jinja_options['extensions'].append('jinja2.ext.do')
Martin Mareš's avatar
Martin Mareš committed
39
40
41
42
app.jinja_env.lstrip_blocks = True
app.jinja_env.trim_blocks = True
app.logger.setLevel(logging.DEBUG)

43
44
45
46
def set_g_now():
    if not hasattr(g, 'now'):
        g.now = datetime.datetime.now(tz=dateutil.tz.UTC)

Martin Mareš's avatar
Martin Mareš committed
47
def filter_timeformat(dt):
48
    return dt.astimezone().strftime("%Y-%m-%d %H:%M")
Martin Mareš's avatar
Martin Mareš committed
49

Martin Mareš's avatar
Martin Mareš committed
50
51
52
53
def filter_reltimeformat(dt):
    if dt is None:
        return ""

54
    dt = dt.astimezone()
Martin Mareš's avatar
Martin Mareš committed
55
    absolute = dt.strftime("%Y-%m-%d %H:%M")
56
    set_g_now()
Martin Mareš's avatar
Martin Mareš committed
57
58
59
60
61
62
63
64
65
66

    def unit(x, u):
        if x > 1:
            return f"{x} {u}s"
        else:
            return f"{x} {u}"

    rel = abs(dt - g.now)
    sec = int(rel.total_seconds())
    if sec < 5:
Martin Mareš's avatar
Martin Mareš committed
67
        return absolute + " (about now)"
Martin Mareš's avatar
Martin Mareš committed
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

    if sec >= 2*86400:
        out = unit(sec // 86400, 'day')
    elif sec >= 2*3600:
        out = unit(sec // 3600, 'hour')
    elif sec >= 2*60:
        out = unit(sec // 60, 'minute')
    else:
        out = unit(sec, 'second')

    if dt > g.now:
        out = "in " + out
    else:
        out = out + " ago"

Martin Mareš's avatar
Martin Mareš committed
83
    return f"{absolute} ({out})"
Martin Mareš's avatar
Martin Mareš committed
84

Martin Mareš's avatar
Martin Mareš committed
85
86
87
88
def filter_mailto(email, text=None):
    safe_email = urllib.parse.quote(email, safe='@')
    return Markup(f'<a href="mailto:{escape(safe_email)}">{escape(text if text is not None else email)}</a>')

Martin Mareš's avatar
Martin Mareš committed
89
app.jinja_env.filters['timeformat'] = filter_timeformat
Martin Mareš's avatar
Martin Mareš committed
90
app.jinja_env.filters['reltimeformat'] = filter_reltimeformat
Martin Mareš's avatar
Martin Mareš committed
91
app.jinja_env.filters['mailto'] = filter_mailto
Martin Mareš's avatar
Martin Mareš committed
92

Martin Mareš's avatar
Martin Mareš committed
93
94
95
96
97
98
99
100
### Database connection ###

db_connection = None
db = None

def db_connect():
    global db_connection, db
    db_connection = psycopg2.connect(
Martin Mareš's avatar
Martin Mareš committed
101
                host = app.config['DB_HOST'],
Martin Mareš's avatar
Martin Mareš committed
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
                user = app.config['DB_USER'],
                password = app.config['DB_PASSWD'],
                dbname = app.config['DB_NAME'],
            )
    db = db_connection.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)

def db_query(query, args=()):
    if db is None:
        db_connect()
    try:
        db.execute(query, args)
    except psycopg2.DatabaseError:
        # Reconnect if the connection died (timeout, server restart etc.)
        db_connect()
        db.execute(query, args)

def db_reset_signal(sender, **extra):
    # At the end of every request, we have to close the implicitly opened
    # transaction. Otherwise we end up with serving stale data.
    if db_connection is not None:
        try:
            db_connection.rollback()
        except:
            pass

request_tearing_down.connect(db_reset_signal, app)

### CAS ###

cas_client = CASClient(
    version = 'CAS_2_SAML_1_0',
    server_url = 'https://cas.cuni.cz/cas/'
)


def have_session_p():
Martin Mareš's avatar
Martin Mareš committed
138
139
140
141
142
    if 'uid' not in session:
        return False

    g.uid = session['uid']
    g.name = session['name']
Martin Mareš's avatar
Martin Mareš committed
143
    g.is_admin = ('admin' in session)
144
    g.inline_att = session.get('inline', False)
Martin Mareš's avatar
Martin Mareš committed
145
    return True
Martin Mareš's avatar
Martin Mareš committed
146
147


Martin Mareš's avatar
Martin Mareš committed
148
149
150
def login_redirect(next=None, current=False):
    if current:
        next = request.script_root + request.path
151
    return redirect(url_for('login', next=next or url_for('index')))
Martin Mareš's avatar
Martin Mareš committed
152
153
154
155
156
157
158
159
160
161


### Main ###


@app.route('/')
def index():
    if not have_session_p():
        return login_redirect()

162
163
164
165
166
167
168
    db_query("""
        SELECT *
        FROM owl_semesters
        ORDER BY rank DESC
        """)
    semesters = db.fetchall()

Martin Mareš's avatar
Martin Mareš committed
169
170
171
172
173
    db_query("""
            SELECT *
            FROM owl_courses
            JOIN owl_enroll USING(cid)
            WHERE uid=%s
174
            ORDER BY name
Martin Mareš's avatar
Martin Mareš committed
175
        """, (g.uid,))
176
177
178
179
180
181
182
183
    course_list = db.fetchall()

    courses = {s.semid: [] for s in semesters}
    sem_teacher = {s.semid: False for s in semesters}
    for c in course_list:
        courses[c.semid].append(c)
        if c.is_teacher:
            sem_teacher[c.semid] = True
Martin Mareš's avatar
Martin Mareš committed
184

185
    return render_template('main.html',
186
        semesters=semesters,
187
        courses=courses,
188
189
        sem_teacher=sem_teacher,
    )
Martin Mareš's avatar
Martin Mareš committed
190
191


192
193
194
195
196
@app.route('/doc/manual')
def manual():
    return render_template('manual.html')


Martin Mareš's avatar
Martin Mareš committed
197
198
199
200
201
202
class EnrollTokenForm(FlaskForm):
    token = wtforms.StringField("Token", validators=[validators.DataRequired()])


@app.route('/join/', methods=('GET', 'POST'))
def enroll():
Martin Mareš's avatar
Martin Mareš committed
203
    if not have_session_p():
Martin Mareš's avatar
Martin Mareš committed
204
        return login_redirect(current=True)
Martin Mareš's avatar
Martin Mareš committed
205

Martin Mareš's avatar
Martin Mareš committed
206
    form = EnrollTokenForm()
Martin Mareš's avatar
Martin Mareš committed
207
208
209

    if not form.validate_on_submit():
        return render_template('join.html', form=form, error=None)
Martin Mareš's avatar
Martin Mareš committed
210

Martin Mareš's avatar
Martin Mareš committed
211
    token = form.token.data
212
    db_query("SELECT c.*, s.ident AS sident FROM owl_courses c JOIN owl_semesters s USING(semid) WHERE enroll_token=%s", (token,))
Martin Mareš's avatar
Martin Mareš committed
213
214
    course = db.fetchone()
    if not course:
Martin Mareš's avatar
Martin Mareš committed
215
216
        app.logger.info('Invalid enroll token for uid=%d', g.uid)
        return render_template('join.html', form=form, error='Unrecognized token')
Martin Mareš's avatar
Martin Mareš committed
217

Martin Mareš's avatar
Martin Mareš committed
218
    app.logger.info('Enrolling uid=%d to cid=%d', g.uid, course.cid)
Martin Mareš's avatar
Martin Mareš committed
219
220
    db_query("""
            INSERT INTO owl_enroll(uid, cid)
Martin Mareš's avatar
Martin Mareš committed
221
            VALUES (%s, %s)
Martin Mareš's avatar
Martin Mareš committed
222
223
            ON CONFLICT DO NOTHING
        """,
Martin Mareš's avatar
Martin Mareš committed
224
        (g.uid, course.cid))
Martin Mareš's avatar
Martin Mareš committed
225
226
    db_connection.commit()

227
    return redirect(url_for('course_index', sident=course.sident, cident=course.ident))
Martin Mareš's avatar
Martin Mareš committed
228
229


230
def course_init(sident, cident):
Martin Mareš's avatar
Martin Mareš committed
231
    if not have_session_p():
Martin Mareš's avatar
Martin Mareš committed
232
        return login_redirect(current=True)
Martin Mareš's avatar
Martin Mareš committed
233

234
    db_query("""
235
            SELECT c.*, s.ident AS sident
236
237
            FROM owl_courses c
            JOIN owl_semesters s USING(semid)
238
            WHERE s.ident=%s AND c.ident=%s
239
        """,
240
        (sident, cident))
Martin Mareš's avatar
Martin Mareš committed
241
242
    g.course = db.fetchone()
    if not g.course:
Martin Mareš's avatar
Martin Mareš committed
243
        return "No such course", HTTPStatus.NOT_FOUND
Martin Mareš's avatar
Martin Mareš committed
244

245
246
247
248
    if g.is_admin:
        g.is_teacher = True
        return None

Martin Mareš's avatar
Martin Mareš committed
249
    db_query("SELECT * FROM owl_enroll WHERE uid=%s AND cid=%s", (g.uid, g.course.cid))
250
251
    enroll = db.fetchone()
    if not enroll:
Martin Mareš's avatar
Martin Mareš committed
252
        return "Not enrolled in this course", HTTPStatus.FORBIDDEN
Martin Mareš's avatar
Martin Mareš committed
253

254
    g.is_teacher = enroll.is_teacher
Martin Mareš's avatar
Martin Mareš committed
255
256
257
    return None


258
259
def topic_init(sident, cident, tident):
    err = course_init(sident, cident)
Martin Mareš's avatar
Posts    
Martin Mareš committed
260
261
262
263
264
265
    if err:
        return err

    db_query("SELECT * FROM owl_topics WHERE cid=%s AND ident=%s", (g.course.cid, tident))
    g.topic = db.fetchone()
    if not g.topic:
Martin Mareš's avatar
Martin Mareš committed
266
        return "No such topic", HTTPStatus.NOT_FOUND
Martin Mareš's avatar
Posts    
Martin Mareš committed
267

Martin Mareš's avatar
Martin Mareš committed
268
    if g.topic.type not in "DTA":
Martin Mareš's avatar
Martin Mareš committed
269
        return "Bad topic type", HTTPStatus.NOT_FOUND
Martin Mareš's avatar
Posts    
Martin Mareš committed
270

Martin Mareš's avatar
Martin Mareš committed
271
    if not g.topic.public and not g.is_teacher and not g.is_admin:
Martin Mareš's avatar
Martin Mareš committed
272
        return "This is a private topic", HTTPStatus.FORBIDDEN
Martin Mareš's avatar
Martin Mareš committed
273

274
275
276
277
278
279
    if g.course.student_grading:
        db_query("SELECT * FROM owl_student_graders WHERE tid=%s AND uid=%s", (g.topic.tid, g.uid));
        g.is_grader = g.is_teacher or bool(db.fetchone())
    else:
        g.is_grader = g.is_teacher

Martin Mareš's avatar
Posts    
Martin Mareš committed
280
281
282
    return None


Martin Mareš's avatar
Martin Mareš committed
283
284
285
286
287
288
289
def must_be_teacher():
    if not g.is_teacher and not g.is_admin:
        return "You must be a teacher of this course", HTTPStatus.FORBIDDEN

    return None


Martin Mareš's avatar
Martin Mareš committed
290
291
292
293
294
295
296
def must_be_admin():
    if not g.is_admin:
        return "You must be an administrator", HTTPStatus.FORBIDDEN

    return None


297
298
299
300
def full_name(u):
    return u.first_name + ' ' + u.last_name


301
302
303
@app.route('/c/<sident>/<cident>/')
def course_index(sident, cident):
    err = course_init(sident, cident)
Martin Mareš's avatar
Martin Mareš committed
304
305
306
    if err:
        return err

307
308
309
310
311
    if g.is_teacher:
        target_uid = -1
    else:
        target_uid = g.uid

312
313
314
315
316
317
318
319
    if g.course.student_grading:
        sql_is_grader = "EXISTS (SELECT * FROM owl_student_graders x WHERE x.tid=t.tid AND x.uid=%s)"
        sql_is_grader_params = [g.uid]
    else:
        sql_is_grader = "FALSE"
        sql_is_grader_params = []

    db_query(f"""
320
321
322
323
324
            SELECT t.cid, t.tid, t.ident, t.title, t.type, t.deadline, t.max_points,
                s.seen AS last_seen,
                (SELECT MAX(x.created)
                 FROM owl_posts x
                 WHERE x.tid = t.tid
325
                   AND (x.target_uid=-1 OR x.target_uid = %s)
326
327
328
329
                ) AS last_posted,
                (SELECT x.points
                 FROM owl_posts x
                 WHERE x.tid = t.tid
330
                   AND (x.target_uid=-1 OR x.target_uid = %s)
331
332
333
                   AND x.points IS NOT NULL
                 ORDER BY x.created DESC
                 LIMIT 1
334
335
                ) AS points,
                {sql_is_grader} AS is_grader
336
337
338
            FROM owl_topics t
            LEFT JOIN owl_seen s ON s.tid = t.tid
                                 AND s.observer_uid = %s
339
                                 AND (s.target_uid=%s OR t.type='D' AND s.target_uid=-1)
340
341
            WHERE t.cid = %s
              AND t.public = true
Martin Mareš's avatar
Posts    
Martin Mareš committed
342
            ORDER by rank, created
343
        """, [g.uid, g.uid] + sql_is_grader_params + [g.uid, target_uid, g.course.cid])
Martin Mareš's avatar
Posts    
Martin Mareš committed
344
345
    topics = db.fetchall()

346
347
    course_total = Decimal('0.00')
    course_max = Decimal('0.00')
348
    is_grader = False
Martin Mareš's avatar
Martin Mareš committed
349
350
351
352
353
    for t in topics:
        if t.points is not None:
            course_total += t.points
        if t.max_points is not None:
            course_max += t.max_points
354
355
        if t.is_grader:
            is_grader = True
Martin Mareš's avatar
Martin Mareš committed
356
357
358
359
360

    return render_template(
        'course.html',
        topics=topics,
        course_total=course_total,
361
362
        course_max=course_max,
        is_grader=is_grader)
Martin Mareš's avatar
Posts    
Martin Mareš committed
363
364


Martin Mareš's avatar
Martin Mareš committed
365
366
367
### Posts ###


Martin Mareš's avatar
Posts    
Martin Mareš committed
368
def validate_attachment(form, field):
Martin Mareš's avatar
Martin Mareš committed
369
370
    f = field.data
    if not f:
Martin Mareš's avatar
Posts    
Martin Mareš committed
371
372
        return

Martin Mareš's avatar
Martin Mareš committed
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
    if f.content_length > 4*2**20:
        raise wtforms.ValidationError("Attachment too large (4MB maximum)")

    if is_pdf(f):
        g.att_type = 'pdf'
    elif is_utf8(f):
        g.att_type = 'txt'
    else:
        raise wtforms.ValidationError("Must be a PDF or plain text file")

    f.seek(0)


def is_pdf(f):
    f.seek(0)
    header = f.read(9)
Martin Mareš's avatar
Martin Mareš committed
389
    return len(header) == 9 and (header[:7] == b'%PDF-1.' or header[:7] == b'%PDF-2.')
Martin Mareš's avatar
Martin Mareš committed
390
391
392
393
394
395
396
397
398
399
400


def is_utf8(f):
    # FIXME: This is inefficient
    f.seek(0)
    x = f.read()
    try:
        x.decode(encoding='utf-8', errors='strict')
    except:
        return False
    return True
Martin Mareš's avatar
Martin Mareš committed
401

Martin Mareš's avatar
Posts    
Martin Mareš committed
402
403
404

class TopicPostForm(FlaskForm):
    comment = wtforms.TextAreaField("Comment:", validators=[validators.Length(max=65536)])
Martin Mareš's avatar
Martin Mareš committed
405
    attachment = flask_wtf.file.FileField("Attachment (PDF or UTF-8 text):", validators=[validate_attachment])
Martin Mareš's avatar
Martin Mareš committed
406
    points = wtforms.DecimalField("Points:", validators=[validators.Optional()])
Martin Mareš's avatar
Posts    
Martin Mareš committed
407
    submit = wtforms.SubmitField("Submit")
Martin Mareš's avatar
Martin Mareš committed
408
    ack_time = wtforms.HiddenField()
Martin Mareš's avatar
Posts    
Martin Mareš committed
409
410


411
412
413
414
@app.route('/c/<sident>/<cident>/<tident>/', methods=('GET', 'POST'))
@app.route('/c/<sident>/<cident>/<tident>/<int:student_uid>/', methods=('GET', 'POST'))
def topic_index(sident, cident, tident, student_uid=None):
    err = topic_init(sident, cident, tident)
Martin Mareš's avatar
Posts    
Martin Mareš committed
415
416
417
    if err:
        return err

418
    if student_uid is not None:
419
420
        if not g.is_grader:
            return "Only graders are allowed to do that", HTTPStatus.FORBIDDEN
Martin Mareš's avatar
Martin Mareš committed
421
    else:
Martin Mareš's avatar
Martin Mareš committed
422
423
        if g.topic.type == 'D':
            # In discussion threads, all posts are global
424
            student_uid = -1
Martin Mareš's avatar
Martin Mareš committed
425
        elif g.is_teacher:
Martin Mareš's avatar
Martin Mareš committed
426
            # Teachers view and post only global items at their page
427
            student_uid = -1
Martin Mareš's avatar
Martin Mareš committed
428
429
        else:
            student_uid = g.uid
430

Martin Mareš's avatar
Posts    
Martin Mareš committed
431
432
    form = TopicPostForm()

433
434
435
436
437
    if request.method == 'POST':
        if form.validate_on_submit():
            return topic_post(form, student_uid)
        else:
            flash('Post not submitted, see below for errors.', 'error')
Martin Mareš's avatar
Posts    
Martin Mareš committed
438
439

    db_query("""
440
            SELECT p.pid, p.target_uid, p.author_uid, CONCAT(u.first_name, ' ', u.last_name) AS author_name, p.created, p.modified, p.comment, p.attachment, p.points
Martin Mareš's avatar
Martin Mareš committed
441
442
            FROM owl_posts p
            LEFT JOIN owl_users u ON u.uid = p.author_uid
Martin Mareš's avatar
Posts    
Martin Mareš committed
443
            WHERE tid=%s
444
              AND (target_uid=%s OR target_uid=-1)
Martin Mareš's avatar
Posts    
Martin Mareš committed
445
            ORDER by created
446
        """, (g.topic.tid, student_uid))
Martin Mareš's avatar
Posts    
Martin Mareš committed
447
448
    posts = db.fetchall()

Martin Mareš's avatar
Martin Mareš committed
449
    seen = None
Martin Mareš's avatar
Martin Mareš committed
450
451
452
453
454
455
456
457
458
459
    db_query("""
            SELECT seen
            FROM owl_seen
            WHERE observer_uid=%s
              AND target_uid IS NOT DISTINCT FROM %s
              AND tid=%s
        """, (g.uid, student_uid, g.topic.tid))
    seen_row = db.fetchone()
    if seen_row:
        seen = seen_row.seen
Martin Mareš's avatar
Martin Mareš committed
460

Martin Mareš's avatar
Martin Mareš committed
461
462
463
    if posts and (seen == None or seen < posts[-1].created):
        form.ack_time.data = posts[-1].created

Martin Mareš's avatar
Martin Mareš committed
464
    return render_template('topic.html', posts=posts, form=form, student_uid=student_uid, seen=seen)
Martin Mareš's avatar
Posts    
Martin Mareš committed
465
466


Martin Mareš's avatar
Martin Mareš committed
467
468
469
470
471
472
473
474
475
def strip_comment(c):
    c = c.replace("\r\n", "\n")
    c = c.strip()
    if c != "":
        return c
    else:
        return None


476
def topic_post(form, student_uid):
Martin Mareš's avatar
Martin Mareš committed
477
    comment = None
Martin Mareš's avatar
Posts    
Martin Mareš committed
478
    if form.comment.data:
Martin Mareš's avatar
Martin Mareš committed
479
        comment = strip_comment(form.comment.data)
Martin Mareš's avatar
Posts    
Martin Mareš committed
480

Martin Mareš's avatar
Martin Mareš committed
481
    attach = None
Martin Mareš's avatar
Posts    
Martin Mareš committed
482
483
    if form.attachment.data:
        f = form.attachment.data
Martin Mareš's avatar
Martin Mareš committed
484
        attach = secrets.token_hex(16) + "." + g.att_type   # Type filled in by form field validator
Martin Mareš's avatar
Martin Mareš committed
485
        f.save(os.path.join(app.instance_path, "files", attach))
Martin Mareš's avatar
Martin Mareš committed
486
        app.logger.info("Uploaded file: attach=%s by_uid=%d", attach, g.uid)
Martin Mareš's avatar
Posts    
Martin Mareš committed
487

Martin Mareš's avatar
Martin Mareš committed
488
    points = None
489
    if g.is_grader and form.points.data is not None:
Martin Mareš's avatar
Martin Mareš committed
490
        points = form.points.data
Martin Mareš's avatar
Posts    
Martin Mareš committed
491

Martin Mareš's avatar
Martin Mareš committed
492
493
494
495
    if comment or attach or points is not None:
        db_query("""
                INSERT INTO owl_posts
                (tid, target_uid, author_uid, comment, attachment, points)
Martin Mareš's avatar
Martin Mareš committed
496
                VALUES (%s, %s, %s, %s, %s, %s)
497
                RETURNING *
Martin Mareš's avatar
Martin Mareš committed
498
            """,
499
            (g.topic.tid, student_uid, g.uid, comment, attach, points))
Martin Mareš's avatar
Martin Mareš committed
500

501
        new_post = db.fetchone()
Martin Mareš's avatar
Martin Mareš committed
502
        assert new_post
503
        app.logger.info(f"New post: pid={new_post.pid} tid={new_post.tid} author_uid={new_post.author_uid} target_uid={new_post.target_uid} points={points} attach={attach}")
504

505
        db_query("INSERT INTO owl_notify(pid) VALUES(%s) ON CONFLICT DO NOTHING", (new_post.pid,))
506
507
    else:
        new_post = None
508

Martin Mareš's avatar
Martin Mareš committed
509
510
511
512
513
514
515
516
517
518
519
520
521
522
    if new_post:
        ack_time = new_post.created
    elif form.ack_time.data:
        ack_time = form.ack_time.data
    else:
        ack_time = None

    if ack_time:
        db_query("""
                INSERT INTO owl_seen
                (observer_uid, target_uid, tid)
                VALUES (%s, %s, %s)
                ON CONFLICT (observer_uid, target_uid, tid) DO UPDATE SET seen = %s
            """, (g.uid, student_uid, g.topic.tid, ack_time))
Martin Mareš's avatar
Martin Mareš committed
523
524

    db_connection.commit()
Martin Mareš's avatar
Posts    
Martin Mareš committed
525

526
    if new_post:
527
        wake_up_mule()
528
        return redirect(url_for('topic_index',
529
                sident=g.course.sident,
530
531
532
533
                cident=g.course.ident,
                tident=g.topic.ident,
                student_uid=(student_uid if student_uid != g.uid and student_uid >= 0 else None)
            ) + "#p" + str(new_post.pid))
534
    elif g.is_teacher:
535
        return redirect(url_for('teacher', sident=g.course.sident, cident=g.course.ident))
536
    elif g.is_grader:
537
        return redirect(url_for('topic_student_grade', sident=g.course.sident, cident=g.course.ident, tident=g.topic.ident))
538
    else:
539
        return redirect(url_for('course_index', sident=g.course.sident, cident=g.course.ident))
540

Martin Mareš's avatar
Martin Mareš committed
541

Martin Mareš's avatar
Martin Mareš committed
542
543
544
545
class EditPostForm(FlaskForm):
    comment = wtforms.TextAreaField("Comment:", validators=[validators.Length(max=65536)])
    points = wtforms.DecimalField("Points:", validators=[validators.Optional()])
    submit = wtforms.SubmitField("Submit")
546
    silent_submit = wtforms.SubmitField("Silent submit")
Martin Mareš's avatar
Martin Mareš committed
547
548
549
550
    delete = wtforms.SubmitField("☠ Delete post ☠")


def edit_allowed_p(post):
551
552
553
554
    # We must check that the post belongs to the selected course (for which g.is_teacher is valid)
    if g.topic.cid != g.course.cid:
        return False

Martin Mareš's avatar
Martin Mareš committed
555
556
557
558
559
    return (g.is_admin or
            g.is_teacher or
            post.author_uid == g.uid and g.topic.public)


560
561
562
@app.route('/c/<sident>/<cident>/<tident>/grade')
def topic_student_grade(sident=None, cident=None, tident=None):
    err = topic_init(sident, cident, tident)
563
564
565
    if err:
        return err

Václav Končický's avatar
Václav Končický committed
566
567
568
569
    if not g.is_grader:
        return "Only graders can grade", HTTPStatus.FORBIDDEN

    db_query("""
570
            SELECT s.uid, s.first_name, s.last_name
Václav Končický's avatar
Václav Končický committed
571
572
573
574
            FROM owl_enroll e
            JOIN owl_users s ON s.uid = e.uid
            WHERE e.cid = %s
              AND e.is_teacher = false
575
            ORDER BY s.last_name, s.first_name
Václav Končický's avatar
Václav Končický committed
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
        """, (g.course.cid,))

    students = {}
    for s in db.fetchall():
        students[s.uid] = s

    solutions = {}
    for uid, s in students.items():
        solutions[uid] = TSolution(
            points=None,
            last_activity=None,
            last_seen_by_me=None,
            last_seen_by_teacher=None,
        )

    db_query("""
            SELECT p.target_uid AS target_uid, p.created AS post_created, p.points
            FROM owl_posts p
            JOIN owl_users s ON s.uid = p.target_uid
            WHERE p.tid = %s
              AND p.target_uid >= 0
            ORDER BY p.target_uid, p.created, p.pid
        """, (g.topic.tid,))

    for p in db.fetchall():
        s = solutions[p.target_uid]
        if p.points is not None:
            s.points = p.points
        s.last_activity = p.post_created

    db_query("""
            SELECT s.target_uid, s.seen
            FROM owl_seen s
            WHERE s.tid = %s
              AND s.observer_uid = %s
        """, (g.topic.tid, g.uid))

    for t in db.fetchall():
        if t.target_uid in solutions:
            s = solutions[t.target_uid]
            s.last_seen_by_me = t.seen

    return render_template('topic-grade.html', students=students, solutions=solutions)
619
620


621
622
623
@app.route('/post/<sident>/<cident>/<int:pid>/', methods=('GET', 'POST'))
def edit_post(sident, cident, pid):
    err = course_init(sident, cident)
Martin Mareš's avatar
Martin Mareš committed
624
625
626
627
628
629
630
631
632
633
634
635
    if err:
        return err

    db_query("SELECT * FROM owl_posts WHERE pid=%s FOR UPDATE", (pid,))
    post = db.fetchone()
    if not post:
        return "Not allowed to edit this post", HTTPStatus.FORBIDDEN

    db_query("SELECT * FROM owl_topics WHERE tid=%s", (post.tid,))
    g.topic = db.fetchone()
    assert g.topic

636
637
638
639
640
641
    if g.course.student_grading:
        db_query("SELECT * FROM owl_student_graders WHERE tid=%s AND uid=%s", (g.topic.tid, g.uid));
        g.is_grader = g.is_teacher or bool(db.fetchone())
    else:
        g.is_grader = g.is_teacher

Martin Mareš's avatar
Martin Mareš committed
642
643
644
645
646
647
    if not edit_allowed_p(post):
        return "Not allowed to edit this post", HTTPStatus.FORBIDDEN

    if request.method == 'POST':
        form = EditPostForm()
        if not form.validate_on_submit():
648
            flash('Post not submitted, see below for errors.', 'error')
Martin Mareš's avatar
Martin Mareš committed
649
650
            return render_template('edit-post.html', form=form)

651
        if g.is_grader and post.target_uid >= 0:
Martin Mareš's avatar
Martin Mareš committed
652
653
654
            student_uid = post.target_uid
        else:
            student_uid = None
655
        return_to = url_for('topic_index', sident=g.course.sident, cident=g.course.ident, tident=g.topic.ident, student_uid=student_uid)
Martin Mareš's avatar
Martin Mareš committed
656
657
658

        if form.delete.data:
            if g.is_teacher:
659
                app.logger.info(f"Deleted post: pid={pid} tid={post.tid} author_uid={post.author_uid} target_uid={post.target_uid} by_uid={g.uid} public={g.topic.public}")
Martin Mareš's avatar
Martin Mareš committed
660
661
                db_query("DELETE FROM owl_posts WHERE pid=%s", (pid,))
                db_connection.commit()
662
                flash('Post deleted.', 'info')
Martin Mareš's avatar
Martin Mareš committed
663
664
                return redirect(return_to)
            cmt = "*Post deleted by its author.*"
665
            att = None
666
            points = None
Martin Mareš's avatar
Martin Mareš committed
667
668
        else:
            cmt = strip_comment(form.comment.data)
669
            att = post.attachment
670
            points = form.points.data
Martin Mareš's avatar
Martin Mareš committed
671
672

        changed = False
673
674
        if cmt != post.comment or att != post.attachment:
            db_query("UPDATE owl_posts SET comment=%s, attachment=%s WHERE pid=%s", (cmt, att, pid))
Martin Mareš's avatar
Martin Mareš committed
675
            changed = True
676
677
        if g.is_grader and points != post.points:
            db_query("UPDATE owl_posts SET points=%s WHERE pid=%s", (points, pid))
Martin Mareš's avatar
Martin Mareš committed
678
679
680
            changed = True

        if changed:
681
            app.logger.info(f"Edited post: pid={pid} tid={post.tid} author_uid={post.author_uid} target_uid={post.target_uid} by_uid={g.uid} public={g.topic.public}")
Martin Mareš's avatar
Martin Mareš committed
682
683
684
685
            if g.topic.public:
                db_query("UPDATE owl_posts SET modified=CURRENT_TIMESTAMP WHERE pid=%s", (pid,))
            else:
                db_query("UPDATE owl_posts SET created=CURRENT_TIMESTAMP, modified=NULL WHERE pid=%s", (pid,))
686
687
            if not form.silent_submit.data:
                db_query("INSERT INTO owl_notify(pid) VALUES(%s) ON CONFLICT DO NOTHING", (pid,))
688
            flash('Post changed.', 'info')
Martin Mareš's avatar
Martin Mareš committed
689
690
691
692
693
694
695
696
697
698
699
700

        db_connection.commit()

        if changed:
            wake_up_mule()

        return redirect(return_to)

    form = EditPostForm(obj=post)
    return render_template('edit-post.html', form=form)


Martin Mareš's avatar
Martin Mareš committed
701
702
703
### Teacher's overview ###


Martin Mareš's avatar
Martin Mareš committed
704
705
706
707
708
@dataclass
class TSolution:
    points: Decimal
    last_activity: datetime.datetime
    last_seen_by_me: datetime.datetime
709
    last_seen_by_teacher: datetime.datetime
Martin Mareš's avatar
Martin Mareš committed
710
711


712
713
714
@app.route('/teacher/c/<sident>/<cident>/')
@app.route('/teacher/s/<sident>/')
def teacher(sident, cident=None):
715
    if not have_session_p():
Martin Mareš's avatar
Martin Mareš committed
716
        return login_redirect(current=True)
Martin Mareš's avatar
Martin Mareš committed
717

Martin Mareš's avatar
Martin Mareš committed
718
719
720
    db_query("CREATE TEMPORARY TABLE tmp_cids (cid int) ON COMMIT DROP");

    if cident is not None:
721
        err = course_init(sident, cident)
Martin Mareš's avatar
Martin Mareš committed
722
723
724
725
        if err:
            return err
        db_query("INSERT INTO tmp_cids VALUES(%s)", (g.course.cid,))
    else:
726
        db_query("SELECT * FROM owl_semesters WHERE ident=%s", (sident,))
727
728
729
730
        semester = db.fetchone()
        if not semester:
            return "No such semester", HTTPStatus.NOT_FOUND

Martin Mareš's avatar
Martin Mareš committed
731
732
        db_query("""
                INSERT INTO tmp_cids
733
734
735
736
737
738
739
                SELECT cid
                    FROM owl_enroll
                    JOIN owl_courses c USING(cid)
                    WHERE uid=%s
                      AND is_teacher=true
                      AND c.semid=%s
            """, (g.uid, semester.semid))
Martin Mareš's avatar
Martin Mareš committed
740

741
    db_query("""
742
743
744
            SELECT c.*, s.ident AS sident
            FROM owl_courses c
            JOIN owl_semesters s USING(semid)
Martin Mareš's avatar
Martin Mareš committed
745
            WHERE cid IN (SELECT cid FROM tmp_cids)
746
            ORDER BY c.ident
Martin Mareš's avatar
Martin Mareš committed
747
        """)
Martin Mareš's avatar
Martin Mareš committed
748
749

    courses = {}
Martin Mareš's avatar
Martin Mareš committed
750
    course_totals = {}
751
    topics = {}
752
753
    topics_followed_by_header = {}
    last_tid = {}
754
755
756
757
    for c in db.fetchall():
        courses[c.cid] = c
        course_totals[c.cid] = 0
        topics[c.cid] = {}
758
759
        topics_followed_by_header[c.cid] = {}
        last_tid[c.cid] = None
760
761
762
763

    db_query("""
            SELECT *
            FROM owl_topics
Martin Mareš's avatar
Martin Mareš committed
764
            WHERE cid IN (SELECT cid FROM tmp_cids)
Martin Mareš's avatar
Martin Mareš committed
765
              AND type IN ('H', 'T', 'A')
766
            ORDER BY cid, rank
Martin Mareš's avatar
Martin Mareš committed
767
        """)
768

Martin Mareš's avatar
Martin Mareš committed
769
    for t in db.fetchall():
Martin Mareš's avatar
Martin Mareš committed
770
        if t.type in "TA":
771
772
773
774
775
776
777
778
779
780
            topics[t.cid][t.tid] = t
            if t.max_points is not None:
                course_totals[t.cid] += t.max_points
            last_tid[t.cid] = t.tid
        elif t.type == 'H':
            if last_tid[t.cid] is not None:
                topics_followed_by_header[t.cid][last_tid[t.cid]] = True

    for cid, tid in last_tid.items():
        topics_followed_by_header[cid][tid] = True
Martin Mareš's avatar
Martin Mareš committed
781
782

    db_query("""
783
            SELECT c.cid, s.uid, s.first_name, s.last_name, s.email
784
785
            FROM owl_courses c
            JOIN owl_enroll e ON e.cid = c.cid
Martin Mareš's avatar
Martin Mareš committed
786
            JOIN owl_users s ON s.uid = e.uid
Martin Mareš's avatar
Martin Mareš committed
787
            WHERE c.cid IN (SELECT cid FROM tmp_cids)
Martin Mareš's avatar
Martin Mareš committed
788
              AND e.is_teacher = false
789
            ORDER BY c.ident, s.last_name, s.first_name
Martin Mareš's avatar
Martin Mareš committed
790
        """)
Martin Mareš's avatar
Martin Mareš committed
791
792
793
794
795
796
797

    students = { cid: {} for cid in courses.keys() }
    for s in db.fetchall():
        students[s.cid][s.uid] = s

    solutions = {}
    for c in courses.values():
Martin Mareš's avatar
Martin Mareš committed
798
        cid = c.cid
Martin Mareš's avatar
Martin Mareš committed
799
800
801
802
803
        solutions[cid] = {}
        for s in students[cid].values():
            uid = s.uid
            solutions[cid][uid] = {}
            for t in topics[cid].values():
Martin Mareš's avatar
Martin Mareš committed
804
805
806
807
                solutions[cid][uid][t.tid] = TSolution(
                    points=None,
                    last_activity=None,
                    last_seen_by_me=None,
808
                    last_seen_by_teacher=None,
Martin Mareš's avatar
Martin Mareš committed
809
                )
Martin Mareš's avatar
Martin Mareš committed
810
811
812
813

    db_query("""
            SELECT c.cid, t.tid, p.author_uid, p.target_uid AS target_uid, p.created AS post_created, p.points
            FROM owl_courses c
814
815
            JOIN owl_topics t ON t.cid = c.cid
            JOIN owl_posts p ON p.tid = t.tid
Martin Mareš's avatar
Martin Mareš committed
816
            JOIN owl_users s ON s.uid = p.target_uid
Martin Mareš's avatar
Martin Mareš committed
817
            WHERE c.cid IN (SELECT cid FROM tmp_cids)
818
              AND p.target_uid >= 0
Martin Mareš's avatar
Martin Mareš committed
819
            ORDER BY c.ident, t.ident, p.target_uid, p.created, p.pid
Martin Mareš's avatar
Martin Mareš committed
820
        """)
821

Martin Mareš's avatar
Martin Mareš committed
822
823
824
    for p in db.fetchall():
        s = solutions[p.cid][p.target_uid][p.tid]
        if p.points is not None:
Martin Mareš's avatar
Martin Mareš committed
825
826
            s.points = p.points
        s.last_activity = p.post_created
Martin Mareš's avatar
Martin Mareš committed
827

Martin Mareš's avatar
Martin Mareš committed
828
829
830
831
832
833
    totals = {}
    for c in solutions.keys():
        totals[c] = {}
        for u in solutions[c].keys():
            totals[c][u] = 0
            for s in solutions[c][u].values():
Martin Mareš's avatar
Martin Mareš committed
834
835
                if s.points is not None:
                    totals[c][u] += s.points
Martin Mareš's avatar
Martin Mareš committed
836

Martin Mareš's avatar
Martin Mareš committed
837
838
839
840
841
    db_query("""
            SELECT c.cid, t.tid, s.target_uid, s.seen
            FROM owl_courses c
            JOIN owl_topics t ON t.cid = c.cid
            JOIN owl_seen s ON s.tid = t.tid
Martin Mareš's avatar
Martin Mareš committed
842
            WHERE c.cid IN (SELECT cid FROM tmp_cids)
Martin Mareš's avatar
Martin Mareš committed
843
              AND s.observer_uid = %s
Martin Mareš's avatar
Martin Mareš committed
844
        """, (g.uid,))
Martin Mareš's avatar
Martin Mareš committed
845
846

    for t in db.fetchall():
Martin Mareš's avatar
Martin Mareš committed
847
848
849
850
        try:
            s = solutions[t.cid][t.target_uid][t.tid]
            s.last_seen_by_me = t.seen
        except KeyError:
851
852
853
854
855
856
857
            pass

    db_query("""
            SELECT c.cid, t.tid, s.target_uid, s.seen
            FROM owl_courses c
            JOIN owl_topics t ON t.cid = c.cid
            JOIN owl_seen s ON s.tid = t.tid
Martin Mareš's avatar
Martin Mareš committed
858
            WHERE c.cid IN (SELECT cid FROM tmp_cids)
859
860
              AND s.observer_uid IN (SELECT uid FROM owl_enroll WHERE cid=c.cid AND is_teacher=true)
              AND s.observer_uid <> %s
Martin Mareš's avatar
Martin Mareš committed
861
        """, (g.uid,))
862
863
864
865
866
867

    for t in db.fetchall():
        try:
            s = solutions[t.cid][t.target_uid][t.tid]
            s.last_seen_by_teacher = t.seen
        except KeyError:
Martin Mareš's avatar
Martin Mareš committed
868
            pass
Martin Mareš's avatar
Martin Mareš committed
869

Martin Mareš's avatar
Martin Mareš committed
870
871
872
873
874
    # app.logger.debug(courses)
    # app.logger.debug(topics)
    # app.logger.debug(students)
    # app.logger.debug(solutions)
    # app.logger.debug(totals)
Martin Mareš's avatar
Martin Mareš committed
875

876
    set_g_now()
Martin Mareš's avatar
Martin Mareš committed
877
878
879
880
    return render_template(
        'teacher.html',
        courses=courses,
        topics=topics,
881
        topics_followed_by_header=topics_followed_by_header,
Martin Mareš's avatar
Martin Mareš committed
882
883
884
885
        students=students,
        solutions=solutions,
        course_totals=course_totals,
        totals=totals)
886
887


Martin Mareš's avatar
Martin Mareš committed
888
889
890
### Serving files ###


Martin Mareš's avatar
Martin Mareš committed
891
892
893
894
895
896
897
898
899
900
901
902
903
904
ext_to_mime_type = {
    'pdf': 'application/pdf',
    'txt': 'text/plain; charset=utf-8',
}


@app.route('/files/<name>')
def serve_file(name):
    m = re.fullmatch(r'([0-9a-zA-Z]+)\.([0-9a-zA-Z]+)', name)
    if m:
        return send_file(os.path.join(app.instance_path, "files", name),
            mimetype = ext_to_mime_type[m.group(2)],
            attachment_filename = name)
    else:
Martin Mareš's avatar
Martin Mareš committed
905
        return "No such file", HTTPStatus.NOT_FOUND
Martin Mareš's avatar
Martin Mareš committed
906
907


908
909
### Exports ###

910
911
912
@app.route('/teacher/c/<sident>/<cident>/results.json')
def results_json(sident, cident):
    err = course_init(sident, cident) or must_be_teacher()
913
914
915
916
917
918
    if err:
        return err

    return export_points('json')


919
920
921
@app.route('/teacher/c/<sident>/<cident>/results.csv')
def results_csv(sident, cident):
    err = course_init(sident, cident) or must_be_teacher()
922
923
924
925
926
927
928
929
    if err:
        return err

    return export_points('csv')


def export_points(format):
    db_query("""
930
            SELECT u.uid, u.ukco, u.first_name, u.last_name
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
            FROM owl_users u
            JOIN owl_enroll e USING(uid)
            WHERE e.cid = %s
              AND e.is_teacher = false
            ORDER BY uid
        """, (g.course.cid,))
    students = db.fetchall()

    db_query("""
            SELECT *
            FROM owl_topics
            WHERE cid=%s
              AND type IN ('A', 'T')
            ORDER BY rank, tid
        """, (g.course.cid,))
    topics = db.fetchall()

    db_query("""
            SELECT t.ident AS tident, p.target_uid, p.points
            FROM owl_topics t
            JOIN owl_posts p USING(tid)
            WHERE t.cid = %s
              AND p.points IS NOT NULL
            ORDER BY p.created
        """, (g.course.cid,))
    awards = db.fetchall()

    points = { s.uid: {} for s in students }
    for a in awards:
        if a.target_uid == -1:
            for uid in points.keys():
                points[uid][a.tident] = float(a.points)
        elif a.target_uid in points:
            points[a.target_uid][a.tident] = float(a.points)

    if format == "json":
        out = []
        for s in students:
            out.append({
                "uid": s.uid,
                "ukco": s.ukco,
972
                "name": full_name(s),
973
974
975
976
977
978
                "tasks": points[s.uid],
                "points": sum(points[s.uid].values()),
            })

        enc = JSONEncoder(ensure_ascii=False, sort_keys=True, indent=4)
        resp = make_response(enc.encode(out))
979
        resp.mimetype = 'application/json'
980
981
982
983
984
985
986
987
988
        return resp
    elif format == "csv":
        out = io.StringIO()
        writer = csv.writer(out, delimiter=',', quoting=csv.QUOTE_MINIMAL)
        writer.writerow(['uid', 'ukco', 'name', 'points'] + [t.ident for t in topics])
        for s in students:
            writer.writerow([
                s.uid,
                s.ukco,
989
                full_name(s),
990
991
992
                sum(points[s.uid].values()),
            ] + [points[s.uid].get(t.ident, "") for t in topics])
        resp = make_response(out.getvalue())
993
994
        resp.mimetype = 'text/csv'
        resp.mimetype_params['charset'] = 'utf-8'
995
996
997
998
999
        return resp
    else:
        raise NotImplementedError()


Martin Mareš's avatar
Martin Mareš committed
1000
### User settings ###