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); } }