# CapEx v2 — Henry Feedback Design Spec
**Date:** 2026-03-26
**Author:** Eric Dunn

---

## Overview

Four improvements to the CapEx v2 Planning tab based on post-demo feedback from Henry:

1. **Updated Estimated Cost** — new manual field for revised quotes, replaces original in Variance calculation
2. **Export column headers** — both tab exports use friendly display names instead of raw DB column names
3. **Last Updated date range filter** — filter Planning tab rows by `updated` timestamp
4. **Blendco Order** — new Yes/No dropdown field on Planning tab

Future requests (out of scope for this spec): user/permissions system, full rework linking both tabs.

---

## Scope: Files Changed

| File | What changes |
|---|---|
| DB migration | `ALTER TABLE capex_planning` — add 2 columns |
| `api/capex_planning_table.php` | Add new columns to SELECT, update variance, render new table columns |
| `support/capex_planning_editor.php` | Lock `estimated_cost` readonly, add 2 new fields |
| `support/capex_planning_create.php` | Add 2 new fields |
| `support/capex_update_planning.php` | Add 2 new columns to INSERT/UPDATE |
| `capex_planning_fragment.php` | Add Last Updated date range inputs to filter bar; add params to JS allowed list |
| `api/capex_planning_filters.php` | Add Last Updated WHERE clause |
| `export_planning.php` | Replace `fetch_fields()` with column map; add new fields to SELECT; update variance |
| `export.php` | Replace `fetch_fields()` with column map |

---

## 1. Database Changes

```sql
ALTER TABLE capex_planning
    ADD COLUMN updated_estimated_cost DECIMAL(10,2) NULL DEFAULT NULL
    AFTER estimated_cost;

ALTER TABLE capex_planning
    ADD COLUMN blendco_order ENUM('Yes','No') NULL DEFAULT NULL
    AFTER quote_confirmed;
```

Both columns are nullable. Existing rows default to NULL — no backfill needed.

---

## 2. Updated Estimated Cost

### Concept
Manual field for a revised quote. When populated, it replaces `estimated_cost` in the Variance calculation. `estimated_cost` becomes read-only in the editor (still editable in the create form for new records).

### Variance Calculation
Everywhere variance is computed, replace the current formula with:
```sql
CASE
    WHEN (IFNULL(spend_details.spend,0) - IFNULL(spend_details.creds,0)) = 0 THEN 0
    ELSE COALESCE(po_list.updated_estimated_cost, po_list.estimated_cost)
         - (IFNULL(spend_details.spend,0) - IFNULL(spend_details.creds,0))
END AS variance
```
Applies to: `api/capex_planning_table.php` and `export_planning.php`.

### Table Display
- Add `po_list.updated_estimated_cost` to SELECT
- Render as new column **"Updated Est. Cost"** immediately after "Estimated Cost"
- Format as currency (same as Estimated Cost); display empty cell if NULL

### Editor (`capex_planning_editor.php`)
- `estimated_cost` input: add `readonly` attribute
- Add new `updated_estimated_cost` input immediately next to it — editable decimal, nullable

### Create Form (`capex_planning_create.php`)
- `estimated_cost` remains editable (new records only)
- Add `updated_estimated_cost` as optional editable field

### Update Handler (`capex_update_planning.php`)
- Add `updated_estimated_cost` to the INSERT/UPDATE column list

---

## 3. Export Column Headers

Replace `fetch_fields()` dynamic header extraction with an explicit `$column_map` array in both export files. Loop over the map for both header row and data rows (ensures column order matches the map).

### Planning Export Map (`export_planning.php`)
Also update the SELECT to include `updated_estimated_cost` and `blendco_order`, and apply the updated variance formula.

| DB Column | Export Header |
|---|---|
| ID | ID |
| created | Created |
| capex_requestor | Requestor |
| market | Market |
| store_number | Store # |
| item | Item |
| year_built | Year Built |
| timeline | Timeline |
| deployment_month | Month Deployed |
| estimated_cost | Estimated Cost |
| updated_estimated_cost | Updated Est. Cost |
| committed_uncommitted | Commitment |
| actual_cost | Actual Cost |
| variance | Variance |
| carryover | Carryover |
| capex_type | Capex Type |
| capex_category | Category |
| equipment_type | Equipment |
| type | Type |
| status | Status |
| order_date | Order Date |
| po_number | PO Number |
| request_year | Year |
| priority | Priority |
| quote_confirmed | Quote Confirmed |
| blendco_order | Blendco Order |
| notes_maintenance_history | Notes |
| updated | Last Updated |

### Tracker Export Map (`export.php`)
Exports from `v_capex_spend_v2` view — no query changes needed.

| DB Column | Export Header |
|---|---|
| ID | ID |
| cogID | Cog ID |
| status | Status |
| projectName | Project Name |
| requestor | Requestor |
| locationName | Location |
| PO | PO |
| acct_status | Acct Status |
| facilities_status | Facilities Status |
| totalbudget | Total Budget |
| spent | Spent |
| variance | Variance |
| reconcile | Reconcile |
| maintainxID | MaintainX ID |
| posted_date | Posted Date |

---

## 4. Last Updated Date Range Filter

### Filter Bar (`capex_planning_fragment.php`)
Add two date inputs following the same pattern as the Posted Date filter:

```html
<td><label>Last Updated From</label>
    <input type="date" name="plan_updated_start" id="plan_updated_start" ...></td>
<td><label>Last Updated To</label>
    <input type="date" name="plan_updated_end" id="plan_updated_end" ...></td>
```

Add both params to the `allowed` array in `refreshPlanningTable()`:
```js
'plan_updated_start', 'plan_updated_end'
```

### Filter Backend (`api/capex_planning_filters.php`)
Add to the WHERE clause (bound params, same pattern as posted_date filter):
```sql
AND po_list.updated >= ?
AND po_list.updated < DATE_ADD(?, INTERVAL 1 DAY)
```
The `+1 DAY` on the end date makes the range inclusive (end date is a date, `updated` is a datetime).

---

## 5. Blendco Order

### Concept
Yes/No dropdown field. Nullable — blank means not yet set. Position: after Quote Confirmed in table, editor, and export (before Last Updated).

### Table Display (`api/capex_planning_table.php`)
- Add `po_list.blendco_order` to SELECT
- Render as new column **"Blendco Order"** after "Quote Confirmed"
- Display "Yes" / "No" as-is; empty cell if NULL

### Editor (`capex_planning_editor.php`)
Add Yes/No dropdown after Quote Confirmed field:
```html
<select name="blendco_order">
    <option value="">— Select —</option>
    <option value="Yes" <?= ($edit_entry['blendco_order'] ?? '') === 'Yes' ? 'selected' : '' ?>>Yes</option>
    <option value="No"  <?= ($edit_entry['blendco_order'] ?? '') === 'No'  ? 'selected' : '' ?>>No</option>
</select>
```

### Create Form (`capex_planning_create.php`)
Same dropdown, no default selected.

### Update Handler (`capex_update_planning.php`)
Add `blendco_order` to the INSERT/UPDATE column list.

---

## Out of Scope

- User/permissions system connecting to employee table (future)
- Full rework linking Tracker and Planning tabs (future)
- Tracker tab changes other than export headers