# IAP Enhancements — Design Spec
**Date:** 2026-03-19
**Project:** WhiteWater Intranet — Internal Application Portal (IAP)
**Author:** Eric Dunn

---

## Background

The IAP allows employees to submit internal position applications. Team leaders and shift leaders lack intranet access, so managers submit on their behalf. The current form has a free-text position field, a full location list, and several essay sections that are being removed. The confirmation page needs the full ATTN notice content in place of the minimal JazzHR reminder.

**MySQL version:** 8.0.18-google — `REGEXP_REPLACE` is available.

---

## Change 1: Submit on Behalf ("This is not me")

### Problem
Managers with intranet access need to submit applications on behalf of team/shift leaders who do not have intranet access.

### Solution
Add a proxy toggle to the top of the **Your Information** section in `apply.php`.

**UI:**
- Checkbox: "I am submitting on behalf of another employee"
- When checked: a text search input appears with a filtered `<select>` of active employees (non-terminated, from `employee_adp.employeeName` + `employeeID`)
- On employee selection: AJAX GET to `iap.php?page=employee_lookup&id={employeeID}` — returns JSON, JS repopulates read-only display fields and swaps the hidden `employee_id` value
- When unchecked: JS resets all display fields and `employee_id` from a JS object holding the original session employee data embedded in the page on load (no round-trip needed)
- Submit button is disabled while an AJAX call is in flight

**employee_lookup JSON response schema:**
```json
{
  "employee_id": "17438",
  "first_name": "Eric",
  "last_name": "Dunn",
  "email": "edunn@whitewatercw.com",
  "job_title": "Lead Software Engineer",
  "location_name": "Corporate",
  "days_in_role": 182,
  "manager": "Joshua McCown",
  "manager_email": "jmccown@whitewatercw.com"
}
```
`manager_email` is fetched via self-join on `employee_adp` using `reports_to_associateOID`:
```sql
SELECT mgr.workEmail FROM employee_adp mgr WHERE mgr.associateOID = e.reports_to_associateOID
```
`manager` (name) is already displayed as a read-only field. `manager_email` is not displayed — stored and used for notification only.

Fields map directly to the read-only display inputs in the Your Information section.

**Data:**
- Add column `submitted_by VARCHAR(20) NULL` to `internal_applications` (after `employee_id`)
- `submit_application.php` always writes `$_SESSION['user_id']` to `submitted_by`, regardless of proxy use
- `employee_id` stores the applicant (the person being applied for)

**Security policy:** Any logged-in intranet user may submit on behalf of any employee. No server-side restriction on which `employee_id` a user may post — this is an internal HR tool and accepted risk. `employee_lookup.php` validates that the requested `employee_id` exists in `employee_adp` before returning data.

**New file:** `app/HR/IAP/employee_lookup.php`
- Accepts `$_GET['id']`, queries `employee_adp` using `DATEDIFF(CURDATE(), COALESCE(e.effective_date, e.hireDate))` for `days_in_role` (matches the formula used in `apply.php` and `submit_application.php`)
- Emits `Content-Type: application/json`, then `exit` — no HTML output
- Returns HTTP 404 + `{"error":"not found"}` if employee does not exist
- Implementation must verify `$_SESSION['user_id']` is the correct session key by checking `app/Support/auth.php` before shipping

---

## Change 2: Position Details — Jazz Jobs Dropdown + Dynamic Locations

### Problem
- "Position Applying For" is free-text — produces inconsistent data
- "Desired Location" shows all active locations regardless of position
- Career Goals and Examples sections add unnecessary friction and are removed

### Solution

**Position dropdown:**
Replace free-text `position_applying_for` input with a `<select>`:
```sql
SELECT DISTINCT REGEXP_REPLACE(title, ' [0-9]+$', '') AS clean_title
FROM jazz_jobs
WHERE status = 'Open'
  AND title NOT LIKE 'General Application%'
  AND title != 'Testing Only'
ORDER BY clean_title
```
WX999 departments (corporate) ARE included — they contain real positions (e.g., General Manager). Only "General Application%" regional buckets and "Testing Only" test records are excluded by title filter.

**Department normalization:**
The `department` field can have suffixes (e.g., `WX999 - HR`, `WX999 - Operations`, `WX503`). Before extracting the store number, strip any ` - suffix` with `REGEXP_REPLACE(department, ' - .*$', '')` to normalize to `WXNNN`. Then extract digits. Special case: if extracted number = `999`, map to `locationID = 99` (Corporate).

**Dynamic location cascade:**
- Desired Location `<select>` starts empty with placeholder "— Select a position first —" and is disabled until a position is chosen
- On position selection: AJAX GET to `iap.php?page=locations_for_title&title={clean_title}`
- Location dropdown repopulates with matching results; submit button re-enabled after results load
- If AJAX returns zero results: show "No open locations for this position — contact HR" and keep submit disabled

**New file:** `app/HR/IAP/locations_for_title.php`
- Emits `Content-Type: application/json`, then `exit` — no HTML output
```sql
SELECT DISTINCT li.locationNameFull
FROM jazz_jobs jj
JOIN locationInfo li
  ON CASE
       WHEN CAST(REGEXP_REPLACE(REGEXP_REPLACE(jj.department, ' - .*$', ''), '[^0-9]', '') AS UNSIGNED) = 999
       THEN 99
       ELSE CAST(REGEXP_REPLACE(REGEXP_REPLACE(jj.department, ' - .*$', ''), '[^0-9]', '') AS UNSIGNED)
     END = li.locationID
WHERE jj.status = 'Open'
  AND jj.title NOT LIKE 'General Application%'
  AND jj.title != 'Testing Only'
  AND REGEXP_REPLACE(jj.title, ' [0-9]+$', '') = ?
ORDER BY li.locationNameFull
```
Returns JSON array of `locationNameFull` strings (e.g., `["99 - Corporate", "101 - Tomball", "214 - Crowley"]`).

