diff options
Diffstat (limited to 'ustayml/views/students.py')
| -rw-r--r-- | ustayml/views/students.py | 91 |
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 |