public SimulationModel CloneSimulation(int simulationId, BridgeCareContext db, string username)
{
var simulation = db.Simulations.AsNoTracking()
.Include(s => s.INVESTMENTS)
.Include(s => s.PERFORMANCES)
.Include(s => s.TREATMENTS.Select(t => t.CONSEQUENCES))
.Include(s => s.TREATMENTS.Select(t => t.COSTS))
.Include(s => s.TREATMENTS.Select(t => t.FEASIBILITIES))
.Include(s => s.TREATMENTS.Select(t => t.SCHEDULEDS))
.Include(s => s.PRIORITIES.Select(p => p.PRIORITYFUNDS))
.Include(s => s.TARGETS)
.Include(s => s.DEFICIENTS)
.Include(s => s.REMAINING_LIFE_LIMITS)
.Include(s => s.SPLIT_TREATMENTS.Select(st => st.SPLIT_TREATMENT_LIMITS))
.Include(s => s.COMMITTEDPROJECTS.Select(c => c.COMMIT_CONSEQUENCES))
.Include(s => s.YEARLYINVESTMENTS)
.Include(s => s.PRIORITIZEDNEEDS)
.Include(s => s.TARGET_DEFICIENTS)
.Include(s => s.CriteriaDrivenBudgets)
.First(entity => entity.SIMULATIONID == simulationId);
simulation.OWNER = username;
db.Simulations.Add(simulation); // Primary key will automatically be changed
db.SaveChanges();
return(new SimulationModel(simulation));
}
/// <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);
}
/// <summary>
/// Fetches bridge data using a list of br keys
/// </summary>
/// <param name="brKeys">br keys list</param>
/// <param name="db">BridgeCareContext</param>
/// <returns>BridgeDataModel list</returns>
public List <BridgeDataModel> GetBridgeData(List <int> brKeys, BridgeCareContext db)
{
var bridgeDataModels = new List <BridgeDataModel>();
var penndotBridgeData = db.PennDotBridgeData.Where(p => brKeys.Contains(p.BRKEY)).ToList();
var pennDotReportAData = db.PennDotReportAData.Where(p => brKeys.Contains(p.BRKEY)).ToList();
var sdRisk = db.SdRisks.Where(s => brKeys.Contains(s.BRKEY)).ToList();
brKeys = brKeys.OrderBy(b => b).ToList();
foreach (var BRKey in brKeys)
{
var penndotBridgeDataRow = penndotBridgeData.Where(b => b.BRKEY == BRKey).FirstOrDefault();
var pennDotReportADataRow = pennDotReportAData.Where(p => p.BRKEY == BRKey).FirstOrDefault();
var sdRiskRow = sdRisk.Where(s => s.BRKEY == BRKey).FirstOrDefault();
bridgeDataModels.Add(CreateBridgeDataModel(penndotBridgeDataRow, pennDotReportADataRow, sdRiskRow));
}
return(bridgeDataModels);
}
/// <summary>
/// Deletes a simulation and all records with a foreign key relation into the simulations table
/// Simply returns if no simulation is found
/// </summary>
/// <param name="id">Simulation identifier</param>
/// <param name="db">BridgeCareContext</param>
public void DeleteSimulation(int id, BridgeCareContext db)
{
if (!db.Simulations.Any(s => s.SIMULATIONID == id))
{
return;
}
var simulation = db.Simulations.Single(b => b.SIMULATIONID == id);
db.Entry(simulation).State = EntityState.Deleted;
db.SaveChanges();
var connection = new SqlConnection(db.Database.Connection.ConnectionString);
connection.Open();
var dropQuery = $"IF OBJECT_ID ( 'SIMULATION_{simulation.NETWORKID}_{id}' , 'U' ) IS NOT NULL DROP TABLE SIMULATION_{simulation.NETWORKID}_{id} " +
$"IF OBJECT_ID ( 'REPORT_{simulation.NETWORKID}_{id}' , 'U' ) IS NOT NULL DROP TABLE REPORT_{simulation.NETWORKID}_{id} " +
$"IF OBJECT_ID ( 'BENEFITCOST_{simulation.NETWORKID}_{id}' , 'U' ) IS NOT NULL DROP TABLE BENEFITCOST_{simulation.NETWORKID}_{id} " +
$"IF OBJECT_ID ( 'TARGET_{simulation.NETWORKID}_{id}' , 'U' ) IS NOT NULL DROP TABLE TARGET_{simulation.NETWORKID}_{id} ";
var cmd = new SqlCommand(dropQuery, connection)
{
CommandType = CommandType.Text
};
cmd.ExecuteNonQuery();
connection.Close();
}
public void ValidateEquation(ValidateEquationModel model, BridgeCareContext db)
{
CalculateEvaluate calcEval = new CalculateEvaluate();
if (model.IsPiecewise)
{
checkPiecewise(model.Equation);
}
else
{
string equation = model.Equation.Trim();
equation = checkAttributes(equation, model.IsFunction, db);
if (model.IsFunction)
{
calcEval.BuildFunctionClass(equation, "double", null);
}
else
{
calcEval.BuildTemporaryClass(equation, true);
}
calcEval.CompileAssembly();
}
}
private string checkAttributes(string target, bool isFunction, BridgeCareContext db)
{
List <AttributesEntity> attributes = GetAllowedAttributes(isFunction, db);
target = target.Replace('[', '?');
foreach (AttributesEntity allowedAttribute in attributes)
{
if (target.IndexOf("?" + allowedAttribute.ATTRIBUTE_ + "]") >= 0)
{
if (allowedAttribute.Type_ == "STRING")
{
target = target.Replace("?" + allowedAttribute.ATTRIBUTE_ + "]", "[@" + allowedAttribute.ATTRIBUTE_ + "]");
}
else
{
target = target.Replace("?" + allowedAttribute.ATTRIBUTE_ + "]", "[" + allowedAttribute.ATTRIBUTE_ + "]");
}
}
}
if (target.Count(f => f == '?') > 0)
{
int start = target.IndexOf('?');
int end = target.IndexOf(']');
log.Error("Unsupported Attribute " + target.Substring(start + 1, end - 1));
throw new InvalidOperationException("Unsupported Attribute " + target.Substring(start + 1, end - 1));
}
return(target);
}
/// <summary>
/// Deletes a simulation and all records with a foreign key relation into the simulations table
/// Simply returns if no simulation is found
/// </summary>
/// <param name="id">Simulation identifier</param>
/// <param name="db">BridgeCareContext</param>
private void DeleteSimulation(int id, BridgeCareContext db)
{
var simulation = db.Simulations.Single(b => b.SIMULATIONID == id);
var splitTreatment = db.SplitTreatments.Where(s => s.SIMULATIONID == id);
db.Entry(simulation).State = System.Data.Entity.EntityState.Deleted;
db.SplitTreatments.RemoveRange(db.SplitTreatments.Where(s => s.SIMULATIONID == id));
foreach (var item in splitTreatment)
{
db.SplitTreatmentLimits.RemoveRange(db.SplitTreatmentLimits.Where(r => r.SPLIT_TREATMENT_ID == item.SPLIT_TREATMENT_ID));
}
db.SaveChanges();
using (var connection = new SqlConnection(db.Database.Connection.ConnectionString))
{
connection.Open();
var dropQuery = $"IF OBJECT_ID ( 'SIMULATION_{simulation.NETWORKID}_{id}_0' , 'U' ) IS NOT NULL DROP TABLE SIMULATION_{simulation.NETWORKID}_{id} " +
$"IF OBJECT_ID ( 'REPORT_{simulation.NETWORKID}_{id}' , 'U' ) IS NOT NULL DROP TABLE REPORT_{simulation.NETWORKID}_{id} " +
$"IF OBJECT_ID ( 'BENEFITCOST_{simulation.NETWORKID}_{id}' , 'U' ) IS NOT NULL DROP TABLE BENEFITCOST_{simulation.NETWORKID}_{id} " +
$"IF OBJECT_ID ( 'TARGET_{simulation.NETWORKID}_{id}' , 'U' ) IS NOT NULL DROP TABLE TARGET_{simulation.NETWORKID}_{id} ";
using (var command = new SqlCommand(dropQuery, connection)
{
CommandType = CommandType.Text
})
{
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Updates a simulation; Throws a RowNotInTableException if no simulation is found
/// </summary>
/// <param name="model">SimulationModel</param>
/// <param name="db">BridgeCareContext</param>
private void UpdateSimulation(SimulationModel model, BridgeCareContext db)
{
var simulation = db.Simulations.Single(b => b.SIMULATIONID == model.simulationId);
simulation.SIMULATION = model.simulationName;
db.SaveChanges();
}
/// <summary>
/// Updates the last run date of a simulation
/// Throws a RowNotInTableException if no simulation is found
/// </summary>
/// <param name="id">Simulation identifier</param>
/// <param name="db">BridgeCareContext</param>
public void SetSimulationLastRunDate(int id, BridgeCareContext db)
{
if (!db.Simulations.Any(s => s.SIMULATIONID == id))
{
throw new RowNotInTableException($"No scenario was found with id {id}");
}
var simulation = db.Simulations.Single(s => s.SIMULATIONID == id);
var lastRun = DateTime.Now;
simulation.DATE_LAST_RUN = lastRun;
db.SaveChanges();
#if DEBUG
var mongoConnection = Settings.Default.MongoDBDevConnectionString;
#else
var mongoConnection = Settings.Default.MongoDBProdConnectionString;
#endif
var mongoClient = new MongoClient(mongoConnection);
var mongoDB = mongoClient.GetDatabase("BridgeCare");
var simulations = mongoDB.GetCollection <SimulationModel>("scenarios");
var updateLastRunDate = Builders <SimulationModel> .Update.Set("lastRun", lastRun);
simulations.UpdateOne(s => s.simulationId == id, updateLastRunDate);
}
public byte[] CreateExcelReport(SimulationModel data)
{
// Getting data from the database
var yearlyInvestment = investment.GetYearsData(data);
// Using BridgeCareContext because manual control is needed for the
// creation of the object. This object is going through data heavy
// operation. That is why it is not shared.
var dbContext = new BridgeCareContext();
var totalYears = yearlyInvestment.Select(_ => _.Year).Distinct().ToArray();
using (ExcelPackage excelPackage = new ExcelPackage(new System.IO.FileInfo("DetailedReport.xlsx")))
{
//create a WorkSheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Detailed report");
detailReport.Fill(worksheet, totalYears, data, dbContext);
// Adding new Excel TAB for Budget results
ExcelWorksheet budgetSheet = excelPackage.Workbook.Worksheets.Add("Budget results");
budgetReport.Fill(budgetSheet, totalYears, data, yearlyInvestment);
// Adding new Excel TAB for Deficient results
ExcelWorksheet deficientSheet = excelPackage.Workbook.Worksheets.Add("Deficient results");
deficientReport.Fill(deficientSheet, data, totalYears);
// Adding new Excel TAB for Target Results
ExcelWorksheet targetSheet = excelPackage.Workbook.Worksheets.Add("Target results");
targetReport.Fill(targetSheet, data, totalYears);
return(excelPackage.GetAsByteArray());
}
}
/// <summary>
/// Gets a simulation's analysis data, regardless of ownership
/// </summary>
/// <param name="id">Simulation identifier</param>
/// <param name="db">BridgeCareContext</param>
/// <returns>SimulationAnalysisModel</returns>
public SimulationAnalysisModel GetAnySimulationAnalysis(int id, BridgeCareContext db)
{
if (!db.Simulations.Any(s => s.SIMULATIONID == id))
{
throw new RowNotInTableException($"No scenario found with id {id}");
}
return(GetSimulationAnalysis(id, db));
}
/// <summary>
/// Fetches a simulation's criteria driven budgets
/// Throws RowNotInTableException if no such simulation is found
/// </summary>
/// <param name="id">Simulation id</param>
/// <param name="db">BridgeCareContext</param>
/// <returns>CriteriaDrivenBudgetModel list</returns>
public List <CriteriaDrivenBudgetModel> GetAnyCriteriaDrivenBudgets(int id, BridgeCareContext db)
{
if (!db.Simulations.Any(s => s.SIMULATIONID == id))
{
throw new RowNotInTableException($"No scenario found with {id}");
}
return(GetCriteriaDrivenBudgets(id, db));
}
/// <summary>
/// Updates a simulation regardless of ownership; Throws a RowNotInTableException if no simulation is found
/// </summary>
/// <param name="model">SimulationModel</param>
/// <param name="db">BridgeCareContext</param>
public void UpdateAnySimulation(SimulationModel model, BridgeCareContext db)
{
if (!db.Simulations.Any(s => s.SIMULATIONID == model.simulationId))
{
throw new RowNotInTableException($"No scenario found with id {model.simulationId}");
}
UpdateSimulation(model, db);
}
public CriteriaDrivenBudgetsController(ICriteriaDrivenBudgets repo, BridgeCareContext db)
{
this.repo = repo ?? throw new ArgumentNullException(nameof(repo));
this.db = db ?? throw new ArgumentNullException(nameof(db));
CriteriaDrivenBudgetsGetMethods = CreateGetMethods();
CriteriaDrivenBudgetsSaveMethods = CreateSaveMethods();
}
public DeficientController(IDeficient repo, BridgeCareContext db)
{
this.repo = repo ?? throw new ArgumentNullException(nameof(repo));
this.db = db ?? throw new ArgumentNullException(nameof(db));
DeficientLibraryGetMethods = CreateGetMethods();
DeficientLibrarySaveMethods = CreateSaveMethods();
}
/// <summary>
/// Fetches a simulation's performance library data regardless of ownership
/// Throws a RowNotInTableException if no simulation is found
/// </summary>
/// <param name="id"></param>
/// <param name="db"></param>
/// <returns>PerformanceLibraryModel</returns>
public PerformanceLibraryModel GetAnySimulationPerformanceLibrary(int id, BridgeCareContext db)
{
if (!db.Simulations.Any(s => s.SIMULATIONID == id))
{
throw new RowNotInTableException($"No scenario was found with id {id}");
}
return(GetSimulationPerformanceLibrary(id, db));
}
/// <summary>
/// Deletes a simulation regardless of ownership
/// Simply returns if no simulation is found
/// </summary>
/// <param name="id">Simulation identifier</param>
/// <param name="db">BridgeCareContext</param>
public void DeleteAnySimulation(int id, BridgeCareContext db)
{
if (!db.Simulations.Any(s => s.SIMULATIONID == id))
{
return;
}
DeleteSimulation(id, db);
}
/// <summary>
/// Fetches a simulation's priority library data
/// Throws a RowNotInTableException if no simulation is found
/// </summary>
/// <param name="id">Simulation identifier</param>
/// <param name="db">BridgeCareContext</param>
/// <returns>PriorityLibraryModel</returns>
private PriorityLibraryModel GetSimulationPriorityLibrary(int id, BridgeCareContext db)
{
var simulation = db.Simulations.Include(s => s.PRIORITIES)
.Include(s => s.PRIORITIES.Select(p => p.PRIORITYFUNDS))
.Single(s => s.SIMULATIONID == id);
return(new PriorityLibraryModel(simulation));
}
public PerformanceLibraryController(IPerformanceLibrary repo, BridgeCareContext db)
{
this.repo = repo ?? throw new ArgumentNullException(nameof(repo));
this.db = db ?? throw new ArgumentNullException(nameof(db));
PerformanceLibraryGetMethods = CreateGetMethods();
PerformanceLibrarySaveMethods = CreateSaveMethods();
}
public SimulationAnalysisController(ISimulationAnalysis simulationAnalysis, BridgeCareContext context)
{
repo = simulationAnalysis ?? throw new ArgumentNullException(nameof(simulationAnalysis));
db = context ?? throw new ArgumentNullException(nameof(context));
SimulationAnalysisGetMethods = CreateGetMethods();
SimulationAnalysisUpdateMethods = CreateUpdateMethods();
}
/// <summary>
/// Fetches all networks data
/// Throws a RowNotInTableException if no networks are found
/// </summary>
/// <param name="db">BridgeCareContext</param>
/// <returns>NetworkModel list</returns>
public List <NetworkModel> GetAllNetworks(BridgeCareContext db)
{
if (!db.NETWORKS.Any())
{
throw new RowNotInTableException("No network data could be found.");
}
return(db.NETWORKS.ToList().Select(n => new NetworkModel(n)).ToList());
}
/// <summary>
/// Fetches all attributes data
/// Throws a RowNotInTableException if no attributes are found
/// </summary>
/// <param name="db">BridgeCareContext</param>
/// <returns>AttributeModel list</returns>
public List <AttributeModel> GetAttributes(BridgeCareContext db)
{
if (!db.Attributes.Any())
{
throw new RowNotInTableException("No attribute data could be found.");
}
return(db.Attributes.ToList().Select(a => new AttributeModel(a)).ToList());
}
/// <summary>
/// Get BRKey and BMSId pairs in form of InventorySelectionModels
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public List <InventorySelectionModel> GetInventorySelectionModels(BridgeCareContext db)
{
var select = string.Format("SELECT BRKEY as BRKey, BRIDGE_ID as BMSId FROM PennDot_Report_A");
return(db.Database
.SqlQuery <InventorySelectionModel>(select)
.AsQueryable()
.ToList());
}
/// <summary>
/// Fetches a simulation's investment library data
/// Throws a RowNotInTableException if no simulation is found
/// </summary>
/// <param name="id">Simulation identifier</param>
/// <param name="db">BridgeCareContext</param>
/// <returns>InvestmentLibraryModel</returns>
private InvestmentLibraryModel GetSimulationInvestmentLibrary(int id, BridgeCareContext db)
{
var simulation = db.Simulations
.Include(s => s.INVESTMENTS)
.Include(s => s.YEARLYINVESTMENTS)
.Include(s => s.CriteriaDrivenBudgets)
.Single(s => s.SIMULATIONID == id);
return(new InvestmentLibraryModel(simulation));
}
public SummaryReportParameters(ISimulationAnalysis simulationAnalysis, IInvestmentLibrary inflationRate,
ExcelHelper excelHelper, IPriority priorities, ICriteriaDrivenBudgets budget, BridgeCareContext db)
{
analysisData = simulationAnalysis ?? throw new ArgumentNullException(nameof(simulationAnalysis));
getInflationRate = inflationRate ?? throw new ArgumentNullException(nameof(inflationRate));
this.excelHelper = excelHelper;
getPriorities = priorities ?? throw new ArgumentNullException(nameof(priorities));
budgetCriteria = budget ?? throw new ArgumentNullException(nameof(budget));
this.db = db ?? throw new ArgumentNullException(nameof(db));
}
public static void DeleteEntry(PriorityEntity entity, BridgeCareContext db)
{
if (entity.PRIORITYFUNDS.Any())
{
entity.PRIORITYFUNDS.ToList()
.ForEach(pfEntity => PriorityFundEntity.DeleteEntry(pfEntity, db));
}
db.Entry(entity).State = EntityState.Deleted;
}
public IQueryable <DetailedReportDAL> GetRawQuery(SimulationModel data, BridgeCareContext dbContext)
{
var query = "SELECT Facility, Section, Treatment, NumberTreatment, IsCommitted, Years " +
$"FROM Report_{data.networkId}_{data.simulationId} Rpt WITH (NOLOCK) " +
$"INNER JOIN Section_{data.networkId} Sec WITH (NOLOCK) " +
"ON Rpt.SectionID = Sec.SectionID " +
"Order By Facility, Section, Years";
return(dbContext.Database.SqlQuery <DetailedReportDAL>(query).AsQueryable());
}
/// <summary>
/// Fetches a simulation's treatment library data
/// </summary>
/// <param name="id"></param>
/// <param name="db"></param>
/// <returns></returns>
private TreatmentLibraryModel GetSimulationTreatmentLibrary(int id, BridgeCareContext db)
{
var simulation = db.Simulations.Include(s => s.TREATMENTS)
.Include(s => s.TREATMENTS.Select(t => t.FEASIBILITIES))
.Include(s => s.TREATMENTS.Select(t => t.COSTS))
.Include(s => s.TREATMENTS.Select(t => t.CONSEQUENCES))
.Single(s => s.SIMULATIONID == id);
return(new TreatmentLibraryModel(simulation));
}
public static void DeleteEntry(SplitTreatmentEntity entity, BridgeCareContext db)
{
if (entity.SPLIT_TREATMENT_LIMITS.Any())
{
entity.SPLIT_TREATMENT_LIMITS.ToList()
.ForEach(stl => SplitTreatmentLimitEntity.DeleteEntry(stl, db));
}
db.Entry(entity).State = EntityState.Deleted;
}
public ReportCreator(IDetailedReport yearlyReport, Budget getBudget,
Target targetCell, Deficient deficientCell, Detailed details, BridgeCareContext context)
{
db = context ?? throw new ArgumentNullException(nameof(context));
investment = yearlyReport ?? throw new ArgumentNullException(nameof(yearlyReport));
targetReport = targetCell ?? throw new ArgumentNullException(nameof(targetCell));
deficientReport = deficientCell ?? throw new ArgumentNullException(nameof(deficientCell));
detailReport = details ?? throw new ArgumentNullException(nameof(details));
budgetReport = getBudget ?? throw new ArgumentNullException(nameof(getBudget));
}
