/// <summary>
/// Executes the specified workflow.
/// </summary>
/// <param name="rockContext">The rock context.</param>
/// <param name="action">The workflow action.</param>
/// <param name="entity">The entity.</param>
/// <param name="errorMessages">The error messages.</param>
/// <returns></returns>
/// <exception cref="System.NotImplementedException"></exception>
public override bool Execute(RockContext rockContext, Model.WorkflowAction action, Object entity, out List <string> errorMessages)
{
var checkInState = GetCheckInState(entity, out errorMessages);
if (checkInState != null && checkInState.CheckIn.SearchType != null)
{
checkInState.CheckIn.Families = new List <CheckInFamily>();
if (!string.IsNullOrWhiteSpace(checkInState.CheckIn.SearchValue))
{
var personService = new PersonService(rockContext);
var memberService = new GroupMemberService(rockContext);
var groupService = new GroupService(rockContext);
int personRecordTypeId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON.AsGuid()).Id;
int familyGroupTypeId = GroupTypeCache.Get(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid()).Id;
var dvInactive = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE.AsGuid());
IQueryable <int> familyIdQry = null;
if (checkInState.CheckIn.SearchType.Guid.Equals(SystemGuid.DefinedValue.CHECKIN_SEARCH_TYPE_PHONE_NUMBER.AsGuid()))
{
string numericPhone = checkInState.CheckIn.SearchValue.AsNumeric();
var phoneQry = new PhoneNumberService(rockContext).Queryable().AsNoTracking();
if (checkInState.CheckInType == null || checkInState.CheckInType.PhoneSearchType == PhoneSearchType.EndsWith)
{
char[] charArray = numericPhone.ToCharArray();
Array.Reverse(charArray);
phoneQry = phoneQry.Where(o =>
o.NumberReversed.StartsWith(new string( charArray )));
}
else
{
phoneQry = phoneQry.Where(o =>
o.Number.Contains(numericPhone));
}
var tmpQry = phoneQry.Join(personService.Queryable().AsNoTracking(),
o => new { PersonId = o.PersonId, IsDeceased = false, RecordTypeValueId = personRecordTypeId },
p => new { PersonId = p.Id, IsDeceased = p.IsDeceased, RecordTypeValueId = p.RecordTypeValueId.Value },
(pn, p) => new { Person = p, PhoneNumber = pn })
.Join(memberService.Queryable().AsNoTracking(),
pn => pn.Person.Id,
m => m.PersonId,
(o, m) => new { PersonNumber = o.PhoneNumber, GroupMember = m });
familyIdQry = groupService.Queryable().Where(g => tmpQry.Any(o => o.GroupMember.GroupId == g.Id) && g.GroupTypeId == familyGroupTypeId)
.Select(g => g.Id)
.Distinct();
}
else
{
var familyMemberQry = memberService
.AsNoFilter().AsNoTracking()
.Where(m =>
m.Group.GroupTypeId == familyGroupTypeId &&
m.Person.RecordTypeValueId == personRecordTypeId);
if (checkInState.CheckIn.SearchType.Guid.Equals(SystemGuid.DefinedValue.CHECKIN_SEARCH_TYPE_NAME.AsGuid()))
{
var personIds = personService.GetByFullName(checkInState.CheckIn.SearchValue, false).AsNoTracking().Select(p => p.Id);
familyMemberQry = familyMemberQry.Where(f => personIds.Contains(f.PersonId));
}
else if (checkInState.CheckIn.SearchType.Guid.Equals(SystemGuid.DefinedValue.CHECKIN_SEARCH_TYPE_SCANNED_ID.AsGuid()))
{
var personIds = new List <int>();
var dv = DefinedValueCache.Get(SystemGuid.DefinedValue.PERSON_SEARCH_KEYS_ALTERNATE_ID.AsGuid());
if (dv != null)
{
var searchValueService = new PersonSearchKeyService(rockContext);
var personAliases = searchValueService.Queryable().AsNoTracking()
.Where(v =>
v.SearchTypeValueId == dv.Id &&
v.SearchValue == checkInState.CheckIn.SearchValue)
.Select(v => v.PersonAlias);
if (personAliases.Any())
{
checkInState.CheckIn.CheckedInByPersonAliasId = personAliases.First().Id;
personIds = personAliases.Select(a => a.PersonId).ToList();
}
}
if (personIds.Any())
{
familyMemberQry = familyMemberQry.Where(f => personIds.Contains(f.PersonId));
}
else
{
// if there were no matches, try to find a family check-in identifier. V8 has a "run once" job that moves the family identifiers
// to person search values, but in case the job has not yet completed, will still do the check for family ids.
var entityIds = new List <int>();
var attributeValueService = new AttributeValueService(rockContext);
var attr = AttributeCache.Get("8F528431-A438-4488-8DC3-CA42E66C1B37".AsGuid());
if (attr != null)
{
entityIds = new AttributeValueService(rockContext)
.Queryable().AsNoTracking()
.Where(v =>
v.AttributeId == attr.Id &&
v.EntityId.HasValue &&
("|" + v.Value + "|").Contains("|" + checkInState.CheckIn.SearchValue + "|"))
.Select(v => v.EntityId.Value)
.ToList();
}
familyMemberQry = familyMemberQry.Where(f => entityIds.Contains(f.GroupId));
}
}
else if (checkInState.CheckIn.SearchType.Guid.Equals(SystemGuid.DefinedValue.CHECKIN_SEARCH_TYPE_FAMILY_ID.AsGuid()))
{
List <int> searchFamilyIds = checkInState.CheckIn.SearchValue.SplitDelimitedValues().AsIntegerList();
familyMemberQry = familyMemberQry.Where(f => searchFamilyIds.Contains(f.GroupId));
}
else
{
errorMessages.Add("Invalid Search Type");
return(false);
}
familyIdQry = familyMemberQry
.Select(m => m.GroupId)
.Distinct();
}
int maxResults = checkInState.CheckInType != null ? checkInState.CheckInType.MaxSearchResults : 100;
if (maxResults > 0)
{
familyIdQry = familyIdQry.Take(maxResults);
}
// You might think we should do a ToList() on the familyIdQry and use it below,
// but through some extensive testing, we discovered that the next SQL query is better
// optimized if it has the familyIdQry without being to-listed. It was over 270% slower
// when querying names and 120% slower when querying phone numbers.
// Load the family members
var familyMembers = memberService
.Queryable().AsNoTracking()
.Where(m => m.Group.GroupTypeId == familyGroupTypeId && familyIdQry.Contains(m.GroupId)).Select(a =>
new {
Group = a.Group,
GroupId = a.GroupId,
Order = a.GroupRole.Order,
BirthYear = a.Person.BirthYear,
BirthMonth = a.Person.BirthMonth,
BirthDay = a.Person.BirthDay,
Gender = a.Person.Gender,
NickName = a.Person.NickName,
RecordStatusValueId = a.Person.RecordStatusValueId
})
.ToList();
// Add each family
foreach (int familyId in familyMembers.Select(fm => fm.GroupId).Distinct())
{
// Get each of the members for this family
var familyMemberQry = familyMembers
.Where(m =>
m.GroupId == familyId &&
m.NickName != null);
if (checkInState.CheckInType != null && checkInState.CheckInType.PreventInactivePeople && dvInactive != null)
{
familyMemberQry = familyMemberQry
.Where(m =>
m.RecordStatusValueId != dvInactive.Id);
}
var thisFamilyMembers = familyMemberQry.ToList();
if (thisFamilyMembers.Any())
{
var group = thisFamilyMembers
.Select(m => m.Group)
.FirstOrDefault();
var firstNames = thisFamilyMembers
.OrderBy(m => m.Order)
.ThenBy(m => m.BirthYear)
.ThenBy(m => m.BirthMonth)
.ThenBy(m => m.BirthDay)
.ThenBy(m => m.Gender)
.Select(m => m.NickName)
.ToList();
var family = new CheckInFamily();
family.Group = group.Clone(false);
family.Caption = group.ToString();
family.FirstNames = firstNames;
family.SubCaption = firstNames.AsDelimited(", ");
checkInState.CheckIn.Families.Add(family);
}
}
}
return(true);
}
errorMessages.Add("Invalid Check-in State");
return(false);
}
/// <summary>
/// Updates GroupMemberHistorical for any group members in groups that have data group history enabled
/// </summary>
/// <param name="context">The context.</param>
public void UpdateGroupMemberHistorical(IJobExecutionContext context)
{
var rockContext = new RockContext();
var groupMemberHistoricalService = new GroupMemberHistoricalService(rockContext);
var groupMemberService = new GroupMemberService(rockContext);
var groupMembersWithHistoryEnabledQuery = groupMemberService.AsNoFilter().Where(a => a.Group.GroupType.EnableGroupHistory == true).AsNoTracking();
var groupMemberHistoricalsCurrentQuery = groupMemberHistoricalService.Queryable().Where(a => a.CurrentRowIndicator == true).AsNoTracking();
// Mark GroupMemberHistorical Rows as History ( CurrentRowIndicator = false, etc ) if any of the tracked field values change
var groupMemberHistoricalNoLongerCurrent = groupMemberHistoricalsCurrentQuery.Join(
groupMembersWithHistoryEnabledQuery,
gmh => gmh.GroupMemberId,
gm => gm.Id, (gmh, gm) => new
{
GroupMember = gm,
GroupMemberHistorical = gmh
})
.Where(a =>
a.GroupMember.GroupRoleId != a.GroupMemberHistorical.GroupRoleId ||
a.GroupMember.GroupId != a.GroupMemberHistorical.GroupId ||
a.GroupMember.GroupRole.Name != a.GroupMemberHistorical.GroupRoleName ||
a.GroupMember.GroupRole.IsLeader != a.GroupMemberHistorical.IsLeader ||
a.GroupMember.GroupMemberStatus != a.GroupMemberHistorical.GroupMemberStatus ||
a.GroupMember.IsArchived != a.GroupMemberHistorical.IsArchived ||
a.GroupMember.ArchivedDateTime != a.GroupMemberHistorical.ArchivedDateTime ||
a.GroupMember.ArchivedByPersonAliasId != a.GroupMemberHistorical.ArchivedByPersonAliasId ||
a.GroupMember.InactiveDateTime != a.GroupMemberHistorical.InactiveDateTime
).Select(a => a.GroupMemberHistorical).AsNoTracking();
var effectiveExpireDateTime = RockDateTime.Now;
int groupMembersLoggedToHistory = 0;
int groupMembersSaveToHistoryCurrent = 0;
if (groupMemberHistoricalNoLongerCurrent.Any())
{
groupMembersLoggedToHistory = rockContext.BulkUpdate(groupMemberHistoricalNoLongerCurrent, gmh => new GroupMemberHistorical
{
CurrentRowIndicator = false,
ExpireDateTime = effectiveExpireDateTime
});
}
// Insert Group Members (that have a group with GroupType.EnableGroupHistory) that don't have a CurrentRowIndicator row yet ( or don't have a CurrentRowIndicator because it was stamped with CurrentRowIndicator=false )
var groupMembersToAddToHistoricalCurrentsQuery = groupMembersWithHistoryEnabledQuery.Where(gm => !groupMemberHistoricalsCurrentQuery.Any(gmh => gmh.GroupMemberId == gm.Id));
if (groupMembersToAddToHistoricalCurrentsQuery.Any())
{
List <GroupMemberHistorical> groupMemberHistoricalCurrentsToInsert = groupMembersToAddToHistoricalCurrentsQuery
.Include(a => a.GroupRole)
.ToList()
.Select(gm => GroupMemberHistorical.CreateCurrentRowFromGroupMember(gm, effectiveExpireDateTime)).ToList();
groupMembersSaveToHistoryCurrent = groupMemberHistoricalCurrentsToInsert.Count();
rockContext.BulkInsert(groupMemberHistoricalCurrentsToInsert);
}
if (groupMembersLoggedToHistory > 0)
{
_jobStatusMessages.Add($"Logged {groupMembersLoggedToHistory} {"group member history snapshot".PluralizeIf( groupMembersLoggedToHistory != 0 )}");
}
if (groupMembersSaveToHistoryCurrent > 0)
{
int newGroupMembersAddedToHistory = groupMembersSaveToHistoryCurrent - groupMembersLoggedToHistory;
if (newGroupMembersAddedToHistory > 0)
{
_jobStatusMessages.Add($"Added {newGroupMembersAddedToHistory} new {"group member history snapshot".PluralizeIf( newGroupMembersAddedToHistory != 0 )}");
}
}
}
/// <summary>
/// Binds the members grid.
/// </summary>
public void BindMembersGrid()
{
int groupId = hfGroupId.Value.AsInteger();
var rockContext = new RockContext();
var groupMemberService = new GroupMemberService(rockContext);
var groupService = new GroupService(rockContext);
var group = groupService.Get(groupId);
if (group != null)
{
lGroupTitle.Text = group.Name.FormatAsHtmlTitle();
}
lGroupGridTitle.Visible = true;
lGroupMemberPreHtml.Visible = false;
lGroupMemberTitle.Visible = false;
// get the unfiltered list of group members, which includes archived and deceased
var qryGroupMembers = groupMemberService.AsNoFilter().Where(a => a.GroupId == groupId);
// don't include deceased
qryGroupMembers = qryGroupMembers.Where(a => a.Person.IsDeceased == false);
// Filter by First Name
string firstName = tbFirstName.Text;
if (!string.IsNullOrWhiteSpace(firstName))
{
qryGroupMembers = qryGroupMembers.Where(m =>
m.Person.FirstName.StartsWith(firstName) ||
m.Person.NickName.StartsWith(firstName));
}
// Filter by Last Name
string lastName = tbLastName.Text;
if (!string.IsNullOrWhiteSpace(lastName))
{
qryGroupMembers = qryGroupMembers.Where(m => m.Person.LastName.StartsWith(lastName));
}
// Filter by Date Added
var dateAddedRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(sdrDateAdded.DelimitedValues);
if (dateAddedRange.Start.HasValue)
{
qryGroupMembers = qryGroupMembers.Where(a => a.DateTimeAdded >= dateAddedRange.Start.Value);
}
if (dateAddedRange.End.HasValue)
{
qryGroupMembers = qryGroupMembers.Where(a => a.DateTimeAdded < dateAddedRange.End.Value);
}
var dateRemovedRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(sdrDateRemoved.DelimitedValues);
if (dateRemovedRange.Start.HasValue)
{
qryGroupMembers = qryGroupMembers.Where(a => a.ArchivedDateTime >= dateRemovedRange.Start.Value);
}
if (dateRemovedRange.End.HasValue)
{
qryGroupMembers = qryGroupMembers.Where(a => a.ArchivedDateTime < dateRemovedRange.End.Value);
}
// Filter by role
var roles = cblRole.SelectedValues.AsIntegerList();
if (roles.Any())
{
qryGroupMembers = qryGroupMembers.Where(m => roles.Contains(m.GroupRoleId));
}
// Filter by Group Member Status
var statuses = cblGroupMemberStatus.SelectedValues.Select(a => a.ConvertToEnumOrNull <GroupMemberStatus>()).Where(a => a.HasValue).Select(a => a.Value).ToList();
if (statuses.Any())
{
qryGroupMembers = qryGroupMembers.Where(m => statuses.Contains(m.GroupMemberStatus));
}
var sortProperty = gGroupMembers.SortProperty;
if (sortProperty != null)
{
qryGroupMembers = qryGroupMembers.Sort(sortProperty);
}
else
{
qryGroupMembers = qryGroupMembers.OrderBy(a => a.GroupRole.Order).ThenBy(a => a.Person.LastName).ThenBy(a => a.Person.FirstName);
}
qryGroupMembers = qryGroupMembers.Include(a => a.Person);
gGroupMembers.SetLinqDataSource(qryGroupMembers);
gGroupMembers.DataBind();
}