public async Task <SummaryAssessment> CalculateTasks(double desiredCompletionTime, string estimationTimeFormat) { var tasksItems = await GetTasksList(); var totalAssessments = new SummaryAssessment(); totalAssessments.DesiredCompletionTime = desiredCompletionTime; totalAssessments.EstimationTimeFormat = estimationTimeFormat; foreach (var taskItem in tasksItems) { taskItem.Assessments.Estimation = 0; taskItem.Assessments.StDeviation = 0; taskItem.Assessments.Variance = 0; } foreach (var taskItem in tasksItems) { var optimistic = taskItem.Assessments.Optimistic; var mostLikely = taskItem.Assessments.MostLikely; var pessimistic = taskItem.Assessments.Pessimistic; var deviation = await _calculateService.CalculateStDeviation(optimistic, pessimistic); taskItem.Assessments.Estimation = await _calculateService.CalculateEstimation(optimistic, mostLikely, pessimistic); taskItem.Assessments.StDeviation += deviation; taskItem.Assessments.Variance += await _calculateService.CalculateVariance(deviation); totalAssessments.SumEstimation += taskItem.Assessments.Estimation; totalAssessments.SumStDeviation += taskItem.Assessments.StDeviation; totalAssessments.SumVariance += taskItem.Assessments.Variance; } totalAssessments.PercentageOfCompletion = await _calculateService.CalculatePercentageOfCompletion(totalAssessments.SumEstimation, totalAssessments.SumVariance, desiredCompletionTime); await _cacheService.Upadate <SummaryAssessment>(nameof(SummaryAssessment), totalAssessments); return(totalAssessments); }
public async Task GenerateReport(ObservableCollection <TaskItem> tasks, SummaryAssessment totalAssessments, string pathWithFileName) { if (!string.IsNullOrEmpty(pathWithFileName)) { if (tasks == null || !tasks.Any()) { throw ThrowIfNullOrEmpry("tasks"); } if (totalAssessments == null) { throw ThrowIfNullOrEmpry("total assessments"); } var workBook = new XLWorkbook(); workBook = await PrepareSourceDataTable(workBook, tasks, totalAssessments); workBook = await PrepareResultDataTable(workBook, tasks, totalAssessments); await _excelFileService.Save(workBook, pathWithFileName); } }
internal async Task <XLWorkbook> PrepareResultDataTable(XLWorkbook workBook, ObservableCollection <TaskItem> tasks, SummaryAssessment total) { var resultDataSheet = workBook.Worksheets.Add(Properties.Resources.calculationResult); var columnA = resultDataSheet.Column("A"); columnA.Width = 70; var columnB = resultDataSheet.Column("B"); columnB.Width = 25; var columnC = resultDataSheet.Column("C"); columnC.Width = 30; var columnD = resultDataSheet.Column("D"); columnD.Width = 25; var tableHeader = resultDataSheet.Cell("A1"); tableHeader = await SetStyleToCell(tableHeader, backgroundColor : XLColor.FromArgb(196, 215, 155), fontSize : 16); tableHeader.Value = Properties.Resources.calculationResult; resultDataSheet.Range("A1:D1").Merge(); var taskDescriptionHead = resultDataSheet.Cell("A2"); taskDescriptionHead = await SetStyleToCell(taskDescriptionHead, backgroundColor : XLColor.FromArgb(235, 241, 222)); taskDescriptionHead.Value = Properties.Resources.taskDescription; var expectedTimeHead = resultDataSheet.Cell("B2"); expectedTimeHead = await SetStyleToCell(expectedTimeHead, backgroundColor : XLColor.FromArgb(235, 241, 222)); expectedTimeHead.Value = Properties.Resources.expectedTime; var stDeviationHead = resultDataSheet.Cell("C2"); stDeviationHead = await SetStyleToCell(stDeviationHead, backgroundColor : XLColor.FromArgb(235, 241, 222)); stDeviationHead.Value = Properties.Resources.stDeviation; var varianceHead = resultDataSheet.Cell("D2"); varianceHead = await SetStyleToCell(varianceHead, backgroundColor : XLColor.FromArgb(235, 241, 222)); varianceHead.Value = Properties.Resources.variance; var itemNumber = 3; for (int i = 0; i < tasks.Count; i++) { var descriptionCell = resultDataSheet.Cell($"A{itemNumber}"); var expectedTimeCell = resultDataSheet.Cell($"B{itemNumber}"); var stDeviationCell = resultDataSheet.Cell($"C{itemNumber}"); var varianceCell = resultDataSheet.Cell($"D{itemNumber}"); descriptionCell = await SetStyleToCell(descriptionCell); descriptionCell.Value = tasks[i].Description; expectedTimeCell = await SetStyleToCell(expectedTimeCell); expectedTimeCell.Value = tasks[i].Assessments.Estimation; stDeviationCell = await SetStyleToCell(stDeviationCell); stDeviationCell.Value = tasks[i].Assessments.StDeviation; varianceCell = await SetStyleToCell(varianceCell); varianceCell.Value = tasks[i].Assessments.Variance; itemNumber++; } var endOfTable = itemNumber++; var expectedTimeSumCell = resultDataSheet.Cell($"B{endOfTable}"); expectedTimeSumCell = await SetStyleToCell(expectedTimeSumCell, backgroundColor : XLColor.FromArgb(146, 208, 80), fontSize : 14); expectedTimeSumCell.Value = total.SumEstimation; var stDeviationSumCell = resultDataSheet.Cell($"C{endOfTable}"); stDeviationSumCell = await SetStyleToCell(stDeviationSumCell, backgroundColor : XLColor.FromArgb(146, 208, 80), fontSize : 14); stDeviationSumCell.Value = total.SumStDeviation; var varianceSumCell = resultDataSheet.Cell($"D{endOfTable}"); varianceSumCell = await SetStyleToCell(varianceSumCell, backgroundColor : XLColor.FromArgb(146, 208, 80), fontSize : 14); varianceSumCell.Value = total.SumVariance; var probabilityOfCompletionCell = resultDataSheet.Cell($"A{endOfTable++}"); probabilityOfCompletionCell = await SetStyleToCell(probabilityOfCompletionCell, backgroundColor : XLColor.FromArgb(146, 208, 80), fontSize : 14, isFontBold : true); probabilityOfCompletionCell.Value = $"{Properties.Resources.probabilityCompletion}: {total.PercentageOfCompletion} %"; return(workBook); }
internal async Task <XLWorkbook> PrepareSourceDataTable(XLWorkbook workBook, ObservableCollection <TaskItem> tasks, SummaryAssessment total) { var sourceDataSheet = workBook.Worksheets.Add(Properties.Resources.sourceData); var columnA = sourceDataSheet.Column("A"); columnA.Width = 70; var columnB = sourceDataSheet.Column("B"); columnB.Width = 25; var columnC = sourceDataSheet.Column("C"); columnC.Width = 25; var columnD = sourceDataSheet.Column("D"); columnD.Width = 25; var tableHeader = sourceDataSheet.Cell("A1"); tableHeader = await SetStyleToCell(tableHeader, backgroundColor : XLColor.FromArgb(250, 191, 143), fontSize : 16); tableHeader.Value = Properties.Resources.sourceData; sourceDataSheet.Range("A1:D1").Merge(); var taskDescriptionHead = sourceDataSheet.Cell("A2"); taskDescriptionHead = await SetStyleToCell(taskDescriptionHead, backgroundColor : XLColor.Almond); taskDescriptionHead.Value = Properties.Resources.taskDescription; var optimisticHead = sourceDataSheet.Cell("B2"); optimisticHead = await SetStyleToCell(optimisticHead, backgroundColor : XLColor.Almond); optimisticHead.Value = Properties.Resources.optimistic; var mostLikelyHead = sourceDataSheet.Cell("C2"); mostLikelyHead = await SetStyleToCell(mostLikelyHead, backgroundColor : XLColor.Almond); mostLikelyHead.Value = Properties.Resources.mostLikely; var pessimisticHead = sourceDataSheet.Cell("D2"); pessimisticHead = await SetStyleToCell(pessimisticHead, backgroundColor : XLColor.Almond); pessimisticHead.Value = Properties.Resources.pessimistic; var itemNumber = 3; for (int i = 0; i < tasks.Count; i++) { var descriptionCell = sourceDataSheet.Cell($"A{itemNumber}"); var optimisticCell = sourceDataSheet.Cell($"B{itemNumber}"); var mostLikelyCell = sourceDataSheet.Cell($"C{itemNumber}"); var pessimisticCell = sourceDataSheet.Cell($"D{itemNumber}"); descriptionCell = await SetStyleToCell(descriptionCell); descriptionCell.Value = tasks[i].Description; optimisticCell = await SetStyleToCell(optimisticCell); optimisticCell.Value = tasks[i].Assessments.Optimistic; mostLikelyCell = await SetStyleToCell(mostLikelyCell); mostLikelyCell.Value = tasks[i].Assessments.MostLikely; pessimisticCell = await SetStyleToCell(pessimisticCell); pessimisticCell.Value = tasks[i].Assessments.Pessimistic; itemNumber++; } var endOfTable = itemNumber++; var desiredTimeCell = sourceDataSheet.Cell($"A{endOfTable}"); desiredTimeCell = await SetStyleToCell(desiredTimeCell, backgroundColor : XLColor.Almond, fontSize : 14, isFontBold : true); await SetStyleToCell(sourceDataSheet.Cell($"B{endOfTable}"), backgroundColor : XLColor.Almond, fontSize : 14, isFontBold : true); desiredTimeCell.Value = $"{Properties.Resources.desiredCompletionTime}: {total.DesiredCompletionTime}"; sourceDataSheet.Range($"A{endOfTable}:B{endOfTable}").Merge(); var setTimeFormatCell = sourceDataSheet.Cell($"C{endOfTable}"); setTimeFormatCell = await SetStyleToCell(setTimeFormatCell, backgroundColor : XLColor.Almond, fontSize : 14); await SetStyleToCell(sourceDataSheet.Cell($"D{endOfTable}"), backgroundColor : XLColor.Almond, fontSize : 14, isFontBold : true); setTimeFormatCell.Value = $"{Properties.Resources.timeUnit}: {total.EstimationTimeFormat}"; sourceDataSheet.Range($"C{endOfTable}:D{endOfTable}").Merge(); return(workBook); }