/// <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();
}
}
}
public void RemoveSubscribedMeasurements(ICollection <Guid> measurementIDs)
{
ObservableCollection <int> deviceIDsForMeasurementsToBeAdded = new ObservableCollection <int>();
ObservableCollection <DataModelMeasurement> measurementsToBeRemoved;
//If All Devices is not selected on the screen and a specific device is selected then we will just initialize that device.
//Otherwise, maintain a list of unique device ids for which measurements are being subscribed and initialize all of them.
if (CurrentDevice.Key > 0)
{
deviceIDsForMeasurementsToBeAdded.Add(CurrentDevice.Key);
}
if (measurementIDs != null && measurementIDs.Count > 0)
{
Mouse.OverrideCursor = Cursors.Wait;
AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory);
try
{
measurementsToBeRemoved = DataModelMeasurement.LoadFromKeys(database, measurementIDs.ToList());
foreach (DataModelMeasurement measurement in measurementsToBeRemoved)
{
measurement.Subscribed = false;
DataModelMeasurement.Save(database, measurement);
if (CurrentDevice.Key == 0 && measurement.DeviceID != null)
{
if (!deviceIDsForMeasurementsToBeAdded.Contains((int)measurement.DeviceID))
{
deviceIDsForMeasurementsToBeAdded.Add((int)measurement.DeviceID);
}
}
}
if (SubscriptionChanged != null)
{
SubscriptionChanged(this, null);
}
if (deviceIDsForMeasurementsToBeAdded.Count > 0)
{
ThreadPool.QueueUserWorkItem(InitializeDeviceConnection, deviceIDsForMeasurementsToBeAdded);
}
}
catch (Exception ex)
{
Popup("ERROR: " + ex.Message, "Unsubscribe Measurements", MessageBoxImage.Error);
}
finally
{
if (database != null)
{
database.Dispose();
}
Mouse.OverrideCursor = null;
}
}
}
/// <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 int RemoveMeasurementGroups(AdoDataConnection database, Guid subscriberID, List <int> measurementGroupsToBeRemoved)
{
const string QueryFormat = "DELETE FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND MeasurementGroupID = {1}";
bool createdConnection = false;
int rowsAffected = 0;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeRemoved)
{
rowsAffected += database.ExecuteNonQuery(DefaultTimeout, QueryFormat, subscriberID, id);
}
return(rowsAffected);
}
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="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();
}
}
}
// Static Methods
/// <summary>
/// LoadKeys <see cref="Vendor"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</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 vendor IDs.</returns>
public static IList <int> LoadKeys(AdoDataConnection database, string sortMember, string sortDirection)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList <int> vendorList = new List <int>();
DataTable vendorTable;
string sortClause = string.Empty;
if (!string.IsNullOrEmpty(sortMember))
{
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
}
// check the query once again , Does it have to be details or somethng else
vendorTable = database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT ID From VendorDetail {0}", sortClause));
foreach (DataRow row in vendorTable.Rows)
{
vendorList.Add(row.ConvertField <int>("ID"));
}
return(vendorList);
}
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>
/// 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="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>
/// 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>
/// 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();
}
}
}
/// <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();
}
}
}
// Static Methods
/// <summary>
/// LoadKeys <see cref="CalculatedMeasurement"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</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="CalculatedMeasurement"/>.</returns>
public static IList <int> LoadKeys(AdoDataConnection database, string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList <int> calculatedMeasurementList = new List <int>();
DataTable calculatedMeasurementTable;
string sortClause = string.Empty;
if (!string.IsNullOrEmpty(sortMember))
{
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
}
calculatedMeasurementTable = database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT ID From CalculatedMeasurementDetail {0}", sortClause));
foreach (DataRow row in calculatedMeasurementTable.Rows)
{
calculatedMeasurementList.Add((row.ConvertField <int>("ID")));
}
return(calculatedMeasurementList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves collection of <see cref="UserAccount"/>s currently 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 assigned to <see cref="SecurityGroup"/>.</returns>
public static Dictionary <Guid, string> GetCurrentUsers(AdoDataConnection database, Guid groupID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <Guid, string> currentUsers = new Dictionary <Guid, string>();
DataTable currentUsersTable = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT * FROM SecurityGroupUserAccountDetail WHERE SecurityGroupID = {0} ORDER BY UserName", "groupID"), database.Guid(groupID));
foreach (DataRow row in currentUsersTable.Rows)
{
currentUsers[database.Guid(row, "UserAccountID")] = UserInfo.SIDToAccountName(row.Field <string>("UserName"));
}
return(currentUsers);
}
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 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>
/// 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="SecurityGroup"/> 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 security groups defined in the database.</returns>
public static Dictionary <int, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <int, string> securityGroupList = new Dictionary <int, string>();
if (isOptional)
{
securityGroupList.Add(0, "Select Security Group");
}
DataTable securityGroupTable = database.Connection.RetrieveData(database.AdapterType, "SELECT ID, Name FROM SecurityGroup ORDER BY Name");
foreach (DataRow row in securityGroupTable.Rows)
{
securityGroupList[row.ConvertField <int>("ID")] = UserInfo.SIDToAccountName(row.Field <string>("Name"));
}
return(securityGroupList);
}
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 int AddMeasurementGroups(AdoDataConnection database, Guid subscriberID, List <int> measurementGroupsToBeAdded, bool allowed)
{
const string QueryFormat =
"INSERT INTO SubscriberMeasurementGroup (NodeID, SubscriberID, MeasurementGroupID, Allowed, UpdatedOn, UpdatedBy, CreatedOn, CreatedBy) " +
"SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7} " +
"WHERE (SELECT COUNT(*) FROM SubscriberMeasurementGroup WHERE SubscriberID = {1} AND MeasurementGroupID = {2}) = 0";
bool createdConnection = false;
int rowsAffected = 0;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeAdded)
{
rowsAffected += database.ExecuteNonQuery(DefaultTimeout, QueryFormat, CommonFunctions.CurrentNodeID(), subscriberID, id, allowed,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser);
}
return(rowsAffected);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Loads protocol list from database.
/// </summary>
/// <param name="database">ADO database connection.</param>
/// <returns>List of protocols.</returns>
public static ObservableCollection <Protocol> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection <Protocol> protocolList = new ObservableCollection <Protocol>();
DataTable protocolTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM Protocol ORDER BY LoadOrder");
foreach (DataRow row in protocolTable.Rows)
{
protocolList.Add(new Protocol()
{
ID = row.ConvertField <int>("ID"),
Acronym = row.Field <string>("Acronym"),
Name = row.Field <string>("Name"),
Type = row.Field <string>("Type"),
Category = row.Field <string>("Category"),
AssemblyName = row.Field <string>("AssemblyName"),
TypeName = row.Field <string>("TypeName"),
LoadOrder = row.ConvertField <int>("LoadOrder")
});
}
return(protocolList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Vendor"/> 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 vendors defined in the database.</returns>
public static Dictionary <int, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <int, string> vendorList = new Dictionary <int, string>();
if (isOptional)
{
vendorList.Add(0, "Select Vendor");
}
DataTable vendorTable = database.Connection.RetrieveData(database.AdapterType, "SELECT ID, Name FROM Vendor ORDER BY Name");
foreach (DataRow row in vendorTable.Rows)
{
vendorList[row.ConvertField <int>("ID")] = row.Field <string>("Name");
}
return(vendorList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <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();
}
}
}
// Static Methods
/// <summary>
/// Loads <see cref="Node"/> IDs as an <see cref="IList{T}"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</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="Guid"/>.</returns>
public static IList <Guid> LoadKeys(AdoDataConnection database, string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList <Guid> nodeList = new List <Guid>();
string sortClause = string.Empty;
DataTable nodeTable;
if (!string.IsNullOrEmpty(sortMember))
{
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
}
nodeTable = database.Connection.RetrieveData(database.AdapterType, string.Format("Select ID From NodeDetail {0}", sortClause));
foreach (DataRow row in nodeTable.Rows)
{
nodeList.Add(database.Guid(row, "ID"));
}
return(nodeList);
}
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>
/// 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();
}
}
}
// Static Methods
/// <summary>
/// LoadKeys <see cref="OutputStreamDevice"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamID">ID of the OutputStream 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 outputStreamID, string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList <int> outputStreamDeviceList = new List <int>();
DataTable outputStreamDeviceTable;
string sortClause = string.Empty;
if (!string.IsNullOrEmpty(sortMember))
{
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
}
outputStreamDeviceTable = database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT ID From OutputStreamDeviceDetail WHERE AdapterID = {1} {0}", sortClause, outputStreamID));
foreach (DataRow row in outputStreamDeviceTable.Rows)
{
outputStreamDeviceList.Add(row.ConvertField <int>("ID"));
}
return(outputStreamDeviceList);
}
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>
/// 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>
/// 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>
/// 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>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="SignalType"/> 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>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> signalTypeList = new Dictionary<int, string>();
DataTable signalTypeTable;
if (isOptional)
signalTypeList.Add(0, "Select SignalType");
signalTypeTable = database.Connection.RetrieveData(database.AdapterType, "SELECT ID, Name FROM SignalType ORDER BY Name");
foreach (DataRow row in signalTypeTable.Rows)
signalTypeList[row.ConvertField<int>("ID")] = row.Field<string>("Name");
return signalTypeList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="SignalType"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="source">Type if source to filter data.</param>
/// <param name="phasorType">Type of phasor type to filter data.</param>
/// <returns>Collection of <see cref="SignalType"/>.</returns>
public static ObservableCollection<SignalType> Load(AdoDataConnection database, string source = "", string phasorType = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<SignalType> signalTypeList = new ObservableCollection<SignalType>();
string query = "SELECT ID, Acronym, Name, Suffix, Abbreviation, Source, EngineeringUnits FROM SignalType ORDER BY Name";
if (!string.IsNullOrEmpty(source) && source.ToUpper() == "PMU")
{
query = "SELECT ID, Acronym, Name, Suffix, Abbreviation, Source, EngineeringUnits FROM SignalType Where Source = 'PMU' AND SUFFIX IN ('FQ','DF','SF','AV','DV','CV') ORDER BY Name";
}
else if (!string.IsNullOrEmpty(source) && source.ToUpper() == "PHASOR" && !string.IsNullOrEmpty(phasorType))
{
if (phasorType.ToUpper() == "V")
query = "SELECT ID, Acronym, Name, Suffix, Abbreviation, Source, EngineeringUnits FROM SignalType Where Source = 'Phasor' " +
"AND Acronym IN ('VPHM', 'VPHA') ORDER BY Name";
else if (phasorType.ToUpper() == "I")
query = "SELECT ID, Acronym, Name, Suffix, Abbreviation, Source, EngineeringUnits FROM SignalType Where Source = 'Phasor' " +
"AND Acronym IN ('IPHM', 'IPHA') ORDER BY Name";
}
DataTable signalTypeTable = database.Connection.RetrieveData(database.AdapterType, query);
foreach (DataRow row in signalTypeTable.Rows)
{
signalTypeList.Add(new SignalType()
{
ID = row.ConvertField<int>("ID"),
Acronym = row.Field<string>("Acronym"),
Name = row.Field<string>("Name"),
Suffix = row.Field<string>("Suffix"),
Abbreviation = row.Field<string>("Abbreviation"),
Source = row.Field<string>("Source"),
EngineeringUnits = row["EngineeringUnits"].ToNonNullString()
});
}
return signalTypeList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
private static void GetDeleteMeasurementDetails(AdoDataConnection database, int outputStreamDevicePhasorID, out string angleSignalReference, out string magnitudeSignalReference, out int adapterID, out int outputStreamDeviceID)
{
const string outputPhasorFormat = "SELECT Label, OutputStreamDeviceID FROM OutputStreamDevicePhasor WHERE ID = {0}";
const string outputDeviceFormat = "SELECT Acronym, AdapterID FROM OutputStreamDevice WHERE ID = {0}";
const string measurementDetailFormat = "SELECT PointTag FROM MeasurementDetail WHERE DeviceAcronym = '{0}' AND PhasorLabel = '{1}' AND SignalTypeSuffix = '{2}'";
const string outputMeasurementDetailFormat = "SELECT SignalReference FROM OutputStreamMeasurementDetail WHERE SourcePointTag = '{0}'";
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataRow outputPhasorRecord;
DataRow outputDeviceRecord;
string labelName;
string deviceName;
string anglePointTag;
string magnitudePointTag;
outputPhasorRecord = database.Connection.RetrieveData(database.AdapterType, string.Format(outputPhasorFormat, outputStreamDevicePhasorID)).Rows[0];
labelName = outputPhasorRecord.Field<string>("Label");
outputStreamDeviceID = outputPhasorRecord.ConvertField<int>("OutputStreamDeviceID");
outputDeviceRecord = database.Connection.RetrieveData(database.AdapterType, string.Format(outputDeviceFormat, outputStreamDeviceID)).Rows[0];
deviceName = outputDeviceRecord.Field<string>("Acronym");
adapterID = outputDeviceRecord.ConvertField<int>("AdapterID");
anglePointTag = database.Connection.ExecuteScalar(string.Format(measurementDetailFormat, deviceName, labelName, "PA")).ToNonNullString();
angleSignalReference = database.Connection.ExecuteScalar(string.Format(outputMeasurementDetailFormat, anglePointTag)).ToNonNullString();
magnitudePointTag = database.Connection.ExecuteScalar(string.Format(measurementDetailFormat, deviceName, labelName, "PM")).ToNonNullString();
magnitudeSignalReference = database.Connection.ExecuteScalar(string.Format(outputMeasurementDetailFormat, magnitudePointTag)).ToNonNullString();
}
catch (Exception ex)
{
CommonFunctions.LogException(database, "OutputStreamDevicePhasor.GetDeleteMeasurementDetails", ex);
throw;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Creates an instance of <see cref="App"/> class.
/// </summary>
public App()
{
AppDomain.CurrentDomain.SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal);
m_errorLogger = new ErrorLogger();
m_defaultErrorText = m_errorLogger.ErrorTextMethod;
m_errorLogger.ErrorTextMethod = ErrorText;
m_errorLogger.ExitOnUnhandledException = false;
m_errorLogger.HandleUnhandledException = true;
m_errorLogger.LogToEmail = false;
m_errorLogger.LogToEventLog = true;
m_errorLogger.LogToFile = true;
m_errorLogger.LogToScreenshot = true;
m_errorLogger.LogToUI = true;
m_errorLogger.Initialize();
m_title = AssemblyInfo.EntryAssembly.Title;
// Setup default cache for measurement keys and associated Guid based signal ID's
AdoDataConnection database = null;
try
{
database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory);
MeasurementKey.EstablishDefaultCache(database.Connection, database.AdapterType);
}
catch (Exception ex)
{
// First attempt to display a modal dialog will fail to block this
// thread -- modal dialog displayed by the error logger will block now
MessageBox.Show(ex.Message);
// Log and display error, then exit application - manager must connect to database to continue
m_errorLogger.Log(new InvalidOperationException(string.Format("{0} cannot connect to database: {1}", m_title, ex.Message), ex), true);
}
finally
{
if (database != null)
database.Dispose();
}
IsolatedStorageManager.WriteToIsolatedStorage("MirrorMode", false);
}
/// <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>
/// 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>
/// 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 int RemoveMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeRemoved)
{
const string QueryFormat = "DELETE FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND MeasurementGroupID = {1}";
bool createdConnection = false;
int rowsAffected = 0;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeRemoved)
rowsAffected += database.ExecuteNonQuery(DefaultTimeout, QueryFormat, subscriberID, id);
return rowsAffected;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Counts measurements assigned 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"/> for which to count measurements.</param>
/// <param name="allowed">boolean flag to indicate if measurements are allowed or denied.</param>
/// <returns>string, indicating success for UI display.</returns>
public static int GetGroupCount(AdoDataConnection database, Guid subscriberID, bool allowed)
{
const string QueryFormat = "SELECT COUNT(*) FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND Allowed = {1}";
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
return database.ExecuteNonQuery(DefaultTimeout, QueryFormat, subscriberID, allowed);
}
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>
/// 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>
/// 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>
/// 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();
}
}
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();
}
}
// 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>
/// Loads <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="keys">Keys of the phasors to be loaded from the database</param>
/// <returns>Collection of <see cref="Phasor"/>.</returns>
public static ObservableCollection<Phasor> Load(AdoDataConnection database, IList<int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
Phasor[] phasorList = null;
DataTable phasorTable;
int id;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2);
query = string.Format("SELECT ID, DeviceID, Label, Type, Phase, DestinationPhasorID, SourceIndex, CreatedBy, CreatedOn, UpdatedBy, UpdatedOn FROM Phasor WHERE ID IN ({0})", commaSeparatedKeys);
phasorTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout);
phasorList = new Phasor[phasorTable.Rows.Count];
foreach (DataRow row in phasorTable.Rows)
{
id = row.ConvertField<int>("ID");
phasorList[keys.IndexOf(id)] = new Phasor()
{
ID = id,
DeviceID = row.ConvertField<int>("DeviceID"),
Label = row.Field<string>("Label"),
Type = row.Field<string>("Type"),
Phase = row.Field<string>("Phase"),
SourceIndex = row.ConvertField<int>("SourceIndex")
};
}
}
return new ObservableCollection<Phasor>(phasorList ?? new Phasor[0]);
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets output stream.
/// </summary>
/// <param name="database">Source database connection.</param>
/// <param name="whereClause">Where filter clause.</param>
/// <returns>Output stream.</returns>
public static OutputStream GetOutputStream(AdoDataConnection database, string whereClause)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable outputStreamTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM OutputStreamDetail " + whereClause);
if (outputStreamTable.Rows.Count == 0)
return null;
DataRow row = outputStreamTable.Rows[0];
int type = Convert.ToInt32(row.Field<object>("Type"));
OutputStream outputStream = new OutputStream
{
NodeID = database.Guid(row, "NodeID"),
ID = Convert.ToInt32(row.Field<object>("ID")),
Acronym = row.Field<string>("Acronym"),
Name = row.Field<string>("Name"),
Type = type,
ConnectionString = row.Field<string>("ConnectionString"),
IDCode = Convert.ToInt32(row.Field<object>("IDCode")),
CommandChannel = row.Field<string>("CommandChannel"),
DataChannel = row.Field<string>("DataChannel"),
AutoPublishConfigFrame = Convert.ToBoolean(row.Field<object>("AutoPublishConfigFrame")),
AutoStartDataChannel = Convert.ToBoolean(row.Field<object>("AutoStartDataChannel")),
NominalFrequency = Convert.ToInt32(row.Field<object>("NominalFrequency")),
FramesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
LagTime = row.ConvertField<double>("LagTime"),
LeadTime = row.ConvertField<double>("LeadTime"),
UseLocalClockAsRealTime = Convert.ToBoolean(row.Field<object>("UseLocalClockAsRealTime")),
AllowSortsByArrival = Convert.ToBoolean(row.Field<object>("AllowSortsByArrival")),
LoadOrder = Convert.ToInt32(row.Field<object>("LoadOrder")),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_nodeName = row.Field<string>("NodeName"),
m_typeName = (type == 1) ? "IEEE C37.118" : (type == 2) ? "BPA" : "IEC 61850-90-5",
IgnoreBadTimeStamps = Convert.ToBoolean(row.Field<object>("IgnoreBadTimeStamps")),
TimeResolution = Convert.ToInt32(row.Field<object>("TimeResolution")),
AllowPreemptivePublishing = Convert.ToBoolean(row.Field<object>("AllowPreemptivePublishing")),
DownSamplingMethod = row.Field<string>("DownsamplingMethod"),
DataFormat = row.Field<string>("DataFormat"),
CoordinateFormat = row.Field<string>("CoordinateFormat"),
CurrentScalingValue = Convert.ToInt32(row.Field<object>("CurrentScalingValue")),
VoltageScalingValue = Convert.ToInt32(row.Field<object>("VoltageScalingValue")),
AnalogScalingValue = Convert.ToInt32(row.Field<object>("AnalogScalingValue")),
DigitalMaskValue = Convert.ToInt32(row.Field<object>("DigitalMaskValue")),
PerformTimestampReasonabilityCheck = Convert.ToBoolean(row.Field<object>("PerformTimeReasonabilityCheck"))
};
return outputStream;
}
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();
}
}
// 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>
/// Retrieves <see cref="Phasor"/> information based on query string filter.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="whereClause">query string to filter data.</param>
/// <returns><see cref="Phasor"/> information.</returns>
public static Phasor GetPhasor(AdoDataConnection database, string whereClause)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable phasorTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM PhasorDetail " + whereClause);
if (phasorTable.Rows.Count == 0)
return null;
DataRow row = phasorTable.Rows[0];
Phasor phasor = new Phasor
{
ID = row.ConvertField<int>("ID"),
DeviceID = row.ConvertField<int>("DeviceID"),
Label = row.Field<string>("Label"),
Type = row.Field<string>("Type"),
Phase = row.Field<string>("Phase"),
SourceIndex = row.ConvertField<int>("SourceIndex")
};
return phasor;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <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="keys">Keys of the measuremnets to be loaded from the database</param>
/// <returns>Collection of <see cref="OutputStream"/>.</returns>
public static ObservableCollection<OutputStream> Load(AdoDataConnection database, IList<int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<OutputStream> outputStreamList = new ObservableCollection<OutputStream>();
DataTable outputStreamTable;
string query;
string commaSeparatedKeys;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2);
query = string.Format("SELECT * FROM OutputStreamDetail WHERE ID IN ({0})", commaSeparatedKeys);
outputStreamTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout);
outputStreamList = new ObservableCollection<OutputStream>(from item in outputStreamTable.AsEnumerable()
let id = item.ConvertField<int>("ID")
let type = item.ConvertField<int>("Type") + 1
orderby keys.IndexOf(id)
select new OutputStream
{
NodeID = database.Guid(item, "NodeID"),
ID = id,
Acronym = item.Field<string>("Acronym"),
Name = item.Field<string>("Name"),
Type = type,
ConnectionString = item.Field<string>("ConnectionString"),
IDCode = item.ConvertField<int>("IDCode"),
CommandChannel = item.Field<string>("CommandChannel"),
DataChannel = item.Field<string>("DataChannel"),
AutoPublishConfigFrame = Convert.ToBoolean(item.Field<object>("AutoPublishConfigFrame")),
AutoStartDataChannel = Convert.ToBoolean(item.Field<object>("AutoStartDataChannel")),
NominalFrequency = item.ConvertField<int>("NominalFrequency"),
FramesPerSecond = item.ConvertNullableField<int>("FramesPerSecond") ?? 30,
LagTime = item.ConvertField<double>("LagTime"),
LeadTime = item.ConvertField<double>("LeadTime"),
UseLocalClockAsRealTime = Convert.ToBoolean(item.Field<object>("UseLocalClockAsRealTime")),
AllowSortsByArrival = Convert.ToBoolean(item.Field<object>("AllowSortsByArrival")),
LoadOrder = item.ConvertField<int>("LoadOrder"),
Enabled = Convert.ToBoolean(item.Field<object>("Enabled")),
m_nodeName = item.Field<string>("NodeName"),
m_typeName = (type == 1) ? "IEEE C37.118" : (type == 2) ? "BPA" : "IEC 61850-90-5",
IgnoreBadTimeStamps = Convert.ToBoolean(item.Field<object>("IgnoreBadTimeStamps")),
TimeResolution = item.ConvertField<int>("TimeResolution"),
AllowPreemptivePublishing = Convert.ToBoolean(item.Field<object>("AllowPreemptivePublishing")),
DownSamplingMethod = item.Field<string>("DownsamplingMethod"),
DataFormat = item.Field<string>("DataFormat"),
CoordinateFormat = item.Field<string>("CoordinateFormat"),
CurrentScalingValue = item.ConvertField<int>("CurrentScalingValue"),
VoltageScalingValue = item.ConvertField<int>("VoltageScalingValue"),
AnalogScalingValue = item.ConvertField<int>("AnalogScalingValue"),
DigitalMaskValue = item.ConvertField<int>("DigitalMaskValue"),
PerformTimestampReasonabilityCheck = Convert.ToBoolean(item.Field<object>("PerformTimeReasonabilityCheck")),
m_mirroringSourceDevice = GetMirroringSource(database, id)
});
return outputStreamList;
}
return outputStreamList;
}
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();
}
}
private static string GetMirroringSource(AdoDataConnection database, int outputStreamID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList<int> keys = OutputStreamDevice.LoadKeys(database, outputStreamID);
// Get first output stream device.
ObservableCollection<OutputStreamDevice> outputStreamDevices = OutputStreamDevice.Load(database, keys);
if (outputStreamDevices.Count == 0)
return "";
OutputStreamDevice outputStreamDevice = outputStreamDevices[0];
// Get OriginalSource value for the above outputstreamdevice from the input Device table.
Device device = Device.GetDevice(database, " WHERE Acronym LIKE '%" + outputStreamDevice.Acronym + "'");
if (device == null)
return "";
return device.OriginalSource;
}
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>
/// 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>
/// 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 int AddMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeAdded, bool allowed)
{
const string QueryFormat =
"INSERT INTO SubscriberMeasurementGroup (NodeID, SubscriberID, MeasurementGroupID, Allowed, UpdatedOn, UpdatedBy, CreatedOn, CreatedBy) " +
"SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7} " +
"WHERE (SELECT COUNT(*) FROM SubscriberMeasurementGroup WHERE SubscriberID = {1} AND MeasurementGroupID = {2}) = 0";
bool createdConnection = false;
int rowsAffected = 0;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeAdded)
{
rowsAffected += database.ExecuteNonQuery(DefaultTimeout, QueryFormat, CommonFunctions.CurrentNodeID(), subscriberID, id, allowed,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser);
}
return rowsAffected;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// LoadKeys <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="outputStreamDeviceID">ID of the output stream 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="OutputStreamDevicePhasor"/>.</returns>
public static IList<int> LoadKeys(AdoDataConnection database, int outputStreamDeviceID, string sortMember, string sortDirection)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList<int> outputStreamDevicePhasorList = new List<int>();
DataTable OutputStreamDevicePhasorTable;
string sortClause = string.Empty;
if (!string.IsNullOrEmpty(sortMember))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
OutputStreamDevicePhasorTable = database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT ID FROM OutputStreamDevicePhasor WHERE OutputStreamDeviceID = {0} {1}", outputStreamDeviceID, sortClause));
foreach (DataRow row in OutputStreamDevicePhasorTable.Rows)
{
outputStreamDevicePhasorList.Add((row.ConvertField<int>("ID")));
}
return outputStreamDevicePhasorList;
}
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>
/// 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();
}
}
/// <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>
/// 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();
}
}