public static void LoadPerformanceCurvesToDecisionEngine(int simulationID, string asset, Boolean addAssetClause) { string assetTypesOID = OMS.GetAssetTypeOID(asset); if (assetTypesOID != null) { OMSAssetConditionIndexStore assetOCI = OMS.GetAssetConditionIndex(asset); string predictionGroupTable = assetOCI.ConditionCategoryTable.Replace("ConditionCategories", "PredictionGroups"); string predictionPointsTable = predictionGroupTable.Replace("PredictionGroups", "PredictionPoints"); using (SqlConnection connection = new SqlConnection(DB.OMSConnectionString)) { connection.Open(); string select = "SELECT " + predictionGroupTable + "OID, PredictionGroup, Filter FROM " + predictionGroupTable + " WHERE Inactive = '0'"; SqlCommand cmd = new SqlCommand(select, connection); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { string predictionGroupOID = dr[predictionGroupTable + "OID"].ToString(); string predictionGroup = ""; if (dr["PredictionGroup"] != DBNull.Value) { predictionGroup = dr["PredictionGroup"].ToString(); } //OMS doesn't use .amount - the parser definitely does not. string criteria = dr["Filter"].ToString().Replace(".amount", "").Replace("[Type]", "[Type1]"); if (addAssetClause) { criteria += " && ([AssetType] is equal to \"" + asset + "\")"; } InsertPredictionPoints(simulationID, predictionGroupOID, predictionGroup, criteria, predictionGroupTable, predictionPointsTable); } } } }
/// <summary> /// Gets the OCI weightings from the OMS database and inserts the result into the DecisionEngine OCI_WEIGHTS table /// </summary> /// <param name="simulation">SimlulationID for which to insert OCI weights</param> public static void ConditionCategoryWeights(String simulationID, List <String> assets) { DeleteScenario.DeleteConditionCategoryWeight(simulationID); foreach (String asset in assets) { OMSAssetConditionIndexStore oci = OMS.GetAssetConditionIndex(asset); for (int i = 0; i < oci.ConditionIndexes.Count; i++) { string conditionIndex = oci.ConditionIndexes[i].AttributeDE; string weight = oci.Weights[i].Weight.ToString(); string criteria = oci.Weights[i].Criteria; if (assets.Count > 1 && criteria != null) { criteria += "[AssetType]=|" + asset + "|"; } else if (assets.Count > 1) { criteria = "[AssetType]=|" + asset + "|"; } try { using (SqlConnection connection = new SqlConnection(DB.ConnectionString)) { connection.Open(); string insert = "INSERT INTO " + DB.TablePrefix + "OCI_WEIGHTS (SIMULATIONID,CONDITION_CATEGORY,WEIGHT,CRITERIA) VALUES(@simulationID,@conditionIndex,@weight,@criteria)"; SqlCommand cmd = new SqlCommand(insert, connection); cmd.Parameters.Add(new SqlParameter("simulationID", simulationID)); cmd.Parameters.Add(new SqlParameter("conditionIndex", "__" + conditionIndex.Replace(" ", "").Replace("/", ""))); cmd.Parameters.Add(new SqlParameter("weight", weight)); if (criteria == null) { cmd.Parameters.Add(new SqlParameter("criteria", DBNull.Value)); } else { cmd.Parameters.Add(new SqlParameter("criteria", criteria)); } cmd.ExecuteNonQuery(); } } catch (Exception ex) { Utility.ExceptionHandling.DataAccessExceptionHandler.HandleException(ex, false); } } } }
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); } } } }
/// <summary> /// Fills the Targets and Deficients tables with values from SimulationStore /// </summary> /// <param name="simulation">SimulationStore for this scenario</param> public static void TargetAndDeficients(SimulationStore simulation) { string simulationID = simulation.SimulationID; //Delete previous Targets and deficient. Recalculate from SimulationStore DeleteScenario.DeleteTargets(simulationID); //This function will only perform useful work if Deficients are added below. DeleteScenario.DeleteDeficients(simulationID); try { //If USE_TARGET then set analysis type to Until Targets and Deficients Met using (SqlConnection connection = new SqlConnection(DB.ConnectionString)) { connection.Open(); string insert = "INSERT INTO " + DB.TablePrefix + "TARGETS " + "(SIMULATIONID,ATTRIBUTE_,TARGETMEAN,TARGETNAME)" + "VALUES('" + simulationID + "','OverallConditionIndex','" + simulation.TargetOCI + "','OMS')"; SqlCommand cmd = new SqlCommand(insert, connection); cmd.ExecuteNonQuery(); //Allow determination of OCI limit and possible separate OCI Remaining Life insert = "INSERT INTO " + DB.TablePrefix + "DEFICIENTS " + "(SIMULATIONID,ATTRIBUTE_,DEFICIENTNAME,DEFICIENT,PERCENTDEFICIENT)" + "VALUES('" + simulationID + "','OverallConditionIndex','OCI Deficient','0','0')"; cmd = new SqlCommand(insert, connection); cmd.ExecuteNonQuery(); //This is so remaining life can be calculated for each individiual condition index OMSAssetConditionIndexStore oci = OMS.GetAssetConditionIndex(simulation.Asset); foreach (OMSConditionIndexStore ci in oci.ConditionIndexes) { insert = "INSERT INTO " + DB.TablePrefix + "DEFICIENTS " + "(SIMULATIONID,ATTRIBUTE_,DEFICIENTNAME,DEFICIENT,PERCENTDEFICIENT)" + "VALUES('" + simulationID + "','" + ci.AttributeDE + "','" + ci.AttributeDE + " Deficient','0','0')"; cmd = new SqlCommand(insert, connection); cmd.ExecuteNonQuery(); } } } catch (Exception ex) { Utility.ExceptionHandling.DataAccessExceptionHandler.HandleException(ex, false); } }
/// <summary> /// Convert SimulationStore into necessary tables entries for RoadCare /// </summary> /// <param name="simulationID"></param> public static void Simulation(string simulationID) { SimulationStore simulation = SelectScenario.GetSimulationStore(simulationID); OMSAssetConditionIndexStore oci = OMS.GetAssetConditionIndex(simulation.Asset); PrepareAnalysis.Priorities(simulation); //PrepareAnalysis.TargetAndDeficients(simulation); PrepareAnalysis.ConditionCategoryWeights(simulation); //PrepareAnalysis.Performance(simulation); //PrepareAnalysis.RepeatedActivities(simulation); //PrepareAnalysis.ActivityFeasibilityCostConsequences(simulation); List <AttributeStore> attributes = PrepareAnalysis.Attributes(simulation); AssetRequestOMSDataStore assetRequest = new AssetRequestOMSDataStore(DateTime.Now, attributes, oci); Dictionary <string, AssetReplyOMSLookupTable> assetLookups = OMS.GetAssetLookupData(assetRequest); List <AssetReplyOMSDataStore> assets = OMS.GetAssetData(assetRequest); List <AssetReplyOMSDataStore> assetsWithCondition = assets.FindAll(delegate(AssetReplyOMSDataStore arods) { return(arods.ConditionIndices != null); }); PrepareAnalysis.Assets(simulation, assetsWithCondition, attributes, assetRequest, assetLookups); }
private static List <AttributeStore> Attributes(SimulationStore simulation) { DeleteScenario.DeleteAttributes(simulation.SimulationID); List <AttributeStore> attributes = DecisionEngine.GetTreatmentAttributes(simulation.Asset, simulation.SimulationID); List <AttributeStore> attributePriority = DecisionEngine.GetPriorityAttributes(simulation.Asset, simulation.SimulationID); foreach (AttributeStore attribute in attributePriority) { if (!attributes.Contains(attribute)) { attributes.Add(attribute); } } List <AttributeStore> attributePerformance = DecisionEngine.GetPerformanceAttributes(simulation.Asset, simulation.SimulationID); foreach (AttributeStore attribute in attributePerformance) { if (!attributes.Contains(attribute)) { attributes.Add(attribute); } } List <AttributeStore> attributeArea = OMS.ParseAttributes(simulation.Asset, simulation.SimulationArea); foreach (AttributeStore attribute in attributeArea) { if (!attributes.Contains(attribute)) { attributes.Add(attribute); } } //Retrieve a list of attributes that are marked as condition categories. List <AttributeStore> conditionCategoryAttributes = OMS.GetAssetAttributes(simulation.Asset).FindAll(delegate(AttributeStore a) { return(a.IsConditionCategory == true); }); foreach (AttributeStore attribute in conditionCategoryAttributes) { if (!attributes.Contains(attribute)) { attributes.Add(attribute); } } OMSAssetConditionIndexStore oci = OMS.GetAssetConditionIndex(simulation.Asset); for (int i = 0; i < oci.ConditionIndexes.Count; i++) { string criteria = oci.Weights[i].Criteria; if (criteria != null) { List <AttributeStore> ociCriteriaAttributes = OMS.ParseAttributes(simulation.Asset, criteria); foreach (AttributeStore attribute in ociCriteriaAttributes) { if (!attributes.Contains(attribute)) { attributes.Add(attribute); } } } } //Adds attributes for JurisdictionFilter List <AttributeStore> jurisdictionAttributes = OMS.ParseAttributes(simulation.Asset, simulation.Jurisdiction); foreach (AttributeStore attribute in jurisdictionAttributes) { if (!attributes.Contains(attribute)) { attributes.Add(attribute); } } //Add street for output display AttributeStore attributeStreet = OMS.GetAssetAttributes(simulation.Asset).Find(delegate(AttributeStore a) { return(a.OmsObjectUserIDHierarchy == "Street"); }); if (attributeStreet != null && !attributes.Contains(attributeStreet)) { attributes.Add(attributeStreet); } //Add ID for output display AttributeStore attributeID = OMS.GetAssetAttributes(simulation.Asset).Find(delegate(AttributeStore a) { return(a.OmsObjectUserIDHierarchy == "ID"); }); if (attributeID != null && !attributes.Contains(attributeID)) { attributes.Add(attributeID); } //Add Installed for calculation of AGE AttributeStore attributeInstalled = OMS.GetAssetAttributes(simulation.Asset).Find(delegate(AttributeStore a) { return(a.OmsObjectUserIDHierarchy == "Installed"); }); if (attributeInstalled != null && !attributes.Contains(attributeInstalled)) { attributes.Add(attributeInstalled); } //Add Replaced for calculation of AGE AttributeStore attributeReplaced = OMS.GetAssetAttributes(simulation.Asset).Find(delegate(AttributeStore a) { return(a.OmsObjectUserIDHierarchy == "Replaced"); }); if (attributeReplaced != null && !attributes.Contains(attributeReplaced)) { attributes.Add(attributeReplaced); } //Add gis shape for output display AttributeStore attributecgShape = OMS.GetAssetAttributes(simulation.Asset).Find(delegate(AttributeStore a) { return(a.OmsObjectUserIDHierarchy == "cgShape"); }); if (attributecgShape != null && !attributes.Contains(attributecgShape)) { attributes.Add(attributecgShape); } foreach (AttributeStore attribute in attributes) { string simulationID = simulation.SimulationID; string attribute_ = attribute.OmsObjectUserIDHierarchy; if (attribute.IsConditionCategory && attribute_ != "OverallConditionIndex") { attribute_ = "__" + attribute_.Replace(" ", "").Replace("/", ""); } string type_ = attribute.FieldType; string default_value = ""; string maximum = ""; string minimum = ""; bool ascending = attribute.Ascending; string format = ""; string isConditionCategory = attribute.IsConditionCategory.ToString();; if (attribute.InitialValue != null) { default_value = attribute.InitialValue; } if (!float.IsNaN(attribute.Minimum)) { minimum = attribute.Minimum.ToString(); } if (!float.IsNaN(attribute.Maximum)) { maximum = attribute.Maximum.ToString(); } if (attribute.Format != null) { format = attribute.Format; } switch (type_) { case "Time": case "Date": case "DateTime": type_ = "DATETIME"; break; case "YesNo": case "Text": case "Lookup": case "Quantity.unit": type_ = "STRING"; break; case "Number": case "Integer": case "Quantity": case "Currency": type_ = "NUMBER"; break; default: break; } try { using (SqlConnection connection = new SqlConnection(DB.ConnectionString)) { connection.Open(); string insert = "INSERT INTO " + DB.TablePrefix + "OMS_ATTRIBUTES " + "(SIMULATIONID,ATTRIBUTE_,TYPE_,DEFAULT_VALUE,MINIMUM_,MAXIMUM,ASCENDING,FORMAT,IS_CONDITION_INDEX,ATTRIBUTE_OMS)" + "VALUES('" + simulationID + "','" + attribute_ + "','" + type_ + "','" + default_value + "','" + minimum + "','" + maximum + "','" + ascending.ToString() + "','" + format + "','" + isConditionCategory + "','" + attribute.OmsHierarchy + "')"; SqlCommand cmd = new SqlCommand(insert, connection); cmd.ExecuteNonQuery(); } } catch (Exception ex) { Utility.ExceptionHandling.DataAccessExceptionHandler.HandleException(ex, false); } } return(attributes); }
public static List <AttributeStore> InitializeRoadCareAttributes(List <String> assets, List <String> additionalAttributes) { DeleteScenario.DeleteAssets("0"); List <AttributeStore> crossAssets = new List <AttributeStore>(); foreach (String asset in assets) { List <AttributeStore> attributes = new List <AttributeStore>(); OMSAssetConditionIndexStore oci = OMS.GetAssetConditionIndex(asset); for (int i = 0; i < oci.ConditionIndexes.Count; i++) { string criteria = oci.Weights[i].Criteria; if (criteria != null) { List <AttributeStore> ociCriteriaAttributes = OMS.ParseAttributes(asset, criteria); foreach (AttributeStore attribute in ociCriteriaAttributes) { if (!attributes.Contains(attribute)) { attributes.Add(attribute); } } } } //Get additional data requested. List <AttributeStore> attributeAsset = OMS.GetAssetAttributes(asset); foreach (String additionalAttribute in additionalAttributes) { AttributeStore additional = attributeAsset.Find(delegate(AttributeStore a) { return(a.AttributeField == additionalAttribute); }); if (!attributes.Contains(additional)) { if (additional != null) { attributes.Add(additional); } } AttributeStore crossAsset = crossAssets.Find(delegate(AttributeStore a) { return(a.AttributeField == additionalAttribute); }); if (crossAsset == null) { if (additional != null) { crossAssets.Add(additional); } } } AssetRequestOMSDataStore assetRequest = new AssetRequestOMSDataStore(DateTime.Now, attributes, oci); Dictionary <string, AssetReplyOMSLookupTable> assetLookups = OMS.GetAssetLookupData(assetRequest); List <AssetReplyOMSDataStore> assetsFromRequest = OMS.GetAssetData(assetRequest); List <AssetReplyOMSDataStore> assetsWithCondition = assetsFromRequest.FindAll(delegate(AssetReplyOMSDataStore arods) { return(arods.ConditionIndices != null); }); PrepareAnalysis.Assets("0", assetsWithCondition, attributes, assetRequest, assetLookups, false); } //Do something with cross attributes. AttributeStore assetType = new AttributeStore(null, "AssetType", "AssetType", null); assetType.FieldType = "Text"; crossAssets.Add(assetType); AttributeStore overallConditionIndex = new AttributeStore(null, "OverallConditionIndex", "OverallConditionIndex", null); overallConditionIndex.FieldType = "Number"; overallConditionIndex.Minimum = 0; overallConditionIndex.Maximum = 100; overallConditionIndex.Format = "f1"; overallConditionIndex.InitialValue = "100"; overallConditionIndex.Ascending = true; crossAssets.Add(overallConditionIndex); foreach (String asset in assets) { OMSAssetConditionIndexStore oci = OMS.GetAssetConditionIndex(asset); foreach (OMSConditionIndexStore ci in oci.ConditionIndexes) { String conditionIndex = "__" + ci.ConditionCategory.Replace(" ", "").Replace("/", ""); AttributeStore attributeConditionIndex = crossAssets.Find(delegate(AttributeStore a) { return(a.OmsObjectUserIDHierarchy == conditionIndex); }); if (attributeConditionIndex == null) { attributeConditionIndex = new AttributeStore(null, conditionIndex, conditionIndex, null); attributeConditionIndex.FieldType = "Number"; crossAssets.Add(attributeConditionIndex); } } } return(crossAssets); }
public static void AddActivity(OMSActivityStore activity, String simulationID, String asset) { //Insert activity in treatments //Get Identity string treatmentID = null; int year = DateTime.Now.Year + 1; using (SqlConnection connection = new SqlConnection(DB.ConnectionString)) { connection.Open(); string insert = "INSERT INTO " + DB.TablePrefix + "TREATMENTS (SIMULATIONID,TREATMENT,BEFOREANY,BEFORESAME,OMS_REPEAT_START, OMS_REPEAT_INTERVAL, OMS_OID, OMS_IS_SELECTED,OMS_IS_EXCLUSIVE, OMS_IS_REPEAT)" + " VALUES ('" + simulationID + "','" + activity.ActivityName + "','1','1','" + year + "','1','" + activity.ActivityOID + "','0','0','0')"; try { SqlCommand cmd = new SqlCommand(insert, connection); cmd.ExecuteNonQuery(); string selectIdentity = "SELECT IDENT_CURRENT ('" + DB.TablePrefix + "TREATMENTS') FROM " + DB.TablePrefix + "TREATMENTS"; cmd = new SqlCommand(selectIdentity, connection); treatmentID = cmd.ExecuteScalar().ToString(); } catch (Exception e) { Utility.ExceptionHandling.DataAccessExceptionHandler.HandleException(e, false); } try { double cost = 0; if (activity.OmsCost.CostPerUnit != double.NaN) { cost = activity.OmsCost.CostPerUnit; if (cost <= 0) { cost = 1; } } else { cost = 1; } insert = "INSERT INTO " + DB.TablePrefix + "COSTS (TREATMENTID,COST_) VALUES ('" + treatmentID + "','" + cost.ToString() + "')"; SqlCommand cmd = new SqlCommand(insert, connection); cmd.ExecuteNonQuery(); } catch (Exception e) { Utility.ExceptionHandling.DataAccessExceptionHandler.HandleException(e, false); } OMSAssetConditionIndexStore oci = OMS.GetAssetConditionIndex(asset); foreach (OMSConsequenceStore consequence in activity.ConsequenceList) { try { OMSConditionIndexStore condition = oci.ConditionIndexes.Find(delegate(OMSConditionIndexStore ci) { return(ci.ConditionCategory == consequence.ConditionCategory); }); if (condition != null) { insert = "INSERT INTO " + DB.TablePrefix + "CONSEQUENCES (TREATMENTID,ATTRIBUTE_,CHANGE_) VALUES ('" + treatmentID + "','__" + condition.AttributeDE.Replace(" ", "").Replace("/", "") + "','" + consequence.GetDecisionEngineConsequence() + "')"; SqlCommand cmd = new SqlCommand(insert, connection); cmd.ExecuteNonQuery(); } } catch (Exception e) { Utility.ExceptionHandling.DataAccessExceptionHandler.HandleException(e, false); } } try { string conditionIndexCriteria = GetConditionIndexCriteria(activity.ConsequenceList); if (String.IsNullOrWhiteSpace(conditionIndexCriteria)) { conditionIndexCriteria = "[AssetType]=|" + asset + "|"; } else { conditionIndexCriteria = " AND [AssetType]=|" + asset + "|"; } insert = "INSERT INTO " + DB.TablePrefix + "FEASIBILITY (TREATMENTID,CRITERIA) VALUES ('" + treatmentID + "','" + conditionIndexCriteria + "')"; SqlCommand cmd = new SqlCommand(insert, connection); cmd.ExecuteNonQuery(); } catch (Exception e) { Utility.ExceptionHandling.DataAccessExceptionHandler.HandleException(e, false); } } }