/// <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>
/// 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);
}
}
private void UserControl_Loaded(object sender, System.Windows.RoutedEventArgs e)
{
string webHostURL;
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
string nodeSettings = connection.ExecuteScalar <string>("SELECT Settings FROM Node WHERE ID = {0}", connection.CurrentNodeID());
Dictionary <string, string> lookup = nodeSettings.ParseKeyValuePairs();
if (!lookup.TryGetValue("WebHostURL", out webHostURL))
{
webHostURL = "http://localhost:8180/";
lookup.Add("WebHostURL", webHostURL);
nodeSettings = lookup.JoinKeyValuePairs();
connection.ExecuteNonQuery("UPDATE Node SET Settings = {0} WHERE ID = {1}", nodeSettings, connection.CurrentNodeID());
}
}
m_browser.Source = new Uri(webHostURL.TrimEnd('/') + "/" + m_urlPath.TrimStart('/'));
}
public ActionResult GetComponents(int eventID)
{
using (AdoDataConnection pqiConnection = new AdoDataConnection(m_configuration["IndustrialPQ:ConnectionString"], m_configuration["IndustrialPQ:DataProviderString"]))
using (AdoDataConnection upqiConnection = new AdoDataConnection(m_configuration["UserIndustrialPQ:ConnectionString"], m_configuration["UserIndustrialPQ:DataProviderString"]))
using (AdoDataConnection xdaConnection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
int facilityID = xdaConnection.ExecuteScalar <int>("SELECT FacilityID FROM MeterFacility WHERE MeterID = (SELECT MeterID FROM Event WHERE ID = {0})", eventID);
DataTable pqiTable = pqiConnection.RetrieveData(@"
select
asec.Title,
tc.TestCurveID,
ac.CurveDB
From
UserIndustrialPQ.dbo.FacilityAudit fa JOIN
UserIndustrialPQ.dbo.AuditSection asec on asec.FacilityAuditID = fa.FacilityAuditID JOIN
UserIndustrialPQ.dbo.AuditCurve ac on ac.AuditSectionID = asec.AuditSectionID JOIN
IndustrialPQ.dbo.TestCurve tc on ac.CurveID = tc.TestCurveID JOIN
IndustrialPQ.dbo.Component com on tc.ComponentID = com.ComponentID
WHERE
fa.FacilityID = {0} AND ac.CurveDB = 'EPRI'
", facilityID);
DataTable upqiTable = upqiConnection.RetrieveData(@"
select
asec.Title,
tc.TestCurveID,
ac.CurveDB
From
UserIndustrialPQ.dbo.FacilityAudit fa JOIN
UserIndustrialPQ.dbo.AuditSection asec on asec.FacilityAuditID = fa.FacilityAuditID JOIN
UserIndustrialPQ.dbo.AuditCurve ac on ac.AuditSectionID = asec.AuditSectionID JOIN
UserIndustrialPQ.dbo.TestCurve tc on ac.CurveID = tc.TestCurveID JOIN
UserIndustrialPQ.dbo.Component com on tc.ComponentID = com.ComponentID
WHERE
fa.FacilityID = {0} AND ac.CurveDB = 'USER'
", facilityID);
pqiTable.Merge(upqiTable);
return(Ok(pqiTable));
}
}
private static int GetOrAddSubStation(string name, LookupTables lookupTables, AdoDataConnection connection)
{
SubStation station = new SubStation
{
Name = name,
};
if (lookupTables.SubStationsLookup.ContainsKey(name))
{
station.ID = lookupTables.SubStationsLookup[name].ID;
(new TableOperations <SubStation>(connection)).UpdateRecord(station);
}
else
{
(new TableOperations <SubStation>(connection)).AddNewRecord(station);
station.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY");
lookupTables.SubStationsLookup.Add(name, station);
}
return(station.ID);
}
private static int GetOrAddCircuit(string name, XElement deviceElement, LookupTables lookupTables, AdoDataConnection connection)
{
Circuit circuit = new Circuit {
Name = name,
SystemID = GetOrAddSystem((string)deviceElement.Element("lines").Elements("line").First().Element("voltage"), lookupTables, connection)
};
if (lookupTables.CircuitsLookup.ContainsKey(name))
{
circuit.ID = lookupTables.CircuitsLookup[name].ID;
(new TableOperations <Circuit>(connection)).UpdateRecord(circuit);
}
else
{
(new TableOperations <Circuit>(connection)).AddNewRecord(circuit);
circuit.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY");
lookupTables.CircuitsLookup.Add(name, circuit);
}
return(circuit.ID);
}
private void LoadEvents(AdoDataConnection connection, List <Event> events)
{
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
TableOperations <EventData> eventDataTable = new TableOperations <EventData>(connection);
TableOperations <DbDisturbance> disturbanceTable = new TableOperations <DbDisturbance>(connection);
foreach (Event evt in events)
{
IDbDataParameter startTime2 = ToDateTime2(connection, evt.StartTime);
IDbDataParameter endTime2 = ToDateTime2(connection, evt.EndTime);
if (eventTable.QueryRecordsWhere("StartTime = {0} AND EndTime = {1} AND Samples = {2} AND MeterID = {3} AND LineID = {4}", startTime2, endTime2, evt.Samples, evt.MeterID, evt.LineID).Any())
{
continue;
}
EventData eventData = evt.EventData;
if ((object)eventData != null)
{
eventDataTable.AddNewRecord(eventData);
eventData.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY");
evt.EventDataID = eventData.ID;
}
eventTable.AddNewRecord(evt);
evt.ID = eventTable.QueryRecordWhere("StartTime = {0} AND EndTime = {1} AND Samples = {2} AND MeterID = {3} AND LineID = {4}", startTime2, endTime2, evt.Samples, evt.MeterID, evt.LineID).ID;
foreach (DbDisturbance disturbance in evt.Disturbances)
{
disturbance.EventID = evt.ID;
disturbanceTable.AddNewRecord(disturbance);
}
connection.ExecuteNonQuery(@"
IF dbo.EventHasImpactedComponents({0}) = 1
INSERT INTO PQIResult VALUES ({0})
", evt.ID);
}
}
public BenRunner(int deviceId, int taskId)
{
m_adoDataConnection = new AdoDataConnection(Program.OpenMiConfigurationFile.Settings["systemSettings"]["ConnectionString"].Value, Program.OpenMiConfigurationFile.Settings["systemSettings"]["DataProviderString"].Value);
try
{
string taskSettingsString = m_adoDataConnection.ExecuteScalar <string>("Select Settings From ConnectionProfileTask WHERE ID = {0}", taskId);
m_connectionProfileTaskSettings = taskSettingsString.ParseKeyValuePairs();
m_deviceRecord = m_adoDataConnection.RetrieveRow("Select * From Device WHERE ID = {0}", deviceId);
Dictionary <string, string> deviceConnection = m_deviceRecord["connectionString"].ToString().ParseKeyValuePairs();
m_connectionProfile = m_adoDataConnection.RetrieveRow("SELECT * FROM connectionProfile WHERE ID = {0}", deviceConnection["connectionProfileID"]);
m_folder = m_deviceRecord["OriginalSource"].ToString();
m_ipAddress = deviceConnection["connectionUserName"].Split('&')[0];
m_localPath = m_connectionProfileTaskSettings["localPath"];
m_siteName = m_deviceRecord["Name"].ToString();
m_serialNumber = deviceConnection["connectionUserName"].Split('&')[1];
string tempDirectory = System.IO.Path.GetTempPath();
System.IO.Directory.CreateDirectory(tempDirectory + "\\BenDownloader\\" + m_siteName);
m_tempDirectoryName = tempDirectory + "BenDownloader\\" + m_siteName + "\\";
//Console.WriteLine(m_tempDirectoryName);
m_fileWatcher = new SafeFileWatcher(m_tempDirectoryName);
m_fileWatcher.NotifyFilter = NotifyFilters.Size | NotifyFilters.CreationTime | NotifyFilters.LastWrite | NotifyFilters.LastAccess | NotifyFilters.FileName;
m_fileWatcher.Changed += m_fileWatcher_Changed;
m_fileWatcher.Created += m_fileWatcher_Changed;
m_activityMonitor = new System.Timers.Timer(5000.0D);
m_activityMonitor.Elapsed += m_activityMonitor_Elapsed;
m_activityMonitor.AutoReset = true;
m_lastFileDownloaded = GetLastDownloadedFile();
m_lastFileDownloadedThisSession = "";
}
catch (Exception ex)
{
Program.Log(ex.ToString(), m_tempDirectoryName);
}
}
public void Parse(string filePath)
{
m_pqdifReader.Parse(filePath);
using (AdoDataConnection connection = CreateDbConnection())
{
Func <DateTime, IDbDataParameter> toDateTime2 = dateTime => ToDateTime2(connection, dateTime);
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
string meterKey = GetMeterKey(filePath, m_filePattern);
int meterID = connection.ExecuteScalar <int>("SELECT ID FROM Meter WHERE AssetKey = {0}", meterKey);
Predicate <DataSeries> isDuplicate = dataSeries =>
{
int samples = dataSeries.DataPoints.Count;
if (samples == 0)
{
return(false);
}
DateTime startTime = dataSeries.DataPoints[0].Time;
DateTime endTime = dataSeries.DataPoints[dataSeries.DataPoints.Count - 1].Time;
RecordRestriction recordRestriction =
new RecordRestriction("MeterID = {0}", meterID) &
new RecordRestriction("StartTime = {0}", toDateTime2(startTime)) &
new RecordRestriction("EndTime = {0}", toDateTime2(endTime)) &
new RecordRestriction("Samples = {0}", samples);
int eventCount = eventTable.QueryRecordCount(recordRestriction);
return(eventCount > 0);
};
MeterDataSet meterDataSet = m_pqdifReader.MeterDataSet;
meterDataSet.DataSeries.RemoveAll(isDuplicate);
meterDataSet.Digitals.RemoveAll(isDuplicate);
}
}
private ActionResult HandleVerifyResendCode(VerifyCodeModel formData, ConfirmableUserAccount user)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
string url = connection.ExecuteScalar <string>("SELECT AltText1 FROM ValueList WHERE Text = 'URL' AND GroupID = (SELECT ID FROM ValueListGroup WHERE Name = 'System')");
// if email changed force reconfirmation
if (formData.type == "email")
{
// generate code for email confirmation
string code = Random.Int32Between(0, 999999).ToString("D6");
s_memoryCache.Set("email" + user.ID.ToString(), code, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromDays(1)
});
string emailServiceName = GetEmailServiceName();
string subject = $"{emailServiceName} requires you to confirm your email.";
string body = $"From your workstation, input {code} at {url}/email/verify/email";
SendEmail(user.Email, subject, body);
}
// if phone changed force reconfirmation
if (formData.type == "sms")
{
string code = Random.Int32Between(0, 999999).ToString("D6");
s_memoryCache.Set("sms" + user.ID.ToString(), code, new CacheItemPolicy {
SlidingExpiration = TimeSpan.FromDays(1)
});
string emailServiceName = GetEmailServiceName();
string subject = $"{emailServiceName} requires you to confirm your SMS number.";
string body = $"From your workstation, input {code} at {url}/email/verify/sms";
SendEmail(user.Phone, subject, body);
}
return(RedirectToAction("Verify", new { id = formData.type }));
}
}
public Task <int> GetCount([FromBody] GetDataFilesPostData postData, CancellationToken cancellationToken)
{
return(Task.Run(() =>
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
const string QueryFormat =
"SELECT COUNT(*) " +
"FROM " +
"( " +
" SELECT DISTINCT FileGroupID " +
" FROM DataFile " +
" WHERE {0} " +
") DataFile";
TableOperations <DataFile> dataFileTable = new TableOperations <DataFile>(connection);
RecordRestriction restriction = dataFileTable.GetSearchRestriction(postData.filterString);
string query = string.Format(QueryFormat, restriction?.FilterExpression ?? "1=1");
return connection.ExecuteScalar <int>(query, restriction?.Parameters ?? new object[0]);
}
}, cancellationToken));
}
public IHttpActionResult AddLineSegmentToLine(int segmentID, int lineID)
{
if (PatchRoles == string.Empty || User.IsInRole(PatchRoles))
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
AssetConnection assetConnection = new AssetConnection()
{
AssetRelationshipTypeID = connection.ExecuteScalar <int>("SELECT ID FROM AssetRelationShipType WHERE Name = 'Line-LineSegment'"),
ChildID = lineID,
ParentID = segmentID
};
(new TableOperations <AssetConnection>(connection)).AddNewRecord(assetConnection);
return(Ok(1));
}
}
else
{
return(Unauthorized());
}
}
// Gets the series info data for the series with the given identifier.
// Optimized to prevent loading series data for irrelevant channels.
private Series GetSeriesInfo(Meter meter, int seriesID)
{
List <Channel> channels = meter.Channels;
// Disable lazy loading
var connectionFactory = meter.ConnectionFactory;
meter.ConnectionFactory = null;
// Search for a series that has already been loaded
Series seriesInfo = channels
.Where(channel => (object)channel.Series != null)
.SelectMany(channel => channel.Series)
.FirstOrDefault(series => series.ID == seriesID);
// Restore lazy loading
meter.ConnectionFactory = connectionFactory;
// If the series was found without
// lazy loading, return that series
if ((object)seriesInfo != null)
{
return(seriesInfo);
}
int channelID;
using (AdoDataConnection connection = meter.ConnectionFactory())
{
// Get the channel ID of the series so we can skip lazy loading series collections for irrelevant channels
channelID = connection.ExecuteScalar <int>("SELECT ChannelID FROM Series WHERE ID = {0}", seriesID);
}
// Now filter to the appropriate channel and search its series collection
return(channels
.Where(channel => channel.ID == channelID)
.SelectMany(channel => channel.Series)
.FirstOrDefault(series => series.ID == seriesID));
}
public List <EventView> Get(int templateID)
{
NameValueCollection queryParameters = Request.RequestUri.ParseQueryString();
string countValue = queryParameters["count"];
if (!int.TryParse(countValue, out int count))
{
count = 50;
}
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
string getTriggerSQL =
"SELECT TriggersEmailSQL " +
"FROM " +
" EventEmailParameters JOIN " +
" EmailType ON EventEmailParameters.EmailTypeID = EmailType.ID " +
"WHERE XSLTemplateID = {0}";
string triggerFormat = connection.ExecuteScalar <string>(getTriggerSQL, templateID);
string triggerSQL = string.Format(triggerFormat, "E.ID");
string getEvents =
$"SELECT TOP {count} EventView.* " +
$"FROM " +
$" Event as E CROSS APPLY " +
$" ({triggerSQL}) EmailTrigger(Value) JOIN" +
$" EventView ON E.ID = EventView.ID " +
$"WHERE EmailTrigger.Value <> 0 " +
$"ORDER BY EventView.StartTime DESC";
TableOperations <EventView> eventTable = new TableOperations <EventView>(connection);
return(connection.RetrieveData(getEvents)
.AsEnumerable()
.Select(eventTable.LoadRecord)
.ToList());
}
}
public UserAccountMetaDataUpdater()
{
try
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
ChangedUserAccounts = new List <ChangedUser>();
FromAddress = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.FromAddress'");
EnableSSL = connection.ExecuteScalar <bool>("SELECT Value FROM Setting WHERE Name = 'Email.EnableSSL'");
SMTPServer = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.SMTPServer'");
AdminAddress = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.AdminAddress'");
Username = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.Username'");
SecurePassword = connection.ExecuteScalar <SecureString>("SELECT Value FROM Setting WHERE Name = 'Email.Password'");
Url = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'SystemCenter.Url'");
}
}
catch (Exception ex) {
Log.Error(ex.Message, ex);
}
}
public ActionResult GetCount()
{
using (AdoDataConnection sCConnection = new AdoDataConnection(m_configuration["SystemCenter:ConnectionString"], m_configuration["SystemCenter:DataProviderString"]))
using (AdoDataConnection connection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
string orgId = (User.Identity as ClaimsIdentity).Claims.FirstOrDefault(c => c.Type == "org_id")?.Value;
DataTable meters = sCConnection.RetrieveData(@"SELECT OpenXDAMeterID FROM CompanyMeter WHERE CompanyID = (SELECT ID FROM Company WHERE CompanyID = {0})", orgId);
if (meters.Rows.Count == 0)
{
return(Ok(new DataTable()));
}
return(Ok(connection.ExecuteScalar <int>(@"
SELECT
COUNT(*)
FROM
Event
WHERE
StartTime BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AND GETDATE() AND
MeterID IN (" + string.Join(",", meters.Select().Select(row => row["OpenXDAMeterID"])) + @")
")));
}
}
// 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
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));
}
}
}
public IHttpActionResult Get(int eventID, int timeWindow)
{
try
{
using (AdoDataConnection connection = new AdoDataConnection(SettingsCategory))
using (AdoDataConnection xdaConnection = new AdoDataConnection("dbOpenXDA"))
{
DateTime eventTime = xdaConnection.ExecuteScalar <DateTime>("SELECT StartTime FROM Event WHERE ID = {0}", eventID);
DataTable table = connection.RetrieveData(@"
SELECT
alarmdatetime as Time,
stationname + ' ' + alarmpoint as Alarm,
alarmstatus as Status
FROM soealarmdetailwithhierarchy
WHERE alarmdatetime between {0} and {1}
", eventTime.AddSeconds(-1 * timeWindow), eventTime.AddSeconds(timeWindow));
return(Ok(table));
}
}
catch (Exception ex) {
return(InternalServerError(ex));
}
}
public ActionResult Get(int eventID, int pixels)
{
using (AdoDataConnection connection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
Event evt = new TableOperations <Event>(connection).QueryRecordWhere("ID = {0}", eventID);
if (evt == null)
{
return(BadRequest("Must provide a valid EventID"));
}
Meter meter = new TableOperations <Meter>(connection).QueryRecordWhere("ID = {0}", evt.MeterID);
meter.ConnectionFactory = () => new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]);
Dictionary <string, IEnumerable <double[]> > returnData = new Dictionary <string, IEnumerable <double[]> >();
DataGroupHelper dataGroupHelper = new DataGroupHelper(m_configuration, m_memoryCache);
double systemFrequency = connection.ExecuteScalar <double?>("SELECT Value FROM Setting WHERE Name = 'SystemFrequency'") ?? 60.0;
DataGroup dataGroup = dataGroupHelper.QueryDataGroup(eventID, meter);;
Dictionary <string, List <double[]> > returnList = new Dictionary <string, List <double[]> >();
List <DataSeries> vAN = dataGroup.DataSeries.Where(x => x.SeriesInfo.Channel.MeasurementType.Name == "Voltage" && x.SeriesInfo.Channel.MeasurementCharacteristic.Name == "Instantaneous" && x.SeriesInfo.Channel.Phase.Name == "AN").ToList();
List <DataSeries> iAN = dataGroup.DataSeries.Where(x => x.SeriesInfo.Channel.MeasurementType.Name == "Current" && x.SeriesInfo.Channel.MeasurementCharacteristic.Name == "Instantaneous" && x.SeriesInfo.Channel.Phase.Name == "AN").ToList();
List <DataSeries> vBN = dataGroup.DataSeries.Where(x => x.SeriesInfo.Channel.MeasurementType.Name == "Voltage" && x.SeriesInfo.Channel.MeasurementCharacteristic.Name == "Instantaneous" && x.SeriesInfo.Channel.Phase.Name == "BN").ToList();
List <DataSeries> iBN = dataGroup.DataSeries.Where(x => x.SeriesInfo.Channel.MeasurementType.Name == "Current" && x.SeriesInfo.Channel.MeasurementCharacteristic.Name == "Instantaneous" && x.SeriesInfo.Channel.Phase.Name == "BN").ToList();
List <DataSeries> vCN = dataGroup.DataSeries.Where(x => x.SeriesInfo.Channel.MeasurementType.Name == "Voltage" && x.SeriesInfo.Channel.MeasurementCharacteristic.Name == "Instantaneous" && x.SeriesInfo.Channel.Phase.Name == "CN").ToList();
List <DataSeries> iCN = dataGroup.DataSeries.Where(x => x.SeriesInfo.Channel.MeasurementType.Name == "Current" && x.SeriesInfo.Channel.MeasurementCharacteristic.Name == "Instantaneous" && x.SeriesInfo.Channel.Phase.Name == "CN").ToList();
returnList.Add("VAN", GenerateTHD(systemFrequency, vAN.First()));
returnList.Add("VBN", GenerateTHD(systemFrequency, vBN.First()));
returnList.Add("VCN", GenerateTHD(systemFrequency, vCN.First()));
returnList.Add("IAN", GenerateTHD(systemFrequency, iAN.First()));
returnList.Add("IBN", GenerateTHD(systemFrequency, iBN.First()));
returnList.Add("ICN", GenerateTHD(systemFrequency, iCN.First()));
return(Ok(returnList));
}
}
/// <summary>
/// Provides an entry point for custom authorization checks.
/// </summary>
/// <param name="user">The <see cref="IPrincipal"/> for the client being authorize</param>
/// <returns>
/// <c>true</c> if the user is authorized, otherwise, <c>false</c>.
/// </returns>
protected override bool UserAuthorized(IPrincipal user)
{
// Get current user name
string userName = user.Identity.Name;
// Setup the principal
Thread.CurrentPrincipal = user;
SecurityProviderCache.ValidateCurrentProvider();
user = Thread.CurrentPrincipal;
// Verify that the current thread principal has been authenticated.
if (!Thread.CurrentPrincipal.Identity.IsAuthenticated)
{
throw new SecurityException($"Authentication failed for user '{userName}': {SecurityProviderCache.CurrentProvider.AuthenticationFailureReason}");
}
if (AllowedRoles.Length > 0 && !AllowedRoles.Any(role => user.IsInRole(role)))
{
throw new SecurityException($"Access is denied for user '{userName}': minimum required roles = {AllowedRoles.ToDelimitedString(", ")}.");
}
// Make sure current user ID is cached
if (!AuthorizationCache.UserIDs.ContainsKey(userName))
{
using (AdoDataConnection connection = new AdoDataConnection(SettingsCategory))
{
Guid?userID = connection.ExecuteScalar <Guid?>("SELECT ID FROM UserAccount WHERE Name={0}", UserInfo.UserNameToSID(userName));
if ((object)userID != null)
{
AuthorizationCache.UserIDs.TryAdd(userName, userID.GetValueOrDefault());
}
}
}
return(true);
}
public override IHttpActionResult Post([FromBody] JObject record)
{
try
{
if (User.IsInRole(PostRoles))
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
BaseConfig newRecord = record.ToObject <BaseConfig>();
int result = new TableOperations <BaseConfig>(connection).AddNewRecord(newRecord);
int id = connection.ExecuteScalar <int>("SELECT ID FROM BaseConfig where MeterID = {0} and Name = {1} AND Pattern = {2}", newRecord.MeterId, newRecord.Name, newRecord.Pattern);
JToken fields;
if (record.TryGetValue("Fields", out fields))
{
List <ComplianceField> flds = fields.ToObject <List <ComplianceField> >();
flds.ForEach(fld =>
{
fld.BaseConfigId = id;
new TableOperations <ComplianceField>(connection).AddNewRecord(fld);
});
}
return(Ok(result));
}
}
else
{
return(Unauthorized());
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
private static MeterLocation LoadRemoteMeterLocationAttributes(LookupTables lookupTables, XElement lineElement, AdoDataConnection connection)
{
MeterLocation meterLocation = new MeterLocation {
Name = (string)lineElement.Element("endStationName"),
ShortName = new string(((string)lineElement.Element("endStationName")).Take(50).ToArray()),
AssetKey = (string)lineElement.Element("endStationID"),
Latitude = double.Parse((string)lineElement.Element("endStationLatitude") ?? "0"),
Longitude = double.Parse((string)lineElement.Element("endStationLongitude") ?? "0")
};
if (lookupTables.MeterLocationLookup.ContainsKey(meterLocation.AssetKey))
{
meterLocation.ID = lookupTables.MeterLocationLookup[meterLocation.AssetKey].ID;
(new TableOperations <MeterLocation>(connection)).UpdateRecord(meterLocation);
}
else
{
(new TableOperations <MeterLocation>(connection)).AddNewRecord(meterLocation);
meterLocation.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY");
lookupTables.MeterLocationLookup.Add(meterLocation.AssetKey, meterLocation);
}
return(meterLocation);
}
public DailyEmail()
{
try
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
ConnectionString = connection.Connection.ConnectionString;
FromAddress = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.FromAddress'");
EnableSSL = connection.ExecuteScalar <bool>("SELECT Value FROM Setting WHERE Name = 'Email.EnableSSL'");
SMTPServer = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.SMTPServer'");
AdminAddress = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.AdminAddress'");
Username = connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Email.Username'");
SecurePassword = connection.ExecuteScalar <SecureString>("SELECT Value FROM Setting WHERE Name = 'Email.Password'");
}
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
}
}
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));
}
}
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");
}
}
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...");
}
// Static Methods
private static Series GetSeriesInfo(Meter meter, DataGroup dataGroup, string measurementTypeName, string phaseName)
{
int lineID = dataGroup.Line.ID;
string measurementCharacteristicName = "Instantaneous";
string seriesTypeName = "Values";
char typeDesignation = (measurementTypeName == "Current") ? 'I' : measurementTypeName[0];
string phaseDesignation = (phaseName == "RES") ? "R" : phaseName.TrimEnd('N');
string channelName = string.Concat(typeDesignation, phaseDesignation);
ChannelKey channelKey = new ChannelKey(lineID, 0, channelName, measurementTypeName, measurementCharacteristicName, phaseName);
SeriesKey seriesKey = new SeriesKey(channelKey, seriesTypeName);
Series dbSeries = meter.Channels
.SelectMany(channel => channel.Series)
.FirstOrDefault(series => seriesKey.Equals(new SeriesKey(series)));
if ((object)dbSeries == null)
{
using (AdoDataConnection connection = meter.ConnectionFactory())
{
Channel dbChannel = meter.Channels
.FirstOrDefault(channel => channelKey.Equals(new ChannelKey(channel)));
if ((object)dbChannel == null)
{
TableOperations <Channel> channelTable = new TableOperations <Channel>(connection);
TableOperations <MeasurementType> measurementTypeTable = new TableOperations <MeasurementType>(connection);
TableOperations <MeasurementCharacteristic> measurementCharacteristicTable = new TableOperations <MeasurementCharacteristic>(connection);
TableOperations <Phase> phaseTable = new TableOperations <Phase>(connection);
MeasurementType measurementType = measurementTypeTable.GetOrAdd(measurementTypeName);
MeasurementCharacteristic measurementCharacteristic = measurementCharacteristicTable.GetOrAdd(measurementCharacteristicName);
Phase phase = phaseTable.GetOrAdd(phaseName);
dbChannel = new Channel()
{
MeterID = meter.ID,
LineID = lineID,
MeasurementTypeID = measurementType.ID,
MeasurementCharacteristicID = measurementCharacteristic.ID,
PhaseID = phase.ID,
Name = channelKey.Name,
SamplesPerHour = dataGroup.SamplesPerHour,
Description = string.Concat(measurementCharacteristicName, " ", measurementTypeName, " ", phaseName),
Enabled = true
};
channelTable.AddNewRecord(dbChannel);
dbChannel.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY");
meter.Channels = null;
}
TableOperations <Series> seriesTable = new TableOperations <Series>(connection);
TableOperations <SeriesType> seriesTypeTable = new TableOperations <SeriesType>(connection);
SeriesType seriesType = seriesTypeTable.GetOrAdd(seriesTypeName);
dbSeries = new Series()
{
ChannelID = dbChannel.ID,
SeriesTypeID = seriesType.ID,
SourceIndexes = string.Empty
};
seriesTable.AddNewRecord(dbSeries);
dbSeries.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY");
dbChannel.Series = null;
dbSeries = meter.Channels
.SelectMany(channel => channel.Series)
.First(series => seriesKey.Equals(new SeriesKey(series)));
}
}
return(dbSeries);
}
/// <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);
}
// 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);
}
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");
}
}
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 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>
/// Saves <see cref="UserAccount"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="userAccount">Information about <see cref="UserAccount"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, UserAccount userAccount)
{
const string ErrorMessage = "User name already exists.";
bool createdConnection = false;
string query;
string userAccountSID;
int existing;
try
{
createdConnection = CreateConnection(ref database);
string pColumn = "Password";
if (database.IsJetEngine)
pColumn = "[Password]";
object changePasswordOn = userAccount.ChangePasswordOn;
if (userAccount.ChangePasswordOn == DateTime.MinValue)
changePasswordOn = (object)DBNull.Value;
else if (database.IsJetEngine)
changePasswordOn = userAccount.ChangePasswordOn.ToOADate();
userAccountSID = UserInfo.UserNameToSID(userAccount.Name);
if (!userAccount.UseADAuthentication || !UserInfo.IsUserSID(userAccountSID))
userAccountSID = userAccount.Name;
if (userAccount.ID == Guid.Empty)
{
existing = Convert.ToInt32(database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM UserAccount WHERE Name = {0}", "name"), DefaultTimeout, userAccountSID));
if (existing > 0)
throw new InvalidOperationException(ErrorMessage);
query = database.ParameterizedQueryString("INSERT INTO UserAccount (Name, " + pColumn + ", FirstName, LastName, DefaultNodeID, Phone, Email, " +
"LockedOut, UseADAuthentication, ChangePasswordOn, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, " +
"{9}, {10}, {11}, {12}, {13})", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut", "useADAuthentication",
"changePasswordOn", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.CurrentNodeID(),
userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
CommonFunctions.LogEvent(string.Format("New user \"{0}\" created successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 2);
}
else
{
existing = database.ExecuteScalar<int>("SELECT COUNT(*) FROM UserAccount WHERE Name = {0} AND ID <> {1}", userAccountSID, userAccount.ID);
if (existing > 0)
throw new InvalidOperationException(ErrorMessage);
query = database.ParameterizedQueryString("UPDATE UserAccount SET Name = {0}, " + pColumn + " = {1}, FirstName = {2}, LastName = {3}, " +
"DefaultNodeID = {4}, Phone = {5}, Email = {6}, LockedOut = {7}, UseADAuthentication = {8}, ChangePasswordOn = {9}, UpdatedBy = {10}, " +
"UpdatedOn = {11} WHERE ID = {12}", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut",
"useADAuthentication", "changePasswordOn", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.Guid(userAccount.DefaultNodeID),
userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, database.Guid(userAccount.ID));
CommonFunctions.LogEvent(string.Format("Information about user \"{0}\" updated successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 3);
}
return "User account information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
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";
}
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();
}
/// <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;
}
// 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);
}
/// <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>
/// 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);
}
/// <summary>
/// Called when authorization is required.
/// </summary>
/// <param name="filterContext">The filter context.</param>
public void OnAuthorization(AuthorizationContext filterContext)
{
SecurityProviderCache.ValidateCurrentProvider();
// Setup the principal
filterContext.HttpContext.User = Thread.CurrentPrincipal;
// Get current user name
string userName = Thread.CurrentPrincipal.Identity.Name;
// Verify that the current thread principal has been authenticated.
if (!Thread.CurrentPrincipal.Identity.IsAuthenticated)
throw new SecurityException($"Authentication failed for user '{userName}': {SecurityProviderCache.CurrentProvider.AuthenticationFailureReason}");
if (AllowedRoles.Length > 0 && !AllowedRoles.Any(role => filterContext.HttpContext.User.IsInRole(role)))
throw new SecurityException($"Access is denied for user '{userName}': minimum required roles = {AllowedRoles.ToDelimitedString(", ")}.");
// Make sure current user ID is cached
if (!AuthorizationCache.UserIDs.ContainsKey(userName))
{
using (AdoDataConnection connection = new AdoDataConnection(SettingsCategory))
{
AuthorizationCache.UserIDs.TryAdd(userName, connection.ExecuteScalar<Guid?>("SELECT ID FROM UserAccount WHERE Name={0}", UserInfo.UserNameToSID(userName)) ?? Guid.Empty);
}
}
}
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);
}
}
}
}
}
}
private static void GenerateEmail(int eventID)
{
SystemInfoDataContext systemInfo;
MeterInfoDataContext meterInfo;
FaultLocationInfoDataContext faultInfo;
EventTableAdapter eventAdapter;
EventTypeTableAdapter eventTypeAdapter;
EventRow eventRow;
EventDataTable systemEvent;
int faultTypeID;
string eventDetail;
XDocument htmlDocument;
List<Attachment> attachments;
string subject;
string html;
bool alreadySent;
systemInfo = s_dbAdapterContainer.GetAdapter<SystemInfoDataContext>();
meterInfo = s_dbAdapterContainer.GetAdapter<MeterInfoDataContext>();
faultInfo = s_dbAdapterContainer.GetAdapter<FaultLocationInfoDataContext>();
eventAdapter = s_dbAdapterContainer.GetAdapter<EventTableAdapter>();
eventTypeAdapter = s_dbAdapterContainer.GetAdapter<EventTypeTableAdapter>();
faultTypeID = eventTypeAdapter.GetData()
.Where(eventType => eventType.Name == "Fault")
.Select(eventType => eventType.ID)
.FirstOrDefault();
// Load the system event before the eventDetail record to avoid race conditions causing missed emails
eventRow = eventAdapter.GetDataByID(eventID)[0];
systemEvent = eventAdapter.GetSystemEvent(eventRow.StartTime, eventRow.EndTime, s_timeTolerance);
eventDetail = eventAdapter.GetEventDetail(eventID);
List<IGrouping<int, Guid>> templateGroups;
using (SqlCommand command = new SqlCommand("GetEventEmailRecipients", s_dbAdapterContainer.Connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable recipientTable = new DataTable();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@eventID", eventID);
adapter.Fill(recipientTable);
templateGroups = recipientTable
.Select()
.GroupBy(row => row.ConvertField<int>("TemplateID"), row => row.ConvertField<Guid>("UserAccountID"))
.ToList();
}
foreach (IGrouping<int, Guid> templateGroup in templateGroups)
{
string template;
List<string> recipients;
using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
{
template = connection.ExecuteScalar<string>("SELECT Template FROM XSLTemplate WHERE ID = {0}", templateGroup.Key);
string paramString = string.Join(",", templateGroup.Select((userAccountID, index) => $"{{{index}}}"));
string sql = $"SELECT Email FROM UserAccount WHERE Email IS NOT NULL AND Email <> '' AND ID IN ({paramString})";
DataTable emailTable = connection.RetrieveData(sql, templateGroup.Cast<object>().ToArray());
recipients = emailTable.Select().Select(row => row.ConvertField<string>("Email")).ToList();
}
htmlDocument = XDocument.Parse(eventDetail.ApplyXSLTransform(template), LoadOptions.PreserveWhitespace);
htmlDocument.TransformAll("format", element => element.Format());
attachments = new List<Attachment>();
try
{
htmlDocument.TransformAll("chart", (element, index) =>
{
string cid = $"chart{index:00}.png";
Stream image = ChartGenerator.ConvertToChartImageStream(s_dbAdapterContainer, element);
Attachment attachment = new Attachment(image, cid);
attachment.ContentId = attachment.Name;
attachments.Add(attachment);
return new XElement("img", new XAttribute("src", $"cid:{cid}"));
});
subject = (string)htmlDocument.Descendants("title").FirstOrDefault() ?? "Fault detected by openXDA";
html = htmlDocument.ToString(SaveOptions.DisableFormatting).Replace("&", "&");
alreadySent = false;
try
{
int sentEmailID;
using (AdoDataConnection connection = new AdoDataConnection(s_dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
{
string systemEventIDs = string.Join(",", systemEvent.Where(row => row.LineID == eventRow.LineID).Select(row => row.ID));
string query =
$"SELECT SentEmail.ID " +
$"FROM " +
$" SentEmail JOIN " +
$" EventSentEmail ON EventSentEmail.SentEmailID = SentEmail.ID " +
$"WHERE " +
$" EventSentEmail.EventID IN ({systemEventIDs}) AND " +
$" SentEmail.Message = {{0}}";
sentEmailID = connection.ExecuteScalar(-1, DataExtensions.DefaultTimeoutDuration, query, html);
}
alreadySent = (sentEmailID != -1);
if (!alreadySent)
sentEmailID = LoadSentEmail(recipients, subject, html);
LoadEventSentEmail(eventRow, systemEvent, sentEmailID);
}
catch (Exception ex)
{
// Failure to load the email into the database should
// not prevent us from attempting to send the email
Log.Error(ex.Message, ex);
}
if (!alreadySent)
SendEmail(recipients, subject, html, attachments);
}
finally
{
foreach (Attachment attachment in attachments)
attachment.Dispose();
}
}
if (templateGroups.Any())
Log.Info($"All emails sent for event ID {eventID}.");
}
/// <summary>
/// Saves <see cref="Phasor"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="phasor">Information about <see cref="Phasor"/>.</param>
/// <param name="oldSourceIndex">The old source index of the phasor.</param>
/// <param name="skipMeasurementUpdate">Skips associated measurement update if this is already being handled.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string SaveAndReorder(AdoDataConnection database, Phasor phasor, int oldSourceIndex, bool skipMeasurementUpdate = false)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (phasor.SourceIndex == 0)
phasor.SourceIndex = database.ExecuteScalar<int>("SELECT MAX(SourceIndex) FROM Phasor WHERE DeviceID = {0}", phasor.DeviceID) + 1;
// Since phasors could be reordered in the source device, this test could inadvertently throw an exception when it should not - so the validation has been removed
//if (database.ExecuteScalar<int>("SELECT COUNT(*) FROM Phasor WHERE ID <> {0} AND DeviceID = {1} AND SourceIndex = {2}", phasor.ID, phasor.DeviceID, phasor.SourceIndex) > 0)
// throw new InvalidOperationException("Phasor source index must be unique per device.");
if (phasor.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Phasor (DeviceID, Label, Type, Phase, SourceIndex, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "createdBy",
"createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type, phasor.Phase, phasor.SourceIndex,
CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE Phasor SET DeviceID = {0}, Label = {1}, Type = {2}, Phase = {3}, SourceIndex = {4}, " +
"UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type,
phasor.Phase, phasor.SourceIndex, CommonFunctions.CurrentUser, database.UtcNow, phasor.ID);
}
// Get reference to the device to which phasor is being added.
Device device = Device.GetDevice(database, "WHERE ID = " + phasor.DeviceID);
// Get Phasor signal types.
ObservableCollection<SignalType> signals;
if (phasor.Type == "V")
signals = SignalType.GetVoltagePhasorSignalTypes();
else
signals = SignalType.GetCurrentPhasorSignalTypes();
// Get reference to phasor which has just been added.
Phasor addedPhasor = GetPhasor(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SourceIndex = " + phasor.SourceIndex);
foreach (SignalType signal in signals)
{
Measurement measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SignalTypeSuffix = '" + signal.Suffix + "' AND PhasorSourceIndex = " + oldSourceIndex);
if ((object)measurement == null)
{
measurement = new Measurement();
measurement.DeviceID = device.ID;
measurement.HistorianID = device.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]);
measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex;
measurement.SignalTypeID = signal.ID;
measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name;
measurement.PhasorSourceIndex = addedPhasor.SourceIndex;
measurement.Enabled = true;
Measurement.Save(database, measurement);
}
else if (!skipMeasurementUpdate || addedPhasor.SourceIndex != oldSourceIndex) // || measurement.SignalTypeID != signal.ID
{
// Update existing record when needed or when phasor source index has changed
measurement.HistorianID = device.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]);
measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex;
measurement.SignalTypeID = signal.ID;
measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name;
measurement.PhasorSourceIndex = addedPhasor.SourceIndex;
Measurement.Save(database, measurement);
}
}
return "Phasor information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
private static int GetEmailCount(DbAdapterContainer dbAdapterContainer, int eventID)
{
using (AdoDataConnection connection = new AdoDataConnection(dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
{
int timeout = dbAdapterContainer.CommandTimeout;
string sql = "SELECT COUNT(*) FROM EventSentEmail WHERE EventID = {0}";
object[] parameters = { eventID };
return connection.ExecuteScalar<int>(timeout: timeout, sqlFormat: sql, parameters: parameters);
}
}
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);
}
// Static Methods
public static Stream ConvertToChartImageStream(DbAdapterContainer dbAdapterContainer, XElement chartElement)
{
ChartGenerator chartGenerator;
Lazy<DataRow> faultSummary;
Lazy<double> systemFrequency;
DateTime inception;
DateTime clearing;
int width;
int height;
double prefaultCycles;
double postfaultCycles;
string title;
List<string> keys;
List<string> names;
DateTime startTime;
DateTime endTime;
int eventID;
int faultID;
// Read parameters from the XML data and set up defaults
eventID = Convert.ToInt32((string)chartElement.Attribute("eventID") ?? "-1");
faultID = Convert.ToInt32((string)chartElement.Attribute("faultID") ?? "-1");
prefaultCycles = Convert.ToDouble((string)chartElement.Attribute("prefaultCycles") ?? "NaN");
postfaultCycles = Convert.ToDouble((string)chartElement.Attribute("postfaultCycles") ?? "NaN");
title = (string)chartElement.Attribute("yAxisTitle");
keys = GetKeys(chartElement);
names = GetNames(chartElement);
width = Convert.ToInt32((string)chartElement.Attribute("width"));
height = Convert.ToInt32((string)chartElement.Attribute("height"));
startTime = DateTime.MinValue;
endTime = DateTime.MaxValue;
using (AdoDataConnection connection = new AdoDataConnection(dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
{
faultSummary = new Lazy<DataRow>(() => connection.RetrieveData("SELECT * FROM FaultSummary WHERE ID = {0}", faultID).Select().FirstOrDefault());
systemFrequency = new Lazy<double>(() => connection.ExecuteScalar(60.0D, "SELECT Value FROM Setting WHERE Name = 'SystemFrequency'"));
// If prefaultCycles is specified and we have a fault summary we can use,
// we can determine the start time of the chart based on fault inception
if (!double.IsNaN(prefaultCycles) && (object)faultSummary.Value != null)
{
inception = faultSummary.Value.ConvertField<DateTime>("Inception");
startTime = inception.AddSeconds(-prefaultCycles / systemFrequency.Value);
}
// If postfaultCycles is specified and we have a fault summary we can use,
// we can determine the start time of the chart based on fault clearing
if (!double.IsNaN(postfaultCycles) && (object)faultSummary.Value != null)
{
inception = faultSummary.Value.ConvertField<DateTime>("Inception");
clearing = inception.AddSeconds(faultSummary.Value.ConvertField<double>("DurationSeconds"));
endTime = clearing.AddSeconds(postfaultCycles / systemFrequency.Value);
}
// Create the chart generator to generate the chart
chartGenerator = new ChartGenerator(dbAdapterContainer, eventID);
using (Chart chart = chartGenerator.GenerateChart(title, keys, names, startTime, endTime))
{
// Set the chart size based on the specified width and height;
// this allows us to dynamically change font sizes and line
// widths before converting the chart to an image
SetChartSize(chart, width, height);
// Determine if either the minimum or maximum of the y-axis is specified explicitly
if ((object)chartElement.Attribute("yAxisMaximum") != null)
chart.ChartAreas[0].AxisY.Maximum = Convert.ToDouble((string)chartElement.Attribute("yAxisMaximum"));
if ((object)chartElement.Attribute("yAxisMinimum") != null)
chart.ChartAreas[0].AxisY.Minimum = Convert.ToDouble((string)chartElement.Attribute("yAxisMinimum"));
// If the calculation cycle is to be highlighted, determine whether the highlight should be in the range of a single index or a full cycle.
// If we have a fault summary we can use, apply the appropriate highlight based on the calculation cycle
if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "index", StringComparison.OrdinalIgnoreCase))
{
if ((object)faultSummary.Value != null)
{
int calculationCycle = faultSummary.Value.ConvertField<int>("CalculationCycle");
DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle);
double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds;
chart.ChartAreas[0].CursorX.Position = calculationPosition;
}
}
else if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "cycle", StringComparison.OrdinalIgnoreCase))
{
if ((object)faultSummary.Value != null)
{
int calculationCycle = faultSummary.Value.ConvertField<int>("CalculationCycle");
DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle);
double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds;
chart.ChartAreas[0].CursorX.SelectionStart = calculationPosition;
chart.ChartAreas[0].CursorX.SelectionEnd = calculationPosition + 1.0D / 60.0D;
}
}
// Convert the generated chart to an image
return ConvertToImageStream(chart, ChartImageFormat.Png);
}
}
}
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();
}
// Static Methods
public static Stream ConvertToChartImageStream(DbAdapterContainer dbAdapterContainer, XElement chartElement)
{
ChartGenerator chartGenerator;
Lazy <DataRow> faultSummary;
Lazy <double> systemFrequency;
DateTime inception;
DateTime clearing;
int width;
int height;
double prefaultCycles;
double postfaultCycles;
string title;
List <string> keys;
List <string> names;
DateTime startTime;
DateTime endTime;
int eventID;
int faultID;
// Read parameters from the XML data and set up defaults
eventID = Convert.ToInt32((string)chartElement.Attribute("eventID") ?? "-1");
faultID = Convert.ToInt32((string)chartElement.Attribute("faultID") ?? "-1");
prefaultCycles = Convert.ToDouble((string)chartElement.Attribute("prefaultCycles") ?? "NaN");
postfaultCycles = Convert.ToDouble((string)chartElement.Attribute("postfaultCycles") ?? "NaN");
title = (string)chartElement.Attribute("yAxisTitle");
keys = GetKeys(chartElement);
names = GetNames(chartElement);
width = Convert.ToInt32((string)chartElement.Attribute("width"));
height = Convert.ToInt32((string)chartElement.Attribute("height"));
startTime = DateTime.MinValue;
endTime = DateTime.MaxValue;
using (AdoDataConnection connection = new AdoDataConnection(dbAdapterContainer.Connection, typeof(SqlDataAdapter), false))
{
faultSummary = new Lazy <DataRow>(() => connection.RetrieveData("SELECT * FROM FaultSummary WHERE ID = {0}", faultID).Select().FirstOrDefault());
systemFrequency = new Lazy <double>(() => connection.ExecuteScalar(60.0D, "SELECT Value FROM Setting WHERE Name = 'SystemFrequency'"));
// If prefaultCycles is specified and we have a fault summary we can use,
// we can determine the start time of the chart based on fault inception
if (!double.IsNaN(prefaultCycles) && (object)faultSummary.Value != null)
{
inception = faultSummary.Value.ConvertField <DateTime>("Inception");
startTime = inception.AddSeconds(-prefaultCycles / systemFrequency.Value);
}
// If postfaultCycles is specified and we have a fault summary we can use,
// we can determine the start time of the chart based on fault clearing
if (!double.IsNaN(postfaultCycles) && (object)faultSummary.Value != null)
{
inception = faultSummary.Value.ConvertField <DateTime>("Inception");
clearing = inception.AddSeconds(faultSummary.Value.ConvertField <double>("DurationSeconds"));
endTime = clearing.AddSeconds(postfaultCycles / systemFrequency.Value);
}
// Create the chart generator to generate the chart
chartGenerator = new ChartGenerator(dbAdapterContainer, eventID);
using (Chart chart = chartGenerator.GenerateChart(title, keys, names, startTime, endTime))
{
// Set the chart size based on the specified width and height;
// this allows us to dynamically change font sizes and line
// widths before converting the chart to an image
SetChartSize(chart, width, height);
// Determine if either the minimum or maximum of the y-axis is specified explicitly
if ((object)chartElement.Attribute("yAxisMaximum") != null)
{
chart.ChartAreas[0].AxisY.Maximum = Convert.ToDouble((string)chartElement.Attribute("yAxisMaximum"));
}
if ((object)chartElement.Attribute("yAxisMinimum") != null)
{
chart.ChartAreas[0].AxisY.Minimum = Convert.ToDouble((string)chartElement.Attribute("yAxisMinimum"));
}
// If the calculation cycle is to be highlighted, determine whether the highlight should be in the range of a single index or a full cycle.
// If we have a fault summary we can use, apply the appropriate highlight based on the calculation cycle
if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "index", StringComparison.OrdinalIgnoreCase))
{
if ((object)faultSummary.Value != null)
{
int calculationCycle = faultSummary.Value.ConvertField <int>("CalculationCycle");
DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle);
double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds;
chart.ChartAreas[0].CursorX.Position = calculationPosition;
}
}
else if (string.Equals((string)chartElement.Attribute("highlightCalculation"), "cycle", StringComparison.OrdinalIgnoreCase))
{
if ((object)faultSummary.Value != null)
{
int calculationCycle = faultSummary.Value.ConvertField <int>("CalculationCycle");
DateTime calculationTime = chartGenerator.ToDateTime(calculationCycle);
double calculationPosition = chart.ChartAreas[0].AxisX.Minimum + (calculationTime - startTime).TotalSeconds;
chart.ChartAreas[0].CursorX.SelectionStart = calculationPosition;
chart.ChartAreas[0].CursorX.SelectionEnd = calculationPosition + 1.0D / 60.0D;
}
}
// Convert the generated chart to an image
return(ConvertToImageStream(chart, ChartImageFormat.Png));
}
}
}
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;
}
protected override bool PropagateDataSource(DataSet dataSource)
{
const string DeviceGroupMeasurementsTableName = "DeviceGroupMeasurements";
try
{
// Augment data source with device group measurements metadata table
DataTable activeMeasurements = dataSource.Tables["ActiveMeasurements"];
DataTable deviceGroupMeasurements = activeMeasurements.Clone();
deviceGroupMeasurements.TableName = DeviceGroupMeasurementsTableName;
// Append device group specific columns
deviceGroupMeasurements.Columns.Add(new DataColumn("DeviceGroup", typeof(string)));
deviceGroupMeasurements.Columns.Add(new DataColumn("DeviceGroupName", typeof(string)));
deviceGroupMeasurements.Columns.Add(new DataColumn("DeviceGroupID", typeof(int)));
int deviceGroupAcronymIndex = deviceGroupMeasurements.Columns["DeviceGroup"].Ordinal;
int deviceGroupNameIndex = deviceGroupMeasurements.Columns["DeviceGroupName"].Ordinal;
int deviceGroupIDIndex = deviceGroupMeasurements.Columns["DeviceGroupID"].Ordinal;
if (dataSource.Tables.Contains(DeviceGroupMeasurementsTableName))
{
dataSource.Tables.Remove(DeviceGroupMeasurementsTableName);
}
// Add device group measurements metadata table to data source
dataSource.Tables.Add(deviceGroupMeasurements);
// Populate device group measurements metadata table
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
int virtualProtocolID = s_virtualProtocolID != 0 ? s_virtualProtocolID : s_virtualProtocolID = connection.ExecuteScalar <int>("SELECT ID FROM Protocol WHERE Acronym='VirtualInput'");
TableOperations <DeviceGroup> deviceGroupTable = new TableOperations <DeviceGroup>(connection);
TableOperations <Device> deviceTable = new TableOperations <Device>(connection);
// Query all enabled device groups
foreach (DeviceGroup deviceGroup in deviceGroupTable.QueryRecordsWhere("NodeID = {0} AND ProtocolID = {1} AND AccessID = {2} AND Enabled <> 0", Model.Global.NodeID, virtualProtocolID, DeviceGroup.DefaultAccessID))
{
if (string.IsNullOrWhiteSpace(deviceGroup?.ConnectionString))
{
continue;
}
Dictionary <string, string> settings = deviceGroup.ConnectionString.ParseKeyValuePairs();
if (!settings.TryGetValue("deviceIDs", out string deviceIDs) || string.IsNullOrWhiteSpace(deviceIDs))
{
continue;
}
// Parse device ID list
HashSet <int> deviceIDSet = new HashSet <int>();
foreach (string deviceID in deviceIDs.Split(','))
{
if (int.TryParse(deviceID, out int id))
{
deviceIDSet.Add(id);
}
}
if (deviceIDSet.Count == 0)
{
continue;
}
HashSet <string> deviceAcronyms = new HashSet <string>(deviceTable.QueryRecordsWhere($"ID IN ({string.Join(",", deviceIDSet)})").Select(device => $"'{device.Acronym}'"));
if (deviceAcronyms.Count == 0)
{
continue;
}
// Get active measurements associated with device group's device acronyms
foreach (DataRow row in activeMeasurements.Select($"Device IN ({string.Join(",", deviceAcronyms)})"))
{
DataRow newRow = deviceGroupMeasurements.NewRow();
// Copy common columns from active measurements
for (int i = 0; i < activeMeasurements.Columns.Count; i++)
{
newRow[i] = row[i];
}
// Add device group specific column values
newRow[deviceGroupAcronymIndex] = deviceGroup.Acronym;
newRow[deviceGroupNameIndex] = deviceGroup.Name;
newRow[deviceGroupIDIndex] = deviceGroup.ID;
deviceGroupMeasurements.Rows.Add(newRow);
}
}
}
}
catch (Exception ex)
{
DisplayStatusMessage($"Unable to inject \"{DeviceGroupMeasurementsTableName}\" selection metadata table during configuration dataset propagation due to exception: {0}", UpdateType.Alarm, ex.Message);
LogException(ex);
}
return(base.PropagateDataSource(dataSource));
}
