Also does paging count as another API call?
Comment actions
Post
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)
dt.Dispose()
Dim returnFields As New ClarizenService.stringList
returnFields.Add("ReportedBy")
returnFields.Add("ReportedDate")
returnFields.Add("Duration")
returnFields.Add("WorkItem")
returnFields.Add("RegularHours")
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)
Loop
End If
Else
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")
outfile.Write(sr1.ToString())
End Using
Me.GridView1.DataSource = dv
Me.GridView1.DataBind()
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
dv.Table.Rows.Add(newRow)
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"
returnFields.Add("Name")
returnFields.Add("Description")
returnFields.Add("Project")
returnFields.Add("SYSID")
Case "Project"
returnFields.Add("Name")
returnFields.Add("Description")
returnFields.Add("SYSID")
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)
Else
Return Nothing
End If
End Function
Please sign in to leave a comment.
2 comments
Date
Votes
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.
Ophir
Comment actions