# TODO — Planning Board in Dolibarr (native, no VBA)

## Current Status — 2026-04-01

| Phase | Feature | Status |
|-------|---------|--------|
| 1 | `planning.php` grid render + week/customer grouping | ✅ Done |
| 1 | `llx_planning_schedule` SQL table | ✅ Done |
| 2 | Click-to-edit cells + `ajax/schedule_save.php` | ✅ Done |
| 2 | Capacity override row + `ajax/capacity_save.php` | ✅ Done |
| 2 | Cell colour logic (green/yellow/red) | ✅ Done |
| 3 | Progress bar + remaining hours in row header | ✅ Done |
| 3 | `ajax/calc.php` — full Calc engine | ✅ Done |
| 3 | Calc button + `js/planning.js` integration | ✅ Done |
| 4 | `admin/planning_setup.php` — shift config per WS | ✅ Done |
| 5 | Timeline sync (grid → MO date_start/date_end) | ✅ Done |
| 6 | Material status badges TIC/DEE/JW per MO row | 🔲 TODO |
| 7 | Board integration with `llx_planning_schedule` | 🔲 TODO |
| 8 | Shift-click range fill (Excel-style) | 🔲 Low priority |
| DT-1 | Downtime badge once per day (not per MO row) | ✅ Done |
| DT-2 | Shift-code in downtime tooltip | ✅ Done |
| TL-1 | MO Timeline bar start from `date_start_planned` | ✅ Done |
| LK-1 | Lock transfer on drag-drop reorder | ✅ Done |
| LK-2 | Two-locked-MO swap — skip transfer if target already locked | ✅ Done |

**Next up:** Phase 6 — Material status badges (TIC/DEE/JW), or Phase 7 — Board integration.

---

**Primary approach: build the FM4 planning board natively inside Dolibarr.**
No VBA, no Excel sync. Dolibarr becomes the source of truth.
Excel remains available as a read-only export / printout if needed.

> **From full-screen analysis:** The workbook has **one sheet per machine** (FM2–FM9).
> Each sheet is identical in structure but represents a different forming machine.
> The Dolibarr grid must support a **machine selector** — one grid view per workstation.
> Additional utility sheets: `QTY per hour`, `Sheet Info`, `SAP Masters`, `Tool Clean`,
> `Shifts Control`, `Cust No-Name` — these feed reference data into the planning process.

> The VBA sync approach (Excel → POST → Dolibarr) is documented in section 9
> as a fallback if a full Dolibarr UI replacement is not feasible.

---

## Why native Dolibarr is better than VBA sync

| | VBA sync | Native Dolibarr |
|---|---|---|
| Source of truth | Excel (fragile) | Dolibarr DB |
| Multi-user editing | ❌ one file | ✅ browser, any device |
| Real-time optracking | manual pull | ✅ auto from scanstation |
| Tablet / shop floor | ❌ | ✅ |
| Audit trail | ❌ | ✅ `llx_planning_schedule` |
| Cycle time live calc | manual | ✅ derived from optracking |

The `planning` module already has 80% of what's needed:
- Planning Board (kanban by workstation)
- Timeline (Gantt-like per day)
- optracking integration (qty_done, ghost cards)

**What's missing** = the day-level hour allocation grid (the X+ columns from Excel).
That's the one piece to build.

---

## 0. Excel Workbook Structure (full screen)

### Machine sheets (one per forming machine)

| Sheet | Machine | Notes |
|-------|---------|-------|
| FM2 | Forming Machine 2 | same layout as FM4 |
| FM3 | Forming Machine 3 | same layout |
| FM4 | Forming Machine 4 | **reference sheet** |
| FM5 | Forming Machine 5 | same layout |
| FM6 | Forming Machine 6 | same layout |
| FM7 | Forming Machine 7 | same layout |
| FM8 | Forming Machine 8 | same layout |
| FM9 | Forming Machine 9 | same layout |

Dolibarr equivalent: **one grid view, filtered by workstation (FM2–FM9)**.
URL: `grid.php?fk_ws=X`

### Utility sheets

| Sheet | Content | Dolibarr equivalent |
|-------|---------|--------------------|
| `QTY per hour` | Lookup table — cycle time / qty per hour per product or tool | `llx_workstation_workstation.cycle_time` or product extrafield |
| `Sheet Info` | Batch/sheet size reference | MO extrafields (parts per sheet) |
| `SAP Masters` | Product master data from SAP | Dolibarr `llx_product` (already imported) |
| `Tool Clean` | Tool maintenance schedule | future `pm` module |
| `Shifts Control` | Shift patterns per machine — hours/day, working days | `llx_planning_ws_config` (new admin table) |
| `Cust No-Name` | Customer number → name mapping | Dolibarr `llx_societe` (already exists) |

---

### Header area (rows 1–4)

| Cell | Label | Value / Meaning |
|------|-------|-----------------|
| A1 | Machine name | `FM4` |
| B1 | Priority col header | column B = job priority |
| G2 | Work Hrs | `12` — total hours available this shift |
| P3 | T = Cycle Time | `90` — cycle time in seconds (machine-wide default) |
| P4 | M = Total Made | live count — `SUM(optracking.qty_delta)` for today |
| S3 | Starting Time | `6` — shift starts 06:00 |
| B3 | Job Wallet | button/label — links to job wallet print |
| D3 | Info Board | red button — read-only display mode (large screen / TV board) |
| Q3 | Calc | trigger button — recalculates all derived columns |

