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();
}
}
/// <summary>
/// Initializes the aggregate calculator with a filter.
/// </summary>
public virtual void Initialize(AggregateFilter filter)
{
m_startTime = filter.StartTime;
m_processingInterval = filter.ProcessingInterval;
}
/// <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 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);
}
