public HashSet <string> QueryFilteredTags(string tagFilter)
{
if (sc == null)
{
MessageBox.Show("First, establish the connection with the Historian server");
return(null);
}
else
{
Connect();
if (!IsConnected)
{
Connect();
}
try
{
Historian.TagQueryParams queryTags = new Historian.TagQueryParams {
PageSize = 100
}; // PageSize is the batch size of the while loop below, not recommended to set higher than 500
Historian.ItemErrors itemErrors = new Historian.ItemErrors();
Historian.DataSet dataSet = new Historian.DataSet();
List <Historian.Tag> filteredTags = new List <Historian.Tag>();
List <Historian.Tag> tempTags;
queryTags.Criteria.TagnameMask = tagFilter; //filtering tags
//queryTags.Criteria.DataType = Historian.Tag.NativeDataType.VariableString; //
//execute the query and populate the list datatype
while (sc.ITags.Query(ref queryTags, out tempTags))
{
filteredTags.AddRange(tempTags);
}
filteredTags.AddRange(tempTags);
//impossible to have two tags with the same name, but... using distinct anyway...
return(new HashSet <string>(filteredTags.Select(e => e.Name).Distinct().ToList()));
}
catch (Exception ex)
{
MessageBox.Show("Tag query error: " + ex.Message);
return(null);
}
}
}
public object GetValue(string tag)
{
DataQueryParams query = new CurrentValueQuery(tag);
ItemErrors errors;
Proficy.Historian.ClientAccess.API.DataSet dataset = null;
Connection.IData.Query(ref query, out dataset, out errors);
if (dataset.TotalSamples > 0)
{
return(dataset[tag].GetValue(0));
}
else
{
return("");
}
}
public DataTable QueryTagInterpolatedValues(DateTime queryStart, DateTime queryEnd, uint samples, string tagName)
{
//int totalSamples = 0;
Connect();
if (!IsConnected)
{
Connect();
}
Historian.DataQueryParams query =
new Historian.InterpolatedQuery(queryStart, queryEnd, samples, tagName)
{
Fields = Historian.DataFields.Time | Historian.DataFields.Value | Historian.DataFields.Quality
};
Historian.DataSet set = new Historian.DataSet();
query.Criteria.SamplingMode = Historian.DataCriteria.SamplingModeType.Interpolated;
sc.IData.Query(
ref query,
out set,
out _);
//totalSamples += set.TotalSamples;
var tagData = new List <TagData>();
for (int i = 0; i < set[tagName].Count(); i++)
{
var data = new TagData
{
TagName = tagName,
Value = set[tagName].GetValue(i) == null ? string.Empty : Convert.ToDecimal(set[tagName].GetValue(i)).ToString("N", CultureInfo.GetCultureInfo("hu-HU")), //the decimal separator is dot in the US or UK, etc. (decimal point), comma in Hungary, Germany, etc.
Quality = set[tagName].GetQuality(i).ToString(),
TimeStamp = set[tagName].GetTime(i).ToString("yyyy.MM.dd. HH:mm:ss")
};
tagData.Add(data);
}
return(Extensions.ConvertToDataTable(tagData));
}
private ControlLoopDataModel ProcessDataSet(Historian.DataSet set, string currentTag)
{
//string value, quality, timestamp;
string value;
float quality;
int bad = 0, autCount = 0, manCount = 0;
for (int i = 0; i < set.TotalSamples; i++)
{
value = set[currentTag].GetValue(i) == null ? " 0" : set[currentTag].GetValue(i).ToString();
quality = set[currentTag].GetQuality(i).PercentGood();
//timestamp = set[curr_tag].GetTime(i).ToLongTimeString(); //we dont need the timestamp
if (quality > 50 || value != "0")
{
if (value.In(_autModes))
{
autCount++;
}
else
{
manCount++;
}
}
else
{
bad++;
}
}
var controlLoopData = new ControlLoopDataModel
{
HistTagName = currentTag,
NumOfGoodData = NumOfSamples - bad,
NumOfGoodQltyPercentage = bad / NumOfSamples * 100,
NumOfGoodAUTData = autCount,
NumOfGoodMANData = manCount,
NumOfGoodQltyAUTPercentage = autCount / (NumOfSamples - bad) * 100,
NumOfGoodQltyMANPercentage = manCount / (NumOfSamples - bad) * 100
};
return(controlLoopData);
}
public Dictionary <string, object> GetValues(params string[] tags)
{
if (tags == null || tags.Length == 0)
{
return(new Dictionary <string, object>());
}
DataQueryParams query = new CurrentValueQuery(tags);
ItemErrors errors;
Proficy.Historian.ClientAccess.API.DataSet dataset = null;
Connection.IData.Query(ref query, out dataset, out errors);
Dictionary <string, object> values = new Dictionary <string, object>();
foreach (var item in dataset)
{
if (item.Value.Count() > 0)
{
values.Add(item.Key, item.Value.GetValue(0));
}
}
foreach (string key in tags)
{
if (dataset.ContainsKey(key))
{
values[key] = dataset[key].GetValue(0);
}
else
{
values[key] = "";
}
}
return(values);
}
public object QueryCurrentValue(params string[] tags)
{
ItemErrors errors;
Proficy.Historian.ClientAccess.API.DataSet set = new Proficy.Historian.ClientAccess.API.DataSet();
DataQueryParams query = new CurrentValueQuery(tags) { Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
CurrentValue cv= new CurrentValue();
sc.IData.Query(ref query, out set, out errors);
return set;
}
public System.Data.DataSet QueryInterpolated(DateTime startTime, DateTime endTime, uint numSamples, Type queryType, params TagSpecifics[] tags)
{
//declare general purpose variables
int total = 0;
DateTime st = DateTime.Now;
DateTime en = st;
var ds = new System.Data.DataSet();
//change the cursor to busy
//Cursor = Cursors.WaitCursor;
if (sc == null || !sc.IsConnected()) { this.Connect(); }
//if currently connected
if (sc.IsConnected())
{
var tagsForSeries = new List<string>();
var allTags = new List<string>();
var dependencies = new Dictionary<string, string[]>();
var delegates = new Dictionary<string, TagSpecifics.Formula>();
foreach (TagSpecifics tag in tags)
{
tagsForSeries.Add(tag.Tagname);
dependencies.Add(tag.Tagname, tag.DependencyTagnames);
delegates.Add(tag.Tagname, tag.CalcFormula);
if (tag.DependencyTagnames != null && tag.DependencyTagnames.Length > 0) allTags.AddRange(tag.DependencyTagnames);
if (tag.DependencyTagnames == null || tag.DependencyTagnames.Length == 0) allTags.Add(tag.Tagname);
}
//build the query string
DataQueryParams query;
ItemErrors errors;
Proficy.Historian.ClientAccess.API.DataSet set = new Proficy.Historian.ClientAccess.API.DataSet();
if (queryType == typeof(InterpolatedQuery))
{
query = new InterpolatedQuery(startTime, endTime, numSamples, allTags.ToArray<string>()) { Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "", ref ds, set);
}
else if (queryType == typeof(CalculatedQuery))
{
DataCriteria criteria = new DataCriteria()
{
Start = startTime,
End = endTime,
NumberOfSamples = numSamples,
SamplingMode = DataCriteria.SamplingModeType.Calculated,
CalculationMode = DataCriteria.CalculationModeType.Maximum,
Tagnames = new HashSet<string>(allTags)
};
query = new CalculatedQuery(DataCriteria.CalculationModeType.Maximum, allTags.ToArray<string>()) { Criteria = criteria, Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "_MAX", ref ds, set);
query.Criteria.CalculationMode = DataCriteria.CalculationModeType.Average;
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "_AVG", ref ds, set);
query.Criteria.CalculationMode = DataCriteria.CalculationModeType.Minimum;
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "_MIN", ref ds, set);
query.Criteria.SamplingMode = DataCriteria.SamplingModeType.Interpolated;
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "", ref ds, set);
//query = null;
}
else if (queryType == typeof(RawByTimeQuery))
{
DataCriteria criteria = new DataCriteria()
{
Start = startTime
};
query = new RawByTimeQuery(startTime, allTags.ToArray()) { Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
sc.IData.Query(ref query, out set, out errors);
//ds
}
else
{
query = null;
return null;
}
//execute the query and populate the "set" Historian Dataset
//sum up the total of samples
total += set.TotalSamples;
}
//calculate the elapsed time on the query operation and reset the cursor
en = DateTime.Now;
// Cursor = Cursors.Default;
//grpRead.Text = "Read Data - " + total.ToString() + " Samples - Elapsed Time: " + en.Subtract(st).Seconds.ToString() + " Secs";
return ds;
}
public System.Data.DataSet QueryInterpolated(DateTime startTime, DateTime endTime, uint numSamples, params string[] tags)
{
//declare general purpose variables
int total = 0;
DateTime st = DateTime.Now;
DateTime en = st;
var ds = new System.Data.DataSet();
//change the cursor to busy
//Cursor = Cursors.WaitCursor;
if (sc == null || !sc.IsConnected()) { this.Connect(); }
//if currently connected
if (sc.IsConnected())
{
//create .net datatable to hold the rows selected on the tags grid
System.Data.DataRow dr;
//build the query string
DataQueryParams query = new InterpolatedQuery(startTime, endTime, numSamples, tags) { Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
ItemErrors errors;
var set = new Proficy.Historian.ClientAccess.API.DataSet();
//execute the query and populate the "set" Historian Dataset
sc.IData.Query(ref query, out set, out errors);
//sum up the total of samples
total += set.TotalSamples;
foreach (string tag in tags)
{
var dt = new System.Data.DataTable(tag);
dt.Columns.Add("Timestamp", typeof(DateTime));
dt.Columns.Add("Value", typeof(double));
dt.Columns.Add("Quality", typeof(double));
//loop thru all the samples and populate the .net datatable for data grid
for (int i = 0; i < set[tag].Count(); i++)
{
dr = dt.NewRow();
if (set[tag].GetValue(i) != null)
dr["Value"] = set[tag].GetValue(i);//.ToString();
//else
//dr["Value"] = "null";
dr["Timestamp"] = set[tag].GetTime(i);// .ToLongTimeString();
dr["Quality"] = set[tag].GetQuality(i).PercentGood();//.ToString();
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
}
//populate the data grid
//dataGridData.DataSource = dtTrend;
}
//calculate the elapsed time on the query operation and reset the cursor
en = DateTime.Now;
// Cursor = Cursors.Default;
//grpRead.Text = "Read Data - " + total.ToString() + " Samples - Elapsed Time: " + en.Subtract(st).Seconds.ToString() + " Secs";
return ds;
}
public Proficy.Historian.ClientAccess.API.DataSet QueryRaw(DateTime startTime, DateTime endTime, params TagSpecifics[] tags)
{
var ds = new System.Data.DataSet();
DataQueryParams query;
ItemErrors errors;
Proficy.Historian.ClientAccess.API.DataSet set = new Proficy.Historian.ClientAccess.API.DataSet();
var tagsForSeries = new List<string>();
foreach (TagSpecifics tag in tags)
{
tagsForSeries.Add(tag.Tagname);
}
DataCriteria criteria = new DataCriteria()
{
Start=startTime,
End = endTime,
Tagnames= new HashSet<string>(tagsForSeries),
SamplingMode =DataCriteria.SamplingModeType.RawByTime
};
query = new RawByTimeQuery(startTime, tagsForSeries.ToArray()) { Criteria=criteria, Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
sc.IData.Query(ref query, out set, out errors);
return set;
//ds = AddDatasetFromHistorianDataset(tagsForSeries, new Dictionary<string, string[]>(), new Dictionary<string, TagSpecifics.Formula>(), "", ref ds, set);
//return ds;
}
public List <TagDataModel> GetBadTags(string[] plants)
{
var badTagDatas = new List <TagDataModel>();
try
{
Connect();
if (!IsConnected)
{
Connect();
}
foreach (string plant in plants) //for the tagname mask
{
Historian.TagQueryParams queryTags = new Historian.TagQueryParams {
PageSize = 500
}; // PageSize is the batch size of the while loop below, not recommended to set higher than 500
Historian.ItemErrors itemErrors = new Historian.ItemErrors();
Historian.DataSet dataSet = new Historian.DataSet();
List <Historian.Tag> tagDatas = new List <Historian.Tag>();
List <Historian.Tag> tempTagDatas;
queryTags.Criteria.TagnameMask = $"BC.{plant}*"; //tagname mask
queryTags.Criteria.CollectionDisabled = false;
queryTags.Categories = Historian.Tag.Categories.Basic;
while (sc.ITags.Query(ref queryTags, out tempTagDatas))
{
tagDatas.AddRange(tempTagDatas);
}
tagDatas.AddRange(tempTagDatas);
for (int i = 0; i < tagDatas.Count; i++)
{
var badTagData = new TagDataModel
{
TagName = tagDatas[i].Name,
Desc = tagDatas[i].Description
};
badTagDatas.Add(badTagData);
}
queryTags = new Historian.TagQueryParams {
PageSize = 500
};
queryTags.Criteria.TagnameMask = $"BC.{plant}*";
queryTags.Criteria.CollectionDisabled = false;
queryTags.Categories = Historian.Tag.Categories.Engineering; //for engineering units and limits
tagDatas.Clear();
tempTagDatas.Clear();
string[] tagNames = badTagDatas.AsEnumerable().Select(r => r.TagName).ToArray(); //get only tagnames
while (sc.ITags.Query(ref queryTags, out tempTagDatas))
{
tagDatas.AddRange(tempTagDatas);
}
tagDatas.AddRange(tempTagDatas);
for (int i = 0; i < tagDatas.Count; i++)
{
var obj = badTagDatas.FirstOrDefault(x => x.TagName == tagDatas[i].Name); //get object by tagname
if (obj != null)
{
obj.EGU = tagDatas[i].EngineeringUnits;
obj.LoEGU = tagDatas[i].LoEngineeringUnits.ToString();
obj.HiEGU = tagDatas[i].HiEngineeringUnits.ToString();
}
}
// query the latest values
Historian.DataQueryParams queryValueQuality = new Historian.CurrentValueQuery(tagNames)
{
Fields = Historian.DataFields.Value | Historian.DataFields.Quality | Historian.DataFields.Time
};
sc.IData.Query(ref queryValueQuality, out dataSet, out itemErrors);
for (int i = 0; i < tagNames.Length; i++)
{
var obj = badTagDatas.FirstOrDefault(x => x.TagName == tagNames[i]);
if (obj != null)
{
obj.Value = dataSet[tagNames[i]].GetValue(0) != null ? dataSet[tagNames[i]].GetValue(0).ToString() : "-N/A-";
obj.Quality = dataSet[tagNames[i]].GetQuality(0).ToString();
obj.TimeStamp = dataSet[tagNames[i]].GetTime(0).ToString("yyyy.MM.dd. HH:mm:ss");
}
}
badTagDatas = badTagDatas.Where(x => x.Quality.Contains("Bad")).ToList(); // filter to get only BAD qulity tags
}
return(badTagDatas);
}
catch (Exception ex)
{
Console.WriteLine("Tag query error: " + ex.Message);
throw;
}
finally
{
Disconnect();
}
}
public List <ControlLoopDataModel> QueryControlLoopsTagData(List <ModeTagsDataModel> modeTagsData, DateTime queryStart, DateTime queryEnd)
{
var controlLoopData = new List <ControlLoopDataModel>();
try
{
Connect();
if (!IsConnected)
{
Connect();
}
foreach (var modeTagData in modeTagsData)
{
Historian.DataQueryParams query =
new Historian.InterpolatedQuery(queryStart, queryEnd, NumOfSamples, modeTagData.TagName)
{
Fields = Historian.DataFields.Time | Historian.DataFields.Value | Historian.DataFields.Quality
};
Historian.DataSet set = new Historian.DataSet();
query.Criteria.FilterMode = Historian.DataCriteria.FilterModeType.AfterTime; //obviously
query.Criteria.SamplingMode = Historian.DataCriteria.SamplingModeType.Interpolated; //we need interpolated sampling
query.Criteria.FilterComparisonMode = Historian.DataCriteria.FilterComparisonModeType.GreaterThanEqual;
query.Criteria.FilterComparisonValue = ComparisonValue; //fix 70 percent
query.Criteria.FilterTag = modeTagData.PlantLoadFilterTagName; //here we filter by a tag what shows the plant (where the control loop is installed) load in percentage
sc.IData.Query(
ref query,
out set,
out Historian.ItemErrors errors); //okay, about errors we don't care here... *sad smiley here*
if (modeTagData.ConditionFilter) //additional filtering, for example based on running signals of a motor or a value measured by a flow metering instrument out on the field
{
query.Criteria.FilterMode = Historian.DataCriteria.FilterModeType.AfterTime;
query.Criteria.SamplingMode = Historian.DataCriteria.SamplingModeType.Interpolated;
query.Criteria.FilterTag = modeTagData.ConditionFilterTagName;
query.Criteria.FilterComparisonMode = GetHistFilterType(modeTagData.ConditionFilterType); //filtering comparison mode stroed in the database too, depending on the filter tag
query.Criteria.FilterComparisonValue = modeTagData.ConditionFilterValue;
sc.IData.Query(
ref query,
out set,
out errors);
controlLoopData.Add(ProcessDataSet(set, modeTagData.TagName));
}
else
{
controlLoopData.Add(ProcessDataSet(set, modeTagData.TagName));
}
}
return(controlLoopData);
}
catch (Exception ex)
{
Console.WriteLine("Tag query error: " + ex.Message);
throw;
}
finally
{
Disconnect();
}
}