SQL Advance - README
--------------------

The SQL Advance Flask app requires the following items:

1) A Python virtual environment (venv) with the requisite packages installed.
2) Apache2 running the WSGI web server, configured for Flask and htaccess.
3) A PostgreSQL database - the default is called flask. If it is changed the
/opt/flask-app/app.py must be modified.
4) The SQL Advance files (this package).

::::::::::::::
Python3 Setup
::::::::::::::

To use Flask with Apache, install the WSGI module:
sudo apt install libapache2-mod-wsgi-py3

To set up the Python virtual environment, install pip and the venv module:
sudo apt install python3-pip
sudo apt install python3.*-venv
The wildcard (*) allows flexibility across different Python 3 versions and avoids version mismatch issues.

Next, create a repository for your Flask project:
sudo mkdir /opt/flask-app
cd /opt/flask-app

Initialize a new virtual environment:
sudo python3 -m venv flask-venv

Prebuilt Flask Virtual Environments
For your convenience, prebuilt Flask virtual environments are provided for:

Python 3.10: Setup/Packages/flask-venv-3.10.tar.gz
Python 3.12: Setup/Packages/flask-venv-3.12.tar.gz

To use one:
tar -xzf ./Setup/Packages/flask-venv-3.12.tar.gz
This extracts a flask-venv directory into the project root (i.e., alongside app.py).

source flask-venv/bin/activate

These environments include all required packages. You do not need to run 
pip install -r requirements.txt 
unless you prefer to build your own venv.

Note: The version strings ("pins") in the requirements.txt for SQL Advance have 
been intentionally trimmed to avoid version mismatch errors across Python versions.

Required Python Packages

The dependencies for SQL Advance include:

     blinker
     cachetools
     chardet
     click
     colorama
     distlib
     filelock
     Flask
     flask-htpasswd
     Flask-Login
     Flask-SQLAlchemy
     greenlet
     itsdangerous
     Jinja2
     MarkupSafe
     packaging
     passlib
     platformdirs
     pluggy
     psycopg2
     PyJWT
     pyproject-api
     SQLAlchemy
     tomli
     tox
     typing_extensions
     virtualenv
     Werkzeug

If you've activated your virtual environment (with source flask-venv/bin/activate):
(flask-venv) root@legend:/opt/flask-app# pip install -r /path/to/requirements.txt

To capture your exact working set of installed packages:
(flask-venv) root@legend:/opt/flask-app# pip freeze > /path/to/requirements-backup.txt

Use Ctrl+D or type deactivate to exit the virtual environment.

You may wish to back up the working venv:

sudo chown -R www-data:www-data /opt/flask-app
cd /opt/flask-app
sudo tar czf flask-venv-backup.tgz flask-venv

This will create a compressed archive flask-venv-backup.tgz containing your working environment.

An optional package is python3-flake8 (sudo apt install python3-flake8) which can be run against
a python file for basic syntax checking.

::::::::::::::
Apache2 Setup
::::::::::::::

Essential Modifications for Flask:

*** For Flask to run on Apache it is necessary to create a site and then enable it. ***

Here is a functional /etc/apache2/sites-available/flask.conf that uses SSL:

<VirtualHost _default_:443>
    ServerName flask.hostname.org
    DocumentRoot /opt/flask-app/

    WSGIDaemonProcess app user=www-data group=www-data threads=5 python-home=/opt/flask-app/flask-venv
    WSGIScriptAlias / /opt/flask-app/flask-app.wsgi
    WSGIPassAuthorization On

    ErrorLog ${APACHE_LOG_DIR}/flask-error.log
    CustomLog ${APACHE_LOG_DIR}/flask-access.log combined

    SSLEngine on
    
    # Default certs for private networks, certbot can create certs for public facing systems:
    SSLCertificateFile /etc/ssl/certs/ssl-cert-snakeoil.pem
    SSLCertificateKeyFile /etc/ssl/private/ssl-cert-snakeoil.key

    <Directory /opt/flask-app>
        WSGIProcessGroup app
        WSGIApplicationGroup %{GLOBAL}
        Order deny,allow
        Require all granted
    </Directory>
</VirtualHost>

*** End of /etc/apache2/sites-available/flask.conf ***

To activate the flask.conf run:

    $ sudo a2ensite flask.conf

This creates a symlink in /etc/apache2/sites-enabled pointing to the file you created.

Enable the necessary modules:

    $ sudo a2enmod ssl
    $ sudo a2enmod wsgi

