/// <summary>
/// Sorts the object by the specified sort property.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source">The source.</param>
/// <param name="sortProperty">The sort property.</param>
/// <returns></returns>
public static IOrderedQueryable <T> Sort <T>(this IQueryable <T> source, Rock.Web.UI.Controls.SortProperty sortProperty)
{
string[] columns = sortProperty.Property.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
IOrderedQueryable <T> qry = null;
for (int columnIndex = 0; columnIndex < columns.Length; columnIndex++)
{
string column = columns[columnIndex].Trim();
var direction = sortProperty.Direction;
if (column.ToLower().EndsWith(" desc"))
{
column = column.Left(column.Length - 5);
direction = sortProperty.Direction == SortDirection.Ascending ? SortDirection.Descending : SortDirection.Ascending;
}
if (direction == SortDirection.Ascending)
{
qry = (columnIndex == 0) ? source.OrderBy(column) : qry.ThenBy(column);
}
else
{
qry = (columnIndex == 0) ? source.OrderByDescending(column) : qry.ThenByDescending(column);
}
}
return(qry);
}
/// <summary>
/// Sorts the object by the specified sort property.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source">The source.</param>
/// <param name="sortProperty">The sort property.</param>
/// <returns></returns>
public static IOrderedQueryable <T> Sort <T>(this IQueryable <T> source, Rock.Web.UI.Controls.SortProperty sortProperty)
{
if (sortProperty.Direction == System.Web.UI.WebControls.SortDirection.Ascending)
{
return(source.OrderBy(sortProperty.Property));
}
else
{
return(source.OrderByDescending(sortProperty.Property));
}
}
/// <summary>
/// Gets the specified parameter expression.
/// </summary>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="whereExpression">The where expression.</param>
/// <param name="sortProperty">The sort property.</param>
/// <param name="fetchTop">The fetch top.</param>
/// <returns></returns>
public IQueryable <T> Get(ParameterExpression parameterExpression, Expression whereExpression, Rock.Web.UI.Controls.SortProperty sortProperty, int?fetchTop = null)
{
return(this.Queryable().Where(parameterExpression, whereExpression, sortProperty, fetchTop));
}
/// <summary>
/// Gets a list of items that match the specified expression.
/// </summary>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="whereExpression">The where expression.</param>
/// <param name="sortProperty">The sort property.</param>
/// <returns></returns>
public IQueryable <T> Get(ParameterExpression parameterExpression, Expression whereExpression, Rock.Web.UI.Controls.SortProperty sortProperty)
{
return(Get(parameterExpression, whereExpression, sortProperty, null));
}
/// <summary>
/// Sorts the object by the specified sort property.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source">The source.</param>
/// <param name="sortProperty">The sort property.</param>
/// <returns></returns>
public static IOrderedQueryable <T> Sort <T>(this IQueryable <T> source, Rock.Web.UI.Controls.SortProperty sortProperty)
{
if (sortProperty.Property.StartsWith("attribute:"))
{
var itemType = typeof(T);
var attributeCache = AttributeCache.Get(sortProperty.Property.Substring(10).AsInteger());
if (attributeCache != null && typeof(IModel).IsAssignableFrom(typeof(T)))
{
var entityIds = new List <int>();
var models = new List <IModel>();
source.ToList().ForEach(i => models.Add(i as IModel));
var ids = models.Select(m => m.Id).ToList();
var field = attributeCache.FieldType.Field;
using (var rockContext = new RockContext())
{
foreach (var attributeValue in new AttributeValueService(rockContext)
.Queryable().AsNoTracking()
.Where(v =>
v.AttributeId == attributeCache.Id &&
v.EntityId.HasValue &&
ids.Contains(v.EntityId.Value))
.ToList())
{
var model = models.FirstOrDefault(m => m.Id == attributeValue.EntityId.Value);
if (model != null)
{
model.CustomSortValue = field.SortValue(null, attributeValue.Value, attributeCache.QualifierValues);
}
}
}
var result = new List <T>();
if (sortProperty.Direction == SortDirection.Ascending)
{
models.OrderBy(m => m.CustomSortValue).ToList().ForEach(m => result.Add((T)m));
}
else
{
models.OrderByDescending(m => m.CustomSortValue).ToList().ForEach(m => result.Add((T)m));
}
return(result.AsQueryable().OrderBy(r => 0));
}
}
string[] columns = sortProperty.Property.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
IOrderedQueryable <T> qry = null;
for (int columnIndex = 0; columnIndex < columns.Length; columnIndex++)
{
string column = columns[columnIndex].Trim();
var direction = sortProperty.Direction;
if (column.ToLower().EndsWith(" desc"))
{
column = column.Left(column.Length - 5);
direction = sortProperty.Direction == SortDirection.Ascending ? SortDirection.Descending : SortDirection.Ascending;
}
if (direction == SortDirection.Ascending)
{
qry = (columnIndex == 0) ? source.OrderBy(column) : qry.ThenBy(column);
}
else
{
qry = (columnIndex == 0) ? source.OrderByDescending(column) : qry.ThenByDescending(column);
}
}
return(qry);
}
/// <summary>
/// Queries the specified parameter expression.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryable">The queryable.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="whereExpression">The where expression.</param>
/// <param name="sortProperty">The sort property.</param>
/// <param name="fetchTop">The fetch top.</param>
/// <returns></returns>
public static IQueryable <T> Where <T>(this IQueryable <T> queryable, ParameterExpression parameterExpression, Expression whereExpression, Rock.Web.UI.Controls.SortProperty sortProperty, int?fetchTop = null)
{
if (parameterExpression != null && whereExpression != null)
{
var lambda = Expression.Lambda <Func <T, bool> >(whereExpression, parameterExpression);
queryable = queryable.Where(lambda);
}
if (sortProperty != null)
{
queryable = queryable.Sort(sortProperty);
}
if (fetchTop.HasValue)
{
queryable = queryable.Take(fetchTop.Value);
}
return(queryable);
}
/// <summary>
/// Queries a list of items that match the specified expression.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryable">The queryable.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="whereExpression">The where expression.</param>
/// <param name="sortProperty">The sort property.</param>
/// <returns></returns>
public static IQueryable <T> Where <T>(this IQueryable <T> queryable, ParameterExpression parameterExpression, Expression whereExpression, Rock.Web.UI.Controls.SortProperty sortProperty)
{
return(Where(queryable, parameterExpression, whereExpression, sortProperty, null));
}
public List <object> GetDataSource(Type entityType, Dictionary <int, EntityField> entityFields, Dictionary <int, AttributeCache> attributes, Dictionary <int, ReportField> selectComponents, Rock.Web.UI.Controls.SortProperty sortProperty, int?databaseTimeoutSeconds, out List <string> errorMessages)
{
errorMessages = new List <string>();
return(GetDataSource(entityType, entityFields, attributes, selectComponents, sortProperty, databaseTimeoutSeconds));
}
/// <summary>
/// Returns a IQueryable of the report
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="entityFields">The entity fields.</param>
/// <param name="attributes">The attributes.</param>
/// <param name="selectComponents">The select components.</param>
/// <param name="sortProperty">The sort property.</param>
/// <param name="databaseTimeoutSeconds">The database timeout seconds.</param>
/// <param name="isCommunication">if set to <c>true</c> [is communication].</param>
/// <param name="errorMessages">The error messages.</param>
/// <param name="reportDbContext">The report database context that was used.</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
/// <exception cref="System.Exception"></exception>
public IQueryable GetQueryable(Type entityType, Dictionary <int, EntityField> entityFields, Dictionary <int, AttributeCache> attributes, Dictionary <int, ReportField> selectComponents, Rock.Web.UI.Controls.SortProperty sortProperty, int?databaseTimeoutSeconds, bool isCommunication, out List <string> errorMessages, out System.Data.Entity.DbContext reportDbContext)
{
return(GetQueryable(entityType, entityFields, attributes, selectComponents, sortProperty, null, databaseTimeoutSeconds, isCommunication, out errorMessages, out reportDbContext));
}
/// <summary>
/// Gets the data source.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityType">Type of the entity.</param>
/// <param name="entityFields">The entity fields.</param>
/// <param name="attributes">The attributes.</param>
/// <param name="selectComponents">The select components.</param>
/// <param name="sortProperty">The sort property.</param>
/// <param name="errorMessages">The error messages.</param>
/// <returns></returns>
public List <object> GetDataSource(RockContext context, Type entityType, Dictionary <int, EntityField> entityFields, Dictionary <int, AttributeCache> attributes, Dictionary <int, ReportField> selectComponents, Rock.Web.UI.Controls.SortProperty sortProperty, out List <string> errorMessages)
{
errorMessages = new List <string>();
if (entityType != null)
{
Type[] modelType = { entityType };
Type genericServiceType = typeof(Rock.Data.Service <>);
Type modelServiceType = genericServiceType.MakeGenericType(modelType);
object serviceInstance = Activator.CreateInstance(modelServiceType, new object[] { context });
if (serviceInstance != null)
{
ParameterExpression paramExpression = serviceInstance.GetType().GetProperty("ParameterExpression").GetValue(serviceInstance) as ParameterExpression;
MemberExpression idExpression = Expression.Property(paramExpression, "Id");
// Get AttributeValue queryable and parameter
var attributeValues = context.Set <AttributeValue>();
ParameterExpression attributeValueParameter = Expression.Parameter(typeof(AttributeValue), "v");
// Create the dynamic type
var dynamicFields = new Dictionary <string, Type>();
foreach (var f in entityFields)
{
dynamicFields.Add(string.Format("Entity_{0}_{1}", f.Value.Name, f.Key), f.Value.PropertyType);
}
foreach (var a in attributes)
{
dynamicFields.Add(string.Format("Attribute_{0}_{1}", a.Value.Id, a.Key), typeof(string));
}
foreach (var reportField in selectComponents)
{
DataSelectComponent selectComponent = DataSelectContainer.GetComponent(reportField.Value.DataSelectComponentEntityType.Name);
if (selectComponent != null)
{
dynamicFields.Add(string.Format("Data_{0}_{1}", selectComponent.ColumnPropertyName, reportField.Key), selectComponent.ColumnFieldType);
}
}
if (dynamicFields.Count == 0)
{
errorMessages.Add("At least one field must be defined");
return(null);
}
Type dynamicType = LinqRuntimeTypeBuilder.GetDynamicType(dynamicFields);
ConstructorInfo methodFromHandle = dynamicType.GetConstructor(Type.EmptyTypes);
// Bind the dynamic fields to their expressions
var bindings = new List <MemberBinding>();
foreach (var f in entityFields)
{
bindings.Add(Expression.Bind(dynamicType.GetField(string.Format("entity_{0}_{1}", f.Value.Name, f.Key)), Expression.Property(paramExpression, f.Value.Name)));
}
foreach (var a in attributes)
{
bindings.Add(Expression.Bind(dynamicType.GetField(string.Format("attribute_{0}_{1}", a.Value.Id, a.Key)), GetAttributeValueExpression(attributeValues, attributeValueParameter, idExpression, a.Value.Id)));
}
foreach (var reportField in selectComponents)
{
DataSelectComponent selectComponent = DataSelectContainer.GetComponent(reportField.Value.DataSelectComponentEntityType.Name);
if (selectComponent != null)
{
bindings.Add(Expression.Bind(dynamicType.GetField(string.Format("data_{0}_{1}", selectComponent.ColumnPropertyName, reportField.Key)), selectComponent.GetExpression(context, idExpression, reportField.Value.Selection)));
}
}
ConstructorInfo constructorInfo = dynamicType.GetConstructor(Type.EmptyTypes);
NewExpression newExpression = Expression.New(constructorInfo);
MemberInitExpression memberInitExpression = Expression.MemberInit(newExpression, bindings);
Expression selector = Expression.Lambda(memberInitExpression, paramExpression);
Expression whereExpression = this.DataView.GetExpression(serviceInstance, paramExpression, out errorMessages);
MethodInfo getMethod = serviceInstance.GetType().GetMethod("Get", new Type[] { typeof(ParameterExpression), typeof(Expression), typeof(Rock.Web.UI.Controls.SortProperty) });
if (getMethod != null)
{
var getResult = getMethod.Invoke(serviceInstance, new object[] { paramExpression, whereExpression, sortProperty });
var qry = getResult as IQueryable <IEntity>;
var selectExpression = Expression.Call(typeof(Queryable), "Select", new Type[] { qry.ElementType, dynamicType }, Expression.Constant(qry), selector);
var query = qry.Provider.CreateQuery(selectExpression).AsNoTracking();
// enumerate thru the query results and put into a list
var reportResult = new List <object>();
var enumerator = query.GetEnumerator();
while (enumerator.MoveNext())
{
reportResult.Add(enumerator.Current);
}
return(reportResult);
}
}
}
return(null);
}
/// <summary>
/// Gets the list.
/// </summary>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="whereExpression">The where expression.</param>
/// <param name="sortProperty">The sort property.</param>
/// <returns></returns>
public List <T> GetList(ParameterExpression parameterExpression, Expression whereExpression, Rock.Web.UI.Controls.SortProperty sortProperty)
{
return(Get(parameterExpression, whereExpression, sortProperty).ToList());
}
/// <summary>
/// Gets a list of items that match the specified expression.
/// </summary>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="whereExpression">The where expression.</param>
/// <param name="sortProperty">The sort property.</param>
/// <returns></returns>
public IQueryable <T> Get(ParameterExpression parameterExpression, Expression whereExpression, Rock.Web.UI.Controls.SortProperty sortProperty)
{
if (parameterExpression != null && whereExpression != null)
{
var lambda = Expression.Lambda <Func <T, bool> >(whereExpression, parameterExpression);
var queryable = this.Queryable().Where(lambda);
return(sortProperty != null?queryable.Sort(sortProperty) : queryable);
}
return(this.Queryable());
}
/// <summary>
/// Sorts the object by the specified sort property.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source">The source.</param>
/// <param name="sortProperty">The sort property.</param>
/// <returns></returns>
public static IOrderedQueryable <T> Sort <T>(this IQueryable <T> source, Rock.Web.UI.Controls.SortProperty sortProperty)
{
if (sortProperty.Property.StartsWith("attribute:") && source.Any())
{
var itemType = typeof(T);
var attributeCache = AttributeCache.Get(sortProperty.Property.Substring(10).AsInteger());
if (attributeCache != null && typeof(IModel).IsAssignableFrom(typeof(T)))
{
List <int> ids = new List <int>();
var models = new List <IModel>();
source.ToList().ForEach(i => models.Add(i as IModel));
using (var rockContext = new RockContext())
{
//Check if Attribute Entity Type is same as Source Entity Type
var type = models.First().GetType();
EntityTypeCache modelEntity = EntityTypeCache.Get(type, false);
PropertyInfo modelProperty = null;
//Same Entity Type
if (modelEntity != null && modelEntity.Id == attributeCache.EntityTypeId)
{
ids = models.Select(m => m.Id).ToList();
}
//Different Entity Types
else if (modelEntity != null)
{
//Search the entity properties for a matching entity and save the property information and primary key name
var propertiesWithAttributes = type.GetProperties().Where(a => typeof(IHasAttributes).IsAssignableFrom(a.PropertyType)).ToList();
foreach (var propertyInfo in propertiesWithAttributes)
{
var propertyEntity = EntityTypeCache.Get(propertyInfo.PropertyType, false);
if (propertyEntity != null && propertyEntity.Id == attributeCache.EntityTypeId)
{
Object obj = models.First().GetPropertyValue(propertyInfo.Name);
var prop = obj.GetType().GetProperty("Id");
modelProperty = propertyInfo;
ids = models.Select(m => Int32.Parse(prop.GetValue(m.GetPropertyValue(propertyInfo.Name)).ToString())).ToList();
}
}
}
var field = attributeCache.FieldType.Field;
foreach (var attributeValue in new AttributeValueService(rockContext)
.Queryable().AsNoTracking()
.Where(v =>
v.AttributeId == attributeCache.Id &&
v.EntityId.HasValue &&
ids.Contains(v.EntityId.Value))
.ToList())
{
IModel model = null;
if (modelEntity != null && modelEntity.Id == attributeCache.EntityTypeId)
{
model = models.FirstOrDefault(m => m.Id == attributeValue.EntityId.Value);
}
else if (modelEntity != null)
{
//Use the model property and primary key name to get the foreign key EntityId refers to
model = models.FirstOrDefault(m =>
{
var obj = m.GetPropertyValue(modelProperty.Name);
PropertyInfo prop = obj.GetType().GetProperty("Id");
string val = prop.GetValue(obj).ToString();
return(Int32.Parse(val) == attributeValue.EntityId.Value);
});
}
else
{
//Handle not finding an Entity type
model = null;
}
if (model != null)
{
model.CustomSortValue = field.SortValue(null, attributeValue.Value, attributeCache.QualifierValues);
}
}
}
var result = new List <T>();
if (sortProperty.Direction == SortDirection.Ascending)
{
models.OrderBy(m => m.CustomSortValue).ToList().ForEach(m => result.Add((T)m));
}
else
{
models.OrderByDescending(m => m.CustomSortValue).ToList().ForEach(m => result.Add((T)m));
}
return(result.AsQueryable().OrderBy(r => 0));
}
}
string[] columns = sortProperty.Property.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
IOrderedQueryable <T> qry = null;
for (int columnIndex = 0; columnIndex < columns.Length; columnIndex++)
{
string column = columns[columnIndex].Trim();
var direction = sortProperty.Direction;
if (column.ToLower().EndsWith(" desc"))
{
column = column.Left(column.Length - 5);
direction = sortProperty.Direction == SortDirection.Ascending ? SortDirection.Descending : SortDirection.Ascending;
}
if (direction == SortDirection.Ascending)
{
qry = (columnIndex == 0) ? source.OrderBy(column) : qry.ThenBy(column);
}
else
{
qry = (columnIndex == 0) ? source.OrderByDescending(column) : qry.ThenByDescending(column);
}
}
return(qry);
}
public List <object> GetDataSource(Type entityType, Dictionary <int, EntityField> entityFields, Dictionary <int, AttributeCache> attributes, Dictionary <int, ReportField> selectComponents, Rock.Web.UI.Controls.SortProperty sortProperty, int?databaseTimeoutSeconds)
{
var reportDbContext = Reflection.GetDbContextForEntityType(entityType);
ReportGetQueryableArgs reportGetQueryableArgs = new ReportGetQueryableArgs
{
ReportDbContext = reportDbContext as Rock.Data.DbContext,
EntityFields = entityFields,
Attributes = attributes,
SelectComponents = selectComponents,
SortProperty = sortProperty,
DatabaseTimeoutSeconds = databaseTimeoutSeconds,
};
var qry = GetQueryable(reportGetQueryableArgs);
// enumerate thru the query results and put into a list
var reportResult = new List <object>();
var enumerator = (qry as System.Collections.IEnumerable).GetEnumerator();
while (enumerator.MoveNext())
{
reportResult.Add(enumerator.Current);
}
return(reportResult);
}
/// <summary>
/// Transforms the specified source.
/// </summary>
/// <param name="source">The source.</param>
/// <param name="transformation">The transformation.</param>
/// <param name="sortProperty">The sort property.</param>
/// <returns></returns>
public IQueryable <T> Transform(IQueryable <T> source, Rock.Reporting.DataTransformComponent <T> transformation, Rock.Web.UI.Controls.SortProperty sortProperty = null)
{
var paramExpression = Expression.Parameter(source.ElementType, "p");
var whereExpression = transformation.GetExpression(this, source, paramExpression);
return(Get(paramExpression, whereExpression, sortProperty));
}
/// <summary>
/// Gets the data source.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="entityFields">The entity fields.</param>
/// <param name="attributes">The attributes.</param>
/// <param name="selectComponents">The select components.</param>
/// <param name="sortProperty">The sort property.</param>
/// <param name="databaseTimeoutSeconds">The database timeout seconds.</param>
/// <param name="errorMessages">The error messages.</param>
/// <returns></returns>
public List <object> GetDataSource(Type entityType, Dictionary <int, EntityField> entityFields, Dictionary <int, AttributeCache> attributes, Dictionary <int, ReportField> selectComponents, Rock.Web.UI.Controls.SortProperty sortProperty, int?databaseTimeoutSeconds, out List <string> errorMessages)
{
System.Data.Entity.DbContext reportDbContext;
var qry = GetQueryable(entityType, entityFields, attributes, selectComponents, sortProperty, null, databaseTimeoutSeconds, false, out errorMessages, out reportDbContext);
// enumerate thru the query results and put into a list
var reportResult = new List <object>();
var enumerator = (qry as System.Collections.IEnumerable).GetEnumerator();
while (enumerator.MoveNext())
{
reportResult.Add(enumerator.Current);
}
return(reportResult);
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="scheduleIds">The schedule ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime?startDate = null, DateTime?endDate = null, string groupIds = null, string campusIds = null, int?dataViewId = null, string scheduleIds = null)
{
var qryAttendance = Queryable().AsNoTracking()
.Where(a =>
a.DidAttend.HasValue &&
a.DidAttend.Value &&
a.PersonAlias != null);
if (startDate.HasValue)
{
qryAttendance = qryAttendance.Where(a => a.StartDateTime >= startDate.Value);
}
if (endDate.HasValue)
{
qryAttendance = qryAttendance.Where(a => a.StartDateTime < endDate.Value);
}
if (dataViewId.HasValue)
{
var rockContext = (RockContext)this.Context;
var dataView = new DataViewService(rockContext).Get(dataViewId.Value);
if (dataView != null)
{
var personService = new PersonService(rockContext);
var errorMessages = new List <string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages);
Rock.Web.UI.Controls.SortProperty sort = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where(paramExpression, whereExpression, sort)
.Select(p => p.Id);
qryAttendance = qryAttendance.Where(a => dataViewPersonIdQry.Contains(a.PersonAlias.PersonId));
}
}
if (!string.IsNullOrWhiteSpace(groupIds))
{
var groupIdList = groupIds.Split(',').AsIntegerList();
qryAttendance = qryAttendance.Where(a => a.GroupId.HasValue && groupIdList.Contains(a.GroupId.Value));
}
// If campuses were included, filter attendances by those that have selected campuses
// if 'null' is one of the campuses, treat that as a 'CampusId is Null'
var includeNullCampus = (campusIds ?? "").Split(',').ToList().Any(a => a.Equals("null", StringComparison.OrdinalIgnoreCase));
var campusIdList = (campusIds ?? "").Split(',').AsIntegerList();
// remove 0 from the list, just in case it is there
campusIdList.Remove(0);
if (campusIdList.Any())
{
if (includeNullCampus)
{
// show records that have a campusId in the campusIdsList + records that have a null campusId
qryAttendance = qryAttendance.Where(a => (a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value)) || !a.CampusId.HasValue);
}
else
{
// only show records that have a campusId in the campusIdList
qryAttendance = qryAttendance.Where(a => a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value));
}
}
else if (includeNullCampus)
{
// 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId
qryAttendance = qryAttendance.Where(a => !a.CampusId.HasValue);
}
// If schedules were included, filter attendances by those that have selected schedules
var scheduleIdList = (scheduleIds ?? "").Split(',').AsIntegerList();
scheduleIdList.Remove(0);
if (scheduleIdList.Any())
{
qryAttendance = qryAttendance.Where(a => a.ScheduleId.HasValue && scheduleIdList.Contains(a.ScheduleId.Value));
}
var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime(groupBy);
var summaryQry = qryAttendanceWithSummaryDateTime.Select(a => new
{
a.SummaryDateTime,
Campus = new
{
Id = a.Attendance.CampusId,
Name = a.Attendance.Campus.Name
},
Group = new
{
Id = a.Attendance.GroupId,
Name = a.Attendance.Group.Name
},
Schedule = new
{
Id = a.Attendance.ScheduleId,
Name = a.Attendance.Schedule.Name
},
Location = new
{
Id = a.Attendance.LocationId,
Name = a.Attendance.Location.Name
}
});
List <SummaryData> result = null;
if (graphBy == AttendanceGraphBy.Total)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = "Total",
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Campus)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Campus }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Group)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Group }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Schedule)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Schedule }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Location)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Location }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
return(result);
}
/// <summary>
/// Gets the queryable.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="entityFields">The entity fields.</param>
/// <param name="attributes">The attributes.</param>
/// <param name="selectComponents">The select components.</param>
/// <param name="sortProperty">The sort property.</param>
/// <param name="dataViewFilterOverrides">The data view filter overrides.</param>
/// <param name="databaseTimeoutSeconds">The database timeout seconds.</param>
/// <param name="isCommunication">if set to <c>true</c> [is communication].</param>
/// <param name="errorMessages">The error messages.</param>
/// <param name="reportDbContext">The report database context.</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public IQueryable GetQueryable(Type entityType, Dictionary <int, EntityField> entityFields, Dictionary <int, AttributeCache> attributes, Dictionary <int, ReportField> selectComponents, Rock.Web.UI.Controls.SortProperty sortProperty, DataViewFilterOverrides dataViewFilterOverrides, int?databaseTimeoutSeconds, bool isCommunication, out List <string> errorMessages, out System.Data.Entity.DbContext reportDbContext)
{
errorMessages = new List <string>();
reportDbContext = null;
if (entityType != null)
{
reportDbContext = Reflection.GetDbContextForEntityType(entityType);
IService serviceInstance = Reflection.GetServiceForEntityType(entityType, reportDbContext);
if (databaseTimeoutSeconds.HasValue)
{
reportDbContext.Database.CommandTimeout = databaseTimeoutSeconds.Value;
}
if (serviceInstance != null)
{
ParameterExpression paramExpression = serviceInstance.ParameterExpression;
MemberExpression idExpression = Expression.Property(paramExpression, "Id");
// Get AttributeValue queryable and parameter
var attributeValues = reportDbContext.Set <AttributeValue>();
ParameterExpression attributeValueParameter = Expression.Parameter(typeof(AttributeValue), "v");
// Create the dynamic type
var dynamicFields = new Dictionary <string, Type>();
dynamicFields.Add("Id", typeof(int));
foreach (var f in entityFields)
{
dynamicFields.Add(string.Format("Entity_{0}_{1}", f.Value.Name, f.Key), f.Value.PropertyType);
}
foreach (var a in attributes)
{
dynamicFields.Add(string.Format("Attribute_{0}_{1}", a.Value.Id, a.Key), a.Value.FieldType.Field.AttributeValueFieldType);
}
foreach (var reportField in selectComponents)
{
DataSelectComponent selectComponent = DataSelectContainer.GetComponent(reportField.Value.DataSelectComponentEntityType.Name);
if (selectComponent != null)
{
dynamicFields.Add(string.Format("Data_{0}_{1}", selectComponent.ColumnPropertyName, reportField.Key), selectComponent.ColumnFieldType);
var customSortProperties = selectComponent.SortProperties(reportField.Value.Selection);
if (customSortProperties != null)
{
foreach (var customSortProperty in customSortProperties.Split(','))
{
if (!string.IsNullOrWhiteSpace(customSortProperty))
{
var customSortPropertyType = entityType.GetPropertyType(customSortProperty);
dynamicFields.Add(string.Format("Sort_{0}_{1}", customSortProperty, reportField.Key), customSortPropertyType ?? typeof(string));
}
}
}
if (isCommunication && selectComponent is IRecipientDataSelect)
{
dynamicFields.Add($"Recipient_{selectComponent.ColumnPropertyName}_{reportField.Key}", ((IRecipientDataSelect)selectComponent).RecipientColumnFieldType);
}
}
}
if (dynamicFields.Count == 0)
{
errorMessages.Add("At least one field must be defined");
return(null);
}
Type dynamicType = LinqRuntimeTypeBuilder.GetDynamicType(dynamicFields);
ConstructorInfo methodFromHandle = dynamicType.GetConstructor(Type.EmptyTypes);
// Bind the dynamic fields to their expressions
var bindings = new List <MemberAssignment>();
bindings.Add(Expression.Bind(dynamicType.GetField("id"), idExpression));
foreach (var f in entityFields)
{
bindings.Add(Expression.Bind(dynamicType.GetField(string.Format("entity_{0}_{1}", f.Value.Name, f.Key)), Expression.Property(paramExpression, f.Value.Name)));
}
foreach (var a in attributes)
{
bindings.Add(Expression.Bind(dynamicType.GetField(string.Format("attribute_{0}_{1}", a.Value.Id, a.Key)), GetAttributeValueExpression(attributeValues, attributeValueParameter, idExpression, a.Value.Id)));
}
foreach (var reportField in selectComponents)
{
DataSelectComponent selectComponent = DataSelectContainer.GetComponent(reportField.Value.DataSelectComponentEntityType.Name);
if (selectComponent != null)
{
try
{
var componentExpression = selectComponent.GetExpression(reportDbContext, idExpression, reportField.Value.Selection ?? string.Empty);
if (componentExpression == null)
{
componentExpression = Expression.Constant(null, typeof(string));
}
bindings.Add(Expression.Bind(dynamicType.GetField(string.Format("data_{0}_{1}", selectComponent.ColumnPropertyName, reportField.Key)), componentExpression));
if (isCommunication && selectComponent is IRecipientDataSelect)
{
var recipientPersonIdExpression = ((IRecipientDataSelect)selectComponent).GetRecipientPersonIdExpression(reportDbContext, idExpression, reportField.Value.Selection ?? string.Empty);
if (recipientPersonIdExpression != null)
{
bindings.Add(Expression.Bind(dynamicType.GetField(string.Format("recipient_{0}_{1}", selectComponent.ColumnPropertyName, reportField.Key)), recipientPersonIdExpression));
}
}
var customSortProperties = selectComponent.SortProperties(reportField.Value.Selection);
if (!string.IsNullOrEmpty(customSortProperties))
{
foreach (var customSortProperty in customSortProperties.Split(','))
{
var customSortPropertyParts = customSortProperty.Split('.');
MemberInfo memberInfo = dynamicType.GetField(string.Format("sort_{0}_{1}", customSortProperty, reportField.Key));
Expression memberExpression = null;
foreach (var customSortPropertyPart in customSortPropertyParts)
{
memberExpression = Expression.Property(memberExpression ?? paramExpression, customSortPropertyPart);
}
bindings.Add(Expression.Bind(memberInfo, memberExpression));
}
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Exception in {0}", selectComponent), ex);
}
}
}
ConstructorInfo constructorInfo = dynamicType.GetConstructor(Type.EmptyTypes);
NewExpression newExpression = Expression.New(constructorInfo);
MemberInitExpression memberInitExpression = Expression.MemberInit(newExpression, bindings);
LambdaExpression selector = Expression.Lambda(memberInitExpression, paramExpression);
// NOTE: having a NULL Dataview is OK.
Expression whereExpression = null;
if (this.DataView != null)
{
whereExpression = this.DataView.GetExpression(serviceInstance, paramExpression, dataViewFilterOverrides, out errorMessages);
}
MethodInfo getMethod = serviceInstance.GetType().GetMethod("Get", new Type[] { typeof(ParameterExpression), typeof(Expression), typeof(Rock.Web.UI.Controls.SortProperty), typeof(int?) });
if (getMethod != null)
{
var getResult = getMethod.Invoke(serviceInstance, new object[] { paramExpression, whereExpression, null, null });
var qry = getResult as IQueryable <IEntity>;
var qryExpression = qry.Expression;
// apply the OrderBy clauses to the Expression from whatever columns are specified in sortProperty.Property
string orderByMethod = "OrderBy";
if (sortProperty == null)
{
// if no sorting was specified, sort by Id
sortProperty = new Web.UI.Controls.SortProperty {
Direction = SortDirection.Ascending, Property = "Id"
};
}
/*
* NOTE: The sort property sorting rules can be a little confusing. Here is how it works:
* - SortProperty.Direction of Ascending means sort exactly as what the Columns specification says
* - SortProperty.Direction of Descending means sort the _opposite_ of what the Columns specification says
* Examples:
* 1) SortProperty.Property "LastName desc, FirstName, BirthDate desc" and SortProperty.Direction = Ascending
* OrderBy should be: "order by LastName desc, FirstName, BirthDate desc"
* 2) SortProperty.Property "LastName desc, FirstName, BirthDate desc" and SortProperty.Direction = Descending
* OrderBy should be: "order by LastName, FirstName desc, BirthDate"
*/
foreach (var column in sortProperty.Property.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
string propertyName;
var direction = sortProperty.Direction;
if (column.EndsWith(" desc", StringComparison.OrdinalIgnoreCase))
{
propertyName = column.Left(column.Length - 5);
// if the column ends with " desc", toggle the direction if sortProperty is Descending
direction = sortProperty.Direction == SortDirection.Ascending ? SortDirection.Descending : SortDirection.Ascending;
}
else
{
propertyName = column;
}
string methodName = direction == SortDirection.Descending ? orderByMethod + "Descending" : orderByMethod;
// Call OrderBy on whatever the Expression is for that Column
var sortMember = bindings.FirstOrDefault(a => a.Member.Name.Equals(propertyName, StringComparison.OrdinalIgnoreCase));
LambdaExpression sortSelector = Expression.Lambda(sortMember.Expression, paramExpression);
qryExpression = Expression.Call(typeof(Queryable), methodName, new Type[] { qry.ElementType, sortSelector.ReturnType }, qryExpression, sortSelector);
orderByMethod = "ThenBy";
}
var selectExpression = Expression.Call(typeof(Queryable), "Select", new Type[] { qry.ElementType, dynamicType }, qryExpression, selector);
var query = qry.Provider.CreateQuery(selectExpression).AsNoTracking();
// cast to a dynamic so that we can do a Queryable.Take (the compiler figures out the T in IQueryable at runtime)
dynamic dquery = query;
if (FetchTop.HasValue)
{
dquery = Queryable.Take(dquery, FetchTop.Value);
}
return(dquery as IQueryable);
}
}
}
return(null);
}
public IQueryable GetQueryable(Type entityType, Dictionary <int, EntityField> entityFields, Dictionary <int, AttributeCache> attributes, Dictionary <int, ReportField> selectComponents, Rock.Web.UI.Controls.SortProperty sortProperty,
DataViewFilterOverrides dataViewFilterOverrides, int?databaseTimeoutSeconds, bool isCommunication, out List <string> errorMessages, out System.Data.Entity.DbContext reportDbContext)
{
reportDbContext = Reflection.GetDbContextForEntityType(entityType);
ReportGetQueryableArgs reportGetQueryableArgs = new ReportGetQueryableArgs
{
ReportDbContext = reportDbContext as Rock.Data.DbContext,
EntityFields = entityFields,
Attributes = attributes,
SelectComponents = selectComponents,
SortProperty = sortProperty,
DataViewFilterOverrides = dataViewFilterOverrides,
DatabaseTimeoutSeconds = databaseTimeoutSeconds,
IsCommunication = isCommunication
};
errorMessages = null;
return(GetQueryable(reportGetQueryableArgs));
}
