# Dolibarr Module: oee (OEE + Downtime Log)

## GOAL

Build a combined **Downtime Log + OEE (Overall Equipment Effectiveness)** module
inside Dolibarr. Operators register machine stoppages; the module calculates
Availability, Performance, and Quality per workstation.

Integrates with:
- `llx_workstation_workstation` (machines)
- `llx_mrp_mo_optracking` (actual production quantities)
- `llx_scrap` (scrap qty for Quality factor)
- Scrap module (existing)

---

## SCOPE (MVP - ETAP 1)

* Downtime Log — operator registers stoppage: machine, time from/to, reason category
* Shift Plan — define planned production time per workstation per shift
* OEE Dashboard — Availability / Performance / Quality / OEE per machine (daily / weekly / monthly)
* Reason categories (configurable): Breakdown / Changeover / No Material / No Operator / Planned Maintenance / Other
* Permissions: read / write / admin
* Simple reports per workstation

DO NOT implement in ETAP 1:
* Predictive maintenance
* MTBF / MTTR calculations
* Mobile / tablet optimized interface
* Integration with PLC/SCADA

---

## MODULE INFO

* Name: oee
* Technical name: modOee
* Folder: htdocs/custom/oee/
* Rights class: oee
* Menu: Quality (under Quality top menu, same as Scrap and NCR)

---

## DIRECTORY STRUCTURE

htdocs/custom/oee/

* class/
  * downtime.class.php
  * shiftplan.class.php
  * oeecalc.class.php        (calculation helper, no DB)
* core/modules/
  * modOee.class.php
* sql/
  * install.sql
  * uninstall.sql
* css/
  * oee.css
* js/
  * oee.js
* pages/
  * index.php               (OEE dashboard)
  * downtime_list.php       (list all downtime records)
  * downtime_card.php       (create / edit downtime entry)
  * oee_report.php          (detailed OEE report per machine + period)
  * shiftplan_list.php      (manage shift plans)
  * shiftplan_card.php      (create / edit shift plan)

---

## OEE FORMULA

```
OEE = Availability × Performance × Quality

Availability = (Planned Time - Downtime) / Planned Time
Performance  = (Actual Output / Theoretical Output)
Quality      = (Good Parts) / (Total Parts)
            = 1 - (Scrapped / Total Produced)
```

### Data sources

| Factor | Source |
|--------|--------|
| Planned Time | llx_oee_shiftplan (planned minutes per workstation per day) |
| Downtime | llx_oee_downtime (sum of duration_min per workstation per day) |
| Actual Output | llx_mrp_mo_optracking SUM(qty_delta) per workstation per day |
| Theoretical Output | llx_oee_shiftplan.target_qty_per_shift |
| Scrapped | llx_scrap SUM(qty) per workstation per day |
| Total Produced | llx_mrp_production role='produced' per workstation per day |

---

## DATABASE SCHEMA

### TABLE: llx_oee_downtime

| Column | Type | Description |
|--------|------|-------------|
| rowid | INT PK | |
| fk_workstation | INT | FK to llx_workstation_workstation |
| reason_code | VARCHAR(30) | Breakdown / Changeover / NoMaterial / NoOperator / PlannedMaint / Other |
| reason_detail | TEXT | Optional free text |
| dt_start | DATETIME | Stoppage start |
| dt_end | DATETIME | Stoppage end (NULL = ongoing) |
| duration_min | INT | Computed: TIMESTAMPDIFF(MINUTE, dt_start, dt_end) |
| fk_user_creat | INT | Who logged it |
| fk_mo | INT | Related MO (optional) |
| entity | INT | |
| tms | TIMESTAMP | |

---

### TABLE: llx_oee_shiftplan

Defines planned production time and target output per workstation per shift.

| Column | Type | Description |
|--------|------|-------------|
| rowid | INT PK | |
| fk_workstation | INT | FK to llx_workstation_workstation |
| shift_label | VARCHAR(50) | e.g. "Morning", "Afternoon", "Night" |
| plan_date | DATE | Date this plan applies to |
| planned_minutes | INT | Net planned production time (minutes) |
| target_qty_per_shift | DOUBLE | Theoretical output (parts/shift) |
| fk_user_creat | INT | |
| entity | INT | |
| tms | TIMESTAMP | |

