Beispiel #1
0
        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);
        }
Beispiel #3
0
        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"));
        }
Beispiel #4
0
 public string SelectAllRecordsFromTable(string tableName, AggregateMethods aggregationMethod)
 {
     return("SELECT " + ((aggregationMethod != AggregateMethods.None) ? aggregationMethod + "(*)" : "*") +
            " FROM [" + tableName + "]");
 }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        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);
        }
Beispiel #8
0
        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);
        }
Beispiel #9
0
        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);
        }
Beispiel #11
0
 public Aggregate(Set <T> input, AggregateMethods m)
 {
     _input  = input;
     _method = m;
 }