/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
var rockContext = ( RockContext )serviceInstance.Context;
if (selectionValues.Length >= 2)
{
var communicationId = selectionValues[0].AsInteger();
var communicationStatusType = selectionValues[1].ConvertToEnum <CommunicationStatusType>();
var communicationRecipients = new CommunicationRecipientService(rockContext).GetByCommunicationId(communicationId);
var interactionChannelCommunication = new InteractionChannelService(rockContext).Get(Rock.SystemGuid.InteractionChannel.COMMUNICATION.AsGuid());
var interactionQuery = new InteractionService(rockContext).Queryable()
.Where(a => a.InteractionComponent.ChannelId == interactionChannelCommunication.Id &&
a.InteractionComponent.EntityId.Value == communicationId);
CommunicationRecipientStatus[] sentStatus = new CommunicationRecipientStatus[] { CommunicationRecipientStatus.Opened, CommunicationRecipientStatus.Delivered };
switch (communicationStatusType)
{
case CommunicationStatusType.Open:
{
interactionQuery = interactionQuery.Where(a => a.Operation == "Opened");
communicationRecipients = communicationRecipients.Where(a => sentStatus.Contains(a.Status) && interactionQuery.Any(b => b.EntityId == a.Id));
}
break;
case CommunicationStatusType.Clicked:
{
interactionQuery = interactionQuery.Where(a => a.Operation == "Click");
communicationRecipients = communicationRecipients.Where(a => sentStatus.Contains(a.Status) && interactionQuery.Any(b => b.EntityId == a.Id));
}
break;
case CommunicationStatusType.Unopened:
{
interactionQuery = interactionQuery.Where(a => a.Operation == "Click" || a.Operation == "Opened");
communicationRecipients = communicationRecipients.Where(a => sentStatus.Contains(a.Status) && !interactionQuery.Any(b => b.EntityId == a.Id));
}
break;
case CommunicationStatusType.Failed:
default:
{
CommunicationRecipientStatus[] failedStatus = new CommunicationRecipientStatus[] { CommunicationRecipientStatus.Failed };
communicationRecipients = communicationRecipients.Where(a => failedStatus.Contains(a.Status));
}
break;
}
var qry = new PersonService(( RockContext )serviceInstance.Context).Queryable()
.Where(p => communicationRecipients.Any(x => x.PersonAlias.PersonId == p.Id));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 3)
{
int? registrationTemplateId = selectionValues[0].AsIntegerOrNull();
Guid?registrationInstanceGuid = selectionValues[1].AsGuidOrNull();
var registrationType = selectionValues[2];
var rockContext = (RockContext)serviceInstance.Context;
IQueryable <RegistrationRegistrant> registrantQuery;
IQueryable <Registration> registrationQuery;
IQueryable <Rock.Model.Person> qry;
if (registrationTemplateId == null)
{
// no registration template id selected, so return nothing
return(Expression.Constant(false));
}
// Registrant
if (registrationType == null || registrationType == "2")
{
registrantQuery = new RegistrationRegistrantService(rockContext).Queryable()
.Where(r => r.Registration.RegistrationInstance.RegistrationTemplateId == registrationTemplateId);
if (registrationInstanceGuid != null)
{
registrantQuery = registrantQuery.Where(r => r.Registration.RegistrationInstance.Guid == registrationInstanceGuid);
}
qry = new PersonService(rockContext).Queryable()
.Where(p => registrantQuery.Where(xx => xx.PersonAlias.PersonId == p.Id).Count() >= 1);
}
// Registrar
else
{
registrationQuery = new RegistrationService(rockContext).Queryable()
.Where(r => r.RegistrationInstance.RegistrationTemplateId == registrationTemplateId);
if (registrationInstanceGuid != null)
{
registrationQuery = registrationQuery.Where(r => r.RegistrationInstance.Guid == registrationInstanceGuid);
}
qry = new PersonService(rockContext).Queryable()
.Where(p => registrationQuery.Where(xx => xx.PersonAlias.PersonId == p.Id).Count() >= 1);
}
Expression result = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p");
return(result);
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 1)
{
List <Guid> registrationTemplateGuids = selectionValues[0].Split(',').AsGuidList();
var registrationInstanceService = new RegistrationInstanceService((RockContext)serviceInstance.Context);
var registrationInstanceIds = registrationInstanceService.Queryable().Where(ri => registrationTemplateGuids.Contains(ri.RegistrationTemplate.Guid)).Select(ri => ri.Id).Distinct().ToList();
RegistrationRegistrantService registrationRegistrantService = new RegistrationRegistrantService((RockContext)serviceInstance.Context);
bool includeInactiveRegistrationInstances = false;
if (selectionValues.Length >= 2)
{
includeInactiveRegistrationInstances = selectionValues[1].AsBooleanOrNull() ?? true;;
}
else
{
// if options where saved before this option was added, set to false, even though it would have included inactive before
includeInactiveRegistrationInstances = false;
}
var registrationRegistrantServiceQry = registrationRegistrantService.Queryable();
if (registrationTemplateGuids.Count > 0)
{
registrationRegistrantServiceQry = registrationRegistrantServiceQry.Where(xx => registrationInstanceIds.Contains(xx.Registration.RegistrationInstanceId));
}
if (selectionValues.Length >= 3)
{
string slidingDelimitedValues = selectionValues[2].Replace(',', '|');
DateRange dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(slidingDelimitedValues);
if (dateRange.Start.HasValue)
{
registrationRegistrantServiceQry = registrationRegistrantServiceQry.Where(xx => xx.CreatedDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
registrationRegistrantServiceQry = registrationRegistrantServiceQry.Where(xx => xx.CreatedDateTime < dateRange.End.Value);
}
}
var qry = new PersonService((RockContext)serviceInstance.Context).Queryable()
.Where(p => registrationRegistrantServiceQry.Any(xx => xx.PersonAlias.PersonId == p.Id));
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 2)
{
GroupMemberService groupMemberService = new GroupMemberService((RockContext)serviceInstance.Context);
int groupTypeId = 0;
Guid groupTypeGuid = selectionValues[0].AsGuid();
var groupType = GroupTypeCache.Get(groupTypeGuid);
if (groupType != null)
{
groupTypeId = groupType.Id;
}
var groupMemberServiceQry = groupMemberService.Queryable().Where(xx => xx.Group.GroupTypeId == groupTypeId && xx.Group.IsArchived != true);
bool?groupStatus = null;
if (selectionValues.Length >= 4)
{
groupStatus = selectionValues[3].AsBooleanOrNull();
}
if (groupStatus.HasValue)
{
groupMemberServiceQry = groupMemberServiceQry.Where(xx => xx.Group.IsActive == groupStatus.Value);
}
var groupRoleGuids = selectionValues[1].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(n => n.AsGuid()).ToList();
if (groupRoleGuids.Count() > 0 && groupType != null)
{
var groupRoleIds = groupType.Roles.Where(role => groupRoleGuids.Contains(role.Guid)).Select(a => a.Id).ToList();
groupMemberServiceQry = groupMemberServiceQry.Where(xx => groupRoleIds.Contains(xx.GroupRoleId));
}
GroupMemberStatus?groupMemberStatus = null;
if (selectionValues.Length >= 3)
{
groupMemberStatus = selectionValues[2].ConvertToEnumOrNull <GroupMemberStatus>();
}
if (groupMemberStatus.HasValue)
{
groupMemberServiceQry = groupMemberServiceQry.Where(xx => xx.GroupMemberStatus == groupMemberStatus.Value);
}
var qry = new PersonService((RockContext)serviceInstance.Context).Queryable()
.Where(p => groupMemberServiceQry.Any(xx => xx.PersonId == p.Id));
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var rockContext = (RockContext)serviceInstance.Context;
SelectionConfig selectionConfig = SelectionConfig.Parse(selection);
if (selectionConfig == null)
{
return(null);
}
DateRange dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(selectionConfig.DelimitedValues);
int transactionTypeContributionId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid()).Id;
var financialTransactionsQry = new FinancialTransactionService(rockContext)
.Queryable()
.Where(xx => xx.TransactionTypeValueId == transactionTypeContributionId);
var accountIdList = new FinancialAccountService(( RockContext )serviceInstance.Context).GetByGuids(selectionConfig.AccountGuids).Select(a => a.Id).ToList();
if (accountIdList.Any())
{
if (accountIdList.Count == 1)
{
int accountId = accountIdList.First();
financialTransactionsQry = financialTransactionsQry.Where(xx => xx.TransactionDetails.Any(a => a.AccountId == accountId));
}
else
{
financialTransactionsQry = financialTransactionsQry.Where(xx => xx.TransactionDetails.Any(a => accountIdList.Contains(a.AccountId)));
}
}
var firstContributionDateQry = financialTransactionsQry
.GroupBy(xx => xx.AuthorizedPersonAlias.PersonId)
.Select(ss => new
{
PersonId = ss.Key,
FirstTransactionDate = ss.Min(a => selectionConfig.UseSundayDate == true ? a.SundayDate : a.TransactionDateTime)
});
if (dateRange.Start.HasValue)
{
firstContributionDateQry = firstContributionDateQry.Where(xx => xx.FirstTransactionDate >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
firstContributionDateQry = firstContributionDateQry.Where(xx => xx.FirstTransactionDate < dateRange.End.Value);
}
var innerQry = firstContributionDateQry.Select(xx => xx.PersonId).AsQueryable();
var qry = new PersonService(rockContext).Queryable().Where(p => innerQry.Any(xx => xx == p.Id));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
/// <summary>
/// Gets a filter expression for an attribute value.
/// </summary>
/// <param name="configurationValues">The configuration values.</param>
/// <param name="filterValues">The filter values.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <returns></returns>
public override Expression AttributeFilterExpression(Dictionary <string, ConfigurationValue> configurationValues, List <string> filterValues, ParameterExpression parameterExpression)
{
Expression comparison = null;
if (filterValues.Count > 1)
{
//// OR up the where clauses for each of the selected values
// and make sure to wrap commas around things so we don't collide with partial matches
// so it'll do something like this:
//
// WHERE ',' + Value + ',' like '%,bacon,%'
// OR ',' + Value + ',' like '%,lettuce,%'
// OR ',' + Value + ',' like '%,tomato,%'
// should be either "Contains" or "Not Contains"
ComparisonType comparisonType = filterValues[0].ConvertToEnum <ComparisonType>(ComparisonType.Contains);
// No comparison value was specified, so we can filter if the Comparison Type using no value still makes sense
if ((ComparisonType.IsBlank | ComparisonType.IsNotBlank).HasFlag(comparisonType))
{
// Just checking if IsBlank or IsNotBlank, so let ComparisonExpression do its thing
MemberExpression propertyExpression = Expression.Property(parameterExpression, this.AttributeValueFieldName);
return(ComparisonHelper.ComparisonExpression(comparisonType, propertyExpression, AttributeConstantExpression(string.Empty)));
}
List <string> selectedValues = filterValues[1].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (var selectedValue in selectedValues)
{
var searchValue = "," + selectedValue + ",";
var qryToExtract = new AttributeValueService(new Data.RockContext()).Queryable().Where(a => ("," + a.Value + ",").Contains(searchValue));
var valueExpression = FilterExpressionExtractor.Extract <AttributeValue>(qryToExtract, parameterExpression, "a");
if (comparisonType != ComparisonType.Contains)
{
valueExpression = Expression.Not(valueExpression);
}
if (comparison == null)
{
comparison = valueExpression;
}
else
{
comparison = Expression.Or(comparison, valueExpression);
}
}
}
if (comparison == null)
{
return(new NoAttributeFilterExpression());
}
return(comparison);
}
/// <summary>
/// Gets a filter expression for an attribute value.
/// </summary>
/// <param name="configurationValues">The configuration values.</param>
/// <param name="filterValues">The filter values.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <returns></returns>
public override Expression AttributeFilterExpression(Dictionary <string, ConfigurationValue> configurationValues, List <string> filterValues, ParameterExpression parameterExpression)
{
Expression comparison = null;
if (filterValues.Count > 1)
{
ComparisonType?comparisonType = filterValues[0].ConvertToEnumOrNull <ComparisonType>();
if (comparisonType.HasValue)
{
string compareToValue = filterValues[1];
MemberExpression propertyExpression = Expression.Property(parameterExpression, this.AttributeValueFieldName);
if (!string.IsNullOrWhiteSpace(compareToValue))
{
List <string> selectedValues = compareToValue.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (var selectedValue in selectedValues)
{
var searchValue = "," + selectedValue + ",";
var qryToExtract = new AttributeValueService(new Data.RockContext()).Queryable().Where(a => ("," + a.Value + ",").Contains(searchValue));
var valueExpression = FilterExpressionExtractor.Extract <AttributeValue>(qryToExtract, parameterExpression, "a");
if (comparisonType.Value != ComparisonType.Contains)
{
valueExpression = Expression.Not(valueExpression);
}
if (comparison == null)
{
comparison = valueExpression;
}
else
{
comparison = Expression.Or(comparison, valueExpression);
}
}
}
else
{
// No comparison value was specified, so we can filter if the Comparison Type using no value still makes sense
if ((ComparisonType.IsBlank | ComparisonType.IsNotBlank).HasFlag(comparisonType))
{
// Just checking if IsBlank or IsNotBlank, so let ComparisonExpression do its thing
return(ComparisonHelper.ComparisonExpression(comparisonType.Value, propertyExpression, AttributeConstantExpression(string.Empty)));
}
}
}
}
if (comparison == null)
{
return(new Rock.Data.NoAttributeFilterExpression());
}
return(comparison);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 2)
{
var courseRequirementId = selectionValues[1].AsInteger();
CourseRequirementStatusService courseRequirementStatusService = new CourseRequirementStatusService(( RockContext )serviceInstance.Context);
var statusQry = courseRequirementStatusService.Queryable();
var qry = new PersonService(( RockContext )serviceInstance.Context).Queryable();
switch (selectionValues[0])
{
case "0": //Has Course Requirement
statusQry = statusQry.Where(s => s.CourseRequirementId == courseRequirementId);
qry = qry.Where(p => statusQry.Select(r => r.PersonAlias.PersonId).Contains(p.Id));
break;
case "1": //Has Complete Course Requirement
statusQry = statusQry.Where(s => s.CourseRequirementId == courseRequirementId &&
s.IsComplete &&
(!s.ValidUntil.HasValue || s.ValidUntil >= Rock.RockDateTime.Today));
qry = qry.Where(p => statusQry.Select(r => r.PersonAlias.PersonId).Contains(p.Id));
break;
case "2": //Has Incomplete or Expired Course Requirement
statusQry = statusQry.Where(s => s.CourseRequirementId == courseRequirementId &&
!(s.IsComplete && (!s.ValidUntil.HasValue || s.ValidUntil >= Rock.RockDateTime.Today)));
qry = qry.Where(p => statusQry.Select(r => r.PersonAlias.PersonId).Contains(p.Id));
break;
case "3": //Has Incomplete Course Requirement
statusQry = statusQry.Where(s => s.CourseRequirementId == courseRequirementId && !s.IsComplete);
qry = qry.Where(p => statusQry.Select(r => r.PersonAlias.PersonId).Contains(p.Id));
break;
case "4": //Has Expired Course Requirement
statusQry = statusQry.Where(s => s.CourseRequirementId == courseRequirementId &&
s.IsComplete &&
s.ValidUntil.HasValue &&
s.ValidUntil < Rock.RockDateTime.Today);
qry = qry.Where(p => statusQry.Select(r => r.PersonAlias.PersonId).Contains(p.Id));
break;
default:
break;
}
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var groupAttendanceFilterSelection = GetGroupAttendanceFilterSelection(selection);
if (groupAttendanceFilterSelection.GroupGuids == null || groupAttendanceFilterSelection.GroupGuids.Count == 0)
{
// no groups selected, so return nothing
return(Expression.Constant(false));
}
var rockContext = serviceInstance.Context as RockContext;
var attendanceQry = new AttendanceService(rockContext).Queryable().Where(a => a.DidAttend.HasValue && a.DidAttend.Value);
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(groupAttendanceFilterSelection.SlidingDateRange);
if (dateRange.Start.HasValue)
{
var startDate = dateRange.Start.Value;
attendanceQry = attendanceQry.Where(a => a.Occurrence.OccurrenceDate >= startDate);
}
if (dateRange.End.HasValue)
{
var endDate = dateRange.End.Value;
attendanceQry = attendanceQry.Where(a => a.Occurrence.OccurrenceDate < endDate);
}
var groupIds = GetGroupIds(groupAttendanceFilterSelection.GroupGuids, groupAttendanceFilterSelection.IncludeChildGroups);
if (groupIds.Count == 1)
{
// if there is exactly one groupId we can avoid a 'Contains' (Contains has a small performance impact)
int groupId = groupIds[0];
attendanceQry = attendanceQry.Where(a => a.Occurrence.GroupId.HasValue && a.Occurrence.GroupId.Value == groupId);
}
else if (groupIds.Count > 1)
{
attendanceQry = attendanceQry.Where(a => a.Occurrence.GroupId.HasValue && groupIds.Contains(a.Occurrence.GroupId.Value));
}
if (groupAttendanceFilterSelection.Schedules.Any())
{
attendanceQry = attendanceQry.Where(a => a.Occurrence.ScheduleId.HasValue && groupAttendanceFilterSelection.Schedules.Contains(a.Occurrence.ScheduleId.Value));
}
var qry = new PersonService(rockContext)
.Queryable()
.Where(p => attendanceQry.Where(xx => xx.PersonAlias.PersonId == p.Id).Count() == groupAttendanceFilterSelection.AttendedCount);
var compareEqualExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p") as BinaryExpression;
var comparisonType = groupAttendanceFilterSelection.IntegerCompare.ConvertToEnum <ComparisonType>(ComparisonType.GreaterThanOrEqualTo);
var result = FilterExpressionExtractor.AlterComparisonType(comparisonType, compareEqualExpression, null);
return(result);
}
/// <summary>
/// Builds the expression.
/// </summary>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="idQuery">The id query.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <returns></returns>
private Expression BuildExpression(IService serviceInstance, IQueryable <int> idQuery, ParameterExpression parameterExpression)
{
var rockContext = ( RockContext )serviceInstance.Context;
var personSpouseQuery = idQuery.Select(a => RockUdfHelper.ufnCrm_GetSpousePersonIdFromPersonId(a));
int maritalStatusMarriedId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.PERSON_MARITAL_STATUS_MARRIED.AsGuid()).Id;
var qry = new PersonService(( RockContext )serviceInstance.Context).Queryable().Where(p => p.MaritalStatusValueId == maritalStatusMarriedId && personSpouseQuery.Contains(p.Id));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
/// <summary>
/// Creates a Linq Expression that can be applied to an IQueryable to filter the result set.
/// </summary>
/// <param name="entityType">The type of entity in the result set.</param>
/// <param name="serviceInstance">A service instance that can be queried to obtain the result set.</param>
/// <param name="parameterExpression">The input parameter that will be injected into the filter expression.</param>
/// <param name="selection">A formatted string representing the filter settings.</param>
/// <returns>
/// A Linq Expression that can be used to filter an IQueryable.
/// </returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var qry = new PersonService(( RockContext )serviceInstance.Context).Queryable();
var qryEmails = new PersonService(( RockContext )serviceInstance.Context)
.Queryable()
.AsNoTracking();
qry = qry.Where(p => qryEmails.Any(p2 => (p2.Email != null && p2.Email != "" && p.Email != null && p.Email != "" && p2.Email == p.Email) && p2.Id != p.Id));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
/// <summary>
/// Builds the expression.
/// </summary>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="idQuery">The id query.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <returns></returns>
private Expression BuildExpression(IService serviceInstance, IQueryable <int> idQuery, ParameterExpression parameterExpression)
{
Guid adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
Guid childGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid();
var qry = new PersonService((RockContext)serviceInstance.Context).Queryable()
.Where(p => p.Members.Where(a => a.GroupRole.Guid == adultGuid)
.Any(a => a.Group.Members
.Any(c => c.GroupRole.Guid == childGuid && idQuery.Contains(c.PersonId))));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
/// <summary>
/// Creates a Linq Expression that can be applied to an IQueryable to filter the result set.
/// </summary>
/// <param name="entityType">The type of entity in the result set.</param>
/// <param name="serviceInstance">A service instance that can be queried to obtain the result set.</param>
/// <param name="parameterExpression">The input parameter that will be injected into the filter expression.</param>
/// <param name="selection">A formatted string representing the filter settings.</param>
/// <returns>
/// A Linq Expression that can be used to filter an IQueryable.
/// </returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var settings = new FilterSettings(selection);
var context = (RockContext)serviceInstance.Context;
//
// Define Candidate Group Members.
//
// Get the Group Member Data View that defines the set of candidates from which matching Group Members can be selected.
var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent(settings.GroupMemberDataViewGuid, context);
var memberService = new GroupMemberService(context);
var memberQuery = memberService.Queryable();
if (dataView != null)
{
memberQuery = DataComponentSettingsHelper.FilterByDataView(memberQuery, dataView, memberService);
}
//
// Construct the Query to return the list of People matching the filter conditions.
//
var personQuery = new PersonService(context).Queryable();
BinaryExpression result;
if (settings.MemberCountComparison.HasValue &&
settings.MemberCount.HasValue)
{
var comparisonType = settings.MemberCountComparison.Value;
int memberCountValue = settings.MemberCount.Value;
var memberKeys = memberQuery.Select(x => x.Id);
var memberCountEqualQuery = personQuery.Where(p => p.Members.Count(gm => memberKeys.Contains(gm.Id)) == memberCountValue);
var compareEqualExpression = FilterExpressionExtractor.Extract <Model.Person>(memberCountEqualQuery, parameterExpression, "p") as BinaryExpression;
result = FilterExpressionExtractor.AlterComparisonType(comparisonType, compareEqualExpression, 0);
}
else
{
personQuery = personQuery.Where(p => memberQuery.Any(m => m.PersonId == p.Id));
result = FilterExpressionExtractor.Extract <Model.Person>(personQuery, parameterExpression, "p") as BinaryExpression;
}
return(result);
}
/// <summary>
/// Builds the expression.
/// </summary>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="idQuery">The id query.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <returns></returns>
private Expression BuildExpression(IService serviceInstance, IQueryable <int> idQuery, ParameterExpression parameterExpression)
{
int adultRoleId = GroupTypeCache.GetFamilyGroupType().Roles.Where(a => a.Guid == Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid()).Select(a => a.Id).FirstOrDefault();
int childRoleId = GroupTypeCache.GetFamilyGroupType().Roles.Where(a => a.Guid == Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid()).Select(a => a.Id).FirstOrDefault();
var qry = new PersonService((RockContext)serviceInstance.Context).Queryable()
.Where(p => p.Members.Where(a => a.GroupRoleId == childRoleId)
.Any(a => a.Group.Members
.Any(c => c.GroupRoleId == adultRoleId && idQuery.Contains(c.PersonId))));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 4)
{
var rockContext = ( RockContext )serviceInstance.Context;
var interactionQry = new InteractionService(rockContext).Queryable();
Guid interactionChannelGuid = selectionValues[0].AsGuid();
var interactionComponentGuid = selectionValues[1].AsGuidOrNull();
if (interactionComponentGuid.HasValue)
{
interactionQry = interactionQry.Where(xx => xx.InteractionComponent.Guid == interactionComponentGuid.Value);
}
else
{
interactionQry = interactionQry.Where(xx => xx.InteractionComponent.Channel.Guid == interactionChannelGuid);
}
string operation = string.Empty;
operation = selectionValues[2];
if (!string.IsNullOrEmpty(operation))
{
interactionQry = interactionQry.Where(xx => xx.Operation == operation);
}
string slidingDelimitedValues = selectionValues[3].Replace(',', '|');
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(slidingDelimitedValues);
if (dateRange.Start.HasValue)
{
interactionQry = interactionQry.Where(i => i.InteractionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
interactionQry = interactionQry.Where(i => i.InteractionDateTime <= dateRange.End.Value);
}
var innerQry = interactionQry.Select(xx => xx.PersonAlias.PersonId).AsQueryable();
var qry = new PersonService(( RockContext )serviceInstance.Context).Queryable()
.Where(p => innerQry.Any(xx => xx == p.Id));
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 2)
{
GroupMemberService groupMemberService = new GroupMemberService((RockContext)serviceInstance.Context);
int groupId = 0;
Guid groupGuid = selectionValues[0].AsGuid();
var groupService = new GroupService((RockContext)serviceInstance.Context);
var group = groupService.Get(groupGuid);
if (group != null)
{
groupId = group.Id;
}
bool includeChildGroups = false;
if (selectionValues.Length >= 3)
{
includeChildGroups = selectionValues[2].AsBooleanOrNull() ?? false;
}
var groupMemberServiceQry = groupMemberService.Queryable();
if (includeChildGroups)
{
var childGroupIds = groupService.GetAllDescendents(group.Id).Select(a => a.Id).Distinct().ToList();
groupMemberServiceQry = groupMemberServiceQry.Where(xx => xx.GroupId == groupId || childGroupIds.Contains(xx.GroupId));
}
else
{
groupMemberServiceQry = groupMemberServiceQry.Where(xx => xx.GroupId == groupId);
}
var groupRoleGuids = selectionValues[1].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(n => n.AsGuid()).ToList();
if (groupRoleGuids.Count() > 0)
{
groupMemberServiceQry = groupMemberServiceQry.Where(xx => groupRoleGuids.Contains(xx.GroupRole.Guid));
}
var qry = new PersonService((RockContext)serviceInstance.Context).Queryable()
.Where(p => groupMemberServiceQry.Any(xx => xx.PersonId == p.Id));
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var values = selection.Split('|');
ComparisonType comparisonType = values[0].ConvertToEnum <ComparisonType>(ComparisonType.EqualTo);
decimal? amountValue = values[1].AsDecimalOrNull();
var qry = new FinancialTransactionService((RockContext)serviceInstance.Context).Queryable();
var totalAmountEqualQuery = qry.Where(p => p.TransactionDetails.Sum(a => a.Amount) == amountValue);
BinaryExpression compareEqualExpression = FilterExpressionExtractor.Extract <Rock.Model.FinancialTransaction>(totalAmountEqualQuery, parameterExpression, "p") as BinaryExpression;
BinaryExpression result = FilterExpressionExtractor.AlterComparisonType(comparisonType, compareEqualExpression, null);
return(result);
}
/// <summary>
/// Creates a Linq Expression that can be applied to an IQueryable to filter the result set.
/// </summary>
/// <param name="entityType">The type of entity in the result set.</param>
/// <param name="serviceInstance">A service instance that can be queried to obtain the result set.</param>
/// <param name="parameterExpression">The input parameter that will be injected into the filter expression.</param>
/// <param name="selection">A formatted string representing the filter settings.</param>
/// <returns>
/// A Linq Expression that can be used to filter an IQueryable.
/// </returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var qry = new PersonService(( RockContext )serviceInstance.Context).Queryable();
var qryDuplicateEmail = new PersonService(( RockContext )serviceInstance.Context)
.Queryable()
.AsNoTracking()
.Where(p => p.Email != null && p.Email != string.Empty)
.GroupBy(p => p.Email)
.Where(g => g.Count() > 1)
.Select(g => g.Key);
qry = qry.Where(p => qryDuplicateEmail.Contains(p.Email));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
Guid groupGuid = selection.AsGuid();
var geoQry = new GroupService((RockContext)serviceInstance.Context)
.GetGeofencedFamilies(groupGuid)
.SelectMany(g => g.Members);
var qry = new PersonService((RockContext)serviceInstance.Context).Queryable()
.Where(p => geoQry.Any(xx => xx.PersonId == p.Id));
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
/// <summary>
/// Gets a filter expression for an attribute value.
/// </summary>
/// <param name="configurationValues">The configuration values.</param>
/// <param name="filterValues">The filter values.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <returns></returns>
public override Expression AttributeFilterExpression(Dictionary <string, ConfigurationValue> configurationValues, List <string> filterValues, ParameterExpression parameterExpression)
{
Expression comparison = null;
if (filterValues.Count > 1)
{
//// OR up the where clauses for each of the selected values
// and make sure to wrap commas around things so we don't collide with partial matches
// so it'll do something like this:
//
// WHERE ',' + Value + ',' like '%,bacon,%'
// OR ',' + Value + ',' like '%,lettuce,%'
// OR ',' + Value + ',' like '%,tomato,%'
// should be either "Contains" or "Not Contains"
ComparisonType comparisonType = filterValues[0].ConvertToEnum <ComparisonType>(ComparisonType.Contains);
List <string> selectedValues = filterValues[1].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (var selectedValue in selectedValues)
{
var searchValue = "," + selectedValue + ",";
var qryToExtract = new AttributeValueService(new Data.RockContext()).Queryable().Where(a => ("," + a.Value + ",").Contains(searchValue));
var valueExpression = FilterExpressionExtractor.Extract <AttributeValue>(qryToExtract, parameterExpression, "a");
if (comparisonType != ComparisonType.Contains)
{
valueExpression = Expression.Not(valueExpression);
}
if (comparison == null)
{
comparison = valueExpression;
}
else
{
comparison = Expression.Or(comparison, valueExpression);
}
}
}
if (comparison == null)
{
return(new NoAttributeFilterExpression());
}
return(comparison);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
DateTime currentDate = RockDateTime.Today;
int currentDayOfYear = currentDate.DayOfYear;
var values = selection.Split('|');
ComparisonType comparisonType = values[0].ConvertToEnum <ComparisonType>(ComparisonType.EqualTo);
int? ageValue = values[1].AsIntegerOrNull();
var rockContext = (RockContext)serviceInstance.Context;
var personAgeQuery = new PersonService(rockContext).Queryable();
MemberExpression idExpression = Expression.Property(parameterExpression, "Id");
Expression ageSelect = new Rock.Reporting.DataSelect.Person.AgeSelect().GetExpression(rockContext, idExpression, "");
if (values.Length >= 3 && comparisonType == ComparisonType.Between)
{
var numberRangeEditor = new NumberRangeEditor();
numberRangeEditor.DelimitedValues = values[2];
decimal ageValueStart = numberRangeEditor.LowerValue ?? 0;
decimal ageValueEnd = numberRangeEditor.UpperValue ?? decimal.MaxValue;
var personAgeBetweenQuery = personAgeQuery.Where(
p => ((p.BirthDate > SqlFunctions.DateAdd("year", -SqlFunctions.DateDiff("year", p.BirthDate, currentDate), currentDate)
? SqlFunctions.DateDiff("year", p.BirthDate, currentDate) - 1
: SqlFunctions.DateDiff("year", p.BirthDate, currentDate))
>= ageValueStart) && ((p.BirthDate > SqlFunctions.DateAdd("year", -SqlFunctions.DateDiff("year", p.BirthDate, currentDate), currentDate)
? SqlFunctions.DateDiff("year", p.BirthDate, currentDate) - 1
: SqlFunctions.DateDiff("year", p.BirthDate, currentDate))
<= ageValueEnd));
BinaryExpression result = FilterExpressionExtractor.Extract <Rock.Model.Person>(personAgeBetweenQuery, parameterExpression, "p") as BinaryExpression;
return(result);
}
else
{
var personAgeEqualQuery = personAgeQuery.Where(
p => (p.BirthDate > SqlFunctions.DateAdd("year", -SqlFunctions.DateDiff("year", p.BirthDate, currentDate), currentDate)
? SqlFunctions.DateDiff("year", p.BirthDate, currentDate) - 1
: SqlFunctions.DateDiff("year", p.BirthDate, currentDate))
== ageValue);
BinaryExpression compareEqualExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(personAgeEqualQuery, parameterExpression, "p") as BinaryExpression;
BinaryExpression result = FilterExpressionExtractor.AlterComparisonType(comparisonType, compareEqualExpression, null);
return(result);
}
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 1)
{
var contentChannelId = new ContentChannelService(new RockContext()).GetId(selectionValues[0].AsGuid());
var qry = new ContentChannelItemService(( RockContext )serviceInstance.Context).Queryable()
.Where(p => p.ContentChannelId == contentChannelId);
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.ContentChannelItem>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 2)
{
var courseId = selectionValues[1].AsInteger();
CourseRecordService courseRecordService = new CourseRecordService(( RockContext )serviceInstance.Context);
var recordQry = courseRecordService.Queryable();
var qry = new PersonService(( RockContext )serviceInstance.Context).Queryable();
switch (selectionValues[0])
{
case "0": //Has Completed
recordQry = recordQry.Where(r => r.CourseId == courseId && r.CompletionDateTime != null);
qry = qry.Where(p => recordQry.Select(r => r.PersonAlias.PersonId).Contains(p.Id));
break;
case "1": //Has Not Completed
recordQry = recordQry.Where(r => r.CourseId == courseId && r.CompletionDateTime != null);
qry = qry.Where(p => !recordQry.Select(r => r.PersonAlias.PersonId).Contains(p.Id));
break;
case "2": //Has Started Not Completed
var started = recordQry.Where(r => r.CourseId == courseId && r.CompletionDateTime == null)
.Select(r => r.PersonAlias.PersonId);
var completed = recordQry.Where(r => r.CourseId == courseId && r.CompletionDateTime != null)
.Select(r => r.PersonAlias.PersonId);
qry = qry.Where(p => started.Contains(p.Id) && !completed.Contains(p.Id));
break;
case "3": //Has Not Started
recordQry = recordQry.Where(r => r.CourseId == courseId);
qry = qry.Where(p => !recordQry.Select(r => r.PersonAlias.PersonId).Contains(p.Id));
break;
default:
break;
}
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var rockContext = (RockContext)serviceInstance.Context;
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 2)
{
Location location = new LocationService(rockContext).Get(selectionValues[0].AsGuid());
if (location == null)
{
return(null);
}
var selectedLocationGeoPoint = location.GeoPoint;
double miles = selectionValues[1].AsDoubleOrNull() ?? 0;
double meters = miles * Location.MetersPerMile;
GroupMemberService groupMemberService = new GroupMemberService(rockContext);
var groupTypeFamilyId = GroupTypeCache.GetFamilyGroupType().Id;
// limit to Family's Home Addresses that have are a real location (not a PO Box)
var groupMemberServiceQry = groupMemberService.Queryable()
.Where(xx => xx.Group.GroupTypeId == groupTypeFamilyId);
int groupLocationTypeHomeId = DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME.AsGuid()).Id;
// limit to distance LessThan specified distance (dbGeography uses meters for distance units)
groupMemberServiceQry = groupMemberServiceQry
.Where(xx =>
xx.Group.GroupLocations.Any(l =>
l.GroupLocationTypeValue.Id == groupLocationTypeHomeId &&
l.IsMappedLocation &&
selectedLocationGeoPoint.Buffer(meters).Intersects(l.Location.GeoPoint)
));
var qry = new PersonService(rockContext).Queryable()
.Where(p => groupMemberServiceQry.Any(xx => xx.PersonId == p.Id));
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
return(null);
}
/// <summary>
/// Creates a Linq Expression that can be applied to an IQueryable to filter the result set.
/// </summary>
/// <param name="entityType">The type of entity in the result set.</param>
/// <param name="serviceInstance">A service instance that can be queried to obtain the result set.</param>
/// <param name="parameterExpression">The input parameter that will be injected into the filter expression.</param>
/// <param name="selection">A formatted string representing the filter settings.</param>
/// <returns>
/// A Linq Expression that can be used to filter an IQueryable.
/// </returns>
/// <exception cref="System.Exception">Filter issue(s): + errorMessages.AsDelimited( ; )</exception>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var settings = new FilterSettings(selection);
var context = (RockContext)serviceInstance.Context;
// Get the Location Data View that defines the set of candidates from which proximate Locations can be selected.
var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent(settings.DataViewGuid, context);
// Evaluate the Data View that defines the candidate Locations.
var locationService = new LocationService(context);
var locationQuery = locationService.Queryable();
if (dataView != null)
{
locationQuery = DataComponentSettingsHelper.FilterByDataView(locationQuery, dataView, locationService);
}
// Get all the Family Groups that have a Location matching one of the candidate Locations.
int familyGroupTypeId = GroupTypeCache.Get(SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid()).Id;
var groupLocationsQuery = new GroupLocationService(context).Queryable()
.Where(gl => gl.Group.GroupTypeId == familyGroupTypeId && locationQuery.Any(l => l.Id == gl.LocationId));
// If a Location Type is specified, apply the filter condition.
if (settings.LocationTypeGuid.HasValue)
{
int groupLocationTypeId = DefinedValueCache.Get(settings.LocationTypeGuid.Value).Id;
groupLocationsQuery = groupLocationsQuery.Where(x => x.GroupLocationTypeValue.Id == groupLocationTypeId);
}
// Get all of the Group Members of the qualifying Families.
var groupMemberServiceQry = new GroupMemberService(context).Queryable()
.Where(gm => groupLocationsQuery.Any(gl => gl.GroupId == gm.GroupId));
// Get all of the People corresponding to the qualifying Group Members.
var qry = new PersonService(context).Queryable()
.Where(p => groupMemberServiceQry.Any(gm => gm.PersonId == p.Id));
// Retrieve the Filter Expression.
var extractedFilterExpression = FilterExpressionExtractor.Extract <Model.Person>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 2)
{
Guid tagGuid = selectionValues[1].AsGuid();
var tagItemQry = new TaggedItemService((RockContext)serviceInstance.Context).Queryable()
.Where(x => x.Tag.Guid == tagGuid);
var qry = new PersonService((RockContext)serviceInstance.Context).Queryable()
.Where(p => tagItemQry.Any(x => x.EntityGuid == p.Guid));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 1)
{
int?campusId = GetCampusIdFromSelection(selectionValues);
var qry = new GroupService((RockContext)serviceInstance.Context).Queryable()
.Where(p => p.CampusId == campusId);
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Group>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
return(null);
}
/// <summary>
/// Builds the expression.
/// </summary>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="idQuery">The id query.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <returns></returns>
private Expression BuildExpression(IService serviceInstance, IQueryable <int> idQuery, ParameterExpression parameterExpression)
{
var rockContext = ( RockContext )serviceInstance.Context;
var adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
var adultGroupRoleId = GroupTypeCache.Read(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY).Roles.FirstOrDefault(r => r.Guid == adultGuid).Id;
var marriedStatusId = DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.PERSON_MARITAL_STATUS_MARRIED).Id;
var familyGroupTypeId = GroupTypeCache.Read(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY).Id;
//
// This duplicates the functionality of the ufnCrm_GetSpousePersonIdFromPersonId function
// on a broader level. We are given a list of Person IDs so we need to handle more than one
// at a time. That is what the GroupBy method does at the end. We group by the original person
// Id and then order within that group to find the "first" spouse.
//
var sQuery = new GroupService(rockContext).Queryable()
.Join(rockContext.GroupMembers, f => f.Id, fm1 => fm1.GroupId, (f, fm1) => new { f, fm1 })
.Join(rockContext.People, x => x.fm1.PersonId, p1 => p1.Id, (x, p1) => new { x.f, x.fm1, p1 })
.Join(rockContext.GroupMembers, x => x.f.Id, fm2 => fm2.GroupId, (x, fm2) => new { x.f, x.fm1, x.p1, fm2 })
.Join(rockContext.People, x => x.fm2.PersonId, p2 => p2.Id, (x, p2) => new { x.f, x.fm1, x.p1, x.fm2, p2 })
.Where(x => x.f.GroupTypeId == familyGroupTypeId &&
idQuery.Contains(x.p1.Id) &&
x.fm1.GroupRoleId == adultGroupRoleId &&
x.fm2.GroupRoleId == adultGroupRoleId &&
x.p1.MaritalStatusValueId == marriedStatusId &&
x.p2.MaritalStatusValueId == marriedStatusId &&
x.p1.Id != x.p2.Id &&
(x.p1.Gender != x.p2.Gender || x.p1.Gender == Gender.Unknown || x.p2.Gender == Gender.Unknown))
.Select(x => new
{
p1 = x.p1,
p2 = x.p2,
dif = Math.Abs(DbFunctions.DiffDays(x.p1.BirthDate ?? new DateTime(1, 1, 1), x.p2.BirthDate ?? new DateTime(1, 1, 1)).Value)
})
.GroupBy(x => x.p1)
.Select(x => x.OrderBy(y => y.dif).ThenBy(y => y.p2.Id).FirstOrDefault().p2.Id);
//
// Finaly build a query that gives us all people who are either the original person or
// included in the spouse query.
//
var query = new PersonService(rockContext).Queryable()
.Where(p => sQuery.Contains(p.Id) || idQuery.Contains(p.Id));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(query, parameterExpression, "p"));
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length >= 2)
{
Guid?groupLocationTypeValueGuid = selectionValues[0].AsGuidOrNull();
Location location = new LocationService((RockContext)serviceInstance.Context).Get(selectionValues[1].AsGuid());
if (location == null)
{
return(null);
}
GroupService groupService = new GroupService((RockContext)serviceInstance.Context);
var qry = groupService.Queryable()
.Where(p => p.GroupLocations
.Where(l => l.GroupLocationTypeValue.Guid == groupLocationTypeValueGuid)
.Where(l => l.IsMappedLocation).Any());
// if a specific point was selected (whether a marker, or an address), we'll do a radial search
if (location.GeoPoint != null)
{
double miles = selectionValues[2].AsDoubleOrNull() ?? 0;
double meters = miles * Location.MetersPerMile;
// limit to distance LessThan specified distance (dbGeography uses meters for distance units)
qry = qry
.Where(p => p.GroupLocations.Any(l => l.Location.GeoPoint.Distance(location.GeoPoint) <= meters));
}
// otherwise if a geo fence was drawn, see what points intersect within it
else if (location.GeoFence != null)
{
qry = qry
.Where(p => p.GroupLocations.Any(l => l.Location.GeoPoint.Intersects(location.GeoFence)));
}
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.Group>(qry, parameterExpression, "p");
return(extractedFilterExpression);
}
return(null);
}
/// <summary>
/// Builds the expression.
/// </summary>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="idQuery">The id query.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <returns></returns>
private Expression BuildExpression(IService serviceInstance, IQueryable <int> idQuery, ParameterExpression parameterExpression)
{
var groupeType = GroupTypeCache.Get(Rock.SystemGuid.GroupType.GROUPTYPE_KNOWN_RELATIONSHIPS.AsGuid());
int ownerRoleId = groupeType.Roles.Where(a => a.Guid == SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_OWNER.AsGuid())
.Select(a => a.Id)
.FirstOrDefault();
int grandParentRoleId = groupeType.Roles.Where(a => a.Guid == SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_GRANDPARENT.AsGuid())
.Select(a => a.Id)
.FirstOrDefault();
var qry = new PersonService((RockContext)serviceInstance.Context).Queryable()
.Where(p => p.Members.Where(a => a.GroupRoleId == grandParentRoleId)
.Any(a => a.Group.Members
.Any(c => c.GroupRoleId == ownerRoleId && idQuery.Contains(c.PersonId))));
return(FilterExpressionExtractor.Extract <Rock.Model.Person>(qry, parameterExpression, "p"));
}
