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 AddDetailsForTotalDeckArea(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears, List <SimulationDataModel> simulationDataModels)
{
int startRow, startColumn, row, column;
bridgeWorkSummaryCommon.InitializeLabelCells(worksheet, currentCell, out startRow, out startColumn, out row, out column);
AddTotalDeckArea(worksheet, simulationDataModels, startRow, column, 0);
foreach (var year in simulationYears)
{
row = startRow;
column = ++column;
AddTotalDeckArea(worksheet, simulationDataModels, row, column, year);
}
excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row + 2, column]);
excelHelper.SetCustomFormat(worksheet.Cells[startRow, startColumn + 1, row + 2, column], "Number");
excelHelper.ApplyColor(worksheet.Cells[row + 2, startColumn + 1, row + 2, column], Color.Khaki);
bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, row + 3, 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);
}
private void AddDynamicDataCells(ExcelWorksheet worksheet, List <Section> sectionsForSummaryReport, List <SimulationDataModel> simulationDataModels,
List <BridgeDataModel> bridgeDataModels, CurrentCell currentCell, int columnForRiskScore)
{
var row = 4; // Data starts here
var startingRow = row;
var column = currentCell.Column;
int totalColumn = 0;
int totalColumnValue = 0;
var abbreviatedTreatmentNames = ShortNamesForTreatments.GetShortNamesForTreatments();
// making dictionary to remove if else, which was used to enter value for MinC
valueForMinC = new Dictionary <MinCValue, Func <ExcelWorksheet, int, int, YearsData, int> >();
valueForMinC.Add(MinCValue.defaultValue, new Func <ExcelWorksheet, int, int, YearsData, int>(EnterDefaultMinCValue));
valueForMinC.Add(MinCValue.valueEqualsCulv, new Func <ExcelWorksheet, int, int, YearsData, int>(EnterValueEqualsCulv));
valueForMinC.Add(MinCValue.minOfDeckSubSuper, new Func <ExcelWorksheet, int, int, YearsData, int>(EnterMinDeckSuperSub));
valueForMinC.Add(MinCValue.minOfCulvDeckSubSuper, new Func <ExcelWorksheet, int, int, YearsData, int>(EnterMinDeckSuperSubCulv));
foreach (var bridgeDataModel in bridgeDataModels)
{
if (row % 2 == 0)
{
excelHelper.ApplyColor(worksheet.Cells[row, 1, row, worksheet.Dimension.Columns], Color.LightGray);
}
column = currentCell.Column;
var brKey = bridgeDataModel.BRKey;
var familyId = bridgeDataModel.BridgeFamily;
var workDoneMoreThanOnce = 0;
var section = sectionsForSummaryReport.Where(s => Convert.ToInt32(s.FACILITY) == brKey).FirstOrDefault();
var simulationDataModel = simulationDataModels.Where(s => s.SectionId == section.SECTIONID).FirstOrDefault();
// Save DeckArea for further use
simulationDataModel.DeckArea = bridgeDataModel.DeckArea;
simulationDataModel.BRKey = brKey;
bridgeDataModel.RiskScore = simulationDataModel.RiskScore;
worksheet.Cells[row, columnForRiskScore].Value = simulationDataModel.RiskScore;
var yearsData = simulationDataModel.YearsData;
var projectPickByYear = new Dictionary <int, int>();
// Add work done cells
for (var index = 1; index < yearsData.Count(); index++)
{
var cost = yearsData[index].Cost;
var range = worksheet.Cells[row, ++column];
projectPickByYear.Add(yearsData[index].Year, yearsData[index].ProjectPickType);
setColor(bridgeDataModel.ParallelBridge, yearsData[index].Treatment, projectPickByYear,
yearsData[index].Year, index, yearsData[index].Project, worksheet, row, column);
if (abbreviatedTreatmentNames.ContainsKey(yearsData[index].Treatment))
{
range.Value = string.IsNullOrEmpty(abbreviatedTreatmentNames[yearsData[index].Treatment]) ? "--" : abbreviatedTreatmentNames[yearsData[index].Treatment];
}
else
{
range.Value = string.IsNullOrEmpty(yearsData[index].Treatment) ? "--" : yearsData[index].Treatment;
}
workDoneMoreThanOnce = !range.Value.Equals("--") ? workDoneMoreThanOnce + 1 : workDoneMoreThanOnce;
}
worksheet.Cells[row, ++column].Value = workDoneMoreThanOnce > 1 ? "Yes" : "--";
totalColumnValue = workDoneMoreThanOnce > 1 ? totalColumnValue + 1 : totalColumnValue;
// Empty Total column
column++;
// Add Total of count of Work done more than once column cells if "Yes"
totalColumn = column;
// Add Poor On/Off Rate column: Formula (prev yr MinC < 5 and curr yr Minc >= 5 then "Off"), (prev yr MinC >= 5 and curr ye MinC < 5 then "On")
for (var index = 1; index < yearsData.Count(); index++)
{
var prevYrMinc = yearsData[index - 1].MinC;
var thisYrMinc = yearsData[index].MinC;
worksheet.Cells[row, ++column].Value = prevYrMinc < 5 ? (thisYrMinc >= 5 ? "Off" : "--") : (thisYrMinc < 5 ? "On" : "--");
yearsData[index].PoorOnOffRate = worksheet.Cells[row, column].Value.ToString();
}
// Empty column
column++;
worksheet.Column(column).Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Column(column).Style.Fill.BackgroundColor.SetColor(Color.Gray);
// Last Year simulation data
var lastYearData = yearsData.FirstOrDefault();
column = AddSimulationYearData(worksheet, row, column, lastYearData, familyId, bridgeDataModel, projectPickByYear);
// Add all yrs from current year simulation data
for (var index = 1; index < yearsData.Count(); index++)
{
column = AddSimulationYearData(worksheet, row, column, yearsData[index], familyId, bridgeDataModel, projectPickByYear);
}
row++;
}
if (totalColumn != 0)
{
worksheet.Cells[3, totalColumn].Value = totalColumnValue;
}
currentCell.Row = row - 1;
currentCell.Column = column - 1;
}