# IAP Tracker — AD/RD/MSA Filter Additions
**Date:** 2026-03-23
**Status:** Approved

## Overview

Add three new filter controls to the Internal Applications Tracker: Area Director (AD), Regional Director (RD), and MSA/Market. Filters are based on the location the applicant is applying TO (`desired_location`), not their current home store.

## Data Model

The tracker query already joins `locationInfo` via `employee_adp.locationCode` (alias `l`) for the employee's home store. A second join is needed for the desired location:

```sql
LEFT JOIN locationInfo l2 ON ia.desired_location = l2.locationNameFull
```

Relevant `locationInfo` columns:
- `l2.adName` — Area Director name
- `l2.rdName` — Regional Director name
- `l2.msa` — MSA / Market name

## Files Changed

### `app/HR/IAP/internal_applications_filter.php`
- Add `$filter_ad = $_GET['ad'] ?? []`
- Add `$filter_rd = $_GET['rd'] ?? []`
- Add `$filter_msa = $_GET['msa'] ?? []`
- Add WHERE clauses (same pattern as existing filters):
  - `AND l2.adName IN (...)`
  - `AND l2.rdName IN (...)`
  - `AND l2.msa IN (...)`

### `app/HR/IAP/internal_applications_tracker.php`
- Add `LEFT JOIN locationInfo l2 ON ia.desired_location = l2.locationNameFull` to the main query
- Add 3 populate queries before HTML output:
  - `SELECT DISTINCT adName FROM locationInfo WHERE active='Yes' AND adName IS NOT NULL ORDER BY adName`
  - `SELECT DISTINCT rdName FROM locationInfo WHERE active='Yes' AND rdName IS NOT NULL ORDER BY rdName`
  - `SELECT DISTINCT msa FROM locationInfo WHERE active='Yes' AND msa IS NOT NULL ORDER BY msa`
- Add 3 filter panels in the `filter-grid` div, between Location and Date Range:
  - Area Director (`<details>` + checkbox list, `name="ad[]"`)
  - Regional Director (`<details>` + checkbox list, `name="rd[]"`)
  - MSA / Market (`<details>` + checkbox list, `name="msa[]"`)
- All panels match existing visual style exactly (no border/color distinction)

### `app/HR/IAP/export_applications.php`
- Add `LEFT JOIN locationInfo l2 ON ia.desired_location = l2.locationNameFull` to the export query
  - Required because `internal_applications_filter.php` WHERE clauses reference `l2`, and without the join the query will fail when AD/RD/MSA filters are active
  - The export query does NOT need a `LEFT JOIN employee_adp` — the new filter WHERE clauses only reference `l2`, not `e`
  - The export link in the tracker uses `$_SERVER['QUERY_STRING']` to pass all GET params, so `ad[]`, `rd[]`, `msa[]` carry through to the export automatically — no changes to the export link needed

### `public/hr/iap.php` (router)
- No changes required — it routes to `internal_applications_tracker.php` and `export_applications.php` as includes; all filter logic lives in the included files

## Filter Behavior

- Multi-select checkboxes (same as Status, Position, Location)
- Filters are AND'd together (existing behavior)
- Empty filter = no restriction (existing behavior)
- Populated from active locations only (`active='Yes'`); shows all active AD/RD/MSA values regardless of whether they have current applications — this is acceptable
- NULL values excluded from dropdown population (`AND adName IS NOT NULL`)
- **Unmatched `desired_location`:** If an application's `desired_location` doesn't match any `locationNameFull` in `locationInfo`, the LEFT JOIN produces NULL for `l2` columns. When an AD/RD/MSA filter is active, those rows will be excluded from results. This is acceptable behavior — unresolvable locations are edge cases and HR will not be filtering on them.

## Out of Scope

- No new columns added to the tracker table
- No cascading/dependent filter behavior
- No changes to application detail view