get basic user statistics

2 files changed, 83 insertions, 25 deletions

diff --git a/server/handlers.py b/server/handlers.py
index 0b3dd46..5439d19 100644
--- a/server/handlers.py
+++ b/server/handlers.py
@@ -276,6 +276,13 @@ class EndProblem(CodeqService):
         request.end()
 
 
+
+class GetUserStat(CodeqService):
+    def process(self, request):
+        stat = request.session.get_stat()
+        request.reply({'code': 0, 'stat': stat})
+
+
 # maps actions to their handlers
 incoming_handlers = {
     'create_session': CreateSession(),
@@ -293,7 +300,8 @@ incoming_handlers = {
     'update_settings': UpdateSettings(),
     'test': Test(),
     'load_problem': LoadProblem(),
-    'end_problem': EndProblem()
+    'end_problem': EndProblem(),
+    'user_stat': GetUserStat()
 }
 
 
diff --git a/server/user_session.py b/server/user_session.py
index 6a994f3..b678cea 100644
--- a/server/user_session.py
+++ b/server/user_session.py
@@ -210,51 +210,101 @@ class UserSession(object):
     def update_solution(self, problem_id, trace, solution):
         if (trace is None) and (solution is None):
             return
+        with self._access_lock:
+            uid = self.uid
+            conn = db.get_connection()
+            try:
+                cur = conn.cursor()
+                try:
+                    # TODO: convert to upsert with postgresql 9.5 to eliminate the small window where it's possible for more than one concurrent insert to execute
+                    cur.execute('select id, trace, content from solution where codeq_user_id = %s and problem_id = %s for update', (uid, problem_id))
+                    row = cur.fetchone()
+                    if row:
+                        if row[1]:
+                            new_trace = row[1]
+                            if trace:
+                                new_trace.extend(trace)
+                        else:
+                            new_trace = trace
+                        new_solution = row[2] if solution is None else solution
+                        cur.execute('update solution set content = %s, trace = %s where id = %s', (new_solution, psycopg2.extras.Json(new_trace), row[0]))
+                    else:
+                        # this is the first entry
+                        cur.execute('insert into solution (done, content, problem_id, codeq_user_id, trace) values (%s, %s, %s, %s, %s)', (False, solution, problem_id, uid, psycopg2.extras.Json(trace)))
+                finally:
+                    cur.close()
+                conn.commit()
+            except:
+                conn.rollback()
+                raise
+            finally:
+                db.return_connection(conn)
+
+    def change_password(self, password):
         uid = self.get_uid()
         conn = db.get_connection()
         try:
             cur = conn.cursor()
             try:
-                # TODO: convert to upsert with postgresql 9.5 to eliminate the small window where it's possible for more than one concurrent insert to execute
-                cur.execute('select id, trace, content from solution where codeq_user_id = %s and problem_id = %s for update', (uid, problem_id))
-                row = cur.fetchone()
-                if row:
-                    if row[1]:
-                        new_trace = row[1]
-                        if trace:
-                            new_trace.extend(trace)
-                    else:
-                        new_trace = trace
-                    new_solution = row[2] if solution is None else solution
-                    cur.execute('update solution set content = %s, trace = %s where id = %s', (new_solution, psycopg2.extras.Json(new_trace), row[0]))
-                else:
-                    # this is the first entry
-                    cur.execute('insert into solution (done, content, problem_id, codeq_user_id, trace) values (%s, %s, %s, %s, %s)', (False, solution, problem_id, uid, psycopg2.extras.Json(trace)))
+                cur.execute('update codeq_user set password = %s  where id = %s', (encrypt_password(password), uid))
+                affected = cur.rowcount
+                if affected is None:
+                    raise PasswordChangeFailed('Password change failed')
             finally:
                 cur.close()
-            conn.commit()
-        except:
-            conn.rollback()
-            raise
         finally:
+            conn.commit()
             db.return_connection(conn)
 
-    def change_password(self, password):
+
+    def get_stat(self):
         uid = self.get_uid()
         conn = db.get_connection()
         try:
             cur = conn.cursor()
             try:
-                cur.execute('update codeq_user set password = %s  where id = %s', (encrypt_password(password), uid))
-                affected = cur.rowcount
-                if affected is None:
-                    raise PasswordChangeFailed('Password change failed')
+                cur.execute("""select * from(
+               (SELECT
+                  distinct
+                  l.name as language,
+                  pg.id as problem_group_id,
+                  pg.name 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.name, pg.id, pg.name)
+               UNION ALL
+               (SELECT
+                  distinct
+                  l.name 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.name)
+             ) 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)
 
+
     def send(self, json_obj):
         """Sends a message to the user.