Note: enabling mod_ssl will auto-link the default-ssl.conf file, which may conflict with other configurations (e.g. legacy Perl CGI). You may disable it via:

    $ sudo a2dissite default-ssl.conf

Optional but sometimes helpful:

    $ sudo a2enmod alias
    $ sudo a2enmod rewrite

Now create the /opt/flask-app/flask-app.wsgi file:

    import sys
    sys.path.insert(0, '/opt/flask-app')

    from app import app as application

Restart Apache after making changes:

    $ sudo systemctl restart apache2

::::::::::::::
PostgreSQL Setup
::::::::::::::

1) setup the Pg Host Based Authentication file.
Sample /etc/postgresql/16/main/pg_hba.conf:
# Note: 16 is the Pg version, it changes with different (X)ubuntu releases.
# Ubuntu and Debian based systems use even numbered versions of Pg.

-------------------------------------------------------------------------------

local   all             some_superuser_login_for_testing        trust

local   all             postgres                                peer
local   flask           www-data                                scram-sha-256
host    flask           www-data        127.0.0.1/32            scram-sha-256
host    template1       www-data        127.0.0.1/32            scram-sha-256

# Uncomment the next line for IPv6:
# host    all             all             ::1/128                 scram-sha-256

-------------------------------------------------------------------------------

To enable the changes restart Pg: $ sudo systemcrl restart postgresql

2) Setup the default Pg user - as the postgres user run these commands,
changing the default password to something memorable and hard to guess:

CREATE ROLE "www-data" WITH LOGIN PASSWORD 'Your_Flask_Password_Here';
CREATE DATABASE flask OWNER "www-data";

3) As the www-data default user or postgres superuser:

psql -U www-data -h localhost -W -d flask < database.df >pg_load.log 2>pg_error.log &

The -W will tell Pg to prompt for the password set in the CREATE ROLE command.
pg_load.log will list all objects successfully created.
pg_error.log will list any errors generated by the Pg parser.

4) Log in to the database to ensure all is well. You can use a shell script to do this:

#!/bin/bash
export PGPASSWORD='Your_Flask_Password_Here' 
psql -U www-data flask
unset PGPASSWORD

From the command line:
psql -U www-data flask -W

In psql you can run \dt to list the tables and verify ownership.

*NOTE* remember to change the default password (Your_Flask_Password_Here) to something hard to guess.

::::::::::::::
.htaccess and .htpasswd (User Authentication)
::::::::::::::

Before you can access the flask-app you will need to set up user authentication.

For enhanced security SQL Advance uses the Apache .htaccess strategy via the flask_htpasswd
dependency (installed earlier). This provides a site (shared) login and password.

It is simple to set up. There is an .htaccess file in the SQL Advance project root:

AuthName "SQL Advance"
AuthType Basic
AuthUserFile /opt/flask-app/.htpasswd
Require valid-user

This file tells Flask to look for the /opt/flask-app/.htpasswd file. Here is a sample file's contents:
flask:$apr1$b7fSDb3T$zFCLiN7RS2VooCIlh7vRH/
The user name is followed by a colon and an encrypted password.

There is a shell script in the project root that will create the file. You will be prompted for a user name
and password (change "flask" to whatever you prefer):

sudo htpasswd -c .htpasswd flask
New password:
Re-type new password:
Adding password for user flask

You can add additional site passwords:

sudo  htpasswd .htpasswd other
New password:
Re-type new password:
Adding password for user other
 
This file (.htpasswd) will get the users access to the site ONLY. The individual user accounts are
created in the web interface of the Flask app. The default user name is colt and the password is
Your_Flask_Password_Here (in the Unix tradition colt is a treacherous pun, it refers to Rick Grimes'
sidearm in the Walking Dead - a Colt Python).

Change this password before allowing others to access the site.

The app.py file in the project root contains the information needed to start Flask.
There are two lines in this file that connect the application to the underlying database and
a "secret key". These lines should be edited:

app.config['SECRET_KEY'] = 'Secret Treaties'
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://www-data:Your_Flask_Password_Here@localhost:5432/flask'

Change the key from Secret Treaties to whatever you prefer.
In the database connect string you should change the www-data user's password to whatever you set it to in
the Postgres part of the setup.

Towards the end of app.py there is a psycopg2 call to the database, this too should be updated:
conn = psycopg2.connect("dbname='flask' user='www-data' host='localhost' password='Your_Flask_Password_Here'")

The flask-login dependency installed earlier is set up in the auth.py Python script located in the
project root directory. There is one call to the database in this file:
conn = psycopg2.connect("dbname='flask' user='www-data' host='localhost' password='Your_Flask_Password_Here'")

