server.py 16.7 KB
Newer Older
1
from flask import Flask, g, request, url_for, redirect, session, render_template, flash, 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
Julian Rother's avatar
Julian Rother committed
7
import hashlib
8
9
10
import locale

locale.setlocale(locale.LC_ALL, 'de_DE')
11

12
app = Flask(__name__)
13

Andreas Valder's avatar
Andreas Valder committed
14
15
16
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True

17
18
19
20
21
22
23
24
25
26
def timer_func():
	with app.test_request_context():
		pass # do something
	timer = threading.Timer(60*60, timer_func)
	timer.start()

timer = threading.Timer(0, timer_func)
timer.daemon = True
timer.start()

27
config = app.config
28
29
config['DB_SCHEMA'] = 'db_schema.sql'
config['DB_DATA'] = 'db_example.sql'
30
31
32
config['DB_ENGINE'] = 'sqlite'
config['SQLITE_DB'] = 'db.sqlite'
config['SQLITE_INIT_SCHEMA'] = True
33
config['SQLITE_INIT_DATA'] = False
34
config['DEBUG'] = False
35
config['VIDEOPREFIX'] = 'https://videoag.fsmpi.rwth-aachen.de'
36
37
38
if __name__ == '__main__':
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
39
config.from_pyfile('config.py', silent=True)
Andreas Valder's avatar
Andreas Valder committed
40
41
if config['DEBUG']:
	app.jinja_env.auto_reload = True
Julian Rother's avatar
Julian Rother committed
42

Julian Rother's avatar
Julian Rother committed
43
from db import query, searchquery, ldapauth, ldapget, convert_timestamp
Julian Rother's avatar
Julian Rother committed
44

45
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
46

Julian Rother's avatar
Cleanup    
Julian Rother committed
47
@app.template_global()
48
49
50
51
def ismod(*args):
	return ('user' in session)

def mod_required(func):
52
	mod_endpoints.append(func.__name__)
53
54
	@wraps(func)
	def decorator(*args, **kwargs):
55
		if not ismod():
56
57
58
59
60
61
			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

62
app.jinja_env.globals['navbar'] = []
63
def register_navbar(name, icon=None):
64
	def wrapper(func):
65
66
67
		endpoint = func.__name__
		app.jinja_env.globals['navbar'].append((endpoint, name, icon,
					not endpoint in mod_endpoints))
68
69
70
		return func
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
71
72
73
74
def render_endpoint(endpoint, flashtext=None, **kargs):
	if flashtext:
		flash(flashtext)
	# request.endpoint is used for navbar highlighting
75
	request.url_rule = Rule(request.path, endpoint=endpoint)
Julian Rother's avatar
Cleanup    
Julian Rother committed
76
77
	return app.view_functions[endpoint](**kargs)

78
79
80
81
82
83
84
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
85
86
87
88
				if endpoint:
					return render_endpoint(endpoint, text, **epargs), code
				else:
					return text, code
89
90
91
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
92
93
@app.errorhandler(404)
def handle_not_found(e):
94
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
95

Julian Rother's avatar
Julian Rother committed
96
97
98
99
100
101
102
103
104
105
106
107
@app.template_filter(name='semester')
def human_semester(s):
	return s

@app.template_filter(name='date')
def human_date(d):
	return d.strftime('%x')

@app.template_filter()
def rfc3339(d):
	return d.strftime('%Y-%m-%dT%H:%M:%S+02:00')

108
@app.route('/')
109
@register_navbar('Home', icon='home')
110
def index():
111
	return render_template('index.html', latestvideos=query('''
112
				SELECT lectures.*, max(videos.time_updated) AS lastvidtime, courses.short, courses.downloadable, courses.title AS coursetitle
113
114
115
				FROM lectures
				LEFT JOIN videos ON (videos.lecture_id = lectures.id)
				LEFT JOIN courses on (courses.id = lectures.course_id)
116
				WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
117
118
				GROUP BY videos.lecture_id
				ORDER BY lastvidtime DESC
Andreas Valder's avatar
.    
Andreas Valder committed
119
				LIMIT 6
120
			''', ismod()))
121

122
@app.route('/course')
123
@register_navbar('Videos', icon='film')
Andreas Valder's avatar
Andreas Valder committed
124
def course():
125
126
127
128
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed))', ismod())
	for course in courses:
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
129
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Cleanup    
Julian Rother committed
130
	if groupedby not in ['title', 'semester', 'organizer']:
Andreas Valder's avatar
Andreas Valder committed
131
		groupedby = 'semester'
