Legacy Attendance — Deployment Package

Adds a read-only "Legacy Attendance" sidebar item: dashboard + records list + Excel export, sourced LIVE from the OLD remote MySQL server (contract / employee / locations / records tables). No import, no writes to the remote DB, nothing copied into the local database.

Apply this same package to both LT and GOV (each server uses its own REMOTE_DB_* values in its own .env).

1. Files in this package

NEW files (just drop in — no existing file touched)

File	Purpose
`legacy_attendance_service.py`	Read-only pymysql data access to the legacy DB (queries, pagination, Excel builder)
`routes/legacy_attendance.py`	Blueprint `legacy_attendance` — dashboard, records, export routes
`templates/legacy_attendance_dashboard.html`	Dashboard page
`templates/legacy_attendance_records.html`	Records list (matches the Time Attendance table layout)
`tools/migration_legacy_attendance_remote_indexes.py`	One-time index migration for the REMOTE legacy DB

MODIFIED files (replace existing)

File	What changed
`config.py`	Added `REMOTE_DB_HOST/PORT/USERNAME/PASSWORD/NAME` to `Config`
`app.py`	Registered the `legacy_attendance` blueprint
`templates/base_authenticated.html`	Added "Legacy Attendance" sidebar link (admin + payroll/accounting sections)

Nothing was removed or renamed. Existing routes/functions/variables untouched.

2. .env additions (BOTH servers)

Add these to .env on each server, using that server's own legacy DB credentials:

# Remote MySQL Server Configuration (Source — legacy attendance)
REMOTE_DB_HOST=xxx.xxx.xxx.xxx
REMOTE_DB_PORT=3306
REMOTE_DB_USERNAME=xxx
REMOTE_DB_PASSWORD=xxx
REMOTE_DB_NAME=xxx

If these are left blank, the Legacy Attendance pages show a clean "legacy database is not configured" flash message instead of erroring.

3. One-time index migration (BOTH servers)

The legacy schema ships with no useful indexes, which makes the live queries full-scan and can trip the gunicorn worker timeout at scale. Run once per server (safe to re-run — skips indexes that already exist; only adds indexes, never touches data):

python3 tools/migration_legacy_attendance_remote_indexes.py

Expected first-run output: [ADD] lines for 6 indexes on records / employee / locations, then [DONE].

Note: on a fresh legacy DB dump these indexes may already be present (a recent dump already includes them). In that case every line prints [SKIP] — that's fine.

4. Deploy steps (per server)

Copy the NEW files into place.
Replace the 3 MODIFIED files.
Add the REMOTE_DB_* block to .env.
python3 tools/migration_legacy_attendance_remote_indexes.py
Restart gunicorn.
Log in, open Legacy Attendance in the sidebar, confirm the dashboard stats populate and the records list shows Location Name + Event Description.

No local DB migration is required — this feature reads the remote DB only.

5. Key facts worth remembering

records.locationId holds the numeric locations.index (NOT the location name). The join is locations.index = CAST(records.locationId AS UNSIGNED).
records.employeeId is varchar; joined as employee.id = CAST(records.employeeId AS UNSIGNED).
records.type values are CHECK IN / CHECK OUT.
Records list column mapping: ID=employeeId, Name=Last, First, Platform=Manual/— (from isManual), Date/Time from time, Location Name=locations.location, Action Description=type badge, Event Description=locations.address, Recorded Address=records.recordedAddress.
Read-only by design: no detail page, no delete (so no Actions column).

3.7 KiB Raw Permalink Blame History