server.py 9.04 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
167
168
		assert table in tabs
		assert column in tabs[table][2]
		query('UPDATE %s SET %s = ? WHERE %s = ?'%(tabs[table][0], column,
					tabs[table][1]), val, id)
169
	query('COMMIT')
170
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
171

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

Andreas Valder's avatar
Andreas Valder committed
193
194

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

@app.route('/stats')
@register_navbar('Statistiken', 'stats')
@mod_required
def stats():
	return render_template('stats.html')