/// <summary>
/// Updates the group member requirement result.
/// </summary>
/// <param name="rockContext">The rock context.</param>
/// <param name="personId">The person identifier.</param>
/// <param name="groupId">The group identifier.</param>
/// <param name="meetsGroupRequirement">The meets group requirement.</param>
public void UpdateGroupMemberRequirementResult(RockContext rockContext, int personId, int groupId, MeetsGroupRequirement meetsGroupRequirement)
{
GroupRequirement groupRequirement = this;
var currentDateTime = RockDateTime.Now;
GroupMemberRequirementService groupMemberRequirementService = new GroupMemberRequirementService(rockContext);
var groupMemberService = new GroupMemberService(rockContext);
var groupMemberQry = groupMemberService.Queryable(true).Where(a => a.PersonId == personId && a.GroupId == groupId &&
(
(groupRequirement.GroupId.HasValue && groupRequirement.GroupId == a.GroupId)
||
(groupRequirement.GroupTypeId.HasValue && groupRequirement.GroupTypeId == a.Group.GroupTypeId)
));
if (this.GroupRoleId != null)
{
groupMemberQry = groupMemberQry.Where(a => a.GroupRoleId == this.GroupRoleId);
}
foreach (var groupMemberId in groupMemberQry.Select(a => a.Id))
{
var groupMemberRequirement = groupMemberRequirementService.Queryable().Where(a => a.GroupMemberId == groupMemberId && a.GroupRequirementId == groupRequirement.Id).FirstOrDefault();
if (groupMemberRequirement == null)
{
groupMemberRequirement = new GroupMemberRequirement();
groupMemberRequirement.GroupMemberId = groupMemberId;
groupMemberRequirement.GroupRequirementId = groupRequirement.Id;
groupMemberRequirementService.Add(groupMemberRequirement);
}
groupMemberRequirement.LastRequirementCheckDateTime = currentDateTime;
if ((meetsGroupRequirement == MeetsGroupRequirement.Meets) || (meetsGroupRequirement == MeetsGroupRequirement.MeetsWithWarning))
{
// they meet the requirement so update the Requirement Met Date/Time
groupMemberRequirement.RequirementMetDateTime = currentDateTime;
groupMemberRequirement.RequirementFailDateTime = null;
}
else
{
// they don't meet the requirement so set the Requirement Met Date/Time to null
groupMemberRequirement.RequirementMetDateTime = null;
groupMemberRequirement.RequirementFailDateTime = currentDateTime;
}
if (meetsGroupRequirement == MeetsGroupRequirement.MeetsWithWarning)
{
if (!groupMemberRequirement.RequirementWarningDateTime.HasValue)
{
// they have a warning for the requirement, and didn't have a warning already
groupMemberRequirement.RequirementWarningDateTime = currentDateTime;
}
}
else
{
// no warning, so set to null
groupMemberRequirement.RequirementWarningDateTime = null;
}
}
}
/// <summary>
/// If Group has GroupRequirements, will calculate and update the GroupMemberRequirements for the GroupMember, then save the changes to the database
/// </summary>
/// <param name="rockContext">The rock context.</param>
/// <param name="saveChanges">if set to <c>true</c> [save changes].</param>
public void CalculateRequirements(RockContext rockContext, bool saveChanges = true)
{
// recalculate and store in the database if the groupmember isn't new or changed
var groupMemberRequirementsService = new GroupMemberRequirementService(rockContext);
var group = this.Group ?? new GroupService(rockContext).Queryable("GroupRequirements").FirstOrDefault(a => a.Id == this.GroupId);
if (!group.GetGroupRequirements(rockContext).Any())
{
// group doesn't have requirements so no need to calculate
return;
}
var updatedRequirements = group.PersonMeetsGroupRequirements(rockContext, this.PersonId, this.GroupRoleId);
foreach (var updatedRequirement in updatedRequirements)
{
updatedRequirement.GroupRequirement.UpdateGroupMemberRequirementResult(rockContext, updatedRequirement.PersonId, this.GroupId, updatedRequirement.MeetsGroupRequirement);
}
if (saveChanges)
{
rockContext.SaveChanges();
}
}
/// <summary>
/// Returns a list of each person and their GroupRequiremnt status for this group requirement
/// </summary>
/// <param name="rockContext">The rock context.</param>
/// <param name="personQry">A qry containing the people whose requirements should be checked</param>
/// <param name="groupRoleId">The group role identifier.</param>
/// <returns></returns>
public IEnumerable <PersonGroupRequirementStatus> PersonQueryableMeetsGroupRequirement(RockContext rockContext, IQueryable <Person> personQry, int?groupRoleId)
{
if ((this.GroupRoleId != null) && (groupRoleId != null) && (this.GroupRoleId != groupRoleId))
{
// if this GroupRequirement is for a specific role, the groupRole we are checking for is something different
var result = personQry.ToList().Select(a =>
new PersonGroupRequirementStatus
{
PersonId = a.Id,
GroupRequirement = this,
MeetsGroupRequirement = MeetsGroupRequirement.NotApplicable
});
return(result);
}
if (this.GroupRequirementType.RequirementCheckType == RequirementCheckType.Dataview)
{
if (this.GroupRequirementType.DataViewId.HasValue)
{
var errorMessages = new List <string>();
var personService = new PersonService(rockContext);
var paramExpression = personService.ParameterExpression;
var dataViewWhereExpression = this.GroupRequirementType.DataView.GetExpression(personService, paramExpression, out errorMessages);
var dataViewQry = personService.Get(paramExpression, dataViewWhereExpression);
IQueryable <Person> warningDataViewQry = null;
if (this.GroupRequirementType.WarningDataViewId.HasValue)
{
var warningDataViewWhereExpression = this.GroupRequirementType.WarningDataView.GetExpression(personService, paramExpression, out errorMessages);
warningDataViewQry = personService.Get(paramExpression, warningDataViewWhereExpression);
}
if (dataViewQry != null)
{
var personWithRequirements = from p in personQry
join d in dataViewQry on p equals d into oj
from d in oj.DefaultIfEmpty()
select new { PersonId = p.Id, Included = d != null, WarningIncluded = false };
// if a Warning Database was specified, set the WarningIncluded flag to true if they are included in the Warning Dataview
if (warningDataViewQry != null)
{
personWithRequirements = personWithRequirements.Select(a => new
{
a.PersonId,
a.Included,
WarningIncluded = warningDataViewQry.Any(w => w.Id == a.PersonId)
});
}
var result = personWithRequirements.ToList().Select(a =>
new PersonGroupRequirementStatus
{
PersonId = a.PersonId,
GroupRequirement = this,
MeetsGroupRequirement = a.Included
? (a.WarningIncluded ? MeetsGroupRequirement.MeetsWithWarning : MeetsGroupRequirement.Meets)
: MeetsGroupRequirement.NotMet
});
return(result);
}
}
else
{
throw new Exception("No dataview assigned to Group Requirement Type: " + this.GroupRequirementType.Name);
}
}
else if (this.GroupRequirementType.RequirementCheckType == RequirementCheckType.Sql)
{
string formattedSql = this.GroupRequirementType.SqlExpression.ResolveMergeFields(this.GroupRequirementType.GetMergeObjects(this.Group));
string warningFormattedSql = this.GroupRequirementType.WarningSqlExpression.ResolveMergeFields(this.GroupRequirementType.GetMergeObjects(this.Group));
try
{
var tableResult = DbService.GetDataTable(formattedSql, System.Data.CommandType.Text, null);
if (tableResult.Columns.Count > 0)
{
IEnumerable <int> personIds = tableResult.Rows.OfType <System.Data.DataRow>().Select(r => Convert.ToInt32(r[0]));
IEnumerable <int> warningPersonIds = null;
// if a Warning SQL was specified, get a list of PersonIds that should have a warning with their status
if (!string.IsNullOrWhiteSpace(warningFormattedSql))
{
var warningTableResult = DbService.GetDataTable(warningFormattedSql, System.Data.CommandType.Text, null);
if (warningTableResult.Columns.Count > 0)
{
warningPersonIds = warningTableResult.Rows.OfType <System.Data.DataRow>().Select(r => Convert.ToInt32(r[0]));
}
}
var result = personQry.Select(a => a.Id).ToList().Select(a => new PersonGroupRequirementStatus
{
PersonId = a,
GroupRequirement = this,
MeetsGroupRequirement = personIds.Contains(a)
? ((warningPersonIds != null && warningPersonIds.Contains(a))
? MeetsGroupRequirement.MeetsWithWarning
: MeetsGroupRequirement.Meets
)
: MeetsGroupRequirement.NotMet,
});
return(result);
}
}
catch (Exception ex)
{
// Exception occurred (probably due to bad SQL)
ExceptionLogService.LogException(ex, System.Web.HttpContext.Current);
var result = personQry.Select(a => a.Id).ToList().Select(a => new PersonGroupRequirementStatus
{
PersonId = a,
GroupRequirement = this,
MeetsGroupRequirement = MeetsGroupRequirement.Error
});
return(result);
}
}
else
{
// manual
var groupMemberRequirementQry = new GroupMemberRequirementService(rockContext).Queryable().Where(a => a.GroupRequirementId == this.Id && a.RequirementMetDateTime.HasValue);
var result = personQry.ToList().Select(a =>
new PersonGroupRequirementStatus
{
PersonId = a.Id,
GroupRequirement = this,
MeetsGroupRequirement = groupMemberRequirementQry.Any(r => r.GroupMember.PersonId == a.Id) ? MeetsGroupRequirement.Meets : MeetsGroupRequirement.NotMet
});
return(result);
}
// shouldn't happen
return(null);
}
/// <summary>
/// Updates the group member requirement result.
/// </summary>
/// <param name="rockContext">The rock context.</param>
/// <param name="personId">The person identifier.</param>
/// <param name="groupId">The group identifier.</param>
/// <param name="meetsGroupRequirement">The meets group requirement.</param>
public void UpdateGroupMemberRequirementResult(RockContext rockContext, int personId, int groupId, MeetsGroupRequirement meetsGroupRequirement)
{
GroupRequirement groupRequirement = this;
var currentDateTime = RockDateTime.Now;
GroupMemberRequirementService groupMemberRequirementService = new GroupMemberRequirementService(rockContext);
var groupMemberService = new GroupMemberService(rockContext);
var groupMemberQry = groupMemberService.Queryable(true).Where(a => a.PersonId == personId && a.GroupId == groupId);
if (groupRequirement.GroupId.HasValue)
{
groupMemberQry = groupMemberQry.Where(g => g.GroupId == groupRequirement.GroupId);
}
else if (groupRequirement.GroupTypeId.HasValue)
{
groupMemberQry = groupMemberQry.Where(g => g.Group.GroupTypeId == groupRequirement.GroupTypeId);
}
else
{
// shouldn't happen, but grouprequirement doesn't have a groupId or a GroupTypeId
return;
}
if (this.GroupRoleId != null)
{
groupMemberQry = groupMemberQry.Where(a => a.GroupRoleId == this.GroupRoleId);
}
// just in case the same person is in the same group multiple times, get a list of the groupMember records for this person
foreach (var groupMemberId in groupMemberQry.Select(a => a.Id))
{
var groupMemberRequirement = groupMemberRequirementService.Queryable().Where(a => a.GroupMemberId == groupMemberId && a.GroupRequirementId == groupRequirement.Id).FirstOrDefault();
if (groupMemberRequirement == null)
{
groupMemberRequirement = new GroupMemberRequirement();
groupMemberRequirement.GroupMemberId = groupMemberId;
groupMemberRequirement.GroupRequirementId = groupRequirement.Id;
groupMemberRequirementService.Add(groupMemberRequirement);
}
groupMemberRequirement.LastRequirementCheckDateTime = currentDateTime;
if ((meetsGroupRequirement == MeetsGroupRequirement.Meets) || (meetsGroupRequirement == MeetsGroupRequirement.MeetsWithWarning))
{
// they meet the requirement so update the Requirement Met Date/Time
groupMemberRequirement.RequirementMetDateTime = currentDateTime;
groupMemberRequirement.RequirementFailDateTime = null;
}
else
{
// they don't meet the requirement so set the Requirement Met Date/Time to null
groupMemberRequirement.RequirementMetDateTime = null;
groupMemberRequirement.RequirementFailDateTime = currentDateTime;
}
if (meetsGroupRequirement == MeetsGroupRequirement.MeetsWithWarning)
{
if (!groupMemberRequirement.RequirementWarningDateTime.HasValue)
{
// they have a warning for the requirement, and didn't have a warning already
groupMemberRequirement.RequirementWarningDateTime = currentDateTime;
}
}
else
{
// no warning, so set to null
groupMemberRequirement.RequirementWarningDateTime = null;
}
}
}
/// <summary>
/// Returns a list of each person and their GroupRequiremnt status for this group requirement
/// </summary>
/// <param name="rockContext">The rock context.</param>
/// <param name="personQry">The person qry.</param>
/// <param name="groupId">The group identifier.</param>
/// <param name="groupRoleId">The group role identifier.</param>
/// <returns></returns>
/// <exception cref="System.Exception">No dataview assigned to Group Requirement Type: " + this.GroupRequirementType.Name</exception>
public IEnumerable <PersonGroupRequirementStatus> PersonQueryableMeetsGroupRequirement(RockContext rockContext, IQueryable <Person> personQry, int groupId, int?groupRoleId)
{
if ((this.GroupRoleId != null) && (groupRoleId != null) && (this.GroupRoleId != groupRoleId))
{
// if this GroupRequirement is for a specific role, the groupRole we are checking for is something different
var result = personQry.Select(p => p.Id).ToList().Select(a =>
new PersonGroupRequirementStatus
{
PersonId = Id,
GroupRequirement = this,
MeetsGroupRequirement = MeetsGroupRequirement.NotApplicable
});
return(result);
}
if (this.GroupRequirementType.RequirementCheckType == RequirementCheckType.Dataview)
{
var errorMessages = new List <string>();
var personService = new PersonService(rockContext);
var paramExpression = personService.ParameterExpression;
List <int> warningDataViewPersonIdList = null;
if (this.GroupRequirementType.WarningDataViewId.HasValue)
{
var warningDataViewWhereExpression = this.GroupRequirementType.WarningDataView.GetExpression(personService, paramExpression, out errorMessages);
warningDataViewPersonIdList = personService.Get(paramExpression, warningDataViewWhereExpression).Where(a => personQry.Any(p => p.Id == a.Id)).Select(a => a.Id).ToList();
}
if (this.GroupRequirementType.DataViewId.HasValue)
{
var dataViewWhereExpression = this.GroupRequirementType.DataView.GetExpression(personService, paramExpression, out errorMessages);
var dataViewQry = personService.Get(paramExpression, dataViewWhereExpression);
if (dataViewQry != null)
{
var personWithRequirementsQuery = from p in personQry
join d in dataViewQry on p.Id equals d.Id into oj
from d in oj.DefaultIfEmpty()
select new { PersonId = p.Id, Included = d != null };
var personWithRequirementsList = personWithRequirementsQuery.Select(p => new { PersonId = p.PersonId, Included = p.Included }).ToList();
var result = personWithRequirementsList.Select(a =>
{
var personGroupRequirementStatus = new PersonGroupRequirementStatus
{
PersonId = a.PersonId,
GroupRequirement = this
};
var hasWarning = warningDataViewPersonIdList?.Contains(a.PersonId) == true;
if (a.Included)
{
if (hasWarning)
{
personGroupRequirementStatus.MeetsGroupRequirement = MeetsGroupRequirement.MeetsWithWarning;
}
else
{
personGroupRequirementStatus.MeetsGroupRequirement = MeetsGroupRequirement.Meets;
}
}
else
{
personGroupRequirementStatus.MeetsGroupRequirement = MeetsGroupRequirement.NotMet;
}
return(personGroupRequirementStatus);
}
);
return(result);
}
}
else
{
var personWithIdRequirements = personQry.Select(p => p.Id);
var result = personWithIdRequirements.ToList().Select(a =>
new PersonGroupRequirementStatus
{
PersonId = a,
GroupRequirement = this,
MeetsGroupRequirement = warningDataViewPersonIdList.Contains(a) == true ? MeetsGroupRequirement.MeetsWithWarning : MeetsGroupRequirement.Meets
});
return(result);
}
}
else if (this.GroupRequirementType.RequirementCheckType == RequirementCheckType.Sql)
{
// if requirement set on GroupType, this.Group is null
var targetGroup = this.Group ?? new GroupService(rockContext).Get(groupId);
var personQryIdList = personQry.Select(a => a.Id).ToList();
Person personMergeField = null;
if (personQryIdList.Count == 1)
{
var personId = personQryIdList[0];
personMergeField = new PersonService(rockContext).GetNoTracking(personId);
}
string formattedSql = this.GroupRequirementType.SqlExpression.ResolveMergeFields(this.GroupRequirementType.GetMergeObjects(targetGroup, personMergeField));
string warningFormattedSql = this.GroupRequirementType.WarningSqlExpression.ResolveMergeFields(this.GroupRequirementType.GetMergeObjects(targetGroup, personMergeField));
try
{
var tableResult = DbService.GetDataTable(formattedSql, System.Data.CommandType.Text, null);
if (tableResult.Columns.Count > 0)
{
IEnumerable <int> personIds = tableResult.Rows.OfType <System.Data.DataRow>().Select(r => Convert.ToInt32(r[0]));
IEnumerable <int> warningPersonIds = null;
// if a Warning SQL was specified, get a list of PersonIds that should have a warning with their status
if (!string.IsNullOrWhiteSpace(warningFormattedSql))
{
var warningTableResult = DbService.GetDataTable(warningFormattedSql, System.Data.CommandType.Text, null);
if (warningTableResult.Columns.Count > 0)
{
warningPersonIds = warningTableResult.Rows.OfType <System.Data.DataRow>().Select(r => Convert.ToInt32(r[0]));
}
}
var result = personQryIdList.Select(a => new PersonGroupRequirementStatus
{
PersonId = a,
GroupRequirement = this,
MeetsGroupRequirement = personIds.Contains(a)
? ((warningPersonIds != null && warningPersonIds.Contains(a))
? MeetsGroupRequirement.MeetsWithWarning
: MeetsGroupRequirement.Meets
)
: MeetsGroupRequirement.NotMet,
});
return(result);
}
}
catch (Exception ex)
{
// Exception occurred (probably due to bad SQL)
ExceptionLogService.LogException(ex, System.Web.HttpContext.Current);
var result = personQry.Select(a => a.Id).ToList().Select(a => new PersonGroupRequirementStatus
{
PersonId = a,
GroupRequirement = this,
MeetsGroupRequirement = MeetsGroupRequirement.Error,
CalculationException = ex
});
return(result);
}
}
else
{
// manual
var groupMemberRequirementQry = new GroupMemberRequirementService(rockContext).Queryable().Where(a => a.GroupMember.GroupId == groupId && a.GroupRequirementId == this.Id && a.RequirementMetDateTime.HasValue);
var result = personQry.ToList().Select(a =>
new PersonGroupRequirementStatus
{
PersonId = a.Id,
GroupRequirement = this,
MeetsGroupRequirement = groupMemberRequirementQry.Any(r => r.GroupMember.PersonId == a.Id) ? MeetsGroupRequirement.Meets : MeetsGroupRequirement.NotMet
});
return(result);
}
// shouldn't happen
return(null);
}
/// <summary>
/// Groups the members not meeting requirements.
/// </summary>
/// <param name="groupId">The group identifier.</param>
/// <param name="includeWarnings">if set to <c>true</c> [include warnings].</param>
/// <param name="includeInactive">if set to <c>true</c> [include inactive].</param>
/// <returns></returns>
public Dictionary <GroupMember, Dictionary <PersonGroupRequirementStatus, DateTime> > GroupMembersNotMeetingRequirements(int groupId, bool includeWarnings, bool includeInactive = false)
{
Dictionary <GroupMember, Dictionary <PersonGroupRequirementStatus, DateTime> > results = new Dictionary <GroupMember, Dictionary <PersonGroupRequirementStatus, DateTime> >();
var rockContext = this.Context as RockContext;
var groupRequirementService = new GroupRequirementService(rockContext);
var groupMemberService = new GroupMemberService(rockContext);
var groupMemberRequirementService = new GroupMemberRequirementService(rockContext);
var qryGroupRequirements = groupRequirementService.Queryable().Where(a => a.GroupId == groupId);
bool hasGroupRequirements = qryGroupRequirements.Any();
if (!hasGroupRequirements)
{
// if no group requirements, then there are no members that don't meet the requirements, so return an empty dictionary
return(new Dictionary <GroupMember, Dictionary <PersonGroupRequirementStatus, DateTime> >());
}
var qryGroupMembers = groupMemberService.Queryable().Where(a => a.GroupId == groupId);
var qryGroupMemberRequirements = groupMemberRequirementService.Queryable().Where(a => a.GroupMember.GroupId == groupId);
if (!includeInactive)
{
qryGroupMembers = qryGroupMembers.Where(a => a.GroupMemberStatus == GroupMemberStatus.Active);
}
// get a list of group member ids that don't meet all the requirements
IQueryable <int> qryGroupMemberIdsThatLackGroupRequirements = qryGroupMembers.Where(
a => !qryGroupRequirements.Select(x => x.Id).All(
r => a.GroupMemberRequirements.Where(mr => mr.RequirementMetDateTime.HasValue).Select(x => x.GroupRequirementId).Contains(r))).Select(a => a.Id);
IQueryable <GroupMember> qryMembersWithIssues;
if (includeWarnings)
{
IQueryable <int> qryGroupMemberIdsWithRequirementWarnings = qryGroupMemberRequirements.Where(a => a.RequirementWarningDateTime != null || a.RequirementFailDateTime != null).Select(a => a.GroupMemberId).Distinct();
qryMembersWithIssues = qryGroupMembers.Where(a => qryGroupMemberIdsThatLackGroupRequirements.Contains(a.Id) || qryGroupMemberIdsWithRequirementWarnings.Contains(a.Id));
}
else
{
qryMembersWithIssues = qryGroupMembers.Where(a => qryGroupMemberIdsThatLackGroupRequirements.Contains(a.Id));
}
var qry = qryMembersWithIssues.Select(a => new
{
GroupMember = a,
GroupRequirementStatuses = qryGroupMemberRequirements.Where(x => x.GroupMemberId == a.Id)
});
var currentDateTime = RockDateTime.Now;
foreach (var groupMemberWithIssues in qry)
{
Dictionary <PersonGroupRequirementStatus, DateTime> statuses = new Dictionary <PersonGroupRequirementStatus, DateTime>();
// populate where the status is known
foreach (var requirementStatus in groupMemberWithIssues.GroupRequirementStatuses)
{
PersonGroupRequirementStatus status = new PersonGroupRequirementStatus();
status.GroupRequirement = requirementStatus.GroupRequirement;
status.PersonId = groupMemberWithIssues.GroupMember.PersonId;
DateTime occuranceDate = new DateTime();
if (requirementStatus.RequirementMetDateTime == null)
{
status.MeetsGroupRequirement = MeetsGroupRequirement.NotMet;
occuranceDate = requirementStatus.RequirementFailDateTime ?? currentDateTime;
}
else if (requirementStatus.RequirementWarningDateTime.HasValue)
{
status.MeetsGroupRequirement = MeetsGroupRequirement.MeetsWithWarning;
occuranceDate = requirementStatus.RequirementWarningDateTime.Value;
}
else
{
status.MeetsGroupRequirement = MeetsGroupRequirement.Meets;
occuranceDate = requirementStatus.RequirementMetDateTime.Value;
}
statuses.Add(status, occuranceDate);
}
// also add any groupRequirements that they don't have statuses for (and therefore haven't met)
foreach (var groupRequirement in qryGroupRequirements)
{
if (!statuses.Any(x => x.Key.GroupRequirement.Id == groupRequirement.Id))
{
PersonGroupRequirementStatus status = new PersonGroupRequirementStatus();
status.GroupRequirement = groupRequirement;
status.PersonId = groupMemberWithIssues.GroupMember.PersonId;
status.MeetsGroupRequirement = MeetsGroupRequirement.NotMet;
statuses.Add(status, currentDateTime);
}
}
var statusesWithIssues = statuses.Where(a => a.Key.MeetsGroupRequirement != MeetsGroupRequirement.Meets).ToDictionary(k => k.Key, v => v.Value);
if (statusesWithIssues.Any())
{
results.Add(groupMemberWithIssues.GroupMember, statusesWithIssues);
}
}
return(results);
}