server.py 28.9 KB
Newer Older
1
from flask import Flask, g, request, url_for, redirect, session, render_template, flash, Response, make_response
2
from werkzeug.routing import Rule
3
from functools import wraps
Julian Rother's avatar
Julian Rother committed
4
from datetime import date, timedelta, datetime, time, MINYEAR
5
import threading
6
import os
7
import sys
Julian Rother's avatar
Julian Rother committed
8
import hashlib
9
import random
10
import sched
11
import traceback
12
import string
13
from socket import gethostname
14

15
app = Flask(__name__)
16

Andreas Valder's avatar
Andreas Valder committed
17
18
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True
Julian Rother's avatar
Julian Rother committed
19
app.add_template_global(random.randint, name='randint')
20
21
app.add_template_global(datetime, name='datetime')
app.add_template_global(timedelta, name='timedelta')
22
app.add_template_global(gethostname, name='gethostname')
Andreas Valder's avatar
Andreas Valder committed
23

24
25
scheduler = sched.scheduler()
def run_scheduler():
Andreas Valder's avatar
Andreas Valder committed
26
	import time
27
	time.sleep(1) # UWSGI does weird things on startup
28
29
	while True:
		scheduler.run()
30
		time.sleep(10)
31

32
33
34
def sched_func(delay, priority=0, firstdelay=None, args=[], kargs={}):
	if firstdelay == None:
		firstdelay = random.randint(1, 120)
35
36
37
	def wrapper(func):
		def sched_wrapper():
			with app.test_request_context():
38
39
40
41
				try:
					func(*args, **kargs)
				except Exception:
					traceback.print_exc()
42
			scheduler.enter(delay, priority, sched_wrapper)
43
		scheduler.enter(firstdelay, priority, sched_wrapper)
44
45
46
47
		return func
	return wrapper

threading.Thread(target=run_scheduler, daemon=True).start()
48

49
config = app.config
50
config.from_pyfile('config.py.example', silent=True)
51
52
53
if sys.argv[0].endswith('run.py'): 
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
54
config.from_pyfile('config.py', silent=True)
Andreas Valder's avatar
Andreas Valder committed
55
56
if config['DEBUG']:
	app.jinja_env.auto_reload = True
Andreas Valder's avatar
Andreas Valder committed
57
58
59

# get git commit
import subprocess
Andreas Valder's avatar
Andreas Valder committed
60
61
output = subprocess.check_output(['git', "log", "-g", "-1", "--pretty=%H # %h # %d # %s"]).decode('UTF-8').split('#')
app.jinja_env.globals['gitversion'] = { 'hash': output[1], 'longhash': output[0], 'branch': output[2], 'msg': output[3]  }
62

63
64
if not config.get('SECRET_KEY', None):
	config['SECRET_KEY'] = os.urandom(24)
Julian Rother's avatar
Julian Rother committed
65

Julian Rother's avatar
Julian Rother committed
66
from db import query, modify, searchquery, ldapauth, ldapget
Julian Rother's avatar
Julian Rother committed
67

68
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
69

Julian Rother's avatar
Cleanup    
Julian Rother committed
70
@app.template_global()
71
72
73
74
def ismod(*args):
	return ('user' in session)

def mod_required(func):
75
	mod_endpoints.append(func.__name__)
76
77
	@wraps(func)
	def decorator(*args, **kwargs):
78
		if not ismod():
79
80
81
82
83
84
			flash('Diese Funktion ist nur für Moderatoren verfügbar!')
			return redirect(url_for('login', ref=request.url))
		else:
			return func(*args, **kwargs)
	return decorator

85
86
csrf_endpoints = []

87
def csrf_protect(func):
88
	csrf_endpoints.append(func.__name__)
89
90
91
92
	@wraps(func)
	def decorator(*args, **kwargs):
		if '_csrf_token' in request.values:
			token = request.values['_csrf_token']
Andreas Valder's avatar
Andreas Valder committed
93
		elif request.get_json() and ('_csrf_token' in request.get_json()):
94
95
			token = request.get_json()['_csrf_token']
		else:
96
			token = None
97
98
99
100
101
102
		if not ('_csrf_token' in session) or (session['_csrf_token'] != token ) or not token: 
			return 'csrf test failed', 403
		else:
			return func(*args, **kwargs)
	return decorator

103
104
@app.url_defaults
def csrf_inject(endpoint, values):
105
	if endpoint not in csrf_endpoints or not session.get('_csrf_token'):
