2 followers Follow

Timesheet History

The below code seems very ineffecient for retrieving a users timesheet history.  Anybody have some suggestions?

    Protected Sub s_TimeSheet()

        'Setup DataView

        Dim dt As New Data.DataTable

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Project ID"})

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Project Name"})

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Work Item ID"})

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Work Item Type"})

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Work Item Name"})

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Reported By"})

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Report Date"})

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Duration Value"})

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Duration Unit"})

        dt.Columns.Add(New Data.DataColumn With {.ColumnName = "Regular Hours"})

        Dim dv As New Data.DataView(dt)


        Dim returnFields As New ClarizenService.stringList






        Dim xCompare As New ClarizenService.Compare

        Dim xLeft As New ClarizenService.FieldExpression With {.FieldName = "ReportedBy"}

        Dim xRight As New ClarizenService.ConstantExpression With {.Value = New ClarizenService.EntityId With {.TypeName = "User", .Value = cLogInResults.UserId}}

        xCompare.LeftExpression = xLeft

        xCompare.Operator = ClarizenService.Operator.Equal

        xCompare.RightExpression = xRight

        Dim query As New ClarizenService.TimesheetQuery

        query.Fields = returnFields

        query.Permissions = ClarizenService.PermissionOptions.ObjectAndFields

        query.Where = xCompare

        query.Paging = New ClarizenService.Paging

        Dim queryResult As ClarizenService.QueryResult = cClient.Query(Me.cSessionHeader, query)

        Dim xError As ClarizenService.Error = queryResult.Error

        If queryResult.Entities.Length > 0 Then

            s_TimeSheet_Listing(dv, queryResult)

            If queryResult.Paging.HasMore Then

                Do While queryResult.Paging.HasMore

                    query.Paging = queryResult.Paging

                    queryResult = cClient.Query(Me.cSessionHeader, query)

                    s_TimeSheet_Listing(dv, queryResult)


            End If


            Response.Write("0 Records Returned.")

        End If

        dv.Sort = "Project ID, Work Item ID"

        Dim sr1 As New System.IO.StringWriter

        dv.Table.TableName = "dvTimesheet"

        dv.Table.WriteXml(sr1, System.Data.XmlWriteMode.WriteSchema, False)

        Dim strOutPut As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

        Using outfile As New System.IO.StreamWriter(strOutPut & "\dvTimesheet.xml")


        End Using

        Me.GridView1.DataSource = dv


    End Sub

    Private Sub s_TimeSheet_Listing(ByRef dv As Data.DataView, ByVal queryResult As ClarizenService.QueryResult)

        For Each ge As ClarizenService.GenericEntity In queryResult.Entities

            Dim newRow As Data.DataRow = dv.Table.NewRow

            For x = 0 To ge.Values.Count - 1

                Select Case ge.Values(x).FieldName

                    Case "WorkItem"

                        Dim geWork As ClarizenService.GenericEntity = CType(ge.Values(x).Value, ClarizenService.GenericEntity)

                        Select Case geWork.Id.TypeName

                            Case "Task"

                                Dim geTask As ClarizenService.GenericEntity = f_GetTask("Task", geWork.Id.Value)

                                If Not IsNothing(geTask) Then

                                    For y = 0 To geTask.Values.Count - 1

                                        Select Case geTask.Values(y).FieldName

                                            Case "Project"

                                                Dim geTaskProject As ClarizenService.GenericEntity = CType(geTask.Values(y).Value, ClarizenService.GenericEntity)

                                                Dim geProject As ClarizenService.GenericEntity = f_GetTask("Project", CStr(geTaskProject.Id.Value))

                                                If Not IsNothing(geProject) Then

                                                    For z = 0 To geProject.Values.Count - 1

                                                        Select Case geProject.Values(z).FieldName

                                                            Case "Name"

                                                                newRow.Item("Project Name") = geProject.Values(z).Value

                                                            Case "SYSID"

                                                                newRow.Item("Project ID") = geProject.Values(z).Value

                                                        End Select

                                                    Next z

                                                End If

                                            Case "Name"

                                                newRow.Item("Work Item Name") = geTask.Values(y).Value

                                            Case "SYSID"

                                                newRow.Item("Work Item ID") = geTask.Values(y).Value

                                        End Select

                                    Next y

                                End If

                            Case "Project"

                                Dim geTask As ClarizenService.GenericEntity = f_GetTask("Project", geWork.Id.Value)

                                If Not IsNothing(geTask) Then

                                    For y = 0 To geTask.Values.Count - 1

                                        Select Case geTask.Values(y).FieldName

                                            Case "Name"

                                                newRow.Item("Project Name") = geTask.Values(y).Value

                                            Case "SYSID"

                                                newRow.Item("Project ID") = geTask.Values(y).Value

                                        End Select

                                    Next y

                                End If

                        End Select

                        newRow.Item("Work Item Type") = geWork.Id.TypeName

                    Case "ReportedBy"

                        Dim geReported As ClarizenService.GenericEntity = CType(ge.Values(x).Value, ClarizenService.GenericEntity)

                        newRow.Item("Reported By") = geReported.Id.Value

                    Case "ReportedDate"

                        newRow.Item("Report Date") = Format(CDate(ge.Values(x).Value), "MM/dd/yyyy")

                    Case "Duration"

                        Dim geDuration As ClarizenService.Duration = CType(ge.Values(x).Value, ClarizenService.Duration)

                        newRow.Item("Duration Value") = geDuration.Value

                        newRow.Item("Duration Unit") = geDuration.Unit

                    Case "RegularHours"

                        newRow.Item("Regular Hours") = ge.Values(x).Value

                End Select

            Next x


        Next ge

    End Sub

    Protected Function f_GetTask(ByVal Entity As String, ByVal ID As String) As ClarizenService.GenericEntity

        Dim retrieveMsg As New ClarizenService.RetrieveMessage With {.Id = New ClarizenService.EntityId With {.TypeName = Entity, .Value = ID}}

        Dim returnFields As New ClarizenService.stringList

        Select Case Entity

            Case "Task"





            Case "Project"




        End Select

        retrieveMsg.Fields = returnFields

        retrieveMsg.Permissions = ClarizenService.PermissionOptions.ObjectAndFields

        Dim message() As ClarizenService.BaseMessage = {retrieveMsg}

        Dim result As ClarizenService.Result() = CType(cClient.Execute(cSessionHeader, New ClarizenService.CallInfo, New ClarizenService.CallOptions, message), ClarizenService.Result())

        If result(0).Success Then

            Return CType(CType(result(0), ClarizenService.RetrieveResult).Entity, ClarizenService.GenericEntity)


            Return Nothing

        End If

    End Function




Import from old forum Answered

Please sign in to leave a comment.



Hi Mark.

First, I'd suggest you pull all the information for the task/Project on the Timesheet query. 

The API supports up to 5 'hops' so you can retrieve the project's SysID by specifying 

Workitem.Project.SYSID as a field for the query.

There's no need to issue a RetrieveMessage for each work item.

Second, I'd suggest you use LINQ clauses to retrieve the values of individual fields. That would at least improve readability of your code.

As for paging, yes, each 'Query' message is counted as an API call, including retrieval of more pages.

However, as it seems your code is not UI oriented, I'd suggest you change the page size on the query Paging object, so you can get more results at once. The default page size is 100, which you can increase up to 1,000. 

Using a page size of 1,000 and skipping individual retrieval of the work items would probably save you most of the API calls you previously had for that task and definitely improve the efficiency. 

Hope that helps.


Clarizen Team 0 votes
Comment actions Permalink