Process user stats in Python instead of SQL

Since we ultimately want to include events from traces (hints, tests) in
statistics, SQL will be insufficient.

2 files changed, 46 insertions, 54 deletions

diff --git a/server/problems.py b/server/problems.py
index c63d923..a143e74 100644
--- a/server/problems.py
+++ b/server/problems.py
@@ -43,6 +43,9 @@ def load_module(fullname):
 def load_language(language, tail_module):
     return load_module('{0}.{1}'.format(language, tail_module))
 
+def load_group(language, problem_group, tail_module):
+    return load_module('{0}.problems.{1}.{2}'.format(language, problem_group, tail_module))
+
 def load_problem(language, problem_group, problem, tail_module):
     return load_module('{0}.problems.{1}.{2}.{3}'.format(language, problem_group, problem, tail_module))
 
diff --git a/server/user_session.py b/server/user_session.py
index 41c77ac..b5cdd61 100644
--- a/server/user_session.py
+++ b/server/user_session.py
@@ -1,18 +1,23 @@
 # coding=utf-8
-import traceback
 
-import uuid
-import threading  # multiprocessing.managers.BaseManager uses threading to serve incoming requests
-import hashlib
 import base64
+import datetime
+import hashlib
+from itertools import groupby
+import logging
+from operator import attrgetter
 import random
+import threading  # multiprocessing.managers.BaseManager uses threading to serve incoming requests
+import traceback
+import time
+import uuid
+
 import db
-import server
+from db.models import Problem, Solution
 from errors.session import NoSuchSession, AuthenticationFailed, PasswordChangeFailed, UserExists, SignupFailed, NotLoggedIn
+import server
+from server.problems import load_language, load_group
 import psycopg2.extras
-import datetime
-import logging
-import time
 
 __all__ = ['get_session_by_id', 'UserSession']
 
@@ -256,54 +261,38 @@ class UserSession(object):
             conn.commit()
             db.return_connection(conn)
 
-
     def get_stat(self):
         uid = self.get_uid()
-        conn = db.get_connection()
-        try:
-            cur = conn.cursor()
-            try:
-                cur.execute("""select * from(
-               (SELECT
-                  distinct
-                  l.identifier as language,
-                  pg.id as problem_group_id,
-                  pg.identifier as problem_group,
-                  count(distinct p.id) as all,
-                  count(CASE s.done WHEN true THEN true END) as done,
-                  count(CASE s.done WHEN false THEN true END) as in_progress
-                FROM problem p
-                  INNER JOIN language l ON p.language_id = l.id
-                  INNER JOIN problem_group pg ON p.problem_group_id = pg.id
-                  LEFT JOIN solution s ON s.problem_id = p.id AND s.codeq_user_id = %s
-                GROUP BY l.identifier, pg.id, pg.identifier)
-               UNION ALL
-               (SELECT
-                  distinct
-                  l.identifier as language,
-                  0 as problem_group_id,
-                  null as problem_group,
-                  count(distinct p.id) as all,
-                  count(CASE s.done WHEN true THEN true END) as done,
-                  count(CASE s.done WHEN false THEN true END) as in_progress
-                FROM problem p
-                  INNER JOIN language l ON p.language_id = l.id
-                  LEFT JOIN solution s ON s.problem_id = p.id AND s.codeq_user_id = %s
-                GROUP BY l.identifier)
-             ) as u
-            ORDER BY u.language, problem_group_id""", (uid, uid,))
-                #return cur.fetchall()
-                columns = ('language', 'problem_group_id', 'problem_group', 'problems_count', 'done', 'in_progress')
-                results = []
-                for row in cur.fetchall():
-                    results.append(dict(zip(columns, row)))
-                return results
-            finally:
-                cur.close()
-        finally:
-            conn.commit()
-            db.return_connection(conn)
-
+        gui_lang = self.get_settings().get('gui_lang', 'en')
+
+        solutions = Solution.filter(codeq_user_id=uid)
+        pids_attempted = {s.problem_id for s in solutions}
+        pids_done = {s.problem_id for s in solutions if s.done}
+
+        results = []
+        for language, groups in groupby(Problem.list(), attrgetter('language')):
+            group_results = []
+            l_name = load_language(language, gui_lang).name
+            for group, problems in groupby(groups, attrgetter('group')):
+                problems = list(problems)
+                g_mod = load_group(language, group, gui_lang)
+                g_name = g_mod.name if g_mod else group
+                group_results.append({
+                    'language': l_name,
+                    'problem_group': g_name,
+                    'problems_count': len(problems),
+                    'done': len([p for p in problems if p.id in pids_done]),
+                    'in_progress': len([p for p in problems if p.id in pids_attempted])
+                })
+            results.append({
+                'language': l_name,
+                'problem_group': None,
+                'problems_count': sum([r['problems_count'] for r in group_results]),
+                'done': sum([r['done'] for r in group_results]),
+                'in_progress': sum([r['in_progress'] for r in group_results])
+            })
+            results.extend(group_results)
+        return results
 
     def send(self, json_obj):
         """Sends a message to the user.