### Capacity summary (top-right, per day column)

| Row | Content | Dolibarr equivalent |
|-----|---------|--------------------|
| Row 2 right | **Hours Available** | `shift_hours` from workstation config per weekday |
| Row 3 right | **Total Hours Planned** | `SUM(planned_hours)` from `llx_planning_schedule` per date |
| Row 4 right | **Hours unplanned** | `available - planned` (delta, computed) |

These 3 rows appear as a **footer strip below the grid** in the Dolibarr implementation.

### Day column grouping (top right)

Row 1 right: week labels `WEEK 5`, `WEEK 6`, `WEEK 7` spanning multiple day columns.
Row 5: day labels format `26-Jan-Mon`, `27-Jan-Tue`, …

Dolibarr grid: week group headers in `<colgroup>` or `<th colspan>` above date row.

---

### Row structure and customer grouping

Rows are **grouped by customer** — not by workstation section. Each customer block:
- has a distinct **background colour** (cyan = EADS, dark grey = DASSAULT/SONACA, etc.)
- is separated by **blank spacer rows** (hidden or empty)
- may have **multiple MOs** for the same customer in the same block

| Customer | Cust No | Row colour |
|----------|---------|------------|
| EADS | 3832 | Cyan / light blue |
| DASSAULT | 3804 | Dark grey |
| SONACA | 4167 | Dark grey |
| GARDNER AERO | 5273 | Dark grey |
| BARAT | 5075 | Dark grey |
| MORGAN | 3805 | Dark grey |
| CPP | 3818 | Dark grey |
| BTA | 5262 | Dark grey |
| HYDE | 3812 | Dark grey |

Dolibarr grid: group rows by `llx_societe.nom` with a customer header row (collapsible).

---

### Material status flags (columns R / S / T)

Legend from cell I1–M2:
```
TIC  1 = Material call off = Yes    3 = No
DEE  1 = Material at NSW = Yes      3 = AT NSK
```

| Column | Flag | Code meaning |
|--------|------|--------------|
| R | Metal called off (TIC) | 1 = called off ✅  3 = not called off ⛔ |
| S | Metal @ WORC (DEE) | 1 = at Worcester ✅  3 = at NSK (off-site) ⛔ |
| T | Job Wallet printed | 1 = printed ✅  3 = not printed ⛔ |

Cell value `1` → green fill. Cell value `3` → red fill.
These are stored as **MO extrafields** in Dolibarr.
Grid shows coloured badge per row (same RAG logic).

---

## 1. What already exists in the `planning` module

| Feature | Status | File |
|---------|--------|------|
| Kanban board by workstation | ✅ done | `board.php` |
| Ghost cards (24h after Produced) | ✅ done | `board.php` |
| Dynamic sections (FORMING / TRIMMING) | ✅ done | `board.php` |
| Complete badge per section | ✅ done | `board.php` |
| Timeline / Gantt (day columns) | ✅ done | `timeline.php` |
| Gantt bars proportional to hours | ✅ done | `timeline.php` |
| Create MO from timeline | ✅ done | `ajax/add_job.php` |
| qty_done from optracking | ✅ done | `board_data.php` |
| Shadow cards | ✅ done | `board.php` |

---

## 2. What needs to be built — the day allocation grid

### The FM4 Excel day columns = `llx_planning_schedule`

Each cell in the Excel grid means: **"MO X is planned to run Y hours on date Z"**.
This is the only data that doesn't yet exist in Dolibarr.

### New table

```sql
CREATE TABLE IF NOT EXISTS llx_planning_schedule (
    rowid          INT AUTO_INCREMENT PRIMARY KEY,
    fk_mo          INT NOT NULL,
    schedule_date  DATE NOT NULL,
    planned_hours  DECIMAL(5,2) NOT NULL DEFAULT 0,
    fk_ws          INT,                        -- workstation for this slot
    qty_planned    DECIMAL(10,2),              -- derived: planned_hours * qty_per_hr
    source         VARCHAR(20) DEFAULT 'manual', -- 'manual' | 'excel' | 'api'
    fk_user        INT,
    date_sync      DATETIME,
    entity         INT NOT NULL DEFAULT 1,
    UNIQUE KEY uq_mo_date_ws (fk_mo, schedule_date, fk_ws, entity)
) ENGINE=InnoDB;
```

---

## 3. New view: Planning Grid (`planning/grid.php`)

This is the Dolibarr equivalent of the FM4 Excel sheet — a scrollable 2D grid:

```
Rows    = active MOs (grouped by CUSTOMER, within machine filter)
Columns = days (Mon–Sat, rolling 3–7 weeks, grouped by WEEK N header)
Cell    = planned hours input (editable, saved via AJAX)
```

### Machine selector

URL: `grid.php?fk_ws=FM4` — dropdown at top: `[FM2] [FM3] [FM4] [FM5] …`
One grid = one machine. Switch machine = reload grid, same date window.

### Visual design (faithful to Excel)

