public static string SaveOutputStreamDevice(DataConnection connection, OutputStreamDevice outputStreamDevice, bool isNew, string originalAcronym)
{
//DataConnection connection = new DataConnection();
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (isNew)
command.CommandText = "Insert Into OutputStreamDevice (NodeID, AdapterID, IDCode, Acronym, BpaAcronym, Name, LoadOrder, Enabled, PhasorDataFormat, FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"Values (@nodeID, @adapterID, @idCode, @acronym, @bpaAcronym, @name, @loadOrder, @enabled, @phasorDataFormat, @frequencyDataFormat, @analogDataFormat, @coordinateFormat, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
command.CommandText = "Update OutputStreamDevice Set NodeID = @nodeID, AdapterID = @adapterID, IDCode = @idCode, Acronym = @acronym, " +
"BpaAcronym = @bpaAcronym, Name = @name, LoadOrder = @loadOrder, Enabled = @enabled, PhasorDataFormat = @phasorDataFormat, " +
"FrequencyDataFormat = @frequencyDataFormat, AnalogDataFormat = @analogDataFormat, CoordinateFormat = @coordinateFormat, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@nodeID", outputStreamDevice.NodeID));
command.Parameters.Add(AddWithValue(command, "@adapterID", outputStreamDevice.AdapterID));
command.Parameters.Add(AddWithValue(command, "@idCode", outputStreamDevice.IdCode));
command.Parameters.Add(AddWithValue(command, "@acronym", outputStreamDevice.Acronym.Replace(" ", "").ToUpper()));
command.Parameters.Add(AddWithValue(command, "@bpaAcronym", outputStreamDevice.BpaAcronym.Replace(" ", "").ToUpper()));
command.Parameters.Add(AddWithValue(command, "@name", outputStreamDevice.Name));
command.Parameters.Add(AddWithValue(command, "@loadOrder", outputStreamDevice.LoadOrder));
command.Parameters.Add(AddWithValue(command, "@enabled", outputStreamDevice.Enabled));
command.Parameters.Add(AddWithValue(command, "@phasorDataFormat", outputStreamDevice.PhasorDataFormat));
command.Parameters.Add(AddWithValue(command, "@frequencyDataFormat", outputStreamDevice.FrequencyDataFormat));
command.Parameters.Add(AddWithValue(command, "@analogDataFormat", outputStreamDevice.AnalogDataFormat));
command.Parameters.Add(AddWithValue(command, "@coordinateFormat", outputStreamDevice.CoordinateFormat));
command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
if (isNew)
{
command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
}
else
{
command.Parameters.Add(AddWithValue(command, "@id", outputStreamDevice.ID));
//if output stream device is updated then modify signal references in the measurement table
//to reflect changes in the acronym of the device. Do this only if new and original acronyms are different.
if (!string.IsNullOrEmpty(originalAcronym) && originalAcronym != outputStreamDevice.Acronym)
{
//Microsoft Access does not support REPLACE function in SQL statement.
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
{
List<OutputStreamMeasurement> outputStreamMeasurements = GetOutputStreamMeasurementList(connection, outputStreamDevice.AdapterID);
foreach (OutputStreamMeasurement osm in outputStreamMeasurements)
{
if (osm.SignalReference.StartsWith(originalAcronym + "-"))
{
osm.SignalReference = osm.SignalReference.Replace(originalAcronym, outputStreamDevice.Acronym);
SaveOutputStreamMeasurement(connection, osm, false);
}
}
}
else
{
IDbCommand command1 = connection.Connection.CreateCommand();
command1.CommandType = CommandType.Text;
command1.CommandText = "Update OutputStreamMeasurement Set SignalReference = Replace(SignalReference, @originalAcronym, @newAcronym) Where AdapterID = @adapterID"; // and SignalReference LIKE @signalReference";
command1.Parameters.Add(AddWithValue(command1, "@originalAcronym", originalAcronym));
command1.Parameters.Add(AddWithValue(command1, "@newAcronym", outputStreamDevice.Acronym));
command1.Parameters.Add(AddWithValue(command1, "@adapterID", outputStreamDevice.AdapterID));
command1.ExecuteNonQuery();
}
}
}
command.ExecuteNonQuery();
if (isNew && outputStreamDevice.IdCode == 0)
{
//TODO: update IDCode to auto generated ID value.
OutputStreamDevice deviceJustAdded = GetOutputStreamDevice(connection, outputStreamDevice.AdapterID, outputStreamDevice.Acronym.Replace(" ", "").ToUpper());
command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Update OutputStreamDevice SET IDCode = @idCode Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@idCode", deviceJustAdded.ID));
command.Parameters.Add(AddWithValue(command, "@id", deviceJustAdded.ID));
command.ExecuteNonQuery();
}
return "Output Stream Device Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static List<Company> GetCompanyList(DataConnection connection)
{
//DataConnection connection = new DataConnection();
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
List<Company> companyList = new List<Company>();
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT ID, Acronym, MapAcronym, Name, URL, LoadOrder FROM Company ORDER BY LoadOrder";
DataTable resultTable = new DataTable();
resultTable.Load(command.ExecuteReader());
companyList = (from item in resultTable.AsEnumerable()
select new Company()
{
ID = Convert.ToInt32(item.Field<object>("ID")),
Acronym = item.Field<string>("Acronym"),
MapAcronym = item.Field<string>("MapAcronym"),
Name = item.Field<string>("Name"),
URL = item.Field<string>("URL"),
LoadOrder = Convert.ToInt32(item.Field<object>("LoadOrder"))
}).ToList();
return companyList;
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static ObservableCollection<User> GetCurrentGroupUsers(DataConnection connection, string groupID)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
ObservableCollection<User> users = new ObservableCollection<User>();
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Select * From SecurityGroupUserAccountDetail WHERE SecurityGroupID = @groupID Order By UserName";
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.Parameters.Add(AddWithValue(command, "@groupID", "{" + groupID + "}"));
else
command.Parameters.Add(AddWithValue(command, "@groupID", groupID));
DataTable resultTable = new DataTable();
resultTable.Load(command.ExecuteReader());
users = new ObservableCollection<User>((from item in resultTable.AsEnumerable()
select new User()
{
ID = item.Field<object>("UserAccountID").ToString(),
Name = item.Field<string>("UserName"),
FirstName = item.Field<object>("FirstName") == null ? string.Empty : item.Field<string>("FirstName"),
LastName = item.Field<object>("LastName") == null ? string.Empty : item.Field<string>("LastName"),
Email = item.Field<object>("Email") == null ? string.Empty : item.Field<string>("Email")
}));
return users;
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static string AddDevices(DataConnection connection, int outputStreamID, Dictionary<int, string> devicesToBeAdded, bool addDigitals, bool addAnalogs)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
foreach (KeyValuePair<int, string> deviceInfo in devicesToBeAdded) //loop through all the devices that needs to be added.
{
Device device = new Device();
device = GetDeviceByDeviceID(connection, deviceInfo.Key); //Get all the information about the device to be added.
OutputStreamDevice outputStreamDevice = new OutputStreamDevice();
outputStreamDevice.NodeID = device.NodeID;
outputStreamDevice.AdapterID = outputStreamID;
outputStreamDevice.Acronym = device.Acronym;
outputStreamDevice.BpaAcronym = string.Empty;
outputStreamDevice.Name = device.Name;
outputStreamDevice.LoadOrder = device.LoadOrder;
outputStreamDevice.Enabled = true;
outputStreamDevice.PhasorDataFormat = string.Empty;
outputStreamDevice.FrequencyDataFormat = string.Empty;
outputStreamDevice.AnalogDataFormat = string.Empty;
outputStreamDevice.CoordinateFormat = string.Empty;
outputStreamDevice.IdCode = device.AccessID;
SaveOutputStreamDevice(connection, outputStreamDevice, true, string.Empty); //save in to OutputStreamDevice Table.
int savedOutputStreamDeviceID = GetOutputStreamDevice(connection, outputStreamID, device.Acronym).ID;
//********************************************
List<Phasor> phasorList = new List<Phasor>();
phasorList = GetPhasorList(connection, deviceInfo.Key); //Get all the phasor information for the device to be added.
foreach (Phasor phasor in phasorList)
{
OutputStreamDevicePhasor outputStreamDevicePhasor = new OutputStreamDevicePhasor(); //Add all phasors one by one into OutputStreamDevicePhasor table.
outputStreamDevicePhasor.NodeID = device.NodeID;
outputStreamDevicePhasor.OutputStreamDeviceID = savedOutputStreamDeviceID;
outputStreamDevicePhasor.Label = phasor.Label;
outputStreamDevicePhasor.Type = phasor.Type;
outputStreamDevicePhasor.Phase = phasor.Phase;
outputStreamDevicePhasor.LoadOrder = phasor.SourceIndex;
outputStreamDevicePhasor.ScalingValue = 0;
SaveOutputStreamDevicePhasor(connection, outputStreamDevicePhasor, true);
}
//********************************************
//********************************************
List<Measurement> measurementList = new List<Measurement>();
measurementList = GetMeasurementsByDevice(connection, deviceInfo.Key);
int analogIndex = 0;
foreach (Measurement measurement in measurementList)
{
if (measurement.SignalAcronym != "STAT")
{
OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement();
outputStreamMeasurement.NodeID = device.NodeID;
outputStreamMeasurement.AdapterID = outputStreamID;
outputStreamMeasurement.HistorianID = measurement.HistorianID;
outputStreamMeasurement.PointID = measurement.PointID;
outputStreamMeasurement.SignalReference = measurement.SignalReference;
if (measurement.SignalAcronym == "ALOG")
{
if (addAnalogs)
{
SaveOutputStreamMeasurement(connection, outputStreamMeasurement, true);
OutputStreamDeviceAnalog outputStreamDeviceAnalog = new OutputStreamDeviceAnalog();
outputStreamDeviceAnalog.NodeID = device.NodeID;
outputStreamDeviceAnalog.OutputStreamDeviceID = savedOutputStreamDeviceID;
outputStreamDeviceAnalog.Label = device.Acronym.Length > 12 ? device.Acronym.Substring(0, 12) + ":A" + analogIndex.ToString() : device.Acronym + ":A" + analogIndex.ToString(); // measurement.PointTag;
outputStreamDeviceAnalog.Type = 0; //default
outputStreamDeviceAnalog.LoadOrder = Convert.ToInt32(measurement.SignalReference.Substring((measurement.SignalReference.LastIndexOf("-") + 3)));
outputStreamDeviceAnalog.ScalingValue = 0;
SaveOutputStreamDeviceAnalog(connection, outputStreamDeviceAnalog, true);
analogIndex += 1;
}
}
else if (measurement.SignalAcronym == "DIGI")
{
if (addDigitals)
{
SaveOutputStreamMeasurement(connection, outputStreamMeasurement, true);
OutputStreamDeviceDigital outputStreamDeviceDigital = new OutputStreamDeviceDigital();
outputStreamDeviceDigital.NodeID = device.NodeID;
outputStreamDeviceDigital.OutputStreamDeviceID = savedOutputStreamDeviceID;
outputStreamDeviceDigital.Label = digitalLabel; // measurement.PointTag;
outputStreamDeviceDigital.LoadOrder = Convert.ToInt32(measurement.SignalReference.Substring((measurement.SignalReference.LastIndexOf("-") + 3)));
outputStreamDeviceDigital.MaskValue = 0;
SaveOutputStreamDeviceDigital(connection, outputStreamDeviceDigital, true);
}
}
else
SaveOutputStreamMeasurement(connection, outputStreamMeasurement, true);
}
}
//********************************************
}
return "Output Stream Device(s) Added Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static List<CalculatedMeasurement> GetCalculatedMeasurementList(DataConnection connection, string nodeID)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
List<CalculatedMeasurement> calculatedMeasurementList = new List<CalculatedMeasurement>();
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (string.IsNullOrEmpty(nodeID) || MasterNode(connection, nodeID))
command.CommandText = "Select * From CalculatedMeasurementDetail Order By LoadOrder";
else
{
command.CommandText = "Select * From CalculatedMeasurementDetail Where NodeID = @nodeID Order By LoadOrder";
//command.Parameters.Add(AddWithValue(command, "@nodeID", nodeID));
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.Parameters.Add(AddWithValue(command, "@nodeID", "{" + nodeID + "}"));
else
command.Parameters.Add(AddWithValue(command, "@nodeID", nodeID));
}
DataTable resultTable = new DataTable();
resultTable.Load(command.ExecuteReader());
calculatedMeasurementList = (from item in resultTable.AsEnumerable()
select new CalculatedMeasurement()
{
NodeId = item.Field<object>("NodeID").ToString(),
ID = Convert.ToInt32(item.Field<object>("ID")),
Acronym = item.Field<string>("Acronym"),
Name = item.Field<string>("Name"),
AssemblyName = item.Field<string>("AssemblyName"),
TypeName = item.Field<string>("TypeName"),
ConnectionString = item.Field<string>("ConnectionString"),
ConfigSection = item.Field<string>("ConfigSection"),
InputMeasurements = item.Field<string>("InputMeasurements"),
OutputMeasurements = item.Field<string>("OutputMeasurements"),
MinimumMeasurementsToUse = Convert.ToInt32(item.Field<object>("MinimumMeasurementsToUse")),
FramesPerSecond = Convert.ToInt32(item.Field<object>("FramesPerSecond") ?? 30),
LagTime = item.Field<double>("LagTime"),
LeadTime = item.Field<double>("LeadTime"),
UseLocalClockAsRealTime = Convert.ToBoolean(item.Field<object>("UseLocalClockAsRealTime")),
AllowSortsByArrival = Convert.ToBoolean(item.Field<object>("AllowSortsByArrival")),
LoadOrder = Convert.ToInt32(item.Field<object>("LoadOrder")),
Enabled = Convert.ToBoolean(item.Field<object>("Enabled")),
IgnoreBadTimeStamps = Convert.ToBoolean(item.Field<object>("IgnoreBadTimeStamps")),
TimeResolution = Convert.ToInt32(item.Field<object>("TimeResolution")),
AllowPreemptivePublishing = Convert.ToBoolean(item.Field<object>("AllowPreemptivePublishing")),
DownsamplingMethod = item.Field<string>("DownSamplingMethod"),
NodeName = item.Field<string>("NodeName"),
PerformTimestampReasonabilityCheck = Convert.ToBoolean(item.Field<object>("PerformTimestampReasonabilityCheck"))
}).ToList();
return calculatedMeasurementList;
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
static int GetSignalTypeID(DataConnection connection, string suffix)
{
int signalTypeID = 0;
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Select ID From SignalType Where Suffix = @suffix";
command.Parameters.Add(AddWithValue(command, "@suffix", suffix));
signalTypeID = (int)command.ExecuteScalar();
}
catch (Exception ex)
{
LogException(connection, "GetSignalTypeID", ex);
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
return signalTypeID;
}
public static string DeleteUser(DataConnection connection, string userID)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
//Setup current users context for Delete trigger.
SetCurrentUserContext(connection);
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Delete From UserAccount Where ID = @id";
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.Parameters.Add(AddWithValue(command, "@id", "{" + userID + "}"));
else
command.Parameters.Add(AddWithValue(command, "@id", userID));
command.ExecuteNonQuery();
return "User Deleted Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static string SaveVendor(DataConnection connection, Vendor vendor, bool isNew)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (isNew)
command.CommandText = "Insert Into Vendor (Acronym, Name, PhoneNumber, ContactEmail, URL, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values (@acronym, @name, @phoneNumber, @contactEmail, @url, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
command.CommandText = "Update Vendor Set Acronym = @acronym, Name = @name, PhoneNumber = @phoneNumber, ContactEmail = @contactEmail, URL = @url, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@acronym", vendor.Acronym.Replace(" ", "").ToUpper()));
command.Parameters.Add(AddWithValue(command, "@name", vendor.Name));
command.Parameters.Add(AddWithValue(command, "@phoneNumber", vendor.PhoneNumber));
command.Parameters.Add(AddWithValue(command, "@contactEmail", vendor.ContactEmail));
command.Parameters.Add(AddWithValue(command, "@url", vendor.URL));
command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
if (isNew)
{
command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
}
else
command.Parameters.Add(AddWithValue(command, "@id", vendor.ID));
command.ExecuteNonQuery();
return "Vendor Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static string SaveVendorDevice(DataConnection connection, VendorDevice vendorDevice, bool isNew)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (isNew)
command.CommandText = "Insert Into VendorDevice (VendorID, Name, Description, URL, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values (@vendorID, @name, @description, @url, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
command.CommandText = "Update VendorDevice Set VendorID = @vendorID, Name = @name, Description = @description, URL = @url, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@vendorID", vendorDevice.VendorID));
command.Parameters.Add(AddWithValue(command, "@name", vendorDevice.Name));
command.Parameters.Add(AddWithValue(command, "@description", vendorDevice.Description));
command.Parameters.Add(AddWithValue(command, "@url", vendorDevice.URL));
command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
if (isNew)
{
command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
}
else
command.Parameters.Add(AddWithValue(command, "@id", vendorDevice.ID));
command.ExecuteNonQuery();
return "Vendor Device Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static string SaveRole(DataConnection connection, Role role, bool isNew)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (isNew)
command.CommandText = "Insert Into ApplicationRole (Name, Description, NodeID, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values (@name, @description, @nodeID, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
command.CommandText = "Update ApplicationRole Set Name = @name, Description = @description, NodeID = @nodeID, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@name", role.Name));
command.Parameters.Add(AddWithValue(command, "@description", role.Description));
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.Parameters.Add(AddWithValue(command, "@nodeID", "{" + role.NodeID + "}"));
else
command.Parameters.Add(AddWithValue(command, "@nodeID", role.NodeID));
command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
if (isNew)
{
command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
}
else
command.Parameters.Add(AddWithValue(command, "@id", role.ID));
command.ExecuteNonQuery();
return "Role Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static string SaveUser(DataConnection connection, User user, bool isNew)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (isNew)
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.CommandText = "Insert Into UserAccount (Name, [Password], FirstName, LastName, DefaultNodeID, Phone, Email, LockedOut, UseADAuthentication, ChangePasswordOn, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"Values (@name, @password, @firstName, @lastName, @defaultNodeID, @phone, @email, @lockedOut, @useADAuthentication, @changePasswordOn, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
command.CommandText = "Insert Into UserAccount (Name, Password, FirstName, LastName, DefaultNodeID, Phone, Email, LockedOut, UseADAuthentication, ChangePasswordOn, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"Values (@name, @password, @firstName, @lastName, @defaultNodeID, @phone, @email, @lockedOut, @useADAuthentication, @changePasswordOn, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.CommandText = "Update UserAccount Set Name = @name, [Password] = @password, FirstName = @firstName, LastName = @lastName, DefaultNodeID = @defaultNodeID, Phone = @phone, " +
"Email = @email, LockedOut = @lockedOut, UseADAuthentication = @useADAuthentication, ChangePasswordOn = @changePasswordOn, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id";
else
command.CommandText = "Update UserAccount Set Name = @name, Password = @password, FirstName = @firstName, LastName = @lastName, DefaultNodeID = @defaultNodeID, Phone = @phone, Email = @email, " +
"LockedOut = @lockedOut, UseADAuthentication = @useADAuthentication, ChangePasswordOn = @changePasswordOn, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@name", user.Name));
command.Parameters.Add(AddWithValue(command, "@password", user.Password));
command.Parameters.Add(AddWithValue(command, "@firstName", user.FirstName));
command.Parameters.Add(AddWithValue(command, "@lastName", user.LastName));
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.Parameters.Add(AddWithValue(command, "@defaultNodeID", "{" + user.DefaultNodeID + "}"));
else
command.Parameters.Add(AddWithValue(command, "@defaultNodeID", user.DefaultNodeID));
command.Parameters.Add(AddWithValue(command, "@phone", user.Phone));
command.Parameters.Add(AddWithValue(command, "@email", user.Email));
command.Parameters.Add(AddWithValue(command, "@lockedOut", user.LockedOut));
command.Parameters.Add(AddWithValue(command, "@useADAuthentication", user.UseADAuthentication));
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.Parameters.Add(AddWithValue(command, "@changePasswordOn", user.ChangePasswordOn == DateTime.MinValue ? DateTime.UtcNow.Date : user.ChangePasswordOn.Date));
else
command.Parameters.Add(AddWithValue(command, "@changePasswordOn", user.ChangePasswordOn == DateTime.MinValue ? (object)DBNull.Value : user.ChangePasswordOn));
command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
if (isNew)
{
command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
}
else
{
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.Parameters.Add(AddWithValue(command, "@id", "{" + user.ID + "}"));
else
command.Parameters.Add(AddWithValue(command, "@id", user.ID));
}
command.ExecuteNonQuery();
return "User Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static string SavePhasor(DataConnection connection, Phasor phasor, bool isNew)
{
//DataConnection connection = new DataConnection();
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
if (isNew)
command.CommandText = "Insert Into Phasor (DeviceID, Label, Type, Phase, DestinationPhasorID, SourceIndex, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values (@deviceID, @label, @type, @phase, " +
"@destinationPhasorID, @sourceIndex, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
command.CommandText = "Update Phasor Set DeviceID =@deviceID, Label = @label, Type = @type, Phase = @phase, DestinationPhasorID = @destinationPhasorID, " +
"SourceIndex = @sourceIndex, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@deviceID", phasor.DeviceID));
command.Parameters.Add(AddWithValue(command, "@label", phasor.Label));
command.Parameters.Add(AddWithValue(command, "@type", phasor.Type));
command.Parameters.Add(AddWithValue(command, "@phase", phasor.Phase));
command.Parameters.Add(AddWithValue(command, "@destinationPhasorID", phasor.DestinationPhasorID ?? (object)DBNull.Value));
command.Parameters.Add(AddWithValue(command, "@sourceIndex", phasor.SourceIndex));
command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
if (isNew)
{
command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
}
else
command.Parameters.Add(AddWithValue(command, "@id", phasor.ID));
command.ExecuteNonQuery();
Device device = new Device();
device = GetDeviceByDeviceID(connection, phasor.DeviceID);
Measurement measurement;
if (s_voltagePhasorSignalTypes == null || s_voltagePhasorSignalTypes.Rows.Count == 0)
s_voltagePhasorSignalTypes = GetPhasorSignalTypes(connection, "V");
if (s_currentPhasorSignalTypes == null || s_currentPhasorSignalTypes.Rows.Count == 0)
s_currentPhasorSignalTypes = GetPhasorSignalTypes(connection, "I");
if (phasor.Type == "V")
s_phasorSignalTypes = s_voltagePhasorSignalTypes;
else
s_phasorSignalTypes = s_currentPhasorSignalTypes;
Phasor addedPhasor = new Phasor();
//addedPhasor = GetPhasorByLabel(phasor.DeviceID, phasor.Label);
addedPhasor = GetPhasorBySourceIndex(connection, phasor.DeviceID, phasor.SourceIndex);
//we will try again just to make sure we get information back about the added phasor. As MS Access is very slow and sometimes fails to retrieve data.
if (addedPhasor == null)
{
System.Threading.Thread.Sleep(500);
//addedPhasor = GetPhasorByLabel(phasor.DeviceID, phasor.Label);
addedPhasor = GetPhasorBySourceIndex(connection, phasor.DeviceID, phasor.SourceIndex);
}
foreach (DataRow row in s_phasorSignalTypes.Rows)
{
measurement = new Measurement();
measurement.HistorianID = device.HistorianID;
measurement.DeviceID = device.ID;
if (addedPhasor.DestinationPhasorID.HasValue)
measurement.PointTag = device.CompanyAcronym + "_" + device.Acronym + "-" + GetPhasorByID(connection, addedPhasor.DeviceID, (int)addedPhasor.DestinationPhasorID).Label + ":" + device.VendorAcronym + row["Abbreviation"].ToString();
else
measurement.PointTag = device.CompanyAcronym + "_" + device.Acronym + "-" + row["Suffix"].ToString() + addedPhasor.SourceIndex.ToString() + ":" + device.VendorAcronym + row["Abbreviation"].ToString();
measurement.AlternateTag = string.Empty;
measurement.SignalTypeID = Convert.ToInt32(row["ID"]);
measurement.PhasorSourceIndex = addedPhasor.SourceIndex;
measurement.SignalReference = device.Acronym + "-" + row["Suffix"].ToString() + addedPhasor.SourceIndex.ToString();
measurement.Adder = 0.0d;
measurement.Multiplier = 1.0d;
measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.PhaseType + " " + row["Name"].ToString();
measurement.Enabled = true;
if (isNew) //if it is a new phasor then add measurements as new.
SaveMeasurement(connection, measurement, true);
else //Check if measurement exists, if so then update them otherwise add new.
{
Measurement existingMeasurement = new Measurement();
existingMeasurement = GetMeasurementInfo(connection, measurement.DeviceID, row["Suffix"].ToString(), measurement.PhasorSourceIndex);
if (existingMeasurement == null)
SaveMeasurement(connection, measurement, true);
else
{
measurement.SignalID = existingMeasurement.SignalID;
SaveMeasurement(connection, measurement, false);
}
}
}
return "Phasor Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static string SaveOutputStreamMeasurement(DataConnection connection, OutputStreamMeasurement outputStreamMeasurement, bool isNew)
{
//DataConnection connection = new DataConnection();
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (isNew)
command.CommandText = "Insert Into OutputStreamMeasurement (NodeID, AdapterID, HistorianID, PointID, SignalReference, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"Values (@nodeID, @adapterID, @historianID, @pointID, @signalReference, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
command.CommandText = "Update OutputStreamMeasurement Set NodeID = @nodeID, AdapterID = @adapterID, HistorianID = @historianID, " +
"PointID = @pointID, SignalReference = @signalReference, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn WHERE ID = @id";
command.Parameters.Add(AddWithValue(command, "@nodeID", outputStreamMeasurement.NodeID));
command.Parameters.Add(AddWithValue(command, "@adapterID", outputStreamMeasurement.AdapterID));
command.Parameters.Add(AddWithValue(command, "@historianID", outputStreamMeasurement.HistorianID ?? (object)DBNull.Value));
command.Parameters.Add(AddWithValue(command, "@pointID", outputStreamMeasurement.PointID));
command.Parameters.Add(AddWithValue(command, "@signalReference", outputStreamMeasurement.SignalReference));
command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
if (isNew)
{
command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
}
else
{
command.Parameters.Add(AddWithValue(command, "@id", outputStreamMeasurement.ID));
}
command.ExecuteNonQuery();
return "Output Stream Measurement Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static string SaveOutputStreamDevicePhasor(DataConnection connection, OutputStreamDevicePhasor outputStreamDevicePhasor, bool isNew)
{
//DataConnection connection = new DataConnection();
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (isNew)
command.CommandText = "Insert Into OutputStreamDevicePhasor (NodeID, OutputStreamDeviceID, Label, Type, Phase, LoadOrder, ScalingValue, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"Values (@nodeID, @outputStreamDeviceID, @label, @type, @phase, @loadOrder, @scalingValue, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
command.CommandText = "Update OutputStreamDevicePhasor Set NodeID = @nodeID, OutputStreamDeviceID = @outputStreamDeviceID, Label = @label, " +
"Type = @type, Phase = @phase, LoadOrder = @loadOrder, ScalingValue = @scalingValue, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@nodeID", outputStreamDevicePhasor.NodeID));
command.Parameters.Add(AddWithValue(command, "@outputStreamDeviceID", outputStreamDevicePhasor.OutputStreamDeviceID));
command.Parameters.Add(AddWithValue(command, "@label", outputStreamDevicePhasor.Label));
command.Parameters.Add(AddWithValue(command, "@type", outputStreamDevicePhasor.Type));
command.Parameters.Add(AddWithValue(command, "@phase", outputStreamDevicePhasor.Phase));
command.Parameters.Add(AddWithValue(command, "@loadOrder", outputStreamDevicePhasor.LoadOrder));
command.Parameters.Add(AddWithValue(command, "@scalingValue", outputStreamDevicePhasor.ScalingValue));
command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
if (isNew)
{
command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
}
else
{
command.Parameters.Add(AddWithValue(command, "@id", outputStreamDevicePhasor.ID));
}
command.ExecuteNonQuery();
return "Output Stream Device Phasor Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
//Do not use this method in WCF call or silverlight. It is for internal use only.
static DataTable GetPmuSignalTypes(DataConnection connection)
{
bool createdConnection = false;
DataTable resultTable = new DataTable();
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Select * From SignalType Where Source = 'PMU' AND Suffix IN ('FQ', 'DF', 'SF')";
resultTable.Load(command.ExecuteReader());
}
catch (Exception ex)
{
LogException(connection, "GetPmuSignalTypes", ex);
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
return resultTable;
}
public static string SaveWizardConfigurationInfo(DataConnection connection, string nodeID, List<WizardDeviceInfo> wizardDeviceInfoList, string connectionString,
int? protocolID, int? companyID, int? historianID, int? interconnectionID, int? parentID, bool skipDisableRealTimeData)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
List<string> nondistinctAcronymList = new List<string>();
nondistinctAcronymList = (from item in wizardDeviceInfoList
where item.Include == true
group item by item.Acronym into grouped
where grouped.Count() > 1
select grouped.Key).ToList();
if (nondistinctAcronymList.Count > 0)
{
StringBuilder sb = new StringBuilder("Duplicate Acronyms Exist");
foreach (string item in nondistinctAcronymList)
{
sb.AppendLine();
sb.Append(item);
}
throw new ArgumentException(sb.ToString());
}
int loadOrder = 1;
foreach (WizardDeviceInfo info in wizardDeviceInfoList)
{
if (info.Include)
{
Device device = new Device();
device.NodeID = nodeID;
device.Acronym = info.Acronym;
device.Name = info.Name;
device.IsConcentrator = false;
device.Longitude = info.Longitude;
device.Latitude = info.Latitude;
device.ConnectionString = parentID == null ? connectionString : string.Empty;
device.ProtocolID = protocolID;
device.CompanyID = companyID;
device.HistorianID = historianID;
device.InterconnectionID = interconnectionID;
device.Enabled = true;
device.VendorDeviceID = info.VendorDeviceID == null ? (int?)null : info.VendorDeviceID == 0 ? (int?)null : info.VendorDeviceID;
device.ParentID = parentID;
device.AccessID = info.AccessID;
device.LoadOrder = loadOrder;
device.SkipDisableRealTimeData = skipDisableRealTimeData;
device.TimeZone = string.Empty;
device.TimeAdjustmentTicks = 0;
device.DataLossInterval = 5;
device.MeasuredLines = 1;
device.ContactList = string.Empty;
device.AllowedParsingExceptions = 10;
device.ParsingExceptionWindow = 5;
device.DelayedConnectionInterval = 5;
device.AllowUseOfCachedConfiguration = true;
device.AutoStartDataParsingSequence = true;
device.MeasurementReportingInterval = 100000;
//If Add Digitals and Add Analogs is checked for the device then, if digitals and analogs are available i.e. count>0 then add them as measurements.
int digitalCount = 0;
if (info.AddDigitals && info.DigitalCount > 0)
{
digitalCount = info.DigitalCount;
}
int analogCount = 0;
if (info.AddAnalogs && info.AnalogCount > 0)
{
analogCount = info.AnalogCount;
}
Device existingDevice = GetDeviceByAcronym(connection, info.Acronym);
if (existingDevice != null)
{
device.ID = existingDevice.ID;
device.TimeZone = existingDevice.TimeZone;
device.TimeAdjustmentTicks = existingDevice.TimeAdjustmentTicks;
device.DataLossInterval = existingDevice.DataLossInterval;
device.MeasuredLines = existingDevice.MeasuredLines;
device.ContactList = existingDevice.ContactList;
device.AllowedParsingExceptions = existingDevice.AllowedParsingExceptions;
device.ParsingExceptionWindow = existingDevice.ParsingExceptionWindow;
device.DelayedConnectionInterval = existingDevice.DelayedConnectionInterval;
device.AllowUseOfCachedConfiguration = existingDevice.AllowUseOfCachedConfiguration;
device.AutoStartDataParsingSequence = existingDevice.AutoStartDataParsingSequence;
device.MeasurementReportingInterval = existingDevice.MeasurementReportingInterval;
SaveDevice(connection, device, false, digitalCount, analogCount);
}
else
SaveDevice(connection, device, true, digitalCount, analogCount);
Device addedDevice = GetDeviceByAcronym(connection, info.Acronym);
int count = 1;
foreach (PhasorInfo phasorInfo in info.PhasorList)
{
if (phasorInfo.Label.ToLower() != "unused")
{
Phasor phasor = new Phasor();
phasor.DeviceID = addedDevice.ID;
phasor.Label = phasorInfo.Label;
phasor.Type = phasorInfo.Type;
phasor.Phase = phasorInfo.Phase;
phasor.DestinationPhasorID = null;
phasor.SourceIndex = count;
Phasor existingPhasor = GetPhasorBySourceIndex(connection, addedDevice.ID, phasor.SourceIndex);
if (existingPhasor != null)
{
phasor.ID = existingPhasor.ID;
SavePhasor(connection, phasor, false);
}
else
SavePhasor(connection, phasor, true);
}
count++;
}
}
loadOrder++;
}
return "Configuration Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
private static string GetProtocolAcronymByID(DataConnection connection, int id)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Select Acronym From Protocol Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@id", id));
DataTable resultTable = new DataTable();
resultTable.Load(command.ExecuteReader());
if (resultTable.Rows.Count > 0)
return resultTable.Rows[0]["Acronym"].ToString();
else
return string.Empty;
}
catch (Exception ex)
{
LogException(connection, "GetProtocolAcronymByID", ex);
return string.Empty;
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
// we can just use ID column in the database for delete as it is auto increament.
public static string DeleteOutputStreamMeasurement(DataConnection connection, int outputStreamMeasurementID)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
//Setup current users context for Delete trigger.
SetCurrentUserContext(connection);
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Delete From OutputStreamMeasurement Where ID = @id";
command.Parameters.Add(AddWithValue(command, "@id", outputStreamMeasurementID));
command.ExecuteNonQuery();
return "Output Stream Measurement Deleted Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static void DeleteRoleUsers(DataConnection connection, string applicationRoleID, List<string> userIDsToBeDeleted)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command;
foreach (string userID in userIDsToBeDeleted)
{
command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Delete From ApplicationRoleUserAccount Where ApplicationRoleID = @applicationRoleID AND UserAccountID = @userID";
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
{
command.Parameters.Add(AddWithValue(command, "@applicationRoleID", "{" + applicationRoleID + "}"));
command.Parameters.Add(AddWithValue(command, "@userID", "{" + userID + "}"));
}
else
{
command.Parameters.Add(AddWithValue(command, "@applicationRoleID", applicationRoleID));
command.Parameters.Add(AddWithValue(command, "@userID", userID));
}
command.ExecuteNonQuery();
command.Parameters.Clear();
}
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
/// <summary>
/// Purpose of this method is to supply current user information from the UI to DELETE trigger for change logging.
/// This method must be called before any delete operation on the database in order to log who deleted this record.
/// For SQL server it sets user name into CONTEXT_INFO().
/// For MySQL server it sets user name into session variable @context.
/// MS Access is not supported for change logging.
/// For any other database in the future, such as Oracle, this logic must be extended to support change log in the database.
/// </summary>
/// <param name="connection">Connection used to set user context before any delete operation.</param>
public static void SetCurrentUserContext(DataConnection connection)
{
bool createdConnection = false;
try
{
if (string.IsNullOrEmpty(s_currentUser))
s_currentUser = Thread.CurrentPrincipal.Identity.Name;
if (!string.IsNullOrEmpty(s_currentUser))
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command;
//First of all set Current User for the database session for this connection.
if (connection.Connection.GetType().Name.ToLower() == "sqlconnection")
{
string contextSql = "DECLARE @context VARBINARY(128)\n SELECT @context = CONVERT(VARBINARY(128), CONVERT(VARCHAR(128), @userName))\n SET CONTEXT_INFO @context";
command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = contextSql;
command.Parameters.Add(AddWithValue(command, "@userName", s_currentUser));
command.ExecuteNonQuery();
}
else if (connection.Connection.GetType().Name.ToLower() == "mysqlconnection")
{
try
{
command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SET @context = '" + s_currentUser + "';";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static List<Adapter> GetAdapterList(DataConnection connection, bool enabledOnly, AdapterType adapterType, string nodeID)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
List<Adapter> adapterList = new List<Adapter>();
string viewName;
if (adapterType == AdapterType.Action)
viewName = "CustomActionAdapterDetail";
else if (adapterType == AdapterType.Input)
viewName = "CustomInputAdapterDetail";
else
viewName = "CustomOutputAdapterDetail";
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (string.IsNullOrEmpty(nodeID) || MasterNode(connection, nodeID))
command.CommandText = "Select * From " + viewName + " Order By LoadOrder";
else
{
command.CommandText = "Select * From " + viewName + " Where NodeID = @nodeID Order By LoadOrder";
//command.Parameters.Add(AddWithValue(command, "@nodeID", nodeID));
if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
command.Parameters.Add(AddWithValue(command, "@nodeID", "{" + nodeID + "}"));
else
command.Parameters.Add(AddWithValue(command, "@nodeID", nodeID));
}
DataTable resultTable = new DataTable();
resultTable.Load(command.ExecuteReader());
adapterList = (from item in resultTable.AsEnumerable()
select new Adapter()
{
NodeID = item.Field<object>("NodeID").ToString(),
ID = Convert.ToInt32(item.Field<object>("ID")),
AdapterName = item.Field<string>("AdapterName"),
AssemblyName = item.Field<string>("AssemblyName"),
TypeName = item.Field<string>("TypeName"),
ConnectionString = item.Field<string>("ConnectionString"),
LoadOrder = Convert.ToInt32(item.Field<object>("LoadOrder")),
Enabled = Convert.ToBoolean(item.Field<object>("Enabled")),
NodeName = item.Field<string>("NodeName"),
adapterType = adapterType
}).ToList();
return adapterList;
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static string UpdateOutputStreamStatistics(DataConnection connection, string nodeID, string oldAcronym, string newAcronym, string oldName, string newName)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
////If device is updated then make sure all the statistical measurements get updated too to reflect any change in acronym.
if (!string.IsNullOrEmpty(oldAcronym) && oldAcronym != newAcronym)
{
List<Measurement> measurementList = GetOutputStreamStatistics(connection, nodeID, oldAcronym);
foreach (Measurement measurement in measurementList)
{
measurement.SignalReference = measurement.SignalReference.Replace(oldAcronym, newAcronym);
measurement.PointTag = measurement.PointTag.Replace(oldAcronym, newAcronym);
measurement.Description = System.Text.RegularExpressions.Regex.Replace(measurement.Description, oldName, newName, System.Text.RegularExpressions.RegexOptions.IgnoreCase); //measurement.Description.Replace(oldAcronym, newAcronym);
SaveMeasurement(connection, measurement, false);
}
}
return "";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static Dictionary<int, BasicStatisticInfo> GetBasicStatisticInfoList(DataConnection connection, string nodeID)
{
return GetStatisticInfoList(connection, nodeID).ToDictionary(statistic => statistic.PointID, statistic => statistic.Statistics);
}
private static Measurement GetMeasurementInfoBySignalReference(DataConnection connection, int? deviceID, string signalReference, int? phasorSourceIndex)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand commnad = connection.Connection.CreateCommand();
commnad.CommandType = CommandType.Text;
commnad.CommandText = "Select * From MeasurementDetail Where SignalReference = @signalReference";
commnad.Parameters.Add(AddWithValue(commnad, "@signalReference", signalReference));
if (deviceID != null)
{
commnad.CommandText += " AND DeviceID = @deviceID";
commnad.Parameters.Add(AddWithValue(commnad, "@deviceID", deviceID ?? (object)DBNull.Value));
}
if (phasorSourceIndex != null)
{
commnad.CommandText += " AND PhasorSourceIndex = @phasorSourceIndex";
commnad.Parameters.Add(AddWithValue(commnad, "@phasorSourceIndex", phasorSourceIndex ?? (object)DBNull.Value));
}
DataTable resultTable = GetResultSet(commnad).Tables[0];
if (resultTable.Rows.Count == 0)
return null;
Measurement measurement = (Measurement)(from item in resultTable.AsEnumerable()
select new Measurement()
{
SignalID = item.Field<object>("SignalID").ToString(),
HistorianID = item.NullableInt("HistorianID"),
PointID = Convert.ToInt32(item.Field<object>("PointID")),
DeviceID = Convert.ToInt32(item.Field<object>("DeviceID")),
PointTag = item.Field<string>("PointTag"),
AlternateTag = item.Field<string>("AlternateTag"),
SignalTypeID = Convert.ToInt32(item.Field<object>("SignalTypeID")),
PhasorSourceIndex = item.NullableInt("PhasorSourceIndex"),
SignalReference = item.Field<string>("SignalReference"),
Adder = item.Field<double>("Adder"),
Multiplier = item.Field<double>("Multiplier"),
Description = item.Field<string>("Description"),
Enabled = Convert.ToBoolean(item.Field<object>("Enabled")),
HistorianAcronym = item.Field<string>("HistorianAcronym"),
DeviceAcronym = item.Field<object>("DeviceAcronym") == null ? string.Empty : item.Field<string>("DeviceAcronym"),
SignalName = item.Field<string>("SignalName"),
SignalAcronym = item.Field<string>("SignalAcronym"),
SignalSuffix = item.Field<string>("SignalTypeSuffix"),
PhasorLabel = item.Field<string>("PhasorLabel")
}).First();
return measurement;
}
catch (Exception ex)
{
LogException(connection, "GetMeasurementInfoBySignalReference", ex);
return null;
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static Dictionary<int, string> GetCompanies(DataConnection connection, bool isOptional)
{
//DataConnection connection = new DataConnection();
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
Dictionary<int, string> companyList = new Dictionary<int, string>();
if (isOptional)
companyList.Add(0, "Select Company");
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT ID, Name FROM Company ORDER BY LoadOrder";
DataTable resultTable = new DataTable();
resultTable.Load(command.ExecuteReader());
int id;
foreach (DataRow row in resultTable.Rows)
{
id = int.Parse(row["ID"].ToString());
if (!companyList.ContainsKey(id))
companyList.Add(id, row["Name"].ToString());
}
return companyList;
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
static Phasor GetPhasorBySourceIndex(DataConnection connection, int deviceID, int sourceIndex)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Select * From PhasorDetail Where DeviceID = @deviceID and SourceIndex = @sourceIndex";
command.Parameters.Add(AddWithValue(command, "@deviceID", deviceID));
command.Parameters.Add(AddWithValue(command, "@sourceIndex", sourceIndex));
DataTable resultTable = GetResultSet(command).Tables[0];
if (resultTable.Rows.Count == 0)
return null;
Phasor phasor = (Phasor)(from item in resultTable.AsEnumerable()
select new Phasor()
{
ID = Convert.ToInt32(item.Field<object>("ID")),
DeviceID = Convert.ToInt32(item.Field<object>("DeviceID")),
Label = item.Field<string>("Label"),
Type = item.Field<string>("Type"),
Phase = item.Field<string>("Phase"),
DestinationPhasorID = item.NullableInt("DestinationPhasorID"),
SourceIndex = Convert.ToInt32(item.Field<object>("SourceIndex")),
DestinationPhasorLabel = item.Field<string>("DestinationPhasorLabel"),
DeviceAcronym = item.Field<string>("DeviceAcronym"),
PhasorType = item.Field<string>("Type") == "V" ? "Voltage" : "Current",
PhaseType = item.Field<string>("Phase") == "+" ? "Positive Sequence" : item.Field<string>("Phase") == "-" ? "Negative Sequence" :
item.Field<string>("Phase") == "0" ? "Zero Sequence" : item.Field<string>("Phase") == "A" ? "Phase A" : item.Field<string>("Phase") == "B" ? "Phase B" : "Phase C"
}).First();
return phasor;
}
catch (Exception ex)
{
LogException(connection, "GetPhasorBySourceIndex", ex);
return null;
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
public static Device GetConcentratorDevice(DataConnection connection, int deviceID)
{
try
{
Device device = new Device();
device = GetDeviceByDeviceID(connection, deviceID);
if (device.IsConcentrator)
return device;
else
return null;
}
catch (Exception ex)
{
LogException(connection, "GetConcentratorDevice", ex);
return null;
}
}
static DataTable GetPhasorSignalTypes(DataConnection connection, string phasorType)
{
DataTable resultTable = new DataTable();
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (phasorType == "V")
command.CommandText = "Select * From SignalType Where Source = 'Phasor' AND Acronym LIKE 'V%'";
else
command.CommandText = "Select * From SignalType Where Source = 'Phasor' AND Acronym LIKE 'I%'";
resultTable.Load(command.ExecuteReader());
}
catch (Exception ex)
{
LogException(connection, "GetPhasorSignalTypes", ex);
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
return resultTable;
}
public void SaveDevice(Device device, bool isNew, int digitalCount, int analogCount)
{
SystemMessages sm;
DataConnection connection = new DataConnection();
try
{
string result = CommonFunctions.SaveDevice(connection, device, isNew, digitalCount, analogCount);
//get the ID of the new device added to the system so we can associate that ID to the phasors of the original device.
int deviceID;
if (isNew)
deviceID = CommonFunctions.GetDeviceByAcronym(connection, device.Acronym).ID;
else
deviceID = device.ID;
if (m_deviceToCopy != null && isNew) //if we are copying device then make sure we copy phasors also.
{
List<Phasor> phasorList = CommonFunctions.GetPhasorList(connection, m_deviceToCopy.ID);
foreach (Phasor phasor in phasorList)
{
phasor.DeviceID = deviceID;
CommonFunctions.SavePhasor(connection, phasor, true);
}
}
sm = new SystemMessages(new Message()
{
UserMessage = result, SystemMessage = string.Empty, UserMessageType = MessageType.Success
},
ButtonType.OkOnly);
sm.Owner = Window.GetWindow(this);
sm.WindowStartupLocation = WindowStartupLocation.CenterOwner;
sm.ShowPopup();
//update statistic measurement for a device if device is being updated and acronym has changed.
try
{
if (!isNew && !string.IsNullOrEmpty(m_oldAcronym) && m_oldAcronym != device.Acronym)
{
CommonFunctions.UpdateDeviceStatistics(connection, deviceID, m_oldAcronym, device.Acronym, m_oldDeviceName, device.Name);
//also if acronym has changed then make those changes
}
}
catch (Exception ex)
{
CommonFunctions.LogException(connection, "WPF.UpdateDeviceStatistics", ex);
sm = new SystemMessages(new Message()
{
UserMessage = "Failed to Update Device Statistics", SystemMessage = ex.Message, UserMessageType = MessageType.Error
},
ButtonType.OkOnly);
sm.Owner = Window.GetWindow(this);
sm.WindowStartupLocation = WindowStartupLocation.CenterOwner;
sm.ShowPopup();
}
//Update Metadata in the openPDC Service.
try
{
if (serviceClient != null && serviceClient.Helper.RemotingClient.CurrentState == TVA.Communication.ClientState.Connected)
{
if (device.Enabled) //if device is enabled then send initialize command otherwise send reloadconfig command.
CommonFunctions.SendCommandToWindowsService(serviceClient, "Initialize " + CommonFunctions.GetRuntimeID(connection, "Device", deviceID)); // Convert.ToInt32(TextBlockRuntimeID.Text));
else
CommonFunctions.SendCommandToWindowsService(serviceClient, "ReloadConfig"); //we do this to make sure all statistical measurements are in the system.
if (device.HistorianID != null) //Update historian metadata
{
string runtimeID = CommonFunctions.GetRuntimeID(connection, "Historian", (int)device.HistorianID);
CommonFunctions.SendCommandToWindowsService(serviceClient, "Invoke " + runtimeID + " RefreshMetadata");
}
//now also update Stat historian metadata.
Historian statHistorian = CommonFunctions.GetHistorianByAcronym(connection, "STAT");
if (statHistorian != null)
{
string statRuntimeID = CommonFunctions.GetRuntimeID(connection, "Historian", statHistorian.ID);
CommonFunctions.SendCommandToWindowsService(serviceClient, "Invoke " + statRuntimeID + " RefreshMetadata");
}
CommonFunctions.SendCommandToWindowsService(serviceClient, "Invoke 0 ReloadStatistics");
CommonFunctions.SendCommandToWindowsService(serviceClient, "RefreshRoutes");
}
else
{
sm = new SystemMessages(new openPDCManager.Utilities.Message()
{
UserMessage = "Failed to Perform Configuration Changes", SystemMessage = "Application is disconnected from the openPDC Service.", UserMessageType = openPDCManager.Utilities.MessageType.Information
}, ButtonType.OkOnly);
sm.Owner = Window.GetWindow(this);
sm.WindowStartupLocation = WindowStartupLocation.CenterOwner;
sm.ShowPopup();
}
}
catch (Exception ex)
{
sm = new SystemMessages(new openPDCManager.Utilities.Message()
{
UserMessage = "Failed to Perform Configuration Changes", SystemMessage = ex.Message, UserMessageType = openPDCManager.Utilities.MessageType.Information
}, ButtonType.OkOnly);
sm.Owner = Window.GetWindow(this);
sm.WindowStartupLocation = WindowStartupLocation.CenterOwner;
sm.ShowPopup();
CommonFunctions.LogException(connection, "SaveDevice.RefreshMetadata", ex);
}
ClearForm();
//Navigate to Browse screen upon successful save.
BrowseDevicesUserControl browseDevices = new BrowseDevicesUserControl();
((MasterLayoutWindow)Window.GetWindow(this)).ContentFrame.Navigate(browseDevices);
}
catch (Exception ex)
{
CommonFunctions.LogException(connection, "WPF.SaveDevice", ex);
sm = new SystemMessages(new Message()
{
UserMessage = "Failed to Save Device Information", SystemMessage = ex.Message, UserMessageType = MessageType.Error
},
ButtonType.OkOnly);
sm.Owner = Window.GetWindow(this);
sm.WindowStartupLocation = WindowStartupLocation.CenterOwner;
sm.ShowPopup();
}
finally
{
if (connection != null)
connection.Dispose();
}
}
public static string SaveOutputStream(DataConnection connection, OutputStream outputStream, bool isNew)
{
bool createdConnection = false;
try
{
if (connection == null)
{
connection = new DataConnection();
createdConnection = true;
}
IDbCommand command = connection.Connection.CreateCommand();
command.CommandType = CommandType.Text;
if (isNew)
command.CommandText = "INSERT INTO OutputStream (NodeID, Acronym, Name, Type, ConnectionString, IDCode, CommandChannel, DataChannel, AutoPublishConfigFrame, AutoStartDataChannel, NominalFrequency, FramesPerSecond, LagTime, LeadTime, " +
"UseLocalClockAsRealTime, AllowSortsByArrival, LoadOrder, Enabled, IgnoreBadTimeStamps, TimeResolution, AllowPreemptivePublishing, DownsamplingMethod, DataFormat, CoordinateFormat, CurrentScalingValue, VoltageScalingValue, " +
"AnalogScalingValue, DigitalMaskValue, PerformTimestampReasonabilityCheck, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES (@nodeID, @acronym, @name, @type, @connectionString, @idCode, @commandChannel, @dataChannel, @autoPublishConfigFrame, @autoStartDataChannel, @nominalFrequency, @framesPerSecond, " +
"@lagTime, @leadTime, @useLocalClockAsRealTime, @allowSortsByArrival, @loadOrder, @enabled, @ignoreBadTimeStamps, @timeResolution, @allowPreemptivePublishing, @downsamplingMethod, @dataFormat, @coordinateFormat, " +
"@currentScalingValue, @voltageScalingValue, @analogScalingValue, @digitalMaskValue, @performTimestampReasonabilityCheck, @updatedBy, @updatedOn, @createdBy, @createdOn)";
else
command.CommandText = "UPDATE OutputStream SET NodeID = @nodeID, Acronym = @acronym, Name = @name, Type = @type, ConnectionString = @connectionString, IDCode = @idCode, CommandChannel = @commandChannel, DataChannel = @dataChannel, AutoPublishConfigFrame = @autoPublishConfigFrame, " +
"AutoStartDataChannel = @autoStartDataChannel, NominalFrequency = @nominalFrequency, FramesPerSecond = @framesPerSecond, LagTime = @lagTime, LeadTime = @leadTime, UseLocalClockAsRealTime = @useLocalClockAsRealTime, " +
"AllowSortsByArrival = @allowSortsByArrival, LoadOrder = @loadOrder, Enabled = @enabled, IgnoreBadTimeStamps = @ignoreBadTimeStamps, TimeResolution = @timeResolution, AllowPreemptivePublishing = @allowPreemptivePublishing, " +
"DownsamplingMethod = @downsamplingMethod, DataFormat = @dataFormat, CoordinateFormat = @coordinateFormat, CurrentScalingValue = @currentScalingValue, VoltageScalingValue = @voltageScalingValue, " +
"AnalogScalingValue = @analogScalingValue, DigitalMaskValue = @digitalMaskValue, PerformTimestampReasonabilityCheck = @performTimestampReasonabilityCheck, UpdatedBy = @updatedBy, UpdatedOn = @updatedOn WHERE ID = @id";
command.Parameters.Add(AddWithValue(command, "@nodeID", outputStream.NodeID));
command.Parameters.Add(AddWithValue(command, "@acronym", outputStream.Acronym.Replace(" ", "").ToUpper()));
command.Parameters.Add(AddWithValue(command, "@name", outputStream.Name));
command.Parameters.Add(AddWithValue(command, "@type", outputStream.Type));
command.Parameters.Add(AddWithValue(command, "@connectionString", outputStream.ConnectionString));
command.Parameters.Add(AddWithValue(command, "@idCode", outputStream.IDCode));
command.Parameters.Add(AddWithValue(command, "@commandChannel", outputStream.CommandChannel));
command.Parameters.Add(AddWithValue(command, "@dataChannel", outputStream.DataChannel));
command.Parameters.Add(AddWithValue(command, "@autoPublishConfigFrame", outputStream.AutoPublishConfigFrame));
command.Parameters.Add(AddWithValue(command, "@autoStartDataChannel", outputStream.AutoStartDataChannel));
command.Parameters.Add(AddWithValue(command, "@nominalFrequency", outputStream.NominalFrequency));
command.Parameters.Add(AddWithValue(command, "@framesPerSecond", outputStream.FramesPerSecond));
command.Parameters.Add(AddWithValue(command, "@lagTime", outputStream.LagTime));
command.Parameters.Add(AddWithValue(command, "@leadTime", outputStream.LeadTime));
command.Parameters.Add(AddWithValue(command, "@useLocalClockAsRealTime", outputStream.UseLocalClockAsRealTime));
command.Parameters.Add(AddWithValue(command, "@allowSortsByArrival", outputStream.AllowSortsByArrival));
command.Parameters.Add(AddWithValue(command, "@loadOrder", outputStream.LoadOrder));
command.Parameters.Add(AddWithValue(command, "@enabled", outputStream.Enabled));
command.Parameters.Add(AddWithValue(command, "@ignoreBadTimeStamps", outputStream.IgnoreBadTimeStamps));
command.Parameters.Add(AddWithValue(command, "@timeResolution", outputStream.TimeResolution));
command.Parameters.Add(AddWithValue(command, "@allowPreemptivePublishing", outputStream.AllowPreemptivePublishing));
command.Parameters.Add(AddWithValue(command, "@downsamplingMethod", outputStream.DownsamplingMethod));
command.Parameters.Add(AddWithValue(command, "@dataFormat", outputStream.DataFormat));
command.Parameters.Add(AddWithValue(command, "@coordinateFormat", outputStream.CoordinateFormat));
command.Parameters.Add(AddWithValue(command, "@currentScalingValue", outputStream.CurrentScalingValue));
command.Parameters.Add(AddWithValue(command, "@voltageScalingValue", outputStream.VoltageScalingValue));
command.Parameters.Add(AddWithValue(command, "@analogScalingValue", outputStream.AnalogScalingValue));
command.Parameters.Add(AddWithValue(command, "@digitalMaskValue", outputStream.DigitalMaskValue));
command.Parameters.Add(AddWithValue(command, "@performTimestampReasonabilityCheck", outputStream.PerformTimestampReasonabilityCheck));
command.Parameters.Add(AddWithValue(command, "@updatedBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@updatedOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
if (isNew)
{
command.Parameters.Add(AddWithValue(command, "@createdBy", s_currentUser));
command.Parameters.Add(AddWithValue(command, "@createdOn", command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB") ? DateTime.UtcNow.Date : DateTime.UtcNow));
}
else
{
command.Parameters.Add(AddWithValue(command, "@id", outputStream.ID));
}
command.ExecuteNonQuery();
try
{
// Generate Statistical Measurements for the device.
//CommonPhasorServices.ValidateStatistics(connection.Connection, connection.AdapterType, "'" + outputStream.NodeID + "'", new Action<object, EventArgs<string>>(StatusMessageHandler), new Action<object, EventArgs<Exception>>(ProcessExceptionHandler));
}
catch (Exception ex)
{
//Do not do anything. If this fails then we dont want to interrupt save operation.
LogException(connection, "SaveOutputStream: PhasorDataSourceValidation", ex);
}
return "Output Stream Information Saved Successfully";
}
finally
{
if (createdConnection && connection != null)
connection.Dispose();
}
}
