public void CreateReportForMonth(List <DeveloperEffortDetails> effortDetails, string outputPath)
{
var combinedBacklogEfforts = new Dictionary <string, float>();
foreach (var effortDetail in effortDetails.SelectMany(x => x.BacklogEfforts).GroupBy(x => x.Key))
{
combinedBacklogEfforts.Add(effortDetail.Key, effortDetail.Sum(x => x.Value));
}
var arr = combinedBacklogEfforts.Keys.Select(int.Parse).ToArray();
Uri uri = new Uri(_uri);
VssBasicCredential credentials = new VssBasicCredential("", _personalAccessToken);
using (WorkItemTrackingHttpClient workItemTrackingHttpClient = new WorkItemTrackingHttpClient(uri, credentials))
{
//get work items for the ids found in query
var workItems = workItemTrackingHttpClient.GetWorkItemsAsync(arr, null, null, WorkItemExpand.All).Result;
var taskItems = workItems.Where(x => Equals(x.Fields["System.WorkItemType"], "Task")).ToList();
var parentItems = workItems.Where(x => !Equals(x.Fields["System.WorkItemType"], "Task")).ToList();
foreach (var workItem in taskItems)
{
var url = workItem.Relations.First(x => x.Rel == "System.LinkTypes.Hierarchy-Reverse").Url;
var workItemId = url.Split('/').Last();
if (combinedBacklogEfforts.ContainsKey(workItemId))
{
combinedBacklogEfforts[workItemId] += combinedBacklogEfforts[workItem.Id.ToString()];
}
else
{
combinedBacklogEfforts[workItemId] = combinedBacklogEfforts[workItem.Id.ToString()];
parentItems.Add(workItemTrackingHttpClient.GetWorkItemAsync(int.Parse(workItemId), null, null, WorkItemExpand.None).Result);
}
}
var workItemsFromTasks = workItemTrackingHttpClient.GetReportingLinksAsync(new[] { "Parent" });
using (var excel = new XLWorkbook())
{
using (var worksheet = excel.Worksheets.Add("Estimation"))
{
worksheet.Row(1).Cell(1).Value = "WorkItemId";
worksheet.Row(1).Cell(2).Value = "State";
worksheet.Row(1).Cell(3).Value = "Estimated Effort";
worksheet.Row(1).Cell(4).Value = "Actual Effort";
worksheet.Row(1).Cell(5).Value = "WorkItemType";
var count = 2;
foreach (var workItem in parentItems.OrderBy(x => x.Fields["System.Title"].ToString()))
{
var workItemId = workItem.Id.ToString();
var title = workItem.Fields["System.Title"].ToString();
var state = workItem.Fields["System.State"].ToString();
var estimatedEffort = workItem.Fields.TryGetValue("Microsoft.VSTS.Scheduling.Effort", out double effort) ? effort : 0;
var actualEffort = combinedBacklogEfforts[workItemId];
worksheet.Row(count).Cell(1).Value = $"{workItemId} - {title}";
worksheet.Row(count).Cell(2).Value = state;
worksheet.Row(count).Cell(3).Value = estimatedEffort;
worksheet.Row(count).Cell(4).Value = actualEffort;
worksheet.Row(count).Cell(5).Value = workItem.Fields["System.WorkItemType"].ToString();
count++;
}
}
excel.SaveAs(outputPath);
}
}
}
