Hospital Shift Scheduling System
Final Project – IA637 Data-Driven Application Development

Authors: Ashwins Njolomola, Conrad Mutendera

---

1. Overview

The Hospital Shift Scheduling System is a user-friendly web application designed to make managing hospital staff schedules simpler, clearer, and more efficient. Built with Flask and MySQL, the system brings together all the essential pieces of workforce management employees, departments, shifts, attendance, and administrative review—into one organized platform. Instead of relying on manual scheduling or scattered communication, hospitals can use this system to assign shifts, track who is working, and ensure that coverage is always properly balanced across departments.

Employees have an easy way to stay informed about their workload. They can view upcoming shifts, request assignments, clock in and out with built-in time protections, and provide explanations when they are early or late. For administrators, the system offers a complete set of tools for reviewing attendance issues, approving requests, issuing warnings when necessary, and keeping schedules up to date. In addition to daily workflow management, the system also provides meaningful insights into hospital operations through Matplotlib-generated analytics, such as approval rates, staff workload trends, and hours worked.

Behind the scenes, the application uses a custom ORM-style structure (baseObject.py) to ensure reliable CRUD operations and enforce business logic across all tables. With multiple related database tables, secure authentication, validated inputs, and carefully designed SQL JOIN queries, the project fully meets the requirements for IA637. Overall, the system is built to be both practical and intuitive reducing administrative burden while helping hospitals maintain smooth, well-coordinated staffing operations.

---

2. Users and Roles

Administrator

Admins can:

• Manage users, departments, shifts, and schedules
• Approve or reject shift requests
• Review early/late clock-outs
• Issue warnings to employees
• View analytics (hours worked, shift volume, approval rates)

Employee

Employees can:

• View available shifts for their department
• Request shifts
• View their personal schedule
• Clock in (within a strict allowed time window)
• Clock out (with mandatory reason if early or late)
• View warnings issued by admin

Entity–Relationship Diagram (ERD)

Database schema showing relationships between users, departments, shifts, schedules, and warnings.

---

3. Demo Accounts

All demo accounts use password 123 (hashed internally on insert).

The screenshot above shows all current demo users available for testing. Additional demo users may be inserted using init.py, test.py, or test_user.py if needed.

Click to expand Analytical SQL Queries

These SQL queries power the analytics dashboard used by hospital administrators.
They summarize shift patterns, workload, performance, and operational activity.

---

Click to expand Analytical SQL Queries

These SQL queries power the analytics dashboard used by hospital administrators.
They summarize shift patterns, workload, performance, and operational activity.

---

1. Daily Shift Volume

SELECT DATE(starttime) AS shift_date, COUNT(*) AS total_shifts FROM schedule s JOIN shift sh ON s.sid = sh.sid GROUP BY shift_date ORDER BY shift_date;

2. Shift Approval Rates

SELECT status, COUNT(*) AS total FROM schedule GROUP BY status;

3. Popular Shift Types (by duration)

SELECT TIMESTAMPDIFF(HOUR, sh.starttime, sh.endtime) AS shift_hours, COUNT(*) AS frequency FROM schedule s JOIN shift sh ON s.sid = sh.sid GROUP BY shift_hours ORDER BY frequency DESC;

4. Monthly Hours Worked per Employee

SELECT u.name, DATE_FORMAT(s.clockin, '%Y-%m') AS month, SUM(TIMESTAMPDIFF(HOUR, s.clockin, s.clockout)) AS total_hours FROM schedule s JOIN users u ON s.uid = u.uid WHERE s.clockin IS NOT NULL AND s.clockout IS NOT NULL GROUP BY u.name, month ORDER BY month, u.name;

5. Weekly Hours Worked per Employee

SELECT u.name, YEAR(s.clockin) AS year, WEEK(s.clockin) AS week_number, SUM(TIMESTAMPDIFF(HOUR, s.clockin, s.clockout)) AS total_hours FROM schedule s JOIN users u ON s.uid = u.uid WHERE s.clockin IS NOT NULL AND s.clockout IS NOT NULL GROUP BY u.name, year, week_number ORDER BY year, week_number;

6. Average Hours Worked per Employee

SELECT u.name, AVG(TIMESTAMPDIFF(HOUR, s.clockin, s.clockout)) AS avg_shift_hours FROM schedule s JOIN users u ON s.uid = u.uid WHERE s.clockin IS NOT NULL AND s.clockout IS NOT NULL GROUP BY u.uid, u.name ORDER BY avg_shift_hours DESC;

7. Employees with Repeated Early/Late Clockouts

SELECT u.name, SUM(CASE WHEN s.status LIKE 'EARLY%' THEN 1 ELSE 0 END) AS early_count, SUM(CASE WHEN s.status LIKE 'LATE%' THEN 1 ELSE 0 END) AS late_count FROM schedule s JOIN users u ON s.uid = u.uid GROUP BY u.name ORDER BY early_count DESC, late_count DESC;

8. Total Warnings Issued per Employee

SELECT u.name, COUNT(w.wid) AS warnings_issued FROM warnings w JOIN users u ON w.uid = u.uid GROUP BY u.uid, u.name ORDER BY warnings_issued DESC;

---

4. Core Features

4.1 Authentication

• Login endpoint: /login
• Password hashing (user.hashPassword())
• Session timeout using checkSession()
• Logout endpoint: /logout