```
Machine: [FM4 ▼]   Shift: 06:00  Work Hrs: 12       [Info Board]

              ← WEEK 5 ──────────────┬── WEEK 6 ──────────────┬── WEEK 7 …
              Mon    Tue    Wed    Thu│Fri    Mon    Tue    Wed │Thu …
 Job / Part   26/01  27/01  28/01  29 │30/01  02/02  03/02  04/0│05/02
─────────────┼──────────────────────────────────────────────────────────
▼ EADS (3832)│
 FG1090  JW# │  6      6                     6      6
 FG1091  JW# │         6      6
 FG1287  JW# │                      12      20
─────────────┼──────────────────────────────────────────────────────────
▼ DASSAULT   │
 FG1118  JW# │  [3][3][3]   ← red badges = material not called off
 FG1119  JW# │  [3][3][3]
─────────────┼──────────────────────────────────────────────────────────
─────────────┴──────────────────────────────────────────────────────────
 Avail hrs   │  12     12     12     12     12     12     12     12
 Planned hrs  │   6     12      6     12     26      6      6      0
 Unplanned    │  +6      0     +6      0    ─14     +6     +6    +12   ← red if negative
```

### Material status badges per row (columns R/S/T from Excel)

Each MO row shows 3 small coloured badges after the job number:
```
[TIC] [DEE] [JW]
```
- Green (1) = OK / called off / printed
- Red (3) = not done / missing / at NSK

### Cell colour logic (same as Excel)

| Condition | Colour |
|---|---|
| `planned_hours > 0` and within capacity | green |
| `planned_hours > 0` and total for day > available | yellow / orange |
| MO overdue (today > `date_end`) and still has unplanned hours | red |
| Cell empty | white |

### Interaction

- **Click cell** → editable `<input type="number">`, blur → AJAX save
- **Shift-click drag** → fill a range with the same value (like Excel fill)
- **Click MO row header** → open MO detail in side panel (reuse timeline detail card)
- Column header = date; row header = MO ref + part label + batch qty + remaining hours

---

## 4. Cycle time / Qty per hour — source in DB

> **Confirmed from `llx_product_extrafields`:** qty per hour is stored **directly per product**
> as a decimal field — no calculation from cycle time needed.

### `llx_product_extrafields` columns (relevant to planning)

| Column | Type | Description |
|--------|------|-------------|
| `qty_per_hour_form` | DECIMAL | Parts/hour on **forming** machines (FM2–FM9) |
| `qty_per_hour_laser` | DECIMAL | Parts/hour on laser machines |
| `qty_per_hour_cnc` | DECIMAL | Parts/hour on CNC machines |
| `qty_per_sheet` | DECIMAL | Blanks per sheet (for material planning) |
| `form_tool` | VARCHAR | Form tool reference (e.g. `F1558`) |
| `trim_tool` | VARCHAR | Trim tool reference |
| `customer_pn` | VARCHAR | Customer part number (e.g. `14AD645CP`) |
| `customer` | VARCHAR | Customer name on product |
| `drawing` | VARCHAR | Drawing reference |
| `part_issue` | VARCHAR | Drawing issue/revision |
| `deviation_from_drawing` | VARCHAR | Active deviation reference |

### Which field to use per machine type

> **Confirmed from DB (`llx_product_extrafields`):** Forming, Laser, and CNC machines each use a
> **separate dedicated extrafield** for qty/hr — they are independent columns, not a single shared field.
> Each product can have all three values set independently (e.g. same part runs at 16/hr on forming,
> 20/hr on laser, 12/hr on CNC — as seen in live data).

| Grid filter | Qty/hr extrafield | Notes |
|-------------|-------------------|-------|
| FM2–FM9 (forming machines) | `llx_product_extrafields.qty_per_hour_form` | Confirmed in DB |
| Laser machines | `llx_product_extrafields.qty_per_hour_laser` | Confirmed in DB |
| CNC machines | `llx_product_extrafields.qty_per_hour_cnc` | Confirmed in DB |

The `grid.php` machine selector **must switch the qty/hr field** depending on the workstation type.
Workstation type is resolved from `llx_workstation_workstation` — the label prefix (`FM`, `LASER`, `CNC`)
or a dedicated `type` column determines which extrafield to read.

### Derived values (all computed, not stored)

```
qty_per_hr     = llx_product_extrafields.qty_per_hour_form   (for forming grid)
total_made     = SUM(qty_delta) FROM llx_mrp_mo_optracking WHERE fk_mo = X
remaining_qty  = llx_mrp_mo.qty - total_made
remaining_hrs  = remaining_qty / qty_per_hr
pct_complete   = total_made / llx_mrp_mo.qty * 100
```

### Join path: MO → product extrafields

```sql
SELECT
    m.rowid         AS fk_mo,
    m.ref           AS mo_ref,
    m.qty           AS batch_qty,
    p.ref           AS part_ref,
    pe.qty_per_hour_form AS qty_per_hr,
    pe.qty_per_sheet,
    pe.form_tool,
    pe.customer_pn,
    COALESCE(SUM(ot.qty_delta), 0) AS total_made
FROM llx_mrp_mo m
LEFT JOIN llx_product p ON p.rowid = m.fk_product
LEFT JOIN llx_product_extrafields pe ON pe.fk_object = p.rowid
LEFT JOIN llx_mrp_mo_optracking ot ON ot.fk_mo = m.rowid
WHERE m.entity = 1
  AND m.status IN (1, 2)   -- In progress or Produced
GROUP BY m.rowid, m.ref, m.qty, p.ref, pe.qty_per_hour_form, pe.qty_per_sheet, pe.form_tool, pe.customer_pn
```

