Exemplo n.º 1
0
        /// <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());
            }
        }
Exemplo n.º 2
0
        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);
            }
        }