/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression( Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection )
{
DateTime currentDate = RockDateTime.Today;
int currentDayOfYear = currentDate.DayOfYear;
var values = selection.Split( '|' );
ComparisonType comparisonType = values[0].ConvertToEnum<ComparisonType>( ComparisonType.EqualTo );
int? ageValue = values[1].AsIntegerOrNull();
var rockContext = (RockContext)serviceInstance.Context;
var personAgeQuery = new PersonService( rockContext ).Queryable();
MemberExpression idExpression = Expression.Property( parameterExpression, "Id" );
Expression ageSelect = new Rock.Reporting.DataSelect.Person.AgeSelect().GetExpression( rockContext, idExpression, "" );
var personAgeEqualQuery = personAgeQuery.Where(
p => (p.BirthDate > SqlFunctions.DateAdd( "year", -SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ), currentDate )
? SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) - 1
: SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ))
== ageValue );
BinaryExpression compareEqualExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( personAgeEqualQuery, parameterExpression, "p" ) as BinaryExpression;
BinaryExpression result = FilterExpressionExtractor.AlterComparisonType( comparisonType, compareEqualExpression, null );
return result;
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var rockContext = new RockContext();
var recordTypeValueId = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_BUSINESS.AsGuid() ).Id;
var queryable = new PersonService( rockContext ).Queryable()
.Where( q => q.RecordTypeValueId == recordTypeValueId );
var businessName = string.Empty;
bool viaSearch = false;
// Use the name passed in the page parameter if given
if ( !string.IsNullOrWhiteSpace( PageParameter( "SearchTerm" ) ) )
{
viaSearch = true;
gfBusinessFilter.Visible = false;
businessName = PageParameter( "SearchTerm" );
}
else
{
// Business Name Filter
businessName = gfBusinessFilter.GetUserPreference( "Business Name" );
}
if ( !string.IsNullOrWhiteSpace( businessName ) )
{
queryable = queryable.Where( a => a.LastName.Contains( businessName ) );
}
if ( ! viaSearch )
{
var activeRecordStatusValueId = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE.AsGuid() ).Id;
string activeFilterValue = gfBusinessFilter.GetUserPreference( "Active Status" );
if ( activeFilterValue == "inactive" )
{
queryable = queryable.Where( b => b.RecordStatusValueId != activeRecordStatusValueId );
}
else if ( activeFilterValue == "active" )
{
queryable = queryable.Where( b => b.RecordStatusValueId == activeRecordStatusValueId );
}
SortProperty sortProperty = gBusinessList.SortProperty;
if ( sortProperty != null )
{
queryable = queryable.Sort( sortProperty );
}
else
{
queryable = queryable.OrderBy( q => q.LastName );
}
}
var groupMemberQuery = new GroupMemberService( rockContext ).Queryable();
var businessList = queryable.Select( b => new
{
Id = b.Id,
b.LastName,
BusinessName = b.LastName,
PhoneNumber = b.PhoneNumbers.FirstOrDefault().NumberFormatted,
Email = b.Email,
Address = b.Members
.Where( m => m.Group.GroupType.Guid.ToString() == Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY )
.SelectMany( m => m.Group.GroupLocations )
.FirstOrDefault()
.Location,
Contacts = b.Members
.Where( m => m.Group.GroupType.Guid.ToString() == Rock.SystemGuid.GroupType.GROUPTYPE_KNOWN_RELATIONSHIPS )
.SelectMany( m => m.Group.Members)
.Where( p => p.GroupRole.Guid.ToString() == Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_OWNER && p.PersonId != b.Id)
.Select( p => p.Person.LastName + ", " + p.Person.NickName)
} );
if ( viaSearch && businessList.ToList().Count == 1 )
{
ShowDetailForm( businessList.ToList()[0].Id );
}
else
{
gBusinessList.EntityTypeId = EntityTypeCache.Read<Person>().Id;
gBusinessList.DataSource = businessList.ToList();
gBusinessList.DataBind();
}
}
/// <summary>
/// Executes the specified context.
/// </summary>
/// <param name="context">The context.</param>
public void Execute( IJobExecutionContext context )
{
JobDataMap dataMap = context.JobDetail.JobDataMap;
Guid? groupGuid = dataMap.GetString( "EligibleFollowers" ).AsGuidOrNull();
Guid? systemEmailGuid = dataMap.GetString( "EmailTemplate" ).AsGuidOrNull();
int followingEventsSent = 0;
if ( groupGuid.HasValue && systemEmailGuid.HasValue )
{
var exceptionMsgs = new List<string>();
using ( var rockContext = new RockContext() )
{
var followingService = new FollowingService( rockContext );
var followingEventTypeService = new FollowingEventTypeService( rockContext );
var followingEventNotificationService = new FollowingEventNotificationService( rockContext );
// Get all the active event types
var eventTypes = followingEventTypeService
.Queryable().AsNoTracking()
.Where( e =>
e.EntityTypeId.HasValue &&
e.IsActive )
.OrderBy( e => e.Order )
.ToList();
// Get the required event types
var requiredEventTypes = eventTypes
.Where( e => e.IsNoticeRequired )
.ToList();
// The people who are eligible to get following event notices based on the group type setting for this job
var eligiblePersonIds = new GroupMemberService( rockContext )
.Queryable().AsNoTracking()
.Where( m =>
m.Group != null &&
m.Group.Guid.Equals( groupGuid.Value ) &&
m.GroupMemberStatus == GroupMemberStatus.Active &&
m.Person != null &&
m.Person.Email != null &&
m.Person.Email != "" )
.Select( m => m.PersonId )
.Distinct()
.ToList();
// Get all the subscriptions for the eligible people
var eventSubscriptions = new FollowingEventSubscriptionService( rockContext )
.Queryable( "PersonAlias" ).AsNoTracking()
.Where( f => eligiblePersonIds.Contains( f.PersonAlias.PersonId ) )
.ToList();
// Dictionaries used to store information that will be used to create notification
var personSubscriptions = new Dictionary<int, List<int>>(); // Key: personId, Value: list of event type ids that person subscribes to
var personFollowings = new Dictionary<int, List<int>>(); // Key: personId, Value: list of following ids that person follows
var eventsThatHappened = new Dictionary<int, Dictionary<int, string>>(); // Key: event type id Value: Dictionary of entity id and formatted event notice for the entity
//Get the subscriptions for each person
foreach ( int personId in eligiblePersonIds )
{
var personEventTypes = eventSubscriptions
.Where( s => s.PersonAlias.PersonId == personId )
.Select( s => s.EventType )
.ToList();
personEventTypes.AddRange( requiredEventTypes );
if ( personEventTypes.Any() )
{
personSubscriptions.AddOrIgnore( personId, personEventTypes
.OrderBy( e => e.Order )
.ThenBy( e => e.Name )
.Select( e => e.Id )
.Distinct()
.ToList() );
}
}
// Get a distinct list of each entitytype/entity that is being followed by anyone that subscribes to events
var followings = followingService
.Queryable( "PersonAlias" ).AsNoTracking()
.Where( f => personSubscriptions.Keys.Contains( f.PersonAlias.PersonId ) )
.ToList();
// group the followings by their type
var followedEntityIds = new Dictionary<int, List<int>>();
foreach ( var followedEntity in followings
.Select( f => new
{
f.EntityTypeId,
f.EntityId
} )
.Distinct() )
{
followedEntityIds.AddOrIgnore( followedEntity.EntityTypeId, new List<int>() );
followedEntityIds[followedEntity.EntityTypeId].Add( followedEntity.EntityId );
}
// group the followings by the follower
foreach ( int personId in personSubscriptions.Select( s => s.Key ) )
{
var personFollowing = followings
.Where( f => f.PersonAlias.PersonId == personId )
.Select( f => f.Id )
.ToList();
personFollowings.Add( personId, personFollowing );
}
var timestamp = RockDateTime.Now;
// foreach followed entitytype
foreach ( var keyVal in followedEntityIds )
{
// Get the entitytype
EntityTypeCache itemEntityType = EntityTypeCache.Read( keyVal.Key );
if ( itemEntityType.AssemblyName != null )
{
// get the actual type of what is being followed
Type entityType = itemEntityType.GetEntityType();
if ( entityType != null )
{
var dbContext = Reflection.GetDbContextForEntityType( entityType );
if ( dbContext != null )
{
var serviceInstance = Reflection.GetServiceForEntityType( entityType, dbContext );
if ( serviceInstance != null )
{
MethodInfo qryMethod = serviceInstance.GetType().GetMethod( "Queryable", new Type[] { } );
var entityQry = qryMethod.Invoke( serviceInstance, new object[] { } ) as IQueryable<IEntity>;
// If looking at person alias following, make sure to exclude deceased people
if ( entityType == typeof( Rock.Model.PersonAlias ) )
{
var personAliasQry = entityQry as IQueryable<PersonAlias>;
if ( personAliasQry != null )
{
entityQry = personAliasQry.Where( p => !p.Person.IsDeceased );
}
}
var entityList = entityQry.Where( q => keyVal.Value.Contains( q.Id ) ).ToList();
// If there are any followed entities of this type
if ( entityList.Any() )
{
// Get the active event types for this entity type
foreach ( var eventType in eventTypes.Where( e => e.FollowedEntityTypeId == keyVal.Key ) )
{
try
{
// Get the component
var eventComponent = eventType.GetEventComponent();
if ( eventComponent != null )
{
// Get the previous notificatoins for this event type
var previousNotifications = followingEventNotificationService
.Queryable()
.Where( n => n.FollowingEventTypeId == eventType.Id )
.ToList();
// check each entity that is followed (by anyone)
foreach ( IEntity entity in entityList )
{
var previousNotification = previousNotifications
.Where( n => n.EntityId == entity.Id )
.FirstOrDefault();
DateTime? lastNotification = previousNotification != null ? previousNotification.LastNotified : (DateTime?)null;
// if the event happened
if ( eventComponent.HasEventHappened( eventType, entity, lastNotification ) )
{
// Store the event type id and the entity for later processing of notifications
eventsThatHappened.AddOrIgnore( eventType.Id, new Dictionary<int, string>() );
eventsThatHappened[eventType.Id].Add( entity.Id, eventComponent.FormatEntityNotification( eventType, entity ) );
if ( previousNotification == null )
{
previousNotification = new FollowingEventNotification();
previousNotification.FollowingEventTypeId = eventType.Id;
previousNotification.EntityId = entity.Id;
followingEventNotificationService.Add( previousNotification );
}
previousNotification.LastNotified = timestamp;
}
}
rockContext.SaveChanges();
}
eventType.LastCheckDateTime = RockDateTime.Now;
}
catch ( Exception ex )
{
exceptionMsgs.Add( string.Format( "An exception occurred calculating events for the '{0}' suggestion type:{1} {2}", eventType.Name, Environment.NewLine, ex.Messages().AsDelimited( Environment.NewLine + " " ) ) );
ExceptionLogService.LogException( ex, System.Web.HttpContext.Current );
}
}
}
}
}
}
}
}
// send notificatons
var appRoot = Rock.Web.Cache.GlobalAttributesCache.Read( rockContext ).GetValue( "ExternalApplicationRoot" );
var possibleRecipients = new PersonService( rockContext )
.Queryable().AsNoTracking()
.Where( p => personSubscriptions.Keys.Contains( p.Id ) )
.ToList();
// Loop through the possible recipients that actually subscribe to events
foreach ( var personSubscription in personSubscriptions )
{
// Get the recipient person
int personId = personSubscription.Key;
var person = possibleRecipients.Where( p => p.Id == personId ).FirstOrDefault();
if ( person != null )
{
try
{
// Make sure person is actually following anything
if ( personFollowings.ContainsKey( personId ) )
{
// Dictionary to store the entities that had an event for each event type
var personEventTypeNotices = new List<FollowingEventTypeNotices>();
// Get the event types that person subscribes to
foreach ( var eventType in eventsThatHappened.Where( e => personSubscription.Value.Contains( e.Key ) ) )
{
// Get the EntityTypeId for this event type
int entityTypeId = eventTypes
.Where( e => e.Id == eventType.Key )
.Select( e => e.FollowedEntityTypeId.Value )
.FirstOrDefault();
// Find all the entities with this event type that the person follows
var personFollowedEntityIds = followings
.Where( f =>
personFollowings[personId].Contains( f.Id ) &&
f.EntityTypeId == entityTypeId )
.Select( f => f.EntityId )
.ToList();
// Get any of those entities that had an event happen
var personFollowedEntities = eventType.Value
.Where( e => personFollowedEntityIds.Contains( e.Key ) )
.ToList();
// If any were found
if ( personFollowedEntities.Any() )
{
// Add the entry
var eventTypeObj = eventTypes.Where( e => e.Id == eventType.Key ).FirstOrDefault();
if ( eventTypeObj != null )
{
personEventTypeNotices.Add( new FollowingEventTypeNotices( eventTypeObj, personFollowedEntities.Select( e => e.Value ).ToList() ) );
}
}
}
// If there are any events for any of the entities that this person follows, send a notification
if ( personEventTypeNotices.Any() )
{
// Send the notice
var recipients = new List<RecipientData>();
var mergeFields = new Dictionary<string, object>();
mergeFields.Add( "Person", person );
mergeFields.Add( "EventTypes", personEventTypeNotices.OrderBy( e => e.EventType.Order ).ToList() );
recipients.Add( new RecipientData( person.Email, mergeFields ) );
Email.Send( systemEmailGuid.Value, recipients, appRoot );
followingEventsSent++;
}
}
}
catch ( Exception ex )
{
exceptionMsgs.Add( string.Format( "An exception occurred sending event notice to '{0}':{1} {2}", person.FullName, Environment.NewLine, ex.Messages().AsDelimited( Environment.NewLine + " " ) ) );
ExceptionLogService.LogException( ex, System.Web.HttpContext.Current );
}
}
}
}
context.Result = string.Format( "{0} following events emails sent", followingEventsSent );
if ( exceptionMsgs.Any() )
{
throw new Exception( "One or more exceptions occurred calculating following events..." + Environment.NewLine + exceptionMsgs.AsDelimited( Environment.NewLine ) );
}
}
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression( Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection )
{
// GradeTransitionDate is stored as just MM/DD so it'll resolve to the current year
DateTime? gradeTransitionDate = GlobalAttributesCache.Read().GetValue( "GradeTransitionDate" ).AsDateTime();
int? gradeMaxFactorReactor = null;
int currentYear = RockDateTime.Now.Year;
var values = selection.Split( '|' );
ComparisonType comparisonType = values[0].ConvertToEnum<ComparisonType>( ComparisonType.EqualTo );
int? gradeValue = values[1].AsIntegerOrNull();
var personGradeQuery = new PersonService( (RockContext)serviceInstance.Context ).Queryable();
if ( gradeTransitionDate.HasValue )
{
gradeMaxFactorReactor = ( RockDateTime.Now < gradeTransitionDate ) ? 12 : 13;
var personEqualGradeQuery = personGradeQuery.Where( p => ( gradeMaxFactorReactor - ( SqlFunctions.DatePart( "year", p.GraduationDate ) - currentYear ) == gradeValue ) );
BinaryExpression compareEqualExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( personEqualGradeQuery, parameterExpression, "p" ) as BinaryExpression;
BinaryExpression result = FilterExpressionExtractor.AlterComparisonType( comparisonType, compareEqualExpression, null );
return result;
}
else
{
if ( comparisonType == ComparisonType.IsBlank )
{
// if no gradeTransitionDate, return true (everybody has a blank grade)
personGradeQuery = personGradeQuery.Where( p => true );
}
else
{
// if no gradeTransitionDate, return false (nobody has a grade)
personGradeQuery = personGradeQuery.Where( p => false );
}
return FilterExpressionExtractor.Extract<Rock.Model.Person>( personGradeQuery, parameterExpression, "p" );
}
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var rockContext = new RockContext();
var recordTypeValueId = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_BUSINESS.AsGuid() ).Id;
var queryable = new PersonService( rockContext ).Queryable()
.Where( q => q.RecordTypeValueId == recordTypeValueId );
// Business Name Filter
var businessName = gfBusinessFilter.GetUserPreference( "Business Name" );
if ( !string.IsNullOrWhiteSpace( businessName ) )
{
queryable = queryable.Where( a => a.FirstName.Contains( businessName ) );
}
var activeRecordStatusValueId = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE.AsGuid() ).Id;
string activeFilterValue = gfBusinessFilter.GetUserPreference( "Active Status" );
if (activeFilterValue == "inactive")
{
queryable = queryable.Where( b => b.RecordStatusValueId != activeRecordStatusValueId );
}
else if (activeFilterValue == "active")
{
queryable = queryable.Where( b => b.RecordStatusValueId == activeRecordStatusValueId );
}
SortProperty sortProperty = gBusinessList.SortProperty;
if ( sortProperty != null )
{
gBusinessList.DataSource = queryable.Sort( sortProperty ).ToList();
}
else
{
gBusinessList.DataSource = queryable.OrderBy( q => q.FirstName ).ToList();
}
gBusinessList.DataBind();
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression( Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection )
{
// GradeTransitionDate is stored as just MM/DD so it'll resolve to the current year
DateTime? gradeTransitionDate = GlobalAttributesCache.Read().GetValue( "GradeTransitionDate" ).AsDateTime();
var values = selection.Split( '|' );
ComparisonType comparisonType = values[0].ConvertToEnum<ComparisonType>( ComparisonType.EqualTo );
Guid? gradeDefinedValueGuid = values[1].AsGuidOrNull();
DefinedTypeCache gradeDefinedType = DefinedTypeCache.Read( Rock.SystemGuid.DefinedType.SCHOOL_GRADES.AsGuid() );
DefinedValueCache gradeDefinedValue = gradeDefinedType.DefinedValues.FirstOrDefault( a => a.Guid == gradeDefinedValueGuid );
int? gradeOffset = gradeDefinedValue != null ? gradeDefinedValue.Value.AsIntegerOrNull() : null;
var personGradeQuery = new PersonService( (RockContext)serviceInstance.Context ).Queryable();
// if the next MM/DD of a graduation isn't until next year, treat next year as the current school year
int currentYearAdjustor = 0;
if ( gradeTransitionDate.HasValue && !( RockDateTime.Now < gradeTransitionDate ) )
{
currentYearAdjustor = 1;
}
int currentSchoolYear = RockDateTime.Now.AddYears( currentYearAdjustor ).Year;
if ( gradeTransitionDate.HasValue && gradeOffset.HasValue )
{
/*
* example (assuming defined values are the stock values):
* Billy graduates in 2020, the transition date is 6/1
* In other words, Billy graduates on 6/1/2020
* and current date is Feb 1, 2015.
* Stock Example:
* 9th Grade offset is 3
* 8th Grade offset is 4
* 7th Grade offset is 5
* 6th Grade offset is 6
* Billy graduates on 6/1/2020 and current date is Feb 1, 2015
* Therefore, his current grade offset is 5 yrs, which would mean he is in 7th grade
* *
* If the filter is:
* Equal to 7th grade...
* 7th Graders would be included.
* Grade offset must be LessThanOrEqualTo 5 and GreaterThan 4
* Not-Equal to 7th grade...
* 7th Graders would not be included.
* Grade offset must be LessThanOrEqualTo 4 or GreaterThan 5
* Less than 7th grade..
* 7th Graders would not be included, 6th and younger would be included.
* Grade offset must be GreaterThan 5
* Less than or Equal to 7th grade..
* 7th Graders and younger would be included.
* Grade offset must be GreaterThan 4
* Greater than 7th grade..
* 7th Graders would not be included, 8th Graders and older would be included.
* Grade offset must be LessThanOrEqualTo 4
* Greater than or Equal to 7th grade..
* 7th Graders and older would be included.
* Grade offset must be LessThanOrEqualTo 5
*
* Combined Example:
* High School offset is 3
* Jr High offset is 5
* K-6 offset is 12
* Billy graduates on 6/1/2020 and current date is Feb 1, 2015
* Therefore, his current grade offset is 5 yrs, which would mean he is in Jr High
*
* If the filter is:
* Equal to Jr High...
* Jr High would be included.
* Grade offset must be LessThanOrEqualTo 5 and GreaterThan 3
* Not-Equal to Jr High...
* Jr High would not be included.
* Grade offset must be LessThanOrEqualTo 3 or GreaterThan 5
* Less than Jr High..
* Jr High would not be included, K-6 and younger would be included.
* Grade offset must be GreaterThan 5
* Less than or Equal to Jr High..
* Jr High and younger would be included.
* Grade offset must be GreaterThan 3
* Greater than Jr High..
* Jr High would not be included, High School and older would be included.
* Grade offset must be LessThanOrEqualTo 3
* Greater than or Equal to Jr High..
* Jr High and older would be included.
* Grade offset must be LessThanOrEqualTo 5
*/
DefinedValueCache nextGradeDefinedValue = gradeDefinedType.DefinedValues
.OrderByDescending( a => a.Value.AsInteger() ).Where( a => a.Value.AsInteger() < gradeOffset ).FirstOrDefault();
int nextGradeOffset = nextGradeDefinedValue != null ? nextGradeDefinedValue.Value.AsInteger() : -1;
switch ( comparisonType )
{
case ComparisonType.EqualTo:
// Include people who have have a grade offset LessThanOrEqualTo selected grade's offset, but GreaterThan the next grade's offset
personGradeQuery = personGradeQuery.Where( p => p.GraduationYear - currentSchoolYear <= gradeOffset
&& p.GraduationYear - currentSchoolYear > nextGradeOffset );
break;
case ComparisonType.NotEqualTo:
// Include people who have have a grade offset LessThanOrEqualTo next grade's offset, or GreaterThan the selected grade's offset (and not already graduated)
personGradeQuery = personGradeQuery.Where( p => ( p.GraduationYear - currentSchoolYear <= nextGradeOffset
|| p.GraduationYear - currentSchoolYear > gradeOffset )
&& p.GraduationYear - currentSchoolYear >= 0 );
break;
case ComparisonType.LessThan:
// Grade offset must be GreaterThan selected grade's offset
personGradeQuery = personGradeQuery.Where( p => p.GraduationYear - currentSchoolYear > gradeOffset );
break;
case ComparisonType.LessThanOrEqualTo:
// Grade offset must be GreaterThan next grade's offset
personGradeQuery = personGradeQuery.Where( p => p.GraduationYear - currentSchoolYear > nextGradeOffset );
break;
case ComparisonType.GreaterThan:
// Grade offset must be LessThanOrEqualTo next grade's offset (and not already graduated)
personGradeQuery = personGradeQuery.Where( p => p.GraduationYear - currentSchoolYear <= nextGradeOffset
&& p.GraduationYear - currentSchoolYear >= 0 );
break;
case ComparisonType.GreaterThanOrEqualTo:
// Grade offset must be LessThanOrEqualTo selected grade's offset (and not already graduated)
personGradeQuery = personGradeQuery.Where( p => p.GraduationYear - currentSchoolYear <= gradeOffset
&& p.GraduationYear - currentSchoolYear >= 0 );
break;
case ComparisonType.IsBlank:
// only return people that don't have a graduation year, or have already graduated
personGradeQuery = personGradeQuery.Where( p => !p.GraduationYear.HasValue || (p.GraduationYear - currentSchoolYear) < 0 );
break;
case ComparisonType.IsNotBlank:
// only return people that have a graduation date, and haven't graduated yet
personGradeQuery = personGradeQuery.Where( p => p.GraduationYear.HasValue && ( p.GraduationYear - currentSchoolYear ) >= 0 );
break;
}
Expression result = FilterExpressionExtractor.Extract<Rock.Model.Person>( personGradeQuery, parameterExpression, "p" );
return result;
}
else
{
if ( !gradeTransitionDate.HasValue )
{
if ( comparisonType == ComparisonType.IsBlank )
{
// if no gradeTransitionDate, return true (everybody has a blank grade)
personGradeQuery = personGradeQuery.Where( p => true );
}
else
{
// if no gradeTransitionDate, return false (nobody has a grade)
personGradeQuery = personGradeQuery.Where( p => false );
}
}
else
{
// there is a grade transition date, but the selected gradeOffset is null
if ( comparisonType == ComparisonType.IsBlank )
{
// if trying to find people without a Grade only include people that don't have a graduation date or already graduated
personGradeQuery = personGradeQuery.Where( p => !p.GraduationYear.HasValue || p.GraduationYear.Value < currentSchoolYear );
}
else if ( comparisonType == ComparisonType.IsNotBlank )
{
// if trying to find people with a Grade only include people that have a graduation date and haven't already graduated
personGradeQuery = personGradeQuery.Where( p => p.GraduationYear.HasValue && !( p.GraduationYear.Value < currentSchoolYear ) );
}
else
{
// if no grade selected and they are comparing return false (nobody meets the condition since the condition is invalid)
personGradeQuery = personGradeQuery.Where( p => false );
}
}
return FilterExpressionExtractor.Extract<Rock.Model.Person>( personGradeQuery, parameterExpression, "p" );
}
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression( Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection )
{
DateTime currentDate = RockDateTime.Today;
int currentDayOfYear = currentDate.DayOfYear;
var values = selection.Split( '|' );
ComparisonType comparisonType = values[0].ConvertToEnum<ComparisonType>( ComparisonType.EqualTo );
int? ageValue = values[1].AsIntegerOrNull();
var rockContext = (RockContext)serviceInstance.Context;
var personAgeQuery = new PersonService( rockContext ).Queryable();
MemberExpression idExpression = Expression.Property( parameterExpression, "Id" );
Expression ageSelect = new Rock.Reporting.DataSelect.Person.AgeSelect().GetExpression( rockContext, idExpression, "" );
if ( values.Length >= 3 && comparisonType == ComparisonType.Between )
{
var numberRangeEditor = new NumberRangeEditor();
numberRangeEditor.DelimitedValues = values[2];
decimal ageValueStart = numberRangeEditor.LowerValue ?? 0;
decimal ageValueEnd = numberRangeEditor.UpperValue ?? decimal.MaxValue;
var personAgeBetweenQuery = personAgeQuery.Where(
p => ( ( p.BirthDate > SqlFunctions.DateAdd( "year", -SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ), currentDate )
? SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) - 1
: SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) )
>= ageValueStart ) && ( ( p.BirthDate > SqlFunctions.DateAdd( "year", -SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ), currentDate )
? SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) - 1
: SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) )
<= ageValueEnd ) );
BinaryExpression result = FilterExpressionExtractor.Extract<Rock.Model.Person>( personAgeBetweenQuery, parameterExpression, "p" ) as BinaryExpression;
return result;
}
else
{
var personAgeEqualQuery = personAgeQuery.Where(
p => ( p.BirthDate > SqlFunctions.DateAdd( "year", -SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ), currentDate )
? SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) - 1
: SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) )
== ageValue );
BinaryExpression compareEqualExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( personAgeEqualQuery, parameterExpression, "p" ) as BinaryExpression;
BinaryExpression result = FilterExpressionExtractor.AlterComparisonType( comparisonType, compareEqualExpression, null );
return result;
}
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var rockContext = new RockContext();
var recordTypeValueId = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_BUSINESS.AsGuid() ).Id;
var activeRecordStatusValueId = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE.AsGuid() ).Id;
int? businessRoleId = new GroupTypeRoleService( rockContext ).Queryable()
.Where( r =>
r.Guid.Equals( new Guid( Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_BUSINESS ) ) )
.Select( r => r.Id )
.FirstOrDefault();
var queryable = new PersonService( rockContext ).Queryable()
.Where( q => q.RecordTypeValueId == recordTypeValueId && q.RecordStatusValueId == activeRecordStatusValueId );
// Business Name Filter
var businessName = gfBusinessFilter.GetUserPreference( "Business Name" );
if ( !string.IsNullOrWhiteSpace( businessName ) )
{
queryable = queryable.Where( a => a.FirstName.Contains( businessName ) );
}
// Owner Filter
int ownerId = 0;
if ( int.TryParse( gfBusinessFilter.GetUserPreference( "Owner" ), out ownerId ) && ownerId != 0 )
{
var members = queryable.SelectMany( a => a.Members ).ToList();
foreach ( var member in members )
{
if ( member.GroupRoleId == businessRoleId )
{
var groupMemberService = new GroupMemberService( rockContext );
var owner = groupMemberService.GetInverseRelationship( member, false, CurrentPersonAlias );
if ( owner.PersonId != ownerId )
{
queryable = queryable.Where( a => a.Id != member.PersonId );
}
}
}
}
SortProperty sortProperty = gBusinessList.SortProperty;
if ( sortProperty != null )
{
gBusinessList.DataSource = queryable.Sort( sortProperty ).ToList();
}
else
{
gBusinessList.DataSource = queryable.OrderBy( q => q.FirstName ).ToList();
}
gBusinessList.DataBind();
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="entityType">Type of the entity.</param>
/// <param name="serviceInstance">The service instance.</param>
/// <param name="parameterExpression">The parameter expression.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression( Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection )
{
DateTime currentDate = RockDateTime.Today;
int currentDayOfYear = currentDate.DayOfYear;
var values = selection.Split( '|' );
ComparisonType comparisonType = values[0].ConvertToEnum<ComparisonType>( ComparisonType.EqualTo );
int? ageValue = values[1].AsIntegerOrNull();
var personAgeQuery = new PersonService( (RockContext)serviceInstance.Context ).Queryable();
var personAgeEqualQuery = personAgeQuery.Where(
p => ( currentDayOfYear >= SqlFunctions.DatePart( "dayofyear", p.BirthDate )
? SqlFunctions.DateDiff( "year", p.BirthDate, currentDate )
: SqlFunctions.DateDiff( "year", p.BirthDate, currentDate ) - 1 )
== ageValue );
BinaryExpression compareEqualExpression = FilterExpressionExtractor.Extract<Rock.Model.Person>( personAgeEqualQuery, parameterExpression, "p" ) as BinaryExpression;
BinaryExpression result = FilterExpressionExtractor.AlterComparisonType( comparisonType, compareEqualExpression, null );
return result;
}
/// <summary>
/// Creates a Linq Expression that can be applied to an IQueryable to filter the result set.
/// </summary>
/// <param name="entityType">The type of entity in the result set.</param>
/// <param name="serviceInstance">A service instance that can be queried to obtain the result set.</param>
/// <param name="parameterExpression">The input parameter that will be injected into the filter expression.</param>
/// <param name="selection">A formatted string representing the filter settings.</param>
/// <returns>
/// A Linq Expression that can be used to filter an IQueryable.
/// </returns>
public override Expression GetExpression( Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection )
{
var settings = new FilterSettings( selection );
var context = (RockContext)serviceInstance.Context;
//
// Define Candidate Group Members.
//
// Get the Group Member Data View that defines the set of candidates from which matching Group Members can be selected.
var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent( settings.GroupMemberDataViewGuid, context );
var memberService = new GroupMemberService( context );
var memberQuery = memberService.Queryable();
if (dataView != null)
{
memberQuery = DataComponentSettingsHelper.FilterByDataView( memberQuery, dataView, memberService );
}
//
// Construct the Query to return the list of People matching the filter conditions.
//
var personQuery = new PersonService( context ).Queryable();
BinaryExpression result;
if (settings.MemberCountComparison.HasValue
&& settings.MemberCount.HasValue)
{
var comparisonType = settings.MemberCountComparison.Value;
int memberCountValue = settings.MemberCount.Value;
var memberKeys = memberQuery.Select( x => x.Id );
var memberCountEqualQuery = personQuery.Where( p => p.Members.Count( gm => memberKeys.Contains( gm.Id ) ) == memberCountValue );
var compareEqualExpression = FilterExpressionExtractor.Extract<Model.Person>( memberCountEqualQuery, parameterExpression, "p" ) as BinaryExpression;
result = FilterExpressionExtractor.AlterComparisonType( comparisonType, compareEqualExpression, 0 );
}
else
{
personQuery = personQuery.Where( p => memberQuery.Any(m => m.PersonId == p.Id ));
result = FilterExpressionExtractor.Extract<Model.Person>( personQuery, parameterExpression, "p" ) as BinaryExpression;
}
return result;
}
