diff options
| -rw-r--r-- | README.md | 7 | ||||
| -rw-r--r-- | ustayml/schema.sql | 23 | ||||
| -rw-r--r-- | ustayml/static/style.css | 73 | ||||
| -rw-r--r-- | ustayml/templates/students/index.html | 83 | ||||
| -rw-r--r-- | ustayml/views/students.py | 91 |
5 files changed, 187 insertions, 90 deletions
@@ -74,4 +74,9 @@ Pros: Cons: * Maybe scalability and performance is lower compared to PHP or Javascript - web applications.
\ No newline at end of file + web applications. + +## Next Steps + +* https://www.sqlalchemy.org/ +* https://stackoverflow.com/questions/10434599/get-the-data-received-in-a-flask-request
\ No newline at end of file diff --git a/ustayml/schema.sql b/ustayml/schema.sql index 8062be8..e7e3e68 100644 --- a/ustayml/schema.sql +++ b/ustayml/schema.sql @@ -24,6 +24,11 @@ CREATE TABLE post ( -- Students +create table desertion_risk_class ( + id serial primary key, + "name" text +); + create table pucp_unit ( id serial primary key, "name" text @@ -58,9 +63,10 @@ CREATE TABLE student ( current_ppne3 REAL, current_merit REAL, est_study_length INTEGER, - est_dessertion_risk TEXT, + est_desertion_risk_class_id integer, FOREIGN KEY (administrative_division_id) REFERENCES administrative_division (id), - FOREIGN KEY (pucp_unit_id) REFERENCES pucp_unit (id) + FOREIGN KEY (pucp_unit_id) REFERENCES pucp_unit (id), + FOREIGN KEY (est_desertion_risk_class_id) REFERENCES desertion_risk_class (id) ); -- alter table student @@ -80,6 +86,11 @@ INSERT INTO "user" ("username","password","role_id") VALUES ('mitsuo','scrypt:32768:8:1$DNNf1Ah1SwUcFHIU$c0e40293c9ecff498b74e7727a8d9a348371262b85cc880e8c653438b1afa84849adb7230f4fcd995c82de543a7836a5cda2a411a45c17dc653e7036fe3de482',1) ; +INSERT INTO desertion_risk_class ("name") VALUES + ('Alto'), + ('Bajo'), + ('Medio'); + INSERT INTO pucp_unit ("name") VALUES ('Facultad de Ciencias e Ingenieria'), ('Estudios Generales Ciencias'); @@ -138,7 +149,7 @@ INSERT INTO public.administrative_division (country,subdivision1,subdivision2,su ('Peru','Lima','Lima','VILLA MARÍA DEL TRIUNFO ','Lima Sur','150143',404692,21.1,'district'); -INSERT INTO student (pucp_code,first_name,last_name,email,administrative_division_id,pucp_unit_id,tuition_bracket,current_semester,current_attendance,current_craest,current_ppne3,current_merit,est_study_length,est_dessertion_risk) VALUES - (20171234,'Alberto','Alvarez','[email protected]',40,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L'), - (20171235,'Benito','Bueno','[email protected]',41,2,'G5',5,0.8,58.35,55.23,0.1532,10,'H'), - (20171236,'Carlos','Canto','[email protected]',42,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L');
\ No newline at end of file +INSERT INTO student (pucp_code,first_name,last_name,email,administrative_division_id,pucp_unit_id,tuition_bracket,current_semester,current_attendance,current_craest,current_ppne3,current_merit,est_study_length,est_desertion_risk_class_id) VALUES + (20171234,'Alberto','Alvarez','[email protected]',40,1,'G5',5,0.8,58.35,55.23,0.1532,10,1), + (20171235,'Benito','Bueno','[email protected]',41,2,'G5',5,0.8,58.35,55.23,0.1532,10,1), + (20171236,'Carlos','Canto','[email protected]',42,1,'G5',5,0.8,58.35,55.23,0.1532,10,2);
\ No newline at end of file diff --git a/ustayml/static/style.css b/ustayml/static/style.css index 08b5e9f..9246706 100644 --- a/ustayml/static/style.css +++ b/ustayml/static/style.css @@ -91,21 +91,6 @@ header .action { margin: 1rem 0 0.25rem 0; } -.content > .filters { - margin-top: 1em; - margin-bottom: 1em; -} - -.content .filters-list { - display: flex; - flex-direction: row; - align-items: flex-end; - /* justify-content: center; */ -} - -.filters-list select { - margin-right: 2em; -} .flash { margin: 1em 0; @@ -116,6 +101,8 @@ header .action { /* students */ +/* table */ + .students-table { padding: 2em; width: 100%; @@ -135,6 +122,56 @@ header .action { margin-top: 5em; } +.table-pagination { + display: flex; + flex-direction: row; + align-items: flex-end; +} + +.table-pagination > .page-list { + display:none; + /* flex:auto; */ + /* display: flex; */ + /* flex-direction: column; */ + /* align-items: flex-end; */ + justify-content: flex-end; + margin-right: 1em; +} + +.table-pagination > .page-list a { + margin-left: .2em +} + +.filter-criteria { + margin-top: 1em; + margin-bottom: 1em; +} + +.filter-criteria fieldset { + display: flex; + /* justify-content: space-between; */ + border: 0; + padding: 0; + align-items: flex-end; + justify-content: left; +} + +.filter-criteria label { + width: auto; + display: block; + font-size: small; +} + +/* .filter-criteria input { + position: absolute; + bottom: 0; + right: 0; +} */ + +.filters-list select { + margin-right: 2em; +} + /* post */ .post > header { @@ -171,12 +208,12 @@ header .action { flex-direction: column; } -.content label { +/* .content label { font-weight: bold; margin-bottom: 0.5em; -} +} */ -.content input, +/* .content input, */ .content textarea { margin-bottom: 1em; } diff --git a/ustayml/templates/students/index.html b/ustayml/templates/students/index.html index 74c1847..6fb5312 100644 --- a/ustayml/templates/students/index.html +++ b/ustayml/templates/students/index.html @@ -7,45 +7,58 @@ {% endblock %} {% block content %} - <div class="filters"> - {# <h3>Filtros</h3> #} - <form class="filters-list" method="post"> - <label for="pucp_unit">Unidad: + <div class="filter-criteria"> + <form class="filters" method="get"> + <fieldset class="filters-list"> + <div> + <label for="pucp_unit">Unidad:</label> <select name="pucp_unit" id="pucp_unit"> - <option>Mostrar todas</option> - <option>Facultad de Ciencias e Ingenieria</option> - <option>EEGGCC</option> + <option value="0">Mostrar todas</option> + {% for e in pucp_unit %} + <option value="{{e['id']}}" {% if e['id'] == fc['pucp_unit'] %} selected {% endif %}>{{ e['name'] }}</option> + {% endfor %} </select> - </label> - <label for="semester">Semestre: + </div> + <div> + <label for="semester">Semestre: </label> <select name="semester" id="semester"> - <option>Mostrar todos</option> - <option>2023-2</option> - <option>2023-1</option> - <option>2022-2</option> - <option>2022-1</option> - <option>2021-2</option> - <option>2021-1</option> - <option>2020-2</option> - <option>2020-1</option> - <option>2019-2</option> - <option>2019-1</option> - <option>2018-2</option> - <option>2018-1</option> - <option>2017-2</option> - <option>2017-1</option> + <option value="0">Mostrar todos</option> + {% for e in semester %} + <option value="{{e['id']}}" {% if e['id'] == fc['semester'] %} selected {% endif %}>{{ e['name'] }}</option> + {% endfor %} </select> - </label> - <label for="dessertion_risk">Riesgo deserción: - <select name="dessertion_risk" id="dessertion_risk"> - <option>Mostrar todos</option> - <option>Alto</option> - <option>Medio</option> - <option>Bajo</option> + </div> + <div> + <label for="desertion_risk_class">Riesgo deserción: </label> + <select name="desertion_risk_class" id="desertion_risk_class"> + <option value="0">Mostrar todos</option> + {% for e in desertion_risk_class %} + <option value="{{e['id']}}" {% if e['id'] == fc['desertion_risk_class'] %} selected {% endif %}>{{ e['name'] }}</option> + {% endfor %} </select> - </label> + </div> + <div> + <label for="page">Página: </label> + <select name="page" id="page"> + {% for i in range(pagination['n_pages'])%} + <option value="{{i}}">{{ i + 1 }}</option> + {% endfor %} + </select> + </div> + <div> + <label></label> + <input type="submit" value="Aplicar filtros"> + </div> + </fieldset> </form> - <input type="submit" value="Aplicar filtros"> + </div> + <div class="table-pagination"> + <span> {{ pagination['rowcount'] }} estudiantes encontrados. Página {{ pagination['page'] + 1 }} de {{ pagination['n_pages'] }}.</span> + <div class="page-list"> + {% for i in range(pagination['n_pages'])%} + <a href="#">{{ i + 1 }}</a> + {% endfor %} + </div> </div> <table class="students-table"> <thead> @@ -54,6 +67,7 @@ <th>nombre</th> <th>email</th> <th>distrito</th> + <th>facultad</th> <th>asistencia</th> <th>CRAEst</th> <th>PPNE3</th> @@ -69,12 +83,13 @@ <td>{{ student['fullname'] }}</td> <td>{{ student['email'] }}</td> <td>{{ student['district'] }}</td> + <td>{{ student['pucp_unit'] }}</td> <td>{{ student['current_attendance'] }}</td> <td>{{ student['current_craest'] }}</td> <td>{{ student['current_ppne3'] }}</td> <td>{{ student['current_merit'] }}</td> <td>{{ student['est_study_length'] }}</td> - <td>{{ student['est_dessertion_risk'] }}</td> + <td>{{ student['est_desertion_risk_class'] }}</td> </tr> {% endfor %} </tbody> 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 |