/// <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")); }