public static MeasurementCharacteristic GetOrAdd(this TableOperations <MeasurementCharacteristic> measurementCharacteristicTable, string name, string description = null)
{
MeasurementCharacteristic measurementCharacteristic = measurementCharacteristicTable.QueryRecordWhere("Name = {0}", name);
if ((object)measurementCharacteristic == null)
{
measurementCharacteristic = new MeasurementCharacteristic();
measurementCharacteristic.Name = name;
measurementCharacteristic.Description = description ?? name;
try
{
measurementCharacteristicTable.AddNewRecord(measurementCharacteristic);
}
catch (Exception ex)
{
// Ignore errors regarding unique key constraints
// which can occur as a result of a race condition
bool isUniqueViolation = ExceptionHandler.IsUniqueViolation(ex);
if (!isUniqueViolation)
{
throw;
}
return(measurementCharacteristicTable.QueryRecordWhere("Name = {0}", name));
}
measurementCharacteristic.ID = measurementCharacteristicTable.Connection.ExecuteScalar <int>("SELECT @@IDENTITY");
}
return(measurementCharacteristic);
}
private void CreateOrUpdate(ImportedMeasurement measurement)
{
TransactionScopeOption transactionScopeOption = TransactionScopeOption.Required;
TransactionOptions transactionOptions = new TransactionOptions()
{
IsolationLevel = IsolationLevel.ReadCommitted,
Timeout = TransactionManager.MaximumTimeout
};
measurement.NodeID = NodeID;
measurement.Source = Source;
using (TransactionScope transactionScope = new TransactionScope(transactionScopeOption, transactionOptions))
using (AdoDataConnection connection = CreateDbConnection())
{
TableOperations <ImportedMeasurement> importedMeasurementTable = new TableOperations <ImportedMeasurement>(connection);
RecordRestriction recordRestriction =
new RecordRestriction("NodeID = {0}", measurement.NodeID) &
new RecordRestriction("Source = {0}", measurement.Source) &
new RecordRestriction("PointID = {0}", measurement.PointID);
int measurementCount = importedMeasurementTable.UpdateRecord(measurement, recordRestriction);
if (measurementCount == 0)
{
importedMeasurementTable.AddNewRecord(measurement);
}
transactionScope.Complete();
}
}
public IHttpActionResult AddAssets(int assetGroupID, [FromBody] IEnumerable <int> assets)
{
try
{
if (PostRoles == string.Empty || User.IsInRole(PostRoles))
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
TableOperations <AssetAssetGroup> assetassetGroupTbl = new TableOperations <AssetAssetGroup>(connection);
foreach (int assetID in assets)
{
int n = connection.ExecuteScalar <int>("Select Count(ID) FROM AssetAssetGroup WHERE AssetID = {0} AND AssetGroupID = {1}", assetID, assetGroupID);
if (n == 0)
{
assetassetGroupTbl.AddNewRecord(new AssetAssetGroup()
{
AssetGroupID = assetGroupID, AssetID = assetID
});
}
}
return(Ok(1));
}
}
else
{
return(Unauthorized());
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public override void Execute(MeterDataSet meterDataSet)
{
CycleDataResource cycleDataResource = meterDataSet.GetResource <CycleDataResource>();
for (int i = 0; i < cycleDataResource.DataGroups.Count; i++)
{
EventKey eventKey = CreateEventKey(meterDataSet.FileGroup, cycleDataResource.DataGroups[i]);
VICycleDataGroup viCycleDataGroup = cycleDataResource.VICycleDataGroups[i];
int samplesPerCycle = (int)Math.Round(cycleDataResource.DataGroups[i].SamplesPerSecond / m_systemFrequency);
Process(eventKey, viCycleDataGroup, samplesPerCycle);
}
m_meterDataSet = meterDataSet;
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
{
// Query database for events and store them in a lookup table by event key
TableOperations <Event> eventAdapter = new TableOperations <Event>(connection);
TableOperations <CycleData> cycleDataAdapter = new TableOperations <CycleData>(connection);
IEnumerable <Event> eventTable = eventAdapter.QueryRecordsWhere("FileGroupID = {0}", m_meterDataSet.FileGroup.ID);
Dictionary <EventKey, Event> eventLookup = eventTable.ToDictionary(CreateEventKey);
foreach (Tuple <EventKey, CycleData> tuple in m_cycleDataList)
{
Event eventRow;
if (eventLookup.TryGetValue(tuple.Item1, out eventRow))
{
tuple.Item2.EventID = eventRow.ID;
cycleDataAdapter.AddNewRecord(tuple.Item2);
}
}
}
}
private Device CreateDefaultDevice(TableOperations <Device> table)
{
int protocolId;
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
TableOperations <Protocol> protocolTable = new TableOperations <Protocol>(connection);
protocolId = protocolTable.QueryRecordWhere("Acronym = {0}", "VirtualInput").ID;
}
Device result = new Device
{
Enabled = true,
ProtocolID = protocolId,
Name = "Unbalanced Results",
Acronym = ResultDeviceName,
CreatedOn = DateTime.UtcNow,
UpdatedOn = DateTime.UtcNow,
CreatedBy = UserInfo.CurrentUserID,
UpdatedBy = UserInfo.CurrentUserID,
UniqueID = Guid.NewGuid(),
NodeID = m_nodeID
};
table.AddNewRecord(result);
result = table.QueryRecordWhere("Acronym = {0}", ResultDeviceName);
return(result);
}
private static CustomActionAdapter GetOrAddActionAdapter(TableOperations <CustomActionAdapter> actionAdapterTable, string tielineID, out bool newAdd)
{
string adapterName = $"IMPEDANCE_{tielineID}_CALC";
CustomActionAdapter actionAdapter = actionAdapterTable.QueryRecordWhere("NodeID = {0} AND AdapterName = {1}", nodeID, adapterName);
if ((object)actionAdapter == null)
{
actionAdapter = actionAdapterTable.NewRecord();
actionAdapter.NodeID = nodeID;
actionAdapter.AdapterName = adapterName;
actionAdapter.AssemblyName = AssemblyName;
actionAdapter.TypeName = TypeName;
actionAdapterTable.AddNewRecord(actionAdapter);
// Re-query newly added record to get auto-increment ID
actionAdapter = actionAdapterTable.QueryRecordWhere("NodeID = {0} AND AdapterName = {1}", nodeID, adapterName);
newAdd = true;
if ((object)actionAdapter == null)
{
throw new InvalidOperationException($"Failed to lookup CustomActionAdapter record with AdapterName of \"{adapterName}\".");
}
}
else
{
newAdd = false;
}
return(actionAdapter);
}
private static void AddOutputStreamMeasurement(TableOperations <Measurement> measurementTable, TableOperations <OutputStreamMeasurement> outputStreamMeasurementTable, int outputStreamID, string sourceSignalReference, string destSignalReference)
{
Measurement measurement = measurementTable.QueryRecordWhere("SignalReference = {0}", $"{sourceSignalReference}");
if (measurement == null)
{
Console.WriteLine($"Failed to lookup Measurement record with SignalReference of \"{sourceSignalReference}\"");
}
else
{
OutputStreamMeasurement outputStreamMeasurement = outputStreamMeasurementTable.QueryRecordWhere("AdapterID = {0} AND SignalReference = {1}", outputStreamID, destSignalReference);
if (outputStreamMeasurement == null)
{
outputStreamMeasurement = new OutputStreamMeasurement
{
NodeID = nodeID,
AdapterID = outputStreamID,
HistorianID = measurement.HistorianID,
PointID = measurement.PointID,
SignalReference = destSignalReference,
CreatedOn = DateTime.UtcNow,
CreatedBy = currentUserID,
UpdatedOn = DateTime.UtcNow,
UpdatedBy = currentUserID
};
outputStreamMeasurementTable.AddNewRecord(outputStreamMeasurement);
}
}
}
private void LoadEvents(AdoDataConnection connection, List <Event> events)
{
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
TableOperations <EventData> eventDataTable = new TableOperations <EventData>(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;
}
}
public static EventType GetOrAdd(this TableOperations <EventType> eventTypeTable, string name, string description = null)
{
EventType eventType = eventTypeTable.QueryRecordWhere("Name = {0}", name);
if ((object)eventType == null)
{
eventType = new EventType();
eventType.Name = name;
eventType.Description = description ?? name;
try
{
eventTypeTable.AddNewRecord(eventType);
}
catch (Exception ex)
{
// Ignore errors regarding unique key constraints
// which can occur as a result of a race condition
bool isUniqueViolation = ExceptionHandler.IsUniqueViolation(ex);
if (!isUniqueViolation)
{
throw;
}
return(eventTypeTable.QueryRecordWhere("Name = {0}", name));
}
eventType.ID = eventTypeTable.Connection.ExecuteScalar <int>("SELECT @@IDENTITY");
}
return(eventType);
}
public static FileGroupField GetOrAdd(this TableOperations <FileGroupField> fileGroupFieldTable, string name, string description = null)
{
FileGroupField fileGroupField = fileGroupFieldTable.QueryRecordWhere("Name = {0}", name);
if ((object)fileGroupField == null)
{
fileGroupField = new FileGroupField();
fileGroupField.Name = name;
fileGroupField.Description = description;
try
{
fileGroupFieldTable.AddNewRecord(fileGroupField);
}
catch (Exception ex)
{
// Ignore errors regarding unique key constraints
// which can occur as a result of a race condition
bool isUniqueViolation = ExceptionHandler.IsUniqueViolation(ex);
if (!isUniqueViolation)
{
throw;
}
return(fileGroupFieldTable.QueryRecordWhere("Name = {0}", name));
}
fileGroupField.ID = fileGroupFieldTable.Connection.ExecuteScalar <int>("SELECT @@IDENTITY");
}
return(fileGroupField);
}
public IHttpActionResult SubmittRemedialActionPlan([FromBody] JObject postData)
{
try
{
Dictionary <string, string> posted = postData.ToObject <Dictionary <string, string> >();
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
ComplianceState rap = new TableOperations <ComplianceState>(connection).QueryRecordWhere("Description = 'RAP Submitted'");
string query = "MeterID = {0} AND [Status] IN (SELECT ID FROM ComplianceState WHERE Priority > 3 AND Priority < 6)";
TableOperations <ComplianceAction> actionTable = new TableOperations <ComplianceAction>(connection);
foreach (ComplianceRecordView record in new TableOperations <ComplianceRecordView>(connection).QueryRecordsWhere(query, int.Parse(posted["meterId"])))
{
actionTable.AddNewRecord(new ComplianceAction()
{
Note = posted["Note"],
RecordId = record.ID,
Timestamp = DateTime.UtcNow,
UserAccount = User.Identity.Name,
StateId = rap.ID,
});
}
return(Ok());
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
private void LoadRestrikeAnalysis(MeterDataSet meterDataSet)
{
BreakerDataResource breakerDataResource = meterDataSet.GetResource <BreakerDataResource>();
foreach (var kvp in breakerDataResource.RestrikeLookup)
{
DataGroup dataGroup = kvp.Key;
List <BreakerDataResource.Restrike> restrikes = kvp.Value;
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
{
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
TableOperations <Phase> phaseTable = new TableOperations <Phase>(connection);
TableOperations <BreakerRestrike> breakerRestrikeTable = new TableOperations <BreakerRestrike>(connection);
Event evt = eventTable.GetEvent(meterDataSet.FileGroup, dataGroup);
foreach (BreakerDataResource.Restrike restrike in restrikes)
{
Phase phase = phaseTable.GetOrAdd(restrike.Phase.ToString());
BreakerRestrike breakerRestrike = ProcessRestrike(restrike, phase, evt, new VIDataGroup(dataGroup));
breakerRestrikeTable.AddNewRecord(breakerRestrike);
}
}
}
}
public override void Execute(MeterDataSet meterDataSet)
{
BreakerDataResource breakerDataResource = meterDataSet.GetResource <BreakerDataResource>();
foreach (var kvp in breakerDataResource.RestrikeLookup)
{
DataGroup dataGroup = kvp.Key;
List <BreakerDataResource.Restrike> restrikes = kvp.Value;
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
{
TableOperations <Event> eventTable = new TableOperations <Event>(connection);
TableOperations <Phase> phaseTable = new TableOperations <Phase>(connection);
TableOperations <BreakerRestrike> breakerRestrikeTable = new TableOperations <BreakerRestrike>(connection);
Event evt = eventTable.GetEvent(meterDataSet.FileGroup, dataGroup);
foreach (BreakerDataResource.Restrike restrike in restrikes)
{
Phase phase = phaseTable.GetOrAdd(restrike.Phase.ToString());
BreakerRestrike breakerRestrike = new BreakerRestrike();
breakerRestrike.EventID = evt.ID;
breakerRestrike.PhaseID = phase.ID;
breakerRestrike.Sample = restrike.Sample;
breakerRestrike.Timestamp = restrike.Timestamp;
breakerRestrikeTable.AddNewRecord(breakerRestrike);
}
}
}
LoadGTCRestrikeData(meterDataSet);
}
public override void Execute(MeterDataSet meterDataSet)
{
CycleDataResource cycleDataResource = meterDataSet.GetResource <CycleDataResource>();
SagDataResource sagDataResource = meterDataSet.GetResource <SagDataResource>();
SwellDataResource swellDataResource = meterDataSet.GetResource <SwellDataResource>();
InterruptionDataResource interruptionDataResource = meterDataSet.GetResource <InterruptionDataResource>();
using (AdoDataConnection connection = meterDataSet.CreateDbConnection())
{
TableOperations <Disturbance> disturbanceTable = new TableOperations <Disturbance>(connection);
TableOperations <DisturbanceSeverity> disturbanceSeverityTable = new TableOperations <DisturbanceSeverity>(connection);
TableOperations <VoltageEnvelope> voltageEnvelopeTable = new TableOperations <VoltageEnvelope>(connection);
TableOperations <VoltageCurve> voltageCurveTable = new TableOperations <VoltageCurve>(connection);
TableOperations <VoltageCurvePoint> voltageCurvePointTable = new TableOperations <VoltageCurvePoint>(connection);
List <Disturbance> disturbances = disturbanceTable
.QueryRecordsWhere("EventID IN (SELECT ID FROM Event WHERE FileGroupID = {0})", meterDataSet.FileGroup.ID)
.ToList();
foreach (VoltageEnvelope voltageEnvelope in voltageEnvelopeTable.QueryRecords().ToList())
{
List <VoltageCurve> voltageCurves = voltageCurveTable
.QueryRecordsWhere("ID IN (SELECT VoltageCurveID FROM VoltageEnvelopeCurve WHERE VoltageEnvelopeID = {0})", voltageEnvelope.ID)
.ToList();
foreach (VoltageCurve voltageCurve in voltageCurves)
{
voltageCurve.VoltageCurvePoints = voltageCurvePointTable
.QueryRecordsWhere("VoltageCurveID = {0}", voltageCurve.ID)
.ToList();
}
foreach (Disturbance disturbance in disturbances)
{
int maxSeverityCode = voltageCurves
.Select(voltageCurve => voltageCurve.GetMagnitude(disturbance.DurationSeconds))
.Select(curveMagnitude => (int)((1.0D - disturbance.PerUnitMagnitude) / (1.0D - curveMagnitude)))
.DefaultIfEmpty(0)
.Max();
if (maxSeverityCode < 0)
{
maxSeverityCode = 0;
}
else if (maxSeverityCode > 5)
{
maxSeverityCode = 5;
}
disturbanceSeverityTable.AddNewRecord(new DisturbanceSeverity()
{
VoltageEnvelopeID = voltageEnvelope.ID,
DisturbanceID = disturbance.ID,
SeverityCode = maxSeverityCode
});
}
}
}
}
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})");
}
}
private void UpdateUserAccountAssetGroup(ConfirmableUserAccount userAccount, UpdateSettingModel formData)
{
using (DataContext dataContext = new DataContext("dbOpenXDA"))
{
// update link to asset group
TableOperations <UserAccountAssetGroup> userAccountAssetGroupTable = dataContext.Table <UserAccountAssetGroup>();
IEnumerable <UserAccountAssetGroup> userAccountAssetGroups = userAccountAssetGroupTable.QueryRecordsWhere("UserAccountID = {0}", userAccount.ID);
IEnumerable <int> assetGroups = userAccountAssetGroups.Select(x => x.AssetGroupID);
// formData will come back as null instead of empty array ....
if (formData.region == null)
{
formData.region = new List <int>();
}
// First pass. Add Link in database if the link does not exist.
foreach (int id in formData.region)
{
if (!assetGroups.Contains(id))
{
UserAccountAssetGroup userAccountAssetGroup = new UserAccountAssetGroup();
userAccountAssetGroup.UserAccountID = userAccount.ID;
userAccountAssetGroup.AssetGroupID = id;
userAccountAssetGroup.Dashboard = true;
userAccountAssetGroup.Email = true;
userAccountAssetGroupTable.AddNewRecord(userAccountAssetGroup);
}
else
{
UserAccountAssetGroup userAccountAssetGroup = userAccountAssetGroups.Where(x => x.AssetGroupID == id).First();
if (!userAccountAssetGroup.Dashboard || !userAccountAssetGroup.Email)
{
userAccountAssetGroup.Dashboard = true;
userAccountAssetGroup.Email = true;
userAccountAssetGroupTable.UpdateRecord(userAccountAssetGroup);
}
}
}
userAccountAssetGroups = userAccountAssetGroupTable.QueryRecordsWhere("UserAccountID = {0}", userAccount.ID);
// Second pass. Remove Link if the link does not exist in data from form.
foreach (UserAccountAssetGroup link in userAccountAssetGroups)
{
if (!formData.region.Contains(link.AssetGroupID))
{
userAccountAssetGroupTable.DeleteRecord(link);
}
}
}
}
private List <AlarmRangeLimit> InitializeRangeLimitTable(AdoDataConnection connection, Channel channel)
{
// Fill the range limit table with range limits for the given channel
TableOperations <AlarmRangeLimit> alarmRangeLimitTable = new TableOperations <AlarmRangeLimit>(connection);
List <AlarmRangeLimit> alarmRangeLimits = alarmRangeLimitTable
.QueryRecordsWhere("ChannelID = {0}", channel.ID)
.ToList();
// If limits exist for the given channel,
// range limit table has been successfully initialized
if (alarmRangeLimits.Count != 0)
{
return(alarmRangeLimits);
}
// Get the default range limits for the measurement type and characteristic of this channel
TableOperations <DefaultAlarmRangeLimit> defaultAlarmRangeLimitTable = new TableOperations <DefaultAlarmRangeLimit>(connection);
int measurementTypeID = channel.MeasurementTypeID;
int measurementCharacteristicID = channel.MeasurementCharacteristicID;
List <DefaultAlarmRangeLimit> defaultAlarmRangeLimits = defaultAlarmRangeLimitTable
.QueryRecordsWhere("MeasurementTypeID = {0} AND MeasurementCharacteristicID = {1}", measurementTypeID, measurementCharacteristicID)
.ToList();
// If there are no default limits for the channel,
// then the range limit table has been successfully initialized
if (defaultAlarmRangeLimits.Count == 0)
{
return(alarmRangeLimits);
}
foreach (DefaultAlarmRangeLimit defaultAlarmRangeLimit in defaultAlarmRangeLimits)
{
AlarmRangeLimit alarmRangeLimit = new AlarmRangeLimit()
{
ChannelID = channel.ID,
AlarmTypeID = defaultAlarmRangeLimit.AlarmTypeID,
Severity = defaultAlarmRangeLimit.Severity,
High = defaultAlarmRangeLimit.High,
Low = defaultAlarmRangeLimit.Low,
RangeInclusive = defaultAlarmRangeLimit.RangeInclusive,
PerUnit = defaultAlarmRangeLimit.PerUnit,
IsDefault = true,
Enabled = true
};
alarmRangeLimitTable.AddNewRecord(alarmRangeLimit);
}
return(alarmRangeLimits);
}
public override IHttpActionResult Post([FromBody] JObject record)
{
try
{
if (User.IsInRole(PostRoles))
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
// This Adds a whole bunch of new Things including the appropriate Base COnfiguration and Field objects...
ComplianceMeter newRecord = record["Meter"].ToObject <ComplianceMeter>();
int result = new TableOperations <ComplianceMeter>(connection).AddNewRecord(newRecord);
int meterId = connection.ExecuteScalar <int>("SELECT ID FROM ComplianceMeter WHERE MeterID = {0}", newRecord.MeterId);
List <BaseConfig> BaseConfigurations = record["BaseConfiguration"].ToObject <List <BaseConfig> >();
List <ComplianceField> Fields = record["ConfigurationFields"].ToObject <List <ComplianceField> >();
TableOperations <BaseConfig> baseConfigTbl = new TableOperations <BaseConfig>(connection);
TableOperations <ComplianceField> fieldTable = new TableOperations <ComplianceField>(connection);
BaseConfigurations.ForEach(item =>
{
baseConfigTbl.AddNewRecord(new BaseConfig()
{
MeterId = meterId, Name = item.Name, Pattern = item.Pattern
});
int configId = connection.ExecuteScalar <int>("SELECT @@identity");
Fields.Where(fld => fld.BaseConfigId == item.ID).ToList().ForEach(fld => fieldTable.AddNewRecord(new ComplianceField()
{
BaseConfigId = configId,
Comparison = fld.Comparison,
FieldType = fld.FieldType,
Name = fld.Name,
Value = fld.Value,
Description = fld.Description,
Category = fld.Category,
Label = fld.Label
}));
});
return(Ok(result));
}
}
else
{
return(Unauthorized());
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public void AddFieldValue(AdoDataConnection connection, string name, string value, string description = null)
{
TableOperations <FileGroupField> fileGroupFieldTable = new TableOperations <FileGroupField>(connection);
FileGroupField fileGroupField = fileGroupFieldTable.GetOrAdd(name, description);
TableOperations <FileGroupFieldValue> fileGroupFieldValueTable = new TableOperations <FileGroupFieldValue>(connection);
FileGroupFieldValue fileGroupFieldValue = new FileGroupFieldValue();
fileGroupFieldValue.FileGroupID = ID;
fileGroupFieldValue.FileGroupFieldID = fileGroupField.ID;
fileGroupFieldValue.Value = value;
fileGroupFieldValueTable.AddNewRecord(fileGroupFieldValue);
}
private List <DataQualityRangeLimit> InitializeRangeLimitTable(AdoDataConnection connection, Channel channel)
{
// Query the range limits for the given channel
TableOperations <DataQualityRangeLimit> dataQualityRangeLimitTable = new TableOperations <DataQualityRangeLimit>(connection);
List <DataQualityRangeLimit> dataQualityRangeLimits = dataQualityRangeLimitTable
.QueryRecordsWhere("ChannelID = {0}", channel.ID)
.ToList();
// If limits exist for the given channel,
// range limit table has been successfully initialized
if (dataQualityRangeLimits.Count != 0)
{
return(dataQualityRangeLimits);
}
// Get the default range limits for the measurement type and characteristic of this channel
TableOperations <DefaultDataQualityRangeLimit> defaultDataQualityRangeLimitTable = new TableOperations <DefaultDataQualityRangeLimit>(connection);
int measurementTypeID = channel.MeasurementTypeID;
int measurementCharacteristicID = channel.MeasurementCharacteristicID;
List <DefaultDataQualityRangeLimit> defaultDataQualityRangeLimits = defaultDataQualityRangeLimitTable
.QueryRecordsWhere("MeasurementTypeID = {0} AND MeasurementCharacteristicID = {1}", measurementTypeID, measurementCharacteristicID)
.ToList();
// If there are no default limits for the channel,
// then the range limit table has been successfully initialized
if (defaultDataQualityRangeLimits.Count == 0)
{
return(dataQualityRangeLimits);
}
// Update the table to include this channel's default limits
foreach (DefaultDataQualityRangeLimit defaultDataQualityRangeLimit in defaultDataQualityRangeLimits)
{
DataQualityRangeLimit dataQualityRangeLimit = new DataQualityRangeLimit()
{
ChannelID = channel.ID,
High = defaultDataQualityRangeLimit.High,
Low = defaultDataQualityRangeLimit.Low,
RangeInclusive = defaultDataQualityRangeLimit.RangeInclusive,
PerUnit = defaultDataQualityRangeLimit.PerUnit,
Enabled = true
};
dataQualityRangeLimitTable.AddNewRecord(dataQualityRangeLimit);
}
return(dataQualityRangeLimits);
}
private static void LoadEventSentEmail(AdoDataConnection connection, List <Event> systemEvent, int sentEmailID)
{
TableOperations <EventSentEmail> eventSentEmailTable = new TableOperations <EventSentEmail>(connection);
foreach (Event evt in systemEvent)
{
EventSentEmail eventSentEmail = new EventSentEmail()
{
EventID = evt.ID,
SentEmailID = sentEmailID
};
eventSentEmailTable.AddNewRecord(eventSentEmail);
}
}
private void ProcessSnapshots(MeterDataSet meterDataSet, int eventId, AdoDataConnection connection)
{
SnapshotDataResource snapshotDataResource = meterDataSet.GetResource <SnapshotDataResource>();
TableOperations <SnapshotHarmonics> table = new TableOperations <SnapshotHarmonics>(connection);
foreach (var kvp in snapshotDataResource.Snapshots)
{
table.AddNewRecord(new SnapshotHarmonics()
{
EventID = eventId,
ChannelID = kvp.Key.ID,
SpectralData = kvp.Value
});
}
}
public int GetDefaultIGridConnectionProfileID()
{
TableOperations <ConnectionProfileTaskQueue> profileTaskQueueTable = DataContext.Table <ConnectionProfileTaskQueue>();
TableOperations <ConnectionProfile> profileTable = DataContext.Table <ConnectionProfile>();
ConnectionProfile profile = profileTable.QueryRecordWhere("Name = {0}", DefaultIGridConnectionProfileName);
if ((object)profile == null)
{
ConnectionProfileTaskQueue profileTaskQueue = profileTaskQueueTable.NewRecord();
profileTaskQueue.Name = DefaultIGridConnectionProfileTaskQueueName;
profileTaskQueue.MaxThreadCount = 4;
profileTaskQueue.UseBackgroundThreads = false;
profileTaskQueue.Description = "Connection Profile Task for I-Grid Devices";
profileTaskQueueTable.AddNewRecord(profileTaskQueue);
profileTaskQueue = profileTaskQueueTable.QueryRecordWhere("Name = {0}", DefaultIGridConnectionProfileTaskQueueName);
profile = profileTable.NewRecord();
profile.Name = DefaultIGridConnectionProfileName;
profile.Description = "Connection Profile for I-Grid Devices";
profile.DefaultTaskQueueID = profileTaskQueue.ID;
profileTable.AddNewRecord(profile);
profile.ID = GetDefaultIGridConnectionProfileID();
TableOperations <ConnectionProfileTask> profileTaskTable = DataContext.Table <ConnectionProfileTask>();
profileTaskTable.RootQueryRestriction[0] = profile.ID;
int taskCount = profileTaskTable.QueryRecordCount();
if (taskCount == 0)
{
ConnectionProfileTask profileTask = profileTaskTable.NewRecord();
ConnectionProfileTaskSettings profileTaskSettings = profileTask.Settings;
profileTask.ConnectionProfileID = profile.ID;
profileTask.Name = "I-Grid Default Downloader Task";
profileTaskSettings.FileExtensions = "*.*";
profileTaskSettings.RemotePath = "/";
profileTaskSettings.LocalPath = Program.Host.Model.Global.DefaultLocalPath;
profileTaskSettings.ExternalOperation = "IGridDownloader.exe <DeviceID> <TaskID>";
profileTaskSettings.DirectoryNamingExpression = @"<YYYY><MM>\<DeviceFolderName>";
profileTaskTable.AddNewRecord(profileTask);
}
}
return(profile.ID);
}
private static void Link(int meterLocationID, int lineID, AdoDataConnection connection)
{
TableOperations <MeterLocationLine> table = new TableOperations <MeterLocationLine>(connection);
MeterLocationLine record = table.QueryRecordWhere("MeterLocationID = {0} AND LineID = {1}", meterLocationID, lineID);
if (record == null)
{
MeterLocationLine meterLocationLine = new MeterLocationLine()
{
MeterLocationID = meterLocationID,
LineID = lineID
};
table.AddNewRecord(meterLocationLine);
}
}
private void ProcessMonthlyReport(Meter meter, DateTime firstOfMonth, DateTime endOfMonth, AdoDataConnection connection)
{
Log.Info($"Starting monthly Report for {meter.Name}...");
PQReport pQReport = new PQReport(PQReportsSettings, meter, firstOfMonth, endOfMonth, connection);
byte[] pdf = pQReport.createPDF();
Log.Info($"Completed monthly Report for {meter.Name}");
try
{
TableOperations <Report> to = new TableOperations <Report>(connection);
Report report = to.QueryRecordWhere("MeterID = {0} AND Month = {1} AND Year = {2}", meter.ID, firstOfMonth.Month, firstOfMonth.Year);
if (report != null)
{
report.MeterID = meter.ID;
report.Month = firstOfMonth.Month;
report.Year = firstOfMonth.Year;
report.Results = pQReport.Result;
report.PDF = pdf;
to.UpdateRecord(report);
}
else
{
to.AddNewRecord(new Report()
{
MeterID = meter.ID,
Month = firstOfMonth.Month,
Year = firstOfMonth.Year,
Results = pQReport.Result,
PDF = pdf
});
}
Log.Info($"Loaded monthly Report for {meter.Name}");
}
catch (Exception ex)
{
Log.Error(ex.ToString(), ex);
}
}
private void LoadEvents(AdoDataConnection connection, List <Event> events, MeterDataSet meterDataSet)
{
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})
".Trim(), evt.ID);
ProcessSnapshots(meterDataSet, evt.ID, connection);
}
}
/// <summary>
/// Save All changes.
/// </summary>
public void Save()
{
if (!Changed)
{
return;
}
if (m_removed)
{
m_analytics.ToList().ForEach(a => a.Save());
}
using (AdoDataConnection connection = new AdoDataConnection(ConnectionString, DataProviderString))
{
TableOperations <TemplateSection> templateTbl = new TableOperations <TemplateSection>(connection);
if (m_section.ID <= 0 && !m_removed)
{
int templateId = new TableOperations <Template>(connection).QueryRecordWhere("Name = {0}", TemplateViewModel.Name).Id;
templateTbl.AddNewRecord(new TemplateSection()
{
Name = m_section.Name,
AnalyticTypeID = m_section.AnalyticTypeID,
Order = m_section.Order,
TemplateID = templateId
});
}
else if (!m_removed)
{
templateTbl.UpdateRecord(m_section);
}
else
{
templateTbl.DeleteRecord(m_section);
}
if (!m_removed)
{
m_analytics.ToList().ForEach(a => a.Save());
}
}
}
public static UserDashSettings GetOrAdd(this TableOperations <UserDashSettings> table, string name, Guid user, string value, bool enabled = true)
{
TransactionScopeOption required = TransactionScopeOption.Required;
TransactionOptions transactionOptions = new TransactionOptions()
{
IsolationLevel = IsolationLevel.ReadCommitted,
Timeout = TransactionManager.MaximumTimeout
};
UserDashSettings dashSettings;
using (TransactionScope transactionScope = new TransactionScope(required, transactionOptions))
{
if (value.Contains(","))
{
dashSettings = table.QueryRecordWhere("Name = {0} AND SUBSTRING(Value, 0, CHARINDEX(',', Value)) = {1} AND UserAccountID = {2}", name, value.Split(',').First(), user);
}
else
{
dashSettings = table.QueryRecordWhere("Name = {0} AND Value = {1} AND UserAccountID = {2}", name, value, user);
}
if ((object)dashSettings == null)
{
dashSettings = new UserDashSettings();
dashSettings.Name = name;
dashSettings.Value = value;
dashSettings.Enabled = enabled;
dashSettings.UserAccountID = user;
table.AddNewRecord(dashSettings);
dashSettings.ID = table.Connection.ExecuteScalar <int>("SELECT @@IDENTITY");
}
transactionScope.Complete();
}
return(dashSettings);
}
private static string[] GetOutputMeasurements(TableOperations <Measurement> measurementTable, int deviceID, string tieLineID, string sender, string receiver, OrderedDictionary outputTypes)
{
List <Measurement> measurements = new List <Measurement>();
foreach (string outputType in outputTypes.Keys)
{
string pointTag = $"IMPEDANCE_{tieLineID}-{outputType}:CV";
string description = $"{tieLineID} [{sender} => {receiver}] Calculated {outputTypes[outputType]} Value";
Measurement measurement = measurementTable.QueryRecordWhere("PointTag = {0}", pointTag);
if ((object)measurement == null)
{
measurement = measurementTable.NewRecord();
measurement.DeviceID = deviceID;
measurement.PointTag = pointTag;
measurement.SignalReference = pointTag;
measurement.Description = description;
measurement.Enabled = true;
measurementTable.AddNewRecord(measurement);
measurement = measurementTable.QueryRecordWhere("PointTag = {0}", pointTag);
if ((object)measurement == null)
{
throw new InvalidOperationException($"Failed to lookup Measurement record with PointTag of \"{pointTag}\".");
}
}
else
{
measurement.Description = description;
measurementTable.UpdateRecord(measurement);
}
measurements.Add(measurement);
}
return(measurements.Select(m => m.SignalID.ToString()).ToArray());
}
public static void Patch(this TableOperations <MeterConfiguration> meterConfigurationTable, MeterConfiguration meterConfiguration, string newConfigText)
{
string unpatchedText = Unpatch(meterConfigurationTable, meterConfiguration);
DiffMatchPatch patchProvider = new DiffMatchPatch();
List <Patch> patches = patchProvider.PatchMake(newConfigText, unpatchedText);
if (patches.Count > 0)
{
MeterConfiguration newConfiguration = new MeterConfiguration();
newConfiguration.MeterID = meterConfiguration.MeterID;
newConfiguration.ConfigKey = meterConfiguration.ConfigKey;
newConfiguration.ConfigText = newConfigText;
newConfiguration.RevisionMajor = newConfiguration.RevisionMajor + 1;
newConfiguration.RevisionMinor = 0;
meterConfigurationTable.AddNewRecord(newConfiguration);
meterConfiguration.DiffID = meterConfigurationTable.Connection.ExecuteScalar <int>("SELECT @@IDENTITY");
meterConfiguration.ConfigText = patchProvider.PatchToText(patches);
meterConfigurationTable.UpdateRecord(meterConfiguration);
}
}
