public static IQueryable <PersonAlias> FindNotYetSyncedPersonAlises(RockContext rockContext, IQueryable <int> populationIds,
IQueryable <int> syncedPersonAliasIds)
{
var notSyncedPersonAliadsIds = populationIds.Except(syncedPersonAliasIds).ToList();
var personAliases = new PersonAliasService(rockContext).GetByIds(notSyncedPersonAliadsIds);
return(personAliases.Where(a => notSyncedPersonAliadsIds.Contains(a.Id) &&
a.Person.Email != null && a.Person.Email != string.Empty &&
a.Person.EmailPreference == EmailPreference.EmailAllowed));
}
/// <summary>
/// Loads Rock data that's used globally by the transform
/// </summary>
/// <param name="lookupContext">The lookup context.</param>
private static void LoadRockData(RockContext lookupContext = null)
{
lookupContext = lookupContext ?? new RockContext();
// initialize file providers
DatabaseProvider = new Database();
FileSystemProvider = new FileSystem();
// core-specified attribute guid for setting file root path
RootPathAttribute = AttributeCache.Get(new Guid("3CAFA34D-9208-439B-A046-CB727FB729DE"));
// core-specified blacklist files
FileTypeBlackList = (GlobalAttributesCache.Get().GetValue("ContentFiletypeBlacklist")
?? string.Empty).Split(new char[] { ',', ';' }, StringSplitOptions.RemoveEmptyEntries);
// clean up blacklist
FileTypeBlackList = FileTypeBlackList.Select(a => a.ToLower().TrimStart(new char[] { '.', ' ' }));
FileTypes = new BinaryFileTypeService(lookupContext).Queryable().AsNoTracking().ToList();
// get all the types we'll be importing
var binaryTypeSettings = ConfigurationManager.GetSection("binaryFileTypes") as NameValueCollection;
// create any custom types defined in settings that don't exist yet
foreach (var typeKey in binaryTypeSettings.AllKeys)
{
if (!FileTypes.Any(f => f.Name == typeKey))
{
var newFileType = new BinaryFileType();
lookupContext.BinaryFileTypes.Add(newFileType);
newFileType.Name = typeKey;
newFileType.Description = typeKey;
newFileType.AllowCaching = true;
var typeValue = binaryTypeSettings[typeKey];
if (typeValue != null)
{
// #TODO: support additional storage types (like AWS?)
newFileType.StorageEntityTypeId = typeValue.Equals("Database") ? DatabaseStorageTypeId : FileSystemStorageTypeId;
newFileType.Attributes = new Dictionary <string, AttributeCache>();
newFileType.AttributeValues = new Dictionary <string, AttributeValueCache>();
// save changes to binary type to get an ID
lookupContext.SaveChanges();
var newRootPath = new AttributeValue()
{
AttributeId = RootPathAttribute.Id,
EntityId = newFileType.Id,
Value = typeValue
};
newFileType.Attributes.Add(RootPathAttribute.Key, RootPathAttribute);
newFileType.AttributeValues.Add(RootPathAttribute.Key, new AttributeValueCache(newRootPath));
// save attribute values with the current type ID
lookupContext.AttributeValues.Add(newRootPath);
}
lookupContext.SaveChanges();
FileTypes.Add(newFileType);
}
}
// load attributes on file system types to get the default storage location
foreach (var type in FileTypes)
{
type.LoadAttributes(lookupContext);
if (type.StorageEntityTypeId == FileSystemStorageTypeId && binaryTypeSettings.AllKeys.Any(k => type.Name.Equals(k)))
{
// override the configured storage location since we can't handle relative paths
type.AttributeValues["RootPath"].Value = binaryTypeSettings[type.Name];
}
}
// get a list of all the imported people keys
var personAliasList = new PersonAliasService(lookupContext).Queryable().AsNoTracking().ToList();
ImportedPeople = personAliasList
.Where(pa => pa.ForeignKey != null)
.Select(pa =>
new PersonKeys()
{
PersonAliasId = pa.Id,
PersonId = pa.PersonId,
PersonForeignId = pa.ForeignId,
PersonForeignKey = pa.ForeignKey
}).ToList();
}
/// <summary>
/// Shows the view.
/// </summary>
protected void ShowView()
{
var rockContext = new RockContext();
int resultCount = Int32.Parse(GetAttributeValue("ResultCount"));
int pageNumber = 0;
if (!String.IsNullOrEmpty(PageParameter("page")))
{
pageNumber = Int32.Parse(PageParameter("page"));
}
var skipCount = pageNumber * resultCount;
var query = new NcoaHistoryService(rockContext).Queryable();
var processed = gfNcoaFilter.GetUserPreference("Processed").ConvertToEnumOrNull <Processed>();
if (processed.HasValue)
{
if (processed.Value != Processed.All && processed.Value != Processed.ManualUpdateRequiredOrNotProcessed)
{
query = query.Where(i => i.Processed == processed);
}
else if (processed.Value == Processed.ManualUpdateRequiredOrNotProcessed)
{
query = query.Where(i => i.Processed == Processed.ManualUpdateRequired || i.Processed == Processed.NotProcessed);
}
}
var moveDateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(gfNcoaFilter.GetUserPreference("Move Date"));
if (moveDateRange.Start.HasValue)
{
query = query.Where(e => e.MoveDate.HasValue && e.MoveDate.Value >= moveDateRange.Start.Value);
}
if (moveDateRange.End.HasValue)
{
query = query.Where(e => e.MoveDate.HasValue && e.MoveDate.Value < moveDateRange.End.Value);
}
var ncoaDateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(gfNcoaFilter.GetUserPreference("NCOA Processed Date"));
if (ncoaDateRange.Start.HasValue)
{
query = query.Where(e => e.NcoaRunDateTime >= ncoaDateRange.Start.Value);
}
if (ncoaDateRange.End.HasValue)
{
query = query.Where(e => e.NcoaRunDateTime < ncoaDateRange.End.Value);
}
var moveType = gfNcoaFilter.GetUserPreference("Move Type").ConvertToEnumOrNull <MoveType>();
if (moveType.HasValue)
{
query = query.Where(i => i.MoveType == moveType);
}
var addressStatus = gfNcoaFilter.GetUserPreference("Address Status").ConvertToEnumOrNull <AddressStatus>();
if (addressStatus.HasValue)
{
query = query.Where(i => i.AddressStatus == addressStatus);
}
var addressInvalidReason = gfNcoaFilter.GetUserPreference("Address Invalid Reason").ConvertToEnumOrNull <AddressInvalidReason>();
if (addressInvalidReason.HasValue)
{
query = query.Where(i => i.AddressInvalidReason == addressInvalidReason);
}
decimal?moveDistance = gfNcoaFilter.GetUserPreference("Move Distance").AsDecimalOrNull();
if (moveDistance.HasValue)
{
query = query.Where(i => i.MoveDistance <= moveDistance.Value);
}
string lastName = gfNcoaFilter.GetUserPreference("Last Name");
if (!string.IsNullOrWhiteSpace(lastName))
{
var personAliasQuery = new PersonAliasService(rockContext)
.Queryable()
.Where(p =>
p.Person != null &&
p.Person.LastName.Contains(lastName))
.Select(p => p.Id);
query = query.Where(i => personAliasQuery.Contains(i.PersonAliasId));
}
var campusId = gfNcoaFilter.GetUserPreference("Campus").AsIntegerOrNull();
if (campusId.HasValue)
{
var familyGroupType = GroupTypeCache.Get(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid());
var personAliasQuery = new PersonAliasService(rockContext).Queryable().AsNoTracking();
var campusQuery = new GroupMemberService(rockContext)
.Queryable().AsNoTracking()
.Where(m =>
m.Group.GroupTypeId == familyGroupType.Id &&
m.Group.CampusId.HasValue &&
m.Group.CampusId.Value == campusId.Value)
.Select(m => m.PersonId)
.Join(personAliasQuery, m => m, p => p.PersonId, (m, p) => p.Id);
query = query.Where(i => campusQuery.Contains(i.PersonAliasId));
}
var filteredRecords = query.ToList();
lTotal.Text = string.Format("Records: {0}", filteredRecords.Count());
#region Grouping rows
var ncoaRows = filteredRecords
.Where(a => a.MoveType != MoveType.Individual)
.GroupBy(a => new { a.FamilyId, a.MoveType, a.MoveDate })
.Select(a => new NcoaRow
{
Id = a.Select(b => b.Id).Max(),
FamilyMemberPersonAliasIds = a.Select(b => b.PersonAliasId).ToList()
}).ToList();
var ncoaIndividualRows = filteredRecords
.Where(a => a.MoveType == MoveType.Individual)
.Select(a => new NcoaRow
{
Id = a.Id,
IndividualPersonAliasId = a.PersonAliasId
}).ToList();
ncoaRows.AddRange(ncoaIndividualRows);
#endregion
var pagedNcoaRows = ncoaRows.OrderBy(a => a.Id).Skip(skipCount).Take(resultCount + 1).ToList();
var familyMemberPersonAliasIds = pagedNcoaRows.SelectMany(r => r.FamilyMemberPersonAliasIds).ToList();
var individualPersonAliasIds = pagedNcoaRows.Select(r => r.IndividualPersonAliasId).ToList();
var people = new PersonAliasService(rockContext)
.Queryable().AsNoTracking()
.Where(p =>
familyMemberPersonAliasIds.Contains(p.Id) ||
individualPersonAliasIds.Contains(p.Id))
.Select(p => new
{
PersonAliasId = p.Id,
Person = p.Person
})
.ToList();
foreach (var ncoaRow in pagedNcoaRows)
{
ncoaRow.FamilyMembers = people
.Where(p => ncoaRow.FamilyMemberPersonAliasIds.Contains(p.PersonAliasId))
.Select(p => p.Person)
.ToList();
ncoaRow.Individual = people
.Where(p => p.PersonAliasId == ncoaRow.IndividualPersonAliasId)
.Select(p => p.Person)
.FirstOrDefault();
var ncoaHistoryRecord = filteredRecords.Single(a => a.Id == ncoaRow.Id);
ncoaRow.OriginalAddress = FormattedAddress(ncoaHistoryRecord.OriginalStreet1, ncoaHistoryRecord.OriginalStreet2,
ncoaHistoryRecord.OriginalCity, ncoaHistoryRecord.OriginalState, ncoaHistoryRecord.OriginalPostalCode)
.ConvertCrLfToHtmlBr();
ncoaRow.Status = ncoaHistoryRecord.Processed == Processed.Complete ? "Processed" : "Not Processed";
ncoaRow.StatusCssClass = ncoaHistoryRecord.Processed == Processed.Complete ? "label-success" : "label-default";
ncoaRow.ShowButton = false;
var family = new GroupService(rockContext).Get(ncoaHistoryRecord.FamilyId);
var person = ncoaRow.Individual ?? ncoaRow.FamilyMembers.First();
if (family == null)
{
family = person.GetFamily(rockContext);
}
var personService = new PersonService(rockContext);
ncoaRow.FamilyName = family.Name;
ncoaRow.HeadOftheHousehold = personService.GetHeadOfHousehold(person, family);
if (ncoaHistoryRecord.MoveType != MoveType.Individual)
{
ncoaRow.FamilyMembers = personService.GetFamilyMembers(family, person.Id, true).Select(a => a.Person).ToList();
}
else
{
ncoaRow.FamilyMembers = personService.GetFamilyMembers(family, person.Id, false).Select(a => a.Person).ToList();
}
if (ncoaHistoryRecord.AddressStatus == AddressStatus.Invalid)
{
ncoaRow.TagLine = "Invalid Address";
ncoaRow.TagLineCssClass = "label-warning";
if (ncoaHistoryRecord.Processed != Processed.Complete)
{
ncoaRow.CommandName = "MarkAddressAsPrevious";
ncoaRow.CommandText = "Mark Address As Previous";
ncoaRow.ShowButton = true;
}
}
if (ncoaHistoryRecord.NcoaType == NcoaType.Month48Move)
{
ncoaRow.TagLine = "48 Month Move";
ncoaRow.TagLineCssClass = "label-info";
if (ncoaHistoryRecord.Processed != Processed.Complete)
{
ncoaRow.CommandName = "MarkAddressAsPrevious";
ncoaRow.CommandText = "Mark Address As Previous";
ncoaRow.ShowButton = true;
}
}
if (ncoaHistoryRecord.NcoaType == NcoaType.Move)
{
ncoaRow.TagLine = ncoaHistoryRecord.MoveType.ConvertToString();
ncoaRow.TagLineCssClass = "label-success";
ncoaRow.MoveDate = ncoaHistoryRecord.MoveDate;
ncoaRow.MoveDistance = ncoaHistoryRecord.MoveDistance;
ncoaRow.NewAddress = FormattedAddress(ncoaHistoryRecord.UpdatedStreet1, ncoaHistoryRecord.UpdatedStreet2,
ncoaHistoryRecord.UpdatedCity, ncoaHistoryRecord.UpdatedState, ncoaHistoryRecord.UpdatedPostalCode)
.ConvertCrLfToHtmlBr();
if (ncoaHistoryRecord.Processed != Processed.Complete)
{
ncoaRow.CommandText = "Mark Processed";
ncoaRow.CommandName = "MarkProcessed";
ncoaRow.ShowButton = true;
}
}
}
rptNcoaResultsFamily.DataSource = pagedNcoaRows.Take(resultCount).GroupBy(n => n.FamilyName);
rptNcoaResultsFamily.DataBind();
if (pagedNcoaRows.Count() > resultCount)
{
hlNext.Visible = hlNext.Enabled = true;
Dictionary <string, string> queryStringNext = new Dictionary <string, string>();
queryStringNext.Add("page", (pageNumber + 1).ToString());
var pageReferenceNext = new Rock.Web.PageReference(CurrentPageReference.PageId, CurrentPageReference.RouteId, queryStringNext);
hlNext.NavigateUrl = pageReferenceNext.BuildUrl();
}
else
{
hlNext.Visible = hlNext.Enabled = false;
}
// build prev button
if (pageNumber == 0)
{
hlPrev.Visible = hlPrev.Enabled = false;
}
else
{
hlPrev.Visible = hlPrev.Enabled = true;
Dictionary <string, string> queryStringPrev = new Dictionary <string, string>();
queryStringPrev.Add("page", (pageNumber - 1).ToString());
var pageReferencePrev = new Rock.Web.PageReference(CurrentPageReference.PageId, CurrentPageReference.RouteId, queryStringPrev);
hlPrev.NavigateUrl = pageReferencePrev.BuildUrl();
}
}