To recap: create the .htpasswd file to establish a site password.
Edit app.py and its blueprint auth.py - changing the www-data user's password to whatever you set it to
when you set up Postgres.

With these steps complete, the Apache + Flask + PostgreSQL stack is secured at the entry point and internally.

::::::::::::::
Application Layout
::::::::::::::

flask-app/
├── Advance/                        # Core application features
│   ├── admin/                      # Administrative interface
│   │   ├── __init__.py
│   │   ├── routes.py
│   │   └── templates/
│   ├── chart/                      # Clinical charting
│   └── reports/                    # Reporting functions
├── auth.py                         # Handles user login/authentication
├── app.py                          # Flask app factory and blueprint registration
├── advance_helper.py               # Helper Functions
├── static/                         # Global static files (CSS, images, etc.)
│   ├── main.css
│   └── logo.jpg
├── templates/                      # Global templates (e.g., base layout)
│   ├── _error.html 
│   ├── _footer.html
│   ├── _header.html
│   ├── _macros.html
│   └── base.html
├── requirements.txt                # Python dependencies
└── README                          # This document

app.py sets up the Flask application and registers each blueprint.

auth.py handles authentication using both flask_htpasswd (site-level access control) and flask_login (user identity and session).

Blueprints (Advance/) group routes and templates logically. Each has its own templates/ folder, but not subdivided further — templates sit directly inside each feature's templates/ dir.

Global Templates like _header.html live in the top-level templates/ folder to allow simple updates to site branding (e.g., agency name and address) in one place.

Static Files such as stylesheets and images have been consolidated into the top-level static/ directory for easy reference via /static/... paths in HTML.

::::::::::::::
Application Details
::::::::::::::

app.py — Application Factory
----------------------------

app.py is located in the project root. It is the main application "factory". It creates the connection to the
database. The connect string should be modified to match the password used by the default user (www-data) and 
if necessary the name of the database itself - the default is "flask":

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://www-data:Your_Flask_Password_Here@localhost:5432/flask'

The Key configuration, "SECRET_KEY", should also be changed to something unique.

app.py is where the login tandem, flask-htpasswd and flask-login, site and individual access respectively, 
are set up. In this section flask-sqlalchemy is initialised and the two top level models needed for user
authentication (Users and Login) are imported.

The FLASK_SECRET key should be changed to something unique:
app.config['FLASK_SECRET'] = 'Shock The Code Monkey'

Flask is extensible via the additional of "blueprints".
There is a section in app.py that registers the blueprints, any custom blueprints 
should be registered, and their routes imported, in this section, 

"Advance", referenced in this section refers to the subdirectory where the four clinical charting blueprints
are found:

from auth import auth as auth_blueprint
app.register_blueprint(auth_blueprint)
from Advance.admin.routes import admin
...
app.register_blueprint(admin, url_prefix='/admin')


The last section of app.py is a session timeout section. The default
session timeout limit is 240 minutes (4 hours). The time out threshold can 
be modified as needed and the connect string for psycopg2 SHOULD be modified
in the same manner as the SQLALCHEMY DATABASE URI - updating the password 
and, if necessary, the name of the database:

@app.before_request
def check_session_timeout():
    timeout_threshold = 240
    conn = psycopg2.connect("dbname='flask' user='www-data' host='localhost' password='Your_Flask_Password_Here'")

The "@app.before_request" call ensures that the session timeout threshold is checked each time a route is called.

auth.py — Authentication Blueprint
----------------------------------

auth.py is a Flask blueprint located in the project root alongside app.py. It handles user authentication for the SQL Advance application.

Purpose:
- Enforces site-wide access control via .htpasswd
- Authenticates individual users with the Flask login manager
- Provides routes for login, logout, and session management

Site Authentication with .htpasswd

The root route ("/") is protected with @htpasswd.required, which checks visitor credentials against Apache’s .htpasswd file:

    @htpasswd.required
    def index():
        ...

Only users who pass this check can proceed to Flask-level login.

Flask Login Authentication

After .htpasswd validation, the user is prompted to log in using their individual credentials stored in the database.

The login route accepts a GET form submission, encrypts the user-provided password, and verifies it with Werkzeug:

    from werkzeug.security import check_password_hash
    passwd = check_password_hash(user.password, password)

If authentication succeeds, the user is redirected to /index.

User Dashboard Logic

