# IAP Tracker — AD/RD/MSA Filter Additions 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 Area Director, Regional Director, and MSA/Market filter controls to the Internal Applications Tracker, based on the location the applicant is applying to.

**Architecture:** A second `LEFT JOIN locationInfo l2` is added on `ia.desired_location = l2.locationNameFull` in both the tracker and export queries. The filter file picks up three new GET params (`ad[]`, `rd[]`, `msa[]`) and appends WHERE clauses referencing `l2`. Three new `<details>` + checkbox panels are added to the filter grid, matching the existing visual style exactly.

**Tech Stack:** PHP, MySQL, vanilla HTML/CSS (no new dependencies)

---

## File Map

| File | Change |
|------|--------|
| `app/HR/IAP/internal_applications_filter.php` | Add 3 GET params + 3 WHERE clauses referencing `l2` |
| `app/HR/IAP/internal_applications_tracker.php` | Add `l2` join to main query; add 3 populate queries; add 3 filter panels |
| `app/HR/IAP/export_applications.php` | Add `l2` join to export query |

---

## Task 1: Update Filter Logic

**File:** `app/HR/IAP/internal_applications_filter.php`

- [ ] **Step 1: Add the three new GET param variables**

  After the existing `$date_end` line (line 7), add:

  ```php
  $filter_ad       = $_GET['ad']  ?? [];
  $filter_rd       = $_GET['rd']  ?? [];
  $filter_msa      = $_GET['msa'] ?? [];
  ```

- [ ] **Step 2: Add the three WHERE clauses**

  After the existing `$filter_location` block (after line 27, before the date block), add:

  ```php
  if (!empty($filter_ad)) {
      $formatted_ad = '("' . implode('","', array_map(function($a) use ($conn) {
              return mysqli_real_escape_string($conn, $a);
          }, $filter_ad)) . '")';
      $query_search .= " AND l2.adName IN $formatted_ad ";
  }
  if (!empty($filter_rd)) {
      $formatted_rd = '("' . implode('","', array_map(function($r) use ($conn) {
              return mysqli_real_escape_string($conn, $r);
          }, $filter_rd)) . '")';
      $query_search .= " AND l2.rdName IN $formatted_rd ";
  }
  if (!empty($filter_msa)) {
      $formatted_msa = '("' . implode('","', array_map(function($m) use ($conn) {
              return mysqli_real_escape_string($conn, $m);
          }, $filter_msa)) . '")';
      $query_search .= " AND l2.msa IN $formatted_msa ";
  }
  ```

---

## Task 2: Update Tracker Query and UI

**File:** `app/HR/IAP/internal_applications_tracker.php`

- [ ] **Step 1: Add `l2` join to the main query**

  Current query (line 41–43):
  ```php
  $query = "SELECT ia.*, e.employeeName, l.locationName FROM internal_applications ia
      LEFT JOIN employee_adp e ON ia.employee_id COLLATE utf8mb4_general_ci = e.employeeID COLLATE utf8mb4_general_ci
      LEFT JOIN locationInfo l ON e.locationCode = l.locationID " . $query_search . $order;
  ```

  Replace with:
  ```php
  $query = "SELECT ia.*, e.employeeName, l.locationName FROM internal_applications ia
      LEFT JOIN employee_adp e ON ia.employee_id COLLATE utf8mb4_general_ci = e.employeeID COLLATE utf8mb4_general_ci
      LEFT JOIN locationInfo l ON e.locationCode = l.locationID
      LEFT JOIN locationInfo l2 ON ia.desired_location = l2.locationNameFull " . $query_search . $order;
  ```

- [ ] **Step 2: Add the three populate queries**

  After the existing `$locationsResult` query (line 48), add:

  ```php
  $adResult  = $conn->query("SELECT DISTINCT adName FROM locationInfo WHERE active='Yes' AND adName IS NOT NULL ORDER BY adName");
  $rdResult  = $conn->query("SELECT DISTINCT rdName FROM locationInfo WHERE active='Yes' AND rdName IS NOT NULL ORDER BY rdName");
  $msaResult = $conn->query("SELECT DISTINCT msa    FROM locationInfo WHERE active='Yes' AND msa IS NOT NULL ORDER BY msa");
  ```

