# IAP Enhancements Implementation Plan
> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.
**Goal:** Add proxy submission, Jazz jobs position dropdown with dynamic location cascade, remove essay sections, store manager email, and replace the confirmation notice.
**Architecture:** Six file changes + two new AJAX endpoints, all within the existing `app/HR/IAP/` pattern. No framework — plain PHP/MySQLi/jQuery. DB gets two new columns before any PHP changes.
**Tech Stack:** PHP 8, MySQLi, jQuery 3.7, SendGrid, MySQL 8.0.18, WAMP (local test)
**Note:** No git commits during development. Eric commits when ready. Verify each task in the browser at `http://whitewater-secure/public/hr/iap.php`.
---
## File Map
| File | Action | Purpose |
|------|--------|---------|
| `app/HR/IAP/employee_lookup.php` | Create | AJAX endpoint — employee JSON by ID |
| `app/HR/IAP/locations_for_title.php` | Create | AJAX endpoint — locations for a position title |
| `public/hr/iap.php` | Modify | Register 2 new routes in `$allowed` |
| `app/HR/IAP/apply.php` | Modify | Proxy toggle, position dropdown, location cascade, remove 6 sections |
| `app/HR/IAP/submit_application.php` | Modify | New columns, manager email lookup, SendGrid CC, fixed bind_param |
| `app/HR/IAP/confirmation.php` | Modify | Full ATTN notice replacing minimal notice box |
---
## Task 1: Database — Add New Columns
**Files:** Production MySQL via MCP or direct query tool
- [ ] **Step 1: Run the ALTER statements**
```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;
```
- [ ] **Step 2: Verify columns exist**
```sql
SELECT COLUMN_NAME, COLUMN_TYPE, ORDINAL_POSITION
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'whitewater'
AND TABLE_NAME = 'internal_applications'
ORDER BY ORDINAL_POSITION;
```
Expected: `submitted_by` appears after `employee_id`; `manager_email` appears after `current_manager`.
---
## Task 2: Register New Routes in Dispatcher
**Files:**
- Modify: `public/hr/iap.php`
- [ ] **Step 1: Add two new entries to the `$allowed` array**
Current `$allowed` in `public/hr/iap.php`:
```php
$allowed = [
'tracker' => 'internal_applications_tracker.php',
'detail' => 'application_detail.php',
'apply' => 'apply.php',
'submit' => 'submit_application.php',
'confirmation' => 'confirmation.php',
'export' => 'export_applications.php',
'sync_status' => 'sync_application_status.php',
'sync_eligibility' => 'sync_eligibility_check.php',
];
```
Add two entries:
```php
$allowed = [
'tracker' => 'internal_applications_tracker.php',
'detail' => 'application_detail.php',
'apply' => 'apply.php',
'submit' => 'submit_application.php',
'confirmation' => 'confirmation.php',
'export' => 'export_applications.php',
'sync_status' => 'sync_application_status.php',
'sync_eligibility' => 'sync_eligibility_check.php',
'employee_lookup' => 'employee_lookup.php',
'locations_for_title' => 'locations_for_title.php',
];
```
- [ ] **Step 2: Note**
Do not test this route until Tasks 3 and 4 are complete — `require` on a missing file throws a fatal error. Verify routing works as part of Task 3 Step 2.
---
## Task 3: Create `employee_lookup.php`
**Files:**
- Create: `app/HR/IAP/employee_lookup.php`
- [ ] **Step 1: Create the file**
```php
'missing id']);
exit;
}
$conn = new mysqli(envv('new_servername'), envv('new_username'), envv('new_password'), envv('new_dbname'));
if ($conn->connect_error) {
http_response_code(500);
echo json_encode(['error' => 'db error']);
exit;
}
$stmt = $conn->prepare("
SELECT e.employeeID, e.employeeName,
e.workEmail, e.wwEmail, e.personalEmail,
e.jobTitle,
COALESCE(l.locationName, e.locationCode) AS location_name,
DATEDIFF(CURDATE(), COALESCE(e.effective_date, e.hireDate)) AS days_in_role,
e.reports_to,
mgr.workEmail AS manager_email
FROM employee_adp e
LEFT JOIN locationInfo l ON e.locationCode = l.locationID
LEFT JOIN employee_adp mgr ON mgr.associateOID = e.reports_to_associateOID
WHERE e.employeeID = ?
AND (e.terminationDate IS NULL OR e.terminationDate > CURDATE())
");
$stmt->bind_param('s', $employee_id);
$stmt->execute();
$row = $stmt->get_result()->fetch_assoc();
$stmt->close();
$conn->close();
if (!$row) {
http_response_code(404);
echo json_encode(['error' => 'not found']);
exit;
}
$name_parts = explode(' ', trim($row['employeeName']), 2);
echo json_encode([
'employee_id' => $row['employeeID'],
'first_name' => $name_parts[0] ?? '',
'last_name' => $name_parts[1] ?? '',
'email' => $row['workEmail'] ?? $row['wwEmail'] ?? $row['personalEmail'] ?? '',
'job_title' => $row['jobTitle'] ?? '',
'location_name' => $row['location_name'] ?? '',
'days_in_role' => (int)($row['days_in_role'] ?? 0),
'manager' => $row['reports_to'] ?? '',
'manager_email' => $row['manager_email'] ?? '',
]);
exit;
```
- [ ] **Step 2: Verify in browser**
Navigate to: `http://whitewater-secure/public/hr/iap.php?page=employee_lookup&id=17438`
Expected: JSON object with Eric's employee data including `manager_email`.
Navigate to: `http://whitewater-secure/public/hr/iap.php?page=employee_lookup&id=FAKE`
Expected: `{"error":"not found"}` with HTTP 404.
---
## Task 4: Create `locations_for_title.php`
**Files:**
- Create: `app/HR/IAP/locations_for_title.php`
- [ ] **Step 1: Create the file**
```php
connect_error) {
http_response_code(500);
echo json_encode(['error' => 'db error']);
exit;
}
$stmt = $conn->prepare("
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
");
$stmt->bind_param('s', $title);
$stmt->execute();
$result = $stmt->get_result();
$stmt->close();
$conn->close();
$locations = [];
while ($row = $result->fetch_assoc()) {
$locations[] = $row['locationNameFull'];
}
echo json_encode($locations);
exit;
```
- [ ] **Step 2: Verify in browser**
Navigate to: `http://whitewater-secure/public/hr/iap.php?page=locations_for_title&title=Car+Wash+Attendant`
Expected: JSON array of `locationNameFull` strings like `["101 - Tomball", "110 - Crosby", ...]`
Navigate to: `http://whitewater-secure/public/hr/iap.php?page=locations_for_title&title=General+Manager`
Expected: JSON array containing `"99 - Corporate"` (WX999 → 99 mapping).
Navigate to: `http://whitewater-secure/public/hr/iap.php?page=locations_for_title&title=NOPE`
Expected: `[]`
---
## Task 5: Modify `apply.php`
**Files:**
- Modify: `app/HR/IAP/apply.php`
This is the largest change. Work section by section.
### 5a — Remove unused query + add position query
- [ ] **Step 1: Replace `$locationsQuery` with `$positionsQuery` at the top of the PHP block**
Remove line 30:
```php
$locationsQuery = $conn->query("SELECT * FROM locationInfo WHERE active = 'Yes' ORDER BY locationName");
```
Replace with:
```php
$positions_result = $conn->query("
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
");
```
### 5b — Embed session employee data as JS object
- [ ] **Step 2: Add a JS data block immediately after `
```
### 5c — Add proxy toggle UI + update Your Information section
- [ ] **Step 3: Replace the Your Information section**
Current block (lines 120–132):
```php
Your Information
```
Replace with:
```php
Your Information
```
### 5d — Replace Position Details section
- [ ] **Step 4: Replace the Position Details section**
Current block (lines 134–149):
```php
Position Details
```
Replace with:
```php
Position Details
No open locations for this position — contact HR.
```
### 5e — Remove Career Goals and Examples sections
- [ ] **Step 5: DELETE (do not comment out) these two entire section blocks from `apply.php`**
Commenting out leaves `required` constraints active on hidden fields, silently blocking form submission. Delete the HTML entirely.
Delete the Career Goals block (lines ~185–193):
```php
Career Goals
```
Delete the Examples block (lines ~195–211):
```php
Examples
```
### 5f — Add jQuery logic before `
`
- [ ] **Step 6: Add script block before ``**
```php
```
- [ ] **Step 7: Verify proxy flow in browser**
1. Navigate to `http://whitewater-secure/public/hr/iap.php?page=apply`
2. Check "I am submitting on behalf of another employee" — search panel appears
3. Type a name — employee list filters
4. Select an employee — read-only fields update with their data, hidden `employee_id` changes
5. Uncheck — fields reset to logged-in user's data
6. Select a position — location dropdown populates
7. Select a corporate position (e.g., "General Manager") — location shows "99 - Corporate"
---
## Task 6: Modify `submit_application.php`
**Files:**
- Modify: `app/HR/IAP/submit_application.php`
- [ ] **Step 1: Add server-side guard for required POST fields**
Add immediately after the `$employee_id = $_POST['employee_id'] ?? '';` line:
```php
$position = $_POST['position_applying_for'] ?? '';
$location = $_POST['desired_location'] ?? '';
if (!$position || !$location) {
die("Position and location are required.");
}
```
- [ ] **Step 2: Add `submitted_by` variable after the existing employee lookup**
After `$stmt->close();`, add:
```php
$submitted_by = $_SESSION['user_id'] ?? '';
```
- [ ] **Step 3: Add manager email lookup after `$submitted_by`**
```php
$manager_email = '';
if (!empty($employee['reports_to_associateOID'])) {
$mgr_stmt = $conn->prepare("SELECT workEmail FROM employee_adp WHERE associateOID = ?");
$mgr_stmt->bind_param('s', $employee['reports_to_associateOID']);
$mgr_stmt->execute();
$mgr_row = $mgr_stmt->get_result()->fetch_assoc();
$mgr_stmt->close();
$manager_email = $mgr_row['workEmail'] ?? '';
}
```
- [ ] **Step 4: Replace the full INSERT + bind_param block**
Replace the existing `$insertStmt = $conn->prepare(...)` through `$insertStmt->bind_param(...)` with:
```php
$insertStmt = $conn->prepare("INSERT INTO internal_applications (
employee_id, submitted_by, first_name, last_name, email,
current_position, current_location, current_manager, manager_email,
hire_date, effective_date, length_in_role_days,
position_applying_for, desired_location,
on_pip, corrective_action_12mo, previously_performed_duties,
willing_transitional_training, willing_background_screening,
relevant_experience, certifications_licenses,
certify_accurate, certify_no_guarantee, certify_authorize_review,
application_status, submitted_at
) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'Submitted',NOW())");
$certifications = $_POST['certifications_licenses'] ?? '';
$certify_accurate = isset($_POST['certify_accurate']) ? 1 : 0;
$certify_no_guarantee = isset($_POST['certify_no_guarantee']) ? 1 : 0;
$certify_authorize = isset($_POST['certify_authorize_review']) ? 1 : 0;
$insertStmt->bind_param('sssssssssssisssssssssiii',
$employee_id,
$submitted_by,
$firstName, $lastName, $email,
$employee['jobTitle'], $employee['locationName'], $employee['reports_to'],
$manager_email,
$employee['hireDate'], $employee['effective_date'], $employee['days_in_role'],
$_POST['position_applying_for'], $_POST['desired_location'],
$_POST['on_pip'], $_POST['corrective_action'], $_POST['previously_performed'],
$_POST['willing_training'], $_POST['willing_screening'],
$_POST['relevant_experience'], $certifications,
$certify_accurate, $certify_no_guarantee, $certify_authorize
);
```
- [ ] **Step 5: Add manager email CC to SendGrid notification**
Find the SendGrid block and add the CC line:
```php
$email->setFrom("noreply@whitewatercw.com", "Internal Applications");
$email->setSubject("New Application - " . $firstName . " " . $lastName);
$email->addTo("hr@whitewatercw.com", "HR");
if ($manager_email) {
$email->addTo($manager_email);
}
$email->addContent("text/html", "
New Application #{$application_id}
{$firstName} {$lastName} applied for {$_POST['position_applying_for']}
");
```
- [ ] **Step 6: Verify end-to-end submission**
1. Navigate to `http://whitewater-secure/public/hr/iap.php?page=apply`
2. Fill out the form — select a position, select a location, fill required fields, check certifications
3. Submit
4. Verify redirect to confirmation page
5. Check the DB:
```sql
SELECT application_id, employee_id, submitted_by, manager_email,
position_applying_for, desired_location, application_status
FROM internal_applications
ORDER BY submitted_at DESC LIMIT 3;
```
Expected: new row with `submitted_by` populated, `manager_email` populated (or empty if employee has no manager OID), correct position and location.
---
## Task 7: Modify `confirmation.php`
**Files:**
- Modify: `app/HR/IAP/confirmation.php`
- [ ] **Step 1: Replace the entire file content**
```php
✓
Application Submitted!
Your internal position application has been received.
⚠ ATTN — Additional Step Required
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.
Please ensure your resume is updated before submitting your application.
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.
Please note that candidates must submit both the Internal Position Application and the JazzHR application in order to be considered.