shopdb/docs/archive/INFRASTRUCTURE_SUPPORT_IMPLEMENTATION.md

Infrastructure Vendor/Model Support - Implementation Guide

Date: 2025-10-23 Status: Ready for Implementation Scope: Add vendor/model tracking for servers, switches, and cameras

---

Executive Summary

Goal: Extend the existing vendor/model system (currently used for PCs, Printers, and Machines) to also support infrastructure devices (Servers, Switches, Cameras).

Decision: Vendor types ABANDONED - Keeping the simple vendors table as-is. No boolean flag refactoring needed.

What We're Building

Feature	Status	Impact
Add modelid to servers/switches/cameras	Script ready	Database schema
Create vw_network_devices view	Script ready	Unified infrastructure query
Create server CRUD pages	New development	4 files
Create switch CRUD pages	New development	4 files
Create camera CRUD pages	New development	4 files
Update navigation	New development	Menu items
Update network map	Optional	Display vendor/model

Total New Files: 12 ASP pages + nav updates Total Modified Files: ~2-3 (navigation, possibly network_map.asp) Estimated Time: 16-24 hours

---

Part 1: Database Schema Changes

Migration Script

File: /home/camp/projects/windows/shopdb/sql/add_infrastructure_vendor_model_support.sql

What It Does

1. Adds modelid column to infrastructure tables:

   servers.modelid   → models.modelnumberid (FK)
   switches.modelid  → models.modelnumberid (FK)
   cameras.modelid   → models.modelnumberid (FK)
   

2. Creates unified view for infrastructure:

   CREATE VIEW vw_network_devices AS
   SELECT 'Server' AS device_type, serverid, modelid, modelnumber, vendor, ...
   FROM servers LEFT JOIN models LEFT JOIN vendors
   UNION ALL
   SELECT 'Switch' AS device_type, switchid, modelid, modelnumber, vendor, ...
   FROM switches LEFT JOIN models LEFT JOIN vendors
   UNION ALL
   SELECT 'Camera' AS device_type, cameraid, modelid, modelnumber, vendor, ...
   FROM cameras LEFT JOIN models LEFT JOIN vendors
   

Tables After Migration

servers table:

serverid INT(11) PK AUTO_INCREMENT
modelid INT(11) FK → models.modelnumberid  ← NEW!
serialnumber VARCHAR(100)
ipaddress VARCHAR(15)
description VARCHAR(255)
maptop INT(11)
mapleft INT(11)
isactive BIT(1)

switches table:

switchid INT(11) PK AUTO_INCREMENT
modelid INT(11) FK → models.modelnumberid  ← NEW!
serialnumber VARCHAR(100)
ipaddress VARCHAR(15)
description VARCHAR(255)
maptop INT(11)
mapleft INT(11)
isactive BIT(1)

cameras table:

cameraid INT(11) PK AUTO_INCREMENT
modelid INT(11) FK → models.modelnumberid  ← NEW!
serialnumber VARCHAR(100)
ipaddress VARCHAR(15)
description VARCHAR(255)
maptop INT(11)
mapleft INT(11)
isactive BIT(1)

---

Part 2: Required New Pages

Server Management Pages (4 files)

1. displayservers.asp - Server List View

Purpose: Display all servers in a searchable table Similar to: displayprinters.asp, displaymachines.asp

Key Features:

• Sortable table with columns: ID, Model, Vendor, Serial, IP, Description, Status
• Search/filter functionality
• "Add New Server" button
• Click row → displayserver.asp (detail page)

SQL Query:

SELECT s.serverid, s.serialnumber, s.ipaddress, s.description, s.isactive,
       m.modelnumber, v.vendor
FROM servers s
LEFT JOIN models m ON s.modelid = m.modelnumberid
LEFT JOIN vendors v ON m.vendorid = v.vendorid
WHERE s.isactive = 1
ORDER BY s.serverid DESC

2. displayserver.asp - Server Detail with Inline Edit

Purpose: Show server details with inline edit form Similar to: displayprinter.asp, displaymachine.asp

Key Features:

• Display mode: Show all server info with Edit button
• Edit mode: Inline form to update server
• Model/Vendor dropdown selection
• Save button → saveserver_direct.asp
• Delete/deactivate functionality

SQL Query (Display):

