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);
}