---

4.2 CRUD Operations (Admin)

Each CRUD flow uses routes like:

• /users/manage
• /department/manage
• /shift/manage
• /schedule/manage

Each entity inherits from baseObject and defines:

• verify_new()
• verify_update()

Validated fields include:

• Non-empty department names
• Valid times for shifts
• Matching passwords
• Unique email addresses
• Allowed roles only (admin, employee)

---

4.3 Employee Shift Requests

Route: /employee/request_shift

Features:

• Loads selected shift via sid
• Prevents overlapping approved shifts
• Creates a new schedule record with status="PENDING"

---

4.4 Clock-In Logic

Route: /employee/clockin?scid=...

Checks include:

• Schedule exists
• User not already clocked in
• Current time is inside a strict 10-minute window before shift start
• Saves clockin timestamp

---

4.5 Clock-Out Logic

Route: /employee/clockout?scid=...

Three cases:

1. On time → Saves clockout
2. Early → Redirects to /employee/late_reason?reason_type=early
3. Late → Redirects to /employee/late_reason?reason_type=late

Recorded reason is saved in:

• early_reason
• late_reason

and status is set to:

• EARLY-PENDING
• LATE-PENDING

---

4.6 Admin Early/Late Review

Routes:

• /admin/late_early_review
• /admin/late_early_approve
• /admin/late_early_override
• /admin/late_early_warning

All early/late records appear with:

• User name
• Department name
• Clockin / clockout times
• Submitted reason

Admins can approve, override, or issue warnings.

---

4.7 Employee Warnings

Route: /employee/warnings

Pulls entries from the warnings table:

• wid
• scid
• warning_message
• created_at

---

4.8 Admin Analytics Dashboard

Route: /admin/analytics

The system automatically generates analytical insights for hospital operations. Below are the chart outputs produced using Matplotlib.

---

Shift Approval Rates

Popular Shift Types

Monthly Hours per Employee

Weekly Hours per Employee

Average Hours Worked

---

5. Database Schema

tables:
  user: 'users'
  department: 'department'
  schedule: 'schedule'
  shift: 'shift'

Entity–Relationship Diagram (ERD)

The diagram below shows the relational structure of the system, including users, departments, shifts, schedules, and the warnings table which is logically linked to attendance records.

Additional table used:

• warnings (direct SQL interaction)

---

6. Project Structure

<h2 id="project-structure">7. Project Structure</h2>

The repository is organized as a small but complete Flask application, with a clear separation between:

- **Application logic** (`app.py` + model classes)  
- **Templates** (under `templates/`)  
- **Static assets and generated charts** (under `static/`)  
- **One-off utilities and test/seed scripts**  

<details>
<summary><b>Top-level layout</b></summary>

```text
APP_TEMPLATE_PINNED/
├── app.py
├── baseObject.py
├── user.py
├── department.py
├── shift.py
├── schedule.py
├── init.py
├── test.py
├── test_user.py
├── config.yml.example
├── requirements.txt
├── Procfile
├── README.md
│
├── templates/
│   ├── base.html
│   ├── login.html
│   ├── loginbase.html
│   ├── main.html
│   ├── ok_dialog.html
│   ├── users/
│   │   ├── add.html
│   │   ├── list.html
│   │   └── manage.html
│   ├── department/
│   │   ├── add.html
│   │   ├── list.html
│   │   └── manage.html
│   ├── shift/
│   │   ├── add.html
│   │   ├── list.html
│   │   └── manage.html
│   ├── schedule/
│   │   ├── add.html
│   │   ├── list.html
│   │   └── manage.html
│   ├── employee/
│   │   ├── active_shift.html
│   │   ├── late_reason.html
│   │   ├── schedule.html
│   │   ├── shifts.html
│   │   └── warnings.html
│   └── admin/
│       ├── analytics.html
│       ├── late_early_review.html
│       └── requests.html
│
├── static/
│   ├── charts/          # PNG charts generated by schedule.py
│   ├── analytics        # (helper data / placeholder for analytics)
│   ├── style.css        # Global styling for templates
│   └── test.txt         # simple test asset (for static routing)
│
└── flask_session/       # runtime session files (created by Flask-Session)

---
<h2 id="setup">7. Setup Instructions</h2> <h3>7.1 Configure Database</h3>

Create `config.yml`:

```yaml
db:
  user: 'root'
  pw: 'password'
  host: 'localhost'
  db: 'ia637_project'

tables:
  user: 'users'
  department: 'department'
  schedule: 'schedule'
  shift: 'shift'

---

7.2 Install Dependencies

pip install -r requirements.txt

7.3 Run Server

python app.py

Open:

http://localhost:5000/

Click to expand Analytical SQL Queries

---

8. Conclusion

In summary, the Hospital Shift Scheduling System brings together everything required to support a smooth and reliable hospital staffing workflow. It combines clear Flask routing, clean Jinja-driven interfaces, and a custom CRUD framework to create an application that is both organized and easy to maintain. The system handles real operational challenges—such as managing shift requests, tracking attendance, and reviewing early or late departures—through thoughtfully designed business logic. Its multi-table SQL structure ensures accurate data relationships, while the analytics dashboard provides meaningful insights that can help administrators make informed staffing decisions