SELECT s.*, m.modelnumber, v.vendor, v.vendorid
FROM servers s
LEFT JOIN models m ON s.modelid = m.modelnumberid
LEFT JOIN vendors v ON m.vendorid = v.vendorid
WHERE s.serverid = ?

3. addserver.asp - Add New Server Form

Purpose: Form to add a new server Similar to: addprinter.asp, addmachine.asp

Key Features:

• Model dropdown (filtered from models table)
• Vendor dropdown (auto-filled based on model or separate selector)
• Serial number input (text)
• IP address input (validated)
• Description textarea
• Map coordinates (optional, maptop/mapleft)
• Submit → saveserver_direct.asp

Model Dropdown Query:

SELECT m.modelnumberid, m.modelnumber, v.vendor
FROM models m
INNER JOIN vendors v ON m.vendorid = v.vendorid
WHERE m.isactive = 1
ORDER BY v.vendor, m.modelnumber

Or separate vendor/model selection:

-- Step 1: Select vendor
SELECT vendorid, vendor FROM vendors WHERE isactive = 1 ORDER BY vendor

-- Step 2: Select model (filtered by vendorid)
SELECT modelnumberid, modelnumber FROM models
WHERE vendorid = ? AND isactive = 1 ORDER BY modelnumber

4. saveserver_direct.asp - Server Save Endpoint

Purpose: Backend processor to insert/update server Similar to: saveprinter_direct.asp, savemachine_direct.asp

Key Features:

• Validate all inputs using validation.asp functions
• INSERT for new server
• UPDATE for existing server
• Return JSON response or redirect
• Error handling

Insert Query:

INSERT INTO servers (modelid, serialnumber, ipaddress, description, maptop, mapleft, isactive)
VALUES (?, ?, ?, ?, ?, ?, 1)

Update Query:

UPDATE servers
SET modelid = ?, serialnumber = ?, ipaddress = ?, description = ?,
    maptop = ?, mapleft = ?
WHERE serverid = ?

Switch Management Pages (4 files)

Same structure as servers, just replace "server" with "switch":

• displayswitches.asp - Switch list
• displayswitch.asp - Switch detail with inline edit
• addswitch.asp - Add switch form
• saveswitch_direct.asp - Switch save endpoint

Camera Management Pages (4 files)

Same structure, replace with "camera":

• displaycameras.asp - Camera list
• displaycamera.asp - Camera detail with inline edit
• addcamera.asp - Add camera form
• savecamera_direct.asp - Camera save endpoint

---

Part 3: Navigation Updates

Add Menu Items

File to modify: includes/leftsidebar.asp (or wherever main nav is)

New menu section:

<!-- Infrastructure -->
<li class="nav-header">INFRASTRUCTURE</li>
<li><a href="displayservers.asp"><i class="zmdi zmdi-storage"></i> Servers</a></li>
<li><a href="displayswitches.asp"><i class="zmdi zmdi-device-hub"></i> Switches</a></li>
<li><a href="displaycameras.asp"><i class="zmdi zmdi-videocam"></i> Cameras</a></li>

Or add to existing "Network" or "Devices" section.

---

Part 4: Optional Enhancements

Update network_map.asp

If network_map.asp currently exists and displays network topology:

• Add server/switch/camera markers to the map
• Display vendor/model on hover/click
• Use vw_network_devices view for unified query

Query for map:

SELECT device_type, device_id, vendor, modelnumber,
       ipaddress, description, maptop, mapleft
FROM vw_network_devices
WHERE isactive = 1 AND maptop IS NOT NULL AND mapleft IS NOT NULL

---

Part 5: Code Templates

Template 1: Infrastructure List Page (displayservers.asp)

<!--#include file="./includes/sql.asp"-->
<!--#include file="./includes/header.asp"-->
<!--#include file="./includes/leftsidebar.asp"-->
<!--#include file="./includes/topbarheader.asp"-->

<%
' Fetch all servers with model/vendor
Dim strSQL, rs
strSQL = "SELECT s.serverid, s.serialnumber, s.ipaddress, s.description, s.isactive, " & _
         "m.modelnumber, v.vendor " & _
         "FROM servers s " & _
         "LEFT JOIN models m ON s.modelid = m.modelnumberid " & _
         "LEFT JOIN vendors v ON m.vendorid = v.vendorid " & _
         "WHERE s.isactive = 1 " & _
         "ORDER BY s.serverid DESC"
