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