public void GetQuery(IHqlQuery query, QueryRecord queryRecord)
{
var availableFilters = DescribeFilters().ToList();
foreach (var group in queryRecord.FilterGroups)
{
foreach (var filter in group.Filters)
{
var filterContext = new FilterContext()
{
Query = query,
State = JsonConvert.DeserializeObject(filter.State)
};
string category = filter.Category;
string property = filter.PropertyName;
var descriptor = availableFilters.FirstOrDefault(x => x.Category == category && x.Property == property);
if (descriptor == null)
{
continue;
}
descriptor.Filter(filterContext);
}
}
}
public IEnumerable <AggregationResult> RunEnumerationAggregation(IHqlQuery query, AggregateMethods aggregateMethod, string fieldName, string partName)
{
string groupKey = "field.Value";
string groupValue = string.Empty;
switch (aggregateMethod)
{
case AggregateMethods.Count:
groupValue = "COUNT(*)";
break;
default:
throw new ArgumentException("Aggregate method is not provided");
}
var result = this.RunQuery(query, fieldName, partName, groupKey, groupValue, "StringFieldIndexRecords");
List <AggregationResult> returnValue = new List <AggregationResult>();
foreach (var group in result)
{
double value = double.Parse(group["GroupValue"].ToString());
string key = group["GroupKey"] != null ? group["GroupKey"].ToString() : string.Empty;
string keyLabel = string.IsNullOrEmpty(key) || key == T("Select an option").Text ? T("[{0}]", "Null").Text : key;
AggregationResult aggregationResult = new AggregationResult
{
AggregationValue = value,
Label = keyLabel,
GroupingField = key
};
returnValue.Add(aggregationResult);
}
return(returnValue);
}
private IList <IDictionary> RunQuery(IHqlQuery query, string fieldName, string partName, string groupKey, string groupValue, string fieldTableName)
{
DefaultHqlQuery defaultQuery = query as DefaultHqlQuery;
var queryHql = defaultQuery.ToHql(true);
var hql = @"select {0} as GroupKey, {1} as GroupValue
from Orchard.ContentManagement.Records.ContentItemVersionRecord as kiv
join kiv.ContentItemRecord as ki
join ki.FieldIndexPartRecord as fieldIndexPartRecord
join fieldIndexPartRecord.{5} as field
where (field.PropertyName = '{2}.{3}.') AND (kiv.Published = True) AND kiv.Id in ({4})
group by {0}";
hql = string.Format(CultureInfo.InvariantCulture, hql, groupKey, groupValue, partName, fieldName, queryHql, fieldTableName);
var session = this.sessionLocator.Value.For(typeof(ContentItem));
return(session
.CreateQuery(hql)
.SetCacheable(false)
.SetResultTransformer(Transformers.AliasToEntityMap)
.List <IDictionary>());
}
public int Count()
{
IHqlQuery hqlQuery = HqlQuery();
//hqlQuery.Where(x => x.Named("a"), y => y.Eq("UserName", "admin"));
QueryRecord record = new QueryRecord();
FilterGroupRecord group = new FilterGroupRecord();
var state = new
{
Description = "test",
Operator = "Equals",
Value = "phoenix"
};
group.Filters.Add(new FilterRecord()
{
Category = "UserEntity",
PropertyName = "UserName",
State = JsonConvert.SerializeObject(state)
});
record.FilterGroups.Add(group);
_hqlQueryManager.GetQuery(hqlQuery, record);
return(hqlQuery.Count());
}
public void ApplyFilter(dynamic context)
{
var currentUser = _orchardServices.WorkContext.CurrentUser;
if (currentUser != null)
{
List <Int32> elencoIdQuestionnaires = _userAnswersRecord.Fetch(x => x.User_Id == currentUser.Id).Select(y => y.QuestionnairePartRecord_Id).Distinct().ToList();
//smarco tutte le lingue
//foreach (Int32 idquestion in elencoIdQuestionnaires) {
// _orchardServices.ContentManager.Query<QuestionnairePart,QuestionnairePartRecord>().Where(x=>x.Id==
//}
IHqlQuery query = context.Query;
query.Join(alias => alias.ContentPartRecord(typeof(QuestionnairePartRecord)));
foreach (var id in elencoIdQuestionnaires)
{
var termId = id;
Action <IAliasFactory> selector =
alias => alias.ContentPartRecord <QuestionnairePartRecord>();
Action <IHqlExpressionFactory> filter = x => x.NotEqProperty("Id", id.ToString());
context.Query.Where(selector, filter);
}
}
else
{
IHqlQuery query = context.Query;
query.Join(alias => alias.ContentPartRecord(typeof(QuestionnairePartRecord)));
Action <IAliasFactory> selector =
alias => alias.ContentPartRecord <QuestionnairePartRecord>();
Action <IHqlExpressionFactory> filter = x => x.EqProperty("Id", "-100");
context.Query.Where(selector, filter);
}
//var defaultHqlQuery = query as DefaultHqlQuery;
//query = query.ForType("Questionnarie").ForVersion(VersionOptions.Published);
//var defaultHqlQuery = query as DefaultHqlQuery;
//var fiJoins = typeof(DefaultHqlQuery).GetField("_joins", BindingFlags.Instance | BindingFlags.NonPublic);
//var joins = fiJoins.GetValue(defaultHqlQuery) as List<Tuple<IAlias, Join>>;
//joins.Add(new Tuple<IAlias, Join>(new Alias("Laser.Orchard.Questionnaires.Models"), new Join("UserAnswersRecord", "UserAnswers", ",")));
//query.Join(alias => alias.ContentPartRecord(typeof(QuestionnairePartRecord)));
//context.Query = query.Where(
//alias => alias.Named("UserAnswers"),
// // g=>g.Disjunction(
// f => f.Conjunction(
// x => x.EqProperty("QuestionnairePartRecord_Id", "questionnairePartRecord.Id"),
// predicate => predicate.NotEqProperty("User_Id", currentUser.Id.ToString())
// )
// //,
// // x=>x.
// );
//context.Query = query.Where(
//alias => alias.Named("UserAnswers"),
// g => g.Conjunction(
// h => h.EqProperty("QuestionnairePartRecord_Id", "questionnairePartRecord.Id"),
// i => i.Not(
// f => f.Conjunction(
// x => x.EqProperty("QuestionnairePartRecord_Id", "questionnairePartRecord.Id"),
// predicate => predicate.EqProperty("User_Id", currentUser.Id.ToString())
// )
// )
// )
// );
//context.Query = query.Where(y => y.ContentPartRecord<QuestionnairePartRecord>(), x => x.NotEqProperty("Id", currentUser.Id.ToString()));
// Action<IAliasFactory> s = alias => alias.ContentPartRecord<QuestionnairePartRecord>();
// Action<IHqlExpressionFactory> f = y => y.NotEqProperty("UserPartRecord.Id", currentUser.Id.ToString());
// context.Query.Where(s, f);
// query.Join(alias => alias.ContentPartRecord("UserPartRecord"));
// query.Join(alias=>alias.
// QuestionnairePartRecord_Id
// context.Query = context.Query.Join(x => x.ContentPartRecord(typeof(QuestionnairePartRecord)));
// context.Query = query.Where(x => x.ContentPartRecord<UserPartRecord>(),x=>x.Ge("Id",0));ù
// query.Join(alias => alias.ContentItem());
// var defaultHqlQuery = query as DefaultHqlQuery;
// context.Query = query.ForType("Push");
// context.Query = context.Query.Join(x => x.ContentPartRecord(typeof(UserDeviceRecord)));
// context.Query = query.Where(x => x.ContentPartRecord<UserPartRecord>(),x=>x.Ge("Id",0));ù
// query.ForPart<UserPart>().ForVersion(VersionOptions.Latest);
//context.Query = context.Query.Join(x => x.ContentPartRecord(typeof(UserPartRecord)));
//context.Query = context.Query.Where(y => y.ContentPartRecord(typeof(UserPartRecord)), y => y.Ge("Id", 0));
// context.Query = query.Where(x => x.ContentItem(), x => x.Eq("Id", HardCodedId));
// query.Join(alias => alias.ContentPartRecord());
// Action<IHqlExpressionFactory> joinOn = predicate => predicate.EqProperty("ContentItemRecord", "ci");
//query = query.Where(
//alias => alias.Named("UserDevice"),
//joinOn
//);
return;
}
/// <summary>
/// Applies a partitioned IN() clause on the query, taken the collection of IDs. For each partition a new IN() is created, thus the
/// limitation on the maximal amount of arguments for IN() can be circumvented.
/// </summary>
/// <param name="query"></param>
/// <param name="ids"></param>
/// <param name="partitionSize"></param>
public static void WhereIdIn(this IHqlQuery query, IEnumerable <int> ids, int partitionSize = 1000)
{
query.Where(a => a.ContentItem(), p => p.PartitionedExpression((e, i) => e.In("Id", i), ids, partitionSize));
}
public static IList <KeyValuePair <int?, double> > RunGroupByQuery(ITransactionManager transactionManager, IHqlQuery query, AggregateMethods aggregation, string propertyPath, string state, string propertyAlias)
{
DefaultHqlQuery defaultQuery = query as DefaultHqlQuery;
var queryHql = defaultQuery.ToHql(true);
string aggregateMethod = string.Empty;
switch (aggregation)
{
case AggregateMethods.Average:
aggregateMethod = "AVE";
break;
case AggregateMethods.Sum:
aggregateMethod = "SUM";
break;
case AggregateMethods.Minimum:
aggregateMethod = "MIN";
break;
case AggregateMethods.Maximum:
aggregateMethod = "MAX";
break;
case AggregateMethods.Count:
default:
aggregateMethod = "COUNT";
break;
}
;
string aggregateField = "*";
if (!string.IsNullOrEmpty(state))
{
dynamic stateObj = JObject.Parse(System.Web.HttpUtility.UrlDecode(state));
aggregateField = "ki." + stateObj.AggregationField;
}
var hql = @"select {4}.Id as GroupKey, {2}({3}) as GroupValue
from Orchard.ContentManagement.Records.ContentItemVersionRecord as kiv
join kiv.ContentItemRecord as ki
join ki.{0} as {4}
where (kiv.Published = True) AND kiv.Id in ({1})
group by {4}.Id";
hql = string.Format(CultureInfo.InvariantCulture, hql, propertyPath, queryHql, aggregateMethod, aggregateField, propertyAlias);
var session = transactionManager.GetSession();
var result = session.CreateQuery(hql)
.SetCacheable(false)
.SetResultTransformer(NHibernate.Transform.Transformers.AliasToEntityMap)
.List <IDictionary>();
List <KeyValuePair <int?, double> > returnValue = new List <KeyValuePair <int?, double> >();
foreach (var group in result)
{
double value = group["GroupValue"] != null && !string.IsNullOrEmpty(group["GroupValue"].ToString()) ?
double.Parse(group["GroupValue"].ToString()) :
0;
int?key = group["GroupKey"] != null ? (int?)int.Parse(group["GroupKey"].ToString()) : null;
returnValue.Add(new KeyValuePair <int?, double>(key, value));
}
return(returnValue);
}
public IHqlQuery AddSortCriterion(string category, string type, dynamic state, IHqlQuery groupQuery)
{
if (this.availableSortCriteria == null)
{
this.availableSortCriteria = this.DescribeSortCriteria().ToList();
}
var sortCriterionContext = new SortCriterionContext
{
Query = groupQuery,
State = state
};
// look for the specific filter component
var descriptor = availableSortCriteria.SelectMany(x => x.Descriptors).FirstOrDefault(x => x.Category == category && x.Type == type);
// ignore unfound descriptors
if (descriptor == null)
{
return(groupQuery);
}
// apply alteration
descriptor.Sort(sortCriterionContext);
return(sortCriterionContext.Query);
}
