private IList <KeyValuePair <int?, double> > RunQuery(IHqlQuery query, AggregateMethods aggregation, string propertyPath)
{
DefaultHqlQuery defaultQuery = query as DefaultHqlQuery;
var queryHql = defaultQuery.ToHql(true);
var hql = @"select ticketTable.{0}.Id as GroupKey, COUNT(*) as GroupValue
from Orchard.ContentManagement.Records.ContentItemVersionRecord as kiv
join kiv.ContentItemRecord as ki
join ki.TicketPartRecord as ticketTable
where (kiv.Published = True) AND kiv.Id in ({1})
group by ticketTable.{0}.Id";
hql = string.Format(CultureInfo.InvariantCulture, hql, propertyPath, queryHql);
var session = this.sessionLocator.Value.For(typeof(ContentItem));
var result = session
.CreateQuery(hql)
.SetCacheable(false)
.SetResultTransformer(Transformers.AliasToEntityMap)
.List <IDictionary>();
List <KeyValuePair <int?, double> > returnValue = new List <KeyValuePair <int?, double> >();
foreach (var group in result)
{
double value = double.Parse(group["GroupValue"].ToString());
int? key = group["GroupKey"] != null ? (int?)int.Parse(group["GroupKey"].ToString()) : null;
returnValue.Add(new KeyValuePair <int?, double>(key, value));
}
return(returnValue);
}
public IEnumerable <int> LatestUsersInActivityStreamFilteredByGivenQuery(IHqlQuery contentQuery, int count)
{
if (contentQuery == null)
{
throw new ArgumentNullException("contentQuery");
}
contentQuery = this.ApplyContentPermissionFilter(contentQuery);
DefaultHqlQuery defaultQuery = contentQuery as DefaultHqlQuery;
var hql = defaultQuery.ToHql(true);
hql = string.Format(@"
select u.Id, max(c.Id) as mId from
Orchard.CRM.Core.Models.ActivityStreamRecord as c join c.User as u
WHERE c.RelatedVersion.Id in ({0})
group by u.Id ", hql);
var session = this.transactionManager.GetSession();
var query = session
.CreateQuery(hql)
.SetResultTransformer(Transformers.AliasToEntityMap)
.SetCacheable(false)
.SetFirstResult(0)
.SetMaxResults(count).List <IDictionary>();
return(query.Select(c => (int)c["0"]).ToList());
}
private IEnumerable <ActivityStreamRecord> RunQuery(IHqlQuery[] contentQueries, int pageId, int pageSize, int?userId)
{
List <string> queries = new List <string>();
foreach (var contentQuery in contentQueries)
{
DefaultHqlQuery defaultQuery = contentQuery as DefaultHqlQuery;
queries.Add(defaultQuery.ToHql(true));
}
queries = queries.Select(c => string.Format(CultureInfo.InvariantCulture, "c.RelatedVersion.Id in ({0})", c)).ToList();
List <string> finalWhereConditions = new List <string> {
string.Format(CultureInfo.InvariantCulture, "({0})", string.Join(" or ", queries))
};
if (userId.HasValue)
{
finalWhereConditions.Add(string.Format(CultureInfo.InvariantCulture, "c.User.Id = {0}", userId.Value.ToString(CultureInfo.InvariantCulture)));
}
string condition = string.Join(" and ", finalWhereConditions);
var mainHql = string.Format("select c from Orchard.CRM.Core.Models.ActivityStreamRecord as c WHERE {0} order by c.Id desc", condition);
var session = this.transactionManager.GetSession();
var query = session
.CreateQuery(mainHql)
.SetResultTransformer(Transformers.AliasToEntityMap)
.SetCacheable(false)
.SetFirstResult(pageId * pageSize)
.SetMaxResults(pageSize).List <IDictionary>();
return(query.Select(c => (ActivityStreamRecord)c["0"]).ToList());
}
private int Count(IHqlQuery[] contentQueries, int?userId)
{
List <string> queries = new List <string>();
foreach (var contentQuery in contentQueries)
{
DefaultHqlQuery defaultQuery = contentQuery as DefaultHqlQuery;
queries.Add(defaultQuery.ToHql(true));
}
queries = queries.Select(c => string.Format(CultureInfo.InvariantCulture, "c.RelatedVersion.Id in ({0})", c)).ToList();
List <string> finalWhereConditions = new List <string> {
string.Format(CultureInfo.InvariantCulture, "({0})", string.Join(" or ", queries))
};
if (userId.HasValue)
{
finalWhereConditions.Add(string.Format(CultureInfo.InvariantCulture, "c.User.Id = {0}", userId.Value.ToString(CultureInfo.InvariantCulture)));
}
string condition = string.Join(" and ", finalWhereConditions);
var mainHql = string.Format("select COUNT(c) from Orchard.CRM.Core.Models.ActivityStreamRecord as c WHERE {0}", condition);
var session = this.transactionManager.GetSession();
return(Convert.ToInt32(session.CreateQuery(mainHql)
.SetCacheable(true)
.UniqueResult()));
}
public Collection <KeyValuePair <int?, int> > GetCount(IHqlQuery hglQuery, string entityPath, string groupingProperty)
{
DefaultHqlQuery defaultQuery = hglQuery as DefaultHqlQuery;
var hql = defaultQuery.ToHql(true);
hql = string.Format("select count(*), k.{0} from Orchard.ContentManagement.Records.ContentItemVersionRecord as c join c.ContentItemRecord as d join d.{1} as k where c.Id in ({2}) and (c.Published = True) group by k.{0}", groupingProperty, entityPath, hql);
var session = this.sessionLocator.Value.For(typeof(ContentItem));
var query = session
.CreateQuery(hql)
.SetCacheable(false)
.SetResultTransformer(Transformers.AliasToEntityMap)
.List <IDictionary>();
Collection <KeyValuePair <int?, int> > result = new Collection <KeyValuePair <int?, int> >();
foreach (var group in query)
{
int value = int.Parse(group["0"].ToString());
int?key = group["1"] != null ? (int?)int.Parse(group["1"].ToString()) : null;
result.Add(new KeyValuePair <int?, int>(key, value));
}
return(result);
}
public void ApplyStatusFilter(SortCriterionContext context, string partName, string propertyName)
{
Action <IAliasFactory> alias = null;
// if it needs a join
if (!string.IsNullOrEmpty(partName))
{
alias = x =>
{
x.ContentPartRecord <TicketPartRecord>();
DefaultAliasFactory defaultFactory = x as DefaultAliasFactory;
// left outer join with the property
TicketFieldsSortCriteria.DefaultHqlQueryBindCriteriaByAlias.Invoke(context.Query, new object[] { defaultFactory.Current, partName, partName, "left outer join", null });
// it must be recalled again, in order to set the partName as the current Alias
x.Property(partName, partName);
};
}
else
{
alias = x => x.ContentPartRecord <TicketPartRecord>();
}
context.Query.Join(alias);
bool ascending = (bool)context.State.Sort;
DefaultHqlQuery query = context.Query as DefaultHqlQuery;
context.Query = ascending ?
context.Query.OrderBy(alias, x => x.Asc(propertyName)) :
context.Query.OrderBy(alias, x => x.Desc(propertyName));
}
public void ApplySortCriteria(SortCriterionContext context, string propertyName)
{
Action <IAliasFactory> alias = x => x.ContentPartRecord <MilestonePartRecord>();
context.Query.Join(alias);
bool ascending = (bool)context.State.Sort;
DefaultHqlQuery query = context.Query as DefaultHqlQuery;
context.Query = ascending ?
context.Query.OrderBy(alias, x => x.Asc(propertyName)) :
context.Query.OrderBy(alias, x => x.Desc(propertyName));
}
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 DefaultAliasFactory(DefaultHqlQuery query)
{
this._query = query;
Current = _query.BindFromPath();
}
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);
}
