shopdb/docs/archive/MACHINE_QUICK_REFERENCE.md

# Machine Management - Quick Reference Guide

**Last Updated:** 2025-11-07

---

## Quick Links

| Page | URL | Purpose |
|------|-----|---------|
| **View All Machines** | `displaymachines.asp` | List of all equipment (excludes PCs) |
| **View Machine** | `displaymachine.asp?machineid=XXX` | Display single machine details |
| **Add Machine** | `addmachine.asp` | Create new machine |
| **Edit Machine** | `editmachine.asp?machineid=XXX` | Edit existing machine |

---

## File Structure

```
/home/camp/projects/windows/shopdb/
├── displaymachines.asp          # List all machines (equipment only, no PCs)
├── displaymachine.asp           # Display single machine (5 tabs)
├── addmachine.asp              # Add new machine form (5 tabs)
├── editmachine.asp             # Edit machine form (5 tabs)
├── savemachine_direct.asp      # Save new machine handler
├── savemachineedit.asp         # Save machine edits handler
└── docs/
    ├── MACHINE_MANAGEMENT_COMPLETE.md        # Comprehensive overview
    ├── MACHINE_EDIT_FORM_IMPLEMENTATION.md   # Edit form details
    ├── ADD_EDIT_MACHINE_UPDATES.md           # Add form details
    └── DISPLAY_PAGES_UPDATE_SUMMARY.md       # Display page details
```

---

## Database Tables

### machines
Primary table for all equipment and PCs
- `machineid` - Primary key
- `machinenumber` - Equipment number (unique, required)
- `modelnumberid` - Foreign key to models
- `businessunitid` - Foreign key to businessunits
- `pctypeid` - NULL for equipment, NOT NULL for PCs
- `alias` - Friendly name
- `machinenotes` - Notes
- `mapleft`, `maptop` - Location coordinates

### communications
Network interface data (up to 3 per machine)
- `comid` - Primary key
- `machineid` - Foreign key to machines
- `address` - IP address
- `macaddress` - MAC address
- `interfacename` - "Interface 1", "Interface 2", "Interface 3"
- `isprimary` - 1 for primary, 0 for secondary

### machinerelationships
Relationships between machines
- `relationshipid` - Primary key
- `machineid` - Source machine
- `related_machineid` - Target machine
- `relationshiptypeid` - Foreign key to relationshiptypes

**Relationship Types:**
- **Controls** (one-way): PC → Equipment
- **Dualpath** (bidirectional): Machine ↔ Machine

### compliance
Compliance and security data
- `complianceid` - Primary key
- `machineid` - Foreign key to machines
- `is_third_party_managed` - ENUM('Yes', 'No', 'NA')
- `third_party_vendorid` - Foreign key to vendors
- `ot_asset_system` - OT classification
- `ot_asset_device_type` - DoD classification

---

## Common Queries

### Get Machine with All Data
```sql
SELECT m.*, mo.modelnumber, v.vendor, bu.businessunit, mt.machinetype
FROM machines m
LEFT JOIN models mo ON m.modelnumberid = mo.modelnumberid
LEFT JOIN vendors v ON mo.vendorid = v.vendorid
LEFT JOIN businessunits bu ON m.businessunitid = bu.businessunitID
LEFT JOIN machinetypes mt ON mo.machinetypeid = mt.machinetypeid
WHERE m.machineid = ?
```

### Get Network Interfaces
```sql
SELECT * FROM communications
WHERE machineid = ? AND isactive = 1
ORDER BY isprimary DESC, interfacename ASC
```

### Get Controlling PC
```sql
SELECT m.machineid, m.hostname, c.address
FROM machinerelationships mr
JOIN relationshiptypes rt ON mr.relationshiptypeid = rt.relationshiptypeid
JOIN machines m ON mr.machineid = m.machineid
LEFT JOIN communications c ON m.machineid = c.machineid AND c.isprimary = 1
WHERE mr.related_machineid = ? AND rt.relationshiptype = 'Controls'
```

### Get Dualpath Machines
```sql
SELECT m.machineid, m.machinenumber, mt.machinetype, mo.modelnumber
FROM machinerelationships mr
JOIN relationshiptypes rt ON mr.relationshiptypeid = rt.relationshiptypeid
JOIN machines m ON mr.related_machineid = m.machineid
LEFT JOIN models mo ON m.modelnumberid = mo.modelnumberid
LEFT JOIN machinetypes mt ON mo.machinetypeid = mt.machinetypeid
WHERE mr.machineid = ? AND rt.relationshiptype = 'Dualpath'
```

### Get Compliance Data
```sql
SELECT c.*, v.vendor AS third_party_vendor_name
FROM compliance c
LEFT JOIN vendors v ON c.third_party_vendorid = v.vendorid
WHERE c.machineid = ?
```

---

## Form Field Reference

### Basic Info Tab
- `machinenumber` - Equipment number (required, unique, readonly on edit)
- `modelid` - Model dropdown (required)
- `businessunitid` - Business unit dropdown (required)
- `alias` - Friendly name (optional)
- `machinenotes` - Notes (optional)

### Network Tab
- `ip1`, `mac1` - Primary interface (Interface 1)
- `ip2`, `mac2` - Optional interface (Interface 2)
- `ip3`, `mac3` - Optional interface (Interface 3)

### Relationships Tab
- `controllingpc` - PC dropdown (WHERE pctypeid IS NOT NULL)
- `dualpathid` - Machine dropdown (WHERE pctypeid IS NULL)