### Row header in grid

Each MO row shows (from the above query):
```
[FG1090] 14AD645CP  Batch:6  Done:4  Rem:0.1h  F1558  ██████░░ 67%
          ^custpn    ^qty    ^optrk  ^derived  ^tool  ^progress bar
```

Remaining hours drives the **cell colour** and **overload detection**.

---

## 5. Shift parameters (equivalent of S3, G3 and the `Shifts Control` sheet)

> **Key constraint:** Machines run on **variable shift patterns** — either **8-hour** or **12-hour**
> shifts depending on the day and week. The available hours per day column in the grid **must
> reflect the actual shift length for that specific machine on that specific date**, not a fixed value.

### Shift variants observed in the Excel

| Pattern | Hours | Typical use |
|---------|-------|-------------|
| Day shift only | **8 h** | Mon–Fri normal week, reduced manning |
| Double / extended shift | **12 h** | High demand weeks, overtime |
| Weekend shift | **8 h** or **0** | Saturdays sometimes run, Sundays off |
| Bank holiday / shutdown | **0 h** | No production |

### Why `shift_hours` must be per-day, not per-machine

A single `shift_hours = 12` column in `llx_planning_ws_config` is **not enough**.
The same machine can run 12h Monday, 8h Tuesday, and 0h Sunday in the same week.

### Revised table: `llx_planning_ws_config`

Replaces the single `shift_hours` with **per-weekday hours** so each day can be different:

```sql
CREATE TABLE IF NOT EXISTS llx_planning_ws_config (
    rowid           INT AUTO_INCREMENT PRIMARY KEY,
    fk_ws           INT NOT NULL,
    shift_start     TINYINT DEFAULT 6,       -- shift start hour 0-23 (S3)
    -- Available hours per weekday (0 = not working that day)
    hrs_mon         DECIMAL(4,1) DEFAULT 12,
    hrs_tue         DECIMAL(4,1) DEFAULT 12,
    hrs_wed         DECIMAL(4,1) DEFAULT 12,
    hrs_thu         DECIMAL(4,1) DEFAULT 12,
    hrs_fri         DECIMAL(4,1) DEFAULT 12,
    hrs_sat         DECIMAL(4,1) DEFAULT 0,
    hrs_sun         DECIMAL(4,1) DEFAULT 0,
    entity          INT NOT NULL DEFAULT 1,
    UNIQUE KEY uq_ws (fk_ws, entity)
) ENGINE=InnoDB;
```

For weeks with non-standard hours (e.g. bank holidays, shutdown weeks), the planner
**overrides individual day cells** directly in the "Hours Available" row in the grid
via a separate override table:

```sql
CREATE TABLE IF NOT EXISTS llx_planning_hrs_override (
    rowid           INT AUTO_INCREMENT PRIMARY KEY,
    fk_ws           INT NOT NULL,
    override_date   DATE NOT NULL,
    available_hours DECIMAL(4,1) NOT NULL,  -- 0 = closed, 8 = short shift, 12 = full shift
    note            VARCHAR(100),           -- e.g. "Bank Holiday", "Overtime", "Shutdown"
    fk_user         INT,
    entity          INT NOT NULL DEFAULT 1,
    UNIQUE KEY uq_ws_date (fk_ws, override_date, entity)
) ENGINE=InnoDB;
```

### Available hours resolution logic (priority order)

```
1. llx_planning_hrs_override   → specific date override (highest priority)
2. llx_planning_ws_config      → weekday default (hrs_mon / hrs_tue / …)
3. Fallback                    → 0 (assume no shift if no config)
```

### Grid "Hours Available" row — editing

The **Hours Available** row at the bottom of the grid is **editable** by the planner:
- Click a cell in the row → enter override hours (0, 8, or 12)
- Saves to `llx_planning_hrs_override` for that machine + date
- Red if `planned > available`, green if OK, grey if `available = 0`

### Admin UI — Shift Config (`planning/admin/setup.php`)

One table per machine — editable grid showing weekday defaults:

```
Machine  | Start | Mon | Tue | Wed | Thu | Fri | Sat | Sun |
FM2      |  06:00|  12 |  12 |  12 |  12 |  12 |   8 |   0 |
FM3      |  06:00|  12 |  12 |  12 |  12 |  12 |   8 |   0 |
FM4      |  06:00|  12 |  12 |  12 |  12 |  12 |   8 |   0 |
...
```

Click any cell to change the default hours. Saved via AJAX to `llx_planning_ws_config`.

The `QTY per hour` sheet → qty/hr stored **directly** in `llx_product_extrafields.qty_per_hour_form`
(confirmed from DB). Global fallback (if extrafield is NULL) → derive from machine config or default 1.

---

## 6. Integration with existing board / timeline

The grid is a **3rd view** alongside Board and Timeline — same data, different UX:

| View | Best for |
|------|----------|
| `board.php` | Shop floor — tablet, fullscreen, real-time status |
| `timeline.php` | Planner — Gantt drag, job creation |
| `grid.php` (new) | Planner — capacity allocation, weekly overview, Excel-familiar |

All three read from the same `llx_mrp_mo` + `llx_planning_schedule` + `llx_mrp_mo_optracking`.

---

## 7. Implementation phases

