/// <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);
}
