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.");
}
}
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, " +
$" d.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 = 1 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);
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.");
}
}