/// <summary>
/// Saves measurement back to the configuration database
/// </summary>
/// <param name="database">Database connection for query. Will be created from config if this value is null.</param>
/// <param name="measurement">Measurement to be inserted or updated</param>
public void Save(AdoDataConnection database, PowerMeasurement measurement)
{
var createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
if (measurement.SignalID == Guid.Empty)
{
database.ExecuteNonQuery("INSERT INTO Measurement (DeviceID, PointTag, SignalTypeID, " +
"SignalReference, Adder, Multiplier, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES " +
"({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11})", ToNotNull(measurement.DeviceID), measurement.PointTag,
measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description),
database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, Thread.CurrentPrincipal.Identity.Name, database.UtcNow);
measurement.SignalID = database.ExecuteScalar<Guid>("SELECT SignalID FROM Measurement WHERE PointTag={0}", measurement.PointTag);
}
else
{
database.ExecuteNonQuery("UPDATE Measurement SET DeviceID = {0}, PointTag = {1}, " +
"SignalTypeID = {2}, SignalReference = {3}, Adder = {4}, Multiplier = {5}, Description = {6}, " +
"Enabled = {7}, UpdatedBy = {8}, UpdatedOn = {9} WHERE SignalId = {10}", ToNotNull(measurement.DeviceID), measurement.PointTag,
measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description),
database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, measurement.SignalID);
}
}
finally
{
if (createdConnection)
database?.Dispose();
}
}
internal static void LoadConnectionParameters()
{
try
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
TableOperations<IaonOutputAdapter> operations = new TableOperations<IaonOutputAdapter>(connection);
IaonOutputAdapter record = operations.QueryRecords(limit: 1, restriction: new RecordRestriction
{
FilterExpression = "TypeName = 'openHistorian.Adapters.LocalOutputAdapter'"
})
.FirstOrDefault();
if ((object)record == null)
throw new NullReferenceException("Primary openHistorian adapter instance not found.");
Dictionary<string, string> settings = record.ConnectionString.ParseKeyValuePairs();
string setting;
if (!settings.TryGetValue("port", out setting) || !int.TryParse(setting, out s_portNumber))
s_portNumber = Connection.DefaultHistorianPort;
if (!settings.TryGetValue("instanceName", out s_defaultInstanceName) || string.IsNullOrWhiteSpace(s_defaultInstanceName))
s_defaultInstanceName = record.AdapterName ?? "PPA";
}
}
catch
{
s_defaultInstanceName = "PPA";
s_portNumber = Connection.DefaultHistorianPort;
}
}
/// <summary>
/// Loads <see cref="IaonTree"/> 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="IaonTree"/>.</returns>
public static ObservableCollection<IaonTree> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<IaonTree> iaonTreeList;
DataTable rootNodesTable = new DataTable();
rootNodesTable.Columns.Add(new DataColumn("AdapterType", Type.GetType("System.String")));
DataRow row;
row = rootNodesTable.NewRow();
row["AdapterType"] = "Input Adapters";
rootNodesTable.Rows.Add(row);
row = rootNodesTable.NewRow();
row["AdapterType"] = "Action Adapters";
rootNodesTable.Rows.Add(row);
row = rootNodesTable.NewRow();
row["AdapterType"] = "Output Adapters";
rootNodesTable.Rows.Add(row);
DataSet resultSet = new DataSet();
resultSet.Tables.Add(rootNodesTable);
DataTable iaonTreeTable = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT * FROM IaonTreeView WHERE NodeID = {0}", "nodeID"), database.CurrentNodeID());
resultSet.EnforceConstraints = false;
resultSet.Tables.Add(iaonTreeTable.Copy());
resultSet.Tables[0].TableName = "RootNodesTable";
resultSet.Tables[1].TableName = "AdapterData";
iaonTreeList = new ObservableCollection<IaonTree>(from item in resultSet.Tables["RootNodesTable"].AsEnumerable()
select new IaonTree
{
m_adapterType = item.Field<string>("AdapterType"),
m_adapterList = new ObservableCollection<Adapter>(from obj in resultSet.Tables["AdapterData"].AsEnumerable()
where obj.Field<string>("AdapterType") == item.Field<string>("AdapterType")
select new Adapter
{
NodeID = database.Guid(obj, "NodeID"),
ID = obj.ConvertField<int>("ID"),
AdapterName = obj.Field<string>("AdapterName"),
AssemblyName = obj.Field<string>("AssemblyName"),
TypeName = obj.Field<string>("TypeName"),
ConnectionString = obj.Field<string>("ConnectionString")
})
});
return iaonTreeList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
public WebPageControllerResolver(WebServer webServer, string defaultWebPage, object model, Type modelType, AdoDataConnection database)
{
m_webServer = webServer;
m_defaultWebPage = defaultWebPage;
m_model = model;
m_modelType = modelType;
m_database = database;
}
/// <summary>
/// Validates that data operation and adapter instance exist within database.
/// </summary>
public static void ValidateDatabaseDefinitions()
{
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
{
if (!DataOperationExists(database))
CreateDataOperation(database);
if (!AdapterInstanceExists(database))
CreateAdapterInstance(database);
}
}
/// <summary>
/// Creates a new <see cref="RazorView"/>.
/// </summary>
/// <param name="razorEngine"><see cref="IRazorEngine"/> instance to use.</param>
/// <param name="templateName">Name of template file, typically a .cshtml or .vbhtml file.</param>
/// <param name="model">Reference to model to use when rendering template.</param>
/// <param name="modelType">Type of <paramref name="model"/>.</param>
/// <param name="pagedViewModelDataType">Type of data class for views based on paged view model, if any.</param>
/// <param name="pagedViewModelHubType">Type of SignalR hub for views based on paged view model, if any.</param>
/// <param name="database"><see cref="AdoDataConnection"/> to use, if any.</param>
/// <param name="exceptionHandler">Delegate to handle exceptions, if any.</param>
public RazorView(IRazorEngine razorEngine, string templateName, object model = null, Type modelType = null, Type pagedViewModelDataType = null, Type pagedViewModelHubType = null, AdoDataConnection database = null, Action<Exception> exceptionHandler = null)
{
m_razorEngine = razorEngine;
TemplateName = templateName;
Model = model;
ModelType = modelType;
PagedViewModelDataType = pagedViewModelDataType;
PagedViewModelHubType = pagedViewModelHubType;
Database = database;
ExceptionHandler = exceptionHandler;
}
/// <summary>
/// Delegates control to the data operations that are to be performed at startup.
/// </summary>
private static void PerformTimeSeriesStartupOperations(AdoDataConnection database, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException)
{
// Set up messaging to the service
s_statusMessage = statusMessage;
s_processException = processException;
// Run data operations
ValidateDefaultNode(database, nodeIDQueryString);
ValidateActiveMeasurements(database, nodeIDQueryString);
ValidateAccountsAndGroups(database, nodeIDQueryString);
ValidateDataPublishers(database, nodeIDQueryString, arguments);
ValidateStatistics(database, nodeIDQueryString);
ValidateAlarming(database, nodeIDQueryString);
}
private static bool CreateConnection(ref AdoDataConnection database)
{
if ((object)database != null)
return false;
try
{
database = new AdoDataConnection("systemSettings");
return true;
}
catch
{
return false;
}
}
/// <summary>
/// Creates a new <see cref="DataSubscriptionHubClient"/> instance.
/// </summary>
public DataSubscriptionHubClient()
{
m_statisticSubscriptionInfo = new UnsynchronizedSubscriptionInfo(false);
m_dataSubscriptionInfo = new UnsynchronizedSubscriptionInfo(false);
m_measurements = new List<MeasurementValue>();
m_statistics = new List<MeasurementValue>();
m_statusLights = new List<StatusLight>();
m_deviceDetails = new List<DeviceDetail>();
m_measurementDetails = new List<MeasurementDetail>();
m_phasorDetails = new List<PhasorDetail>();
m_schemaVersion = new List<SchemaVersion>();
m_measurementLock = new object();
using(AdoDataConnection conn = new AdoDataConnection("securityProvider"))
{
int index = conn.ExecuteScalar<int>("Select ID FROM ValueListGroup WHERE Name = 'ModbusSubscriptions'");
m_connectionString = conn.ExecuteScalar<string>("Select Text FROM ValueList WHERE GroupID = {0} AND IsDefault = 1", index);
}
}
/// <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>
/// Data operation to validate and ensure there is a node in the database.
/// </summary>
private static void ValidateDefaultNode(AdoDataConnection database, string nodeIDQueryString)
{
// Queries
const string NodeCountFormat = "SELECT COUNT(*) FROM Node";
const string NodeInsertFormat = "INSERT INTO Node(Name, CompanyID, Description, Settings, MenuType, MenuData, Master, LoadOrder, Enabled) " +
"VALUES('Default', NULL, 'Default node', 'RemoteStatusServerConnectionString={server=localhost:8500};datapublisherport=6165;RealTimeStatisticServiceUrl=http://localhost:6052/historian', " +
"'File', 'Menu.xml', 1, 0, 1)";
const string NodeUpdateFormat = "UPDATE Node SET ID = {0}";
// Determine whether the node exists in the database and create it if it doesn't.
int nodeCount = Convert.ToInt32(database.Connection.ExecuteScalar(NodeCountFormat));
if (nodeCount == 0)
{
database.Connection.ExecuteNonQuery(NodeInsertFormat);
database.Connection.ExecuteNonQuery(string.Format(NodeUpdateFormat, nodeIDQueryString));
}
}
/// <summary>
/// Saves <see cref="InputWizardDevice"/> information in to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="inputWizardDevice">Information about <see cref="InputWizardDevice"/>.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string Save(AdoDataConnection database, InputWizardDevice inputWizardDevice)
{
return("");
}
private void LogStateChanges()
{
IList <StateChange> stateChanges;
StringBuilder insertQuery;
List <object> insertParameters;
StringBuilder deleteQuery;
List <object> deleteParameters;
int count;
Thread.Sleep(m_logProcessingDelay);
stateChanges = m_stateChanges.Dequeue();
insertQuery = new StringBuilder();
insertParameters = new List <object>();
deleteQuery = new StringBuilder();
deleteParameters = new List <object>();
count = 0;
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
foreach (StateChange stateChange in stateChanges)
{
if (insertQuery.Length == 0)
{
insertQuery.Append("INSERT INTO AlarmLog(SignalID, PreviousState, NewState, Ticks, Timestamp, Value) ");
insertQuery.Append("SELECT {0} AS SignalID, {1} AS PreviousState, {2} AS NewState, {3} AS Ticks, {4} AS Timestamp, {5} AS Value");
deleteQuery.Append("DELETE FROM AlarmLog WHERE ");
deleteQuery.Append("(SignalID = {0} AND Ticks < {1})");
}
else
{
insertQuery.Append(" UNION ALL ");
insertQuery.AppendFormat("SELECT {{{0}}}, {{{1}}}, {{{2}}}, {{{3}}}, {{{4}}}, {{{5}}}", Enumerable.Range(count * 6, 6).Cast <object>().ToArray());
deleteQuery.Append(" OR ");
deleteQuery.AppendFormat("(SignalID = {{{0}}} AND Ticks < {{{1}}})", Enumerable.Range(count * 2, 2).Cast <object>().ToArray());
}
insertParameters.Add(stateChange.SignalID);
insertParameters.Add(stateChange.OldStateID);
insertParameters.Add(stateChange.NewStateID);
insertParameters.Add(stateChange.Timestamp.Ticks);
insertParameters.Add(stateChange.Timestamp);
insertParameters.Add(stateChange.Value);
deleteParameters.Add(stateChange.SignalID);
deleteParameters.Add(stateChange.Timestamp.AddHours(-24.0D).Ticks);
count++;
if (count == m_bulkInsertLimit)
{
connection.ExecuteNonQuery(insertQuery.ToString(), insertParameters.ToArray());
connection.ExecuteNonQuery(deleteQuery.ToString(), deleteParameters.ToArray());
insertQuery.Clear();
insertParameters.Clear();
deleteQuery.Clear();
deleteParameters.Clear();
count = 0;
}
}
if (count > 0)
{
connection.ExecuteNonQuery(insertQuery.ToString(), insertParameters.ToArray());
connection.ExecuteNonQuery(deleteQuery.ToString(), deleteParameters.ToArray());
}
}
}
public override void Execute(MeterDataSet meterDataSet)
{
FaultDataResource faultDataResource = meterDataSet.GetResource <FaultDataResource>();
string stationKey = meterDataSet.Meter.MeterLocation.AssetKey;
foreach (var kvp in faultDataResource.FaultLookup)
{
DataGroup dataGroup = kvp.Key;
DataAnalysis.FaultGroup faultGroup = kvp.Value;
string lineKey = dataGroup.Line.AssetKey;
for (int i = 0; i < faultGroup.Faults.Count; i++)
{
int faultNumber = i + 1;
DataAnalysis.Fault fault = faultGroup.Faults[i];
if (fault.IsSuppressed)
{
continue;
}
string distance = fault.Summaries
.Where(summary => summary.IsValid)
.Where(summary => summary.IsSelectedAlgorithm)
.Select(summary => summary.Distance.ToString("0.###"))
.FirstOrDefault();
if (distance == null)
{
return;
}
string url = string.Format(Settings.URLFormat, stationKey, lineKey, distance);
string structureInfo = GetStructureInfo(url);
DataTable structureData = ToDataTable(structureInfo);
if (structureData.Rows.Count == 0)
{
return;
}
Func <string, string> fieldMappingLookup = FieldMappingLookup;
string assetKeyField = fieldMappingLookup("AssetKey");
string latitudeKeyField = fieldMappingLookup("Latitude");
string longitudeKeyField = fieldMappingLookup("Longitude");
if (!structureData.Columns.Contains(assetKeyField))
{
return;
}
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
{
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
Event evt = eventTable.GetEvent(meterDataSet.FileGroup, dataGroup);
int faultSummaryID = connection.ExecuteScalar <int>(FaultSummaryQuery, evt.ID, faultNumber);
TableOperations <Structure> structureTable = new TableOperations <Structure>(connection);
foreach (DataRow row in structureData.Rows)
{
string assetKey = row.Field <string>(assetKeyField);
string latitude = null;
string longitude = null;
if (structureData.Columns.Contains(latitudeKeyField))
{
latitude = row.Field <string>(latitudeKeyField);
}
if (structureData.Columns.Contains(longitudeKeyField))
{
longitude = row.Field <string>(longitudeKeyField);
}
Structure structure = structureTable.QueryRecordWhere("AssetKey = {0}", assetKey)
?? new Structure()
{
AssetKey = assetKey
};
structure.LineID = dataGroup.Line.ID;
if (double.TryParse(latitude, out double lat))
{
structure.Latitude = lat;
}
if (double.TryParse(longitude, out double lon))
{
structure.Longitude = lon;
}
structureTable.AddNewOrUpdateRecord(structure);
if (structure.ID == 0)
{
structure.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY");
}
if (faultSummaryID != 0)
{
connection.ExecuteNonQuery("INSERT INTO NearestStructure(FaultSummaryID, StructureID) VALUES({0}, {1})", faultSummaryID, structure.ID);
}
}
}
}
}
}
/// <summary>
/// Returns true if a data operation exists to run this class. Returns false otherwise.
/// </summary>
/// <param name="database">Database connection to use for checking the data operation</param>
/// <returns>True or false indicating whether the operation exists</returns>
private static bool AdapterInstanceExists(AdoDataConnection database)
{
return(Convert.ToInt32(database.ExecuteScalar($"SELECT COUNT(*) FROM CustomActionAdapter WHERE TypeName='{typeof(PowerMultiCalculatorAdapter).FullName}'")) > 0);
}
/// <summary>
/// Validate accounts and groups to ensure that account names and group names are converted to SIDs.
/// </summary>
/// <param name="database">Data connection to use for database operations.</param>
private static void ValidateAccountsAndGroups(AdoDataConnection database)
{
const string SelectUserAccountQuery = "SELECT ID, Name, UseADAuthentication FROM UserAccount";
const string SelectSecurityGroupQuery = "SELECT ID, Name FROM SecurityGroup";
const string UpdateUserAccountFormat = "UPDATE UserAccount SET Name = '{0}' WHERE ID = '{1}'";
const string UpdateSecurityGroupFormat = "UPDATE SecurityGroup SET Name = '{0}' WHERE ID = '{1}'";
string id;
string sid;
string accountName;
Dictionary <string, string> updateMap;
updateMap = new Dictionary <string, string>();
// Find user accounts that need to be updated
using (IDataReader userAccountReader = database.Connection.ExecuteReader(SelectUserAccountQuery))
{
while (userAccountReader.Read())
{
id = userAccountReader["ID"].ToNonNullString();
accountName = userAccountReader["Name"].ToNonNullString();
if (userAccountReader["UseADAuthentication"].ToNonNullString().ParseBoolean())
{
sid = UserInfo.UserNameToSID(accountName);
if (!ReferenceEquals(accountName, sid) && UserInfo.IsUserSID(sid))
{
updateMap.Add(id, sid);
}
}
}
}
// Update user accounts
foreach (KeyValuePair <string, string> pair in updateMap)
{
database.Connection.ExecuteNonQuery(string.Format(UpdateUserAccountFormat, pair.Value, pair.Key));
}
updateMap.Clear();
// Find security groups that need to be updated
using (IDataReader securityGroupReader = database.Connection.ExecuteReader(SelectSecurityGroupQuery))
{
while (securityGroupReader.Read())
{
id = securityGroupReader["ID"].ToNonNullString();
accountName = securityGroupReader["Name"].ToNonNullString();
if (accountName.Contains('\\'))
{
sid = UserInfo.GroupNameToSID(accountName);
if (!ReferenceEquals(accountName, sid) && UserInfo.IsGroupSID(sid))
{
updateMap.Add(id, sid);
}
}
}
}
// Update security groups
foreach (KeyValuePair <string, string> pair in updateMap)
{
database.Connection.ExecuteNonQuery(string.Format(UpdateSecurityGroupFormat, pair.Value, pair.Key));
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Subscriber"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of subscribers defined in the database.</returns>
public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
Dictionary<Guid, string> subscriberList;
DataTable subscriberTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
subscriberList = new Dictionary<Guid, string>();
if (isOptional)
subscriberList.Add(Guid.Empty, "Select Subscriber");
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Subscriber WHERE Enabled = {0} AND NodeID = {1} ORDER BY Name", "enabled", "nodeID");
subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in subscriberTable.Rows)
{
subscriberList[database.Guid(row, "ID")] = row.Field<string>("Acronym");
}
return subscriberList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> 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();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="RealTimeStream"/> 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="RealTimeStream"/>.</returns>
public static ObservableCollection <RealTimeStream> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
ObservableCollection <RealTimeStream> realTimeStreamList = null;
createdConnection = CreateConnection(ref database);
DataSet resultSet = new DataSet();
resultSet.EnforceConstraints = false;
// Get PDCs list.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, ConnectionString, CompanyName, Enabled FROM DeviceDetail " +
"WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"), DefaultTimeout, database.CurrentNodeID(), database.Bool(true), database.Bool(true)).Copy());
resultSet.Tables[0].TableName = "PdcTable";
// Add a dummy device row in PDC table to associate PMUs which are not PDC and connected directly.
DataRow row = resultSet.Tables["PdcTable"].NewRow();
row["ID"] = 0;
row["Acronym"] = string.Empty;
row["Name"] = "Devices Connected Directly";
row["CompanyName"] = string.Empty;
row["Enabled"] = false;
resultSet.Tables["PdcTable"].Rows.Add(row);
// Get Non-PDC device list.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, CompanyName, ProtocolName, VendorDeviceName, " +
"ParentAcronym, Enabled FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true)).Copy());
resultSet.Tables[1].TableName = "DeviceTable";
// Get non-statistical measurement list
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, DeviceID, SignalID, PointID, PointTag, SignalReference, " +
"SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID = {0} AND " +
"SignalAcronym <> {1} ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT").Copy());
resultSet.Tables[2].TableName = "MeasurementTable";
// Query for any non-statistical measurements that are subscribed via GEP, but are a part of another node in the same database
// IMPORTANT: Make sure columns in this external node query exactly match those of the previous primary measurement query
DataTable otherMeasurements = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, 0 AS DeviceID, SignalID, PointID, PointTag, SignalReference, " +
"SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID <> {0} AND " +
"SignalAcronym <> {1} AND Subscribed <> 0 ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT");
realTimeStreamList = new ObservableCollection <RealTimeStream>(
from pdc in resultSet.Tables["PdcTable"].AsEnumerable()
let settings = pdc.Field <string>("ConnectionString").ToNonNullString().ParseKeyValuePairs()
select new RealTimeStream
{
ID = pdc.ConvertField <int>("ID"),
Acronym = string.IsNullOrEmpty(pdc.Field <string>("Acronym")) ? "DIRECT CONNECTED" : pdc.Field <string>("Acronym"),
Name = pdc.Field <string>("Name"),
CompanyName = pdc.Field <string>("CompanyName"),
StatusColor = string.IsNullOrEmpty(pdc.Field <string>("Acronym")) ? "Transparent" : "Gray",
Enabled = Convert.ToBoolean(pdc.Field <object>("Enabled")),
Expanded = false,
DeviceList = new ObservableCollection <RealTimeDevice>(
from device in resultSet.Tables["DeviceTable"].AsEnumerable()
where device.Field <string>("ParentAcronym").ToNonNullString() == pdc.Field <string>("Acronym")
select new RealTimeDevice
{
ID = device.ConvertNullableField <int>("ID"),
Acronym = device.Field <string>("Acronym"),
Name = device.Field <string>("Name"),
ProtocolName = device.Field <string>("ProtocolName"),
VendorDeviceName = device.Field <string>("VendorDeviceName"),
ParentAcronym = string.IsNullOrEmpty(device.Field <string>("ParentAcronym")) ? "DIRECT CONNECTED" : device.Field <string>("ParentAcronym"),
Expanded = false,
StatusColor = device.ConvertNullableField <int>("ID") == null ? "Transparent" : "Gray",
Enabled = Convert.ToBoolean(device.Field <object>("Enabled")),
MeasurementList = new ObservableCollection <RealTimeMeasurement>(
from measurement in resultSet.Tables["MeasurementTable"].AsEnumerable()
where measurement.ConvertNullableField <int>("DeviceID") == device.ConvertNullableField <int>("ID") && (measurement.ConvertField <bool>("Subscribed") || measurement.ConvertField <bool>("Internal") || (settings.ContainsKey("securityMode") && settings["securityMode"].Equals("None", StringComparison.OrdinalIgnoreCase))) //We will only display measurements which are internal or subscribed to avoid confusion.
select new RealTimeMeasurement
{
ID = measurement.Field <string>("ID"),
DeviceID = measurement.ConvertNullableField <int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()),
PointID = measurement.ConvertField <int>("PointID"),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = measurement.Field <string>("SignalReference"),
Description = measurement.Field <string>("description"),
SignalName = measurement.Field <string>("SignalName"),
SignalAcronym = measurement.Field <string>("SignalAcronym"),
EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ",
LongTimeTag = "N/A",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
}
)
}
)
}
);
if (otherMeasurements.Rows.Count > 0)
{
// Add subscribed measurements from other nodes
realTimeStreamList.Add(new RealTimeStream
{
ID = 0,
Acronym = "SUBSCRIBED",
Name = "Subscribed Measurements",
CompanyName = string.Empty,
StatusColor = "Transparent",
Enabled = false,
Expanded = false,
DeviceList = new ObservableCollection <RealTimeDevice>(
otherMeasurements.Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null)
.Select(measurement => measurement.Field <string>("SignalReference"))
.Select(GetSourceName)
.Distinct()
.Select(source => new RealTimeDevice
{
ID = 0,
Acronym = source,
Name = source,
ProtocolName = string.Empty,
VendorDeviceName = string.Empty,
ParentAcronym = "SUBSCRIBED",
Expanded = false,
StatusColor = "Gray",
Enabled = false,
MeasurementList = new ObservableCollection <RealTimeMeasurement>(
otherMeasurements.Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null && measurement.Field <string>("SignalReference").StartsWith(source))
.Select(measurement => new RealTimeMeasurement
{
ID = measurement.Field <string>("ID"),
DeviceID = measurement.ConvertNullableField <int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()),
PointID = measurement.ConvertField <int>("PointID"),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = measurement.Field <string>("SignalReference"),
Description = measurement.Field <string>("description"),
SignalName = measurement.Field <string>("SignalName"),
SignalAcronym = measurement.Field <string>("SignalAcronym"),
EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ",
LongTimeTag = "N/A",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
})
)
})
)
});
}
if (resultSet.Tables["MeasurementTable"].Select("DeviceID IS NULL").Length > 0)
{
// Add direct connected measurements with no associated device (DeviceID IS NULL)
realTimeStreamList.Add(new RealTimeStream
{
ID = 0,
Acronym = "CALCULATED",
Name = "Calculated Measurements",
CompanyName = string.Empty,
StatusColor = "Transparent",
Enabled = false,
Expanded = false,
DeviceList = new ObservableCollection <RealTimeDevice>(
resultSet.Tables["MeasurementTable"].Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null)
.Select(measurement => measurement.Field <string>("SignalReference"))
.Select(GetSourceName)
.Distinct()
.Select(source => new RealTimeDevice
{
ID = 0,
Acronym = source,
Name = source,
ProtocolName = string.Empty,
VendorDeviceName = string.Empty,
ParentAcronym = "CALCULATED",
Expanded = false,
StatusColor = "Gray",
Enabled = false,
MeasurementList = new ObservableCollection <RealTimeMeasurement>(
resultSet.Tables["MeasurementTable"].Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null && measurement.Field <string>("SignalReference").StartsWith(source))
.Select(measurement => new RealTimeMeasurement
{
ID = measurement.Field <string>("ID"),
DeviceID = measurement.ConvertNullableField <int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()),
PointID = measurement.ConvertField <int>("PointID"),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = measurement.Field <string>("SignalReference"),
Description = measurement.Field <string>("description"),
SignalName = measurement.Field <string>("SignalName"),
SignalAcronym = measurement.Field <string>("SignalAcronym"),
EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ",
LongTimeTag = "N/A",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
})
)
})
)
});
}
// Assign parent references for real-time measurements
foreach (RealTimeStream stream in realTimeStreamList)
{
foreach (RealTimeDevice device in stream.DeviceList)
{
device.Parent = stream;
foreach (RealTimeMeasurement measurement in device.MeasurementList)
{
measurement.Parent = device;
}
}
}
return(realTimeStreamList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
static int Main(string[] args)
{
// System settings
ConfigurationFile configFile = ConfigurationFile.Current;
CategorizedSettingsElementCollection systemSettings = configFile.Settings["systemSettings"];
systemSettings.Add("NodeID", Guid.NewGuid().ToString(), "Unique Node ID");
Guid nodeID = systemSettings["NodeID"].ValueAs <Guid>();
string connectionString = systemSettings["ConnectionString"].Value;
string nodeIDQueryString = null;
string parameterizedQuery;
int protocolID, signalTypeID;
// Define guid with query string delimiters according to database needs
Dictionary <string, string> settings = connectionString.ParseKeyValuePairs();
string setting;
if (settings.TryGetValue("Provider", out setting))
{
// Check if provider is for Access since it uses braces as Guid delimiters
if (setting.StartsWith("Microsoft.Jet.OLEDB", StringComparison.OrdinalIgnoreCase))
{
nodeIDQueryString = "{" + nodeID + "}";
}
}
if (string.IsNullOrWhiteSpace(nodeIDQueryString))
{
nodeIDQueryString = "'" + nodeID + "'";
}
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
{
IDbConnection connection = database.Connection;
if (Convert.ToInt32(connection.ExecuteScalar("SELECT COUNT(*) FROM Protocol WHERE Acronym='WAV'")) == 0)
{
if (database.IsSQLServer || database.IsJetEngine)
{
connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, [Type], Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')");
}
else
{
connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')");
}
}
protocolID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM Protocol WHERE Acronym='WAV'"));
// Typically these values should be defined as analogs, however, we use a voltage magnitude signal type
// since these types of values can be better graphed with auto-scaling in the visualization tools
signalTypeID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='VPHM'"));
//signalTypeID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='ALOG'"));
string pathRoot = FilePath.GetDirectoryName((args.Length > 0) ? args[0] : systemSettings["MusicDirectory"].Value);
string sourcePath = Path.Combine(pathRoot, "*" + Path.DirectorySeparatorChar + "*.wav");
foreach (string sourceFileName in FilePath.GetFileList(sourcePath))
{
WaveFile sourceWave;
string fileName = FilePath.GetFileName(sourceFileName);
char[] invalidChars = new[] { '\'', '[', ']', '(', ')', ',', '-', '.' };
Console.WriteLine("Loading metadata for \"{0}\"...\r\n", fileName);
sourceWave = WaveFile.Load(sourceFileName, false);
fileName = FilePath.GetFileNameWithoutExtension(fileName).RemoveDuplicateWhiteSpace().RemoveCharacters(invalidChars.Contains).Trim();
string acronym = fileName.Replace(' ', '_').ToUpper() + "_" + (int)(sourceWave.SampleRate / SI.Kilo) + "KHZ";
string name = GenerateSongName(sourceWave, fileName);
Console.WriteLine(" Acronym = {0}", acronym);
Console.WriteLine(" Name = {0}", name);
Console.WriteLine("");
// Check to see if device exists
if (Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE Acronym = {0}", "acronym"), acronym)) == 0)
{
parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Device(NodeID, Acronym, Name, ProtocolID, FramesPerSecond, " +
"MeasurementReportingInterval, ConnectionString, Enabled) VALUES(" + nodeIDQueryString + ", {0}, {1}, {2}, {3}, {4}, {5}, {6})",
"acronym", "name", "protocolID", "framesPerSecond", "measurementReportingInterval",
"connectionString", "enabled");
// Insert new device record
connection.ExecuteNonQuery(parameterizedQuery, acronym, name, protocolID, sourceWave.SampleRate, 1000000, string.Format("wavFileName={0}; connectOnDemand=true; outputSourceIDs={1}", FilePath.GetAbsolutePath(sourceFileName), acronym), database.Bool(true));
int deviceID = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT ID FROM Device WHERE Acronym = {0}", "acronym"), acronym));
string pointTag;
// Add a measurement for each defined wave channel
for (int i = 0; i < sourceWave.Channels; i++)
{
int index = i + 1;
pointTag = acronym + ":WAVA" + index;
parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Measurement(DeviceID, PointTag, SignalTypeID, SignalReference, Description, " +
"Enabled) VALUES({0}, {1}, {2}, {3}, {4}, {5})", "deviceID", "pointTag", "signalTypeID", "signalReference", "description", "enabled");
// Insert new measurement record
connection.ExecuteNonQuery(parameterizedQuery, (object)deviceID, pointTag, signalTypeID, acronym + "-AV" + index, name + " - channel " + index, database.Bool(true));
//index = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT PointID FROM Measurement WHERE PointTag = {0}", "pointTag"), pointTag));
}
// Disable all non analog measurements that may be associated with this device
connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE Measurement SET Enabled = {0} WHERE DeviceID = {1} AND SignalTypeID <> {2}", "enabled", "deviceID", "signalTypeID"), database.Bool(false), deviceID, signalTypeID);
}
}
}
return(0);
}
/// <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();
}
}
}
private void HomeUserControl_Loaded(object sender, RoutedEventArgs e)
{
if (!CommonFunctions.CurrentPrincipal.IsInRole("Administrator,Editor"))
{
ButtonInputWizard.IsEnabled = false;
}
m_windowsServiceClient = CommonFunctions.GetWindowsServiceClient();
if (m_windowsServiceClient == null || m_windowsServiceClient.Helper.RemotingClient.CurrentState != ClientState.Connected)
{
// TODO: Disable any buttons here that will not function without service connectivity
}
else
{
m_windowsServiceClient.Helper.ReceivedServiceResponse += Helper_ReceivedServiceResponse;
CommonFunctions.SendCommandToService("Health -actionable");
CommonFunctions.SendCommandToService("Version -actionable");
CommonFunctions.SendCommandToService("Status -actionable");
CommonFunctions.SendCommandToService("Time -actionable");
m_eventHandlerRegistered = true;
}
m_refreshTimer = new DispatcherTimer();
m_refreshTimer.Interval = TimeSpan.FromSeconds(5);
m_refreshTimer.Tick += RefreshTimer_Tick;
m_refreshTimer.Start();
if (IntPtr.Size == 8)
{
TextBlockInstance.Text = "64-bit";
}
else
{
TextBlockInstance.Text = "32-bit";
}
TextBlockLocalTime.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
Version appVersion = AssemblyInfo.EntryAssembly.Version;
TextBlockManagerVersion.Text = appVersion.Major + "." + appVersion.Minor + "." + appVersion.Build + ".0";
try
{
using (AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory))
{
TextBlockDatabaseType.Text = database.DatabaseType.ToString();
try
{
if (database.IsSqlite || database.IsJetEngine)
{
// Extract database file name from connection string for file centric databases
TextBlockDatabaseName.Text = FilePath.GetFileName(database.Connection.ConnectionString.ParseKeyValuePairs()["Data Source"]);
}
else if (database.IsOracle)
{
// Extract user name from connection string for Oracle databases
TextBlockDatabaseName.Text = database.Connection.ConnectionString.ParseKeyValuePairs()["User Id"];
}
else
{
TextBlockDatabaseName.Text = database.Connection.Database;
}
}
catch
{
// Fall back on database name if file anything fails
TextBlockDatabaseName.Text = database.Connection.Database;
}
}
}
catch
{
TextBlockDatabaseName.Text = "Not Avaliable";
}
try
{
using (UserInfo info = new UserInfo(CommonFunctions.CurrentUser))
{
if (info.Exists)
{
TextBlockUser.Text = info.LoginID;
}
else
{
TextBlockUser.Text = CommonFunctions.CurrentUser;
}
}
}
catch
{
TextBlockUser.Text = CommonFunctions.CurrentUser;
}
((HorizontalAxis)ChartPlotterDynamic.MainHorizontalAxis).LabelProvider.LabelStringFormat = "";
//Remove legend on the right.
Panel legendParent = (Panel)ChartPlotterDynamic.Legend.ContentGrid.Parent;
if (legendParent != null)
{
legendParent.Children.Remove(ChartPlotterDynamic.Legend.ContentGrid);
}
ChartPlotterDynamic.NewLegendVisible = false;
m_xAxisDataCollection = new int[NumberOfPointsToPlot];
for (int i = 0; i < NumberOfPointsToPlot; i++)
{
m_xAxisDataCollection[i] = i;
}
m_xAxisBindingCollection = new EnumerableDataSource <int>(m_xAxisDataCollection);
m_xAxisBindingCollection.SetXMapping(x => x);
LoadComboBoxDeviceAsync();
}
private void SetupGrafanaHostingAdapter()
{
try
{
const string GrafanaProcessAdapterName = "GRAFANA!PROCESS";
const string DefaultGrafanaServerPath = GrafanaAuthProxyController.DefaultServerPath;
const string GrafanaAdminRoleName = GrafanaAuthProxyController.GrafanaAdminRoleName;
const string GrafanaAdminRoleDescription = "Grafana Administrator Role";
// Access needed settings from specified categories in configuration file
CategorizedSettingsElementCollection systemSettings = ConfigurationFile.Current.Settings["systemSettings"];
CategorizedSettingsElementCollection grafanaHosting = ConfigurationFile.Current.Settings["grafanaHosting"];
string newNodeID = Guid.NewGuid().ToString();
// Make sure needed settings exist
systemSettings.Add("NodeID", newNodeID, "Unique Node ID");
grafanaHosting.Add("ServerPath", DefaultGrafanaServerPath, "Defines the path to the Grafana server to host - set to empty string to disable hosting.");
// Get settings as currently defined in configuration file
Guid nodeID = Guid.Parse(systemSettings["NodeID"].ValueAs(newNodeID));
string grafanaServerPath = grafanaHosting["ServerPath"].ValueAs(DefaultGrafanaServerPath);
// Only enable adapter if file path to configured Grafana server executable is accessible
bool enabled = File.Exists(FilePath.GetAbsolutePath(grafanaServerPath));
// Open database connection as defined in configuration file "systemSettings" category
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
// Make sure Grafana process adapter exists
TableOperations <CustomActionAdapter> actionAdapterTable = new TableOperations <CustomActionAdapter>(connection);
CustomActionAdapter actionAdapter = actionAdapterTable.QueryRecordWhere("AdapterName = {0}", GrafanaProcessAdapterName) ?? actionAdapterTable.NewRecord();
// Update record fields
actionAdapter.NodeID = nodeID;
actionAdapter.AdapterName = GrafanaProcessAdapterName;
actionAdapter.AssemblyName = "FileAdapters.dll";
actionAdapter.TypeName = "FileAdapters.ProcessLauncher";
actionAdapter.Enabled = enabled;
// Define default adapter connection string if none is defined
if (string.IsNullOrWhiteSpace(actionAdapter.ConnectionString))
{
actionAdapter.ConnectionString =
$"FileName={DefaultGrafanaServerPath}; " +
$"WorkingDirectory={FilePath.GetAbsolutePath("Grafana")}; " +
"ForceKillOnDispose=True; " +
"ProcessOutputAsLogMessages=True; " +
"LogMessageTextExpression={(?<=.*msg\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*file\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*file\\s*\\=\\s*)[^\\s]*(?=s|$)|(?<=.*path\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*path\\s*\\=\\s*)[^\\s]*(?=s|$)|(?<=.*error\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*reason\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*id\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*version\\s*\\=\\s*)[^\\s]*(?=\\s|$)|(?<=.*dbtype\\s*\\=\\s*)[^\\s]*(?=\\s|$)|(?<=.*)commit\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)compiled\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)address\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)protocol\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)subUrl\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)code\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*name\\s*\\=\\s*)[^\\s]*(?=\\s|$)}; " +
"LogMessageLevelExpression={(?<=.*lvl\\s*\\=\\s*)[^\\s]*(?=\\s|$)}; " +
"LogMessageLevelMappings={info=Info; warn=Waning; error=Error; critical=Critical; debug=Debug}";
}
// Preserve connection string on existing records except for Grafana server executable path that comes from configuration file
Dictionary <string, string> settings = actionAdapter.ConnectionString.ParseKeyValuePairs();
settings["FileName"] = grafanaServerPath;
actionAdapter.ConnectionString = settings.JoinKeyValuePairs();
// Save record updates
actionAdapterTable.AddNewOrUpdateRecord(actionAdapter);
// Make sure Grafana admin role exists
TableOperations <ApplicationRole> applicationRoleTable = new TableOperations <ApplicationRole>(connection);
ApplicationRole applicationRole = applicationRoleTable.QueryRecordWhere("Name = {0} AND NodeID = {1}", GrafanaAdminRoleName, nodeID);
if ((object)applicationRole == null)
{
applicationRole = applicationRoleTable.NewRecord();
applicationRole.NodeID = nodeID;
applicationRole.Name = GrafanaAdminRoleName;
applicationRole.Description = GrafanaAdminRoleDescription;
applicationRoleTable.AddNewRecord(applicationRole);
}
}
}
catch (Exception ex)
{
LogPublisher log = Logger.CreatePublisher(typeof(ServiceHost), MessageClass.Application);
log.Publish(MessageLevel.Error, "Error Message", "Failed to setup Grafana hosting adapter", null, ex);
}
}
// Static Methods
/// <summary>
/// Gets a dependency resolver.
/// </summary>
/// <param name="webServer"><see cref="WebServer"/> instance to use for controller; uses default instance if not provided.</param>
/// <param name="defaultWebPage">Default web page name to use for controller; uses "index.html" if not provided.</param>
/// <param name="model">Reference to model to use when rendering Razor templates, if any.</param>
/// <param name="modelType">Type of <paramref name="model"/>, if any.</param>
/// <param name="connection"><see cref="AdoDataConnection"/> to use, if any.</param>
/// <returns>Dependency resolver for the specified parameters.</returns>
public static IDependencyResolver GetDependencyResolver(WebServer webServer, string defaultWebPage = null, object model = null, Type modelType = null, AdoDataConnection connection = null)
{
return(new WebPageControllerResolver(webServer, defaultWebPage, model, modelType, connection));
}
/// <summary>
/// Loads <see cref="Node"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="Subscriber"/>.</returns>
public static ObservableCollection<Subscriber> Load(AdoDataConnection database)
{
ObservableCollection<Subscriber> subscriberList;
DataTable subscriberTable;
bool createdConnection = false;
string query;
SslPolicyErrors validPolicyErrors;
X509ChainStatusFlags validChainFlags;
try
{
createdConnection = CreateConnection(ref database);
subscriberList = new ObservableCollection<Subscriber>();
query = database.ParameterizedQueryString("SELECT ID, NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile," +
" ValidPolicyErrors, ValidChainFlags, AccessControlFilter, Enabled FROM Subscriber WHERE NodeID = {0} ORDER BY Name", "nodeID");
subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in subscriberTable.Rows)
{
subscriberList.Add(new Subscriber()
{
ID = database.Guid(row, "ID"),
NodeID = database.Guid(row, "NodeID"),
Acronym = row.Field<string>("Acronym"),
Name = row.Field<string>("Name"),
SharedSecret = row.Field<string>("SharedSecret"),
AuthKey = row.Field<string>("AuthKey"),
ValidIPAddresses = row.Field<string>("ValidIPAddresses"),
RemoteCertificateFile = row.Field<string>("RemoteCertificateFile"),
ValidPolicyErrors = Enum.TryParse(row.Field<string>("ValidPolicyErrors"), out validPolicyErrors) ? validPolicyErrors : (SslPolicyErrors?)null,
ValidChainFlags = Enum.TryParse(row.Field<string>("ValidChainFlags"), out validChainFlags) ? validChainFlags : (X509ChainStatusFlags?)null,
AccessControlFilter = row.Field<string>("AccessControlFilter"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
AllowedMeasurementGroups = GetAllowedMeasurementGroups(database, database.Guid(row, "ID")),
DeniedMeasurementGroups = GetDeniedMeasurementGroups(database, database.Guid(row, "ID")),
AvailableMeasurementGroups = GetAvailableMeasurementGroups(database, database.Guid(row, "ID")),
StatusColor = "gray",
Version = ""
});
}
return subscriberList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection.
/// </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}"/> type collection.</returns>
/// <remarks>This is only a place holder method with no implementation.</remarks>
public static Dictionary <int, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
return(null);
}
/// <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>
/// Saves <see cref="RealTimeStream"/> information into the database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="stream">Information about <see cref="RealTimeStream"/></param>
/// <returns>String, for display use, indicating success.</returns>
/// <remarks>This is only a place holder method with no implementation.</remarks>
public static string Save(AdoDataConnection database, RealTimeStream stream)
{
return(string.Empty);
}
/// <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>
/// Deletes <see cref="RealTimeStream"/> record from the database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="streamID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
/// <remarks>This is only a place holder method with no implementation.</remarks>
public static string Delete(AdoDataConnection database, int streamID)
{
return(string.Empty);
}
/// <summary>
/// Creates a data operation to run the validations in this class.
/// </summary>
/// <param name="database">Database connection to use for creating the data operation</param>
private static void CreateDataOperation(AdoDataConnection database)
{
database.ExecuteNonQuery($"INSERT INTO DataOperation(Description, AssemblyName, TypeName, MethodName, Enabled) VALUES ('Power Calculation Validations', 'PowerCalculations.dll', '{typeof(PowerCalculationConfigurationValidation).FullName}', 'ValidatePowerCalculationConfigurations', 1)");
}
public void getContourAnimationFrame()
{
int animationID = Convert.ToInt32(HttpContext.Current.Request.QueryString["animation"]);
int frameIndex = Convert.ToInt32(HttpContext.Current.Request.QueryString["frame"]);
byte[] frameImage;
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
frameImage = connection.ExecuteScalar<byte[]>("SELECT FrameImage FROM ContourAnimationFrame WHERE ContourAnimationID = {0} AND FrameIndex = {1}", animationID, frameIndex);
}
HttpContext.Current.Response.ContentType = "image/png";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("inline;filename=ContourFrame{0}x{1}.png", animationID, frameIndex));
HttpContext.Current.Response.BinaryWrite(frameImage);
}
/// <summary>
/// Creates a data operation to run the validations in this class.
/// </summary>
/// <param name="database">Database connection to use for creating the data operation</param>
private static void CreateAdapterInstance(AdoDataConnection database)
{
database.ExecuteNonQuery($"INSERT INTO CustomActionAdapter(NodeID, AdapterName, AssemblyName, TypeName, ConnectionString, Enabled) VALUES ('{ConfigurationFile.Current.Settings["systemSettings"]["NodeID"].ValueAs<Guid>()}', 'PHASOR!POWERCALC', 'PowerCalculations.dll', '{typeof(PowerMultiCalculatorAdapter).FullName}', 'FramesPerSecond=30; LagTime=5.0; LeadTime=3.0', 1)");
}
private PiecewiseLinearFunction GetColorScale(ContourQuery contourQuery)
{
DataTable colorScale;
using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
string query =
"SELECT " +
" ContourColorScalePoint.Value, " +
" ContourColorScalePoint.Color " +
"FROM " +
" ContourColorScale JOIN " +
" ContourColorScalePoint ON ContourColorScalePoint.ContourColorScaleID = ContourColorScale.ID " +
"WHERE ContourColorScale.Name = {0} " +
"ORDER BY ContourColorScalePoint.OrderID";
colorScale = conn.RetrieveData(query, contourQuery.ColorScaleName);
}
double[] colorDomain = colorScale
.Select()
.Select(row => row.ConvertField<double>("Value"))
.ToArray();
double[] colorRange = colorScale
.Select()
.Select(row => (double)(uint)row.ConvertField<int>("Color"))
.ToArray();
return new PiecewiseLinearFunction()
.SetDomain(colorDomain)
.SetRange(colorRange);
}
/// <summary>
/// Loads configuration from the database and configures adapter to run with that configuration
/// </summary>
public override void Initialize()
{
base.Initialize();
HashSet <IMeasurement> outputMeasurements = new HashSet <IMeasurement>();
m_configuredCalculations = new List <PowerCalculation>();
m_adjustmentStrategies = new Dictionary <MeasurementKey, VoltageAdjustmentStrategy>();
m_averageCalculationsPerFrame = new RunningAverage();
m_averageCalculationTime = new RunningAverage();
m_averageTotalCalculationTime = new RunningAverage();
m_lastActivePowerCalculations = new ConcurrentQueue <IMeasurement>();
m_lastReactivePowerCalculations = new ConcurrentQueue <IMeasurement>();
m_lastApparentPowerCalculations = new ConcurrentQueue <IMeasurement>();
if (!Settings.TryGetValue(nameof(TableName), out string tableName))
{
tableName = DefaultTableName;
}
string query = "SELECT " +
// 0 1 2 3 4 5
"ID, CircuitDescription, VoltageAngleSignalID, VoltageMagSignalID, CurrentAngleSignalID, CurrentMagSignalID, " +
// 6 7 8
"ActivePowerOutputSignalID, ReactivePowerOutputSignalID, ApparentPowerOutputSignalID FROM " + tableName + " " +
"WHERE NodeId = {0} AND Enabled <> 0 ";
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
using (IDataReader rdr = database.ExecuteReader(query, ConfigurationFile.Current.Settings["systemSettings"]["NodeID"].ValueAs <Guid>()))
{
while (rdr.Read())
{
m_configuredCalculations.Add(new PowerCalculation
{
PowerCalculationID = rdr.GetInt32(0),
CircuitDescription = rdr.GetString(1),
VoltageAngleMeasurementKey = MeasurementKey.LookUpBySignalID(Guid.Parse(rdr[2].ToString())),
VoltageMagnitudeMeasurementKey = MeasurementKey.LookUpBySignalID(Guid.Parse(rdr[3].ToString())),
CurrentAngleMeasurementKey = MeasurementKey.LookUpBySignalID(Guid.Parse(rdr[4].ToString())),
CurrentMagnitudeMeasurementKey = MeasurementKey.LookUpBySignalID(Guid.Parse(rdr[5].ToString())),
ActivePowerOutputMeasurement = AddOutputMeasurement(Guid.Parse(rdr[6].ToString()), outputMeasurements),
ReactivePowerOutputMeasurement = AddOutputMeasurement(Guid.Parse(rdr[7].ToString()), outputMeasurements),
ApparentPowerOutputMeasurement = AddOutputMeasurement(Guid.Parse(rdr[8].ToString()), outputMeasurements)
});
}
}
if (m_configuredCalculations.Any())
{
InputMeasurementKeys = m_configuredCalculations.SelectMany(pc => new[] { pc.CurrentAngleMeasurementKey, pc.CurrentMagnitudeMeasurementKey, pc.VoltageAngleMeasurementKey, pc.VoltageMagnitudeMeasurementKey }).ToArray();
}
else
{
throw new InvalidOperationException("Skipped initialization of power calculator: no defined power calculations...");
}
if (outputMeasurements.Any())
{
OutputMeasurements = outputMeasurements.ToArray();
}
Dictionary <string, string> settings = Settings;
if (settings.TryGetValue(nameof(AlwaysProduceResult), out string setting))
{
AlwaysProduceResult = setting.ParseBoolean();
}
if (settings.TryGetValue(nameof(AdjustmentStrategy), out setting) && Enum.TryParse(setting, out VoltageAdjustmentStrategy adjustmentStrategy))
{
AdjustmentStrategy = adjustmentStrategy;
}
if (settings.TryGetValue(nameof(EnableTemporalProcessing), out setting))
{
EnableTemporalProcessing = setting.ParseBoolean();
}
// Define per power calculation line adjustment strategies
foreach (PowerCalculation powerCalculation in m_configuredCalculations)
{
if (powerCalculation.VoltageMagnitudeMeasurementKey is null || string.IsNullOrWhiteSpace(powerCalculation.CircuitDescription))
{
continue;
}
try
{
Dictionary <string, string> circuitSettings = powerCalculation.CircuitDescription.ParseKeyValuePairs();
if (circuitSettings.TryGetValue(nameof(AdjustmentStrategy), out setting) && Enum.TryParse(setting, out adjustmentStrategy))
{
m_adjustmentStrategies[powerCalculation.VoltageMagnitudeMeasurementKey] = adjustmentStrategy;
}
}
catch (Exception ex)
{
OnStatusMessage(MessageLevel.Warning, $"Failed to parse settings from circuit description \"{powerCalculation.CircuitDescription}\": {ex.Message}");
}
}
}
public ContourAnimationInfo getContourAnimations(ContourQuery contourQuery)
{
List<List<TrendingDataLocation>> frames = GetFramesFromHistorian(contourQuery);
PiecewiseLinearFunction colorScale = GetColorScale(contourQuery);
Func<double, double> colorFunc = colorScale;
// The actual startDate is the timestamp of the
// first frame after contourQuery.GetStartDate()
DateTime startDate = contourQuery.GetStartDate();
int stepSize = contourQuery.StepSize;
int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize);
startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize);
double minLat = frames.Min(frame => frame.Min(location => location.Latitude)) - GetLatFromMiles(50.0D);
double maxLat = frames.Min(frame => frame.Max(location => location.Latitude)) + GetLatFromMiles(50.0D);
double minLng = frames.Min(frame => frame.Min(location => location.Longitude)) - GetLngFromMiles(50.0D, 0.0D);
double maxLng = frames.Min(frame => frame.Max(location => location.Longitude)) + GetLngFromMiles(50.0D, 0.0D);
GeoCoordinate topLeft = new GeoCoordinate(maxLat, minLng);
GeoCoordinate bottomRight = new GeoCoordinate(minLat, maxLng);
GSF.Drawing.Point topLeftPoint = s_crs.Translate(topLeft, contourQuery.Resolution);
GSF.Drawing.Point bottomRightPoint = s_crs.Translate(bottomRight, contourQuery.Resolution);
topLeftPoint = new GSF.Drawing.Point(Math.Floor(topLeftPoint.X), Math.Floor(topLeftPoint.Y));
bottomRightPoint = new GSF.Drawing.Point(Math.Ceiling(bottomRightPoint.X), Math.Ceiling(bottomRightPoint.Y));
topLeft = s_crs.Translate(topLeftPoint, contourQuery.Resolution);
bottomRight = s_crs.Translate(bottomRightPoint, contourQuery.Resolution);
int width = (int)(bottomRightPoint.X - topLeftPoint.X + 1);
int height = (int)(bottomRightPoint.Y - topLeftPoint.Y + 1);
int animationID;
string timeZoneID = null;
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
connection.ExecuteNonQuery("INSERT INTO ContourAnimation(ColorScaleName, StartTime, EndTime, StepSize) VALUES({0}, {1}, {2}, {3})", contourQuery.ColorScaleName, contourQuery.GetStartDate(), contourQuery.GetEndDate(), contourQuery.StepSize);
animationID = connection.ExecuteScalar<int>("SELECT @@IDENTITY");
if (contourQuery.IncludeWeather)
timeZoneID = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'XDATimeZone'");
}
GSF.Threading.CancellationToken cancellationToken = new GSF.Threading.CancellationToken();
s_cancellationTokens[animationID] = cancellationToken;
ProgressCounter progressCounter = new ProgressCounter(frames.Count);
s_progressCounters[animationID] = progressCounter;
Action<int> createFrame = i =>
{
List<TrendingDataLocation> frame = frames[i];
IDWFunc idwFunction = GetIDWFunction(contourQuery, frame);
uint[] pixelData;
if (contourQuery.IncludeWeather)
{
TimeZoneInfo tzInfo = !string.IsNullOrEmpty(timeZoneID)
? TimeZoneInfo.FindSystemTimeZoneById(timeZoneID)
: TimeZoneInfo.Local;
// Weather data is only available in 5-minute increments
DateTime frameTime = TimeZoneInfo.ConvertTimeToUtc(startDate.AddMinutes(stepSize * i), tzInfo);
double minutes = (frameTime - frameTime.Date).TotalMinutes;
int weatherMinutes = (int)Math.Ceiling(minutes / 5) * 5;
NameValueCollection queryString = HttpUtility.ParseQueryString(string.Empty);
queryString["service"] = "WMS";
queryString["request"] = "GetMap";
queryString["layers"] = "nexrad-n0r-wmst";
queryString["format"] = "image/png";
queryString["transparent"] = "true";
queryString["version"] = "1.1.1";
queryString["time"] = frameTime.Date.AddMinutes(weatherMinutes).ToString("o");
queryString["height"] = height.ToString();
queryString["width"] = width.ToString();
queryString["srs"] = "EPSG:3857";
GSF.Drawing.Point topLeftProjected = s_crs.Projection.Project(topLeft);
GSF.Drawing.Point bottomRightProjected = s_crs.Projection.Project(bottomRight);
queryString["bbox"] = string.Join(",", topLeftProjected.X, bottomRightProjected.Y, bottomRightProjected.X, topLeftProjected.Y);
string weatherURL = "http://mesonet.agron.iastate.edu/cgi-bin/wms/nexrad/n0r-t.cgi?" + queryString.ToString();
using (WebClient client = new WebClient())
using (MemoryStream stream = new MemoryStream(client.DownloadData(weatherURL)))
using (Bitmap bitmap = new Bitmap(stream))
{
pixelData = bitmap.ToPixelData();
}
}
else
{
pixelData = new uint[width * height];
}
if (cancellationToken.IsCancelled)
return;
for (int x = 0; x < width; x++)
{
if (cancellationToken.IsCancelled)
return;
for (int y = 0; y < height; y++)
{
if (cancellationToken.IsCancelled)
return;
if (pixelData[y * width + x] > 0)
continue;
GSF.Drawing.Point offsetPixel = new GSF.Drawing.Point(topLeftPoint.X + x, topLeftPoint.Y + y);
GeoCoordinate pixelCoordinate = s_crs.Translate(offsetPixel, contourQuery.Resolution);
double interpolatedValue = idwFunction(pixelCoordinate.Longitude, pixelCoordinate.Latitude);
pixelData[y * width + x] = (uint)colorFunc(interpolatedValue);
}
}
if (cancellationToken.IsCancelled)
return;
using (Bitmap bitmap = BitmapExtensions.FromPixelData(width, pixelData))
using (MemoryStream stream = new MemoryStream())
{
bitmap.Save(stream, ImageFormat.Png);
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
connection.ExecuteNonQuery("INSERT INTO ContourAnimationFrame VALUES({0}, {1}, {2})", animationID, i, stream.ToArray());
}
}
progressCounter.Increment();
};
Task.Run(() =>
{
ICancellationToken token;
ProgressCounter counter;
Parallel.For(0, frames.Count, createFrame);
s_cancellationTokens.TryRemove(animationID, out token);
s_progressCounters.TryRemove(animationID, out counter);
if (cancellationToken.IsCancelled)
{
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
connection.ExecuteNonQuery("DELETE FROM ContourAnimationFrame WHERE ContourAnimationID = {0}", animationID);
connection.ExecuteNonQuery("DELETE FROM ContourAnimation WHERE ID = {0}", animationID);
}
}
});
s_cleanUpAnimationOperation.TryRunOnceAsync();
return new ContourAnimationInfo()
{
AnimationID = animationID,
ColorDomain = colorScale.Domain,
ColorRange = colorScale.Range,
MinLatitude = bottomRight.Latitude,
MaxLatitude = topLeft.Latitude,
MinLongitude = topLeft.Longitude,
MaxLongitude = bottomRight.Longitude,
Infos = frames.Select((frame, index) => new ContourInfo()
{
Locations = frame,
URL = string.Format("./mapService.asmx/getContourAnimationFrame?animation={0}&frame={1}", animationID, index),
Date = contourQuery.GetStartDate().AddMinutes(index * contourQuery.StepSize).ToString()
}).ToList()
};
}
/// <summary>
/// Retrieves <see cref="ObservableCollection{T}"/> type collection of <see cref="InputWizardDevice"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="InputWizardDevice"/>.</returns>
public static ObservableCollection <InputWizardDevice> Load(AdoDataConnection database)
{
return(new ObservableCollection <InputWizardDevice>());
}
private InverseDistanceWeightingFunction GetIDWFunction(ContourQuery contourQuery, List<TrendingDataLocation> locations = null)
{
CoordinateReferenceSystem crs = new EPSG3857();
List<double> xList = new List<double>();
List<double> yList = new List<double>();
List<double> valueList = new List<double>();
if ((object)locations == null)
locations = GetFrameFromDailySummary(contourQuery);
locations
.Select(location =>
{
GeoCoordinate Coordinate = new GeoCoordinate(location.Latitude, location.Longitude);
double? Value =
(contourQuery.DataType == "Average") ? location.Average :
(contourQuery.DataType == "Minimum") ? location.Minimum :
(contourQuery.DataType == "Maximum") ? location.Maximum :
null;
return new { Coordinate, Value };
})
.Where(obj => (object)obj.Value != null)
.ToList()
.ForEach(obj =>
{
xList.Add(obj.Coordinate.Longitude);
yList.Add(obj.Coordinate.Latitude);
valueList.Add(obj.Value.GetValueOrDefault());
});
if (valueList.Count == 0)
{
xList.Add(0.0D);
yList.Add(0.0D);
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
valueList.Add(connection.ExecuteScalar<double>("SELECT NominalValue FROM ContourColorScale WHERE Name = {0}", contourQuery.ColorScaleName));
}
}
return new InverseDistanceWeightingFunction()
.SetXCoordinates(xList.ToArray())
.SetYCoordinates(yList.ToArray())
.SetValues(valueList.ToArray())
.SetDistanceFunction((x1, y1, x2, y2) =>
{
GeoCoordinate coordinate1 = new GeoCoordinate(y1, x1);
GeoCoordinate coordinate2 = new GeoCoordinate(y2, x2);
return crs.Distance(coordinate1, coordinate2);
});
}
public IHttpActionResult Post(DetailtsForSitesForm form)
{
try
{
using (AdoDataConnection XDAconnection = new AdoDataConnection("dbOpenXDA"))
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
DateTime date = DateTime.Parse(form.targetDate).ToUniversalTime();
DataTable table = XDAconnection.RetrieveData(Query, date, form.siteId, form.context, form.colorScale);
IEnumerable <ValueList> chartSettings = new TableOperations <ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')");
int groupID = connection.ExecuteScalar <int>($"SELECT ID FROM ValueListGroup WHERE Name = 'Chart.{Tab}'");
List <string> skipColumns;
if (Tab == "Events" || Tab == "Disturbances")
{
skipColumns = new List <string>()
{
"EventID", "MeterID", "Site"
}
}
;
else
{
skipColumns = table.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToList();
}
List <string> columnsToRemove = new List <string>();
foreach (DataColumn column in table.Columns)
{
if (skipColumns.Contains(column.ColumnName))
{
continue;
}
//if (!chartSettings.Any(x => x.Text == column.ColumnName))
//{
// Random r = new Random(DateTime.UtcNow.Millisecond);
// new TableOperations<ValueList>(connection).AddNewRecord(new ValueList
// {
// Key = 0,
// GroupID = groupID,
// Text = column.ColumnName,
// Flag = false,
// AltText1 = "#" + r.Next(256).ToString("X2") + r.Next(256).ToString("X2") + r.Next(256).ToString("X2"),
// IsDefault = false,
// Hidden = false,
// Enabled = true
// });
// chartSettings = new TableOperations<ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')");
//}
ValueList setting = chartSettings.FirstOrDefault(x => x.Text == column.ColumnName);
if (setting != null && setting.Enabled == false)
{
columnsToRemove.Add(column.ColumnName);
}
}
foreach (string columnName in columnsToRemove)
{
table.Columns.Remove(columnName);
}
return(Ok(table));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public WebPageControllerResolver(WebServer webServer, string defaultWebPage, object model, Type modelType, AdoDataConnection database)
{
m_webServer = webServer;
m_defaultWebPage = defaultWebPage;
m_model = model;
m_modelType = modelType;
m_database = database;
}
public ContourInfo getLocationsTrendingData(ContourQuery contourQuery)
{
List<TrendingDataLocation> locations = new List<TrendingDataLocation>();
DataTable colorScale;
using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
using (IDbCommand cmd = conn.Connection.CreateCommand())
{
cmd.Parameters.Add(new SqlParameter("@EventDateFrom", contourQuery.GetStartDate()));
cmd.Parameters.Add(new SqlParameter("@EventDateTo", contourQuery.GetEndDate()));
cmd.Parameters.Add(new SqlParameter("@colorScaleName", contourQuery.ColorScaleName));
cmd.Parameters.Add(new SqlParameter("@username", contourQuery.UserName));
cmd.CommandText = "dbo.selectMeterLocationsTrendingData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
TrendingDataLocation ourStatus = new TrendingDataLocation();
ourStatus.Latitude = (double)rdr["Latitude"];
ourStatus.Longitude = (double)rdr["Longitude"];
ourStatus.name = (string)rdr["Name"];
ourStatus.Average = (rdr.IsDBNull(rdr.GetOrdinal("Average")) ? (double?)null : (double)rdr["Average"]);
ourStatus.Maximum = (rdr.IsDBNull(rdr.GetOrdinal("Maximum")) ? (double?)null : (double)rdr["Maximum"]);
ourStatus.Minimum = (rdr.IsDBNull(rdr.GetOrdinal("Minimum")) ? (double?)null : (double)rdr["Minimum"]);
ourStatus.id = (int)rdr["id"];
ourStatus.data.Add(ourStatus.Average);
ourStatus.data.Add(ourStatus.Maximum);
ourStatus.data.Add(ourStatus.Minimum);
locations.Add(ourStatus);
}
}
string query =
"SELECT " +
" ContourColorScalePoint.Value, " +
" ContourColorScalePoint.Color " +
"FROM " +
" ContourColorScale JOIN " +
" ContourColorScalePoint ON ContourColorScalePoint.ContourColorScaleID = ContourColorScale.ID " +
"WHERE ContourColorScale.Name = {0} " +
"ORDER BY ContourColorScalePoint.OrderID";
colorScale = conn.RetrieveData(query, contourQuery.ColorScaleName);
}
double[] colorDomain = colorScale
.Select()
.Select(row => row.ConvertField<double>("Value"))
.ToArray();
double[] colorRange = colorScale
.Select()
.Select(row => (double)(uint)row.ConvertField<int>("Color"))
.ToArray();
return new ContourInfo()
{
Locations = locations,
ColorDomain = colorDomain,
ColorRange = colorRange,
};
}
/// <summary>
/// Determines Color based on Channel Information for Full Data Channels
/// </summary>
/// <param name="channel">Channel that represents the signal</param>
/// <returns>A color designation</returns>
public static string GetColor(Channel channel)
{
if (channel == null)
{
return("random");
}
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
if (channel.MeasurementType.Name == "Voltage")
{
switch (channel.Phase.Name)
{
case ("AN"):
return("Va");
case ("BN"):
return("Vb");
case ("CN"):
return("Vc");
case ("AB"):
return("Vab");
case ("BC"):
return("Vbc");
case ("CA"):
return("Vca");
case ("NG"):
return("Ires");
default: // Should be random
return("random");
}
}
else if (channel.MeasurementType.Name == "Current")
{
switch (channel.Phase.Name)
{
case ("AN"):
return("Ia");
case ("BN"):
return("Ib");
case ("CN"):
return("Ic");
case ("NG"):
return("Ires");
case ("RES"):
return("Ires");
default: // Should be random
return("random");
}
}
}
//Should be Random
return("random");
}
private static void CleanUpAnimation()
{
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
DateTime yesterday = DateTime.UtcNow.AddDays(-1);
connection.ExecuteNonQuery("DELETE FROM ContourAnimationFrame WHERE ContourAnimationID IN (SELECT ID FROM ContourAnimation WHERE AccessedOn < {0})", yesterday);
connection.ExecuteNonQuery("DELETE FROM ContourAnimation WHERE AccessedOn < {0}", yesterday);
}
}
// from new EvendData Blob format
private static byte[] ProcessLegacyBlob(int eventID, int requestedSeriesID, AdoDataConnection connection)
{
int eventDataID = connection.ExecuteScalar <int>("SELECT EventDataID FROM ChannelData WHERE SeriesID = {0} AND EventID = {1}", requestedSeriesID, eventID);
byte[] timeDomainData = connection.ExecuteScalar <byte[]>("SELECT TimeDomainData FROM EventData WHERE ID = {0}", eventDataID);
byte[] resultData = null;
// If the blob contains the GZip header,
// use the legacy deserialization algorithm
if (timeDomainData[0] == 0x1F && timeDomainData[1] == 0x8B)
{
return(FromData_Legacy(timeDomainData, requestedSeriesID, eventID, connection));
}
// Restore the GZip header before uncompressing
timeDomainData[0] = 0x1F;
timeDomainData[1] = 0x8B;
byte[] uncompressedData = GZipStream.UncompressBuffer(timeDomainData);
int offset = 0;
int m_samples = LittleEndian.ToInt32(uncompressedData, offset);
offset += sizeof(int);
List <DateTime> times = new List <DateTime>();
while (times.Count < m_samples)
{
int timeValues = LittleEndian.ToInt32(uncompressedData, offset);
offset += sizeof(int);
long currentValue = LittleEndian.ToInt64(uncompressedData, offset);
offset += sizeof(long);
times.Add(new DateTime(currentValue));
for (int i = 1; i < timeValues; i++)
{
currentValue += LittleEndian.ToUInt16(uncompressedData, offset);
offset += sizeof(ushort);
times.Add(new DateTime(currentValue));
}
}
while (offset < uncompressedData.Length)
{
List <DataPoint> dataSeries = new List <DataPoint>();
int seriesID = LittleEndian.ToInt32(uncompressedData, offset);
offset += sizeof(int);
const ushort NaNValue = ushort.MaxValue;
double decompressionOffset = LittleEndian.ToDouble(uncompressedData, offset);
double decompressionScale = LittleEndian.ToDouble(uncompressedData, offset + sizeof(double));
offset += 2 * sizeof(double);
for (int i = 0; i < m_samples; i++)
{
ushort compressedValue = LittleEndian.ToUInt16(uncompressedData, offset);
offset += sizeof(ushort);
double decompressedValue = decompressionScale * compressedValue + decompressionOffset;
if (compressedValue == NaNValue)
{
decompressedValue = double.NaN;
}
dataSeries.Add(new DataPoint()
{
Time = times[i],
Value = decompressedValue
});
}
if (seriesID == requestedSeriesID)
{
resultData = ToData(dataSeries, seriesID, m_samples);
}
// Insert into correct ChannelData.....
connection.ExecuteNonQuery("UPDATE ChannelData SET TimeDomainData = {0} WHERE SeriesID = {1} AND EventID = {2}", ToData(dataSeries, seriesID, m_samples), seriesID, eventID);
}
connection.ExecuteNonQuery("DELETE FROM EventData WHERE ID = {0}", eventDataID);
return(resultData);
}
private List<TrendingDataLocation> GetFrameFromDailySummary(ContourQuery contourQuery)
{
List<TrendingDataLocation> locations = new List<TrendingDataLocation>();
using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
using (IDbCommand cmd = conn.Connection.CreateCommand())
{
cmd.Parameters.Add(new SqlParameter("@EventDateFrom", contourQuery.GetStartDate()));
cmd.Parameters.Add(new SqlParameter("@EventDateTo", contourQuery.GetEndDate()));
cmd.Parameters.Add(new SqlParameter("@colorScaleName", contourQuery.ColorScaleName));
cmd.Parameters.Add(new SqlParameter("@username", contourQuery.UserName));
cmd.CommandText = "dbo.selectMeterLocationsTrendingData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
TrendingDataLocation ourStatus = new TrendingDataLocation();
ourStatus.Latitude = (double)rdr["Latitude"];
ourStatus.Longitude = (double)rdr["Longitude"];
ourStatus.name = (string)rdr["Name"];
ourStatus.Average = (rdr.IsDBNull(rdr.GetOrdinal("Average")) ? (double?)null : (double)rdr["Average"]);
ourStatus.Maximum = (rdr.IsDBNull(rdr.GetOrdinal("Maximum")) ? (double?)null : (double)rdr["Maximum"]);
ourStatus.Minimum = (rdr.IsDBNull(rdr.GetOrdinal("Minimum")) ? (double?)null : (double)rdr["Minimum"]);
ourStatus.id = (int)rdr["id"];
locations.Add(ourStatus);
}
}
}
if (!string.IsNullOrEmpty(contourQuery.Meters))
{
const int byteSize = 8;
// Meter selections are stored as a base-64 string without padding, using '-' instead of '+' and '_' instead of '/'
string padding = "A==".Remove(3 - (contourQuery.Meters.Length + 3) % 4);
string base64 = contourQuery.Meters.Replace('-', '+').Replace('_', '/') + padding;
byte[] meterSelections = Convert.FromBase64String(base64);
// The resulting byte array is a simple set of bitflags ordered by meter ID and packed into the most significant bits.
// In order to properly interpret the bytes, we must first order the data by meter ID to determine the location of
// each meter's bitflag. Then we can filter out the unwanted data from the original list of meters
locations = locations
.OrderBy(location => location.id)
.Where((location, index) => (meterSelections[index / byteSize] & (0x80 >> (index % byteSize))) > 0)
.ToList();
}
return locations;
}
// From old EventDataBlob format
private static byte[] FromData_Legacy(byte[] data, int requestedSeriesID, int eventID, AdoDataConnection connection)
{
byte[] resultData = null;
byte[] uncompressedData;
int offset;
DateTime[] times;
int seriesID;
uncompressedData = GZipStream.UncompressBuffer(data);
offset = 0;
int m_samples = LittleEndian.ToInt32(uncompressedData, offset);
offset += sizeof(int);
times = new DateTime[m_samples];
for (int i = 0; i < m_samples; i++)
{
times[i] = new DateTime(LittleEndian.ToInt64(uncompressedData, offset));
offset += sizeof(long);
}
while (offset < uncompressedData.Length)
{
seriesID = LittleEndian.ToInt32(uncompressedData, offset);
offset += sizeof(int);
List <DataPoint> points = new List <DataPoint>();
for (int i = 0; i < m_samples; i++)
{
points.Add(new DataPoint()
{
Time = times[i],
Value = LittleEndian.ToDouble(uncompressedData, offset)
});
offset += sizeof(double);
}
if (seriesID == requestedSeriesID)
{
resultData = ToData(points, seriesID, m_samples);
}
// Insert into correct ChannelData.....
connection.ExecuteNonQuery("UPDATE ChannelData SET TimeDomainData = {0} WHERE SeriesID = {1} AND EventID = {2}", ToData(points, seriesID, m_samples), seriesID, eventID);
}
//Remove EventData
int eventDataID = connection.ExecuteScalar <int>("SELECT EventDataID FROM ChannelData WHERE SeriesID = {0} AND EventID = {1}", requestedSeriesID, eventID);
connection.ExecuteNonQuery("DELETE FROM EventData WHERE ID = {0}", eventDataID);
return(resultData);
}
private List<List<TrendingDataLocation>> GetFramesFromHistorian(ContourQuery contourQuery)
{
DataTable idTable;
string historianServer;
string historianInstance;
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
string query =
"SELECT " +
" Channel.ID AS ChannelID, " +
" Meter.ID AS MeterID, " +
" Meter.Name AS MeterName, " +
" MeterLocation.Latitude, " +
" MeterLocation.Longitude, " +
" Channel.PerUnitValue " +
"FROM " +
" Meter JOIN " +
" MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN " +
" Channel ON " +
" Channel.MeterID = Meter.ID AND " +
" Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourColorScaleName = {1}) " +
"WHERE Meter.ID IN (SELECT * FROM authMeters({0}))";
idTable = connection.RetrieveData(query, contourQuery.UserName, contourQuery.ColorScaleName);
historianServer = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
historianInstance = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
}
if (!string.IsNullOrEmpty(contourQuery.Meters))
{
const int byteSize = 8;
// Meter selections are stored as a base-64 string without padding, using '-' instead of '+' and '_' instead of '/'
string padding = "A==".Remove(3 - (contourQuery.Meters.Length + 3) % 4);
string base64 = contourQuery.Meters.Replace('-', '+').Replace('_', '/') + padding;
byte[] meterSelections = Convert.FromBase64String(base64);
// The resulting byte array is a simple set of bitflags ordered by meter ID and packed into the most significant bits.
// In order to properly interpret the bytes, we must first group and order the data by meter ID to determine the location
// of each meter's bitflag. Then we can filter out the unwanted data from the original table of IDs
idTable.Select()
.Select((Row, Index) => new { Row, Index })
.GroupBy(obj => obj.Row.ConvertField<int>("MeterID"))
.OrderBy(grouping => grouping.Key)
.Where((grouping, index) => (meterSelections[index / byteSize] & (0x80 >> (index % byteSize))) == 0)
.SelectMany(grouping => grouping)
.OrderByDescending(obj => obj.Index)
.ToList()
.ForEach(obj => idTable.Rows.RemoveAt(obj.Index));
}
List<DataRow> meterRows = idTable
.Select()
.DistinctBy(row => row.ConvertField<int>("MeterID"))
.ToList();
DateTime startDate = contourQuery.GetStartDate();
DateTime endDate = contourQuery.GetEndDate();
int stepSize = contourQuery.StepSize;
// The frames to be included are those whose timestamps fall
// within the range which is specified by startDate and
// endDate. We start by aligning startDate and endDate with
// the nearest frame timestamps which fall within that range
int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize);
startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize);
int endTimeOffset = (int)Math.Floor((endDate - endDate.Date).TotalMinutes / stepSize);
endDate = endDate.Date.AddMinutes(endTimeOffset * stepSize);
// Since each frame includes data from all timestamps between
// the previous frame's timestamp and its own timestamp, we
// must include one additional frame of data before startDate
startDate = startDate.AddMinutes(-stepSize);
int frameCount = (int)((endDate - startDate).TotalMinutes / stepSize);
List<Dictionary<int, TrendingDataLocation>> frames = Enumerable.Repeat(meterRows, frameCount)
.Select(rows => rows.Select(row => new TrendingDataLocation()
{
id = row.ConvertField<int>("MeterID"),
name = row.ConvertField<string>("MeterName"),
Latitude = row.ConvertField<double>("Latitude"),
Longitude = row.ConvertField<double>("Longitude")
}))
.Select(locations => locations.ToDictionary(location => location.id))
.ToList();
Dictionary<int, double?> nominalLookup = idTable
.Select("ChannelID IS NOT NULL")
.ToDictionary(row => row.ConvertField<int>("ChannelID"), row => row.ConvertField<double?>("PerUnitValue"));
Dictionary<int, List<TrendingDataLocation>> lookup = idTable
.Select("ChannelID IS NOT NULL")
.Select(row =>
{
int meterID = row.ConvertField<int>("MeterID");
return new
{
ChannelID = row.ConvertField<int>("ChannelID"),
Frames = frames.Select(locationLookup => locationLookup[meterID]).ToList()
};
})
.ToDictionary(obj => obj.ChannelID, obj => obj.Frames);
using (Historian historian = new Historian(historianServer, historianInstance))
{
foreach (TrendingDataPoint point in historian.Read(lookup.Keys, startDate, endDate))
{
List<TrendingDataLocation> locations = lookup[point.ChannelID];
// Use ceiling to sort data into the next nearest frame.
// Subtract 1 because startDate was shifted to include one additional frame of data
int frameIndex = (int)Math.Ceiling((point.Timestamp - startDate).TotalMinutes / stepSize) - 1;
if (frameIndex < 0 || frameIndex >= locations.Count)
continue;
TrendingDataLocation frame = locations[frameIndex];
double nominal = nominalLookup[point.ChannelID] ?? 1.0D;
double value = point.Value / nominal;
switch (point.SeriesID)
{
case SeriesID.Minimum:
frame.Minimum = Math.Min(value, frame.Minimum ?? value);
break;
case SeriesID.Maximum:
frame.Maximum = Math.Max(value, frame.Maximum ?? value);
break;
case SeriesID.Average:
frame.Aggregate(value);
frame.Average = frame.GetAverage();
break;
}
}
}
return frames
.Select(frame => frame.Values.ToList())
.ToList();
}
// This is going through this function to migtrate all EventdataBlobs over to ChannelDataBlobs as they are read eventually removing the legacy table (eventData)
public static List <byte[]> DataFromEvent(int eventID, AdoDataConnection connection)
{
List <byte[]> result = new List <byte[]>();
List <int> directChannelIDs = new List <int>();
//This Should start by getting multiple datasets
string query = "SELECT SeriesID FROM ChannelData WHERE EventID = {0}";
DataTable table = connection.RetrieveData(query, eventID);
result.AddRange(table.Rows.Cast <DataRow>().Select(row =>
{
int seriesID = row.Field <int>("SeriesID");
byte[] singleSeriesData = connection.ExecuteScalar <byte[]>("SELECT TimeDomainData FROM ChannelData WHERE SeriesID = {0} AND EventID = {1}"
, seriesID, eventID);
if (singleSeriesData == null)
{
singleSeriesData = ProcessLegacyBlob(eventID, seriesID, connection);
}
directChannelIDs.Add(connection.ExecuteScalar <int>("SELECT ChannelID FROM Series WHERE ID = {0}", seriesID));
return(singleSeriesData);
}
));
//This Will get the extended Data (throught connections)....
Asset asset = new TableOperations <Asset>(connection).QueryRecordWhere("ID = (SELECT AssetID FROM Event WHERE ID = {0})", eventID);
asset.ConnectionFactory = () => { return(new AdoDataConnection(connection.Connection.ConnectionString, typeof(SqlConnection), typeof(SqlDataAdapter))); };
List <int> channelIDs = asset.ConnectedChannels.Select(item => item.ID).ToList();
foreach (int channelID in channelIDs)
{
if (directChannelIDs.Contains(channelID))
{
continue;
}
//Find any Series where Event happens at the same time and ChannelID is ChannelID
//-> note that this assumes any Channel is only associated with a single event at a time
int channelDataID = connection.ExecuteScalar <int>("SELECT COUNT(ChannelData.ID) FROM ChannelData LEFT JOIN Event ON ChannelData.EventID = Event.ID " +
"LEFT JOIN Series ON ChannelData.SeriesID = Series.ID " +
"WHERE(Series.ChannelID = {0}) AND(Event.MeterID = (SELECT EV.MeterID FROM Event EV WHERE EV.ID = {1})) AND " +
"(Event.StartTime <= (SELECT EV.EndTime FROM Event EV WHERE EV.ID = {1})) AND " +
"(Event.EndTime >= (SELECT EV.StartTime FROM Event EV WHERE EV.ID = {1}))", channelID, eventID);
if (channelDataID == 0)
{
continue;
}
channelDataID = connection.ExecuteScalar <int>("SELECT ChannelData.ID FROM ChannelData LEFT JOIN Event ON ChannelData.EventID = Event.ID " +
"LEFT JOIN Series ON ChannelData.SeriesID = Series.ID " +
"WHERE(Series.ChannelID = {0}) AND(Event.MeterID = (SELECT EV.MeterID FROM Event EV WHERE EV.ID = {1})) AND " +
"(Event.StartTime <= (SELECT EV.EndTime FROM Event EV WHERE EV.ID = {1})) AND " +
"(Event.EndTime >= (SELECT EV.StartTime FROM Event EV WHERE EV.ID = {1}))", channelID, eventID);
byte[] singleSeriesData = connection.ExecuteScalar <byte[]>("SELECT TimeDomainData FROM ChannelData WHERE ID = {0}"
, channelDataID);
if (singleSeriesData == null)
{
ChannelData channelData = new TableOperations <ChannelData>(connection).QueryRecordWhere("ID = {0}", channelDataID);
singleSeriesData = ProcessLegacyBlob(channelData.EventID, channelData.SeriesID, connection);
}
result.Add(singleSeriesData);
}
return(result);
}
/// <summary>
/// Handles loaded event for <see cref="SubscriberUserControl"/>.
/// </summary>
/// <param name="sender">Source of the event.</param>
/// <param name="e">Event arguments.</param>
private void SubscriberUserControl_Loaded(object sender, RoutedEventArgs e)
{
// Attach to load/before save event so class can load/add crypto keys from/to local keyIV cache
m_dataContext.PropertyChanged += SubscriberUserControl_PropertyChanged;
m_dataContext.BeforeSave += SubscriberUserControl_BeforeSave;
LoadCurrentKeyIV();
try
{
using (AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory))
{
Dictionary<string, string> settings;
string server;
IPAddress[] hostIPs = null;
IEnumerable<IPAddress> localIPs;
settings = database.DataPublisherConnectionString().ToNonNullString().ParseKeyValuePairs();
if (settings.TryGetValue("server", out server))
hostIPs = Dns.GetHostAddresses(server.Split(':')[0]);
localIPs = Dns.GetHostAddresses("localhost").Concat(Dns.GetHostAddresses(Dns.GetHostName()));
// Check to see if entered host name corresponds to a local IP address
if ((object)hostIPs == null)
MessageBox.Show("Failed to find service host address. If using Gateway security, secure key exchange may not succeed." + Environment.NewLine + "Please make sure to run manager application with administrative privileges on the server where service is hosted.", "Authorize Subcriber", MessageBoxButton.OK, MessageBoxImage.Warning);
else if (!hostIPs.Any(ip => localIPs.Contains(ip)))
MessageBox.Show("If using Gateway security, secure key exchange may not succeed." + Environment.NewLine + "Please make sure to run manager application with administrative privileges on the server where service is hosted.", "Authorize Subscriber", MessageBoxButton.OK, MessageBoxImage.Warning);
}
// If the user is not an Administrator, then the following properties for these controls are readonly and not enable
bool isAdmin = CommonFunctions.CurrentPrincipal.IsInRole("Administrator,Editor");
AcronymField.IsReadOnly = !isAdmin;
NameField.IsReadOnly = !isAdmin;
ValidIpAddressesField.IsReadOnly = !isAdmin;
EnablePGConnectionCheckBox.IsEnabled = isAdmin;
ImportSRQButton.IsEnabled = isAdmin;
ImportCERButton.IsEnabled = isAdmin;
FooterControl.IsEnabled = isAdmin;
}
catch
{
MessageBox.Show("Please make sure to run manager application with administrative privileges on the server where service is hosted.", "Authorize Subscriber", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
/// <summary>
/// Loads <see cref="Adapter"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="adapterType"><see cref="AdapterType"/> collection to be returned.</param>
/// <param name="keys">Keys of the adapters to be loaded from the database.</param>
/// <returns>Collection of <see cref="Adapter"/>.</returns>
public static ObservableCollection <Adapter> Load(AdoDataConnection database, AdapterType adapterType, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string viewName;
if (adapterType == AdapterType.Action)
{
viewName = "CustomActionAdapterDetail";
}
else if (adapterType == AdapterType.Filter)
{
viewName = "CustomFilterAdapterDetail";
}
else if (adapterType == AdapterType.Input)
{
viewName = "CustomInputAdapterDetail";
}
else
{
viewName = "CustomOutputAdapterDetail";
}
string query;
string commaSeparatedKeys;
Adapter[] adapterList = null;
DataTable adapterTable;
int id;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2);
query = database.ParameterizedQueryString(string.Format("SELECT NodeID, ID, AdapterName, AssemblyName, TypeName, ConnectionString, " +
"LoadOrder, Enabled, NodeName FROM {0} WHERE NodeID = {{0}} AND ID IN ({1})", viewName, commaSeparatedKeys), "nodeID");
adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
adapterList = new Adapter[adapterTable.Rows.Count];
foreach (DataRow row in adapterTable.Rows)
{
id = row.ConvertField <int>("ID");
adapterList[keys.IndexOf(id)] = new Adapter()
{
NodeID = database.Guid(row, "NodeID"),
ID = id,
AdapterName = row.Field <string>("AdapterName"),
AssemblyName = row.Field <string>("AssemblyName"),
TypeName = row.Field <string>("TypeName"),
ConnectionString = row.Field <string>("ConnectionString"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
NodeName = row.Field <string>("NodeName"),
Type = adapterType
};
}
}
return(new ObservableCollection <Adapter>(adapterList ?? new Adapter[0]));
}
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>
/// Loads <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="keys">Keys of the measurement to be loaded from the datbase</param>
/// <returns>Collection of <see cref="OutputStreamDevice"/>.</returns>
public static ObservableCollection <OutputStreamDevice> Load(AdoDataConnection database, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
OutputStreamDevice[] outputStreamDeviceList = null;
DataTable outputStreamDeviceTable;
int id;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => key.ToString()).Aggregate((str1, str2) => str1 + "," + str2);
query = string.Format("SELECT NodeID, AdapterID, ID, IDCode, Acronym, BpaAcronym, Name, PhasorDataFormat, " +
"FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, LoadOrder, Enabled, Virtual " +
"FROM OutputStreamDeviceDetail WHERE ID IN ({0})", commaSeparatedKeys);
if (database.IsMySQL)
{
query = string.Format("SELECT NodeID, AdapterID, ID, IDCode, Acronym, BpaAcronym, Name, PhasorDataFormat, " +
"FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, LoadOrder, Enabled, `Virtual` " +
"FROM OutputStreamDeviceDetail WHERE ID IN ({0})", commaSeparatedKeys);
}
outputStreamDeviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout);
outputStreamDeviceList = new OutputStreamDevice[outputStreamDeviceTable.Rows.Count];
foreach (DataRow row in outputStreamDeviceTable.Rows)
{
id = row.ConvertField <int>("ID");
outputStreamDeviceList[keys.IndexOf(id)] = new OutputStreamDevice()
{
NodeID = database.Guid(row, "NodeID"),
AdapterID = row.ConvertField <int>("AdapterID"),
ID = id,
IDCode = row.ConvertField <int>("IDCode"),
Acronym = row.Field <string>("Acronym"),
BpaAcronym = row.Field <string>("BpaAcronym"),
Name = row.Field <string>("Name"),
PhasorDataFormat = row.Field <string>("PhasorDataFormat"),
FrequencyDataFormat = row.Field <string>("FrequencyDataFormat"),
AnalogDataFormat = row.Field <string>("AnalogDataFormat"),
CoordinateFormat = row.Field <string>("CoordinateFormat"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
m_virtual = Convert.ToBoolean(row.Field <object>("Virtual"))
};
}
}
return(new ObservableCollection <OutputStreamDevice>(outputStreamDeviceList ?? new OutputStreamDevice[0]));
}
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>
/// Adds multiple devices to output steam.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamID">ID of the output stream to which devices needs to be added.</param>
/// <param name="devices">Collection of devices to be added.</param>
/// <param name="addDigitals">Boolean flag indicating if analogs should be added.</param>
/// <param name="addAnalogs">Boolean flag indicating if digirals should be added.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string AddDevices(AdoDataConnection database, int outputStreamID, ObservableCollection <Device> devices, bool addDigitals, bool addAnalogs)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Device device in devices)
{
OutputStreamDevice outputStreamDevice = new OutputStreamDevice();
outputStreamDevice.NodeID = device.NodeID;
outputStreamDevice.AdapterID = outputStreamID;
outputStreamDevice.Acronym = device.Acronym.Substring(device.Acronym.LastIndexOf("!", StringComparison.Ordinal) + 1);
outputStreamDevice.BpaAcronym = string.Empty;
outputStreamDevice.Name = device.Name;
outputStreamDevice.LoadOrder = device.LoadOrder;
outputStreamDevice.Enabled = true;
outputStreamDevice.IDCode = device.AccessID;
Save(database, outputStreamDevice);
outputStreamDevice = GetOutputStreamDevice(database, "WHERE Acronym = '" + outputStreamDevice.Acronym + "' AND AdapterID = " + outputStreamID);
if ((object)outputStreamDevice != null)
{
IList <int> keys = Phasor.LoadKeys(database, device.ID);
ObservableCollection <Phasor> phasors = Phasor.Load(database, keys);
foreach (Phasor phasor in phasors)
{
OutputStreamDevicePhasor outputStreamDevicePhasor = new OutputStreamDevicePhasor();
outputStreamDevicePhasor.NodeID = device.NodeID;
outputStreamDevicePhasor.OutputStreamDeviceID = outputStreamDevice.ID;
outputStreamDevicePhasor.Label = phasor.Label;
outputStreamDevicePhasor.Type = phasor.Type;
outputStreamDevicePhasor.Phase = phasor.Phase;
outputStreamDevicePhasor.LoadOrder = phasor.SourceIndex;
OutputStreamDevicePhasor.Save(database, outputStreamDevicePhasor);
}
ObservableCollection <Measurement> measurements = Measurement.Load(database, device.ID);
int analogIndex = 0;
foreach (Measurement measurement in measurements)
{
if (measurement.SignalAcronym != "STAT" && measurement.SignalAcronym != "QUAL")
{
measurement.SignalReference = measurement.SignalReference.Substring(measurement.SignalReference.LastIndexOf("!", StringComparison.Ordinal) + 1);
if ((measurement.SignalAcronym != "ALOG" && measurement.SignalAcronym != "DIGI") || (measurement.SignalAcronym == "ALOG" && addAnalogs) || (measurement.SignalAcronym == "DIGI" && addDigitals))
{
OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement();
outputStreamMeasurement.NodeID = device.NodeID;
outputStreamMeasurement.AdapterID = outputStreamID;
outputStreamMeasurement.HistorianID = measurement.HistorianID;
outputStreamMeasurement.PointID = measurement.PointID;
outputStreamMeasurement.SignalReference = measurement.SignalReference;
OutputStreamMeasurement.Save(database, outputStreamMeasurement);
}
if (addAnalogs && measurement.SignalAcronym == "ALOG")
{
OutputStreamDeviceAnalog outputStreamDeviceAnalog = new OutputStreamDeviceAnalog();
outputStreamDeviceAnalog.NodeID = device.NodeID;
outputStreamDeviceAnalog.OutputStreamDeviceID = outputStreamDevice.ID;
outputStreamDeviceAnalog.Label = string.IsNullOrEmpty(measurement.AlternateTag) ? device.Acronym.Length > 12 ? device.Acronym.Substring(0, 12) + ":A" + analogIndex : device.Acronym + ":A" + analogIndex : measurement.AlternateTag; // measurement.PointTag;
int charIndex = measurement.SignalReference.LastIndexOf("-", StringComparison.Ordinal);
int signalIndex;
if (charIndex >= 0 && charIndex + 3 < measurement.SignalReference.Length && int.TryParse(measurement.SignalReference.Substring(charIndex + 3), out signalIndex))
{
outputStreamDeviceAnalog.LoadOrder = signalIndex;
}
else
{
outputStreamDeviceAnalog.LoadOrder = 999;
}
OutputStreamDeviceAnalog.Save(database, outputStreamDeviceAnalog);
analogIndex++;
}
else if (addDigitals && measurement.SignalAcronym == "DIGI")
{
OutputStreamDeviceDigital outputStreamDeviceDigital = new OutputStreamDeviceDigital();
outputStreamDeviceDigital.NodeID = device.NodeID;
outputStreamDeviceDigital.OutputStreamDeviceID = outputStreamDevice.ID;
outputStreamDeviceDigital.Label = string.IsNullOrEmpty(measurement.AlternateTag) ? DefaultDigitalLabel : measurement.AlternateTag; // measurement.PointTag;
int charIndex = measurement.SignalReference.LastIndexOf("-", StringComparison.Ordinal);
int signalIndex;
if (charIndex >= 0 && charIndex + 3 < measurement.SignalReference.Length && int.TryParse(measurement.SignalReference.Substring(charIndex + 3), out signalIndex))
{
outputStreamDeviceDigital.LoadOrder = signalIndex;
}
else
{
outputStreamDeviceDigital.LoadOrder = 999;
}
OutputStreamDeviceDigital.Save(database, outputStreamDeviceDigital);
}
}
}
}
}
return("Output Stream Device(s) added 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>
/// Gets collection of output stream devices.
/// </summary>
/// <param name="database">Source database connection.</param>
/// <param name="whereClause">Where filter clause.</param>
/// <returns>Collection of output stream devices.</returns>
public static ObservableCollection <OutputStreamDevice> GetOutputStreamDevices(AdoDataConnection database, string whereClause)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection <OutputStreamDevice> outputStreamDeviceList = new ObservableCollection <OutputStreamDevice>();
DataTable outputStreamDeviceTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM OutputStreamDeviceDetail " + whereClause);
foreach (DataRow row in outputStreamDeviceTable.Rows)
{
outputStreamDeviceList.Add(new OutputStreamDevice
{
NodeID = database.Guid(row, "NodeID"),
AdapterID = row.ConvertField <int>("AdapterID"),
ID = row.ConvertField <int>("ID"),
IDCode = row.ConvertField <int>("IDCode"),
Acronym = row.Field <string>("Acronym"),
BpaAcronym = row.Field <string>("BpaAcronym"),
Name = row.Field <string>("Name"),
PhasorDataFormat = row.Field <string>("PhasorDataFormat"),
FrequencyDataFormat = row.Field <string>("FrequencyDataFormat"),
AnalogDataFormat = row.Field <string>("AnalogDataFormat"),
CoordinateFormat = row.Field <string>("CoordinateFormat"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
m_virtual = Convert.ToBoolean(row.Field <object>("Virtual"))
});
}
return(outputStreamDeviceList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="Subscriber"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriber">Information about <see cref="Subscriber"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Subscriber subscriber)
{
bool createdConnection = false;
SslPolicyErrors validPolicyErrors;
X509ChainStatusFlags validChainFlags;
string query;
try
{
createdConnection = CreateConnection(ref database);
validPolicyErrors = (subscriber.ValidPolicyErrors ?? SslPolicyErrors.None) | (subscriber.RemoteCertificateIsSelfSigned ? SslPolicyErrors.RemoteCertificateChainErrors : SslPolicyErrors.None);
validChainFlags = (subscriber.ValidChainFlags ?? X509ChainStatusFlags.NoError) | (subscriber.RemoteCertificateIsSelfSigned ? X509ChainStatusFlags.UntrustedRoot : X509ChainStatusFlags.NoError);
if (subscriber.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO Subscriber (NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile, ValidPolicyErrors, ValidChainFlags, " +
"AccessControlFilter, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " +
"{13}, {14})", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags",
"accessControlFilter", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(),
subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(),
validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE Subscriber SET NodeID = {0}, Acronym = {1}, Name = {2}, SharedSecret = {3}, AuthKey = {4}, ValidIPAddresses = {5}, RemoteCertificateFile = {6}, " +
"ValidPolicyErrors = {7}, ValidChainFlags = {8}, AccessControlFilter = {9}, Enabled = {10}, UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID",
"acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter",
"enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriber.NodeID), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(),
subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(),
validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
database.Guid(subscriber.ID));
}
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
return "Subscriber information saved successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message;
}
return "Subscriber information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
private static void CreateOutputMeasurementsWhereNull(AdoDataConnection database, string nodeIDQueryString, Action <string> statusMessage)
{
statusMessage("Checking for calculations with null output measurements...");
string query =
$"SELECT " +
$" pc.ID, " +
$" pc.CircuitDescription, " +
$" pc.ActivePowerOutputSignalID, " +
$" pc.ApparentPowerOutputSignalID, " +
$" pc.ReactivePowerOutputSignalID, " +
$" v.Acronym AS VendorAcronym, " +
$" d.Acronym AS DeviceAcronym, " +
$" c.Acronym AS CompanyAcronym, " +
$" d.id AS DeviceID, " +
$" vm.HistorianID AS HistorianID, " +
$" p.Label AS CurrentLabel " +
$"FROM " +
$" PowerCalculation pc JOIN " +
$" Measurement vm ON vm.SignalID = pc.VoltageAngleSignalID JOIN " +
$" Measurement im ON im.SignalID = pc.CurrentAngleSignalID LEFT OUTER JOIN " +
$" Phasor p ON im.DeviceID = p.DeviceID AND im.PhasorSourceIndex = p.SourceIndex LEFT OUTER JOIN " +
$" Device d ON vm.DeviceID = d.ID LEFT OUTER JOIN " +
$" VendorDevice vd ON vd.ID = d.VendorDeviceID LEFT OUTER JOIN " +
$" Vendor v ON vd.VendorID = v.ID LEFT OUTER JOIN " +
$" Company c ON d.CompanyID = c.ID " +
$"WHERE " +
$" pc.Enabled <> 0 AND " +
$" pc.NodeID = {nodeIDQueryString} AND " +
$" ( " +
$" pc.ActivePowerOutputSignalID IS NULL OR " +
$" pc.ReactivePowerOutputSignalID IS NULL OR " +
$" pc.ApparentPowerOutputSignalID IS NULL " +
$" )";
Dictionary <int, PowerMeasurement> activePowerUpdates = new Dictionary <int, PowerMeasurement>();
Dictionary <int, PowerMeasurement> reactivePowerUpdates = new Dictionary <int, PowerMeasurement>();
Dictionary <int, PowerMeasurement> apparentPowerUpdates = new Dictionary <int, PowerMeasurement>();
using (IDbCommand cmd = database.Connection.CreateCommand())
{
cmd.CommandText = query;
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
int powerCalculationID = rdr.GetInt32(0);
string companyAcronym = rdr.IsDBNull(7) ? "" : rdr.GetString(7);
string vendorAcronym = rdr.IsDBNull(5) ? "" : rdr.GetString(5);
string signalTypeAcronym = "CALC";
string circuitDescription = rdr.IsDBNull(1) ? "" : rdr.GetString(1);
int deviceID = rdr.GetInt32(8);
int? historianID = rdr.IsDBNull(9) ? null : (int?)rdr.GetInt32(9);
// Remove any settings defined in circuit description
int semicolonIndex = circuitDescription.IndexOf(';');
if (semicolonIndex > -1)
{
circuitDescription = circuitDescription.Substring(0, semicolonIndex);
}
if (rdr.IsDBNull(2)) // Real - MW
{
// create active power output measurement
PowerMeasurement measurement = CreateMeasurement(companyAcronym, circuitDescription + "-MW", vendorAcronym, signalTypeAcronym, circuitDescription, deviceID, historianID, "Active Power Calculation");
activePowerUpdates.Add(powerCalculationID, measurement);
}
if (rdr.IsDBNull(3)) // Apparent - MVA
{
// create apparent power output measurement
PowerMeasurement measurement = CreateMeasurement(companyAcronym, circuitDescription + "-MVA", vendorAcronym, signalTypeAcronym, circuitDescription, deviceID, historianID, "Apparent Power Calculation");
apparentPowerUpdates.Add(powerCalculationID, measurement);
}
if (rdr.IsDBNull(4)) // Reactive - MVAR
{
//create reactive power output measurement
PowerMeasurement measurement = CreateMeasurement(companyAcronym, circuitDescription + "-MVAR", vendorAcronym, signalTypeAcronym, circuitDescription, deviceID, historianID, "Reactive Power Calculation");
reactivePowerUpdates.Add(powerCalculationID, measurement);
}
}
}
}
int newMeasurementsCount = activePowerUpdates.Count + reactivePowerUpdates.Count + apparentPowerUpdates.Count;
if (newMeasurementsCount > 0)
{
MeasurementRepository repo = new MeasurementRepository();
statusMessage($"Creating {newMeasurementsCount} new output measurements for power calculation...");
foreach (KeyValuePair <int, PowerMeasurement> update in activePowerUpdates)
{
repo.Save(database, update.Value);
UpdatePowerCalculation(database, update.Key, activePowerOutputSignalID: update.Value.SignalID);
}
statusMessage("Successfully created new active power calculations.");
foreach (KeyValuePair <int, PowerMeasurement> update in reactivePowerUpdates)
{
repo.Save(database, update.Value);
UpdatePowerCalculation(database, update.Key, reactivePowerOutputSignalID: update.Value.SignalID);
}
statusMessage("Successfully created new reactive power calculations.");
foreach (KeyValuePair <int, PowerMeasurement> update in apparentPowerUpdates)
{
repo.Save(database, update.Value);
UpdatePowerCalculation(database, update.Key, apparentPowerOutputSignalID: update.Value.SignalID);
}
statusMessage("Successfully created new apparent power calculations.");
statusMessage("Completed creation of new measurements for null output measurements on power calculations.");
}
}
public TrendingDataSet getTrendsforChannelIDDate(string ChannelID, string targetDate)
{
//DateTime epoch = new DateTime(1970, 1, 1);
//string theSproc = "dbo.selectTrendingDataByChannelIDDate2";
//DataSet dataSet = new DataSet();
//TrendingDataSet trendingDataSet = new TrendingDataSet();
//using (SqlConnection connection = new SqlConnection(connectionstring))
//using (SqlCommand command = connection.CreateCommand())
//using (SqlDataAdapter adapter = new SqlDataAdapter(command))
//{
// command.CommandText = theSproc;
// command.CommandType = CommandType.StoredProcedure;
// command.Parameters.AddWithValue("@EventDate", targetDate);
// command.Parameters.AddWithValue("@ChannelID", ChannelID);
// command.CommandTimeout = 300;
// connection.Open();
// adapter.Fill(dataSet);
// trendingDataSet.ChannelData = dataSet.Tables[0].Rows
// .Cast<DataRow>()
// .Select(row => new TrendingDataPoint()
// {
// Time = row.Field<DateTime>("thedate").Subtract(epoch).TotalMilliseconds,
// Maximum = row.Field<double>("themaximum"),
// Minimum = row.Field<double>("theminimum"),
// Average = row.Field<double>("theaverage")
// })
// .ToArray();
// trendingDataSet.AlarmLimits = dataSet.Tables[1].Rows
// .Cast<DataRow>()
// .Select(row => new TrendingAlarmLimit()
// {
// TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds,
// TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds,
// High = row.Field<double?>("alarmlimithigh"),
// Low = row.Field<double?>("alarmlimitlow")
// })
// .ToArray();
// trendingDataSet.OffNormalLimits = dataSet.Tables[2].Rows
// .Cast<DataRow>()
// .Select(row => new TrendingAlarmLimit()
// {
// TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds,
// TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds,
// High = row.Field<double?>("offlimithigh"),
// Low = row.Field<double?>("offlimitlow")
// })
// .ToArray();
//}
string historianServer;
string historianInstance;
IEnumerable<int> channelIDs = new List<int>() { Convert.ToInt32(ChannelID) };
DateTime startDate = Convert.ToDateTime(targetDate);
DateTime endDate = startDate.AddDays(1);
TrendingDataSet trendingDataSet = new TrendingDataSet();
DateTime epoch = new DateTime(1970, 1, 1);
using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter)))
{
historianServer = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
historianInstance = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
using (Historian historian = new Historian(historianServer, historianInstance))
{
foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIDs, startDate, endDate))
{
if (!trendingDataSet.ChannelData.Exists(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds))
{
trendingDataSet.ChannelData.Add(new TrendingDataDatum());
trendingDataSet.ChannelData[trendingDataSet.ChannelData.Count - 1].Time = point.Timestamp.Subtract(epoch).TotalMilliseconds;
}
if (point.SeriesID.ToString() == "Average")
trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Average = point.Value;
else if (point.SeriesID.ToString() == "Minimum")
trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Minimum = point.Value;
else if (point.SeriesID.ToString() == "Maximum")
trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Maximum = point.Value;
}
}
IEnumerable<DataRow> table = Enumerable.Empty<DataRow>();
table = connection.RetrieveData(" Select {0} AS thedatefrom, " +
" DATEADD(DAY, 1, {0}) AS thedateto, " +
" CASE WHEN AlarmRangeLimit.PerUnit <> 0 AND Channel.PerUnitValue IS NOT NULL THEN AlarmRangeLimit.High * PerUnitValue ELSE AlarmRangeLimit.High END AS alarmlimithigh," +
" CASE WHEN AlarmRangeLimit.PerUnit <> 0 AND Channel.PerUnitValue IS NOT NULL THEN AlarmRangeLimit.Low * PerUnitValue ELSE AlarmRangeLimit.Low END AS alarmlimitlow " +
" FROM AlarmRangeLimit JOIN " +
" Channel ON AlarmRangeLimit.ChannelID = Channel.ID " +
"WHERE AlarmRangeLimit.AlarmTypeID = (SELECT ID FROM AlarmType where Name = 'Alarm') AND " +
" AlarmRangeLimit.ChannelID = {1}", startDate, Convert.ToInt32(ChannelID)).Select();
foreach (DataRow row in table)
{
trendingDataSet.AlarmLimits.Add(new TrendingAlarmLimit() { High = row.Field<double?>("alarmlimithigh"), Low = row.Field<double?>("alarmlimitlow"), TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds, TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds });
}
table = Enumerable.Empty<DataRow>();
table = connection.RetrieveData(" DECLARE @dayOfWeek INT = DATEPART(DW, {0}) - 1 " +
" DECLARE @hourOfWeek INT = @dayOfWeek * 24 " +
" ; WITH HourlyIndex AS" +
" ( " +
" SELECT @hourOfWeek AS HourOfWeek " +
" UNION ALL " +
" SELECT HourOfWeek + 1 " +
" FROM HourlyIndex" +
" WHERE (HourOfWeek + 1) < @hourOfWeek + 24" +
" ) " +
" SELECT " +
" DATEADD(HOUR, HourlyIndex.HourOfWeek - @hourOfWeek, {0}) AS thedatefrom, " +
" DATEADD(HOUR, HourlyIndex.HourOfWeek - @hourOfWeek + 1, {0}) AS thedateto, " +
" HourOfWeekLimit.High AS offlimithigh, " +
" HourOfWeekLimit.Low AS offlimitlow " +
" FROM " +
" HourlyIndex LEFT OUTER JOIN " +
" HourOfWeekLimit ON HourOfWeekLimit.HourOfWeek = HourlyIndex.HourOfWeek " +
" WHERE " +
" HourOfWeekLimit.ChannelID IS NULL OR " +
" HourOfWeekLimit.ChannelID = {1} ", startDate, Convert.ToInt32(ChannelID)).Select();
foreach (DataRow row in table)
{
trendingDataSet.OffNormalLimits.Add(new TrendingAlarmLimit() { High = row.Field<double?>("offlimithigh"), Low = row.Field<double?>("offlimitlow"), TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds, TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds });
}
}
return trendingDataSet;
}
/// <summary>
/// Returns true if a data operation exists to run this class. Returns false otherwise.
/// </summary>
/// <param name="database">Database connection to use for checking the data operation</param>
/// <returns>True or false indicating whether the operation exists</returns>
private static bool DataOperationExists(AdoDataConnection database)
{
return(Convert.ToInt32(database.ExecuteScalar($"SELECT COUNT(*) FROM DataOperation WHERE TypeName='{typeof(PowerCalculationConfigurationValidation).FullName}' AND MethodName='ValidatePowerCalculationConfigurations'")) > 0);
}
