From 907f8e563b648b426f93ee162f9f703013f9ad50 Mon Sep 17 00:00:00 2001 From: Mitsuo Tokumori Date: Sun, 12 Nov 2023 17:44:28 -0500 Subject: QOF change: use SQL view for student data display --- ustayml/schema.sql | 16 ++++++++++++- ustayml/static/img/sample-bar_chart.png | Bin 0 -> 14541 bytes ustayml/static/style.css | 40 ++++++++++++++++++++++++++++++++ ustayml/templates/students/details.html | 27 +++++++++++++++++++++ ustayml/templates/students/index.html | 2 +- ustayml/views/students.py | 34 ++++++++++++++------------- 6 files changed, 101 insertions(+), 18 deletions(-) create mode 100644 ustayml/static/img/sample-bar_chart.png create mode 100644 ustayml/templates/students/details.html diff --git a/ustayml/schema.sql b/ustayml/schema.sql index e7e3e68..a160605 100644 --- a/ustayml/schema.sql +++ b/ustayml/schema.sql @@ -75,7 +75,21 @@ CREATE TABLE student ( -- REFERENCES administrative_division (id) -- ; --- Sample data (later move to tests) +-- VIEWS + +create view view_student as +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 +; + +-- SAMPLE DATA (LATER MOVE TO TESTS) INSERT INTO user_role ("id", "name") VALUES (1, 'admin'), diff --git a/ustayml/static/img/sample-bar_chart.png b/ustayml/static/img/sample-bar_chart.png new file mode 100644 index 0000000..35f5ccc Binary files /dev/null and b/ustayml/static/img/sample-bar_chart.png differ diff --git a/ustayml/static/style.css b/ustayml/static/style.css index 9246706..0ff4975 100644 --- a/ustayml/static/style.css +++ b/ustayml/static/style.css @@ -19,9 +19,26 @@ h5, h6 { font-family: serif; color: #377ba8; + margin: 0; +} + +h1 { + font-size: 22pt; margin: 1rem 0; } +h2 { + font-size: 18pt; +} + +h3 { + font-size: 14pt; +} + +p { + font-size: 12pt; +} + a { color: #377ba8; } @@ -84,6 +101,7 @@ header .action { border-bottom: 1px solid lightgray; display: flex; align-items: flex-end; + margin-bottom: 1em; } .content > header h1 { @@ -172,6 +190,28 @@ header .action { margin-right: 2em; } +/* student.details */ + +.student-details-container { + display: flex; + justify-content: space-between; + min-height: 20em; +} + +.student-details-container > div { + padding: .5em; + border: 1px solid black; +} + +.student-details-container > #information-left { + width: 40%; + margin-right: 1em; +} + +.student-details-container > #information-right { + flex: auto; +} + /* post */ .post > header { diff --git a/ustayml/templates/students/details.html b/ustayml/templates/students/details.html new file mode 100644 index 0000000..6c28ea4 --- /dev/null +++ b/ustayml/templates/students/details.html @@ -0,0 +1,27 @@ +{% extends 'base.html' %} + +{% block header %} +

{% block title %}Reporte del estudiante{% endblock %}

+ Reporte generado el {{ date.strftime("%Y-%m-%d") }} +{% endblock %} + +{% block content %} +
+
+

Información general:

+

Información socioeconómica:

+

Información académica:

+
+
+

Resultado:

+ Riesgo de deserción alto +
    +
  • Factor de riesgo: 666
    • +
+ Significancia de variables: +
+ +
+
+
+{% endblock %} \ No newline at end of file diff --git a/ustayml/templates/students/index.html b/ustayml/templates/students/index.html index 6fb5312..050783b 100644 --- a/ustayml/templates/students/index.html +++ b/ustayml/templates/students/index.html @@ -79,7 +79,7 @@ {% for student in students %} - {{ student['pucp_code'] }} + {{ student['pucp_code'] }} {{ student['fullname'] }} {{ student['email'] }} {{ student['district'] }} diff --git a/ustayml/views/students.py b/ustayml/views/students.py index b96907e..fef1a82 100644 --- a/ustayml/views/students.py +++ b/ustayml/views/students.py @@ -1,4 +1,5 @@ import math +import datetime from flask import ( Blueprint, flash, g, redirect, render_template, request, url_for ) @@ -18,6 +19,7 @@ def index(): semester = [{'id': x, 'name': x} for x in range(1, 13)] desertion_risks_class = db.execute("select * from desertion_risk_class;").fetchall() + # pagination pagination = { 'pagesize': 100, 'page': None , 'n_pages': None, 'rowcount': None} pagination['page'] = int(request.args.get('page', 0)) @@ -30,37 +32,27 @@ def index(): where_stmt = "where 1=1\n" params = [] if fc['pucp_unit'] != 0: - where_stmt += " and stu.pucp_unit_id = %s\n" + where_stmt += " and pucp_unit_id = %s\n" params.append(fc['pucp_unit']) if fc['semester'] != 0: - where_stmt += " and stu.current_semester = %s\n" + where_stmt += " and 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" + where_stmt += " and 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 + select * from view_student {where_stmt} """ - students = get_paginated_items(query, params=params, pagination=pagination) - # formatting + # output & formatting + students = get_paginated_items(query, params=params, pagination=pagination) 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, @@ -70,6 +62,16 @@ def index(): fc=fc ) +@bp.route('/', methods=('GET', 'POST')) +@login_required +def details(id): + db = get_db() + pucp_unit = db.execute("select * from view_student;").fetchall() + return render_template( + 'students/details.html', + date=datetime.datetime.now() + ) + # Helper functions -- cgit v1.2.3