server.py 9.56 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',
Julian Rother's avatar
Julian Rother committed
152
153
				'internal', 'responsible']),
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
Andreas Valder's avatar
Andreas Valder committed
154
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist']),
155
		'site_texts': ('site_texts', 'key', ['value']),
Andreas Valder's avatar
Andreas Valder committed
156
		'videos': ('videos_data', 'id', ['visible'])
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
@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'])
179
180
	ip = request.headers.get('X-Real-IP', '')
	videos = query('''SELECT videos.path, videos.id
181
182
183
184
185
186
			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())
187
	if videos and (url.startswith('pub') or ismod()):
188
		query('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.now(), videos[0]['id'], url)
189
		return "OK", 200
190
	elif url.endswith('jpg'):
191
192
193
		return "OK", 200
	else:
		return "Not allowed", 403
Julian Rother's avatar
Julian Rother committed
194

Andreas Valder's avatar
Andreas Valder committed
195
196

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

@app.route('/stats')
@register_navbar('Statistiken', 'stats')
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
254
255
256
257
258

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