- [ ] **Step 3: Add the three filter panels to the HTML**

  Locate the closing `</div>` of the Location filter panel (after `endwhile` for `$locationsResult`, around line 196). Insert the three new panels immediately after it, before the Date Range `<div class="filter-col">`:

  ```php
                  <div class="filter-col">
                          <b>Area Director</b><br>
                          <details>
                              <summary>Select AD</summary>
                              <div class="checkbox-list">
                                  <?php while ($ad_row = $adResult->fetch_assoc()):
                                      $a_val   = $ad_row['adName'];
                                      $checked = in_array($a_val, $filter_ad ?? []) ? 'checked' : '';
                                  ?>
                                      <label><input type="checkbox" name="ad[]" value="<?=htmlspecialchars($a_val)?>" <?=$checked?>> <?=htmlspecialchars($a_val)?></label>
                                  <?php endwhile; ?>
                              </div>
                          </details>
                  </div>
                  <div class="filter-col">
                          <b>Regional Director</b><br>
                          <details>
                              <summary>Select RD</summary>
                              <div class="checkbox-list">
                                  <?php while ($rd_row = $rdResult->fetch_assoc()):
                                      $r_val   = $rd_row['rdName'];
                                      $checked = in_array($r_val, $filter_rd ?? []) ? 'checked' : '';
                                  ?>
                                      <label><input type="checkbox" name="rd[]" value="<?=htmlspecialchars($r_val)?>" <?=$checked?>> <?=htmlspecialchars($r_val)?></label>
                                  <?php endwhile; ?>
                              </div>
                          </details>
                  </div>
                  <div class="filter-col">
                          <b>MSA / Market</b><br>
                          <details>
                              <summary>Select MSA</summary>
                              <div class="checkbox-list">
                                  <?php while ($msa_row = $msaResult->fetch_assoc()):
                                      $m_val   = $msa_row['msa'];
                                      $checked = in_array($m_val, $filter_msa ?? []) ? 'checked' : '';
                                  ?>
                                      <label><input type="checkbox" name="msa[]" value="<?=htmlspecialchars($m_val)?>" <?=$checked?>> <?=htmlspecialchars($m_val)?></label>
                                  <?php endwhile; ?>
                              </div>
                          </details>
                  </div>
  ```

- [ ] **Step 4: Verify tracker loads with no filters active**

  Open: `http://whitewater-secure/public/hr/iap.php?page=tracker`

  Expected:
  - Page loads with no PHP errors
  - Three new filter panels (Area Director, Regional Director, MSA / Market) appear between Location and Date Range
  - All existing records still appear in the table
  - All existing filters (Status, Position, Location, Date Range) still work

---

## Task 3: Update Export Query

**File:** `app/HR/IAP/export_applications.php`

- [ ] **Step 1: Add `l2` join to the export query**

  Current query (line 25):
  ```php
  $query = "SELECT ia.* FROM internal_applications ia " . $query_search . $order;
  ```

  Replace with:
  ```php
  $query = "SELECT ia.* FROM internal_applications ia
      LEFT JOIN locationInfo l2 ON ia.desired_location = l2.locationNameFull " . $query_search . $order;
  ```

---

## Task 4: End-to-End Verification

- [ ] **Step 1: Test AD filter**

  1. Open `http://whitewater-secure/public/hr/iap.php?page=tracker`
  2. Expand "Area Director" panel — confirm it lists AD names
  3. Check one AD, click Apply Filters
  4. Confirm only applications for locations under that AD are shown

- [ ] **Step 2: Test RD filter**

  Same as above using Regional Director panel.

- [ ] **Step 3: Test MSA filter**

  Same as above using MSA / Market panel.

- [ ] **Step 4: Test combined filters**

  Select one AD + one Status, apply — confirm both filters AND correctly.

- [ ] **Step 5: Test export with active AD filter**

  With an AD filter active, click Export CSV — confirm the download respects the filter (row count matches tracker).

- [ ] **Step 6: Test Clear Filters**

  With filters active, click Clear Filters — confirm all panels reset and full record set returns.