**Sections removed — explicit steps required in both files:**

From `apply.php` HTML: delete the entire Career Goals `<div class="section-header">` block and Examples `<div class="section-header">` block and all their child `<div class="full-field">` elements:
- `why_interested` textarea
- `career_alignment` textarea
- `leadership_example` textarea
- `problem_solving_example` textarea
- `accountability_example` textarea
- `development_needs` textarea

Also remove the now-unused static `$locationsQuery` from the top of `apply.php` (line 30) since the location dropdown is now AJAX-driven.

From `submit_application.php`: remove all 6 of these columns from the INSERT column list, remove their 6 corresponding `?` placeholders from VALUES, and remove their bound variables from `bind_param`. The resulting INSERT and bind_param must match the 23-param spec below exactly.

**`submit_application.php` other changes:**
- Add `submitted_by` to INSERT at position 2 (after `employee_id`), binding `$_SESSION['user_id']`
- Look up `manager_email` at submit time via self-join on `employee_adp.reports_to_associateOID` → `mgr.workEmail`; store in `$manager_email` variable
- Add `manager_email` to INSERT at position 9 (after `current_manager`)
- POST field name `corrective_action` (HTML form) stays as-is — only the INSERT column name is corrected to `corrective_action_12mo` to match the DB column. `$_POST['corrective_action']` remains unchanged.
- SendGrid notification: add `$manager_email` as a second `addTo` recipient so manager is notified on submission

**Final `bind_param` after all changes — 24 params:**

| # | Column | Type | Source |
|---|--------|------|--------|
| 1 | employee_id | s | POST |
| 2 | submitted_by | s | SESSION user_id |
| 3 | first_name | s | derived |
| 4 | last_name | s | derived |
| 5 | email | s | employee_adp |
| 6 | current_position | s | employee_adp |
| 7 | current_location | s | employee_adp |
| 8 | current_manager | s | employee_adp |
| 9 | manager_email | s | employee_adp self-join |
| 10 | hire_date | s | employee_adp |
| 11 | effective_date | s | employee_adp |
| 12 | length_in_role_days | i | computed |
| 13 | position_applying_for | s | POST |
| 14 | desired_location | s | POST |
| 15 | on_pip | s | POST |
| 16 | corrective_action_12mo | s | POST |
| 17 | previously_performed_duties | s | POST |
| 18 | willing_transitional_training | s | POST |
| 19 | willing_background_screening | s | POST |
| 20 | relevant_experience | s | POST |
| 21 | certifications_licenses | s | POST |
| 22 | certify_accurate | i | POST |
| 23 | certify_no_guarantee | i | POST |
| 24 | certify_authorize_review | i | POST |

**Format string:** `sssssssssssissssssssssiii` (24 chars)

DB columns for dropped fields are retained — not dropped. Existing data preserved.

---

## Change 3: Confirmation Page — ATTN Notice

### Problem
Current confirmation page shows only a minimal JazzHR link reminder. The full ATTN notice must be displayed.

### Solution
Replace the existing notice box in `confirmation.php` with full styled HTML:

**Content:**
1. There is an additional step required to complete your application for this role. To be considered, you must also apply directly through the job posting in JazzHR.
2. Please ensure your resume is updated before submitting your application.
3. If the position is not available in JazzHR, please contact the Area Manager for the role or a member of the Talent Acquisition team for assistance.
4. Please note that candidates must submit both the **Internal Position Application** and the JazzHR application in order to be considered.

**Styling:** Yellow `notice-box` (matching `apply.php` style), `⚠ ATTN` header, bulleted list for the four points, direct link to JazzHR jobs page.

Note: `confirmation.php` currently loads Bootstrap from CDN while other IAP files use the intranet stylesheets. The `notice-box` style will be inlined to maintain visual consistency regardless of which stylesheet is active.

**Acknowledged gap:** Proxy submissions (where `submitted_by ≠ employee_id`) generate the same HR notification email as self-submissions. HR cannot distinguish proxy vs self from the email alone. Updating the notification is out of scope for this spec — HR can view the tracker detail page to see `submitted_by` if needed in future.

---

## Files Modified

| File | Change |
|------|--------|
| `app/HR/IAP/apply.php` | Proxy toggle + employee search; position dropdown; dynamic location cascade; remove Career Goals + Examples |
| `app/HR/IAP/submit_application.php` | Add `submitted_by`; remove 6 dropped fields; fix `corrective_action` → `corrective_action_12mo`; new bind_param (23 params) |
| `app/HR/IAP/confirmation.php` | Replace notice box with full ATTN notice HTML |
| `public/hr/iap.php` | Add `employee_lookup` and `locations_for_title` to `$allowed` routes array |

## Files Added

| File | Purpose |
|------|---------|
| `app/HR/IAP/employee_lookup.php` | AJAX endpoint — returns employee JSON by ID |
| `app/HR/IAP/locations_for_title.php` | AJAX endpoint — returns locationNameFull list for a clean position title |

## Database

```sql
ALTER TABLE internal_applications
  ADD COLUMN submitted_by VARCHAR(20) NULL AFTER employee_id;

ALTER TABLE internal_applications
  ADD COLUMN manager_email VARCHAR(255) NULL AFTER current_manager;
```

---

## Out of Scope
- Dropping removed columns from the DB schema
- Tracker/detail view display of `submitted_by`
- Email notification changes for proxy submissions