stats.py

from server import *
import json
from jobs import date_json_handler
from hashlib import md5
from datetime import datetime

@app.route('/internal/stats')
@app.route('/internal/stats/<semester>')
@register_navbar('Statistiken', icon='stats')
@mod_required
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'):
			s['from'] = datetime(year,4,1)
			s['to'] =   datetime(year,10,1)
		if s['semester'].endswith('ws'):
			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'))

statsqueries = {}
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"
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"
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"
statsqueries['lecture_totalviews'] = "SELECT 42"

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

@app.route('/internal/stats/generic/<req>')
@app.route('/internal/stats/generic/<req>/<param>')
@mod_required
def stats_generic(req, param=None):
	if req not in statsqueries:
		return 404, 'Not found'
	rows = query(statsqueries[req], *(statsqueries[req].count('?')*[param]))
	if req == 'live_views':
		res = {'x': [], 'y': []}
	else:
		res = {}
	lastx = 0
	for row in rows:
		for key, val in row.items():
			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)
				lastx = int(val)
			if key not in res:
				res[key] = []
			res[key].append(val)
	return Response(json.dumps([res], default=plotly_date_handler),  mimetype='application/json')

@app.route('/internal/stats/viewsperday/<req>')
@app.route('/internal/stats/viewsperday/<req>/<param>')
@mod_required
def stats_viewsperday(req, param=""):
	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'
	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 = ?'
	queries = {
		'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'''
	}
	expr = queries[req].replace('%T', '"'+query(date_subexpr%('viewsperday.'+req), param)[0]['t']+'"')
	params = [param]*expr.count('?')
	try:
		modify(update_expr%('viewsperday.'+req, expr), param, *(params+[datetime.combine(date.today(), time())]))
		modify('COMMIT')
	except Exception:
		traceback.print_exc()
	expr = queries[req].replace('%T', '"'+str(date.today())+'"')
	rows = query(query_expr%('viewsperday.'+req, expr), param, *params)
	start = None
	traces = set()
	data = {}
	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()
	res = [{'name': trace, 'x': [], 'y': []} for trace in traces]

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