server.py 22.5 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

14
app = Flask(__name__)
15

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

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

30
31
32
def sched_func(delay, priority=0, firstdelay=None, args=[], kargs={}):
	if firstdelay == None:
		firstdelay = random.randint(1, 120)
33
34
35
	def wrapper(func):
		def sched_wrapper():
			with app.test_request_context():
36
				func(*args, **kargs)
37
			scheduler.enter(delay, priority, sched_wrapper)
38
		scheduler.enter(firstdelay, priority, sched_wrapper)
39
40
41
42
		return func
	return wrapper

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

44
config = app.config
45
config.from_pyfile('config.py.example', silent=True)
46
47
48
if sys.argv[0].endswith('run.py'): 
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
49
config.from_pyfile('config.py', silent=True)
Andreas Valder's avatar
Andreas Valder committed
50
51
if config['DEBUG']:
	app.jinja_env.auto_reload = True
52
53
if not config.get('SECRET_KEY', None):
	config['SECRET_KEY'] = os.urandom(24)
Julian Rother's avatar
Julian Rother committed
54

Julian Rother's avatar
Julian Rother committed
55
from db import query, modify, searchquery, ldapauth, ldapget
Julian Rother's avatar
Julian Rother committed
56

57
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
58

Julian Rother's avatar
Cleanup    
Julian Rother committed
59
@app.template_global()
60
61
62
63
def ismod(*args):
	return ('user' in session)

def mod_required(func):
64
	mod_endpoints.append(func.__name__)
65
66
	@wraps(func)
	def decorator(*args, **kwargs):
67
		if not ismod():
68
69
70
71
72
73
			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

74
75
csrf_endpoints = []

76
def csrf_protect(func):
77
	csrf_endpoints.append(func.__name__)
78
79
80
81
	@wraps(func)
	def decorator(*args, **kwargs):
		if '_csrf_token' in request.values:
			token = request.values['_csrf_token']
Andreas Valder's avatar
Andreas Valder committed
82
		elif request.get_json() and ('_csrf_token' in request.get_json()):
83
84
			token = request.get_json()['_csrf_token']
		else:
85
			token = None
86
87
88
89
90
91
		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

92
93
94
95
96
97
@app.url_defaults
def csrf_inject(endpoint, values):
	if endpoint not in csrf_endpoints or not session['_csrf_token']:
		return
	values['_csrf_token'] = session['_csrf_token']

98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
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'}]
116
117

@app.template_filter()
118
119
120
121
def checkperm(perms, username=None, password=None):
	perms = evalperm(perms)
	for perm in perms:
		if perm['type'] == 'public':
122
			return True
123
124
		elif perm['type'] == 'password':
			if perm['param1'] == username and perm['param2'] == password:
125
				return True
126
127
		elif perm['type'] == 'l2p':
			if perm['param1'] in session.get('l2p_courses', []):
128
				return True
129
		elif perm['type'] == 'rwth':
130
131
132
133
134
			if session.get('rwthintern', False):
				return True
	return False

@app.template_filter()
135
136
def permdescr(perms):
	perms = evalperm(perms)
137
138
139
140
	public = False
	password = False
	l2p_courses = []
	rwth_intern = False
141
142
	for perm in perms:
		if perm['type'] == 'public':
143
			public = True
144
		elif perm['type'] == 'password':
145
			password = True
146
		elif perm['type'] == 'l2p':
147
			l2p_courses.append(perm['param1'])
148
		elif perm['type'] == 'rwth':
149
			rwth_intern = True
150
	if public or not perms:
151
152
153
154
155
156
157
		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'
	if l2p_courses:
		if password:
158
159
			return 'l2p', 'Nur für Teilnehmer der Veranstaltung und Nutzer mit Passwort verfügbar'
		return 'l2p', 'Nur für Teilnehmer der Veranstaltung verfügbar'
160
161
162
163
	if password:
		return 'password', 'Nur für Nutzer mit Passwort verfügbar'
	return 'public', 'Öffentlich verfügbar'

164
app.jinja_env.globals['navbar'] = []
165
166
167
168
169
# 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):
170
	def wrapper(func):
171
		endpoint = func.__name__
172
		app.jinja_env.globals['navbar'].append((endpoint, name, iconlib, icon, not endpoint in mod_endpoints))
173
174
175
		return func
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
176
177
178
179
def render_endpoint(endpoint, flashtext=None, **kargs):
	if flashtext:
		flash(flashtext)
	# request.endpoint is used for navbar highlighting
180
	request.url_rule = Rule(request.path, endpoint=endpoint)
