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); }