server.py 8.85 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 as dt, 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
21
if __name__ == '__main__':
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
22

23
config.from_pyfile('config.py', silent=True)
Julian Rother's avatar
Julian Rother committed
24

25
from db import query, searchquery, ldapauth, ldapget
Julian Rother's avatar
Julian Rother committed
26

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

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

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

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

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

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

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

@app.route('/faq')
82
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
83
def faq():
84
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
85

Andreas Valder's avatar
Andreas Valder committed
86
87
@app.route('/play')
def play():
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
	if not 'lectureid' in request.args:
		return redirect(url_for('videos'))
	id = request.args.get('lectureid')
	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)
103
104
105
106
107
108
109

@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'],
110
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
111
112
113
	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)',
114
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
115
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
116

Andreas Valder's avatar
Andreas Valder committed
117
118
@app.route('/course')
def course():
119
120
121
122
123
124
125
126
127
	if not 'courseid' in request.args:
		return redirect(url_for('videos'))
	id = request.args['courseid']
	courses = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', 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('''
Andreas Valder's avatar
Andreas Valder committed
128
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description
129
130
131
			FROM videos
			JOIN lectures ON (videos.lecture_id = lectures.id)
			JOIN formats ON (videos.video_format = formats.id)
Andreas Valder's avatar
Andreas Valder committed
132
			JOIN courses ON (lectures.course_id = courses.id)
133
			WHERE lectures.course_id= ? AND (? OR videos.visible)
134
			ORDER BY formats.prio DESC
135
			''', courses[0]['id'], ismod())
136
	return render_template('course.html', course=courses[0], lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
137

138
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
139
def login():
140
141
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
142
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
Julian Rother's avatar
Julian Rother committed
143
	if user and 'users' in groups:
Julian Rother's avatar
Julian Rother committed
144
		session['user'] = ldapget(user)
145
146
	else:
		flash('Login fehlgeschlagen!')
Julian Rother's avatar
Julian Rother committed
147
148
149
150
151
152
153
154
155
156
157
158
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	else:
		return redirect(url_for('index'))

@app.route('/logout')
def logout():
	session.pop('user')
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	else:
		return redirect(url_for('index'))
Julian Rother's avatar
Julian Rother committed
159

160
@app.route('/edit', methods=['GET', 'POST'])
161
@mod_required
Julian Rother's avatar
Julian Rother committed
162
163
164
def edit():
	tabs = {
		'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
Andreas Valder's avatar
Andreas Valder committed
165
				'handle', 'organizer', 'subject', 'semester', 'downloadable',
Julian Rother's avatar
Julian Rother committed
166
167
				'internal', 'responsible']),
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
Andreas Valder's avatar
Andreas Valder committed
168
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist']),
169
		'site_texts': ('site_texts', 'key', ['value']),
Andreas Valder's avatar
Andreas Valder committed
170
		'videos': ('videos_data', 'id', ['visible'])
Julian Rother's avatar
Julian Rother committed
171
172
	}
	query('BEGIN TRANSACTION')
173
	if request.is_json:
Julian Rother's avatar
Julian Rother committed
174
175
176
177
		changes = request.get_json().items()
	else:
		changes = request.args.items()
	for key, val in changes:
178
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
179
180
181
182
183
		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)
	query('COMMIT TRANSACTION')
184
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
185

186
187
188
189
190
@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'])
191
192
	ip = request.headers.get('X-Real-IP', '')
	videos = query('''SELECT videos.path, videos.id
193
194
195
196
197
198
			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())
199
200
	if videos and (url.startswith('pub') or ismod()):
		query('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.datetime.now(), videos[0]['id'], url)
201
		return "OK", 200
202
	elif url.endswith('jpg'):
203
204
205
		return "OK", 200
	else:
		return "Not allowed", 403
Julian Rother's avatar
Julian Rother committed
206

Andreas Valder's avatar
Andreas Valder committed
207
208

@app.route('/schedule')
209
210
@register_navbar('Drehplan', 'calendar')
@mod_required
Andreas Valder's avatar
Andreas Valder committed
211
def schedule():
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
	start = date.today() - timedelta(days=date.today().weekday()+7*20)
	days = [{'date': start, 'lectures': [], 'atonce':0 }]
	for i in range(1,7):
		days.append({'date': days[i-1]['date'] + timedelta(days=1), 'lectures':[], 'atonce':0 })
	for i in days:
		# date and times are burning in sqlite
		s = dt.combine(i['date'],time())
		e = dt.combine(i['date'],time(23,59))
		i['lectures'] = query ('SELECT lectures.*,courses.* FROM lectures JOIN courses ON (lectures.course_id = courses.id) WHERE (time < ?) AND (time > ?) ORDER BY time ASC',e,s);
		# sweepline to find out how many lectures overlap
		maxcol=0;
		curcol=0;
		freecol=[];
		for l in i['lectures']:
			l['time_asdate'] = dt.strptime(l['time'],'%Y-%m-%d %H:%M:%S')
			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()
			else:
				curcol -= 1
				freecol.append(l[2]['schedule_col'])
		i['maxcol'] = max(maxcol,1)
	times=[]
	for i in range(0,int(60*24/15)):
		t = i*15
		times.append(time(int(t/60),t%60))
	
	return render_template('schedule.html',days=days,times=times)