1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
import math
from flask import (
Blueprint, flash, g, redirect, render_template, request, url_for
)
from werkzeug.exceptions import abort
from ustayml.views.auth import login_required
from ustayml.db import get_db
bp = Blueprint('students', __name__, url_prefix='/students')
@bp.route('/', methods=('GET', 'POST'))
@login_required
def index():
db = get_db()
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", 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 = get_paginated_items(query, params=params, pagination=pagination)
# formatting
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,
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
|
