/// <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();
}
}
}
/// <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();
}
}
private static void Update(List <GrafanaAlarm> alarms, DataRow alarmRecord, AdoDataConnection connection)
{
int index = alarms.FindIndex(item => item.ID == alarmRecord.Field <int>("value"));
string recordState = "unknown";
if (index == -1)
{
connection.ExecuteNonQuery("UPDATE alert SET state = {0} WHERE ID = {1}", recordState, alarmRecord.Field <int>("id"));
return;
}
Alarm alarmState = AlarmAdapter.Default?.GetAlarmStatus(alarms[index].SignalID)?.FirstOrDefault(item => item.ID == alarms[index].ID);
if (alarmState != null)
{
if (alarmState.State == GSF.TimeSeries.AlarmState.Cleared)
{
recordState = "ok";
}
else if (alarmState.State == GSF.TimeSeries.AlarmState.Raised)
{
recordState = "alerting";
}
}
connection.ExecuteNonQuery("UPDATE alert SET state = {0} WHERE ID = {1}", recordState, alarmRecord.Field <int>("id"));
}
// Static Constructor
static SecurityHub()
{
CategorizedSettingsElementCollection systemSettings = ConfigurationFile.Current.Settings["systemSettings"];
// Retrieve default NodeID
DefaultNodeID = Guid.Parse(systemSettings["NodeID"].Value.ToNonNullString(Guid.NewGuid().ToString()));
// Determine whether the node exists in the database and create it if it doesn't
if (DefaultNodeID != Guid.Empty)
{
using (AdoDataConnection connection = new AdoDataConnection("securityProvider"))
{
const string NodeCountFormat = "SELECT COUNT(*) FROM Node";
const string NodeInsertFormat = "INSERT INTO Node(Name, Description, Enabled) VALUES('Default', 'Default node', 1)";
const string NodeUpdateFormat = "UPDATE Node SET ID = {0}";
int nodeCount = connection.ExecuteScalar <int?>(NodeCountFormat) ?? 0;
if (nodeCount == 0)
{
connection.ExecuteNonQuery(NodeInsertFormat);
connection.ExecuteNonQuery(NodeUpdateFormat, connection.Guid(DefaultNodeID));
}
}
}
}
// 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 void HandleMetaSignalCommand(ClientConnection connection, byte[] buffer, int startIndex, int length)
{
MakeConfigurationChanges(() =>
{
try
{
if (startIndex + sizeof(int) > Math.Min(length, buffer.Length))
{
throw new InvalidOperationException("No message payload.");
}
int index = startIndex;
int payloadByteLength = BigEndian.ToInt32(buffer, index);
index += sizeof(int);
if (payloadByteLength < 0 || index + payloadByteLength > Math.Min(length, buffer.Length))
{
throw new InvalidOperationException($"Payload byte length out of bounds ({payloadByteLength}).");
}
string parameterString = connection.Encoding.GetString(buffer, index, payloadByteLength);
MetaSignal metaSignal = new MetaSignal();
new ConnectionStringParser <SettingAttribute>().ParseConnectionString(parameterString, metaSignal);
string deviceAcronym = Regex.Replace($"{metaSignal.AnalyticProjectName}!{metaSignal.AnalyticInstanceName}".ToUpper(), @"[^A-Z0-9\-!_\.@#\$]", "");
string deviceName = $"{metaSignal.AnalyticProjectName} {metaSignal.AnalyticInstanceName}";
using (AdoDataConnection dbConnection = new AdoDataConnection("systemSettings"))
{
if (dbConnection.ExecuteScalar <int>("SELECT COUNT(*) FROM Device WHERE UniqueID = {0}", metaSignal.DeviceID) > 0)
{
dbConnection.ExecuteNonQuery("UPDATE Device SET Acronym = {0} WHERE UniqueID = {1}", deviceAcronym, metaSignal.DeviceID);
}
else
{
dbConnection.ExecuteNonQuery("INSERT INTO Device(Acronym, Name, UniqueID, ProtocolID) VALUES({0}, {1}, {2}, (SELECT ID FROM Protocol WHERE Acronym = 'VirtualInput'))", deviceAcronym, deviceName, metaSignal.DeviceID);
}
int deviceID = dbConnection.ExecuteScalar <int>("SELECT ID FROM Device WHERE UniqueID = {0}", metaSignal.DeviceID);
int signalTypeID = dbConnection.ExecuteScalar <int>("SELECT ID FROM SignalType WHERE Acronym = {0}", metaSignal.SignalType);
if (dbConnection.ExecuteScalar <int>("SELECT COUNT(*) FROM Measurement WHERE SignalID = {0}", metaSignal.SignalID) > 0)
{
dbConnection.ExecuteNonQuery("UPDATE Measurement SET DeviceID = {0}, PointTag = {1}, SignalTypeID = {2}, Description = {3}", metaSignal.DeviceID, metaSignal.PointTag, signalTypeID, metaSignal.Description);
}
else
{
dbConnection.ExecuteNonQuery("INSERT INTO Measurement(DeviceID, SignalID, PointTag, SignalTypeID, Description) VALUES({0}, {1}, {2}, {3}, {4})", deviceID, metaSignal.SignalID, metaSignal.PointTag, signalTypeID, metaSignal.Description);
}
}
}
catch (Exception ex)
{
string errorMessage = $"Meta-signal command failed due to exception: {ex.Message}";
OnProcessException(new Exception(errorMessage, ex));
SendClientResponse(connection.ClientID, ServerResponse.Failed, (ServerCommand)ECAServerCommand.StatusMessage, errorMessage);
}
});
}
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);
}
}
private void Load(AdoDataConnection database)
{
TableOperations <DbIncident> incidentTable = new TableOperations <DbIncident>(database);
foreach (Incident incident in m_incidents)
{
if (incident.ExistingIncidents.Count == 0 || incident.ExistingIncidents.Count > 1)
{
incidentTable.AddNewRecord(
new DbIncident()
{
MeterID = m_meterID,
StartTime = incident.StartTime,
EndTime = incident.EndTime
}
);
}
}
List <Incident> expand = m_incidents
.Where(incident => incident.ExistingIncidents.Count == 1)
.ToList();
List <Incident> cleanup = m_incidents
.Where(incident => incident.ExistingIncidents.Count > 1)
.ToList();
if (expand.Count == 0 && cleanup.Count == 0)
{
return;
}
const string DateTimeFormat = "yyyy-MM-dd HH:mm:ss.fffffff";
foreach (Incident incident in expand)
{
database.ExecuteNonQuery("UPDATE Incident SET StartTime = {0}, EndTime = {1} WHERE ID = {2}", incident.StartTime.ToString(DateTimeFormat), incident.EndTime.ToString(DateTimeFormat), int.Parse(incident.ExistingIncidents[0]["ID"].ToString()));
}
foreach (Incident incident in cleanup)
{
string incidentIDs = string.Join(",", incident.ExistingIncidents.Select(inc => inc["ID"].ToString()));
database.ExecuteNonQuery($"UPDATE Event SET IncidentID = (SELECT ID FROM Incident WHERE StartTime = {{0}} AND EndTime = {{1}}) WHERE IncidentID IN ({incidentIDs})", incident.StartTime.ToString(DateTimeFormat), incident.EndTime.ToString(DateTimeFormat));
}
if (cleanup.Count > 0)
{
string allIncidentIDs = string.Join(",", cleanup
.SelectMany(incident => incident.ExistingIncidents)
.Select(incident => incident["ID"].ToString()));
database.ExecuteNonQuery($"DELETE FROM IncidentAttribute WHERE IncidentID IN ({allIncidentIDs})");
database.ExecuteNonQuery($"DELETE FROM Incident WHERE ID IN ({allIncidentIDs})");
}
}
public virtual IHttpActionResult Delete(T record)
{
try
{
if (DeleteRoles == string.Empty || User.IsInRole(DeleteRoles))
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
TableNameAttribute tableNameAttribute;
string tableName;
if (typeof(T).TryGetAttribute(out tableNameAttribute))
{
tableName = tableNameAttribute.TableName;
}
else
{
tableName = typeof(T).Name;
}
PropertyInfo idProp = typeof(T).GetProperty(PrimaryKeyField);
if (idProp.PropertyType == typeof(int))
{
int id = (int)idProp.GetValue(record);
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete '{tableName}', '{PrimaryKeyField} = {id}'");
return(Ok(result));
}
else if (idProp.PropertyType == typeof(Guid))
{
Guid id = (Guid)idProp.GetValue(record);
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete '{tableName}', '{PrimaryKeyField} = ''{id}'''");
return(Ok(result));
}
else if (idProp.PropertyType == typeof(string))
{
string id = (string)idProp.GetValue(record);
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete '{tableName}', '{PrimaryKeyField} = ''{id}'''");
return(Ok(result));
}
else
{
int result = new TableOperations <T>(connection).DeleteRecord(record);
return(Ok(result));
}
}
}
else
{
return(Unauthorized());
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
private void UpdateStatusLogDatabase(string message, bool success)
{
try
{
DataRow log = m_adoDataConnection.RetrieveRow("SELECT * FROM StatusLog WITH (NOLOCK) WHERE DeviceID = {0}", int.Parse(m_deviceRecord["ID"].ToString()));
if (success)
{
if (m_deviceRecord["ID"].ToString() == log["DeviceID"].ToString() && m_lastFileDownloadedThisSession != string.Empty)
{
m_adoDataConnection.ExecuteNonQuery("Update StatusLog SET LastSuccess = {0}, LastFile = {1}, FileDownloadTimestamp = {2} WHERE DeviceID = {3}", DateTime.UtcNow, m_lastFileDownloadedThisSession, DateTime.UtcNow, m_deviceRecord["ID"]);
}
else if (m_deviceRecord["ID"].ToString() == log["DeviceID"].ToString() && m_lastFileDownloadedThisSession == string.Empty)
{
m_adoDataConnection.ExecuteNonQuery("Update StatusLog SET LastSuccess = {0} WHERE DeviceID = {1}", DateTime.UtcNow, m_deviceRecord["ID"]);
}
else
{
m_adoDataConnection.ExecuteNonQuery("INSERT INTO StatusLog (LastSuccess , LastFile, FileDownloadTimestamp, DeviceID) VALUES ({0},{1},{2})", DateTime.UtcNow, m_lastFileDownloadedThisSession, DateTime.UtcNow, m_deviceRecord["ID"]);
}
if (m_lastFileDownloadedThisSession != string.Empty)
{
m_adoDataConnection.ExecuteNonQuery("INSERT INTO DownloadedFile (DeviceID , CreationTime, File, FileSize, Timestamp) VALUES ({0},{1},{2})", m_deviceRecord["ID"], DateTime.UtcNow, m_lastFileDownloadedThisSession, 0, DateTime.UtcNow);
}
int maxDownloadThreshold = Program.OpenMiConfigurationFile.Settings?["systemSettings"]["MaxDownloadThreshold"].ValueAsInt32() ?? 0;
if (maxDownloadThreshold > 0)
{
DateTime timeWindow = DateTime.UtcNow.AddHours(-(Program.OpenMiConfigurationFile.Settings?["systemSettings"]["MaxDownloadThresholdTimeWindow"].ValueAsInt32() ?? 24));
int count = m_adoDataConnection.ExecuteScalar <int>("SELECT COUNT(*) FROM DownloadedFile WHERE Timestamp >= {0} AND DeviceID = {1}", timeWindow, m_deviceRecord["ID"]);
if (count > maxDownloadThreshold)
{
m_adoDataConnection.ExecuteNonQuery("UPDATE Device SET Enabled = 0 WHERE ID = {0}", m_deviceRecord["ID"]);
string fail = "Disabled due to excessive file production.";
m_adoDataConnection.ExecuteNonQuery("Update StatusLog SET LastFailure = {0}, Message = {1} WHERE DeviceID = {2}", DateTime.UtcNow, fail, m_deviceRecord["ID"]);
Program.Log($"[{m_deviceRecord["Name"]} Disabled due to excessive file downloads. Setting: {maxDownloadThreshold}; Count: {count}");
}
}
}
else
{
if (m_deviceRecord["ID"].ToString() == log["DeviceID"].ToString())
{
m_adoDataConnection.ExecuteNonQuery("Update StatusLog SET LastFailure = {0}, Message = {1} WHERE DeviceID = {2}", DateTime.UtcNow, message, m_deviceRecord["ID"]);
}
else
{
m_adoDataConnection.ExecuteNonQuery("INSERT INTO StatusLog (LastFailure , Message, DeviceID) VALUES ({0},{1},{2})", DateTime.UtcNow, message, m_deviceRecord["ID"]);
}
}
}
catch (Exception ex)
{
Program.Log("StatusLog Update (" + m_siteName + ") failed: " + ex.Message, m_tempDirectoryName);
}
}
/// <summary>
/// Saves <see cref="CompressionSetting"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="compressionSetting">Information about <see cref="CompressionSetting"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, CompressionSetting compressionSetting)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string successMessage = "CompressionSetting information saved successfully";
if (compressionSetting.IsNew)
{
database.ExecuteNonQuery(
$"INSERT INTO CompressionSetting ({nameof(PointID)}, {nameof(CompressionMinTime)}, {nameof(CompressionMaxTime)}, {nameof(CompressionLimit)}) " +
$"VALUES({{0}}, {{1}}, {{2}}, {{3}})", compressionSetting.PointID, compressionSetting.CompressionMinTime, compressionSetting.CompressionMaxTime, compressionSetting.CompressionLimit);
compressionSetting.IsNew = false;
}
else
{
database.ExecuteNonQuery(
$"UPDATE CompressionSetting SET " +
$" {nameof(CompressionMinTime)} = {{0}}, " +
$" {nameof(CompressionMaxTime)} = {{1}}, " +
$" {nameof(CompressionLimit)} = {{2}} " +
$"WHERE {nameof(PointID)} = {{3}}",
compressionSetting.CompressionMinTime, compressionSetting.CompressionMaxTime, compressionSetting.CompressionLimit, compressionSetting.PointID);
}
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
CommonFunctions.LogException(database, "CompressionSetting Save", ex);
}
return(successMessage);
}
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>
/// Method to add <see cref="Measurement"/>s to <see cref="MeasurementGroup"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="measurementGroupID">ID of the <see cref="MeasurementGroup"/> to add <see cref="Measurement"/> to.</param>
/// <param name="measurementsToBeAdded">List of <see cref="Measurement"/> signal ids to be added.</param>
/// <returns>string, indicating success for UI display.</returns>
public static int AddMeasurements(AdoDataConnection database, int measurementGroupID, ICollection <Guid> measurementsToBeAdded)
{
string QueryFormat =
"INSERT INTO MeasurementGroupMeasurement (NodeID, MeasurementGroupID, SignalID) " +
"SELECT {0}, {1}, {2} " +
"WHERE (SELECT COUNT(*) FROM MeasurementGroupMeasurement WHERE MeasurementGroupID = {1} AND SignalID = {2}) = 0";
bool createdConnection = false;
int rowsAffected = 0;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in measurementsToBeAdded)
{
rowsAffected += database.ExecuteNonQuery(DefaultTimeout, QueryFormat, CommonFunctions.CurrentNodeID(), measurementGroupID, id);
}
return(rowsAffected);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
private void DeleteFileBlobs(AdoDataConnection connection, int meterID, DateTime startTime, DateTime endTime, CancellationToken cancellationToken)
{
const string QueryFormat =
"DELETE FROM FileBlob " +
"WHERE DataFileID IN " +
"( " +
" SELECT TOP 50 DataFile.ID " +
" FROM " +
" DataFile JOIN " +
" FileGroup ON DataFile.FileGroupID = FileGroup.ID " +
" WHERE " +
" FileGroup.MeterID = {0} AND " +
" FileGroup.DataEndTime >= {1} AND " +
" FileGroup.DataStartTime <= {2} " +
")";
while (true)
{
if (cancellationToken.IsCancellationRequested)
{
break;
}
int count = connection.ExecuteNonQuery(QueryFormat, meterID, startTime, endTime);
if (count == 0)
{
break;
}
}
}
/// <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();
}
}
}
private static void ValidateAlarmStatistics(AdoDataConnection connection, Guid nodeID, string source)
{
const string MissingStatisticsFormat = "SELECT DISTINCT Severity FROM Alarm WHERE Severity <> 0 AND Severity NOT IN (SELECT Arguments FROM Statistic WHERE Source = {0} AND MethodName = {1})";
const string MaxSignalIndexFormat = "SELECT COALESCE(MAX(SignalIndex), 0) FROM Statistic WHERE Source = {0}";
const string InsertAlarmStatisticFormat = "INSERT INTO Statistic(Source, SignalIndex, Name, Description, AssemblyName, TypeName, MethodName, Arguments, Enabled, DataType, DisplayFormat, IsConnectedState, LoadOrder) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12})";
string methodName;
DataTable missingStatistics;
int signalIndex;
int severity;
string name;
string description;
// Add statistics for the alarms defined in the Alarm table.
methodName = string.Format("Get{0}Statistic_MeasurementCountForSeverity", source);
missingStatistics = connection.RetrieveData(MissingStatisticsFormat, source, methodName);
if (missingStatistics.Rows.Count > 0)
{
signalIndex = connection.ExecuteScalar <int>(MaxSignalIndexFormat, source);
foreach (DataRow missingStatistic in missingStatistics.Rows)
{
signalIndex++;
severity = missingStatistic.ConvertField <int>("Severity");
name = string.Format("Alarm Severity {0}", severity);
description = string.Format("Number of measurements received while alarm with severity {0} was raised during the last reporting interval.", severity);
connection.ExecuteNonQuery(InsertAlarmStatisticFormat, source, signalIndex, name, description, "DataQualityMonitoring.dll", "DataQualityMonitoring.AlarmStatistics", methodName, severity, 1, "System.Int32", "{0:N0}", 0, 1001 - severity);
}
}
}
public static ActionResult DatabaseQueryAction(Session session)
{
string connectionString;
string dataProviderString;
string query;
session.Log("Begin DatabaseQueryAction");
// Get properties from the installer session
connectionString = session.CustomActionData["CONNECTIONSTRING"];
dataProviderString = session.CustomActionData["DATAPROVIDERSTRING"];
query = session.CustomActionData["DBQUERY"];
try
{
// Execute the database script
using (AdoDataConnection connection = new AdoDataConnection(connectionString, dataProviderString))
{
connection.ExecuteNonQuery(query);
}
}
catch (Exception ex)
{
// Log the error and return failure code
LogInstallMessage(session, EventLogEntryType.Error, string.Format("Failed to execute database query: {0}.", ex.Message));
LogInstallMessage(session, EventLogEntryType.Error, string.Format("Database Query: {0}", query));
LogInstallMessage(session, EventLogEntryType.Error, string.Format("Connection string: {0}", connectionString));
LogInstallMessage(session, EventLogEntryType.Error, string.Format("Data provider string: {0}", dataProviderString));
return(ActionResult.Failure);
}
session.Log("End DatabaseQueryAction");
return(ActionResult.Success);
}
private static int LoadSentEmail(AdoDataConnection connection, List <string> recipients, string subject, string body)
{
DateTime now = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, s_timeZone);
string toLine = string.Join("; ", recipients.Select(recipient => recipient.Trim()));
connection.ExecuteNonQuery("INSERT INTO SentEmail VALUES({0}, {1}, {2}, {3})", now, toLine, subject, body);
return(connection.ExecuteScalar <int>("SELECT @@IDENTITY"));
}
public virtual IHttpActionResult Delete(T record)
{
try
{
if (DeleteAuthCheck() && !ViewOnly)
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
string tableName = new TableOperations <T>(connection).TableName;
PropertyInfo idProp = typeof(T).GetProperty(PrimaryKeyField);
if (idProp.PropertyType == typeof(int))
{
int id = (int)idProp.GetValue(record);
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete {tableName}, '{PrimaryKeyField} = {id}'");
return(Ok(result));
}
else if (idProp.PropertyType == typeof(Guid))
{
Guid id = (Guid)idProp.GetValue(record);
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete {tableName}, '{PrimaryKeyField} = ''{id}'''");
return(Ok(result));
}
else if (idProp.PropertyType == typeof(string))
{
string id = (string)idProp.GetValue(record);
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete {tableName}, '{PrimaryKeyField} = ''{id}'''");
return(Ok(result));
}
else
{
int result = new TableOperations <T>(connection).DeleteRecord(record);
return(Ok(result));
}
}
}
else
{
return(Unauthorized());
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
private void MoveRecords(AdoDataConnection connection, string table, int fromMeterID, int toMeterID)
{
string queryFormat =
$"UPDATE {table} " +
$"SET MeterID = {{1}} " +
$"WHERE MeterID = {{0}}";
connection.ExecuteNonQuery(queryFormat, fromMeterID, toMeterID);
}
private int LoadSentEmail(AdoDataConnection connection, List <string> recipients, string subject, string body)
{
TimeZoneInfo xDATimeZone = TimeZoneInfo.FindSystemTimeZoneById(connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'XDATimeZone'"));
DateTime now = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, xDATimeZone);
string toLine = string.Join("; ", recipients.Select(recipient => recipient.Trim()));
connection.ExecuteNonQuery("INSERT INTO SentEmail VALUES({0}, {1}, {2}, {3})", now, toLine, subject, body);
return(connection.ExecuteScalar <int>("SELECT @@IDENTITY"));
}
/// <summary>
/// Adds role for newly added node, e.g., Administrator, Editor, Viewer.
/// </summary>
/// <param name="database">Data connection to use for database operations.</param>
/// <param name="nodeID">Node ID to which roles are being assigned.</param>
/// <param name="roleName">Name of role to be added.</param>
private static void AddRolesForNode(AdoDataConnection database, Guid nodeID, string roleName)
{
// Queries
const string InsertRoleFormat = "INSERT INTO ApplicationRole(Name, Description, NodeID, UpdatedBy, CreatedBy) VALUES('{0}', '{0} Role', {{0}}, {{1}}, {{2}})";
string currentUserSID = UserInfo.UserNameToSID(UserInfo.CurrentUserID);
database.ExecuteNonQuery(string.Format(InsertRoleFormat, roleName), database.Guid(nodeID), currentUserSID, currentUserSID);
}
public override void Execute(MeterDataSet meterDataSet)
{
LightningDataResource lightningDataResource = meterDataSet.GetResource <LightningDataResource>();
if (lightningDataResource.LightningStrikeLookup.Count == 0)
{
return;
}
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
{
foreach (var kvp in lightningDataResource.LightningStrikeLookup)
{
DataGroup dataGroup = kvp.Key;
List <ILightningStrike> lightningStrikes = kvp.Value;
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
Event evt = eventTable.GetEvent(meterDataSet.FileGroup, dataGroup);
if (evt == null)
{
continue;
}
foreach (ILightningStrike lightningStrike in lightningStrikes)
{
const string Query =
"INSERT INTO LightningStrike " +
"( " +
" EventID, " +
" Service, " +
" UTCTime, " +
" DisplayTime, " +
" Amplitude, " +
" Latitude, " +
" Longitude " +
") " +
"VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6})";
object[] parameters =
{
evt.ID,
lightningStrike.Service,
ToDateTime2(connection, lightningStrike.UTCTime),
lightningStrike.DisplayTime,
lightningStrike.Amplitude,
lightningStrike.Latitude,
lightningStrike.Longitude
};
connection.ExecuteNonQuery(Query, parameters);
}
}
}
}
/// <summary>
/// Saves <see cref="Company"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="powerCalculation">Information about <see cref="PowerCalculation"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, PowerCalculation powerCalculation)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (powerCalculation.ID == 0)
{
query = "INSERT INTO PowerCalculation (CircuitDescription, VoltageAngleSignalID, VoltageMagSignalID, CurrentAngleSignalID, CurrentMagSignalID, ActivePowerOutputSignalID, ReactivePowerOutputSignalID, ApparentPowerOutputSignalID, Enabled, NodeID) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})";
database.ExecuteNonQuery(DefaultTimeout, query, powerCalculation.CircuitDescription, powerCalculation.VoltagePhasor.AngleMeasurement.SignalID,
powerCalculation.VoltagePhasor.MagnitudeMeasurement.SignalID, powerCalculation.CurrentPhasor.AngleMeasurement.SignalID, powerCalculation.CurrentPhasor.MagnitudeMeasurement.SignalID,
(object)powerCalculation.ActivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ReactivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ApparentPowerOutputMeasurement?.SignalID ?? DBNull.Value,
powerCalculation.Enabled, database.CurrentNodeID());
}
else
{
query = "UPDATE PowerCalculation SET CircuitDescription = {0}, VoltageAngleSignalID = {1}, VoltageMagSignalID = {2}, CurrentAngleSignalID = {3}, CurrentMagSignalID = {4}, " +
"activePowerOutputSignalID = {5}, ReactivePowerOutputSignalID = {6}, ApparentPowerOutputSignalID = {7}, Enabled = {8} WHERE ID = {9}";
database.ExecuteNonQuery(DefaultTimeout, query, powerCalculation.CircuitDescription,
database.Guid(powerCalculation.VoltagePhasor.AngleMeasurement.SignalID), powerCalculation.VoltagePhasor.MagnitudeMeasurement.SignalID, powerCalculation.CurrentPhasor.AngleMeasurement.SignalID, powerCalculation.CurrentPhasor.MagnitudeMeasurement.SignalID,
(object)powerCalculation.ActivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ReactivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ApparentPowerOutputMeasurement?.SignalID ?? DBNull.Value, powerCalculation.Enabled, powerCalculation.ID);
}
return("Power Calculation information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
public async Task DeleteOperationAsync(int dataRescueOperationID, CancellationToken cancellationToken = default)
{
using (AdoDataConnection connection = CreateDbConnection())
{
DataRescueOperation ToOperation(DataRow row) => new DataRescueOperation()
{
ID = row.ConvertField <int>("ID"),
MeterID = row.ConvertField <int>("MeterID"),
StartTime = row.ConvertField <DateTime>("StartTime"),
EndTime = row.ConvertField <DateTime>("EndTime")
};
DataRescueOperation operation = new Func <DataRescueOperation>(() =>
{
const string QueryFormat =
"SELECT " +
" ID, " +
" MeterID, " +
" StartTime, " +
" EndTime " +
"FROM DataRescueOperation " +
"WHERE ID = {0}";
using (DataTable table = connection.RetrieveData(QueryFormat, dataRescueOperationID))
{
return(table
.AsEnumerable()
.Select(ToOperation)
.FirstOrDefault());
}
})();
const string DeleteFormat =
"DELETE FROM DataRescueTimeShift " +
"WHERE DataRescueOperationID = {0} " +
"" +
"DELETE FROM DataRescueChannelAdjustment " +
"WHERE DataRescueOperationID = {0}";
connection.ExecuteNonQuery(DeleteFormat, operation.ID);
PurgeStaleEventData(connection, operation);
await PurgeStaleTrendingDataAsync(operation, cancellationToken);
ReprocessAffectedFiles(connection, operation);
string baseCriteria = $"ID = {dataRescueOperationID}";
CascadeDelete(connection, "DataRescueOperation", baseCriteria);
XDANodeClient.NotifyNodes("openXDA.Nodes.dll", "openXDA.Nodes.Types.Analysis.AnalysisNode", "PollTaskQueue");
}
}
private void UpdateStatusLogDatabase(string message, bool success)
{
try
{
DataRow log = m_adoDataConnection.RetrieveRow("SELECT * FROM StatusLog WITH (NOLOCK) WHERE DeviceID = {0}", int.Parse(m_deviceRecord["ID"].ToString()));
if (success)
{
if (m_deviceRecord["ID"].ToString() == log["DeviceID"].ToString() && m_lastFileDownloadedThisSession != "")
{
m_adoDataConnection.ExecuteNonQuery("Update StatusLog SET LastSuccess = {0}, LastFile = {1} WHERE DeviceID = {2}", DateTime.UtcNow, m_lastFileDownloadedThisSession, m_deviceRecord["ID"]);
}
else if (m_deviceRecord["ID"].ToString() == log["DeviceID"].ToString() && m_lastFileDownloadedThisSession == "")
{
m_adoDataConnection.ExecuteNonQuery("Update StatusLog SET LastSuccess = {0} WHERE DeviceID = {1}", DateTime.UtcNow, m_deviceRecord["ID"]);
}
else
{
m_adoDataConnection.ExecuteNonQuery("INSERT INTO StatusLog (LastSuccess , LastFile, DeviceID) VALUES ({0},{1},{2})", DateTime.UtcNow, m_lastFileDownloadedThisSession, m_deviceRecord["ID"]);
}
}
else
{
if (m_deviceRecord["ID"].ToString() == log["DeviceID"].ToString())
{
m_adoDataConnection.ExecuteNonQuery("Update StatusLog SET LastFailure = {0}, Message = {1} WHERE DeviceID = {2}", DateTime.UtcNow, message, m_deviceRecord["ID"]);
}
else
{
m_adoDataConnection.ExecuteNonQuery("INSERT INTO StatusLog (LastFailure , Message, DeviceID) VALUES ({0},{1},{2})", DateTime.UtcNow, message, m_deviceRecord["ID"]);
}
}
}
catch (Exception ex)
{
Program.Log("StatusLog Update (" + m_siteName + ") failed: " + ex.Message, m_tempDirectoryName);
}
}
private void DisableChannels(AdoDataConnection connection, int meterID)
{
const string QueryFormat =
"UPDATE Series " +
"SET SourceIndexes = 'NONE' " +
"WHERE ChannelID IN " +
"( " +
" SELECT ID " +
" FROM Channel " +
" WHERE MeterID = {0} " +
")";
connection.ExecuteNonQuery(QueryFormat, meterID);
}
public IHttpActionResult Delete(int id)
{
try
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete 'AssetTypeField', 'ID = {id}'");
return(Ok(result));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
private static void Add(GrafanaAlarm alarm, QueryRequest query, AdoDataConnection connection)
{
const string insertSQL = "INSERT INTO alert (dashboard_id, version, org_id, panel_id, name,state, new_state_date, message, settings, frequency, handler, severity, silenced, execution_error, state_changes, created, updated) VALUES " +
"({0}, 0, 1, {1}, {2}, {3}, {4}, '', '', 60, 0, 0, 0, '', 0, {5}, {6})";
// Start by adding the Alarm
Alarm alarmState = AlarmAdapter.Default?.GetAlarmStatus(alarm.SignalID)?.FirstOrDefault(item => item.ID == alarm.ID);
if (alarmState is null)
{
return;
}
string recordState = "ok";
if (alarmState.State == GSF.TimeSeries.AlarmState.Raised)
{
recordState = "alerting";
}
connection.ExecuteNonQuery(insertSQL, query.dashboardId, query.panelId, alarm.Description, recordState, alarmState.TimeRaised, alarm.CreatedOn, DateTime.UtcNow);
// Note: the 'last_insert_rowid' function is specific to SQLite
int alarmID = connection.ExecuteScalar <int>("SELECT last_insert_rowid()");
// Check if Tag for this alarm already Exists
int tagID = connection.ExecuteScalar <int>("SELECT id FROM tag WHERE key = 'OpenHistorianID' AND value = {0}", alarm.ID);
if (tagID == 0)
{
connection.ExecuteNonQuery("INSERT INTO tag (key, value) VALUES ('OpenHistorianID', {0})", alarm.ID);
tagID = connection.ExecuteScalar <int>("SELECT last_insert_rowid()");
}
connection.ExecuteNonQuery("INSERT INTO alert_rule_tag (alert_id, tag_id) VALUES ({0}, {1})", alarmID, tagID);
}
public IHttpActionResult DisconnectLineSegmentFromLine(int segmentID, int lineID)
{
if (DeleteRoles == string.Empty || User.IsInRole(DeleteRoles))
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
int typeID = connection.ExecuteScalar <int>("SELECT ID FROM AssetRelationShipType WHERE Name = 'Line-LineSegment'");
connection.ExecuteNonQuery("DELETE FROM AssetRelationship WHERE AssetRelationshipTypeID = {0} AND ((ChildID = {1} AND ParentID = {2}) OR (ChildID = {2} AND ParentID = {1}))", typeID, lineID, segmentID);
return(Ok(1));
}
}
else
{
return(Unauthorized());
}
}
private void ReprocessAffectedFiles(AdoDataConnection connection, DataRescueOperation operation)
{
const string QueryFormat =
"INSERT INTO AnalysisTask(FileGroupID, MeterID, Priority) " +
"SELECT " +
" DataRescueFileGroup.FileGroupID, " +
" {1} MeterID, " +
" 1 Priority " +
"FROM DataRescueFileGroup " +
"WHERE DataRescueFileGroup.DataRescueOperationID = {0}";
int operationID = operation.ID;
int meterID = operation.MeterID;
connection.ExecuteNonQuery(QueryFormat, operationID, meterID);
XDANodeClient.NotifyNodes("openXDA.Nodes.dll", "openXDA.Nodes.Types.Analysis.AnalysisNode", "PollTaskQueue");
}
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);
}
}
public static ActionResult DatabaseQueryAction(Session session)
{
string connectionString;
string dataProviderString;
string query;
session.Log("Begin DatabaseQueryAction");
// Get properties from the installer session
connectionString = session.CustomActionData["CONNECTIONSTRING"];
dataProviderString = session.CustomActionData["DATAPROVIDERSTRING"];
query = session.CustomActionData["DBQUERY"];
try
{
// Execute the database script
using (AdoDataConnection connection = new AdoDataConnection(connectionString, dataProviderString))
{
connection.ExecuteNonQuery(query);
}
}
catch (Exception ex)
{
// Log the error and return failure code
LogInstallMessage(session, EventLogEntryType.Error, string.Format("Failed to execute database query: {0}.", ex.Message));
LogInstallMessage(session, EventLogEntryType.Error, string.Format("Database Query: {0}", query));
LogInstallMessage(session, EventLogEntryType.Error, string.Format("Connection string: {0}", connectionString));
LogInstallMessage(session, EventLogEntryType.Error, string.Format("Data provider string: {0}", dataProviderString));
return ActionResult.Failure;
}
session.Log("End DatabaseQueryAction");
return ActionResult.Success;
}
private static void ValidateAlarmStatistics(AdoDataConnection connection, Guid nodeID, string source)
{
const string MissingStatisticsFormat = "SELECT DISTINCT Severity FROM Alarm WHERE Severity <> 0 AND Severity NOT IN (SELECT Arguments FROM Statistic WHERE Source = {0} AND MethodName = {1})";
const string MaxSignalIndexFormat = "SELECT COALESCE(MAX(SignalIndex), 0) FROM Statistic WHERE Source = {0}";
const string InsertAlarmStatisticFormat = "INSERT INTO Statistic(Source, SignalIndex, Name, Description, AssemblyName, TypeName, MethodName, Arguments, Enabled, DataType, DisplayFormat, IsConnectedState, LoadOrder) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12})";
string methodName;
DataTable missingStatistics;
int signalIndex;
int severity;
string name;
string description;
// Add statistics for the alarms defined in the Alarm table.
methodName = string.Format("Get{0}Statistic_MeasurementCountForSeverity", source);
missingStatistics = connection.RetrieveData(MissingStatisticsFormat, source, methodName);
if (missingStatistics.Rows.Count > 0)
{
signalIndex = connection.ExecuteScalar<int>(MaxSignalIndexFormat, source);
foreach (DataRow missingStatistic in missingStatistics.Rows)
{
signalIndex++;
severity = missingStatistic.ConvertField<int>("Severity");
name = string.Format("Alarm Severity {0}", severity);
description = string.Format("Number of measurements received while alarm with severity {0} was raised during the last reporting interval.", severity);
connection.ExecuteNonQuery(InsertAlarmStatisticFormat, source, signalIndex, name, description, "DataQualityMonitoring.dll", "DataQualityMonitoring.AlarmStatistics", methodName, severity, 1, "System.Int32", "{0:N0}", 0, 1001 - severity);
}
}
}
/// <summary>
/// Data operation to validate and ensure that certain records
/// that are required for alarming exist in the database.
/// </summary>
private static void ValidateAlarming(AdoDataConnection connection, string nodeIDQueryString)
{
// SELECT queries
const string AlarmCountFormat = "SELECT COUNT(*) FROM Alarm";
const string AlarmAdapterCountFormat = "SELECT COUNT(*) FROM CustomActionAdapter WHERE AdapterName = 'ALARM!SERVICES' AND NodeID = {0}";
const string AlarmConfigEntityCountFormat = "SELECT COUNT(*) FROM ConfigurationEntity WHERE RuntimeName = 'Alarms'";
const string AlarmSignalTypeCountFormat = "SELECT COUNT(*) FROM SignalType WHERE Name = 'Alarm'";
// INSERT queries
const string AlarmAdapterInsertFormat = "INSERT INTO CustomActionAdapter(NodeID, AdapterName, AssemblyName, TypeName, LoadOrder, Enabled) VALUES({0}, 'ALARM!SERVICES', 'DataQualityMonitoring.dll', 'DataQualityMonitoring.AlarmAdapter', 0, 1)";
const string AlarmConfigEntityInsertFormat = "INSERT INTO ConfigurationEntity(SourceName, RuntimeName, Description, LoadOrder, Enabled) VALUES('Alarm', 'Alarms', 'Defines alarms that monitor the values of measurements', 17, 1)";
const string AlarmSignalTypeInsertFormat = "INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, Source, EngineeringUnits) VALUES('Alarm', 'ALRM', 'AL', 'AL', 'Any', '')";
bool alarmTableExists;
Guid nodeID;
int alarmAdapterCount;
int alarmConfigEntityCount;
int alarmSignalTypeCount;
try
{
// Determine whether the alarm table exists
// before inserting records related to alarming
connection.Connection.ExecuteScalar(AlarmCountFormat);
alarmTableExists = true;
}
catch
{
alarmTableExists = false;
}
if (alarmTableExists)
{
nodeID = Guid.Parse(nodeIDQueryString.Trim('\''));
// Ensure that the alarm adapter is defined.
alarmAdapterCount = connection.ExecuteScalar<int>(AlarmAdapterCountFormat, nodeID);
if (alarmAdapterCount == 0)
connection.ExecuteNonQuery(AlarmAdapterInsertFormat, nodeID);
// Ensure that the alarm record is defined in the ConfigurationEntity table.
alarmConfigEntityCount = connection.ExecuteScalar<int>(AlarmConfigEntityCountFormat);
if (alarmConfigEntityCount == 0)
connection.ExecuteNonQuery(AlarmConfigEntityInsertFormat);
// Ensure that the alarm record is defined in the SignalType table.
alarmSignalTypeCount = connection.ExecuteScalar<int>(AlarmSignalTypeCountFormat);
if (alarmSignalTypeCount == 0)
connection.ExecuteNonQuery(AlarmSignalTypeInsertFormat);
ValidateAlarmStatistics(connection, nodeID, "Point");
}
}
/// <summary>
/// Saves <see cref="Company"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="powerCalculation">Information about <see cref="PowerCalculation"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, PowerCalculation powerCalculation)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (powerCalculation.ID == 0)
{
query = "INSERT INTO PowerCalculation (CircuitDescription, VoltageAngleSignalID, VoltageMagSignalID, CurrentAngleSignalID, CurrentMagSignalID, ActivePowerOutputSignalID, ReactivePowerOutputSignalID, ApparentPowerOutputSignalID, Enabled, NodeID) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})";
database.ExecuteNonQuery(DefaultTimeout, query, powerCalculation.CircuitDescription, powerCalculation.VoltagePhasor.AngleMeasurement.SignalID,
powerCalculation.VoltagePhasor.MagnitudeMeasurement.SignalID, powerCalculation.CurrentPhasor.AngleMeasurement.SignalID, powerCalculation.CurrentPhasor.MagnitudeMeasurement.SignalID,
(object)powerCalculation.ActivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ReactivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ApparentPowerOutputMeasurement?.SignalID ?? DBNull.Value,
powerCalculation.Enabled, database.CurrentNodeID());
}
else
{
query = "UPDATE PowerCalculation SET CircuitDescription = {0}, VoltageAngleSignalID = {1}, VoltageMagSignalID = {2}, CurrentAngleSignalID = {3}, CurrentMagSignalID = {4}, " +
"activePowerOutputSignalID = {5}, ReactivePowerOutputSignalID = {6}, ApparentPowerOutputSignalID = {7}, Enabled = {8} WHERE ID = {9}";
database.ExecuteNonQuery(DefaultTimeout, query, powerCalculation.CircuitDescription,
database.Guid(powerCalculation.VoltagePhasor.AngleMeasurement.SignalID), powerCalculation.VoltagePhasor.MagnitudeMeasurement.SignalID, powerCalculation.CurrentPhasor.AngleMeasurement.SignalID, powerCalculation.CurrentPhasor.MagnitudeMeasurement.SignalID,
(object)powerCalculation.ActivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ReactivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ApparentPowerOutputMeasurement?.SignalID ?? DBNull.Value, powerCalculation.Enabled, powerCalculation.ID);
}
return "Power Calculation information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <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=3; LeadTime=1', 1)");
}
/// <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();
}
}
private static int LoadSentEmail(List<string> recipients, string subject, string body)
{
DateTime now = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, s_timeZone);
string toLine = string.Join("; ", recipients.Select(recipient => recipient.Trim()));
using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
{
connection.ExecuteNonQuery("INSERT INTO SentEmail VALUES({0}, {1}, {2}, {3})", now, toLine, subject, body);
return connection.ExecuteScalar<int>("SELECT @@IDENTITY");
}
}
public static ActionResult DatabaseQueryAction(Session session)
{
string connectionString;
string dataProviderString;
string query;
LogInstallMessage(session, "Begin DatabaseQueryAction");
// Get properties from the installer session
connectionString = GetPropertyValue(session, "CONNECTIONSTRING");
dataProviderString = GetPropertyValue(session, "DATAPROVIDERSTRING");
query = GetPropertyValue(session, "DBQUERY");
try
{
// Execute the database script
using (AdoDataConnection connection = new AdoDataConnection(connectionString, dataProviderString))
{
connection.ExecuteNonQuery(query);
}
}
catch (Exception ex)
{
// Log the error and return failure code
string message = $"Failed to execute database query: {ex.Message}.";
LogInstallMessage(session, InstallMessage.Error, message);
LogInstallMessage(session, EventLogEntryType.Error, ex);
LogInstallMessage(session, InstallMessage.Error,
$"Database Query: {query}{Environment.NewLine}" +
$"Connection string: {connectionString}{Environment.NewLine}" +
$"Data provider string: {dataProviderString}");
return ActionResult.Failure;
}
LogInstallMessage(session, "End DatabaseQueryAction");
return ActionResult.Success;
}
private static void MeasurementDeviceAssociation(AdoDataConnection connection, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException)
{
if (string.IsNullOrEmpty(arguments))
{
statusMessage("WARNING: No arguments supplied to MeasurementDeviceAssociation data operation - no action will be performed. Expecting \"deviceAcronym\" and \"lookupExpression\" settings at a minimum.");
return;
}
Dictionary<string, string> args = arguments.ParseKeyValuePairs();
string deviceAcronym;
if (!args.TryGetValue("DeviceAcronym", out deviceAcronym))
{
statusMessage("WARNING: No \"deviceAcronyym\" argument supplied to MeasurementDeviceAssociation data operation - no action will be performed. Expecting \"deviceAcronym\" and \"lookupExpression\" settings at a minimum.");
return;
}
string lookupExpression;
if (!args.TryGetValue("LookupExpression", out lookupExpression))
{
statusMessage("WARNING: No \"lookupExpression\" argument supplied to MeasurementDeviceAssociation data operation - no action will be performed. Expecting \"deviceAcronym\" and \"lookupExpression\" settings at a minimum.");
return;
}
// Make sure device acronym exists
if (connection.ExecuteScalar<int>($"SELECT COUNT(*) FROM Device WHERE NodeID={nodeIDQueryString} AND Acronym={{0}}", deviceAcronym) == 0)
{
// Lookup virtual device protocol
if (connection.ExecuteScalar<int>("SELECT COUNT(*) FROM Protocol WHERE Acronym='VirtualInput'") == 0)
{
statusMessage("WARNING: No VirutalInput device protocol was found in source database configuration for MeasurementDeviceAssociation data operation - no action will be performed.");
return;
}
statusMessage($"Creating new \"{deviceAcronym}\" virtual device...");
int virtualProtocolID = connection.ExecuteScalar<int>("SELECT ID FROM Protocol WHERE Acronym='VirtualInput'");
// Create new virtual device record
connection.ExecuteNonQuery($"INSERT INTO Device(NodeID, Acronym, Name, ProtocolID, Enabled) VALUES({nodeIDQueryString}, {{0}}, {{1}}, {{2}}, 1)", deviceAcronym, deviceAcronym, virtualProtocolID);
}
statusMessage($"Validating \"{deviceAcronym}\" virtual device measurement associations...");
// Get device ID
int deviceID = connection.ExecuteScalar<int>($"SELECT ID FROM Device WHERE NodeID={nodeIDQueryString} AND Acronym={{0}}", deviceAcronym);
// Get measurements that should be associated with device ID but are not currently
IEnumerable<DataRow> measurements = connection.RetrieveData($"SELECT PointID FROM Measurement WHERE ({lookupExpression}) AND (DeviceID IS NULL OR DeviceID <> {{0}})", deviceID).AsEnumerable();
int associatedMeasurements = 0;
foreach (DataRow row in measurements)
{
connection.ExecuteNonQuery("UPDATE Measurement SET DeviceID={0} WHERE PointID={1}", deviceID, row.Field<int>("PointID"));
associatedMeasurements++;
}
if (associatedMeasurements > 0)
statusMessage($"Associated \"{associatedMeasurements}\" measurements to \"{deviceAcronym}\" virtual device...");
}
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>
/// Method to add <see cref="Measurement"/>s to <see cref="MeasurementGroup"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="measurementGroupID">ID of the <see cref="MeasurementGroup"/> to add <see cref="Measurement"/> to.</param>
/// <param name="measurementsToBeAdded">List of <see cref="Measurement"/> signal ids to be added.</param>
/// <returns>string, indicating success for UI display.</returns>
public static int AddMeasurements(AdoDataConnection database, int measurementGroupID, ICollection<Guid> measurementsToBeAdded)
{
string QueryFormat =
"INSERT INTO MeasurementGroupMeasurement (NodeID, MeasurementGroupID, SignalID) " +
"SELECT {0}, {1}, {2} " +
"WHERE (SELECT COUNT(*) FROM MeasurementGroupMeasurement WHERE MeasurementGroupID = {1} AND SignalID = {2}) = 0";
bool createdConnection = false;
int rowsAffected = 0;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in measurementsToBeAdded)
rowsAffected += database.ExecuteNonQuery(DefaultTimeout, QueryFormat, CommonFunctions.CurrentNodeID(), measurementGroupID, id);
return rowsAffected;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Counts measurements assigned to <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> for which to count measurements.</param>
/// <param name="allowed">boolean flag to indicate if measurements are allowed or denied.</param>
/// <returns>string, indicating success for UI display.</returns>
public static int GetGroupCount(AdoDataConnection database, Guid subscriberID, bool allowed)
{
const string QueryFormat = "SELECT COUNT(*) FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND Allowed = {1}";
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
return database.ExecuteNonQuery(DefaultTimeout, QueryFormat, subscriberID, allowed);
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
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());
}
}
}
/// <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>
/// 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)");
}
