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