public override plcdb_lib.Models.Model.TagsDataTable BrowseTags()
{
try
{
Model.TagsDataTable Table = new Model.TagsDataTable();
if (Server == null)
{
return(Table);
}
ServerAddressSpaceBrowser Browser = Server.GetAddressSpaceBrowser();
foreach (String ItemId in Browser.GetItemIds(BrowseType.Flat, string.Empty, VarEnum.VT_EMPTY, 0))
{
Model.TagsRow Row = Table.NewTagsRow();
Row.Address = ItemId;
Row.Controller = ControllerInfo.PK;
Table.AddTagsRow(Row);
}
Table.AcceptChanges();
return(Table);
}
catch (Exception ex)
{
throw new Exception("Error browsing tags from OPC server: " + ControllerInfo.Address + "\\" + ControllerInfo.opc_server, ex);
}
}
private void OnRefreshTags()
{
Model.TagsDataTable AllTags = CurrentController.Controller.BrowseTags();
//add new tags
foreach (Model.TagsRow row in AllTags)
{
if (ActiveModel.Tags.Where(p => p.Controller == CurrentController.PK && p.Address == row.Address).Count() == 0)
{
Model.TagsRow NewRow = ActiveModel.Tags.NewTagsRow();
NewRow.Controller = CurrentController.PK;
foreach (DataColumn col in ActiveModel.Tags.Columns)
{
if (col.ColumnName != "PK")
{
NewRow[col.ColumnName] = row[col.ColumnName];
}
}
ActiveModel.Tags.AddTagsRow(NewRow);
}
}
//delete old tags
foreach (Model.TagsRow row in ActiveModel.Tags.Where(p => p.Controller == CurrentController.PK))
{
if (AllTags.Where(p => p.Address == row.Address).Count() == 0)
{
row.Delete();
}
}
ActiveModel.Tags.AcceptChanges();
SetAvailableTags();
}
private void btnTagBrowse_Click(object sender, RoutedEventArgs e)
{
QueryPopupViewModel vm = this.DataContext as QueryPopupViewModel;
Model.TagsRow Tag = null;
if (e.Source == btnTriggerTag)
{
Tag = vm.TriggerTag;
}
else
{
Tag = vm.CurrentTagMapping.TagsRow;
}
TagBrowserPopup popup = new TagBrowserPopup();
popup.DataContext = new TagBrowserViewModel()
{
CurrentTag = Tag
};
popup.ShowDialog();
if (popup.DialogResult.HasValue && popup.DialogResult.Value)
{
if (e.Source == btnTriggerTag)
{
vm.TriggerTag = (popup.DataContext as TagBrowserViewModel).CurrentTag;
}
else
{
vm.CurrentTagMapping.Tag = (popup.DataContext as TagBrowserViewModel).CurrentTag.PK;
}
dgTagMappings.Items.Refresh();
}
}
private void ProcessInsertQuery()
{
Log.TraceEnterFunction(this);
SqlConnection sqlConnection = new SqlConnection(ActiveQuery.DatabasesRow.ConnectionString);
SqlCommand sqlCommand = new SqlCommand("", sqlConnection);
String InsertQueryStart = "INSERT INTO " + ActiveQuery.QueryText + " (";
String InsertQueryEnd = " VALUES (";
var TagMappings = ActiveModel.QueryTagMappings.Where(p => p.Query == ActiveQuery.PK);
foreach (Model.QueryTagMappingsRow TagMapping in TagMappings)
{
if (TagMapping != null && !TagMapping.IsColumnNameNull() && TagMapping.TagsRow != null && TagMapping.QueriesRow != null)
{
Log.Debug(this, "Mapping parameter to query: " + TagMapping.TagsRow + " to column " + TagMapping.ColumnName);
Model.TagsRow Tag = TagMapping.TagsRow;
ControllerBase Controller = Tag.ControllersRow.Controller;
InsertQueryStart += TagMapping.ColumnName + ", ";
String ValueRead = Controller.Read(TagMapping.TagsRow).ToSqlString() + ", ";
InsertQueryEnd += ValueRead;
Log.Info(this, "Read input parameter for column '" + TagMapping.ColumnName + " from Tag " + TagMapping.TagsRow + "'. Value Read=" + ValueRead);
}
}
InsertQueryStart = InsertQueryStart.Remove(InsertQueryStart.Length - 2) + ")";
InsertQueryEnd = InsertQueryEnd.Remove(InsertQueryEnd.Length - 2) + ")";
String InsertQuery = InsertQueryStart + InsertQueryEnd;
Log.Info(this, "Query string assembled: " + InsertQuery);
sqlCommand.CommandText = InsertQuery;
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
Log.TraceExitFunction(this);
}
private void SetPropertiesFromTagRow(Model.TagsRow value)
{
SiemensTag Tag = CreateTag(value.Address);
Start = Tag.Start;
Length = Tag.Length;
Bit = Tag.Bit;
}
public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
{
Model.TagsRow Tag = value as Model.TagsRow;
if (Tag == null)
{
return("");
}
String TagName = Tag.Name == null || Tag.Name == String.Empty ? Tag.Address : Tag.Name;
return("[" + Tag.ControllersRow.Name + "]" + TagName);
}
public override Model.TagsDataTable BrowseTags()
{
if (Tags == null)
{
Tags = new Model.TagsDataTable();
Model.TagsRow Sine = Tags.NewTagsRow();
Sine.Address = "Doubles\\Sine";
Sine.Name = Sine.Address;
Sine.DataType = typeof(Double);
Sine.Controller = ControllerInfo.PK;
Model.TagsRow Rand = Tags.NewTagsRow();
Rand.Address = "Doubles\\Rand";
Rand.Name = Rand.Address;
Rand.DataType = typeof(Double);
Rand.Controller = ControllerInfo.PK;
Model.TagsRow AlwaysOn = Tags.NewTagsRow();
AlwaysOn.Address = "Bools\\AlwaysOn";
AlwaysOn.Name = AlwaysOn.Address;
AlwaysOn.DataType = typeof(Boolean);
AlwaysOn.Controller = ControllerInfo.PK;
Model.TagsRow AlwaysOff = Tags.NewTagsRow();
AlwaysOff.Address = "Bools\\AlwaysOff";
AlwaysOff.Name = AlwaysOff.Address;
AlwaysOff.DataType = typeof(Boolean);
AlwaysOff.Controller = ControllerInfo.PK;
Model.TagsRow RandBool = Tags.NewTagsRow();
RandBool.Address = "Bools\\RandBool";
RandBool.Name = RandBool.Address;
RandBool.DataType = typeof(Boolean);
RandBool.Controller = ControllerInfo.PK;
Model.TagsRow Now = Tags.NewTagsRow();
Now.Address = "DateTimes\\Now";
Now.Name = Now.Address;
Now.DataType = typeof(DateTime);
Now.Controller = ControllerInfo.PK;
Tags.AddTagsRow(Sine);
Tags.AddTagsRow(Rand);
Tags.AddTagsRow(AlwaysOn);
Tags.AddTagsRow(AlwaysOff);
Tags.AddTagsRow(RandBool);
Tags.AddTagsRow(Now);
}
return(Tags);
}
public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
{
try
{
Model.TagsRow Tag = ActiveModel.Tags.FindByPK((long)value) as Model.TagsRow;
if (Tag == null)
{
return("");
}
String TagName = Tag.Name == null || Tag.Name == String.Empty ? Tag.Address : Tag.Name;
return("[" + Tag.ControllersRow.Name + "]" + TagName);
}
catch (Exception e)
{
return("");
}
}
public override ValidationResult Validate(object value, CultureInfo ultureInfo)
{
if (value == null || value == "")
{
return(new ValidationResult(false, "Invalid controller name"));
}
String input = (String)value;
Match ControllerMatch = Regex.Match(input, @"\[(.*?)\]");
if (!ControllerMatch.Success)
{
return(new ValidationResult(false, "Invalid controller name"));
}
String ControllerName = ControllerMatch.Value.Substring(1, ControllerMatch.Value.Length - 2);
var MatchingControllers = ActiveModel.Controllers.Where(p => p.Name == ControllerName);
if (MatchingControllers.Count() == 0)
{
return(new ValidationResult(false, "Invalid controller name"));
}
Model.ControllersRow Controller = MatchingControllers.First();
String TagName = input.Substring(input.LastIndexOf(']') + 1);
var MatchingTags = ActiveModel.Tags.Where(p => p.Controller == Controller.PK && (p.Address == TagName || p.Name == TagName));
bool ValidNewTag = Controller.Controller.ValidateTag(TagName);
if (MatchingTags.Count() == 0 && !ValidNewTag)
{
return(new ValidationResult(false, "Invalid tag address"));
}
else if (MatchingTags.Count() == 0 && ValidNewTag)
{
Model.TagsRow NewRow = ActiveModel.Tags.NewTagsRow();
NewRow.Address = TagName;
NewRow.Name = TagName;
NewRow.Controller = Controller.PK;
ActiveModel.Tags.AddTagsRow(NewRow);
return(new ValidationResult(true, null));;
}
return(new ValidationResult(true, null));
}
private void ProcessSelectQuery()
{
Log.TraceEnterFunction(this);
SqlConnection sqlConnection = new SqlConnection(ActiveQuery.DatabasesRow.ConnectionString);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(ActiveQuery.QueryText, sqlConnection);
DataTable dataResults = new DataTable();
Log.Info(this, "Performing query: " + ActiveQuery.QueryText);
sqlDataAdapter.Fill(dataResults);
Log.Debug(this, "Query completed");
if (dataResults.Rows.Count > 100)
{
Log.Warn("Warning! Query returned many results (" + dataResults.Rows.Count + " rows)!");
}
else if (dataResults.Rows.Count == 0)
{
Log.Warn("Warning! Query returned zero results!");
}
foreach (DataRow row in dataResults.Rows)
{
foreach (DataColumn col in dataResults.Columns)
{
Model.QueryTagMappingsRow TagMapping = ActiveModel.QueryTagMappings.FirstOrDefault(p => p.Query == ActiveQuery.PK && p.ColumnName == col.ColumnName);
if (TagMapping != null && !TagMapping.IsColumnNameNull() && TagMapping.TagsRow != null && TagMapping.QueriesRow != null)
{
Log.Debug(this, "Mapping result to tag: " + TagMapping.ColumnName + " to tag " + TagMapping.TagsRow);
Model.TagsRow TagToWrite = TagMapping.TagsRow;
ControllerBase ControllerToWrite = TagToWrite.ControllersRow.Controller;
ControllerToWrite.Write(TagToWrite, row[col]);
Log.Info(this, "Wrote column '" + TagMapping.ColumnName + "', value '" + row[col] + "' to tag " + TagToWrite);
}
else
{
Log.Debug(this, "Query triggered but TagMapping not set up for column '" + col.ColumnName + "'");
}
}
}
Log.TraceExitFunction(this);
}
public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
{
if (value == null || value == "")
{
return(null);
}
String input = (String)value;
Match ControllerMatch = Regex.Match(input, @"\[(.*?)\]");
if (!ControllerMatch.Success)
{
throw new Exception("Invalid tag string");
}
String ControllerName = ControllerMatch.Value.Substring(1, ControllerMatch.Value.Length - 2);
var MatchingControllers = ActiveModel.Controllers.Where(p => p.Name == ControllerName);
if (MatchingControllers.Count() == 0)
{
throw new Exception("Invalid controller name");
}
Model.ControllersRow Controller = MatchingControllers.First();
String TagName = input.Substring(input.LastIndexOf(']') + 1);
var MatchingTags = ActiveModel.Tags.Where(p => p.Controller == Controller.PK && (p.Address == TagName || p.Name == TagName));
if (MatchingTags.Count() == 0 && Controller.Controller.ValidateTag(TagName))
{
Model.TagsRow NewRow = ActiveModel.Tags.NewTagsRow();
NewRow.Address = TagName;
NewRow.Name = TagName;
NewRow.Controller = Controller.PK;
ActiveModel.Tags.AddTagsRow(NewRow);
return(NewRow);
}
return(MatchingTags.First());
}
private void AddTagToGroup(Model.TagsRow tag)
{
try
{
if (TagGroup == null)
{
String OpcName = "plcdb-" + ControllerInfo.PK;
TagGroup = Server.AddGroup(Interlocked.Increment(ref GroupClientId), OpcName, true, 100, (float)0.0);
}
OpcTag NewTag = new OpcTag();
NewTag.TagRow = tag;
NewTag.Item = new Item()
{
//AccessPath = TagRow.Address,
Active = true,
ClientId = (int)tag.PK,
ItemId = tag.Address,
RequestedDataType = VarEnum.VT_EMPTY// TagRow.IsDataTypeNull() ? VarEnum.VT_UNKNOWN : TypeToOpcType(TagRow.DataType)
};
NewTag.ItemValue = new ItemValue();
var TagResult = TagGroup.AddItems(new Item[] { NewTag.Item }).First();
NewTag.ItemResult = TagResult;
ActiveTags.Add(NewTag);
//add event handler -- make sure we are not adding a duplicate copy
TagGroup.ReadComplete -= TagGroup_ReadComplete;
TagGroup.DataChange -= TagGroup_ReadComplete;
TagGroup.ReadComplete += TagGroup_ReadComplete;
TagGroup.DataChange += TagGroup_ReadComplete;
}
catch (Exception ex)
{
throw new Exception("Error adding tag '" + tag + "' to group on OPC server: " + ControllerInfo.Address + "\\" + ControllerInfo.opc_server, ex);
}
}
private void ProcessStoredProcedure()
{
Log.TraceEnterFunction(this);
Log.Info(this, "Beginning STORED PROCEDURE: " + ActiveQuery.QueryText);
SqlConnection sqlConnection = new SqlConnection(ActiveQuery.DatabasesRow.ConnectionString);
SqlCommand sqlCommand = new SqlCommand(ActiveQuery.QueryText, sqlConnection);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlCommand.CommandType = CommandType.StoredProcedure;
foreach (Model.QueryTagMappingsRow tagMapping in ActiveQuery.GetQueryTagMappingsRows())
{
if (tagMapping != null && !tagMapping.IsColumnNameNull() && tagMapping.TagsRow != null && tagMapping.QueriesRow != null)
{
Log.Debug(this, "Setting up tag mapping for column '" + tagMapping.ColumnName + "'");
SqlParameter Parameter = new SqlParameter(tagMapping.ColumnName, null);
if (tagMapping.ColumnName == "@RETURN_VALUE")
{
Parameter.Direction = ParameterDirection.ReturnValue;
}
else if (tagMapping.OutputToController)
{
Parameter.Direction = ParameterDirection.InputOutput;
Parameter.Value = tagMapping.TagsRow.ControllersRow.Controller.Read(tagMapping.TagsRow).ToSqlString();
Log.Info(this, "Read InOut parameter '" + Parameter.ParameterName + "' from tag " + tagMapping.TagsRow + ". Value read=" + Parameter.Value);
}
else
{
Parameter.Direction = ParameterDirection.Input;
Parameter.Value = tagMapping.TagsRow.ControllersRow.Controller.Read(tagMapping.TagsRow).ToSqlString();
Log.Info(this, "Read input parameter '" + Parameter.ParameterName + "' from tag " + tagMapping.TagsRow + ". Value read=" + Parameter.Value);
}
Log.Debug(this, "Parameterization complete for for column '" + tagMapping.ColumnName + "', type=" + Parameter.Direction.ToString());
sqlCommand.Parameters.Add(Parameter);
}
else
{
Log.Debug(this, "Query triggered but TagMapping not set up for column '" + tagMapping.ColumnName + "'");
}
}
sqlConnection.Open();
DataTable dataResults = new DataTable();
sqlDataAdapter.Fill(dataResults);
Log.Debug(this, "Stored procedure execution completed");
foreach (SqlParameter Parameter in sqlCommand.Parameters)
{
if (Parameter.Direction != ParameterDirection.Input)
{
Log.Debug(this, "Mapping output parameter '" + Parameter.ParameterName + "'");
Model.QueryTagMappingsRow TagMapping = ActiveModel.QueryTagMappings.FirstOrDefault(p => p.Query == ActiveQuery.PK && p.ColumnName == Parameter.ParameterName);
if (TagMapping != null && !TagMapping.IsColumnNameNull() && TagMapping.TagsRow != null && TagMapping.QueriesRow != null)
{
Model.TagsRow TagToWrite = TagMapping.TagsRow;
ControllerBase ControllerToWrite = TagToWrite.ControllersRow.Controller;
ControllerToWrite.Write(TagToWrite, Parameter.Value);
Log.Info(this, "Wrote output parameter '" + Parameter.ParameterName + "', value '" + Parameter.Value.ToString() + "' to tag '" + TagToWrite + "'");
}
else
{
Log.Debug(this, "Query triggered but TagMapping not set up for column '" + Parameter.ParameterName + "'");
}
}
}
Log.TraceExitFunction(this);
}
public void DoWork()
{
Log.Info(this, "Worker thread beginning for Query PK " + QueryPK + " (" + ActiveQuery.Name + ")");
if (ActiveModel == null || ActiveQuery == null)
{
Log.Error(this, "ActiveModel or ActiveQuery is null");
return;
}
try
{
TriggerTag = ActiveModel.Tags.FindByPK(ActiveQuery.TriggerTag);
TriggerController = TriggerTag.ControllersRow.Controller;
Log.Debug(this, "Trigger tag and controller found");
}
catch (Exception e)
{
Status = StatusEnum.Error;
Log.Error(this, "Error finding trigger tag or controller");
Log.Error(this, e);
}
while (!StopRequest)
{
Log.Debug(this, "Starting event loop");
try
{
bool QueryTriggered = (bool)TriggerController.Read(TriggerTag);
if (QueryTriggered)
{
Log.Debug(this, "Query triggered");
switch (ActiveQuery.QueryType)
{
case "SELECT":
ProcessSelectQuery();
break;
case "INSERT":
ProcessInsertQuery();
break;
case "UPDATE":
ProcessUpdateQuery();
break;
case "DELETE":
ProcessDeleteQuery();
break;
case "STORED PROCEDURE":
ProcessStoredProcedure();
break;
default:
throw new KeyNotFoundException("Invalid query type: " + ActiveQuery.QueryType);
}
Log.Debug(this, "Query completed");
}
else
{
Log.Debug(this, "Query not triggered");
}
Status = StatusEnum.Good;
}
catch (Exception e)
{
Log.Error(this, "Error during loop execution: " + e.Message);
Status = StatusEnum.Error;
Log.Error(this, e);
}
Thread.Sleep((int)ActiveQuery.RefreshRate);
}
}
public abstract bool Write(Model.TagsRow t, object val);
public abstract object Read(Model.TagsRow t);
public override bool Write(Model.TagsRow t, object val)
{
Comm.Write(t.Address, val.ToString());
return(true);
}
public override object Read(Model.TagsRow t)
{
t.CurrentValue = Comm.Read(t.Address);
return(t.CurrentValue);
}