# Design: Add WX Location Code to Site Roster

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

---

## Summary

Add a `locationCode` column (WX-format, e.g. `WX0101`) to the Site Roster tab on `home.php` and the Excel export. The code is derived from `locationInfo.locationID` using a CASE expression that maps regional 999-group locations (Southwest, Midwest) to Corporate (`WX0099`) and all others to `CONCAT('WX', LPAD(locationID, 4, '0'))`. The column is added to the `v_location_employees` view so all consumers (UI, API, export) pick it up from a single source.

---

## Scope

### In scope
- Add `locationCode` (WX format) as a new column in `v_location_employees`
- Surface it in the Site Roster table on `home.php` (immediately after the Location/Name column)
- Include it in the `api/search_site_list.php` JSON response
- Include it in the `site_export.php` Excel export (automatic via `SELECT *`)

### Out of scope
- Company Directory tab (Tab 1) — already shows `locationCode` from `employee_adp`
- Any changes to `locationInfo` table schema
- Sorting or filtering by WX code

---

## Architecture

The WX code is a derived value computed once in the view and propagated to all consumers. No application logic computes or formats this value. Two locationIDs (`0` = Southwest, `9991` = Midwest) are legacy 999-group regional entries that have no real store number — they are mapped to `WX0099` (Corporate) via a CASE expression.

```
locationInfo.locationID (int)
    └─► v_location_employees.locationCode  (CONCAT derived)
            ├─► search_site_list.php  (API response JSON)
            │       └─► home.php JS renderRows()  (Site Roster table)
            └─► site_export.php  (SELECT * → Excel column)
```

---

## Changes

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

Add `locationCode` as the second column, immediately after `locationNameFull`:

```sql
CASE
  WHEN locationID IN (0, 9991) THEN 'WX0099'
  ELSE CONCAT('WX', LPAD(locationID, 4, '0'))
END AS locationCode,
```

`locationID IN (0, 9991)` covers "999 - Southwest" and "999 - Midwest" — both are legacy regional entries that should display as Corporate. The full ALTER must regenerate the view with this column inserted in position 2. The rest of the view columns are unchanged.

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

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

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

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

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

### 3. `public/home.php` — Tab 2 `<thead>`

Add one `<th>` immediately after `<th>Location</th>`:

```html
<th>Location</th>
<th>WX Code</th>
<th>Address</th>
...
```

### 4. `public/home.php` — Tab 2 `<tbody>` PHP render loop

Add one `<td>` after the Location cell:

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

### 5. `public/home.php` — Tab 2 JS `renderRows()`

- Bump `const COLS = 22` → `const COLS = 23`
- Add `<td>` after the location cell in the row template:

```js
<td>${escapeHtml(r.locationNameFull)}</td>
<td>${escapeHtml(r.locationCode)}</td>
<td>${escapeHtml(r.address)}</td>
```

### 6. `public/assets/tools/site_export.php`

**No changes required.** Uses `SELECT * FROM v_location_employees` — the new column is included automatically.

---

## Edge Cases

| locationID | locationNameFull   | locationCode (output) | Rule |
|------------|--------------------|-----------------------|------|
| 0          | 999 - Southwest    | `WX0099`              | CASE override → Corporate |
| 9991       | 999 - Midwest      | `WX0099`              | CASE override → Corporate |
| 99         | 99 - Corporate     | `WX0099`              | Normal formula |
| 101        | 101 - Tomball      | `WX0101`              | Normal formula |
| 402        | 402 - (site name)  | `WX0402`              | Normal formula |

---

## File Change Summary

| File | Change type |
|------|-------------|
| `v_location_employees` (DB) | ALTER VIEW — add `locationCode` column |
| `public/api/search_site_list.php` | Add column to SELECT and response array |
| `public/home.php` | Add `<th>`, `<td>` (PHP + JS), bump COLS |
| `public/assets/tools/site_export.php` | No change |