/// <summary> /// Deletes specified <see cref="Node"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="nodeID">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, Guid nodeID) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); // Before we delete node, make sure to delete all the devices associated with this node. // All other node related items get deleted by cascade delete. database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Device WHERE NodeID = {0}", "nodeID"), DefaultTimeout, database.Guid(nodeID)); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Node WHERE ID = {0}", "nodeID"), DefaultTimeout, database.Guid(nodeID)); return("Node deleted successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Deletes <see cref="SecurityGroup"/> from <see cref="ApplicationRole"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="roleID">ID of <see cref="ApplicationRole"/> from which <see cref="SecurityGroup"/>s are being deleted.</param> /// <param name="groupsToBeDeleted">List of <see cref="SecurityGroup"/> IDs to be deleted.</param> /// <returns>string, for display use, indicating success.</returns> public static string RemoveGroups(AdoDataConnection database, Guid roleID, List <Guid> groupsToBeDeleted) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); foreach (Guid id in groupsToBeDeleted) { string groupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), DefaultTimeout, database.Guid(id)).ToNonNullString(); string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString(); string query = database.ParameterizedQueryString("DELETE FROM ApplicationRoleSecurityGroup WHERE ApplicationRoleID = {0} AND SecurityGroupID = {1}", "roleID", "groupID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id)); CommonFunctions.LogEvent(string.Format("Group \"{0}\" removed from role \"{1}\" by user \"{2}\".", UserInfo.SIDToAccountName(groupName), roleName, CommonFunctions.CurrentUser), 11); } return("Security groups deleted from role successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Deletes <see cref="UserAccount"/> from <see cref="ApplicationRole"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="roleID">ID of <see cref="ApplicationRole"/> from which <see cref="UserAccount"/>s are being deleted.</param> /// <param name="usersToBeDeleted">List of <see cref="UserAccount"/> IDs to be deleted.</param> /// <returns>string, for display use, indicating success.</returns> public static string RemoveUsers(AdoDataConnection database, Guid roleID, List <Guid> usersToBeDeleted) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); foreach (Guid id in usersToBeDeleted) { string userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(id)).ToNonNullString(); string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString(); string query = database.ParameterizedQueryString("DELETE FROM ApplicationRoleUserAccount WHERE ApplicationRoleID = {0} AND UserAccountID = {1}", "roleID", "userID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id)); CommonFunctions.LogEvent($"User \"{UserInfo.SIDToAccountName(userName)}\" removed from role \"{roleName}\" by user \"{CommonFunctions.CurrentUser}\".", 5); } return("User accounts deleted from role successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Deletes <see cref="UserAccount"/> from <see cref="SecurityGroup"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="groupID">ID of <see cref="SecurityGroup"/> from which <see cref="UserAccount"/>s are being deleted.</param> /// <param name="usersToBeDeleted">List of <see cref="UserAccount"/> IDs to be deleted.</param> /// <returns>string, for display use, indicating success.</returns> public static string RemoveUsers(AdoDataConnection database, Guid groupID, List <Guid> usersToBeDeleted) { bool createdConnection = false; string query; string userName; string securityGroupName; try { createdConnection = CreateConnection(ref database); foreach (Guid id in usersToBeDeleted) { userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userID"), database.Guid(id)).ToNonNullString(); securityGroupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), database.Guid(groupID)).ToNonNullString(); query = database.ParameterizedQueryString("DELETE FROM SecurityGroupUserAccount WHERE SecurityGroupID = {0} AND UserAccountID = {1}", "groupID", "userID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(groupID), database.Guid(id)); CommonFunctions.LogEvent(string.Format("User \"{0}\" successfully removed from security group \"{1}\" by user \"{2}\".", UserInfo.SIDToAccountName(userName), UserInfo.SIDToAccountName(securityGroupName), CommonFunctions.CurrentUser), 9); } return("User accounts deleted from group successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Adds <see cref="SecurityGroup"/> to <see cref="ApplicationRole"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="roleID">ID of <see cref="ApplicationRole"/> to which <see cref="SecurityGroup"/>s are being added.</param> /// <param name="groupsToBeAdded">List of <see cref="SecurityGroup"/> IDs to be added.</param> /// <returns>string, for display use, indicating success.</returns> public static string AddGroups(AdoDataConnection database, Guid roleID, List <Guid> groupsToBeAdded) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); foreach (Guid id in groupsToBeAdded) { string groupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), DefaultTimeout, database.Guid(id)).ToNonNullString(); string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString(); string query = database.ParameterizedQueryString("INSERT INTO ApplicationRoleSecurityGroup (ApplicationRoleID, SecurityGroupID) Values ({0}, {1})", "roleID", "groupID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id)); CommonFunctions.LogEvent($"Group \"{UserInfo.SIDToAccountName(groupName)}\" added to role \"{roleName}\" by user \"{CommonFunctions.CurrentUser}\".", 10); } return("Security groups added to role successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Deletes specified <see cref="SecurityGroup"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="securityGroupID">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, Guid securityGroupID) { bool createdConnection = false; string securityGroupName; try { createdConnection = CreateConnection(ref database); // Get the name of the security group to be deleted securityGroupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(securityGroupID)).ToNonNullString(); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); // Delete the security group from the database database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), DefaultTimeout, database.Guid(securityGroupID)); // Write to the event log CommonFunctions.LogEvent(string.Format("Security group \"{0}\" deleted successfully by user \"{1}\".", UserInfo.SIDToAccountName(securityGroupName), CommonFunctions.CurrentUser), 13); return("Security group deleted successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Deletes specified <see cref="OutputStreamDevice"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamID">ID of the output stream to filter data.</param> /// <param name="outputStreamDeviceAcronym">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, int outputStreamID, string outputStreamDeviceAcronym) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevice WHERE AdapterID = {0} AND Acronym = {1}", "outputStreamID", "outputStreamDeviceAcronym"), DefaultTimeout, outputStreamID, outputStreamDeviceAcronym); try { database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE From OutputStreamMeasurement WHERE AdapterID = {0} AND SignalReference LIKE {1}", "outputStreamID", "outputStreamDeviceAcronym"), DefaultTimeout, outputStreamID, "%" + outputStreamDeviceAcronym + "%"); } catch (Exception ex) { throw new InvalidOperationException("Failed to delete measurements associated with output stream device.", ex); } return("OutputStreamDevice deleted successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="OutputStreamDevice"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDevice">Information about <see cref="OutputStreamDevice"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, OutputStreamDevice outputStreamDevice) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (outputStreamDevice.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO OutputStreamDevice (NodeID, AdapterID, IDCode, Acronym, BpaAcronym, Name, " + "PhasorDataFormat, FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn)" + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15})", "nodeID", "adapterID", "idCode", "acronym", "bpaAcronym", "name", "phasorDataFormat", "frequencyDataFormat", "analogDataFormat", "coordinateFormat", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDevice.AdapterID, outputStreamDevice.IDCode, outputStreamDevice.Acronym, outputStreamDevice.BpaAcronym.ToNotNull(), outputStreamDevice.Name, outputStreamDevice.PhasorDataFormat.ToNotNull(), outputStreamDevice.FrequencyDataFormat.ToNotNull(), outputStreamDevice.AnalogDataFormat.ToNotNull(), outputStreamDevice.CoordinateFormat.ToNotNull(), outputStreamDevice.LoadOrder, database.Bool(outputStreamDevice.Enabled), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { OutputStreamDevice originalDevice = GetOutputStreamDevice(database, "WHERE ID = " + outputStreamDevice.ID); query = database.ParameterizedQueryString("UPDATE OutputStreamDevice SET NodeID = {0}, AdapterID = {1}, IDCode = {2}, Acronym = {3}, BpaAcronym = {4}, " + "Name = {5}, PhasorDataFormat = {6}, FrequencyDataFormat = {7}, AnalogDataFormat = {8}, CoordinateFormat = {9}, LoadOrder = {10}, Enabled = {11}, " + " UpdatedBy = {12}, UpdatedOn = {13} WHERE ID = {14}", "nodeID", "adapterID", "idCode", "acronym", "bpaAcronym", "name", "phasorDataFormat", "frequencyDataFormat", "analogDataFormat", "coordinateFormat", "loadOrder", "enabled", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(outputStreamDevice.NodeID), outputStreamDevice.AdapterID, outputStreamDevice.IDCode, outputStreamDevice.Acronym, outputStreamDevice.BpaAcronym.ToNotNull(), outputStreamDevice.Name, outputStreamDevice.PhasorDataFormat.ToNotNull(), outputStreamDevice.FrequencyDataFormat.ToNotNull(), outputStreamDevice.AnalogDataFormat.ToNotNull(), outputStreamDevice.CoordinateFormat.ToNotNull(), outputStreamDevice.LoadOrder, database.Bool(outputStreamDevice.Enabled), CommonFunctions.CurrentUser, database.UtcNow, outputStreamDevice.ID); if (originalDevice != null && originalDevice.Acronym != outputStreamDevice.Acronym) { IList <int> keys = OutputStreamMeasurement.LoadKeys(database, originalDevice.AdapterID); foreach (OutputStreamMeasurement measurement in OutputStreamMeasurement.Load(database, keys)) { measurement.SignalReference = measurement.SignalReference.Replace(originalDevice.Acronym + "-", outputStreamDevice.Acronym + "-"); OutputStreamMeasurement.Save(database, measurement); } } } return("OutputStreamDevice information saved successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Adds <see cref="UserAccount"/> to <see cref="ApplicationRole"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="roleID">ID of <see cref="ApplicationRole"/> to which <see cref="UserAccount"/>s are being added.</param> /// <param name="usersToBeAdded">List of <see cref="UserAccount"/> IDs to be added.</param> /// <returns>string, for display use, indicating success.</returns> public static string AddUsers(AdoDataConnection database, Guid roleID, List <Guid> usersToBeAdded) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); foreach (Guid id in usersToBeAdded) { string userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(id)).ToNonNullString(); string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString(); string query = database.ParameterizedQueryString("INSERT INTO ApplicationRoleUserAccount (ApplicationRoleID, UserAccountID) VALUES ({0}, {1})", "roleID", "userID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id)); CommonFunctions.LogEvent(string.Format("User \"{0}\" added to role \"{1}\" by user \"{2}\".", UserInfo.SIDToAccountName(userName), roleName, CommonFunctions.CurrentUser), 4); } return("User accounts added to role successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="ApplicationRole"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="applicationRole">Information about <see cref="ApplicationRole"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, ApplicationRole applicationRole) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (applicationRole.ID == null || applicationRole.ID == Guid.Empty) { query = database.ParameterizedQueryString("INSERT INTO ApplicationRole (Name, Description, NodeID, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6})", "name", "description", "nodeID", "updatedBy", "updatedBy", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, applicationRole.Name, applicationRole.Description.ToNotNull(), database.CurrentNodeID(), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE ApplicationRole SET Name = {0}, Description = {1}, NodeID = {2}, UpdatedBy = {3}, UpdatedOn = {4} WHERE ID = {5}", "name", "description", "nodeID", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, applicationRole.Name, applicationRole.Description.ToNotNull(), applicationRole.NodeID, CommonFunctions.CurrentUser, database.UtcNow, database.Guid(applicationRole.ID)); } return("Application role information saved successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="Adapter"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="adapter">Information about <see cref="Adapter"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, Adapter adapter) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string tableName; if (adapter.Type == AdapterType.Action) { tableName = "CustomActionAdapter"; } else if (adapter.Type == AdapterType.Filter) { tableName = "CustomFilterAdapter"; } else if (adapter.Type == AdapterType.Input) { tableName = "CustomInputAdapter"; } else { tableName = "CustomOutputAdapter"; } if (adapter.ID == 0) { database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("INSERT INTO " + tableName + " (NodeID, AdapterName, AssemblyName, TypeName, ConnectionString, LoadOrder, " + "Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})", "nodeID", "adapterName", "assemblyName", "typeName", "connectionString", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn"), DefaultTimeout, (adapter.NodeID != Guid.Empty) ? database.Guid(adapter.NodeID) : database.CurrentNodeID(), adapter.AdapterName, adapter.AssemblyName, adapter.TypeName, adapter.ConnectionString.ToNotNull(), adapter.LoadOrder, database.Bool(adapter.Enabled), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE " + tableName + " SET NodeID = {0}, AdapterName = {1}, AssemblyName = {2}, " + "TypeName = {3}, ConnectionString = {4}, LoadOrder = {5}, Enabled = {6}, UpdatedBy = {7}, " + "UpdatedOn = {8} WHERE ID = {9}", "nodeID", "adapterName", "assemblyName", "typeName", "connectionString", "loadOrder", "enabled", "updatedBy", "updatedOn", "id"), DefaultTimeout, (adapter.NodeID != Guid.Empty) ? database.Guid(adapter.NodeID) : database.CurrentNodeID(), adapter.AdapterName, adapter.AssemblyName, adapter.TypeName, adapter.ConnectionString.ToNotNull(), adapter.LoadOrder, database.Bool(adapter.Enabled), CommonFunctions.CurrentUser, database.UtcNow, adapter.ID); } return("Adapter information saved successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="Subscriber"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriber">Information about <see cref="Subscriber"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, Subscriber subscriber) { bool createdConnection = false; SslPolicyErrors validPolicyErrors; X509ChainStatusFlags validChainFlags; string query; try { createdConnection = CreateConnection(ref database); validPolicyErrors = (subscriber.ValidPolicyErrors ?? SslPolicyErrors.None) | (subscriber.RemoteCertificateIsSelfSigned ? SslPolicyErrors.RemoteCertificateChainErrors : SslPolicyErrors.None); validChainFlags = (subscriber.ValidChainFlags ?? X509ChainStatusFlags.NoError) | (subscriber.RemoteCertificateIsSelfSigned ? X509ChainStatusFlags.UntrustedRoot : X509ChainStatusFlags.NoError); if (subscriber.ID == Guid.Empty) { query = database.ParameterizedQueryString("INSERT INTO Subscriber (NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile, ValidPolicyErrors, ValidChainFlags, " + "AccessControlFilter, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " + "{13}, {14})", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(), subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(), validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE Subscriber SET NodeID = {0}, Acronym = {1}, Name = {2}, SharedSecret = {3}, AuthKey = {4}, ValidIPAddresses = {5}, RemoteCertificateFile = {6}, " + "ValidPolicyErrors = {7}, ValidChainFlags = {8}, AccessControlFilter = {9}, Enabled = {10}, UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter", "enabled", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriber.NodeID), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(), subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(), validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow, database.Guid(subscriber.ID)); } try { CommonFunctions.SendCommandToService("ReloadConfig"); } catch (Exception ex) { return("Subscriber information saved successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message); } return("Subscriber information saved successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="OutputStreamDeviceAnalog"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDeviceAnalog">Information about <see cref="OutputStreamDeviceAnalog"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, OutputStreamDeviceAnalog outputStreamDeviceAnalog) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (outputStreamDeviceAnalog.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO OutputStreamDeviceAnalog (NodeID, OutputStreamDeviceID, Label, Type, ScalingValue, LoadOrder, " + "UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})", "nodeID", "outputStreamDeviceID", "label", "type", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn"); // TypeName, "typeName", database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDeviceAnalog.OutputStreamDeviceID, outputStreamDeviceAnalog.Label, outputStreamDeviceAnalog.Type, outputStreamDeviceAnalog.ScalingValue, outputStreamDeviceAnalog.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); //outputStreamDeviceAnalog.TypeName, } else { query = database.ParameterizedQueryString("UPDATE OutputStreamDeviceAnalog SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, Type = {3}, " + "ScalingValue = {4}, LoadOrder = {5}, UpdatedBy = {6}, UpdatedOn = {7} WHERE ID = {8}", "nodeID", "outputStreamDeviceID", "label", "type", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "id"); // TypeName= {6}, "typeName", database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamDeviceAnalog.NodeID, outputStreamDeviceAnalog.OutputStreamDeviceID, outputStreamDeviceAnalog.Label, outputStreamDeviceAnalog.Type, outputStreamDeviceAnalog.ScalingValue, outputStreamDeviceAnalog.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, outputStreamDeviceAnalog.ID); } // OutputStreamDeviceAnalog.TypeName, return("OutputStreamDeviceAnalog information saved successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Removed measurement groups from <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to which measurement groups to be removed.</param> /// <param name="measurementGroupsToBeRemoved">List of <see cref="MeasurementGroup"/> IDs to be removed.</param> /// <returns>string, indicating success for UI display.</returns> public static string RemoveMeasurementGroups(AdoDataConnection database, Guid subscriberID, List <int> measurementGroupsToBeRemoved) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); foreach (int id in measurementGroupsToBeRemoved) { query = database.ParameterizedQueryString("DELETE FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND MeasurementGroupID = {1}", "subscriberID", "measurementGroupID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriberID), id); } return("Measurement groups removed from allowed measurement groups list for subscriber successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves a <see cref="Dictionary{T1,T2}"/> style list of <see cref="MeasurementGroup"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of <see cref="MeasurementGroup"/>s defined in the database.</returns> public static Dictionary <int, string> GetLookupList(AdoDataConnection database, bool isOptional = true) { Dictionary <int, string> measurementGroupList; DataTable measurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); measurementGroupList = new Dictionary <int, string>(); if (isOptional) { measurementGroupList.Add(0, "Select MeasurementGroup"); } query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE NodeID = {0} ORDER BY SourceIndex", "nodeID"); measurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in measurementGroupTable.Rows) { measurementGroupList[row.ConvertField <int>("ID")] = row.Field <string>("Name"); } return(measurementGroupList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Measurement"/> assigned to <see cref="MeasurementGroup"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="measurementGroupId">ID of the <see cref="MeasurementGroup"/> to filter data.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing SignalID and PointTag of <see cref="Measurement"/>s assigned to <see cref="MeasurementGroup"/>.</returns> public static Dictionary <Guid, string> GetCurrentMeasurements(AdoDataConnection database, int measurementGroupId) { Dictionary <Guid, string> currentMeasurements; DataTable currentMeasurementTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); currentMeasurements = new Dictionary <Guid, string>(); query = database.ParameterizedQueryString("SELECT * FROM MeasurementGroupMeasDetail WHERE MeasurementGroupID = {0} ORDER BY PointID", "measurementGroupID"); currentMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, measurementGroupId); foreach (DataRow row in currentMeasurementTable.Rows) { currentMeasurements[database.Guid(row, "SignalID")] = row.Field <string>("PointTag"); } return(currentMeasurements); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Method to add <see cref="Measurement"/>s to <see cref="MeasurementGroup"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="measurementGroupID">ID of the <see cref="MeasurementGroup"/> to add <see cref="Measurement"/> to.</param> /// <param name="measurementsToBeAdded">List of <see cref="Measurement"/> signal ids to be added.</param> /// <returns>string, indicating success for UI display.</returns> public static string AddMeasurements(AdoDataConnection database, int measurementGroupID, List <Guid> measurementsToBeAdded) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); foreach (Guid id in measurementsToBeAdded) { query = database.ParameterizedQueryString("INSERT INTO MeasurementGroupMeasurement (NodeID, MeasurementGroupID, SignalID) VALUES ({0}, {1}, {2})", "nodeID", "measurementGroupID", "signalID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroupID, database.Guid(id)); } return("Measurements added to group successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves collection of <see cref="UserAccount"/>s currently NOT assinged to security group. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="groupID">ID of <see cref="SecurityGroup"/> to filter users.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of <see cref="UserAccount"/>s currently NOT assigned to <see cref="SecurityGroup"/>.</returns> public static Dictionary <Guid, string> GetPossibleUsers(AdoDataConnection database, Guid groupID) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary <Guid, string> possibleGroupUsers = new Dictionary <Guid, string>(); string query = database.ParameterizedQueryString("SELECT ID, Name FROM UserAccount WHERE ID NOT IN (SELECT UserAccountID FROM SecurityGroupUserAccount WHERE SecurityGroupID = {0}) ORDER BY Name", "groupID"); DataTable possibleUsersTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(groupID)); foreach (DataRow row in possibleUsersTable.Rows) { possibleGroupUsers[database.Guid(row, "ID")] = UserInfo.SIDToAccountName(row.Field <string>("Name")); } return(possibleGroupUsers); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Deletes specified <see cref="Subscriber"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="id">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, Guid id) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Subscriber WHERE ID = {0}", "id"), DefaultTimeout, database.Guid(id)); try { CommonFunctions.SendCommandToService("ReloadConfig"); } catch (Exception ex) { return("Subscriber deleted successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message); } return("Subscriber deleted successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> neither allowed nor denied for <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns> public static Dictionary <int, string> GetAvailableMeasurementGroups(AdoDataConnection database, Guid subscriberID) { Dictionary <int, string> availableMeasurementGroups; DataTable availableMeasurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); availableMeasurementGroups = new Dictionary <int, string>(); query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE ID NOT IN (SELECT MeasurementGroupID FROM SubscriberMeasurementGroup WHERE SubscriberID = {0}) ORDER BY Name", "subscriberID"); availableMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID)); foreach (DataRow row in availableMeasurementGroupTable.Rows) { availableMeasurementGroups[row.ConvertField <int>("ID")] = row.Field <string>("Name"); } return(availableMeasurementGroups); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Subscriber"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of subscribers defined in the database.</returns> public static Dictionary <Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false) { Dictionary <Guid, string> subscriberList; DataTable subscriberTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); subscriberList = new Dictionary <Guid, string>(); if (isOptional) { subscriberList.Add(Guid.Empty, "Select Subscriber"); } query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Subscriber WHERE Enabled = {0} AND NodeID = {1} ORDER BY Name", "enabled", "nodeID"); subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID()); foreach (DataRow row in subscriberTable.Rows) { subscriberList[database.Guid(row, "ID")] = row.Field <string>("Acronym"); } return(subscriberList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> denied for <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns> public static Dictionary <int, string> GetDeniedMeasurementGroups(AdoDataConnection database, Guid subscriberID) { Dictionary <int, string> deniedMeasurementGroups; DataTable deniedMeasurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); deniedMeasurementGroups = new Dictionary <int, string>(); query = database.ParameterizedQueryString("SELECT MeasurementGroupID, MeasurementGroupName FROM SubscriberMeasGroupDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY MeasurementGroupName", "subscriberID", "allowed"); deniedMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(false)); foreach (DataRow row in deniedMeasurementGroupTable.Rows) { deniedMeasurementGroups[row.ConvertField <int>("MeasurementGroupID")] = row.Field <string>("MeasurementGroupName"); } return(deniedMeasurementGroups); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="Measurement"/> allowed for <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of SignalID and PointTag of <see cref="Measurement"/>.</returns> public static Dictionary <Guid, string> GetAllowedMeasurements(AdoDataConnection database, Guid subscriberID) { Dictionary <Guid, string> allowedMeasurements; DataTable allowedMeasurementTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); allowedMeasurements = new Dictionary <Guid, string>(); query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM SubscriberMeasurementDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY PointTag", "subscriberID", "allowed"); allowedMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(true)); foreach (DataRow row in allowedMeasurementTable.Rows) { allowedMeasurements[database.Guid(row, "SignalID")] = row.Field <string>("PointTag"); } return(allowedMeasurements); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Method to remove <see cref="Measurement"/>s from <see cref="MeasurementGroup"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="measurementGroupID">ID of the <see cref="MeasurementGroup"/> to remove <see cref="Measurement"/> from.</param> /// <param name="measurementsToBeRemoved">List of <see cref="Measurement"/> signal ids to be removed.</param> /// <returns>string, indicating success for UI display.</returns> public static string RemoveMeasurements(AdoDataConnection database, int measurementGroupID, List <Guid> measurementsToBeRemoved) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); foreach (Guid id in measurementsToBeRemoved) { query = database.ParameterizedQueryString("DELETE FROM MeasurementGroupMeasurement WHERE MeasurementGroupID = {0} AND SignalID = {1}", "measurementGroupID", "signalID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurementGroupID, database.Guid(id)); } return("Measurements deleted from group successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Deletes specified <see cref="CalculatedMeasurement"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="calculatedMeasurementID">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, int calculatedMeasurementID) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM CalculatedMeasurement WHERE ID = {0}", "calculatedMeasurementID"), DefaultTimeout, calculatedMeasurementID); CommonFunctions.SendCommandToService("ReloadConfig"); return("Calculated measurement deleted successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves collection of <see cref="SecurityGroup"/>s assigned to <see cref="ApplicationRole"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="roleID">ID of the <see cref="ApplicationRole"/> to search for.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of <see cref="SecurityGroup"/>.</returns> public static Dictionary <Guid, string> GetCurrentGroups(AdoDataConnection database, Guid roleID) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary <Guid, string> currentGroups = new Dictionary <Guid, string>(); string query = database.ParameterizedQueryString("SELECT * FROM AppRoleSecurityGroupDetail WHERE ApplicationRoleID = {0} ORDER BY SecurityGroupName", "applicationRoleID"); DataTable currentGroupsTable = database.Connection.RetrieveData(database.AdapterType, query, database.Guid(roleID)); foreach (DataRow row in currentGroupsTable.Rows) { currentGroups[database.Guid(row, "SecurityGroupID")] = UserInfo.SIDToAccountName(row.Field <string>("SecurityGroupName")); } return(currentGroups); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
// Loads the data set used to determine effective permissions private void LoadPermissionsDataSet() { Dictionary <string, string> dataTableDefinitions = new Dictionary <string, string>() { { "ActiveMeasurement", "ActiveMeasurements" }, { "Subscriber", "Subscribers" }, { "SubscriberMeasurement", "SubscriberMeasurements" }, { "SubscriberMeasurementGroup", "SubscriberMeasurementGroups" }, { "MeasurementGroup", "MeasurementGroups" }, { "MeasurementGroupMeasurement", "MeasurementGroupMeasurements" } }; DataTable dataTable; string queryFormat; string parameterizedQuery; m_subscriberPermissionsDataSet = new DataSet(); using (AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory)) { foreach (KeyValuePair <string, string> definition in dataTableDefinitions) { queryFormat = string.Format("SELECT * FROM {0} WHERE NodeID = {{0}}", definition.Key); parameterizedQuery = database.ParameterizedQueryString(queryFormat, "nodeID"); dataTable = database.Connection.RetrieveData(database.AdapterType, parameterizedQuery, DataExtensions.DefaultTimeoutDuration, database.CurrentNodeID()); dataTable.TableName = definition.Value; dataTable.DataSet.Tables.Remove(dataTable); m_subscriberPermissionsDataSet.Tables.Add(dataTable); } } UpdateEffectivePermissions(); }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Node"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of nodes defined in the database.</returns> public static Dictionary <Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary <Guid, string> nodeList = new Dictionary <Guid, string>(); if (isOptional) { nodeList.Add(Guid.Empty, "Select Node"); } string query = database.ParameterizedQueryString("SELECT ID, Name FROM Node WHERE Enabled = {0} ORDER BY LoadOrder", "enabled"); DataTable nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true)); foreach (DataRow row in nodeTable.Rows) { nodeList[database.Guid(row, "ID")] = row.Field <string>("Name"); } return(nodeList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="OutputStreamDevice"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamID">ID of the output stream to filter data.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of OutputStreamDevice defined in the database.</returns> public static Dictionary <int, string> GetLookupList(AdoDataConnection database, int outputStreamID, bool isOptional = false) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary <int, string> OutputStreamDeviceList = new Dictionary <int, string>(); if (isOptional) { OutputStreamDeviceList.Add(0, "Select OutputStreamDevice"); } string query = database.ParameterizedQueryString("SELECT ID, Name FROM OutputStreamDevice WHERE AdapterID = {0} ORDER BY LoadOrder", "adapterID"); DataTable OutputStreamDeviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, outputStreamID); foreach (DataRow row in OutputStreamDeviceTable.Rows) { OutputStreamDeviceList[row.ConvertField <int>("ID")] = row.Field <string>("Name"); } return(OutputStreamDeviceList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Loads <see cref="Historian"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="keys">Keys to load, if any.</param> /// <returns>Collection of <see cref="Historian"/>.</returns> public static ObservableCollection <Historian> Load(AdoDataConnection database, IList <int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query; string commaSeparatedKeys; Historian[] historianList = null; DataTable historianTable; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => key.ToString()).Aggregate((str1, str2) => str1 + "," + str2); query = database.ParameterizedQueryString(string.Format("SELECT NodeID, ID, Acronym, Name, AssemblyName, TypeName, " + "ConnectionString, IsLocal, Description, LoadOrder, Enabled, MeasurementReportingInterval, NodeName" + " FROM HistorianDetail WHERE NodeID = {{0}} AND ID IN ({0})", commaSeparatedKeys), "nodeID"); historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); historianList = new Historian[historianTable.Rows.Count]; foreach (DataRow row in historianTable.Rows) { id = row.ConvertField <int>("ID"); historianList[keys.IndexOf(id)] = new Historian() { NodeID = database.Guid(row, "NodeID"), ID = id, Acronym = row.Field <string>("Acronym"), Name = row.Field <string>("Name"), AssemblyName = row.Field <string>("AssemblyName"), TypeName = row.Field <string>("TypeName"), ConnectionString = row.Field <string>("ConnectionString"), IsLocal = Convert.ToBoolean(row.Field <object>("IsLocal")), Description = row.Field <string>("Description"), LoadOrder = row.ConvertField <int>("LoadOrder"), Enabled = Convert.ToBoolean(row.Field <object>("Enabled")), MeasurementReportingInterval = row.ConvertField <int>("MeasurementReportingInterval"), m_nodeName = row.Field <string>("NodeName") }; } } return(new ObservableCollection <Historian>(historianList ?? new Historian[0])); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Deletes specified <see cref="OutputStreamDevicePhasor"/> record and its associated measurements from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDevicePhasorID">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, int outputStreamDevicePhasorID) { bool createdConnection = false; int adapterID; int outputStreamDeviceID; int deletedSignalReferenceIndex; int presentDevicePhasorCount; string angleSignalReference; string angleSignalReferenceBase; string magnitudeSignalReference; string magnitudeSignalReferenceBase; string previousAngleSignalReference; string previousMagnitudeSignalReference; string nextAngleSignalReference = string.Empty; string nextMagnitudeSignalReference = string.Empty; string lastAffectedMeasurementsMessage = string.Empty; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); GetDeleteMeasurementDetails(database, outputStreamDevicePhasorID, out angleSignalReference, out magnitudeSignalReference, out adapterID, out outputStreamDeviceID); // Delete angle/magnitude of measurements if they exist database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamMeasurement WHERE SignalReference = {0} AND AdapterID = {1}", "signalReference", "adapterID"), DefaultTimeout, angleSignalReference, adapterID); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamMeasurement WHERE SignalReference = {0} AND AdapterID = {1}", "signalReference", "adapterID"), DefaultTimeout, magnitudeSignalReference, adapterID); presentDevicePhasorCount = Convert.ToInt32(database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM OutputStreamDevicePhasor WHERE OutputStreamDeviceID = {0}", "outputStreamDeviceID"), DefaultTimeout, outputStreamDeviceID)); // Using signal reference angle/mag deleted build the next signal reference (increment by 1) int.TryParse(Regex.Match(magnitudeSignalReference, @"\d+$").Value, out deletedSignalReferenceIndex); angleSignalReferenceBase = Regex.Replace(angleSignalReference, @"\d+$", ""); magnitudeSignalReferenceBase = Regex.Replace(magnitudeSignalReference, @"\d+$", ""); for (int i = deletedSignalReferenceIndex; i < presentDevicePhasorCount; i++) { // We will be modifying the measurements with signal reference index i+1 to have signal refrence index i. previousAngleSignalReference = string.Format("{0}{1}", angleSignalReferenceBase, i); nextAngleSignalReference = string.Format("{0}{1}", angleSignalReferenceBase, i + 1); previousMagnitudeSignalReference = string.Format("{0}{1}", magnitudeSignalReferenceBase, i); nextMagnitudeSignalReference = string.Format("{0}{1}", magnitudeSignalReferenceBase, i + 1); // For angle... // Obtain details of measurements after the deleted measurements, then modify the signal reference (decrement by 1) and put it back OutputStreamMeasurement outputStreamMeasurement = GetOutputMeasurementDetails(database, nextAngleSignalReference, adapterID); outputStreamMeasurement.SignalReference = previousAngleSignalReference; OutputStreamMeasurement.Save(database, outputStreamMeasurement); // For magnitude... outputStreamMeasurement = GetOutputMeasurementDetails(database, nextMagnitudeSignalReference, adapterID); outputStreamMeasurement.SignalReference = previousMagnitudeSignalReference; OutputStreamMeasurement.Save(database, outputStreamMeasurement); } database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevicePhasor WHERE ID = {0}", "outputStreamDevicePhasorID"), DefaultTimeout, outputStreamDevicePhasorID); return "OutputStreamDevicePhasor deleted successfully"; } catch (Exception ex) { if (!string.IsNullOrEmpty(nextMagnitudeSignalReference)) lastAffectedMeasurementsMessage = string.Format("{0}(Last affected measurements: {1} {2})", Environment.NewLine, nextMagnitudeSignalReference, nextAngleSignalReference); CommonFunctions.LogException(database, "OutputStreamDevicePhasor.Delete", ex); MessageBoxResult dialogResult = MessageBox.Show(string.Format("Could not delete or modify measurements.{0}Do you still wish to delete this Phasor?{1}", Environment.NewLine, lastAffectedMeasurementsMessage), "", MessageBoxButton.YesNo); if (dialogResult == MessageBoxResult.Yes) { database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevicePhasor WHERE ID = {0}", "outputStreamDevicePhasorID"), DefaultTimeout, outputStreamDevicePhasorID); return "OutputStreamDevicePhasor deleted successfully but failed to modify all measurements "; } else { Exception exception = ex.InnerException ?? ex; return string.Format("Delete OutputStreamDevicePhasor was unsuccessful: {0}", exception.Message); } } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> neither allowed nor denied for <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns> public static Dictionary<int, string> GetAvailableMeasurementGroups(AdoDataConnection database, Guid subscriberID) { Dictionary<int, string> availableMeasurementGroups; DataTable availableMeasurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); availableMeasurementGroups = new Dictionary<int, string>(); query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE ID NOT IN (SELECT MeasurementGroupID FROM SubscriberMeasurementGroup WHERE SubscriberID = {0}) ORDER BY Name", "subscriberID"); availableMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID)); foreach (DataRow row in availableMeasurementGroupTable.Rows) availableMeasurementGroups[row.ConvertField<int>("ID")] = row.Field<string>("Name"); return availableMeasurementGroups; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Deletes specified <see cref="Phasor"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="phasorID">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, int phasorID) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); Phasor phasor = GetPhasor(database, "WHERE ID = " + phasorID); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Phasor WHERE ID = {0}", "phasorID"), DefaultTimeout, phasorID); if (phasor != null) { try { database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Measurement WHERE DeviceID = {0} AND PhasorSourceIndex = {1}", "deviceID", "phasorSourceIndex"), DefaultTimeout, phasor.DeviceID, phasor.SourceIndex); } catch (Exception ex) { CommonFunctions.LogException(database, "Phasor.Delete", ex); throw new Exception("Phasor deleted successfully but failed to delete measurements. " + Environment.NewLine + ex.Message); } } return "Phasor deleted successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Phasor"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="deviceID">ID of the <see cref="Device"/> to filter data.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Label of phasors defined in the database.</returns> public static Dictionary<int, string> GetLookupList(AdoDataConnection database, int deviceID, bool isOptional = true) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary<int, string> phasorList = new Dictionary<int, string>(); if (isOptional) phasorList.Add(0, "Select Phasor"); if (deviceID == 0) return phasorList; string query = database.ParameterizedQueryString("SELECT ID, Label FROM Phasor WHERE DeviceID = {0} ORDER BY SourceIndex", "deviceID"); DataTable phasorTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, deviceID); foreach (DataRow row in phasorTable.Rows) phasorList[row.ConvertField<int>("ID")] = row.Field<string>("Label"); return phasorList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Adds measurement groups to <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to which measurements to be added.</param> /// <param name="measurementGroupsToBeAdded">List of <see cref="MeasurementGroup"/> IDs to be added.</param> /// <param name="allowed">boolean flag to indicate if measurement groups are allowed or denied.</param> /// <returns>string, indicating success for UI display.</returns> public static string AddMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeAdded, bool allowed) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); foreach (int id in measurementGroupsToBeAdded) { query = database.ParameterizedQueryString("INSERT INTO SubscriberMeasurementGroup (NodeID, SubscriberID, MeasurementGroupID, Allowed, UpdatedOn, " + "UpdatedBy, CreatedOn, CreatedBy) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", "nodeID", "subscriberID", "measurementGroupID", "allowed", "updatedOn", "updatedBy", "createdOn", "createdBy"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), database.Guid(subscriberID), id, database.Bool(allowed), database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser); } if (allowed) return "Measurement groups added to allowed measurement groups list for subscriber successfully"; else return "Measurement groups added to denied measurement groups list for subscriber successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="OutputStream"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of <see cref="OutputStream"/>s defined in the database.</returns> public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = true) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary<int, string> osList = new Dictionary<int, string>(); DataTable results = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Name FROM OutputStream WHERE NodeID = {0} ORDER BY Name", "nodeID"), DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in results.Rows) osList[row.ConvertField<int>("ID")] = row.Field<string>("Name"); return osList; } finally { if (createdConnection && database != null) database.Dispose(); } }
private static void PhasorDataSourceValidation(AdoDataConnection database, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException) { // Make sure setting exists to allow user to by-pass phasor data source validation at startup ConfigurationFile configFile = ConfigurationFile.Current; CategorizedSettingsElementCollection settings = configFile.Settings["systemSettings"]; settings.Add("ProcessPhasorDataSourceValidation", true, "Determines if the phasor data source validation should be processed at startup"); // See if this node should process phasor source validation if (!settings["ProcessPhasorDataSourceValidation"].ValueAsBoolean()) return; Dictionary<string, string> args = new Dictionary<string, string>(); bool skipOptimization = false, renameAllPointTags = false; string arg, acronym; if (!string.IsNullOrEmpty(arguments)) args = arguments.ParseKeyValuePairs(); if (args.TryGetValue("skipOptimization", out arg)) skipOptimization = arg.ParseBoolean(); if (args.TryGetValue("renameAllPointTags", out arg)) renameAllPointTags = arg.ParseBoolean(); CreateDefaultNode(database, nodeIDQueryString, statusMessage, processException); LoadDefaultConfigurationEntity(database, statusMessage, processException); LoadDefaultInterconnection(database, statusMessage, processException); LoadDefaultProtocol(database, statusMessage, processException); LoadDefaultSignalType(database, statusMessage, processException); LoadDefaultStatistic(database, statusMessage, processException); EstablishDefaultMeasurementKeyCache(database, statusMessage, processException); statusMessage("Validating signal types..."); // Validate that the acronym for status flags is FLAG (it was STAT in prior versions) if (database.Connection.ExecuteScalar("SELECT Acronym FROM SignalType WHERE Suffix='SF'").ToNonNullString().ToUpper() == "STAT") database.Connection.ExecuteNonQuery("UPDATE SignalType SET Acronym='FLAG' WHERE Suffix='SF'"); // Validate that the calculation and statistic signal types are defined (they did not in initial release) if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE Acronym='CALC'")) == 0) database.Connection.ExecuteNonQuery("INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, LongAcronym, Source, EngineeringUnits) VALUES('Calculated Value', 'CALC', 'CV', 'CV', 'Calculated', 'PMU', '')"); if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE Acronym='STAT'")) == 0) database.Connection.ExecuteNonQuery("INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, LongAcronym, Source, EngineeringUnits) VALUES('Statistic', 'STAT', 'ST', 'ST', 'Statistic', 'Any', '')"); if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE Acronym='QUAL'")) == 0) database.Connection.ExecuteNonQuery("INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, LongAcronym, Source, EngineeringUnits) VALUES('Quality Flags', 'QUAL', 'QF', 'QF', 'QualityFlags', 'Frame', '')"); // Make sure values are defined for long acronyms (did not exist in prior versions) if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE LongAcronym='Undefined'")) > 0) { // Update abbreviations to better values for consistent custom point tag naming convention if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='ALOG'").ToNonNullString().ToUpper() == "A") database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='AV' WHERE Acronym='ALOG'"); if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='DIGI'").ToNonNullString().ToUpper() == "D") database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='DV' WHERE Acronym='DIGI'"); if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='CALC'").ToNonNullString().ToUpper() == "C") database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='CV' WHERE Acronym='CALC'"); if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='STAT'").ToNonNullString().ToUpper() == "P") database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='ST' WHERE Acronym='STAT'"); if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='QUAL'").ToNonNullString().ToUpper() == "Q") database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='QF' WHERE Acronym='QUAL'"); IEnumerable<DataRow> signalTypes = database.Connection.RetrieveData(database.AdapterType, "SELECT Name, Acronym FROM SignalType WHERE LongAcronym='Undefined'").AsEnumerable(); string longAcronym; foreach (DataRow row in signalTypes) { acronym = row.Field<string>("Acronym").ToUpperInvariant().Trim(); switch (acronym) { case "IPHM": longAcronym = "CurrentMagnitude"; break; case "IPHA": longAcronym = "CurrentAngle"; break; case "VPHM": longAcronym = "VoltageMagnitude"; break; case "VPHA": longAcronym = "VoltageAngle"; break; case "FREQ": longAcronym = "Frequency"; break; case "DFDT": longAcronym = "DfDt"; break; case "ALOG": longAcronym = "Analog"; break; case "FLAG": longAcronym = "StatusFlags"; break; case "DIGI": longAcronym = "Digital"; break; case "CALC": longAcronym = "Calculated"; break; case "STAT": longAcronym = "Statistic"; break; case "ALRM": longAcronym = "Alarm"; break; case "QUAL": longAcronym = "QualityFlags"; break; default: longAcronym = row.Field<string>("Name").Trim().RemoveWhiteSpace(); if (string.IsNullOrEmpty(longAcronym)) longAcronym = acronym.ToNonNullString("?"); break; } database.Connection.ExecuteNonQuery(string.Format("UPDATE SignalType SET LongAcronym='{0}' WHERE Acronym='{1}'", longAcronym, acronym)); } } statusMessage("Validating output stream device ID codes..."); // Validate all ID codes for output stream devices are not set their default value database.Connection.ExecuteNonQuery("UPDATE OutputStreamDevice SET IDCode = ID WHERE IDCode = 0"); statusMessage("Verifying statistics archive exists..."); // Validate that the statistics historian exists if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Historian WHERE Acronym='STAT' AND NodeID={0}", nodeIDQueryString))) == 0) database.Connection.ExecuteNonQuery(string.Format("INSERT INTO Historian(NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, IsLocal, Description, LoadOrder, Enabled) VALUES({0}, 'STAT', 'Statistics Archive', 'HistorianAdapters.dll', 'HistorianAdapters.LocalOutputAdapter', '', 1, 'Local historian used to archive system statistics', 9999, 1)", nodeIDQueryString)); // Make sure statistics path exists to hold historian files string statisticsPath = FilePath.GetAbsolutePath(FilePath.AddPathSuffix("Statistics")); if (!Directory.Exists(statisticsPath)) Directory.CreateDirectory(statisticsPath); // Make sure needed statistic historian configuration settings are properly defined settings = configFile.Settings["statMetadataFile"]; settings.Add("FileName", string.Format("Statistics{0}stat_dbase.dat", Path.DirectorySeparatorChar), "Name of the statistics meta-data file including its path."); settings.Add("LoadOnOpen", true, "True if file records are to be loaded in memory when opened; otherwise False - this defaults to True for the statistics meta-data file."); settings.Add("ReloadOnModify", false, "True if file records loaded in memory are to be re-loaded when file is modified on disk; otherwise False - this defaults to False for the statistics meta-data file."); settings["LoadOnOpen"].Update(true); settings["ReloadOnModify"].Update(false); settings = configFile.Settings["statStateFile"]; settings.Add("FileName", string.Format("Statistics{0}stat_startup.dat", Path.DirectorySeparatorChar), "Name of the statistics state file including its path."); settings.Add("AutoSaveInterval", 10000, "Interval in milliseconds at which the file records loaded in memory are to be saved automatically to disk. Use -1 to disable automatic saving - this defaults to 10,000 for the statistics state file."); settings.Add("LoadOnOpen", true, "True if file records are to be loaded in memory when opened; otherwise False - this defaults to True for the statistics state file."); settings.Add("SaveOnClose", true, "True if file records loaded in memory are to be saved to disk when file is closed; otherwise False - this defaults to True for the statistics state file."); settings.Add("ReloadOnModify", false, "True if file records loaded in memory are to be re-loaded when file is modified on disk; otherwise False - this defaults to False for the statistics state file."); settings["AutoSaveInterval"].Update(10000); settings["LoadOnOpen"].Update(true); settings["SaveOnClose"].Update(true); settings["ReloadOnModify"].Update(false); settings = configFile.Settings["statIntercomFile"]; settings.Add("FileName", string.Format("Statistics{0}scratch.dat", Path.DirectorySeparatorChar), "Name of the statistics intercom file including its path."); settings.Add("AutoSaveInterval", 10000, "Interval in milliseconds at which the file records loaded in memory are to be saved automatically to disk. Use -1 to disable automatic saving - this defaults to 10,000 for the statistics intercom file."); settings.Add("LoadOnOpen", true, "True if file records are to be loaded in memory when opened; otherwise False - this defaults to True for the statistics intercom file."); settings.Add("SaveOnClose", true, "True if file records loaded in memory are to be saved to disk when file is closed; otherwise False - this defaults to True for the statistics intercom file."); settings.Add("ReloadOnModify", false, "True if file records loaded in memory are to be re-loaded when file is modified on disk; otherwise False - this defaults to False for the statistics intercom file."); settings["AutoSaveInterval"].Update(1000); settings["LoadOnOpen"].Update(true); settings["SaveOnClose"].Update(true); settings["ReloadOnModify"].Update(false); settings = configFile.Settings["statArchiveFile"]; settings.Add("FileName", string.Format("Statistics{0}stat_archive.d", Path.DirectorySeparatorChar), "Name of the statistics working archive file including its path."); settings.Add("CacheWrites", true, "True if writes are to be cached for performance; otherwise False - this defaults to True for the statistics working archive file."); settings.Add("ConserveMemory", false, "True if attempts are to be made to conserve memory; otherwise False - this defaults to False for the statistics working archive file."); settings["CacheWrites"].Update(true); settings["ConserveMemory"].Update(false); settings = configFile.Settings["statMetadataService"]; settings.Add("Endpoints", "http.rest://localhost:6051/historian", "Semicolon delimited list of URIs where the web service can be accessed - this defaults to http.rest://localhost:6051/historian for the statistics meta-data service."); settings = configFile.Settings["statTimeSeriesDataService"]; settings.Add("Endpoints", "http.rest://localhost:6052/historian", "Semicolon delimited list of URIs where the web service can be accessed - this defaults to http.rest://localhost:6052/historian for the statistics time-series data service."); configFile.Save(); // Get the needed statistic related IDs int statHistorianID = Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT ID FROM Historian WHERE Acronym='STAT' AND NodeID={0}", nodeIDQueryString))); // Load the defined system statistics IEnumerable<DataRow> statistics = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM Statistic ORDER BY Source, SignalIndex").AsEnumerable(); // Filter statistics to device, input stream and output stream types IEnumerable<DataRow> deviceStatistics = statistics.Where(row => string.Compare(row.Field<string>("Source"), "Device", true) == 0).ToList(); IEnumerable<DataRow> inputStreamStatistics = statistics.Where(row => string.Compare(row.Field<string>("Source"), "InputStream", true) == 0).ToList(); // Define kinds of output signal that will designate a location in an output stream protocol frame - other non-mappable measurements will be removed from output stream measurements SignalKind[] validOutputSignalKinds = { SignalKind.Angle, SignalKind.Magnitude, SignalKind.Frequency, SignalKind.DfDt, SignalKind.Status, SignalKind.Analog, SignalKind.Digital, SignalKind.Quality }; HashSet<int> measurementIDsToDelete = new HashSet<int>(); SignalReference deviceSignalReference; string query, signalReference, pointTag, company, description, protocolIDs; int adapterID, deviceID, signalIndex; bool firstStatisticExisted; int? historianID; string[] trackedTables; ulong changes; try { // Determine the tables for which changes are tracked if (trackingVersion != ulong.MinValue) { trackedTables = database.Connection.RetrieveData(database.AdapterType, "SELECT Name FROM TrackedTable").Select() .Select(row => row["Name"].ToNonNullString()) .ToArray(); } else { trackedTables = new string[0]; } } catch { trackedTables = new string[0]; } statusMessage("Validating device protocols..."); // Extract IDs for phasor protocols StringBuilder protocolIDList = new StringBuilder(); DataTable protocols = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM Protocol"); if (protocols.Columns.Contains("Category")) { // Make sure new protocol types exist if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='GatewayTransport'"))) == 0) { database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('GatewayTransport', 'Gateway Transport', 'Measurement', 'Gateway', 'GSF.TimeSeries.dll', 'GSF.TimeSeries.Transport.DataSubscriber', " + (protocols.Rows.Count + 1) + ")"); if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='WAV'"))) == 0) database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter', " + (protocols.Rows.Count + 2) + ")"); if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='IeeeC37_118V2'"))) == 0) database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('IeeeC37_118V2', 'IEEE C37.118.2-2011', 'Frame', 'Phasor', 'PhasorProtocolAdapters.dll', 'PhasorProtocolAdapters.PhasorMeasurementMapper', 2)"); if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='VirtualInput'"))) == 0) database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('VirtualInput', 'Virtual Device', 'Frame', 'Virtual', 'TestingAdapters.dll', 'TestingAdapters.VirtualInputAdapter', " + (protocols.Rows.Count + 4) + ")"); } if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='Iec61850_90_5'"))) == 0) database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('Iec61850_90_5', 'IEC 61850-90-5', 'Frame', 'Phasor', 'PhasorProtocolAdapters.dll', 'PhasorProtocolAdapters.PhasorMeasurementMapper', 12)"); foreach (DataRow protocol in protocols.Rows) { if (string.Compare(protocol.Field<string>("Category"), "Phasor", true) == 0) { if (protocolIDList.Length > 0) protocolIDList.Append(", "); protocolIDList.Append(protocol.ConvertField<int>("ID")); } } } else { // Older schemas do not include protocol categories and assembly info foreach (DataRow protocol in protocols.Rows) { if (protocolIDList.Length > 0) protocolIDList.Append(", "); protocolIDList.Append(protocol.ConvertField<int>("ID")); } } protocolIDs = protocolIDList.ToString(); try { // Determine how many changes were made to devices and measurements - // if no changes were made, we can skip the next few steps if (trackedTables.Contains("Device") && trackedTables.Contains("Measurement")) changes = Convert.ToUInt64(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM TrackedChange WHERE (TableName = 'Device' OR TableName = 'Measurement') AND ID > {0}", trackingVersion))); else changes = ulong.MaxValue; } catch { changes = ulong.MaxValue; } if (skipOptimization || changes != 0L) { statusMessage("Validating device measurements..."); // Get protocol ID list for those protocols that support time quality flags DataTable timeQualityProtocols = database.Connection.RetrieveData(database.AdapterType, "SELECT ID FROM Protocol WHERE Acronym = 'IeeeC37_118V1' OR Acronym = 'IeeeC37_118V2' OR Acronym = 'IeeeC37_118D6' OR Acronym = 'Iec61850_90_5'"); StringBuilder timeQualityProtocolIDList = new StringBuilder(); string timeQualityProtocolIDs; foreach (DataRow timeQualityProtocol in timeQualityProtocols.Rows) { if (timeQualityProtocolIDList.Length > 0) timeQualityProtocolIDList.Append(", "); timeQualityProtocolIDList.Append(timeQualityProtocol.ConvertField<int>("ID")); } timeQualityProtocolIDs = timeQualityProtocolIDList.ToString(); int qualityFlagsSignalTypeID = Convert.ToInt32(database.Connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='QUAL'")); // Make sure one device quality flags measurement exists for each "connection" for devices that support time quality flags foreach (DataRow device in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM Device WHERE ((IsConcentrator = 0 AND ParentID IS NULL) OR IsConcentrator = 1) AND NodeID = {0} AND ProtocolID IN ({1})", nodeIDQueryString, timeQualityProtocolIDs)).Rows) { deviceID = device.ConvertField<int>("ID"); acronym = device.Field<string>("Acronym"); signalReference = SignalReference.ToString(acronym, SignalKind.Quality); // See if quality flags measurement exists for device if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Measurement WHERE SignalReference = '{0}' AND DeviceID = {1}", signalReference, deviceID))) == 0) { historianID = device.ConvertNullableField<int>("HistorianID"); company = (string)database.Connection.ExecuteScalar(string.Format("SELECT MapAcronym FROM Company WHERE ID = {0}", device.ConvertNullableField<int>("CompanyID") ?? 0)); if (string.IsNullOrEmpty(company)) company = configFile.Settings["systemSettings"]["CompanyAcronym"].Value.TruncateRight(3); pointTag = CreatePointTag(company, acronym, null, "QUAL"); description = string.Format("{0} Time Quality Flags", device.Field<string>("Name")); query = database.ParameterizedQueryString("INSERT INTO Measurement(HistorianID, DeviceID, PointTag, SignalTypeID, PhasorSourceIndex, " + "SignalReference, Description, Enabled) VALUES({0}, {1}, {2}, {3}, NULL, {4}, {5}, 1)", "historianID", "deviceID", "pointTag", "signalTypeID", "signalReference", "description"); database.Connection.ExecuteNonQuery(query, DataExtensions.DefaultTimeoutDuration, historianID.HasValue ? (object)historianID.Value : (object)DBNull.Value, deviceID, pointTag, qualityFlagsSignalTypeID, signalReference, description); } } // Make sure needed device statistic measurements exist, currently statistics are only associated with phasor devices so we filter based on protocol foreach (DataRow device in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM Device WHERE IsConcentrator = 0 AND NodeID = {0} AND ProtocolID IN ({1})", nodeIDQueryString, protocolIDs)).Rows) { foreach (DataRow statistic in deviceStatistics) { string oldAcronym; string oldSignalReference; signalIndex = statistic.ConvertField<int>("SignalIndex"); oldAcronym = device.Field<string>("Acronym"); acronym = oldAcronym + "!PMU"; oldSignalReference = SignalReference.ToString(oldAcronym, SignalKind.Statistic, signalIndex); signalReference = SignalReference.ToString(acronym, SignalKind.Statistic, signalIndex); // If the original format for device statistics is found in the database, update to new format if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Measurement WHERE SignalReference='{0}' AND HistorianID={1}", oldSignalReference, statHistorianID))) > 0) database.Connection.ExecuteNonQuery(string.Format("UPDATE Measurement SET SignalReference='{0}' WHERE SignalReference='{1}' AND HistorianID={2}", signalReference, oldSignalReference, statHistorianID)); else if (!skipOptimization) break; } } statusMessage("Validating input stream measurements..."); // Make sure devices associated with a concentrator do not have any extraneous input stream statistic measurements - this can happen // when a device was once a direct connect device but now is part of a concentrator... foreach (DataRow inputStream in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM Device WHERE (IsConcentrator = 0 AND ParentID IS NOT NULL) AND NodeID = {0} AND ProtocolID IN ({1})", nodeIDQueryString, protocolIDs)).Rows) { firstStatisticExisted = false; foreach (DataRow statistic in inputStreamStatistics) { acronym = inputStream.Field<string>("Acronym") + "!IS"; signalIndex = statistic.ConvertField<int>("SignalIndex"); signalReference = SignalReference.ToString(acronym, SignalKind.Statistic, signalIndex); // To reduce time required to execute these steps, only first statistic is verified to exist if (!skipOptimization && !firstStatisticExisted) { firstStatisticExisted = (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Measurement WHERE SignalReference='{0}'", signalReference))) > 0); // If the first extraneous input statistic doesn't exist, we assume no others do as well if (!firstStatisticExisted) break; } // Remove extraneous input statistics database.Connection.ExecuteNonQuery(string.Format("DELETE FROM Measurement WHERE SignalReference = '{0}'", signalReference)); } } } try { // Determine how many changes were made to output streams, devices, and measurements - // if no changes were made, we can skip the next few steps if (trackedTables.Contains("OutputStream") && trackedTables.Contains("OutputStreamDevice") && trackedTables.Contains("OutputStreamMeasurement") && trackedTables.Contains("Measurement")) changes = Convert.ToUInt64(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM TrackedChange WHERE (TableName = 'OutputStream' OR TableName = 'OutputStreamDevice' OR TableName = 'OutputStreamMeasurement' OR TableName = 'Measurement') AND ID > {0}", trackingVersion))); else changes = ulong.MaxValue; } catch { changes = ulong.MaxValue; } if (skipOptimization || changes != 0L) { statusMessage("Validating output stream measurements..."); // Make sure needed output stream statistic measurements exist foreach (DataRow outputStream in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM OutputStream WHERE NodeID = {0}", nodeIDQueryString)).Rows) { adapterID = outputStream.ConvertField<int>("ID"); // Load devices acronyms associated with this output stream List<string> deviceAcronyms = database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT Acronym FROM OutputStreamDevice WHERE AdapterID = {0} AND NodeID = {1}", adapterID, nodeIDQueryString)) .AsEnumerable() .Select(row => row.Field<string>("Acronym")) .ToList(); // Since measurements can be added to the output stream device itself (e.g., quality flags) - we add it as a valid mapping destination as well deviceAcronyms.Add(outputStream.Field<string>("Acronym")); // Sort list so binary search can be used to speed lookups deviceAcronyms.Sort(StringComparer.OrdinalIgnoreCase); // Validate measurements associated with this output stream foreach (DataRow outputStreamMeasurement in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM OutputStreamMeasurement WHERE AdapterID = {0} AND NodeID = {1}", adapterID, nodeIDQueryString)).Rows) { // Parse output stream measurement signal reference deviceSignalReference = new SignalReference(outputStreamMeasurement.Field<string>("SignalReference")); // Validate that the signal reference is associated with one of the output stream's devices if (deviceAcronyms.BinarySearch(deviceSignalReference.Acronym, StringComparer.OrdinalIgnoreCase) < 0) { // This measurement has a signal reference for a device that is not part of the associated output stream, so we mark it for deletion measurementIDsToDelete.Add(outputStreamMeasurement.ConvertField<int>("ID")); } // Validate that the signal reference type is valid for an output stream if (!validOutputSignalKinds.Any(validSignalKind => deviceSignalReference.Kind == validSignalKind)) { // This measurement has a signal reference type that is not valid for an output stream, so we mark it for deletion measurementIDsToDelete.Add(outputStreamMeasurement.ConvertField<int>("ID")); } } } } if (measurementIDsToDelete.Count > 0) { statusMessage(string.Format("Removing {0} unused output stream device measurements...", measurementIDsToDelete.Count)); foreach (int measurementID in measurementIDsToDelete) { database.Connection.ExecuteNonQuery(string.Format("DELETE FROM OutputStreamMeasurement WHERE ID = {0} AND NodeID = {1}", measurementID, nodeIDQueryString)); } } if (renameAllPointTags) { statusMessage("Renaming all point tags..."); string device, vendor, signalAcronym; char? phase; int? vendorDeviceID; SignalReference signal; foreach (DataRow measurement in database.Connection.RetrieveData(database.AdapterType, "SELECT SignalID, CompanyAcronym, DeviceAcronym, VendorDeviceID, SignalReference, SignalAcronym, Phase FROM MeasurementDetail WHERE SignalAcronym <> 'STAT' AND Internal <> 0 AND Subscribed = 0").Rows) { company = measurement.ConvertField<string>("CompanyAcronym"); if (string.IsNullOrEmpty(company)) company = configFile.Settings["systemSettings"]["CompanyAcronym"].Value.TruncateRight(3); device = measurement.ConvertField<string>("DeviceAcronym"); if ((object)device != null) { vendorDeviceID = measurement.ConvertNullableField<int>("VendorDeviceID"); if (vendorDeviceID.HasValue) vendor = (string)database.Connection.ExecuteScalar("SELECT Acronym FROM Vendor WHERE ID = " + vendorDeviceID.Value); else vendor = null; signalAcronym = measurement.ConvertField<string>("SignalAcronym"); try { signal = new SignalReference(measurement.ConvertField<string>("SignalReference")); signalIndex = signal.Index; if (signalIndex <= 0) signalIndex = -1; } catch { signalIndex = -1; } phase = measurement.ConvertNullableField<char>("Phase"); database.Connection.ExecuteNonQuery(string.Format("UPDATE Measurement SET PointTag = '{0}' WHERE SignalID = '{1}'", CreatePointTag(company, device, vendor, signalAcronym, signalIndex, phase ?? '_'), database.Guid(measurement, "SignalID"))); } } } if (skipOptimization || renameAllPointTags) { // If skipOptimization is set to true, automatically set it back to false const string clearParametersQuery = "UPDATE DataOperation SET Arguments = '' " + "WHERE AssemblyName = 'PhasorProtocolAdapters.dll' " + "AND TypeName = 'PhasorProtocolAdapters.CommonPhasorServices' " + "AND MethodName = 'PhasorDataSourceValidation'"; database.Connection.ExecuteNonQuery(clearParametersQuery); } }
// Static Methods /// <summary> /// LoadKeys <see cref="Phasor"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="parentID">ID of the parent device to filter data.</param> /// <param name="searchText">The text to search by.</param> /// <param name="sortMember">The field to sort by.</param> /// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param> /// <returns>Collection of <see cref="Phasor"/>.</returns> public static IList<int> LoadKeys(AdoDataConnection database, int parentID = 0, string searchText = "", string sortMember = "", string sortDirection = "") { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); IList<int> deviceList = new List<int>(); DataTable deviceTable; string query; string searchParam = null; string searchQuery = null; string sortClause = string.Empty; if (!string.IsNullOrEmpty(searchText)) { searchParam = string.Format("%{0}%", searchText); if (database.IsJetEngine) { // Access queries do not support UPPER but are case-insensitive anyway searchQuery = string.Format("ID LIKE '{0}' OR UniqueID LIKE '{0}' OR Acronym LIKE '{0}' " + "OR Name LIKE '{0}' OR OriginalSource LIKE '{0}' OR Longitude LIKE '{0}' OR Latitude LIKE '{0}' " + "OR ConnectionString LIKE '{0}' OR TimeZone LIKE '{0}' OR FramesPerSecond LIKE '{0}' " + "OR ContactList LIKE '{0}' OR CompanyName LIKE '{0}' OR CompanyAcronym LIKE '{0}' " + "OR CompanyMapAcronym LIKE '{0}' OR HistorianAcronym LIKE '{0}' OR VendorAcronym LIKE '{0}' " + "OR VendorDeviceName LIKE '{0}' OR ProtocolName LIKE '{0}' OR InterconnectionName LIKE '{0}'", searchParam.Replace("'", "''")); } else { searchQuery = database.ParameterizedQueryString("ID LIKE {0} OR UPPER(UniqueID) LIKE UPPER({0}) OR UPPER(Acronym) LIKE UPPER({0}) " + "OR UPPER(Name) LIKE UPPER({0}) OR UPPER(OriginalSource) LIKE UPPER({0}) OR Longitude LIKE {0} OR Latitude LIKE {0} " + "OR UPPER(ConnectionString) LIKE UPPER({0}) OR UPPER(TimeZone) LIKE UPPER({0}) OR FramesPerSecond LIKE {0} " + "OR UPPER(ContactList) LIKE UPPER({0}) OR UPPER(CompanyName) LIKE UPPER({0}) OR UPPER(CompanyAcronym) LIKE UPPER({0}) " + "OR UPPER(CompanyMapAcronym) LIKE UPPER({0}) OR UPPER(HistorianAcronym) LIKE UPPER({0}) OR UPPER(VendorAcronym) LIKE UPPER({0}) " + "OR UPPER(VendorDeviceName) LIKE UPPER({0}) OR UPPER(ProtocolName) LIKE UPPER({0}) OR UPPER(InterconnectionName) LIKE UPPER({0})", "searchParam"); } } if (!string.IsNullOrEmpty(sortMember)) sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection); if (parentID > 0) { if (string.IsNullOrEmpty(searchText)) { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ParentID = {{1}} {0}", sortClause), "nodeID", "parentID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), parentID); } else if (!database.IsJetEngine) { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ParentID = {{1}} AND ({0}) {1}", searchText, sortClause), "nodeID", "parentID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), parentID, searchParam); } else { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ParentID = {{1}} AND ({0}) {1}", searchText, sortClause), "nodeID", "parentID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), parentID); } } else { if (string.IsNullOrEmpty(searchText)) { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} {0}", sortClause), "nodeID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); } else if (!database.IsJetEngine) { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ({0}) {1}", searchQuery, sortClause), "nodeID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), searchParam); } else { query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ({0}) {1}", searchQuery, sortClause), "nodeID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); } } foreach (DataRow row in deviceTable.Rows) { deviceList.Add(row.ConvertField<int>("ID")); } return deviceList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Retrieves a collection of devices where original source is not null for mirroring output stream. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="isOptional">Boolean flag indicating if selection is optional on the UI.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of Devices.</returns> public static Dictionary<string, string> GetDevicesForMirroringOutputStream(AdoDataConnection database, bool isOptional = true) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary<string, string> deviceList = new Dictionary<string, string>(); if (isOptional) deviceList.Add("", "Select Mirroring Source"); DataTable deviceTable; string query; query = database.ParameterizedQueryString("SELECT DISTINCT OriginalSource FROM Device WHERE NodeID = {0} AND OriginalSource IS NOT NULL ORDER BY OriginalSource", "nodeID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in deviceTable.Rows) deviceList[row.ConvertField<string>("OriginalSource")] = row.Field<string>("OriginalSource"); return deviceList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Device"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="deviceType"><see cref="DeviceType"/> to filter data.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <param name="showAll">boolean flag indicates if all the devices should be returned irrespective of node.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of devices defined in the database.</returns> public static Dictionary<int, string> GetLookupList(AdoDataConnection database, DeviceType deviceType = DeviceType.DirectConnected, bool isOptional = false, bool showAll = false) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary<int, string> deviceList = new Dictionary<int, string>(); DataTable deviceTable; string query; if (isOptional) deviceList.Add(0, "Select Device"); if (deviceType == DeviceType.Concentrator) { query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Device WHERE IsConcentrator = {0} AND NodeID = {1} AND Enabled= {2} ORDER BY LoadOrder", "isConcentrator", "nodeID", "Enabled"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID(), database.Bool(true)); } else if (deviceType == DeviceType.DirectConnected) { query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Device WHERE IsConcentrator = {0} AND NodeID = {1} AND Enabled= {2} ORDER BY LoadOrder", "isConcentrator", "nodeID", "Enabled"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(false), database.CurrentNodeID(), database.Bool(true)); } else { if (showAll) { query = "SELECT ID, Acronym FROM Device ORDER BY LoadOrder"; deviceTable = database.Connection.RetrieveData(database.AdapterType, query); } else { query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Device WHERE NodeID = {0} ORDER BY LoadOrder", "nodeID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); } } foreach (DataRow row in deviceTable.Rows) deviceList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym"); return deviceList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Saves <see cref="Subscriber"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriber">Information about <see cref="Subscriber"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, Subscriber subscriber) { bool createdConnection = false; SslPolicyErrors validPolicyErrors; X509ChainStatusFlags validChainFlags; string query; try { createdConnection = CreateConnection(ref database); validPolicyErrors = (subscriber.ValidPolicyErrors ?? SslPolicyErrors.None) | (subscriber.RemoteCertificateIsSelfSigned ? SslPolicyErrors.RemoteCertificateChainErrors : SslPolicyErrors.None); validChainFlags = (subscriber.ValidChainFlags ?? X509ChainStatusFlags.NoError) | (subscriber.RemoteCertificateIsSelfSigned ? X509ChainStatusFlags.UntrustedRoot : X509ChainStatusFlags.NoError); if (subscriber.ID == Guid.Empty) { query = database.ParameterizedQueryString("INSERT INTO Subscriber (NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile, ValidPolicyErrors, ValidChainFlags, " + "AccessControlFilter, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " + "{13}, {14})", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(), subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(), validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE Subscriber SET NodeID = {0}, Acronym = {1}, Name = {2}, SharedSecret = {3}, AuthKey = {4}, ValidIPAddresses = {5}, RemoteCertificateFile = {6}, " + "ValidPolicyErrors = {7}, ValidChainFlags = {8}, AccessControlFilter = {9}, Enabled = {10}, UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter", "enabled", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriber.NodeID), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(), subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(), validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow, database.Guid(subscriber.ID)); } try { CommonFunctions.SendCommandToService("ReloadConfig"); } catch (Exception ex) { return "Subscriber information saved successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message; } return "Subscriber information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
private static OutputStreamMeasurement GetOutputMeasurementDetails(AdoDataConnection database, string signalReference, int adapterID) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query = database.ParameterizedQueryString("SELECT * FROM OutputStreamMeasurement WHERE SignalReference = {0} AND AdapterID = {1}", "signalReference", "adapterID"); DataRow row = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, signalReference, adapterID).Rows[0]; OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement { NodeID = row.ConvertField<Guid>("NodeID"), AdapterID = row.Field<int>("AdapterID"), ID = row.Field<int>("ID"), HistorianID = row.Field<int>("HistorianID"), PointID = row.Field<int>("PointID"), SignalReference = row.ConvertField<string>("SignalReference"), CreatedOn = row.ConvertField<DateTime>("CreatedOn"), CreatedBy = row.Field<string>("CreatedBy"), UpdatedOn = row.ConvertField<DateTime>("UpdatedOn"), UpdatedBy = row.Field<string>("UpdatedBy") }; return outputStreamMeasurement; } catch (Exception ex) { CommonFunctions.LogException(database, "OutputStreamDevicePhasor.GetOutputMeasurementDetails", ex); throw; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Handles meta-data synchronization to local system. /// </summary> /// <remarks> /// This function should only be initiated from call to <see cref="SynchronizeMetadata(DataSet)"/> to make /// sure only one meta-data synchronization happens at once. Users can override this method to customize /// process of meta-data synchronization. /// </remarks> protected virtual void SynchronizeMetadata() { bool dataMonitoringEnabled = false; // TODO: This function is complex and very closely tied to the current time-series data schema - perhaps it should be moved outside this class and referenced // TODO: as a delegate that can be assigned and called to allow other schemas as well. DataPublisher is already very flexible in what data it can deliver. try { DataSet metadata = m_receivedMetadata; // Only perform database synchronization if meta-data has changed since last update if (!SynchronizedMetadataChanged(metadata)) return; if ((object)metadata == null) { OnStatusMessage("WARNING: Meta-data synchronization was not performed, deserialized dataset was empty."); return; } // Reset data stream monitor while meta-data synchronization is in progress if ((object)m_dataStreamMonitor != null && m_dataStreamMonitor.Enabled) { m_dataStreamMonitor.Enabled = false; dataMonitoringEnabled = true; } // Track total meta-data synchronization process time Ticks startTime = DateTime.UtcNow.Ticks; DateTime updateTime; DateTime latestUpdateTime = DateTime.MinValue; // Open the configuration database using settings found in the config file using (AdoDataConnection database = new AdoDataConnection("systemSettings")) using (IDbCommand command = database.Connection.CreateCommand()) { IDbTransaction transaction = null; if (m_useTransactionForMetadata) transaction = database.Connection.BeginTransaction(database.DefaultIsloationLevel); try { if ((object)transaction != null) command.Transaction = transaction; // Query the actual record ID based on the known run-time ID for this subscriber device int parentID = Convert.ToInt32(command.ExecuteScalar($"SELECT SourceID FROM Runtime WHERE ID = {ID} AND SourceTable='Device'", m_metadataSynchronizationTimeout)); // Validate that the subscriber device is marked as a concentrator (we are about to associate children devices with it) if (!command.ExecuteScalar($"SELECT IsConcentrator FROM Device WHERE ID = {parentID}", m_metadataSynchronizationTimeout).ToString().ParseBoolean()) command.ExecuteNonQuery($"UPDATE Device SET IsConcentrator = 1 WHERE ID = {parentID}", m_metadataSynchronizationTimeout); // Get any historian associated with the subscriber device object historianID = command.ExecuteScalar($"SELECT HistorianID FROM Device WHERE ID = {parentID}", m_metadataSynchronizationTimeout); // Determine the active node ID - we cache this since this value won't change for the lifetime of this class if (m_nodeID == Guid.Empty) m_nodeID = Guid.Parse(command.ExecuteScalar($"SELECT NodeID FROM IaonInputAdapter WHERE ID = {(int)ID}", m_metadataSynchronizationTimeout).ToString()); // Determine the protocol record auto-inc ID value for the gateway transport protocol (GEP) - this value is also cached since it shouldn't change for the lifetime of this class if (m_gatewayProtocolID == 0) m_gatewayProtocolID = int.Parse(command.ExecuteScalar("SELECT ID FROM Protocol WHERE Acronym='GatewayTransport'", m_metadataSynchronizationTimeout).ToString()); // Ascertain total number of actions required for all meta-data synchronization so some level feed back can be provided on progress InitSyncProgress(metadata.Tables.Cast<DataTable>().Select(dataTable => (long)dataTable.Rows.Count).Sum() + 3); // Prefix all children devices with the name of the parent since the same device names could appear in different connections (helps keep device names unique) string sourcePrefix = Name + "!"; Dictionary<string, int> deviceIDs = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase); string deviceAcronym, signalTypeAcronym; decimal longitude, latitude; decimal? location; object originalSource; int deviceID; // Check to see if data for the "DeviceDetail" table was included in the meta-data if (metadata.Tables.Contains("DeviceDetail")) { DataTable deviceDetail = metadata.Tables["DeviceDetail"]; List<Guid> uniqueIDs = new List<Guid>(); DataRow[] deviceRows; // Define SQL statement to query if this device is already defined (this should always be based on the unique guid-based device ID) string deviceExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0}", "uniqueID"); // Define SQL statement to insert new device record string insertDeviceSql = database.ParameterizedQueryString("INSERT INTO Device(NodeID, ParentID, HistorianID, Acronym, Name, ProtocolID, FramesPerSecond, OriginalSource, AccessID, Longitude, Latitude, ContactList, IsConcentrator, Enabled) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, 0, 1)", "nodeID", "parentID", "historianID", "acronym", "name", "protocolID", "framesPerSecond", "originalSource", "accessID", "longitude", "latitude", "contactList"); // Define SQL statement to update device's guid-based unique ID after insert string updateDeviceUniqueIDSql = database.ParameterizedQueryString("UPDATE Device SET UniqueID = {0} WHERE Acronym = {1}", "uniqueID", "acronym"); // Define SQL statement to query if a device can be safely updated string deviceIsUpdatableSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0} AND (ParentID <> {1} OR ParentID IS NULL)", "uniqueID", "parentID"); // Define SQL statement to update existing device record string updateDeviceSql = database.ParameterizedQueryString("UPDATE Device SET Acronym = {0}, Name = {1}, OriginalSource = {2}, ProtocolID = {3}, FramesPerSecond = {4}, HistorianID = {5}, AccessID = {6}, Longitude = {7}, Latitude = {8}, ContactList = {9} WHERE UniqueID = {10}", "acronym", "name", "originalSource", "protocolID", "framesPerSecond", "historianID", "accessID", "longitude", "latitude", "contactList", "uniqueID"); // Define SQL statement to retrieve device's auto-inc ID based on its unique guid-based ID string queryDeviceIDSql = database.ParameterizedQueryString("SELECT ID FROM Device WHERE UniqueID = {0}", "uniqueID"); // Define SQL statement to retrieve all unique device ID's for the current parent to check for mismatches string queryUniqueDeviceIDsSql = database.ParameterizedQueryString("SELECT UniqueID FROM Device WHERE ParentID = {0}", "parentID"); // Define SQL statement to remove device records that no longer exist in the meta-data string deleteDeviceSql = database.ParameterizedQueryString("DELETE FROM Device WHERE UniqueID = {0}", "uniqueID"); // Determine which device rows should be synchronized based on operational mode flags if (ReceiveInternalMetadata && ReceiveExternalMetadata) deviceRows = deviceDetail.Select(); else if (ReceiveInternalMetadata) deviceRows = deviceDetail.Select("OriginalSource IS NULL"); else if (ReceiveExternalMetadata) deviceRows = deviceDetail.Select("OriginalSource IS NOT NULL"); else deviceRows = new DataRow[0]; // Check existence of optional meta-data fields DataColumnCollection deviceDetailColumns = deviceDetail.Columns; bool accessIDFieldExists = deviceDetailColumns.Contains("AccessID"); bool longitudeFieldExists = deviceDetailColumns.Contains("Longitude"); bool latitudeFieldExists = deviceDetailColumns.Contains("Latitude"); bool companyAcronymFieldExists = deviceDetailColumns.Contains("CompanyAcronym"); bool protocolNameFieldExists = deviceDetailColumns.Contains("ProtocolName"); bool vendorAcronymFieldExists = deviceDetailColumns.Contains("VendorAcronym"); bool vendorDeviceNameFieldExists = deviceDetailColumns.Contains("VendorDeviceName"); bool interconnectionNameFieldExists = deviceDetailColumns.Contains("InterconnectionName"); bool updatedOnFieldExists = deviceDetailColumns.Contains("UpdatedOn"); // Older versions of GEP did not include the AccessID field, so this is treated as optional int accessID = 0; foreach (DataRow row in deviceRows) { Guid uniqueID = Guid.Parse(row.Field<object>("UniqueID").ToString()); bool recordNeedsUpdating; // Track unique device Guids in this meta-data session, we'll need to remove any old associated devices that no longer exist uniqueIDs.Add(uniqueID); // Determine if record has changed since last synchronization if (updatedOnFieldExists) { try { updateTime = Convert.ToDateTime(row["UpdatedOn"]); recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime; if (updateTime > latestUpdateTime) latestUpdateTime = updateTime; } catch { recordNeedsUpdating = true; } } else { recordNeedsUpdating = true; } // We will synchronize meta-data only if the source owns this device and it's not defined as a concentrator (these should normally be filtered by publisher - but we check just in case). if (!row["IsConcentrator"].ToNonNullString("0").ParseBoolean()) { if (accessIDFieldExists) accessID = row.ConvertField<int>("AccessID"); // Get longitude and latitude values if they are defined longitude = 0M; latitude = 0M; if (longitudeFieldExists) { location = row.ConvertNullableField<decimal>("Longitude"); if (location.HasValue) longitude = location.Value; } if (latitudeFieldExists) { location = row.ConvertNullableField<decimal>("Latitude"); if (location.HasValue) latitude = location.Value; } // Save any reported extraneous values from device meta-data in connection string formatted contact list - all fields are considered optional Dictionary<string, string> contactList = new Dictionary<string, string>(); if (companyAcronymFieldExists) contactList["companyAcronym"] = row.Field<string>("CompanyAcronym") ?? string.Empty; if (protocolNameFieldExists) contactList["protocolName"] = row.Field<string>("ProtocolName") ?? string.Empty; if (vendorAcronymFieldExists) contactList["vendorAcronym"] = row.Field<string>("VendorAcronym") ?? string.Empty; if (vendorDeviceNameFieldExists) contactList["vendorDeviceName"] = row.Field<string>("VendorDeviceName") ?? string.Empty; if (interconnectionNameFieldExists) contactList["interconnectionName"] = row.Field<string>("InterconnectionName") ?? string.Empty; // Determine if device record already exists if (Convert.ToInt32(command.ExecuteScalar(deviceExistsSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID))) == 0) { // Insert new device record command.ExecuteNonQuery(insertDeviceSql, m_metadataSynchronizationTimeout, database.Guid(m_nodeID), parentID, historianID, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), m_gatewayProtocolID, row.ConvertField<int>("FramesPerSecond"), m_internal ? (object)DBNull.Value : string.IsNullOrEmpty(row.Field<string>("ParentAcronym")) ? sourcePrefix + row.Field<string>("Acronym") : sourcePrefix + row.Field<string>("ParentAcronym"), accessID, longitude, latitude, contactList.JoinKeyValuePairs()); // Guids are normally auto-generated during insert - after insertion update the Guid so that it matches the source data. Most of the database // scripts have triggers that support properly assigning the Guid during an insert, but this code ensures the Guid will always get assigned. command.ExecuteNonQuery(updateDeviceUniqueIDSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID), sourcePrefix + row.Field<string>("Acronym")); } else if (recordNeedsUpdating) { // Perform safety check to preserve device records which are not safe to overwrite if (Convert.ToInt32(command.ExecuteScalar(deviceIsUpdatableSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID), parentID)) > 0) continue; // Gateway is assuming ownership of the device records when the "internal" flag is true - this means the device's measurements can be forwarded to another party. From a device record perspective, // ownership is inferred by setting 'OriginalSource' to null. When gateway doesn't own device records (i.e., the "internal" flag is false), this means the device's measurements can only be consumed // locally - from a device record perspective this means the 'OriginalSource' field is set to the acronym of the PDC or PMU that generated the source measurements. This field allows a mirrored source // restriction to be implemented later to ensure all devices in an output protocol came from the same original source connection, if desired. originalSource = m_internal ? (object)DBNull.Value : string.IsNullOrEmpty(row.Field<string>("ParentAcronym")) ? sourcePrefix + row.Field<string>("Acronym") : sourcePrefix + row.Field<string>("ParentAcronym"); // Update existing device record command.ExecuteNonQuery(updateDeviceSql, m_metadataSynchronizationTimeout, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), originalSource, m_gatewayProtocolID, row.ConvertField<int>("FramesPerSecond"), historianID, accessID, longitude, latitude, contactList.JoinKeyValuePairs(), database.Guid(uniqueID)); } } // Capture local device ID auto-inc value for measurement association deviceIDs[row.Field<string>("Acronym")] = Convert.ToInt32(command.ExecuteScalar(queryDeviceIDSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID))); // Periodically notify user about synchronization progress UpdateSyncProgress(); } // Remove any device records associated with this subscriber that no longer exist in the meta-data if (uniqueIDs.Count > 0) { // Sort unique ID list so that binary search can be used for quick lookups uniqueIDs.Sort(); DataTable deviceUniqueIDs = command.RetrieveData(database.AdapterType, queryUniqueDeviceIDsSql, m_metadataSynchronizationTimeout, parentID); Guid uniqueID; foreach (DataRow deviceRow in deviceUniqueIDs.Rows) { uniqueID = database.Guid(deviceRow, "UniqueID"); // Remove any devices in the database that are associated with the parent device and do not exist in the meta-data if (uniqueIDs.BinarySearch(uniqueID) < 0) command.ExecuteNonQuery(deleteDeviceSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID)); } UpdateSyncProgress(); } } // Check to see if data for the "MeasurementDetail" table was included in the meta-data if (metadata.Tables.Contains("MeasurementDetail")) { DataTable measurementDetail = metadata.Tables["MeasurementDetail"]; List<Guid> signalIDs = new List<Guid>(); DataRow[] measurementRows; // Define SQL statement to query if this measurement is already defined (this should always be based on the unique signal ID Guid) string measurementExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Measurement WHERE SignalID = {0}", "signalID"); // Define SQL statement to insert new measurement record string insertMeasurementSql = database.ParameterizedQueryString("INSERT INTO Measurement(DeviceID, HistorianID, PointTag, AlternateTag, SignalTypeID, PhasorSourceIndex, SignalReference, Description, Internal, Subscribed, Enabled) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, 0, 1)", "deviceID", "historianID", "pointTag", "alternateTag", "signalTypeID", "phasorSourceIndex", "signalReference", "description", "internal"); // Define SQL statement to update measurement's signal ID after insert string updateMeasurementSignalIDSql = database.ParameterizedQueryString("UPDATE Measurement SET SignalID = {0}, AlternateTag = NULL WHERE AlternateTag = {1}", "signalID", "alternateTag"); // Define SQL statement to update existing measurement record string updateMeasurementSql = database.ParameterizedQueryString("UPDATE Measurement SET HistorianID = {0}, PointTag = {1}, SignalTypeID = {2}, PhasorSourceIndex = {3}, SignalReference = {4}, Description = {5}, Internal = {6} WHERE SignalID = {7}", "historianID", "pointTag", "signalTypeID", "phasorSourceIndex", "signalReference", "description", "internal", "signalID"); // Define SQL statement to retrieve all measurement signal ID's for the current parent to check for mismatches - note that we use the ActiveMeasurements view // since it associates measurements with their top-most parent runtime device ID, this allows us to easily query all measurements for the parent device string queryMeasurementSignalIDsSql = database.ParameterizedQueryString("SELECT SignalID FROM ActiveMeasurement WHERE DeviceID = {0}", "deviceID"); // Define SQL statement to retrieve measurement's associated device ID, i.e., actual record ID, based on measurement's signal ID string queryMeasurementDeviceIDSql = database.ParameterizedQueryString("SELECT DeviceID FROM Measurement WHERE SignalID = {0}", "signalID"); // Define SQL statement to remove device records that no longer exist in the meta-data string deleteMeasurementSql = database.ParameterizedQueryString("DELETE FROM Measurement WHERE SignalID = {0}", "signalID"); // Load signal type ID's from local database associated with their acronym for proper signal type translation Dictionary<string, int> signalTypeIDs = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase); foreach (DataRow row in command.RetrieveData(database.AdapterType, "SELECT ID, Acronym FROM SignalType").Rows) { signalTypeAcronym = row.Field<string>("Acronym"); if (!string.IsNullOrWhiteSpace(signalTypeAcronym)) signalTypeIDs[signalTypeAcronym] = row.ConvertField<int>("ID"); } // Determine which measurement rows should be synchronized based on operational mode flags if (ReceiveInternalMetadata && ReceiveExternalMetadata) measurementRows = measurementDetail.Select(); else if (ReceiveInternalMetadata) measurementRows = measurementDetail.Select("Internal <> 0"); else if (ReceiveExternalMetadata) measurementRows = measurementDetail.Select("Internal = 0"); else measurementRows = new DataRow[0]; // Check existence of optional meta-data fields DataColumnCollection measurementDetailColumns = measurementDetail.Columns; bool phasorSourceIndexFieldExists = measurementDetailColumns.Contains("PhasorSourceIndex"); bool updatedOnFieldExists = measurementDetailColumns.Contains("UpdatedOn"); object phasorSourceIndex = DBNull.Value; foreach (DataRow row in measurementRows) { bool recordNeedsUpdating; // Determine if record has changed since last synchronization if (updatedOnFieldExists) { try { updateTime = Convert.ToDateTime(row["UpdatedOn"]); recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime; if (updateTime > latestUpdateTime) latestUpdateTime = updateTime; } catch { recordNeedsUpdating = true; } } else { recordNeedsUpdating = true; } // Get device and signal type acronyms deviceAcronym = row.Field<string>("DeviceAcronym") ?? string.Empty; signalTypeAcronym = row.Field<string>("SignalAcronym") ?? string.Empty; // Get phasor source index if field is defined if (phasorSourceIndexFieldExists) { // Using ConvertNullableField extension since publisher could use SQLite database in which case // all integers would arrive in data set as longs and need to be converted back to integers int? index = row.ConvertNullableField<int>("PhasorSourceIndex"); phasorSourceIndex = index.HasValue ? (object)index.Value : (object)DBNull.Value; } // Make sure we have an associated device and signal type already defined for the measurement if (!string.IsNullOrWhiteSpace(deviceAcronym) && deviceIDs.ContainsKey(deviceAcronym) && !string.IsNullOrWhiteSpace(signalTypeAcronym) && signalTypeIDs.ContainsKey(signalTypeAcronym)) { Guid signalID = Guid.Parse(row.Field<object>("SignalID").ToString()); // Track unique measurement signal Guids in this meta-data session, we'll need to remove any old associated measurements that no longer exist signalIDs.Add(signalID); // Prefix the tag name with the "updated" device name string pointTag = sourcePrefix + row.Field<string>("PointTag"); // Look up associated device ID (local DB auto-inc) deviceID = deviceIDs[deviceAcronym]; // Determine if measurement record already exists if (Convert.ToInt32(command.ExecuteScalar(measurementExistsSql, m_metadataSynchronizationTimeout, database.Guid(signalID))) == 0) { string alternateTag = Guid.NewGuid().ToString(); // Insert new measurement record command.ExecuteNonQuery(insertMeasurementSql, m_metadataSynchronizationTimeout, deviceID, historianID, pointTag, alternateTag, signalTypeIDs[signalTypeAcronym], phasorSourceIndex, sourcePrefix + row.Field<string>("SignalReference"), row.Field<string>("Description") ?? string.Empty, database.Bool(m_internal)); // Guids are normally auto-generated during insert - after insertion update the Guid so that it matches the source data. Most of the database // scripts have triggers that support properly assigning the Guid during an insert, but this code ensures the Guid will always get assigned. command.ExecuteNonQuery(updateMeasurementSignalIDSql, m_metadataSynchronizationTimeout, database.Guid(signalID), alternateTag); } else if (recordNeedsUpdating) { // Update existing measurement record. Note that this update assumes that measurements will remain associated with a static source device. command.ExecuteNonQuery(updateMeasurementSql, m_metadataSynchronizationTimeout, historianID, pointTag, signalTypeIDs[signalTypeAcronym], phasorSourceIndex, sourcePrefix + row.Field<string>("SignalReference"), row.Field<string>("Description") ?? string.Empty, database.Bool(m_internal), database.Guid(signalID)); } } // Periodically notify user about synchronization progress UpdateSyncProgress(); } // Remove any measurement records associated with existing devices in this session but no longer exist in the meta-data if (signalIDs.Count > 0) { // Sort signal ID list so that binary search can be used for quick lookups signalIDs.Sort(); // Query all the guid-based signal ID's for all measurement records associated with the parent device using run-time ID DataTable measurementSignalIDs = command.RetrieveData(database.AdapterType, queryMeasurementSignalIDsSql, m_metadataSynchronizationTimeout, (int)ID); Guid signalID; // Walk through each database record and see if the measurement exists in the provided meta-data foreach (DataRow measurementRow in measurementSignalIDs.Rows) { signalID = database.Guid(measurementRow, "SignalID"); // Remove any measurements in the database that are associated with received devices and do not exist in the meta-data if (signalIDs.BinarySearch(signalID) < 0) { // Measurement was not in the meta-data, get the measurement's actual record based ID for its associated device object measurementDeviceID = command.ExecuteScalar(queryMeasurementDeviceIDSql, m_metadataSynchronizationTimeout, database.Guid(signalID)); // If the unknown measurement is directly associated with a device that exists in the meta-data it is assumed that this measurement // was removed from the publishing system and no longer exists therefore we remove it from the local measurement cache. If the user // needs custom local measurements associated with a remote device, they should be associated with the parent device only. if (measurementDeviceID != null && !(measurementDeviceID is DBNull) && deviceIDs.ContainsValue(Convert.ToInt32(measurementDeviceID))) command.ExecuteNonQuery(deleteMeasurementSql, m_metadataSynchronizationTimeout, database.Guid(signalID)); } } UpdateSyncProgress(); } } // Check to see if data for the "PhasorDetail" table was included in the meta-data if (metadata.Tables.Contains("PhasorDetail")) { Dictionary<int, int> maxSourceIndicies = new Dictionary<int, int>(); int sourceIndex; // Phasor data is normally only needed so that the user can properly generate a mirrored IEEE C37.118 output stream from the source data. // This is necessary since, in this protocol, the phasors are described (i.e., labeled) as a unit (i.e., as a complex number) instead of // as two distinct angle and magnitude measurements. // Define SQL statement to query if phasor record is already defined (no Guid is defined for these simple label records) string phasorExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Phasor WHERE DeviceID = {0} AND SourceIndex = {1}", "deviceID", "sourceIndex"); // Define SQL statement to insert new phasor record string insertPhasorSql = database.ParameterizedQueryString("INSERT INTO Phasor(DeviceID, Label, Type, Phase, SourceIndex) VALUES ({0}, {1}, {2}, {3}, {4})", "deviceID", "label", "type", "phase", "sourceIndex"); // Define SQL statement to update existing phasor record string updatePhasorSql = database.ParameterizedQueryString("UPDATE Phasor SET Label = {0}, Type = {1}, Phase = {2} WHERE DeviceID = {3} AND SourceIndex = {4}", "label", "type", "phase", "deviceID", "sourceIndex"); // Define SQL statement to delete a phasor record string deletePhasorSql = database.ParameterizedQueryString("DELETE FROM Phasor WHERE DeviceID = {0} AND SourceIndex > {1}", "deviceID", "sourceIndex"); foreach (DataRow row in metadata.Tables["PhasorDetail"].Rows) { // Get device acronym deviceAcronym = row.Field<string>("DeviceAcronym") ?? string.Empty; // Make sure we have an associated device already defined for the phasor record if (!string.IsNullOrWhiteSpace(deviceAcronym) && deviceIDs.ContainsKey(deviceAcronym)) { bool recordNeedsUpdating; // Determine if record has changed since last synchronization try { updateTime = Convert.ToDateTime(row["UpdatedOn"]); recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime; if (updateTime > latestUpdateTime) latestUpdateTime = updateTime; } catch { recordNeedsUpdating = true; } deviceID = deviceIDs[deviceAcronym]; // Determine if phasor record already exists if (Convert.ToInt32(command.ExecuteScalar(phasorExistsSql, m_metadataSynchronizationTimeout, deviceID, row.ConvertField<int>("SourceIndex"))) == 0) { // Insert new phasor record command.ExecuteNonQuery(insertPhasorSql, m_metadataSynchronizationTimeout, deviceID, row.Field<string>("Label") ?? "undefined", (row.Field<string>("Type") ?? "V").TruncateLeft(1), (row.Field<string>("Phase") ?? "+").TruncateLeft(1), row.ConvertField<int>("SourceIndex")); } else if (recordNeedsUpdating) { // Update existing phasor record command.ExecuteNonQuery(updatePhasorSql, m_metadataSynchronizationTimeout, row.Field<string>("Label") ?? "undefined", (row.Field<string>("Type") ?? "V").TruncateLeft(1), (row.Field<string>("Phase") ?? "+").TruncateLeft(1), deviceID, row.ConvertField<int>("SourceIndex")); } // Track largest source index for each device maxSourceIndicies.TryGetValue(deviceID, out sourceIndex); if (row.ConvertField<int>("SourceIndex") > sourceIndex) maxSourceIndicies[deviceID] = row.ConvertField<int>("SourceIndex"); } // Periodically notify user about synchronization progress UpdateSyncProgress(); } // Remove any phasor records associated with existing devices in this session but no longer exist in the meta-data if (maxSourceIndicies.Count > 0) { foreach (KeyValuePair<int, int> deviceIndexPair in maxSourceIndicies) { command.ExecuteNonQuery(deletePhasorSql, m_metadataSynchronizationTimeout, deviceIndexPair.Key, deviceIndexPair.Value); } } } if ((object)transaction != null) transaction.Commit(); // Update local in-memory synchronized meta-data cache m_synchronizedMetadata = metadata; } catch (Exception ex) { OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex)); if ((object)transaction != null) { try { transaction.Rollback(); } catch (Exception rollbackException) { OnProcessException(new InvalidOperationException("Failed to roll back database transaction due to exception: " + rollbackException.Message, rollbackException)); } } return; } finally { if ((object)transaction != null) transaction.Dispose(); } } // New signals may have been defined, take original remote signal index cache and apply changes if (m_remoteSignalIndexCache != null) m_signalIndexCache = new SignalIndexCache(DataSource, m_remoteSignalIndexCache); m_lastMetaDataRefreshTime = latestUpdateTime > DateTime.MinValue ? latestUpdateTime : DateTime.UtcNow; OnStatusMessage("Meta-data synchronization completed successfully in {0}", (DateTime.UtcNow.Ticks - startTime).ToElapsedTimeString(2)); // Send notification that system configuration has changed OnConfigurationChanged(); } catch (Exception ex) { OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex)); } finally { // Restart data stream monitor after meta-data synchronization if it was originally enabled if (dataMonitoringEnabled && (object)m_dataStreamMonitor != null) m_dataStreamMonitor.Enabled = true; } }
/// <summary> /// Deletes specified <see cref="OutputStream"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamID">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, int outputStreamID) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Get Acronym of output stream we need to delete and save it DataTable outputStreamAcronym = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT Acronym FROM OutputStream WHERE ID = {0}", "outputStreamID"), DefaultTimeout, outputStreamID); // Delete output stream from database database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStream WHERE ID = {0}", "outputStreamID"), DefaultTimeout, outputStreamID); // Delete statistic measurements from database using the output stream acronym we have just deleted database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Measurement WHERE SignalReference LIKE '" + outputStreamAcronym.Rows[0].Field<string>("Acronym") + "!OS-ST%'"), DefaultTimeout); CommonFunctions.SendCommandToService("ReloadConfig"); return "Output Stream Deleted Successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Removed measurement groups from <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to which measurement groups to be removed.</param> /// <param name="measurementGroupsToBeRemoved">List of <see cref="MeasurementGroup"/> IDs to be removed.</param> /// <returns>string, indicating success for UI display.</returns> public static string RemoveMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeRemoved) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); foreach (int id in measurementGroupsToBeRemoved) { query = database.ParameterizedQueryString("DELETE FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND MeasurementGroupID = {1}", "subscriberID", "measurementGroupID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriberID), id); } return "Measurement groups removed from allowed measurement groups list for subscriber successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
// Static Methods /// <summary> /// Loads <see cref="OutputStream"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="enabledOnly">Boolean flag indicating if only enabled <see cref="OutputStream"/>s needed.</param> /// <param name="sortMember">The field to sort by.</param> /// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param> /// <returns>Collection of <see cref="OutputStream"/>.</returns> public static IList<int> LoadKeys(AdoDataConnection database, bool enabledOnly, string sortMember, string sortDirection) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); IList<int> outputStreamList = new List<int>(); DataTable outputStreamTable; string query; string sortClause = string.Empty; if (!string.IsNullOrEmpty(sortMember)) sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection); if (enabledOnly) { query = database.ParameterizedQueryString(string.Format("SELECT ID FROM OutputStreamDetail WHERE NodeID = {{0}} AND Enabled = {{1}} {0}", sortClause), "nodeID", "enabled"); outputStreamTable = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID(), database.Bool(true)); } else { query = database.ParameterizedQueryString(string.Format("SELECT * FROM OutputStreamDetail WHERE NodeID = {{0}} {0}", sortClause), "nodeID"); outputStreamTable = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID()); } foreach (DataRow row in outputStreamTable.Rows) { outputStreamList.Add(row.ConvertField<int>("ID")); } return outputStreamList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Saves <see cref="OutputStream"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStream">Information about <see cref="OutputStream"/>.</param> /// <param name="mirrorMode">Boolean value to use mirror mode when saving output stream.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, OutputStream outputStream, bool mirrorMode) { bool createdConnection = false; string query; try { OutputStream oldOutputStream; createdConnection = CreateConnection(ref database); if (outputStream.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO OutputStream (NodeID, Acronym, Name, Type, ConnectionString, IDCode, CommandChannel, DataChannel, " + "AutoPublishConfigFrame, AutoStartDataChannel, NominalFrequency, FramesPerSecond, LagTime, LeadTime, UseLocalClockAsRealTime, AllowSortsByArrival, " + "LoadOrder, Enabled, IgnoreBadTimeStamps, TimeResolution, AllowPreemptivePublishing, DownSamplingMethod, DataFormat, CoordinateFormat, " + "CurrentScalingValue, VoltageScalingValue, AnalogScalingValue, DigitalMaskValue, PerformTimeReasonabilityCheck, UpdatedBy, UpdatedOn, " + "CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, " + "{21}, {22}, {23}, {24}, {25}, {26}, {27}, {28}, {29}, {30}, {31}, {32})", "nodeID", "acronym", "name", "type", "connectionString", "idCode", "commandChannel", "dataChannel", "autoPublishConfigFrame", "autoStartDataChannel", "nominalFrequency", "framesPerSecond", "lagTime", "leadTime", "useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps", "timeResolution", "allowPreemptivePublishing", "downSamplingMethod", "dataFormat", "coordinateFormat", "currentScalingValue", "voltageScalingValue", "analogScalingValue", "digitalMaskValue", "performTimeReasonabilityCheck", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, database.CurrentNodeID(), outputStream.Acronym.Replace(" ", "").ToUpper(), outputStream.Name.ToNotNull(), outputStream.Type - 1, outputStream.ConnectionString.ToNotNull(), outputStream.IDCode, outputStream.CommandChannel.ToNotNull(), outputStream.DataChannel.ToNotNull(), database.Bool(outputStream.AutoPublishConfigFrame), database.Bool(outputStream.AutoStartDataChannel), outputStream.NominalFrequency, outputStream.FramesPerSecond, outputStream.LagTime, outputStream.LeadTime, database.Bool(outputStream.UseLocalClockAsRealTime), database.Bool(outputStream.AllowSortsByArrival), outputStream.LoadOrder, database.Bool(outputStream.Enabled), database.Bool(outputStream.IgnoreBadTimeStamps), outputStream.TimeResolution, database.Bool(outputStream.AllowPreemptivePublishing), outputStream.DownSamplingMethod.ToNotNull(), outputStream.DataFormat.ToNotNull(), outputStream.CoordinateFormat.ToNotNull(), outputStream.CurrentScalingValue, outputStream.VoltageScalingValue, outputStream.AnalogScalingValue, outputStream.DigitalMaskValue, database.Bool(outputStream.PerformTimestampReasonabilityCheck), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { oldOutputStream = GetOutputStream(database, " WHERE ID = " + outputStream.ID); query = database.ParameterizedQueryString("UPDATE OutputStream SET NodeID = {0}, Acronym = {1}, Name = {2}, Type = {3}, ConnectionString = {4}, " + "IDCode = {5}, CommandChannel = {6}, DataChannel = {7}, AutoPublishConfigFrame = {8}, AutoStartDataChannel = {9}, NominalFrequency = {10}, " + "FramesPerSecond = {11}, LagTime = {12}, LeadTime = {13}, UseLocalClockAsRealTime = {14}, AllowSortsByArrival = {15}, LoadOrder = {16}, " + "Enabled = {17}, IgnoreBadTimeStamps = {18}, TimeResolution = {19}, AllowPreemptivePublishing = {20}, DownSamplingMethod = {21}, " + "DataFormat = {22}, CoordinateFormat = {23}, CurrentScalingValue = {24}, VoltageScalingValue = {25}, AnalogScalingValue = {26}, " + "DigitalMaskValue = {27}, PerformTimeReasonabilityCheck = {28}, UpdatedBy = {29}, UpdatedOn = {30} WHERE ID = {31}", "nodeID", "acronym", "name", "type", "connectionString", "idCode", "commandChannel", "dataChannel", "autoPublishConfigFrame", "autoStartDataChannel", "nominalFrequency", "framesPerSecond", "lagTime", "leadTime", "useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps", "timeResolution", "allowPreemptivePublishing", "downsamplingMethod", "dataFormat", "coordinateFormat", "currentScalingValue", "voltageScalingValue", "analogScalingValue", "digitalMaskValue", "performTimeReasonabilityCheck", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(outputStream.NodeID), outputStream.Acronym.Replace(" ", "").ToUpper(), outputStream.Name.ToNotNull(), outputStream.Type - 1, outputStream.ConnectionString.ToNotNull(), outputStream.IDCode, outputStream.CommandChannel.ToNotNull(), outputStream.DataChannel.ToNotNull(), database.Bool(outputStream.AutoPublishConfigFrame), database.Bool(outputStream.AutoStartDataChannel), outputStream.NominalFrequency, outputStream.FramesPerSecond, outputStream.LagTime, outputStream.LeadTime, database.Bool(outputStream.UseLocalClockAsRealTime), database.Bool(outputStream.AllowSortsByArrival), outputStream.LoadOrder, database.Bool(outputStream.Enabled), database.Bool(outputStream.IgnoreBadTimeStamps), outputStream.TimeResolution, database.Bool(outputStream.AllowPreemptivePublishing), outputStream.DownSamplingMethod.ToNotNull(), outputStream.DataFormat.ToNotNull(), outputStream.CoordinateFormat.ToNotNull(), outputStream.CurrentScalingValue, outputStream.VoltageScalingValue, outputStream.AnalogScalingValue, outputStream.DigitalMaskValue, database.Bool(outputStream.PerformTimestampReasonabilityCheck), CommonFunctions.CurrentUser, database.UtcNow, outputStream.ID); if (oldOutputStream != null && oldOutputStream.Acronym != outputStream.Acronym.Replace(" ", "").ToUpper()) { ObservableCollection<Measurement> measurementList = Measurement.GetOutputStatisticMeasurements(database, oldOutputStream.Acronym); foreach (Measurement measurement in measurementList) { measurement.SignalReference = measurement.SignalReference.Replace(oldOutputStream.Acronym, outputStream.Acronym.Replace(" ", "").ToUpper()); measurement.PointTag = measurement.PointTag.Replace(oldOutputStream.Acronym, outputStream.Acronym.Replace(" ", "").ToUpper()); measurement.Description = Regex.Replace(measurement.Description, oldOutputStream.Name, outputStream.Name ?? outputStream.Acronym.Replace(" ", "").ToUpper(), RegexOptions.IgnoreCase); Measurement.Save(database, measurement); } SignalType qualityType = SignalType.Load(database).FirstOrDefault(type => type.Acronym == "QUAL"); if ((object)qualityType != null) { IList<int> keys = database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT ID FROM OutputStreamMeasurement WHERE AdapterID = {0}", outputStream.ID)) .Select().Select(row => row.ConvertField<int>("ID")).ToList(); foreach (OutputStreamMeasurement measurement in OutputStreamMeasurement.Load(database, keys)) { if (Regex.IsMatch(measurement.SignalReference, string.Format("{0}-{1}", oldOutputStream.Acronym, qualityType.Suffix))) { measurement.SignalReference = measurement.SignalReference.Replace(oldOutputStream.Acronym, outputStream.Acronym.Replace(" ", "").ToUpper()); OutputStreamMeasurement.Save(database, measurement); } } } } } if (mirrorMode) { // Get ID of the output stream if a new one was inserted above. if (outputStream.ID == 0) outputStream.ID = GetOutputStream(database, " WHERE Acronym = '" + outputStream.Acronym.Replace(" ", "").ToUpper() + "'").ID; IList<int> keys = OutputStreamDevice.LoadKeys(database, outputStream.ID); // Get all existing devices associated with output stream and delete them. ObservableCollection<OutputStreamDevice> outputStreamDevices = OutputStreamDevice.Load(database, keys); foreach (OutputStreamDevice outputStreamDevice in outputStreamDevices) OutputStreamDevice.Delete(database, outputStream.ID, outputStreamDevice.Acronym); if (!string.IsNullOrEmpty(outputStream.MirroringSourceDevice)) { // Get list of input devices, filter by original source = outputstream.MirrorSourceDevice. ObservableCollection<Device> devices = Device.GetDevices(database, "WHERE OriginalSource = '" + outputStream.MirroringSourceDevice + "'"); // Add these above input devices as output stream devices. OutputStreamDevice.AddDevices(database, outputStream.ID, devices, true, true); } } return "Output Stream Information Saved Successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Loads <see cref="OutputStreamDevicePhasor"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="keys">Keys of the measuremnets to be loaded from the database</param> /// <returns>Collection of <see cref="OutputStreamDevicePhasor"/>.</returns> public static ObservableCollection<OutputStreamDevicePhasor> Load(AdoDataConnection database, IList<int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query; string commaSeparatedKeys; OutputStreamDevicePhasor[] outputStreamDevicePhasorList = null; DataTable outputStreamDevicePhasorTable; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2); query = database.ParameterizedQueryString(string.Format("SELECT NodeID, OutputStreamDeviceID, ID, Label, Type, Phase, ScalingValue, LoadOrder " + "FROM OutputStreamDevicePhasor WHERE ID IN ({0})", commaSeparatedKeys)); outputStreamDevicePhasorTable = database.Connection.RetrieveData(database.AdapterType, query); outputStreamDevicePhasorList = new OutputStreamDevicePhasor[outputStreamDevicePhasorTable.Rows.Count]; foreach (DataRow row in outputStreamDevicePhasorTable.Rows) { id = row.ConvertField<int>("ID"); outputStreamDevicePhasorList[keys.IndexOf(id)] = new OutputStreamDevicePhasor() { NodeID = database.Guid(row, "NodeID"), OutputStreamDeviceID = row.ConvertField<int>("OutputStreamDeviceID"), ID = id, Label = row.Field<string>("Label"), Type = row.Field<string>("Type"), Phase = row.Field<string>("Phase"), ScalingValue = row.ConvertField<int>("ScalingValue"), LoadOrder = row.ConvertField<int>("LoadOrder"), m_phaseType = row.Field<string>("Phase") == "+" ? "Positive Sequence" : row.Field<string>("Phase") == "-" ? "Negative Sequence" : row.Field<string>("Phase") == "0" ? "Zero Sequence" : row.Field<string>("Phase") == "A" ? "Phase A" : row.Field<string>("Phase") == "B" ? "Phase B" : "Phase C", m_phasorType = row.Field<string>("Type") == "V" ? "Voltage" : "Current" }; } } return new ObservableCollection<OutputStreamDevicePhasor>(outputStreamDevicePhasorList ?? new OutputStreamDevicePhasor[0]); } finally { if (createdConnection && database != null) database.Dispose(); } }
// Static Methods /// <summary> /// LoadKeys <see cref="Phasor"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="deviceID">ID of the <see cref="Device"/> to filter data.</param> /// <param name="sortMember">The field to sort by.</param> /// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param> /// <returns>Collection of <see cref="Phasor"/>.</returns> public static IList<int> LoadKeys(AdoDataConnection database, int deviceID, string sortMember = "", string sortDirection = "") { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); IList<int> phasorList = new List<int>(); DataTable phasorTable; string query; string sortClause = string.Empty; if (!string.IsNullOrEmpty(sortMember)) sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection); query = database.ParameterizedQueryString(string.Format("SELECT ID From PhasorDetail WHERE DeviceID = {{0}} {0}", sortClause), "deviceID"); phasorTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, deviceID); foreach (DataRow row in phasorTable.Rows) { phasorList.Add(row.ConvertField<int>("ID")); } return phasorList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="OutputStreamDevicePhasor"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDeviceID">ID of the output stream device to filter data.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Label of OutputStreamDevicePhasors defined in the database.</returns> public static Dictionary<int, string> GetLookupList(AdoDataConnection database, int outputStreamDeviceID, bool isOptional = false) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary<int, string> OutputStreamDevicePhasorList = new Dictionary<int, string>(); if (isOptional) OutputStreamDevicePhasorList.Add(0, "Select OutputStreamDevicePhasor"); string query = database.ParameterizedQueryString("SELECT ID, Label FROM OutputStreamDevicePhasor " + "WHERE OutputStreamDeviceID = {0} ORDER BY LoadOrder", "outputStreamDeviceID"); DataTable OutputStreamDevicePhasorTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, outputStreamDeviceID); foreach (DataRow row in OutputStreamDevicePhasorTable.Rows) OutputStreamDevicePhasorList[row.ConvertField<int>("ID")] = row.Field<string>("Label"); return OutputStreamDevicePhasorList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Saves <see cref="Phasor"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="phasor">Information about <see cref="Phasor"/>.</param> /// <param name="oldSourceIndex">The old source index of the phasor.</param> /// <param name="skipMeasurementUpdate">Skips associated measurement update if this is already being handled.</param> /// <returns>String, for display use, indicating success.</returns> public static string SaveAndReorder(AdoDataConnection database, Phasor phasor, int oldSourceIndex, bool skipMeasurementUpdate = false) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (phasor.SourceIndex == 0) phasor.SourceIndex = database.ExecuteScalar<int>("SELECT MAX(SourceIndex) FROM Phasor WHERE DeviceID = {0}", phasor.DeviceID) + 1; // Since phasors could be reordered in the source device, this test could inadvertently throw an exception when it should not - so the validation has been removed //if (database.ExecuteScalar<int>("SELECT COUNT(*) FROM Phasor WHERE ID <> {0} AND DeviceID = {1} AND SourceIndex = {2}", phasor.ID, phasor.DeviceID, phasor.SourceIndex) > 0) // throw new InvalidOperationException("Phasor source index must be unique per device."); if (phasor.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO Phasor (DeviceID, Label, Type, Phase, SourceIndex, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type, phasor.Phase, phasor.SourceIndex, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE Phasor SET DeviceID = {0}, Label = {1}, Type = {2}, Phase = {3}, SourceIndex = {4}, " + "UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type, phasor.Phase, phasor.SourceIndex, CommonFunctions.CurrentUser, database.UtcNow, phasor.ID); } // Get reference to the device to which phasor is being added. Device device = Device.GetDevice(database, "WHERE ID = " + phasor.DeviceID); // Get Phasor signal types. ObservableCollection<SignalType> signals; if (phasor.Type == "V") signals = SignalType.GetVoltagePhasorSignalTypes(); else signals = SignalType.GetCurrentPhasorSignalTypes(); // Get reference to phasor which has just been added. Phasor addedPhasor = GetPhasor(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SourceIndex = " + phasor.SourceIndex); foreach (SignalType signal in signals) { Measurement measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SignalTypeSuffix = '" + signal.Suffix + "' AND PhasorSourceIndex = " + oldSourceIndex); if ((object)measurement == null) { measurement = new Measurement(); measurement.DeviceID = device.ID; measurement.HistorianID = device.HistorianID; measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]); measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex; measurement.SignalTypeID = signal.ID; measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name; measurement.PhasorSourceIndex = addedPhasor.SourceIndex; measurement.Enabled = true; Measurement.Save(database, measurement); } else if (!skipMeasurementUpdate || addedPhasor.SourceIndex != oldSourceIndex) // || measurement.SignalTypeID != signal.ID { // Update existing record when needed or when phasor source index has changed measurement.HistorianID = device.HistorianID; measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]); measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex; measurement.SignalTypeID = signal.ID; measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name; measurement.PhasorSourceIndex = addedPhasor.SourceIndex; Measurement.Save(database, measurement); } } return "Phasor information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Saves <see cref="OutputStreamDevicePhasor"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDevicePhasor">Information about <see cref="OutputStreamDevicePhasor"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, OutputStreamDevicePhasor outputStreamDevicePhasor) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (outputStreamDevicePhasor.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO OutputStreamDevicePhasor (NodeID, OutputStreamDeviceID, Label, Type, Phase, ScalingValue, " + "LoadOrder, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})", "nodeID", "outputStreamDeviceID", "label", "type", "phase", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDevicePhasor.OutputStreamDeviceID, outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue, outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); // PhasorType, PhaseType @phasorName, @phaseType, OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType } else { query = database.ParameterizedQueryString("UPDATE OutputStreamDevicePhasor SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, Type = {3}, " + "Phase = {4}, ScalingValue = {5}, LoadOrder = {6}, UpdatedBy = {7}, UpdatedOn = {8} WHERE ID = {9}", "nodeID", "outputStreamDeviceID", "label", "type", "phase", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamDevicePhasor.NodeID, outputStreamDevicePhasor.OutputStreamDeviceID, outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue, outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, outputStreamDevicePhasor.ID); //PhasorType = @typeName, PhaseType = @PhaseType" OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType, } return "OutputStreamDevicePhasor information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Device"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="protocolType">Type of protocol to filter data.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of devices defined in the database.</returns> public static Dictionary<int, string> GetLookupList(AdoDataConnection database, string protocolType, bool isOptional) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); Dictionary<int, string> deviceList = new Dictionary<int, string>(); DataTable deviceTable; string query; if (isOptional) deviceList.Add(0, "All Device"); query = database.ParameterizedQueryString("SELECT ID, Acronym, IsConcentrator FROM DeviceDetail WHERE NodeID = {0} AND ProtocolType = {1} ORDER BY LoadOrder", "nodeID", "protocolType"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), protocolType); foreach (DataRow row in deviceTable.Rows) { if (row.ConvertField<bool>("IsConcentrator")) { ObservableCollection<Device> devices = GetDevices(database, " WHERE ParentID = " + row.ConvertField<int>("ID")); if (devices != null) { foreach (Device device in devices) deviceList[device.ID] = device.Acronym; } } else { deviceList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym"); } } return deviceList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Loads <see cref="Node"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <returns>Collection of <see cref="Subscriber"/>.</returns> public static ObservableCollection<Subscriber> Load(AdoDataConnection database) { ObservableCollection<Subscriber> subscriberList; DataTable subscriberTable; bool createdConnection = false; string query; SslPolicyErrors validPolicyErrors; X509ChainStatusFlags validChainFlags; try { createdConnection = CreateConnection(ref database); subscriberList = new ObservableCollection<Subscriber>(); query = database.ParameterizedQueryString("SELECT ID, NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile," + " ValidPolicyErrors, ValidChainFlags, AccessControlFilter, Enabled FROM Subscriber WHERE NodeID = {0} ORDER BY Name", "nodeID"); subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in subscriberTable.Rows) { subscriberList.Add(new Subscriber() { ID = database.Guid(row, "ID"), NodeID = database.Guid(row, "NodeID"), Acronym = row.Field<string>("Acronym"), Name = row.Field<string>("Name"), SharedSecret = row.Field<string>("SharedSecret"), AuthKey = row.Field<string>("AuthKey"), ValidIPAddresses = row.Field<string>("ValidIPAddresses"), RemoteCertificateFile = row.Field<string>("RemoteCertificateFile"), ValidPolicyErrors = Enum.TryParse(row.Field<string>("ValidPolicyErrors"), out validPolicyErrors) ? validPolicyErrors : (SslPolicyErrors?)null, ValidChainFlags = Enum.TryParse(row.Field<string>("ValidChainFlags"), out validChainFlags) ? validChainFlags : (X509ChainStatusFlags?)null, AccessControlFilter = row.Field<string>("AccessControlFilter"), Enabled = Convert.ToBoolean(row.Field<object>("Enabled")), AllowedMeasurementGroups = GetAllowedMeasurementGroups(database, database.Guid(row, "ID")), DeniedMeasurementGroups = GetDeniedMeasurementGroups(database, database.Guid(row, "ID")), AvailableMeasurementGroups = GetAvailableMeasurementGroups(database, database.Guid(row, "ID")), StatusColor = "gray", Version = "" }); } return subscriberList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> denied for <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns> public static Dictionary<int, string> GetDeniedMeasurementGroups(AdoDataConnection database, Guid subscriberID) { Dictionary<int, string> deniedMeasurementGroups; DataTable deniedMeasurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); deniedMeasurementGroups = new Dictionary<int, string>(); query = database.ParameterizedQueryString("SELECT MeasurementGroupID, MeasurementGroupName FROM SubscriberMeasGroupDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY MeasurementGroupName", "subscriberID", "allowed"); deniedMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(false)); foreach (DataRow row in deniedMeasurementGroupTable.Rows) deniedMeasurementGroups[row.ConvertField<int>("MeasurementGroupID")] = row.Field<string>("MeasurementGroupName"); return deniedMeasurementGroups; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Saves <see cref="Device"/> information to database along with analogs and digital measurements if requested.. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="device">Information about <see cref="Device"/>.</param> /// <param name="notifyService">Boolean value to notify service if needed.</param> /// <param name="digitalCount">Number of digital measurements to add.</param> /// <param name="analogCount">Number of analog measurements to add.</param> /// <param name="digitalLabels">Collection of digital labels associated with a device in configuration frame.</param> /// <param name="analogLabels">Collection of analog labels associated with a device in configuration frame.</param> /// <returns>String, for display use, indicating success.</returns> public static string SaveWithAnalogsDigitals(AdoDataConnection database, Device device, bool notifyService, int digitalCount, int analogCount, List<string> digitalLabels = null, List<string> analogLabels = null) { bool createdConnection = false; string query; try { Device oldDevice = null; createdConnection = CreateConnection(ref database); object nodeID; if (device.NodeID == Guid.Empty) nodeID = database.CurrentNodeID(); else nodeID = database.Guid(device.NodeID); if (device.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO Device (NodeID, ParentID, UniqueID, Acronym, Name, IsConcentrator, CompanyID, HistorianID, AccessID, VendorDeviceID, " + "ProtocolID, Longitude, Latitude, InterconnectionID, ConnectionString, TimeZone, FramesPerSecond, TimeAdjustmentTicks, DataLossInterval, ContactList, " + "MeasuredLines, LoadOrder, Enabled, AllowedParsingExceptions, ParsingExceptionWindow, DelayedConnectionInterval, AllowUseOfCachedConfiguration, " + "AutoStartDataParsingSequence, SkipDisableRealTimeData, MeasurementReportingInterval, ConnectOndemand, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values " + "({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, " + "{26}, {27}, {28}, {29}, {30}, {31}, {32}, {33}, {34})", "nodeID", "parentID", "uniqueID", "acronym", "name", "isConcentrator", "companyID", "historianID", "accessID", "vendorDeviceID", "protocolID", "longitude", "latitude", "interconnectionID", "connectionString", "timezone", "framesPerSecond", "timeAdjustmentTicks", "dataLossInterval", "contactList", "measuredLines", "loadOrder", "enabled", "allowedParsingExceptions", "parsingExceptionWindow", "delayedConnectionInterval", "allowUseOfCachedConfiguration", "autoStartDataParsingSequence", "skipDisableRealTimeData", "measurementReportingInterval", "connectOndemand", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, nodeID, device.ParentID.ToNotNull(), database.Guid(Guid.NewGuid()), device.Acronym.Replace(" ", "").ToUpper(), device.Name.ToNotNull(), database.Bool(device.IsConcentrator), device.CompanyID.ToNotNull(), device.HistorianID.ToNotNull(), device.AccessID, device.VendorDeviceID.ToNotNull(), device.ProtocolID.ToNotNull(), device.Longitude.ToNotNull(), device.Latitude.ToNotNull(), device.InterconnectionID.ToNotNull(), BuildConnectionString(device), device.TimeZone.ToNotNull(), device.FramesPerSecond ?? 30, device.TimeAdjustmentTicks, device.DataLossInterval, device.ContactList.ToNotNull(), device.MeasuredLines.ToNotNull(), device.LoadOrder, database.Bool(device.Enabled), device.AllowedParsingExceptions, device.ParsingExceptionWindow, device.DelayedConnectionInterval, database.Bool(device.AllowUseOfCachedConfiguration), database.Bool(device.AutoStartDataParsingSequence), database.Bool(device.SkipDisableRealTimeData), device.MeasurementReportingInterval, database.Bool(device.ConnectOnDemand), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { oldDevice = GetDevice(database, " WHERE ID = " + device.ID); query = database.ParameterizedQueryString("UPDATE Device SET NodeID = {0}, ParentID = {1}, UniqueID = {2}, Acronym = {3}, Name = {4}, " + "IsConcentrator = {5}, CompanyID = {6}, HistorianID = {7}, AccessID = {8}, VendorDeviceID = {9}, ProtocolID = {10}, Longitude = {11}, " + "Latitude = {12}, InterconnectionID = {13}, ConnectionString = {14}, TimeZone = {15}, FramesPerSecond = {16}, TimeAdjustmentTicks = {17}, " + "DataLossInterval = {18}, ContactList = {19}, MeasuredLines = {20}, LoadOrder = {21}, Enabled = {22}, AllowedParsingExceptions = {23}, " + "ParsingExceptionWindow = {24}, DelayedConnectionInterval = {25}, AllowUseOfCachedConfiguration = {26}, AutoStartDataParsingSequence = {27}, " + "SkipDisableRealTimeData = {28}, MeasurementReportingInterval = {29}, ConnectOnDemand = {30}, UpdatedBy = {31}, UpdatedOn = {32} WHERE ID = {33}", "nodeID", "parentID", "uniqueID", "acronym", "name", "isConcentrator", "companyID", "historianID", "accessID", "vendorDeviceID", "protocolID", "longitude", "latitude", "interconnectionID", "connectionString", "timezone", "framesPerSecond", "timeAdjustmentTicks", "dataLossInterval", "contactList", "measuredLines", "loadOrder", "enabled", "allowedParsingExceptions", "parsingExceptionWindow", "delayedConnectionInterval", "allowUseOfCachedConfiguration", "autoStartDataParsingSequence", "skipDisableRealTimeData", "measurementReportingInterval", "connectOnDemand", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, nodeID, device.ParentID.ToNotNull(), database.Guid(device.UniqueID), device.Acronym.Replace(" ", "").ToUpper(), device.Name.ToNotNull(), database.Bool(device.IsConcentrator), device.CompanyID.ToNotNull(), device.HistorianID.ToNotNull(), device.AccessID, device.VendorDeviceID.ToNotNull(), device.ProtocolID.ToNotNull(), device.Longitude.ToNotNull(), device.Latitude.ToNotNull(), device.InterconnectionID.ToNotNull(), BuildConnectionString(device), device.TimeZone.ToNotNull(), device.FramesPerSecond ?? 30, device.TimeAdjustmentTicks, device.DataLossInterval, device.ContactList.ToNotNull(), device.MeasuredLines.ToNotNull(), device.LoadOrder, database.Bool(device.Enabled), device.AllowedParsingExceptions, device.ParsingExceptionWindow, device.DelayedConnectionInterval, database.Bool(device.AllowUseOfCachedConfiguration), database.Bool(device.AutoStartDataParsingSequence), database.Bool(device.SkipDisableRealTimeData), device.MeasurementReportingInterval, database.Bool(device.ConnectOnDemand), CommonFunctions.CurrentUser, database.UtcNow, device.ID); } Device savedDevice = GetDevice(database, "WHERE Acronym = '" + device.Acronym.Replace(" ", "").ToUpper() + "'"); if ((object)savedDevice == null) return "Device information saved successfully but failed to create measurements"; // Determine if device is using a phasor protocol bool deviceIsUsingPhasorProtocol = (string.Compare(savedDevice.ProtocolCategory, "Phasor", StringComparison.OrdinalIgnoreCase) == 0); // Add default measurements for non-concentrator devices when device protocol category is Phasor if (!savedDevice.IsConcentrator && deviceIsUsingPhasorProtocol) { // Setup and/or validate default signals associated with non-concentrator devices (e.g., directly connected PMUs or PMUs in a concentrator) foreach (TimeSeries.UI.DataModels.SignalType signal in TimeSeries.UI.DataModels.SignalType.GetPmuSignalTypes()) { Measurement measurement; if (signal.Suffix == "AV" && analogCount > 0) { for (int i = 1; i <= analogCount; i++) { measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + savedDevice.ID + " AND SignalReference = '" + savedDevice.Acronym + "-AV" + i + "'"); if ((object)measurement == null) { measurement = new Measurement(); measurement.DeviceID = savedDevice.ID; measurement.HistorianID = savedDevice.HistorianID; measurement.PointTag = CommonPhasorServices.CreatePointTag(savedDevice.CompanyAcronym, savedDevice.Acronym, savedDevice.VendorAcronym, "ALOG", i); measurement.SignalReference = savedDevice.Acronym + "-AV" + i; measurement.Description = savedDevice.Name + (string.IsNullOrWhiteSpace(savedDevice.VendorDeviceName) ? "" : " " + savedDevice.VendorDeviceName) + " Analog Value " + i; measurement.SignalTypeID = signal.ID; measurement.PhasorSourceIndex = (int?)null; measurement.Enabled = true; if ((object)analogLabels != null && (object)analogLabels[i - 1] != null) measurement.AlternateTag = analogLabels[i - 1]; Measurement.Save(database, measurement); } else if (measurement.SignalTypeID != signal.ID) { // Correct signal type if it has been changed measurement.SignalTypeID = signal.ID; Measurement.Save(database, measurement); } } } else if (signal.Suffix == "DV" && digitalCount > 0) { for (int i = 1; i <= digitalCount; i++) { measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + savedDevice.ID + " AND SignalReference = '" + savedDevice.Acronym + "-DV" + i + "'"); if ((object)measurement == null) { measurement = new Measurement(); measurement.DeviceID = savedDevice.ID; measurement.HistorianID = savedDevice.HistorianID; measurement.PointTag = CommonPhasorServices.CreatePointTag(savedDevice.CompanyAcronym, savedDevice.Acronym, savedDevice.VendorAcronym, "DIGI", i); measurement.SignalReference = savedDevice.Acronym + "-DV" + i; measurement.SignalTypeID = signal.ID; measurement.Description = savedDevice.Name + (string.IsNullOrWhiteSpace(savedDevice.VendorDeviceName) ? "" : " " + savedDevice.VendorDeviceName) + " Digital Value " + i; measurement.PhasorSourceIndex = (int?)null; measurement.Enabled = true; if ((object)digitalLabels != null && (object)digitalLabels[i - 1] != null) measurement.AlternateTag = digitalLabels[i - 1]; Measurement.Save(database, measurement); } else if (measurement.SignalTypeID != signal.ID) { // Correct signal type if it has been changed measurement.SignalTypeID = signal.ID; Measurement.Save(database, measurement); } } } else if (signal.Suffix == "FQ" || signal.Suffix == "DF" || signal.Suffix == "SF") { measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + savedDevice.ID + " AND SignalTypeSuffix = '" + signal.Suffix + "'"); if ((object)measurement == null) { measurement = new Measurement(); measurement.DeviceID = savedDevice.ID; measurement.HistorianID = savedDevice.HistorianID; measurement.PointTag = CommonPhasorServices.CreatePointTag(savedDevice.CompanyAcronym, savedDevice.Acronym, savedDevice.VendorAcronym, signal.Acronym); measurement.SignalReference = savedDevice.Acronym + "-" + signal.Suffix; measurement.SignalTypeID = signal.ID; measurement.Description = savedDevice.Name + (string.IsNullOrWhiteSpace(savedDevice.VendorDeviceName) ? "" : " " + savedDevice.VendorDeviceName) + " " + signal.Name; measurement.PhasorSourceIndex = (int?)null; measurement.Enabled = true; Measurement.Save(database, measurement); } // Based on query filter of SignalTypeSuffix, the following will never be true //else if (measurement.SignalTypeID != signal.ID) //{ // // Correct signal type if it has been changed // measurement.SignalTypeID = signal.ID; // Measurement.Save(database, measurement); //} } } } if (device.ID > 0) { if (!device.IsConcentrator) { // For existing non-concentrator devices, call Save on each phasor so that measurements related to those phasors will reflect possible device changes. IList<int> keys = Phasor.LoadKeys(database, device.ID); foreach (Phasor phasor in Phasor.Load(database, keys)) { Phasor.SaveWithoutMeasurementUpdate(database, phasor); } } // Update existing device measurements to reflect possible device changes if ((object)oldDevice != null) { bool companyUpdated = (deviceIsUsingPhasorProtocol && savedDevice.CompanyID != oldDevice.CompanyID); bool deviceRenamed = (deviceIsUsingPhasorProtocol && (string.CompareOrdinal(savedDevice.Acronym, oldDevice.Acronym) != 0 || string.CompareOrdinal(savedDevice.Name, oldDevice.Name) != 0)); bool historianUpdated = (savedDevice.HistorianID != oldDevice.HistorianID); if (companyUpdated || deviceRenamed || historianUpdated) { string companyAcronym = ""; int underScoreIndex; if (companyUpdated) { if (savedDevice.CompanyID.HasValue && !string.IsNullOrWhiteSpace(savedDevice.CompanyAcronym)) companyAcronym = savedDevice.CompanyAcronym; } foreach (Measurement measurement in Measurement.GetMeasurements(database, "WHERE DeviceID = " + oldDevice.ID)) { if (companyUpdated) { // WARNING: This assumes company name is followed by an underscore - this may not be a valid assumption for custom point tag naming conventions underScoreIndex = measurement.PointTag.ToNonNullString().IndexOf('_'); if (underScoreIndex > -1) measurement.PointTag = companyAcronym + measurement.PointTag.Substring(underScoreIndex); } if (deviceRenamed) { measurement.PointTag = measurement.PointTag.Replace(oldDevice.Acronym, savedDevice.Acronym); measurement.SignalReference = measurement.SignalReference.Replace(oldDevice.Acronym, savedDevice.Acronym); measurement.Description = Regex.Replace(measurement.Description.ToNonNullString(), oldDevice.Name, savedDevice.Name, RegexOptions.IgnoreCase); } if (historianUpdated && string.Compare(measurement.HistorianAcronym, "STAT", StringComparison.OrdinalIgnoreCase) != 0) measurement.HistorianID = savedDevice.HistorianID; Measurement.Save(database, measurement); } } // If changing the historian for a concentrator style device - must assume desire to change historian for all children devices if (historianUpdated && device.IsConcentrator) { foreach (Device childDevice in GetDevices(database, "WHERE ParentID = " + device.ID)) { // Recursively call this function for each child device with updated historian which will also fix measurement's historian childDevice.HistorianID = savedDevice.HistorianID; SaveWithAnalogsDigitals(database, childDevice, false, 0, 0); } } } } try { // Notify service about configuration changes made here. if (notifyService) NotifyService(savedDevice); } catch (Exception ex) { return "Device information saved successfully. Failed to send Initialize command to backend service." + Environment.NewLine + ex.Message; } return "Device information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Subscriber"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of subscribers defined in the database.</returns> public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false) { Dictionary<Guid, string> subscriberList; DataTable subscriberTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); subscriberList = new Dictionary<Guid, string>(); if (isOptional) subscriberList.Add(Guid.Empty, "Select Subscriber"); query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Subscriber WHERE Enabled = {0} AND NodeID = {1} ORDER BY Name", "enabled", "nodeID"); subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID()); foreach (DataRow row in subscriberTable.Rows) { subscriberList[database.Guid(row, "ID")] = row.Field<string>("Acronym"); } return subscriberList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Deletes specified <see cref="Device"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="device">Device to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, Device device) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); // Does not delete the Parent Device database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE Device SET ParentID = null WHERE ParentID = {0}", "OldParentID", "NewParentID"), DefaultTimeout, device.ID); // Deletes the Parent Device //database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Device WHERE ParentID = {0}", "ParentID"), DefaultTimeout, device.ID); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Device WHERE ID = {0}", "deviceID"), DefaultTimeout, device.ID); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevice WHERE Acronym = {0}", "deviceAcronym"), DefaultTimeout, device.Acronym); return "Device deleted successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Deletes specified <see cref="Subscriber"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="id">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, Guid id) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Subscriber WHERE ID = {0}", "id"), DefaultTimeout, database.Guid(id)); try { CommonFunctions.SendCommandToService("ReloadConfig"); } catch (Exception ex) { return "Subscriber deleted successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message; } return "Subscriber deleted successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Retrieves devices for output stream. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamID">ID of the output stream to filter out devices that already exist.</param> /// <returns>Collection of <see cref="Device"/>.</returns> public static ObservableCollection<Device> GetNewDevicesForOutputStream(AdoDataConnection database, int outputStreamID) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); ObservableCollection<Device> deviceList = new ObservableCollection<Device>(); DataTable deviceTable; string query; // Note that OleDB does not support parameterized sub-query. if (database.DatabaseType == DatabaseType.Access) { query = database.ParameterizedQueryString("SELECT * FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Acronym NOT IN " + "(SELECT Acronym FROM OutputStreamDevice WHERE AdapterID = " + outputStreamID + ") ORDER BY Acronym", "nodeID", "isConcentrator"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), database.Bool(false)); } else { query = database.ParameterizedQueryString("SELECT * FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Acronym NOT IN " + "(SELECT Acronym FROM OutputStreamDevice WHERE AdapterID = {2}) ORDER BY Acronym", "nodeID", "isConcentrator", "adapterID"); deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), database.Bool(false), outputStreamID); } foreach (DataRow row in deviceTable.Rows) { deviceList.Add(new Device { NodeID = database.Guid(row, "NodeID"), ID = row.ConvertField<int>("ID"), ParentID = row.ConvertNullableField<int>("ParentID"), UniqueID = database.Guid(row, "UniqueID"), Acronym = row.Field<string>("Acronym"), Name = row.Field<string>("Name"), IsConcentrator = Convert.ToBoolean(row.Field<object>("IsConcentrator")), CompanyID = row.ConvertNullableField<int>("CompanyID"), HistorianID = row.ConvertNullableField<int>("HistorianID"), AccessID = row.ConvertField<int>("AccessID"), VendorDeviceID = row.ConvertNullableField<int>("VendorDeviceID"), ProtocolID = row.ConvertNullableField<int>("ProtocolID"), Longitude = row.ConvertNullableField<decimal>("Longitude"), Latitude = row.ConvertNullableField<decimal>("Latitude"), InterconnectionID = row.ConvertNullableField<int>("InterconnectionID"), ConnectionString = ParseConnectionString(row.Field<string>("ConnectionString").ToNonNullString()), AlternateCommandChannel = ParseAlternateCommand(row.Field<string>("ConnectionString").ToNonNullString()), TimeZone = row.Field<string>("TimeZone"), FramesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30), TimeAdjustmentTicks = Convert.ToInt64(row.Field<object>("TimeAdjustmentTicks")), DataLossInterval = row.ConvertField<double>("DataLossInterval"), ContactList = row.Field<string>("ContactList"), MeasuredLines = row.ConvertNullableField<int>("MeasuredLines"), LoadOrder = row.ConvertField<int>("LoadOrder"), Enabled = false, // We will use enable flag for check boxes on output stream device wizard so that we do not need to add selected flag. CreatedOn = row.Field<DateTime>("CreatedOn"), AllowedParsingExceptions = Convert.ToInt32(row.Field<object>("AllowedParsingExceptions")), ParsingExceptionWindow = row.ConvertField<double>("ParsingExceptionWindow"), DelayedConnectionInterval = row.ConvertField<double>("DelayedConnectionInterval"), AllowUseOfCachedConfiguration = Convert.ToBoolean(row.Field<object>("AllowUseOfCachedConfiguration")), AutoStartDataParsingSequence = Convert.ToBoolean(row.Field<object>("AutoStartDataParsingSequence")), SkipDisableRealTimeData = Convert.ToBoolean(row.Field<object>("SkipDisableRealTimeData")), MeasurementReportingInterval = Convert.ToInt32(row.Field<object>("MeasurementReportingInterval")), ConnectOnDemand = Convert.ToBoolean(row.Field<object>("ConnectOnDemand")), m_companyName = row.Field<string>("CompanyName"), m_companyAcronym = row.Field<string>("CompanyAcronym"), m_historianAcronym = row.Field<string>("HistorianAcronym"), m_vendorDeviceName = row.Field<string>("VendorDeviceName"), m_vendorAcronym = row.Field<string>("VendorAcronym"), m_protocolName = row.Field<string>("ProtocolName"), m_protocolCategory = row.Field<string>("Category"), m_interconnectionName = row.Field<string>("InterconnectionName"), m_nodeName = row.Field<string>("NodeName"), m_parentAcronym = row.Field<string>("ParentAcronym"), m_originalSource = row.Field<string>("OriginalSource") }); } return deviceList; } finally { if (createdConnection && database != null) database.Dispose(); } }