ge-aerospace/shopdb
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
shopdb/apishopfloor.asp
cproudlock 8945fe2a0a Add PC-machine relationships API and report, fix shopfloor dashboard 
- Add getPCMachineRelationships API endpoint for PC-to-machine mappings
- Add pcmachinerelationships.asp report page with copy table/CSV/JSON export
- Fix shopfloor dashboard to immediately hide deactivated notifications
- Add Firewall (machinetypeid 46) support to network device pages
- Add model migration warning banner to networkdevices.asp
- Create SQL script for hybrid model/machine type view

Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
2026-01-29 16:06:33 -05:00

506 lines
21 KiB
Plaintext
Raw Permalink Blame History

<%@ Language=VBScript %>
<%
Response.ContentType = "application/json"
Response.Charset = "UTF-8"
Response.AddHeader "Access-Control-Allow-Origin", "*"
Response.AddHeader "Cache-Control", "no-cache, no-store, must-revalidate"
%><!--#include file="./includes/sql.asp"--><%
Dim strSQL, jsonOutput, isFirstCurrent, isFirstUpcoming
Dim businessUnitFilter
Dim st, et, isCurrent, isResolved, isUpcoming
Dim typeName, empSsoRaw, ssoArr, idx
Dim singleSSO, singleName, singlePicture
Dim empName, empPicture
Dim upTypeName, upEmpSsoRaw, upSsoArr, upIdx
Dim upSingleSSO, upSingleName, upSinglePicture
Dim upEmpName, upEmpPicture
' Get business unit filter from query string
businessUnitFilter = Request.QueryString("businessunit")
strSQL = "SELECT n.notificationid, n.notification, n.starttime, n.endtime, " & _
"n.ticketnumber, n.link, n.isactive, n.isshopfloor, n.businessunitid, " & _
"n.employeesso, nt.typename, nt.typecolor, bu.businessunit, " & _
"CASE " & _
" WHEN n.starttime <= NOW() AND (n.endtime IS NULL OR n.endtime >= NOW()) AND n.isactive = 1 THEN 1 " & _
" WHEN n.endtime IS NOT NULL AND n.endtime < NOW() AND DATE_ADD(n.endtime, INTERVAL 30 MINUTE) >= NOW() THEN 1 " & _
" ELSE 0 " & _
"END as is_current, " & _
"CASE " & _
" WHEN n.isactive = 0 THEN 1 " & _
" WHEN n.endtime IS NOT NULL AND n.endtime < NOW() THEN 1 " & _
" ELSE 0 " & _
"END as is_resolved, " & _
"CASE " & _
" WHEN n.starttime > NOW() AND n.starttime <= DATE_ADD(NOW(), INTERVAL 5 DAY) THEN 1 " & _
" ELSE 0 " & _
"END as is_upcoming, " & _
"TIMESTAMPDIFF(MINUTE, n.endtime, NOW()) as minutes_since_end " & _
"FROM notifications n " & _
"LEFT JOIN notificationtypes nt ON n.notificationtypeid = nt.notificationtypeid " & _
"LEFT JOIN businessunits bu ON n.businessunitid = bu.businessunitid " & _
"WHERE n.isshopfloor = 1 AND n.isactive = 1 AND (" & _
" n.endtime IS NULL OR " & _
" DATE_ADD(n.endtime, INTERVAL 30 MINUTE) >= NOW()" & _
")"
' Add business unit filter
If businessUnitFilter <> "" And IsNumeric(businessUnitFilter) Then
' Specific business unit selected - show that BU's notifications AND null (all units) notifications
strSQL = strSQL & " AND (n.businessunitid = " & CLng(businessUnitFilter) & " OR n.businessunitid IS NULL)"
Else
' "All Units" selected - only show notifications with NULL businessunitid (truly for all units)
strSQL = strSQL & " AND n.businessunitid IS NULL"
End If
strSQL = strSQL & " ORDER BY n.notificationid DESC"
Set rs = objConn.Execute(strSQL)
jsonOutput = "{""success"":true,""version"":""v2"",""timestamp"":""" & FormatDateTime(Now(), 2) & " " & FormatDateTime(Now(), 4) & """,""current"":["
isFirstCurrent = True
Do While Not rs.EOF
st = rs("starttime")
et = rs("endtime")
isCurrent = rs("is_current")
isResolved = rs("is_resolved")
If isCurrent = 1 Then
' Check if this is a Recognition with multiple employees
typeName = rs("typename") & ""
empSsoRaw = rs("employeesso") & ""
If LCase(typeName) = "recognition" And Len(empSsoRaw) > 0 And InStr(empSsoRaw, ",") > 0 Then
' Split into individual cards for each employee
ssoArr = Split(empSsoRaw, ",")
For idx = 0 To UBound(ssoArr)
singleSSO = Trim(ssoArr(idx))
singleName = LookupSingleEmployeeName(singleSSO)
singlePicture = LookupSingleEmployeePicture(singleSSO)
If Not isFirstCurrent Then jsonOutput = jsonOutput & ","
isFirstCurrent = False
jsonOutput = jsonOutput & "{"
jsonOutput = jsonOutput & """notificationid"":" & rs("notificationid") & ","
jsonOutput = jsonOutput & """notification"":""" & JSEscape(rs("notification") & "") & ""","
jsonOutput = jsonOutput & """starttime"":""" & ISODate(st) & ""","
jsonOutput = jsonOutput & """endtime"":" & ISODateOrNull(et) & ","
jsonOutput = jsonOutput & """ticketnumber"":" & StrOrNull(rs("ticketnumber")) & ","
jsonOutput = jsonOutput & """link"":" & StrOrNull(rs("link")) & ","
jsonOutput = jsonOutput & """isactive"":" & LCase(CStr(CBool(rs("isactive")))) & ","
jsonOutput = jsonOutput & """isshopfloor"":true,"
jsonOutput = jsonOutput & """resolved"":" & LCase(CStr(CBool(isResolved))) & ","
If Not IsNull(rs("minutes_since_end")) Then
jsonOutput = jsonOutput & """minutes_since_end"":" & rs("minutes_since_end") & ","
Else
jsonOutput = jsonOutput & """minutes_since_end"":null,"
End If
jsonOutput = jsonOutput & """typename"":""" & JSEscape(rs("typename") & "") & ""","
jsonOutput = jsonOutput & """typecolor"":""" & JSEscape(rs("typecolor") & "") & ""","
jsonOutput = jsonOutput & """businessunit"":" & StrOrNull(rs("businessunit")) & ","
jsonOutput = jsonOutput & """employeesso"":" & StrOrNull(singleSSO) & ","
jsonOutput = jsonOutput & """employeename"":" & StrOrNull(singleName) & ","
jsonOutput = jsonOutput & """employeepicture"":" & StrOrNull(singlePicture) & ""
jsonOutput = jsonOutput & "}"
Next
Else
' Single employee or non-recognition - build normally
If Not isFirstCurrent Then jsonOutput = jsonOutput & ","
isFirstCurrent = False
jsonOutput = jsonOutput & "{"
jsonOutput = jsonOutput & """notificationid"":" & rs("notificationid") & ","
jsonOutput = jsonOutput & """notification"":""" & JSEscape(rs("notification") & "") & ""","
jsonOutput = jsonOutput & """starttime"":""" & ISODate(st) & ""","
jsonOutput = jsonOutput & """endtime"":" & ISODateOrNull(et) & ","
jsonOutput = jsonOutput & """ticketnumber"":" & StrOrNull(rs("ticketnumber")) & ","
jsonOutput = jsonOutput & """link"":" & StrOrNull(rs("link")) & ","
jsonOutput = jsonOutput & """isactive"":" & LCase(CStr(CBool(rs("isactive")))) & ","
jsonOutput = jsonOutput & """isshopfloor"":true,"
jsonOutput = jsonOutput & """resolved"":" & LCase(CStr(CBool(isResolved))) & ","
If Not IsNull(rs("minutes_since_end")) Then
jsonOutput = jsonOutput & """minutes_since_end"":" & rs("minutes_since_end") & ","
Else
jsonOutput = jsonOutput & """minutes_since_end"":null,"
End If
jsonOutput = jsonOutput & """typename"":""" & JSEscape(rs("typename") & "") & ""","
jsonOutput = jsonOutput & """typecolor"":""" & JSEscape(rs("typecolor") & "") & ""","
jsonOutput = jsonOutput & """businessunit"":" & StrOrNull(rs("businessunit")) & ","
' Handle employeesso - can be SSO or NAME:customname
If Left(empSsoRaw, 5) = "NAME:" Then
' Custom name - extract name, no picture
empName = Mid(empSsoRaw, 6)
empPicture = ""
jsonOutput = jsonOutput & """employeesso"":null,"
Else
' SSO - lookup name and picture
empName = LookupEmployeeNames(empSsoRaw)
empPicture = LookupEmployeePictures(empSsoRaw)
jsonOutput = jsonOutput & """employeesso"":" & StrOrNull(empSsoRaw) & ","
End If
jsonOutput = jsonOutput & """employeename"":" & StrOrNull(empName) & ","
jsonOutput = jsonOutput & """employeepicture"":" & StrOrNull(empPicture) & ""
jsonOutput = jsonOutput & "}"
End If
End If
rs.MoveNext
Loop
rs.Close
Set rs = objConn.Execute(strSQL)
jsonOutput = jsonOutput & "],""upcoming"":["
isFirstUpcoming = True
Do While Not rs.EOF
st = rs("starttime")
et = rs("endtime")
isUpcoming = rs("is_upcoming")
If isUpcoming = 1 Then
' Check if this is a Recognition with multiple employees
upTypeName = rs("typename") & ""
upEmpSsoRaw = rs("employeesso") & ""
If LCase(upTypeName) = "recognition" And Len(upEmpSsoRaw) > 0 And InStr(upEmpSsoRaw, ",") > 0 Then
' Split into individual cards for each employee
upSsoArr = Split(upEmpSsoRaw, ",")
For upIdx = 0 To UBound(upSsoArr)
upSingleSSO = Trim(upSsoArr(upIdx))
upSingleName = LookupSingleEmployeeName(upSingleSSO)
upSinglePicture = LookupSingleEmployeePicture(upSingleSSO)
If Not isFirstUpcoming Then jsonOutput = jsonOutput & ","
isFirstUpcoming = False
jsonOutput = jsonOutput & "{"
jsonOutput = jsonOutput & """notificationid"":" & rs("notificationid") & ","
jsonOutput = jsonOutput & """notification"":""" & JSEscape(rs("notification") & "") & ""","
jsonOutput = jsonOutput & """starttime"":""" & ISODate(st) & ""","
jsonOutput = jsonOutput & """endtime"":" & ISODateOrNull(et) & ","
jsonOutput = jsonOutput & """ticketnumber"":" & StrOrNull(rs("ticketnumber")) & ","
jsonOutput = jsonOutput & """link"":" & StrOrNull(rs("link")) & ","
jsonOutput = jsonOutput & """isactive"":" & LCase(CStr(CBool(rs("isactive")))) & ","
jsonOutput = jsonOutput & """isshopfloor"":true,"
jsonOutput = jsonOutput & """typename"":""" & JSEscape(rs("typename") & "") & ""","
jsonOutput = jsonOutput & """typecolor"":""" & JSEscape(rs("typecolor") & "") & ""","
jsonOutput = jsonOutput & """businessunit"":" & StrOrNull(rs("businessunit")) & ","
jsonOutput = jsonOutput & """employeesso"":" & StrOrNull(upSingleSSO) & ","
jsonOutput = jsonOutput & """employeename"":" & StrOrNull(upSingleName) & ","
jsonOutput = jsonOutput & """employeepicture"":" & StrOrNull(upSinglePicture) & ""
jsonOutput = jsonOutput & "}"
Next
Else
' Single employee or non-recognition - build normally
If Not isFirstUpcoming Then jsonOutput = jsonOutput & ","
isFirstUpcoming = False
jsonOutput = jsonOutput & "{"
jsonOutput = jsonOutput & """notificationid"":" & rs("notificationid") & ","
jsonOutput = jsonOutput & """notification"":""" & JSEscape(rs("notification") & "") & ""","
jsonOutput = jsonOutput & """starttime"":""" & ISODate(st) & ""","
jsonOutput = jsonOutput & """endtime"":" & ISODateOrNull(et) & ","
jsonOutput = jsonOutput & """ticketnumber"":" & StrOrNull(rs("ticketnumber")) & ","
jsonOutput = jsonOutput & """link"":" & StrOrNull(rs("link")) & ","
jsonOutput = jsonOutput & """isactive"":" & LCase(CStr(CBool(rs("isactive")))) & ","
jsonOutput = jsonOutput & """isshopfloor"":true,"
jsonOutput = jsonOutput & """typename"":""" & JSEscape(rs("typename") & "") & ""","
jsonOutput = jsonOutput & """typecolor"":""" & JSEscape(rs("typecolor") & "") & ""","
jsonOutput = jsonOutput & """businessunit"":" & StrOrNull(rs("businessunit")) & ","
' Handle employeesso - can be SSO or NAME:customname
If Left(upEmpSsoRaw, 5) = "NAME:" Then
' Custom name - extract name, no picture
upEmpName = Mid(upEmpSsoRaw, 6)
upEmpPicture = ""
jsonOutput = jsonOutput & """employeesso"":null,"
Else
' SSO - lookup name and picture
upEmpName = LookupEmployeeNames(upEmpSsoRaw)
upEmpPicture = LookupEmployeePictures(upEmpSsoRaw)
jsonOutput = jsonOutput & """employeesso"":" & StrOrNull(upEmpSsoRaw) & ","
End If
jsonOutput = jsonOutput & """employeename"":" & StrOrNull(upEmpName) & ","
jsonOutput = jsonOutput & """employeepicture"":" & StrOrNull(upEmpPicture) & ""
jsonOutput = jsonOutput & "}"
End If
End If
rs.MoveNext
Loop
rs.Close
jsonOutput = jsonOutput & "]}"
Response.Write jsonOutput
Function JSEscape(s)
Dim r
r = s
r = Replace(r, "\", "\\")
r = Replace(r, """", "\""")
r = Replace(r, Chr(13), "")
r = Replace(r, Chr(10), "\n")
r = Replace(r, Chr(9), "\t")
JSEscape = r
End Function
Function ISODate(d)
If Not IsDate(d) Then
ISODate = ""
Exit Function
End If
ISODate = Year(d) & "-" & Right("0" & Month(d), 2) & "-" & Right("0" & Day(d), 2) & "T" & _
Right("0" & Hour(d), 2) & ":" & Right("0" & Minute(d), 2) & ":" & Right("0" & Second(d), 2)
End Function
Function ISODateOrNull(d)
If IsNull(d) Or Not IsDate(d) Then
ISODateOrNull = "null"
Else
ISODateOrNull = """" & ISODate(d) & """"
End If
End Function
Function StrOrNull(s)
If IsNull(s) Then
StrOrNull = "null"
Else
StrOrNull = """" & JSEscape(s & "") & """"
End If
End Function
' Look up a single employee name from SSO or NAME: prefix
Function LookupSingleEmployeeName(ssoInput)
If IsNull(ssoInput) Or Len(ssoInput & "") = 0 Then
LookupSingleEmployeeName = ""
Exit Function
End If
Dim sso
sso = Trim(ssoInput & "")
' Check if this is a custom NAME: entry
If Left(UCase(sso), 5) = "NAME:" Then
LookupSingleEmployeeName = Mid(sso, 6)
Exit Function
End If
' Try DB lookup for numeric SSO
If IsNumeric(sso) And Len(sso) > 0 Then
Dim empConn, empCmd, empRs, firstName, lastName
On Error Resume Next
Set empConn = Server.CreateObject("ADODB.Connection")
empConn.ConnectionString = GetEmployeeConnectionString()
empConn.Open
If Err.Number = 0 Then
Set empCmd = Server.CreateObject("ADODB.Command")
empCmd.ActiveConnection = empConn
empCmd.CommandText = "SELECT First_Name, Last_Name FROM employees WHERE SSO = ?"
empCmd.CommandType = 1
empCmd.Parameters.Append empCmd.CreateParameter("@sso", 3, 1, , CLng(sso))
Set empRs = empCmd.Execute()
If Err.Number = 0 And Not empRs.EOF Then
firstName = empRs("First_Name") & ""
lastName = empRs("Last_Name") & ""
LookupSingleEmployeeName = firstName & " " & lastName
Else
LookupSingleEmployeeName = sso
End If
If Not empRs Is Nothing Then
If empRs.State = 1 Then empRs.Close
Set empRs = Nothing
End If
Set empCmd = Nothing
empConn.Close
Else
LookupSingleEmployeeName = sso
End If
Set empConn = Nothing
On Error GoTo 0
Else
LookupSingleEmployeeName = sso
End If
End Function
' Look up a single employee picture from SSO
Function LookupSingleEmployeePicture(ssoInput)
If IsNull(ssoInput) Or Len(ssoInput & "") = 0 Then
LookupSingleEmployeePicture = ""
Exit Function
End If
Dim sso
sso = Trim(ssoInput & "")
' NAME: entries have no picture
If Left(UCase(sso), 5) = "NAME:" Then
LookupSingleEmployeePicture = ""
Exit Function
End If
' Try DB lookup for numeric SSO
If IsNumeric(sso) And Len(sso) > 0 Then
Dim empConn, empCmd, empRs, picture
On Error Resume Next
Set empConn = Server.CreateObject("ADODB.Connection")
empConn.ConnectionString = GetEmployeeConnectionString()
empConn.Open
If Err.Number = 0 Then
Set empCmd = Server.CreateObject("ADODB.Command")
empCmd.ActiveConnection = empConn
empCmd.CommandText = "SELECT Picture FROM employees WHERE SSO = ?"
empCmd.CommandType = 1
empCmd.Parameters.Append empCmd.CreateParameter("@sso", 3, 1, , CLng(sso))
Set empRs = empCmd.Execute()
If Err.Number = 0 And Not empRs.EOF Then
picture = empRs("Picture") & ""
LookupSingleEmployeePicture = picture
Else
LookupSingleEmployeePicture = ""
End If
If Not empRs Is Nothing Then
If empRs.State = 1 Then empRs.Close
Set empRs = Nothing
End If
Set empCmd = Nothing
empConn.Close
Else
LookupSingleEmployeePicture = ""
End If
Set empConn = Nothing
On Error GoTo 0
Else
LookupSingleEmployeePicture = ""
End If
End Function
' Look up employee name(s) from SSO(s)
Function LookupEmployeeNames(ssoInput)
If IsNull(ssoInput) Or Len(ssoInput & "") = 0 Then
LookupEmployeeNames = ""
Exit Function
End If
Dim empConn, empCmd, empRs, ssoList, names, i, sso, firstName, lastName
On Error Resume Next
Set empConn = Server.CreateObject("ADODB.Connection")
empConn.ConnectionString = GetEmployeeConnectionString()
empConn.Open
If Err.Number <> 0 Then
' DEBUG: Return error info
LookupEmployeeNames = "[DB Error: " & Err.Description & "]"
Exit Function
End If
ssoList = Split(ssoInput & "", ",")
names = ""
For i = 0 To UBound(ssoList)
sso = Trim(ssoList(i))
If IsNumeric(sso) And Len(sso) > 0 Then
Set empCmd = Server.CreateObject("ADODB.Command")
empCmd.ActiveConnection = empConn
empCmd.CommandText = "SELECT First_Name, Last_Name FROM employees WHERE SSO = ?"
empCmd.CommandType = 1
empCmd.Parameters.Append empCmd.CreateParameter("@sso", 3, 1, , CLng(sso))
Set empRs = empCmd.Execute()
If Err.Number = 0 And Not empRs.EOF Then
firstName = empRs("First_Name") & ""
lastName = empRs("Last_Name") & ""
If Len(names) > 0 Then names = names & ", "
names = names & firstName & " " & lastName
End If
If Not empRs Is Nothing Then
If empRs.State = 1 Then empRs.Close
Set empRs = Nothing
End If
Set empCmd = Nothing
End If
Next
empConn.Close
Set empConn = Nothing
On Error GoTo 0
If Len(names) > 0 Then
LookupEmployeeNames = names
Else
' DEBUG: No names found
LookupEmployeeNames = "[Not found: " & ssoInput & "]"
End If
End Function
' Look up employee picture(s) from SSO(s)
Function LookupEmployeePictures(ssoInput)
If IsNull(ssoInput) Or Len(ssoInput & "") = 0 Then
LookupEmployeePictures = ""
Exit Function
End If
Dim empConn, empCmd, empRs, ssoList, pictures, i, sso, picture
On Error Resume Next
Set empConn = Server.CreateObject("ADODB.Connection")
empConn.ConnectionString = GetEmployeeConnectionString()
empConn.Open
If Err.Number <> 0 Then
LookupEmployeePictures = ""
Exit Function
End If
ssoList = Split(ssoInput & "", ",")
pictures = ""
For i = 0 To UBound(ssoList)
sso = Trim(ssoList(i))
If IsNumeric(sso) And Len(sso) > 0 Then
Set empCmd = Server.CreateObject("ADODB.Command")
empCmd.ActiveConnection = empConn
empCmd.CommandText = "SELECT Picture FROM employees WHERE SSO = ?"
empCmd.CommandType = 1
empCmd.Parameters.Append empCmd.CreateParameter("@sso", 3, 1, , CLng(sso))
Set empRs = empCmd.Execute()
If Err.Number = 0 And Not empRs.EOF Then
picture = empRs("Picture") & ""
If Len(picture) > 0 Then
If Len(pictures) > 0 Then pictures = pictures & ","
pictures = pictures & picture
End If
End If
If Not empRs Is Nothing Then
If empRs.State = 1 Then empRs.Close
Set empRs = Nothing
End If
Set empCmd = Nothing
End If
Next
empConn.Close
Set empConn = Nothing
On Error GoTo 0
LookupEmployeePictures = pictures
End Function
%>
Reference in New Issue View Git Blame Copy Permalink