/// <summary>
/// Gets the transaction dates for the specified person.
/// </summary>
/// <param name="achievementTypeCache">The achievementTypeCache.</param>
/// <param name="personId">The person alias identifier.</param>
/// <param name="minDate">The minimum date.</param>
/// <param name="maxDate">The maximum date.</param>
/// <returns></returns>
private List <DateTime?> GetOrderedFinancialTransactionDatesByPerson(AchievementTypeCache achievementTypeCache, int personId, DateTime minDate, DateTime maxDate)
{
var rockContext = new RockContext();
var query = GetSourceEntitiesQuery(achievementTypeCache, rockContext) as IQueryable <FinancialTransaction>;
var dayAfterMaxDate = maxDate.AddDays(1);
// Get the Person's Giving ID
var givingId = new PersonService(rockContext).GetSelect(personId, p => p.GivingId);
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasQuery = new PersonAliasService(rockContext)
.Queryable()
.AsNoTracking()
.Where(pa => pa.Person.GivingId == givingId)
.Select(pa => pa.Id);
return(query
.AsNoTracking()
.Where(i =>
i.AuthorizedPersonAliasId.HasValue &&
personAliasQuery.Contains(i.AuthorizedPersonAliasId.Value) &&
i.TransactionDateTime >= minDate &&
i.TransactionDateTime < dayAfterMaxDate)
.Select(i => i.TransactionDateTime)
.OrderBy(d => d)
.ToList());
}
/// <summary>
/// Shows the giving alerts
/// </summary>
private void ShowGivingAlerts()
{
var rockContext = new RockContext();
var financialTransactionAlertService = new FinancialTransactionAlertService(rockContext);
var givingId = Person.GivingId;
var givingIdPersonAliasIdQuery = new PersonAliasService(rockContext).Queryable().Where(a => a.Person.GivingId == givingId).Select(a => a.Id);
var financialTransactionAlertQry = financialTransactionAlertService.Queryable().AsNoTracking()
.Where(a => givingIdPersonAliasIdQuery.Contains(a.PersonAliasId));
var financialTransactionGratitudeCount = financialTransactionAlertQry.Where(a => a.FinancialTransactionAlertType.AlertType == AlertType.Gratitude).Count();
var financialTransactionFollowupCount = financialTransactionAlertQry.Where(a => a.FinancialTransactionAlertType.AlertType == AlertType.FollowUp).Count();
var alertListUrl = LinkedPageUrl(AttributeKey.AlertListPage, new Dictionary <string, string> {
{ "PersonGuid", Person.Guid.ToString() }
});
var hasAlertListLink = !alertListUrl.IsNullOrWhiteSpace();
var givingAlertsBadges = $"<span class=\"badge badge-success align-text-bottom\">{financialTransactionGratitudeCount}</span> <span class=\"badge badge-warning align-text-bottom\">{financialTransactionFollowupCount}</span>";
if (hasAlertListLink)
{
lGivingAlertsBadgesHtml.Text = $"<a href=\"{alertListUrl}\">" + givingAlertsBadges + "</a>";
}
else
{
lGivingAlertsBadgesHtml.Text = givingAlertsBadges;
}
}
public IQueryable <FinancialTransaction> GetByGivingId(string givingId)
{
if (string.IsNullOrWhiteSpace(givingId) || !(givingId.StartsWith("P") || givingId.StartsWith("G")))
{
var response = new HttpResponseMessage(HttpStatusCode.BadRequest);
response.Content = new StringContent("The supplied givingId is not valid");
throw new HttpResponseException(response);
}
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService((RockContext)this.Service.Context).Queryable().Where(a => a.Person.GivingId == givingId).Select(a => a.Id).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
return(Get().Where(t => t.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains(t.AuthorizedPersonAliasId.Value)));
}
/// <summary>
/// Adds the family members to the group.
/// </summary>
/// <param name="personAliasGuids">The person alias unique identifiers to be added.</param>
/// <param name="group">The group to add the people to.</param>
/// <param name="rockContext">The rock context.</param>
/// <returns>A list of <see cref="GroupMember"/> objects that were added to the group.</returns>
private List <GroupMember> AddFamilyMembersToGroup(List <Guid> personAliasGuids, Group group, RockContext rockContext)
{
// Abort early if we don't have a person or don't allow family members.
if (RequestContext.CurrentPerson == null || FamilyOptions != FamilyOption.FamilyMemberSelection)
{
return(new List <GroupMember>());
}
// If there are no person aliases to add then also abort.
if (personAliasGuids == null || personAliasGuids.Count == 0)
{
return(new List <GroupMember>());
}
// This is all a bit round-about, but it's to make sure
// they can't just send us arbitrary person alias guids and
// have us add them to the group.
var validFamilyMembers = RequestContext.CurrentPerson
.GetFamilyMembers(false, rockContext)
.Select(a => a.Person)
.ToList();
// Get all the person identifiers the client wants to add.
var parameterPersonIds = new PersonAliasService(rockContext).Queryable()
.Where(a => personAliasGuids.Contains(a.Guid))
.Select(a => a.PersonId)
.Distinct()
.ToList();
// Convert the person Ids back into the full Person objects.
var familyMembersToAdd = validFamilyMembers
.Where(a => parameterPersonIds.Contains(a.Id))
.ToList();
var members = new List <GroupMember>();
// Loop through each person and add them to the group.
foreach (var person in familyMembersToAdd)
{
members.Add(AddPersonToGroup(person, group, rockContext));
}
return(members);
}
/// <summary>
/// Renders the specified writer.
/// </summary>
/// <param name="badge">The badge.</param>
/// <param name="writer">The writer.</param>
public override void Render(BadgeCache badge, System.Web.UI.HtmlTextWriter writer)
{
if (Person == null)
{
return;
}
var accountGuids = this.GetAttributeValue(badge, "Accounts")?.SplitDelimitedValues().AsGuidList();
var minimumAmount = this.GetAttributeValue(badge, "MinimumAmount")?.AsDecimalOrNull();
var slidingDateRangeDelimitedValues = this.GetAttributeValue(badge, "DateRange");
var lavaTemplate = this.GetAttributeValue(badge, "LavaTemplate");
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(slidingDateRangeDelimitedValues);
var dateRangeSummary = SlidingDateRangePicker.FormatDelimitedValues(slidingDateRangeDelimitedValues);
var mergeFields = Lava.LavaHelper.GetCommonMergeFields(null, null, new Lava.CommonMergeFieldsOptions {
GetLegacyGlobalMergeFields = false
});
mergeFields.Add("Person", this.Person);
using (var rockContext = new RockContext())
{
mergeFields.Add("Badge", badge);
mergeFields.Add("DateRange", new { Dates = dateRange, Summary = dateRangeSummary });
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService(rockContext).Queryable().Where(a => a.Person.GivingId == this.Person.GivingId).Select(a => a.Id).ToList();
var transactionTypeContributionValueId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid()).Id;
var qry = new FinancialTransactionService(rockContext).Queryable().Where(a => a.TransactionTypeValueId == transactionTypeContributionValueId);
// get the transactions for the person or all the members in the person's giving group (Family)
qry = qry.Where(t => t.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains(t.AuthorizedPersonAliasId.Value));
if (dateRange.Start.HasValue)
{
qry = qry.Where(t => t.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
qry = qry.Where(t => t.TransactionDateTime < dateRange.End.Value);
}
if (minimumAmount.HasValue)
{
qry = qry.Where(a => a.TransactionDetails.Sum(d => ( decimal? )d.Amount) > minimumAmount);
}
if (accountGuids.Any())
{
qry = qry.Where(t => t.TransactionDetails.Any(d => accountGuids.Contains(d.Account.Guid)));
}
var contributionList = qry.Select(a => new
{
a.TransactionDateTime,
ContributionAmount = a.TransactionDetails.Sum(d => ( decimal? )d.Amount)
}).ToList();
if (contributionList.Any())
{
var contributionResult = new
{
Count = contributionList.Count(),
LastDateTime = contributionList.Max(a => a.TransactionDateTime),
TotalAmount = contributionList.Sum(a => a.ContributionAmount),
};
mergeFields.Add("Contributions", contributionResult);
}
string output = lavaTemplate.ResolveMergeFields(mergeFields);
writer.Write(output);
}
}
private void DisplayResults()
{
RockContext rockContext = new RockContext();
var statementYear = RockDateTime.Now.Year;
if (Request["StatementYear"] != null)
{
Int32.TryParse(Request["StatementYear"].ToString(), out statementYear);
}
FinancialTransactionDetailService financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
Person targetPerson = CurrentPerson;
List <Guid> excludedCurrencyTypes = new List <Guid>();
if (!string.IsNullOrWhiteSpace(GetAttributeValue("ExcludedCurrencyTypes")))
{
excludedCurrencyTypes = GetAttributeValue("ExcludedCurrencyTypes").Split(',').Select(Guid.Parse).ToList();
}
if (GetAttributeValue("AllowPersonQuerystring").AsBoolean())
{
if (!string.IsNullOrWhiteSpace(Request["PersonGuid"]))
{
Guid?personGuid = Request["PersonGuid"].AsGuidOrNull();
if (personGuid.HasValue)
{
var person = new PersonService(rockContext).Get(personGuid.Value);
if (person != null)
{
targetPerson = person;
}
}
}
}
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService(rockContext).Queryable().Where(a => a.Person.GivingId == targetPerson.GivingId).Select(a => a.Id).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
var qry = financialTransactionDetailService.Queryable().AsNoTracking()
.Where(t => t.Transaction.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains(t.Transaction.AuthorizedPersonAliasId.Value));
qry = qry.Where(t => t.Transaction.TransactionDateTime.Value.Year == statementYear);
if (string.IsNullOrWhiteSpace(GetAttributeValue("Accounts")))
{
qry = qry.Where(t => t.Account.IsTaxDeductible);
}
else
{
var accountGuids = GetAttributeValue("Accounts").Split(',').Select(Guid.Parse).ToList();
qry = qry.Where(t => accountGuids.Contains(t.Account.Guid));
}
if (excludedCurrencyTypes.Count > 0)
{
qry = qry.Where(t => !excludedCurrencyTypes.Contains(t.Transaction.FinancialPaymentDetail.CurrencyTypeValue.Guid));
}
qry = qry.OrderByDescending(t => t.Transaction.TransactionDateTime);
var mergeFields = new Dictionary <string, object>();
mergeFields.Add("StatementStartDate", "1/1/" + statementYear.ToString());
if (statementYear == RockDateTime.Now.Year)
{
mergeFields.Add("StatementEndDate", RockDateTime.Now);
}
else
{
mergeFields.Add("StatementEndDate", "12/31/" + statementYear.ToString());
}
var familyGroupTypeId = GroupTypeCache.Read(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY).Id;
var groupMemberQry = new GroupMemberService(rockContext).Queryable().Where(m => m.Group.GroupTypeId == familyGroupTypeId);
// get giving group members in order by family role (adult -> child) and then gender (male -> female)
var givingGroup = new PersonService(rockContext).Queryable().AsNoTracking()
.Where(p => p.GivingId == targetPerson.GivingId)
.GroupJoin(
groupMemberQry,
p => p.Id,
m => m.PersonId,
(p, m) => new { p, m })
.SelectMany(x => x.m.DefaultIfEmpty(), (y, z) => new { Person = y.p, GroupMember = z })
.Select(p => new { FirstName = p.Person.NickName, LastName = p.Person.LastName, FamilyRoleOrder = p.GroupMember.GroupRole.Order, Gender = p.Person.Gender, PersonId = p.Person.Id })
.DistinctBy(p => p.PersonId)
.OrderBy(p => p.FamilyRoleOrder).ThenBy(p => p.Gender)
.ToList();
string salutation = string.Empty;
if (givingGroup.GroupBy(g => g.LastName).Count() == 1)
{
salutation = string.Join(", ", givingGroup.Select(g => g.FirstName)) + " " + givingGroup.FirstOrDefault().LastName;
if (salutation.Contains(","))
{
salutation = salutation.ReplaceLastOccurrence(",", " &");
}
}
else
{
salutation = string.Join(", ", givingGroup.Select(g => g.FirstName + " " + g.LastName));
if (salutation.Contains(","))
{
salutation = salutation.ReplaceLastOccurrence(",", " &");
}
}
mergeFields.Add("Salutation", salutation);
var homeAddress = targetPerson.GetHomeLocation();
if (homeAddress != null)
{
mergeFields.Add("StreetAddress1", homeAddress.Street1);
mergeFields.Add("StreetAddress2", homeAddress.Street2);
mergeFields.Add("City", homeAddress.City);
mergeFields.Add("State", homeAddress.State);
mergeFields.Add("PostalCode", homeAddress.PostalCode);
mergeFields.Add("Country", homeAddress.Country);
}
else
{
mergeFields.Add("StreetAddress1", string.Empty);
mergeFields.Add("StreetAddress2", string.Empty);
mergeFields.Add("City", string.Empty);
mergeFields.Add("State", string.Empty);
mergeFields.Add("PostalCode", string.Empty);
mergeFields.Add("Country", string.Empty);
}
mergeFields.Add("TransactionDetails", qry.ToList());
mergeFields.Add("AccountSummary", qry.GroupBy(t => t.Account.Name).Select(s => new AccountSummary {
AccountName = s.Key, Total = s.Sum(a => a.Amount), Order = s.Max(a => a.Account.Order)
}).OrderBy(s => s.Order));
// pledge information
var pledges = new FinancialPledgeService(rockContext).Queryable().AsNoTracking()
.Where(p => p.PersonAliasId.HasValue && personAliasIds.Contains(p.PersonAliasId.Value))
.GroupBy(p => p.Account)
.Select(g => new PledgeSummary
{
AccountId = g.Key.Id,
AccountName = g.Key.Name,
AmountPledged = g.Sum(p => p.TotalAmount),
PledgeStartDate = g.Min(p => p.StartDate),
PledgeEndDate = g.Max(p => p.EndDate)
})
.ToList();
// add detailed pledge information
foreach (var pledge in pledges)
{
var adjustedPedgeEndDate = pledge.PledgeEndDate.Value.Date.AddDays(1);
pledge.AmountGiven = new FinancialTransactionDetailService(rockContext).Queryable()
.Where(t =>
t.AccountId == pledge.AccountId &&
t.Transaction.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains(t.Transaction.AuthorizedPersonAliasId.Value) &&
t.Transaction.TransactionDateTime >= pledge.PledgeStartDate &&
t.Transaction.TransactionDateTime < adjustedPedgeEndDate)
.Sum(t => ( decimal? )t.Amount) ?? 0;
pledge.AmountRemaining = (pledge.AmountGiven > pledge.AmountPledged) ? 0 : (pledge.AmountPledged - pledge.AmountGiven);
if (pledge.AmountPledged > 0)
{
var test = (double)pledge.AmountGiven / (double)pledge.AmountPledged;
pledge.PercentComplete = (int)((pledge.AmountGiven * 100) / pledge.AmountPledged);
}
}
mergeFields.Add("Pledges", pledges);
var template = GetAttributeValue("LavaTemplate");
lResults.Text = template.ResolveMergeFields(mergeFields);
// show debug info
if (GetAttributeValue("EnableDebug").AsBoolean() && IsUserAuthorized(Authorization.EDIT))
{
lDebug.Visible = true;
lDebug.Text = mergeFields.lavaDebugInfo();
}
}
private void DisplayResults()
{
RockContext rockContext = new RockContext();
var statementYear = RockDateTime.Now.Year;
if (PageParameter("StatementYear").IsNotNullOrWhiteSpace())
{
Int32.TryParse(PageParameter("StatementYear"), out statementYear);
}
FinancialTransactionDetailService financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
Person targetPerson = CurrentPerson;
// get excluded currency types setting
List <Guid> excludedCurrencyTypes = new List <Guid>();
if (GetAttributeValue("ExcludedCurrencyTypes").IsNotNullOrWhiteSpace())
{
excludedCurrencyTypes = GetAttributeValue("ExcludedCurrencyTypes").Split(',').Select(Guid.Parse).ToList();
}
var personGuid = PageParameter("PersonGuid").AsGuidOrNull();
if (personGuid.HasValue)
{
// if "AllowPersonQueryString is False", only use the PersonGuid if it is a Guid of one of the current person's businesses
var isCurrentPersonsBusiness = targetPerson != null && targetPerson.GetBusinesses().Any(b => b.Guid == personGuid.Value);
if (GetAttributeValue("AllowPersonQuerystring").AsBoolean() || isCurrentPersonsBusiness)
{
var person = new PersonService(rockContext).Get(personGuid.Value);
if (person != null)
{
targetPerson = person;
}
}
}
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService(rockContext).Queryable().Where(a => a.Person.GivingId == targetPerson.GivingId).Select(a => a.Id).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
var qry = financialTransactionDetailService.Queryable().AsNoTracking()
.Where(t => t.Transaction.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains(t.Transaction.AuthorizedPersonAliasId.Value));
qry = qry.Where(t => t.Transaction.TransactionDateTime.Value.Year == statementYear);
if (string.IsNullOrWhiteSpace(GetAttributeValue("Accounts")))
{
qry = qry.Where(t => t.Account.IsTaxDeductible);
}
else
{
var accountGuids = GetAttributeValue("Accounts").Split(',').Select(Guid.Parse).ToList();
qry = qry.Where(t => accountGuids.Contains(t.Account.Guid));
}
if (excludedCurrencyTypes.Count > 0)
{
qry = qry.Where(t => !excludedCurrencyTypes.Contains(t.Transaction.FinancialPaymentDetail.CurrencyTypeValue.Guid));
}
qry = qry.OrderByDescending(t => t.Transaction.TransactionDateTime);
var mergeFields = new Dictionary <string, object>();
mergeFields.Add("StatementStartDate", "1/1/" + statementYear.ToString());
if (statementYear == RockDateTime.Now.Year)
{
mergeFields.Add("StatementEndDate", RockDateTime.Now);
}
else
{
mergeFields.Add("StatementEndDate", "12/31/" + statementYear.ToString());
}
var familyGroupTypeId = GroupTypeCache.Get(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY).Id;
var groupMemberQry = new GroupMemberService(rockContext).Queryable().Where(m => m.Group.GroupTypeId == familyGroupTypeId);
// get giving group members in order by family role (adult -> child) and then gender (male -> female)
var givingGroup = new PersonService(rockContext).Queryable().AsNoTracking()
.Where(p => p.GivingId == targetPerson.GivingId)
.GroupJoin(
groupMemberQry,
p => p.Id,
m => m.PersonId,
(p, m) => new { p, m })
.SelectMany(x => x.m.DefaultIfEmpty(), (y, z) => new { Person = y.p, GroupMember = z })
.Select(p => new { FirstName = p.Person.NickName, LastName = p.Person.LastName, FamilyRoleOrder = p.GroupMember.GroupRole.Order, Gender = p.Person.Gender, PersonId = p.Person.Id })
.DistinctBy(p => p.PersonId)
.OrderBy(p => p.FamilyRoleOrder).ThenBy(p => p.Gender)
.ToList();
string salutation = string.Empty;
if (givingGroup.GroupBy(g => g.LastName).Count() == 1)
{
salutation = string.Join(", ", givingGroup.Select(g => g.FirstName)) + " " + givingGroup.FirstOrDefault().LastName;
if (salutation.Contains(","))
{
salutation = salutation.ReplaceLastOccurrence(",", " &");
}
}
else
{
salutation = string.Join(", ", givingGroup.Select(g => g.FirstName + " " + g.LastName));
if (salutation.Contains(","))
{
salutation = salutation.ReplaceLastOccurrence(",", " &");
}
}
mergeFields.Add("Salutation", salutation);
var mailingAddress = targetPerson.GetMailingLocation();
if (mailingAddress != null)
{
mergeFields.Add("StreetAddress1", mailingAddress.Street1);
mergeFields.Add("StreetAddress2", mailingAddress.Street2);
mergeFields.Add("City", mailingAddress.City);
mergeFields.Add("State", mailingAddress.State);
mergeFields.Add("PostalCode", mailingAddress.PostalCode);
mergeFields.Add("Country", mailingAddress.Country);
}
else
{
mergeFields.Add("StreetAddress1", string.Empty);
mergeFields.Add("StreetAddress2", string.Empty);
mergeFields.Add("City", string.Empty);
mergeFields.Add("State", string.Empty);
mergeFields.Add("PostalCode", string.Empty);
mergeFields.Add("Country", string.Empty);
}
mergeFields.Add("TransactionDetails", qry.ToList());
mergeFields.Add("AccountSummary", qry.GroupBy(t => new { t.Account.Name, t.Account.PublicName, t.Account.Description })
.Select(s => new AccountSummary
{
AccountName = s.Key.Name,
PublicName = s.Key.PublicName,
Description = s.Key.Description,
Total = s.Sum(a => a.Amount),
Order = s.Max(a => a.Account.Order)
})
.OrderBy(s => s.Order));
// pledge information
if (GetAttributeValue("DisplayPledges").AsBoolean())
{
List <PledgeSummary> pledges = GetPledgeDataForPersonYear(rockContext, statementYear, personAliasIds);
mergeFields.Add("Pledges", pledges);
}
var template = GetAttributeValue("LavaTemplate");
lResults.Text = template.ResolveMergeFields(mergeFields);
}
/// <summary>
/// Checks to see if the PersonAlias entity should be added to the FollowingSuggestion table. If the entity is not a PersonAlias then addSuggestion will be true.
/// If the Entity is a PersonAlias then this method will check if the associated Person has another PersonAlias that is being followed.
/// If there is another PersonAlias being followed then the Following and FollowingSuggestion are updated with this EntityId (PersonAliasId)
/// which is the PrimaryPersonAliasId.
/// </summary>
/// <param name="followerPersonId">The follower person identifier.</param>
/// <param name="suggestionTypeComponent">The suggestion type component.</param>
/// <param name="entityIdToBeSavedAsSuggestions">The entity ids.</param>
/// <param name="entityTypeId">The entity type identifier.</param>
/// <param name="suggestionContext">The suggestion context.</param>
/// <param name="followedEntityId">The followed entity identifier.</param>
/// <param name="addSuggestion">if set to <c>true</c> then the suggestion should be inserted.</param>
/// <returns>
/// True if the PersonAlias following suggestion should be inserted or if the EntityType is not a PersonAlias.
/// </returns>
private void ProcessFollowingSuggestionAndPersonAliasEntity(int followerPersonId, SuggestionTypeComponent suggestionTypeComponent, List <int> entityIdToBeSavedAsSuggestions, int entityTypeId, RockContext suggestionContext, int followedEntityId, out bool addSuggestion)
{
addSuggestion = true;
// If the Entity is not a PersonAlias no other checks are needed just return with addSuggestion = true
if (!IsEntityTypePersonAlias(entityTypeId))
{
return;
}
// since this is a person alias see if a different alias is being used to follow
var followedPersonId = new PersonAliasService(suggestionContext).Get(followedEntityId).PersonId;
var followedPersonAliases = new PersonAliasService(suggestionContext)
.Queryable()
.Where(a => a.PersonId == followedPersonId && a.Id != followedEntityId)
.Select(a => a.Id)
.ToList();
if (!followedPersonAliases.Any())
{
// There are no alternate person alias' there is nothing else to check so just return with addSuggestion = true
return;
}
int existingFollowingPersonAliasId = suggestionTypeComponent.ExistingFollowings
.GetValueOrDefault(followerPersonId, new List <int>())
.Where(f => followedPersonAliases.Contains(f))
.FirstOrDefault();
// Update the existing following record to use the primary PersonAlias ID and remove it from the list.
if (existingFollowingPersonAliasId != 0)
{
var personAliasEntityTypeId = EntityTypeCache.GetId(Rock.SystemGuid.EntityType.PERSON_ALIAS) ?? 0;
addSuggestion = false;
entityIdToBeSavedAsSuggestions.Remove(followedEntityId);
using (var followingContext = new RockContext())
{
var following = new FollowingService(followingContext)
.GetByEntityAndPerson(personAliasEntityTypeId, existingFollowingPersonAliasId, followerPersonId)
.FirstOrDefault();
following.EntityId = followedEntityId;
var suggested = new FollowingSuggestedService(followingContext)
.GetByEntityAndPerson(personAliasEntityTypeId, existingFollowingPersonAliasId, followerPersonId)
.FirstOrDefault();
if (suggested != null)
{
// Get the Followed Person's Primary PersonAliasId from FollowingSuggestions if it exists, PersonEntitySuggestions is already filtered for EntityType
int existingFollowingSuggestedPrimaryPersonAliasId = suggestionTypeComponent.PersonEntitySuggestions
.Where(s => s.PersonId == followerPersonId && s.EntityId == followedEntityId)
.Select(s => s.EntityId)
.FirstOrDefault();
if (existingFollowingSuggestedPrimaryPersonAliasId != 0)
{
// Since the Primary PersonAlias is already in FollowingSuggestions then just delete this one as a duplicate
new FollowingSuggestedService(followingContext).Delete(suggested);
}
else
{
// Update the outdated PersonAliasId to the primary PersonAliasId which is the followedEntityId
suggested.EntityId = followedEntityId;
}
}
followingContext.SaveChanges();
}
}
}
/// <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();
}
}
public static void AddMergeFields(Dictionary <string, object> mergeFields, Person targetPerson, DateRange dateRange, List <Guid> excludedCurrencyTypes, List <Guid> accountGuids = null)
{
RockContext rockContext = new RockContext();
FinancialTransactionDetailService financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
// fetch all the possible PersonAliasIds that have this GivingID to help optimize the SQL
var personAliasIds = new PersonAliasService(rockContext).Queryable().Where(a => a.Person.GivingId == targetPerson.GivingId).Select(a => a.Id).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
var qry = financialTransactionDetailService.Queryable().AsNoTracking()
.Where(t => t.Transaction.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains(t.Transaction.AuthorizedPersonAliasId.Value));
qry = qry.Where(t => t.Transaction.TransactionDateTime.Value >= dateRange.Start && t.Transaction.TransactionDateTime.Value <= dateRange.End);
if (accountGuids == null)
{
qry = qry.Where(t => t.Account.IsTaxDeductible);
}
else
{
qry = qry.Where(t => accountGuids.Contains(t.Account.Guid));
}
var excludedQry = qry;
if (excludedCurrencyTypes.Count > 0)
{
qry = qry.Where(t => !excludedCurrencyTypes.Contains(t.Transaction.FinancialPaymentDetail.CurrencyTypeValue.Guid));
excludedQry = excludedQry.Where(t => excludedCurrencyTypes.Contains(t.Transaction.FinancialPaymentDetail.CurrencyTypeValue.Guid));
excludedQry = excludedQry.OrderByDescending(t => t.Transaction.TransactionDateTime);
}
qry = qry.OrderByDescending(t => t.Transaction.TransactionDateTime);
mergeFields.Add("StatementStartDate", dateRange.Start?.ToShortDateString());
mergeFields.Add("StatementEndDate", dateRange.End?.ToShortDateString());
var familyGroupTypeId = GroupTypeCache.Get(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY).Id;
var groupMemberQry = new GroupMemberService(rockContext).Queryable(true).Where(m => m.Group.GroupTypeId == familyGroupTypeId);
// get giving group members in order by family role (adult -> child) and then gender (male -> female)
var givingGroup = new PersonService(rockContext).Queryable(true).AsNoTracking()
.Where(p => p.GivingId == targetPerson.GivingId)
.GroupJoin(
groupMemberQry,
p => p.Id,
m => m.PersonId,
(p, m) => new { p, m })
.SelectMany(x => x.m.DefaultIfEmpty(), (y, z) => new { Person = y.p, GroupMember = z })
.Select(p => new { FirstName = p.Person.NickName, LastName = p.Person.LastName, FamilyRoleOrder = p.GroupMember.GroupRole.Order, Gender = p.Person.Gender, PersonId = p.Person.Id })
.DistinctBy(p => p.PersonId)
.OrderBy(p => p.FamilyRoleOrder).ThenBy(p => p.Gender)
.ToList();
string salutation = string.Empty;
if (givingGroup.GroupBy(g => g.LastName).Count() == 1)
{
salutation = string.Join(", ", givingGroup.Select(g => g.FirstName)) + " " + givingGroup.FirstOrDefault().LastName;
if (salutation.Contains(","))
{
salutation = salutation.ReplaceLastOccurrence(",", " &");
}
}
else
{
salutation = string.Join(", ", givingGroup.Select(g => g.FirstName + " " + g.LastName));
if (salutation.Contains(","))
{
salutation = salutation.ReplaceLastOccurrence(",", " &");
}
}
mergeFields.Add("Salutation", salutation);
var mailingAddress = targetPerson.GetMailingLocation();
if (mailingAddress != null)
{
mergeFields.Add("StreetAddress1", mailingAddress.Street1);
mergeFields.Add("StreetAddress2", mailingAddress.Street2);
mergeFields.Add("City", mailingAddress.City);
mergeFields.Add("State", mailingAddress.State);
mergeFields.Add("PostalCode", mailingAddress.PostalCode);
mergeFields.Add("Country", mailingAddress.Country);
}
else
{
mergeFields.Add("StreetAddress1", string.Empty);
mergeFields.Add("StreetAddress2", string.Empty);
mergeFields.Add("City", string.Empty);
mergeFields.Add("State", string.Empty);
mergeFields.Add("PostalCode", string.Empty);
mergeFields.Add("Country", string.Empty);
}
var transactionDetails = qry.GroupJoin(new AttributeValueService(rockContext).Queryable(),
ft => ft.Id,
av => av.Id,
(obj, av) => new { Transaction = obj, AttributeValues = av })
.ToList()
.Select(obj =>
{
obj.Transaction.Attributes = obj.AttributeValues.Select(av2 => AttributeCache.Get(av2.Attribute)).ToDictionary(k => k.Key, v => v);
obj.Transaction.AttributeValues = obj.AttributeValues.Select(av2 => new AttributeValueCache(av2)).ToDictionary(k => k.AttributeKey, v => v);
return(obj.Transaction);
});
mergeFields.Add("TransactionDetails", transactionDetails);
if (excludedCurrencyTypes.Count > 0)
{
mergeFields.Add("ExcludedTransactionDetails", excludedQry.ToList());
}
mergeFields.Add("AccountSummary", qry.GroupBy(t => new { t.Account.Name, t.Account.PublicName, t.Account.Description })
.Select(s => new AccountSummary
{
AccountName = s.Key.Name,
PublicName = s.Key.PublicName,
Description = s.Key.Description,
Total = s.Sum(a => a.Amount),
Order = s.Max(a => a.Account.Order)
})
.OrderBy(s => s.Order));
// pledge information
var pledges = new FinancialPledgeService(rockContext).Queryable().AsNoTracking()
.Where(p => p.PersonAliasId.HasValue && personAliasIds.Contains(p.PersonAliasId.Value) &&
p.StartDate <= dateRange.End && p.EndDate >= dateRange.Start)
.GroupBy(p => p.Account)
.Select(g => new PledgeSummary
{
AccountId = g.Key.Id,
AccountName = g.Key.Name,
PublicName = g.Key.PublicName,
AmountPledged = g.Sum(p => p.TotalAmount),
PledgeStartDate = g.Min(p => p.StartDate),
PledgeEndDate = g.Max(p => p.EndDate)
})
.ToList();
// add detailed pledge information
foreach (var pledge in pledges)
{
var adjustedPledgeEndDate = pledge.PledgeEndDate.Value.Date;
if (adjustedPledgeEndDate != DateTime.MaxValue.Date)
{
adjustedPledgeEndDate = adjustedPledgeEndDate.AddDays(1);
}
if (adjustedPledgeEndDate > dateRange.End)
{
adjustedPledgeEndDate = dateRange.End.Value;
}
if (adjustedPledgeEndDate > RockDateTime.Now)
{
adjustedPledgeEndDate = RockDateTime.Now;
}
pledge.AmountGiven = new FinancialTransactionDetailService(rockContext).Queryable()
.Where(t =>
t.AccountId == pledge.AccountId &&
t.Transaction.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains(t.Transaction.AuthorizedPersonAliasId.Value) &&
t.Transaction.TransactionDateTime >= pledge.PledgeStartDate &&
t.Transaction.TransactionDateTime < adjustedPledgeEndDate)
.Sum(t => (decimal?)t.Amount) ?? 0;
pledge.AmountRemaining = (pledge.AmountGiven > pledge.AmountPledged) ? 0 : (pledge.AmountPledged - pledge.AmountGiven);
if (pledge.AmountPledged > 0)
{
var test = (double)pledge.AmountGiven / (double)pledge.AmountPledged;
pledge.PercentComplete = (int)((pledge.AmountGiven * 100) / pledge.AmountPledged);
}
}
mergeFields.Add("Pledges", pledges);
}
/// <summary>
/// Binds the contribution statements.
/// </summary>
private void BindContributionStatements()
{
var numberOfYears = GetAttributeValue(AttributeKey.MaxYearsToDisplay).AsInteger();
var rockContext = new RockContext();
var financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
var personAliasIds = new PersonAliasService(rockContext).Queryable().Where(a => a.Person.GivingId == Person.GivingId).Select(a => a.Id).ToList();
// get the transactions for the person or all the members in the person's giving group (Family)
var qry = financialTransactionDetailService.Queryable().AsNoTracking().Where(t =>
t.Transaction.AuthorizedPersonAliasId.HasValue &&
personAliasIds.Contains(t.Transaction.AuthorizedPersonAliasId.Value) &&
t.Transaction.TransactionDateTime.HasValue);
if (string.IsNullOrWhiteSpace(GetAttributeValue(AttributeKey.Accounts)))
{
qry = qry.Where(t => t.Account.IsTaxDeductible);
}
else
{
var accountGuids = GetAttributeValue(AttributeKey.Accounts).Split(',').Select(Guid.Parse).ToList();
qry = qry.Where(t => accountGuids.Contains(t.Account.Guid));
}
var yearQry = qry.GroupBy(t => t.Transaction.TransactionDateTime.Value.Year)
.Select(g => g.Key)
.OrderByDescending(y => y);
var statementYears = yearQry.Take(numberOfYears).ToList();
rptContributionStatementsYYYY.DataSource = statementYears;
rptContributionStatementsYYYY.DataBind();
pnlStatement.Visible = statementYears.Any();
}