132
	return render_template('course.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
133

Andreas Valder's avatar
Andreas Valder committed
134
@app.route('/course/<id>')
135
@app.route('/course/<int:numid>')
136
137
@handle_errors('course', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
def course_id(numid=None, id=None):
138
	if numid:
Andreas Valder's avatar
Andreas Valder committed
139
		courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', numid, ismod())[0]
140
	else:
Andreas Valder's avatar
Andreas Valder committed
141
142
		courses = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', id, ismod())[0]
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible)', courses['id'], ismod())
Andreas Valder's avatar
Andreas Valder committed
143
144
145
146
147
148
149
150
	videos = query('''
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description
			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
Andreas Valder's avatar
Andreas Valder committed
151
152
			''', courses['id'], ismod())
	return render_template('course_id.html', course=courses, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
153

Andreas Valder's avatar
Andreas Valder committed
154
@app.route('/faq')
155
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
156
def faq():
157
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
158

159
@app.route('/play/<int:id>')
160
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
161
def play(id):
162
163
164
165
166
167
	lectures = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())
	videos = query('SELECT * FROM videos WHERE lecture_id = ? AND (? OR visible)', id, ismod())
	if not videos:
		flash('Zu dieser Vorlesung wurden noch keine Videos veröffentlicht!')
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR (visible AND listed))', lectures[0]['course_id'], ismod())
	if not courses:
168
		return render_endpoint('course', 'Diese Veranstaltung existiert nicht!'), 404
169
	return render_template('play.html', course=courses[0], lecture=lectures[0], videos=videos)
170
171
172
173
174
175
176

@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'],
177
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
178
179
180
	lectures = searchquery(q, 'lectures.*, courses.visible AS coursevisible, courses.listed, courses.short, courses.downloadable, courses.title AS coursetitle',
			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
181
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
182
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
183

184
185
186
def check_mod(user, groups):
	return user and 'users' in groups

187
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
188
def login():
189
190
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
191
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
192
	if not check_mod(user, groups):
193
		flash('Login fehlgeschlagen!')
194
195
196
197
198
199
200
		return render_template('login.html')
	session['user'] = ldapget(user)
	dbuser = query('SELECT * FROM users WHERE name = ?', user)
	if not dbuser:
		query('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
		dbuser = query('SELECT * FROM users WHERE name = ?', user)
	session['user']['dbid'] = dbuser[0]['id']
Julian Rother's avatar
Julian Rother committed
201
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
202

Julian Rother's avatar
Julian Rother committed
203
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
204
205
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
206
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
207

208
@app.route('/edit', methods=['GET', 'POST'])
209
@mod_required
Julian Rother's avatar
Julian Rother committed
210
211
212
def edit():
	tabs = {
		'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
Andreas Valder's avatar
Andreas Valder committed
213
				'handle', 'organizer', 'subject', 'semester', 'downloadable',
214
				'internal', 'responsible','deleted']),
Julian Rother's avatar
Julian Rother committed
215
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
216
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted']),
217
		'site_texts': ('site_texts', 'key', ['value']),
218
		'videos': ('videos_data', 'id', ['visible','deleted'])
Julian Rother's avatar
Julian Rother committed
219
	}
220
	query('BEGIN')
221
	if request.is_json:
Julian Rother's avatar
Julian Rother committed
222
223
224
225
		changes = request.get_json().items()
	else:
		changes = request.args.items()
	for key, val in changes:
226
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
227
228
		assert table in tabs
		assert column in tabs[table][2]
229
		query('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']['givenName'])
230
		query('UPDATE %s SET %s = ? WHERE %s = ?'%(tabs[table][0], column,tabs[table][1]), val, id)
231
	query('COMMIT')
232
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
233

234
235
236
237
238
@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'])
239
	ip = request.headers.get('X-Real-IP', '')
240
	if url.endswith('jpg'):
241
		return "OK", 200
242
	videos = query('''SELECT videos.path, videos.id, lectures.id AS lecture_id, courses.id AS course_id, auth.*
243
244
245
      FROM videos
      JOIN lectures ON (videos.lecture_id = lectures.id)
      JOIN courses ON (lectures.course_id = courses.id)
246
			LEFT JOIN auth ON (videos.id = auth.video_id OR lectures.id = auth.lecture_id OR courses.id = auth.course_id)
247
248
      WHERE videos.path = ?
      AND (? OR (courses.visible AND lectures.visible AND videos.visible))
249
			ORDER BY auth.video_id DESC, auth.lecture_id DESC, auth.course_id DESC''',
250
251
			url, ismod())
	if not videos:
252
		return "Not allowed", 403
253
254
255
256
	allowed = False
	types = []
	auth = request.authorization
	for video in videos:
257
258
		if videos[0] and ((videos[0]['video_id'] and not video['video_id']) \
				or (videos[0]['lecture_id'] and not video['lecture_id'])):
259
260
261
262
263
264
265
266
267
			break
		types.append(video['auth_type'])
		if video['auth_type'] == 'public':
			allowed = True
			break
		elif video['auth_type'] == 'password':
			if auth and video['auth_user'] == auth.username and video['auth_passwd'] == auth.password:
				allowed = True
				break
