cutprogress.py

from server import *

@register_navbar('Schnittfortschritt User', icon='spinner', iconlib='fa', userendpoint=True, endpoint='cutprogress_user')
@register_navbar('Schnittfortschritt', icon='spinner', iconlib='fa')
@app.route('/internal/cutprogress', endpoint="cutprogress")
@app.route('/internal/user/<int:user>/cutprogress', endpoint='cutprogress_user')
@mod_required
def cutprogress(user=None):
	allsemester = query('SELECT DISTINCT semester from courses ORDER BY semester DESC')
	semester = request.values.get('semester', allsemester[0]['semester'])
	courses = query('''
		SELECT courses.id, courses.handle, courses.short
		FROM courses
		WHERE semester = ?
		ORDER by id DESC
		''', semester)
	# Fetch list of people responsible for every course
	for course in courses:
		people = query('''
			SELECT users.*
			FROM users
			JOIN responsible ON responsible.user_id = users.id
			WHERE responsible.course_id = ?
			ORDER BY users.realname ASC
			''', course['id'])
		if not people:
			people = [{ 'realname': 'Niemand', 'id': -1 }]
		course['responsible'] = people
	if user is not None:
		courses = [
			c for c in courses
			if user in (r['id'] for r in c['responsible'])
		]
	# Fetch lectures for all courses
	lectures = []
	for c in courses:
		lectures += query('''
			SELECT
				lectures.id,
				lectures.course_id,
				lectures.time,
				lectures.title,
				COUNT(videos.id) as videos_total,
				COUNT(videos.visible) as videos_visible
			FROM lectures
			JOIN courses ON courses.id = lectures.course_id
			LEFT JOIN videos ON lectures.id = videos.lecture_id
			WHERE courses.id = ?
				AND lectures.time <= ?
				AND NOT lectures.norecording
			GROUP BY lectures.id
			ORDER BY lectures.time ASC, lectures.id ASC
			''', c['id'], datetime.now())
	# Generate list of days, figure out when weeks change
	dates = sorted({row['time'].date() for row in lectures}, reverse=True)
	is_new_weeks = [
		False if (i == 0) else thisdate.isocalendar()[1] != dates[i-1].isocalendar()[1]
		for i, thisdate in enumerate(dates)
	]
	# Sort into cells
	tablebody = [
		{
			'date': date, # row header
			'is_new_week': is_new_week,
			'cells': [ # this is the body of the row
				[ # this list is a cell
					lecture
					for lecture in lectures
					if lecture['course_id'] == course['id'] and lecture['time'].date() == date
				]
				for course in courses
			]
		}
		for date, is_new_week in zip(dates, is_new_weeks)
	]
	return render_template('cutprogress.html',
		# dropdown selection
		allsemester=allsemester, # options
		semester=semester, # choice
		user=query('SELECT * FROM users WHERE id = ?', user)[0] if user else None,
		# content
		courses=courses,
		tablebody=tablebody
	)