stats.py 6.76 KB
Newer Older
1 2
from server import *
import json
3
from hashlib import md5
4
from datetime import datetime
5

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 11 12 13 14
def stats():
	semester = query('SELECT DISTINCT semester from courses WHERE semester != ""');
	for s in semester:
		year = int(s['semester'][0:4])
		if s['semester'].endswith('ss'):
Andreas Valder's avatar
Andreas Valder committed
15 16
			s['from'] = datetime(year, 4, 1)
			s['to'] = datetime(year, 10, 1)
17
		if s['semester'].endswith('ws'):
Andreas Valder's avatar
Andreas Valder committed
18 19 20
			s['from'] = datetime(year, 10, 1)
			s['to'] = datetime(year+1, 4, 1)
	return render_template('stats.html', semester=semester, filter=request.args.get('filter'))
21 22

statsqueries = {}
23 24 25 26 27 28
statsqueries['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"
statsqueries['course_count'] = 'SELECT semester AS x, count(id) AS y FROM courses WHERE semester != "" GROUP BY semester'
statsqueries['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'
statsqueries['categories_courses'] = "SELECT courses.subject AS labels, count(courses.id) AS `values` FROM courses GROUP BY courses.subject ORDER BY labels DESC LIMIT 100"
statsqueries['organizer_courses'] = "SELECT courses.organizer AS labels, count(courses.id) AS `values` FROM courses GROUP BY courses.organizer ORDER BY labels DESC LIMIT 100"
statsqueries['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"
Julian Rother's avatar
Julian Rother committed
29
statsqueries['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"
30
statsqueries['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"
Andreas Valder's avatar
Andreas Valder committed
31
statsqueries['lecture_totalviews'] = "SELECT 42"
32 33 34

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

36 37
@app.route('/internal/stats/generic/<req>')
@app.route('/internal/stats/generic/<req>/<param>')
38
@mod_required
39 40 41 42
def stats_generic(req, param=None):
	if req not in statsqueries:
		return 404, 'Not found'
	rows = query(statsqueries[req], *(statsqueries[req].count('?')*[param]))
43 44 45 46 47
	if req == 'live_views':
		res = {'x': [], 'y': []}
	else:
		res = {}
	lastx = 0
48
	for row in rows:
49
		for key, val in row.items():
Andreas Valder's avatar
Andreas Valder committed
50 51 52 53 54
			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)
55
				lastx = int(val)
56 57 58
			if key not in res:
				res[key] = []
			res[key].append(val)
59
	return Response(json.dumps([res], default=plotly_date_handler), mimetype='application/json')
60

61 62
@app.route('/internal/stats/viewsperday/<req>')
@app.route('/internal/stats/viewsperday/<req>/<param>')
Julian Rother's avatar
Julian Rother committed
63
@mod_required
64
def stats_viewsperday(req, param=""):
65 66
	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'
67
	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 = ?'
68
	queries = {
Andreas Valder's avatar
Andreas Valder committed
69 70 71 72 73 74 75 76 77 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
		'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'''
115
	}
116
	expr = queries[req].replace('%T', '"'+query(date_subexpr%('viewsperday.'+req), param)[0]['t']+'"')
117 118
	params = [param]*expr.count('?')
	try:
119
		modify(update_expr%('viewsperday.'+req, expr), param, *(params+[datetime.combine(date.today(), time())]))
120
		modify('COMMIT')
121 122
	except Exception:
		traceback.print_exc()
123
	expr = queries[req].replace('%T', '"'+str(date.today())+'"')
124 125
	rows = query(query_expr%('viewsperday.'+req, expr), param, *params)
	start = None
126
	traces = set()
127
	data = {}
128 129 130 131 132 133 134 135
	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()
136
	res = [{'name': trace, 'x': [], 'y': []} for trace in traces]
137 138 139 140 141 142

	filter = request.args.get('filter')
	if filter:
		filter = filter.split('-')
		start = date.fromtimestamp(int(filter[0]))
		end = date.fromtimestamp(int(filter[1]))
143
	while start and start <= end:
144 145 146
		for trace in res:
			trace['x'].append(start)
			trace['y'].append(data.get(start, {}).get(trace['name'], 0))
147
		start += timedelta(days=1)
148
	return Response(json.dumps(res, default=plotly_date_handler), mimetype='application/json')
149