### PHASE 1 — `llx_planning_schedule` table + basic grid ✅ DONE (2026-03-27)
- [x] SQL: `sql/llx_planning_schedule.sql`
- [x] `planning.php` — static render: MO rows × day columns, week group headers
- [x] Day column width configurable via `?weeks=N` (1–7 weeks)
- [x] Row grouping by customer (`llx_societe.nom`)
- [x] Shift capacity row (available vs planned per day in footer)

### PHASE 2 — Editable cells via AJAX ✅ DONE (2026-03-27)
- [x] `ajax/schedule_save.php` — POST (fk_mo, date, hours, fk_ws) → UPSERT `llx_planning_schedule`
- [x] Click-to-edit cell with inline `<input>`
- [x] Colour logic: green / yellow (overloaded) / red (late MO) based on capacity
- [x] Capacity override row — inline edit → `ajax/capacity_save.php`
- [x] `llx_planning_capacity_override` table + UPSERT/DELETE endpoint

### PHASE 3 — Qty per hour + Calc engine ✅ DONE (2026-03-27)
- [x] Remaining hrs + % progress bar shown in MO row header
- [x] `ajax/calc.php` — full Calc engine: DELETE schedule from today + INSERT new rows
  - Loads MO queue (status 1/2, ordered by date_start_planned ASC)
  - Resolves qty_per_hr from product_extrafields (form/laser/cnc by WS type)
  - Fills capacity day by day (ws_config + override)
  - Returns JSON summary + warnings for MOs with no qty_per_hr
- [x] Calc button in toolbar → confirm dialog → fetch → page reload (`js/planning.js`)
- [x] `data-calc-endpoint` on grid page element

### PHASE 4 — Shift config in admin ✅ DONE (2026-03-27)
- [x] `admin/planning_setup.php` — per-workstation shift hours table (Mon–Sun + shift_start)
- [x] `llx_planning_ws_config` — per-weekday hours, UPSERT via form POST
- [x] Available hours in grid footer resolved: override > ws_config > 0

### PHASE 5 — Timeline sync ✅ DONE (2026-04-01)
- [x] `ajax/calc.php` lines 425–451: writes `date_start_planned` / `date_end_planned` on each MO after scheduling
- [x] `date_start_planned_locked` field respected — locked MOs keep their start date
- [x] MOs with no schedule slots get `date_start_planned = NULL` / `date_end_planned = NULL` (cleared)

> **Note:** The Gantt bar in `mo_gantt.php` derives from `llx_mrp_mo.date_start_planned` /
> `date_end_planned` — which are now written by Calc. The grid → Gantt sync is therefore
> automatic after running Calc.

### PHASE 6 — Material status badges (TIC / DEE / JW) 🔲 TODO
> From Excel columns R/S/T — per MO row badges in grid header
- [ ] Add extrafields to `llx_mrp_mo_extrafields`: `metal_calloff` (TIC), `metal_at_worc` (DEE), `job_wallet` (JW)
- [ ] Include in `planning.php` SQL query
- [ ] Render badges in row header: green (1=OK) / red (3=not done)
- [ ] Inline toggle (click badge → flip value 1↔3 → AJAX save to MO extrafields)

### PHASE 7 — Board integration 🔲 TODO
- [ ] `board.php` / `board_data.php`: JOIN `llx_planning_schedule`
- [ ] Show today's planned hours as badge on board card
- [ ] Highlight MOs where `planned_hours > total_time` (overallocated)

### PHASE 8 — UX: shift-click range fill 🔲 TODO (low priority)
- [ ] Shift-click drag across day cells → fill range with same value (Excel-style)

---

## 8. FM4 column mapping → Dolibarr fields

| Excel col | Field | Dolibarr |
|-----------|-------|----------|
| A | Lookup | `llx_mrp_mo.rowid` |
| B | Part No | `llx_product.ref` |
| H | Works Order No | `llx_mrp_mo_extrafields.job_number` |
| L | Customer No | `llx_societe.code_client` |
| M | Customer | `llx_societe.nom` |
| N | Description | `llx_mrp_mo.label` |
| O | Batch QTY | `llx_mrp_mo.qty` |
| P | Qty/hr | `llx_product_extrafields.qty_per_hour_form` (direct — confirmed from DB) |
| Q | Total Time | `qty / qty_per_hr` (computed) |
| P4 | Total Made | `SUM(optracking.qty_delta)` |
| S3 | Starting Time | workstation extrafield `shift_start` |
| G3 | Work Hrs | workstation extrafield `shift_hours` |
| X+ | Day hours | `llx_planning_schedule.planned_hours` |
| colours | Schedule status | computed from remaining_hours vs planned_hours |

Columns C/D/E/F/G/J/K/R/T/U/V (tooling, metal, fixtures, flags) → MO extrafields or job card module fields.

---

## 9. VBA fallback (if grid UI build is deferred)

If building `grid.php` is too much work upfront, the VBA sync from the original
plan can be used as a temporary bridge:

- Excel stays as the planning input
- VBA macro POSTs to `planning/ajax/excel_sync.php`
- Dolibarr board/timeline automatically reflects the imported data
- When `grid.php` is ready, switch operators to use it and retire the Excel

The VBA code and Excel sync endpoint are documented in the original
`TODO_Planning_update.md` sections 2–5.

