protected void ShowDetail(bool setSelectControlValues = true)
{
var rockContext = new RockContext();
var isExported = false;
var debugEnabled = GetAttributeValue(AttributeKey.EnableDebug).AsBoolean();
var exportMode = GetAttributeValue(AttributeKey.ExportMode);
_financialBatch = new FinancialBatchService(rockContext).Get(_batchId);
DateTime?dateExported = null;
_variance = 0;
if (_financialBatch != null)
{
var financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
var qryTransactionDetails = financialTransactionDetailService.Queryable().Where(a => a.Transaction.BatchId == _financialBatch.Id);
decimal txnTotal = qryTransactionDetails.Select(a => ( decimal? )a.Amount).Sum() ?? 0;
_variance = txnTotal - _financialBatch.ControlAmount;
_financialBatch.LoadAttributes();
dateExported = ( DateTime? )_financialBatch.GetAttributeValueAsType("rocks.kfs.Intacct.DateExported");
if (dateExported != null && dateExported > DateTime.MinValue)
{
isExported = true;
}
if (debugEnabled)
{
var debugLava = Session["IntacctDebugLava"].ToStringSafe();
if (!string.IsNullOrWhiteSpace(debugLava))
{
lDebug.Visible = true;
lDebug.Text += debugLava;
Session["IntacctDebugLava"] = string.Empty;
}
}
}
if (ValidSettings() && !isExported)
{
btnExportToIntacct.Text = GetAttributeValue(AttributeKey.ButtonText);
btnExportToIntacct.Visible = true;
if (exportMode == "JournalEntry")
{
pnlOtherReceipt.Visible = false;
}
else
{
SetupOtherReceipts(setSelectControlValues);
}
SetExportButtonVisibility();
}
else if (isExported)
{
litDateExported.Text = string.Format("<div class=\"small\">Exported: {0}</div>", dateExported.ToRelativeDateString());
litDateExported.Visible = true;
pnlExportedDetails.Visible = true;
if (UserCanEdit)
{
btnRemoveDate.Visible = true;
}
}
}
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);
var qry = financialTransactionDetailService.Queryable().AsNoTracking()
.Where(t => t.Transaction.AuthorizedPersonAlias.Person.GivingId == CurrentPerson.GivingId);
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));
}
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 == CurrentPerson.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 = CurrentPerson.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.PersonAlias.Person.GivingId == CurrentPerson.GivingId &&
(p.StartDate.Year == statementYear || p.EndDate.Year == statementYear))
.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.AddHours(23).AddMinutes(59).AddSeconds(59);
pledge.AmountGiven = new FinancialTransactionDetailService(rockContext).Queryable()
.Where(t =>
t.AccountId == pledge.AccountId &&
t.Transaction.TransactionDateTime >= pledge.PledgeStartDate &&
t.Transaction.TransactionDateTime <= adjustedPedgeEndDate)
.Sum(t => t.Amount);
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();
}
}
/// <summary>
/// Shows the financial batch summary.
/// </summary>
/// <param name="batch">The financial batch.</param>
private void ShowReadonlyDetails(FinancialBatch batch)
{
SetEditMode(false);
if (batch != null)
{
hfBatchId.SetValue(batch.Id);
SetHeadingInfo(batch, batch.Name);
string campusName = string.Empty;
if (batch.CampusId.HasValue)
{
var campus = CampusCache.Read(batch.CampusId.Value);
if (campus != null)
{
campusName = campus.ToString();
}
}
var rockContext = new RockContext();
var financialTransactionService = new FinancialTransactionService(rockContext);
var batchTransactions = financialTransactionService.Queryable().Where(a => a.BatchId.HasValue && a.BatchId.Value == batch.Id);
var financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
var qryTransactionDetails = financialTransactionDetailService.Queryable().Where(a => a.Transaction.BatchId == batch.Id);
decimal txnTotal = qryTransactionDetails.Select(a => (decimal?)a.Amount).Sum() ?? 0;
decimal variance = txnTotal - batch.ControlAmount;
string amountFormat = string.Format(
"{0} / {1} / " + (variance == 0.0M ? "{2}" : "<span class='label label-danger'>{2}</span>"),
txnTotal.FormatAsCurrency(),
batch.ControlAmount.FormatAsCurrency(),
variance.FormatAsCurrency());
lDetails.Text = new DescriptionList()
.Add("Date Range", new DateRange(batch.BatchStartDateTime, batch.BatchEndDateTime).ToString("g"))
.Add("Transaction / Control / Variance", amountFormat)
.Add("Accounting Code", batch.AccountingSystemCode)
.Add("Notes", batch.Note)
.Html;
// Account Summary
gAccounts.DataSource = qryTransactionDetails
.GroupBy(d => new
{
AccountId = d.AccountId,
AccountName = d.Account.Name
})
.Select(s => new
{
Id = s.Key.AccountId,
Name = s.Key.AccountName,
Amount = s.Sum(a => (decimal?)a.Amount) ?? 0.0M
})
.OrderBy(s => s.Name)
.ToList();
gAccounts.DataBind();
// Currency Summary
gCurrencyTypes.DataSource = batchTransactions
.GroupBy(c => new
{
CurrencyTypeValueId = c.FinancialPaymentDetailId.HasValue ? c.FinancialPaymentDetail.CurrencyTypeValueId : 0,
})
.Select(s => new
{
CurrencyTypeValueId = s.Key.CurrencyTypeValueId,
Amount = s.Sum(a => (decimal?)a.TransactionDetails.Sum(t => t.Amount)) ?? 0.0M
})
.ToList()
.Select(s => new
{
Id = s.CurrencyTypeValueId,
Name = DefinedValueCache.GetName(s.CurrencyTypeValueId),
Amount = s.Amount
}).OrderBy(a => a.Name).ToList();
gCurrencyTypes.DataBind();
}
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var contributionType = DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid());
if (contributionType != null)
{
var rockContext = new RockContext();
var transactionDetailService = new FinancialTransactionDetailService(rockContext);
var qry = transactionDetailService.Queryable().AsNoTracking()
.Where(a =>
a.Transaction.TransactionTypeValueId == contributionType.Id &&
a.Transaction.TransactionDateTime.HasValue);
var targetPerson = this.ContextEntity <Person>();
if (targetPerson != null)
{
qry = qry.Where(t => t.Transaction.AuthorizedPersonAlias.Person.GivingId == targetPerson.GivingId);
}
List <SummaryRecord> summaryList;
using (new Rock.Data.QueryHintScope(rockContext, QueryHintType.RECOMPILE))
{
summaryList = qry
.GroupBy(a => new { a.Transaction.TransactionDateTime.Value.Year, a.AccountId })
.Select(t => new SummaryRecord
{
Year = t.Key.Year,
AccountId = t.Key.AccountId,
TotalAmount = t.Sum(d => d.Amount)
}).OrderByDescending(a => a.Year)
.ToList();
}
var mergeObjects = GlobalAttributesCache.GetMergeFields(this.CurrentPerson);
var financialAccounts = new FinancialAccountService(rockContext).Queryable().Select(a => new { a.Id, a.Name }).ToDictionary(k => k.Id, v => v.Name);
var yearsMergeObjects = new List <Dictionary <string, object> >();
foreach (var item in summaryList.GroupBy(a => a.Year))
{
var year = item.Key;
var accountsList = new List <object>();
foreach (var a in item)
{
var accountDictionary = new Dictionary <string, object>();
accountDictionary.Add("Account", financialAccounts.ContainsKey(a.AccountId) ? financialAccounts[a.AccountId] : string.Empty);
accountDictionary.Add("TotalAmount", a.TotalAmount);
accountsList.Add(accountDictionary);
}
var yearDictionary = new Dictionary <string, object>();
yearDictionary.Add("Year", year);
yearDictionary.Add("SummaryRows", accountsList);
yearsMergeObjects.Add(yearDictionary);
}
mergeObjects.Add("Rows", yearsMergeObjects);
lLavaOutput.Text = string.Empty;
if (GetAttributeValue("EnableDebug").AsBooleanOrNull().GetValueOrDefault(false))
{
lLavaOutput.Text = mergeObjects.lavaDebugInfo(rockContext);
}
string template = GetAttributeValue("LavaTemplate");
lLavaOutput.Text += template.ResolveMergeFields(mergeObjects).ResolveClientIds(upnlContent.ClientID);
}
}
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>
/// Bind the grid to the donations that should be visible for the proper context.
/// </summary>
protected void BindGrid(bool isExporting = false)
{
var rockContext = new RockContext();
var groupMemberService = new GroupMemberService(rockContext);
var financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
var entityTypeIdGroupMember = EntityTypeCache.GetId <GroupMember>();
var hideGridColumns = GetAttributeValue("HideGridColumns").Split(',');
var hideGridActions = GetAttributeValue("HideGridActions").Split(',');
var mergeFields = LavaHelper.GetCommonMergeFields(RockPage, CurrentPerson);
Group group = null;
Dictionary <int, GroupMember> groupMembers;
//
// Get the donations for the entire opportunity group or for just the
// one individual being viewed.
//
if (ContextEntity <Group>() != null)
{
group = ContextEntity <Group>();
groupMembers = groupMemberService.Queryable()
.Where(m => m.GroupId == group.Id)
.ToDictionary(m => m.Id);
}
else
{
var groupMember = ContextEntity <GroupMember>();
group = groupMember.Group;
groupMembers = new Dictionary <int, GroupMember> {
{ groupMember.Id, groupMember }
};
}
//
// Get the list of donation entries for the grid that match the list of members.
//
var groupMemberIds = groupMembers.Keys.ToList();
var donations = financialTransactionDetailService.Queryable()
.Where(d => d.EntityTypeId == entityTypeIdGroupMember && groupMemberIds.Contains(d.EntityId.Value))
.ToList()
.Select(d => new
{
IsExporting = isExporting,
DonorId = d.Transaction.AuthorizedPersonAlias.PersonId,
Donor = d.Transaction.AuthorizedPersonAlias.Person,
Group = group,
Participant = groupMembers[d.EntityId.Value],
Amount = d.Amount,
Address = d.Transaction.AuthorizedPersonAlias.Person.GetHomeLocation(rockContext).ToStringSafe().ConvertCrLfToHtmlBr(),
Date = d.Transaction.TransactionDateTime
}).AsQueryable();
//
// Apply user sorting or default to donor name.
//
if (gDonations.SortProperty != null)
{
donations = donations.Sort(gDonations.SortProperty);
}
else
{
donations = donations.Sort(new SortProperty {
Property = "Donor.LastName, Donor.NickName"
});
}
gDonations.ObjectList = donations.Select(d => d.Donor)
.DistinctBy(p => p.Id)
.Cast <object>()
.ToDictionary(p => (( Person )p).Id.ToString());
//
// Hide any columns they don't want visible to the user.
//
gDonations.ColumnsOfType <CurrencyField>()
.First(c => c.DataField == "Amount")
.Visible = !hideGridColumns.Contains("Amount");
gDonations.ColumnsOfType <RockBoundField>()
.First(c => c.DataField == "Address")
.Visible = !hideGridColumns.Contains("Donor Address");
gDonations.ColumnsOfType <RockBoundField>()
.First(c => c.DataField == "Donor.Email")
.Visible = !hideGridColumns.Contains("Donor Email");
gDonations.ColumnsOfType <RockLiteralField>()
.First(c => c.HeaderText == "Participant")
.Visible = !hideGridColumns.Contains("Participant") && ContextEntity <GroupMember>() == null;
//
// Hide any grid actions they don't want visible to the user.
//
gDonations.Actions.ShowCommunicate = !hideGridActions.Contains("Communicate");
gDonations.Actions.ShowMergePerson = !hideGridActions.Contains("Merge Person");
gDonations.Actions.ShowBulkUpdate = !hideGridActions.Contains("Bulk Update");
gDonations.Actions.ShowExcelExport = !hideGridActions.Contains("Excel Export");
gDonations.Actions.ShowMergeTemplate = !hideGridActions.Contains("Merge Template");
//
// If all the grid actions are hidden, hide the select column too.
//
gDonations.ColumnsOfType <SelectField>().First().Visible = gDonations.Actions.ShowCommunicate || gDonations.Actions.ShowMergePerson || gDonations.Actions.ShowBulkUpdate || gDonations.Actions.ShowExcelExport || gDonations.Actions.ShowMergeTemplate;
gDonations.DataSource = donations.ToList();
gDonations.DataBind();
}
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>
/// Bind the grid to the donations that should be visible for the proper context.
/// </summary>
protected void BindGrid(bool isExporting = false)
{
var rockContext = new RockContext();
var groupMemberService = new GroupMemberService(rockContext);
var financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
var entityTypeIdGroupMember = EntityTypeCache.GetId <GroupMember>();
Dictionary <int, GroupMember> groupMembers;
//
// Get the donations for the entire opportunity group or for just the
// one individual being viewed.
//
if (ContextEntity <Group>() != null)
{
var group = ContextEntity <Group>();
groupMembers = groupMemberService.Queryable()
.Where(m => m.GroupId == group.Id)
.ToDictionary(m => m.Id);
gDonations.Columns.OfType <RockTemplateField>().Where(c => c.HeaderText == "Participant").ToList().ForEach(c => c.Visible = true);
gDonations.Columns.OfType <DateField>().Where(c => c.HeaderText == "Date").ToList().ForEach(c => c.Visible = false);
}
else
{
var groupMember = ContextEntity <GroupMember>();
groupMembers = new Dictionary <int, GroupMember> {
{ groupMember.Id, groupMember }
};
gDonations.Columns.OfType <RockTemplateField>().Where(c => c.HeaderText == "Participant").ToList().ForEach(c => c.Visible = false);
gDonations.Columns.OfType <DateField>().Where(c => c.HeaderText == "Date").ToList().ForEach(c => c.Visible = true);
}
var showDonorPersonAsLink = GetAttributeValue("ShowDonorPersonAsLink").AsBoolean();
var showParticipantPersonLink = GetAttributeValue("ShowParticipantPersonLink").AsBoolean();
var showParticipantGroupMemberLink = GetAttributeValue("ShowParticipantGroupMemberLink").AsBoolean();
//
// Get the list of donation entries for the grid that match the list of members.
//
var groupMemberIds = groupMembers.Keys.ToList();
var donations = financialTransactionDetailService.Queryable()
.Where(d => d.EntityTypeId == entityTypeIdGroupMember && groupMemberIds.Contains(d.EntityId.Value))
.ToList()
.Select(d => new
{
DonorId = d.Transaction.AuthorizedPersonAlias.PersonId,
Donor = d.Transaction.AuthorizedPersonAlias.Person,
DonorName = ((isExporting || !showDonorPersonAsLink) ? d.Transaction.AuthorizedPersonAlias.Person.FullName : string.Format("<a href=\"{0}\">{1}</a>", DonorPersonLink.Replace("{DonorPersonId}", d.Transaction.AuthorizedPersonAlias.Person.Id.ToString()).Replace("{GroupMemberId}", groupMembers[d.EntityId.Value].Id.ToString()).Replace("{GroupMemberPersonId}", groupMembers[d.EntityId.Value].PersonId.ToString()), d.Transaction.AuthorizedPersonAlias.Person.FullName)),
Email = d.Transaction.AuthorizedPersonAlias.Person.Email,
Participant = groupMembers[d.EntityId.Value],
ParticipantName = (isExporting ? groupMembers[d.EntityId.Value].Person.FullName :
((showParticipantPersonLink || showParticipantGroupMemberLink) ?
(showParticipantPersonLink ? string.Format("<a href=\"{0}\" class=\"pull-right margin-l-sm btn btn-sm btn-default\"><i class=\"fa fa-user\"></i></a>", ParticipantPersonLink.Replace("{DonorPersonId}", d.Transaction.AuthorizedPersonAlias.Person.Id.ToString()).Replace("{GroupMemberId}", groupMembers[d.EntityId.Value].Id.ToString()).Replace("{GroupMemberPersonId}", groupMembers[d.EntityId.Value].PersonId.ToString())) : string.Empty) +
(showParticipantGroupMemberLink ? string.Format("<a href=\"{0}\">{1}</a>", ParticipantGroupMemberLink.Replace("{DonorPersonId}", d.Transaction.AuthorizedPersonAlias.Person.Id.ToString()).Replace("{GroupMemberId}", groupMembers[d.EntityId.Value].Id.ToString()).Replace("{GroupMemberPersonId}", groupMembers[d.EntityId.Value].PersonId.ToString()), groupMembers[d.EntityId.Value].Person.FullName) : string.Empty)
: groupMembers[d.EntityId.Value].Person.FullName)
),
Amount = d.Amount,
Address = d.Transaction.AuthorizedPersonAlias.Person.GetHomeLocation(rockContext).ToStringSafe().ConvertCrLfToHtmlBr(),
Date = d.Transaction.TransactionDateTime
}).AsQueryable();
//
// Apply user sorting or default to donor name.
//
if (gDonations.SortProperty != null)
{
donations = donations.Sort(gDonations.SortProperty);
}
else
{
donations = donations.Sort(new SortProperty {
Property = "Donor.LastName, Donor.NickName"
});
}
gDonations.ObjectList = donations.Select(d => d.Donor)
.DistinctBy(p => p.Id)
.Cast <object>()
.ToDictionary(p => (( Person )p).Id.ToString());
if (!GetAttributeValue("ShowDonorAddress").AsBoolean())
{
gDonations.Columns[2].Visible = false;
}
if (!GetAttributeValue("ShowDonorEmail").AsBoolean())
{
gDonations.Columns[3].Visible = false;
}
if (!GetAttributeValue("ShowParticipantColumn").AsBoolean())
{
gDonations.Columns[4].Visible = false;
}
gDonations.Columns[6].Visible = GetAttributeValue("ShowAmount").AsBoolean();
gDonations.Actions.ShowCommunicate = GetAttributeValue("ShowCommunicate").AsBoolean();
gDonations.Actions.ShowMergePerson = GetAttributeValue("ShowMergePerson").AsBoolean();
gDonations.Actions.ShowBulkUpdate = GetAttributeValue("ShowBulkUpdate").AsBoolean();
gDonations.Actions.ShowExcelExport = GetAttributeValue("ShowExcelExport").AsBoolean();
gDonations.Actions.ShowMergeTemplate = GetAttributeValue("ShowMergeTemplate").AsBoolean();
gDonations.DataSource = donations.ToList();
gDonations.DataBind();
}
/// <summary>
/// Bind the grid to the donations that should be visible for the proper context.
/// </summary>
protected void BindGrid()
{
var rockContext = new RockContext();
var groupMemberService = new GroupMemberService(rockContext);
var financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
var entityTypeIdGroupMember = EntityTypeCache.GetId <GroupMember>();
Dictionary <int, GroupMember> groupMembers;
//
// Get the donations for the entire opportunity group or for just the
// one individual being viewed.
//
if (ContextEntity <Group>() != null)
{
var group = ContextEntity <Group>();
groupMembers = groupMemberService.Queryable()
.Where(m => m.GroupId == group.Id)
.ToDictionary(m => m.Id);
gDonations.Columns.OfType <RockTemplateField>().Where(c => c.HeaderText == "Participant").ToList().ForEach(c => c.Visible = true);
gDonations.Columns.OfType <DateField>().Where(c => c.HeaderText == "Date").ToList().ForEach(c => c.Visible = false);
}
else
{
var groupMember = ContextEntity <GroupMember>();
groupMembers = new Dictionary <int, GroupMember> {
{ groupMember.Id, groupMember }
};
gDonations.Columns.OfType <RockTemplateField>().Where(c => c.HeaderText == "Participant").ToList().ForEach(c => c.Visible = false);
gDonations.Columns.OfType <DateField>().Where(c => c.HeaderText == "Date").ToList().ForEach(c => c.Visible = true);
}
//
// Get the list of donation entries for the grid that match the list of members.
//
var groupMemberIds = groupMembers.Keys.ToList();
var donations = financialTransactionDetailService.Queryable()
.Where(d => d.EntityTypeId == entityTypeIdGroupMember && groupMemberIds.Contains(d.EntityId.Value))
.ToList()
.Select(d => new
{
DonorId = d.Transaction.AuthorizedPersonAlias.PersonId,
Donor = d.Transaction.AuthorizedPersonAlias.Person,
Participant = groupMembers[d.EntityId.Value],
Amount = d.Amount,
Address = d.Transaction.AuthorizedPersonAlias.Person.GetHomeLocation(rockContext).ToStringSafe().ConvertCrLfToHtmlBr(),
Date = d.Transaction.TransactionDateTime
}).AsQueryable();
//
// Apply user sorting or default to donor name.
//
if (gDonations.SortProperty != null)
{
donations = donations.Sort(gDonations.SortProperty);
}
else
{
donations = donations.Sort(new SortProperty {
Property = "Donor.LastName, Donor.NickName"
});
}
gDonations.ObjectList = donations.Select(d => d.Donor)
.DistinctBy(p => p.Id)
.Cast <object>()
.ToDictionary(p => (( Person )p).Id.ToString());
gDonations.DataSource = donations.ToList();
gDonations.DataBind();
}
/// <summary>
/// Handles the Click event of the lbSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void lbSave_Click(object sender, EventArgs e)
{
var rockContext = new RockContext();
var txnService = new FinancialTransactionService(rockContext);
var txnDetailService = new FinancialTransactionDetailService(rockContext);
var txnImageService = new FinancialTransactionImageService(rockContext);
var binaryFileService = new BinaryFileService(rockContext);
FinancialTransaction txn = null;
int?txnId = hfTransactionId.Value.AsIntegerOrNull();
int?batchId = hfBatchId.Value.AsIntegerOrNull();
if (txnId.HasValue)
{
txn = txnService.Get(txnId.Value);
}
if (txn == null)
{
txn = new FinancialTransaction();
txnService.Add(txn);
txn.BatchId = batchId;
}
if (txn != null)
{
if (ppAuthorizedPerson.PersonId.HasValue)
{
txn.AuthorizedPersonAliasId = ppAuthorizedPerson.PersonAliasId;
}
txn.TransactionDateTime = dtTransactionDateTime.SelectedDateTime;
txn.TransactionTypeValueId = ddlTransactionType.SelectedValue.AsInteger();
txn.SourceTypeValueId = ddlSourceType.SelectedValueAsInt();
Guid?gatewayGuid = cpPaymentGateway.SelectedValueAsGuid();
if (gatewayGuid.HasValue)
{
var gatewayEntity = EntityTypeCache.Read(gatewayGuid.Value);
if (gatewayEntity != null)
{
txn.GatewayEntityTypeId = gatewayEntity.Id;
}
else
{
txn.GatewayEntityTypeId = null;
}
}
else
{
txn.GatewayEntityTypeId = null;
}
txn.TransactionCode = tbTransactionCode.Text;
txn.CurrencyTypeValueId = ddlCurrencyType.SelectedValueAsInt();
txn.CreditCardTypeValueId = ddlCreditCardType.SelectedValueAsInt();
txn.Summary = tbSummary.Text;
if (!Page.IsValid || !txn.IsValid)
{
return;
}
foreach (var txnDetail in TransactionDetailsState)
{
if (!txnDetail.IsValid)
{
return;
}
}
rockContext.WrapTransaction(() =>
{
// Save the transaction
rockContext.SaveChanges();
// Delete any transaction details that were removed
var txnDetailsInDB = txnDetailService.Queryable().Where(a => a.TransactionId.Equals(txn.Id)).ToList();
var deletedDetails = from txnDetail in txnDetailsInDB
where !TransactionDetailsState.Select(d => d.Guid).Contains(txnDetail.Guid)
select txnDetail;
deletedDetails.ToList().ForEach(txnDetail =>
{
txnDetailService.Delete(txnDetail);
});
rockContext.SaveChanges();
// Save Transaction Details
foreach (var editorTxnDetail in TransactionDetailsState)
{
// Add or Update the activity type
var txnDetail = txn.TransactionDetails.FirstOrDefault(d => d.Guid.Equals(editorTxnDetail.Guid));
if (txnDetail == null)
{
txnDetail = new FinancialTransactionDetail();
txnDetail.Guid = editorTxnDetail.Guid;
txn.TransactionDetails.Add(txnDetail);
}
txnDetail.AccountId = editorTxnDetail.AccountId;
txnDetail.Amount = UseSimpleAccountMode ? tbSingleAccountAmount.Text.AsDecimal() : editorTxnDetail.Amount;
txnDetail.Summary = editorTxnDetail.Summary;
}
rockContext.SaveChanges();
// Delete any transaction images that were removed
var orphanedBinaryFileIds = new List <int>();
var txnImagesInDB = txnImageService.Queryable().Where(a => a.TransactionId.Equals(txn.Id)).ToList();
foreach (var txnImage in txnImagesInDB.Where(i => !TransactionImagesState.Contains(i.BinaryFileId)))
{
orphanedBinaryFileIds.Add(txnImage.BinaryFileId);
txnImageService.Delete(txnImage);
}
// Save Transaction Images
int imageOrder = 0;
foreach (var binaryFileId in TransactionImagesState)
{
// Add or Update the activity type
var txnImage = txnImagesInDB.FirstOrDefault(i => i.BinaryFileId == binaryFileId);
if (txnImage == null)
{
txnImage = new FinancialTransactionImage();
txnImage.TransactionId = txn.Id;
txn.Images.Add(txnImage);
}
txnImage.BinaryFileId = binaryFileId;
txnImage.Order = imageOrder;
imageOrder++;
}
rockContext.SaveChanges();
// Make sure updated binary files are not temporary
foreach (var binaryFile in binaryFileService.Queryable().Where(f => TransactionImagesState.Contains(f.Id)))
{
binaryFile.IsTemporary = false;
}
// Delete any orphaned images
foreach (var binaryFile in binaryFileService.Queryable().Where(f => orphanedBinaryFileIds.Contains(f.Id)))
{
binaryFileService.Delete(binaryFile);
}
rockContext.SaveChanges();
});
// Save selected options to session state in order to prefill values for next added txn
Session["NewTxnDefault_BatchId"] = txn.BatchId;
Session["NewTxnDefault_TransactionDateTime"] = txn.TransactionDateTime;
Session["NewTxnDefault_TransactionType"] = txn.TransactionTypeValueId;
Session["NewTxnDefault_SourceType"] = txn.SourceTypeValueId;
Session["NewTxnDefault_CurrencyType"] = txn.CurrencyTypeValueId;
Session["NewTxnDefault_CreditCardType"] = txn.CreditCardTypeValueId;
if (TransactionDetailsState.Count() == 1)
{
Session["NewTxnDefault_Account"] = TransactionDetailsState.First().AccountId;
}
else
{
Session.Remove("NewTxnDefault_Account");
}
// Requery the batch to support EF navigation properties
var savedTxn = GetTransaction(txn.Id);
ShowReadOnlyDetails(savedTxn);
}
}
/// <summary>
/// Binds the attendees grid.
/// </summary>
private void BindGiversGrid()
{
// Get all the selected criteria values
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(drpSlidingDateRange.DelimitedValues);
var start = dateRange.Start;
var end = dateRange.End;
var minAmount = nreAmount.LowerValue;
var maxAmount = nreAmount.UpperValue;
var currencyTypeIds = new List <int>();
cblCurrencyTypes.SelectedValues.ForEach(i => currencyTypeIds.Add(i.AsInteger()));
var sourceTypeIds = new List <int>();
cblTransactionSource.SelectedValues.ForEach(i => sourceTypeIds.Add(i.AsInteger()));
var accountIds = new List <int>();
foreach (var cblAccounts in phAccounts.Controls.OfType <RockCheckBoxList>())
{
accountIds.AddRange(cblAccounts.SelectedValuesAsInt);
}
var dataViewId = dvpDataView.SelectedValueAsInt();
GiversViewBy viewBy = hfViewBy.Value.ConvertToEnumOrNull <GiversViewBy>() ?? GiversViewBy.Giver;
// Clear all the existing grid columns
gGiversGifts.Columns.Clear();
// Add a column for selecting rows
gGiversGifts.Columns.Add(new SelectField());
// Add a column for the person's name
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "PersonName",
HeaderText = "Person",
SortExpression = "LastName,NickName"
});
// add a column for email (but is only included on excel export)
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "Email",
HeaderText = "Email",
SortExpression = "Email",
Visible = false,
ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude
});
// Add a column for total amount
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = "TotalAmount",
HeaderText = "Total",
SortExpression = "TotalAmount"
});
// Add columns for the selected account totals
if (accountIds.Any())
{
var accounts = new FinancialAccountService(_rockContext)
.Queryable().AsNoTracking()
.Where(a => accountIds.Contains(a.Id))
.ToList();
foreach (int accountId in accountIds)
{
var account = accounts.FirstOrDefault(a => a.Id == accountId);
if (account != null)
{
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = account.Id.ToString(),
HeaderText = account.Name,
SortExpression = account.Id.ToString()
});
}
}
}
// Add a column for the number of gifts
var numberGiftsField = new RockBoundField
{
DataField = "NumberGifts",
HeaderText = "Number of Gifts",
SortExpression = "NumberGifts",
DataFormatString = "{0:N0}",
};
numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
gGiversGifts.Columns.Add(numberGiftsField);
// Add a column to indicate if this is a first time giver
gGiversGifts.Columns.Add(
new BoolField
{
DataField = "IsFirstEverGift",
HeaderText = "Is First Gift",
SortExpression = "IsFirstEverGift"
});
// Add a column for the first gift date ( that matches criteria )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstGift",
HeaderText = "First Gift",
SortExpression = "FirstGift"
});
// Add a column for the first-ever gift date ( to any tax-deductible account )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstEverGift",
HeaderText = "First Gift Ever",
SortExpression = "FirstEverGift"
});
var transactionDetailService = new FinancialTransactionDetailService(_rockContext);
var personService = new PersonService(_rockContext);
// If dataview was selected get the person id's returned by the dataview
var dataViewPersonIds = new List <int>();
if (dataViewId.HasValue)
{
var dataView = new DataViewService(_rockContext).Get(dataViewId.Value);
if (dataView != null)
{
var errorMessages = new List <string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages);
SortProperty sortProperty = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where(paramExpression, whereExpression, sortProperty)
.Select(p => p.Id);
dataViewPersonIds = dataViewPersonIdQry.ToList();
}
}
// Check to see if grid should display only people who gave a certain number of times and if so
// set the min value
int minCount = 0;
var previousPersonIds = new List <int>();
if (radByPattern.Checked)
{
minCount = tbPatternXTimes.Text.AsInteger();
var missedStart = drpPatternDateRange.LowerValue;
var missedEnd = drpPatternDateRange.UpperValue;
if (missedStart.HasValue && missedEnd.HasValue)
{
// Get the givingids that gave any amount during the pattern's date range. These
// are needed so that we know who to exclude from the result set
var previousGivingIds = transactionDetailService
.Queryable().AsNoTracking()
.Where(d =>
d.Transaction.TransactionDateTime.HasValue &&
d.Transaction.TransactionDateTime.Value >= missedStart.Value &&
d.Transaction.TransactionDateTime.Value < missedEnd.Value &&
accountIds.Contains(d.AccountId) &&
d.Amount != 0.0M)
.Select(d => d.Transaction.AuthorizedPersonAlias.Person.GivingId);
// Now get the person ids from the givingids
previousPersonIds = personService
.Queryable().AsNoTracking()
.Where(p => previousGivingIds.Contains(p.GivingId))
.Select(p => p.Id)
.ToList();
}
}
// Call the stored procedure to get all the giving data that matches the selected criteria.
// The stored procedure returns two tables. First is a list of all matching transaction summary
// information and the second table is each giving leader's first-ever gift date to a tax-deductible account
DataSet ds = FinancialTransactionDetailService.GetGivingAnalytics(start, end, minAmount, maxAmount,
accountIds, currencyTypeIds, sourceTypeIds, dataViewId, viewBy);
// Get the results table
DataTable dtResults = ds.Tables[0];
// Get the first-ever gift dates and load them into a dictionary for faster matching
DataTable dtFirstEver = ds.Tables[1];
var firstEverVals = new Dictionary <int, DateTime>();
foreach (DataRow row in ds.Tables[1].Rows)
{
if (!DBNull.Value.Equals(row["FirstEverGift"]))
{
firstEverVals.Add((int)row["PersonId"], (DateTime)row["FirstEverGift"]);
}
}
// Add columns to the result set for the first-ever data
dtResults.Columns.Add(new DataColumn("IsFirstEverGift", typeof(bool)));
dtResults.Columns.Add(new DataColumn("FirstEverGift", typeof(DateTime)));
foreach (DataRow row in dtResults.Rows)
{
bool rowValid = true;
// Get the person id
int personId = (int)row["Id"];
if (radByPattern.Checked)
{
// If pattern was specified check minimum gifts and other date range
int numberGifts = (int)row["NumberGifts"];
if (numberGifts < minCount)
{
rowValid = false;
}
else
{
// If this giving leader gave during the pattern date, remove the row since we
// only want those who did not
if (previousPersonIds.Contains(personId))
{
rowValid = false;
}
}
}
if (dataViewId.HasValue)
{
// If a dataview filter was specified, and this row is not part of dataview,
// remove it
if (!dataViewPersonIds.Contains(personId))
{
rowValid = false;
// Remove person id from list so that list can be used later to optionally
// add rows for remaining people who were in the dataview, but not in the
// result set
dataViewPersonIds.Remove(personId);
}
}
if (rowValid)
{
// Set the first ever information for each row
bool isFirstEverGift = false;
DateTime firstGift = (DateTime)row["FirstGift"];
if (firstEverVals.ContainsKey(personId))
{
DateTime firstEverGift = firstEverVals[personId];
isFirstEverGift = firstEverGift.Equals(firstGift);
row["FirstEverGift"] = firstEverGift;
}
// If only first time givers should be included, remove any that are not
if (radFirstTime.Checked && !isFirstEverGift)
{
rowValid = false;
}
else
{
row["IsFirstEverGift"] = isFirstEverGift;
}
}
if (!rowValid)
{
row.Delete();
}
}
// if dataview was selected and it includes people not in the result set,
if (dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any())
{
// Query for the names of each of these people
foreach (var person in personService
.Queryable().AsNoTracking()
.Select(p => new {
p.Id,
p.Guid,
p.NickName,
p.LastName,
p.Email
}))
{
// Check for a first ever gift date
var firstEverGiftDate = firstEverVals
.Where(f => f.Key == person.Id)
.Select(f => f.Value)
.FirstOrDefault();
DataRow row = dtResults.NewRow();
row["Id"] = person.Id;
row["Guid"] = person.Guid;
row["NickName"] = person.NickName;
row["LastName"] = person.LastName;
row["PersonName"] = person.NickName + " " + person.LastName;
row["Email"] = person.Email;
row["IsFirstEverGift"] = false;
row["FirstEverGift"] = firstEverGiftDate;
dtResults.Rows.Add(row);
}
}
// Update the changes (deletes) in the datatable
dtResults.AcceptChanges();
// Calculate Total
if (viewBy == GiversViewBy.Giver)
{
pnlTotal.Visible = true;
object amountTotalObj = dtResults.Compute("Sum(TotalAmount)", null);
if (amountTotalObj != null)
{
decimal amountTotal = amountTotalObj.ToString().AsDecimal();
lTotal.Text = amountTotal.FormatAsCurrency();
}
else
{
lTotal.Text = string.Empty;
}
}
else
{
pnlTotal.Visible = false;
}
// Sort the results
System.Data.DataView dv = dtResults.DefaultView;
if (gGiversGifts.SortProperty != null)
{
try
{
var sortProperties = new List <string>();
foreach (string prop in gGiversGifts.SortProperty.Property.SplitDelimitedValues(false))
{
sortProperties.Add(string.Format("[{0}] {1}", prop, gGiversGifts.SortProperty.DirectionString));
}
dv.Sort = sortProperties.AsDelimited(", ");
}
catch
{
dv.Sort = "[LastName] ASC, [NickName] ASC";
}
}
else
{
dv.Sort = "[LastName] ASC, [NickName] ASC";
}
gGiversGifts.DataSource = dv;
gGiversGifts.DataBind();
}
/// <summary>
/// Shows the report output.
/// </summary>
public void ShowReportOutput()
{
var rockContext = new RockContext();
var financialTransactionDetailService = new FinancialTransactionDetailService(rockContext);
var qry = financialTransactionDetailService.Queryable();
var startDateTime = srpFilterDates.SelectedDateRange.Start;
var endDateTime = srpFilterDates.SelectedDateRange.End;
this.SetBlockUserPreference(UserPreferenceKey.SlidingDateRangeDelimitedValues, srpFilterDates.DelimitedValues);
this.SetBlockUserPreference(UserPreferenceKey.AccountIds, apAccounts.SelectedIds.ToList().AsDelimited(","));
qry = qry.Where(a => a.Transaction.TransactionDateTime >= startDateTime && a.Transaction.TransactionDateTime < endDateTime);
var selectedAccountIds = apAccounts.SelectedIds;
if (selectedAccountIds.Any())
{
if (selectedAccountIds.Count() == 1)
{
var accountId = selectedAccountIds[0];
qry = qry.Where(a => a.AccountId == accountId);
}
else
{
qry = qry.Where(a => selectedAccountIds.Contains(a.AccountId));
}
}
qry = qry.Where(a => a.Transaction.TransactionDetails.Any(x => x.FeeCoverageAmount.HasValue));
var totals = qry.Select(a => new { a.FeeCoverageAmount, a.TransactionId, a.Transaction.FinancialPaymentDetail.CurrencyTypeValueId });
var transactionFeeCoverageList = totals.ToList();
var totalsByTransactionId = transactionFeeCoverageList
.GroupBy(a => a.TransactionId)
.Select(a => new
{
TransactionId = a.Key,
// There is only one currency per transaction, so FirstOrDefault works here
CurrencyTypeValueId = a.FirstOrDefault().CurrencyTypeValueId,
FeeCoverageAmount = a.Sum(x => x.FeeCoverageAmount ?? 0.00M),
});
var currencyTypeIdCreditCard = DefinedValueCache.GetId(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD.AsGuid());
var currencyTypeIdACH = DefinedValueCache.GetId(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH.AsGuid());
var creditCardTransactions = totalsByTransactionId.Where(a => a.CurrencyTypeValueId == currencyTypeIdCreditCard).ToList();
var achTransactions = totalsByTransactionId.Where(a => a.CurrencyTypeValueId == currencyTypeIdACH).ToList();
var achCount = achTransactions.Count();
var achFeeCoverageTotal = achTransactions.Sum(a => a.FeeCoverageAmount);
var creditCardCount = creditCardTransactions.Count();
var creditCardFeeCoverageTotal = creditCardTransactions.Sum(a => a.FeeCoverageAmount);
var kpiLava = @"
{[kpis style:'card' columncount:'3']}
[[ kpi icon:'fa-list' value:'{{TotalFeeCoverageAmount | FormatAsCurrency }}' label:'{{TotalFeeCoverageLabel}}' color:'blue-500']][[ endkpi ]]
[[ kpi icon:'fa-credit-card' value:'{{CreditCardFeeCoverageAmount | FormatAsCurrency }}' label:'{{CreditFeeCoverageLabel}}' color:'green-500']][[ endkpi ]]
[[ kpi icon:'fa fa-money-check-alt' value:'{{ACHFeeCoverageAmount | FormatAsCurrency }}' label:'{{ACHFeeCoverageLabel}}' color:'indigo-500' ]][[ endkpi ]]
{[endkpis]}";
var mergeFields = Rock.Lava.LavaHelper.GetCommonMergeFields(this.RockPage);
mergeFields.Add("TotalFeeCoverageAmount", achFeeCoverageTotal + creditCardFeeCoverageTotal);
mergeFields.Add("CreditCardFeeCoverageAmount", creditCardFeeCoverageTotal);
mergeFields.Add("ACHFeeCoverageAmount", achFeeCoverageTotal);
var totalFeeCoverageLabel = string.Format("Total Fees<br/>{0:N0} Transactions with Fees", achCount + creditCardCount);
var creditCardFeeCoverageLabel = string.Format("Credit Card Fees<br/>{0:N0} Transactions with Fees", creditCardCount);
var achFeeCoverageLabel = string.Format("ACH Fees<br/>{0:N0} Transactions with Fees", achCount);
mergeFields.Add("TotalFeeCoverageLabel", totalFeeCoverageLabel);
mergeFields.Add("CreditFeeCoverageLabel", creditCardFeeCoverageLabel);
mergeFields.Add("ACHFeeCoverageLabel", achFeeCoverageLabel);
var kpiHtml = kpiLava.ResolveMergeFields(mergeFields);
lKPIHtml.Text = kpiHtml;
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var rockContext = new RockContext();
String errorMessage = "";
// Date Range
DateTime endDate = DateTime.Today;
DateTime startDate = endDate.AddDays(-6);
var drp = new DateRangePicker();
drp.DelimitedValues = gfTransactions.GetUserPreference("Date Range");
if (drp.LowerValue.HasValue)
{
startDate = drp.LowerValue.Value;
}
if (drp.UpperValue.HasValue)
{
endDate = drp.UpperValue.Value;
}
FinancialGateway financialGateway = null;
Guid? gatewayGuid = GetAttributeValue("FinancialGateway").AsGuidOrNull();
if (gatewayGuid.HasValue)
{
financialGateway = new FinancialGatewayService(rockContext).Get(gatewayGuid.Value);
if (financialGateway != null)
{
financialGateway.LoadAttributes(rockContext);
}
}
if (financialGateway != null)
{
// var transactionsFromNMI = new List<Payment>();
// transactionsFromNMI = GetTransactions( financialGateway, startDate, endDate, out errorMessage );
var transactionsFromNMI = new List <FinancialTransaction>();
transactionsFromNMI = GetTransactions(financialGateway, startDate, endDate, out errorMessage);
transactionsFromNMI.ToList();
// Get a list of the transactions in Rock for the time frame specified.
FinancialTransactionService transactionService = new FinancialTransactionService(rockContext);
var transactionsInRock = transactionService.Queryable();
FinancialTransactionDetailService transactionDetailService = new FinancialTransactionDetailService(rockContext);
var transactionDetailsInRock = transactionDetailService.Queryable();
SortProperty sort = gTransactions.SortProperty;
// Get a list of all transactions from NMI that do not have a matching transaction
// code in Rock.
List <FinancialTransaction> missing = transactionsFromNMI.Where(tn =>
(!transactionsInRock.Any(tr => tr.TransactionCode == tn.TransactionCode))
).ToList();
// This is another way to do the transactions from NMI that are in Rock but do not
// have matching amounts. We think the way below is probably faster.
// List<FinancialTransaction> notMissing = transactionsInRock.AsEnumerable().Where( tr =>
// ( transactionsFromNMI.Any( tn => tn.TransactionCode == tr.TransactionCode ) ) )
// .ToList();
// Get a list of all transactions from NMI that are in Rock but do not have matching amounts.
// Step 1: List of all the transaction codes from the NMI extract.
List <String> codes = transactionsFromNMI.Select(t => t.TransactionCode).ToList();
// Step 2: Get a list of all the transactions from Rock that match the codes from Step 1.
List <FinancialTransaction> notMissing = transactionsInRock.Where(tr =>
codes.Contains(tr.TransactionCode)
).ToList();
// Step 3: Create a list of all transactions in Rock that do not match amounts.
List <FinancialTransaction> wrongAmount = notMissing.Where(nm =>
(transactionDetailsInRock.Where(td =>
td.TransactionId == nm.Id).Sum(td => td.Amount)
) != nm.TotalAmount
).ToList();
// Combine the missing codes list with the non-matching amount list to create a list to
// display in the block grid.
List <FinancialTransaction> transactionsToList = missing.Union(wrongAmount).ToList();
SortProperty sortProperty = gTransactions.SortProperty;
if (sortProperty != null)
{
transactionsToList.AsQueryable().Sort(sortProperty);
}
else
{
transactionsToList.OrderByDescending(t => t.TransactionDateTime).ThenByDescending(t => t.TransactionCode);
}
// Show the transactions that are left after the filtering in the grid.
gTransactions.DataSource = transactionsToList.Select(t => new
{
Name = t.ForeignKey,
EmailAddress = t.CheckMicrEncrypted,
t.TransactionDateTime,
Amount = t.TransactionDetails.Select(d => d.Amount).FirstOrDefault(),
t.TransactionCode,
t.Status,
t.StatusMessage,
Scheduled = t.CheckMicrHash,
}).ToList();
gTransactions.DataBind();
}
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
var rockContext = new RockContext();
var transactionIdsQry = new FinancialTransactionService(rockContext).Queryable();
var transactionDetailService = new FinancialTransactionDetailService(rockContext);
var qry = transactionDetailService.Queryable().AsNoTracking()
.Where(a => a.Transaction.TransactionDateTime.HasValue);
var targetPerson = this.ContextEntity <Person>();
if (targetPerson != null)
{
qry = qry.Where(t => t.Transaction.AuthorizedPersonAlias.Person.GivingId == targetPerson.GivingId);
}
var yearlyAccountDetails = qry.Select(t => new
{
Year = t.Transaction.TransactionDateTime.Value.Year,
AccountId = t.Account.Id,
AccountName = t.Account.Name,
Amount = t.Amount
})
.ToList();
var summaryList = yearlyAccountDetails
.GroupBy(a => a.Year)
.Select(t => new
{
Year = t.Key,
Accounts = t.GroupBy(b => b.AccountId).Select(c => new
{
AccountName = c.Max(d => d.AccountName),
TotalAmount = c.Sum(d => d.Amount)
}).OrderBy(e => e.AccountName)
}).OrderByDescending(a => a.Year)
.ToList();
var mergeObjects = GlobalAttributesCache.GetMergeFields(this.CurrentPerson);
var yearsMergeObjects = new List <Dictionary <string, object> >();
foreach (var item in summaryList)
{
var accountsList = new List <object>();
foreach (var a in item.Accounts)
{
var accountDictionary = new Dictionary <string, object>();
accountDictionary.Add("Account", a.AccountName);
accountDictionary.Add("TotalAmount", a.TotalAmount);
accountsList.Add(accountDictionary);
}
var yearDictionary = new Dictionary <string, object>();
yearDictionary.Add("Year", item.Year);
yearDictionary.Add("SummaryRows", accountsList);
yearsMergeObjects.Add(yearDictionary);
}
mergeObjects.Add("Rows", yearsMergeObjects);
lLavaOutput.Text = string.Empty;
if (GetAttributeValue("EnableDebug").AsBooleanOrNull().GetValueOrDefault(false))
{
lLavaOutput.Text = mergeObjects.lavaDebugInfo(rockContext);
}
string template = GetAttributeValue("LavaTemplate");
lLavaOutput.Text += template.ResolveMergeFields(mergeObjects).ResolveClientIds(upnlContent.ClientID);
}