/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType"></param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
/// <exception cref="System.Exception">Filter issue(s): + errorMessages.AsDelimited( ; )</exception>
public override Expression GetExpression( Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection )
{
int dataviewId = int.MinValue;
if ( int.TryParse( selection, out dataviewId ) )
{
var dataView = new DataViewService( (RockContext)serviceInstance.Context ).Get( dataviewId );
if ( dataView != null && dataView.DataViewFilter != null )
{
// Verify that there is not a child filter that uses this view (would result in stack-overflow error)
if ( !IsViewInFilter( dataView.Id, dataView.DataViewFilter ) )
{
// TODO: Should probably verify security again on the selected dataview and it's filters,
// as that could be a moving target.
var errorMessages = new List<string>();
Expression expression = dataView.GetExpression( serviceInstance, parameterExpression, out errorMessages );
if ( errorMessages.Any() )
{
throw new System.Exception( "Filter issue(s): " + errorMessages.AsDelimited( "; " ) );
}
return expression;
}
}
}
return null;
}
/// <summary>
/// Binds the attendees grid.
/// </summary>
private void BindAttendeesGrid()
{
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
if ( dateRange.End == null || dateRange.End > RockDateTime.Now )
{
dateRange.End = RockDateTime.Now;
}
var rockContext = new RockContext();
// make a qryPersonAlias so that the generated SQL will be a "WHERE .. IN ()" instead of an OUTER JOIN (which is incredibly slow for this)
var qryPersonAlias = new PersonAliasService( rockContext ).Queryable();
var qryAttendance = new AttendanceService( rockContext ).Queryable();
qryAttendance = qryAttendance.Where( a => a.DidAttend.HasValue && a.DidAttend.Value );
var groupType = this.GetSelectedTemplateGroupType();
var qryAllVisits = qryAttendance;
if ( groupType != null )
{
var childGroupTypeIds = new GroupTypeService( rockContext ).GetChildGroupTypes( groupType.Id ).Select( a => a.Id );
qryAllVisits = qryAttendance.Where( a => childGroupTypeIds.Any( b => b == a.Group.GroupTypeId ) );
}
else
{
return;
}
var groupIdList = new List<int>();
string groupIds = GetSelectedGroupIds().AsDelimited( "," );
if ( !string.IsNullOrWhiteSpace( groupIds ) )
{
groupIdList = groupIds.Split( ',' ).AsIntegerList();
qryAttendance = qryAttendance.Where( a => a.GroupId.HasValue && groupIdList.Contains( a.GroupId.Value ) );
}
//// If campuses were included, filter attendances by those that have selected campuses
//// if 'null' is one of the campuses, treat that as a 'CampusId is Null'
var includeNullCampus = clbCampuses.SelectedValues.Any( a => a.Equals( "null", StringComparison.OrdinalIgnoreCase ) );
var campusIdList = clbCampuses.SelectedValues.AsIntegerList();
// remove 0 from the list, just in case it is there
campusIdList.Remove( 0 );
if ( campusIdList.Any() )
{
if ( includeNullCampus )
{
// show records that have a campusId in the campusIdsList + records that have a null campusId
qryAttendance = qryAttendance.Where( a => ( a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) ) || !a.CampusId.HasValue );
}
else
{
// only show records that have a campusId in the campusIdList
qryAttendance = qryAttendance.Where( a => a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) );
}
}
else if ( includeNullCampus )
{
// 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId
qryAttendance = qryAttendance.Where( a => !a.CampusId.HasValue );
}
// have the "Missed" query be the same as the qry before the Main date range is applied since it'll have a different date range
var qryMissed = qryAttendance;
if ( dateRange.Start.HasValue )
{
qryAttendance = qryAttendance.Where( a => a.StartDateTime >= dateRange.Start.Value );
}
if ( dateRange.End.HasValue )
{
qryAttendance = qryAttendance.Where( a => a.StartDateTime < dateRange.End.Value );
}
// we want to get the first 2 visits at a minimum so we can show the date in the grid
int nthVisitsTake = 2;
int? byNthVisit = null;
if ( radByVisit.Checked )
{
// If we are filtering by nth visit, we might want to get up to first 5
byNthVisit = ddlNthVisit.SelectedValue.AsIntegerOrNull();
if ( byNthVisit.HasValue && byNthVisit > 2 )
{
nthVisitsTake = byNthVisit.Value;
}
}
ChartGroupBy groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week;
IQueryable<PersonWithSummary> qryByPersonWithSummary = null;
if ( byNthVisit.HasValue && byNthVisit.Value == 0 )
{
// Show members of the selected groups that did not attend at all during selected date range
// Get all the person ids that did attend
var attendeePersonIds = qryAttendance.Select( a => a.PersonAlias.PersonId );
// Get all the active members of the selected groups who have no attendance within selected date range and campus
qryByPersonWithSummary = new GroupMemberService( rockContext )
.Queryable().AsNoTracking()
.Where( m =>
groupIdList.Contains( m.GroupId ) &&
!attendeePersonIds.Contains( m.PersonId ) &&
m.GroupMemberStatus == GroupMemberStatus.Active )
.Select( m => new PersonWithSummary
{
PersonId = m.PersonId,
FirstVisits = new DateTime[] { }.AsQueryable(),
LastVisit = new AttendancePersonAlias(),
AttendanceSummary = new DateTime[] { }.AsQueryable()
} );
}
else
{
var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime( groupBy );
var qryGroup = new GroupService( rockContext ).Queryable();
var qryJoinPerson = qryAttendance.Join(
qryPersonAlias,
k1 => k1.PersonAliasId,
k2 => k2.Id,
( a, pa ) => new
{
CampusId = a.CampusId,
GroupId = a.GroupId,
ScheduleId = a.ScheduleId,
StartDateTime = a.StartDateTime,
PersonAliasId = pa.Id,
PersonAliasPersonId = pa.PersonId
} );
var qryJoinFinal = qryJoinPerson.Join(
qryGroup,
k1 => k1.GroupId,
k2 => k2.Id,
( a, g ) => new AttendancePersonAlias
{
CampusId = a.CampusId,
GroupId = a.GroupId,
GroupName = g.Name,
ScheduleId = a.ScheduleId,
StartDateTime = a.StartDateTime,
PersonAliasId = a.PersonAliasId,
PersonAliasPersonId = a.PersonAliasPersonId
} );
var qryByPerson = qryJoinFinal.GroupBy( a => a.PersonAliasPersonId ).Select( a => new
{
PersonId = a.Key,
Attendances = a
} );
int? attendedMinCount = null;
int? attendedMissedCount = null;
DateRange attendedMissedDateRange = new DateRange();
if ( radByPattern.Checked )
{
attendedMinCount = tbPatternXTimes.Text.AsIntegerOrNull();
if ( cbPatternAndMissed.Checked )
{
attendedMissedCount = tbPatternMissedXTimes.Text.AsIntegerOrNull();
attendedMissedDateRange = new DateRange( drpPatternDateRange.LowerValue, drpPatternDateRange.UpperValue );
if ( !attendedMissedDateRange.Start.HasValue || !attendedMissedDateRange.End.HasValue )
{
nbMissedDateRangeRequired.Visible = true;
return;
}
}
}
nbMissedDateRangeRequired.Visible = false;
// get either the first 2 visits or the first 5 visits (using a const take of 2 or 5 vs a variable to help the SQL optimizer)
qryByPersonWithSummary = qryByPerson.Select( a => new PersonWithSummary
{
PersonId = a.PersonId,
FirstVisits = qryAllVisits.Where( b => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == b.PersonAliasId ) ).Select( s => s.StartDateTime ).OrderBy( x => x ).Take( 2 ),
LastVisit = a.Attendances.OrderByDescending( x => x.StartDateTime ).FirstOrDefault(),
AttendanceSummary = qryAttendanceWithSummaryDateTime.Where( x => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == x.Attendance.PersonAliasId ) ).GroupBy( g => g.SummaryDateTime ).Select( s => s.Key )
} );
if ( nthVisitsTake > 2 )
{
qryByPersonWithSummary = qryByPerson.Select( a => new PersonWithSummary
{
PersonId = a.PersonId,
FirstVisits = qryAllVisits.Where( b => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == b.PersonAliasId ) ).Select( s => s.StartDateTime ).OrderBy( x => x ).Take( 5 ),
LastVisit = a.Attendances.OrderByDescending( x => x.StartDateTime ).FirstOrDefault(),
AttendanceSummary = qryAttendanceWithSummaryDateTime.Where( x => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == x.Attendance.PersonAliasId ) ).GroupBy( g => g.SummaryDateTime ).Select( s => s.Key )
} );
}
if ( byNthVisit.HasValue )
{
// only return attendees where their nth visit is within the selected daterange
int skipCount = byNthVisit.Value - 1;
qryByPersonWithSummary = qryByPersonWithSummary.Where( a => a.FirstVisits.OrderBy( x => x ).Skip( skipCount ).Take( 1 ).Any( d => d >= dateRange.Start && d < dateRange.End ) );
}
if ( attendedMinCount.HasValue )
{
qryByPersonWithSummary = qryByPersonWithSummary.Where( a => a.AttendanceSummary.Count() >= attendedMinCount );
}
if ( attendedMissedCount.HasValue )
{
if ( attendedMissedDateRange.Start.HasValue && attendedMissedDateRange.End.HasValue )
{
var attendedMissedPossible = GetPossibleAttendancesForDateRange( attendedMissedDateRange, groupBy );
int attendedMissedPossibleCount = attendedMissedPossible.Count();
qryMissed = qryMissed.Where( a => a.StartDateTime >= attendedMissedDateRange.Start.Value && a.StartDateTime < attendedMissedDateRange.End.Value );
var qryMissedAttendanceByPersonAndSummary = qryMissed.GetAttendanceWithSummaryDateTime( groupBy )
.GroupBy( g1 => new { g1.SummaryDateTime, g1.Attendance.PersonAlias.PersonId } )
.GroupBy( a => a.Key.PersonId )
.Select( a => new
{
PersonId = a.Key,
AttendanceCount = a.Count()
} );
var qryMissedByPerson = qryMissedAttendanceByPersonAndSummary
.Where( x => ( attendedMissedPossibleCount - x.AttendanceCount ) >= attendedMissedCount );
// filter to only people that missed at least X weeks/months/years between specified missed date range
qryByPersonWithSummary = qryByPersonWithSummary.Where( a => qryMissedByPerson.Any( b => b.PersonId == a.PersonId ) );
}
}
}
var personService = new PersonService( rockContext );
// Filter by dataview
var dataViewId = dvpDataView.SelectedValueAsInt();
if ( dataViewId.HasValue )
{
var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages );
SortProperty sort = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.Id );
qryByPersonWithSummary = qryByPersonWithSummary.Where( a => dataViewPersonIdQry.Contains( a.PersonId ) );
}
}
// declare the qryResult that we'll use in case they didn't choose IncludeParents or IncludeChildren (and the Anonymous Type will also work if we do include parents or children)
var qryPerson = personService.Queryable();
var qryResult = qryByPersonWithSummary.Join(
qryPerson,
a => a.PersonId,
p => p.Id,
( a, p ) => new
{
a.PersonId,
ParentId = (int?)null,
ChildId = (int?)null,
Person = p,
Parent = (Person)null,
Child = (Person)null,
a.FirstVisits,
a.LastVisit,
p.PhoneNumbers,
a.AttendanceSummary
} );
var includeParents = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ParentsOfAttendees;
var includeChildren = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ChildrenOfAttendees;
// if Including Parents, join with qryChildWithParent instead of qryPerson
if ( includeParents )
{
var qryChildWithParent = new PersonService( rockContext ).GetChildWithParent();
qryResult = qryByPersonWithSummary.Join(
qryChildWithParent,
a => a.PersonId,
p => p.Child.Id,
( a, p ) => new
{
a.PersonId,
ParentId = (int?)p.Parent.Id,
ChildId = (int?)null,
Person = p.Child,
Parent = p.Parent,
Child = (Person)null,
a.FirstVisits,
a.LastVisit,
p.Parent.PhoneNumbers,
a.AttendanceSummary
} );
}
if ( includeChildren )
{
var qryParentWithChildren = new PersonService( rockContext ).GetParentWithChild();
qryResult = qryByPersonWithSummary.Join(
qryParentWithChildren,
a => a.PersonId,
p => p.Parent.Id,
( a, p ) => new
{
a.PersonId,
ParentId = (int?)null,
ChildId = (int?)p.Child.Id,
Person = p.Parent,
Parent = (Person)null,
Child = p.Child,
a.FirstVisits,
a.LastVisit,
p.Child.PhoneNumbers,
a.AttendanceSummary
} );
}
var parentField = gAttendeesAttendance.Columns.OfType<PersonField>().FirstOrDefault( a => a.HeaderText == "Parent" );
if ( parentField != null )
{
parentField.Visible = includeParents;
}
var parentEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Parent Email" );
if ( parentEmailField != null )
{
parentEmailField.ExcelExportBehavior = includeParents ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
var childField = gAttendeesAttendance.Columns.OfType<PersonField>().FirstOrDefault( a => a.HeaderText == "Child" );
if ( childField != null )
{
childField.Visible = includeChildren;
}
var childEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Child Email" );
if ( childEmailField != null )
{
childEmailField.ExcelExportBehavior = includeChildren ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
SortProperty sortProperty = gAttendeesAttendance.SortProperty;
if ( sortProperty != null )
{
if ( sortProperty.Property == "AttendanceSummary.Count" )
{
if ( sortProperty.Direction == SortDirection.Descending )
{
qryResult = qryResult.OrderByDescending( a => a.AttendanceSummary.Count() );
}
else
{
qryResult = qryResult.OrderBy( a => a.AttendanceSummary.Count() );
}
}
else if ( sortProperty.Property == "FirstVisit.StartDateTime" )
{
if ( sortProperty.Direction == SortDirection.Descending )
{
qryResult = qryResult.OrderByDescending( a => a.FirstVisits.Min() );
}
else
{
qryResult = qryResult.OrderBy( a => a.FirstVisits.Min() );
}
}
else
{
qryResult = qryResult.Sort( sortProperty );
}
}
else
{
qryResult = qryResult.OrderBy( a => a.Person.LastName ).ThenBy( a => a.Person.NickName );
}
var attendancePercentField = gAttendeesAttendance.Columns.OfType<RockTemplateField>().First( a => a.HeaderText.EndsWith( "Attendance %" ) );
attendancePercentField.HeaderText = string.Format( "{0}ly Attendance %", groupBy.ConvertToString() );
// Calculate all the possible attendance summary dates
UpdatePossibleAttendances( dateRange, groupBy );
// pre-load the schedule names since FriendlyScheduleText requires building the ICal object, etc
_scheduleNameLookup = new ScheduleService( rockContext ).Queryable()
.ToList()
.ToDictionary( k => k.Id, v => v.FriendlyScheduleText );
if ( includeParents )
{
gAttendeesAttendance.PersonIdField = "ParentId";
gAttendeesAttendance.DataKeyNames = new string[] { "ParentId", "PersonId" };
}
else if ( includeChildren )
{
gAttendeesAttendance.PersonIdField = "ChildId";
gAttendeesAttendance.DataKeyNames = new string[] { "ChildId", "PersonId" };
}
else
{
gAttendeesAttendance.PersonIdField = "PersonId";
gAttendeesAttendance.DataKeyNames = new string[] { "PersonId" };
}
// Create the dynamic attendance grid columns as needed
CreateDynamicAttendanceGridColumns();
try
{
nbAttendeesError.Visible = false;
// increase the timeout from 30 to 90. The Query can be slow if SQL hasn't calculated the Query Plan for the query yet.
// Sometimes, most of the time consumption is figuring out the Query Plan, but after it figures it out, it caches it so that the next time it'll be much faster
rockContext.Database.CommandTimeout = 90;
gAttendeesAttendance.SetLinqDataSource( qryResult.AsNoTracking() );
gAttendeesAttendance.DataBind();
}
catch ( Exception exception )
{
LogAndShowException( exception );
}
}
/// <summary>
/// Job that will sync groups.
///
/// Called by the <see cref="IScheduler" /> when a
/// <see cref="ITrigger" /> fires that is associated with
/// the <see cref="IJob" />.
/// </summary>
public virtual void Execute( IJobExecutionContext context )
{
JobDataMap dataMap = context.JobDetail.JobDataMap;
bool requirePasswordReset = dataMap.GetBoolean( "RequirePasswordReset" );
int groupsSynced = 0;
int groupsChanged = 0;
try
{
// get groups set to sync
GroupService groupService = new GroupService( new RockContext() );
var groupIdsThatSync = groupService.Queryable().Where( g => g.SyncDataViewId != null ).Select( a => a.Id ).ToList();
foreach ( var syncGroupId in groupIdsThatSync )
{
bool hasGroupChanged = false;
// use a fresh rockContext per group so that ChangeTracker doesn't get bogged down
using ( var rockContext = new RockContext() )
{
var syncGroup = new GroupService( rockContext ).Get( syncGroupId );
GroupMemberService groupMemberService = new GroupMemberService( rockContext );
// increase the timeout just in case the dataview source is slow
rockContext.Database.CommandTimeout = 180;
var syncSource = new DataViewService( rockContext ).Get( syncGroup.SyncDataViewId.Value );
// ensure this is a person dataview
bool isPersonDataSet = syncSource.EntityTypeId == EntityTypeCache.Read( typeof( Rock.Model.Person ) ).Id;
if ( isPersonDataSet )
{
SortProperty sortById = new SortProperty();
sortById.Property = "Id";
sortById.Direction = System.Web.UI.WebControls.SortDirection.Ascending;
List<string> errorMessages = new List<string>();
var personService = new PersonService( rockContext );
var parameterExpression = personService.ParameterExpression;
var whereExpression = syncSource.GetExpression( personService, parameterExpression, out errorMessages );
var sourceItems = personService.Get( parameterExpression, whereExpression ).Select( q => q.Id ).ToList();
var targetItems = groupMemberService.Queryable().Where( gm => gm.GroupId == syncGroup.Id ).ToList();
// delete items from the target not in the source
foreach ( var targetItem in targetItems.Where( t => !sourceItems.Contains( t.PersonId ) ) )
{
// made a clone of the person as it will be detached when the group member is deleted. Also
// saving the delete before the email is sent in case an exception occurs so the user doesn't
// get an email everytime the agent runs.
Person recipient = (Person)targetItem.Person.Clone();
groupMemberService.Delete( targetItem );
rockContext.SaveChanges();
hasGroupChanged = true;
if ( syncGroup.ExitSystemEmailId.HasValue )
{
SendExitEmail( syncGroup.ExitSystemEmailId.Value, recipient, syncGroup );
}
}
// add items not in target but in the source
foreach ( var sourceItem in sourceItems.Where( s => !targetItems.Select( t => t.PersonId ).Contains( s ) ) )
{
// add source to target
var newGroupMember = new GroupMember { Id = 0 };
newGroupMember.PersonId = sourceItem;
newGroupMember.Group = syncGroup;
newGroupMember.GroupMemberStatus = GroupMemberStatus.Active;
newGroupMember.GroupRoleId = syncGroup.GroupType.DefaultGroupRoleId ?? syncGroup.GroupType.Roles.FirstOrDefault().Id;
groupMemberService.Add( newGroupMember );
hasGroupChanged = true;
if ( syncGroup.WelcomeSystemEmailId.HasValue )
{
SendWelcomeEmail( syncGroup.WelcomeSystemEmailId.Value, sourceItem, syncGroup, syncGroup.AddUserAccountsDuringSync ?? false, requirePasswordReset );
}
}
if ( hasGroupChanged )
{
groupsChanged++;
}
groupsSynced++;
rockContext.SaveChanges();
if ( hasGroupChanged && ( syncGroup.IsSecurityRole || syncGroup.GroupType.Guid.Equals( Rock.SystemGuid.GroupType.GROUPTYPE_SECURITY_ROLE.AsGuid() ) ) )
{
Rock.Security.Role.Flush( syncGroup.Id );
}
}
}
}
var resultMessage = string.Empty;
if ( groupsSynced == 0 )
{
resultMessage = "No groups to sync";
}
else if ( groupsSynced == 1 )
{
resultMessage = "1 group was sync'ed";
}
else
{
resultMessage = string.Format( "{0} groups were sync'ed", groupsSynced );
}
resultMessage += string.Format( " and {0} groups where changed", groupsChanged );
context.Result = resultMessage;
}
catch ( System.Exception ex )
{
HttpContext context2 = HttpContext.Current;
ExceptionLogService.LogException( ex, context2 );
throw;
}
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="scheduleIds">The schedule ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime?startDate = null, DateTime?endDate = null, string groupIds = null, string campusIds = null, int?dataViewId = null, string scheduleIds = null)
{
var qryAttendance = Queryable().AsNoTracking()
.Where(a =>
a.DidAttend.HasValue &&
a.DidAttend.Value &&
a.PersonAlias != null);
if (startDate.HasValue)
{
qryAttendance = qryAttendance.Where(a => a.StartDateTime >= startDate.Value);
}
if (endDate.HasValue)
{
qryAttendance = qryAttendance.Where(a => a.StartDateTime < endDate.Value);
}
if (dataViewId.HasValue)
{
var rockContext = (RockContext)this.Context;
var dataView = new DataViewService(rockContext).Get(dataViewId.Value);
if (dataView != null)
{
var personService = new PersonService(rockContext);
var errorMessages = new List <string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages);
Rock.Web.UI.Controls.SortProperty sort = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where(paramExpression, whereExpression, sort)
.Select(p => p.Id);
qryAttendance = qryAttendance.Where(a => dataViewPersonIdQry.Contains(a.PersonAlias.PersonId));
}
}
if (!string.IsNullOrWhiteSpace(groupIds))
{
var groupIdList = groupIds.Split(',').AsIntegerList();
qryAttendance = qryAttendance.Where(a => a.GroupId.HasValue && groupIdList.Contains(a.GroupId.Value));
}
// If campuses were included, filter attendances by those that have selected campuses
// if 'null' is one of the campuses, treat that as a 'CampusId is Null'
var includeNullCampus = (campusIds ?? "").Split(',').ToList().Any(a => a.Equals("null", StringComparison.OrdinalIgnoreCase));
var campusIdList = (campusIds ?? "").Split(',').AsIntegerList();
// remove 0 from the list, just in case it is there
campusIdList.Remove(0);
if (campusIdList.Any())
{
if (includeNullCampus)
{
// show records that have a campusId in the campusIdsList + records that have a null campusId
qryAttendance = qryAttendance.Where(a => (a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value)) || !a.CampusId.HasValue);
}
else
{
// only show records that have a campusId in the campusIdList
qryAttendance = qryAttendance.Where(a => a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value));
}
}
else if (includeNullCampus)
{
// 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId
qryAttendance = qryAttendance.Where(a => !a.CampusId.HasValue);
}
// If schedules were included, filter attendances by those that have selected schedules
var scheduleIdList = (scheduleIds ?? "").Split(',').AsIntegerList();
scheduleIdList.Remove(0);
if (scheduleIdList.Any())
{
qryAttendance = qryAttendance.Where(a => a.ScheduleId.HasValue && scheduleIdList.Contains(a.ScheduleId.Value));
}
var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime(groupBy);
var summaryQry = qryAttendanceWithSummaryDateTime.Select(a => new
{
a.SummaryDateTime,
Campus = new
{
Id = a.Attendance.CampusId,
Name = a.Attendance.Campus.Name
},
Group = new
{
Id = a.Attendance.GroupId,
Name = a.Attendance.Group.Name
},
Schedule = new
{
Id = a.Attendance.ScheduleId,
Name = a.Attendance.Schedule.Name
},
Location = new
{
Id = a.Attendance.LocationId,
Name = a.Attendance.Location.Name
}
});
List <SummaryData> result = null;
if (graphBy == AttendanceGraphBy.Total)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = "Total",
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Campus)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Campus }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Group)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Group }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Schedule)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Schedule }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Location)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Location }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
return(result);
}
/// <summary>
/// Binds the attendees grid.
/// </summary>
private void BindGiversGrid()
{
// Get all the selected criteria values
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
var start = dateRange.Start;
var end = dateRange.End;
var minAmount = nreAmount.LowerValue;
var maxAmount = nreAmount.UpperValue;
var currencyTypeIds = new List<int>();
cblCurrencyTypes.SelectedValues.ForEach( i => currencyTypeIds.Add( i.AsInteger() ) );
var sourceTypeIds = new List<int>();
cblTransactionSource.SelectedValues.ForEach( i => sourceTypeIds.Add( i.AsInteger() ) );
var accountIds = new List<int>();
foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() )
{
accountIds.AddRange( cblAccounts.SelectedValuesAsInt );
}
var dataViewId = dvpDataView.SelectedValueAsInt();
GiversViewBy viewBy = GiversViewBy.Giver;
if ( !HideViewByOption )
{
viewBy = hfViewBy.Value.ConvertToEnumOrNull<GiversViewBy>() ?? GiversViewBy.Giver;
}
// Clear all the existing grid columns
var selectField = new SelectField();
var oldSelectField = gGiversGifts.ColumnsOfType<SelectField>().FirstOrDefault();
if (oldSelectField != null )
{
selectField.SelectedKeys.AddRange( oldSelectField.SelectedKeys );
}
gGiversGifts.Columns.Clear();
// Add a column for selecting rows
gGiversGifts.Columns.Add( selectField );
// Add a column for the person's name
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "PersonName",
HeaderText = "Person",
SortExpression = "LastName,NickName"
} );
// add a column for email (but is only included on excel export)
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "Email",
HeaderText = "Email",
SortExpression = "Email",
Visible = false,
ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude
} );
// Add a column for total amount
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = "TotalAmount",
HeaderText = "Total",
SortExpression = "TotalAmount"
} );
// Add columns for the selected account totals
if ( accountIds.Any() )
{
var accounts = new FinancialAccountService( _rockContext )
.Queryable().AsNoTracking()
.Where( a => accountIds.Contains( a.Id ) )
.ToList();
foreach ( int accountId in accountIds )
{
var account = accounts.FirstOrDefault( a => a.Id == accountId );
if ( account != null )
{
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = account.Id.ToString(),
HeaderText = account.Name,
SortExpression = account.Id.ToString()
} );
}
}
}
// Add a column for the number of gifts
var numberGiftsField = new RockBoundField
{
DataField = "NumberGifts",
HeaderText = "Number of Gifts",
SortExpression = "NumberGifts",
DataFormatString = "{0:N0}",
};
numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
gGiversGifts.Columns.Add( numberGiftsField );
// Add a column to indicate if this is a first time giver
gGiversGifts.Columns.Add(
new BoolField
{
DataField = "IsFirstEverGift",
HeaderText = "Is First Gift",
SortExpression = "IsFirstEverGift"
} );
// Add a column for the first gift date ( that matches criteria )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstGift",
HeaderText = "First Gift",
SortExpression = "FirstGift"
} );
// Add a column for the first-ever gift date ( to any tax-deductible account )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstEverGift",
HeaderText = "First Gift Ever",
SortExpression = "FirstEverGift"
} );
var transactionDetailService = new FinancialTransactionDetailService( _rockContext );
var personService = new PersonService( _rockContext );
// If dataview was selected get the person id's returned by the dataview
var dataViewPersonIds = new List<int>();
if ( dataViewId.HasValue )
{
var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages );
SortProperty sortProperty = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sortProperty )
.Select( p => p.Id );
dataViewPersonIds = dataViewPersonIdQry.ToList();
}
}
// Check to see if grid should display only people who gave a certain number of times and if so
// set the min value
int minCount = 0;
var previousPersonIds = new List<int>();
if ( radByPattern.Checked )
{
minCount = tbPatternXTimes.Text.AsInteger();
var missedStart = drpPatternDateRange.LowerValue;
var missedEnd = drpPatternDateRange.UpperValue;
if ( missedStart.HasValue && missedEnd.HasValue )
{
// Get the givingids that gave any amount during the pattern's date range. These
// are needed so that we know who to exclude from the result set
var previousGivingIds = transactionDetailService
.Queryable().AsNoTracking()
.Where( d =>
d.Transaction.TransactionDateTime.HasValue &&
d.Transaction.TransactionDateTime.Value >= missedStart.Value &&
d.Transaction.TransactionDateTime.Value < missedEnd.Value &&
accountIds.Contains( d.AccountId ) &&
d.Amount != 0.0M )
.Select( d => d.Transaction.AuthorizedPersonAlias.Person.GivingId );
// Now get the person ids from the givingids
previousPersonIds = personService
.Queryable().AsNoTracking()
.Where( p => previousGivingIds.Contains( p.GivingId ) )
.Select( p => p.Id )
.ToList();
}
}
// Call the stored procedure to get all the giving data that matches the selected criteria.
// The stored procedure returns two tables. First is a list of all matching transaction summary
// information and the second table is each giving leader's first-ever gift date to a tax-deductible account
DataSet ds = FinancialTransactionDetailService.GetGivingAnalytics( start, end, minAmount, maxAmount,
accountIds, currencyTypeIds, sourceTypeIds, dataViewId, viewBy );
// Get the results table
DataTable dtResults = ds.Tables[0];
// Get the first-ever gift dates and load them into a dictionary for faster matching
DataTable dtFirstEver = ds.Tables[1];
var firstEverVals = new Dictionary<int, DateTime>();
foreach( DataRow row in ds.Tables[1].Rows )
{
if ( !DBNull.Value.Equals( row["FirstEverGift"] ) )
{
firstEverVals.Add( (int)row["PersonId"], (DateTime)row["FirstEverGift"] );
}
}
// Add columns to the result set for the first-ever data
dtResults.Columns.Add( new DataColumn( "IsFirstEverGift", typeof( bool ) ) );
dtResults.Columns.Add( new DataColumn( "FirstEverGift", typeof( DateTime ) ) );
foreach( DataRow row in dtResults.Rows )
{
bool rowValid = true;
// Get the person id
int personId = (int)row["Id"];
if ( radByPattern.Checked )
{
// If pattern was specified check minimum gifts and other date range
int numberGifts = (int)row["NumberGifts"];
if ( numberGifts < minCount )
{
rowValid = false;
}
else
{
// If this giving leader gave during the pattern date, remove the row since we
// only want those who did not
if ( previousPersonIds.Contains( personId ) )
{
rowValid = false;
}
}
}
if ( dataViewId.HasValue )
{
// If a dataview filter was specified, and this row is not part of dataview,
// remove it
if ( !dataViewPersonIds.Contains(personId))
{
rowValid = false;
// Remove person id from list so that list can be used later to optionally
// add rows for remaining people who were in the dataview, but not in the
// result set
dataViewPersonIds.Remove( personId );
}
}
if ( rowValid )
{
// Set the first ever information for each row
bool isFirstEverGift = false;
DateTime firstGift = (DateTime)row["FirstGift"];
if ( firstEverVals.ContainsKey( personId ) )
{
DateTime firstEverGift = firstEverVals[personId];
isFirstEverGift = firstEverGift.Equals( firstGift );
row["FirstEverGift"] = firstEverGift;
}
// If only first time givers should be included, remove any that are not
if ( radFirstTime.Checked && !isFirstEverGift )
{
rowValid = false;
}
else
{
row["IsFirstEverGift"] = isFirstEverGift;
}
}
if ( !rowValid )
{
row.Delete();
}
}
// if dataview was selected and it includes people not in the result set,
if ( dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any() )
{
// Query for the names of each of these people
foreach( var person in personService
.Queryable().AsNoTracking()
.Select( p => new {
p.Id,
p.Guid,
p.NickName,
p.LastName,
p.Email
}))
{
// Check for a first ever gift date
var firstEverGiftDate = firstEverVals
.Where( f => f.Key == person.Id )
.Select( f => f.Value )
.FirstOrDefault();
DataRow row = dtResults.NewRow();
row["Id"] = person.Id;
row["Guid"] = person.Guid;
row["NickName"] = person.NickName;
row["LastName"] = person.LastName;
row["PersonName"] = person.NickName + " " + person.LastName;
row["Email"] = person.Email;
row["IsFirstEverGift"] = false;
row["FirstEverGift"] = firstEverGiftDate;
dtResults.Rows.Add( row );
}
}
// Update the changes (deletes) in the datatable
dtResults.AcceptChanges();
// Calculate Total
if ( viewBy == GiversViewBy.Giver )
{
pnlTotal.Visible = true;
object amountTotalObj = dtResults.Compute( "Sum(TotalAmount)", null );
if ( amountTotalObj != null )
{
decimal amountTotal = amountTotalObj.ToString().AsDecimal();
lTotal.Text = amountTotal.FormatAsCurrency();
}
else
{
lTotal.Text = string.Empty;
}
}
else
{
pnlTotal.Visible = false;
}
// Sort the results
System.Data.DataView dv = dtResults.DefaultView;
if ( gGiversGifts.SortProperty != null )
{
try
{
var sortProperties = new List<string>();
foreach( string prop in gGiversGifts.SortProperty.Property.SplitDelimitedValues(false))
{
sortProperties.Add( string.Format( "[{0}] {1}", prop, gGiversGifts.SortProperty.DirectionString ) );
}
dv.Sort = sortProperties.AsDelimited( ", " );
}
catch
{
dv.Sort = "[LastName] ASC, [NickName] ASC";
}
}
else
{
dv.Sort = "[LastName] ASC, [NickName] ASC";
}
gGiversGifts.DataSource = dv;
gGiversGifts.DataBind();
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="scheduleIds">The schedule ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable<IChartData> GetChartData( ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime? startDate = null, DateTime? endDate = null, string groupIds = null, string campusIds = null, int? dataViewId = null, string scheduleIds = null )
{
var qryAttendance = Queryable().AsNoTracking()
.Where( a =>
a.DidAttend.HasValue &&
a.DidAttend.Value &&
a.PersonAlias != null );
if ( startDate.HasValue )
{
qryAttendance = qryAttendance.Where( a => a.StartDateTime >= startDate.Value );
}
if ( endDate.HasValue )
{
qryAttendance = qryAttendance.Where( a => a.StartDateTime < endDate.Value );
}
if ( dataViewId.HasValue )
{
var rockContext = (RockContext)this.Context;
var dataView = new DataViewService( rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var personService = new PersonService( rockContext );
var errorMessages = new List<string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages );
Rock.Web.UI.Controls.SortProperty sort = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.Id );
qryAttendance = qryAttendance.Where( a => dataViewPersonIdQry.Contains( a.PersonAlias.PersonId ) );
}
}
if ( !string.IsNullOrWhiteSpace( groupIds ) )
{
var groupIdList = groupIds.Split( ',' ).AsIntegerList();
qryAttendance = qryAttendance.Where( a => a.GroupId.HasValue && groupIdList.Contains( a.GroupId.Value ) );
}
// If campuses were included, filter attendances by those that have selected campuses
// if 'null' is one of the campuses, treat that as a 'CampusId is Null'
var includeNullCampus = ( campusIds ?? "" ).Split( ',' ).ToList().Any( a => a.Equals( "null", StringComparison.OrdinalIgnoreCase ) );
var campusIdList = ( campusIds ?? "" ).Split( ',' ).AsIntegerList();
// remove 0 from the list, just in case it is there
campusIdList.Remove( 0 );
if ( campusIdList.Any() )
{
if ( includeNullCampus )
{
// show records that have a campusId in the campusIdsList + records that have a null campusId
qryAttendance = qryAttendance.Where( a => ( a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) ) || !a.CampusId.HasValue );
}
else
{
// only show records that have a campusId in the campusIdList
qryAttendance = qryAttendance.Where( a => a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) );
}
}
else if ( includeNullCampus )
{
// 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId
qryAttendance = qryAttendance.Where( a => !a.CampusId.HasValue );
}
// If schedules were included, filter attendances by those that have selected schedules
var scheduleIdList = ( scheduleIds ?? "" ).Split( ',' ).AsIntegerList();
scheduleIdList.Remove( 0 );
if ( scheduleIdList.Any() )
{
qryAttendance = qryAttendance.Where( a => a.ScheduleId.HasValue && scheduleIdList.Contains( a.ScheduleId.Value ) );
}
var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime( groupBy );
var summaryQry = qryAttendanceWithSummaryDateTime.Select( a => new
{
a.SummaryDateTime,
Campus = new
{
Id = a.Attendance.CampusId,
Name = a.Attendance.Campus.Name
},
Group = new
{
Id = a.Attendance.GroupId,
Name = a.Attendance.Group.Name
},
Schedule = new
{
Id = a.Attendance.ScheduleId,
Name = a.Attendance.Schedule.Name
},
Location = new
{
Id = a.Attendance.LocationId,
Name = a.Attendance.Location.Name
}
} );
List<SummaryData> result = null;
if ( graphBy == AttendanceGraphBy.Total )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = "Total",
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Campus )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Campus } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Group )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Group } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Schedule )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Schedule } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Location )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Location } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
return result;
}
/// <summary>
/// Binds the attendees grid.
/// </summary>
private void BindGiversGrid()
{
// Get all the selected criteria values
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
var start = dateRange.Start;
var end = dateRange.End;
var minAmount = nreAmount.LowerValue;
var maxAmount = nreAmount.UpperValue;
var currencyTypeIds = new List<int>();
cblCurrencyTypes.SelectedValues.ForEach( i => currencyTypeIds.Add( i.AsInteger() ) );
var sourceIds = new List<int>();
cblTransactionSource.SelectedValues.ForEach( i => sourceIds.Add( i.AsInteger() ) );
var accountIds = new List<int>();
foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() )
{
accountIds.AddRange( cblAccounts.SelectedValuesAsInt );
}
var groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week;
var graphBy = hfGraphBy.Value.ConvertToEnumOrNull<TransactionGraphBy>() ?? TransactionGraphBy.Total;
GiversViewBy viewBy = GiversViewBy.Giver;
if ( !HideViewByOption )
{
viewBy = hfViewBy.Value.ConvertToEnumOrNull<GiversViewBy>() ?? GiversViewBy.Giver;
}
// Collection of async queries to run before assembling data
var qryTasks = new List<Task>();
// Get all person summary data
var personInfoList = new List<PersonInfo>();
qryTasks.Add( Task.Run( () =>
{
var dt = FinancialTransactionDetailService.GetGivingAnalyticsPersonSummary(
start, end, minAmount, maxAmount, accountIds, currencyTypeIds, sourceIds )
.Tables[0];
foreach ( DataRow row in dt.Rows )
{
var personInfo = new PersonInfo();
if ( !DBNull.Value.Equals( row["Id"] ) )
{
personInfo.Id = (int)row["Id"];
}
if ( !DBNull.Value.Equals( row["Guid"] ) )
{
personInfo.Guid = row["Guid"].ToString().AsGuid();
}
if ( !DBNull.Value.Equals( row["NickName"] ) )
{
personInfo.NickName = row["NickName"].ToString();
}
if ( !DBNull.Value.Equals( row["LastName"] ) )
{
personInfo.LastName = row["LastName"].ToString();
}
if ( !DBNull.Value.Equals( row["Email"] ) )
{
personInfo.Email = row["Email"].ToString();
}
if ( !DBNull.Value.Equals( row["GivingId"] ) )
{
personInfo.GivingId = row["GivingId"].ToString();
}
if ( !DBNull.Value.Equals( row["FirstGift"] ) )
{
personInfo.FirstGift = row["FirstGift"].ToString().AsDateTime();
}
if ( !DBNull.Value.Equals( row["LastGift"] ) )
{
personInfo.LastGift = row["LastGift"].ToString().AsDateTime();
}
if ( !DBNull.Value.Equals( row["NumberGifts"] ) )
{
personInfo.NumberGifts = (int)row["NumberGifts"];
}
if ( !DBNull.Value.Equals( row["TotalAmount"] ) )
{
personInfo.TotalAmount = (decimal)row["TotalAmount"];
}
if ( !DBNull.Value.Equals( row["IsGivingLeader"] ) )
{
personInfo.IsGivingLeader = (bool)row["IsGivingLeader"];
}
if ( !DBNull.Value.Equals( row["IsAdult"] ) )
{
personInfo.IsAdult = (bool)row["IsAdult"];
}
if ( !DBNull.Value.Equals( row["IsChild"] ) )
{
personInfo.IsChild = (bool)row["IsChild"];
}
personInfo.AccountAmounts = new Dictionary<int, decimal>();
personInfoList.Add( personInfo );
}
} ) );
// Get the account summary values
DataTable dtAccountSummary = null;
qryTasks.Add( Task.Run( () =>
{
dtAccountSummary = FinancialTransactionDetailService.GetGivingAnalyticsAccountTotals(
start, end, accountIds, currencyTypeIds, sourceIds )
.Tables[0];
} ) );
// Get the first/last ever dates
var firstEverVals = new Dictionary<string, DateTime>();
var lastEverVals = new Dictionary<string, DateTime>();
qryTasks.Add( Task.Run( () =>
{
var dt = FinancialTransactionDetailService.GetGivingAnalyticsFirstLastEverDates()
.Tables[0];
foreach ( DataRow row in dt.Rows )
{
if ( !DBNull.Value.Equals( row["GivingId"] ) )
{
if ( !DBNull.Value.Equals( row["FirstEverGift"] ) )
{
firstEverVals.Add( row["GivingId"].ToString(), row["FirstEverGift"].ToString().AsDateTime().Value );
}
if ( !DBNull.Value.Equals( row["LastEverGift"] ) )
{
lastEverVals.Add( row["GivingId"].ToString(), row["LastEverGift"].ToString().AsDateTime().Value );
}
}
}
} ) );
// If a dataview filter was included, find the people who match that criteria
List<int> dataViewPersonIds = null;
var dataViewId = dvpDataView.SelectedValueAsInt();
if ( dataViewId.HasValue )
{
qryTasks.Add( Task.Run( () =>
{
dataViewPersonIds = new List<int>();
var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
var dvPersonService = new PersonService( _rockContext );
ParameterExpression paramExpression = dvPersonService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( dvPersonService, paramExpression, out errorMessages );
SortProperty sort = null;
var dataViewPersonIdQry = dvPersonService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.Id );
dataViewPersonIds = dataViewPersonIdQry.ToList();
}
} ) );
}
qryTasks.Add( Task.Run( () =>
{
// Clear all the existing grid columns
var selectField = new SelectField();
var oldSelectField = gGiversGifts.ColumnsOfType<SelectField>().FirstOrDefault();
if ( oldSelectField != null )
{
selectField.SelectedKeys.AddRange( oldSelectField.SelectedKeys );
}
gGiversGifts.Columns.Clear();
// Add a column for selecting rows
gGiversGifts.Columns.Add( selectField );
// Add a hidden column for person id
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "Id",
HeaderText = "Person Id",
SortExpression = "Id",
Visible = false,
ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude
} );
// Add a column for the person's name
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "PersonName",
HeaderText = "Person",
SortExpression = "LastName,NickName"
} );
// add a column for email (but is only included on excel export)
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "Email",
HeaderText = "Email",
SortExpression = "Email",
Visible = false,
ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude
} );
// Add a column for total amount
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = "TotalAmount",
HeaderText = "Total",
SortExpression = "TotalAmount"
} );
// Add columns for the selected account totals
if ( accountIds.Any() )
{
var accounts = new FinancialAccountService( _rockContext )
.Queryable().AsNoTracking()
.Where( a => accountIds.Contains( a.Id ) )
.ToList();
foreach ( int accountId in accountIds )
{
var account = accounts.FirstOrDefault( a => a.Id == accountId );
if ( account != null )
{
gGiversGifts.Columns.Add(
new GivingAnalyticsAccountField
{
DataField = string.Format( "Account_{0}", account.Id ),
HeaderText = account.Name,
SortExpression = string.Format( "Account:{0}", account.Id ),
} );
}
}
}
// Add a column for the number of gifts
var numberGiftsField = new RockBoundField
{
DataField = "NumberGifts",
HeaderText = "Number of Gifts",
SortExpression = "NumberGifts",
DataFormatString = "{0:N0}",
};
numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
gGiversGifts.Columns.Add( numberGiftsField );
if ( !radFirstTime.Checked )
{
// Add a column to indicate if this is a first time giver
gGiversGifts.Columns.Add(
new BoolField
{
DataField = "IsFirstEverGift",
HeaderText = "Is First Gift",
SortExpression = "IsFirstEverGift"
} );
// Add a column for the first gift date ( that matches criteria )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstGift",
HeaderText = "First Gift in Period",
SortExpression = "FirstGift"
} );
}
// Add a column for the first-ever gift date ( to any tax-deductible account )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstEverGift",
HeaderText = "First Gift Ever",
SortExpression = "FirstEverGift"
} );
// Add a column for the first gift date ( that matches criteria )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "LastGift",
HeaderText = "Last Gift in Period",
SortExpression = "LastGift"
} );
// Add a column for the last-ever gift date ( to any tax-deductible account )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "LastEverGift",
HeaderText = "Last Gift Ever",
SortExpression = "LastEverGift"
} );
} ) );
// Wait for all the queries to finish
Task.WaitAll( qryTasks.ToArray() );
// If dataview was selected and it's being used to filter results people, not in dataview
if ( dataViewId.HasValue && rblDataViewAction.SelectedValue != "All" && dataViewPersonIds.Any() )
{
personInfoList = personInfoList.Where( c => dataViewPersonIds.Contains( c.Id ) ).ToList();
}
// if dataview was selected and it includes people not in the result set,
if ( dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any() )
{
// Query for the names of each of these people
foreach ( var person in new PersonService( _rockContext )
.Queryable().AsNoTracking()
.Where( p => dataViewPersonIds.Contains( p.Id ) )
.Select( p => new
{
p.Id,
p.GivingId,
p.GivingLeaderId,
p.Guid,
p.NickName,
p.LastName,
p.Email
} ) )
{
// Check for a first ever gift date
var firstEverGiftDate = firstEverVals
.Where( f => f.Key == person.GivingId )
.Select( f => f.Value )
.FirstOrDefault();
// Check for a last ever gift date
var lastEverGiftDate = lastEverVals
.Where( f => f.Key == person.GivingId )
.Select( f => f.Value )
.FirstOrDefault();
var personInfo = new PersonInfo();
personInfo.Id = person.Id;
personInfo.Guid = person.Guid;
personInfo.NickName = person.NickName;
personInfo.LastName = person.LastName;
personInfo.Email = person.Email;
personInfo.GivingId = person.GivingId;
personInfo.IsGivingLeader = person.Id == person.GivingLeaderId;
personInfo.FirstEverGift = firstEverGiftDate;
personInfo.LastEverGift = lastEverGiftDate;
personInfoList.Add( personInfo );
}
}
// Filter out recs that don't match the view by
switch ( viewBy )
{
case GiversViewBy.Giver:
{
personInfoList = personInfoList.Where( p => p.IsGivingLeader ).ToList();
break;
}
case GiversViewBy.Adults:
{
personInfoList = personInfoList.Where( p => p.IsAdult ).ToList();
break;
}
case GiversViewBy.Children:
{
personInfoList = personInfoList.Where( p => p.IsChild ).ToList();
break;
}
}
// Add the first/last gift dates
foreach ( var personInfo in personInfoList )
{
if ( firstEverVals.ContainsKey( personInfo.GivingId ) )
{
personInfo.FirstEverGift = firstEverVals[personInfo.GivingId];
}
if ( lastEverVals.ContainsKey( personInfo.GivingId ) )
{
personInfo.LastEverGift = lastEverVals[personInfo.GivingId];
}
}
// Check to see if we're only showing first time givers
if ( radFirstTime.Checked )
{
personInfoList = personInfoList
.Where( p => p.IsFirstEverGift )
.ToList();
}
// Check to see if grid should display only people who gave a certain number of times and if so
// set the min value
if ( radByPattern.Checked )
{
int minCount = tbPatternXTimes.Text.AsInteger();
var previousGivingIds = new List<string>();
if ( cbPatternAndMissed.Checked )
{
var missedStart = drpPatternDateRange.LowerValue;
var missedEnd = drpPatternDateRange.UpperValue;
if ( missedStart.HasValue && missedEnd.HasValue )
{
// Get the givingleaderids that gave any amount during the pattern's date range. These
// are needed so that we know who to exclude from the result set
previousGivingIds = new FinancialTransactionDetailService( _rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.Transaction.TransactionDateTime.HasValue &&
d.Transaction.TransactionDateTime.Value >= missedStart.Value &&
d.Transaction.TransactionDateTime.Value < missedEnd.Value &&
( accountIds.Any() && accountIds.Contains( d.AccountId ) || d.Account.IsTaxDeductible ) &&
d.Amount != 0.0M )
.Select( d => d.Transaction.AuthorizedPersonAlias.Person.GivingId )
.ToList();
}
}
personInfoList = personInfoList
.Where( p =>
!previousGivingIds.Contains( p.GivingId ) &&
p.NumberGifts >= minCount )
.ToList();
}
// Add account summary info
foreach ( DataRow row in dtAccountSummary.Rows )
{
if ( !DBNull.Value.Equals( row["GivingId"] ) &&
!DBNull.Value.Equals( row["AccountId"] ) &&
!DBNull.Value.Equals( row["Amount"] ) )
{
string givingId = row["GivingId"].ToString();
int accountId = (int)row["AccountId"];
decimal amount = (decimal)row["Amount"];
foreach ( var personInfo in personInfoList.Where( p => p.GivingId == givingId ) )
{
personInfo.AccountAmounts.AddOrIgnore( accountId, amount );
}
}
}
// Calculate Total
if ( viewBy == GiversViewBy.Giver )
{
pnlTotal.Visible = true;
decimal amountTotal = personInfoList.Sum( p => p.TotalAmount );
lTotal.Text = amountTotal.FormatAsCurrency();
}
else
{
pnlTotal.Visible = false;
}
var qry = personInfoList.AsQueryable();
if ( gGiversGifts.SortProperty != null )
{
if ( gGiversGifts.SortProperty.Property.StartsWith( "Account" ) )
{
int? accountId = gGiversGifts.SortProperty.Property.Substring( 8 ).AsIntegerOrNull();
if ( accountId.HasValue )
{
foreach ( var personInfo in personInfoList )
{
personInfo.SortAmount = personInfo.AccountAmounts.ContainsKey( accountId.Value ) ?
personInfo.AccountAmounts[accountId.Value] : 0.0M;
if ( gGiversGifts.SortProperty.Direction == SortDirection.Ascending )
{
gGiversGifts.DataSource = personInfoList.OrderBy( p => p.SortAmount ).ToList();
}
else
{
gGiversGifts.DataSource = personInfoList.OrderByDescending( p => p.SortAmount ).ToList();
}
}
}
else
{
gGiversGifts.DataSource = qry.OrderBy( p => p.LastName ).ThenBy( p => p.NickName ).ToList();
}
}
else
{
gGiversGifts.DataSource = qry.Sort( gGiversGifts.SortProperty ).ToList();
}
}
else
{
gGiversGifts.DataSource = qry.OrderBy( p => p.LastName ).ThenBy( p => p.NickName ).ToList();
}
gGiversGifts.DataBind();
}
/// <summary>
/// Gets financial transaction details based on selected filter values.
/// </summary>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="minAmount">The minimum amount.</param>
/// <param name="maxAmount">The maximum amount.</param>
/// <param name="currencyTypeIds">The currency type ids.</param>
/// <param name="sourceTypeIds">The source type ids.</param>
/// <param name="accountIds">The account ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IQueryable<FinancialTransactionDetail> GetGifts(
DateTime? start, DateTime? end, decimal? minAmount, decimal? maxAmount,
List<int> currencyTypeIds, List<int> sourceTypeIds, List<int> accountIds, int? dataViewId )
{
// Base Transaction Detail query
var qry = GetGifts();
// Start Date Filter
if ( start.HasValue )
{
qry = qry.Where( t => t.Transaction.TransactionDateTime >= start.Value );
}
// End Date Filter
if ( end.HasValue )
{
qry = qry.Where( t => t.Transaction.TransactionDateTime < end.Value );
}
// Account Id Filter
var distictAccountIds = accountIds.Where( i => i != 0 ).Distinct().ToList();
if ( distictAccountIds.Any() )
{
qry = qry
.Where( t =>
distictAccountIds.Contains( t.AccountId ) );
}
// Currency Type Filter
var distictCurrencyTypeIds = currencyTypeIds.Where( i => i != 0 ).Distinct().ToList();
if ( distictCurrencyTypeIds.Any() )
{
qry = qry
.Where( t =>
t.Transaction.FinancialPaymentDetail != null &&
t.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.HasValue &&
distictCurrencyTypeIds.Contains( t.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.Value ) );
}
// Source Type Filter
var distictSourceTypeIds = sourceTypeIds.Where( i => i != 0 ).Distinct().ToList();
if ( distictSourceTypeIds.Any() )
{
qry = qry
.Where( t =>
t.Transaction.SourceTypeValueId.HasValue &&
distictSourceTypeIds.Contains( t.Transaction.SourceTypeValueId.Value ) );
}
// Amount Range Filter
if ( minAmount.HasValue || maxAmount.HasValue )
{
var givingIdQry = qry
.GroupBy( d => d.Transaction.AuthorizedPersonAlias.Person.GivingId )
.Select( d => new { d.Key, Total = d.Sum( t => t.Amount ) } )
.Where( s =>
( !minAmount.HasValue || s.Total >= minAmount.Value ) &&
( !maxAmount.HasValue || s.Total <= maxAmount.Value ) )
.Select( s => s.Key );
qry = qry
.Where( d =>
givingIdQry.Contains( d.Transaction.AuthorizedPersonAlias.Person.GivingId ) );
}
// Data View Filter
if ( dataViewId.HasValue )
{
var rockContext = (RockContext)this.Context;
if ( rockContext != null )
{
var personService = new PersonService( rockContext );
var dataView = new DataViewService( rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages );
SortProperty sortProperty = null;
var dataViewGivingIdQry = personService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sortProperty )
.Select( p => p.GivingId );
qry = qry
.Where( t =>
dataViewGivingIdQry.Contains( t.Transaction.AuthorizedPersonAlias.Person.GivingId ) );
}
}
}
return qry;
}
private IEnumerable<Rock.Chart.IChartData> GetGivingChartData()
{
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
var currencyTypeIds = new List<int>();
cblCurrencyTypes.SelectedValues.ForEach( i => currencyTypeIds.Add( i.AsInteger() ) );
var sourceIds = new List<int>();
cblTransactionSource.SelectedValues.ForEach( i => sourceIds.Add( i.AsInteger() ) );
var accountIds = new List<int>();
foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() )
{
accountIds.AddRange( cblAccounts.SelectedValuesAsInt );
}
var groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week;
var graphBy = hfGraphBy.Value.ConvertToEnumOrNull<TransactionGraphBy>() ?? TransactionGraphBy.Total;
// Collection of async queries to run before assembling date
var qryTasks = new List<Task>();
// Get the chart data
var transactionInfoList = new List<TransactionInfo>();
qryTasks.Add( Task.Run( () =>
{
transactionInfoList = new List<TransactionInfo>();
var ds = FinancialTransactionDetailService.GetGivingAnalyticsTransactionData(
dateRange.Start,
dateRange.End,
accountIds,
currencyTypeIds,
sourceIds );
if ( ds != null )
{
foreach ( DataRow row in ds.Tables[0].Rows )
{
var chartData = new TransactionInfo();
if ( !DBNull.Value.Equals( row["GivingId"] ) )
{
chartData.GivingId = row["GivingId"].ToString();
}
if ( !DBNull.Value.Equals( row["Amount"] ) )
{
chartData.Amount = (decimal)row["Amount"];
}
if ( !DBNull.Value.Equals( row["TransactionDateTime"] ) )
{
chartData.TransactionDateTime = row["TransactionDateTime"].ToString().AsDateTime();
}
switch ( groupBy )
{
case ChartGroupBy.Week:
if ( !DBNull.Value.Equals( row["SundayDate"] ) )
{
chartData.SummaryDate = row["SundayDate"].ToString().AsDateTime();
}
break;
case ChartGroupBy.Month:
if ( !DBNull.Value.Equals( row["MonthDate"] ) )
{
chartData.SummaryDate = row["MonthDate"].ToString().AsDateTime();
}
break;
case ChartGroupBy.Year:
if ( !DBNull.Value.Equals( row["YearDate"] ) )
{
chartData.SummaryDate = row["YearDate"].ToString().AsDateTime();
}
break;
}
if ( !DBNull.Value.Equals( row["AccountId"] ) )
{
chartData.AccountId = (int)row["AccountId"];
}
if ( !DBNull.Value.Equals( row["AccountName"] ) )
{
chartData.AccountName = row["AccountName"].ToString();
}
if ( !DBNull.Value.Equals( row["GLCode"] ) )
{
chartData.GLCode = row["GLCode"].ToString();
}
if ( !DBNull.Value.Equals( row["CampusId"] ) )
{
chartData.CampusId = row["CampusId"].ToString().AsIntegerOrNull();
}
if ( chartData.CampusId.HasValue )
{
var campus = CampusCache.Read( chartData.CampusId.Value );
if ( campus != null )
{
chartData.CampusName = campus.Name;
}
}
transactionInfoList.Add( chartData );
}
}
} ) );
// If min or max amount values were entered, need to get summary so we know who gave within that range
List<string> idsWithValidTotals = null;
if ( nreAmount.LowerValue.HasValue || nreAmount.UpperValue.HasValue )
{
qryTasks.Add( Task.Run( () =>
{
idsWithValidTotals = new List<string>();
var dtPersonSummary = FinancialTransactionDetailService.GetGivingAnalyticsPersonSummary(
dateRange.Start,
dateRange.End,
nreAmount.LowerValue,
nreAmount.UpperValue,
accountIds,
currencyTypeIds,
sourceIds ).Tables[0];
foreach ( DataRow row in dtPersonSummary.Rows )
{
if ( !DBNull.Value.Equals( row["GivingId"] ) )
{
idsWithValidTotals.Add( row["GivingId"].ToString() );
}
}
} ) );
}
// If a dataview filter was included, find the people who match that criteria
List<string> dataViewGivingIds = null;
var dataViewId = dvpDataView.SelectedValueAsInt();
if ( dataViewId.HasValue )
{
qryTasks.Add( Task.Run( () =>
{
dataViewGivingIds = new List<string>();
var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
var dvPersonService = new PersonService( _rockContext );
ParameterExpression paramExpression = dvPersonService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( dvPersonService, paramExpression, out errorMessages );
SortProperty sort = null;
var dataViewPersonIdQry = dvPersonService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.GivingId );
dataViewGivingIds = dataViewPersonIdQry.ToList();
}
} ) );
}
// Wait for all the queries to finish
Task.WaitAll( qryTasks.ToArray() );
// Remove any giving leaders outside the min/max gift amounts
if ( idsWithValidTotals != null )
{
transactionInfoList = transactionInfoList.Where( c => idsWithValidTotals.Contains( c.GivingId ) ).ToList();
}
// Remove any giving leaders, not in dataview
if ( dataViewGivingIds != null )
{
transactionInfoList = transactionInfoList.Where( c => dataViewGivingIds.Contains( c.GivingId ) ).ToList();
}
// Group the results by week/month/year
List<SummaryData> result = null;
switch ( graphBy )
{
case TransactionGraphBy.Total:
{
result = transactionInfoList
.Where( c => c.SummaryDate.HasValue )
.GroupBy( c => new { c.SummaryDate.Value } )
.Select( r => new SummaryData
{
DateTimeStamp = r.Key.Value.ToJavascriptMilliseconds(),
DateTime = r.Key.Value,
SeriesName = "Total",
YValue = r.Sum( a => a.Amount )
} )
.OrderBy( r => r.DateTime )
.ToList();
break;
}
case TransactionGraphBy.Campus:
{
result = transactionInfoList
.Where( c => c.SummaryDate.HasValue )
.GroupBy( c => new { c.SummaryDate.Value, c.CampusName } )
.Select( r => new SummaryData
{
DateTimeStamp = r.Key.Value.ToJavascriptMilliseconds(),
DateTime = r.Key.Value,
SeriesName = r.Key.CampusName,
YValue = r.Sum( a => a.Amount )
} )
.OrderBy( r => r.DateTime )
.ToList();
break;
}
case TransactionGraphBy.FinancialAccount:
{
result = transactionInfoList
.Where( c => c.SummaryDate.HasValue )
.GroupBy( c => new { c.SummaryDate.Value, c.AccountName, c.GLCode } )
.Select( r => new SummaryData
{
DateTimeStamp = r.Key.Value.ToJavascriptMilliseconds(),
DateTime = r.Key.Value,
SeriesName = r.Key.AccountName,
SeriesAddlInfo = r.Key.GLCode,
YValue = r.Sum( a => a.Amount )
} )
.OrderBy( r => r.DateTime )
.ToList();
break;
}
}
return result;
}
/// <summary>
/// Gets the data view expression.
/// </summary>
/// <param name="dataViewId">The data view identifier.</param>
/// <param name="service">The service.</param>
/// <param name="parmExpression">The parm expression.</param>
/// <param name="entityTypeCache">The entity type cache.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
private Expression GetDataViewExpression( int dataViewId, IService service, ParameterExpression parmExpression, EntityTypeCache entityTypeCache )
{
var dataViewSource = new DataViewService( _rockContext ).Get( dataViewId );
bool isCorrectDataType = dataViewSource.EntityTypeId == entityTypeCache.Id;
if ( isCorrectDataType )
{
List<string> errorMessages = new List<string>();
var whereExpression = dataViewSource.GetExpression( service, parmExpression, out errorMessages );
return whereExpression;
}
else
{
throw new Exception( string.Format( "The DataView provided is not of type {0}.", entityTypeCache.FriendlyName ) );
}
}
/// <summary>
/// Binds the attendees grid.
/// </summary>
private void BindAttendeesGrid( bool isExporting = false )
{
// Get Group Type filter
var groupTypes = this.GetSelectedGroupTypes();
if ( groupTypes == null || !groupTypes.Any() )
{
return;
}
var groupTypeIdList = groupTypes.Select( t => t.Id ).ToList();
// Get the daterange filter
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
if ( dateRange.End == null )
{
dateRange.End = RockDateTime.Now;
}
var start = dateRange.Start;
var end = dateRange.End;
// Get the group filter
var groupIdList = new List<int>();
string groupIds = GetSelectedGroupIds().AsDelimited( "," );
if ( !string.IsNullOrWhiteSpace( groupIds ) )
{
groupIdList = groupIds.Split( ',' ).AsIntegerList();
}
// If campuses were included, filter attendances by those that have selected campuses
// if 'null' is one of the campuses, treat that as a 'CampusId is Null'
var includeNullCampus = clbCampuses.SelectedValues.Any( a => a.Equals( "null", StringComparison.OrdinalIgnoreCase ) );
var campusIdList = clbCampuses.SelectedValues.AsIntegerList();
campusIdList.Remove( 0 ); // remove 0 from the list, just in case it is there
if ( !includeNullCampus && !campusIdList.Any() )
{
campusIdList = null;
}
// If schedules were included, filter attendance by those that have the selected schedules
var scheduleIdList = spSchedules.SelectedValues.AsIntegerList();
scheduleIdList.Remove( 0 );
if ( !scheduleIdList.Any() )
{
scheduleIdList = null;
}
// we want to get the first 2 visits at a minimum so we can show the dates in the grid
int nthVisitsTake = 2;
int? byNthVisit = null;
if ( radByVisit.Checked )
{
// If we are filtering by nth visit, we might want to get up to first 5
byNthVisit = ddlNthVisit.SelectedValue.AsIntegerOrNull();
if ( byNthVisit.HasValue && byNthVisit > 2 )
{
nthVisitsTake = byNthVisit.Value;
}
}
bool showNonAttenders = byNthVisit.HasValue && byNthVisit.Value == 0;
// Get any attendance pattern filters
int? attendedMinCount = null;
int? attendedMissedCount = null;
DateRange attendedMissedDateRange = new DateRange();
if ( radByPattern.Checked )
{
attendedMinCount = tbPatternXTimes.Text.AsIntegerOrNull();
if ( cbPatternAndMissed.Checked )
{
attendedMissedCount = tbPatternMissedXTimes.Text.AsIntegerOrNull();
attendedMissedDateRange = new DateRange( drpPatternDateRange.LowerValue, drpPatternDateRange.UpperValue );
if ( !attendedMissedDateRange.Start.HasValue || !attendedMissedDateRange.End.HasValue )
{
nbMissedDateRangeRequired.Visible = true;
return;
}
}
}
nbMissedDateRangeRequired.Visible = false;
// Determine how dates shold be grouped
ChartGroupBy groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week;
// Determine if parents or children are being included with results
var includeParents = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ParentsOfAttendees;
var includeChildren = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ChildrenOfAttendees;
// Atttendance results
var allAttendeeVisits = new Dictionary<int, AttendeeVisits>();
var allResults = new List<AttendeeResult>();
// Collection of async queries to run before assembling data
var qryTasks = new List<Task>();
DataTable dtAttendeeLastAttendance = null;
DataTable dtAttendees = null;
DataTable dtAttendeeFirstDates = null;
List<int> personIdsWhoDidNotMiss = null;
if ( !showNonAttenders )
{
// Call the stored procedure to get all the person ids and their attendance dates for anyone
// whith attendance that matches the selected criteria.
qryTasks.Add( Task.Run( () =>
{
DataTable dtAttendeeDates = AttendanceService.GetAttendanceAnalyticsAttendeeDates(
groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList ).Tables[0];
foreach ( DataRow row in dtAttendeeDates.Rows )
{
int personId = (int)row["PersonId"];
allAttendeeVisits.AddOrIgnore( personId, new AttendeeVisits() );
var result = allAttendeeVisits[personId];
result.PersonId = personId;
DateTime summaryDate = DateTime.MinValue;
switch ( groupBy )
{
case ChartGroupBy.Week: summaryDate = (DateTime)row["SundayDate"]; break;
case ChartGroupBy.Month: summaryDate = (DateTime)row["MonthDate"]; break;
case ChartGroupBy.Year: summaryDate = (DateTime)row["YearDate"]; break;
}
if ( !result.AttendanceSummary.Contains( summaryDate ) )
{
result.AttendanceSummary.Add( summaryDate );
}
}
} ) );
// Call the stored procedure to get the last attendance
qryTasks.Add( Task.Run( () =>
{
dtAttendeeLastAttendance = AttendanceService.GetAttendanceAnalyticsAttendeeLastAttendance(
groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList ).Tables[0];
} ) );
// Call the stored procedure to get the names/demographic info for attendess
qryTasks.Add( Task.Run( () =>
{
dtAttendees = AttendanceService.GetAttendanceAnalyticsAttendees(
groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList, includeParents, includeChildren ).Tables[0];
} ) );
// If checking for missed attendance, get the people who missed that number of dates during the missed date range
if ( attendedMissedCount.HasValue &&
attendedMissedDateRange.Start.HasValue &&
attendedMissedDateRange.End.HasValue )
{
qryTasks.Add( Task.Run( () =>
{
personIdsWhoDidNotMiss = new List<int>();
DataTable dtAttendeeDatesMissed = AttendanceService.GetAttendanceAnalyticsAttendeeDates(
groupIdList, attendedMissedDateRange.Start.Value, attendedMissedDateRange.End.Value,
campusIdList, includeNullCampus, scheduleIdList ).Tables[0];
var missedResults = new Dictionary<int, AttendeeResult>();
foreach ( DataRow row in dtAttendeeDatesMissed.Rows )
{
int personId = (int)row["PersonId"];
missedResults.AddOrIgnore( personId, new AttendeeResult() );
var missedResult = missedResults[personId];
missedResult.PersonId = personId;
DateTime summaryDate = DateTime.MinValue;
switch ( groupBy )
{
case ChartGroupBy.Week: summaryDate = (DateTime)row["SundayDate"]; break;
case ChartGroupBy.Month: summaryDate = (DateTime)row["MonthDate"]; break;
case ChartGroupBy.Year: summaryDate = (DateTime)row["YearDate"]; break;
}
if ( !missedResult.AttendanceSummary.Contains( summaryDate ) )
{
missedResult.AttendanceSummary.Add( summaryDate );
}
}
var missedPossibleDates = GetPossibleAttendancesForDateRange( attendedMissedDateRange, groupBy );
int missedPossibleCount = missedPossibleDates.Count();
personIdsWhoDidNotMiss = missedResults
.Where( m => missedPossibleCount - m.Value.AttendanceSummary.Count < attendedMissedCount.Value )
.Select( m => m.Key )
.ToList();
} ) );
}
// Call the stored procedure to get the first five dates that any person attended this group type
qryTasks.Add( Task.Run( () =>
{
dtAttendeeFirstDates = AttendanceService.GetAttendanceAnalyticsAttendeeFirstDates(
groupTypeIdList, groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList ).Tables[0];
} ) );
}
else
{
qryTasks.Add( Task.Run( () =>
{
DataSet ds = AttendanceService.GetAttendanceAnalyticsNonAttendees(
groupTypeIdList, groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList, includeParents, includeChildren );
DataTable dtNonAttenders = ds.Tables[0];
dtAttendeeFirstDates = ds.Tables[1];
dtAttendeeLastAttendance = ds.Tables[2];
foreach ( DataRow row in dtNonAttenders.Rows )
{
int personId = (int)row["Id"];
var result = new AttendeeResult();
result.PersonId = personId;
var person = new PersonInfo();
person.NickName = row["NickName"].ToString();
person.LastName = row["LastName"].ToString();
person.Email = row["Email"].ToString();
person.Birthdate = row["BirthDate"] as DateTime?;
person.Age = Person.GetAge( person.Birthdate );
person.ConnectionStatusValueId = row["ConnectionStatusValueId"] as int?;
result.Person = person;
if ( includeParents )
{
result.ParentId = (int)row["ParentId"];
var parent = new PersonInfo();
parent.NickName = row["ParentNickName"].ToString();
parent.LastName = row["ParentLastName"].ToString();
parent.Email = row["ParentEmail"].ToString();
parent.Birthdate = row["ParentBirthDate"] as DateTime?;
parent.Age = Person.GetAge( parent.Birthdate );
result.Parent = parent;
}
if ( includeChildren )
{
var child = new PersonInfo();
result.ChildId = (int)row["ChildId"];
child.NickName = row["ChildNickName"].ToString();
child.LastName = row["ChildLastName"].ToString();
child.Email = row["ChildEmail"].ToString();
child.Birthdate = row["ChildBirthDate"] as DateTime?;
child.Age = Person.GetAge( child.Birthdate );
result.Child = child;
}
allResults.Add( result );
}
} ) );
}
// If a dataview filter was included, find the people who match that criteria
List<int> dataViewPersonIds = null;
qryTasks.Add( Task.Run( () =>
{
var dataViewId = dvpDataView.SelectedValueAsInt();
if ( dataViewId.HasValue )
{
dataViewPersonIds = new List<int>();
var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
var dvPersonService = new PersonService( _rockContext );
ParameterExpression paramExpression = dvPersonService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( dvPersonService, paramExpression, out errorMessages );
SortProperty sort = null;
var dataViewPersonIdQry = dvPersonService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.Id );
dataViewPersonIds = dataViewPersonIdQry.ToList();
}
}
} ) );
// Wait for all the queries to finish
Task.WaitAll( qryTasks.ToArray() );
if ( !showNonAttenders )
{
var attendees = allAttendeeVisits.AsQueryable();
// If dataview filter was included remove anyone not in that dataview
if ( dataViewPersonIds != null )
{
attendees = attendees.Where( p => dataViewPersonIds.Contains( p.Key ) );
}
// If filter for number missed was included, remove anyone who did not match that filter
if ( personIdsWhoDidNotMiss != null )
{
attendees = attendees.Where( p => !personIdsWhoDidNotMiss.Contains( p.Key ) );
}
// If filtering by minimum times attended
if ( attendedMinCount.HasValue )
{
attendees = attendees.Where( p => p.Value.AttendanceSummary.Count() >= attendedMinCount );
}
// Force filter application
allAttendeeVisits = attendees.ToDictionary( k => k.Key, v => v.Value );
// Add the First Visit information
foreach ( DataRow row in dtAttendeeFirstDates.Rows )
{
int personId = (int)row["PersonId"];
if ( allAttendeeVisits.ContainsKey( personId ) )
{
allAttendeeVisits[personId].FirstVisits.Add( (DateTime)row["StartDate"] );
}
}
// If filtering based on visit time, only include those who visited the selected time during the date range
if ( byNthVisit.HasValue )
{
int skipCount = byNthVisit.Value - 1;
allAttendeeVisits = allAttendeeVisits
.Where( p => p.Value.FirstVisits.Skip( skipCount ).Take( 1 ).Any( d => d >= start && d < end ) )
.ToDictionary( k => k.Key, v => v.Value );
}
// Add the Last Attended information
if ( dtAttendeeLastAttendance != null )
{
foreach ( DataRow row in dtAttendeeLastAttendance.Rows )
{
int personId = (int)row["PersonId"];
if ( allAttendeeVisits.ContainsKey( personId ) )
{
var result = allAttendeeVisits[personId];
if ( result.LastVisit == null )
{
var lastAttendance = new PersonLastAttendance();
lastAttendance.CampusId = row["CampusId"] as int?;
lastAttendance.GroupId = row["GroupId"] as int?;
lastAttendance.GroupName = row["GroupName"].ToString();
lastAttendance.RoleName = row["RoleName"].ToString();
lastAttendance.InGroup = !string.IsNullOrWhiteSpace( lastAttendance.RoleName );
lastAttendance.ScheduleId = row["ScheduleId"] as int?;
lastAttendance.StartDateTime = (DateTime)row["StartDateTime"];
lastAttendance.LocationId = row["LocationId"] as int?;
lastAttendance.LocationName = row["LocationName"].ToString();
result.LastVisit = lastAttendance;
}
}
}
}
// Add the Demographic information
if ( dtAttendees != null )
{
var newResults = new Dictionary<int, AttendeeResult>();
foreach ( DataRow row in dtAttendees.Rows )
{
int personId = (int)row["Id"];
if ( allAttendeeVisits.ContainsKey( personId ) )
{
var result = new AttendeeResult( allAttendeeVisits[personId] );
var person = new PersonInfo();
person.NickName = row["NickName"].ToString();
person.LastName = row["LastName"].ToString();
person.Email = row["Email"].ToString();
person.Birthdate = row["BirthDate"] as DateTime?;
person.Age = Person.GetAge( person.Birthdate );
person.ConnectionStatusValueId = row["ConnectionStatusValueId"] as int?;
result.Person = person;
if ( includeParents )
{
result.ParentId = (int)row["ParentId"];
var parent = new PersonInfo();
parent.NickName = row["ParentNickName"].ToString();
parent.LastName = row["ParentLastName"].ToString();
parent.Email = row["ParentEmail"].ToString();
parent.Birthdate = row["ParentBirthDate"] as DateTime?;
parent.Age = Person.GetAge( parent.Birthdate );
result.Parent = parent;
}
if ( includeChildren )
{
var child = new PersonInfo();
result.ChildId = (int)row["ChildId"];
child.NickName = row["ChildNickName"].ToString();
child.LastName = row["ChildLastName"].ToString();
child.Email = row["ChildEmail"].ToString();
child.Birthdate = row["ChildBirthDate"] as DateTime?;
child.Age = Person.GetAge( child.Birthdate );
result.Child = child;
}
allResults.Add( result );
}
}
}
}
else
{
// If dataview filter was included remove anyone not in that dataview
if ( dataViewPersonIds != null )
{
allResults = allResults
.Where( p => dataViewPersonIds.Contains( p.PersonId ) )
.ToList();
}
// Add the first visit dates for people
foreach ( DataRow row in dtAttendeeFirstDates.Rows )
{
int personId = (int)row["PersonId"];
foreach ( var result in allResults.Where( r => r.PersonId == personId ) )
{
result.FirstVisits.Add( (DateTime)row["StartDate"] );
}
}
// Add the Last Attended information
if ( dtAttendeeLastAttendance != null )
{
foreach ( DataRow row in dtAttendeeLastAttendance.Rows )
{
int personId = (int)row["PersonId"];
foreach ( var result in allResults.Where( r => r.PersonId == personId ) )
{
if ( result.LastVisit == null )
{
var lastAttendance = new PersonLastAttendance();
lastAttendance.CampusId = row["CampusId"] as int?;
lastAttendance.GroupId = row["GroupId"] as int?;
lastAttendance.GroupName = row["GroupName"].ToString();
lastAttendance.RoleName = row["RoleName"].ToString();
lastAttendance.InGroup = !string.IsNullOrWhiteSpace( lastAttendance.RoleName );
lastAttendance.ScheduleId = row["ScheduleId"] as int?;
lastAttendance.StartDateTime = (DateTime)row["StartDateTime"];
lastAttendance.LocationId = row["LocationId"] as int?;
lastAttendance.LocationName = row["LocationName"].ToString();
result.LastVisit = lastAttendance;
}
}
}
}
}
// Begin formatting the columns
var qryResult = allResults.AsQueryable();
var personUrlFormatString = ( (RockPage)this.Page ).ResolveRockUrl( "~/Person/{0}" );
var personHyperLinkField = gAttendeesAttendance.Columns.OfType<HyperLinkField>().FirstOrDefault( a => a.HeaderText == "Name" );
if ( personHyperLinkField != null )
{
personHyperLinkField.DataNavigateUrlFormatString = personUrlFormatString;
}
var parentHyperLinkField = gAttendeesAttendance.Columns.OfType<HyperLinkField>().FirstOrDefault( a => a.HeaderText == "Parent" );
if ( parentHyperLinkField != null )
{
parentHyperLinkField.Visible = includeParents;
parentHyperLinkField.DataNavigateUrlFormatString = personUrlFormatString;
}
var parentField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Parent" );
if ( parentField != null )
{
parentField.ExcelExportBehavior = includeParents ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
var parentEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Parent Email" );
if ( parentEmailField != null )
{
parentEmailField.ExcelExportBehavior = includeParents ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
var childHyperLinkField = gAttendeesAttendance.Columns.OfType<HyperLinkField>().FirstOrDefault( a => a.HeaderText == "Child" );
if ( childHyperLinkField != null )
{
childHyperLinkField.Visible = includeChildren;
childHyperLinkField.DataNavigateUrlFormatString = personUrlFormatString;
}
var childfield = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Child" );
if ( childfield != null )
{
childfield.ExcelExportBehavior = includeChildren ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
var childEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Child Email" );
if ( childEmailField != null )
{
childEmailField.ExcelExportBehavior = includeChildren ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
var childAgeField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Child Age" );
if ( childAgeField != null )
{
childAgeField.ExcelExportBehavior = includeChildren ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
SortProperty sortProperty = gAttendeesAttendance.SortProperty;
if ( sortProperty != null )
{
if ( sortProperty.Property == "AttendanceSummary.Count" )
{
if ( sortProperty.Direction == SortDirection.Descending )
{
qryResult = qryResult.OrderByDescending( a => a.AttendanceSummary.Count() );
}
else
{
qryResult = qryResult.OrderBy( a => a.AttendanceSummary.Count() );
}
}
else if ( sortProperty.Property == "FirstVisit.StartDateTime" )
{
if ( sortProperty.Direction == SortDirection.Descending )
{
qryResult = qryResult.OrderByDescending( a => a.FirstVisits.Min() );
}
else
{
qryResult = qryResult.OrderBy( a => a.FirstVisits.Min() );
}
}
else
{
qryResult = qryResult.Sort( sortProperty );
}
}
else
{
qryResult = qryResult.OrderBy( a => a.Person.LastName ).ThenBy( a => a.Person.NickName );
}
var attendancePercentField = gAttendeesAttendance.Columns.OfType<RockTemplateField>().First( a => a.HeaderText.EndsWith( "Attendance %" ) );
attendancePercentField.HeaderText = string.Format( "{0}ly Attendance %", groupBy.ConvertToString() );
// Calculate all the possible attendance summary dates
UpdatePossibleAttendances( dateRange, groupBy );
// pre-load the schedule names since FriendlyScheduleText requires building the ICal object, etc
_scheduleNameLookup = new ScheduleService( _rockContext ).Queryable()
.ToList()
.ToDictionary( k => k.Id, v => v.FriendlyScheduleText );
if ( includeParents )
{
gAttendeesAttendance.PersonIdField = "ParentId";
gAttendeesAttendance.DataKeyNames = new string[] { "ParentId", "PersonId" };
}
else if ( includeChildren )
{
gAttendeesAttendance.PersonIdField = "ChildId";
gAttendeesAttendance.DataKeyNames = new string[] { "ChildId", "PersonId" };
}
else
{
gAttendeesAttendance.PersonIdField = "PersonId";
gAttendeesAttendance.DataKeyNames = new string[] { "PersonId" };
}
// Create the dynamic attendance grid columns as needed
CreateDynamicAttendanceGridColumns();
try
{
nbAttendeesError.Visible = false;
gAttendeesAttendance.SetLinqDataSource( qryResult );
var currentPageItems = gAttendeesAttendance.DataSource as List<AttendeeResult>;
if ( currentPageItems != null )
{
var currentPagePersonIds = new List<int>();
if ( includeParents )
{
currentPagePersonIds = currentPageItems.Select( i => i.ParentId ).ToList();
gAttendeesAttendance.PersonIdField = "ParentId";
gAttendeesAttendance.DataKeyNames = new string[] { "ParentId", "PersonId" };
}
else if ( includeChildren )
{
currentPagePersonIds = currentPageItems.Select( i => i.ChildId ).ToList();
gAttendeesAttendance.PersonIdField = "ChildId";
gAttendeesAttendance.DataKeyNames = new string[] { "ChildId", "PersonId" };
}
else
{
currentPagePersonIds = currentPageItems.Select( i => i.PersonId ).ToList();
gAttendeesAttendance.PersonIdField = "PersonId";
gAttendeesAttendance.DataKeyNames = new string[] { "PersonId" };
}
LoadCurrentPageObjects( currentPagePersonIds );
}
_currentlyExporting = isExporting;
gAttendeesAttendance.DataBind();
_currentlyExporting = false;
}
catch ( Exception exception )
{
LogAndShowException( exception );
}
}
/// <summary>
/// Gets financial transaction details based on selected filter values.
/// </summary>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="minAmount">The minimum amount.</param>
/// <param name="maxAmount">The maximum amount.</param>
/// <param name="currencyTypeIds">The currency type ids.</param>
/// <param name="sourceTypeIds">The source type ids.</param>
/// <param name="accountIds">The account ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IQueryable <FinancialTransactionDetail> GetGifts(
DateTime?start, DateTime?end, decimal?minAmount, decimal?maxAmount,
List <int> currencyTypeIds, List <int> sourceTypeIds, List <int> accountIds, int?dataViewId)
{
// Base Transaction Detail query
var qry = GetGifts();
// Start Date Filter
if (start.HasValue)
{
qry = qry.Where(t => t.Transaction.TransactionDateTime >= start.Value);
}
// End Date Filter
if (end.HasValue)
{
qry = qry.Where(t => t.Transaction.TransactionDateTime < end.Value);
}
// Account Id Filter
var distictAccountIds = accountIds.Where(i => i != 0).Distinct().ToList();
if (distictAccountIds.Any())
{
qry = qry
.Where(t =>
distictAccountIds.Contains(t.AccountId));
}
// Currency Type Filter
var distictCurrencyTypeIds = currencyTypeIds.Where(i => i != 0).Distinct().ToList();
if (distictCurrencyTypeIds.Any())
{
qry = qry
.Where(t =>
t.Transaction.FinancialPaymentDetail != null &&
t.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.HasValue &&
distictCurrencyTypeIds.Contains(t.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.Value));
}
// Source Type Filter
var distictSourceTypeIds = sourceTypeIds.Where(i => i != 0).Distinct().ToList();
if (distictSourceTypeIds.Any())
{
qry = qry
.Where(t =>
t.Transaction.SourceTypeValueId.HasValue &&
distictSourceTypeIds.Contains(t.Transaction.SourceTypeValueId.Value));
}
// Amount Range Filter
if (minAmount.HasValue || maxAmount.HasValue)
{
var givingIdQry = qry
.GroupBy(d => d.Transaction.AuthorizedPersonAlias.Person.GivingId)
.Select(d => new { d.Key, Total = d.Sum(t => t.Amount) })
.Where(s =>
(!minAmount.HasValue || s.Total >= minAmount.Value) &&
(!maxAmount.HasValue || s.Total <= maxAmount.Value))
.Select(s => s.Key);
// put all the givingIds into a List instead of a subquery to prevent a timeout issue
var givingIdList = givingIdQry.ToList();
qry = qry
.Where(d =>
givingIdList.Contains(d.Transaction.AuthorizedPersonAlias.Person.GivingId));
}
// Data View Filter
if (dataViewId.HasValue)
{
var rockContext = (RockContext)this.Context;
if (rockContext != null)
{
var personService = new PersonService(rockContext);
var dataView = new DataViewService(rockContext).Get(dataViewId.Value);
if (dataView != null)
{
var errorMessages = new List <string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages);
SortProperty sortProperty = null;
var dataViewGivingIdQry = personService
.Queryable().AsNoTracking()
.Where(paramExpression, whereExpression, sortProperty)
.Select(p => p.GivingId);
qry = qry
.Where(t =>
dataViewGivingIdQry.Contains(t.Transaction.AuthorizedPersonAlias.Person.GivingId));
}
}
}
return(qry);
}
private void BindData()
{
using ( var rockContext = new RockContext() )
{
var dataView = new DataViewService( rockContext ).Get( _dataViewGuid ?? Guid.Empty );
if ( dataView != null )
{
var personService = new PersonService( rockContext );
// Filter people by dataview
var errorMessages = new List<string>();
var paramExpression = personService.ParameterExpression;
var whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages );
var personQry = personService
.Queryable( false, false ).AsNoTracking()
.Where( paramExpression, whereExpression, null );
var dvPersonIdQry = personQry.Select( p => p.Id );
bool filteredQry = false;
// Filter by first name
string firstName = tbFirstName.Text.Trim();
if ( !string.IsNullOrWhiteSpace( firstName ) )
{
personQry = personQry.Where( p =>
p.FirstName.StartsWith( firstName ) ||
p.NickName.StartsWith( firstName ) );
filteredQry = true;
}
// Filter by last name
string lastName = tbLastName.Text.Trim();
if ( !string.IsNullOrWhiteSpace( lastName ) )
{
personQry = personQry.Where( p =>
p.LastName.StartsWith( lastName ) );
filteredQry = true;
}
if ( filteredQry || _showAllPeople )
{
SetColumnWidths();
if ( _optOutGroupGuid.HasValue )
{
var optOutPersonIdQry = new GroupMemberService( rockContext )
.Queryable().AsNoTracking()
.Where( g => g.Group.Guid.Equals( _optOutGroupGuid.Value ) )
.Select( g => g.PersonId );
personQry = personQry.Where( p =>
!optOutPersonIdQry.Contains( p.Id ) );
}
if ( _showFamily )
{
BindFamilies( rockContext, personQry, dvPersonIdQry );
}
else
{
BindPeople( rockContext, personQry );
}
}
else
{
rptPeople.Visible = false;
rptFamilies.Visible = false;
}
}
else
{
rptPeople.Visible = false;
rptFamilies.Visible = false;
ShowMessages( new List<string> { "This block requires a valid Data View setting." } );
}
if ( CurrentPerson != null && _optOutGroupGuid.HasValue )
{
bool optedOut = new GroupMemberService( rockContext )
.Queryable().AsNoTracking()
.Any( m =>
m.PersonId == CurrentPerson.Id &&
m.Group.Guid.Equals( _optOutGroupGuid.Value ) );
lbOptInOut.Text = optedOut ? "Opt in to the Directory" : "Opt Out of the Directory";
}
}
}