### Fixed header cells (Row 1–4)

| Cell | Meaning | Notes |
|------|---------|-------|
| `S3` | Starting time (hour) | e.g. `6` = shift starts 06:00 |
| `P3` | T = Cycle Time (sec or min per part) | e.g. `90` |
| `P4` | M = Total Made today | live counter from optracking |
| `G3` | Work Hrs available | e.g. `12` |
| `Q3` | `Calc` button range | triggers recalc |

### Column map (row 5 = header, rows 6–N = data)

| Col | Field | Dolibarr target |
|-----|-------|-----------------|
| A | Lookup (MO rowid or job ref) | `llx_mrp_mo.rowid` / `ref` |
| B | Part No | `llx_mrp_mo` → product ref |
| C | Tool No | `llx_mrp_production.fk_product` (tooling) |
| D | Metal Code | extra field or product ref |
| E | Parts per sheet | `llx_mrp_production.qty` |
| F | Setup Dia | extra field |
| G | Clamp sheet req | extra field |
| H | Works Order No | `llx_mrp_mo_extrafields.job_number` or `ref` |
| I | Pack Number | extra field |
| J | Metal Cut | checkbox / status flag |
| K | Form Fixture | checkbox |
| L | Customer No | `llx_mrp_mo.fk_soc` |
| M | Customer | `llx_societe.nom` |
| N | Description | `llx_mrp_mo` label |
| O | Batch QTY | `llx_mrp_mo.qty` |
| P | Qty/hr | derived from cycle time |
| Q | Total Time | `estimated_hours` (= Batch QTY / Qty/hr) |
| R | Later Stage | flag — ops done downstream |
| S | Starting Time | shift start offset (hours from 00:00) |
| T | Metal called off | flag |
| U | Metal @ WORC | flag |
| V | Job Wallet | job wallet printed flag |
| W | Notes | `llx_mrp_mo.note_public` |
| X+ | **Day columns** (Mon–Sun × weeks) | `llx_planning_schedule.planned_hours` per day |

### Day column convention

- Column X onward = 1 col per weekday
- Row 3 = date label (`26-Jan-Mon`, `27-Jan-Tue`, …)
- Cell value = **hours planned** for that MO on that day
- Green fill = scheduled
- Yellow fill = partial / warning
- Red fill = overloaded / late

### Hidden rows

Some jobs are hidden (grouped/filtered):
- **First data row** = row 6 (after header row 5)
- Hidden rows contain jobs that are completed or not yet released
- The macro must **unhide all rows** in the data range before reading

---

## 2. VBA — Phase A: Read & Parse Sheet

```vba
' ============================================================
' SyncPlanToDolibarr — Phase A: Read sheet data
' ============================================================
Sub SyncPlanToDolibarr()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("FM4")   ' adjust sheet name

    ' --- 1. Unhide all data rows ---
    Dim firstDataRow As Long
    Dim lastRow As Long
    firstDataRow = 6

    ' Find last row with data in column A (Lookup)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Unhide rows firstDataRow..lastRow
    ws.Rows(firstDataRow & ":" & lastRow).Hidden = False

    ' --- 2. Read shift parameters ---
    Dim shiftStart  As Double   ' S3 — e.g. 6 (= 06:00)
    Dim cycleTime   As Double   ' P3 — cycle time in seconds
    Dim totalMade   As Long     ' P4 — parts made today
    Dim workHrs     As Double   ' G3 — available hours

    shiftStart = ws.Range("S3").Value
    cycleTime  = ws.Range("P3").Value
    totalMade  = ws.Range("P4").Value
    workHrs    = ws.Range("G3").Value

    ' --- 3. Find day columns ---
    ' Row 3 has date labels starting at column X (col 24 = "X")
    Dim firstDayCol As Long
    Dim lastDayCol  As Long
    firstDayCol = 24   ' adjust to actual first day column index

    ' Find last day column (scan row 3 until empty)
    lastDayCol = firstDayCol
    Do While ws.Cells(3, lastDayCol + 1).Value <> ""
        lastDayCol = lastDayCol + 1
    Loop

    ' --- 4. Read each job row ---
    Dim jobs() As JobRecord
    Dim jobCount As Long
    jobCount = 0
    ReDim jobs(1 To lastRow - firstDataRow + 1)

    Dim r As Long
    For r = firstDataRow To lastRow

        ' Skip completely empty rows
        If ws.Cells(r, 1).Value = "" Then GoTo NextRow

        Dim j As JobRecord
        j.Lookup     = CStr(ws.Cells(r, 1).Value)   ' A — MO ref / rowid
        j.PartNo     = CStr(ws.Cells(r, 2).Value)   ' B
        j.WorksOrder = CStr(ws.Cells(r, 8).Value)   ' H
        j.BatchQty   = CDbl(ws.Cells(r, 15).Value)  ' O
        j.QtyPerHr   = CDbl(ws.Cells(r, 16).Value)  ' P
        j.TotalTime  = CDbl(ws.Cells(r, 17).Value)  ' Q

        ' Day allocations: array of (date, hours)
        Dim dayCount As Long
        dayCount = lastDayCol - firstDayCol + 1
        ReDim j.DayHours(1 To dayCount)
        ReDim j.DayDates(1 To dayCount)

        Dim c As Long
        For c = 1 To dayCount
            j.DayDates(c) = CStr(ws.Cells(3, firstDayCol + c - 1).Value) ' e.g. "26-Jan-Mon"
            Dim cellVal As Variant
            cellVal = ws.Cells(r, firstDayCol + c - 1).Value
            If IsNumeric(cellVal) And cellVal > 0 Then
                j.DayHours(c) = CDbl(cellVal)
            Else
                j.DayHours(c) = 0
            End If
        Next c

        jobCount = jobCount + 1
        jobs(jobCount) = j

NextRow:
    Next r

    ' --- 5. Hand off to Phase B (HTTP push) ---
    Call PushToDolibarr(jobs, jobCount)

    MsgBox "Sync complete. " & jobCount & " jobs processed.", vbInformation

End Sub

' ---- Helper type ----
Type JobRecord
    Lookup     As String
    PartNo     As String
    WorksOrder As String
    BatchQty   As Double
    QtyPerHr   As Double
    TotalTime  As Double
    DayDates() As String
    DayHours() As Double
End Type
```

