Index: ActiviteitenOpvolging/ActiviteitenOpvolging/domain/WorkItem.vb =================================================================== diff -u -r1677 -r1679 --- ActiviteitenOpvolging/ActiviteitenOpvolging/domain/WorkItem.vb (.../WorkItem.vb) (revision 1677) +++ ActiviteitenOpvolging/ActiviteitenOpvolging/domain/WorkItem.vb (.../WorkItem.vb) (revision 1679) @@ -5,9 +5,9 @@ Public Property DeliverToResource As String Implements IWorkItem.DeliverToResource Public Property ProductGroup As String Implements IWorkItem.ProductGroup Public Property Remark As String Implements IWorkItem.Remark - Public Property StartBeforeTimestamp As TimeSpan Implements IWorkItem.StartBeforeTimestamp - Public Property StartAlmostTimeStamp As TimeSpan Implements IWorkItem.StartAlmostTimeStamp - Public Property StartTimeStamp As TimeSpan Implements IWorkItem.StartTimeStamp - Public Property EndTimeStamp As TimeSpan Implements IWorkItem.EndTimeStamp + Public Property StartBeforeTimestamp As DateTime Implements IWorkItem.StartBeforeTimestamp + Public Property StartAlmostTimeStamp As DateTime Implements IWorkItem.StartAlmostTimeStamp + Public Property StartTimeStamp As DateTime Implements IWorkItem.StartTimeStamp + Public Property EndTimeStamp As DateTime Implements IWorkItem.EndTimeStamp Public Property Activities As List(Of IActivity) Implements IWorkItem.Activities End Class Index: ActiviteitenOpvolging/ActiviteitenOpvolging/domain/IWorkItem.vb =================================================================== diff -u -r1677 -r1679 --- ActiviteitenOpvolging/ActiviteitenOpvolging/domain/IWorkItem.vb (.../IWorkItem.vb) (revision 1677) +++ ActiviteitenOpvolging/ActiviteitenOpvolging/domain/IWorkItem.vb (.../IWorkItem.vb) (revision 1679) @@ -5,9 +5,9 @@ Property DeliverToResource As String Property ProductGroup As String Property Remark As String - Property StartBeforeTimestamp As TimeSpan - Property StartAlmostTimeStamp As TimeSpan - Property StartTimeStamp As TimeSpan - Property EndTimeStamp As TimeSpan + Property StartBeforeTimestamp As DateTime + Property StartAlmostTimeStamp As DateTime + Property StartTimeStamp As DateTime + Property EndTimeStamp As DateTime Property Activities As List(Of IActivity) End Interface Index: ActiviteitenOpvolging/ActiviteitenOpvolging/enums/ActivityStateEnum.vb =================================================================== diff -u -r1671 -r1679 --- ActiviteitenOpvolging/ActiviteitenOpvolging/enums/ActivityStateEnum.vb (.../ActivityStateEnum.vb) (revision 1671) +++ ActiviteitenOpvolging/ActiviteitenOpvolging/enums/ActivityStateEnum.vb (.../ActivityStateEnum.vb) (revision 1679) @@ -32,4 +32,25 @@ End Select End Function + ''' + ''' Gets the corresponding enum value from ActivityStateEnum with a char value. + ''' + ''' + ''' + public Function GetActivityStateFromLetter(letter As Char) As ActivityStateEnum + 'If Nothing return default. + if letter = nothing Then Return ActivityStateEnum.Defaulted + 'Check for type and return enum value. + Select Case Char.ToUpper(letter) + Case "S"C + Return ActivityStateEnum.Pauzed + Case "P"C + Return ActivityStateEnum.Started + Case "F"C + Return ActivityStateEnum.Finished + Case Else + Throw New Exception($"The activity state value letter is unknown for getting a corresponding enum value: '{letter}'.") + End Select + End Function + End Module Index: ActiviteitenOpvolging/ActiviteitenOpvolging/mappers/CacheMapper.vb =================================================================== diff -u -r1678 -r1679 --- ActiviteitenOpvolging/ActiviteitenOpvolging/mappers/CacheMapper.vb (.../CacheMapper.vb) (revision 1678) +++ ActiviteitenOpvolging/ActiviteitenOpvolging/mappers/CacheMapper.vb (.../CacheMapper.vb) (revision 1679) @@ -28,6 +28,62 @@ End Sub ''' + ''' Execution of a query into a data reader. + ''' + ''' The query + ''' The ODBC data reader + Private Function ExecuteIntoDataReader(query As String) As OdbcDataReader + Try + ConnectToCache() + Return New OdbcCommand(query, _sqlConnection).ExecuteReader() + Catch ex As Exception + Throw New Exception($"Could not get data reader from the Caché database.{vbNewLine}{ex.Message}") + End Try + End Function + + Private Function ExtractItemFromDataReader(Of T)(ByRef dr As OdbcDataReader, key As String, Optional defaultValue As T = Nothing) As T + Dim value As Object + Try + 'Check if the column name is not present. If not present, return default value. + If Not HasColumn(dr, key) Then Return defaultValue + + 'Extract the value with the key. + value = dr.Item(key) + 'Check if the value is DBNull or Nothing. + If IsDBNull(value) Or value Is Nothing Then + 'If so, return default value. + Return defaultValue + End If + Catch ex As Exception + Throw New Exception($"A value for a work item can't be fetched from the data reader. The key is '{key}'.") + End Try + Try + 'Do the conversion explicitly, so exceptions can be caught. + Return CType(value, T) + Catch ex As Exception + Throw New Exception($"A value for a work item can't be converted to the specified type. The key is '{key}', the type is {GetType(T).Name}, the value is {value}.") + End Try + End Function + + ''' + ''' Check if the data reader has a column with that name. + ''' + ''' + ''' + ''' + Public Function HasColumn(ByRef dr As OdbcDataReader, columnName As String) As Boolean + 'Loop through each row. + For Each row As DataRow In dr.GetSchemaTable().Rows + 'Check if column name matches defined one. + If row("ColumnName").ToString() = columnName Then Return True + Next + 'Column name was not found. + Return False + End Function + +#End Region + + ''' ''' Fetch all the work items for a workpost. ''' The corresponding query will get fetched in here. ''' @@ -43,13 +99,25 @@ 'Execute query and store datareader. Dim dr = ExecuteIntoDataReader(query) + 'Fetch amount of activities. + Dim activityAmount = FetchNumberOfActivities(workPostIndex) + 'Loop through every (result) record. While dr.Read() - 'Read values of one result (line/record). + 'Values of one result (line/record). + Dim workItem As New WorkItem + 'Fill with values. With workItem - .BatchVisual = ExtractItemFromDataReader(Of Integer)(dr, "BatchVisual", 0) - .DeliverToResource = ExtractItemFromDataReader(Of String)(dr, "skrr", String.Empty) + .BatchVisual = ExtractItemFromDataReader(dr, "BatchVisual", 0) + .DeliverToResource = ExtractItemFromDataReader(dr, "Naar", String.Empty) + .ProductGroup = ExtractItemFromDataReader(dr, "Productgroep", String.Empty) + .Remark = ExtractItemFromDataReader(dr, "Opmerking", String.Empty) + .StartBeforeTimestamp = ExtractItemFromDataReader(dr, "StartVoorafTijdstip", DateTime.MinValue) + .StartAlmostTimeStamp = ExtractItemFromDataReader(dr, "StartBijnaTijdstip", DateTime.MinValue) + .StartTimeStamp = ExtractItemFromDataReader(dr, "StartTijdstip", DateTime.MinValue) + .EndTimeStamp = ExtractItemFromDataReader(dr, "EindTijdstip", DateTime.MinValue) + .Activities = ExtractActivities(dr, activityAmount) End With 'Add to the result list. @@ -82,59 +150,52 @@ Return sqlQueries.Item(workPostIndex) End Function +#Region "Activities" ''' - ''' Execution of a query into a data reader. + ''' Extract all activities for one work item from the data reader. ''' - ''' The query - ''' The ODBC data reader - Private Function ExecuteIntoDataReader(query As String) As OdbcDataReader - Try - ConnectToCache() - Return New OdbcCommand(query, _sqlConnection).ExecuteReader() - Catch ex As Exception - Throw New Exception($"Could not get data reader from the Caché database.{vbNewLine}{ex.Message}") - End Try - End Function + ''' + ''' + Private Function ExtractActivities(dr As OdbcDataReader, numberOfActivities As Integer) As List(Of IActivity) + 'Declare result list. + Dim activities As New List(Of IActivity) - Private Function ExtractItemFromDataReader(Of T)(ByRef dr As OdbcDataReader, key As String, Optional defaultValue As T = Nothing) As T - Dim value As Object - Try - 'Check if the column name is not present. If not present, return default value. - if not HasColumn(dr, key) Then Return defaultValue + 'Loop for every activity. + For i As Integer = 1 To numberOfActivities + Dim activity As New Activity + With activity + .Id = ExtractItemFromDataReader(dr, $"ActiviteitID_{i}", String.Empty) + .QuantityToProduce = ExtractItemFromDataReader(dr, $"Aantal_{i}", 0) + .Label = ExtractItemFromDataReader(dr, $"Label_{i}", String.Empty) + .Status = ActivityStateEnumMapper.GetActivityStateFromLetter(ExtractItemFromDataReader(of Char)(dr, $"Status_{i}")) + .User = ExtractItemFromDataReader(dr, $"Gebruiker_{i}", String.Empty) + .IsReadOnly = ExtractItemFromDataReader(dr, $"ReadOnly_{i}", False) + End With + 'Add to collection of activities. + activities.Add(activity) + Next - 'Extract the value with the key. - value = dr.Item(key) - 'Check if the value is DBNull or Nothing. - If IsDBNull(value) Or value Is Nothing Then - 'If so, return default value. - Return defaultValue - End If - Catch ex As Exception - Throw New Exception($"A value for a work item can't be fetched from the data reader. The key is '{key}'.") - End Try - Try - 'Do the conversion explicitly, so exceptions can be caught. - Return CType(value, T) - Catch ex As Exception - Throw New Exception($"A value for a work item can't be converted to the specified type. The key is '{key}', the type is {NameOf(T)}, the value is {value}.") - End Try + 'Return list. + Return activities End Function ''' - ''' Check if the data reader has a column with that name. + ''' Get the correct amount of activities. ''' - ''' - ''' + ''' ''' - Public Function HasColumn(ByRef dr As OdbcDataReader, columnName As String) As Boolean - 'Loop through each row. - For Each row As DataRow In dr.GetSchemaTable().Rows - 'Check if column name matches defined one. - If row("ColumnName").ToString() = ColumnName Then Return True - Next - 'Column name was not found. - Return False + Private Function FetchNumberOfActivities(workPostIndex As Integer) As String + 'Fetch queries from config. + Dim activityAmounts = ConfigsLoader.NumbersOfActivities() + 'Check if index is valid. + If workPostIndex >= activityAmounts.Count Or workPostIndex < 0 Then + Throw New InternalException($"Can't fetch the amount of activities with this work post index: {workPostIndex}") + End If + 'If so, return correct amount. + Return activityAmounts.Item(workPostIndex) End Function #End Region + + End Module