/// <summary> /// Loads the dropdowns. /// </summary> private void BindLocations() { var locations = new Dictionary<int, string> { { 0, "" } }; if ( _group != null ) { var locationPaths = new Dictionary<int, string>(); var locationService = new LocationService( _rockContext ); foreach ( var location in _group.GroupLocations .Where( l => l.Location.Name != null && l.Location.Name != "" ) .Select( l => l.Location ) ) { // Get location path string parentLocationPath = string.Empty; if ( location.ParentLocationId.HasValue ) { var locId = location.ParentLocationId.Value; if ( !locationPaths.ContainsKey( locId ) ) { locationPaths.Add( locId, locationService.GetPath( locId ) ); } parentLocationPath = locationPaths[locId]; } if ( !locations.ContainsKey( location.Id ) ) { locations.Add( location.Id, new List<string> { parentLocationPath, location.Name }.AsDelimited( " > " ) ); } } } if ( locations.Any() ) { ddlLocation.DataSource = locations; ddlLocation.DataBind(); } }
/// <summary> /// Gets occurrence data for the selected group /// </summary> /// <param name="group">The group.</param> /// <param name="fromDateTime">From date time.</param> /// <param name="toDateTime">To date time.</param> /// <param name="locationIds">The location ids.</param> /// <param name="scheduleIds">The schedule ids.</param> /// <param name="loadSummaryData">if set to <c>true</c> [load summary data].</param> /// <param name="campusId">The campus identifier.</param> /// <returns></returns> public List <ScheduleOccurrence> GetGroupOccurrences(Group group, DateTime?fromDateTime, DateTime?toDateTime, List <int> locationIds, List <int> scheduleIds, bool loadSummaryData, int?campusId) { var occurrences = new List <ScheduleOccurrence>(); if (group != null) { var rockContext = (RockContext)this.Context; var attendanceService = new AttendanceService(rockContext); var scheduleService = new ScheduleService(rockContext); var locationService = new LocationService(rockContext); using (new Rock.Data.QueryHintScope(rockContext, QueryHintType.RECOMPILE)) { // Set up an 'occurrences' query for the group var qry = attendanceService .Queryable().AsNoTracking() .Where(a => a.GroupId == group.Id); // Filter by date range if (fromDateTime.HasValue) { var fromDate = fromDateTime.Value.Date; qry = qry.Where(a => DbFunctions.TruncateTime(a.StartDateTime) >= (fromDate)); } if (toDateTime.HasValue) { var toDate = toDateTime.Value.Date; qry = qry.Where(a => DbFunctions.TruncateTime(a.StartDateTime) < (toDate)); } // Location Filter if (locationIds.Any()) { qry = qry.Where(a => locationIds.Contains(a.LocationId ?? 0)); } // Schedule Filter if (scheduleIds.Any()) { qry = qry.Where(a => scheduleIds.Contains(a.ScheduleId ?? 0)); } // Get the unique combination of location/schedule/date for the selected group var occurrenceDates = qry .Select(a => new { a.LocationId, a.ScheduleId, Date = DbFunctions.TruncateTime(a.StartDateTime) }) .Distinct() .ToList(); // Get the locations for each unique location id var selectedlocationIds = occurrenceDates.Select(o => o.LocationId).Distinct().ToList(); var locations = locationService .Queryable().AsNoTracking() .Where(l => selectedlocationIds.Contains(l.Id)) .Select(l => new { l.Id, l.ParentLocationId, l.Name }) .ToList(); var locationNames = new Dictionary <int, string>(); locations.ForEach(l => locationNames.Add(l.Id, l.Name)); // Get the parent location path for each unique location var parentlocationPaths = new Dictionary <int, string>(); locations .Where(l => l.ParentLocationId.HasValue) .Select(l => l.ParentLocationId.Value) .Distinct() .ToList() .ForEach(l => parentlocationPaths.Add(l, locationService.GetPath(l))); var locationPaths = new Dictionary <int, string>(); locations .Where(l => l.ParentLocationId.HasValue) .ToList() .ForEach(l => locationPaths.Add(l.Id, parentlocationPaths[l.ParentLocationId.Value])); // Get the schedules for each unique schedule id var selectedScheduleIds = occurrenceDates.Select(o => o.ScheduleId).Distinct().ToList(); var schedules = scheduleService .Queryable().AsNoTracking() .Where(s => selectedScheduleIds.Contains(s.Id)) .ToList(); var scheduleNames = new Dictionary <int, string>(); var scheduleStartTimes = new Dictionary <int, TimeSpan>(); schedules .ForEach(s => { scheduleNames.Add(s.Id, s.Name); scheduleStartTimes.Add(s.Id, s.StartTimeOfDay); }); foreach (var occurrence in occurrenceDates.Where(o => o.Date.HasValue)) { occurrences.Add( new ScheduleOccurrence( occurrence.Date.Value, occurrence.ScheduleId.HasValue && scheduleStartTimes.ContainsKey(occurrence.ScheduleId.Value) ? scheduleStartTimes[occurrence.ScheduleId.Value] : new TimeSpan(), occurrence.ScheduleId, occurrence.ScheduleId.HasValue && scheduleNames.ContainsKey(occurrence.ScheduleId.Value) ? scheduleNames[occurrence.ScheduleId.Value] : string.Empty, occurrence.LocationId, occurrence.LocationId.HasValue && locationNames.ContainsKey(occurrence.LocationId.Value) ? locationNames[occurrence.LocationId.Value] : string.Empty, occurrence.LocationId.HasValue && locationPaths.ContainsKey(occurrence.LocationId.Value) ? locationPaths[occurrence.LocationId.Value] : string.Empty )); } } // Load the attendance data for each occurrence if (loadSummaryData && occurrences.Any()) { var minDate = occurrences.Min(o => o.Date); var maxDate = occurrences.Max(o => o.Date).AddDays(1); var attendanceQry = attendanceService .Queryable().AsNoTracking() .Where(a => a.GroupId.HasValue && a.GroupId == group.Id && a.StartDateTime >= minDate && a.StartDateTime < maxDate && a.PersonAlias != null && a.PersonAliasId.HasValue) .Select(a => new { a.LocationId, a.ScheduleId, a.StartDateTime, a.DidAttend, a.DidNotOccur, a.PersonAliasId, PersonId = a.PersonAlias.PersonId }); if (campusId.HasValue) { var familyGroupType = GroupTypeCache.Read(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid()); var campusQry = new GroupMemberService(rockContext) .Queryable() .Where(g => g.Group != null && g.Group.GroupTypeId == familyGroupType.Id && g.Group.CampusId.HasValue && g.Group.CampusId.Value == campusId.Value ) .Select(m => m.PersonId); attendanceQry = attendanceQry .Where(s => campusQry.Contains(s.PersonId)); } var attendances = attendanceQry.ToList(); foreach (var summary in attendances .GroupBy(a => new { a.LocationId, a.ScheduleId, Date = a.StartDateTime.Date }) .Select(a => new { a.Key.LocationId, a.Key.ScheduleId, a.Key.Date, DidAttendCount = a .Where(t => t.DidAttend.HasValue && t.DidAttend.Value) .Select(t => t.PersonAliasId.Value) .Distinct() .Count(), DidNotOccurCount = a .Where(t => t.DidNotOccur.HasValue && t.DidNotOccur.Value) .Select(t => t.PersonAliasId.Value) .Distinct() .Count(), TotalCount = a .Select(t => t.PersonAliasId) .Distinct() .Count() })) { var occurrence = occurrences .Where(o => o.ScheduleId.Equals(summary.ScheduleId) && o.LocationId.Equals(summary.LocationId) && o.Date.Equals(summary.Date)) .FirstOrDefault(); if (occurrence != null) { occurrence.DidAttendCount = summary.DidAttendCount; occurrence.DidNotOccurCount = summary.DidNotOccurCount; occurrence.TotalCount = summary.TotalCount; } } } // Create any missing occurrences from the group's schedule (not location schedules) Schedule groupSchedule = null; if (group.ScheduleId.HasValue) { groupSchedule = group.Schedule; if (groupSchedule == null) { groupSchedule = new ScheduleService(rockContext).Get(group.ScheduleId.Value); } } if (groupSchedule != null) { var newOccurrences = new List <ScheduleOccurrence>(); var existingDates = occurrences .Where(o => o.ScheduleId.Equals(groupSchedule.Id)) .Select(o => o.Date) .Distinct() .ToList(); var startDate = fromDateTime.HasValue ? fromDateTime.Value : RockDateTime.Today.AddMonths(-2); var endDate = toDateTime.HasValue ? toDateTime.Value : RockDateTime.Today.AddDays(1); DDay.iCal.Event calEvent = groupSchedule.GetCalenderEvent(); if (calEvent != null) { // If schedule has an iCal schedule, get all the past occurrences foreach (var occurrence in calEvent.GetOccurrences(startDate, endDate)) { var scheduleOccurrence = new ScheduleOccurrence( occurrence.Period.StartTime.Date, occurrence.Period.StartTime.TimeOfDay, groupSchedule.Id, groupSchedule.Name); if (!existingDates.Contains(scheduleOccurrence.Date)) { newOccurrences.Add(scheduleOccurrence); } } } else { // if schedule does not have an iCal, then check for weekly schedule and calculate occurrences starting with first attendance or current week if (groupSchedule.WeeklyDayOfWeek.HasValue) { // default to start with date 2 months earlier startDate = fromDateTime.HasValue ? fromDateTime.Value : RockDateTime.Today.AddMonths(-2); if (existingDates.Any(d => d < startDate)) { startDate = existingDates.Min(); } // Back up start time to the correct day of week while (startDate.DayOfWeek != groupSchedule.WeeklyDayOfWeek.Value) { startDate = startDate.AddDays(-1); } // Add the start time if (groupSchedule.WeeklyTimeOfDay.HasValue) { startDate = startDate.Add(groupSchedule.WeeklyTimeOfDay.Value); } // Create occurrences up to current time while (startDate < endDate) { if (!existingDates.Contains(startDate.Date)) { var scheduleOccurrence = new ScheduleOccurrence(startDate.Date, startDate.TimeOfDay, groupSchedule.Id, groupSchedule.Name); newOccurrences.Add(scheduleOccurrence); } startDate = startDate.AddDays(7); } } } if (newOccurrences.Any()) { // Filter Exclusions var groupType = GroupTypeCache.Read(group.GroupTypeId); foreach (var exclusion in groupType.GroupScheduleExclusions) { if (exclusion.Start.HasValue && exclusion.End.HasValue) { foreach (var occurrence in newOccurrences.ToList()) { if (occurrence.Date >= exclusion.Start.Value && occurrence.Date < exclusion.End.Value.AddDays(1)) { newOccurrences.Remove(occurrence); } } } } } foreach (var occurrence in newOccurrences) { occurrences.Add(occurrence); } } } return(occurrences); }
/// <summary> /// Binds the filter. /// </summary> protected void BindFilter() { string dateRangePreference = rFilter.GetUserPreference( MakeKeyUniqueToGroup( "Date Range" ) ); if ( string.IsNullOrWhiteSpace( dateRangePreference ) ) { // set the dateRangePreference to force rFilter_DisplayFilterValue to show our default three month limit dateRangePreference = ","; rFilter.SaveUserPreference( MakeKeyUniqueToGroup( "Date Range" ), "Date Range", dateRangePreference ); } var dateRange = DateRangePicker.CalculateDateRangeFromDelimitedValues( dateRangePreference ); // if there is no start date, default to three months ago to minimize the chance of loading too much data drpDates.LowerValue = dateRange.Start ?? RockDateTime.Today.AddMonths( -3 ); drpDates.UpperValue = dateRange.End; if ( _group != null ) { var grouplocations = _group.GroupLocations .Where( l => l.Location.Name != null && l.Location.Name != string.Empty ) .ToList(); var locations = new Dictionary<string, string> { { string.Empty, string.Empty } }; var locationService = new LocationService( _rockContext ); foreach ( var location in grouplocations.Select( l => l.Location ) ) { if ( !locations.ContainsKey( location.Id.ToString() ) ) { locations.Add( location.Id.ToString(), locationService.GetPath( location.Id ) ); } var parentLocation = location.ParentLocation; while ( parentLocation != null ) { string key = string.Format( "P{0}", parentLocation.Id ); if ( !locations.ContainsKey( key ) ) { locations.Add( key, locationService.GetPath( parentLocation.Id ) ); } parentLocation = parentLocation.ParentLocation; } } if ( locations.Any() ) { ddlLocation.Visible = true; gOccurrences.Columns[2].Visible = true; ddlLocation.DataSource = locations.OrderBy( l => l.Value ); ddlLocation.DataBind(); ddlLocation.SetValue( rFilter.GetUserPreference( MakeKeyUniqueToGroup( "Location" ) ) ); } else { ddlLocation.Visible = false; gOccurrences.Columns[2].Visible = false; } var schedules = new Dictionary<int, string> { { 0, string.Empty } }; grouplocations.SelectMany( l => l.Schedules ).OrderBy( s => s.Name ).ToList() .ForEach( s => schedules.AddOrIgnore( s.Id, s.Name ) ); if ( schedules.Any() ) { ddlSchedule.Visible = true; gOccurrences.Columns[1].Visible = true; ddlSchedule.DataSource = schedules; ddlSchedule.DataBind(); ddlSchedule.SetValue( rFilter.GetUserPreference( MakeKeyUniqueToGroup( "Schedule" ) ) ); } else { ddlSchedule.Visible = false; gOccurrences.Columns[1].Visible = false; } } }
/// <summary> /// Gets occurrence data for the selected group /// </summary> /// <param name="group">The group.</param> /// <param name="fromDateTime">From date time.</param> /// <param name="toDateTime">To date time.</param> /// <param name="locationIds">The location ids.</param> /// <param name="scheduleIds">The schedule ids.</param> /// <param name="loadSummaryData">if set to <c>true</c> [load summary data].</param> /// <returns></returns> public List<ScheduleOccurrence> GetGroupOccurrences( Group group, DateTime? fromDateTime, DateTime? toDateTime, List<int> locationIds, List<int> scheduleIds, bool loadSummaryData ) { var occurrences = new List<ScheduleOccurrence>(); if ( group != null ) { var rockContext = (RockContext)this.Context; var attendanceService = new AttendanceService( rockContext ); var scheduleService = new ScheduleService( rockContext ); var locationService = new LocationService( rockContext ); // Set up an 'occurrences' query for the group var qry = attendanceService .Queryable().AsNoTracking() .Where( a => a.GroupId == group.Id ); // Filter by date range if ( fromDateTime.HasValue ) { var fromDate = fromDateTime.Value.Date; qry = qry.Where( a => DbFunctions.TruncateTime( a.StartDateTime ) >= ( fromDate ) ); } if ( toDateTime.HasValue ) { var toDate = toDateTime.Value.Date; qry = qry.Where( a => DbFunctions.TruncateTime( a.StartDateTime ) < ( toDate ) ); } // Location Filter if ( locationIds.Any() ) { qry = qry.Where( a => a.LocationId.HasValue && locationIds.Contains( a.LocationId.Value ) ); } // Schedule Filter if ( scheduleIds.Any() ) { qry = qry.Where( a => a.ScheduleId.HasValue && scheduleIds.Contains( a.ScheduleId.Value ) ); } // Get the unique combination of location/schedule/date for the selected group var occurrenceDates = qry .Select( a => new { a.LocationId, a.ScheduleId, Date = DbFunctions.TruncateTime( a.StartDateTime ) } ) .Distinct() .ToList(); // Get the locations for each unique location id var selectedlocationIds = occurrenceDates.Select( o => o.LocationId ).Distinct().ToList(); var locations = locationService .Queryable().AsNoTracking() .Where( l => selectedlocationIds.Contains( l.Id ) ) .Select( l => new { l.Id, l.ParentLocationId, l.Name } ) .ToList(); var locationNames = new Dictionary<int, string>(); locations.ForEach( l => locationNames.Add( l.Id, l.Name ) ); // Get the parent location path for each unique location var parentlocationPaths = new Dictionary<int, string>(); locations .Where( l => l.ParentLocationId.HasValue ) .Select( l => l.ParentLocationId.Value ) .Distinct() .ToList() .ForEach( l => parentlocationPaths.Add( l, locationService.GetPath( l ) ) ); var locationPaths = new Dictionary<int, string>(); locations .Where( l => l.ParentLocationId.HasValue ) .ToList() .ForEach( l => locationPaths.Add( l.Id, parentlocationPaths[l.ParentLocationId.Value] ) ); // Get the schedules for each unique schedule id var selectedScheduleIds = occurrenceDates.Select( o => o.ScheduleId ).Distinct().ToList(); var schedules = scheduleService .Queryable().AsNoTracking() .Where( s => selectedScheduleIds.Contains( s.Id ) ) .ToList(); var scheduleNames = new Dictionary<int, string>(); var scheduleStartTimes = new Dictionary<int, TimeSpan>(); schedules .ForEach( s => { scheduleNames.Add( s.Id, s.Name ); scheduleStartTimes.Add( s.Id, s.StartTimeOfDay ); }); foreach ( var occurrence in occurrenceDates.Where( o => o.Date.HasValue ) ) { occurrences.Add( new ScheduleOccurrence( occurrence.Date.Value, occurrence.ScheduleId.HasValue && scheduleStartTimes.ContainsKey( occurrence.ScheduleId.Value ) ? scheduleStartTimes[occurrence.ScheduleId.Value] : new TimeSpan(), occurrence.ScheduleId, occurrence.ScheduleId.HasValue && scheduleNames.ContainsKey( occurrence.ScheduleId.Value ) ? scheduleNames[occurrence.ScheduleId.Value] : string.Empty, occurrence.LocationId, occurrence.LocationId.HasValue && locationNames.ContainsKey( occurrence.LocationId.Value ) ? locationNames[occurrence.LocationId.Value] : string.Empty, occurrence.LocationId.HasValue && locationPaths.ContainsKey( occurrence.LocationId.Value ) ? locationPaths[occurrence.LocationId.Value] : string.Empty ) ); } // Load the attendance data for each occurrence if ( loadSummaryData && occurrences.Any()) { var minDate = occurrences.Min( o => o.Date ); var maxDate = occurrences.Max( o => o.Date ).AddDays( 1 ); foreach( var summary in attendanceService .Queryable().AsNoTracking() .Where( a => a.PersonAliasId.HasValue && a.GroupId.HasValue && a.GroupId == group.Id && a.StartDateTime >= minDate && a.StartDateTime < maxDate ) .GroupBy( a => new { a.LocationId, a.ScheduleId, Date = DbFunctions.TruncateTime( a.StartDateTime ) } ) .Select( a => new { a.Key.LocationId, a.Key.ScheduleId, a.Key.Date, DidAttendCount = a .Where( t => t.DidAttend.HasValue && t.DidAttend.Value ) .Select( t => t.PersonAliasId.Value ) .Distinct() .Count(), DidNotOccurCount = a .Where( t => t.DidNotOccur.HasValue && t.DidNotOccur.Value ) .Select( t => t.PersonAliasId.Value ) .Distinct() .Count(), TotalCount = a .Select( t => t.PersonAliasId ) .Distinct() .Count() } ) ) { var occurrence = occurrences .Where( o => o.ScheduleId.Equals( summary.ScheduleId ) && o.LocationId.Equals( summary.LocationId ) && o.Date.Equals( summary.Date ) ) .FirstOrDefault(); if ( occurrence != null ) { occurrence.DidAttendCount = summary.DidAttendCount; occurrence.DidNotOccurCount = summary.DidNotOccurCount; occurrence.TotalCount = summary.TotalCount; } } } // Create any missing occurrences from the group's schedule (not location schedules) Schedule groupSchedule = null; if ( group.ScheduleId.HasValue ) { groupSchedule = group.Schedule; if ( groupSchedule == null ) { groupSchedule = new ScheduleService( rockContext ).Get( group.ScheduleId.Value ); } } if ( groupSchedule != null ) { var newOccurrences = new List<ScheduleOccurrence>(); var existingDates = occurrences .Where( o => o.ScheduleId.Equals( groupSchedule.Id ) ) .Select( o => o.Date ) .Distinct() .ToList(); var startDate = fromDateTime.HasValue ? fromDateTime.Value : RockDateTime.Today.AddMonths( -2 ); var endDate = toDateTime.HasValue ? toDateTime.Value : RockDateTime.Today.AddDays( 1 ); DDay.iCal.Event calEvent = groupSchedule.GetCalenderEvent(); if ( calEvent != null ) { // If schedule has an iCal schedule, get all the past occurrences foreach ( var occurrence in calEvent.GetOccurrences( startDate, endDate ) ) { var scheduleOccurrence = new ScheduleOccurrence( occurrence.Period.StartTime.Date, occurrence.Period.StartTime.TimeOfDay, groupSchedule.Id, groupSchedule.Name ); if ( !existingDates.Contains( scheduleOccurrence.Date ) ) { newOccurrences.Add( scheduleOccurrence ); } } } else { // if schedule does not have an iCal, then check for weekly schedule and calculate occurrences starting with first attendance or current week if ( groupSchedule.WeeklyDayOfWeek.HasValue ) { // default to start with date 2 months earlier startDate = fromDateTime.HasValue ? fromDateTime.Value : RockDateTime.Today.AddMonths( -2 ); if ( existingDates.Any( d => d < startDate ) ) { startDate = existingDates.Min(); } // Back up start time to the correct day of week while ( startDate.DayOfWeek != groupSchedule.WeeklyDayOfWeek.Value ) { startDate = startDate.AddDays( -1 ); } // Add the start time if ( groupSchedule.WeeklyTimeOfDay.HasValue ) { startDate = startDate.Add( groupSchedule.WeeklyTimeOfDay.Value ); } // Create occurrences up to current time while ( startDate < endDate ) { if ( !existingDates.Contains( startDate.Date ) ) { var scheduleOccurrence = new ScheduleOccurrence( startDate.Date, startDate.TimeOfDay, groupSchedule.Id, groupSchedule.Name ); newOccurrences.Add( scheduleOccurrence ); } startDate = startDate.AddDays( 7 ); } } } if ( newOccurrences.Any() ) { // Filter Exclusions var groupType = GroupTypeCache.Read( group.GroupTypeId ); foreach ( var exclusion in groupType.GroupScheduleExclusions ) { if ( exclusion.Start.HasValue && exclusion.End.HasValue ) { foreach ( var occurrence in newOccurrences.ToList() ) { if ( occurrence.Date >= exclusion.Start.Value && occurrence.Date < exclusion.End.Value.AddDays( 1 ) ) { newOccurrences.Remove( occurrence ); } } } } } foreach( var occurrence in newOccurrences ) { occurrences.Add( occurrence ); } } } return occurrences; }