---

## 3. VBA — Phase B: Push to Dolibarr REST API

Dolibarr exposes a REST API at `/api/index.php`. We will call a **custom
endpoint** `GET /custom/planning/ajax/excel_sync.php` that accepts a JSON payload
and writes directly to the DB — cleaner than chaining 50 standard REST calls.

```vba
' ============================================================
' PushToDolibarr — Phase B: HTTP POST
' ============================================================
Sub PushToDolibarr(jobs() As JobRecord, jobCount As Long)

    Dim baseUrl  As String
    Dim apiKey   As String
    baseUrl = "https://develop.for-our.info/custom/planning/ajax/excel_sync.php"
    apiKey  = "YOUR_DOLIBARR_API_KEY"    ' or use session token

    ' Build JSON payload
    Dim json As String
    json = "{""jobs"":["

    Dim i As Long
    For i = 1 To jobCount
        Dim j As JobRecord
        j = jobs(i)

        ' Build day_alloc array
        Dim dayAlloc As String
        dayAlloc = "["
        Dim d As Long
        For d = 1 To UBound(j.DayDates)
            If j.DayHours(d) > 0 Then
                ' Parse "26-Jan-Mon" → ISO date  (helper function below)
                Dim isoDate As String
                isoDate = ParseExcelDateLabel(j.DayDates(d))   ' "2026-01-26"
                dayAlloc = dayAlloc & "{""date"":""" & isoDate & """," & _
                           """hours"":" & j.DayHours(d) & "},"
            End If
        Next d
        If Right(dayAlloc, 1) = "," Then dayAlloc = Left(dayAlloc, Len(dayAlloc) - 1)
        dayAlloc = dayAlloc & "]"

        json = json & "{" & _
               """lookup"":"""  & EscapeJson(j.Lookup)     & """," & _
               """works_order"":""" & EscapeJson(j.WorksOrder) & """," & _
               """batch_qty"":"    & j.BatchQty & "," & _
               """qty_per_hr"":"   & j.QtyPerHr & "," & _
               """total_time"":"   & j.TotalTime & "," & _
               """day_alloc"":"    & dayAlloc & "},"
    Next i

    If Right(json, 1) = "," Then json = Left(json, Len(json) - 1)
    json = json & "]}"

    ' HTTP POST
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP.6.0")
    http.Open "POST", baseUrl, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "DOLAPIKEY", apiKey
    http.Send json

    If http.Status <> 200 Then
        MsgBox "HTTP error " & http.Status & ": " & http.responseText, vbCritical
    End If

End Sub

' ---- Parse "26-Jan-Mon" → "2026-01-26" ----
Function ParseExcelDateLabel(label As String) As String
    ' label format: "DD-Mon-DayName" e.g. "26-Jan-Mon"
    Dim parts() As String
    parts = Split(label, "-")
    If UBound(parts) < 1 Then ParseExcelDateLabel = "" : Exit Function

    Dim dayNum  As String
    Dim monStr  As String
    dayNum = parts(0)
    monStr = parts(1)

    Dim monthNum As String
    Select Case LCase(monStr)
        Case "jan": monthNum = "01"
        Case "feb": monthNum = "02"
        Case "mar": monthNum = "03"
        Case "apr": monthNum = "04"
        Case "may": monthNum = "05"
        Case "jun": monthNum = "06"
        Case "jul": monthNum = "07"
        Case "aug": monthNum = "08"
        Case "sep": monthNum = "09"
        Case "oct": monthNum = "10"
        Case "nov": monthNum = "11"
        Case "dec": monthNum = "12"
        Case Else:  monthNum = "00"
    End Select

    ' Year: derive from current year (assumes plan is always current or next year)
    Dim yr As String
    yr = CStr(Year(Date))

    ParseExcelDateLabel = yr & "-" & monthNum & "-" & Format(CInt(dayNum), "00")
End Function

' ---- Escape JSON string ----
Function EscapeJson(s As String) As String
    s = Replace(s, "\", "\\")
    s = Replace(s, """", "\""")
    EscapeJson = s
End Function
```

---

## 4. Dolibarr — PHP endpoint: `planning/ajax/excel_sync.php`

This file receives the POST, validates auth, and writes to the DB.

### Tables to write

| Table | Action | When |
|-------|--------|------|
| `llx_planning_schedule` | UPSERT row per (fk_mo, date) | Always |
| `llx_mrp_mo_optracking` | INSERT if hours > 0 | Optional — feed qty tracking |

