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