public DeficientResult GetData(SimulationModel data, int[] totalYears) { // Deficient and DeficientResults are models. Deficient gets data // from the database. DeficientResult gets the processed data IQueryable <DeficientReportModel> deficients = null; DeficientResult result = null; var select = "SELECT TargetID, Years, TargetMet, IsDeficient " + " FROM Target_" + data.NetworkId + "_" + data.SimulationId; try { var rawDeficientList = db.Database.SqlQuery <DeficientReportModel>(select).AsQueryable(); deficients = rawDeficientList.Where(_ => _.IsDeficient == true); var targetAndYear = this.deficients.GetData(deficients); result = GetDeficientInformation(data, targetAndYear, totalYears); } catch (SqlException ex) { HandleException.SqlError(ex, "Target"); } catch (OutOfMemoryException ex) { HandleException.OutOfMemoryError(ex); } catch (Exception ex) { HandleException.GeneralError(ex); } return(result); }
/// <summary> /// Get Simulation_x_y dynamic table data, x = Newtwork Id, y = Simulation Id /// </summary> /// <param name="simulationModel"></param> /// <param name="dbContext"></param> /// <param name="simulationYears"></param> /// <returns>Datatable for run time selected columns</returns> public DataTable GetSimulationData(SimulationModel simulationModel, BridgeCareContext dbContext, List <int> simulationYears) { var simulationDataTable = new DataTable(); var dynamicColumns = GetDynamicColumns(simulationYears); var selectSimulationStatement = "SELECT SECTIONID, " + Properties.Resources.DeckSeeded + "0, " + Properties.Resources.SupSeeded + "0, " + Properties.Resources.SubSeeded + "0, " + Properties.Resources.CulvSeeded + "0, " + Properties.Resources.DeckDurationN + "0, " + Properties.Resources.SupDurationN + "0, " + Properties.Resources.SubDurationN + "0, " + Properties.Resources.CulvDurationN + "0" + dynamicColumns + " FROM SIMULATION_" + simulationModel.NetworkId + "_" + simulationModel.SimulationId + " WITH (NOLOCK)"; try { var connection = new SqlConnection(dbContext.Database.Connection.ConnectionString); using (var cmd = new SqlCommand(selectSimulationStatement, connection)) { cmd.CommandTimeout = 180; var dataAdapter = new SqlDataAdapter(cmd); dataAdapter.Fill(simulationDataTable); dataAdapter.Dispose(); } } catch (SqlException ex) { var table = "Simulation_" + simulationModel.NetworkId + "_" + simulationModel.SimulationId; if (ex.Number == 207) { throw new InvalidOperationException($"{table} table does not have all the required simulation variables in the database to run summary report."); } HandleException.SqlError(ex, table); } catch (OutOfMemoryException ex) { HandleException.OutOfMemoryError(ex); } return(simulationDataTable); }
public SectionLocationModel Locate(SectionModel section, BridgeCareContext db) { try { var sectionLocationModel = new SectionLocationModel(); // Including isnull statments to change these to '0' to avoid an // exception as mapping a null to a non nullable data type // crashes the entity framwork var query = $"SELECT Sectionid, isnull(Lat,0) as Latitude, isnull(Long,0) as Longitude FROM Segment_{section.NetworkId}_NS0 WHERE SectionId = @sectionId"; // create and open db connection var connection = new SqlConnection(db.Database.Connection.ConnectionString); connection.Open(); // create a sql command with the query and connection var sqlCommand = new SqlCommand(query, connection); // add parameter to sqlCommand sqlCommand.Parameters.Add(new SqlParameter() { ParameterName = "@sectionId", Value = section.SectionId }); // create data reader from the sql command var dataReader = sqlCommand.ExecuteReader(); // check if data reader has rows and can execute a Read if (dataReader.HasRows && dataReader.Read()) { // get the section location details sectionLocationModel.SectionId = dataReader.GetFieldValue <int>(0); sectionLocationModel.Latitude = dataReader.GetFieldValue <double>(1); sectionLocationModel.Longitude = dataReader.GetFieldValue <double>(2); } // close the data reader dataReader.Close(); // close the connection connection.Close(); // return the sectionLocationModel return(sectionLocationModel); } catch (SqlException ex) { log.Error(ex.Message); HandleException.SqlError(ex, "SectionLocator_"); } catch (OutOfMemoryException ex) { log.Error(ex.Message); HandleException.OutOfMemoryError(ex); } catch (Exception ex) { log.Error(ex.Message); HandleException.GeneralError(ex); } return(new SectionLocationModel()); }
public IQueryable <SectionModel> GetSections(int networkId, BridgeCareContext db) { IQueryable <SectionModel> rawQueryForData = null; var select = String.Format("SELECT sectionid, facility as referenceKey, section as referenceId, {0} as networkId FROM Section_{0}", networkId); try { rawQueryForData = db.Database.SqlQuery <SectionModel>(select).AsQueryable(); } catch (SqlException ex) { HandleException.SqlError(ex, "Section_"); } catch (OutOfMemoryException ex) { HandleException.OutOfMemoryError(ex); } return(rawQueryForData); }
public int GetBrKey(int networkId, int sectionId, BridgeCareContext db) { try { var brKey = -1; // create sql connection var connection = new SqlConnection(db.Database.Connection.ConnectionString); connection.Open(); // create sql parameter sectionIdParameter var sectionIdParameter = new SqlParameter() { ParameterName = "@sectionId", Value = sectionId }; // create query string var query = $"SELECT facility FROM Section_{networkId} WHERE sectionId = @sectionId"; // create sql command with query string and connection var sqlCommand = new SqlCommand(query, connection); // add sectionIdParameter as a parameter for the sql command sqlCommand.Parameters.Add(sectionIdParameter); // get data reader from sql command var dataReader = sqlCommand.ExecuteReader(); // check that the data reader has returned rows and can execute a Read if (dataReader.HasRows && dataReader.Read()) { brKey = Int32.Parse(dataReader.GetFieldValue <string>(0)); } // close the data reader dataReader.Close(); // close the connection connection.Close(); // return brKey return(brKey); } catch (SqlException ex) { log.Error(ex.Message); HandleException.SqlError(ex, "SectionId error"); } return(-1); }
/// <summary> /// Get Project, Cost related data from dynamic table Report_x_y, x = Network Id, y = Simulation Id /// </summary> /// <param name="simulationModel"></param> /// <param name="dbContext"></param> /// <param name="simulationYears"></param> /// <returns></returns> public IQueryable <ReportProjectCost> GetReportData(SimulationModel simulationModel, BridgeCareContext dbContext, List <int> simulationYears) { IQueryable <ReportProjectCost> rawQueryForReportData = null; var years = string.Join(",", simulationYears); var selectReportStatement = "SELECT SECTIONID, TREATMENT, COST_, YEARS " + " FROM REPORT_" + simulationModel.NetworkId + "_" + simulationModel.SimulationId + " WITH(NOLOCK) WHERE BUDGET = 'actual_spent' AND YEARS IN (" + years + ")"; try { rawQueryForReportData = dbContext.Database.SqlQuery <ReportProjectCost>(selectReportStatement).AsQueryable(); } catch (SqlException ex) { HandleException.SqlError(ex, "Report_" + simulationModel.NetworkId + "_" + simulationModel.SimulationId); } catch (OutOfMemoryException ex) { HandleException.OutOfMemoryError(ex); } return(rawQueryForReportData); }
public int GetSectionId(int networkId, int brKey, BridgeCareContext db) { try { var sectionId = -1; // create sql connection var connection = new SqlConnection(db.Database.Connection.ConnectionString); connection.Open(); // create sql parameter facilityParameter var facilityParameter = new SqlParameter() { ParameterName = "@facility", Value = brKey }; // create query string var query = $"SELECT Sectionid FROM Section_{networkId} WHERE facility = @facility"; // create sql command with query string and connection var sqlCommand = new SqlCommand(query, connection); // add facilityParameter as a parameter for the sql command sqlCommand.Parameters.Add(facilityParameter); // get data reader from sql command var dataReader = sqlCommand.ExecuteReader(); // check that the data reader has returned rows and can execute a Read if (dataReader.HasRows && dataReader.Read()) { sectionId = dataReader.GetFieldValue <int>(0); } // close the data reader dataReader.Close(); // close the connection connection.Close(); // return sectionId return(sectionId); } catch (SqlException ex) { HandleException.SqlError(ex, "SectionId error"); } return(-1); }
/// <summary> /// Get Section_x dynamic table data, x = Network Id /// </summary> /// <param name="simulationModel"></param> /// <param name="dbContext"></param> /// <returns>IQueryable<Section></returns> public IQueryable <Section> GetSectionData(SimulationModel simulationModel, BridgeCareContext dbContext) { IQueryable <Section> rawQueryForSectionData = null; // FACILITY is BRKEY, SECTION is BRIDGE_ID var selectSectionStatement = "SELECT SECTIONID, FACILITY, SECTION " + " FROM SECTION_" + simulationModel.NetworkId + " Rpt WITH(NOLOCK) Order By FACILITY ASC"; try { rawQueryForSectionData = dbContext.Database.SqlQuery <Section>(selectSectionStatement).AsQueryable(); } catch (SqlException ex) { HandleException.SqlError(ex, "Section_" + simulationModel.SimulationId); } catch (OutOfMemoryException ex) { HandleException.OutOfMemoryError(ex); } return(rawQueryForSectionData); }
public List <YearlyDataModel> GetYearsData(SimulationModel data) { var yearsForBudget = new List <YearlyDataModel>(); try { yearsForBudget = db.YearlyInvestments.AsNoTracking().Where(_ => _.SIMULATIONID == data.simulationId) .OrderBy(year => year.YEAR_) .Select(p => new YearlyDataModel { Year = p.YEAR_, Amount = p.AMOUNT, BudgetName = p.BUDGETNAME }).ToList(); } catch (SqlException ex) { var log = log4net.LogManager.GetLogger(typeof(DetailedReportDAL)); log.Error(ex.Message); HandleException.SqlError(ex, "Years"); } return(yearsForBudget); }
public YearlyBudgetAndCost GetData(SimulationModel data, string[] budgetTypes) { var budgetYearView = new Hashtable(); var select = "SELECT Years, Budget, Cost_ " + " FROM Report_" + data.NetworkId + "_" + data.SimulationId + " WHERE BUDGET is not null"; try { var rawQueryForData = db.Database.SqlQuery <BudgetModel>(select).AsQueryable(); double sum = 0; foreach (var row in rawQueryForData) { double.TryParse(row.Cost_.Value.ToString(), out double cost); Hashtable yearView; //[NOTE].. Filling up the hash table. Data is coming from the database in such a way // that an Hashtable cannot be filled in one go and the checks are necessary. if (!budgetYearView.Contains(row.Budget)) { yearView = new Hashtable(); budgetYearView.Add(row.Budget, yearView); } else { yearView = (Hashtable)budgetYearView[row.Budget]; } if (yearView.Contains(row.Years)) { sum = (double)yearView[row.Years]; yearView.Remove(row.Years); } sum += cost; yearView.Add(row.Years, sum); costs.Add(new CostDetails { Cost = row.Cost_.Value, Years = row.Years, Budget = row.Budget }); } foreach (string item in budgetYearView.Keys) { if (!budgetTypes.Contains(item)) { budgetYearView.Remove(item); } } } catch (SqlException ex) { HandleException.SqlError(ex, "Network or Simulation"); } catch (OutOfMemoryException ex) { HandleException.OutOfMemoryError(ex); } var budgetReportDetails = new YearlyBudgetAndCost { BudgetForYear = budgetYearView, CostDetails = costs }; return(budgetReportDetails); }