106
107
108
		return
	values['_csrf_token'] = session['_csrf_token']

109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
def evalperm(perms):
	cperms = []
	lperms = []
	vperms = []
	for perm in perms:
		if perm['course_id']:
			cperms.append(perm)
		elif perm['lecture_id']:
			lperms.append(perm)
		elif perm['video_id']:
			vperms.append(perm)
	if vperms:
		return vperms
	elif lperms:
	 	return lperms
	elif cperms:
		return cperms
	return [{'type': 'public'}]
127
128

@app.template_filter()
129
def checkperm(perms, username=None, password=None):
130
131
	if ismod():
		return True
132
133
134
	perms = evalperm(perms)
	for perm in perms:
		if perm['type'] == 'public':
135
			return True
136
137
		elif perm['type'] == 'password':
			if perm['param1'] == username and perm['param2'] == password:
138
				return True
139
140
		elif perm['type'] == 'l2p':
			if perm['param1'] in session.get('l2p_courses', []):
141
				return True
142
		elif perm['type'] == 'rwth':
143
144
			if session.get('rwthintern', False):
				return True
145
146
147
148
149
150
			if 'X-Real-IP' not in request.headers:
				continue
			ip = ip_address(request.headers['X-Real-IP'])
			for net in RWTH_IP_RANGES:
				if ip in ip_network(net):
					return True
151
152
153
	return False

@app.template_filter()
154
155
def permdescr(perms):
	perms = evalperm(perms)
156
157
158
159
	public = False
	password = False
	l2p_courses = []
	rwth_intern = False
160
	fsmpi_intern = False
161
162
	for perm in perms:
		if perm['type'] == 'public':
163
			public = True
164
		elif perm['type'] == 'password':
165
			password = True
166
		elif perm['type'] == 'l2p':
167
			l2p_courses.append(perm['param1'])
168
		elif perm['type'] == 'rwth':
169
			rwth_intern = True
170
171
		elif perm['type'] == 'fsmpi':
			fsmpi_intern = True
172
	if public or not perms:
173
174
175
176
177
		return 'public', 'Öffentlich verfügbar'
	if rwth_intern:
		if password:
			return 'rwth', 'Nur für RWTH-Angehörige und Nutzer mit Passwort verfügbar'
		return 'rwth', 'Nur für RWTH-Angehörige verfügbar'
178
179
	if fsmpi_intern:
		return 'fsmpi', 'Nur für Fachschaftler verfügbar'
180
181
	if l2p_courses:
		if password:
182
183
			return 'l2p', 'Nur für Teilnehmer der Veranstaltung und Nutzer mit Passwort verfügbar'
		return 'l2p', 'Nur für Teilnehmer der Veranstaltung verfügbar'
184
185
186
187
	if password:
		return 'password', 'Nur für Nutzer mit Passwort verfügbar'
	return 'public', 'Öffentlich verfügbar'

188
app.jinja_env.globals['navbar'] = []
189
190
191
192
193
# iconlib can be 'bootstrap'
# ( see: http://getbootstrap.com/components/#glyphicons )
# or 'fa'
# ( see: http://fontawesome.io/icons/ )
def register_navbar(name, iconlib='bootstrap', icon=None):
194
	def wrapper(func):
195
		endpoint = func.__name__
196
		app.jinja_env.globals['navbar'].append((endpoint, name, iconlib, icon, not endpoint in mod_endpoints))
197
198
199
		return func
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
200
201
202
203
def render_endpoint(endpoint, flashtext=None, **kargs):
	if flashtext:
		flash(flashtext)
	# request.endpoint is used for navbar highlighting
204
	request.url_rule = Rule(request.path, endpoint=endpoint)
Julian Rother's avatar
Cleanup    
Julian Rother committed
205
206
	return app.view_functions[endpoint](**kargs)

207
208
209
210
211
212
213
def handle_errors(endpoint, text, code, *errors, **epargs):
	def wrapper(func):
		@wraps(func)
		def decorator(*args, **kwargs):
			try:
				return func(*args, **kwargs)
			except errors:
Julian Rother's avatar
Julian Rother committed
214
				if endpoint:
215
					return make_response(render_endpoint(endpoint, text, **epargs), code)
Julian Rother's avatar
Julian Rother committed
216
217
				else:
					return text, code
218
219
220
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
221
222
@app.errorhandler(404)
def handle_not_found(e):
223
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
224

225
226
227
228
@app.errorhandler(500)
@app.errorhandler(Exception)
def handle_internal_error(e):
	traceback.print_exc()
