From b538d8236cd9cf92c36df47ddf244096896f5068 Mon Sep 17 00:00:00 2001
From: Emma Koistila <ekoistil@local>
Date: Sun, 3 Dec 2023 14:11:21 +0200
Subject: Moving comparison calculations to SQL query.

---
 db/analyse.py     | 20 +++++++++++++++++++-
 routes/analyse.py | 34 ++++++++++++----------------------
 routes/answer.py  |  4 ++--
 3 files changed, 33 insertions(+), 25 deletions(-)

diff --git a/db/analyse.py b/db/analyse.py
index a4076f5..612f637 100644
--- a/db/analyse.py
+++ b/db/analyse.py
@@ -4,7 +4,7 @@ class DBAnalyse:
     def __init__(self, db):
         self.db = db
         
-    def comparable(self, quiz_id,user1,user2):
+    def pagedata(self, quiz_id,user1,user2):
         sql = "SELECT q.question, q.neg_answer, q.pos_answer, \
                 a1.answer, a2.answer, \
                 100 - ABS( a1.answer - a2.answer ) / 10 \
@@ -20,3 +20,21 @@ class DBAnalyse:
                 "user1":user1,
                 "user2":user2
             } ).fetchall()
+            
+    def compare(self, quiz_id,user1,user2):
+        sql = "SELECT \
+                AVG (100 - ABS( a1.answer - a2.answer ) / 10 ) \
+                FROM questionaires quiz \
+                JOIN questions q ON q.id = ANY(quiz.questionset) \
+                JOIN answers a1 ON a1.user_id = (:user1) \
+                JOIN answers a2 ON a2.user_id = (:user2) \
+                WHERE a1.question_id = q.id \
+                AND a2.question_id = q.id \
+                AND quiz.id = (:quiz_id);"
+        return self.db.session.execute( text(sql), {
+                "quiz_id":quiz_id,
+                "user1":user1,
+                "user2":user2
+            } ).scalar()
+        
+        
diff --git a/routes/analyse.py b/routes/analyse.py
index 152a189..279b02e 100644
--- a/routes/analyse.py
+++ b/routes/analyse.py
@@ -4,25 +4,14 @@ from flask import render_template,session,request,redirect
 from routes.tools import rows2dicts, get_alert, get_nick, csrf_check
 
 def find_best_and_worst(aid, uid):
-    answers=D.quiz.answers(aid)
-    alist=rows2dicts( answers, ['q','u','a'] )
-    questions = set(x['q'] for x in alist)
-    users = set(x['u'] for x in alist)
-    data = {}
-    for q in questions:
-        data[q]={}
-    for i in alist:
-        data[i['q']][i['u']]=i['a']
     match = {}
+    users = [ x[0] for x in D.quiz.users(aid) ]
     comb = list(combinations(users,2))
     if len(comb)<1:
         comb=[(uid,uid)]
     min, minme, max, maxme = 101, 101, -1, -1
     for pair in comb:
-        sum=0
-        for q in questions:
-            sum += 1000 - abs(data[q][pair[0]]-data[q][pair[1]])
-        match[pair]=int(sum / len(questions) / 10 + 0.5)
+        match[pair] = int(D.analyse.compare(aid,pair[0],pair[1]))
         if match[pair] < min:
             min = match[pair]
             min_pair = pair
@@ -69,26 +58,27 @@ def analyse():
 
     uid2 = session["anal_user2"] if "anal_user2" in session else sid
     uid2 = sid if uid2 != sid and not D.quiz.user(aid,uid2) else uid2
+    
+    best = find_best_and_worst(aid, sid)
+    
+    if uid1 == uid2:
+        uid1 = best['maxme_u1']
+        uid2 = best['maxme_u2']
 
 
-    comparable = D.analyse.comparable( aid, uid1, uid2 )
-    avg=0
-    for i in range(len(comparable)):
-        avg += comparable[i][5]
-    avg//=len(comparable)
-    
     return render_template(
             "analyse.html",
             caller="analyse",
             alert=get_alert(),
             nick=get_nick(),
             code=D.quiz.get_link(aid),
-            questions = rows2dicts( comparable, ['q','n','p','a1','a2','c'] ),
+            questions = rows2dicts( D.analyse.pagedata( aid, uid1, uid2 ),
+                    ['q','n','p','a1','a2','c'] ),
             users = rows2dicts( D.quiz.users(aid), ['id','nick'] ),
                 user1=int(uid1),
                 user2=int(uid2),
-                avg = avg,
-                best = find_best_and_worst(aid, sid)
+                avg = int(D.analyse.compare(aid,uid1,uid2)),
+                best = best
         )
 
 @app.route("/set/compare",methods=["POST"])
diff --git a/routes/answer.py b/routes/answer.py
index 2307a83..2c2be82 100644
--- a/routes/answer.py
+++ b/routes/answer.py
@@ -31,12 +31,12 @@ def answer_id():
         session["alert"] = "Koodilla ei löytynyt kyselmää"
         return redirect(next)
         
-    if next == "/#analyse" and not D.is_user_answered( aid, sid ):
+    if next == "/#analyse" and not D.quiz.user( aid, sid ):
         session["alert"] = "Et ole vielä vastannut tähän kyselmään. \
                             Voit tutkia vastaksia vastattuasi."
         return redirect("/#answer")
 
-    if next == "/#answer" and D.is_user_answered( aid, sid ):
+    if next == "/#answer" and D.quiz.user( aid, sid ):
         session["alert"] = "Olet jo vastannut valitsemaasi kyselyyn."
         return redirect("/#analyse")
 
-- 
cgit v1.2.3