Add filtering and pagination to bp.estudiantes

5 files changed, 187 insertions, 90 deletions

diff --git a/README.md b/README.md
index 2dc8520..19baa71 100644
--- a/README.md
+++ b/README.md
@@ -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