461 lines
13 KiB
Plaintext
461 lines
13 KiB
Plaintext
# KPI Tracking System - Complete Optimization Implementation Guide
|
|
|
|
## Overview
|
|
|
|
This guide provides step-by-step instructions to implement all optimization requirements from `optimization_prompt.txt`. All necessary code and SQL files have been prepared in your `.node-red` directory.
|
|
|
|
---
|
|
|
|
## ✅ Implementation Summary
|
|
|
|
### Issues Addressed
|
|
|
|
✅ **Issue 1: Data Persistence** - Session state saved to database, crash recovery implemented
|
|
✅ **Issue 2: Cycle Count Capping** - 100 cycle limit with warnings and alerts
|
|
✅ **Issue 3: Hardware Irregularity Tracking** - Anomaly detection for cycle times
|
|
✅ **Issue 4: Intelligent Downtime Categorization** - Stop reason prompt with planned/unplanned distinction
|
|
✅ **Issue 5: Session Management** - Production sessions tracked for pattern analysis
|
|
|
|
---
|
|
|
|
## 📁 Files Created
|
|
|
|
All files are in `/home/mdares/.node-red/`:
|
|
|
|
1. **complete_optimization_migration.sql** - Complete database schema (all 8 tables)
|
|
2. **enhanced_machine_cycles_function.js** - Updated Machine cycles function with time tracking
|
|
3. **enhanced_work_order_buttons_function.js** - Updated Work Order buttons with session management
|
|
4. **startup_recovery_function.js** - Crash recovery logic
|
|
5. **stop_reason_ui_template.html** - Stop reason prompt UI
|
|
6. **IMPLEMENTATION_GUIDE.md** - This file
|
|
|
|
---
|
|
|
|
## 🚀 Implementation Steps
|
|
|
|
### Phase 1: Database Migration (5 minutes)
|
|
|
|
#### Step 1.1: Run Database Migration
|
|
|
|
**Option A: Using Node-RED MySQL Node (Recommended)**
|
|
|
|
1. Open Node-RED editor (`http://localhost:1880`)
|
|
2. Create a new flow tab called "Database Setup"
|
|
3. Add an **Inject** node
|
|
4. Add a **Function** node with this code:
|
|
```javascript
|
|
// Read the SQL file content (paste complete_optimization_migration.sql here)
|
|
msg.topic = `
|
|
-- Paste complete_optimization_migration.sql content here
|
|
`;
|
|
return msg;
|
|
```
|
|
5. Add a **MySQL** node, configure with:
|
|
- Database: `machine_data`
|
|
- Name: `Run Migration`
|
|
6. Wire: Inject → Function → MySQL
|
|
7. Click the Inject button
|
|
8. Check debug output for success
|
|
|
|
**Option B: Using MySQL Client (if available)**
|
|
|
|
```bash
|
|
mysql -h 10.147.20.244 -u root -p'alp-ha-7-echo' machine_data < complete_optimization_migration.sql
|
|
```
|
|
|
|
#### Step 1.2: Verify Tables Created
|
|
|
|
Run this query in Node-RED or MySQL client:
|
|
|
|
```sql
|
|
SHOW TABLES;
|
|
```
|
|
|
|
You should see these NEW tables:
|
|
- `kpi_snapshots`
|
|
- `alert_history`
|
|
- `shift_definitions`
|
|
- `session_state`
|
|
- `stop_events`
|
|
- `production_sessions`
|
|
- `cycle_anomalies`
|
|
|
|
And these UPDATED tables:
|
|
- `work_orders` (with new columns: `scrap_count`, `total_sessions`, `total_operating_time`, `total_downtime`, `avg_session_duration`)
|
|
|
|
---
|
|
|
|
### Phase 2: Update Node-RED Flows (15 minutes)
|
|
|
|
#### Step 2.1: Update "Machine cycles" Function
|
|
|
|
1. In Node-RED, find the **"Machine cycles"** function node (ID: `0d023d87a13bf56f`)
|
|
2. Open it for editing
|
|
3. **IMPORTANT**: Change "Outputs" from **2** to **4** at the bottom of the editor
|
|
4. Replace the entire function code with content from `enhanced_machine_cycles_function.js`
|
|
5. Click "Done"
|
|
|
|
#### Step 2.2: Wire the New Outputs
|
|
|
|
The Machine cycles node now has 4 outputs:
|
|
|
|
- **Output 1**: Database update for work_orders (existing wire)
|
|
- **Output 2**: State messages / Scrap prompt / Cycle cap alert (existing wire)
|
|
- **Output 3**: State backup to session_state table (NEW - needs MySQL node)
|
|
- **Output 4**: Anomaly detection to cycle_anomalies table (NEW - needs MySQL node)
|
|
|
|
**Action Required:**
|
|
1. Add two new **MySQL** nodes
|
|
2. Wire Output 3 → First MySQL node (label: "State Backup")
|
|
3. Wire Output 4 → Second MySQL node (label: "Anomaly Tracker")
|
|
4. Both MySQL nodes should connect to the `machine_data` database
|
|
|
|
#### Step 2.3: Update "Work Order buttons" Function
|
|
|
|
1. Find the **"Work Order buttons"** function node (ID: `9bbd4fade968036d`)
|
|
2. Open it for editing
|
|
3. **IMPORTANT**: Change "Outputs" from **4** to **5** at the bottom
|
|
4. Replace the entire function code with content from `enhanced_work_order_buttons_function.js`
|
|
5. Click "Done"
|
|
|
|
#### Step 2.4: Wire the New Output
|
|
|
|
The Work Order buttons node now has 5 outputs:
|
|
|
|
- **Output 1**: Upload Excel (existing)
|
|
- **Output 2**: Refresh work orders / Stop prompt (existing, UPDATE THIS)
|
|
- **Output 3**: Start work order (existing)
|
|
- **Output 4**: Complete work order (existing)
|
|
- **Output 5**: Session management queries (NEW - needs MySQL node)
|
|
|
|
**Action Required:**
|
|
1. Add a new **MySQL** node (label: "Session Manager")
|
|
2. Wire Output 5 → MySQL node
|
|
3. Connect to `machine_data` database
|
|
|
|
**IMPORTANT - Update Output 2 Wire:**
|
|
Output 2 now also sends the stop-prompt message. You need to:
|
|
1. Find where Output 2 is currently wired
|
|
2. Add a **Switch** node to handle different message modes
|
|
3. Configure Switch node:
|
|
- Property: `msg._mode`
|
|
- Rules:
|
|
- `== "select"` → Send to existing work orders refresh handler
|
|
- `== "stop-prompt"` → Send to Stop Reason UI (see next step)
|
|
|
|
#### Step 2.5: Add Stop Reason UI
|
|
|
|
1. Add a new **ui_template** node
|
|
2. Open it and paste the content from `stop_reason_ui_template.html`
|
|
3. Set:
|
|
- Group: Home tab (or where your START/STOP buttons are)
|
|
- Size: Should be hidden by default (use CSS `display: none` initially)
|
|
- Template Type: Angular
|
|
4. Wire the "stop-prompt" output from the Switch node (step 2.4) to this template
|
|
5. Wire the output of this template back to "Work Order buttons" function input
|
|
|
|
---
|
|
|
|
### Phase 3: Add Startup Recovery (10 minutes)
|
|
|
|
#### Step 3.1: Create Startup Recovery Flow
|
|
|
|
1. Create a new tab called "Startup Recovery"
|
|
2. Add an **Inject** node:
|
|
- Payload: `{ "mode": "check" }`
|
|
- Repeat: None
|
|
- **Inject once after**: 5 seconds (on Node-RED start)
|
|
3. Add a **Function** node:
|
|
- Name: "Startup Recovery"
|
|
- Code: Paste content from `startup_recovery_function.js`
|
|
- Outputs: **2**
|
|
4. Add two **MySQL** nodes:
|
|
- First: "Query Session State"
|
|
- Second: "Update Session State"
|
|
- Both connect to `machine_data` database
|
|
|
|
#### Step 3.2: Wire the Recovery Flow
|
|
|
|
```
|
|
Inject (on startup)
|
|
↓
|
|
Startup Recovery Function
|
|
↓ (output 1) → UI notification (optional: connect to dashboard)
|
|
↓ (output 2) → MySQL node
|
|
↓
|
|
Function (mode: process-results)
|
|
↓
|
|
(User prompt for restore/fresh - implement UI prompt similar to stop reason)
|
|
↓
|
|
Back to Startup Recovery Function with mode: "restore" or "start-fresh"
|
|
```
|
|
|
|
**Simple Implementation (Auto-restore without prompt):**
|
|
|
|
If you want automatic restoration without user prompt:
|
|
|
|
1. Wire: Inject → Startup Recovery (mode: check) → MySQL → Function node
|
|
2. In the function node after MySQL, check results and call Startup Recovery with mode: "restore" if session exists
|
|
3. This bypasses the user prompt
|
|
|
|
---
|
|
|
|
### Phase 4: Update Database Handler Routing (5 minutes)
|
|
|
|
#### Step 4.1: Add Mode-Based Routing
|
|
|
|
Currently, your database updates might go through a single MySQL node. Now you have different message modes:
|
|
|
|
- `_mode: "cycle"` → work_orders UPDATE
|
|
- `_mode: "state-backup"` → session_state UPDATE
|
|
- `_mode: "cycle-anomaly"` → cycle_anomalies INSERT
|
|
- `_mode: "create-session"` → production_sessions INSERT
|
|
- `_mode: "close-session"` → production_sessions UPDATE
|
|
- `_mode: "create-stop-event"` → stop_events INSERT
|
|
- `_mode: "resume-stop"` → stop_events UPDATE
|
|
|
|
**Action Required:**
|
|
|
|
Add **Switch** nodes to route different message types to appropriate handlers. Or, simpler approach: let all messages with `msg.topic` go through the same MySQL node (they're all SQL queries).
|
|
|
|
---
|
|
|
|
## 🧪 Testing Procedures
|
|
|
|
### Test 1: Basic Time Tracking
|
|
|
|
1. Start a work order
|
|
2. Click START button
|
|
3. Let machine run for a few cycles
|
|
4. Query database:
|
|
```sql
|
|
SELECT * FROM session_state WHERE session_key = 'current_session';
|
|
```
|
|
5. Verify `operating_time` and `cycle_count` are updating
|
|
|
|
### Test 2: Stop Reason Categorization
|
|
|
|
1. Click STOP button
|
|
2. Verify modal appears with stop reason options
|
|
3. Select a **planned** stop (e.g., "Lunch break")
|
|
4. Click Submit
|
|
5. Query database:
|
|
```sql
|
|
SELECT * FROM stop_events ORDER BY id DESC LIMIT 1;
|
|
```
|
|
6. Verify `affects_availability = 0` for planned stop
|
|
|
|
### Test 3: Cycle Count Capping
|
|
|
|
1. Manually set cycle count to 95:
|
|
```javascript
|
|
global.set("cycleCount", 95);
|
|
```
|
|
2. Run 5 more cycles
|
|
3. At cycle 100, verify alert appears
|
|
4. Try to run another cycle - should be blocked
|
|
|
|
### Test 4: Anomaly Detection
|
|
|
|
1. Start work order with theoretical cycle time = 30 seconds
|
|
2. Manually trigger a very slow cycle (e.g., wait 50 seconds)
|
|
3. Query database:
|
|
```sql
|
|
SELECT * FROM cycle_anomalies ORDER BY id DESC LIMIT 5;
|
|
```
|
|
4. Verify anomaly was recorded with deviation percentage
|
|
|
|
### Test 5: Crash Recovery
|
|
|
|
1. With an active work order running:
|
|
- Note current cycle count
|
|
- Click **Restart Node-RED** (or kill process)
|
|
2. When Node-RED restarts:
|
|
- Wait 5 seconds
|
|
- Check if session was restored
|
|
- Verify cycle count matches previous value
|
|
|
|
### Test 6: Session Management
|
|
|
|
1. Start work order, click START
|
|
2. Run 10 cycles
|
|
3. Click STOP, select reason
|
|
4. Click START again (RESUME)
|
|
5. Query database:
|
|
```sql
|
|
SELECT * FROM production_sessions WHERE work_order_id = 'YOUR_WO_ID' ORDER BY start_time DESC;
|
|
```
|
|
6. Verify two separate sessions were created
|
|
|
|
---
|
|
|
|
## 📊 KPI Calculation Updates
|
|
|
|
### Availability Calculation (Updated for Issue 4)
|
|
|
|
**Old Formula:**
|
|
```
|
|
Availability = Operating Time / (Operating Time + All Downtime)
|
|
```
|
|
|
|
**New Formula:**
|
|
```
|
|
Availability = Operating Time / (Operating Time + Unplanned Downtime Only)
|
|
```
|
|
|
|
**Implementation:**
|
|
|
|
When calculating availability, query only unplanned stops:
|
|
|
|
```sql
|
|
SELECT SUM(duration) as unplanned_downtime
|
|
FROM stop_events
|
|
WHERE work_order_id = 'WO_ID'
|
|
AND affects_availability = 1; -- Only unplanned stops
|
|
```
|
|
|
|
---
|
|
|
|
## 🔧 Configuration Options
|
|
|
|
### Cycle Backup Frequency
|
|
|
|
Default: Every 10 cycles
|
|
|
|
To change, edit `enhanced_machine_cycles_function.js` line 162:
|
|
|
|
```javascript
|
|
if (cyclesSinceBackup >= 10) { // Change this number
|
|
```
|
|
|
|
### Anomaly Detection Threshold
|
|
|
|
Default: 20% deviation
|
|
|
|
To change, edit `enhanced_machine_cycles_function.js` line 123:
|
|
|
|
```javascript
|
|
if (Math.abs(deviation) > 20) { // Change this percentage
|
|
```
|
|
|
|
### Cycle Count Cap
|
|
|
|
Default: 100 cycles
|
|
|
|
To change, edit `enhanced_machine_cycles_function.js` line 82:
|
|
|
|
```javascript
|
|
if (cycles >= 100) { // Change this number
|
|
```
|
|
|
|
---
|
|
|
|
## 🐛 Troubleshooting
|
|
|
|
### Issue: "Table doesn't exist" errors
|
|
|
|
**Solution:** Re-run the migration SQL script. Check that you're connected to the correct database.
|
|
|
|
### Issue: Global variables not persisting
|
|
|
|
**Solution:** Check that:
|
|
1. `session_state` table exists
|
|
2. State backup messages are reaching MySQL node (Output 3)
|
|
3. Backup is running (every 10 cycles)
|
|
|
|
### Issue: Stop reason prompt not showing
|
|
|
|
**Solution:** Check that:
|
|
1. `stop_reason_ui_template.html` is in a ui_template node
|
|
2. Wire from "Work Order buttons" Output 2 → Switch node → ui_template
|
|
3. JavaScript console for errors (F12 in browser)
|
|
|
|
### Issue: Anomaly detection not working
|
|
|
|
**Solution:**
|
|
1. Verify `theoreticalCycleTime` is set on work order
|
|
2. Check that cycles are being counted (cycle > 1 required)
|
|
3. Verify deviation exceeds 20%
|
|
|
|
---
|
|
|
|
## 📈 Expected Results
|
|
|
|
After implementation:
|
|
|
|
✅ **Zero data loss** on Node-RED restart/crash
|
|
✅ **Accurate availability KPI** (planned stops excluded)
|
|
✅ **Complete production history** with session tracking
|
|
✅ **Anomaly alerts** for irregular machine behavior
|
|
✅ **Cycle count safety** with 100 cycle cap
|
|
✅ **Pattern analysis capability** via production_sessions table
|
|
|
|
---
|
|
|
|
## 🎯 Next Steps (Optional - Phase 3 from optimization_prompt.txt)
|
|
|
|
After successful deployment, consider:
|
|
|
|
1. **Analytics Dashboard** - Build graphs from `kpi_snapshots` and `production_sessions`
|
|
2. **Predictive Maintenance** - Analyze `cycle_anomalies` trends
|
|
3. **Shift Reports** - Use `shift_definitions` for time-based filtering
|
|
4. **Alert System** - Implement automatic alerts using `alert_history` table
|
|
|
|
---
|
|
|
|
## 📝 Deployment Checklist
|
|
|
|
- [ ] Database migration completed successfully
|
|
- [ ] All 7 new tables exist in database
|
|
- [ ] work_orders table updated with new columns
|
|
- [ ] Machine cycles function updated (4 outputs)
|
|
- [ ] Work Order buttons function updated (5 outputs)
|
|
- [ ] All new MySQL nodes added and wired
|
|
- [ ] Stop reason UI template deployed
|
|
- [ ] Startup recovery flow created
|
|
- [ ] Test 1: Time tracking ✓
|
|
- [ ] Test 2: Stop categorization ✓
|
|
- [ ] Test 3: Cycle capping ✓
|
|
- [ ] Test 4: Anomaly detection ✓
|
|
- [ ] Test 5: Crash recovery ✓
|
|
- [ ] Test 6: Session management ✓
|
|
- [ ] Documentation updated for operators
|
|
- [ ] Backup of flows.json created
|
|
|
|
---
|
|
|
|
## 🆘 Support
|
|
|
|
If you encounter issues:
|
|
|
|
1. Check Node-RED debug panel for error messages
|
|
2. Review MySQL node outputs
|
|
3. Check database logs
|
|
4. Verify all wiring matches this guide
|
|
5. Test each component individually
|
|
|
|
All implementation files are in `/home/mdares/.node-red/` for reference.
|
|
|
|
---
|
|
|
|
## Summary of Changes
|
|
|
|
**Database:**
|
|
- 7 new tables added
|
|
- 5 new columns added to work_orders
|
|
- 1 table initialized with session state
|
|
|
|
**Node-RED Functions:**
|
|
- Machine cycles: 170 → 280 lines (time tracking, capping, anomaly detection)
|
|
- Work Order buttons: 120 → 350 lines (session management, stop categorization)
|
|
- Startup Recovery: NEW - 200 lines (crash recovery)
|
|
|
|
**UI:**
|
|
- Stop reason prompt modal (planned vs unplanned)
|
|
- Cycle cap alert
|
|
- Recovery prompt (optional)
|
|
|
|
**Estimated Downtime for Deployment:** 10-15 minutes (can be done during scheduled maintenance)
|
|
|
|
Good luck with your implementation! 🚀
|