/// <summary>
/// Saves measurement back to the configuration database
/// </summary>
/// <param name="database">Database connection for query. Will be created from config if this value is null.</param>
/// <param name="measurement">Measurement to be inserted or updated</param>
public void Save(AdoDataConnection database, PowerMeasurement measurement)
{
var createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
if (measurement.SignalID == Guid.Empty)
{
database.ExecuteNonQuery("INSERT INTO Measurement (DeviceID, PointTag, SignalTypeID, " +
"SignalReference, Adder, Multiplier, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES " +
"({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11})", ToNotNull(measurement.DeviceID), measurement.PointTag,
measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description),
database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, Thread.CurrentPrincipal.Identity.Name, database.UtcNow);
measurement.SignalID = database.ExecuteScalar <Guid>("SELECT SignalID FROM Measurement WHERE PointTag={0}", measurement.PointTag);
}
else
{
database.ExecuteNonQuery("UPDATE Measurement SET DeviceID = {0}, PointTag = {1}, " +
"SignalTypeID = {2}, SignalReference = {3}, Adder = {4}, Multiplier = {5}, Description = {6}, " +
"Enabled = {7}, UpdatedBy = {8}, UpdatedOn = {9} WHERE SignalId = {10}", ToNotNull(measurement.DeviceID), measurement.PointTag,
measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description),
database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, measurement.SignalID);
}
}
finally
{
if (createdConnection)
{
database?.Dispose();
}
}
}
/// <summary>
/// Saves measurement back to the configuration database
/// </summary>
/// <param name="database">Database connection for query. Will be created from config if this value is null.</param>
/// <param name="measurement">Measurement to be inserted or updated</param>
public void Save(AdoDataConnection database, PowerMeasurement measurement)
{
var createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
if (measurement.SignalID == Guid.Empty)
{
database.ExecuteNonQuery("INSERT INTO Measurement (DeviceID, PointTag, SignalTypeID, " +
"SignalReference, Adder, Multiplier, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES " +
"({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11})", ToNotNull(measurement.DeviceID), measurement.PointTag,
measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description),
database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, Thread.CurrentPrincipal.Identity.Name, database.UtcNow);
measurement.SignalID = database.ExecuteScalar<Guid>("SELECT SignalID FROM Measurement WHERE PointTag={0}", measurement.PointTag);
}
else
{
database.ExecuteNonQuery("UPDATE Measurement SET DeviceID = {0}, PointTag = {1}, " +
"SignalTypeID = {2}, SignalReference = {3}, Adder = {4}, Multiplier = {5}, Description = {6}, " +
"Enabled = {7}, UpdatedBy = {8}, UpdatedOn = {9} WHERE SignalId = {10}", ToNotNull(measurement.DeviceID), measurement.PointTag,
measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description),
database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, measurement.SignalID);
}
}
finally
{
if (createdConnection)
database?.Dispose();
}
}
/// <summary>
/// Creates a new measurement object for power calculation output measurements
/// </summary>
private static PowerMeasurement CreateMeasurement(string companyAcronym, string deviceAcronym, string vendorAcronym, string signalTypeAcronym, string circuitDescription, int deviceID, int?historianID, string descriptionSuffix)
{
PowerMeasurement measurement = new PowerMeasurement
{
PointTag = CommonPhasorServices.CreatePointTag(companyAcronym, deviceAcronym, vendorAcronym, signalTypeAcronym),
Adder = 0,
Multiplier = 1,
Description = $"{circuitDescription} {descriptionSuffix}",
DeviceID = deviceID,
HistorianID = historianID,
SignalTypeID = 10,
Enabled = true,
SignalID = Guid.Empty
};
if (measurement.PointTag != null)
{
int beginIndex = measurement.PointTag.IndexOf('_');
measurement.SignalReference = measurement.PointTag.Substring(beginIndex + 1);
}
return(measurement);
}
private static void CreateOutputMeasurementsWhereNull(AdoDataConnection database, string nodeIDQueryString, Action <string> statusMessage)
{
statusMessage("Checking for calculations with null output measurements...");
string query =
$"SELECT " +
$" pc.ID, " +
$" pc.CircuitDescription, " +
$" pc.ActivePowerOutputSignalID, " +
$" pc.ApparentPowerOutputSignalID, " +
$" pc.ReactivePowerOutputSignalID, " +
$" v.Acronym AS VendorAcronym, " +
$" d.Acronym AS DeviceAcronym, " +
$" c.Acronym AS CompanyAcronym, " +
$" d.id AS DeviceID, " +
$" vm.HistorianID AS HistorianID, " +
$" p.Label AS CurrentLabel " +
$"FROM " +
$" PowerCalculation pc JOIN " +
$" Measurement vm ON vm.SignalID = pc.VoltageAngleSignalID JOIN " +
$" Measurement im ON im.SignalID = pc.CurrentAngleSignalID LEFT OUTER JOIN " +
$" Phasor p ON im.DeviceID = p.DeviceID AND im.PhasorSourceIndex = p.SourceIndex LEFT OUTER JOIN " +
$" Device d ON vm.DeviceID = d.ID LEFT OUTER JOIN " +
$" VendorDevice vd ON vd.ID = d.VendorDeviceID LEFT OUTER JOIN " +
$" Vendor v ON vd.VendorID = v.ID LEFT OUTER JOIN " +
$" Company c ON d.CompanyID = c.ID " +
$"WHERE " +
$" pc.Enabled <> 0 AND " +
$" pc.NodeID = {nodeIDQueryString} AND " +
$" ( " +
$" pc.ActivePowerOutputSignalID IS NULL OR " +
$" pc.ReactivePowerOutputSignalID IS NULL OR " +
$" pc.ApparentPowerOutputSignalID IS NULL " +
$" )";
Dictionary <int, PowerMeasurement> activePowerUpdates = new Dictionary <int, PowerMeasurement>();
Dictionary <int, PowerMeasurement> reactivePowerUpdates = new Dictionary <int, PowerMeasurement>();
Dictionary <int, PowerMeasurement> apparentPowerUpdates = new Dictionary <int, PowerMeasurement>();
using (IDbCommand cmd = database.Connection.CreateCommand())
{
cmd.CommandText = query;
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
int powerCalculationID = rdr.GetInt32(0);
string companyAcronym = rdr.IsDBNull(7) ? "" : rdr.GetString(7);
string vendorAcronym = rdr.IsDBNull(5) ? "" : rdr.GetString(5);
string signalTypeAcronym = "CALC";
string circuitDescription = rdr.IsDBNull(1) ? "" : rdr.GetString(1);
int deviceID = rdr.GetInt32(8);
int? historianID = rdr.IsDBNull(9) ? null : (int?)rdr.GetInt32(9);
// Remove any settings defined in circuit description
int semicolonIndex = circuitDescription.IndexOf(';');
if (semicolonIndex > -1)
{
circuitDescription = circuitDescription.Substring(0, semicolonIndex);
}
if (rdr.IsDBNull(2)) // Real - MW
{
// create active power output measurement
PowerMeasurement measurement = CreateMeasurement(companyAcronym, circuitDescription + "-MW", vendorAcronym, signalTypeAcronym, circuitDescription, deviceID, historianID, "Active Power Calculation");
activePowerUpdates.Add(powerCalculationID, measurement);
}
if (rdr.IsDBNull(3)) // Apparent - MVA
{
// create apparent power output measurement
PowerMeasurement measurement = CreateMeasurement(companyAcronym, circuitDescription + "-MVA", vendorAcronym, signalTypeAcronym, circuitDescription, deviceID, historianID, "Apparent Power Calculation");
apparentPowerUpdates.Add(powerCalculationID, measurement);
}
if (rdr.IsDBNull(4)) // Reactive - MVAR
{
//create reactive power output measurement
PowerMeasurement measurement = CreateMeasurement(companyAcronym, circuitDescription + "-MVAR", vendorAcronym, signalTypeAcronym, circuitDescription, deviceID, historianID, "Reactive Power Calculation");
reactivePowerUpdates.Add(powerCalculationID, measurement);
}
}
}
}
int newMeasurementsCount = activePowerUpdates.Count + reactivePowerUpdates.Count + apparentPowerUpdates.Count;
if (newMeasurementsCount > 0)
{
MeasurementRepository repo = new MeasurementRepository();
statusMessage($"Creating {newMeasurementsCount} new output measurements for power calculation...");
foreach (KeyValuePair <int, PowerMeasurement> update in activePowerUpdates)
{
repo.Save(database, update.Value);
UpdatePowerCalculation(database, update.Key, activePowerOutputSignalID: update.Value.SignalID);
}
statusMessage("Successfully created new active power calculations.");
foreach (KeyValuePair <int, PowerMeasurement> update in reactivePowerUpdates)
{
repo.Save(database, update.Value);
UpdatePowerCalculation(database, update.Key, reactivePowerOutputSignalID: update.Value.SignalID);
}
statusMessage("Successfully created new reactive power calculations.");
foreach (KeyValuePair <int, PowerMeasurement> update in apparentPowerUpdates)
{
repo.Save(database, update.Value);
UpdatePowerCalculation(database, update.Key, apparentPowerOutputSignalID: update.Value.SignalID);
}
statusMessage("Successfully created new apparent power calculations.");
statusMessage("Completed creation of new measurements for null output measurements on power calculations.");
}
}
/// <summary>
/// Creates a new measurement object for power calculation output measurements
/// </summary>
private static PowerMeasurement CreateMeasurement(string companyAcronym, string deviceAcronym, string vendorAcronym, string signalTypeAcronym, string circuitDescription, int deviceID, int? historianID, string descriptionSuffix)
{
PowerMeasurement measurement = new PowerMeasurement
{
PointTag = CommonPhasorServices.CreatePointTag(companyAcronym, deviceAcronym, vendorAcronym, signalTypeAcronym),
Adder = 0,
Multiplier = 1,
Description = $"{circuitDescription} {descriptionSuffix}",
DeviceID = deviceID,
HistorianID = historianID,
SignalTypeID = 10,
Enabled = true,
SignalID = Guid.Empty
};
if (measurement.PointTag != null)
{
int beginIndex = measurement.PointTag.IndexOf('_');
measurement.SignalReference = measurement.PointTag.Substring(beginIndex + 1);
}
return measurement;
}
