/// <summary> /// Creates and returns a list of missing attendance occurrences for the specified date, scheduleId and groupLocationIds. /// </summary> /// <param name="occurrenceDate">The occurrence date.</param> /// <param name="scheduleId">The schedule identifier.</param> /// <param name="groupLocationIds">The group location ids.</param> /// <returns></returns> public List <AttendanceOccurrence> CreateMissingAttendanceOccurrences(DateTime occurrenceDate, int scheduleId, List <int> groupLocationIds) { var groupLocationQuery = new GroupLocationService(this.Context as RockContext).GetByIds(groupLocationIds); var attendanceOccurrencesQuery = this.Queryable() .Where(a => a.GroupId.HasValue && a.LocationId.HasValue && groupLocationQuery.Any(gl => gl.GroupId == a.GroupId && gl.LocationId == gl.LocationId) && a.ScheduleId == scheduleId && a.OccurrenceDate == occurrenceDate); var missingAttendanceOccurrences = groupLocationQuery.Where(gl => !attendanceOccurrencesQuery.Any(ao => ao.LocationId == gl.LocationId && ao.GroupId == gl.GroupId)) .ToList() .Select(gl => new AttendanceOccurrence { GroupId = gl.GroupId, Group = gl.Group, LocationId = gl.LocationId, Location = gl.Location, ScheduleId = scheduleId, OccurrenceDate = occurrenceDate }).ToList(); return(missingAttendanceOccurrences); }
/// <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.Read( 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.Read( 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> /// Creates and returns a list of missing attendance occurrences for the specified dates, scheduleId and groupLocationIds. /// These will be new AttendanceOccurrence records that haven't been saved to the database yet. /// </summary> /// <param name="occurrenceDateList">The occurrence date list.</param> /// <param name="scheduleId">The schedule identifier.</param> /// <param name="groupLocationIds">The group location ids.</param> /// <returns></returns> public List <AttendanceOccurrence> CreateMissingAttendanceOccurrences(List <DateTime> occurrenceDateList, int scheduleId, List <int> groupLocationIds) { if (!groupLocationIds.Any()) { return(new List <AttendanceOccurrence>()); } var groupLocationQuery = new GroupLocationService(this.Context as RockContext).GetByIds(groupLocationIds); int?groupId = null; int?locationId = null; if (groupLocationIds.Count == 1) { // if there is only one group location, we can optimize the attendanceOccurrencesQuery to use a simpler LINQ expression var groupLocationInfo = groupLocationQuery.Select(a => new { a.GroupId, a.LocationId }).FirstOrDefault(); groupId = groupLocationInfo.GroupId; locationId = groupLocationInfo?.LocationId; } List <AttendanceOccurrence> missingAttendanceOccurrenceList = new List <AttendanceOccurrence>(); foreach (var occurrenceDate in occurrenceDateList) { var attendanceOccurrencesQuery = this.Queryable() .Where(a => a.GroupId.HasValue && a.LocationId.HasValue && a.ScheduleId == scheduleId && a.OccurrenceDate == occurrenceDate); if (groupId.HasValue && locationId.HasValue) { // since we have just group location id (and date and schedule), we just have to check if the attendance occurrence exists attendanceOccurrencesQuery = attendanceOccurrencesQuery.Where(a => a.GroupId == groupId.Value && a.LocationId == locationId.Value); if (attendanceOccurrencesQuery.Any()) { continue; } else { missingAttendanceOccurrenceList.Add(new AttendanceOccurrence { GroupId = groupId.Value, LocationId = locationId.Value, ScheduleId = scheduleId, OccurrenceDate = occurrenceDate }); } continue; } else { attendanceOccurrencesQuery = attendanceOccurrencesQuery.Where(a => groupLocationQuery.Any(gl => gl.GroupId == a.GroupId && gl.LocationId == a.LocationId)); List <AttendanceOccurrence> missingAttendanceOccurrencesForOccurrenceDate = groupLocationQuery.Where(gl => !attendanceOccurrencesQuery.Any(ao => ao.LocationId == gl.LocationId && ao.GroupId == gl.GroupId)) .Select(gl => new { gl.GroupId, gl.LocationId, }) .AsNoTracking() .ToList() .Select(gl => new AttendanceOccurrence { GroupId = gl.GroupId, LocationId = gl.LocationId, ScheduleId = scheduleId, OccurrenceDate = occurrenceDate }).ToList(); if (missingAttendanceOccurrencesForOccurrenceDate.Any()) { missingAttendanceOccurrenceList.AddRange(missingAttendanceOccurrencesForOccurrenceDate); } } } return(missingAttendanceOccurrenceList); }
public IQueryable<MapItem> GetFamiliesMapInfo( int groupId, int statusId, string campusIds) { // Enable proxy creation since security is being checked and need to navigate parent authorities SetProxyCreation( true ); var group = ( (GroupService)Service ).Queryable( "GroupLocations.Location" ) .Where( g => g.Id == groupId ) .FirstOrDefault(); if ( group != null ) { var person = GetPerson(); if ( group.IsAuthorized( Rock.Security.Authorization.VIEW, person ) ) { var mapItems = new List<MapItem>(); foreach ( var location in group.GroupLocations .Where( l => l.Location.GeoFence != null ) .Select( l => l.Location ) ) { var familyGroupTypeId = GroupTypeCache.GetFamilyGroupType().Id; var recordStatusActiveId = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE.AsGuid() ).Id; var families = new GroupLocationService( (RockContext)Service.Context ).Queryable() .Where( l => l.IsMappedLocation && l.Group.GroupTypeId == familyGroupTypeId && l.Location.GeoPoint.Intersects( location.GeoFence ) && l.Group.Members.Any( m => m.Person.RecordStatusValueId.HasValue && m.Person.RecordStatusValueId == recordStatusActiveId && m.Person.ConnectionStatusValueId.HasValue && m.Person.ConnectionStatusValueId.Value == statusId ) ) .Select( l => new { l.Location, l.Group.Id, l.Group.Name, l.Group.CampusId, MinStatus = l.Group.Members .Where( m => m.Person.RecordStatusValueId.HasValue && m.Person.RecordStatusValueId == recordStatusActiveId && m.Person.ConnectionStatusValueId.HasValue ) .OrderBy( m => m.Person.ConnectionStatusValue.Order ) .Select( m => m.Person.ConnectionStatusValue.Id ) .FirstOrDefault() } ); var campusIdList = ( campusIds ?? string.Empty ).SplitDelimitedValues().AsIntegerList(); if ( campusIdList.Any() ) { families = families.Where( a => a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) ); } foreach ( var family in families.Where( f => f.MinStatus == statusId ) ) { var mapItem = new MapItem( family.Location ); mapItem.EntityTypeId = EntityTypeCache.Read( "Rock.Model.Group" ).Id; mapItem.EntityId = family.Id; mapItem.Name = family.Name; if ( mapItem.Point != null || mapItem.PolygonPoints.Any() ) { mapItems.Add( mapItem ); } } } return mapItems.AsQueryable(); } else { throw new HttpResponseException( HttpStatusCode.Unauthorized ); } } else { throw new HttpResponseException( HttpStatusCode.BadRequest ); } }
public IQueryable<MapItem> GetFamiliesMapInfo( int groupId, int statusId ) { var group = ( (GroupService)Service ).Queryable( "GroupLocations.Location" ) .Where( g => g.Id == groupId ) .FirstOrDefault(); if ( group != null ) { var person = GetPerson(); if ( group.IsAuthorized( Rock.Security.Authorization.VIEW, person ) ) { var mapItems = new List<MapItem>(); foreach ( var location in group.GroupLocations .Where( l => l.Location.GeoFence != null ) .Select( l => l.Location ) ) { var familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid(); var activeGuid = Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE.AsGuid(); var families = new GroupLocationService( (RockContext)Service.Context ).Queryable() .Where( l => l.IsMappedLocation && l.Group.GroupType.Guid.Equals( familyGuid ) && l.Location.GeoPoint.Intersects( location.GeoFence ) && l.Group.Members.Any( m => m.Person.RecordStatusValue != null && m.Person.RecordStatusValue.Guid.Equals(activeGuid) && m.Person.ConnectionStatusValueId.HasValue && m.Person.ConnectionStatusValueId.Value == statusId ) ) .Select( l => new { l.Location, l.Group.Id, l.Group.Name, MinStatus = l.Group.Members .Where( m => m.Person.RecordStatusValue != null && m.Person.RecordStatusValue.Guid.Equals( activeGuid ) && m.Person.ConnectionStatusValueId.HasValue ) .OrderBy( m => m.Person.ConnectionStatusValue.Order ) .Select( m => m.Person.ConnectionStatusValue.Id ) .FirstOrDefault() } ); foreach ( var family in families.Where( f => f.MinStatus == statusId ) ) { var mapItem = new MapItem( family.Location ); mapItem.EntityTypeId = EntityTypeCache.Read( "Rock.Model.Group" ).Id; mapItem.EntityId = family.Id; mapItem.Name = family.Name; if ( mapItem.Point != null || mapItem.PolygonPoints.Any() ) { mapItems.Add( mapItem ); } } } return mapItems.AsQueryable(); } else { throw new HttpResponseException( HttpStatusCode.Unauthorized ); } } else { throw new HttpResponseException( HttpStatusCode.BadRequest ); } }
/// <summary> /// Creates and returns a list of missing attendance occurrences for the specified dates, scheduleId and groupLocationIds. /// </summary> /// <param name="occurrenceDateList">The occurrence date list.</param> /// <param name="scheduleId">The schedule identifier.</param> /// <param name="groupLocationIds">The group location ids.</param> /// <returns></returns> public List <AttendanceOccurrence> CreateMissingAttendanceOccurrences(List <DateTime> occurrenceDateList, int scheduleId, List <int> groupLocationIds) { var groupLocationQuery = new GroupLocationService(this.Context as RockContext).GetByIds(groupLocationIds); List <AttendanceOccurrence> missingAttendanceOccurrenceList = new List <AttendanceOccurrence>(); foreach (var occurrenceDate in occurrenceDateList) { var attendanceOccurrencesQuery = this.Queryable() .Where(a => a.GroupId.HasValue && a.LocationId.HasValue && groupLocationQuery.Any(gl => gl.GroupId == a.GroupId && gl.LocationId == gl.LocationId) && a.ScheduleId == scheduleId && a.OccurrenceDate == occurrenceDate); List <AttendanceOccurrence> missingAttendanceOccurrencesForOccurrenceDate = groupLocationQuery.Where(gl => !attendanceOccurrencesQuery.Any(ao => ao.LocationId == gl.LocationId && ao.GroupId == gl.GroupId)) .ToList() .Select(gl => new AttendanceOccurrence { GroupId = gl.GroupId, Group = gl.Group, LocationId = gl.LocationId, Location = gl.Location, ScheduleId = scheduleId, OccurrenceDate = occurrenceDate }).ToList(); missingAttendanceOccurrenceList.AddRange(missingAttendanceOccurrencesForOccurrenceDate); } return(missingAttendanceOccurrenceList); }
/// <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 ); string postalCode = values[1]; var familyGroupTypeGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid(); var groupLocationQry = new GroupLocationService( ( RockContext ) serviceInstance.Context ).Queryable(); switch ( comparisonType ) { case ComparisonType.EqualTo: groupLocationQry = groupLocationQry.Where( gl => gl.Location.PostalCode == postalCode ); break; case ComparisonType.NotEqualTo: groupLocationQry = groupLocationQry.Where( gl => gl.Location.PostalCode != postalCode ); break; case ComparisonType.StartsWith: groupLocationQry = groupLocationQry.Where( gl => gl.Location.PostalCode.StartsWith( postalCode ) ); break; case ComparisonType.Contains: groupLocationQry = groupLocationQry.Where( gl => gl.Location.PostalCode.Contains( postalCode ) ); break; case ComparisonType.DoesNotContain: groupLocationQry = groupLocationQry.Where( gl => !gl.Location.PostalCode.Contains( postalCode ) ); break; case ComparisonType.IsBlank: groupLocationQry = groupLocationQry .Where( gl => gl.Location.PostalCode == null || gl.Location.PostalCode == string.Empty ); break; case ComparisonType.IsNotBlank: groupLocationQry = groupLocationQry .Where( gl => gl.Location.PostalCode != null && gl.Location.PostalCode != string.Empty ); break; case ComparisonType.EndsWith: groupLocationQry = groupLocationQry.Where( gl => gl.Location.PostalCode.EndsWith( postalCode ) ); break; default: break; } IQueryable<Rock.Model.Person> qry; var groupMemberQry = groupLocationQry.Select( gl => gl.Group ) .Where( g => g.GroupType.Guid == familyGroupTypeGuid ) .SelectMany( g => g.Members ); // Families which do not have locations need to be added separately if ( comparisonType == ComparisonType.IsBlank || comparisonType == ComparisonType.DoesNotContain || comparisonType == ComparisonType.NotEqualTo ) { var noLocationGroupMembersQry = new GroupService( ( RockContext ) serviceInstance.Context ).Queryable() .Where( g => g.GroupType.Guid == familyGroupTypeGuid && !g.GroupLocations.Any() ) .SelectMany( g => g.Members ); qry = new PersonService( ( RockContext ) serviceInstance.Context ).Queryable() .Where( p => groupMemberQry.Any( xx => xx.PersonId == p.Id ) || noLocationGroupMembersQry.Any( xx => xx.PersonId == p.Id ) ); } else { qry = new PersonService( ( RockContext ) serviceInstance.Context ).Queryable() .Where( p => groupMemberQry.Any( xx => xx.PersonId == p.Id ) ); } Expression extractedFilterExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( qry, parameterExpression, "p" ); return extractedFilterExpression; }