Set rs = objConn.Execute(strSQL)
%>

<div class="content-wrapper">
    <h2>Servers <a href="addserver.asp" class="btn btn-primary">Add Server</a></h2>

    <table class="table table-striped">
        <thead>
            <tr>
                <th>ID</th>
                <th>Vendor</th>
                <th>Model</th>
                <th>Serial Number</th>
                <th>IP Address</th>
                <th>Description</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
        <% Do While Not rs.EOF %>
            <tr>
                <td><%=rs("serverid")%></td>
                <td><%=Server.HTMLEncode(rs("vendor") & "")%></td>
                <td><%=Server.HTMLEncode(rs("modelnumber") & "")%></td>
                <td><%=Server.HTMLEncode(rs("serialnumber") & "")%></td>
                <td><%=Server.HTMLEncode(rs("ipaddress") & "")%></td>
                <td><%=Server.HTMLEncode(rs("description") & "")%></td>
                <td><a href="displayserver.asp?serverid=<%=rs("serverid")%>">View</a></td>
            </tr>
        <%
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
        %>
        </tbody>
    </table>
</div>

<!--#include file="./includes/footer.asp"-->

Template 2: Add Infrastructure Device Form (addserver.asp)

<!--#include file="./includes/sql.asp"-->
<!--#include file="./includes/header.asp"-->
<!--#include file="./includes/leftsidebar.asp"-->
<!--#include file="./includes/topbarheader.asp"-->

<div class="content-wrapper">
    <h2>Add Server</h2>

    <form method="post" action="saveserver_direct.asp">
        <div class="form-group">
            <label>Model</label>
            <select name="modelid" required class="form-control">
                <option value="">-- Select Model --</option>
                <%
                Dim strSQL, rsModels
                strSQL = "SELECT m.modelnumberid, m.modelnumber, v.vendor " & _
                         "FROM models m " & _
                         "INNER JOIN vendors v ON m.vendorid = v.vendorid " & _
                         "WHERE m.isactive = 1 " & _
                         "ORDER BY v.vendor, m.modelnumber"
                Set rsModels = objConn.Execute(strSQL)
                Do While Not rsModels.EOF
                %>
                    <option value="<%=rsModels("modelnumberid")%>">
                        <%=Server.HTMLEncode(rsModels("vendor") & " - " & rsModels("modelnumber"))%>
                    </option>
                <%
                    rsModels.MoveNext
                Loop
                rsModels.Close
                Set rsModels = Nothing
                %>
            </select>
        </div>

        <div class="form-group">
            <label>Serial Number</label>
            <input type="text" name="serialnumber" class="form-control" maxlength="100">
        </div>

        <div class="form-group">
            <label>IP Address</label>
            <input type="text" name="ipaddress" class="form-control" maxlength="15"
                   pattern="^[0-9\.]+$" placeholder="192.168.1.100">
        </div>

        <div class="form-group">
            <label>Description</label>
            <textarea name="description" class="form-control" rows="3"></textarea>
        </div>

        <button type="submit" class="btn btn-success">Save Server</button>
        <a href="displayservers.asp" class="btn btn-secondary">Cancel</a>
    </form>
</div>

<!--#include file="./includes/footer.asp"-->

Template 3: Save Infrastructure Device (saveserver_direct.asp)

<!--#include file="./includes/sql.asp"-->
<!--#include file="./includes/error_handler.asp"-->
<!--#include file="./includes/validation.asp"-->
<!--#include file="./includes/db_helpers.asp"-->

<%
' Validate inputs
Dim modelid, serialnumber, ipaddress, description
modelid = GetSafeInteger("FORM", "modelid", 0, 1, 999999)
serialnumber = GetSafeString("FORM", "serialnumber", "", 0, 100, "^[A-Za-z0-9\-]+$")
ipaddress = GetSafeString("FORM", "ipaddress", "", 0, 15, "^[0-9\.]+$")
description = GetSafeString("FORM", "description", "", 0, 255, "")

' Validate required fields
If modelid = 0 Then
    Response.Write("Error: Model is required")
    Response.End
End If

' Insert server
Dim strSQL
strSQL = "INSERT INTO servers (modelid, serialnumber, ipaddress, description, isactive) " & _
         "VALUES (?, ?, ?, ?, 1)"

Set rs = ExecuteParameterizedQuery(objConn, strSQL, Array(modelid, serialnumber, ipaddress, description))

