Add filtering and pagination to bp.estudiantes

1 files changed, 60 insertions, 31 deletions

diff --git a/ustayml/views/students.py b/ustayml/views/students.py
index b46c230..b96907e 100644
--- a/ustayml/views/students.py
+++ b/ustayml/views/students.py
@@ -1,3 +1,4 @@
+import math
 from flask import (
     Blueprint, flash, g, redirect, render_template, request, url_for
 )
@@ -13,38 +14,44 @@ bp = Blueprint('students', __name__, url_prefix='/students')
 @login_required
 def index():
     db = get_db()
-    pucp_units = db.execute("select * from pucp_unit;").fetchall()
-    semesters = list(range(20, 0, -1))
-    dessertion_risks = ['H', 'M', 'L']
-
-    # filters
-    filters = {}
-    where_clause = "1=1\n"
-    if request.method == 'POST':
-        filters['pucp_unit'] = request.form['pucp_unit']
-        filters['semester'] = request.form['semester']
-        filters['dessertion_risk'] = request.form['dessertion_risk']
-
-        if filters['pucp_unit'] in pucp_units:
-            where_clause += f"and stu.pucp_unit_id = {filters['pucp_unit']}\n"
-        if filters['semester'] in semesters:
-            where_clause += f"and stu.current_semester  = {filters['semester']}"
-        if filters['dessertion_risk'] in dessertion_risks:
-            where_clause += f"and stu.est_dessertion_risk  = {filters['dessertion_risk']}"
-
-        error = None
-        if error is not None:
-            flash(error)
-        else:
-            pass
-    
+    pucp_unit = db.execute("select * from pucp_unit;").fetchall()
+    semester = [{'id': x, 'name': x} for x in range(1, 13)]
+    desertion_risks_class = db.execute("select * from desertion_risk_class;").fetchall()
+
+    pagination = { 'pagesize': 100, 'page': None , 'n_pages': None, 'rowcount': None}
+    pagination['page'] = int(request.args.get('page', 0))
+
+    # filter criteria    | field choices
+    fc = {
+        'pucp_unit': int(request.args.get('pucp_unit', 0)),
+        'semester': int(request.args.get('semester', 0)),
+        'desertion_risk_class': int(request.args.get('desertion_risk_class', 0)),
+    }
+    where_stmt = "where 1=1\n"
+    params = []
+    if fc['pucp_unit'] != 0:
+        where_stmt += "    and stu.pucp_unit_id = %s\n"
+        params.append(fc['pucp_unit'])
+    if fc['semester'] != 0:
+        where_stmt += "    and stu.current_semester = %s\n"
+        params.append(fc['semester'])
+    if fc['desertion_risk_class'] != 0:
+        where_stmt += "    and stu.est_desertion_risk_class_id = %s\n"
+        params.append(fc['desertion_risk_class'])
+
     query = f"""
-        select stu.*, adm.subdivision3 as "district" 
-        from student stu left join administrative_division adm 
-            on stu.administrative_division_id = adm.id
-        ;
+        select stu.*, adm.subdivision3 as "district", puc.name as "pucp_unit",
+            des.name as "est_desertion_risk_class"
+        from student stu 
+            left join administrative_division adm 
+                on stu.administrative_division_id = adm.id
+            left join pucp_unit as puc
+                on stu.pucp_unit_id = puc.id
+            left join desertion_risk_class as des
+                on stu.est_desertion_risk_class_id = des.id
+        {where_stmt}
     """
-    students = db.execute(query).fetchall()
+    students = get_paginated_items(query, params=params, pagination=pagination)
 
     # formatting
     for s in students:
@@ -52,4 +59,26 @@ def index():
         s['current_attendance'] = f"{s['current_attendance']*100:.2f}%"
         s['current_merit'] = f"{s['current_merit']*100:.2f}%"
 
-    return render_template('students/index.html', students=students)
+    print(query, params)
+
+    return render_template(
+        'students/index.html', 
+        students=students, pagination=pagination,
+        pucp_unit=pucp_unit,
+        semester=semester,
+        desertion_risk_class=desertion_risks_class,
+        fc=fc
+    )
+
+
+# Helper functions
+
+def get_paginated_items(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