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')