Julian Rother's avatar
Cleanup    
Julian Rother committed
181
182
	return app.view_functions[endpoint](**kargs)

183
184
185
186
187
188
189
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
190
				if endpoint:
191
					return make_response(render_endpoint(endpoint, text, **epargs), code)
Julian Rother's avatar
Julian Rother committed
192
193
				else:
					return text, code
194
195
196
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
197
198
@app.errorhandler(404)
def handle_not_found(e):
199
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
200

201
202
203
204
@app.errorhandler(500)
@app.errorhandler(Exception)
def handle_internal_error(e):
	traceback.print_exc()
205
	return render_template('500.html'), 500
206

207
208
209
210
211
212
213
214
215
216
@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
217
218
# debian ships jinja2 without this test...
@app.template_test(name='equalto')
219
220
221
def equalto(a,b):
	return a == b

Julian Rother's avatar
Julian Rother committed
222
@app.template_filter(name='semester')
223
224
225
226
227
228
229
230
231
232
233
234
235
236
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
237
238
239

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

Andreas Valder's avatar
Andreas Valder committed
242
@app.template_filter(name='time')
243
def human_time(d):
Andreas Valder's avatar
Andreas Valder committed
244
245
	return d.strftime('%H:%M')

Julian Rother's avatar
Julian Rother committed
246
247
248
249
@app.template_filter()
def rfc3339(d):
	return d.strftime('%Y-%m-%dT%H:%M:%S+02:00')

250
251
@app.template_global()
def get_announcements(minlevel=0):
252
253
	offset = timedelta()
	if ismod():
254
		offset = timedelta(hours=24)
255
256
257
258
	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 []
259

260
261
262
263
264
@app.template_filter()
def fixnl(s):
	# To be remove, as soon as db schema is cleaned-up
	return str(s).replace('\n', '<br>')

265
@app.route('/')
266
@register_navbar('Home', icon='home')
267
def index():
268
269
	start = date.today() - timedelta(days=1)
	end = start + timedelta(days=7)
