diff options
| -rw-r--r-- | .gitignore | 2 | ||||
| -rw-r--r-- | tests/test_auth.py | 2 | ||||
| -rw-r--r-- | ustayml/__init__.py | 7 | ||||
| -rw-r--r-- | ustayml/db.py | 19 | ||||
| -rw-r--r-- | ustayml/schema.sql | 141 | ||||
| -rw-r--r-- | ustayml/static/img/logo_pucp.svg (renamed from ustayml/static/logo_pucp.svg) | 0 | ||||
| -rw-r--r-- | ustayml/static/style.css | 64 | ||||
| -rw-r--r-- | ustayml/templates/auth/login.html | 2 | ||||
| -rw-r--r-- | ustayml/templates/base.html | 6 | ||||
| -rw-r--r-- | ustayml/templates/blog/create.html | 2 | ||||
| -rw-r--r-- | ustayml/templates/students/index.html | 82 | ||||
| -rw-r--r-- | ustayml/views/auth.py | 8 | ||||
| -rw-r--r-- | ustayml/views/blog.py | 14 | ||||
| -rw-r--r-- | ustayml/views/students.py | 55 |
14 files changed, 369 insertions, 35 deletions
@@ -12,3 +12,5 @@ htmlcov/ dist/ build/ *.egg-info/ + +bin/ diff --git a/tests/test_auth.py b/tests/test_auth.py index 6684f43..d2735dc 100644 --- a/tests/test_auth.py +++ b/tests/test_auth.py @@ -12,7 +12,7 @@ def test_register(client, app): with app.app_context(): assert get_db().execute( - "SELECT * FROM user WHERE username = 'a'", + "SELECT * FROM \"user\" WHERE username = 'a'", ).fetchone() is not None diff --git a/ustayml/__init__.py b/ustayml/__init__.py index 1c411e7..135ef57 100644 --- a/ustayml/__init__.py +++ b/ustayml/__init__.py @@ -35,8 +35,11 @@ def create_app(test_config=None): from .views import auth app.register_blueprint(auth.bp) - from .views import blog - app.register_blueprint(blog.bp) + # from .views import blog + # app.register_blueprint(blog.bp) + + from .views import students + app.register_blueprint(students.bp) # Extra app.add_url_rule('/', endpoint='index') diff --git a/ustayml/db.py b/ustayml/db.py index eb4e8b4..8fb1ec9 100644 --- a/ustayml/db.py +++ b/ustayml/db.py @@ -1,4 +1,4 @@ -import sqlite3 +import psycopg import click from flask import current_app, g @@ -10,12 +10,17 @@ def get_db(): sqlite3: https://docs.python.org/3/library/sqlite3.html """ if 'db' not in g: - g.db = sqlite3.connect( - current_app.config['DATABASE'], - detect_types=sqlite3.PARSE_DECLTYPES + # g.db = sqlite3.connect( + # current_app.config['DATABASE'], + # detect_types=sqlite3.PARSE_DECLTYPES + # ) + # # Return rows that behave like dicts + # g.db.row_factory = sqlite3.Row + + g.db = psycopg.connect( + "dbname=ustayml user=mitsuo", + row_factory=psycopg.rows.dict_row ) - # Return rows that behave like dicts - g.db.row_factory = sqlite3.Row return g.db @@ -33,7 +38,7 @@ def init_db(): db = get_db() with current_app.open_resource('schema.sql') as f: - db.executescript(f.read().decode('utf8')) + db.execute(f.read().decode('utf8')) @click.command('init-db') diff --git a/ustayml/schema.sql b/ustayml/schema.sql index be76d7e..8062be8 100644 --- a/ustayml/schema.sql +++ b/ustayml/schema.sql @@ -1,17 +1,144 @@ -DROP TABLE IF EXISTS user; -DROP TABLE IF EXISTS post; +-- System -CREATE TABLE user ( - id INTEGER PRIMARY KEY AUTOINCREMENT, +CREATE TABLE user_role ( + id SERIAL PRIMARY KEY, + name TEXT +); + +CREATE TABLE "user" ( + id SERIAL PRIMARY KEY, username TEXT UNIQUE NOT NULL, - password TEXT NOT NULL + password TEXT NOT NULL, + role_id INTEGER NOT NULL, + FOREIGN KEY (role_id) REFERENCES user_role (id) ); CREATE TABLE post ( - id INTEGER PRIMARY KEY AUTOINCREMENT, + id SERIAL PRIMARY KEY, author_id INTEGER NOT NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, title TEXT NOT NULL, body TEXT NOT NULL, - FOREIGN KEY (author_id) REFERENCES user (id) + FOREIGN KEY (author_id) REFERENCES "user" (id) +); + +-- Students + +create table pucp_unit ( + id serial primary key, + "name" text +); + +create table administrative_division ( + id serial primary key, + "name" text, + country text, + subdivision1 text, + subdivision2 text, + subdivision3 text, + subdivision10 text, + ubigeo text, + "population" integer, + poverty real +); + +CREATE TABLE student ( + id SERIAL PRIMARY KEY, + pucp_code INTEGER, + first_name TEXT, + last_name TEXT, + email TEXT, + -- district TEXT, + administrative_division_id integer, + pucp_unit_id integer, + tuition_bracket TEXT, + current_semester integer, + current_attendance REAL, + current_craest REAL, + current_ppne3 REAL, + current_merit REAL, + est_study_length INTEGER, + est_dessertion_risk TEXT, + FOREIGN KEY (administrative_division_id) REFERENCES administrative_division (id), + FOREIGN KEY (pucp_unit_id) REFERENCES pucp_unit (id) ); + +-- alter table student +-- add constraint fk_administrative_division +-- FOREIGN KEY (administrative_division_id) +-- REFERENCES administrative_division (id) +-- ; + +-- Sample data (later move to tests) + +INSERT INTO user_role ("id", "name") VALUES + (1, 'admin'), + (2, 'user') +; + +INSERT INTO "user" ("username","password","role_id") VALUES + ('mitsuo','scrypt:32768:8:1$DNNf1Ah1SwUcFHIU$c0e40293c9ecff498b74e7727a8d9a348371262b85cc880e8c653438b1afa84849adb7230f4fcd995c82de543a7836a5cda2a411a45c17dc653e7036fe3de482',1) +; + +INSERT INTO pucp_unit ("name") VALUES + ('Facultad de Ciencias e Ingenieria'), + ('Estudios Generales Ciencias'); + +-- sample 2019 data +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','','','','','',29009326,28.9,'country'), + ('Peru','Lima','','','','150000',8981440,15.4,'region'), + ('Peru','Lima','Lima','','','150100',8095747,17.5,'province'), + ('Peru','Lima','Lima','LIMA ','Lima Centro','150101',302056,12.3,'district'), + ('Peru','Lima','Lima','ANCÓN ','Lima Norte','150102',36401,19.6,'district'), + ('Peru','Lima','Lima','ATE ','Lima Este','150103',521692,18.9,'district'), + ('Peru','Lima','Lima','BARRANCO ','Lima Centro','150104',33996,5.3,'district'), + ('Peru','Lima','Lima','BREÑA ','Lima Centro','150105',82987,8.5,'district'), + ('Peru','Lima','Lima','CARABAYLLO ','Lima Norte','150106',237269,26.3,'district'), + ('Peru','Lima','Lima','CHACLACAYO ','Lima Este','150107',42884,10.1,'district'); +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','Lima','Lima','CHORRILLOS ','Lima Centro','150108',303913,17.2,'district'), + ('Peru','Lima','Lima','CIENEGUILLA ','Lima Este','150109',31160,24.8,'district'), + ('Peru','Lima','Lima','COMAS ','Lima Norte','150110',509976,22.3,'district'), + ('Peru','Lima','Lima','EL AGUSTINO ','Lima Este','150111',188138,22.1,'district'), + ('Peru','Lima','Lima','INDEPENDENCIA ','Lima Norte','150112',215941,21.3,'district'), + ('Peru','Lima','Lima','JESÚS MARÍA ','Lima Centro','150113',71139,1.7,'district'), + ('Peru','Lima','Lima','LA MOLINA ','Lima Este','150114',144491,0.7,'district'), + ('Peru','Lima','Lima','LA VICTORIA ','Lima Centro','150115',193592,14.9,'district'), + ('Peru','Lima','Lima','LINCE ','Lima Centro','150116',55733,4.0,'district'), + ('Peru','Lima','Lima','LOS OLIVOS ','Lima Norte','150117',339028,13.4,'district'); +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','Lima','Lima','LURIGANCHO ','Lima Este','150118',184593,24.4,'district'), + ('Peru','Lima','Lima','LURÍN ','Lima Sur','150119',69282,30.2,'district'), + ('Peru','Lima','Lima','MAGDALENA DEL MAR ','Lima Centro','150120',54116,2.3,'district'), + ('Peru','Lima','Lima','PUEBLO LIBRE','Lima Centro','150121',77892,2.0,'district'), + ('Peru','Lima','Lima','MIRAFLORES ','Lima Centro','150122',86920,0.8,'district'), + ('Peru','Lima','Lima','PACHACAMAC ','Lima Este','150123',81145,32.9,'district'), + ('Peru','Lima','Lima','PUCUSANA ','Lima Sur','150124',12148,26.7,'district'), + ('Peru','Lima','Lima','PUENTE PIEDRA ','Lima Norte','150125',263594,35.6,'district'), + ('Peru','Lima','Lima','PUNTA HERMOSA ','Lima Sur','150126',6309,7.4,'district'), + ('Peru','Lima','Lima','PUNTA NEGRA ','Lima Sur','150127',5951,9.5,'district'); +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','Lima','Lima','RIMAC ','Lima Centro','150128',178869,16.5,'district'), + ('Peru','Lima','Lima','SAN BARTOLO ','Lima Sur','150129',6368,9.7,'district'), + ('Peru','Lima','Lima','SAN BORJA ','Lima Centro','150130',111208,0.8,'district'), + ('Peru','Lima','Lima','SAN ISIDRO ','Lima Centro','150131',58920,0.6,'district'), + ('Peru','Lima','Lima','SAN JUAN DE LURIGANCHO ','Lima Este','150132',962554,27.0,'district'), + ('Peru','Lima','Lima','SAN JUAN DE MIRAFLORES ','Lima Sur','150133',382531,19.7,'district'), + ('Peru','Lima','Lima','SAN LUIS ','Lima Este','150134',57080,7.1,'district'), + ('Peru','Lima','Lima','SAN MARTÍN DE PORRES ','Lima Norte','150135',620193,10.9,'district'), + ('Peru','Lima','Lima','SAN MIGUEL ','Lima Centro','150136',134666,2.3,'district'), + ('Peru','Lima','Lima','SANTA ANITA ','Lima Este','150137',199282,12.0,'district'); +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','Lima','Lima','SANTA MARÍA DEL MAR ','Lima Sur','150138',924,4.0,'district'), + ('Peru','Lima','Lima','SANTA ROSA ','Lima Norte','150139',12641,12.0,'district'), + ('Peru','Lima','Lima','SANTIAGO DE SURCO ','Lima Centro','150140',309889,3.3,'district'), + ('Peru','Lima','Lima','SURQUILLO ','Lima Centro','150141',93271,5.2,'district'), + ('Peru','Lima','Lima','VILLA EL SALVADOR ','Lima Sur','150142',410313,25.9,'district'), + ('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 diff --git a/ustayml/static/logo_pucp.svg b/ustayml/static/img/logo_pucp.svg index 8ffb164..8ffb164 100644 --- a/ustayml/static/logo_pucp.svg +++ b/ustayml/static/img/logo_pucp.svg diff --git a/ustayml/static/style.css b/ustayml/static/style.css index 1a73cc5..08b5e9f 100644 --- a/ustayml/static/style.css +++ b/ustayml/static/style.css @@ -1,13 +1,14 @@ html { font-family: sans-serif; background: #eee; - padding: 1rem; + /* padding: 1rem; */ } body { + min-width: 480px; max-width: 960px; margin: 0 auto; - background: white; + /* background: white; */ } h1, @@ -31,14 +32,21 @@ hr { } nav { - background: lightgray; + min-height: 80px; + background: #042254; /* PUCP dark blue 1 */ display: flex; align-items: center; + justify-content: space-between; padding: 0 0.5rem; } +nav a, +nav span { + color: white; +} + nav h1 { - flex: auto; + /* flex: auto; */ margin: 0; } @@ -54,6 +62,11 @@ nav ul { padding: 0; } +.pad-navbar-links { + margin-left: 2rem; + margin-right: 2rem; +} + nav ul li a, nav ul li span, header .action { @@ -62,6 +75,8 @@ header .action { } .content { + background: white; + margin-top: 1rem; padding: 0 1rem 1rem; } @@ -76,6 +91,22 @@ 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; padding: 1em; @@ -83,6 +114,29 @@ header .action { border: 1px solid #377ba8; } +/* students */ + +.students-table { + padding: 2em; + width: 100%; + border-collapse: collapse; +} + +.students-table th, +.students-table td { + border: 1px solid #ccc; + padding: 0.2em 0.2em; +} + +.report-info { + display: flex; + justify-content: space-between; + padding: 5em; + margin-top: 5em; +} + +/* post */ + .post > header { display: flex; align-items: flex-end; @@ -111,7 +165,7 @@ header .action { margin-bottom: 0; } -.content form { +.content .simple-form { margin: 1em 0; display: flex; flex-direction: column; diff --git a/ustayml/templates/auth/login.html b/ustayml/templates/auth/login.html index b7dd5dc..45acd7f 100644 --- a/ustayml/templates/auth/login.html +++ b/ustayml/templates/auth/login.html @@ -5,7 +5,7 @@ {% endblock %} {% block content %} - <form method="post"> + <form class="simple-form" method="post"> <label for="username">Username</label> <input name="username" id="username" required> <label for="password">Password</label> diff --git a/ustayml/templates/base.html b/ustayml/templates/base.html index 1b2c384..b8d243c 100644 --- a/ustayml/templates/base.html +++ b/ustayml/templates/base.html @@ -5,6 +5,12 @@ <nav> <h1><a href="{{ url_for('index') }}">u-stayML</a></h1> <ul> + <li><a href="{{ url_for('students.index') }}">Dashboard</a></li> + <li><a href="{{ url_for('students.index') }}">Estudiantes</a></li> + <li><a href="#">Historial</a> </li> + <li><a href="#">Cargar datos</a> </li> + </ul> + <ul> {% if g.user %} <li><span>{{ g.user['username'] }}</span> <li><a href="{{ url_for('auth.logout') }}">Log Out</a> diff --git a/ustayml/templates/blog/create.html b/ustayml/templates/blog/create.html index 88e31e4..9132641 100644 --- a/ustayml/templates/blog/create.html +++ b/ustayml/templates/blog/create.html @@ -10,6 +10,6 @@ <input name="title" id="title" value="{{ request.form['title'] }}" required> <label for="body">Body</label> <textarea name="body" id="body">{{ request.form['body'] }}</textarea> - <input type="submit" value="Save"> + <input type="submit" value="Aplicar"> </form> {% endblock %} diff --git a/ustayml/templates/students/index.html b/ustayml/templates/students/index.html new file mode 100644 index 0000000..74c1847 --- /dev/null +++ b/ustayml/templates/students/index.html @@ -0,0 +1,82 @@ +{% extends 'base.html' %} + + +{% block header %} + <h1>{% block title %}Estudiantes{% endblock %}</h1> + <span>💡TIP: hacer click en un estudiante para ver su detalle</span> +{% endblock %} + +{% block content %} + <div class="filters"> + {# <h3>Filtros</h3> #} + <form class="filters-list" method="post"> + <label for="pucp_unit">Unidad: + <select name="pucp_unit" id="pucp_unit"> + <option>Mostrar todas</option> + <option>Facultad de Ciencias e Ingenieria</option> + <option>EEGGCC</option> + </select> + </label> + <label for="semester">Semestre: + <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> + </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> + </select> + </label> + </form> + <input type="submit" value="Aplicar filtros"> + </div> + <table class="students-table"> + <thead> + <tr> + <th>código</th> + <th>nombre</th> + <th>email</th> + <th>distrito</th> + <th>asistencia</th> + <th>CRAEst</th> + <th>PPNE3</th> + <th>mérito</th> + <th>ciclos est.</th> + <th>riesgo est.</th> + </tr> + </thead> + <tbody> + {% for student in students %} + <tr> + <td>{{ student['pucp_code'] }}</td> + <td>{{ student['fullname'] }}</td> + <td>{{ student['email'] }}</td> + <td>{{ student['district'] }}</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> + </tr> + {% endfor %} + </tbody> + </table> +{% endblock %}
\ No newline at end of file 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') + + [email protected]('/', 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) |