---

### TABLE: llx_oee_reason_category

Configurable reason categories (admin editable).

| Column | Type | Description |
|--------|------|-------------|
| rowid | INT PK | |
| code | VARCHAR(30) | UNIQUE, e.g. BREAKDOWN |
| label | VARCHAR(100) | Display label |
| color | VARCHAR(10) | Hex color for charts |
| is_planned | TINYINT | 1 = planned loss (excluded from Availability calc) |
| entity | INT | |

Default seed data:
| Code | Label | Planned |
|------|-------|---------|
| BREAKDOWN | Machine Breakdown | 0 |
| CHANGEOVER | Setup / Changeover | 1 |
| NO_MATERIAL | No Material | 0 |
| NO_OPERATOR | No Operator | 0 |
| PLAN_MAINT | Planned Maintenance | 1 |
| OTHER | Other | 0 |

---

## CORE LOGIC

### Availability calculation

```
Unplanned Downtime = SUM(duration_min) WHERE reason.is_planned = 0
                     for given workstation + date range

Availability = (Planned Time - Unplanned Downtime) / Planned Time × 100%
```

Planned maintenance and changeover are EXCLUDED from availability loss
(standard OEE definition — they reduce planned time, not availability).

---

### Performance calculation

```
Performance = (Actual Output × Ideal Cycle Time) / Operating Time × 100%

Or simplified:
Performance = Actual Output / Target Output × 100%
```

Where:
- `Actual Output` = SUM(qty_delta) from `llx_mrp_mo_optracking` for the workstation
- `Target Output` = SUM(target_qty_per_shift) from `llx_oee_shiftplan`

---

### Quality calculation

```
Quality = (Total Produced - Scrapped) / Total Produced × 100%
```

Where:
- `Total Produced` = SUM from `llx_mrp_production` role='produced' for workstation
- `Scrapped` = SUM(qty) from `llx_scrap` WHERE fk_ws = workstation

---

### Duration auto-calculation

When `dt_end` is saved → `duration_min` = TIMESTAMPDIFF(MINUTE, dt_start, dt_end).
Stored redundantly for fast aggregation queries.

---

## PERMISSIONS

| Right | Description |
|-------|-------------|
| `read` | View OEE dashboard and reports |
| `write` | Log downtime entries |
| `admin` | Manage shift plans, reason categories |

---

## UI / PAGES

### 1. OEE Dashboard (index.php)

* Date range selector (default: today)
* Workstation selector (all or single)
* Cards per workstation:
  * Availability % (green/yellow/red)
  * Performance %
  * Quality %
  * **OEE %** (large, color-coded: ≥85% green, 60-85% yellow, <60% red)
* Bar chart: OEE per workstation (Chart.js)
* Downtime breakdown pie chart per reason category

---

### 2. Downtime List (downtime_list.php)

Table columns:
* Workstation | Start | End | Duration | Reason | MO | Logged by

Filters: Workstation, Date range, Reason code

---

### 3. Downtime Card (downtime_card.php)

* Select workstation (dropdown from llx_workstation_workstation)
* Reason category (dropdown from llx_oee_reason_category)
* dt_start (datetime picker, default = now)
* dt_end (datetime picker, or "Still ongoing" checkbox)
* Reason detail (textarea)
* Link to MO (optional autocomplete)

Quick-log mode: single form on dashboard — fill workstation + reason + times, submit.

---

### 4. OEE Report (oee_report.php)

* Select: workstation, date range, granularity (day / week / month)
* Table: Date | Planned min | Downtime min | Availability | Output | Target | Performance | Scrapped | Quality | OEE
* Line chart: OEE trend over time (Chart.js)
* Export: CSV

---

### 5. Shift Plan (shiftplan_list.php + shiftplan_card.php)

* List shift plans per workstation per date
* Create/edit: workstation, shift label, date, planned minutes, target qty
* Copy previous week's plan (bulk)

---

## MENU STRUCTURE

Under Quality top menu:

```
Quality
  └── OEE
        ├── Dashboard         → pages/index.php
        ├── Log Downtime      → pages/downtime_card.php?action=create
        ├── Downtime List     → pages/downtime_list.php
        ├── OEE Report        → pages/oee_report.php
        └── Shift Plans       → pages/shiftplan_list.php
```

