private void VerifyAggregateRule() { if (AggregateRule == null) { return; } IEnumerable <T> filtered; if (AggregateFilter != null) { filtered = AggregateFilter.Compile().Invoke(StorageMechanism().Cast <T>(), _ruleRequest); } else { filtered = StorageMechanism().Cast <T>(); } var invalid = AggregateRule.Compile().Invoke(filtered); if (invalid) { _logger("WARN", "Rule Failed VerifyAggregateRule"); InvalidAction(); } }
/// <summary> /// Creates a new patient index table based on Biochemistry which selects the distinct dates of "NA" test results /// for every patient /// </summary> /// <param name="db"></param> /// <param name="people"></param> /// <param name="r"></param> /// <param name="cic"></param> /// <returns></returns> private JoinableCohortAggregateConfiguration SetupPatientIndexTable(DiscoveredDatabase db, PersonCollection people, Random r, CohortIdentificationConfiguration cic) { var syntax = db.Server.GetQuerySyntaxHelper(); var tbl = CreateDataset <Biochemistry>(db, people, 10000, r); var cata = Import(tbl, out _, out _, out _, out ExtractionInformation[] eis); var chi = eis.Single(ei => ei.GetRuntimeName().Equals("chi", StringComparison.CurrentCultureIgnoreCase)); var code = eis.Single(ei => ei.GetRuntimeName().Equals("TestCode", StringComparison.CurrentCultureIgnoreCase)); var date = eis.Single(ei => ei.GetRuntimeName().Equals("SampleDate", StringComparison.CurrentCultureIgnoreCase)); chi.IsExtractionIdentifier = true; chi.SaveToDatabase(); var ac = new AggregateConfiguration(CatalogueRepository, cata, "NA by date"); ac.AddDimension(chi); ac.AddDimension(code); ac.AddDimension(date); ac.CountSQL = null; cic.EnsureNamingConvention(ac); var and = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter = new AggregateFilter(CatalogueRepository, "TestCode is NA", and); filter.WhereSQL = syntax.EnsureWrapped("TestCode") + " = 'NA'"; filter.SaveToDatabase(); ac.RootFilterContainer_ID = and.ID; ac.SaveToDatabase(); return(new JoinableCohortAggregateConfiguration(CatalogueRepository, cic, ac)); }
private void AddFilter(DiscoveredDatabase db, string filterName, string whereSqlFirstHalf, AggregateConfiguration ac, bool useParameter, string paramName, string paramValue) { var container = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); ac.RootFilterContainer_ID = container.ID; ac.SaveToDatabase(); //create a filter var filter = new AggregateFilter(CatalogueRepository, filterName, container); if (useParameter) { filter.WhereSQL = whereSqlFirstHalf + paramName; var parameterSql = db.Server.GetQuerySyntaxHelper() .GetParameterDeclaration(paramName, new DatabaseTypeRequest(typeof(DateTime))); var parameter = filter.GetFilterFactory().CreateNewParameter(filter, parameterSql); parameter.Value = paramValue; parameter.SaveToDatabase(); } else { filter.WhereSQL = whereSqlFirstHalf + paramValue; } filter.SaveToDatabase(); }
private void CreateParameters(string param1Value, string param2Value) { container1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); acDataset.RootFilterContainer_ID = container1.ID; acDataset.SaveToDatabase(); AggregateFilter filter1 = new AggregateFilter(CatalogueRepository, "Filter1", container1); filter1.WhereSQL = "@bob = 'bob'"; filter1.SaveToDatabase(); var paramCreator = new ParameterCreator(filter1.GetFilterFactory(), null, null); paramCreator.CreateAll(filter1, null); container2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); acCohort.RootFilterContainer_ID = container2.ID; acCohort.SaveToDatabase(); AggregateFilter filter2 = new AggregateFilter(CatalogueRepository, "Filter2", container2); filter2.WhereSQL = "@bob = 'fish'"; filter2.SaveToDatabase(); paramCreator.CreateAll(filter2, null); parama1 = filter1.GetAllParameters()[0]; parama1.Value = param1Value; parama1.SaveToDatabase(); parama2 = filter2.GetAllParameters()[0]; parama2.Value = param2Value; parama2.SaveToDatabase(); }
public override void VerifyRule(IRuleRequest request = null) { base.VerifyRule(request); if (!StorageMechanism.Items.Any()) { Logger("INFO", $"Cache empty for {RuleSetName}"); return; } if (AggregateRule == null) { Logger("ERROR", $"Aggregate rule for {RuleSetName} is empty"); return; } var filtered = AggregateFilter != null ? AggregateFilter.Compile().Invoke(StorageMechanism.Items, RuleRequest) : StorageMechanism.Items; var invalid = AggregateRule.Compile().Invoke(filtered); if (!invalid) { return; } FailRule(); }
private static string BuildCypherRecommendationQuery(string query, AggregateFilter filters, ContentTypeDistribution distribution) { string where = string.Empty; if (filters != null) { where = filters.ToConditions(); } Log.Debug("BuildRecommendationQuery", "where", where); var filteredQueryText = query.Replace("\r\n", " ").Replace("\n", " "); if (!string.IsNullOrEmpty(where) && filteredQueryText.Contains(AggregateFilter.Where)) { Log.Debug("BuildRecommendationQuery", "Query before where", filteredQueryText); int wherePos = filteredQueryText.LastIndexOf(AggregateFilter.Where); string left = filteredQueryText.Substring(0, wherePos); Log.Debug("BuildRecommendationQuery", "left", left); string right = filteredQueryText.Substring(wherePos + 6); Log.Debug("BuildRecommendationQuery", "right", right); filteredQueryText = left + AggregateFilter.Where + where + " " + AggregateFilter.And + right; Log.Debug("BuildRecommendationQuery", "Query with where clause", filteredQueryText); } // Added the distribution configured field in the queries if (distribution != null && distribution.Quotas != null && distribution.Quotas.Count > 0) { Log.Debug("BuildRecommendationQuery", "Query before replace for quota", filteredQueryText); filteredQueryText = filteredQueryText.Replace("RETURN", string.Format("RETURN {0},", string.Join(",", distribution.GetFieldNames().Select(f => string.Format("c.{0}? as {0}", f))))); } Log.Debug("BuildRecommendationQuery", "Query generated", filteredQueryText); return(filteredQueryText); }
/// <summary> /// Creates the subscription. /// </summary> private void CreateSubscription() { if (m_session == null) { return; } m_subscription = new Subscription(); m_subscription.Handle = this; m_subscription.DisplayName = null; m_subscription.PublishingInterval = 1000; m_subscription.KeepAliveCount = 10; m_subscription.LifetimeCount = 100; m_subscription.MaxNotificationsPerPublish = 1000; m_subscription.PublishingEnabled = true; m_subscription.TimestampsToReturn = TimestampsToReturn.Both; m_session.AddSubscription(m_subscription); m_subscription.Create(); m_monitoredItem = new MonitoredItem(); m_monitoredItem.StartNodeId = m_nodeId; m_monitoredItem.AttributeId = Attributes.Value; m_monitoredItem.SamplingInterval = (int)SamplingIntervalNP.Value; m_monitoredItem.QueueSize = 1000; m_monitoredItem.DiscardOldest = true; // specify aggregate filter. if (AggregateCB.SelectedItem != null) { AggregateFilter filter = new AggregateFilter(); if (StartTimeCK.Checked) { filter.StartTime = StartTimeDP.Value.ToUniversalTime(); } else { filter.StartTime = DateTime.UtcNow; } filter.ProcessingInterval = (double)ResampleIntervalNP.Value; filter.AggregateType = ((AvailableAggregate)AggregateCB.SelectedItem).NodeId; if (filter.AggregateType != null) { m_monitoredItem.Filter = filter; } } m_monitoredItem.Notification += new MonitoredItemNotificationEventHandler(MonitoredItem_Notification); m_subscription.AddItem(m_monitoredItem); m_subscription.ApplyChanges(); SubscriptionStateChanged(); }
private static List <IMongoQuery> GetFilterQuery(AggregateFilter filters) { var queries = new List <IMongoQuery>(); if (filters != null && filters.Filters != null) { foreach (var filter in filters.Filters) { List <IMongoQuery> fqs = new List <IMongoQuery>(); if (filter.Operator == FilterOperator.Equals) { foreach (var value in filter.Values) { Log.Debug("ProductDataAccess.GetFilterQuery", "Filter Equals added", filter.ColumnNameMongo, GetFilterValue(value, filter.IsString)); fqs.Add(Query.EQ(filter.ColumnNameMongo, GetFilterValue(value, filter.IsString))); } if (fqs.Count > 1) { queries.Add(Query.Or(fqs)); } else { if (fqs.Count == 1) { queries.Add(fqs[0]); } } } else if (filter.Operator == FilterOperator.Different) { foreach (var value in filter.Values) { Log.Debug("ProductDataAccess.GetFilterQuery", "Filter Not Equels added", filter.ColumnNameMongo, GetFilterValue(value, filter.IsString)); fqs.Add(Query.NE(filter.ColumnNameMongo, GetFilterValue(value, filter.IsString))); } if (fqs.Count > 1) { queries.Add(Query.And(fqs)); } else { if (fqs.Count == 1) { queries.Add(fqs[0]); } } } } } return(queries); }
public void CreateAFilter() { aggregate1.RootFilterContainer_ID = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND).ID; aggregate1.SaveToDatabase(); _chiExtractionInformation = aggregate1.AggregateDimensions.Single().ExtractionInformation; _container = aggregate1.RootFilterContainer; _filter = new AggregateFilter(CatalogueRepository, "folk", _container); }
public void TestImportTree_FromCohortIdentificationConfiguration_ToSelectedDatasets_PreserveOperation() { var sds = WhenIHaveA <SelectedDataSets>(); var cata = sds.ExtractableDataSet.Catalogue; var cic = new CohortIdentificationConfiguration(Repository, "my cic"); cic.CreateRootContainerIfNotExists(); var ac = new AggregateConfiguration(Repository, cata, "myagg"); ac.CreateRootContainerIfNotExists(); cic.RootCohortAggregateContainer.AddChild(ac, 1); var filterToImport = new AggregateFilter(Repository, "MyFilter") { WhereSQL = "true" }; var root = ac.RootFilterContainer; root.AddChild(filterToImport); root.Operation = FilterContainerOperation.OR; root.SaveToDatabase(); // add 2 subcontainers, these should also get cloned and should preserve the Operation correctly root.AddChild(new AggregateFilterContainer(Repository, FilterContainerOperation.AND)); root.AddChild(new AggregateFilterContainer(Repository, FilterContainerOperation.OR)); //there should be no root container Assert.IsNull(sds.RootFilterContainer); //run the command var mgr = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier()); mgr.DisallowInput = true; var cmd = new ExecuteCommandImportFilterContainerTree(mgr, sds, ac); Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible); cmd.Execute(); sds.ClearAllInjections(); Assert.AreEqual(FilterContainerOperation.OR, sds.RootFilterContainer.Operation); Assert.IsNotNull(sds.RootFilterContainer); Assert.AreEqual(1, sds.RootFilterContainer.GetFilters().Length); var subContainers = sds.RootFilterContainer.GetSubContainers(); Assert.AreEqual(2, subContainers.Length); Assert.AreEqual(1, subContainers.Count(e => e.Operation == FilterContainerOperation.AND)); Assert.AreEqual(1, subContainers.Count(e => e.Operation == FilterContainerOperation.OR)); }
protected override void SetUp() { base.SetUp(); aggregate1.RootFilterContainer_ID = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND).ID; aggregate1.SaveToDatabase(); _chiExtractionInformation = aggregate1.AggregateDimensions.Single().ExtractionInformation; _container = (AggregateFilterContainer)aggregate1.RootFilterContainer; _filter = new AggregateFilter(CatalogueRepository, "folk", _container); }
/// <summary> /// Create an AggregateCalculator for a subscription /// </summary> /// <param name="filter"></param> /// <param name="steppedVariable"></param> /// <returns></returns> public static AggregateCalculatorImpl CreateAggregator(AggregateFilter filter, bool steppedVariable) { AggregatorFactory aci = null; if (Lookup.TryGetValue(filter.AggregateType, out aci)) { AggregateCalculatorImpl retval = aci(); retval.StartTime = filter.StartTime; retval.EndTime = DateTime.MaxValue; retval.ProcessingInterval = filter.ProcessingInterval; retval.Configuration = filter.AggregateConfiguration; retval.SteppedVariable = steppedVariable; return retval; } return null; }
/// <summary> /// Convert to service model /// </summary> /// <param name="model"></param> /// <param name="context"></param> /// <returns></returns> public static AggregateFilterModel ToServiceModel(this AggregateFilter model, ServiceMessageContext context) { if (model == null) { return(null); } return(new AggregateFilterModel { AggregateConfiguration = model.AggregateConfiguration.ToServiceModel(), AggregateTypeId = model.AggregateType.AsString(context), StartTime = model.StartTime == DateTime.MinValue ? (DateTime?)null : model.StartTime, ProcessingInterval = (int)model.ProcessingInterval == 0 ? (double?)null : model.ProcessingInterval }); }
/// <summary> /// Create an AggregateCalculator for a subscription /// </summary> /// <param name="filter"></param> /// <param name="steppedVariable"></param> /// <returns></returns> public static AggregateCalculatorImpl CreateAggregator(AggregateFilter filter, bool steppedVariable) { AggregatorFactory aci = null; if (Lookup.TryGetValue(filter.AggregateType, out aci)) { AggregateCalculatorImpl retval = aci(); retval.StartTime = filter.StartTime; retval.EndTime = DateTime.MaxValue; retval.ProcessingInterval = filter.ProcessingInterval; retval.Configuration = filter.AggregateConfiguration; retval.SteppedVariable = steppedVariable; return(retval); } return(null); }
public void TestImportTree_FromCohortIdentificationConfiguration_ToSelectedDatasets() { var sds = WhenIHaveA <SelectedDataSets>(); var cata = sds.ExtractableDataSet.Catalogue; var cic = new CohortIdentificationConfiguration(Repository, "my cic"); cic.CreateRootContainerIfNotExists(); var ac = new AggregateConfiguration(Repository, cata, "myagg"); ac.CreateRootContainerIfNotExists(); cic.RootCohortAggregateContainer.AddChild(ac, 1); var filterToImport = new AggregateFilter(Repository, "MyFilter") { WhereSQL = "true" }; ac.RootFilterContainer.AddChild(filterToImport); //there should be no root container Assert.IsNull(sds.RootFilterContainer); //run the command var mgr = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier()); mgr.DisallowInput = true; var cmd = new ExecuteCommandImportFilterContainerTree(mgr, sds, ac); Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible); cmd.Execute(); sds.ClearAllInjections(); Assert.IsNotNull(sds.RootFilterContainer); Assert.AreEqual(1, sds.RootFilterContainer.GetFilters().Length); Assert.AreEqual("MyFilter", sds.RootFilterContainer.GetFilters()[0].Name); Assert.AreEqual("true", sds.RootFilterContainer.GetFilters()[0].WhereSQL); Assert.AreNotEqual(filterToImport.GetType(), sds.RootFilterContainer.GetFilters()[0].GetType()); }
/// <summary> /// Creates a table HospitalAdmissions that uses the patient index table <paramref name="joinable"/> to return distinct patients /// who have been hospitalised after receiving an NA test (no time limit) /// </summary> /// <param name="db"></param> /// <param name="people"></param> /// <param name="r"></param> /// <param name="cic"></param> /// <param name="joinable"></param> private AggregateConfiguration SetupPatientIndexTableUser(DiscoveredDatabase db, PersonCollection people, Random r, CohortIdentificationConfiguration cic, JoinableCohortAggregateConfiguration joinable) { var syntax = db.Server.GetQuerySyntaxHelper(); var ac = SetupAggregateConfiguration(db, people, r, cic); var and = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter = new AggregateFilter(CatalogueRepository, "Hospitalised after an NA", and); filter.WhereSQL = syntax.EnsureWrapped("AdmissionDate") + " > " + syntax.EnsureWrapped("SampleDate"); filter.SaveToDatabase(); ac.RootFilterContainer_ID = and.ID; ac.SaveToDatabase(); //ac joins against the joinable new JoinableCohortAggregateConfigurationUse(CatalogueRepository, ac, joinable); return(ac); }
public AggregateFilterUIOptions(AggregateFilter aggregateFilter) : base(aggregateFilter) { var aggregateConfiguration = aggregateFilter.GetAggregate(); if (aggregateConfiguration == null) { throw new Exception("AggregateFilter '" + aggregateFilter + "' (ID=" + aggregateFilter.ID + ") does not belong to any AggregateConfiguration, is it somehow an orphan?"); } //it part of an AggregateConfiguration so get the same factory that is used by AggregateEditorUI to tell us about the globals and the columns var options = new AggregateBuilderOptionsFactory().Create(aggregateConfiguration); _globals = options.GetAllParameters(aggregateConfiguration); //get all the tables _tables = aggregateConfiguration.Catalogue.GetTableInfoList(true); //but also add the ExtractionInformations and AggregateDimensions - in the case of PatientIndex table join usages (duplicates are ignored by _autoCompleteProvider) _columns = options.GetAvailableWHEREColumns(aggregateConfiguration); }
/// <summary> /// Filters a query /// </summary> /// <param name="query">The query to filter</param> /// <param name="filter">The filter</param> /// <returns>The filtered queryable</returns> protected virtual IQueryable <TRoot> Filter(IQueryable <TRoot> query, AggregateFilter filter) { Validate.IsNotNull(filter); if (filter.CreatedRange != null) { var startDate = filter.CreatedRange.StartDate; var endDate = filter.CreatedRange.EndDate.AddDays(1); query = query.Where(_ => _.DateCreated >= startDate && _.DateCreated < endDate); } if (filter.ModifiedRange != null) { var startDate = filter.ModifiedRange.StartDate; var endDate = filter.ModifiedRange.EndDate.AddDays(1); query = query.Where(_ => _.DateModified >= startDate && _.DateModified < endDate); } return(query); }
private IFilter CreateFilter(AggregateConfiguration graph, string name, string whereSql) { AggregateFilterContainer container; if (graph.RootFilterContainer_ID == null) { container = new AggregateFilterContainer(_repos.CatalogueRepository, FilterContainerOperation.AND); graph.RootFilterContainer_ID = container.ID; graph.SaveToDatabase(); } else { container = graph.RootFilterContainer; } var filter = new AggregateFilter(_repos.CatalogueRepository, name, container); filter.WhereSQL = whereSql; filter.SaveToDatabase(); return(filter); }
public void QueryBuilderTest_JoinableCloning() { var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated")); aggregate2.AddDimension(anotherCol); //make aggregate 2 a joinable var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2); joinable2.AddUser(aggregate1); string expectedTableAlias = "ix" + joinable2.ID; var filterContainer1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filterContainer2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter1 = new AggregateFilter(CatalogueRepository, "Within 1 year of event", filterContainer1); var filter2 = new AggregateFilter(CatalogueRepository, "DateAfter2001", filterContainer2); filter1.WhereSQL = string.Format("ABS(DATEDIFF(year, {0}.dtCreated, [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].dtCreated)) <= 1", expectedTableAlias); filter1.SaveToDatabase(); filter2.WhereSQL = "dtCreated > '2001-01-01'"; filter2.SaveToDatabase(); aggregate1.RootFilterContainer_ID = filterContainer1.ID; aggregate1.SaveToDatabase(); aggregate2.RootFilterContainer_ID = filterContainer2.ID; aggregate2.SaveToDatabase(); //add the first aggregate to the configuration rootcontainer.AddChild(aggregate1, 1); var globalParameter = new AnyTableSqlParameter(CatalogueRepository, cohortIdentificationConfiguration, "DECLARE @fish varchar(50)"); globalParameter.Comment = "Comments for the crazies"; globalParameter.Value = "'fishes'"; globalParameter.SaveToDatabase(); var builder = new CohortQueryBuilder(cohortIdentificationConfiguration); try { var clone = cohortIdentificationConfiguration.CreateClone(new ThrowImmediatelyCheckNotifier()); var cloneBuilder = new CohortQueryBuilder(clone); string origSql = builder.SQL; string cloneOrigSql = cloneBuilder.SQL; Console.WriteLine("//////////////////////////////////////////////VERBATIM//////////////////////////////////////////////"); Console.WriteLine(origSql); Console.WriteLine(cloneOrigSql); Console.WriteLine("//////////////////////////////////////////////END VERBATIM//////////////////////////////////////////////"); var builderSql = Regex.Replace(Regex.Replace(origSql, "cic_[0-9]+_", ""), "ix[0-9]+", "ix"); var cloneBuilderSql = Regex.Replace(Regex.Replace(cloneOrigSql, "cic_[0-9]+_", ""), "ix[0-9]+", "ix").Replace("(Clone)", "");//get rid of explicit ix53 etc for the comparison Console.WriteLine("//////////////////////////////////////////////TEST COMPARISON IS//////////////////////////////////////////////"); Console.WriteLine(builderSql); Console.WriteLine(cloneBuilderSql); Console.WriteLine("//////////////////////////////////////////////END COMPARISON//////////////////////////////////////////////"); Assert.AreEqual(builderSql, cloneBuilderSql); ////////////////Cleanup Database////////////////////////////// //find the WHERE logic too var containerClone = clone.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively() //get all the aggregates .Union(clone.GetAllJoinables().Select(j => j.AggregateConfiguration)) //including the joinables .Where(a => a.RootFilterContainer_ID != null) //that have WHERE sql .Select(ag => ag.RootFilterContainer); //grab their containers so we can clean them SetUp ((IDeleteable)clone.GetAllParameters()[0]).DeleteInDatabase(); clone.DeleteInDatabase(); //delete the WHERE logic too foreach (AggregateFilterContainer c in containerClone) { c.DeleteInDatabase(); } } finally { rootcontainer.RemoveChild(aggregate1); filter1.DeleteInDatabase(); filter2.DeleteInDatabase(); filterContainer1.DeleteInDatabase(); filterContainer2.DeleteInDatabase(); joinable2.Users[0].DeleteInDatabase(); joinable2.DeleteInDatabase(); globalParameter.DeleteInDatabase(); } }
public void QueryBuilderTest_AdditionalColumn() { var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated")); aggregate2.AddDimension(anotherCol); //make aggregate 2 a joinable var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2); joinable2.AddUser(aggregate1); string expectedTableAlias = "ix" + joinable2.ID; var filterContainer1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filterContainer2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter1 = new AggregateFilter(CatalogueRepository, "Within 1 year of event", filterContainer1); var filter2 = new AggregateFilter(CatalogueRepository, "DateAfter2001", filterContainer2); filter1.WhereSQL = string.Format("ABS(DATEDIFF(year, {0}.dtCreated, [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].dtCreated)) <= 1", expectedTableAlias); filter1.SaveToDatabase(); filter2.WhereSQL = "dtCreated > '2001-01-01'"; filter2.SaveToDatabase(); aggregate1.RootFilterContainer_ID = filterContainer1.ID; aggregate1.SaveToDatabase(); aggregate2.RootFilterContainer_ID = filterContainer2.ID; aggregate2.SaveToDatabase(); var builder = new CohortQueryBuilder(aggregate1, null); Console.WriteLine(builder.SQL); try { using (var con = (SqlConnection)Database.Server.GetConnection()) { con.Open(); var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader(); //can read at least one row Assert.IsTrue(dbReader.Read()); } //after joinables Assert.AreEqual( CollapseWhitespace( string.Format( @"/*cic_{1}_UnitTestAggregate1*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] LEFT Join ( /*cic_{1}_UnitTestAggregate2*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi], [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[dtCreated] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] WHERE ( /*DateAfter2001*/ dtCreated > '2001-01-01' ) ){0} on [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] = {0}.chi WHERE ( /*Within 1 year of event*/ ABS(DATEDIFF(year, {0}.dtCreated, [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].dtCreated)) <= 1 )", expectedTableAlias, cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.SQL)); } finally { filter1.DeleteInDatabase(); filter2.DeleteInDatabase(); filterContainer1.DeleteInDatabase(); filterContainer2.DeleteInDatabase(); joinable2.Users[0].DeleteInDatabase(); joinable2.DeleteInDatabase(); } }
public void TestGettingAggregateSQLFromEntirity_Filter_IsDisabled() { CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration); //setup a filter (all filters must be in a container so the container is a default AND container) var AND1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter1_1 = new AggregateFilter(CatalogueRepository, "filter1_1", AND1); var filter1_2 = new AggregateFilter(CatalogueRepository, "filter1_2", AND1); var AND2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter2_1 = new AggregateFilter(CatalogueRepository, "filter2_1", AND2); var filter2_2 = new AggregateFilter(CatalogueRepository, "filter2_2", AND2); //Filters must belong to containers BEFORE parameter creation //Make aggregate1 use the filter set we just setup aggregate1.RootFilterContainer_ID = AND1.ID; aggregate1.SaveToDatabase(); //Make aggregate3 use the other filter set we just setup aggregate2.RootFilterContainer_ID = AND2.ID; aggregate2.SaveToDatabase(); //set the order so that 2 comes before 1 rootcontainer.AddChild(aggregate2, 1); rootcontainer.AddChild(aggregate1, 5); filter2_2.IsDisabled = true; filter2_2.SaveToDatabase(); //give the filter an implicit parameter requiring bit of SQL foreach (var filter in new IFilter[] { filter1_1, filter1_2, filter2_1, filter2_2 }) { filter.WhereSQL = "@bob = 'bob'"; filter.SaveToDatabase(); //get it to create the parameters for us new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); //get the parameter it just created, set it's value and save it var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "'Boom!'"; param.ParameterSQL = "DECLARE @bob AS varchar(10);"; //change the values of the parameters if (filter.Equals(filter2_1) || Equals(filter, filter2_2)) { param.Value = "'Grenades Are Go'"; } param.SaveToDatabase(); } Console.WriteLine(builder.SQL); try { Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @bob AS varchar(10); SET @bob='Grenades Are Go'; DECLARE @bob_2 AS varchar(10); SET @bob_2='Boom!'; ( /*cic_{0}_UnitTestAggregate2*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] WHERE ( /*filter2_1*/ @bob = 'bob' ) EXCEPT /*cic_{0}_UnitTestAggregate1*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] WHERE ( /*filter1_1*/ @bob_2 = 'bob' AND /*filter1_2*/ @bob_2 = 'bob' ) ) ", cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.SQL)); } finally { filter2_2.IsDisabled = false; filter2_2.SaveToDatabase(); rootcontainer.RemoveChild(aggregate2); rootcontainer.RemoveChild(aggregate1); filter1_1.DeleteInDatabase(); filter1_2.DeleteInDatabase(); filter2_1.DeleteInDatabase(); filter2_2.DeleteInDatabase(); AND1.DeleteInDatabase(); AND2.DeleteInDatabase(); } }
public void TestGettingAggregateSQLFromEntirity_IncludingParametersAtTop() { CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration); //setup a filter (all filters must be in a container so the container is a default AND container) var AND = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter = new AggregateFilter(CatalogueRepository, "hithere", AND); //give the filter an implicit parameter requiring bit of SQL filter.WhereSQL = "1=@abracadabra"; filter.SaveToDatabase(); //Make aggregate1 use the filter we just setup (required to happen before parameter creator gets hit because otherwise it won't know the IFilter DatabaseType because IFilter is an orphan at the moment) aggregate1.RootFilterContainer_ID = AND.ID; aggregate1.SaveToDatabase(); //get it to create the parameters for us new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); //get the parameter it just created, set it's value and save it var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "1"; param.ParameterSQL = "DECLARE @abracadabra AS int;"; param.SaveToDatabase(); //set the order so that 2 comes before 1 rootcontainer.AddChild(aggregate2, 1); rootcontainer.AddChild(aggregate1, 5); try { Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @abracadabra AS int; SET @abracadabra=1; ( /*cic_{0}_UnitTestAggregate2*/ SELECT distinct [" + _scratchDatabaseName + @"]..[BulkData].[chi] FROM [" + _scratchDatabaseName + @"]..[BulkData] EXCEPT /*cic_{0}_UnitTestAggregate1*/ SELECT distinct [" + _scratchDatabaseName + @"]..[BulkData].[chi] FROM [" + _scratchDatabaseName + @"]..[BulkData] WHERE ( /*hithere*/ 1=@abracadabra ) ) ", cohortIdentificationConfiguration.ID)) , CollapseWhitespace(builder.SQL)); CohortQueryBuilder builder2 = new CohortQueryBuilder(aggregate1, null); Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @abracadabra AS int; SET @abracadabra=1; /*cic_{0}_UnitTestAggregate1*/ SELECT distinct [" + _scratchDatabaseName + @"]..[BulkData].[chi] FROM [" + _scratchDatabaseName + @"]..[BulkData] WHERE ( /*hithere*/ 1=@abracadabra )", cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder2.SQL)); string selectStar = new CohortQueryBuilder(aggregate1, null).GetDatasetSampleSQL(); Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @abracadabra AS int; SET @abracadabra=1; /*cic_{0}_UnitTestAggregate1*/ SELECT TOP 1000 * FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] WHERE ( /*hithere*/ 1=@abracadabra )" , cohortIdentificationConfiguration.ID)), CollapseWhitespace(selectStar)); } finally { filter.DeleteInDatabase(); AND.DeleteInDatabase(); rootcontainer.RemoveChild(aggregate1); rootcontainer.RemoveChild(aggregate2); } }
public ItemSpiral(Item[] itemsToDisplay, AggregateFilter filter, Func <Item, Dictionary <Filter, bool>, ItemBehaviour> itemBuilder) { this.itemsToDisplay = itemsToDisplay; this.filter = filter; this.itemBuilder = itemBuilder; }
public ItemSpiral(Item[] itemsToDisplay, Filter filter) { this.itemsToDisplay = itemsToDisplay; this.filter = new AggregateFilter(new Filter[] { filter }); this.itemBuilder = null; }
public ItemSpiral(Item[] itemsToDisplay, AggregateFilter filter) { this.itemsToDisplay = itemsToDisplay; this.filter = filter; this.itemBuilder = null; }
/// <summary> /// Filters and paginates a query for asynchronous operations /// </summary> /// <param name="query">The query to filter and paginate</param> /// <param name="filter">The filter</param> /// <returns>An asynchronous paged collection</returns> protected virtual IAsyncPagedCollection <TRoot> FilterAndPaginateAsync(IQueryable <TRoot> query, AggregateFilter filter) { Validate.IsNotNull(filter); query = Filter(query, filter); return(PaginateAsync(query, filter.PageSize)); }
/// <summary> /// Initializes the aggregate calculator with a filter. /// </summary> public virtual void Initialize(AggregateFilter filter) { m_startTime = filter.StartTime; m_processingInterval = filter.ProcessingInterval; }
public void CohortIdentificationConfiguration_CloneEntirely() { //set the order so that 2 comes before 1 rootcontainer.AddChild(aggregate1, 5); rootcontainer.AddChild(container1); container1.AddChild(aggregate2, 1); container1.AddChild(aggregate3, 2); //create a filter too var container = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.OR); aggregate1.RootFilterContainer_ID = container.ID; aggregate1.SaveToDatabase(); var filter = new AggregateFilter(CatalogueRepository, "MyFilter", container); filter.WhereSQL = "sex=@sex"; new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); filter.SaveToDatabase(); //with a parameter too var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "'M'"; param.SaveToDatabase(); cohortIdentificationConfiguration.RootCohortAggregateContainer_ID = rootcontainer.ID; cohortIdentificationConfiguration.SaveToDatabase(); try { var clone = cohortIdentificationConfiguration.CreateClone(new ThrowImmediatelyCheckNotifier()); //the objects should be different Assert.AreNotEqual(cohortIdentificationConfiguration.ID, clone.ID); Assert.IsTrue(clone.Name.EndsWith("(Clone)")); Assert.AreNotEqual(clone.RootCohortAggregateContainer_ID, cohortIdentificationConfiguration.RootCohortAggregateContainer_ID); Assert.IsNotNull(clone.RootCohortAggregateContainer_ID); var beforeSQL = new CohortQueryBuilder(cohortIdentificationConfiguration, null).SQL; var cloneSQL = new CohortQueryBuilder(clone, null).SQL; beforeSQL = Regex.Replace(beforeSQL, "cic_[0-9]+_", ""); cloneSQL = Regex.Replace(cloneSQL, "cic_[0-9]+_", ""); //the SQL should be the same for them Assert.AreEqual(beforeSQL, cloneSQL); var containerClone = clone.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively() .Where(a => a.RootFilterContainer_ID != null) .Select(ag => ag.RootFilterContainer).Single(); Assert.AreNotEqual(container, containerClone); //cleanup phase clone.DeleteInDatabase(); containerClone.DeleteInDatabase(); } finally { rootcontainer.RemoveChild(aggregate1); container1.RemoveChild(aggregate2); container1.RemoveChild(aggregate3); filter.DeleteInDatabase(); container.DeleteInDatabase(); } }
public void CloneChildWithFilter_IDsDifferent() { //aggregate 1 is now a normal non cohort aggregate var container = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.OR); aggregate1.CountSQL = "count(*)"; aggregate1.RootFilterContainer_ID = container.ID; aggregate1.SaveToDatabase(); //with filters var filter = new AggregateFilter(CatalogueRepository, "MyFilter", container); filter.WhereSQL = "sex=@sex"; //and parameters new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); filter.SaveToDatabase(); var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "'M'"; param.SaveToDatabase(); //we are importing this graph aggregate as a new cohort identification aggregate var clone = cohortIdentificationConfiguration.ImportAggregateConfigurationAsIdentifierList(aggregate1, null); //since its a cohort aggregate it should be identical to the origin Aggregate except it has a different ID and no count SQL Assert.AreEqual(clone.CountSQL, null); //get the original sql var aggregateSql = aggregate1.GetQueryBuilder().SQL; try { Assert.AreNotEqual(clone.ID, aggregate1.ID); Assert.AreNotEqual(clone.RootFilterContainer_ID, aggregate1.RootFilterContainer_ID); var cloneContainer = clone.RootFilterContainer; var cloneFilter = cloneContainer.GetFilters().Single(); Assert.AreNotEqual(cloneContainer.ID, container.ID); Assert.AreNotEqual(cloneFilter.ID, filter.ID); var cloneParameter = (AggregateFilterParameter)cloneFilter.GetAllParameters().Single(); Assert.AreNotEqual(cloneParameter.ID, param.ID); //it has a different ID and is part of an aggregate filter container (It is presumed to be involved with cohort identification cohortIdentificationConfiguration) which means it will be called cic_X_ string cohortAggregateSql = new CohortQueryBuilder(clone, null, null).SQL; //the basic aggregate has the filter, parameter and group by Assert.AreEqual(CollapseWhitespace( string.Format( @"DECLARE @sex AS varchar(50); SET @sex='M'; /*cic_{0}_UnitTestAggregate1*/ SELECT [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi], count(*) FROM [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData] WHERE ( /*MyFilter*/ sex=@sex ) group by [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi] order by [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi]", cohortIdentificationConfiguration.ID)), CollapseWhitespace(aggregateSql)); //the expected differences are //1. should not have the count //2. should not have the group by //3. should be marked with the cic comment with the ID matching the CohortIdentificationConfiguration.ID //4. should have a distinct on the identifier column Assert.AreEqual( @"DECLARE @sex AS varchar(50); SET @sex='M'; /*cic_" + cohortIdentificationConfiguration.ID + @"_UnitTestAggregate1*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData] WHERE ( /*MyFilter*/ sex=@sex )", cohortAggregateSql); clone.RootFilterContainer.DeleteInDatabase(); container.DeleteInDatabase(); } finally { clone.DeleteInDatabase(); } }
private static IMongoQuery GetQuery(WebSites webSite, ContentTypeQuota quota, AggregateFilter filters, out string queryUniqueString) { IMongoQuery query = null; List <IMongoQuery> queries = new List <IMongoQuery>(); StringBuilder sb = new StringBuilder("w" + ((int)webSite).ToString()); if (quota != null) { sb.Append("q" + quota.Id.ToString()); foreach (var filter in quota.Values.Values) { queries.Add(Query.EQ(filter.MongoName, GetFilterValue(filter.Value, filter.IsString))); Log.Debug("ProductDataAccess.GetQuery", "quota filter", queries.Last()); } } if (filters != null && filters.Filters != null) { //TODO Replace GetHashCode and Fibonacci approach by other way string filterStr = filters.ToString(); sb.Append(filterStr.GetHashCode()); for (int i = 0; i < Fibonacci.Length; i++) { if (filterStr.Length < Fibonacci[i]) { sb.Append(filterStr[Fibonacci[i]]); } else { break; } } queries.AddRange(GetFilterQuery(filters)); } queryUniqueString = sb.ToString(); Log.Debug("ProductDataAccess.GetQuery", "queryUniqueString", queryUniqueString); // only active products queries.Add(Query.EQ("ItemStatus", 1)); // final query var webSiteId = (int)webSite; queries.Add(Query.EQ(WebSiteIdField, new BsonInt32(webSiteId))); query = Query.And(queries); Log.Debug("ProductDataAccess.GetQuery", "Final query", query.ToJson()); return(query); }