229
	return render_template('500.html'), 500
230

231
232
233
234
235
236
237
238
239
240
@sched_func(5*60, firstdelay=0)
def dump_error_page():
	if 'ERROR_PAGE' not in config:
		return
	request.url_rule = Rule(request.path, endpoint='handle_internal_error')
	text = render_template('500.html')
	f = open(config['ERROR_PAGE'], 'w')
	f.write(text)
	f.close()

Andreas Valder's avatar
Andreas Valder committed
241
242
# debian ships jinja2 without this test...
@app.template_test(name='equalto')
243
244
245
def equalto(a,b):
	return a == b

Julian Rother's avatar
Julian Rother committed
246
@app.template_filter(name='semester')
247
248
249
250
251
252
253
254
255
256
257
258
259
260
def human_semester(s, long=False):
	if not s or s == 'zeitlos' or len(s) != 6:
		return 'Zeitlos'
	year = s[0:4]
	semester = s[4:6].upper()
	if not year.isdigit() or semester not in ['SS', 'WS']:
		print('Invalid semester string "%s"'%s)
		return '??'
	if not long:
		return semester+year[2:]
	elif semester == 'SS':
		return 'Sommersemester %s'%year
	else:
		return 'Wintersemester %s/%s'%(year, str(int(year)+1)[2:])
Julian Rother's avatar
Julian Rother committed
261
262
263

@app.template_filter(name='date')
def human_date(d):
Andreas Valder's avatar
Andreas Valder committed
264
	return d.strftime('%d.%m.%Y')
Julian Rother's avatar
Julian Rother committed
265

Andreas Valder's avatar
Andreas Valder committed
266
@app.template_filter(name='time')
267
def human_time(d):
Andreas Valder's avatar
Andreas Valder committed
268
269
	return d.strftime('%H:%M')

Julian Rother's avatar
Julian Rother committed
270
271
272
273
@app.template_filter()
def rfc3339(d):
	return d.strftime('%Y-%m-%dT%H:%M:%S+02:00')

274
275
@app.template_global()
def get_announcements(minlevel=0):
276
277
	offset = timedelta()
	if ismod():
278
		offset = timedelta(hours=24)
279
280
281
282
	try:
		return query('SELECT * FROM announcements WHERE NOT deleted AND ((time_expire = NULL) OR time_expire > ?) AND (? OR (visible AND time_publish < ?)) AND level >= ? ORDER BY level DESC', datetime.now()-offset, ismod(), datetime.now(), minlevel)
	except:
		return []
283

284
285
286
287
288
@app.template_filter()
def fixnl(s):
	# To be remove, as soon as db schema is cleaned-up
	return str(s).replace('\n', '<br>')

289
290
291
292
293
294
295
296
297
298
299
@app.template_filter()
def tagid(s):
	if not s:
		return 'EMPTY'
	s = s.replace(' ', '_').lower()
	r = ''
	for c in s:
		if c in string.ascii_lowercase+string.digits+'_':
			r = r + c
	return r

300
@app.route('/')
301
@register_navbar('Home', icon='home')
302
def index():
303
304
305
	# handle legacy urls...
	if 'course' in request.args:
		return redirect(url_for('course', handle=request.args['course']),code=302)
306
307
	if 'view' in request.args:
		if (request.args['view'] == 'player') and ('lectureid' in request.args) :
308
309
310
311
			courses = query('SELECT courses.handle FROM courses JOIN lectures ON courses.id = lectures.course_id WHERE lectures.id = ?', request.args['lectureid'])
			if not courses:
				return "Not found", 404
			return redirect(url_for('lecture', course=courses[0]['handle'], id=request.args['lectureid']),code=302)
312

313
314
	start = date.today() - timedelta(days=1)
	end = start + timedelta(days=7)
315
316
	upcomming = query('''
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
317
318
		FROM lectures
		JOIN courses ON (lectures.course_id = courses.id)
319
320
		WHERE (time > ?) AND (time < ?) and lectures.visible and courses.visible and courses.listed
		ORDER BY time ASC LIMIT 30''',start,end)
Andreas Valder's avatar
Andreas Valder committed
321
322
323
	for i in upcomming:
		i['date'] = i['time'].date()
	latestvideos=query('''
324
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
325
326
327
328
329
		FROM lectures
		LEFT JOIN videos ON (videos.lecture_id = lectures.id)
		LEFT JOIN courses on (courses.id = lectures.course_id)
		WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
		GROUP BY videos.lecture_id
330
		ORDER BY MAX(videos.time_created) DESC
Andreas Valder's avatar
Andreas Valder committed
331
		LIMIT 6	''',ismod())