### Compliance Tab
- `thirdpartymanaged` - Dropdown (N/A, Yes, No)
- `thirdpartyvendorid` - Vendor dropdown
- `otassetsystem` - Text input (100 chars)
- `dodassettype` - Text input (100 chars)

### Location Tab
- `mapleft` - X coordinate (from map picker)
- `maptop` - Y coordinate (from map picker)

---

## Code Patterns

### Parameterized Query Example
```asp
Dim strSQL, rsResult
strSQL = "SELECT * FROM machines WHERE machineid = ?"
Set rsResult = ExecuteParameterizedQuery(objConn, strSQL, Array(machineid))
If Not rsResult.EOF Then
    ' Process results
End If
rsResult.Close
Set rsResult = Nothing
```

### HTML Encoding Pattern
```asp
Response.Write("<td>" & Server.HTMLEncode(rsData("fieldname") & "") & "</td>")
```

### NULL Handling Pattern
```asp
' Force to string to prevent NULL errors
Dim displayValue
displayValue = rsData("fieldname") & ""  ' Converts NULL to empty string

' Use IIf for database inserts
cmd.Parameters.Append cmd.CreateParameter("@param", 200, 1, 50, IIf(value <> "", value, Null))
```

---

## Common Tasks

### Adding a New Machine
1. Navigate to `addmachine.asp`
2. Fill Basic Info tab (machine number, model, business unit)
3. Fill Network tab (at least one IP/MAC)
4. Select relationships (optional)
5. Fill compliance data (optional)
6. Click map to set location (optional)
7. Click "Add Equipment"

### Editing a Machine
1. Navigate to `displaymachine.asp?machineid=XXX`
2. Click "Edit Machine" button
3. Update any tab
4. Click "Update Equipment"

### Viewing Machine Relationships
1. Navigate to `displaymachine.asp?machineid=XXX`
2. Click "Relationships" tab
3. See three sections:
   - Controls (PC that controls this)
   - Controlled By This PC (if it's a PC)
   - Dualpath (redundant machines)

### Viewing Network Interfaces
1. Navigate to `displaymachine.asp?machineid=XXX`
2. Click "Network" tab
3. See all interfaces in table format

---

## Troubleshooting

### Machine redirects to homepage
**Cause:** NULL machinetypeid in model
**Fix:** Use LEFT JOIN instead of INNER JOIN for machinetypes

### Edit button doesn't work
**Cause:** Invalid machineid or file doesn't exist
**Fix:** Check URL parameter, verify editmachine.asp exists

### Data doesn't save
**Cause:** Validation error or database connection issue
**Fix:** Check error message, review logs in `/home/camp/projects/windows/logs/`

### Map doesn't load
**Cause:** Missing Leaflet.js or map images
**Fix:** Verify leaflet.js, sitemap2025-dark.png, sitemap2025-light.png exist

### "New" button doesn't work
**Cause:** JavaScript event handler issue
**Fix:** Check browser console for errors, verify jQuery loaded

### Relationships not saving
**Cause:** Relationship types don't exist or invalid machine IDs
**Fix:** Verify relationshiptypes table has 'Controls' and 'Dualpath'

---

## Security Checklist

-  All queries use parameterized commands
-  All output uses Server.HTMLEncode()
-  All numeric inputs validated with IsNumeric()
-  All string inputs have length limits
-  NULL values handled properly
-  No direct variable interpolation in SQL
-  Error messages don't expose sensitive data
-  Database connections always closed

---

## Testing Checklist

**Display Page:**
- [ ] View machine with full data
- [ ] View machine with no network interfaces
- [ ] View machine with no relationships
- [ ] Click "Edit Machine" button
- [ ] Click related machine links

**Add Form:**
- [ ] Add machine with all fields
- [ ] Add machine with only required fields
- [ ] Test IP/MAC validation
- [ ] Create new model
- [ ] Create new vendor
- [ ] Use map picker

**Edit Form:**
- [ ] Edit basic info
- [ ] Add/remove network interfaces
- [ ] Change controlling PC
- [ ] Change dualpath machine
- [ ] Update compliance data
- [ ] Move location on map

---

## Quick Command Reference

### View Logs
```bash
tail -f /home/camp/projects/windows/logs/*.log
```

### Check Database
```bash
mysql -u root -p shopdb -e "SELECT COUNT(*) FROM machines WHERE pctypeid IS NULL"
mysql -u root -p shopdb -e "SELECT COUNT(*) FROM communications"
mysql -u root -p shopdb -e "SELECT COUNT(*) FROM machinerelationships"
```

### Find Machine by Number
```sql
SELECT machineid FROM machines WHERE machinenumber = '4500'
```

### List All Relationships
```sql
SELECT m1.machinenumber AS source, m2.machinenumber AS target, rt.relationshiptype
FROM machinerelationships mr
JOIN machines m1 ON mr.machineid = m1.machineid
JOIN machines m2 ON mr.related_machineid = m2.machineid
JOIN relationshiptypes rt ON mr.relationshiptypeid = rt.relationshiptypeid
WHERE mr.isactive = 1
```

---

## Support Resources

- **Full Documentation:** `MACHINE_MANAGEMENT_COMPLETE.md`
- **Edit Form Details:** `MACHINE_EDIT_FORM_IMPLEMENTATION.md`
- **Add Form Details:** `ADD_EDIT_MACHINE_UPDATES.md`
- **Display Page Details:** `DISPLAY_PAGES_UPDATE_SUMMARY.md`
- **Migration Scripts:** `/home/camp/projects/windows/shopdb/sql/migration_phase2/`
- **Import Logs:** `/tmp/inventory_import_final.log`

---

**Last Updated:** 2025-11-07
**Version:** 1.0
**Status:** Production Ready