From f6fcf9cc3ae3d93d59391b3f12843fba3297f0b2 Mon Sep 17 00:00:00 2001
From: Mitsuo Tokumori <rtokumori@pucp.edu.pe>
Date: Sun, 12 Nov 2023 19:37:13 -0500
Subject: Add data and tooltips to student.details

---
 ustayml/db.py                           | 12 +++++--
 ustayml/static/style.css                | 51 ++++++++++++++++++++++++++++++
 ustayml/templates/students/details.html | 55 ++++++++++++++++++++++++++++++---
 ustayml/templates/students/index.html   |  2 +-
 ustayml/views/students.py               | 21 ++++++++-----
 5 files changed, 126 insertions(+), 15 deletions(-)

diff --git a/ustayml/db.py b/ustayml/db.py
index 25d0188..04587cc 100644
--- a/ustayml/db.py
+++ b/ustayml/db.py
@@ -57,12 +57,18 @@ def init_app(app):
 
 # Helper functions
 
-def get_paginated_items(query: str, params: list=[], pagination: dict={}):
+def get_paginated_rows(query: str, params: list=[], pagination: dict={}):
     limit = pagination.get('pagesize', 100)
     offset = pagination.get('page', 0) * limit
     with get_db().cursor() as cursor:
         cursor.execute(f"{query} LIMIT %s OFFSET %s;", params + [limit, offset])
         pagination['n_pages'] = math.ceil(cursor.rowcount / limit)
         pagination['rowcount'] = cursor.rowcount
-        items = cursor.fetchall()
-    return items
\ No newline at end of file
+        rows = cursor.fetchall()
+    return rows
+
+def get_row(query: str, params: list=[]):
+    with get_db().cursor() as cursor:
+        cursor.execute(query, params)
+        row = cursor.fetchone()
+    return row
\ No newline at end of file
diff --git a/ustayml/static/style.css b/ustayml/static/style.css
index 71ffd97..0357f46 100644
--- a/ustayml/static/style.css
+++ b/ustayml/static/style.css
@@ -97,6 +97,8 @@ header .action {
   padding: 0 1rem 1rem;
 }
 
