server.py 15.6 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
4
from datetime import date, timedelta, datetime, time
5
import threading
6
import os
7

8
app = Flask(__name__)
9

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

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

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

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

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

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

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

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

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

74
75
76
77
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:
				return render_endpoint(endpoint, text, **epargs), code
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
85
86
@app.errorhandler(404)
def handle_not_found(e):
87
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
88

89
@app.route('/')
90
@register_navbar('Home', icon='home')
91
def index():
92
	return render_template('index.html', latestvideos=query('''
93
				SELECT lectures.*, max(videos.time_updated) AS lastvidtime, courses.short, courses.downloadable, courses.title AS coursetitle
94
95
96
				FROM lectures
				LEFT JOIN videos ON (videos.lecture_id = lectures.id)
				LEFT JOIN courses on (courses.id = lectures.course_id)
97
				WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
98
99
				GROUP BY videos.lecture_id
				ORDER BY lastvidtime DESC
Andreas Valder's avatar
.    
Andreas Valder committed
100
				LIMIT 6
101
			''', ismod()))
102

103
@app.route('/course')
104
@register_navbar('Videos', icon='film')
Andreas Valder's avatar
Andreas Valder committed
105
def course():
106
107
108
109
	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
110
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Cleanup    
Julian Rother committed
111
	if groupedby not in ['title', 'semester', 'organizer']:
Andreas Valder's avatar
Andreas Valder committed
112
		groupedby = 'semester'
113
	return render_template('course.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
114

Andreas Valder's avatar
Andreas Valder committed
115
@app.route('/course/<id>')
116
@app.route('/course/<int:numid>')
117
118
@handle_errors('course', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
def course_id(numid=None, id=None):
119
	if numid:
Andreas Valder's avatar
Andreas Valder committed
120
		courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', numid, ismod())[0]
121
	else:
Andreas Valder's avatar
Andreas Valder committed
122
123
		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
124
125
126
127
128
129
130
131
	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
132
133
			''', courses['id'], ismod())
	return render_template('course_id.html', course=courses, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
134

Andreas Valder's avatar
Andreas Valder committed
135
@app.route('/faq')
136
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
137
def faq():
138
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
139

140
@app.route('/play/<int:id>')
141
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
142
def play(id):
143
144
145
146
147
148
	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:
149
		return render_endpoint('course', 'Diese Veranstaltung existiert nicht!'), 404
150
	return render_template('play.html', course=courses[0], lecture=lectures[0], videos=videos)
151
152
153
154
155
156
157

@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'],
158
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
159
160
161
	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)',
162
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
163
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
164

165
166
167
def check_mod(user, groups):
	return user and 'users' in groups

168
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
169
def login():
170
171
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
172
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
173
	if not check_mod(user, groups):
174
		flash('Login fehlgeschlagen!')
175
176
177
178
179
180
181
		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
182
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
183

Julian Rother's avatar
Julian Rother committed
184
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
185
186
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
187
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
188

189
@app.route('/edit', methods=['GET', 'POST'])
190
@mod_required
Julian Rother's avatar
Julian Rother committed
191
192
193
def edit():
	tabs = {
		'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
Andreas Valder's avatar
Andreas Valder committed
194
				'handle', 'organizer', 'subject', 'semester', 'downloadable',
195
				'internal', 'responsible','deleted']),
Julian Rother's avatar
Julian Rother committed
196
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
197
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted']),
198
		'site_texts': ('site_texts', 'key', ['value']),
199
		'videos': ('videos_data', 'id', ['visible','deleted'])
Julian Rother's avatar
Julian Rother committed
200
	}
201
	query('BEGIN')
202
	if request.is_json:
Julian Rother's avatar
Julian Rother committed
203
204
205
206
		changes = request.get_json().items()
	else:
		changes = request.args.items()
	for key, val in changes:
207
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
208
209
		assert table in tabs
		assert column in tabs[table][2]
210
		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'])
211
		query('UPDATE %s SET %s = ? WHERE %s = ?'%(tabs[table][0], column,tabs[table][1]), val, id)
212
	query('COMMIT')
213
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
214

215

216
217
218
219
220
@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'])
221
	ip = request.headers.get('X-Real-IP', '')
222
	if url.endswith('jpg'):
223
		return "OK", 200
224
	videos = query('''SELECT videos.path, videos.id, lectures.id AS lecture_id, courses.id AS course_id, auth.*
225
226
227
      FROM videos
      JOIN lectures ON (videos.lecture_id = lectures.id)
      JOIN courses ON (lectures.course_id = courses.id)
228
			LEFT JOIN auth ON (videos.id = auth.video_id OR lectures.id = auth.lecture_id OR courses.id = auth.course_id)
229
230
      WHERE videos.path = ?
      AND (? OR (courses.visible AND lectures.visible AND videos.visible))
231
			ORDER BY auth.video_id DESC, auth.lecture_id DESC, auth.course_id DESC''',
232
233
			url, ismod())
	if not videos:
234
		return "Not allowed", 403
235
236
237
238
	allowed = False
	types = []
	auth = request.authorization
	for video in videos:
239
240
		if videos[0] and ((videos[0]['video_id'] and not video['video_id']) \
				or (videos[0]['lecture_id'] and not video['lecture_id'])):
241
242
243
244
245
246
247
248
249
			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
250
251
	if not types[0] or allowed or ismod() or \
			(auth and check_mod(*ldapauth(auth.username, auth.password))):
252
253
254
255
256
		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
257
258

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

@app.route('/stats')
@register_navbar('Statistiken', 'stats')
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
319
320
321
322
323

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

327
328
@app.route('/import/<source>/<int:id>', methods=['GET', 'POST'])
#@handle_errors('course', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
Andreas Valder's avatar
Andreas Valder committed
329
@mod_required
330
331
332
333
334
def import_from(source=None, id=None):

	def recursive_dict(element):
		     return element.tag, dict(map(recursive_dict, element)) or element.text

Andreas Valder's avatar
Andreas Valder committed
335
336
	if source != "campus":
		return "Unknown source", 404
Andreas Valder's avatar
Andreas Valder committed
337

338
339
340
	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
341
342
	campus={}
	for i in request.values:
343
		group, importid, field = i.split('.', 2)
Andreas Valder's avatar
Andreas Valder committed
344
		if group == 'campus':
345
346
347
348
349
350
351
352
353
354
355
356
357
358
			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
359

360
361
362
363
364
365
366
367
368
369
370
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
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()
#			i['single']=html.tostring(singletable)
			for row in singletable.xpath("tr/td[2]"):
				rowdata = {}
				rowdata['place'] = row.xpath("text()[2]")[0][2:-1]
				rowdata['date'] = row.xpath("text()[1]")[0][4:14]
				rowdata['start'] = row.xpath("text()[1]")[0][17:22]
				rowdata['end'] = row.xpath("text()[1]")[0][27:32]
				events_raw.append(rowdata)
			events = []
			i['events'] = events
			i['events_raw'] = events_raw

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

	return render_template('import_campus.html', course=courses, lectures=lectures, import_campus=import_campus)