# Design: WX Location Display — Site Roster & Company Directory

**Date:** 2026-04-21
**Author:** Eric Dunn
**Status:** Approved

---

## Summary

Add a `locationNameWX` column to `v_location_employees` (e.g. "WX0101 - Tomball") and surface it in the Site Roster tab replacing the current `locationNameFull` display. Update the Company Directory tab to show a WX-formatted location code via a `LEFT JOIN locationInfo`, replacing the plain numeric `locationCode` display. Neither existing column is modified — both changes are additive at the DB/query level.

---

## Scope

### In scope
- Add `locationNameWX` to `v_location_employees` view
- Site Roster (Tab 2): replace `locationNameFull` display with `locationNameWX` in PHP render, JS `renderRows()`, and `search_site_list.php`
- Company Directory (Tab 1): add `LEFT JOIN locationInfo`, compute `wx_location_code`, replace `locationCode` display cell
- Sorting and filtering by the new WX columns in both tabs

### Out of scope
- Modifying `locationNameFull` — it stays unchanged in the view
- `site_export.php` — picks up `locationNameWX` automatically via `SELECT *`

---

## Architecture

```
locationInfo.locationID
    └─► v_location_employees.locationCode  (existing, 2026-04-13)
            └─► v_location_employees.locationNameWX  (new — CONCAT of locationCode + name suffix)
                    ├─► search_site_list.php  (API response JSON)
                    │       └─► home.php JS renderRows()  (Site Roster table)
                    ├─► home.php PHP render loop  (Site Roster table — initial load)
                    └─► site_export.php  (SELECT * — no change needed)

employee_adp.locationCode
    └─► LEFT JOIN locationInfo li ON ea.locationCode = li.locationID
            └─► wx_location_code  (CASE computed in query)
                    └─► home.php PHP render loop  (Company Directory table)
```

---

## Changes

### 1. `v_location_employees` (MySQL view — ALTER)

Add `locationNameWX` as a computed column, derived from the already-present `locationCode` and the name suffix of `locationNameFull`:

```sql
CONCAT(locationCode, SUBSTRING(locationNameFull, LOCATE(' - ', locationNameFull))) AS locationNameWX
```

`locationCode` already handles all edge cases (locationID 0 and 9991 → `WX0099`), so no additional CASE logic is required here. `locationNameFull` is unchanged.

**Edge case examples:**

| locationID | locationNameFull    | locationCode | locationNameWX         |
|------------|---------------------|--------------|------------------------|
| 0          | 999 - Southwest     | WX0099       | WX0099 - Southwest     |
| 9991       | 999 - Midwest       | WX0099       | WX0099 - Midwest       |
| 99         | 99 - Corporate      | WX0099       | WX0099 - Corporate     |
| 101        | 101 - Tomball       | WX0101       | WX0101 - Tomball       |
| 402        | 402 - (site name)   | WX0402       | WX0402 - (site name)   |

---

### 2. `public/api/search_site_list.php`

**`$COLUMNS` string** — add `locationNameWX` after `locationNameFull`:

```php
$COLUMNS = "
    locationNameFull,
    locationNameWX,
    locationCode,
    address,
    ...
";
```

**`$rows[]` output array** — add entry:

```php
'locationNameWX' => $row['locationNameWX'] ?? '',
```

**Search filter** — swap `locationNameFull` → `locationNameWX` in the LIKE clause. `LIKE '%101%'` still matches "WX0101 - Tomball", so search behavior is preserved.

---

### 3. `public/home.php` — Tab 2 Site Roster

**PHP render loop** — swap display column:

```php
<td><?= htmlspecialchars($row2['locationNameWX'] ?? '') ?></td>
```

**JS `renderRows()`** — swap field reference:

```js
<td>${escapeHtml(r.locationNameWX)}</td>
```

Column header stays `<th>Location</th>` — no change.

---

### 4. `public/home.php` — Tab 1 Company Directory

**SQL query** — add `LEFT JOIN` and computed column:

```sql
SELECT ea.*,
       DATEDIFF(CURDATE(), hireDate) AS tenure_in_days,
       CASE
         WHEN li.locationID IN (0, 9991) THEN 'WX0099'
         WHEN li.locationID IS NOT NULL   THEN CONCAT('WX', LPAD(li.locationID, 4, '0'))
         ELSE CONCAT('WX', LPAD(ea.locationCode, 4, '0'))
       END AS wx_location_code
FROM employee_adp ea
LEFT JOIN locationInfo li ON ea.locationCode = li.locationID
WHERE ea.status_adp = 'Active'
  AND employeeName != 'AApple Jaxx'
  AND ea.jobTitle NOT IN ('Shift Leader', 'Team Leader')
```

The `ELSE` branch handles corp employees where the join fails (known collation/corp join limitation) — falls back to padding `ea.locationCode` directly.

**PHP render loop** — replace location code display cell:

```php
<td><?= htmlspecialchars($row['wx_location_code'] ?? '') ?></td>
```

---

## File Change Summary

| File | Change |
|------|--------|
| `v_location_employees` (DB) | ALTER VIEW — add `locationNameWX` column |
| `public/api/search_site_list.php` | Add `locationNameWX` to SELECT and response array; swap in search filter |
| `public/home.php` (Tab 2) | Swap `locationNameFull` → `locationNameWX` in PHP loop and JS `renderRows()` |
| `public/home.php` (Tab 1) | Add `LEFT JOIN locationInfo`, add `wx_location_code` CASE, replace display cell |
| `public/assets/tools/site_export.php` | No change — `SELECT *` picks up new view column automatically |