# Medmecs MIS — Collection Dashboard Platform

Pure-PHP 8 + MySQL 8 reporting platform with a dedicated **Collection Dashboard**: ingests raw EHR exports, joins facility mapping by `visit_id`, applies a year-aware fee schedule for expected payments, and renders comprehensive analytics — every dashboard has a matching Excel export (same colours, totals, supporting data).

**No Laravel. No Composer. No build step.** Drop into XAMPP `htdocs`, run two batch files, sign in.

## Stack

| Layer | Choice |
|---|---|
| Backend | PHP 8.2+ (procedural with PDO) |
| Database | MySQL 8 (InnoDB + JSON columns) |
| Frontend | Bootstrap 5 + Tabler.io v1.0 (CDN) |
| Charts | ApexCharts 3.45 (CDN) |
| Tables | DataTables 1.13 with Buttons + FixedHeader + ColVis |
| Server | XAMPP Apache |
| Auth | bcrypt cost 12, IP-bound session, CSRF, audit log, rate-limited login |
| Excel reading | Pure-PHP ZipArchive + SimpleXML XLSX parser |
| Excel writing | Pure-PHP writer with HTML-matching colour styles |

## Quick start

1. Double-click **`setup.bat`** — creates `medmecs_mis`, imports core schema + collection schema + starter fee schedule.
2. Double-click **`create-htdocs-link.bat`** — junctions `C:\xampp\htdocs\medmecs` → this folder.
3. Open **`http://localhost/medmecs/login.php`**
   - Email: `admin@medmecs.local`
   - Password: `ChangeMe@2026` *(forced rotation on first login)*

## Collection Dashboard — the primary feature

```
┌──────────────────────────────────────────────────────────────────┐
│  Collection Dashboard (sidebar)                                  │
├──────────────────────────────────────────────────────────────────┤
│  Tabs: Overview · Revenue · Aging · Collection Rate · Top CPTs │
│         Carrier Mix · Unique Patients · Facility · Provider    │
│         Payment Velocity · Raw Detail                            │
│  Filters: Year, Month, Facility, Provider, Carrier              │
│  Every tab → 'Excel' button → multi-sheet workbook              │
│                with HTML-matching colours + supporting data.    │
└──────────────────────────────────────────────────────────────────┘
```

### Data flow

```
   Facility Mapping CSV ─────┐
                             ▼
                       facility_mappings  (visit_id → facility)
                             │
   Collection Raw CSV ───────┤  JOIN on visit_id
                             ▼
                       collection_raw  (43 cols + facility + expected_payment)
                             ▲
   Fee Schedule (per year) ──┤  JOIN on (charge_code, dos_year)
                             │
   sql/fee-schedule-starter ─┘  (CMS PFS allowable, 2024/25/26 starter)
```

### Atomic replace (no data gaps)

Every upload follows the same pattern:

1. Load → `*_stage` table (validate + parse + normalize)
2. Compute derived columns on stage (DOS → year/month, facility JOIN, expected payment JOIN)
3. **Only on success**, in one transaction: `TRUNCATE live; INSERT live SELECT * FROM stage`
4. If anything fails, the previous data is preserved exactly as-is

This means "old data stays until the new file is fully ingested and validated" — even if you swap in the middle of a workday, queries against the live tables never see an empty state.

### Cross-file mapping resilience

- Upload **Facility Mapping first** → mapping is ready when collection arrives.
- Upload **Collection first** → rows ingest without facility; uploading mapping later auto-re-maps all existing collection rows via `applyFacilityToCollection()`.
- Upload a new **Fee Schedule** for a year → `expected_payment` is re-derived on that year's collection rows via `applyFeeScheduleToCollection($year)`.

### Year / Month from DOS

Computed once at ingest, stored as `dos_year` / `dos_month` (INT, indexed) so every aggregation is fast — no string parsing in queries.

### Unique-patient reports

`COUNT(DISTINCT chart_number)` grouped by `facility × provider_name × dos_year × dos_month` — drives the "Unique Patients" tab and the Excel sheet of the same name.

### Excel rule

Every dashboard tab links to **`admin/export-collection.php`** with the same filter state. The output workbook has identical analyses on numbered sheets:

| Sheet | Contents |
|---|---|
| 1. Cover & KPIs | All filters + the KPI block from the Overview tab |
| 2. Revenue Monthly | Same table as Revenue tab, amber totals row |
| 3. Aging | Buckets 0-30 / 31-60 / 61-90 / 91-120 / 121+ |
| 4. Facilities | Top 50 facilities by charge |
| 5. Providers | Top 50 providers by charge |
| 6. Top CPTs | Top 100 CPTs with variance vs Expected |
| 7. Carrier Mix | Top 25 primary carriers |
| 8. Unique Patients | Facility × Provider × Month grid |
| 9. Velocity | Days-to-first-payment histogram |
| 10. Supporting Data | Up to 5 000 raw rows that feed the analyses |