Call CleanupResources()

' Redirect to list
Response.Redirect("displayservers.asp")
%>

---

Part 6: Implementation Checklist

Phase 1: Database Migration

• Review add_infrastructure_vendor_model_support.sql
• Backup database
• Run migration on test database
• Verify modelid columns added to servers/switches/cameras
• Verify foreign keys created
• Verify vw_network_devices view created
• Test view returns correct data

Phase 2: Server Pages (Do This First)

• Create displayservers.asp (list view)
• Create addserver.asp (add form)
• Create saveserver_direct.asp (save endpoint)
• Create displayserver.asp (detail with inline edit)
• Test: Add new server
• Test: Edit existing server
• Test: View server list

Phase 3: Switch Pages

• Create displayswitches.asp (list view)
• Create addswitch.asp (add form)
• Create saveswitch_direct.asp (save endpoint)
• Create displayswitch.asp (detail with inline edit)
• Test: Add/edit/view switches

Phase 4: Camera Pages

• Create displaycameras.asp (list view)
• Create addcamera.asp (add form)
• Create savecamera_direct.asp (save endpoint)
• Create displaycamera.asp (detail with inline edit)
• Test: Add/edit/view cameras

Phase 5: Navigation & Polish

• Add menu items to navigation
• Test all navigation links
• Update dashboard (optional - add infrastructure stats)
• Update search (optional - add infrastructure to search results)

Phase 6: Optional Enhancements

• Update network_map.asp to show infrastructure devices
• Add infrastructure reports (count by vendor, etc.)
• Add bulk import for infrastructure (CSV upload)

Phase 7: Documentation & Deployment

• Update user documentation
• Update technical documentation
• Test on production-like data
• Create deployment checklist
• Deploy to production

---

Part 7: Testing Plan

Unit Tests (Per Device Type)

• Can add device with valid model
• Can add device without model (modelid NULL)
• Can edit device and change model
• Can delete/deactivate device
• Form validation works (IP format, required fields)
• SQL injection prevention (parameterized queries)

Integration Tests

• Device appears in list immediately after creation
• Device detail page shows vendor/model info correctly
• Model dropdown only shows active models
• Vendor name displays correctly (from model FK)
• Map coordinates save/display correctly

Data Integrity Tests

• Foreign keys enforce referential integrity
• Deleting a model doesn't break device (ON DELETE SET NULL)
• View vw_network_devices returns all device types
• NULL model handling (device with no model assigned)

---

Part 8: Rollback Plan

If issues arise:

1. Migration script is non-destructive - only adds columns, doesn't modify existing data
2. Can drop columns: ALTER TABLE servers DROP COLUMN modelid
3. Can drop view: DROP VIEW vw_network_devices
4. New ASP pages can be deleted without affecting existing functionality
5. Navigation changes can be reverted

Risk Level: LOW - This is pure additive functionality, no changes to existing code.

---

Part 9: Time Estimates

Task	Time	Notes
Database migration	30 min	Run script + verify
Server pages (4 files)	4-6 hours	First set, establish pattern
Switch pages (4 files)	2-3 hours	Copy/modify from servers
Camera pages (4 files)	2-3 hours	Copy/modify from servers
Navigation updates	30 min	Add menu items
Testing	3-4 hours	Full testing cycle
Documentation	1-2 hours	User guide updates
Total	13-19 hours	~2-3 days of work

---

Part 10: Success Criteria

Database:

• All 3 tables have modelid column with FK to models
• vw_network_devices view returns data from all 3 tables

Functionality:

• Can add/edit/view/delete servers, switches, cameras
• Vendor/model information displays correctly
• Forms validate inputs properly
• No SQL errors

User Experience:

• Navigation easy to find
• Forms intuitive (like printer/machine forms)
• List views show relevant info at a glance

Code Quality:

• Follows existing coding standards (STANDARDS.md)
• Uses parameterized queries (no SQL injection)
• Proper error handling
• Consistent with printer/machine patterns

---

Next Steps

1. Get approval on this simplified approach
2. Run database migration on test environment
3. Start with server pages - establish the pattern
4. Copy/adapt for switches and cameras - reuse code
5. Test thoroughly
6. Document and deploy

---

Document Status: Ready for Implementation Last Updated: 2025-10-23 Approved By: [Pending]