---

## ETAP 1 checklist

- [ ] modOee.class.php — module descriptor
- [ ] install.sql — create llx_oee_downtime, llx_oee_shiftplan, llx_oee_reason_category
- [ ] uninstall.sql
- [ ] Seed data for reason categories in install.sql
- [ ] pages/index.php — OEE dashboard with cards + bar chart
- [ ] pages/downtime_card.php — log downtime entry
- [ ] pages/downtime_list.php — list with filters
- [ ] pages/oee_report.php — detailed report + trend chart
- [ ] pages/shiftplan_list.php + shiftplan_card.php
- [ ] class/oeecalc.class.php — Availability / Performance / Quality / OEE methods
- [ ] Duration auto-calc on save
- [ ] OEE color coding (≥85 green / 60-85 yellow / <60 red)
- [ ] CSV export from OEE report
- [ ] CSS + Chart.js integration

---

## ETAP 2 — MTBF / MTTR + Alerts

### MTBF / MTTR per workstation

```
MTBF (Mean Time Between Failures) = Operating Time / Number of Breakdowns
MTTR (Mean Time To Repair)        = Total Repair Time / Number of Breakdowns
```

Only `reason_code = BREAKDOWN` entries counted.

* Report page: MTBF / MTTR per workstation per month
* Trend chart: MTBF over time (improving = maintenance working)

### Downtime Alerts

* If a single downtime entry exceeds X minutes → create `llx_actioncomm` task for maintenance manager
* Threshold configurable per workstation (admin setting)
* Dashboard badge: "X ongoing stoppages"

### Recurring Downtime Detection

* If same workstation has BREAKDOWN > N times in 7 days → show warning on dashboard
* Threshold N configurable in module settings

### ETAP 2 checklist

- [ ] MTBF / MTTR calculation + report page
- [ ] Downtime alert threshold per workstation
- [ ] Auto-create actioncomm on long downtime
- [ ] Recurring breakdown detection + dashboard warning
- [ ] Module settings page (admin/setup.php)

---

## ETAP 3 — Predictive + Integration

### Maintenance Integration

* Link downtime BREAKDOWN entries to a Maintenance Work Order (future module)
* Show maintenance history on workstation card

### Shift Comparison

* Compare OEE across shifts (Morning vs Afternoon vs Night)
* Identify which shift has lowest performance

### Product-level OEE

* OEE drill-down per product (which product runs worst on which machine)
* Data from: optracking + scrap + MO

### Integration with Planning module

* If downtime overlaps with planned MO → flag MO as "at risk"
* Show expected delay

### ETAP 3 checklist

- [ ] Shift comparison report
- [ ] Product-level OEE breakdown
- [ ] Maintenance WO link (when maintenance module exists)
- [ ] Planning integration (MO at risk flag)
- [ ] Heatmap: workstation × day OEE grid

---

## INTEGRATION SUMMARY

| Module | How it connects |
|--------|----------------|
| Workstation | Core — all downtime and OEE is per workstation |
| Scrap | Quality factor = 1 - scrap/produced |
| MRP optracking | Performance factor = actual output |
| MRP production | Quality denominator = total produced |
| NCR | Breakdown downtime can trigger NCR creation |
| Planning | Downtime overlapping MO → delay risk |
| DocControl | Changeover SOP linked as Work Instruction |

---

## TECHNICAL NOTES

### Chart.js

Dolibarr 17+ includes Chart.js. Use:
```php
print '<script src="'.DOL_URL_ROOT.'/includes/chart.js/chart.min.js"></script>';
```

### Workstation list query

```sql
SELECT rowid, ref, label FROM llx_workstation_workstation
WHERE entity = {entity} AND status = 1
ORDER BY ref
```

### Daily OEE query pattern

```sql
-- Downtime per workstation per day
SELECT fk_workstation, DATE(dt_start) AS day, SUM(duration_min) AS total_down
FROM llx_oee_downtime
WHERE dt_end IS NOT NULL
  AND entity = {entity}
  AND DATE(dt_start) BETWEEN '{from}' AND '{to}'
GROUP BY fk_workstation, DATE(dt_start)
```

---

## GIT

```
git add -A
git commit -m "feat(oee): initial OEE + Downtime module scaffold"
git push origin master
```