270
271
	upcomming = query('''
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
272
273
		FROM lectures
		JOIN courses ON (lectures.course_id = courses.id)
274
275
		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
276
277
278
	for i in upcomming:
		i['date'] = i['time'].date()
	latestvideos=query('''
279
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
280
281
282
283
284
		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
285
		ORDER BY MAX(videos.time_updated) DESC
Andreas Valder's avatar
Andreas Valder committed
286
		LIMIT 6	''',ismod())
287
288
	featured = query('SELECT * FROM featured WHERE NOT deleted AND (? OR visible)', ismod())
	return render_template('index.html', latestvideos=latestvideos, upcomming=upcomming, featured=featured)
289

290
@app.route('/course')
291
@register_navbar('Videos', icon='film')
292
def courses():
293
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed)) ORDER BY title', ismod())
294
295
296
	for course in courses:
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
297
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Cleanup    
Julian Rother committed
298
	if groupedby not in ['title', 'semester', 'organizer']:
Andreas Valder's avatar
Andreas Valder committed
299
		groupedby = 'semester'
300
	return render_template('courses.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
301

302
303
@app.route('/course/<handle>')
@app.route('/course/<int:id>')
304
@handle_errors('courses', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
305
306
def course(id=None, handle=None):
	if id:
307
		course = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', id, ismod())[0]
308
	else:
309
		course = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', handle, ismod())[0]
310
311
	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'])
312
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible) ORDER BY time, duration DESC', course['id'], ismod())
313
	for lecture in lectures:
314
		lecture['perm'] = []
315
		lecture['perm'] += course['perm']
316
		lecture['course'] = course
317
318
319
		for perm in perms:
			if perm['lecture_id'] == lecture['id']:
				lecture['perm'].append(perm)
Andreas Valder's avatar
Andreas Valder committed
320
	videos = query('''
321
			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
322
323
324
325
326
327
			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
328
329
			''', course['id'], ismod())
	return render_template('course.html', course=course, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
330

Andreas Valder's avatar
Andreas Valder committed
331
@app.route('/faq')
332
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
333
def faq():
334
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
335

336
@app.route('/play/<int:id>')
Andreas Valder's avatar
Andreas Valder committed
337
@app.route('/embed/<int:id>', endpoint='embed')
338
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
339
def lecture(id):
Andreas Valder's avatar
Andreas Valder committed
340
341
342
343
344
	lecture = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())[0]
	videos = query('''
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description, formats.player_prio, formats.prio
			FROM videos
			JOIN formats ON (videos.video_format = formats.id)
345
346
347
348
			JOIN courses ON (courses.id = ?)
			WHERE videos.lecture_id = ? AND (? OR videos.visible)
			ORDER BY formats.prio DESC
			''', lecture['course_id'], lecture['id'], ismod())
349
	perms = query('SELECT perm.* FROM perm WHERE ((NOT perm.deleted) AND (perm.lecture_id = ? OR perm.course_id = ?))',
350
			lecture['id'], lecture['course_id'])
351
352
	if not videos:
		flash('Zu dieser Vorlesung wurden noch keine Videos veröffentlicht!')
353
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', lecture['course_id'], ismod())
354
355
	if not courses:
		return render_endpoint('courses', 'Diese Veranstaltung existiert nicht!'), 404
356
	chapters = query('SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (? OR visible) ORDER BY time ASC', id, ismod())
357
358
	if not checkperm(perms):
		mode, text = permdescr(perms)
359
360
361
362
363
364
		if mode == 'rwth':
			flash(text+'. <a target="_blank" href="'+url_for('start_rwthauth')+'">Hier authorisieren</a>.')
		elif mode == 'l2p':
			flash(text+'. <a target="_blank" href="'+url_for('start_l2pauth')+'">Hier authorisieren</a>.')
		else:
			flash(text+'.')
365
	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
366

367
368
369
370
371
372
373

@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'],
374
			'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
375
376
377
378
379
	#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',
380
381
			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
382
			'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
383
384
385
386
387
	for lecture in lectures:
		lecture['course'] = {}
		for key in lecture:
			if key.startswith('courses_'):
				lecture['course'][key[8:]] = lecture[key]
388
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
389

390
391
392
def check_mod(user, groups):
	return user and 'users' in groups

393
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
394
def login():
395
396
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
397
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
398
	if not check_mod(user, groups):
399
		flash('Login fehlgeschlagen!')
400
401
402
403
		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
404
		modify('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
405
406
		dbuser = query('SELECT * FROM users WHERE name = ?', user)
	session['user']['dbid'] = dbuser[0]['id']
407
	session['_csrf_token'] = ''.join(random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(128))
Julian Rother's avatar
Julian Rother committed
408
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
409

Julian Rother's avatar
Julian Rother committed
410
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
411
412
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
413
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
414

415
# name: (tablename, idcolumn, [editable_fields], [fields_to_set_at_creation_time])
416
417
418
tabs = {
	'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
			'handle', 'organizer', 'subject', 'semester', 'downloadable',
419
			'internal', 'responsible','deleted','description'],
420
			['created_by', 'time_created', 'time_updated']),
421
	'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
422
423
424
425
426
427
428
429
430
431
			'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']),
	'featured': ('featured', 'id', ['title', 'text', 'internal', 'visible', 'deleted'],
432
			['created_by', 'time_created', 'time_updated']),
433
	'perm': ('perm', 'id', ['type', 'param1', 'param2', 'deleted'],
434
435
436
			['course_id', 'lecture_id', 'video_id', 'created_by', 'time_created', 'time_updated']),
	'sorterrorlog': ('sorterrorlog_data', 'id', ['deleted'],
			['time_created', 'time_updated'])
437
438
}

439
@app.route('/edit', methods=['GET', 'POST'])
440
@mod_required
441
@csrf_protect
442
def edit(prefix='', ignore=[]):
443
	# All editable tables are expected to have a 'time_updated' field
444
	ignore.append('ref')
445
	ignore.append('prefix')
446
	ignore.append('_csrf_token')
447
448
	if not prefix and 'prefix' in request.args:
		prefix = request.args['prefix']
Julian Rother's avatar
Julian Rother committed
449
	modify('BEGIN')
450
	changes = request.values.items()
451
	if (request.method == 'POST') and (request.get_json()):
Julian Rother's avatar
Julian Rother committed
452
453
		changes = request.get_json().items()
	for key, val in changes:
454
455
456
		if key in ignore:
			continue
		key = prefix+key
457
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
458
459
		assert table in tabs
		assert column in tabs[table][2]
460
461
		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]),
				table, id, tabs[table][1], column, val, id, datetime.now(), session['user']['dbid'])
Julian Rother's avatar
Julian Rother committed
462
463
		modify('UPDATE %s SET %s = ?, time_updated = ? WHERE %s = ?'%(tabs[table][0], column, tabs[table][1]), val, datetime.now(), id)
	modify('COMMIT')
464
465
	if 'ref' in request.values:
		return redirect(request.values['ref'])
466
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
467

468
@app.route('/new/<table>', methods=['GET', 'POST'])
469
@mod_required
470
@csrf_protect
471
472
def create(table):
	assert table in tabs
473
474
475
476
477
478
479
	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
480
	args = request.values.items()
481
	if (request.method == 'POST') and (request.get_json()):
482
		args = request.get_json().items()
Andreas Valder's avatar
Andreas Valder committed
483
	print(args)
484
	for column, val in args:
Andreas Valder's avatar
Andreas Valder committed
485
		print(column,val)
486
		if (column == 'ref') or (column == '_csrf_token'):
487
			continue
488
489
		assert column in tabs[table][2]+tabs[table][3]
		assert column not in defaults
490
491
492
493
		columns.append(column)
		values.append(val)
	id = modify('INSERT INTO %s (%s) VALUES (%s)'%(tabs[table][0],
				','.join(columns), ','.join(['?']*len(values))), *values)
494
495
496
497
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return str(id), 200

498
499
500
501
502
@app.route('/auth')
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'])
503
	ip = request.headers.get('X-Real-IP', '')
504
	if url.endswith('jpg'):
505
		return "OK", 200
506
	perms = query('''SELECT videos.path, videos.id AS vid, perm.*
507
508
509
      FROM videos
      JOIN lectures ON (videos.lecture_id = lectures.id)
      JOIN courses ON (lectures.course_id = courses.id)
510
			LEFT JOIN perm ON (videos.id = perm.video_id OR lectures.id = perm.lecture_id OR courses.id = perm.course_id)
511
512
      WHERE videos.path = ?
      AND (? OR (courses.visible AND lectures.visible AND videos.visible))
513
			ORDER BY perm.video_id DESC, perm.lecture_id DESC, perm.course_id DESC''',
514
			url, ismod())
515

516
	if not perms:
517
		return "Not allowed", 403
518
	auth = request.authorization
519
520
521
522
	username = password = None
	if auth:
		username = auth.username
		password = auth.password
523
	if checkperm(perms, username=username, password=password):
524
		return 'OK', 200
525
		modify('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.now(), perms[0]['vid'], url)
526
	password_auth = False
527
528
	for perm in perms:
		if perm['type'] == 'password':
529
530
531
			password_auth = True
			break
	if password_auth:
532
533
		return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'})
	return "Not allowed", 403
Andreas Valder's avatar
Andreas Valder committed
534

Andreas Valder's avatar
Andreas Valder committed
535
@app.route('/stats')
Andreas Valder's avatar
Andreas Valder committed
536
@register_navbar('Statistiken', icon='stats')
Andreas Valder's avatar
Andreas Valder committed
537
538
539
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
540

Andreas Valder's avatar
Andreas Valder committed
541
@app.route('/changelog')
Andreas Valder's avatar
Andreas Valder committed
542
@register_navbar('Changelog', icon='book')
Andreas Valder's avatar
Andreas Valder committed
543
@mod_required
544
def changelog():
545
546
547
	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']])
548
	return render_template('changelog.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
549

Julian Rother's avatar
Julian Rother committed
550
551
552
553
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

554
555
556
557
558
@app.route('/newchapter/<int:lectureid>', methods=['POST', 'GET'])
def suggest_chapter(lectureid):
	time = request.values['time']
	text = request.values['text']
	assert(time and text)
559
560
561
562
563
564
565
	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')
		
566
567
568
	submitter = None
	if not ismod():
		submitter = request.environ['REMOTE_ADDR']
Julian Rother's avatar
Julian Rother committed
569
	id = modify('INSERT INTO chapters (lecture_id, time, text, time_created, time_updated, created_by, submitted_by) VALUES (?, ?, ?, ?, ?, ?, ?)',
570
571
572
573
574
				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

575
576
577
578
579
580
581
582
583
584
@app.route('/chapters/<int:lectureid>')
def chapters(lectureid):
	chapters = query("SELECT * FROM chapters WHERE lecture_id = ? and visible ORDER BY time DESC", lectureid)
	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
585
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
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']):
			pages.append([url_for('lecture',id=j['id'])])


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

Julian Rother's avatar
Julian Rother committed
601
import feeds
602
import importer
Andreas Valder's avatar
Andreas Valder committed
603
import sorter
604
605
if 'ICAL_URL' in config:
	import meetings
606
import l2pauth
Andreas Valder's avatar
Andreas Valder committed
607
608
if 'JOBS_API_KEY' in config:
	import jobs
Andreas Valder's avatar
Andreas Valder committed
609
import timetable