private int AddCostsOfCommittedWork(ExcelWorksheet worksheet, List <int> simulationYears, CurrentCell currentCell,
List <WorkSummaryByBudgetModel> comittedProjectsData)
{
var startYear = simulationYears[0];
int startRow, startColumn, row, column;
bridgeWorkSummaryCommon.SetRowColumns(currentCell, out startRow, out startColumn, out row, out column);
currentCell.Column = column;
var committedTotalRow = 0;
var uniqueTreatments = new Dictionary <string, int>();
var costForTreatments = new Dictionary <string, double>();
foreach (var data in comittedProjectsData)
{
if (data.YEARS < startYear || data.TREATMENT.ToLower() == "no treatment")
{
continue;
}
if (!uniqueTreatments.ContainsKey(data.TREATMENT))
{
uniqueTreatments.Add(data.TREATMENT, currentCell.Row);
worksheet.Cells[currentCell.Row, currentCell.Column].Value = data.TREATMENT;
var cellToEnterCost = data.YEARS - startYear;
worksheet.Cells[uniqueTreatments[data.TREATMENT], currentCell.Column + cellToEnterCost + 2].Value = data.CostPerTreatmentPerYear;
costForTreatments.Add(data.TREATMENT, data.CostPerTreatmentPerYear);
currentCell.Row += 1;
}
else
{
var cellToEnterCost = data.YEARS - startYear;
worksheet.Cells[uniqueTreatments[data.TREATMENT], currentCell.Column + cellToEnterCost + 2].Value = data.CostPerTreatmentPerYear;
}
}
row = currentCell.Row;
column = currentCell.Column;
worksheet.Cells[row, column].Value = Properties.Resources.CommittedTotal;
column++;
var fromColumn = column + 1;
foreach (var year in simulationYears)
{
var yearlyBudget = comittedProjectsData.FindAll(_ => _.YEARS == year);
var aggregateAmountPerYear = yearlyBudget.Sum(s => s.CostPerTreatmentPerYear);
column = ++column;
worksheet.Cells[row, column].Value = aggregateAmountPerYear;
TotalCommittedSpent.Add(year, aggregateAmountPerYear);
committedTotalRow = row;
}
excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column]);
excelHelper.SetCustomFormat(worksheet.Cells[startRow, fromColumn, row, column], "NegativeCurrency");
excelHelper.ApplyColor(worksheet.Cells[startRow, fromColumn, row, column], Color.FromArgb(198, 224, 180));
excelHelper.ApplyColor(worksheet.Cells[committedTotalRow, fromColumn, committedTotalRow, column], Color.FromArgb(84, 130, 53));
excelHelper.SetTextColor(worksheet.Cells[committedTotalRow, fromColumn, committedTotalRow, column], Color.White);
bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, ++row, column);
return(committedTotalRow);
}
private void AddDetailsForTotalPoorBridgesDeckArea(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears, List <SimulationDataModel> simulationDataModels)
{
int startRow, startColumn, row, column;
bridgeWorkSummaryCommon.SetRowColumns(currentCell, out startRow, out startColumn, out row, out column);
worksheet.Cells[row, column++].Value = Properties.Resources.BridgeCare;
worksheet.Cells[row, column].Value = bridgeWorkSummaryComputationHelper.CalculateTotalPoorBridgesDeckArea(simulationDataModels, 0);
foreach (var year in simulationYears)
{
column = ++column;
worksheet.Cells[row, column].Value = bridgeWorkSummaryComputationHelper.CalculateTotalPoorBridgesDeckArea(simulationDataModels, year);
}
excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column]);
excelHelper.SetCustomFormat(worksheet.Cells[startRow, startColumn + 1, row, column], "Number");
bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, ++row, column);
}
private void AddCountsOfCulvertsWorkedOn(ExcelWorksheet worksheet, List <SimulationDataModel> simulationDataModels, List <int> simulationYears, CurrentCell currentCell, ProjectRowNumberModel projectRowNumberModel)
{
int startRow, startColumn, row, column;
bridgeWorkSummaryCommon.SetRowColumns(currentCell, out startRow, out startColumn, out row, out column);
worksheet.Cells[row++, column].Value = Properties.Resources.NoTreatment;
worksheet.Cells[row++, column].Value = Properties.Resources.Preservation;
worksheet.Cells[row++, column].Value = Properties.Resources.PreservationPoorFix;
worksheet.Cells[row++, column].Value = Properties.Resources.Rehabilitation;
worksheet.Cells[row++, column].Value = Properties.Resources.Replacement;
worksheet.Cells[row++, column].Value = Properties.Resources.Total;
column++;
var fromColumn = column + 1;
foreach (var year in simulationYears)
{
row = startRow;
column = ++column;
var noTreatmentCount = bridgeWorkSummaryComputationHelper.CalculateNoTreatmentCountForCulverts(simulationDataModels, year);
worksheet.Cells[row, column].Value = noTreatmentCount;
projectRowNumberModel.CulvertsNoTreatmentRow = row;
int preservationPoorFixrow = row + 2;
var preservationPoorFixCount = bridgeWorkSummaryComputationHelper.CalculatePreservationPoorFixCount(simulationDataModels, year);
worksheet.Cells[preservationPoorFixrow, column].Value = preservationPoorFixCount;
var preservationCount = bridgeWorkSummaryComputationHelper.CalculateCountByProject(simulationDataModels, year, Properties.Resources.CulvertPreservation) - preservationPoorFixCount;
worksheet.Cells[++row, column].Value = preservationCount;
projectRowNumberModel.CulvertsPreservationRow = row;
row = preservationPoorFixrow + 1;
var rehabilitationCount = bridgeWorkSummaryComputationHelper.CalculateCountByProject(simulationDataModels, year, Properties.Resources.CulvertRehabilitation);
worksheet.Cells[row, column].Value = rehabilitationCount;
projectRowNumberModel.CulvertsRehabilitationRow = row;
var replacementCount = bridgeWorkSummaryComputationHelper.CalculateCountByProject(simulationDataModels, year, Properties.Resources.CulvertReplacement);
worksheet.Cells[++row, column].Value = replacementCount;
projectRowNumberModel.CulvertsReplacementRow = row;
worksheet.Cells[++row, column].Value = preservationCount + preservationPoorFixCount + rehabilitationCount + replacementCount;
}
excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column]);
excelHelper.ApplyColor(worksheet.Cells[startRow, fromColumn, row, column], Color.LightSteelBlue);
bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, ++row, column);
}
private void AddCountsOfCulvertsWorkedOn(ExcelWorksheet worksheet, List <SimulationDataModel> simulationDataModels, List <int> simulationYears, CurrentCell currentCell, ProjectRowNumberModel projectRowNumberModel, List <string> treatments)
{
int startRow, startColumn, row, column;
bridgeWorkSummaryCommon.SetRowColumns(currentCell, out startRow, out startColumn, out row, out column);
foreach (var item in treatments)
{
if (item.ToLower().Contains("culvert"))
{
worksheet.Cells[row++, column].Value = item;
}
}
worksheet.Cells[row++, column].Value = Properties.Resources.Total;
column++;
var fromColumn = column + 1;
foreach (var year in simulationYears)
{
row = startRow;
column = ++column;
double culvertTotalCount = 0;
foreach (var item in treatments)
{
if (item.ToLower().Contains("culvert"))
{
var culvertCount = bridgeWorkSummaryComputationHelper.CalculateCountByProject(simulationDataModels, year, item);
worksheet.Cells[row, column].Value = culvertCount;
projectRowNumberModel.TreatmentsCount.Add(item + "_" + year, row);
row++;
culvertTotalCount += culvertCount;
}
}
worksheet.Cells[row, column].Value = culvertTotalCount;
}
excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column]);
excelHelper.ApplyColor(worksheet.Cells[startRow, fromColumn, row, column], Color.LightSteelBlue);
bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, ++row, column);
}
private int AddCostsOfCulvertWork(ExcelWorksheet worksheet, List <SimulationDataModel> simulationDataModels, List <int> simulationYears, CurrentCell currentCell)
{
int startRow, startColumn, row, column;
bridgeWorkSummaryCommon.SetRowColumns(currentCell, out startRow, out startColumn, out row, out column);
int culvertTotalRow = 0;
worksheet.Cells[row++, column].Value = Properties.Resources.Preservation;
worksheet.Cells[row++, column].Value = Properties.Resources.Rehabilitation;
worksheet.Cells[row++, column].Value = Properties.Resources.Replacement;
worksheet.Cells[row++, column].Value = Properties.Resources.CulvertTotal;
column++;
var fromColumn = column + 1;
foreach (var year in simulationYears)
{
row = startRow;
column = ++column;
var preservationCost = bridgeWorkSummaryComputationHelper.CalculateCost(simulationDataModels, year, Properties.Resources.CulvertPreservation);
worksheet.Cells[row, column].Value = preservationCost;
var rehabilitationCost = bridgeWorkSummaryComputationHelper.CalculateCost(simulationDataModels, year, Properties.Resources.CulvertRehabilitation);
worksheet.Cells[++row, column].Value = rehabilitationCost;
var replacementCost = bridgeWorkSummaryComputationHelper.CalculateCost(simulationDataModels, year, Properties.Resources.CulvertReplacement);
worksheet.Cells[++row, column].Value = replacementCost;
worksheet.Cells[++row, column].Value = preservationCost + rehabilitationCost + replacementCost;
culvertTotalRow = row;
}
excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column]);
excelHelper.SetCustomFormat(worksheet.Cells[startRow, fromColumn, row, column], "NegativeCurrency");
excelHelper.ApplyColor(worksheet.Cells[startRow, fromColumn, row, column], Color.DarkSeaGreen);
bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, ++row, column);
return(culvertTotalRow);
}