332
333
334
335
336
337
	livestreams = query('''SELECT streams.handle AS live, lectures.*, "course" AS sep, courses.*
		FROM streams
		JOIN lectures ON lectures.id = streams.lecture_id
		JOIN courses ON courses.id = lectures.course_id
		WHERE streams.active AND (? OR (streams.visible AND courses.visible AND courses.listed AND lectures.visible))
		''', ismod())
Julian Rother's avatar
Julian Rother committed
338
	featured = query('SELECT * FROM featured WHERE (? OR visible) ORDER BY `order`', ismod())
339
	featured = list(filter(lambda x: not x['deleted'], featured))
Julian Rother's avatar
Julian Rother committed
340
341
342
343
	for item in featured:
		if item['type'] == 'courses':
			if item['param'] not in ['title', 'semester', 'organizer', 'subject']:
				continue
344
			item['courses'] = query('SELECT * FROM courses WHERE (visible AND listed) AND `%s` = ? ORDER BY `%s`'%(item['param'], item['param']), item['param2'])
345
	return render_template('index.html', latestvideos=livestreams+latestvideos, upcomming=upcomming, featured=featured)
346

347
@app.route('/courses')
348
@register_navbar('Videos', icon='film')
349
def courses():
350
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed)) ORDER BY title', ismod())
351
352
353
	for course in courses:
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
354
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Julian Rother committed
355
	if groupedby not in ['title', 'semester', 'organizer', 'subject']:
Andreas Valder's avatar
Andreas Valder committed
356
		groupedby = 'semester'
