public static string GetSimulationIDFromActivityID(string activityID)
{
string simulationID = null;
using (SqlConnection connection = new SqlConnection(DB.ConnectionString))
{
try
{
connection.Open();
string query = "SELECT SIMULATIONID FROM " + DB.TablePrefix + "TREATMENTS WHERE TREATMENTID='" + activityID + "'";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
if (dr["SIMULATIONID"] != DBNull.Value)
{
simulationID = dr["SIMULATIONID"].ToString();
}
}
}
catch (Exception e)
{
DataAccessExceptionHandler.HandleException(e, false);
simulationID = null;
}
}
return(simulationID);
}
/// <summary>
/// Checks if table exists in database with input connection string.
/// </summary>
/// <param name="tableName">Table name to check</param>
/// /// <param name="connectionString">Connection string</param>
/// <returns>1 if table exists, 0 if table does not exist</returns>
public static int CheckIfTableExists(string tableName, string connectionString)
{
int exists = 0;
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
SqlCommand cmd = new SqlCommand("select case when exists((select * from information_schema.tables where table_name = @tableName)) then 1 else 0 end", connection);
cmd.Parameters.Add(new SqlParameter("tableName", tableName));
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
exists = Convert.ToInt32(dr[0]);
}
}
catch (Exception e)
{
DataAccessExceptionHandler.HandleException(e, false);
return(exists);
}
}
return(exists);
}
private static string GetYearID(string simulationID, int year, string budgetName)
{
string yearID = null;
using (SqlConnection connection = new SqlConnection(DB.ConnectionString))
{
try
{
connection.Open();
string query = "SELECT YEARID FROM " + DB.TablePrefix + "YEARLYINVESTMENT WHERE SIMULATIONID='" + simulationID + "' AND YEAR_='" + year.ToString() + "' AND BUDGETNAME='" + budgetName + "'";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
if (dr["YEARID"] != DBNull.Value)
{
yearID = dr["YEARID"].ToString();
}
}
}
catch (Exception e)
{
DataAccessExceptionHandler.HandleException(e, false);
yearID = null;
}
}
return(yearID);
}
/// <summary>
/// Retrieves a list of all Attributes available in treatments. Extend this to include Cost and Consequence criteria (and consquence attributes)
/// </summary>
/// <param name="simulationID"></param>
/// <returns></returns>
public static List <AttributeStore> GetTreatmentAttributes(string assetName, string simulationID)
{
List <AttributeStore> attributes = new List <AttributeStore>();
using (SqlConnection connection = new SqlConnection(DB.ConnectionString))//DecisionEngine connection string
{
try
{
connection.Open();
string query = "SELECT CRITERIA FROM " + DB.TablePrefix + "FEASIBILITY F INNER JOIN " + DB.TablePrefix + "TREATMENTS T ON F.TREATMENTID=T.TREATMENTID WHERE T.SIMULATIONID='" + simulationID + "'";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string criteria = null;
if (dr["CRITERIA"] != DBNull.Value)
{
criteria = dr["CRITERIA"].ToString();
List <AttributeStore> criteriaAttributes = OMS.ParseAttributes(assetName, criteria);
foreach (AttributeStore attribute in criteriaAttributes)
{
if (!attributes.Contains(attribute))
{
attributes.Add(attribute);
}
}
}
}
dr.Close();
//Get COSTS
query = "SELECT COST_ FROM " + DB.TablePrefix + "COSTS C INNER JOIN " + DB.TablePrefix + "TREATMENTS T on C.TREATMENTID = T.TREATMENTID WHERE T.SIMULATIONID =@simulationID";
cmd = new SqlCommand(query, connection);
cmd.Parameters.Add(new SqlParameter("simulationID", simulationID));
dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["COST_"] != DBNull.Value)
{
string cost = dr["COST_"].ToString();
List <AttributeStore> costAttributes = OMS.ParseAttributes(assetName, cost);
foreach (AttributeStore attribute in costAttributes)
{
if (!attributes.Contains(attribute))
{
attributes.Add(attribute);
}
}
}
}
}
catch (Exception e)
{
DataAccessExceptionHandler.HandleException(e, false);
}
}
return(attributes);
}
/// <summary>
/// Retrieves a list of all Attributes available in Performance. Extend if Equation based performance.
/// </summary>
/// <param name="simulationID"></param>
/// <returns></returns>
public static List <AttributeStore> GetPerformanceAttributes(string assetName, string simulationID)
{
List <AttributeStore> attributes = new List <AttributeStore>();
using (SqlConnection connection = new SqlConnection(DB.ConnectionString))//DecisionEngine connection string
{
try
{
connection.Open();
string query = "SELECT CRITERIA FROM " + DB.TablePrefix + "PERFORMANCE WHERE SIMULATIONID='" + simulationID + "'";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string criteria = null;
if (dr["CRITERIA"] != DBNull.Value)
{
criteria = dr["CRITERIA"].ToString();
List <AttributeStore> criteriaAttributes = OMS.ParseAttributes(assetName, criteria);
foreach (AttributeStore attribute in criteriaAttributes)
{
if (!attributes.Contains(attribute))
{
attributes.Add(attribute);
}
}
}
}
}
catch (Exception e)
{
DataAccessExceptionHandler.HandleException(e, false);
}
}
AttributeStore ageAttribute = new AttributeStore();
ageAttribute.AttributeField = "AGE";
ageAttribute.DisplayName = "AGE";
ageAttribute.OmsObjectUserIDHierarchy = "AGE";
ageAttribute.FieldType = "NUMBER";
ageAttribute.Minimum = 0;
ageAttribute.Maximum = 100;
ageAttribute.InitialValue = "0";
attributes.Add(ageAttribute);
return(attributes);
}
private static void UpdateImpact(string assetType, string activityID, string conditionIndex, string value)
{
OMSAssetConditionIndexStore oci = OMS.GetAssetConditionIndex(assetType);
OMSConditionIndexStore omsConditionIndex = oci.ConditionIndexes.Find(delegate(OMSConditionIndexStore ci) { return(ci.ConditionCategory == conditionIndex); });
if (omsConditionIndex != null)
{
string attribute = omsConditionIndex.AttributeDE;
if (string.IsNullOrWhiteSpace(value))//The user has deleted an impact.
{
DeleteScenario.DeleteImpact(activityID, attribute);
}
else
{
bool isExists = false;
using (SqlConnection connection = new SqlConnection(DB.ConnectionString))
{
try
{
connection.Open();
string query = "SELECT CONSEQUENCEID FROM " + DB.TablePrefix + "CONSEQUENCES WHERE TREATMENTID='" + activityID + "' AND ATTRIBUTE_='" + attribute + "'";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
isExists = true;
}
}
catch (Exception e)
{
DataAccessExceptionHandler.HandleException(e, false);
return;
}
}
if (isExists)
{
//Update impact
UpdateImpact(activityID, attribute, value);
}
else //Insert impact
{
InsertImpact(activityID, attribute, value);
}
}
}
}
public IEnumerable <Product> GetProducts()
{
try
{
var context = new NorthwindEntities();
return(context.Products.ToList());
}
catch (Exception ex)
{
bool rethrow = DataAccessExceptionHandler.HandleException(ref ex);
if (rethrow)
{
throw ex;
}
return(null);
}
}
/// <summary>
/// Connects to and verifies the username and password exist in the Cartegraph database.
/// </summary>
/// <param name="username">The connecting user</param>
/// <param name="password">The users password</param>
/// <returns>True if the user and password match a user and password in the database</returns>
public static bool GetAuthentication(string username, string password)
{
bool authenticated = false;
if (username == "admin" && password == "cartegraph")
{
authenticated = true;
}
else
{
try
{
authenticated = (System.Web.Security.Membership.ValidateUser(username, password));
}
catch (Exception e)
{
DataAccessExceptionHandler.HandleException(e, false);
}
}
return(authenticated);
}
public void Delete(TEntity entity)
{
using (TContext context = new TContext())
{
//context.Set<TEntity>().Remove(entity);
//context.SaveChanges();
try
{
var deletedEntity = context.Entry(entity);
deletedEntity.State = EntityState.Deleted;
context.SaveChanges();
}
catch (Exception ex)
{
bool rethrow = DataAccessExceptionHandler.HandleException(ref ex);
if (rethrow)
{
throw ex;
}
}
}
}
/// <summary>
/// Returns a list of all attributes used in Priority Critera.
/// </summary>
/// <param name="assetName">Asset type for which simulation is performed</param>
/// <param name="simulationID">SimulationID of anlaysis</param>
/// <returns></returns>
public static List <AttributeStore> GetPriorityAttributes(string assetName, string simulationID)
{
List <AttributeStore> attributes = new List <AttributeStore>();
using (SqlConnection connection = new SqlConnection(DB.ConnectionString))//DecisionEngine connection string
{
try
{
connection.Open();
string query = "SELECT CRITERIA FROM " + DB.TablePrefix + "PRIORITY WHERE SIMULATIONID='" + simulationID + "'";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string criteria = null;
if (dr["CRITERIA"] != DBNull.Value)
{
criteria = dr["CRITERIA"].ToString();
List <AttributeStore> criteriaAttributes = OMS.ParseAttributes(assetName, criteria);
foreach (AttributeStore attribute in criteriaAttributes)
{
if (!attributes.Contains(attribute))
{
attributes.Add(attribute);
}
}
}
}
}
catch (Exception e)
{
DataAccessExceptionHandler.HandleException(e, false);
}
}
return(attributes);
}
