QOF change: use SQL view for student data display

1 files changed, 18 insertions, 16 deletions

diff --git a/ustayml/views/students.py b/ustayml/views/students.py
index b96907e..fef1a82 100644
--- a/ustayml/views/students.py
+++ b/ustayml/views/students.py
@@ -1,4 +1,5 @@
 import math
+import datetime
 from flask import (
     Blueprint, flash, g, redirect, render_template, request, url_for
 )
@@ -18,6 +19,7 @@ def index():
     semester = [{'id': x, 'name': x} for x in range(1, 13)]
     desertion_risks_class = db.execute("select * from desertion_risk_class;").fetchall()
 
+    # pagination
     pagination = { 'pagesize': 100, 'page': None , 'n_pages': None, 'rowcount': None}
     pagination['page'] = int(request.args.get('page', 0))
 
@@ -30,37 +32,27 @@ def index():
     where_stmt = "where 1=1\n"
     params = []
     if fc['pucp_unit'] != 0:
-        where_stmt += "    and stu.pucp_unit_id = %s\n"
+        where_stmt += "    and pucp_unit_id = %s\n"
         params.append(fc['pucp_unit'])
     if fc['semester'] != 0:
-        where_stmt += "    and stu.current_semester = %s\n"
+        where_stmt += "    and 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"
+        where_stmt += "    and est_desertion_risk_class_id = %s\n"
         params.append(fc['desertion_risk_class'])
 
     query = f"""
-        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
+        select * from view_student
         {where_stmt}
     """
-    students = get_paginated_items(query, params=params, pagination=pagination)
 
-    # formatting
+    # output & formatting
+    students = get_paginated_items(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}%"
         s['current_merit'] = f"{s['current_merit']*100:.2f}%"
 
-    print(query, params)
-
     return render_template(
         'students/index.html', 
         students=students, pagination=pagination,
@@ -70,6 +62,16 @@ def index():
         fc=fc
     )
 
+@bp.route('/<int:id>', methods=('GET', 'POST'))
+@login_required
+def details(id):
+    db = get_db()
+    pucp_unit = db.execute("select * from view_student;").fetchall()
+    return render_template(
+        'students/details.html',
+        date=datetime.datetime.now()
+    )
+
 
 # Helper functions