357
	return render_template('courses.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
358

359
360
361
362
363
364
365
366
def genlive(streams):
	for stream in streams:
		stream['visible'] = True
		stream['downloadable'] = False
		stream['path'] = 'pub/hls/%s.m3u8'%stream['live']
		stream['file_size'] = 0
	return streams

367
368
@app.route('/<handle>')
@app.route('/<int:id>')
369
@handle_errors('courses', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
370
371
def course(id=None, handle=None):
	if id:
372
		course = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', id, ismod())[0]
373
	else:
374
		course = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', handle, ismod())[0]
375
376
	course['perm'] = query('SELECT * FROM perm WHERE (NOT perm.deleted) AND course_id = ? ORDER BY type', course['id'])
	perms = query('SELECT perm.* FROM perm JOIN lectures ON (perm.lecture_id = lectures.id) WHERE (NOT perm.deleted) AND lectures.course_id = ? ORDER BY perm.type', course['id'])
377
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible) ORDER BY time, duration DESC', course['id'], ismod())
378
	for lecture in lectures:
379
		lecture['perm'] = []
380
		lecture['perm'] += course['perm']
381
		lecture['course'] = course
382
383
384
		for perm in perms:
			if perm['lecture_id'] == lecture['id']:
				lecture['perm'].append(perm)
Andreas Valder's avatar
Andreas Valder committed
385
	videos = query('''
386
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description, formats.player_prio, formats.prio
Andreas Valder's avatar
Andreas Valder committed
387
388
389
390
391
392
			FROM videos
			JOIN lectures ON (videos.lecture_id = lectures.id)
			JOIN formats ON (videos.video_format = formats.id)
			JOIN courses ON (lectures.course_id = courses.id)
			WHERE lectures.course_id= ? AND (? OR videos.visible)
			ORDER BY lectures.time, formats.prio DESC
393
			''', course['id'], ismod())
394
395
396
397
398
399
400
	livestreams = query('''SELECT streams.handle AS live, streams.lecture_id, formats.description AS format_description, formats.player_prio, formats.prio
			FROM streams
			JOIN lectures ON lectures.id = streams.lecture_id
			JOIN formats ON formats.keywords = "hls"
			WHERE streams.active AND (? OR streams.visible) AND lectures.course_id = ?
			''', ismod(), course['id'])
	videos += genlive(livestreams)
401
	return render_template('course.html', course=course, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
402

Andreas Valder's avatar
Andreas Valder committed
403
@app.route('/faq')
404
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
405
def faq():
406
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
407

408
409
410
411
@app.route('/<course>/<int:id>')
@app.route('/<int:courseid>/<int:id>')
@app.route('/<course>/<int:id>/embed', endpoint='embed')
@app.route('/<int:courseid>/<int:id>/embed', endpoint='embed')
412
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
413
def lecture(id, course=None, courseid=None):
Andreas Valder's avatar
Andreas Valder committed
414
415
	lecture = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())[0]
	videos = query('''
416
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description, formats.player_prio, formats.prio, formats.mimetype
Andreas Valder's avatar
Andreas Valder committed
417
418
			FROM videos
			JOIN formats ON (videos.video_format = formats.id)
419
420
421
422
			JOIN courses ON (courses.id = ?)
			WHERE videos.lecture_id = ? AND (? OR videos.visible)
			ORDER BY formats.prio DESC
			''', lecture['course_id'], lecture['id'], ismod())
423
424
425
426
427
428
429
	livestreams = query('''SELECT streams.handle AS live, streams.lecture_id, formats.description AS format_description, formats.player_prio, formats.prio, formats.mimetype
			FROM streams
			JOIN lectures ON lectures.id = streams.lecture_id
			JOIN formats ON formats.keywords = "hls"
			WHERE streams.active AND (? OR streams.visible) AND lectures.id = ?
			''', ismod(), id)
	videos += genlive(livestreams)
430
	perms = query('SELECT perm.* FROM perm WHERE ((NOT perm.deleted) AND (perm.lecture_id = ? OR perm.course_id = ?))',
431
			lecture['id'], lecture['course_id'])
432
433
	if not videos:
		flash('Zu dieser Vorlesung wurden noch keine Videos veröffentlicht!')
434
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', lecture['course_id'], ismod())
435
436
	if not courses:
		return render_endpoint('courses', 'Diese Veranstaltung existiert nicht!'), 404
437
	chapters = query('SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (? OR visible) ORDER BY time ASC', id, ismod())
438
439
	if not checkperm(perms):
		mode, text = permdescr(perms)
440
		if mode == 'rwth':
441
			flash(text+'. <a target="_blank" class="reloadonclose" href="'+url_for('start_rwthauth')+'">Hier authorisieren</a>.', category='player')
442
		elif mode == 'l2p':
443
			if 'l2p_courses' in session:
444
				flash(text+'. Du bist kein Teilnehmer des L2P-Kurses! <a target="_blank" class="reloadonclose" href="'+url_for('start_l2pauth')+'">Kurse aktualisieren</a>.', category='player')
445
			else:
446
				flash(text+'. <a target="_blank" class="reloadonclose" href="'+url_for('start_l2pauth')+'">Hier authorisieren</a>.', category='player')
447
		else:
448
			flash(text+'.', category='player')
449
	return render_template('embed.html' if request.endpoint == 'embed' else 'lecture.html', course=courses[0], lecture=lecture, videos=videos, chapters=chapters)
Andreas Valder's avatar
Andreas Valder committed
450

451
452
453
454
455
456
457

@app.route('/search')
def search():
	if 'q' not in request.args:
		return redirect(url_for('index'))
	q = request.args['q']
	courses = searchquery(q, '*', ['title', 'short', 'organizer', 'subject', 'description'],
458
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
Julian Rother's avatar
Julian Rother committed
459
460
461
462
463
	#lectures = searchquery(q, 'lectures.*, courses.visible AS coursevisible, courses.listed, "course" AS sep, courses.*',
	#			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
	#			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
	#			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
	lectures = searchquery(q, 'lectures.*, courses.visible AS coursevisible, courses.listed, courses.id AS courses_id, courses.visible AS courses_visible, courses.listed AS courses_listed, courses.title AS courses_title, courses.short AS courses_short, courses.handle AS courses_handle, courses.organizer AS courses_organizer, courses.subject AS courses_subject, courses.credits AS courses_credits, courses.created_by AS courses_created_by, courses.time_created AS courses_time_created, courses.time_updated AS courses_time_updated, courses.semester AS courses_semester, courses.downloadable AS courses_downloadable, courses.embedinvisible AS courses_embedinvisible, courses.description AS courses_description, courses.internal AS courses_internal, courses.responsible AS courses_responsible',
464
465
			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
466
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
Julian Rother's avatar
Julian Rother committed
467
468
469
470
471
	for lecture in lectures:
		lecture['course'] = {}
		for key in lecture:
			if key.startswith('courses_'):
				lecture['course'][key[8:]] = lecture[key]
472
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
473

474
475
476
def check_mod(user, groups):
	return user and 'users' in groups

477
@app.route('/internal/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
478
def login():
479
480
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
481
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
482
	if not check_mod(user, groups):
483
		flash('Login fehlgeschlagen!')
484
485
486
487
		return render_template('login.html')
	session['user'] = ldapget(user)
	dbuser = query('SELECT * FROM users WHERE name = ?', user)
	if not dbuser:
Julian Rother's avatar
Julian Rother committed
488
		modify('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
489
490
		dbuser = query('SELECT * FROM users WHERE name = ?', user)
	session['user']['dbid'] = dbuser[0]['id']
491
	session['_csrf_token'] = ''.join(random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(128))
Julian Rother's avatar
Julian Rother committed
492
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
493

494
@app.route('/internal/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
495
496
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
497
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
498

499
# name: (tablename, idcolumn, [editable_fields], [fields_to_set_at_creation_time])
500
501
502
tabs = {
	'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
			'handle', 'organizer', 'subject', 'semester', 'downloadable',
503
			'internal', 'responsible','deleted','description'],
504
			['created_by', 'time_created', 'time_updated']),
505
	'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
506
507
508
509
510
511
512
513
514
			'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted'],
			['course_id', 'time_created', 'time_updated']),
	'videos': ('videos_data', 'id', ['visible','deleted'],
			['created_by', 'time_created', 'time_updated']),
	'chapters': ('chapters', 'id', ['time', 'text', 'visible', 'deleted'],
			['created_by', 'time_created', 'time_updated']),
	'announcements': ('announcements', 'id', ['text', 'level', 'visible',
			'deleted', 'time_publish', 'time_expire'],
			['created_by', 'time_created', 'time_updated']),
Julian Rother's avatar
Julian Rother committed
515
	'featured': ('featured', 'id', ['title', 'text', 'internal', 'visible', 'deleted', 'param', 'param2', 'order'],
516
			['created_by', 'time_created', 'time_updated', 'type']),
517
	'perm': ('perm', 'id', ['type', 'param1', 'param2', 'deleted'],
518
519
520
			['course_id', 'lecture_id', 'video_id', 'created_by', 'time_created', 'time_updated']),
	'sorterrorlog': ('sorterrorlog_data', 'id', ['deleted'],
			['time_created', 'time_updated'])
521
522
}

523
@app.route('/internal/edit', methods=['GET', 'POST'])
524
@mod_required
525
@csrf_protect
526
def edit(prefix='', ignore=[]):
527
	# All editable tables are expected to have a 'time_updated' field
528
	ignore.append('ref')
529
	ignore.append('prefix')
530
	ignore.append('_csrf_token')
531
532
	if not prefix and 'prefix' in request.args:
		prefix = request.args['prefix']
533
	changes = request.values.items()
534
	if (request.method == 'POST') and (request.get_json()):
Julian Rother's avatar
Julian Rother committed
535
536
		changes = request.get_json().items()
	for key, val in changes:
537
538
539
		if key in ignore:
			continue
		key = prefix+key
540
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
541
542
		assert table in tabs
		assert column in tabs[table][2]
543
		modify('INSERT INTO changelog (`table`,id_value, id_key, field, value_new, value_old, `when`, who, executed) VALUES (?,?,?,?,?,(SELECT `%s` FROM %s WHERE %s = ?),?,?,1)'%(column, tabs[table][0], tabs[table][1]),
544
				table, id, tabs[table][1], column, val, id, datetime.now(), session['user']['dbid'])
545
		modify('UPDATE %s SET `%s` = ?, time_updated = ? WHERE `%s` = ?'%(tabs[table][0], column, tabs[table][1]), val, datetime.now(), id)
546
547
	if 'ref' in request.values:
		return redirect(request.values['ref'])
548
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
549

550
@app.route('/internal/new/<table>', methods=['GET', 'POST'])
551
@mod_required
552
@csrf_protect
553
554
def create(table):
	assert table in tabs
555
556
557
558
559
560
561
	defaults = {'created_by': session['user']['dbid'], 'time_created': datetime.now(), 'time_updated': datetime.now()}
	columns = []
	values = []
	for column, val in defaults.items():
		if column in tabs[table][3]:
			columns.append(column)
			values.append(val)
Andreas Valder's avatar
Andreas Valder committed
562
	args = request.values.items()
563
	if (request.method == 'POST') and (request.get_json()):
564
565
566
		args = request.get_json().items()
	for column, val in args:
		if (column == 'ref') or (column == '_csrf_token'):
567
			continue
568
569
		assert column in tabs[table][2]+tabs[table][3]
		assert column not in defaults
Julian Rother's avatar
Julian Rother committed
570
		columns.append('`'+column+'`')
571
572
573
		values.append(val)
	id = modify('INSERT INTO %s (%s) VALUES (%s)'%(tabs[table][0],
				','.join(columns), ','.join(['?']*len(values))), *values)
574
575
576
577
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return str(id), 200

578
@app.route('/internal/auth')
579
580
581
582
def auth(): # For use with nginx auth_request
	if 'X-Original-Uri' not in request.headers:
		return 'Internal Server Error', 500
	url = request.headers['X-Original-Uri'].lstrip(config['VIDEOPREFIX'])
Julian Rother's avatar
Julian Rother committed
583
584
	if request.cookies.get('tracking', '') and request.cookies['tracking'].isdigit():
		cookie = int(request.cookies['tracking'])
585
	else:
Julian Rother's avatar
Julian Rother committed
586
		cookie = random.getrandbits(8*8-1)
587
	if url.endswith('jpg') or ismod():
588
		return "OK", 200
589
590
	if url.startswith('pub/hls/'):
		handle = url[len('pub/hls/'):].split('_')[0].split('.')[0]
591
		perms = query('''SELECT lectures.id AS lecture, perm.*
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
				FROM streams
				JOIN lectures ON (streams.lecture_id = lectures.id)
				JOIN courses ON (lectures.course_id = courses.id)
				LEFT JOIN perm ON ((lectures.id = perm.lecture_id OR courses.id = perm.course_id) AND NOT perm.deleted)
				WHERE streams.handle = ?
				AND (courses.visible AND lectures.visible AND streams.visible)
				ORDER BY perm.video_id DESC, perm.lecture_id DESC, perm.course_id DESC''', handle)
	else:
		perms = query('''SELECT videos.path, videos.id AS vid, perm.*
				FROM videos
				JOIN lectures ON (videos.lecture_id = lectures.id)
				JOIN courses ON (lectures.course_id = courses.id)
				LEFT JOIN perm ON ((videos.id = perm.video_id OR lectures.id = perm.lecture_id OR courses.id = perm.course_id) AND NOT perm.deleted)
				WHERE videos.path = ?
				AND (courses.visible AND lectures.visible AND videos.visible)
				ORDER BY perm.video_id DESC, perm.lecture_id DESC, perm.course_id DESC''',
				url)
609
	if not perms:
610
		return "Not allowed", 403
611
	auth = request.authorization
612
613
614
615
	username = password = None
	if auth:
		username = auth.username
		password = auth.password
616
	if checkperm(perms, username=username, password=password):
617
		try:
618
619
			if not url.startswith('pub/hls/'):
				modify('INSERT INTO log (id, `time`, `date`, video, source) VALUES (?, ?, ?, ?, 1)', cookie, datetime.now(), datetime.combine(date.today(), time()), perms[0]['vid'])
620
621
622
623
			elif url.endswith('.ts'):
				fmt = url.split('_')[-1].split('-')[0]
				seg = url.split('.')[0].split('-')[-1]
				modify('INSERT INTO hlslog (id, `time`, segment, lecture, handle, format) VALUES (?, ?, ?, ?, ?, ?)', cookie, datetime.now(), seg, perms[0]['lecture'], handle, fmt)
624
625
626
		except:
			pass
		r = make_response('OK', 200)
Julian Rother's avatar
Julian Rother committed
627
		r.set_cookie('tracking', str(cookie), max_age=2147483647) # Many many years
628
		return r
629
	password_auth = False
630
631
	for perm in perms:
		if perm['type'] == 'password':
632
633
634
			password_auth = True
			break
	if password_auth:
635
636
		return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'})
	return "Not allowed", 403
Andreas Valder's avatar
Andreas Valder committed
637

638
@app.route('/internal/changelog')
Andreas Valder's avatar
Andreas Valder committed
639
@register_navbar('Changelog', icon='book')
Andreas Valder's avatar
Andreas Valder committed
640
@mod_required
641
def changelog():
642
643
644
	changelog = query('SELECT * FROM changelog LEFT JOIN users ON (changelog.who = users.id) ORDER BY `when` DESC LIMIT 50')
	for entry in changelog:
		entry['path'] = '.'.join([entry['table'], entry['id_value'], entry['field']])
645
	return render_template('changelog.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
646

Julian Rother's avatar
Julian Rother committed
647
648
649
650
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

651
@app.route('/internal/newchapter/<int:lectureid>', methods=['POST', 'GET'])
652
653
654
655
def suggest_chapter(lectureid):
	time = request.values['time']
	text = request.values['text']
	assert(time and text)
656
657
658
659
660
661
662
	try:
		x = datetime.strptime(time,'%H:%M:%S')
		time= timedelta(hours=x.hour,minutes=x.minute,seconds=x.second).total_seconds()
		time = int(time)
	except ValueError:
		flash('Falsches Zeitformat, "%H:%M:%S" wird erwartet. Z.B. "01:39:42" für eine Kapitel bei Stunde 1, Minute 39, Sekunde 42')
		
663
664
665
	submitter = None
	if not ismod():
		submitter = request.environ['REMOTE_ADDR']
Julian Rother's avatar
Julian Rother committed
666
	id = modify('INSERT INTO chapters (lecture_id, time, text, time_created, time_updated, created_by, submitted_by) VALUES (?, ?, ?, ?, ?, ?, ?)',
667
668
669
670
671
				lectureid, time, text, datetime.now(), datetime.now(), session.get('user', {'dbid':None})['dbid'], submitter)
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return 'OK',  200

672
@app.route('/internal/chapters/<int:lectureid>')
673
def chapters(lectureid):
674
	chapters = query("SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (visible OR ?) ORDER BY time DESC", lectureid, ismod())
675
676
	if not chapters:
		return 'No chapters found', 404
677
678
679
680
681
682
683
	last = None
	for c in chapters:
		c['start'] = c['time']
		c['end'] = last['start'] if last else 9999
		last = c
	return Response(render_template('chapters.srt',chapters=chapters), 200, {'Content-Type':'text/vtt'})

Andreas Valder's avatar
Andreas Valder committed
684
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
685
686
687
688
689
690
691
692
693
694
def sitemap():
	pages=[]
	# static pages
	for rule in app.url_map.iter_rules():
		if 'GET' in rule.methods and len(rule.arguments)==0:
			if rule.endpoint not in mod_endpoints:
				pages.append([rule.rule])
	for i in query('select * from courses where visible and listed'):
		pages.append([url_for('course',handle=i['handle'])])
		for j in query('select * from lectures where (course_id = ? and visible)',i['id']):
695
			pages.append([url_for('lecture',course=i['handle'],id=j['id'])])
Andreas Valder's avatar
Andreas Valder committed
696
697
698
699


	return Response(render_template('sitemap.xml', pages=pages), 200, {'Content-Type': 'application/atom+xml'} )

700
701
702
703
704

@app.route('/site/')
@app.route('/site/<string:phpfile>')
def legacy(phpfile=None):
	if phpfile=='embed.php' and ('lecture' in request.args):
705
706
707
708
		courses = query('SELECT courses.handle FROM courses JOIN lectures ON courses.id = lectures.course_id WHERE lectures.id = ?', request.args['lecture'])
		if not courses:
			return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
		return redirect(url_for('embed', course=courses[0]['handle'], id=request.args['lecture']),code=302)
709
710
711
712
713
714
715
716
	if phpfile=='embed.php' and ('vid' in request.args):
		lectures = query('SELECT lecture_id FROM videos WHERE id = ?', request.args['vid'])
		if not lectures:
			return render_endpoint('index', 'Dieses Videos existiert nicht!'), 404
		courses = query('SELECT courses.handle FROM courses JOIN lectures ON courses.id = lectures.course_id WHERE lectures.id = ?', lectures[0]['lecture_id'])
		if not courses:
			return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
		return redirect(url_for('embed', course=courses[0]['handle'], id=lectures[0]['lecture_id']),code=302)
717
718
719
720
721
722
723
724
725
726
	if phpfile=='feed.php' and ('all' in request.args):
		return redirect(url_for('feed'),code=302)
	if phpfile=='feed.php' and ('newcourses' in request.args):
		return redirect(url_for('courses_feed'),code=302)
	if phpfile=='feed.php':
		return redirect(url_for('feed', handle=request.args.copy().popitem()[0]),code=302)
	print("Unknown legacy url:",request.url)
	return redirect(url_for('index'),code=302)
	

Julian Rother's avatar
Julian Rother committed
727
import feeds
728
import importer
729
import stats
Andreas Valder's avatar
Andreas Valder committed
730
import sorter
731
732
if 'ICAL_URL' in config:
	import meetings
733
import l2pauth
Andreas Valder's avatar
Andreas Valder committed
734
735
if 'JOBS_API_KEY' in config:
	import jobs
Andreas Valder's avatar
Andreas Valder committed
736
import timetable