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
app = Flask(__name__)
10

Andreas Valder's avatar
Andreas Valder committed
11
12
13
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True

14
15
16
17
18
19
20
21
22
23
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()

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

Julian Rother's avatar
Julian Rother committed
40
from db import query, searchquery, ldapauth, ldapget, convert_timestamp
Julian Rother's avatar
Julian Rother committed
41

42
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
43

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

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

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

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

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

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

Julian Rother's avatar
Julian Rother committed
93
94
95
96
97
98
99
100
101
102
103
104
@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')

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

119
@app.route('/course')
120
@register_navbar('Videos', icon='film')
Andreas Valder's avatar
Andreas Valder committed
121
def course():
122
123
124
125
	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
126
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Cleanup    
Julian Rother committed
127
	if groupedby not in ['title', 'semester', 'organizer']:
Andreas Valder's avatar
Andreas Valder committed
128
		groupedby = 'semester'
129
	return render_template('course.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
130

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

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

156
@app.route('/play/<int:id>')
157
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
158
def play(id):
159
160
161
162
163
164
	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:
165
		return render_endpoint('course', 'Diese Veranstaltung existiert nicht!'), 404
166
	return render_template('play.html', course=courses[0], lecture=lectures[0], videos=videos)
167
168
169
170
171
172
173

@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'],
174
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
175
176
177
	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)',
178
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
179
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
180

181
182
183
def check_mod(user, groups):
	return user and 'users' in groups

184
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
185
def login():
186
187
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
188
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
189
	if not check_mod(user, groups):
190
		flash('Login fehlgeschlagen!')
191
192
193
194
195
196
197
		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
198
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
199

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

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

231
232
233
234
235
@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'])
236
	ip = request.headers.get('X-Real-IP', '')
237
	if url.endswith('jpg'):
238
		return "OK", 200
239
	videos = query('''SELECT videos.path, videos.id, lectures.id AS lecture_id, courses.id AS course_id, auth.*
240
241
242
      FROM videos
      JOIN lectures ON (videos.lecture_id = lectures.id)
      JOIN courses ON (lectures.course_id = courses.id)
243
			LEFT JOIN auth ON (videos.id = auth.video_id OR lectures.id = auth.lecture_id OR courses.id = auth.course_id)
244
245
      WHERE videos.path = ?
      AND (? OR (courses.visible AND lectures.visible AND videos.visible))
246
			ORDER BY auth.video_id DESC, auth.lecture_id DESC, auth.course_id DESC''',
247
248
			url, ismod())
	if not videos:
249
		return "Not allowed", 403
250
251
252
253
	allowed = False
	types = []
	auth = request.authorization
	for video in videos:
254
255
		if videos[0] and ((videos[0]['video_id'] and not video['video_id']) \
				or (videos[0]['lecture_id'] and not video['lecture_id'])):
256
257
258
259
260
261
262
263
264
			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
265
266
	if not types[0] or allowed or ismod() or \
			(auth and check_mod(*ldapauth(auth.username, auth.password))):
267
268
269
270
271
		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
272
273

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

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

@app.route('/log')
@register_navbar('Changelog', 'book')
@mod_required
def log():
339
	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')
340
	return render_template('log.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
341

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

Andreas Valder's avatar
Andreas Valder committed
346
347
	if source != "campus":
		return "Unknown source", 404
Andreas Valder's avatar
Andreas Valder committed
348

349
350
351
	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
352
353
	campus={}
	for i in request.values:
354
		group, importid, field = i.split('.', 2)
Andreas Valder's avatar
Andreas Valder committed
355
		if group == 'campus':
356
357
358
359
360
361
362
363
364
365
366
367
368
369
			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
370
	events = []
371
372
373
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
	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]
412
				rowdata['dates'] = [row.xpath("text()[1]")[0][4:14]]
413
414
415
				rowdata['start'] = row.xpath("text()[1]")[0][17:22]
				rowdata['end'] = row.xpath("text()[1]")[0][27:32]
				events_raw.append(rowdata)
416
417
418
419
420
421
422
423
424

			#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
425
					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
426
427
					events.append(e)
			# it is pared.
Andreas Valder's avatar
Andreas Valder committed
428
429


430
431
432
433

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

Andreas Valder's avatar
Andreas Valder committed
434
435
436
437
438
439
440
441
442
	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
443
444
445
446
447
448

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

import feeds