Demand report from RSView32

Public Sub UpdateStatistics()
    On Error GoTo ErrorHandler
    Dim msName As String
    msName = "UpdateStatistics"
    
    Dim nMachineNumber, i, j As Integer
    Dim oMeasuredTags As Tags
    Dim oTag As tag
    Dim sTagNames As StringList
    Dim sigmaTagName As String
    
    Dim Data() As Variant
    Dim oDataLogModel As DataLogModel
    Dim dtStart, dtEnd As Date
    Dim oDLNarrowRecs As DataLogNarrowRecords
    Dim nDataLogSeconds As Integer
    
    Dim dtSubStart, dtSubEnd As Date
    dtSubStart = Now
    gTagDb.GetTag("nLastStatCalcTime").Value = gTagDb.GetTag("nRSViewRuntime").Value
        
    'the number of seconds of data to grab from data log. Statisical calculations will be performed on this data.
    'ie. 60 seconds means that the previous 60 seconds (from the newest record in the data log) will be used for the calcs.
    nDataLogSeconds = CInt(gTagDb.GetTag("Setpoints\nStdDevDataLogDuration").Value) * 60

    'perform the stat calcs for each machine group
     For i = 1 To ThisProject.sMachineGroups.Count
    
        Set oDataLogModel = gDataLog.DataLogModels(ThisProject.sMachineGroups(i) & "_LocalTest")
        Set oMeasuredTags = gTagDb.QueryForTags(ThisProject.sMachineGroups(i) & "\*Measured")
        Set sTagNames = New StringList
        
        'Set the end date to the newest time in the model.
        dtEnd = oDataLogModel.GetTimeOfNewestRecord
        
        'Set the start time to be the previous nDataLogSeconds
        dtStart = DateAdd("s", -nDataLogSeconds, dtEnd)
                       
        For Each oTag In oMeasuredTags
        
            sTagNames.RemoveAll
            sTagNames.Add oTag.FullName
            ReDim Data(0)
                        
            'get the last (dtStart - dtEnd) seconds of data from the data log for the current 'measured' tag
            Set oDLNarrowRecs = oDataLogModel.ReadTagDataNarrow(sTagNames, dtStart, dtEnd, , roMaxReadRecordsAll)
                
            If oDLNarrowRecs.Count <= 0 Then
                MsgBox "Error in VBA Sub UpdateStats - no data log records returned for [" & oTag.FullName & "]", vbOKOnly, "Error in VBA sub UpdateStats"
                Exit Sub
            Else
                'extract the actual tag data from the record
                 For j = 0 To oDLNarrowRecs.Count - 1
                    ReDim Preserve Data(j)
                    Data(j) = oDLNarrowRecs.Item(j + 1).DataLogTagValue.Value
                 Next
                       
                'calculate the standard deviation for the extracted tag data
                sigmaTagName = VBA.Mid(oTag.FullName, 1, Len(oTag.FullName) - 8) & "sigma"  'remove the 'Measured' from the end of the tagname and append 'sigma' instead
                CalcStandardDev Data(), sigmaTagName
            End If
        Next
    Next
    
    'clean up
    Set oDataLogModel = Nothing
    Set oMeasuredTags = Nothing
    Set sTagNames = Nothing
    Set oDLNarrowRecs = Nothing
    ReDim Data(0)
    
    dtSubEnd = Now
    gActivity.Log msName & " duration: dtSubStart: [" & CStr(dtSubStart) & "]" & " dtSubEnd: [" & CStr(dtSubEnd) & "]", , , , "CUSTOM VBA"
    Exit Sub
    
ErrorHandler:
  MsgBox "ERROR #" & Err.Number & ": " & Err.Description, vbOKOnly, "ERROR: " & msName
  gActivity.Log "ERROR #" & Err.Number & ": " & Err.Description, roActivityError, roActivityCustom1, 0, msName
End Sub

Public Function GetAlarmCounts(alarmFilter As String, dbFileName As String) As Variant
    On Error GoTo ErrorHandler
    Dim msName As String
    msName = "GetAlarmCounts"

    Dim oConn As ADODB.Connection
    Dim oRstAlarmCounts As ADODB.Recordset
    Dim oFS As New FileSystemObject
    Dim sSQL As String
    Dim sReturn() As Variant
    Dim i As Integer
        
    Set oRstAlarmCounts = New ADODB.Recordset
        
    'make sure we have a valid alarm log to work with
    If oFS.FileExists(gProject.Path & "\ALMLOG\" & dbFileName & ".dbf") Then
        
        Set oConn = OpenAlarmDB(gProject.Path & "\ALMLOG")
        
        'create a query to get an ordered list of all *distinct* incoming alarms (InFlt or InAlm) associated with the supplied alarm filter
        sSQL = "SELECT TagName, Count(TagName) as Count, TransType FROM " & dbFileName & _
                " WHERE TagName LIKE '%" & alarmFilter & "%'" & _
                " AND TransType LIKE 'In%'" & _
                " GROUP BY TagName"
                        
        oRstAlarmCounts.CursorLocation = adUseClient
        
        'execute the query
        oRstAlarmCounts.Open sSQL, oConn, adOpenDynamic, adLockOptimistic
        
        ReDim sReturn(0, 1)
        sReturn(0, 0) = "No Alarms Found."
        sReturn(0, 1) = 0
        
        'make sure we got some alarms
        If Not oRstAlarmCounts.EOF Then
            
            'create an array sized to the number of alarms we've retrieved
            ReDim sReturn(oRstAlarmCounts.RecordCount - 1, 1)
            
            oRstAlarmCounts.MoveFirst
            
            'loop through the list of alarms and obtain a count for each one.
            For i = 1 To oRstAlarmCounts.RecordCount
                
                ' Add the alarm tagname and count to the return array
                sReturn(i - 1, 0) = VBA.Trim(oRstAlarmCounts.Fields("TagName").Value)
                sReturn(i - 1, 1) = oRstAlarmCounts.Fields("Count").Value
                
                oRstAlarmCounts.MoveNext
            Next
        End If
        
        GetAlarmCounts = Sort2DArray(sReturn)
        
    Else
        MsgBox "VBA sub " & msName & " was supplied with one or more invalid parameters:" & vbNewLine & vbNewLine & _
        "machineFilter:" & vbTab & "[" & alarmFilter & "]" & vbNewLine & _
        "dbFileName (no extension):" & vbTab & "[" & dbFileName & "]", vbOKOnly, "VBA Error in " & msName
    
    End If
    
    oConn.Close
    Set oConn = Nothing
    Set oFS = Nothing
    Set oRstAlarmCounts = Nothing
    
    Exit Function
  
ErrorHandler:
  MsgBox "ERROR #" & Err.Number & ": " & Err.Description, vbOKOnly, "ERROR: " & msName
  gActivity.Log "ERROR #" & Err.Number & ": " & Err.Description, roActivityError, roActivityCustom1, 0, msName
End Function


Public Function OpenAlarmDB(dbPath As String)
    Dim oConn As New ADODB.Connection
    
      oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
               "SourceType=DBF;" & _
               "SourceDB=" & dbPath & ";" & _
               "Exclusive=No"

    Set OpenAlarmDB = oConn

End Function