Colours match the HTML palette: blue `#206BC4` headers, slate `#475569` legacy headers, amber `#FEF3C7` totals, white-on-slate section titles.

## Open-source fee schedule starter

`sql/fee-schedule-starter.sql` ships with ~70 common CPT codes for behavioural-health, internal-medicine, and outpatient settings, with Medicare-allowable amounts derived from public CMS Physician Fee Schedule reference data. Years 2024, 2025 and 2026 are pre-populated. Replace from **Fee Schedule** sidebar item whenever the client's actual contracted schedule is received — uploading for a year wipes only that year.

## Project structure

```
medmecs/
├── index.php · login.php · logout.php · change-password.php
├── dashboard.php                    main landing
├── .htaccess
├── admin/
│   ├── collection.php               ← Collection Dashboard (11 tabs)
│   ├── upload-collection.php        Replace Collection Raw (atomic)
│   ├── facility-mapping.php         Upload + browse visit_id → facility
│   ├── fee-schedule.php             Upload + browse year × CPT rates
│   ├── export-collection.php        Multi-sheet Excel matching HTML
│   ├── verticals · clients · processes  (master data CRUD)
│   ├── reports.php · report-builder.php · report-view.php · upload.php · export.php
│   ├── users · audit · login-logs · access · scheduled · notifications · settings · reference
│   ├── restore.php · impersonate.php
├── client/                          branded customer portal
│   ├── login.php · dashboard.php · report.php
├── includes/
│   ├── config.php                   EDIT: DB creds, app URL, security policy
│   ├── db.php · auth.php · csrf.php · audit.php · helpers.php
│   ├── header.php · footer.php      shared layout + dark mode + Ctrl+K palette
│   ├── xlsx-reader.php              pure-PHP XLSX parser
│   ├── xlsx-writer.php              pure-PHP XLSX writer (HTML-matching styles)
│   ├── collection-engine.php        atomic-swap ingest + cross-file re-derive
│   └── report-engine.php            generic report-builder ingest engine
├── sql/
│   ├── schema.sql                   40 core tables + RBAC + seed sysadmin
│   ├── collection-dashboard.sql     5 collection tables + summary view
│   ├── fee-schedule-starter.sql     CMS-derived starter rates (2024-2026)
│   ├── seed-additional-roles.sql    Manager / Supervisor / User roles
│   └── fix-max-packet.sql           one-line tip for very large uploads
├── storage/uploads/                 per-kind upload subfolders
├── docs/                            deployment + security
├── setup.bat                        full DB rebuild + collection schema
├── create-htdocs-link.bat           junction to C:\xampp\htdocs\medmecs
└── enable-php-extensions.bat        enable zip/gd/intl in php.ini
```

## Deployment

### Prerequisites on the live host

- PHP 8.2+ with these extensions enabled: `pdo_mysql`, `zip`, `mbstring`, `fileinfo`
- MySQL 8.x reachable from PHP
- Apache (or any LAMP-equivalent) with mod_rewrite recommended for clean URLs

### Steps

1. Copy this directory to your web root.
2. Edit `includes/config.php`:
   - `DB_HOST`, `DB_NAME`, `DB_USER`, `DB_PASS`
   - `APP_BASE_URL` (no trailing slash)
   - `SESSION_SECURE = true` if you're behind HTTPS (you should be)
3. Run `sql/schema.sql`, then `sql/collection-dashboard.sql`, then `sql/fee-schedule-starter.sql` against your live DB.
4. Make `storage/` writable by the web user.
5. Sign in at `/login.php`, rotate the seeded admin password.

The `.htaccess` files in `/includes`, `/sql`, `/docs`, and `/bin` deny direct web access — preserve them.

### Performance hints

- `sql/fix-max-packet.sql` raises MySQL's `max_allowed_packet`. Recommended on first deploy.
- `storage/` will grow with uploaded source files. Set up a quarterly rotation.
- For Collection CSVs >200 MB, set PHP `memory_limit = 1G` and `max_execution_time = 0` in your php.ini (or use `set_time_limit(0)` already present in upload-collection.php).

## Security highlights

- bcrypt cost 12 password hashing; per-(email,IP) rate-limit on login
- Forced password rotation on first login
- IP- and User-Agent-bound sessions, idle timeout
- HttpOnly, Secure (when HTTPS), SameSite=Strict cookies
- CSRF token on every state-changing form
- Generic "Invalid email or password" error (never leaks which side failed)
- Account lockout after 5 failed attempts with timed unlock
- Full audit log for every state-changing action including before/after JSON diff
- `/includes`, `/sql`, `/docs`, `/bin` blocked at HTTP layer

## What's deliberately NOT here

- No Laravel, Symfony, or any PHP framework
- No Composer / vendor folder
- No npm / webpack / Vite / build step
- No external API calls (charts loaded from CDN; Excel writer is pure PHP)
- No session driver other than PHP's native file/DB session
