server.py 9.75 KB
Newer Older
Julian Rother's avatar
Julian Rother committed
1
#!/bin/python
2
from flask import Flask, g, request, url_for, redirect, session, render_template, flash
3
from functools import wraps
4
from datetime import date, timedelta, datetime, time
5
import os
6

7
app = Flask(__name__)
8

9
config = app.config
10
11
config['DB_SCHEMA'] = 'db_schema.sql'
config['DB_DATA'] = 'db_example.sql'
12
13
14
config['DB_ENGINE'] = 'sqlite'
config['SQLITE_DB'] = 'db.sqlite'
config['SQLITE_INIT_SCHEMA'] = True
15
config['SQLITE_INIT_DATA'] = False
16
config['DEBUG'] = False
17
config['VIDEOPREFIX'] = 'https://videoag.fsmpi.rwth-aachen.de'
18
19
20
if __name__ == '__main__':
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
21
config.from_pyfile('config.py', silent=True)
Julian Rother's avatar
Julian Rother committed
22

23
from db import query, searchquery, ldapauth, ldapget
Julian Rother's avatar
Julian Rother committed
24

25
26
app.jinja_env.globals['videoprefix'] = config['VIDEOPREFIX']
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
27

28
29
30
31
32
33
def ismod(*args):
	return ('user' in session)

app.jinja_env.globals['ismod'] = ismod

def mod_required(func):
34
	mod_endpoints.append(func.__name__)
35
36
	@wraps(func)
	def decorator(*args, **kwargs):
37
		if not ismod():
38
39
40
41
42
43
			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

44
app.jinja_env.globals['navbar'] = []
45
def register_navbar(name, icon=None):
46
	def wrapper(func):
47
48
49
		endpoint = func.__name__
		app.jinja_env.globals['navbar'].append((endpoint, name, icon,
					not endpoint in mod_endpoints))
50
51
52
		return func
	return wrapper

53
@app.route('/')
54
@register_navbar('Home', icon='home')
55
def index():
56
	return render_template('index.html', latestvideos=query('''
57
				SELECT lectures.*, max(videos.time_updated) AS lastvidtime, courses.short, courses.downloadable, courses.title AS coursetitle
58
59
60
				FROM lectures
				LEFT JOIN videos ON (videos.lecture_id = lectures.id)
				LEFT JOIN courses on (courses.id = lectures.course_id)
61
				WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
62
63
				GROUP BY videos.lecture_id
				ORDER BY lastvidtime DESC
Andreas Valder's avatar
.    
Andreas Valder committed
64
				LIMIT 6
65
			''', ismod()))
66

67
@app.route('/course')
68
@register_navbar('Videos', icon='film')
Andreas Valder's avatar
Andreas Valder committed
69
def course():
70
71
72
73
	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
74
75
76
	groupedby = request.args.get('groupedby')
	if groupedby not in ['title','semester','organizer']:
		groupedby = 'semester'
77
	return render_template('course.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
78

Andreas Valder's avatar
Andreas Valder committed
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
@app.route('/course/<id>')
def course_id(id):
	courses = query('SELECT * FROM courses WHERE ((handle = ?) or id = ?) AND (? OR visible)', id, id, ismod())
	if not courses:
		flash('Diese Veranstaltung existiert nicht!')
		return app.view_functions['videos'](), 404
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible)', courses[0]['id'], ismod())
	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
			''', courses[0]['id'], ismod())
	return render_template('course_id.html', course=courses[0], lectures=lectures, videos=videos)

Andreas Valder's avatar
Andreas Valder committed
97
@app.route('/faq')
98
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
99
def faq():
100
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
101

102
103
@app.route('/play/<int:id>')
def play(id):
104
105
106
107
108
109
110
111
112
113
114
115
	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 lectures:
		flash('Diese Vorlesung existiert nicht!')
		return app.view_functions['videos'](), 404
	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:
		flash('Diese Veranstaltung existiert nicht!')
		return app.view_functions['videos'](), 404
	return render_template('play.html', course=courses[0], lecture=lectures[0], videos=videos)
116
117
118
119
120
121
122

@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'],
123
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
124
125
126
	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)',
127
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
128
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
129

130
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
131
def login():
132
133
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
134
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
Julian Rother's avatar
Julian Rother committed
135
	if user and 'users' in groups:
Julian Rother's avatar
Julian Rother committed
136
		session['user'] = ldapget(user)
137
138
	else:
		flash('Login fehlgeschlagen!')
Julian Rother's avatar
Julian Rother committed
139
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
140

Julian Rother's avatar
Julian Rother committed
141
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
142
143
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
144
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
145

146
@app.route('/edit', methods=['GET', 'POST'])
147
@mod_required
Julian Rother's avatar
Julian Rother committed
148
149
150
def edit():
	tabs = {
		'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
Andreas Valder's avatar
Andreas Valder committed
151
				'handle', 'organizer', 'subject', 'semester', 'downloadable',
152
				'internal', 'responsible','deleted']),
Julian Rother's avatar
Julian Rother committed
153
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
154
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted']),
155
		'site_texts': ('site_texts', 'key', ['value']),
156
		'videos': ('videos_data', 'id', ['visible','deleted'])
Julian Rother's avatar
Julian Rother committed
157
	}
158
	query('BEGIN')
159
	if request.is_json:
Julian Rother's avatar
Julian Rother committed
160
161
162
163
		changes = request.get_json().items()
	else:
		changes = request.args.items()
	for key, val in changes:
164
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
165
166
		assert table in tabs
		assert column in tabs[table][2]
167
		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'])
Julian Rother's avatar
Julian Rother committed
168
169
		query('UPDATE %s SET %s = ? WHERE %s = ?'%(tabs[table][0], column,
					tabs[table][1]), val, id)
170

171
	query('COMMIT')
172
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
173

174

175
176
177
178
179
@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'])
180
181
	ip = request.headers.get('X-Real-IP', '')
	videos = query('''SELECT videos.path, videos.id
