/// <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>
/// 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>
private IQueryable <RelatedPersonInfo> GetAllRelatedPeopleQuery(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.Get(SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid()).Id;
int knownRelationshipGroupTypeId = GroupTypeCache.Get(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.Get(marriedStatusGuid).Id;
/*
* 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 familyGroupMembers = new GroupMemberService(context).Queryable(true)
.Where(m => m.Group.GroupTypeId == familyGroupTypeId);
var personSpouseQuery = new PersonService(context).Queryable(new PersonService.PersonQueryOptions {
IncludeDeceased = true
})
.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)
.Where(gm => gm.Person.IsDeceased == false)
.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);
}
return(allRelatedPeopleQuery);
}