stats.py 6.86 KB
Newer Older
1
import json
2
from datetime import datetime
3

4 5
from server import *

6
@app.route('/internal/stats')
Andreas Valder's avatar
Andreas Valder committed
7
@app.route('/internal/stats/<semester>')
8 9
@register_navbar('Statistiken', icon='stats')
@mod_required
10
def stats():
11 12 13 14 15 16 17 18 19
	semester = query('SELECT DISTINCT semester from courses WHERE semester != ""')
	for i in semester:
		year = int(i['semester'][0:4])
		if i['semester'].endswith('ss'):
			i['from'] = datetime(year, 4, 1)
			i['to'] = datetime(year, 10, 1)
		if i['semester'].endswith('ws'):
			i['from'] = datetime(year, 10, 1)
			i['to'] = datetime(year+1, 4, 1)
Andreas Valder's avatar
Andreas Valder committed
20
	return render_template('stats.html', semester=semester, filter=request.args.get('filter'))
21

22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
STATS_QUERIES = {}
STATS_QUERIES['formats_views'] = "SELECT formats.description AS labels, count(DISTINCT log.id) AS `values` FROM log \
		JOIN videos ON (videos.id = log.video) JOIN formats ON (formats.id = videos.video_format) GROUP BY formats.id"
STATS_QUERIES['course_count'] = 'SELECT semester AS x, count(id) AS y FROM courses WHERE semester != "" GROUP BY semester'
STATS_QUERIES['lectures_count'] = 'SELECT semester AS x, count(lectures.id) AS y FROM lectures \
		JOIN courses ON (courses.id = lectures.course_id) WHERE semester != "" GROUP BY semester'
STATS_QUERIES['categories_courses'] = "SELECT courses.subject AS labels, count(courses.id) AS `values` FROM courses \
		GROUP BY courses.subject ORDER BY labels DESC LIMIT 100"
STATS_QUERIES['organizer_courses'] = "SELECT courses.organizer AS labels, count(courses.id) AS `values` FROM courses \
		GROUP BY courses.organizer ORDER BY labels DESC LIMIT 100"
STATS_QUERIES['categories_lectures'] = "SELECT courses.subject AS labels, count(lectures.id) AS `values` FROM lectures \
		JOIN courses ON (courses.id = lectures.course_id) WHERE lectures.visible GROUP BY courses.subject ORDER BY `values` DESC LIMIT 100"
STATS_QUERIES['lecture_views'] = "SELECT lectures.time AS x, count(DISTINCT log.id) AS y FROM log \
		JOIN videos ON (videos.id = log.video) \
		JOIN lectures ON (lectures.id = videos.lecture_id) \
		WHERE (lectures.course_id = ?) GROUP BY lectures.id ORDER BY lectures.time"
STATS_QUERIES['live_views'] = "SELECT hlslog.segment AS x, COUNT(DISTINCT hlslog.id) AS y FROM hlslog WHERE hlslog.lecture = ? \
		GROUP BY hlslog.segment ORDER BY hlslog.segment"
STATS_QUERIES['lecture_totalviews'] = "SELECT 42"
41 42 43

def plotly_date_handler(obj):
	return obj.strftime("%Y-%m-%d %H:%M:%S")
44

45 46
@app.route('/internal/stats/generic/<req>')
@app.route('/internal/stats/generic/<req>/<param>')
47
@mod_required
48
def stats_generic(req, param=None):
49
	if req not in STATS_QUERIES:
50
		return 404, 'Not found'
51
	rows = query(STATS_QUERIES[req], *(STATS_QUERIES[req].count('?')*[param]))
52 53 54 55 56
	if req == 'live_views':
		res = {'x': [], 'y': []}
	else:
		res = {}
	lastx = 0
57
	for row in rows:
58
		for key, val in row.items():
Andreas Valder's avatar
Andreas Valder committed
59 60 61 62 63
			if req == 'live_views' and key == 'x':
				if lastx != int(val)-1:
					for i in range(lastx, int(val)-1):
						res['x'].append(i)
						res['y'].append(0)
64
				lastx = int(val)
65 66 67
			if key not in res:
				res[key] = []
			res[key].append(val)
68
	return Response(json.dumps([res], default=plotly_date_handler), mimetype='application/json')
69

70 71
@app.route('/internal/stats/viewsperday/<req>')
@app.route('/internal/stats/viewsperday/<req>/<param>')
Julian Rother's avatar
Julian Rother committed
72
@mod_required
73
def stats_viewsperday(req, param=""): #pylint: disable=too-many-locals
74 75
	update_expr = 'INSERT INTO logcache (req, param, trace, date, value) SELECT "%s", ?, trace, date, y FROM (%s) AS cachetmp WHERE date < ?'
	query_expr = 'SELECT date, trace, value AS y FROM logcache WHERE req = "%s" AND param = ? UNION SELECT * FROM (%s) AS cachetmp'
