internal static void LoadConnectionParameters()
{
try
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
TableOperations<IaonOutputAdapter> operations = new TableOperations<IaonOutputAdapter>(connection);
IaonOutputAdapter record = operations.QueryRecords(limit: 1, restriction: new RecordRestriction
{
FilterExpression = "TypeName = 'openHistorian.Adapters.LocalOutputAdapter'"
})
.FirstOrDefault();
if ((object)record == null)
throw new NullReferenceException("Primary openHistorian adapter instance not found.");
Dictionary<string, string> settings = record.ConnectionString.ParseKeyValuePairs();
string setting;
if (!settings.TryGetValue("port", out setting) || !int.TryParse(setting, out s_portNumber))
s_portNumber = Connection.DefaultHistorianPort;
if (!settings.TryGetValue("instanceName", out s_defaultInstanceName) || string.IsNullOrWhiteSpace(s_defaultInstanceName))
s_defaultInstanceName = record.AdapterName ?? "PPA";
}
}
catch
{
s_defaultInstanceName = "PPA";
s_portNumber = Connection.DefaultHistorianPort;
}
}
public List <D3Series> BuildDataSeries(NameValueCollection requestParameters)
{
int eventID = int.Parse(requestParameters["eventID"]);
bool displayVolt = requestParameters["displayVolt"] == null ? true: bool.Parse(requestParameters["displayVolt"]);
bool displayCur = requestParameters["displayCur"] == null ? true : bool.Parse(requestParameters["displayCur"]);
bool displayTCE = requestParameters["displayTCE"] == null ? false : bool.Parse(requestParameters["displayTCE"]);
bool breakerdigitals = requestParameters["breakerdigitals"] == null ? false : bool.Parse(requestParameters["breakerdigitals"]);
bool displayAnalogs = requestParameters["displayAnalogs"] == null ? false : bool.Parse(requestParameters["displayAnalogs"]);
int filterOrder = requestParameters["filterOrder"] == null? 0: int.Parse(requestParameters["filterOrder"]);
double Trc = requestParameters["Trc"] == null ? 0.0 : double.Parse(requestParameters["Trc"]);
int harmonic = requestParameters["harmonic"] == null ? 1 : int.Parse(requestParameters["harmonic"]);
string displayAnalytics = requestParameters["displayAnalytics"] == null ? "" : requestParameters["displayAnalytics"];
using (AdoDataConnection connection = new AdoDataConnection("dbOpenXDA"))
{
Event evt = (new TableOperations <Event>(connection)).QueryRecordWhere("ID = {0}", eventID);
Meter meter = new TableOperations <Meter>(connection).QueryRecordWhere("ID = {0}", evt.MeterID);
meter.ConnectionFactory = () => new AdoDataConnection("dbOpenXDA");
IEnumerable <D3Series> returnList = new List <D3Series>();
if (displayVolt)
{
returnList = returnList.Concat(QueryVoltageData(meter, evt));
}
if (displayCur)
{
returnList = returnList.Concat(QueryCurrentData(meter, evt));
}
if (displayTCE)
{
returnList = returnList.Concat(QueryTCEData(meter, evt));
}
if (displayAnalogs)
{
returnList = returnList.Concat(QueryAnalogData(meter, evt));
}
if (breakerdigitals)
{
returnList = returnList.Concat(QueryDigitalData(meter, evt));
}
if (displayAnalytics != "")
{
returnList = returnList.Concat(QueryAnalyticData(meter, evt, displayAnalytics, filterOrder, Trc, harmonic));
}
returnList = AlignData(returnList.ToList());
return(returnList.ToList());
}
}
public static void DeleteChildDeviceByIDCode(this Device[] devices, TableOperations <Device> deviceTable, int parentID, ushort idCode)
{
Device device = devices.FindChildDeviceByIDCode(parentID, idCode);
if (device is not null)
{
deviceTable?.DeleteRecord(device);
}
}
private List <DbDisturbance> GetDisturbances(AdoDataConnection connection, MeterDataSet meterDataSet, DataGroup dataGroup)
{
SagDataResource sagDataResource = meterDataSet.GetResource <SagDataResource>();
SwellDataResource swellDataResource = meterDataSet.GetResource <SwellDataResource>();
InterruptionDataResource interruptionDataResource = meterDataSet.GetResource <InterruptionDataResource>();
TransientDataResource transientDataResource = meterDataSet.GetResource <TransientDataResource>();
List <DbDisturbance> dbDisturbances = new List <DbDisturbance>();
List <Disturbance> disturbances;
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
if (dataGroup.Classification == DataClassification.Trend)
{
return(dbDisturbances);
}
if (dataGroup.Classification == DataClassification.Unknown)
{
return(dbDisturbances);
}
if (sagDataResource.Sags.TryGetValue(dataGroup, out disturbances))
{
foreach (Disturbance sag in disturbances)
{
dbDisturbances.Add(GetDisturbanceRow(connection, sag));
}
}
if (swellDataResource.Swells.TryGetValue(dataGroup, out disturbances))
{
foreach (Disturbance swell in disturbances)
{
dbDisturbances.Add(GetDisturbanceRow(connection, swell));
}
}
if (interruptionDataResource.Interruptions.TryGetValue(dataGroup, out disturbances))
{
foreach (Disturbance interruption in disturbances)
{
dbDisturbances.Add(GetDisturbanceRow(connection, interruption));
}
}
if (transientDataResource.Transients.TryGetValue(dataGroup, out disturbances))
{
foreach (Disturbance transient in disturbances)
{
dbDisturbances.Add(GetDisturbanceRow(connection, transient));
}
}
return(dbDisturbances);
}
public int QueryIncidentEventCycleDataViewCount(string date, string name, string levels, string limits, string timeContext, string filterString)
{
TableOperations <IncidentEventCycleDataView> table = DataContext.Table <IncidentEventCycleDataView>();
RecordRestriction filterRestriction = table.GetSearchRestriction(filterString);
DateTime startDate = DateTime.ParseExact(date, "yyyyMMddHH", CultureInfo.InvariantCulture);
DateTime endDate = (DateTime)typeof(DateTime).GetMethod("Add" + timeContext).Invoke(startDate, new object[] { 1 });
return(table.QueryRecordCount(filterRestriction + new RecordRestriction("StartTime BETWEEN {0} AND {1}", startDate, endDate) + new RecordRestriction(levels + " = {0}", name)));
}
private void BulkCalculationState_Load(object sender, EventArgs e)
{
try
{
// Load current settings registering a symbolic reference to this form instance for use by default value expressions
m_settings = new Settings(new Dictionary <string, object> {
{ "Form", this }
}.RegisterSymbols());
// Restore last window size/location
this.RestoreLayout();
string configFile = GetConfigurationFileName();
if (!File.Exists(configFile))
{
throw new FileNotFoundException("Host service config file was not found.");
}
XDocument serviceConfig = XDocument.Load(configFile);
string connectionString = serviceConfig
.Descendants("systemSettings")
.SelectMany(systemSettings => systemSettings.Elements("add"))
.Where(element => "ConnectionString".Equals((string)element.Attribute("name"), StringComparison.OrdinalIgnoreCase))
.Select(element => (string)element.Attribute("value"))
.FirstOrDefault();
string dataProviderString = serviceConfig
.Descendants("systemSettings")
.SelectMany(systemSettings => systemSettings.Elements("add"))
.Where(element => "DataProviderString".Equals((string)element.Attribute("name"), StringComparison.OrdinalIgnoreCase))
.Select(element => (string)element.Attribute("value"))
.FirstOrDefault();
m_connection = new AdoDataConnection(connectionString, dataProviderString);
m_actionAdapterTable = new TableOperations <CustomActionAdapter>(m_connection);
RefreshActionAdapters();
m_updateTotals = new ShortSynchronizedOperation(UpdateTotals);
SyncCheckedListBox();
ConnectConsole();
m_formLoaded = true;
}
catch (Exception ex)
{
m_log.Publish(MessageLevel.Error, "FormLoad", "Failed while loading settings", exception: ex);
#if DEBUG
throw;
#else
MessageBox.Show(this, $"Failed during initialization: {ex.Message}", "Initialization Failure", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
#endif
}
}
public IHttpActionResult GetValueListForGroup(string groupName)
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
string tableName = new TableOperations <ValueListGroup>(connection).TableName;
IEnumerable <ValueList> records = new TableOperations <ValueList>(connection).QueryRecordsWhere($"GroupID = ( SELECT ID FROM {tableName} WHERE Name = {{0}})", groupName);
return(Ok(records));
}
}
public Task <DataTable> GetDataFiles([FromBody] GetDataFilesPostData postData, CancellationToken cancellationToken)
{
return(Task.Run(() =>
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
const string QueryFormat =
"WITH cte AS " +
"( " +
" SELECT * " +
" FROM DataFile " +
" WHERE {0} " +
") " +
"SELECT DataFile.* " +
"FROM " +
"( " +
" SELECT " +
" ROW_NUMBER() OVER(ORDER BY {2}) AS RowNumber, " +
" DataFile.* " +
" FROM (SELECT DISTINCT FileGroupID ID FROM cte) FileGroup CROSS APPLY " +
" ( " +
" SELECT TOP 1 * " +
" FROM cte DataFile " +
" WHERE DataFile.FileGroupID = FileGroup.ID " +
" ORDER BY FileSize DESC, FilePath " +
" ) DataFile " +
") DataFile " +
"WHERE {1} " +
"ORDER BY RowNumber";
TableOperations <DataFile> dataFileTable = new TableOperations <DataFile>(connection);
RecordRestriction searchRestriction = dataFileTable.GetSearchRestriction(postData.filterString)
?? new RecordRestriction("1=1");
string searchClause = searchRestriction.FilterExpression;
int paramIndex = searchRestriction.Parameters.Length;
string pageClause = $"RowNumber BETWEEN {{{paramIndex}}} AND {{{paramIndex + 1}}}";
string sortOrder = postData.ascending ? "ASC" : "DESC";
string orderByClause = $"{postData.sortField} {sortOrder}";
string query = string.Format(QueryFormat, searchClause, pageClause, orderByClause);
int pageStart = (postData.page - 1) * postData.pageSize + 1;
int pageEnd = pageStart + postData.pageSize - 1;
object[] parameters = searchRestriction.Parameters
.Concat(new object[] { pageStart, pageEnd })
.ToArray();
return connection
.RetrieveData(query, parameters);
}
}, cancellationToken));
}
public OpenMICController()
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
TableOperations <Setting> table = new TableOperations <Setting>(connection);
InstanceUrl = table.QueryRecordWhere("Name = 'OpenMIC.Url'")?.Value ?? "";
Credential = table.QueryRecordWhere("Name = 'OpenMIC.Url'")?.Value ?? "";
Password = table.QueryRecordWhere("Name = 'OpenMIC.Url'")?.Value ?? "";
}
}
public int QueryDeviceCount(Guid nodeID, string filterText)
{
TableOperations <Device> deviceTable = DataContext.Table <Device>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0}", nodeID) +
deviceTable.GetSearchRestriction(filterText);
return(deviceTable.QueryRecordCount(restriction));
}
public IEnumerable <ReportMeasurements> QueryReportMeasurments(string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <ReportMeasurements> tableOperations = m_reportOperations.Table();
if (tableOperations == null)
{
return(new List <ReportMeasurements>());
}
return(tableOperations.QueryRecords(sortField, ascending, page, pageSize, filterText));
}
public IEnumerable <Device> QueryDevices(Guid nodeID, string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <Device> deviceTable = DataContext.Table <Device>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0}", nodeID) +
deviceTable.GetSearchRestriction(filterText);
return(deviceTable.QueryRecords(sortField, ascending, page, pageSize, restriction));
}
public int QuerySNRMeasurmentCount(string filterText)
{
TableOperations <ReportMeasurements> tableOperations = m_reportOperations.Table();
if (tableOperations == null)
{
return(0);
}
return(tableOperations.QueryRecordCount(filterText));
}
public int QueryDeviceGroupCount(Guid nodeID, string filterText)
{
TableOperations <DeviceGroup> deviceGroupTable = DataContext.Table <DeviceGroup>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0} AND ProtocolID = {1} AND AccessID = {2}", nodeID, VirtualProtocolID, DeviceGroup.DefaultAccessID) +
deviceGroupTable.GetSearchRestriction(filterText);
return(deviceGroupTable.QueryRecordCount(restriction));
}
public void Fire(IWebDriver webDriver, string xPath, string[] inputValues)
{
IWebElement rowToBeClicked = TableOperations.GetMatchingRow(webDriver, xPath, inputValues);
if (rowToBeClicked == null)
{
throw new NoSuchElementException("Row with given values not found.");
}
rowToBeClicked.Click();
}
public int QueryPhasorCount(int deviceID, string filterText)
{
TableOperations <PhasorDetail> phasorDetailTable = DataContext.Table <PhasorDetail>();
RecordRestriction restriction = (deviceID > 0 ?
new RecordRestriction("DeviceID = {0}", deviceID) : null) +
phasorDetailTable.GetSearchRestriction(filterText);
return(phasorDetailTable.QueryRecordCount(restriction));
}
public IEnumerable <PhasorDetail> QueryPhasors(int deviceID, string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <PhasorDetail> phasorDetailTable = DataContext.Table <PhasorDetail>();
RecordRestriction restriction = (deviceID > 0 ?
new RecordRestriction("DeviceID = {0}", deviceID) : null) +
phasorDetailTable.GetSearchRestriction(filterText);
return(DataContext.Table <PhasorDetail>().QueryRecords(sortField, ascending, page, pageSize, restriction));
}
public IEnumerable <DeviceGroup> QueryDeviceGroups(Guid nodeID, string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <DeviceGroup> deviceGroupTable = DataContext.Table <DeviceGroup>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0} AND ProtocolID = {1} AND AccessID = {2}", nodeID, VirtualProtocolID, DeviceGroup.DefaultAccessID) +
deviceGroupTable.GetSearchRestriction(filterText);
return(deviceGroupTable.QueryRecords(sortField, ascending, page, pageSize, restriction));
}
public override void Execute(MeterDataSet meterDataSet)
{
LightningDataResource lightningDataResource = meterDataSet.GetResource <LightningDataResource>();
if (lightningDataResource.LightningStrikeLookup.Count == 0)
{
return;
}
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
{
foreach (var kvp in lightningDataResource.LightningStrikeLookup)
{
DataGroup dataGroup = kvp.Key;
List <ILightningStrike> lightningStrikes = kvp.Value;
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
Event evt = eventTable.GetEvent(meterDataSet.FileGroup, dataGroup);
if (evt == null)
{
continue;
}
foreach (ILightningStrike lightningStrike in lightningStrikes)
{
const string Query =
"INSERT INTO LightningStrike " +
"( " +
" EventID, " +
" Service, " +
" UTCTime, " +
" DisplayTime, " +
" Amplitude, " +
" Latitude, " +
" Longitude " +
") " +
"VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6})";
object[] parameters =
{
evt.ID,
lightningStrike.Service,
ToDateTime2(connection, lightningStrike.UTCTime),
lightningStrike.DisplayTime,
lightningStrike.Amplitude,
lightningStrike.Latitude,
lightningStrike.Longitude
};
connection.ExecuteNonQuery(Query, parameters);
}
}
}
}
private void Load(AdoDataConnection database)
{
TableOperations <DbIncident> incidentTable = new TableOperations <DbIncident>(database);
foreach (Incident incident in m_incidents)
{
if (incident.ExistingIncidents.Count == 0 || incident.ExistingIncidents.Count > 1)
{
incidentTable.AddNewRecord(
new DbIncident()
{
MeterID = m_meterID,
StartTime = incident.StartTime,
EndTime = incident.EndTime
}
);
}
}
List <Incident> expand = m_incidents
.Where(incident => incident.ExistingIncidents.Count == 1)
.ToList();
List <Incident> cleanup = m_incidents
.Where(incident => incident.ExistingIncidents.Count > 1)
.ToList();
if (expand.Count == 0 && cleanup.Count == 0)
{
return;
}
const string DateTimeFormat = "yyyy-MM-dd HH:mm:ss.fffffff";
foreach (Incident incident in expand)
{
database.ExecuteNonQuery("UPDATE Incident SET StartTime = {0}, EndTime = {1} WHERE ID = {2}", incident.StartTime.ToString(DateTimeFormat), incident.EndTime.ToString(DateTimeFormat), int.Parse(incident.ExistingIncidents[0]["ID"].ToString()));
}
foreach (Incident incident in cleanup)
{
string incidentIDs = string.Join(",", incident.ExistingIncidents.Select(inc => inc["ID"].ToString()));
database.ExecuteNonQuery($"UPDATE Event SET IncidentID = (SELECT ID FROM Incident WHERE StartTime = {{0}} AND EndTime = {{1}}) WHERE IncidentID IN ({incidentIDs})", incident.StartTime.ToString(DateTimeFormat), incident.EndTime.ToString(DateTimeFormat));
}
if (cleanup.Count > 0)
{
string allIncidentIDs = string.Join(",", cleanup
.SelectMany(incident => incident.ExistingIncidents)
.Select(incident => incident["ID"].ToString()));
database.ExecuteNonQuery($"DELETE FROM IncidentAttribute WHERE IncidentID IN ({allIncidentIDs})");
database.ExecuteNonQuery($"DELETE FROM Incident WHERE ID IN ({allIncidentIDs})");
}
}
public virtual IHttpActionResult Delete(T record)
{
try
{
if (DeleteRoles == string.Empty || User.IsInRole(DeleteRoles))
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
TableNameAttribute tableNameAttribute;
string tableName;
if (typeof(T).TryGetAttribute(out tableNameAttribute))
{
tableName = tableNameAttribute.TableName;
}
else
{
tableName = typeof(T).Name;
}
PropertyInfo idProp = typeof(T).GetProperty(PrimaryKeyField);
if (idProp.PropertyType == typeof(int))
{
int id = (int)idProp.GetValue(record);
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete '{tableName}', '{PrimaryKeyField} = {id}'");
return(Ok(result));
}
else if (idProp.PropertyType == typeof(Guid))
{
Guid id = (Guid)idProp.GetValue(record);
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete '{tableName}', '{PrimaryKeyField} = ''{id}'''");
return(Ok(result));
}
else if (idProp.PropertyType == typeof(string))
{
string id = (string)idProp.GetValue(record);
int result = connection.ExecuteNonQuery($"EXEC UniversalCascadeDelete '{tableName}', '{PrimaryKeyField} = ''{id}'''");
return(Ok(result));
}
else
{
int result = new TableOperations <T>(connection).DeleteRecord(record);
return(Ok(result));
}
}
}
else
{
return(Unauthorized());
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public IEnumerable <Channel> GetChannel(AdoDataConnection connection)
{
if ((object)connection == null)
{
return(null);
}
TableOperations <Channel> channelTable = new TableOperations <Channel>(connection);
return(channelTable.QueryRecordsWhere("LineID = {0}", ID));
}
public IEnumerable <MeterLine> GetMeterLines(AdoDataConnection connection)
{
if ((object)connection == null)
{
return(null);
}
TableOperations <MeterLine> meterLineTable = new TableOperations <MeterLine>(connection);
return(meterLineTable.QueryRecordsWhere("LineID = {0}", ID));
}
public IEnumerable <LineSegmentConnections> GetConnectedSegments(AdoDataConnection connection)
{
if ((object)connection == null)
{
return(null);
}
TableOperations <LineSegmentConnections> connectionTable = new TableOperations <LineSegmentConnections>(connection);
return(connectionTable.QueryRecordsWhere("ParentSegment = {0} OR ChildSegment = {1}", ID, ID));
}
public Line GetLine(AdoDataConnection connection)
{
if ((object)connection == null)
{
return(null);
}
TableOperations <Line> lineTable = new TableOperations <Line>(connection);
return(lineTable.QueryRecordWhere("ID = {0}", LineID));
}
public Meter GetMeter(AdoDataConnection connection)
{
if ((object)connection == null)
{
return(null);
}
TableOperations <Meter> meterTable = new TableOperations <Meter>(connection);
return(meterTable.QueryRecordWhere("ID = {0}", MeterID));
}
public IEnumerable <Device> QueryEnabledDevices(Guid nodeID, int limit, string filterText)
{
TableOperations <Device> deviceTable = DataContext.Table <Device>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0} AND NOT (ProtocolID = {1} AND AccessID = {2})", nodeID, VirtualProtocolID, DeviceGroup.DefaultAccessID) +
new RecordRestriction("Enabled <> 0") +
deviceTable.GetSearchRestriction(filterText);
return(deviceTable.QueryRecords("Acronym", restriction, limit));
}
public Asset GetAsset(AdoDataConnection connection)
{
if ((object)connection == null)
{
return(null);
}
TableOperations <Asset> assetTable = new TableOperations <Asset>(connection);
return(assetTable.QueryRecordWhere("ID = {0}", AssetID));
}
public IEnumerable <AssetConnection> GetConnection(AdoDataConnection connection)
{
if ((object)connection == null)
{
return(null);
}
TableOperations <AssetConnection> channelTable = new TableOperations <AssetConnection>(connection);
return(channelTable.QueryRecordsWhere("ParentID = {0} OR ChildID = {1}", ID, ID));
}
public IEnumerable <Device> QueryEnabledDevices(Guid nodeID, int limit, string filterText)
{
TableOperations <Device> deviceTable = DataContext.Table <Device>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0}", nodeID) +
new RecordRestriction("Enabled <> 0") +
deviceTable.GetSearchRestriction(filterText);
return(deviceTable.QueryRecords("Acronym", restriction, limit));
}
public IEnumerable <AssetLocation> GetAssetLocations(AdoDataConnection connection)
{
if ((object)connection == null)
{
return(null);
}
TableOperations <AssetLocation> assetLocationTable = new TableOperations <AssetLocation>(connection);
return(assetLocationTable.QueryRecordsWhere("AssetID = {0}", ID));
}