268
269
	if not types[0] or allowed or ismod() or \
			(auth and check_mod(*ldapauth(auth.username, auth.password))):
270
271
272
273
274
		return 'OK', 200
		query('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.now(), videos[0]['id'], url)
	elif 'password' in types:
		return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'})
	return "Not allowed", 403
Andreas Valder's avatar
Andreas Valder committed
275
276

@app.route('/schedule')
277
278
@register_navbar('Drehplan', 'calendar')
@mod_required
Andreas Valder's avatar
Andreas Valder committed
279
def schedule():
280
281
282
283
284
	if 'kw' not in request.args:
		kw=0
	else:
		kw=int(request.args['kw'])
	start = date.today() - timedelta(days=date.today().weekday() -7*kw)
Andreas Valder's avatar
Andreas Valder committed
285
286
287
	days = [{'date': start, 'lectures': [], 'atonce':0, 'index': 0 }]
	earlieststart=time(23,59)
	latestend=time(0,0)
288
	for i in range(1,7):
Andreas Valder's avatar
Andreas Valder committed
289
		days.append({'date': days[i-1]['date'] + timedelta(days=1), 'atonce':0, 'index': i, 'lectures':[] })
290
291
	for i in days:
		# date and times are burning in sqlite
292
293
		s = datetime.combine(i['date'],time())
		e = datetime.combine(i['date'],time(23,59))
294
295
296
297
298
299
300
		i['lectures'] = query ('''
					SELECT lectures.*,courses.short
					FROM lectures 
					JOIN courses ON (lectures.course_id = courses.id) 
					WHERE (time < ?) AND (time > ?) 
					ORDER BY time ASC'''
				,e,s);
301
302
303
304
305
		# sweepline to find out how many lectures overlap
		maxcol=0;
		curcol=0;
		freecol=[];
		for l in i['lectures']:
306
			# who the hell inserts lectures with zero length?!?!?
307
			l['time_end'] = l['time']+timedelta(minutes=max(l['duration'],1))
308
		for l in sorted([(l['time'],True,l) for l in i['lectures']] + [(l['time_end'],False,l) for l in i['lectures']],key=lambda t:(t[0],t[1])):
309
310
311
312
313
314
315
			if l[1]:
				curcol += 1
				if curcol > maxcol:
					maxcol = curcol
				if len(freecol) == 0:
					freecol.append(maxcol)
				l[2]['schedule_col'] = freecol.pop()
Andreas Valder's avatar
Andreas Valder committed
316
317
				if earlieststart > l[0].time():
					earlieststart = l[0].time()
318
319
320
			else:
				curcol -= 1
				freecol.append(l[2]['schedule_col'])
Andreas Valder's avatar
Andreas Valder committed
321
322
				if latestend < l[0].time():
					latestend = l[0].time()
323
324
		i['maxcol'] = max(maxcol,1)
	times=[]
Andreas Valder's avatar
Andreas Valder committed
325
	s = min(earlieststart,time(8,0))
326
327
	e = max(latestend,time(19,0))
	for i in range(s.hour*4,min(int((60*e.hour/15)/4)*4+5,24*4)):
328
329
		t = i*15
		times.append(time(int(t/60),t%60))
330
	return render_template('schedule.html',days=days,times=times,kw=kw)
Andreas Valder's avatar
Andreas Valder committed
331
332
333
334
335
336

@app.route('/stats')
@register_navbar('Statistiken', 'stats')
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
337
338
339
340
341

@app.route('/log')
@register_navbar('Changelog', 'book')
@mod_required
def log():
342
	changelog = query('SELECT *, ( "table" || "." || id_value || "." ||field) as path FROM changelog LEFT JOIN users ON (changelog.who = users.id) ORDER BY "when" DESC LIMIT 50')
343
	return render_template('log.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
344

345
@app.route('/import/<source>/<int:id>', methods=['GET', 'POST'])
Andreas Valder's avatar
Andreas Valder committed
346
@mod_required
347
348
def import_from(source=None, id=None):

Andreas Valder's avatar
Andreas Valder committed
349
350
	if source != "campus":
		return "Unknown source", 404
Andreas Valder's avatar
Andreas Valder committed
351

352
353
354
	courses = query('SELECT * FROM courses WHERE id = ?', id)[0]
	lectures = query('SELECT * FROM lectures WHERE course_id = ?', courses['id'])
	
Andreas Valder's avatar
Andreas Valder committed
355
356
	campus={}
	for i in request.values:
357
		group, importid, field = i.split('.', 2)
Andreas Valder's avatar
Andreas Valder committed
358
		if group == 'campus':
359
360
361
362
363
364
365
366
367
368
369
370
371
372
			if not importid in  campus:
				campus[importid] = {}
			campus[importid][field] = request.values[i]
	for i in campus:
		if i.startswith('new'):
			if campus[i]['url'] != '':
				query('INSERT INTO import_campus (url, type, course_id, last_checked, changed) VALUES (?, ?, ?, ?, 1)',campus[i]['url'],campus[i]['type'],id,datetime.now())
		else:
			if campus[i]['url'] != '':
				query('UPDATE import_campus SET url = ?, `type` = ? WHERE (course_id = ?) AND (id = ?)', campus[i]['url'],campus[i]['type'],id,int(i))	
			else:
				query('DELETE FROM import_campus WHERE (id = ?) AND (course_id = ?)',int(i),id)
	
	import_campus = query('SELECT * FROM import_campus WHERE course_id = ?',id)
Andreas Valder's avatar
Andreas Valder committed
373
	events = []
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
	try:
		from lxml import html
		from lxml import etree
		import urllib.request
		# if u have to port this to anything new, god be with you.
		for i in import_campus:
			remote_html = urllib.request.urlopen(i['url']).read()
			tablexpath = "//td[text()='Termine und Ort']/following::table[1]"
			basetable = html.fromstring(remote_html).xpath(tablexpath)[0]
			parsebase = html.tostring(basetable);

			#parse recurring events
			toparse = [i['url']]
			for j in basetable.xpath("//table[@cellpadding='5']//tr[@class='hierarchy4' and td[@name='togglePeriodApp']]"):
				url = str(j.xpath("td[@name='togglePeriodApp']/a/@href")[0])
				toparse.append(url)
			events_raw = []
			for j in toparse:
				if j.startswith('event'):
					url = 'https://www.campus.rwth-aachen.de/rwth/all/'+j
				else:
					url = j
				text = urllib.request.urlopen(url).read()
				dom = html.fromstring(text).xpath(tablexpath)[0]
				#we get the "heading" row, from it extract the room and time. best way to get it is to match on the picture -.-
				baserow = dom.xpath("//table[@cellpadding='5']//tr[@class='hierarchy4' and td[@name='togglePeriodApp']/*/img[@src='../../server/img/minus.gif']]")
				if not baserow:
					continue
				baserow = baserow[0]
				rowdata = {'dates': []}
				rowdata['place'] = baserow.xpath("td[6]/text()")[0][2:-1]
				rowdata['start'] = baserow.xpath("td[3]/text()")[0]
				rowdata['end'] = baserow.xpath("td[5]/text()")[0]
				rowdata['dates'] = baserow.getparent().xpath("tr[@class='hierarchy5']//td[@colspan='3']/text()")
				events_raw.append(rowdata)

			# parse single appointments
			singletable = basetable.xpath("//table[@cellpadding='3']/tr/td[text()='Einmalige Termine:']")[0].getparent().getparent()
			for row in singletable.xpath("tr/td[2]"):
				rowdata = {}
				rowdata['place'] = row.xpath("text()[2]")[0][2:-1]
415
				rowdata['dates'] = [row.xpath("text()[1]")[0][4:14]]
416
417
418
				rowdata['start'] = row.xpath("text()[1]")[0][17:22]
				rowdata['end'] = row.xpath("text()[1]")[0][27:32]
				events_raw.append(rowdata)
419
420
421
422
423
424
425
426
427

			#now we have to filter our data and do some lookups
			for j in events_raw:
				for k in j['dates']:
					e = {}
					fmt= "%d.%m.%Y %H:%M"
					e['time'] = datetime.strptime("%s %s"%(k,j['start']) ,fmt)
					e['duration'] = int((datetime.strptime("%s %s"%(k,j['end']) ,fmt) - e['time']).seconds/60)
					e['place'] = query("SELECT name FROM places WHERE (campus_name = ?) OR ((NOT campus_name) AND name = ?)",j['place'],j['place'])[0]['name'];
Andreas Valder's avatar
Andreas Valder committed
428
					e['exists'] = len(query("SELECT id from lectures WHERE (time = ?) and (duration = ?) and (place = ?) and (course_id = ?)",e['time'],e['duration'],e['place'],id)) > 0
429
430
					events.append(e)
			# it is pared.
Andreas Valder's avatar
Andreas Valder committed
431
432


433
434
435
436

	except ImportError:
		flash('python-lxml not found, campus import will not work.')

Andreas Valder's avatar
Andreas Valder committed
437
438
439
440
441
442
443
444
445
	uniqevents = []
	for i in events:
		seen = False
		for j in events:
			seen = (i['place'] == j['place']) and (i['time'] == j['time']) and (i['duration'] == j['duration'])
		if not seen:
			uniqevents.append(i)

	return render_template('import_campus.html', course=courses, lectures=lectures, import_campus=import_campus, events=uniqevents)
Julian Rother's avatar
Julian Rother committed
446
447
448
449
450
451

@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

import feeds