/// <summary> /// Gets the join queryable of AttendanceOccurrence, GroupLocation, and GroupLocationScheduleConfig for the specified occurrenceDate, scheduleId and groupLocationIds /// </summary> /// <param name="occurrenceDateList">The occurrence date list.</param> /// <param name="scheduleIds">The schedule ids.</param> /// <param name="groupLocationIds">The group location ids.</param> /// <returns></returns> public IQueryable <AttendanceOccurrenceGroupLocationScheduleConfigJoinResult> AttendanceOccurrenceGroupLocationScheduleConfigJoinQuery(List <DateTime> occurrenceDateList, List <int> scheduleIds, 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 && a.ScheduleId.HasValue && groupLocationQuery.Any(gl => gl.GroupId == a.GroupId && gl.LocationId == a.LocationId) && scheduleIds.Contains(a.ScheduleId.Value) && occurrenceDateList.Contains(a.OccurrenceDate)); // join with the GroupLocation var joinQuery = from ao in attendanceOccurrencesQuery join gl in groupLocationQuery on new { LocationId = ao.LocationId.Value, GroupId = ao.GroupId.Value } equals new { gl.LocationId, gl.GroupId } select new AttendanceOccurrenceGroupLocationScheduleConfigJoinResult { AttendanceOccurrence = ao, GroupLocation = gl, GroupLocationScheduleConfig = gl.GroupLocationScheduleConfigs .Where(c => c.ScheduleId == ao.ScheduleId).FirstOrDefault() }; return(joinQuery); }
/// <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> /// 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> /// Gets the locations for the Group and Schedule /// </summary> /// <param name="scheduleId">The schedule identifier.</param> /// <param name="groupId">The group identifier.</param> /// <returns></returns> public IQueryable <Location> GetByGroupSchedule(int scheduleId, int groupId) { var groupLocationQuery = new GroupLocationService(this.Context as RockContext).Queryable().Where(gl => gl.Schedules.Any(s => s.Id == scheduleId) && gl.GroupId == groupId); return(this.Queryable().Where(l => groupLocationQuery.Any(gl => gl.LocationId == l.Id))); }
/// <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); }
/// <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 locationGuid = selection.AsGuid(); RockContext rockContext = ( RockContext ) serviceInstance.Context; DbGeography geoFence = new LocationService( rockContext ) .Get( locationGuid ).GeoFence; Guid familyGroupTypeGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid(); int familyGroupTypeId = new GroupTypeService( rockContext ).Get( familyGroupTypeGuid ).Id; var geoQry = new GroupLocationService( rockContext ) .GetMappedLocationsByGeofences( new List<DbGeography> { geoFence } ) .Where( gl => gl.Group.GroupType.Id == familyGroupTypeId ) .SelectMany( g => g.Group.Members ); var qry = new PersonService( rockContext ).Queryable() .Where( p => geoQry.Any( xx => xx.PersonId == p.Id ) ); Expression extractedFilterExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( qry, parameterExpression, "p" ); return extractedFilterExpression; }