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