shopfloor-dashboard/api_notifications.asp

<%@ Language=VBScript %>
<!--#include file="../shopdb/includes/sql.asp"-->
<%
' ============================================================================
' API Endpoint: Get Notifications
' Returns current and upcoming notifications in JSON format
' Uses shared sql.asp connection from shopdb project
' ============================================================================

Option Explicit
Response.ContentType = "application/json"
Response.Charset = "UTF-8"

' Allow CORS if needed
Response.AddHeader "Access-Control-Allow-Origin", "*"

Dim objRS, strSQL
Dim now, future
Dim currentEvents, upcomingEvents
Dim jsonOutput

' Error handling
On Error Resume Next

' Initialize arrays
currentEvents = ""
upcomingEvents = ""

' Calculate time window (72 hours)
now = Now()
future = DateAdd("h", 72, now)

' objConn is already created and opened by includes/sql.asp

' Query with isshopfloor filter
strSQL = "SELECT n.notificationid, n.notification, n.starttime, n.endtime, " & _
         "n.ticketnumber, n.link, n.isactive, n.isshopfloor, " & _
         "nt.typename, nt.typecolor " & _
         "FROM notifications n " & _
         "LEFT JOIN notificationtypes nt ON n.notificationtypeid = nt.notificationtypeid " & _
         "WHERE n.isactive = 1 " & _
         "AND n.isshopfloor = 1 " & _
         "AND ((n.starttime <= ? AND (n.endtime IS NULL OR n.endtime >= ?)) " & _
         "     OR (n.starttime BETWEEN ? AND ?)) " & _
         "ORDER BY n.starttime ASC"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, 1, 1

If Err.Number <> 0 Then
    ' Query error
    Response.Write "{""success"":false,""error"":""Database query failed: " & JSONEscape(Err.Description) & """}"
    objConn.Close
    Response.End
End If

' Process records
Do While Not objRS.EOF
    Dim eventJSON, startTime, endTime
    Dim isCurrent

    startTime = objRS("starttime")
    If IsNull(objRS("endtime")) Then
        endTime = Null
    Else
        endTime = objRS("endtime")
    End If

    ' Determine if current or upcoming
    isCurrent = False
    If startTime <= now Then
        If IsNull(endTime) Or endTime >= now Then
            isCurrent = True
        End If
    End If

    ' Build JSON for this event
    eventJSON = "{" & _
        """notificationid"":" & objRS("notificationid") & "," & _
        """notification"":""" & JSONEscape(objRS("notification")) & """," & _
        """starttime"":""" & FormatISO8601(startTime) & """," & _
        """endtime"":" & IIf(IsNull(endTime), "null", """" & FormatISO8601(endTime) & """") & "," & _
        """ticketnumber"":" & IIf(IsNull(objRS("ticketnumber")), "null", """" & JSONEscape(objRS("ticketnumber")) & """") & "," & _
        """link"":" & IIf(IsNull(objRS("link")), "null", """" & JSONEscape(objRS("link")) & """") & "," & _
        """isactive"":" & IIf(CBool(objRS("isactive")), "true", "false") & "," & _
        """isshopfloor"":" & IIf(CBool(objRS("isshopfloor")), "true", "false") & "," & _
        """typename"":""" & JSONEscape(objRS("typename")) & """," & _
        """typecolor"":""" & JSONEscape(objRS("typecolor")) & """" & _
        "}"

    ' Add to appropriate array
    If isCurrent Then
        If Len(currentEvents) > 0 Then currentEvents = currentEvents & ","
        currentEvents = currentEvents & eventJSON
    Else
        If Len(upcomingEvents) > 0 Then upcomingEvents = upcomingEvents & ","
        upcomingEvents = upcomingEvents & eventJSON
    End If

    objRS.MoveNext
Loop

' Close recordset
objRS.Close
' objConn is managed by sql.asp - don't close it here

' Build final JSON response
jsonOutput = "{" & _
    """success"":true," & _
    """timestamp"":""" & FormatISO8601(Now()) & """," & _
    """current"":[" & currentEvents & "]," & _
    """upcoming"":[" & upcomingEvents & "]" & _
    "}"

Response.Write jsonOutput

' ============================================================================
' Helper Functions
' ============================================================================

Function JSONEscape(str)
    If IsNull(str) Then
        JSONEscape = ""
        Exit Function
    End If
    Dim result
    result = Replace(str, "\", "\\")
    result = Replace(result, """", "\""")
    result = Replace(result, Chr(13), "\r")
    result = Replace(result, Chr(10), "\n")
    result = Replace(result, Chr(9), "\t")
    JSONEscape = result
End Function

Function FormatISO8601(dt)
    ' Format date as ISO 8601: 2025-10-24T14:30:00
    If IsNull(dt) Or Not IsDate(dt) Then
        FormatISO8601 = ""
        Exit Function
    End If
    FormatISO8601 = Year(dt) & "-" & _
                    Right("0" & Month(dt), 2) & "-" & _
                    Right("0" & Day(dt), 2) & "T" & _
                    Right("0" & Hour(dt), 2) & ":" & _
                    Right("0" & Minute(dt), 2) & ":" & _
                    Right("0" & Second(dt), 2)
End Function

Function IIf(condition, trueValue, falseValue)
    If condition Then
        IIf = trueValue
    Else
        IIf = falseValue
    End If
End Function
%>