+/* header */
+
 .content > header {
   border-bottom: 1px solid lightgray;
   /* display: flex;
@@ -118,6 +120,40 @@ header .action {
   align-items: flex-end;
 }
 
+/* tooltip */
+
+.tooltip {
+  position: relative;
+  display: inline-block;
+  border-bottom: 1px dotted black;
+}
+
+.tooltip .tooltiptext {
+  visibility: hidden;
+  width:max-content;
+  min-width: 10rem;
+  max-width: 60rem;
+  /* white-space: nowrap; */
+  background-color: rgba(255, 255, 224, 0.9);
+  color: black;
+  text-align: left;
+  border: 1px solid darkgoldenrod;
+  /* border-radius: 6px; */
+  padding: 0 0.2em;
+
+  /* Position the tooltip */
+  position: absolute;
+  z-index: 1;
+  top: 100%;
+  left: 100%;
+  margin-left: -5rem;
+}
+
+.tooltip:hover .tooltiptext {
+  visibility: visible;
+}
+
+/* flash */
 
 .flash {
   margin: 1em 0;
@@ -280,3 +316,18 @@ input[type="submit"] {
   align-self: start;
   min-width: 10em;
 }
+
+/* text status styles */
+
+.text-high-risk {
+  color: red;
+  background-color: rgba(255, 0, 0, .2);
+}
+
+.text-medium-risk {
+  color: orange;
+}
+
+.text-low-risk {
+  color: green;
+}
\ No newline at end of file
diff --git a/ustayml/templates/students/details.html b/ustayml/templates/students/details.html
index ed79961..a9c0cca 100644
--- a/ustayml/templates/students/details.html
+++ b/ustayml/templates/students/details.html
@@ -3,7 +3,7 @@
 {% block header %}
   <a href="#" onclick="window.history.go(-1); return false;">Regresar</a>
   <div class="flex-container-horizontal">
-    <h1>{% block title %}Reporte del estudiante{% endblock %}</h1>
+    <h1>{% block title %} Reporte de: {{ student['fullname']}}{% endblock %}</h1>
     <span>Reporte generado el {{ date.strftime("%Y-%m-%d") }}</span>
   </div>
 {% endblock %}
@@ -12,15 +12,62 @@
   <div class="student-details-body">
     <div id="information-left">
       <h3>Información general:</h3>
+      <ul>
+        <li>Código: {{ student['pucp_code'] }}</li>
+        <li>Nombre: {{ student['fullname'] }}</li>
+        <li>Email: {{ student['email'] }}</li>
+        <li>Distrito: {{ student['district'] }}</li>
+      </ul>
       <h3>Información socioeconómica:</h3>
+      <ul>
+        <li>Escala: {{ student['tuition_bracket'] }}</li>
+      </ul>
       <h3>Información académica:</h3>
+      <ul>
+        <li>Unidad: {{ student['pucp_unit'] }}</li>
+        <li>Ciclo actual: {{ student['current_semester'] }}</li>
+        <li>Asistencia: {{ student['current_attendance'] }}</li>
+        <li>
+          <div class="tooltip">
+            CRAEst:
+            <span class="tooltiptext">
+              Coeficiente de Rendimiento Académico Estandarizado
+            </span>
+            {{ student['current_craest'] }}
+          </div>
+        </li>
+        <li>
+          <div class="tooltip">
+            PPNE3:
+            <span class="tooltiptext">
+              Promedio Ponderado de Notas Estandarizadas<br>
+              de los últimos 3 semestres
+            </span>
+            {{ student['current_ppne3'] }}
+          </div>
+        </li>
+        <li>Mérito: {{ student['current_merit'] }}</li>
+
+        <li>
+          <div class="tooltip">
+            Long. de estudios est. (semestres):
+            <span class="tooltiptext">
+              Longitud de estudios estimada<br>(en semestres académicos)
+            </span>
+            {{ student['est_study_length'] }}
+          </div>
+        </li>
+        <li>Riesgo de deserción est.: {{ student['est_desertion_risk_class'] }}</li>
+      </ul>
     </div>
     <div id="information-right">
       <h2>Resultado:</h2>
-      <span class="high-risk">Riesgo de deserción alto</span>
-      <ul>
+      <span class="text-high-risk">Riesgo de deserción: {{ student['est_desertion_risk_class'] }}</span>
+      {# <ul>
         <li>Factor de riesgo: 666</li>
-      </ul>
+      </ul> #}
+      <hr>
+      <div></div>
       <span>Significancia de variables:</span>
       <div class="chart">
         <img src="{{ url_for('static', filename='img/sample-bar_chart.png') }}">
diff --git a/ustayml/templates/students/index.html b/ustayml/templates/students/index.html
index 74f84e3..29d1048 100644
--- a/ustayml/templates/students/index.html
+++ b/ustayml/templates/students/index.html
@@ -81,7 +81,7 @@
     <tbody>
       {% for student in students %}
         <tr>
-          <td><a href="{{ url_for('students.details', id=student['id']) }}">{{ student['pucp_code'] }}</a></td>
+          <td><a href="{{ url_for('students.details', student_id=student['id']) }}">{{ student['pucp_code'] }}</a></td>
           <td>{{ student['fullname'] }}</td>
           <td>{{ student['email'] }}</td>
           <td>{{ student['district'] }}</td>
diff --git a/ustayml/views/students.py b/ustayml/views/students.py
index ba1de04..6132746 100644
--- a/ustayml/views/students.py
+++ b/ustayml/views/students.py
@@ -5,7 +5,7 @@ from flask import (
 from werkzeug.exceptions import abort
 
 from ustayml.views.auth import login_required
-from ustayml.db import get_db, get_paginated_items
+from ustayml.db import get_db, get_paginated_rows, get_row
 
 bp = Blueprint('students', __name__, url_prefix='/students')
 
@@ -46,7 +46,7 @@ def index():
     """
 
     # output & formatting
-    students = get_paginated_items(query, params=params, pagination=pagination)
+    students = get_paginated_rows(query, params=params, pagination=pagination)
     for s in students:
         s['fullname'] = f"{s['first_name']} {s['last_name']}"
         s['current_attendance'] = f"{s['current_attendance']*100:.2f}%"
@@ -61,12 +61,19 @@ def index():
         fc=fc
     )
 
-@bp.route('/<int:id>', methods=('GET', 'POST'))
+@bp.route('/<int:student_id>', methods=('GET', 'POST'))
 @login_required
-def details(id):
-    db = get_db()
-    pucp_unit = db.execute("select * from view_student;").fetchall()
+def details(student_id):
+    s = get_row("select * from view_student vs where vs.id = %s;", 
+        [student_id]
+    )
+
+    s['fullname'] = f"{s['first_name']} {s['last_name']}"
+    s['current_attendance'] = f"{s['current_attendance'] * 100:.2f}%"
+    s['current_merit'] = f"{s['current_merit'] * 100:.2f}%"
+
     return render_template(
         'students/details.html',
-        date=datetime.datetime.now()
+        date=datetime.datetime.now(),
+        student=s
     )
\ No newline at end of file
-- 
cgit v1.2.3