private void AddDetailsForNonNHSCountAndArea(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears,
int totalBridgeCountOrDeckAreaStartRow, int nHSBridgeCountOrDeckAreaStartRow, bool isDeckArea)
{
int startRow, startColumn, row, column;
bridgeWorkSummaryCommon.InitializeLabelCells(worksheet, currentCell, out startRow, out startColumn, out row, out column);
for (var index = 0; index <= simulationYears.Count; index++)
{
var bridgeGood = Convert.ToDouble(worksheet.Cells[totalBridgeCountOrDeckAreaStartRow + 1, column].Value);
var bridgeFair = Convert.ToDouble(worksheet.Cells[totalBridgeCountOrDeckAreaStartRow + 2, column].Value);
var bridgePoor = Convert.ToDouble(worksheet.Cells[totalBridgeCountOrDeckAreaStartRow + 3, column].Value);
var NHSGood = Convert.ToDouble(worksheet.Cells[nHSBridgeCountOrDeckAreaStartRow + 1, column].Value);
var NHSFair = Convert.ToDouble(worksheet.Cells[nHSBridgeCountOrDeckAreaStartRow + 2, column].Value);
var NHSPoor = Convert.ToDouble(worksheet.Cells[nHSBridgeCountOrDeckAreaStartRow + 3, column].Value);
var nonNHSGood = bridgeGood - NHSGood;
var nonNHSFair = bridgeFair - NHSFair;
var nonNHSPoor = bridgePoor - NHSPoor;
worksheet.Cells[startRow, column].Value = nonNHSGood;
worksheet.Cells[startRow + 1, column].Value = nonNHSFair;
worksheet.Cells[startRow + 2, column].Value = nonNHSPoor;
column++;
}
excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, row, column - 1]);
excelHelper.SetCustomFormat(worksheet.Cells[startRow, startColumn + 1, row, column - 1], "Number");
//if (isDeckArea)
//{
// excelHelper.ApplyColor(worksheet.Cells[row - 1, startColumn + 1, row - 1, column - 1], Color.Khaki);
//}
bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, row + 1, column);
}
private void AddMergeSectionHeader(ExcelWorksheet worksheet, string headerText, int yearsCount, CurrentCell currentCell)
{
var row = currentCell.Row;
var column = currentCell.Column;
worksheet.Cells[row, ++column].Value = headerText;
var cells = worksheet.Cells[row, column];
excelHelper.ApplyStyle(cells);
excelHelper.MergeCells(worksheet, row, column, row, column + yearsCount - 1);
cells = worksheet.Cells[row, column, row, column + yearsCount - 1];
excelHelper.ApplyBorder(cells);
++row;
UpdateCurrentCell(currentCell, row, column);
}
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 CurrentCell AddHeadersCells(ExcelWorksheet worksheet, List <string> headers, List <int> simulationYears)
{
int headerRow = 1;
for (int column = 0; column < headers.Count; column++)
{
worksheet.Cells[headerRow, column + 1].Value = headers[column];
}
var currentCell = new CurrentCell {
Row = headerRow, Column = headers.Count
};
excelHelper.ApplyBorder(worksheet.Cells[headerRow, 1, headerRow + 1, worksheet.Dimension.Columns]);
AddDynamicHeadersCells(worksheet, currentCell, simulationYears);
return(currentCell);
}
private int FillTotalBudgetSection(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears, Dictionary <int, List <double> > yearlyBudgetAmounts)
{
bridgeWorkSummaryCommon.AddHeaders(worksheet, currentCell, simulationYears, "Total Budget", "Totals");
worksheet.Cells[currentCell.Row, simulationYears.Count + 3].Value = "Total Analysis Budget (all year)";
excelHelper.ApplyStyle(worksheet.Cells[currentCell.Row, simulationYears.Count + 3]);
excelHelper.ApplyBorder(worksheet.Cells[currentCell.Row, simulationYears.Count + 3]);
var budgetTotalRow = AddDetailsForTotalBudget(worksheet, simulationYears, currentCell, yearlyBudgetAmounts);
return(budgetTotalRow);
}
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 AddDetailsForTotalBridgeAndDeckPercent(ExcelWorksheet worksheet, CurrentCell currentCell, List <int> simulationYears, int dataStartRow)
{
int startRow, startColumn, row, column;
bridgeWorkSummaryCommon.InitializeLabelCells(worksheet, currentCell, out startRow, out startColumn, out row, out column);
for (var index = 0; index <= simulationYears.Count; index++)
{
var sumFormula = "SUM(" + worksheet.Cells[dataStartRow, column, dataStartRow + 2, column] + ")";
worksheet.Cells[startRow, column].Formula = worksheet.Cells[dataStartRow, column] + "/" + sumFormula;
worksheet.Cells[startRow + 1, column].Formula = worksheet.Cells[dataStartRow + 1, column] + "/" + sumFormula;
worksheet.Cells[startRow + 2, column].Formula = worksheet.Cells[dataStartRow + 2, column] + "/" + sumFormula;
column++;
}
excelHelper.ApplyBorder(worksheet.Cells[startRow, startColumn, startRow + 2, column - 1]);
excelHelper.SetCustomFormat(worksheet.Cells[startRow, startColumn + 1, startRow + 2, column], "Percentage");
bridgeWorkSummaryCommon.UpdateCurrentCell(currentCell, row, column - 1);
}