182
183
184
185
186
187
			FROM videos
			JOIN lectures ON (videos.lecture_id = lectures.id)
			JOIN courses ON (lectures.course_id = courses.id)
			WHERE videos.path = ?
			AND (? OR (courses.visible AND lectures.visible AND videos.visible))''',
			url, ismod())
188
	if videos and (url.startswith('pub') or ismod()):
189
		query('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.now(), videos[0]['id'], url)
190
		return "OK", 200
191
	elif url.endswith('jpg'):
192
193
194
		return "OK", 200
	else:
		return "Not allowed", 403
Julian Rother's avatar
Julian Rother committed
195

Andreas Valder's avatar
Andreas Valder committed
196
197

@app.route('/schedule')
198
199
@register_navbar('Drehplan', 'calendar')
@mod_required
Andreas Valder's avatar
Andreas Valder committed
200
def schedule():
201
202
203
204
205
	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
206
207
208
	days = [{'date': start, 'lectures': [], 'atonce':0, 'index': 0 }]
	earlieststart=time(23,59)
	latestend=time(0,0)
209
	for i in range(1,7):
Andreas Valder's avatar
Andreas Valder committed
210
		days.append({'date': days[i-1]['date'] + timedelta(days=1), 'atonce':0, 'index': i, 'lectures':[] })
211
212
	for i in days:
		# date and times are burning in sqlite
213
214
		s = datetime.combine(i['date'],time())
		e = datetime.combine(i['date'],time(23,59))
215
216
217
218
219
220
221
		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);
222
223
224
225
226
		# sweepline to find out how many lectures overlap
		maxcol=0;
		curcol=0;
		freecol=[];
		for l in i['lectures']:
227
			l['time_asdate'] = datetime.strptime(l['time'],'%Y-%m-%d %H:%M:%S')
228
229
			# who the hell inserts lectures with zero length?!?!?
			l['end_asdate'] = l['time_asdate']+timedelta(minutes=max(l['duration'],1))
230
231
232
233
234
235
236
237
		for l in sorted([(l['time_asdate'],True,l) for l in i['lectures']] + [(l['end_asdate'],False,l) for l in i['lectures']],key=lambda t:(t[0],t[1])):
			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
238
239
				if earlieststart > l[0].time():
					earlieststart = l[0].time()
240
241
242
			else:
				curcol -= 1
				freecol.append(l[2]['schedule_col'])
Andreas Valder's avatar
Andreas Valder committed
243
244
				if latestend < l[0].time():
					latestend = l[0].time()
245
246
		i['maxcol'] = max(maxcol,1)
	times=[]
Andreas Valder's avatar
Andreas Valder committed
247
	s = min(earlieststart,time(8,0))
248
249
	e = max(latestend,time(19,0))
	for i in range(s.hour*4,min(int((60*e.hour/15)/4)*4+5,24*4)):
250
251
252
		t = i*15
		times.append(time(int(t/60),t%60))
	
253
	return render_template('schedule.html',days=days,times=times,kw=kw)
Andreas Valder's avatar
Andreas Valder committed
254
255
256
257
258
259

@app.route('/stats')
@register_navbar('Statistiken', 'stats')
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
260
261
262
263
264

@app.route('/log')
@register_navbar('Changelog', 'book')
@mod_required
def log():
265
	return render_template('log.html', changelog=query('SELECT *, ( "table" || "." || id_value || "." ||field) as path FROM changelog ORDER BY "when" DESC LIMIT 50'))