public SoftwareItem[] MoreThanTenAvailableLicenses()
{
//Create a statement object with the table definition
CompositeSelectStatement statement = new CompositeSelectStatement(
new CompositeSelectStatement.TableDefinition("PurchaseOrderSoftwareInventoryApplication_software_item", "si"));
//Add the fields you want to return from the query (must match the db field names)
statement.PrimaryTable.Fields.Add(new CompositeSelectStatement.FieldDefinition("product_name"));
statement.PrimaryTable.Fields.Add(new CompositeSelectStatement.FieldDefinition("available_licenses"));
statement.PrimaryTable.Fields.Add(new CompositeSelectStatement.FieldDefinition("entity_folder_id"));
//Create Join(s)
CompositeSelectStatement.TableDefinition entityFolderTable = new CompositeSelectStatement.TableDefinition("entity_folder", "ef");
//Fields you want to select from Join Table
entityFolderTable.Fields.Add(new CompositeSelectStatement.FieldDefinition("full_path"));
CompositeSelectStatement.JoinDefinition joinEntityFolder =
new CompositeSelectStatement.JoinDefinition(
CompositeSelectStatement.JoinType.InnerJoin,
entityFolderTable,
new AndWhereSet(
new[] {
new FieldToFieldWhereCondition("si.entity_folder_id", "ef.folder_id", QueryMatchType.Equals)
}
)
);
//Add Join(s) to statement
statement.JoinList.Add(joinEntityFolder);
//You can add WhereConditions to filter out results
WhereCondition numberOfLicenses = new LiteralWhereCondition("available_licenses > 10");
//Add the where condition(s) to the statement
statement.WhereConditions.WhereConditions.Add(numberOfLicenses);
//Execute the query
DataSet queryResults = new DynamicORM().RunQuery(statement);
//Parse the query results
List <SoftwareItem> softwareItems = new List <SoftwareItem>();
foreach (DataRow row in queryResults.Tables[0].Rows)
{
SoftwareItem softwareItem = new SoftwareItem();
softwareItem.ProductName = (string)row.ItemArray[0];
softwareItem.AvailableLicenses = (int)row.ItemArray[1];
softwareItem.EntityFolderId = (string)row.ItemArray[2];
softwareItem.FullPath = (string)row.ItemArray[3];
softwareItems.Add(softwareItem);
}
return(softwareItems.ToArray());
}
public override void AfterDelete()
{
var orm = new DynamicORM();
orm.Delete(typeof(OPCEventValue), new WhereCondition[]
{
new FieldWhereCondition("event_id", QueryMatchType.Equals, this.id)
});
orm.Delete(typeof(ElementRegistration), this.flowId);
orm.Delete(typeof(ElementRegistration), this.ruleId);
base.AfterDelete();
}
public DataDescription[] GetSubItems()
{
Log.Debug($"GetSubItems called on path {Path}");
if (Path == null || Path == OPCEventFlowBehavior.SNAPSHOT_DATA) // If we're at the top, just return all server names:
{
return(opcServerOrm.Fetch()
.Select(x => new DataDescription(typeof(OPCDataProvider), x.GetEntity().EntityName, false)
{
NestedVariableName = x.GetEntity().EntityName
}).ToArray());
}
string[] pathSplit = Path.Split(new char[] { '.' }, 2); // {Server Name}.{remaining.tag.path}
DynamicORM orm = new DynamicORM();
HashSet <string> nextPaths = new HashSet <string>();
List <DataDescription> dds = new List <DataDescription>();
foreach (string key in OPCEngine.mostRecentValues.Keys) // keys are like "<guid>|Channel1.Device1.Tag1"
{
string[] keySplit = key.Split('|');
string eventId = keySplit[0];
string keyTagPath = keySplit[1];
OPCEvent ev = orm.Fetch(typeof(OPCEvent), eventId) as OPCEvent;
if (ev == null)
{
throw new Exception("OPCEvent not found with id " + eventId);
}
string serverName = (orm.Fetch(typeof(Folder), ev.EntityFolderID) as Folder)?.EntityName;
if (string.IsNullOrEmpty(serverName))
{
throw new Exception("Server name not found");
}
if (serverName != pathSplit[0])
{
continue;
}
if (pathSplit.Length == 1) // This is the node for the server, so include everything under it:
{
nextPaths.Add(keyTagPath.Split('.')[0]);
}
else
{
// If "Channel1.Device1.Tag1" starts with "Channel1.Device1", for example:
if (keyTagPath.StartsWith(pathSplit[1]))
{
string remainingPath = keyTagPath.Substring(pathSplit[1].Length + 1); // "Tag1"
string[] splitRemaining = remainingPath.Split('.');
if (splitRemaining.Length == 1) // This is a tag, so find its type
{
BaseTagValue tagValue;
if (!OPCEngine.mostRecentValues.TryGetValue(key, out tagValue))
{
throw new Exception("Could not find type for tag " + key);
}
if (dds.Any(d => d.Name == splitRemaining[0])) // Don't create duplicates - most recent value will be used.
{
continue;
}
DataDescription dd = TagValueUtils.GetDataDescriptionFromTagType(tagValue.GetType(), splitRemaining[0]);
dd.NestedVariableName = $"{this.Path}.{splitRemaining[0]}";
dds.Add(dd);
}
else
{
nextPaths.Add(splitRemaining[0]);
}
}
}
}
foreach (string nextPath in nextPaths)
{
dds.Add(new DataDescription(typeof(OPCDataProvider), nextPath, false)
{
NestedVariableName = $"{this.Path}.{nextPath}"
});
}
return(dds.ToArray());
}
/*
* In this method you define the actual data which will be returned to the report by building up a DataTable.
* It has the limits and pages as imports to utilize in your build out.
*/
public override DataTable GetData(DataTable table, IReportFilter[] filters, int?limitCount, int pageIndex)
{
//If the table hasn't been initialized, initialize it
if (table == null)
{
table = new DataTable();
}
//Add the data range from the created Report Fields
table.Columns.AddRange(GetColumnsFromReportFields(ReportFields));
//Create a statement object with the table definition
CompositeSelectStatement statement = new CompositeSelectStatement(
new CompositeSelectStatement.TableDefinition("vwSoftwareItemsDetails"));
//Add the fields you want to return from the query (must match the db field names)
statement.PrimaryTable.Fields.Add(new CompositeSelectStatement.FieldDefinition("product_name"));
statement.PrimaryTable.Fields.Add(new CompositeSelectStatement.FieldDefinition("available_licenses"));
statement.PrimaryTable.Fields.Add(new CompositeSelectStatement.FieldDefinition("entity_folder_id"));
statement.PrimaryTable.Fields.Add(new CompositeSelectStatement.FieldDefinition("full_path"));
//You can add WhereConditions to filter out results
//You can build these where conditions from the filters passed in
//Add the where condition(s) to the statement
//You can use the IReportFilter[] to process the filters and add the appropriate clauses to the query.
foreach (IReportFilter filter in filters)
{
//If the filter is of the GreaterThanCustomFilter type add the Where Condition
if (typeof(GreaterThanCustomFilter).IsInstanceOfType(filter))
{
//Add a literal where condition if the Column Name is specified
GreaterThanCustomFilter greaterThanCustomFilter = (GreaterThanCustomFilter)filter;
if (greaterThanCustomFilter.ColumnName != null)
{
statement.WhereConditions.WhereConditions.Add(new LiteralWhereCondition(greaterThanCustomFilter.ColumnName + " > " +
greaterThanCustomFilter.GreaterThanValue));
}
}
}
//Add Order By to consistently order results for handling pagination
statement.OrderBy.Add("available_licenses", ORMResultOrder.Descending);
statement.OrderBy.Add("product_name", ORMResultOrder.Descending);
//Select Top x to limit results, default to 500
if (limitCount != null)
{
statement.Top = (pageIndex + 1) * limitCount;
}
else
{
statement.Top = 500;
}
//Example Data to Access Rows Per Page - filters[0].Report.RowsPerPage
//Execute the query
DataSet queryResults = new DynamicORM().RunQuery(statement);
//Parse the Results and build the table
foreach (DataRow row in queryResults.Tables[0].Rows)
{
DataRow dr = table.NewRow();
for (int i = 0; i < ReportFields.Length; i++)
{
dr[ReportFields[i].FieldName] = row.ItemArray[i];
}
table.Rows.Add(dr);
}
return(table);
}
