/// <summary>
/// Generate Bridge Summary Report for given simulation details.
/// </summary>
/// <param name="simulationModel"></param>
/// <returns></returns>
public byte[] GenerateExcelReport(SimulationModel simulationModel)
{
// Get data
var simulationId = simulationModel.SimulationId;
var simulationYearsModel = commonSummaryReportData.GetSimulationYearsData(simulationId);
var simulationYears = simulationYearsModel.Years;
var simulationYearsCount = simulationYears.Count;
var dbContext = new BridgeCareContext();
using (ExcelPackage excelPackage = new ExcelPackage(new System.IO.FileInfo("SummaryReport.xlsx")))
{
// Bridge Data tab
var bridgeDataModels = new List <BridgeDataModel>();
var worksheet = excelPackage.Workbook.Worksheets.Add("Bridge Data");
var workSummaryModel = summaryReportBridgeData.Fill(worksheet, simulationModel, simulationYears, dbContext);
// Bridge Work Summary tab
var bridgeWorkSummaryWorkSheet = excelPackage.Workbook.Worksheets.Add("Bridge Work Summary");
var chartRowsModel = bridgeWorkSummary.Fill(bridgeWorkSummaryWorkSheet, workSummaryModel.SimulationDataModels, workSummaryModel.BridgeDataModels, simulationYears, dbContext, simulationId);
// NHS Condition Bridge Cnt tab
worksheet = excelPackage.Workbook.Worksheets.Add("NHS Condition Bridge Cnt");
nhsConditionChart.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.NHSBridgeCountPercentSectionYearsRow, Properties.Resources.NHSConditionByBridgeCountLLCC, simulationYearsCount);
// NHS Condition DA tab
worksheet = excelPackage.Workbook.Worksheets.Add("NHS Condition DA");
nhsConditionChart.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.NHSBridgeDeckAreaPercentSectionYearsRow, Properties.Resources.NHSConditionByDeckAreaLLCC, simulationYearsCount);
// Condition Bridge Cnt tab
worksheet = excelPackage.Workbook.Worksheets.Add("Condition Bridge Cnt");
conditionBridgeCount.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalBridgeCountSectionYearsRow, simulationYearsCount);
// Condition DA tab
worksheet = excelPackage.Workbook.Worksheets.Add("Condition DA");
conditionDeckArea.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalDeckAreaSectionYearsRow, simulationYearsCount);
// Poor Bridge Cnt tab
worksheet = excelPackage.Workbook.Worksheets.Add("Poor Bridge Cnt");
poorBridgeCount.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalPoorBridgesCountSectionYearsRow, simulationYearsCount);
// Poor Bridge DA tab
worksheet = excelPackage.Workbook.Worksheets.Add("Poor Bridge DA");
poorBridgeDeckArea.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalPoorBridgesDeckAreaSectionYearsRow, simulationYearsCount);
return(excelPackage.GetAsByteArray());
}
}
public void GenerateExcelReport(SimulationModel simulationModel)
{
// Get data
var simulationId = simulationModel.simulationId;
var simulationYearsModel = commonSummaryReportData.GetSimulationYearsData(simulationId);
var simulationYears = simulationYearsModel.Years;
simulationYears.Sort();
var simulationYearsCount = simulationYears.Count;
var dbContext = new BridgeCareContext();
using (ExcelPackage excelPackage = new ExcelPackage(new System.IO.FileInfo("SummaryReport.xlsx")))
{
#if DEBUG
var mongoConnection = Settings.Default.MongoDBDevConnectionString;
#else
var mongoConnection = Settings.Default.MongoDBProdConnectionString;
#endif
var client = new MongoClient(mongoConnection);
var MongoDatabase = client.GetDatabase("BridgeCare");
var simulations = MongoDatabase.GetCollection <SimulationModel>("scenarios");
var updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "Begin summary report generation");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
// Simulation parameters TAB
var parametersWorksheet = excelPackage.Workbook.Worksheets.Add("Parameters");
// Bridge Data tab
updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "Begin Bridge Data TAB Generation");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
var bridgeDataModels = new List <BridgeDataModel>();
var worksheet = excelPackage.Workbook.Worksheets.Add("Bridge Data");
var workSummaryModel = summaryReportBridgeData.Fill(worksheet, simulationModel, simulationYears, dbContext);
updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "End of Bridge Data TAB Generation");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
// Filling up parameters tab
summaryReportParameters.Fill(parametersWorksheet, simulationModel, simulationYearsCount, workSummaryModel.ParametersModel);
// Unfunded Recommendations TAB
var unfundedRecommendationWorksheet = excelPackage.Workbook.Worksheets.Add("Unfunded Recommendations");
unfundedRecommendations.Fill(unfundedRecommendationWorksheet, workSummaryModel.UnfundedRecommendations, workSummaryModel.BridgeDataModels, simulationYears);
// Simulation Legend TAB
var shortNameWorksheet = excelPackage.Workbook.Worksheets.Add("Legend");
summaryReportGlossary.Fill(shortNameWorksheet);
// Bridge Work Summary tab
updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "Begin Work summary TAB");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
var bridgeWorkSummaryWorkSheet = excelPackage.Workbook.Worksheets.Add("Bridge Work Summary");
var chartRowsModel = bridgeWorkSummary.Fill(bridgeWorkSummaryWorkSheet, workSummaryModel.SimulationDataModels,
workSummaryModel.BridgeDataModels, simulationYears, dbContext, simulationModel, workSummaryModel.Treatments);
// Bridge work summary by Budget TAB
updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "Begin Work Summary By Budget TAB");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
var summaryByBudgetWorksheet = excelPackage.Workbook.Worksheets.Add("Bridge Work Summary By Budget");
bridgeWorkSummaryByBudget.Fill(summaryByBudgetWorksheet, simulationModel, simulationYears);
// NHS Condition Bridge Cnt tab
worksheet = excelPackage.Workbook.Worksheets.Add("NHS Condition Bridge Cnt");
nhsConditionChart.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.NHSBridgeCountPercentSectionYearsRow, Properties.Resources.NHSConditionByBridgeCountLLCC, simulationYearsCount);
updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "Report generation - NHS Condition TAB");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
// NHS Condition DA tab
worksheet = excelPackage.Workbook.Worksheets.Add("NHS Condition DA");
nhsConditionChart.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.NHSBridgeDeckAreaPercentSectionYearsRow, Properties.Resources.NHSConditionByDeckAreaLLCC, simulationYearsCount);
// Non-NHS Condition Bridge Count
worksheet = excelPackage.Workbook.Worksheets.Add("Non-NHS Condition Bridge Cnt");
nonNHSconditionBridgeCount.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.NonNHSBridgeCountPercentSectionYearsRow, simulationYearsCount);
// Non-NHS Condition DA
worksheet = excelPackage.Workbook.Worksheets.Add("Non-NHS Condition DA");
nonNHSConditionDeckArea.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.NonNHSDeckAreaPercentSectionYearsRow, simulationYearsCount);
// Condition Bridge Cnt tab
worksheet = excelPackage.Workbook.Worksheets.Add("Combined Condition Bridge Cnt");
conditionBridgeCount.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalBridgeCountPercentYearsRow, simulationYearsCount);
updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "Report generation - condition bridge count TAB");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
// Condition DA tab
worksheet = excelPackage.Workbook.Worksheets.Add("Combined Condition DA");
conditionDeckArea.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalDeckAreaPercentYearsRow, simulationYearsCount);
// Poor Bridge Cnt tab
worksheet = excelPackage.Workbook.Worksheets.Add("Poor Bridge Cnt");
poorBridgeCount.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalPoorBridgesCountSectionYearsRow, simulationYearsCount);
updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "Report generation - Poor Bridge count TAB");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
// Poor Bridge DA tab
worksheet = excelPackage.Workbook.Worksheets.Add("Poor Bridge DA");
poorBridgeDeckArea.Fill(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalPoorBridgesDeckAreaSectionYearsRow, simulationYearsCount);
updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "Report generation - Poor Bridge DA TAB");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
// Poor Bridge DA By BPN TAB
worksheet = excelPackage.Workbook.Worksheets.Add("Poor Bridge DA By BPN");
bridgeWorkSummaryCharts.FillPoorDeckAreaByBPN(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalPoorDeckAreaByBPNSectionYearsRow, simulationYearsCount);
// Posted By BPN Bridge Count TAB
//worksheet = excelPackage.Workbook.Worksheets.Add("Posted By BPN Bridge Count");
//bridgeWorkSummaryCharts.FillPostedBridgeCountByBPN(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalBridgePostedCountByBPNYearsRow, simulationYearsCount);
// Posted By BPN Bridge DA
//worksheet = excelPackage.Workbook.Worksheets.Add("Posted By BPN Bridge DA");
//bridgeWorkSummaryCharts.FillPostedBridgeDeckAreaByBPN(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalPostedBridgeDeckAreaByBPNYearsRow, simulationYearsCount);
// Closed By BPN Bridge count
//worksheet = excelPackage.Workbook.Worksheets.Add("Closed By BPN Bridge count");
//bridgeWorkSummaryCharts.FillClosedBridgeCountByBPN(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalClosedBridgeCountByBPNYearsRow, simulationYearsCount);
// Closed By BPN Bridge Deck Area
//worksheet = excelPackage.Workbook.Worksheets.Add("Closed By BPN Bridge DA");
//bridgeWorkSummaryCharts.FillClosedBridgeDeckAreaByBPN(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalClosedBridgeDeckAreaByBPNYearsRow, simulationYearsCount);
// Combiled posted and closed
//worksheet = excelPackage.Workbook.Worksheets.Add("Combined Posted and Closed");
//bridgeWorkSummaryCharts.FillCombinedPostedAndClosedByBPN(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalPostedAndClosedByBPNYearsRow, simulationYearsCount);
// Cash Needed DA By BPN
//worksheet = excelPackage.Workbook.Worksheets.Add("Cash Needed DA By BPN");
//bridgeWorkSummaryCharts.FillCashNeededDeckAreaByBPN(worksheet, bridgeWorkSummaryWorkSheet, chartRowsModel.TotalCashNeededByBPNYearsRow, simulationYearsCount);
var folderPathForSimulation = $"DownloadedReports\\{simulationModel.simulationId}";
string relativeFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, folderPathForSimulation);
Directory.CreateDirectory(relativeFolderPath);
var filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, folderPathForSimulation, "SummaryReport.xlsx");
byte[] bin = excelPackage.GetAsByteArray();
File.WriteAllBytes(filePath, bin);
updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, "Summary report has been generated");
simulations.UpdateOne(s => s.simulationId == simulationId, updateStatus);
}
}