### `llx_planning_schedule` (new table — needs SQL migration)

```sql
CREATE TABLE IF NOT EXISTS llx_planning_schedule (
    rowid        INT AUTO_INCREMENT PRIMARY KEY,
    fk_mo        INT NOT NULL,
    schedule_date DATE NOT NULL,
    planned_hours DECIMAL(5,2) NOT NULL DEFAULT 0,
    qty_per_hr    DECIMAL(10,3),
    source        VARCHAR(20) DEFAULT 'excel',   -- 'excel' | 'manual' | 'api'
    fk_user       INT,
    date_sync     DATETIME,
    entity        INT NOT NULL DEFAULT 1,
    UNIQUE KEY uq_mo_date (fk_mo, schedule_date, entity)
) ENGINE=InnoDB;
```

### `excel_sync.php` logic (pseudocode)

```
1. Auth check (DOLAPIKEY header or session)
2. Decode JSON body
3. For each job:
   a. Resolve fk_mo:
      - Try llx_mrp_mo.rowid if lookup is numeric
      - Else try llx_mrp_mo.ref LIKE lookup
      - Else try llx_mrp_mo_extrafields.job_number = works_order
   b. For each day_alloc entry (date + hours):
      - UPSERT llx_planning_schedule (fk_mo, schedule_date, planned_hours, source='excel')
4. Return JSON {status: ok, synced: N, skipped: M}
```

---

## 5. cycle time → qty/hr calculation (Excel side)

The Excel already has P3 = cycle time (seconds), so:

```vba
' Qty per hour from cycle time
j.QtyPerHr = 3600 / cycleTime        ' parts per hour
j.TotalTime = j.BatchQty / j.QtyPerHr  ' hours needed
```

Starting time offset from `S3`:
```vba
' Calculate planned start date/time for a job
' shiftStart = S3 value (e.g. 6 for 06:00)
Dim startHour As Date
startHour = TimeSerial(shiftStart, 0, 0)
```

---

## 6. Colour-coded cell read (optional — detect green/yellow/red)

```vba
' Read fill colour of day cell to detect status
Dim cellColor As Long
cellColor = ws.Cells(r, firstDayCol + c - 1).Interior.Color

Select Case cellColor
    Case RGB(0, 255, 0), RGB(146, 208, 80)   ' green shades
        j.DayStatus(c) = "scheduled"
    Case RGB(255, 255, 0), RGB(255, 217, 102) ' yellow
        j.DayStatus(c) = "partial"
    Case RGB(255, 0, 0), RGB(255, 102, 102)   ' red
        j.DayStatus(c) = "overload"
    Case Else
        j.DayStatus(c) = ""
End Select
```

Pass `status` in the JSON payload → stored in `llx_planning_schedule.status`.

---

## 7. Implementation Phases

### PHASE 1 — Excel read + JSON output to file (no Dolibarr yet) 🔲
- [ ] Implement `SyncPlanToDolibarr()` — unhide rows, read data
- [ ] Implement `ParseExcelDateLabel()` + `EscapeJson()`
- [ ] Output JSON to `C:\Temp\plan_sync.json` for inspection
- [ ] Verify all rows and day allocations are captured correctly

### PHASE 2 — Dolibarr endpoint `excel_sync.php` 🔲
- [ ] Create `llx_planning_schedule` SQL table
- [ ] Write `planning/ajax/excel_sync.php`:
  - Auth, MO resolution (rowid → ref → job_number fallback)
  - UPSERT `llx_planning_schedule`
  - Return counters
- [ ] Test with `curl` / Postman before connecting VBA

### PHASE 3 — Wire VBA HTTP POST 🔲
- [ ] Replace JSON file write with `MSXML2.XMLHTTP.6.0` POST
- [ ] Handle HTTP errors gracefully (dialog + log to sheet cell)
- [ ] Add progress indicator (status bar `Application.StatusBar`)

### PHASE 4 — Planning Board reads `llx_planning_schedule` 🔲
- [ ] Update `board.php` / `board_data.php` to JOIN `llx_planning_schedule`
- [ ] Show planned hours per MO per day as a tooltip or badge
- [ ] Highlight MOs where `planned_hours` > `total_time` (overallocated)

### PHASE 5 — Reverse sync (optional) 🔲
- [ ] Dolibarr → Excel: read `llx_mrp_mo_optracking.qty_delta` and write
  actual produced qty back into column P4 area

---

## 8. Key decisions / open questions

- [ ] **MO identification**: is column A the `rowid` or the `ref` string? Or is
  column H (Works Order No) the reliable key? → Decide before Phase 2.
- [ ] **Date year handling**: `ParseExcelDateLabel` assumes current year — does
  the plan ever span a year boundary (e.g. Dec→Jan)? Need cross-year logic.
- [ ] **Delta sync vs full sync**: UPSERT on (fk_mo, date) means full re-sync
  is safe (idempotent). Or track a `last_synced` timestamp and only push changed cells.
- [ ] **Who triggers the sync**: manual button in Excel, or scheduled VBA
  `Workbook_BeforeSave` event?
- [ ] **API key storage**: store in a hidden named range `_DOLI_API_KEY` in the
  workbook, or prompt user on first run and store in registry.