The /index route displays a summary of unread and read messages for the user:

    from sqlalchemy import text

    sth = text("SELECT COUNT(*) FROM message WHERE receiver_id = :user_id AND (read IS NULL OR read = false)")
    unseen = db.session.execute(sth, {"user_id": current_user.user_id}).scalar()

    sth = text("SELECT COUNT(*) FROM message WHERE receiver_id = :user_id AND read = true")
    seen = db.session.execute(sth, {"user_id": current_user.user_id}).scalar()

All routes in SQL Advance are protected by @login_required.

Login Logging with psycopg2

Successful logins are logged to the flask database using psycopg2. Be sure to update the password in the connection string to match your Postgres setup:

    conn = psycopg2.connect("dbname='flask' user='www-data' host='localhost' password='Your_Flask_Password_Here'")

Additional Routes

- logout: Logs the current user out of the session.
- logout_all: Logs out all users (by clearing session table or related data — depending on implementation).

advance_helpers.py - module containing reusable functions ("helpers")
------------------------------------------------------------

advance_helpers.py is found in the projec root, alongside app.py.
The file at present:

# advance_helpers.py

from flask import session, render_template
from advance_models import Users
from sqlalchemy import select
from app import db
import re

def get_unit(record):
    """
    Splits a unit_lname string on '--', strips whitespace, and returns an error page if invalid.
    Args: record (str): The input string from the dropdown menu.
    Returns: tuple: (unit_id, unit_lname) if valid, or a rendered error page if not.
    """
    if not record:
        return render_template("error.html", error_str="No unit selected.")

    parts = re.split(r'--', record)
    if len(parts) != 2:
        return render_template("error.html", error_str=f"Invalid unit format: '{record}'. Expected 'ID -- Name'.")

    return parts[0].strip(), parts[1].strip()

def get_user():
    return Users.query.filter_by(username=session["username"]).first()

def secure_remove():
    """Return None if allowed, or rendered error template if not."""
    username=session["username"]
    sth = select(Users.update).where(Users.username == username, Users.remove == 'Yes')
    allowed = db.session.execute(sth).scalar()
    if not allowed:
        error = "Removing a clinical record requires a Remove Privilege."
        return render_template('error.html', error=error)

def secure_update():
    """Return None if allowed, or rendered error template if not."""
    username=session["username"]
    sth = select(Users.update).where(Users.username == username, Users.update == 'Yes')
    allowed = db.session.execute(sth).scalar()
    if not allowed:
        error = "Modifying a clinical record requires an Update Privilege."
        return render_template('error.html', error=error)

def secure_admin():
    """Return None if allowed, or rendered error template if not."""
    username=session["username"]
    sth = select(Users.trust).where((Users.username == username) & (Users.track == 'admin'))
    allowed = db.session.execute(sth).scalar()
    if not allowed:
        error = "This function is reserved for administrative staff."
        return render_template('error.html', error=error)

def secure_trust():
    """Return None if allowed, or rendered error template if not."""
    username=session["username"]
    sth = select(Users.trust).where((Users.username == username) & (Users.trust == 't'))
    allowed = db.session.execute(sth).scalar()
    if not allowed:
        error = "This function is reserved for IT staff."
        return render_template('error.html', error=error)

Advance/chart/routes.py - functions called in the individual routes
-------------------------------------------------------------------

admin/routes.py:

from advance_helpers import get_unit, get_user, secure_update, secure_trust

#   WITH advance_helpers.py MODULE ("helper" file) REPLACT THIS:
#
#   username = session["username"]
#   sth = select(Users.user_id, Users.fullname).where(Users.username == username)
#   results = db.session.execute(sth).fetchone()  # Fetch a single result as a tuple
#   if results:
#       user_id, fullname = results  # Unpack tuple
#   else:
#       user_id, fullname = None, None

#   WITH THIS:

    user = get_user()
    fullname = user.fullname
    user_id = user.user_id

#   REPLACE THIS:

#   username=session["username"]
#   sth = select(Users.user_id, Users.username, Users.track, Users.trust).where(Users.username == username).where(Users.trust == 't')
#   results = db.session.execute(sth)
#   counter = len(results.all())
#   if counter == 0:
#       error = "This function is reserved for IT staff."
#       return render_template('error.html', error=error)
#
#   else:

