public ActionResult EditPost(ReportViewModel model) { if (!this.services.Authorizer.Authorize(Permissions.ManageQueries, T("Not authorized to list Reports"))) { return(new HttpUnauthorizedResult()); } if (!this.ModelState.IsValid) { model = model ?? new ReportViewModel(); this.FillRelatedData(model); return(this.View("Edit", model)); } ReportRecord report = this.reportRepository.Get(model.ReportId); if (report == null) { if (!this.ModelState.IsValid) { this.ModelState.AddModelError("ReportId", T("There is no report with the given Id").Text); this.FillRelatedData(model); return(this.View("Edit", model)); } } var groupByDescriptor = this.DecodeGroupByCategoryAndType(model.CategoryAndType); if (groupByDescriptor == null) { this.ModelState.AddModelError("CategoryAndType", T("There is no GroupBy field matched with the given parameters").Text); this.FillRelatedData(model); return(this.View("Edit", model)); } AggregateMethods selectedAggregate = (AggregateMethods)model.AggregateMethod; if (!groupByDescriptor.AggregateMethods.Any(c => c == selectedAggregate)) { this.ModelState.AddModelError("AggregateMethod", T("The selected field does't support the selected Aggregate method").Text); this.FillRelatedData(model); return(this.View("Edit", model)); } report.State = model.State; report.Title = model.Title; report.Name = model.Name; report.Query = model.QueryId.HasValue ? new QueryPartRecord { Id = model.QueryId.Value } : null; report.GroupByCategory = groupByDescriptor.Category; report.GroupByType = groupByDescriptor.Type; report.ChartType = model.ChartTypeId; report.AggregateMethod = model.AggregateMethod; this.reportRepository.Update(report); this.reportRepository.Flush(); return(this.RedirectToAction("Index")); }
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 ActionResult CreatePost(ReportViewModel model) { if (!this.services.Authorizer.Authorize(Permissions.ManageQueries, T("Not authorized to list Reports"))) { return(new HttpUnauthorizedResult()); } if (!this.ModelState.IsValid) { model = model ?? new ReportViewModel(); this.FillRelatedData(model); return(this.View("Create", model)); } var groupByDescriptor = this.DecodeGroupByCategoryAndType(model.CategoryAndType); if (groupByDescriptor == null) { this.ModelState.AddModelError("CategoryAndType", T("There is no GroupBy field matched with the given parameters").Text); this.FillRelatedData(model); return(this.View("Create", model)); } AggregateMethods selectedAggregate = (AggregateMethods)model.AggregateMethod; if (!groupByDescriptor.AggregateMethods.Any(c => c == selectedAggregate)) { this.ModelState.AddModelError("AggregateMethod", T("The selected field does't support the selected Aggregate method").Text); this.FillRelatedData(model); return(this.View("Create", model)); } ReportRecord newReport = new ReportRecord { Title = model.Title, State = model.State, Name = model.Name, Guid = Guid.NewGuid().ToString(), Query = new QueryPartRecord { Id = model.QueryId.Value }, ChartType = model.ChartTypeId, GroupByCategory = groupByDescriptor.Category, GroupByType = groupByDescriptor.Type, AggregateMethod = model.AggregateMethod }; this.reportRepository.Create(newReport); this.reportRepository.Flush(); var aggregateReportContent = services.ContentManager.Create(ContentTypes.AggregateReportType); aggregateReportContent.As <ReportPart>().ReportId = newReport.Id; services.ContentManager.Publish(aggregateReportContent); return(this.RedirectToAction("Index")); }
public string SelectAllRecordsFromTable(string tableName, AggregateMethods aggregationMethod) { return("SELECT " + ((aggregationMethod != AggregateMethods.None) ? aggregationMethod + "(*)" : "*") + " FROM [" + tableName + "]"); }
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); }
public IEnumerable <AggregationResult> RunNumericAggregation(IHqlQuery query, AggregateMethods aggregateMethod, string fieldName, string partName, int interval) { string groupKey = string.Format(CultureInfo.InvariantCulture, "cast( floor(field.Value/{0}) as Double) ", interval.ToString(CultureInfo.InvariantCulture)); string groupValue = string.Empty; switch (aggregateMethod) { case AggregateMethods.Count: groupValue = "COUNT(*)"; break; case AggregateMethods.Sum: groupValue = "SUM(field.Value)"; break; case AggregateMethods.Average: groupValue = "AVG(field.Value)"; break; case AggregateMethods.Minimum: groupValue = "MAX(field.Value)"; break; case AggregateMethods.Maximum: groupValue = "MIN(field.Value)"; break; default: throw new ArgumentException("Aggregate method is not provided"); } var result = this.RunQuery(query, fieldName, partName, groupKey, groupValue, "DecimalFieldIndexRecords"); List <AggregationResult> returnValue = new List <AggregationResult>(); foreach (var group in result) { double value = double.Parse(group["GroupValue"].ToString()); int? key = group["GroupKey"] != null ? (int?)int.Parse(group["GroupKey"].ToString()) : null; string keyLabel = !key.HasValue ? T("[{0}]", "Null").Text : T("{0} between {1}-{2}", fieldName, (key.Value * interval).ToString(CultureInfo.InvariantCulture), ((key.Value + 1) * interval).ToString(CultureInfo.InvariantCulture)).Text; AggregationResult aggregationResult = new AggregationResult { AggregationValue = value, Label = keyLabel, GroupingField = key }; returnValue.Add(aggregationResult); } return(returnValue); }
private IEnumerable <AggregationResult> RunTicketTypesGroupingQuery(IHqlQuery query, AggregateMethods aggregation, string state) { var queryResult = CRMHelper.RunGroupByQuery(transactionManager.Value, query, aggregation, "TicketPartRecord.TicketType", state, "ticket"); var ticketTypes = this.basicDataService.GetTicketTypes(); List <AggregationResult> returnValue = new List <AggregationResult>(); foreach (var item in queryResult) { var ticketType = item.Key != null?ticketTypes.FirstOrDefault(c => c.Id == item.Key.Value) : null; string label = ticketType == null?T("[No Ticket Type]").Text : ticketType.Name; AggregationResult aggregationResult = new AggregationResult { AggregationValue = item.Value, Label = label, GroupingField = item.Key }; returnValue.Add(aggregationResult); } return(returnValue); }
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); }
protected void CreateDataReport(string queryName, string name, string title, ChartTypes chartType, string groupByCategory, string groupByType, AggregateMethods aggregateMethod) { var query = this.GetQuery(queryName); if (query == null) { return; } var queryPart = query.As <QueryPart>(); ReportRecord newReport = new ReportRecord { Name = name, Title = title, Query = queryPart.Record, ChartType = (int)chartType, GroupByCategory = groupByCategory, GroupByType = groupByType, AggregateMethod = (int)aggregateMethod }; this.reportRepository.Create(newReport); this.reportRepository.Flush(); }
private IEnumerable <AggregationResult> RunTicketPriorityGroupingQuery(IHqlQuery query, AggregateMethods aggregation) { var queryResult = this.RunQuery(query, aggregation, "PriorityRecord"); var priorities = this.basicDataService.GetPriorities(); List <AggregationResult> returnValue = new List <AggregationResult>(); foreach (var item in queryResult) { var priority = item.Key != null?priorities.FirstOrDefault(c => c.Id == item.Key.Value) : null; string label = priority == null?T("[No Priority]").Text : priority.Name; AggregationResult aggregationResult = new AggregationResult { AggregationValue = item.Value, Label = label, GroupingField = item.Key }; returnValue.Add(aggregationResult); } return(returnValue); }
public Aggregate(Set <T> input, AggregateMethods m) { _input = input; _method = m; }