Add students blueprint using psycopg

3 files changed, 66 insertions, 11 deletions

diff --git a/ustayml/views/auth.py b/ustayml/views/auth.py
index 92710a1..9a4eced 100644
--- a/ustayml/views/auth.py
+++ b/ustayml/views/auth.py
@@ -26,11 +26,11 @@ def register():
 
         if error is None:
             try:
-                # NOTE: don't use f-string here.  Use `?` placeholders so that
+                # NOTE: don't use f-string here.  Use placeholders so that
                 # database library can escape the fields 
                 # (otherwise SQL injection vulnerability)
                 db.execute(
-                    "INSERT INTO user (username, password) VALUES (?, ?)",
+                    "INSERT INTO \"user\" (username, password) VALUES (%s, %s)",
                     (username, generate_password_hash(password))
                 )
                 db.commit()
@@ -52,7 +52,7 @@ def login():
         db = get_db()
         error = None
         user = db.execute(
-            'SELECT * FROM user WHERE username = ?', (username,)
+            'SELECT * FROM \"user\" WHERE username = %s', (username,)
         ).fetchone()
 
         if user is None:
@@ -79,7 +79,7 @@ def load_logged_in_user():
         g.user = None
     else:
         g.user = get_db().execute(
-            'SELECT * FROM user WHERE id = ?', (user_id,)
+            'SELECT * FROM "user" WHERE id = %s', (user_id,)
         ).fetchone()
 
 
diff --git a/ustayml/views/blog.py b/ustayml/views/blog.py
index 57ba4ba..165d358 100644
--- a/ustayml/views/blog.py
+++ b/ustayml/views/blog.py
@@ -15,7 +15,7 @@ def index():
     db = get_db()
     posts = db.execute(
         'SELECT p.id, title, body, created, author_id, username'
-        ' FROM post p JOIN user u ON p.author_id = u.id'
+        ' FROM post p JOIN \"user\" u ON p.author_id = u.id'
         ' ORDER BY created DESC'
     ).fetchall()
     return render_template('blog/index.html', posts=posts)
@@ -38,7 +38,7 @@ def create():
             db = get_db()
             db.execute(
                 'INSERT INTO post (title, body, author_id)'
-                ' VALUES (?, ?, ?)',
+                ' VALUES (%s, %s, %s)',
                 (title, body, g.user['id'])
             )
             db.commit()
@@ -65,8 +65,8 @@ def update(id):
         else:
             db = get_db()
             db.execute(
-                'UPDATE post SET title = ?, body = ?'
-                ' WHERE id = ?',
+                'UPDATE post SET title = %s, body = %s'
+                ' WHERE id = %s',
                 (title, body, id)
             )
             db.commit()
@@ -80,7 +80,7 @@ def update(id):
 def delete(id):
     get_post(id)
     db = get_db()
-    db.execute('DELETE FROM post WHERE id = ?', (id,))
+    db.execute('DELETE FROM post WHERE id = %s', (id,))
     db.commit()
     return redirect(url_for('blog.index'))
 
@@ -90,8 +90,8 @@ def delete(id):
 def get_post(id, check_author=True):
     post = get_db().execute(
         'SELECT p.id, title, body, created, author_id, username'
-        ' FROM post p JOIN user u ON p.author_id = u.id'
-        ' WHERE p.id = ?',
+        ' FROM post p JOIN \"user\" u ON p.author_id = u.id'
+        ' WHERE p.id = %s',
         (id,)
     ).fetchone()
 
diff --git a/ustayml/views/students.py b/ustayml/views/students.py
new file mode 100644
index 0000000..b46c230
--- /dev/null
+++ b/ustayml/views/students.py
@@ -0,0 +1,55 @@
+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_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
+    
+    query = f"""
+        select stu.*, adm.subdivision3 as "district" 
+        from student stu left join administrative_division adm 
+            on stu.administrative_division_id = adm.id
+        ;
+    """
+    students = db.execute(query).fetchall()
+
+    # 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}%"
+
+    return render_template('students/index.html', students=students)