#   WITH THIS:

    trust_check = secure_trust()
    if trust_check:
        return trust_check

    if request.method == "POST":
        user = Users(username=request.form.get("username"),
                     password=request.form.get("password"),
                     fullname=request.form.get("fullname"),
    ...

chart.routes.py:

from advance_helpers import get_unit, get_user, secure_remove, secure_update, secure_admin

    # REPLACE THIS:
    # 
    # username=session["username"]
    # sth = select(Users.update).where(Users.username == username).where(Users.update == 'Yes')
    # update_results = db.session.execute(sth)
    # counter = len(update_results.all())
    # if counter == 0:
    #     error = "Modifying a clinical record requires an Update Privilege."
    #     return render_template('chart.html', error=error)

    # WITH THIS:
    update_check = secure_update()
    if update_check:
        return update_check

    # REPLACE THIS: 
    # 
    # username=session["username"]
    # user = Users.query.filter_by(username=username).first()
    # Using advance_helpers.py in the project root for "helpers" (subroutines)
    # WITH THIS: 

    user = get_user()
    fullname = user.fullname
    staff_id = user.user_id

    # REPLACE THIS:
    #
    # username=session["username"]
    # sth = select(Users.update).where(Users.username == username).where(Users.update == 'Yes')
    # update_results = db.session.execute(sth)
    # counter = len(update_results.all())
    # if counter == 0:
    #     error = "Modifying a clinical record requires an Update Privilege."
    #     return render_template('chart.html', error=error)

    # WITH THIS:
    update_check = secure_update()
    if update_check:
        return update_check

    # REPLACE THIS:
    # username=session["username"]
    # sth = select(Users.track, Users.trust).where(Users.username == username).where(Users.track == 'admin')
    # results = db.session.execute(sth)
    # counter = len(results.all())
    # if counter == 0:
    #     flash('Access Is Restricted To Administrative Staff','Warning')
    #     return render_template('chart.html')
    # else:
    # WITH THIS:

    admin_check = secure_admin()
    if admin_check:
        return admin_check

Blueprints in the Advance directory
----------------------------------

Blueprint: chart.routes.py
Location: Advance/chart/routes.py

1. Purpose

This blueprint manages routes related to clinical charting including functional assessments, support (treatment) plans and progress notes. It is part of the modular structure of the SQL Advance application, enabling separation of concerns and easier maintenance. This file contains the heart of the EHR and as such is large. It is also extensively annotated to help others follow the logic.

2. Registration
The chart blueprint is registered in app.py as:

    from Advance.chart.routes import chart
    app.register_blueprint(chart, url_prefix='/chart')

3. Route Overview

Route                    Purpose
----------------------------------------------------------
/chart/                  Menu for Chart routes
/chart/clients           Report - list all clients in the Master Patient Index (MPI)
/chart/occupancy         Occupancy Report - list all clients currently enrolled in a program
/chart/unit_roster       Report - list clients in clients enrolled in an individual unit
/chart/unit_roster_dict  Report - client unit roster in dictionary style

Route                    Methods  Purpose
----------------------------------------------------------
/chart/client_add        GET POST Add a new client to Master Patient Index (mpi)
/chart/client_update     GET POST Update an MPI record (includes Delete option)
/chart/client_chart      GET POST List all clinical documents for selected client
/chart/client_chart_all  GET POST List all clinical documents for all clients
/chart/functional        GET POST Add a Functional Assessment for a client
/chart/functional_update GET POST Update a Functional Assessment for a client
/chart/functional_view   GET POST View all Functional Assessment records for a client
/chart/inpatient         GET POST Enter an Inpatient admission for a client
/chart/inpatient_update  GET POST Update an Inpatient admission for a client
/chart/inpatient_view    GET POST View an Inpatient admission for a client
/chart/placement         GET POST Add a program enrollment for a client
/chart/placement_update  GET POST Update a program enrollment for a client
/chart/placement_view    GET POST View a program enrollment history for a client
/chart/progress_note     GET POST Enter a Progress Note for a client
/chart/progress_update   GET POST Update a Progress Note for a client
/chart/progress_view     GET POST View a Progress Note for a client
/chart/support_plan      GET POST Enter a Support (Treatment) Plan for a client
/chart/support_update    GET POST Update a Support (Treatment) Plan for a client
/chart/support_view      GET POST View a Support (Treatment) Plan for a client
/chart/trust             A database query used to establish credentials for deleting a record

4. Blueprint Headers / Footers

routes.py (blueprint) use directives (from Advance/chart/routes.py)
------------------------------------

from flask import Flask, Blueprint, abort, flash, redirect, render_template, request, session, url_for
from flask_login import login_required
from datetime import datetime, timedelta
from sqlalchemy import select, func, and_, desc, text
import re

from app import db
from advance_helpers import get_user, parse_unit
from advance_models import Users
from Advance.admin.units import Unit
from .clients import (
    MPI, Functional, FunctionalSkills, Inpatient,
    Placement, ProgressNote, SupportPlan
)

chart=Blueprint('chart', __name__,template_folder='templates',static_folder='static')

... individual routes ...


app=Flask(__name__)

app.register_blueprint(chart)
if __name__=='__main__':
    app.run(debug=True)

if False: 
    """
    Some of the routes are very large and could be refactored and this will happen at a later date. 
    For this release the goal is to transition from Perl to Python in a way that's readable and 
    show how Flask can be used to do what Perl CGI did in its heyday.
    """

5. Authentication

All routes are protected by @login_required, ensuring that only authenticated users can access them.
Additional access control is used: updating and removal of clinical records requires respective privileges (Update/Remove);

6. SQL Alchemy & Models
This blueprint uses a Core / ORM / Raw SQL / hybrid approach. It is more complex than the other three blueprints.
chart blueprint uses the clients.py model which has table definitions for all clinical records. Here are examples of the
query types (extracted from ./Advance/chart/routes.py):

    # Hybrid SQLAlchemy style — using Core's select() with ORM models and session-based execution.
    # Note: This uses SQLAlchemy 1.4’s unified query interface where select() is from Core, 
    # but ORM-mapped models are used as tables. db.session calls are ORM.
    # The class-qualified fields (e.g., MPI.lname) act like SQL table aliases, keeping things explicit.
    sth = select(MPI.client_id, MPI.lname, MPI.fname, MPI.dob, MPI.ssn).select_from(MPI)
    results = db.session.execute(sth)
    return render_template('clients.html', clients = results)

    # Pure ORM style — using model instantiation and commit without raw SQL or select().
            mpi = MPI(fname=request.form.get("fname"),
                      lname=request.form.get("lname"),
                      dob=request.form.get("dob"),
                      ssn=request.form.get("ssn"),
                      medicaid=request.form.get("medicaid"))

            db.session.add(mpi)
            db.session.commit()

    # SQL Alchemy ORM Style - SELECT queries:
    # Note: query().filter().first() fetches only the first matching result (not all()
            client = db.session.query(MPI).filter(MPI.client_id == client_id).first()
            return render_template('client_update.html', client_id=client_id, client=client)


    # SQL Alchemy ORM Style - UPDATE queries (DML subset of SQL):
            client_update = db.session.query(MPI).filter(MPI.client_id == client_id).first()

            if client_update:

                client_update.fname    = request.form.get("fname")
                client_update.lname    = request.form.get("lname")
                client_update.dob      = request.form.get("dob")
                client_update.ssn      = request.form.get("ssn")
                client_update.medicaid = request.form.get("medicaid")

                db.session.commit()

    # SQL Alchemy ORM Style with Core component (desc)
    # This query uses a SQL JOIN, "filter" (WHERE clause), ORDER BY
    # and ".all" which equates to 'SELECT * FROM ...'
            places  = (
                db.session.query(Placement, MPI.fname, MPI.lname)
                .join(MPI, Placement.client_id == MPI.client_id)
                .filter(Placement.client_id == client_id)
                .order_by(MPI.lname, MPI.fname, desc(Placement.start_date))
                .all()
            )

    # SQL Alchemy ORM Style with a more complex ORDER BY clause.
    # Order by client name (lname, fname) then show latest document first (start_date DESC)
    functs  = (
        db.session.query(Functional, MPI.fname, MPI.lname)
        .join(MPI, Functional.client_id == MPI.client_id)
        .order_by(MPI.lname, MPI.fname, desc(Functional.start_date))
        .all()
    )

    # SQL Alchemy ORM Style with Core component (and_) - returns an array of support plans
    # for an individual client:
    if request.method == "GET":

        client_id = request.args.get("client_id")
        rec_id = request.args.get("rec_id")

        if client_id and rec_id:
            resident = db.session.query(MPI.fname, MPI.lname).filter(MPI.client_id == client_id).first()
            query = db.session.query(SupportPlan).filter(and_(
                SupportPlan.client_id == client_id,
                SupportPlan.rec_id == rec_id
            ))
            results = query.all()

            return render_template('support_view.html', plans=results, client_id=client_id, resident=resident)

    # SQL Alchemy ORM Style - INSERT query:
        funct = Functional(
            client_id=request.form.get("client_id"),
            unit_id=unit_id,
            unit_lname=unit_lname,
            staff_id=staff_id,
            staff_name=fullname,
            start_date=request.form.get("start_date"),
            end_date=request.form.get("end_date"),
            skill_level=request.form.get("skill_level"),
            skill_area=request.form.get("skill_area"),
            skill_text=request.form.get("skill_text"),
            time_in=datetime.now()
        )
        db.session.add(funct)
        db.session.commit()

    # SQL Alchemy ORM | Python sorted() function to order by rec_id:
    query_results = db.session.query(FunctionalSkills).all()
    skills_list = [skill for skill in query_results]
    sorted_skills = sorted(skills_list, key=lambda skill: skill.rec_id)

    # SQL Alchemy ORM style - returning one record per query: 
            record = db.session.query(Functional).filter(Functional.rec_id == rec_id).first()
            client = db.session.query(MPI.fname, MPI.lname).filter(MPI.client_id == record.client_id).first()
            uname  = db.session.query(Unit.unit_lname).filter(Unit.unit_id == record.unit_id).first()

    # SQL Alchemy ORM style - the delete condition is a form embedded in a Jina template used for UPDATE queries: 
                if delete_condition:  # non-empty value triggers deletion ("delete" is a hidden field)
                    db.session.delete(functional_update)
                    db.session.commit()
                    return render_template('chart.html')

    # SQL Alchemy Hybrid Style using Core components (select and func):
    sth = select(MPI.client_id, MPI.fname, MPI.lname).select_from(MPI)
    clients = db.session.execute(sth)
    today = db.session.query(func.current_date()).scalar()

    # SQL Alchemy ORM style - query embedded in call to render template:
            return render_template('inpatient.html', client=client, startdate=today, units=Unit.query.all(), fullname=fullname)

    # SQL Alchemy ORM style with formatted string function - f""

            client_row = db.session.query(MPI.fname, MPI.lname).filter(MPI.client_id == client_id).first()
            client = f"{client_row[0]} {client_row[1]}" if client_row else None

    # RAW SQL style using text, pretty close to Perl DBI:
    # Note: .fetchall() returns a list of Row objects, like DBI’s fetchall_arrayref, for Perl  hashrefs use .mappings()
    query = text('SELECT x.*, y.* FROM MPI x, Placement y WHERE x.client_id = y.client_id AND y.end_date IS NULL ORDER BY y.unit_lname, x.lname, x.fname')
    result = db.session.execute(query).fetchall()

    # SQL Alchemy Hybrid Style - uses select to assess user privileges:
    username=session["username"]
    user = Users.query.filter_by(username=username).one_or_none()
    user or abort(403)
    fullname = user.fullname
    staff_id = user.user_id
    sth = select(Users.update).where(Users.username == username, Users.update == 'Yes')
    allowed = db.session.execute(sth).scalar()

    if not allowed:
        error = "Modifying a clinical record requires an Update Privilege."
        return render_template('chart.html', error=error)

    # Using advance_helpers.py in the project root for "helpers" (subroutines)
    username=session["username"] # preserved because it is used later in the route.
    # user = Users.query.filter_by(username=username).first() is replaced
    # using advance_helpers.py in the project root for "helpers" (subroutines)
    user = get_user()
    fullname = user.fullname
    staff_id = user.user_id

    # SQL Alchemy ORM syntax querying specific columns rather than full model instances
    # Note - the query looks for active records (NULL end_date).
            place = (
                db.session.query(Placement.unit_id, Placement.unit_lname)
                .filter(and_(MPI.client_id == client_id, Placement.end_date.is_(None)))
                .first()
            )
            if place:
                place_str = f"{place.unit_id} -- {place.unit_lname}"
            else:
                place_str = "0 -- No current placement"

     # SQL Alchemy ORM using a for loop to append / push() - goals onto a list / array
     # Goals will be used to populate a dropdown: users select a billable service or simple case note:
            goal_fields = ["goal_one_header", "goal_two_header", "goal_three_header", "goal_four_header"]
            goals = []
            support_plans = SupportPlan.query.filter(SupportPlan.client_id == client_id).all()

            for plan in support_plans:
                for field in goal_fields:
                    goal_header = getattr(plan, field, None)  # Dynamically get goal field
                    if goal_header:  # Only add if it exists
                        goals.append({"rec_id": plan.rec_id, "goal_header": goal_header})

    # SQL Alchemy ORM using Core components (and_) to form a WHERE predicate:
            query = db.session.query(ProgressNote).filter(and_(ProgressNote.client_id == client_id,
                                                               ProgressNote.notedate >= start_date, 
                                                               ProgressNote.notedate <= end_date))
            results = query.all()

    # SQL Alchemy ORM using Core components (func) to emulate a Perl DBI nested subquery
    # (Note: scalar_subquery() creates a correlated subquery usable within a .filter() clause):
            latest_plan_subquery = db.session.query(func.max(SupportPlan.rec_id)).filter(SupportPlan.client_id == client_id).scalar_subquery()
            support_plans = SupportPlan.query.filter(SupportPlan.rec_id == latest_plan_subquery).all()

    # The mode variable (set to 'view') tells the Jinja template to display an electronic signature.
    # If mode is set to print in the url (or undef) text signature lines will replace the ESOF in the template.
    # From the template: <a href="/chart/progress_view?mode=view">View Progress Note</a>
    # From the template: <a href="/chart/progress_view?mode=print">Print Progress Note</a>
    mode = request.args.get('mode', 'view')

    # RAW SQL Style - using named parameters (similar to variable binding) and mappings to emulate a hashref
                    for level_col, status_col, object_col, header_col in goal_fields:
                        sql = f"""
                            SELECT {level_col} AS level, {status_col} AS status, {object_col} AS object
                            FROM support_plan
                            WHERE client_id = :client_id
                              AND rec_id = :rec_id
                              AND {header_col} = :pattern
                        """
                        result = db.session.execute(
                            text(sql),
                            {
                                'client_id': note.client_id,
                                'rec_id':    note.plan_id,
                                'pattern':   note.pattern
                            }
                        ).mappings().first()

    # This stanza sets skill level and goal status for support plan goals - assigning an empty str if None
                        if result:
                            note.level = result['level']
                            note.objective = result['object']
                            break  # Stop once a match is found for any goal
                        else:
                            note.level = ''      # Default if no match is found
                            note.objective = ''  # Default if no match is found

    # plan is a mapped SQLAlchemy object.  After the commit, plan.rec_id gets 
    # populated with the value assigned by the database SEQUENCE
    # mimicking a RETURNING (rec_id) clause in raw SQL.
    rec_id = plan.rec_id
    if rec_id:
    # This stanza creates a unique plan-goal id for four fields in the table.
    # This id will be used later in the Progress Note route.
        plan.goal_one_id   = f"{rec_id}-1"
        plan.goal_two_id   = f"{rec_id}-2"
        plan.goal_three_id = f"{rec_id}-3"
        plan.goal_four_id  = f"{rec_id}-4"
        db.session.commit()
            
    # RAW SQL Style - using SQL Alchemy's text function with named params 
    # (similar to variable binding) and mappings to emulate a hashref
        if unit_lname:
            query = text("""
                SELECT x.*, y.*
                FROM MPI x
                JOIN Placement y ON x.client_id = y.client_id
                WHERE y.end_date IS NULL
                AND y.unit_lname = :unit_lname
                ORDER BY y.unit_lname, x.lname, x.fname
            """)
            result = db.session.execute(query, {'unit_lname': unit_lname}).mappings().all()

7. Forms

The chart.routes.py blueprint has specific forms for entering and updating the clinical chart including
MPI records, placement (program enrollment), recidivism records (inpatient stays) and
the core clinical documents: functional assessments, support plans and progress notes. The naming
convention for the routes is straightforward, e.g., support_plan for data entry, support_update
for updates and deletions, and, support_view for viewing or printing records. The forms proper are found
in Advance/chart/templates. The forms are rendered by the jinja2 engine and the templates use the
requisite code - jinja has a very nice API.

::::::::::::::
Appendix: FOR Perl CGI Users
::::::::::::::

Perl CGI can coexist with Flask on the same server, optionally running on a separate port for SSL support.

To enable CGI in the default root, modify /etc/apache2/apache2.conf:

Original:

<Directory /var/www/>
    Options Indexes FollowSymLinks
    AllowOverride None
    Require all granted
</Directory>

Modified:

<Directory /var/www/>
    Options All ExecCGI
    AllowOverride All
    Allow from all
    Require all granted
    AddHandler cgi-script .cgi .pl .py
</Directory>

To run CGI on a different port, create /etc/apache2/sites-available/perl.conf:

<VirtualHost *:8443>
    ServerName legacy.hostname.org
    DocumentRoot /var/www/html/

    SSLEngine on
    SSLCertificateFile /etc/ssl/certs/ssl-cert-snakeoil.pem
    SSLCertificateKeyFile /etc/ssl/private/ssl-cert-snakeoil.key

    ErrorLog ${APACHE_LOG_DIR}/perl-error.log
    CustomLog ${APACHE_LOG_DIR}/perl-access.log combined
</VirtualHost>

Tell Apache to listen on that port by adding to /etc/apache2/ports.conf:

<IfModule ssl_module>
    Listen 443
    Listen 8443
</IfModule>

Enable the site and restart Apache:

    $ sudo a2ensite perl.conf
    $ sudo systemctl restart apache2

