private IQueryable <RelatedPersonInfo> GetRelatedPeopleQuery(RockContext context, IEnumerable <int> groupTypeIds, IEnumerable <Guid> principalRoleGuids, IEnumerable <Guid> targetRoleGuids,
string relationshipName = "*")
{
var relationshipGroupMembersQuery = new GroupMemberService(context).Queryable();
relationshipGroupMembersQuery = relationshipGroupMembersQuery.Where(x => groupTypeIds.Contains(x.Group.GroupTypeId));
var relatedPeopleQuery = new PersonService(context).Queryable()
.SelectMany(p => relationshipGroupMembersQuery.Where(gm => gm.PersonId == p.Id && principalRoleGuids.Contains(gm.GroupRole.Guid))
.SelectMany(gm => gm.Group.Members)
.Where(gm => targetRoleGuids.Contains(gm.GroupRole.Guid) && gm.PersonId != p.Id)
.OrderBy(gm => gm.GroupRole.Order)
.ThenBy(gm => gm.Person.BirthDate)
.ThenBy(gm => gm.Person.Gender)
.Select(
gm =>
new RelatedPersonInfo
{
RelatedToPersonId = p.Id,
PersonId = gm.Person.Id,
FirstName = gm.Person.FirstName,
LastName = gm.Person.LastName,
Suffix = gm.Person.SuffixValue.Value,
RelationshipName =
(relationshipName == "*"
? gm.GroupRole.Name
: relationshipName)
}));
return(relatedPeopleQuery);
}
private void ListGroups()
{
RockContext rockContext = new RockContext();
var qry = new GroupMemberService(rockContext)
.Queryable("Group")
.Where(m => m.PersonId == CurrentPersonId &&
m.GroupMemberStatus == GroupMemberStatus.Active &&
m.Group.IsActive == true);
List <Guid> includeGroupTypeGuids = GetAttributeValue("IncludeGroupTypes").SplitDelimitedValues().Select(a => Guid.Parse(a)).ToList();
if (includeGroupTypeGuids.Count > 0)
{
qry = qry.Where(t => includeGroupTypeGuids.Contains(t.Group.GroupType.Guid));
}
List <Guid> excludeGroupTypeGuids = GetAttributeValue("ExcludeGroupTypes").SplitDelimitedValues().Select(a => Guid.Parse(a)).ToList();
if (excludeGroupTypeGuids.Count > 0)
{
qry = qry.Where(t => !excludeGroupTypeGuids.Contains(t.Group.GroupType.Guid));
}
var groups = qry.Select(m => new GroupInvolvementSummary {
Group = m.Group, Role = m.GroupRole.Name, IsLeader = m.GroupRole.IsLeader, GroupType = m.Group.GroupType.Name
}).ToList();
var mergeFields = new Dictionary <string, object>();
mergeFields.Add("Groups", groups);
mergeFields.Add("CurrentPerson", CurrentPerson);
var globalAttributeFields = Rock.Web.Cache.GlobalAttributesCache.GetMergeFields(CurrentPerson);
globalAttributeFields.ToList().ForEach(d => mergeFields.Add(d.Key, d.Value));
Dictionary <string, object> linkedPages = new Dictionary <string, object>();
linkedPages.Add("DetailPage", LinkedPageUrl("DetailPage", null));
mergeFields.Add("LinkedPages", linkedPages);
string template = GetAttributeValue("LavaTemplate");
// show debug info
bool enableDebug = GetAttributeValue("EnableDebug").AsBoolean();
if (enableDebug && IsUserAuthorized(Authorization.EDIT))
{
lDebug.Visible = true;
lDebug.Text = mergeFields.lavaDebugInfo();
}
lContent.Text = template.ResolveMergeFields(mergeFields);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
var adultGuid = SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
var childGuid = SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid();
var familyGuid = SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
//Contains an array of the int representation of EmailPreferences selected
int[] selectedPreferences = null;
if (!string.IsNullOrEmpty(selection))
{
selectedPreferences = Array.ConvertAll(selection.Split(','), s => int.Parse(s));
}
var familyGroupMembers = new GroupMemberService(context).Queryable()
.Where(m => m.Group.GroupType.Guid == familyGuid);
IQueryable <IEnumerable <string> > personParentsEmailQuery;
//Done as an if statement because:
// 1) If you try and check selectedPreferences for null in LINQ it throws an exception
// 2) If all preferences are selected it's quicker to disregard the preference entirely
if (selectedPreferences == null || selectedPreferences.Length == 3)
{
// this returns Enumerable of Email addresses for Parents per row. The Grid then uses ListDelimiterField to convert the list into Parent's Phone Numbers
personParentsEmailQuery = new PersonService(context).Queryable()
.Select(p => familyGroupMembers.Where(s => s.PersonId == p.Id && s.GroupRole.Guid == childGuid)
.SelectMany(gm => gm.Group.Members)
.Where(m => m.GroupRole.Guid == adultGuid)
.Where(m => !string.IsNullOrEmpty(m.Person.Email) && m.Person.IsEmailActive)
.OrderBy(m => m.Group.Members.FirstOrDefault(x => x.PersonId == p.Id).GroupOrder ?? int.MaxValue)
.Select(q => q.Person.Email).AsEnumerable());
}
else
{
// this returns Enumerable of Email addresses for Parents per row. The Grid then uses ListDelimiterField to convert the list into Parent's Phone Numbers
personParentsEmailQuery = new PersonService(context).Queryable()
.Select(p => familyGroupMembers.Where(s => s.PersonId == p.Id && s.GroupRole.Guid == childGuid)
.SelectMany(gm => gm.Group.Members)
.Where(m => m.GroupRole.Guid == adultGuid)
.Where(m => selectedPreferences.Contains((int)m.Person.EmailPreference))
.Where(m => !string.IsNullOrEmpty(m.Person.Email) && m.Person.IsEmailActive)
.OrderBy(m => m.Group.Members.FirstOrDefault(x => x.PersonId == p.Id).GroupOrder ?? int.MaxValue)
.Select(q => q.Person.Email).AsEnumerable());
}
var selectEmail = SelectExpressionExtractor.Extract(personParentsEmailQuery, entityIdProperty, "p");
return(selectEmail);
}
/// <summary>
/// Gets the recipient person identifier expression.
/// </summary>
/// <param name="dbContext">The database context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public Expression GetRecipientPersonIdExpression( System.Data.Entity.DbContext dbContext, MemberExpression entityIdProperty, string selection )
{
var rockContext = dbContext as RockContext;
if ( rockContext != null )
{
Guid adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
Guid childGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid();
Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
var familyGroupMembers = new GroupMemberService( rockContext ).Queryable()
.Where( m => m.Group.GroupType.Guid == familyGuid );
// this returns Enumerable of KidInfo per row. See this.GetGridField to see how it is processed
var personChildrenQuery = new PersonService( rockContext ).Queryable()
.Select( p => familyGroupMembers.Where( s => s.PersonId == p.Id && s.GroupRole.Guid == adultGuid )
.SelectMany( m => m.Group.Members )
.Where( m => m.GroupRole.Guid == childGuid )
.OrderBy( m => m.Group.Members.FirstOrDefault( x => x.PersonId == p.Id ).GroupOrder ?? int.MaxValue )
.ThenBy( m => m.Person.BirthYear ).ThenBy( m => m.Person.BirthMonth ).ThenBy( m => m.Person.BirthDay )
.Select( m => m.Person.Id ).AsEnumerable() );
var selectChildrenExpression = SelectExpressionExtractor.Extract( personChildrenQuery, entityIdProperty, "p" );
return selectChildrenExpression;
}
return null;
}
/// <summary>
/// Gets the recipient person identifier expression.
/// </summary>
/// <param name="dbContext">The database context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public Expression GetRecipientPersonIdExpression(System.Data.Entity.DbContext dbContext, MemberExpression entityIdProperty, string selection)
{
var rockContext = dbContext as RockContext;
if (rockContext != null)
{
Guid adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
Guid childGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid();
Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
var familyGroupMembers = new GroupMemberService(rockContext).Queryable()
.Where(m => m.Group.GroupType.Guid == familyGuid);
// this returns Enumerable of ids for Parents per row. The Grid then uses ListDelimiterField to convert the list into Parents Names
var personParentsQuery = new PersonService(rockContext).Queryable()
.Select(p => familyGroupMembers.Where(s => s.PersonId == p.Id && s.GroupRole.Guid == childGuid)
.SelectMany(m => m.Group.Members)
.Where(m => m.GroupRole.Guid == adultGuid)
.OrderBy(m => m.Group.Members.FirstOrDefault(x => x.PersonId == p.Id).GroupOrder ?? int.MaxValue)
.ThenBy(m => m.Person.Gender)
.Select(m => m.Person.Id).AsEnumerable());
var selectParentsExpression = SelectExpressionExtractor.Extract(personParentsQuery, entityIdProperty, "p");
return(selectParentsExpression);
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
Guid adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
Guid childGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid();
Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
var familyGroupMembers = new GroupMemberService(context).Queryable()
.Where(m => m.Group.GroupType.Guid == familyGuid);
// this returns Enumerable of KidInfo per row. See this.GetGridField to see how it is processed
var personChildrenQuery = new PersonService(context).Queryable()
.Select(p => familyGroupMembers.Where(s => s.PersonId == p.Id && s.GroupRole.Guid == adultGuid)
.SelectMany(m => m.Group.Members)
.Where(m => m.GroupRole.Guid == childGuid)
.OrderBy(m => m.Person.BirthYear).ThenBy(m => m.Person.BirthMonth).ThenBy(m => m.Person.BirthDay)
.Select(m => new KidInfo
{
NickName = m.Person.NickName,
LastName = m.Person.LastName,
SuffixValueId = m.Person.SuffixValueId,
Gender = m.Person.Gender,
BirthDate = m.Person.BirthDate
}).AsEnumerable());
var selectChildrenExpression = SelectExpressionExtractor.Extract(personChildrenQuery, entityIdProperty, "p");
return(selectChildrenExpression);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
//// Spouse is determined if all these conditions are met
//// 1) Adult in the same family as Person (GroupType = Family, GroupRole = Adult, and in same Group)
//// 2) Opposite Gender as Person
//// 3) Both Persons are Married
var selectionParts = selection.Split('|');
bool includeLastname = selectionParts.Length > 0 && selectionParts[0].AsBoolean();
Guid adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
Guid marriedGuid = Rock.SystemGuid.DefinedValue.PERSON_MARITAL_STATUS_MARRIED.AsGuid();
int marriedDefinedValueId = DefinedValueCache.Get(marriedGuid).Id;
Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
var familyGroupMembers = new GroupMemberService(context).Queryable()
.Where(m => m.Group.GroupType.Guid == familyGuid);
var personSpouseQuery = new PersonService(context).Queryable()
.Select(p => familyGroupMembers.Where(s => s.PersonId == p.Id && s.Person.MaritalStatusValueId == marriedDefinedValueId && s.GroupRole.Guid == adultGuid)
.SelectMany(m => m.Group.Members)
.Where(m =>
m.PersonId != p.Id &&
m.GroupRole.Guid == adultGuid &&
m.Person.Gender != p.Gender &&
m.Person.MaritalStatusValueId == marriedDefinedValueId &&
!m.Person.IsDeceased)
.OrderBy(m => m.Group.Members.FirstOrDefault(x => x.PersonId == p.Id).GroupOrder ?? int.MaxValue)
.Select(m => includeLastname ? m.Person.NickName + " " + m.Person.LastName : m.Person.NickName).FirstOrDefault());
var selectSpouseExpression = SelectExpressionExtractor.Extract(personSpouseQuery, entityIdProperty, "p");
return(selectSpouseExpression);
}
/// <summary>
/// Gets the recipient person identifier expression.
/// </summary>
/// <param name="dbContext">The database context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public Expression GetRecipientPersonIdExpression(System.Data.Entity.DbContext dbContext, MemberExpression entityIdProperty, string selection)
{
var rockContext = dbContext as RockContext;
if (rockContext != null)
{
Guid adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
Guid marriedGuid = Rock.SystemGuid.DefinedValue.PERSON_MARITAL_STATUS_MARRIED.AsGuid();
int marriedDefinedValueId = DefinedValueCache.Get(marriedGuid).Id;
Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
var familyGroupMembers = new GroupMemberService(rockContext).Queryable()
.Where(m => m.Group.GroupType.Guid == familyGuid);
var personSpouseQuery = new PersonService(rockContext).Queryable()
.Select(p => familyGroupMembers.Where(s => s.PersonId == p.Id && s.Person.MaritalStatusValueId == marriedDefinedValueId && s.GroupRole.Guid == adultGuid)
.SelectMany(m => m.Group.Members)
.Where(m =>
m.PersonId != p.Id &&
m.GroupRole.Guid == adultGuid &&
m.Person.Gender != p.Gender &&
m.Person.MaritalStatusValueId == marriedDefinedValueId &&
!m.Person.IsDeceased)
.OrderBy(m => m.Group.Members.FirstOrDefault(x => x.PersonId == p.Id).GroupOrder ?? int.MaxValue)
.Select(m => m.Person.Id).FirstOrDefault());
var selectSpouseExpression = SelectExpressionExtractor.Extract(personSpouseQuery, entityIdProperty, "p");
return(selectSpouseExpression);
}
return(null);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
Guid adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
Guid childGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid();
Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
Guid?phoneNumberTypeValueGuid = selection.AsGuidOrNull();
if (!phoneNumberTypeValueGuid.HasValue)
{
phoneNumberTypeValueGuid = Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_HOME.AsGuid();
}
int phoneNumberTypeValueId = DefinedValueCache.Read(phoneNumberTypeValueGuid.Value).Id;
var familyGroupMembers = new GroupMemberService(context).Queryable()
.Where(m => m.Group.GroupType.Guid == familyGuid);
// this returns Enumerable of PhoneNumber for Parents per row. The Grid then uses ListDelimiterField to convert the list into Parent's Phone Numbers
var personParentsPhoneQuery = new PersonService(context).Queryable()
.Select(p => familyGroupMembers.Where(s => s.PersonId == p.Id && s.GroupRole.Guid == childGuid)
.SelectMany(m => m.Group.Members)
.Where(m => m.GroupRole.Guid == adultGuid)
.Select(m => m.Person)
.Where(m => m.PhoneNumbers.Count(t => t.NumberTypeValueId == phoneNumberTypeValueId) != 0)
.Select(m => m.PhoneNumbers.FirstOrDefault(t => t.NumberTypeValueId == phoneNumberTypeValueId)).AsEnumerable());
var selectNumbersExpression = SelectExpressionExtractor.Extract(personParentsPhoneQuery, entityIdProperty, "p");
return(selectNumbersExpression);
}
/// <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 People.
//
var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent(settings.PersonDataViewGuid, context);
var personService = new PersonService(context);
var personQuery = personService.Queryable();
if (dataView != null)
{
personQuery = DataComponentSettingsHelper.FilterByDataView(personQuery, dataView, personService);
}
var personKeys = personQuery.Select(x => x.Id);
//
// Construct the Query to return the list of Group Members matching the filter conditions.
//
var groupMemberQuery = new GroupMemberService(context).Queryable();
groupMemberQuery = groupMemberQuery.Where(gm => personKeys.Contains(gm.PersonId));
var result = FilterExpressionExtractor.Extract <Rock.Model.GroupMember>(groupMemberQuery, parameterExpression, "gm");
return(result);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
//// Spouse is determined if all these conditions are met
//// 1) Adult in the same family as Person (GroupType = Family, GroupRole = Adult, and in same Group)
//// 2) Opposite Gender as Person
//// 3) Both Persons are Married
Guid adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
Guid marriedGuid = Rock.SystemGuid.DefinedValue.PERSON_MARITAL_STATUS_MARRIED.AsGuid();
int marriedDefinedValueId = DefinedValueCache.Read(marriedGuid).Id;
Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
var familyGroupMembers = new GroupMemberService(context).Queryable()
.Where(m => m.Group.GroupType.Guid == familyGuid);
var personSpouseQuery = new PersonService(context).Queryable()
.Select(p => familyGroupMembers.Where(s => s.PersonId == p.Id && s.Person.MaritalStatusValueId == marriedDefinedValueId && s.GroupRole.Guid == adultGuid)
.SelectMany(m => m.Group.Members)
.Where(fm => fm.PersonId != p.Id)
.Where(m => m.GroupRole.Guid == adultGuid)
.Where(m => m.Person.Gender != p.Gender)
.Where(m => m.Person.MaritalStatusValueId == marriedDefinedValueId)
.Select(m => m.Person.FirstName).FirstOrDefault());
var selectSpouseExpression = SelectExpressionExtractor.Extract <Rock.Model.Person>(personSpouseQuery, entityIdProperty, "p");
return(selectSpouseExpression);
}
private IQueryable <RelatedPersonInfo> GetRelatedPeopleQuery(RockContext context, IEnumerable <int> groupTypeIds, IEnumerable <Guid> principalRoleGuids, IEnumerable <Guid> targetRoleGuids,
string relationshipName = "*")
{
/*
* 2020-03-19 - JPH
*
* If a given Report includes deceased individuals, the 'Related People' Field Type should list non-deceased
* people who have known relationships with the deceased individual. Note that this is a one-way street: a
* deceased Person should never be displayed within the 'Related People' Field Type itself, but rather: a
* deceased Person should only be displayed as a row within a given Report when they are the subject of that
* Report (the 'Include Deceased' checkbox has been checked for the underlying Data View).
*
* Reason: Issue #4120 (Reporting on Known Relationships for Deceased Individuals)
* https://github.com/SparkDevNetwork/Rock/issues/4120
*/
var relationshipGroupMembersQuery = new GroupMemberService(context).Queryable(true);
relationshipGroupMembersQuery = relationshipGroupMembersQuery.Where(x => groupTypeIds.Contains(x.Group.GroupTypeId));
var relatedPeopleQuery = new PersonService(context).Queryable(new PersonService.PersonQueryOptions {
IncludeDeceased = true
})
.SelectMany(p => relationshipGroupMembersQuery.Where(gm => gm.PersonId == p.Id && principalRoleGuids.Contains(gm.GroupRole.Guid))
.SelectMany(gm => gm.Group.Members)
.Where(gm => targetRoleGuids.Contains(gm.GroupRole.Guid) && gm.PersonId != p.Id)
.Where(gm => gm.Person.IsDeceased == false)
.OrderBy(gm => gm.GroupRole.Order)
.ThenBy(gm => gm.Person.BirthDate)
.ThenBy(gm => gm.Person.Gender)
.Select(
gm =>
new RelatedPersonInfo
{
RelatedToPersonId = p.Id,
PersonId = gm.Person.Id,
FirstName = gm.Person.FirstName,
LastName = gm.Person.LastName,
Suffix = gm.Person.SuffixValue.Value,
RelationshipName =
(relationshipName == "*"
? gm.GroupRole.Name
: relationshipName)
}));
return(relatedPeopleQuery);
}
/// <summary>
/// Loads the list of people that are in the selected group
/// </summary>
private void LoadDropDowns()
{
var personEntityType = EntityTypeCache.Read <Person>();
var currentPerson = RockPage.GetCurrentContext(personEntityType) as Person;
int?personIdParam = Request.QueryString["personId"].AsIntegerOrNull();
// if a personId is in the page params, use that instead of the person context
if (personIdParam.HasValue)
{
if (currentPerson == null || currentPerson.Id != personIdParam.Value)
{
SetPersonContext(personIdParam.Value, false);
}
}
RockContext rockContext = new RockContext();
Group group = null;
var groupGuid = this.GetAttributeValue("Group").AsGuidOrNull();
if (groupGuid.HasValue)
{
group = new GroupService(rockContext).Get(groupGuid.Value);
}
if (group == null)
{
nbSelectGroupWarning.Visible = true;
}
else
{
nbSelectGroupWarning.Visible = false;
var personQry = new GroupMemberService(rockContext).Queryable().Where(a => a.GroupId == group.Id).Select(a => a.Person).Distinct();
if (currentPerson != null)
{
// ensure that the current person is in the selected group
currentPerson = personQry.Where(a => a.Id == currentPerson.Id).FirstOrDefault();
}
lCurrentSelection.Text = currentPerson != null?currentPerson.ToString() : GetAttributeValue("NoPersonText");
var personList = personQry.OrderBy(a => a.LastName).ThenBy(a => a.NickName).ToList().Select(a => new
{
Name = a.FullName,
Id = a.Id
}).ToList();
// check if the person can be unselected
if (!string.IsNullOrEmpty(GetAttributeValue("ClearSelectionText")))
{
personList.Insert(0, new { Name = GetAttributeValue("ClearSelectionText"), Id = 0 });
}
rptPersons.DataSource = personList;
rptPersons.DataBind();
}
}
/// <summary>
/// Loads the list of people that are in the selected group
/// </summary>
private void LoadDropDowns()
{
var personEntityType = EntityTypeCache.Get <Person>();
var currentPerson = RockPage.GetCurrentContext(personEntityType) as Person;
int?personIdParam = Request.QueryString["PersonId"].AsIntegerOrNull();
// if a personId is in the page parameters, use that instead of the person context
if (personIdParam.HasValue)
{
// if there is a query parameter, ensure that the Person Context cookie is set (and has an updated expiration)
// note, the Person Context might already match due to the query parameter, but has a different cookie context, so we still need to ensure the cookie context is updated
SetPersonContext(personIdParam.Value, false);
}
RockContext rockContext = new RockContext();
Group group = null;
var groupGuid = this.GetAttributeValue(AttributeKey.Group).AsGuidOrNull();
if (groupGuid.HasValue)
{
group = new GroupService(rockContext).Get(groupGuid.Value);
}
if (group == null)
{
nbSelectGroupWarning.Visible = true;
}
else
{
nbSelectGroupWarning.Visible = false;
var personQry = new GroupMemberService(rockContext).Queryable().Where(a => a.GroupId == group.Id).Select(a => a.Person).Distinct();
if (currentPerson != null)
{
// ensure that the current person is in the selected group
currentPerson = personQry.Where(a => a.Id == currentPerson.Id).FirstOrDefault();
}
lCurrentSelection.Text = currentPerson != null?currentPerson.ToString() : GetAttributeValue(AttributeKey.NoPersonText);
var personList = personQry.OrderBy(a => a.LastName).ThenBy(a => a.NickName).ToList().Select(a => new
{
Name = a.FullName,
Id = a.Id
}).ToList();
// check if the person can be unselected
if (!string.IsNullOrEmpty(GetAttributeValue(AttributeKey.ClearSelectionText)))
{
personList.Insert(0, new { Name = GetAttributeValue(AttributeKey.ClearSelectionText), Id = 0 });
}
rptPersons.DataSource = personList;
rptPersons.DataBind();
}
}
public void Execute(IJobExecutionContext context)
{
var dataMap = context.JobDetail.JobDataMap;
RockContext rockContext = null;
IQueryable <GroupMember> familyMembers = null;
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(dataMap.GetString("DateRange") ?? "-1||");
int peopleUpdated = 0;
var familyGroupType = GroupTypeCache.Get(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY);
do
{
if (familyMembers != null)
{
foreach (var familyMember in familyMembers.OrderBy(f => f.Id).Take(100).ToList())
{
familyMember.Person.GivingGroupId = familyMember.GroupId;
peopleUpdated += 1;
}
rockContext.SaveChanges();
}
rockContext = new RockContext();
familyMembers = new GroupMemberService(rockContext)
.Queryable("Group,Person")
.Where(g => g.Group.GroupType.Id == familyGroupType.Id &&
(g.Person.GivingGroupId == 0 || g.Person.GivingGroupId == null));
if (dateRange.Start.HasValue)
{
familyMembers = familyMembers.Where(g => g.Person.CreatedDateTime >= dateRange.Start);
}
if (dateRange.End.HasValue)
{
familyMembers = familyMembers.Where(g => g.Person.CreatedDateTime < dateRange.End);
}
} while (familyMembers.Any());
context.Result = string.Format("Combined giving on {0} {1}", peopleUpdated, peopleUpdated == 1 ? "person" : "people");
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
string[] selectionValues = selection.Split('|');
Location selectedLocation = null;
int? locationTypeValueId = null;
if (selectionValues.Count() >= 2)
{
// the selected Location
selectedLocation = new LocationService(context).Get(selectionValues[0].AsGuid());
// which address type (home, work, previous) to use as the person's location
var locationTypeValueGuid = selectionValues[1].AsGuidOrNull();
if (locationTypeValueGuid.HasValue)
{
var locationTypeValue = DefinedValueCache.Get(locationTypeValueGuid.Value);
if (locationTypeValue != null)
{
locationTypeValueId = locationTypeValue.Id;
}
}
}
Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
int familyGroupTypeId = new GroupTypeService(context).Get(familyGuid).Id;
var groupMemberQuery = new GroupMemberService(context).Queryable();
IQueryable <double?> personLocationDistanceQuery;
if (selectedLocation != null)
{
personLocationDistanceQuery = new PersonService(context).Queryable()
.Select(p =>
groupMemberQuery
.Where(m => m.Group.GroupTypeId == familyGroupTypeId && m.PersonId == p.Id)
.SelectMany(m => m.Group.GroupLocations)
.Where(gl => gl.GroupLocationTypeValueId == locationTypeValueId)
.Where(gl => gl.Location.GeoPoint != null)
.Select(s => DbFunctions.Truncate(s.Location.GeoPoint.Distance(selectedLocation.GeoPoint) * Location.MilesPerMeter, 2))
.FirstOrDefault());
}
else
{
personLocationDistanceQuery = new PersonService(context).Queryable()
.Select(p => (double?)null);
}
var selectExpression = SelectExpressionExtractor.Extract(personLocationDistanceQuery, entityIdProperty, "p");
return(selectExpression);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
Guid adultGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
Guid childGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid();
Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
var familyGroupMembers = new GroupMemberService(context).Queryable()
.Where(m => m.Group.GroupType.Guid == familyGuid);
// this returns Enumerable of Person for Parents per row. The Grid then uses ListDelimiterField to convert the list into Parents Names
var personParentsQuery = new PersonService(context).Queryable()
.Select(p => familyGroupMembers.Where(s => s.PersonId == p.Id && s.GroupRole.Guid == childGuid)
.SelectMany(m => m.Group.Members)
.Where(m => m.GroupRole.Guid == adultGuid)
.OrderBy(m => m.Person.Gender)
.Select(m => m.Person).AsEnumerable());
var selectParentsExpression = SelectExpressionExtractor.Extract <Rock.Model.Person>(personParentsQuery, entityIdProperty, "p");
return(selectParentsExpression);
}
/// <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)
{
if (selection.IsNotNullOrWhiteSpace())
{
var ebPersonFieldType = FieldTypeCache.Get(EBGuid.FieldType.EVENTBRITE_PERSON.AsGuid());
var ebPersonAttribute = new AttributeService(( RockContext )serviceInstance.Context).GetByFieldTypeId(ebPersonFieldType.Id).FirstOrDefault();
if (ebPersonAttribute != null)
{
var qry = new GroupMemberService(( RockContext )serviceInstance.Context).Queryable()
.WhereAttributeValue(( RockContext )serviceInstance.Context, a => a.Attribute.Key == ebPersonAttribute.Key && a.Value != null && a.Value != "" && a.Value.Contains("^"));
var groupMemberIds = new List <int>();
foreach (var groupMember in qry.ToList())
{
if (groupMember.Attributes == null)
{
groupMember.LoadAttributes();
}
var attributeVal = groupMember.GetAttributeValue(ebPersonAttribute.Key);
if (attributeVal.IsNotNullOrWhiteSpace())
{
var containsValue = attributeVal.Split(new char[] { '^' })[1].Split(new string[] { "||" }, StringSplitOptions.RemoveEmptyEntries).Contains(selection);
if (containsValue)
{
groupMemberIds.Add(groupMember.Id);
}
}
}
qry = qry.Where(gm => groupMemberIds.Contains(gm.Id));
Expression extractedFilterExpression = FilterExpressionExtractor.Extract <Rock.Model.GroupMember>(qry, parameterExpression, "gm");
return(extractedFilterExpression);
}
}
return(null);
}
/// <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 SelectSettings(selection);
var context = (RockContext)serviceInstance.Context;
//
// Evaluate the Data View that defines the candidate Groups.
//
var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent(settings.DataViewGuid, context);
var groupService = new GroupService(context);
var groupQuery = groupService.Queryable();
if (dataView != null)
{
groupQuery = DataComponentSettingsHelper.FilterByDataView(groupQuery, dataView, groupService);
}
else
{
// Apply a default Group filter to only show Groups that would be visible in a Group List.
groupQuery = groupQuery.Where(x => x.GroupType.ShowInGroupList);
}
var groupKeys = groupQuery.Select(x => x.Id);
//
// Construct the Query to return the list of Group Members matching the filter conditions.
//
var groupMemberQuery = new GroupMemberService(context).Queryable();
// Filter By Group.
groupMemberQuery = groupMemberQuery.Where(gm => groupKeys.Contains(gm.GroupId));
var selectExpression = FilterExpressionExtractor.Extract <Model.GroupMember>(groupMemberQuery, parameterExpression, "gm");
return(selectExpression);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
var settings = new GroupParticipationSelectSettings(selection);
//
// Define Candidate Groups.
//
// Get the Group Data View that defines the set of candidates from which matching Groups can be selected.
var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent(settings.DataViewGuid, context);
// Evaluate the Data View that defines the candidate Groups.
var groupService = new GroupService(context);
var groupQuery = groupService.Queryable();
if (dataView != null)
{
groupQuery = DataComponentSettingsHelper.FilterByDataView(groupQuery, dataView, groupService);
}
else
{
// Apply a default Group filter to only show Groups that would be visible in a Group List.
groupQuery = groupQuery.Where(x => x.GroupType.ShowInGroupList);
}
var groupKeys = groupQuery.Select(x => x.Id);
//
// Construct the Query to return the list of Group Members matching the filter conditions.
//
var groupMemberQuery = new GroupMemberService(context).Queryable();
// Filter By Group.
groupMemberQuery = groupMemberQuery.Where(x => groupKeys.Contains(x.GroupId));
// Filter By Group Role Type.
switch (settings.RoleType)
{
case RoleTypeSpecifier.Member:
groupMemberQuery = groupMemberQuery.Where(x => !x.GroupRole.IsLeader);
break;
case RoleTypeSpecifier.Leader:
groupMemberQuery = groupMemberQuery.Where(x => x.GroupRole.IsLeader);
break;
}
// Filter by Group Member Status.
if (settings.MemberStatus.HasValue)
{
groupMemberQuery = groupMemberQuery.Where(x => x.GroupMemberStatus == settings.MemberStatus.Value);
}
//
// Create a Select Expression to return the requested values.
//
// Set the Output Format of the field.
Expression selectExpression;
if (settings.ListFormat == ListFormatSpecifier.YesNo)
{
// Define a Query to return True/False text indicating if the Person participates in any of the filtered Groups.
// Note that the text must be returned as an Enumerable to satisfy the expected output of this field.
var personGroupsQuery = new PersonService(context).Queryable()
.Select(p => new List <string> {
groupMemberQuery.Any(s => s.PersonId == p.Id) ? "Yes" : "No"
});
selectExpression = SelectExpressionExtractor.Extract(personGroupsQuery, entityIdProperty, "p");
}
else
{
// Define a Query to return the collection of filtered Groups for each Person.
Expression <Func <Rock.Model.GroupMember, string> > outputExpression;
if (settings.ListFormat == ListFormatSpecifier.GroupOnly)
{
outputExpression = ((m => m.Group.Name));
}
else
{
outputExpression = ((m => m.Group.Name + " [" + m.GroupRole.Name + "]"));
}
// Define a Query to return the collection of filtered Groups for each Person.
var personGroupsQuery = new PersonService(context).Queryable()
.Select(p => groupMemberQuery.Where(s => s.PersonId == p.Id)
.OrderBy(x => x.Group.Name)
.ThenBy(x => x.GroupRole.Name)
.Select(outputExpression).AsEnumerable());
selectExpression = SelectExpressionExtractor.Extract(personGroupsQuery, entityIdProperty, "p");
}
return(selectExpression);
}
/// <summary>
/// Executes the specified context.
/// </summary>
/// <param name="context">The context.</param>
public void Execute(IJobExecutionContext context)
{
var errors = new List <string>();
var rockContext = new RockContext();
JobDataMap dataMap = context.JobDetail.JobDataMap;
Guid? systemEmailGuid = dataMap.GetString("NotificationEmailTemplate").AsGuidOrNull();
if (systemEmailGuid.HasValue)
{
var selectedGroupTypes = new List <Guid>();
if (!string.IsNullOrWhiteSpace(dataMap.GetString("GroupTypes")))
{
selectedGroupTypes = dataMap.GetString("GroupTypes").Split(',').Select(Guid.Parse).ToList();
}
var notificationOption = dataMap.GetString("NotifyParentLeaders").ConvertToEnum <NotificationOption>(NotificationOption.None);
var accountAbilityGroupGuid = dataMap.GetString("AccountabilityGroup").AsGuid();
var groupRequirementsQry = new GroupRequirementService(rockContext).Queryable();
// get groups matching of the types provided
GroupService groupService = new GroupService(rockContext);
var groups = groupService.Queryable().AsNoTracking()
.Where(g => selectedGroupTypes.Contains(g.GroupType.Guid) &&
g.IsActive == true &&
groupRequirementsQry.Any(a => (a.GroupId.HasValue && a.GroupId == g.Id) || (a.GroupTypeId.HasValue && a.GroupTypeId == g.GroupTypeId)));
foreach (var group in groups)
{
// check for members that don't meet requirements
var groupMembersWithIssues = groupService.GroupMembersNotMeetingRequirements(group, true);
if (groupMembersWithIssues.Count > 0)
{
// add issues to issue list
GroupsMissingRequirements groupMissingRequirements = new GroupsMissingRequirements();
groupMissingRequirements.Id = group.Id;
groupMissingRequirements.Name = group.Name;
if (group.GroupType != null)
{
groupMissingRequirements.GroupTypeId = group.GroupTypeId;
groupMissingRequirements.GroupTypeName = group.GroupType.Name;
}
groupMissingRequirements.AncestorPathName = groupService.GroupAncestorPathName(group.Id);
// get list of the group leaders
groupMissingRequirements.Leaders = group.Members
.Where(m => m.GroupRole.ReceiveRequirementsNotifications)
.Select(m => new GroupMemberResult
{
Id = m.Id,
PersonId = m.PersonId,
FullName = m.Person.FullName
})
.ToList();
List <GroupMembersMissingRequirements> groupMembers = new List <GroupMembersMissingRequirements>();
foreach (var groupMemberIssue in groupMembersWithIssues)
{
GroupMembersMissingRequirements groupMember = new GroupMembersMissingRequirements();
groupMember.FullName = groupMemberIssue.Key.Person.FullName;
groupMember.Id = groupMemberIssue.Key.Id;
groupMember.PersonId = groupMemberIssue.Key.PersonId;
groupMember.GroupMemberRole = groupMemberIssue.Key.GroupRole.Name;
List <MissingRequirement> missingRequirements = new List <MissingRequirement>();
foreach (var issue in groupMemberIssue.Value)
{
MissingRequirement missingRequirement = new MissingRequirement();
missingRequirement.Id = issue.Key.GroupRequirement.GroupRequirementType.Id;
missingRequirement.Name = issue.Key.GroupRequirement.GroupRequirementType.Name;
missingRequirement.Status = issue.Key.MeetsGroupRequirement;
missingRequirement.OccurrenceDate = issue.Value;
switch (issue.Key.MeetsGroupRequirement)
{
case MeetsGroupRequirement.Meets:
missingRequirement.Message = issue.Key.GroupRequirement.GroupRequirementType.PositiveLabel;
break;
case MeetsGroupRequirement.MeetsWithWarning:
missingRequirement.Message = issue.Key.GroupRequirement.GroupRequirementType.WarningLabel;
break;
case MeetsGroupRequirement.NotMet:
missingRequirement.Message = issue.Key.GroupRequirement.GroupRequirementType.NegativeLabel;
break;
}
missingRequirements.Add(missingRequirement);
}
groupMember.MissingRequirements = missingRequirements;
groupMembers.Add(groupMember);
}
groupMissingRequirements.GroupMembersMissingRequirements = groupMembers;
_groupsMissingRequriements.Add(groupMissingRequirements);
// add leaders as people to notify
foreach (var leader in group.Members.Where(m => m.GroupRole.ReceiveRequirementsNotifications))
{
NotificationItem notification = new NotificationItem();
notification.GroupId = group.Id;
notification.Person = leader.Person;
_notificationList.Add(notification);
}
// notify parents
if (notificationOption != NotificationOption.None)
{
var parentLeaders = new GroupMemberService(rockContext).Queryable("Person").AsNoTracking()
.Where(m => m.GroupRole.ReceiveRequirementsNotifications);
if (notificationOption == NotificationOption.DirectParent)
{
// just the parent group
parentLeaders = parentLeaders.Where(m => m.GroupId == group.ParentGroupId);
}
else
{
// all parents in the hierarchy
var parentIds = groupService.GetAllAncestorIds(group.Id);
parentLeaders = parentLeaders.Where(m => parentIds.Contains(m.GroupId));
}
foreach (var parentLeader in parentLeaders.ToList())
{
NotificationItem parentNotification = new NotificationItem();
parentNotification.Person = parentLeader.Person;
parentNotification.GroupId = group.Id;
_notificationList.Add(parentNotification);
}
}
}
}
// send out notifications
int recipients = 0;
var notificationRecipients = _notificationList.GroupBy(p => p.Person.Id).ToList();
foreach (var recipientId in notificationRecipients)
{
var recipient = _notificationList.Where(n => n.Person.Id == recipientId.Key).Select(n => n.Person).FirstOrDefault();
if (!recipient.IsEmailActive || recipient.Email.IsNullOrWhiteSpace() || recipient.EmailPreference == EmailPreference.DoNotEmail)
{
continue;
}
var mergeFields = Rock.Lava.LavaHelper.GetCommonMergeFields(null);
mergeFields.Add("Person", recipient);
var notificationGroupIds = _notificationList
.Where(n => n.Person.Id == recipient.Id)
.Select(n => n.GroupId)
.ToList();
var missingRequirements = _groupsMissingRequriements.Where(g => notificationGroupIds.Contains(g.Id)).ToList();
mergeFields.Add("GroupsMissingRequirements", missingRequirements);
var emailMessage = new RockEmailMessage(systemEmailGuid.Value);
emailMessage.AddRecipient(new RecipientData(recipient.Email, mergeFields));
var emailErrors = new List <string>();
emailMessage.Send(out emailErrors);
errors.AddRange(emailErrors);
recipients++;
}
// add accountability group members
if (!accountAbilityGroupGuid.IsEmpty())
{
var accountabilityGroupMembers = new GroupMemberService(rockContext).Queryable().AsNoTracking()
.Where(m => m.Group.Guid == accountAbilityGroupGuid)
.Select(m => m.Person);
var emailMessage = new RockEmailMessage(systemEmailGuid.Value);
foreach (var person in accountabilityGroupMembers)
{
var mergeFields = Rock.Lava.LavaHelper.GetCommonMergeFields(null);
mergeFields.Add("Person", person);
mergeFields.Add("GroupsMissingRequirements", _groupsMissingRequriements);
emailMessage.AddRecipient(new RecipientData(person.Email, mergeFields));
recipients++;
}
var emailErrors = new List <string>();
emailMessage.Send(out emailErrors);
errors.AddRange(emailErrors);
}
context.Result = string.Format("{0} requirement notification {1} sent", recipients, "email".PluralizeIf(recipients != 1));
if (errors.Any())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine();
sb.Append(string.Format("{0} Errors: ", errors.Count()));
errors.ForEach(e => { sb.AppendLine(); sb.Append(e); });
string errorMessage = sb.ToString();
context.Result += errorMessage;
var exception = new Exception(errorMessage);
HttpContext context2 = HttpContext.Current;
ExceptionLogService.LogException(exception, context2);
throw exception;
}
}
else
{
context.Result = "Warning: No NotificationEmailTemplate found";
}
}
/// <summary>
/// Maps the family address.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapFamilyAddress(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var locationService = new LocationService(lookupContext);
var familyGroupMemberList = new GroupMemberService(lookupContext).Queryable().AsNoTracking()
.Where(gm => gm.Group.GroupType.Guid.Equals(new Guid(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY))).ToList();
var customLocationTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.GROUP_LOCATION_TYPE), lookupContext).DefinedValues;
const string otherGroupLocationName = "Other (Imported)";
var otherGroupLocationTypeId = customLocationTypes.Where(dv => dv.TypeName == otherGroupLocationName)
.Select(v => (int?)v.Id).FirstOrDefault();
if (!otherGroupLocationTypeId.HasValue)
{
var otherGroupLocationType = AddDefinedValue(lookupContext, Rock.SystemGuid.DefinedType.GROUP_LOCATION_TYPE, otherGroupLocationName);
customLocationTypes.Add(otherGroupLocationType);
otherGroupLocationTypeId = otherGroupLocationType.Id;
}
var newGroupLocations = new List <GroupLocation>();
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completed = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, $"Verifying address import ({totalRows:N0} found).");
foreach (var row in tableData.Where(r => r != null))
{
var individualId = row["Individual_ID"] as int?;
var householdId = row["Household_ID"] as int?;
var personKeys = GetPersonKeys(individualId, householdId, includeVisitors: false);
if (personKeys != null)
{
var familyGroup = familyGroupMemberList.Where(gm => gm.PersonId == personKeys.PersonId)
.Select(gm => gm.Group).FirstOrDefault();
if (familyGroup != null)
{
var groupLocation = new GroupLocation();
var street1 = row["Address_1"] as string;
var street2 = row["Address_2"] as string;
var city = row["City"] as string;
var state = row["State"] as string;
var country = row["country"] as string; // NOT A TYPO: F1 has property in lower-case
var zip = row["Postal_Code"] as string ?? string.Empty;
// restrict zip to 5 places to prevent duplicates
var familyAddress = locationService.Get(street1, street2, city, state, zip.Left(5), country, verifyLocation: false);
if (familyAddress != null)
{
familyAddress.CreatedByPersonAliasId = ImportPersonAliasId;
familyAddress.Name = familyGroup.Name;
familyAddress.IsActive = true;
groupLocation.GroupId = familyGroup.Id;
groupLocation.LocationId = familyAddress.Id;
groupLocation.IsMailingLocation = false;
groupLocation.IsMappedLocation = false;
var addressType = row["Address_Type"].ToString();
if (addressType.Equals("Primary", StringComparison.CurrentCultureIgnoreCase))
{
groupLocation.GroupLocationTypeValueId = HomeLocationTypeId;
groupLocation.IsMailingLocation = true;
groupLocation.IsMappedLocation = true;
}
else if (addressType.Equals("Business", StringComparison.CurrentCultureIgnoreCase) || addressType.StartsWith("Org", StringComparison.CurrentCultureIgnoreCase))
{
groupLocation.GroupLocationTypeValueId = WorkLocationTypeId;
}
else if (addressType.Equals("Previous", StringComparison.CurrentCultureIgnoreCase))
{
groupLocation.GroupLocationTypeValueId = PreviousLocationTypeId;
}
else if (!string.IsNullOrWhiteSpace(addressType))
{
// look for existing group location types, otherwise mark as imported
var customTypeId = customLocationTypes.Where(dv => dv.Value.Equals(addressType, StringComparison.CurrentCultureIgnoreCase))
.Select(dv => (int?)dv.Id).FirstOrDefault();
groupLocation.GroupLocationTypeValueId = customTypeId ?? otherGroupLocationTypeId;
}
newGroupLocations.Add(groupLocation);
completed++;
if (completed % percentage < 1)
{
var percentComplete = completed / percentage;
ReportProgress(percentComplete, $"{completed:N0} addresses imported ({percentComplete}% complete).");
}
else if (completed % ReportingNumber < 1)
{
SaveFamilyAddress(newGroupLocations);
// Reset context
newGroupLocations.Clear();
lookupContext = new RockContext();
locationService = new LocationService(lookupContext);
ReportPartialProgress();
}
}
}
}
}
if (newGroupLocations.Any())
{
SaveFamilyAddress(newGroupLocations);
}
ReportProgress(100, $"Finished address import: {completed:N0} addresses imported.");
}
/// <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)
{
// Get the job setting(s)
JobDataMap dataMap = context.JobDetail.JobDataMap;
bool requirePasswordReset = dataMap.GetBoolean("RequirePasswordReset");
// Counters for displaying results
int groupsSynced = 0;
int groupsChanged = 0;
string groupName = string.Empty;
string dataViewName = string.Empty;
var errors = new List <string>();
try
{
// get groups set to sync
var activeSyncIds = new List <int>();
using (var rockContext = new RockContext())
{
activeSyncIds = new GroupSyncService(rockContext)
.Queryable().AsNoTracking()
.Select(x => x.Id)
.ToList();
}
foreach (var syncId in activeSyncIds)
{
bool hasSyncChanged = false;
// Use a fresh rockContext per syc so that ChangeTracker doesn't get bogged down
using (var rockContext = new RockContext())
{
// increase the timeout just in case the dataview source is slow
rockContext.Database.CommandTimeout = 180;
rockContext.SourceOfChange = "Group Sync";
// Get the Sync
var sync = new GroupSyncService(rockContext)
.Queryable().AsNoTracking()
.FirstOrDefault(s => s.Id == syncId);
// Ensure that the group's Sync Data View is a person dataview
if (sync != null && sync.SyncDataView.EntityTypeId == EntityTypeCache.Get(typeof(Person)).Id)
{
List <string> syncErrors = new List <string>();
dataViewName = sync.SyncDataView.Name;
groupName = sync.Group.Name;
// Get the person id's from the dataview (source)
var personService = new PersonService(rockContext);
var parameterExpression = personService.ParameterExpression;
var whereExpression = sync.SyncDataView.GetExpression(personService, parameterExpression, out syncErrors);
var sourcePersonIds = new PersonService(rockContext)
.Get(parameterExpression, whereExpression)
.Select(q => q.Id)
.ToList();
// If any error occurred, just skip this sync for now.
if (syncErrors.Count > 0)
{
errors.AddRange(syncErrors);
ExceptionLogService.LogException(new Exception(string.Format("An error occurred while trying to GroupSync group '{0}' and data view '{1}' so the sync was skipped. Error: {2}", groupName, dataViewName, String.Join(",", syncErrors))));
continue;
}
// Get the person id's in the group (target)
var targetPersonIds = new GroupMemberService(rockContext)
.Queryable().AsNoTracking()
.Where(gm => gm.GroupId == sync.GroupId)
.Select(gm => gm.PersonId)
.ToList();
// Delete people from the group/role that are no longer in the dataview
foreach (var personId in targetPersonIds.Where(t => !sourcePersonIds.Contains(t)))
{
// Use a new context to limit the amount of change-tracking required
using (var groupMemberContext = new RockContext())
{
// Delete any group members for the role with the person id
var groupMemberService = new GroupMemberService(groupMemberContext);
foreach (var groupMember in groupMemberService
.Queryable()
.Where(m =>
m.GroupId == sync.GroupId &&
m.GroupRoleId == sync.GroupTypeRoleId &&
m.PersonId == personId)
.ToList())
{
groupMemberService.Delete(groupMember);
}
groupMemberContext.SaveChanges();
// If the Group has an exit email, and person has an email address, send them the exit email
if (sync.ExitSystemEmail != null)
{
var person = new PersonService(groupMemberContext).Get(personId);
if (person.Email.IsNotNullOrWhiteSpace())
{
// Send the exit email
var mergeFields = new Dictionary <string, object>();
mergeFields.Add("Group", sync.Group);
mergeFields.Add("Person", person);
var emailMessage = new RockEmailMessage(sync.ExitSystemEmail);
emailMessage.AddRecipient(new RecipientData(person.Email, mergeFields));
var emailErrors = new List <string>();
emailMessage.Send(out emailErrors);
errors.AddRange(emailErrors);
}
}
}
hasSyncChanged = true;
}
foreach (var personId in sourcePersonIds.Where(s => !targetPersonIds.Contains(s)))
{
// Use a new context to limit the amount of change-tracking required
using (var groupMemberContext = new RockContext())
{
// Add new person to the group with the role specified in the sync
var groupMemberService = new GroupMemberService(groupMemberContext);
var newGroupMember = new GroupMember {
Id = 0
};
newGroupMember.PersonId = personId;
newGroupMember.GroupId = sync.GroupId;
newGroupMember.GroupMemberStatus = GroupMemberStatus.Active;
newGroupMember.GroupRoleId = sync.GroupTypeRoleId;
groupMemberService.Add(newGroupMember);
groupMemberContext.SaveChanges();
// If the Group has a welcome email, and person has an email address, send them the welcome email and possibly create a login
if (sync.WelcomeSystemEmail != null)
{
var person = new PersonService(groupMemberContext).Get(personId);
if (person.Email.IsNotNullOrWhiteSpace())
{
// If the group is configured to add a user account for anyone added to the group, and person does not yet have an
// account, add one for them.
string newPassword = string.Empty;
bool createLogin = sync.AddUserAccountsDuringSync;
// Only create a login if requested, no logins exist and we have enough information to generate a username.
if (createLogin && !person.Users.Any() && !string.IsNullOrWhiteSpace(person.NickName) && !string.IsNullOrWhiteSpace(person.LastName))
{
newPassword = System.Web.Security.Membership.GeneratePassword(9, 1);
string username = Rock.Security.Authentication.Database.GenerateUsername(person.NickName, person.LastName);
UserLogin login = UserLoginService.Create(
groupMemberContext,
person,
AuthenticationServiceType.Internal,
EntityTypeCache.Get(Rock.SystemGuid.EntityType.AUTHENTICATION_DATABASE.AsGuid()).Id,
username,
newPassword,
true,
requirePasswordReset);
}
// Send the welcome email
var mergeFields = new Dictionary <string, object>();
mergeFields.Add("Group", sync.Group);
mergeFields.Add("Person", person);
mergeFields.Add("NewPassword", newPassword);
mergeFields.Add("CreateLogin", createLogin);
var emailMessage = new RockEmailMessage(sync.WelcomeSystemEmail);
emailMessage.AddRecipient(new RecipientData(person.Email, mergeFields));
var emailErrors = new List <string>();
emailMessage.Send(out emailErrors);
errors.AddRange(emailErrors);
}
}
}
hasSyncChanged = true;
}
// Increment Groups Changed Counter (if people were deleted or added to the group)
if (hasSyncChanged)
{
groupsChanged++;
}
// Increment the Groups Synced Counter
groupsSynced++;
}
}
}
// Format the result message
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 were changed", groupsChanged);
if (errors.Any())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine();
sb.Append("Errors: ");
errors.ForEach(e => { sb.AppendLine(); sb.Append(e); });
string errorMessage = sb.ToString();
resultMessage += errorMessage;
throw new Exception(errorMessage);
}
context.Result = resultMessage;
}
catch (System.Exception ex)
{
HttpContext context2 = HttpContext.Current;
ExceptionLogService.LogException(ex, context2);
throw;
}
}
/// <summary>
/// Updates the note watch notification digest.
/// </summary>
/// <param name="personIdNotificationDigestList">The person identifier notification digest list.</param>
/// <param name="rockContext">The rock context.</param>
/// <param name="noteId">The note identifier.</param>
private void UpdateNoteWatchNotificationDigest(Dictionary <int, NoteWatchPersonToNotifyList> personIdNotificationDigestList, RockContext rockContext, int noteId)
{
var noteService = new Rock.Model.NoteService(rockContext);
var note = noteService.Queryable().Include(a => a.EditedByPersonAlias).FirstOrDefault(a => a.Id == noteId);
if (note == null || !note.EntityId.HasValue)
{
// shouldn't' happen
return;
}
var noteType = NoteTypeCache.Get(note.NoteTypeId);
// make sure the note's notetype has an EntityTypeId (is should, but just in case it doesn't)
int?noteEntityTypeId = noteType?.EntityTypeId;
if (!noteEntityTypeId.HasValue)
{
return;
}
var noteWatchService = new Rock.Model.NoteWatchService(rockContext);
// narrow it down to NoteWatches for the same EntityType as the Note
var noteWatchesQuery = noteWatchService.Queryable()
.Where(a =>
(a.EntityTypeId.HasValue && a.EntityTypeId.Value == noteEntityTypeId.Value) ||
(a.NoteTypeId.HasValue && a.NoteType.EntityTypeId == noteEntityTypeId));
// narrow it down to either note watches on..
// 1) specific Entity
// 2) specific Note
// 3) any note of the NoteType
// 4) any note on the EntityType
// specific Entity
noteWatchesQuery = noteWatchesQuery.Where(a =>
(a.EntityId == null) ||
(note.EntityId.HasValue && a.EntityId.Value == note.EntityId.Value));
// or specifically for this Note's ParentNote (a reply to the Note)
noteWatchesQuery = noteWatchesQuery.Where(a =>
(a.NoteId == null) ||
(note.ParentNoteId.HasValue && a.NoteId.Value == note.ParentNoteId));
// or specifically for this note's note type
noteWatchesQuery = noteWatchesQuery.Where(a =>
(a.NoteTypeId == null) ||
(a.NoteTypeId.Value == note.NoteTypeId));
// if there are any NoteWatches that relate to this note, process them
if (noteWatchesQuery.Any())
{
var noteWatchesForNote = noteWatchesQuery.Include(a => a.WatcherPersonAlias.Person).AsNoTracking().ToList();
List <NoteWatchPersonToNotify> noteWatchPersonToNotifyListAll = new List <NoteWatchPersonToNotify>();
// loop thru Watches to get a list of people to possibly notify/override
foreach (var noteWatch in noteWatchesForNote)
{
// if a specific person is the watcher, add them
var watcherPerson = noteWatch.WatcherPersonAlias?.Person;
if (watcherPerson != null)
{ // Since this is iterated do not add the person to the list if they are already there.
var exists = noteWatchPersonToNotifyListAll.Where(p => p.Person.Email.Contains(watcherPerson.Email)).Any();
if (!exists)
{
noteWatchPersonToNotifyListAll.Add(new NoteWatchPersonToNotify(watcherPerson, note, noteWatch));
}
}
if (noteWatch.WatcherGroupId.HasValue)
{
var watcherPersonsFromGroup = new GroupMemberService(rockContext).Queryable()
.Where(a => a.GroupMemberStatus == GroupMemberStatus.Active && a.GroupId == noteWatch.WatcherGroupId.Value)
.Select(a => a.Person).ToList();
if (watcherPersonsFromGroup.Any())
{
// Do not add people from the group that are already added.
var distinctWatchers = watcherPersonsFromGroup.Where(wg => !noteWatchPersonToNotifyListAll.Where(w => w.Person.Email.Contains(wg.Email)).Any());
noteWatchPersonToNotifyListAll.AddRange(distinctWatchers.Select(a => new NoteWatchPersonToNotify(a, note, noteWatch)));
}
}
}
var noteWatchPersonToNotifyList = noteWatchPersonToNotifyListAll.Where(a => a.NoteWatch.IsWatching).ToList();
var noteWatchPersonToNotifyListWatchDisabled = noteWatchPersonToNotifyListAll.Where(a => !a.NoteWatch.IsWatching).ToList();
var noteWatchPersonToNotifyListNoOverride = noteWatchPersonToNotifyList.Where(a => a.NoteWatch.AllowOverride == false).ToList();
foreach (var noteWatchPersonToNotify in noteWatchPersonToNotifyList)
{
// check if somebody wanted to specifically NOT watch this (and there isn't another watch that prevents overrides)
if (noteWatchPersonToNotifyListWatchDisabled.Any(a => a.Person.Id == noteWatchPersonToNotify.Person.Id))
{
// Person Requested to NOT watch, now make sure that they aren't force to watch based on Override = False
if (!noteWatchPersonToNotifyListNoOverride.Any(a => a.Person.Id == noteWatchPersonToNotify.Person.Id))
{
// person requested to NOT watch, and there aren't any overrides to prevent that, so jump out to the next one
continue;
}
}
NoteWatchPersonToNotifyList personToNotifyList;
if (personIdNotificationDigestList.ContainsKey(noteWatchPersonToNotify.Person.Id))
{
// This just create a place holder for the Note referring to item in the digest by key of person
personToNotifyList = personIdNotificationDigestList[noteWatchPersonToNotify.Person.Id] ?? new NoteWatchPersonToNotifyList(noteWatchPersonToNotify.Person);
}
else
{
// This just creates a new place holder for the Note in the digest
personToNotifyList = new NoteWatchPersonToNotifyList(noteWatchPersonToNotify.Person);
personIdNotificationDigestList.Add(noteWatchPersonToNotify.Person.Id, personToNotifyList);
}
// Only include the note if the watcher person is authorized to view the note
if (noteWatchPersonToNotify.Note.IsAuthorized(Rock.Security.Authorization.VIEW, noteWatchPersonToNotify.Person))
{
// This is where the get added to the item of the digest
personToNotifyList.Add(noteWatchPersonToNotify);
}
}
}
}
protected void DoSQL()
{
int SelectedCampusId = cpCampus.SelectedValue.AsInteger();
CampusCache SelectedCampus = CampusCache.Read(SelectedCampusId);
string SelectedCampusName = SelectedCampus != null ? SelectedCampus.Name : "";
var sunday = DateTime.Today.SundayDate().AddDays(-6);
var lastSunday = sunday.AddDays(-7);
SundayDate = sunday.ToShortDateString();
MetricValueService mvServ = new MetricValueService(rockContext);
var metricValues = SelectedCampusId == 0 ? mvServ.Queryable() : mvServ.Queryable().Where(mv => mv.MetricValuePartitions.Where(mvp => mvp.MetricPartition.EntityTypeId == 67).Select(mvp => mvp.EntityId).Contains(SelectedCampusId));
var metric_Goal = metricValues.Where(mv => mv.MetricValueType == MetricValueType.Goal);
var metric_Measure = metricValues.Where(mv => mv.MetricValueType == MetricValueType.Measure);
var metric_LastWeek = metric_Measure.Where(mv => mv.MetricValueDateTime > lastSunday && mv.MetricValueDateTime <= sunday);
//Attendance Last Week - All Environments
int iAttendanceGoalCurrent = decimal.ToInt32(metric_Goal.Where(mv => new int[] { 2, 3, 4, 5 }.Contains(mv.MetricId) && mv.MetricValueDateTime.Value.Year == sunday.Year).Sum(mv => mv.YValue) ?? 0);
int iAttendanceLastWeekAll = decimal.ToInt32(metric_LastWeek.Where(mv => new int[] { 2, 3, 4, 5 }.Contains(mv.MetricId)).Sum(mv => mv.YValue) ?? 0);
int iAttendancePercent = iAttendanceGoalCurrent != 0 ? ( int )((( double )iAttendanceLastWeekAll / iAttendanceGoalCurrent) * 100) : 0;
AttendanceLastWeekAll = iAttendanceLastWeekAll.ToString();
AttendancePercentage = iAttendancePercent.ToString();
AttendanceColor = GetColorForPercent(iAttendancePercent);
//Attendance Last Week - Auditorium
AttendanceLastWeekAud = decimal.ToInt32(metric_LastWeek.Where(mv => new int[] { 2 }.Contains(mv.MetricId)).Sum(mv => mv.YValue) ?? 0).ToString();
//Attendance Last Week - Rainforest + Velocity
AttendanceLastWeekChild = decimal.ToInt32(metric_LastWeek.Where(mv => new int[] { 3, 4 }.Contains(mv.MetricId)).Sum(mv => mv.YValue) ?? 0).ToString();
//Attendance Last Week - The Collective
AttendanceLastWeekStudent = decimal.ToInt32(metric_LastWeek.Where(mv => new int[] { 5 }.Contains(mv.MetricId)).Sum(mv => mv.YValue) ?? 0).ToString();
//Comitments
var commitStartDate = DateTime.Parse("2015-09-01");
//First Time Commitments
int iCommitments = decimal.ToInt32(metric_Measure.Where(mv => mv.MetricId == 12 && mv.MetricValueDateTime >= commitStartDate).Sum(mv => mv.YValue) ?? 0);
Commitments = iCommitments.ToString();
//Re-commitments
int iRecommitments = decimal.ToInt32(metric_Measure.Where(mv => mv.MetricId == 13 && mv.MetricValueDateTime >= commitStartDate).Sum(mv => mv.YValue) ?? 0);
Recommitments = iRecommitments.ToString();
//TotalCommitments
int iTotalCommitments = decimal.ToInt32(metric_Measure.Where(mv => new int[] { 12, 13 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= commitStartDate).Sum(mv => mv.YValue) ?? 0);
int iTotalCommitmentGoal = decimal.ToInt32(metric_Goal.Where(mv => new int[] { 12, 13 }.Contains(mv.MetricId) && mv.MetricValueDateTime.Value.Year == sunday.Year).Sum(mv => mv.YValue) ?? 0);
int iTotalCommitmentPercent = iTotalCommitmentGoal != 0 ? ( int )((( double )iTotalCommitments / iTotalCommitmentGoal) * 100) : 0;
AllCommitments = iTotalCommitments.ToString();
TotalCommitmentPercentage = iTotalCommitmentPercent.ToString();
TotalCommitmentColor = GetColorForPercent(iTotalCommitmentPercent);
//Involvement
var groupMembers = new GroupMemberService(rockContext).Queryable().Where(gm => gm.Group.IsActive && gm.GroupMemberStatus == GroupMemberStatus.Active);
if (SelectedCampusId != 0)
{
groupMembers = groupMembers.Where(gm => gm.Group.CampusId == SelectedCampusId);
}
//Small Group Leaders
SmallGroupLeaders = groupMembers.Where(gm => gm.Group.GroupTypeId == 25 && gm.GroupRoleId == 24).DistinctBy(gm => gm.PersonId).Count().ToString();
//Volunteers
Volunteers = groupMembers.Where(gm => gm.Group.GroupTypeId == 42).DistinctBy(gm => gm.PersonId).Count().ToString();
//Total Involvement
Involvement = groupMembers.Where(gm => (gm.Group.GroupTypeId == 25 && gm.GroupRoleId == 24) || gm.Group.GroupTypeId == 42).DistinctBy(gm => gm.PersonId).Count().ToString();
WorkflowService workServ = new WorkflowService(rockContext);
var today = DateTime.Now;
int iInactiveFollowupComplete;
int iInactiveFollowupIncomplete;
//Inactive Follow-up
if (SelectedCampusId == 0)
{
iInactiveFollowupComplete = workServ.Queryable().Where(w => w.WorkflowTypeId == 120 && w.Status == "Completed" && w.ActivatedDateTime.Value.Year == today.Year && w.ActivatedDateTime.Value.Month == today.Month).Count();
}
else
{
iInactiveFollowupComplete = workServ.Queryable().Where(w => w.WorkflowTypeId == 120 && w.Status == "Completed" && w.ActivatedDateTime.Value.Year == today.Year && w.ActivatedDateTime.Value.Month == today.Month).WhereAttributeValue(rockContext, "Campus", SelectedCampusName).Count();
}
if (SelectedCampusId == 0)
{
iInactiveFollowupIncomplete = workServ.Queryable().Where(w => w.WorkflowTypeId == 120 && w.Status != "Completed" && w.ActivatedDateTime.Value.Year == today.Year && w.ActivatedDateTime.Value.Month == today.Month).Count();
}
else
{
iInactiveFollowupIncomplete = workServ.Queryable().Where(w => w.WorkflowTypeId == 120 && w.Status != "Completed" && w.ActivatedDateTime.Value.Year == today.Year && w.ActivatedDateTime.Value.Month == today.Month).WhereAttributeValue(rockContext, "Campus", SelectedCampusName).Count();
}
int iInactiveFollowup = iInactiveFollowupComplete + iInactiveFollowupIncomplete;
int iInactiveFollowupPercentage = iInactiveFollowup != 0 ? ( int )((( double )iInactiveFollowupComplete / iInactiveFollowup) * 100) : 0;
InactiveFollowupComplete = iInactiveFollowupComplete.ToString();
InactiveFollowupIncomplete = iInactiveFollowupIncomplete.ToString();
InactiveFollowup = iInactiveFollowup.ToString();
InactiveFollowupPercentage = iInactiveFollowupPercentage.ToString();
InactiveFollowupColor = GetColorForPercent(iInactiveFollowupPercentage);
}
/// <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 SelectSettings(selection);
var context = ( RockContext )serviceInstance.Context;
//
// Evaluate the Data View that defines the candidate Groups.
//
var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent(settings.DataViewGuid, context);
var groupService = new GroupService(context);
var groupQuery = groupService.Queryable();
if (dataView != null)
{
groupQuery = DataComponentSettingsHelper.FilterByDataView(groupQuery, dataView, groupService);
}
else
{
// Apply a default Group filter to only show Groups that would be visible in a Group List.
groupQuery = groupQuery.Where(x => x.GroupType.ShowInGroupList);
}
var groupKeys = groupQuery.Select(x => x.Id);
//
// Construct the Query to return the list of Group Members matching the filter conditions.
//
var groupMemberQuery = new GroupMemberService(context).Queryable();
// Filter By Group.
groupMemberQuery = groupMemberQuery.Where(x => groupKeys.Contains(x.GroupId));
// Filter By Group Role Type.
switch (settings.RoleType)
{
case RoleTypeSpecifier.Member:
groupMemberQuery = groupMemberQuery.Where(x => !x.GroupRole.IsLeader);
break;
case RoleTypeSpecifier.Leader:
groupMemberQuery = groupMemberQuery.Where(x => x.GroupRole.IsLeader);
break;
}
// Filter by Group Member Status.
if (settings.MemberStatus.HasValue)
{
groupMemberQuery = groupMemberQuery.Where(x => x.GroupMemberStatus == settings.MemberStatus.Value);
}
//
// Create a Select Expression to return the Person records referenced by the Group Members.
//
var personGroupsQuery = new PersonService(context).Queryable()
.Where(p => groupMemberQuery.Select(gm => gm.PersonId).Contains(p.Id));
var selectExpression = FilterExpressionExtractor.Extract <Model.Person>(personGroupsQuery, parameterExpression, "p");
return(selectExpression);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
var settings = new RelatedPeopleSelectSettings(selection);
bool showRelationship = (settings.ListFormat == ListFormatSpecifier.NameAndRelationship);
// Get Support Data.
var adultGuid = GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT.AsGuid();
var childGuid = GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid();
int familyGroupTypeId = GroupTypeCache.Read(SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid()).Id;
int knownRelationshipGroupTypeId = GroupTypeCache.Read(SystemGuid.GroupType.GROUPTYPE_KNOWN_RELATIONSHIPS.AsGuid()).Id;
//
// Construct a Query to return the list of Related People matching the filter conditions.
//
IQueryable <RelatedPersonInfo> allRelatedPeopleQuery = null;
// If we are looking for Parents...
// Add the Adults from the Family Group in which the Principal participates as a Child.
if (settings.FamilyRelationshipTypeGuids.Contains(FamilyRelationshipParentGuid.AsGuid()))
{
var familyMembersQuery = GetRelatedPeopleQuery(context, new List <int> {
familyGroupTypeId
}, new List <Guid> {
childGuid
}, new List <Guid> {
adultGuid
}, showRelationship ? "Parent" : null);
allRelatedPeopleQuery = GetRelatedPeopleUnionQuery(allRelatedPeopleQuery, familyMembersQuery);
}
// If we are looking for Children...
// Add the Children from the Family Group in which the Principal participates as an Adult.
if (settings.FamilyRelationshipTypeGuids.Contains(FamilyRelationshipChildGuid.AsGuid()))
{
var familyMembersQuery = GetRelatedPeopleQuery(context, new List <int> {
familyGroupTypeId
}, new List <Guid> {
adultGuid
}, new List <Guid> {
childGuid
}, showRelationship ? "Child" : null);
allRelatedPeopleQuery = GetRelatedPeopleUnionQuery(allRelatedPeopleQuery, familyMembersQuery);
}
// If we are looking for Siblings...
// Add other Children from the Family Group in which the Principal participates as a Child.
if (settings.FamilyRelationshipTypeGuids.Contains(FamilyRelationshipSiblingGuid.AsGuid()))
{
var familyMembersQuery = GetRelatedPeopleQuery(context, new List <int> {
familyGroupTypeId
}, new List <Guid> {
childGuid
}, new List <Guid> {
childGuid
}, showRelationship ? "Sibling" : null);
allRelatedPeopleQuery = GetRelatedPeopleUnionQuery(allRelatedPeopleQuery, familyMembersQuery);
}
// If we are looking for a Spouse...
// Add other Married Adult in the Family Group in which the Principal participates as a Married Adult.
if (settings.FamilyRelationshipTypeGuids.Contains(FamilyRelationshipSpouseGuid.AsGuid()))
{
var marriedStatusGuid = SystemGuid.DefinedValue.PERSON_MARITAL_STATUS_MARRIED.AsGuid();
int marriedStatusId = DefinedValueCache.Read(marriedStatusGuid).Id;
var familyGroupMembers = new GroupMemberService(context).Queryable()
.Where(m => m.Group.GroupTypeId == familyGroupTypeId);
var personSpouseQuery = new PersonService(context).Queryable()
.SelectMany(
p =>
familyGroupMembers.Where(gm => gm.PersonId == p.Id && gm.Person.MaritalStatusValueId == marriedStatusId && gm.GroupRole.Guid == adultGuid)
.SelectMany(gm => gm.Group.Members)
.Where(gm => gm.PersonId != p.Id && gm.GroupRole.Guid == adultGuid && gm.Person.MaritalStatusValueId == marriedStatusId)
.Select(
gm =>
new RelatedPersonInfo
{
RelatedToPersonId = p.Id,
PersonId = gm.Person.Id,
FirstName = gm.Person.FirstName,
LastName = gm.Person.LastName,
Suffix = gm.Person.SuffixValue.Value,
RelationshipName = showRelationship ? "Spouse" : null
}));
allRelatedPeopleQuery = GetRelatedPeopleUnionQuery(allRelatedPeopleQuery, personSpouseQuery);
}
// If we are looking for a Known Relationship...
// Add other People from the Known Relationship Group having the specified Roles and in which the Principal is the Owner.
if (settings.KnownRelationshipTypeGuids.Any())
{
var ownerGuid = GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_OWNER.AsGuid();
var principalRoleGuids = new List <Guid>();
var targetRoleGuids = new List <Guid>(settings.KnownRelationshipTypeGuids);
principalRoleGuids.Add(ownerGuid);
var knownPersonsQuery = GetRelatedPeopleQuery(context, new List <int> {
knownRelationshipGroupTypeId
}, principalRoleGuids, targetRoleGuids, showRelationship ? "*" : null);
allRelatedPeopleQuery = GetRelatedPeopleUnionQuery(allRelatedPeopleQuery, knownPersonsQuery);
}
//
// Create a Select Expression to return the requested values.
//
var personQuery = new PersonService(context).Queryable().Select(p => allRelatedPeopleQuery.Where(rpi => rpi.RelatedToPersonId == p.Id).AsEnumerable());
var selectExpression = SelectExpressionExtractor.Extract(personQuery, entityIdProperty, "p");
return(selectExpression);
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
// Find all the Group Types
var groupTypeIds = GetAvailableGroupTypes();
if (GetAttributeValue("DisplayFilter").AsBooleanOrNull() ?? false)
{
int?groupTypeFilter = gfSettings.GetUserPreference("Group Type").AsIntegerOrNull();
if (groupTypeFilter.HasValue)
{
groupTypeIds = groupTypeIds.Where(g => g == groupTypeFilter.Value).ToList();
}
}
// filter to a specific group type if provided in the query string
if (!string.IsNullOrWhiteSpace(RockPage.PageParameter("GroupTypeId")))
{
int?groupTypeId = RockPage.PageParameter("GroupTypeId").AsIntegerOrNull();
if (groupTypeId.HasValue)
{
groupTypeIds.Clear();
groupTypeIds.Add(groupTypeId.Value);
}
}
var rockContext = new RockContext();
var groupService = new GroupService(rockContext);
SortProperty sortProperty = gGroups.SortProperty;
if (sortProperty == null)
{
sortProperty = new SortProperty(new GridViewSortEventArgs("Name", SortDirection.Ascending));
}
bool onlySecurityGroups = GetAttributeValue("LimittoSecurityRoleGroups").AsBoolean();
var qryGroups = groupService.Queryable()
.Where(g => groupTypeIds.Contains(g.GroupTypeId) && (!onlySecurityGroups || g.IsSecurityRole));
string limitToActiveStatus = GetAttributeValue("LimittoActiveStatus");
bool showActive = true;
bool showInactive = true;
if (limitToActiveStatus == "all" && gfSettings.Visible)
{
// Filter by active/inactive unless the block settings restrict it
if (ddlActiveFilter.SelectedIndex > -1)
{
switch (ddlActiveFilter.SelectedValue)
{
case "active":
showInactive = false;
break;
case "inactive":
showActive = false;
break;
}
}
}
else if (limitToActiveStatus != "all")
{
// filter by the block setting for Active Status
if (limitToActiveStatus == "active")
{
showInactive = false;
}
}
var groupTypePurposeValue = gfSettings.GetUserPreference("Group Type Purpose").AsIntegerOrNull();
var groupList = new List <GroupListRowInfo>();
// Person context will exist if used on a person detail page
int personEntityTypeId = EntityTypeCache.Read("Rock.Model.Person").Id;
if (ContextTypesRequired.Any(e => e.Id == personEntityTypeId))
{
var personContext = ContextEntity <Person>();
if (personContext != null)
{
// limit to Groups that the person is a member of
var qry = new GroupMemberService(rockContext).Queryable(true)
.Where(m => m.PersonId == personContext.Id)
.Join(qryGroups, gm => gm.GroupId, g => g.Id, (gm, g) => new { Group = g, GroupMember = gm });
// Filter by Active Status of Group and Group Membership.
if (showActive && !showInactive)
{
// Show only active Groups and active Memberships.
qry = qry.Where(gmg => gmg.Group.IsActive && gmg.GroupMember.GroupMemberStatus == GroupMemberStatus.Active);
}
else if (!showActive)
{
// Show only inactive Groups or inactive Memberships.
qry = qry.Where(gmg => !gmg.Group.IsActive || gmg.GroupMember.GroupMemberStatus == GroupMemberStatus.Inactive);
}
if (groupTypePurposeValue.HasValue && gfSettings.Visible)
{
qry = qry.Where(t => t.Group.GroupType.GroupTypePurposeValueId == groupTypePurposeValue);
}
groupList = qry
.AsEnumerable()
.Where(gm => gm.Group.IsAuthorized(Rock.Security.Authorization.VIEW, CurrentPerson))
.Select(m => new GroupListRowInfo
{
Id = m.Group.Id,
Path = string.Empty,
Name = m.Group.Name,
GroupTypeName = m.Group.GroupType.Name,
GroupOrder = m.Group.Order,
GroupTypeOrder = m.Group.GroupType.Order,
Description = m.Group.Description,
IsSystem = m.Group.IsSystem,
GroupRole = m.GroupMember.GroupRole.Name,
DateAdded = m.GroupMember.DateTimeAdded ?? m.GroupMember.CreatedDateTime,
IsActive = m.Group.IsActive && (m.GroupMember.GroupMemberStatus == GroupMemberStatus.Active),
IsActiveOrder = (m.Group.IsActive && (m.GroupMember.GroupMemberStatus == GroupMemberStatus.Active) ? 1 : 2),
IsSynced = m.Group.SyncDataViewId.HasValue,
MemberCount = 0
})
.AsQueryable()
.Sort(sortProperty)
.ToList();
}
}
else
{
var roleGroupType = GroupTypeCache.Read(Rock.SystemGuid.GroupType.GROUPTYPE_SECURITY_ROLE.AsGuid());
int roleGroupTypeId = roleGroupType != null ? roleGroupType.Id : 0;
bool useRolePrefix = onlySecurityGroups || groupTypeIds.Contains(roleGroupTypeId);
if (!showInactive)
{
qryGroups = qryGroups.Where(x => x.IsActive);
}
else if (!showActive)
{
qryGroups = qryGroups.Where(x => !x.IsActive);
}
if (groupTypePurposeValue.HasValue && gfSettings.Visible)
{
qryGroups = qryGroups.Where(t => t.GroupType.GroupTypePurposeValueId == groupTypePurposeValue);
}
groupList = qryGroups
.AsEnumerable()
.Where(g => g.IsAuthorized(Rock.Security.Authorization.VIEW, CurrentPerson))
.Select(g => new GroupListRowInfo
{
Id = g.Id,
Path = string.Empty,
Name = ((useRolePrefix && g.GroupType.Id != roleGroupTypeId) ? "GROUP - " : "") + g.Name,
GroupTypeName = g.GroupType.Name,
GroupOrder = g.Order,
GroupTypeOrder = g.GroupType.Order,
Description = g.Description,
IsSystem = g.IsSystem,
IsActive = g.IsActive,
IsActiveOrder = g.IsActive ? 1 : 2,
GroupRole = string.Empty,
DateAdded = DateTime.MinValue,
IsSynced = g.SyncDataViewId.HasValue,
MemberCount = g.Members.Count()
})
.AsQueryable()
.Sort(sortProperty)
.ToList();
}
if (_showGroupPath)
{
foreach (var groupRow in groupList)
{
groupRow.Path = groupService.GroupAncestorPathName(groupRow.Id);
}
}
gGroups.DataSource = groupList;
gGroups.EntityTypeId = EntityTypeCache.Read <Group>().Id;
gGroups.DataBind();
// hide the group type column if there's only one type; must come after DataBind()
if (_groupTypesCount == 1)
{
var groupTypeColumn = this.gGroups.ColumnsOfType <RockBoundField>().FirstOrDefault(a => a.DataField == "GroupTypeName");
groupTypeColumn.Visible = false;
}
}
/// <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;
try
{
int notificationsSent = 0;
int errorsEncountered = 0;
int pendingMembersCount = 0;
// get groups set to sync
RockContext rockContext = new RockContext();
Guid?groupTypeGuid = dataMap.GetString("GroupType").AsGuidOrNull();
Guid?systemEmailGuid = dataMap.GetString("NotificationEmail").AsGuidOrNull();
Guid?groupRoleFilterGuid = dataMap.GetString("GroupRoleFilter").AsGuidOrNull();
int? pendingAge = dataMap.GetString("PendingAge").AsIntegerOrNull();
bool includePreviouslyNotificed = dataMap.GetString("IncludePreviouslyNotified").AsBoolean();
// get system email
SystemEmailService emailService = new SystemEmailService(rockContext);
SystemEmail systemEmail = null;
if (!systemEmailGuid.HasValue || systemEmailGuid == Guid.Empty)
{
context.Result = "Job failed. Unable to find System Email";
throw new Exception("No system email found.");
}
systemEmail = emailService.Get(systemEmailGuid.Value);
// get group members
if (!groupTypeGuid.HasValue || groupTypeGuid == Guid.Empty)
{
context.Result = "Job failed. Unable to find group type";
throw new Exception("No group type found");
}
var qry = new GroupMemberService(rockContext).Queryable("Person, Group, Group.Members.GroupRole")
.Where(m => m.Group.GroupType.Guid == groupTypeGuid.Value &&
m.GroupMemberStatus == GroupMemberStatus.Pending);
if (!includePreviouslyNotificed)
{
qry = qry.Where(m => m.IsNotified == false);
}
if (groupRoleFilterGuid.HasValue)
{
qry = qry.Where(m => m.GroupRole.Guid == groupRoleFilterGuid.Value);
}
if (pendingAge.HasValue && pendingAge.Value > 0)
{
var ageDate = RockDateTime.Now.AddDays(pendingAge.Value * -1);
qry = qry.Where(m => m.ModifiedDateTime > ageDate);
}
var pendingGroupMembers = qry.ToList();
var groups = pendingGroupMembers.GroupBy(m => m.Group);
var errorList = new List <string>();
foreach (var groupKey in groups)
{
var group = groupKey.Key;
// get list of pending people
var qryPendingIndividuals = group.Members.Where(m => m.GroupMemberStatus == GroupMemberStatus.Pending);
if (!includePreviouslyNotificed)
{
qryPendingIndividuals = qryPendingIndividuals.Where(m => m.IsNotified == false);
}
if (groupRoleFilterGuid.HasValue)
{
qryPendingIndividuals = qryPendingIndividuals.Where(m => m.GroupRole.Guid == groupRoleFilterGuid.Value);
}
var pendingIndividuals = qryPendingIndividuals.Select(m => m.Person).ToList();
if (!pendingIndividuals.Any())
{
continue;
}
// get list of leaders
var groupLeaders = group.Members.Where(m => m.GroupRole.IsLeader == true && m.Person != null && m.Person.Email != null && m.Person.Email != string.Empty);
if (!groupLeaders.Any())
{
errorList.Add("Unable to send emails to members in group " + group.Name + " because there is no group leader");
continue;
}
var recipients = new List <RecipientData>();
foreach (var leader in groupLeaders)
{
// create merge object
var mergeFields = new Dictionary <string, object>();
mergeFields.Add("PendingIndividuals", pendingIndividuals);
mergeFields.Add("Group", group);
mergeFields.Add("ParentGroup", group.ParentGroup);
mergeFields.Add("Person", leader.Person);
recipients.Add(new RecipientData(leader.Person.Email, mergeFields));
}
var errorMessages = new List <string>();
var emailMessage = new RockEmailMessage(systemEmail.Guid);
emailMessage.SetRecipients(recipients);
emailMessage.Send(out errorMessages);
errorsEncountered += errorMessages.Count;
errorList.AddRange(errorMessages);
// be conservative: only mark as notified if we are sure the email didn't fail
if (errorMessages.Any())
{
continue;
}
notificationsSent += recipients.Count();
// mark pending members as notified as we go in case the job fails
var notifiedPersonIds = pendingIndividuals.Select(p => p.Id);
foreach (var pendingGroupMember in pendingGroupMembers.Where(m => m.IsNotified == false && notifiedPersonIds.Contains(m.PersonId)))
{
pendingGroupMember.IsNotified = true;
}
rockContext.SaveChanges();
}
context.Result = string.Format("Sent {0} emails to leaders for {1} pending individuals. {2} errors encountered.", notificationsSent, pendingMembersCount, errorsEncountered);
if (errorList.Any())
{
StringBuilder sb = new StringBuilder();
sb.AppendLine();
sb.Append("Errors: ");
errorList.ForEach(e => { sb.AppendLine(); sb.Append(e); });
string errors = sb.ToString();
context.Result += errors;
throw new Exception(errors);
}
}
catch (Exception ex)
{
HttpContext context2 = HttpContext.Current;
ExceptionLogService.LogException(ex, context2);
throw;
}
}
private void ListGroups()
{
RockContext rockContext = new RockContext();
var qry = new GroupMemberService(rockContext)
.Queryable("Group");
var parentGroupGuid = GetAttributeValue("ParentGroup").AsGuidOrNull();
if (parentGroupGuid != null)
{
var availableGroupIds = (List <int>)GetCacheItem("GroupListPersonalizedLava:" + parentGroupGuid.ToString());
if (availableGroupIds == null)
{
var parentGroup = new GroupService(rockContext).Get(parentGroupGuid ?? new Guid());
if (parentGroup != null)
{
availableGroupIds = GetChildGroups(parentGroup).Select(g => g.Id).ToList();
}
else
{
availableGroupIds = new List <int>();
}
var cacheLength = GetAttributeValue("CacheDuration").AsInteger();
string cacheTags = GetAttributeValue("CacheTags") ?? string.Empty;
AddCacheItem("GroupListPersonalizedLava:" + parentGroupGuid.ToString(), availableGroupIds, cacheLength, cacheTags);
}
qry = qry.Where(m => availableGroupIds.Contains(m.GroupId));
}
qry = qry.Where(m => m.PersonId == CurrentPersonId &&
m.GroupMemberStatus == GroupMemberStatus.Active);
if (_hideInactive)
{
qry = qry.Where(m => m.Group.IsActive == true && !m.Group.IsArchived);
}
List <Guid> includeGroupTypeGuids = GetAttributeValue("IncludeGroupTypes").SplitDelimitedValues().Select(a => Guid.Parse(a)).ToList();
if (includeGroupTypeGuids.Count > 0)
{
qry = qry.Where(t => includeGroupTypeGuids.Contains(t.Group.GroupType.Guid));
}
List <Guid> excludeGroupTypeGuids = GetAttributeValue("ExcludeGroupTypes").SplitDelimitedValues().Select(a => Guid.Parse(a)).ToList();
if (excludeGroupTypeGuids.Count > 0)
{
qry = qry.Where(t => !excludeGroupTypeGuids.Contains(t.Group.GroupType.Guid));
}
var groups = new List <GroupInvolvementSummary>();
foreach (var groupMember in qry.ToList())
{
if (groupMember.Group.IsAuthorized(Authorization.VIEW, CurrentPerson))
{
groups.Add(new GroupInvolvementSummary
{
Group = groupMember.Group,
Role = groupMember.GroupRole.Name,
IsLeader = groupMember.GroupRole.IsLeader,
GroupType = groupMember.Group.GroupType.Name
});
}
}
var mergeFields = Rock.Lava.LavaHelper.GetCommonMergeFields(this.RockPage, this.CurrentPerson);
mergeFields.Add("Groups", groups);
Dictionary <string, object> linkedPages = new Dictionary <string, object>();
linkedPages.Add("DetailPage", LinkedPageRoute("DetailPage"));
mergeFields.Add("LinkedPages", linkedPages);
if (this.GetAttributeValue("DisplayInactiveGroups").AsBoolean())
{
mergeFields.Add("ShowInactive", this.GetAttributeValue("DisplayInactiveGroups"));
mergeFields.Add("InitialActive", this.GetAttributeValue("InitialActiveSetting"));
mergeFields.Add("InactiveParameter", this.GetAttributeValue("InactiveParameterName"));
}
string template = GetAttributeValue("LavaTemplate");
lContent.Text = template.ResolveMergeFields(mergeFields);
}