76
	date_subexpr = 'SELECT CASE WHEN MAX(date) IS NULL THEN "2000-00-00" ELSE MAX(date) END AS t FROM `logcache` WHERE req = "%s" AND param = ?'
Julian Rother's avatar
Julian Rother committed
77
	queries = {
Andreas Valder's avatar
Andreas Valder committed
78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
		'lecture': # views per day per lecture (split per format)
			'''SELECT log.date AS date, formats.description AS trace, COUNT(DISTINCT log.id) AS y
			FROM log
			JOIN videos ON videos.id = log.video
			JOIN formats ON formats.id = videos.video_format
			WHERE log.date > %T AND videos.lecture_id = ?
			GROUP BY log.date, videos.video_format
			UNION
				SELECT log.date AS date, "total" AS trace, COUNT(DISTINCT log.id) AS y
				FROM log JOIN videos ON videos.id = log.video
				WHERE log.date > %T AND videos.lecture_id = ? GROUP BY log.date''',

		'course': # views per day per format for a single course
			'''SELECT log.date AS date, formats.description AS trace, COUNT(DISTINCT log.id) AS y
			FROM log JOIN videos ON videos.id = log.video
			JOIN lectures ON lectures.id = videos.lecture_id
			JOIN formats ON formats.id = videos.video_format
			WHERE log.date > %T AND lectures.course_id = ?
			GROUP BY log.date, videos.video_format
			UNION
				SELECT log.date AS date, "total" AS trace, COUNT(DISTINCT log.id) AS y
				FROM log
				JOIN videos ON videos.id = log.video
				JOIN lectures ON lectures.id = videos.lecture_id
				WHERE log.date > %T AND lectures.course_id = ?
				GROUP BY log.date''',

		'global': # views per format per day (split per format)
			'''SELECT log.date AS date, formats.description AS trace, COUNT(DISTINCT log.id) AS y
			FROM log
			JOIN videos ON videos.id = log.video
			JOIN formats ON formats.id = videos.video_format
			WHERE log.date > %T GROUP BY log.date, videos.video_format
			UNION
				SELECT log.date AS date, "total" AS trace, COUNT(DISTINCT log.id) AS y
				FROM log
				WHERE log.date > %T
				GROUP BY log.date''',

		'courses': # views per course per day
			'''SELECT log.date AS date, courses.handle AS trace, COUNT(DISTINCT log.id) AS y
			FROM log JOIN videos ON videos.id = log.video
			JOIN lectures ON lectures.id = videos.lecture_id
			JOIN courses ON courses.id = lectures.course_id
			WHERE log.date > %T
			GROUP BY log.date, courses.id'''
Julian Rother's avatar
Julian Rother committed
124
	}
125
	expr = queries[req].replace('%T', '"'+query(date_subexpr%('viewsperday.'+req), param)[0]['t']+'"')
126 127
	params = [param]*expr.count('?')
	try:
128
		modify("BEGIN")
129
		modify(update_expr%('viewsperday.'+req, expr), param, *(params+[datetime.combine(date.today(), time())]))
130
		modify('COMMIT')
131
	except Exception: #pylint: disable=broad-except
132
		traceback.print_exc()
133
	expr = queries[req].replace('%T', '"'+str(date.today())+'"')
134 135
	rows = query(query_expr%('viewsperday.'+req, expr), param, *params)
	start = None
Julian Rother's avatar
Julian Rother committed
136
	traces = set()
137
	data = {}
Julian Rother's avatar
Julian Rother committed
138 139 140 141 142 143 144 145
	for row in rows:
		if not start or row['date'] < start:
			start = row['date']
		traces.add(row['trace'])
		if row['date'] not in data:
			data[row['date']] = {}
		data[row['date']][row['trace']] = row['y']
	end = date.today()
146
	res = [{'name': trace, 'x': [], 'y': []} for trace in traces]
147 148 149 150 151 152

	filter = request.args.get('filter')
	if filter:
		filter = filter.split('-')
		start = date.fromtimestamp(int(filter[0]))
		end = date.fromtimestamp(int(filter[1]))
153
	while start and start <= end:
154 155 156
		for trace in res:
			trace['x'].append(start)
			trace['y'].append(data.get(start, {}).get(trace['name'], 0))
157
		start += timedelta(days=1)
158
	return Response(json.dumps(res, default=plotly_date_handler), mimetype='application/json')