public StatementGeneratorRecipientResult GetStatementGeneratorRecipientResult(int groupId, int?personId, Guid?locationGuid, [FromBody] StatementGeneratorOptions options)
{
if (options == null)
{
throw new Exception("StatementGenerationOption options must be specified");
}
if (options.LayoutDefinedValueGuid == null)
{
throw new Exception("LayoutDefinedValueGuid option must be specified");
}
var result = new StatementGeneratorRecipientResult();
result.GroupId = groupId;
result.PersonId = personId;
using (var rockContext = new RockContext())
{
var financialTransactionQry = this.GetFinancialTransactionQuery(options, rockContext, false);
var financialPledgeQry = GetFinancialPledgeQuery(options, rockContext, false);
var personList = new List <Person>();
Person person = null;
if (personId.HasValue)
{
person = new PersonService(rockContext).Queryable().Include(a => a.Aliases).Where(a => a.Id == personId.Value).FirstOrDefault();
personList.Add(person);
}
else
{
// get transactions for all the persons in the specified group that have specified that group as their GivingGroup
GroupMemberService groupMemberService = new GroupMemberService(rockContext);
personList = groupMemberService.GetByGroupId(groupId).Where(a => a.Person.GivingGroupId == groupId).Select(s => s.Person).Include(a => a.Aliases).ToList();
person = personList.FirstOrDefault();
}
if (options.ExcludeOptedOutIndividuals == true && !options.DataViewId.HasValue)
{
int?doNotSendGivingStatementAttributeId = AttributeCache.Get(Rock.StatementGenerator.SystemGuid.Attribute.PERSON_DO_NOT_SEND_GIVING_STATEMENT.AsGuid())?.Id;
if (doNotSendGivingStatementAttributeId.HasValue)
{
var personIds = personList.Select(a => a.Id).ToList();
var optedOutPersonQry = new AttributeValueService(rockContext).Queryable().Where(a => a.AttributeId == doNotSendGivingStatementAttributeId);
if (personIds.Count == 1)
{
int entityPersonId = personIds[0];
optedOutPersonQry = optedOutPersonQry.Where(a => a.EntityId == entityPersonId);
}
else
{
optedOutPersonQry = optedOutPersonQry.Where(a => personIds.Contains(a.EntityId.Value));
}
var optedOutPersonIds = optedOutPersonQry
.Select(a => new
{
PersonId = a.EntityId.Value,
a.Value
}).ToList().Where(a => a.Value.AsBoolean() == true).Select(a => a.PersonId).ToList();
if (optedOutPersonIds.Any())
{
bool givingLeaderOptedOut = personList.Any(a => optedOutPersonIds.Contains(a.Id) && a.GivingLeaderId == a.Id);
var remaingPersonIds = personList.Where(a => !optedOutPersonIds.Contains(a.Id)).ToList();
if (givingLeaderOptedOut || !remaingPersonIds.Any())
{
// If the giving leader opted out, or if there aren't any people in the giving statement that haven't opted out, return NULL and OptedOut = true
result.OptedOut = true;
result.Html = null;
return(result);
}
}
}
}
var personAliasIds = personList.SelectMany(a => a.Aliases.Select(x => x.Id)).ToList();
if (personAliasIds.Count == 1)
{
var personAliasId = personAliasIds[0];
financialTransactionQry = financialTransactionQry.Where(a => a.AuthorizedPersonAliasId.Value == personAliasId);
}
else
{
financialTransactionQry = financialTransactionQry.Where(a => personAliasIds.Contains(a.AuthorizedPersonAliasId.Value));
}
var financialTransactionsList = financialTransactionQry
.Include(a => a.FinancialPaymentDetail)
.Include(a => a.FinancialPaymentDetail.CurrencyTypeValue)
.Include(a => a.TransactionDetails)
.Include(a => a.TransactionDetails.Select(x => x.Account))
.OrderBy(a => a.TransactionDateTime).ToList();
foreach (var financialTransaction in financialTransactionsList)
{
if (options.TransactionAccountIds != null)
{
// remove any Accounts that were not included (in case there was a mix of included and not included accounts in the transaction)
financialTransaction.TransactionDetails = financialTransaction.TransactionDetails.Where(a => options.TransactionAccountIds.Contains(a.AccountId)).ToList();
}
financialTransaction.TransactionDetails = financialTransaction.TransactionDetails.OrderBy(a => a.Account.Order).ThenBy(a => a.Account.Name).ToList();
}
var lavaTemplateValue = DefinedValueCache.Get(options.LayoutDefinedValueGuid.Value);
var lavaTemplateLava = lavaTemplateValue.GetAttributeValue("LavaTemplate");
var lavaTemplateFooterLava = lavaTemplateValue.GetAttributeValue("FooterHtml");
var mergeFields = Rock.Lava.LavaHelper.GetCommonMergeFields(null, null, new Lava.CommonMergeFieldsOptions {
GetLegacyGlobalMergeFields = false, GetDeviceFamily = false, GetOSFamily = false, GetPageContext = false, GetPageParameters = false, GetCampuses = true, GetCurrentPerson = true
});
mergeFields.Add("LavaTemplate", lavaTemplateValue);
mergeFields.Add("PersonList", personList);
mergeFields.Add("StatementStartDate", options.StartDate);
var humanFriendlyEndDate = options.EndDate.HasValue ? options.EndDate.Value.AddDays(-1) : RockDateTime.Now.Date;
mergeFields.Add("StatementEndDate", humanFriendlyEndDate);
var familyTitle = Rock.Data.RockUdfHelper.ufnCrm_GetFamilyTitle(rockContext, personId, groupId, null, false, !options.ExcludeInActiveIndividuals);
mergeFields.Add("Salutation", familyTitle);
Location mailingAddress;
if (locationGuid.HasValue)
{
// get the location that was specified for the recipient
mailingAddress = new LocationService(rockContext).Get(locationGuid.Value);
}
else
{
// for backwards compatibility, get the first address
IQueryable <GroupLocation> groupLocationsQry = GetGroupLocationQuery(rockContext);
mailingAddress = groupLocationsQry.Where(a => a.GroupId == groupId).Select(a => a.Location).FirstOrDefault();
}
mergeFields.Add("MailingAddress", mailingAddress);
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 transactionDetailListAll = financialTransactionsList.SelectMany(a => a.TransactionDetails).ToList();
if (options.HideRefundedTransactions && transactionDetailListAll.Any(a => a.Amount < 0))
{
var allRefunds = transactionDetailListAll.SelectMany(a => a.Transaction.Refunds).ToList();
foreach (var refund in allRefunds)
{
foreach (var refundedOriginalTransactionDetail in refund.OriginalTransaction.TransactionDetails)
{
// remove the refund's original TransactionDetails from the results
if (transactionDetailListAll.Contains(refundedOriginalTransactionDetail))
{
transactionDetailListAll.Remove(refundedOriginalTransactionDetail);
foreach (var refundDetailId in refund.FinancialTransaction.TransactionDetails.Select(a => a.Id))
{
// remove the refund's transaction from the results
var refundDetail = transactionDetailListAll.FirstOrDefault(a => a.Id == refundDetailId);
if (refundDetail != null)
{
transactionDetailListAll.Remove(refundDetail);
}
}
}
}
}
}
if (options.HideCorrectedTransactions && transactionDetailListAll.Any(a => a.Amount < 0))
{
// Hide transactions that are corrected on the same date. Transactions that have a matching negative dollar amount on the same date and same account will not be shown.
// get a list of dates that have at least one negative transaction
var transactionsByDateList = transactionDetailListAll.GroupBy(a => a.Transaction.TransactionDateTime.Value.Date).Select(a => new
{
Date = a.Key,
TransactionDetails = a.ToList()
})
.Where(a => a.TransactionDetails.Any(x => x.Amount < 0))
.ToList();
foreach (var transactionsByDate in transactionsByDateList)
{
foreach (var negativeTransaction in transactionsByDate.TransactionDetails.Where(a => a.Amount < 0))
{
// find the first transaction that has an amount that matches the negative amount (on the same day and same account)
// and make sure the matching transaction doesn't already have a refund associated with it
var correctedTransactionDetail = transactionsByDate.TransactionDetails
.Where(a => (a.Amount == (-negativeTransaction.Amount) && a.AccountId == negativeTransaction.AccountId) && !a.Transaction.Refunds.Any())
.FirstOrDefault();
if (correctedTransactionDetail != null)
{
// if the transaction was corrected, remove it, and also remove the associated correction (the negative one) transaction
transactionDetailListAll.Remove(correctedTransactionDetail);
transactionDetailListAll.Remove(negativeTransaction);
}
}
}
}
List <FinancialTransactionDetail> transactionDetailListCash = transactionDetailListAll;
List <FinancialTransactionDetail> transactionDetailListNonCash = new List <FinancialTransactionDetail>();
if (options.CurrencyTypeIdsCash != null)
{
// NOTE: if there isn't a FinancialPaymentDetail record, assume it is Cash
transactionDetailListCash = transactionDetailListCash.Where(a =>
(a.Transaction.FinancialPaymentDetailId == null) ||
(a.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.HasValue && options.CurrencyTypeIdsCash.Contains(a.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.Value))).ToList();
}
if (options.CurrencyTypeIdsNonCash != null)
{
transactionDetailListNonCash = transactionDetailListAll.Where(a =>
a.Transaction.FinancialPaymentDetailId.HasValue &&
a.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.HasValue &&
options.CurrencyTypeIdsNonCash.Contains(a.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.Value)).ToList();
}
// Add Merge Fields for Transactions for custom Statements that might want to organize the output by Transaction instead of TransactionDetail
var transactionListCash = transactionDetailListCash.GroupBy(a => a.Transaction).Select(a => a.Key).ToList();
var transactionListNonCash = transactionDetailListNonCash.GroupBy(a => a.Transaction).Select(a => a.Key).ToList();
mergeFields.Add("Transactions", transactionListCash);
mergeFields.Add("TransactionsNonCash", transactionListNonCash);
// Add the standard TransactionDetails and TransactionDetailsNonCash that the default Rock templates use
mergeFields.Add("TransactionDetails", transactionDetailListCash);
mergeFields.Add("TransactionDetailsNonCash", transactionDetailListNonCash);
mergeFields.Add("TotalContributionAmount", transactionDetailListCash.Sum(a => a.Amount));
mergeFields.Add("TotalContributionCount", transactionDetailListCash.Count());
mergeFields.Add("TotalContributionAmountNonCash", transactionDetailListNonCash.Sum(a => a.Amount));
mergeFields.Add("TotalContributionCountNonCash", transactionDetailListNonCash.Count());
mergeFields.Add("AccountSummary", transactionDetailListCash.GroupBy(t => t.Account.Name).Select(s => new { AccountName = s.Key, Total = s.Sum(a => a.Amount), Order = s.Max(a => a.Account.Order) }).OrderBy(s => s.Order));
mergeFields.Add("AccountSummaryNonCash", transactionDetailListNonCash.GroupBy(t => t.Account.Name).Select(s => new { AccountName = s.Key, Total = s.Sum(a => a.Amount), Order = s.Max(a => a.Account.Order) }).OrderBy(s => s.Order));
if (options.PledgesAccountIds.Any())
{
var pledgeList = financialPledgeQry
.Where(p => p.PersonAliasId.HasValue && personAliasIds.Contains(p.PersonAliasId.Value))
.Include(a => a.Account)
.OrderBy(a => a.Account.Order)
.ThenBy(a => a.Account.PublicName)
.ToList();
var pledgeSummaryByPledgeList = pledgeList
.Select(p => new
{
p.Account,
Pledge = p
})
.ToList();
//// Pledges but organized by Account (in case more than one pledge goes to the same account)
//// NOTE: In the case of multiple pledges to the same account (just in case they accidently or intentionally had multiple pledges to the same account)
//// -- Date Range
//// -- StartDate: Earliest StartDate of all the pledges for that account
//// -- EndDate: Lastest EndDate of all the pledges for that account
//// -- Amount Pledged: Sum of all Pledges to that account
//// -- Amount Given:
//// -- The sum of transaction amounts to that account between
//// -- Start Date: Earliest Start Date of all the pledges to that account
//// -- End Date: Whatever is earlier (Statement End Date or Pledges' End Date)
var pledgeSummaryList = pledgeSummaryByPledgeList.GroupBy(a => a.Account).Select(a => new PledgeSummary
{
Account = a.Key,
PledgeList = a.Select(x => x.Pledge).ToList()
}).ToList();
// add detailed pledge information
if (pledgeSummaryList.Any())
{
int statementPledgeYear = options.StartDate.Value.Year;
List <int> pledgeCurrencyTypeIds = null;
if (options.CurrencyTypeIdsCash != null)
{
pledgeCurrencyTypeIds = options.CurrencyTypeIdsCash;
if (options.PledgesIncludeNonCashGifts && options.CurrencyTypeIdsNonCash != null)
{
pledgeCurrencyTypeIds = options.CurrencyTypeIdsCash.Union(options.CurrencyTypeIdsNonCash).ToList();
}
}
foreach (var pledgeSummary in pledgeSummaryList)
{
DateTime adjustedPledgeEndDate = pledgeSummary.PledgeEndDate.Value.Date;
if (pledgeSummary.PledgeEndDate.Value.Date < DateTime.MaxValue.Date)
{
adjustedPledgeEndDate = pledgeSummary.PledgeEndDate.Value.Date.AddDays(1);
}
if (options.EndDate.HasValue)
{
if (adjustedPledgeEndDate > options.EndDate.Value)
{
adjustedPledgeEndDate = options.EndDate.Value;
}
}
var pledgeFinancialTransactionDetailQry = new FinancialTransactionDetailService(rockContext).Queryable().Where(t =>
t.Transaction.AuthorizedPersonAliasId.HasValue && personAliasIds.Contains(t.Transaction.AuthorizedPersonAliasId.Value) &&
t.Transaction.TransactionDateTime >= pledgeSummary.PledgeStartDate &&
t.Transaction.TransactionDateTime < adjustedPledgeEndDate);
if (options.PledgesIncludeChildAccounts)
{
// If PledgesIncludeChildAccounts = true, we'll include transactions to those child accounts as part of the pledge (but only one level deep)
pledgeFinancialTransactionDetailQry = pledgeFinancialTransactionDetailQry.Where(t =>
t.AccountId == pledgeSummary.AccountId
||
(t.Account.ParentAccountId.HasValue && t.Account.ParentAccountId == pledgeSummary.AccountId)
);
}
else
{
pledgeFinancialTransactionDetailQry = pledgeFinancialTransactionDetailQry.Where(t => t.AccountId == pledgeSummary.AccountId);
}
if (pledgeCurrencyTypeIds != null)
{
pledgeFinancialTransactionDetailQry = pledgeFinancialTransactionDetailQry.Where(t =>
t.Transaction.FinancialPaymentDetailId.HasValue &&
t.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.HasValue && pledgeCurrencyTypeIds.Contains(t.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.Value));
}
pledgeSummary.AmountGiven = pledgeFinancialTransactionDetailQry.Sum(t => ( decimal? )t.Amount) ?? 0;
}
}
// Pledges ( organized by each Account in case an account is used by more than one pledge )
mergeFields.Add("Pledges", pledgeSummaryList);
}
mergeFields.Add("Options", options);
var currentPerson = this.GetPerson();
result.Html = lavaTemplateLava.ResolveMergeFields(mergeFields, currentPerson);
if (!string.IsNullOrEmpty(lavaTemplateFooterLava))
{
result.FooterHtml = lavaTemplateFooterLava.ResolveMergeFields(mergeFields, currentPerson);
}
result.Html = result.Html.Trim();
}
return(result);
}
/// <summary>
/// Creates the table controls.
/// </summary>
/// <param name="batchId">The batch identifier.</param>
/// <param name="dataViewId">The data view identifier.</param>
private void BindHtmlGrid(int?batchId, int?dataViewId)
{
_financialTransactionDetailList = null;
RockContext rockContext = new RockContext();
nbSaveSuccess.Visible = false;
btnSave.Visible = false;
List <DataControlField> tableColumns = new List <DataControlField>();
tableColumns.Add(new RockLiteralField {
ID = "lPerson", HeaderText = "Person"
});
tableColumns.Add(new RockLiteralField {
ID = "lAmount", HeaderText = "Amount"
});
tableColumns.Add(new RockLiteralField {
ID = "lAccount", HeaderText = "Account"
});
tableColumns.Add(new RockLiteralField {
ID = "lTransactionType", HeaderText = "Transaction Type"
});
string entityColumnHeading = this.GetAttributeValue("EntityColumnHeading");
if (string.IsNullOrEmpty(entityColumnHeading))
{
if (_transactionEntityType != null)
{
entityColumnHeading = _entityTypeQualifiedName;
}
}
tableColumns.Add(new RockLiteralField {
ID = "lEntityColumn", HeaderText = entityColumnHeading
});
var additionalColumns = this.GetAttributeValue(CustomGridColumnsConfig.AttributeKey).FromJsonOrNull <CustomGridColumnsConfig>();
if (additionalColumns != null)
{
foreach (var columnConfig in additionalColumns.ColumnsConfig)
{
int insertPosition;
if (columnConfig.PositionOffsetType == CustomGridColumnsConfig.ColumnConfig.OffsetType.LastColumn)
{
insertPosition = tableColumns.Count - columnConfig.PositionOffset;
}
else
{
insertPosition = columnConfig.PositionOffset;
}
var column = columnConfig.GetGridColumn();
tableColumns.Insert(insertPosition, column);
insertPosition++;
}
}
StringBuilder headers = new StringBuilder();
foreach (var tableColumn in tableColumns)
{
if (tableColumn.HeaderStyle.CssClass.IsNotNullOrWhiteSpace())
{
headers.AppendFormat("<th class='{0}'>{1}</th>", tableColumn.HeaderStyle.CssClass, tableColumn.HeaderText);
}
else
{
headers.AppendFormat("<th>{0}</th>", tableColumn.HeaderText);
}
}
lHeaderHtml.Text = headers.ToString();
int?transactionId = this.PageParameter("TransactionId").AsIntegerOrNull();
if (batchId.HasValue || dataViewId.HasValue || transactionId.HasValue)
{
var financialTransactionDetailQuery = new FinancialTransactionDetailService(rockContext).Queryable()
.Include(a => a.Transaction)
.Include(a => a.Transaction.AuthorizedPersonAlias.Person);
if (batchId.HasValue)
{
financialTransactionDetailQuery = financialTransactionDetailQuery.Where(a => a.Transaction.BatchId == batchId.Value);
}
if (dataViewId.HasValue && dataViewId > 0)
{
var dataView = new DataViewService(rockContext).Get(dataViewId.Value);
List <string> errorMessages;
var transactionDetailIdsQry = dataView.GetQuery(null, rockContext, null, out errorMessages).Select(a => a.Id);
financialTransactionDetailQuery = financialTransactionDetailQuery.Where(a => transactionDetailIdsQry.Contains(a.Id));
}
if (transactionId.HasValue)
{
financialTransactionDetailQuery = financialTransactionDetailQuery.Where(a => transactionId == a.TransactionId);
}
int maxResults = this.GetAttributeValue("MaxNumberofResults").AsIntegerOrNull() ?? 1000;
_financialTransactionDetailList = financialTransactionDetailQuery.OrderByDescending(a => a.Transaction.TransactionDateTime).Take(maxResults).ToList();
phTableRows.Controls.Clear();
btnSave.Visible = _financialTransactionDetailList.Any();
string appRoot = this.ResolveRockUrl("~/");
string themeRoot = this.ResolveRockUrl("~~/");
foreach (var financialTransactionDetail in _financialTransactionDetailList)
{
var tr = new HtmlGenericContainer("tr");
foreach (var tableColumn in tableColumns)
{
var literalControl = new LiteralControl();
if (tableColumn is RockLiteralField)
{
tr.Controls.Add(literalControl);
var literalTableColumn = tableColumn as RockLiteralField;
if (literalTableColumn.ID == "lPerson")
{
literalControl.Text = string.Format("<td>{0}</td>", financialTransactionDetail.Transaction.AuthorizedPersonAlias);
}
else if (literalTableColumn.ID == "lAmount")
{
literalControl.Text = string.Format("<td>{0}</td>", financialTransactionDetail.Amount.FormatAsCurrency());
}
else if (literalTableColumn.ID == "lAccount")
{
literalControl.Text = string.Format("<td>{0}</td>", financialTransactionDetail.Account.ToString());
}
else if (literalTableColumn.ID == "lTransactionType")
{
literalControl.ID = "lTransactionType_" + financialTransactionDetail.Id.ToString();
literalControl.Text = string.Format("<td>{0}</td>", financialTransactionDetail.Transaction.TransactionTypeValue);
}
else if (literalTableColumn.ID == "lEntityColumn")
{
var tdEntityControls = new HtmlGenericContainer("td")
{
ID = "pnlEntityControls_" + financialTransactionDetail.Id.ToString()
};
tr.Controls.Add(tdEntityControls);
if (_transactionEntityType != null)
{
if (_transactionEntityType.Id == EntityTypeCache.GetId <GroupMember>())
{
var ddlGroup = new RockDropDownList {
ID = "ddlGroup_" + financialTransactionDetail.Id.ToString(), EnhanceForLongLists = true
};
ddlGroup.Label = "Group";
ddlGroup.AutoPostBack = true;
ddlGroup.SelectedIndexChanged += ddlGroup_SelectedIndexChanged;
tdEntityControls.Controls.Add(ddlGroup);
var ddlGroupMember = new RockDropDownList {
ID = "ddlGroupMember_" + financialTransactionDetail.Id.ToString(), Visible = false, EnhanceForLongLists = true
};
ddlGroupMember.Label = "Group Member";
tdEntityControls.Controls.Add(ddlGroupMember);
}
else if (_transactionEntityType.Id == EntityTypeCache.GetId <Group>())
{
var ddlGroup = new RockDropDownList {
ID = "ddlGroup_" + financialTransactionDetail.Id.ToString(), EnhanceForLongLists = true
};
ddlGroup.AutoPostBack = false;
tdEntityControls.Controls.Add(ddlGroup);
}
else if (_transactionEntityType.Id == EntityTypeCache.GetId <DefinedValue>())
{
var ddlDefinedValue = new DefinedValuePicker {
ID = "ddlDefinedValue_" + financialTransactionDetail.Id.ToString(), EnhanceForLongLists = true
};
tdEntityControls.Controls.Add(ddlDefinedValue);
}
else if (_transactionEntityType.SingleValueFieldType != null)
{
var entityPicker = _transactionEntityType.SingleValueFieldType.Field.EditControl(new Dictionary <string, Rock.Field.ConfigurationValue>(), "entityPicker_" + financialTransactionDetail.Id.ToString());
tdEntityControls.Controls.Add(entityPicker);
}
}
}
}
else if (tableColumn is LavaField)
{
tr.Controls.Add(literalControl);
var lavaField = tableColumn as LavaField;
Dictionary <string, object> mergeValues = new Dictionary <string, object>();
mergeValues.Add("Row", financialTransactionDetail);
string lavaOutput = lavaField.LavaTemplate.ResolveMergeFields(mergeValues);
// Resolve any dynamic url references
lavaOutput = lavaOutput.Replace("~~/", themeRoot).Replace("~/", appRoot);
if (lavaField.ItemStyle.CssClass.IsNotNullOrWhiteSpace())
{
literalControl.Text = string.Format("<td class='{0}'>{1}</td>", lavaField.ItemStyle.CssClass, lavaOutput);
}
else
{
literalControl.Text = string.Format("<td>{0}</td>", lavaOutput);
}
}
}
phTableRows.Controls.Add(tr);
pnlTransactions.Visible = true;
}
}
else
{
pnlTransactions.Visible = false;
}
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override System.Linq.Expressions.Expression GetExpression(Data.RockContext context, System.Linq.Expressions.MemberExpression entityIdProperty, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length < 4)
{
return(null);
}
/* 2020-05-19 MDP
* The TotalAmount Comparison logic is that the displayed TotalAmount will show blank if the criteria doesn't match
* For example:
* Total Amount >= $100.00
* If a person's total giving is $100.01, $100.01 will be displayed as the total giving in the report
* If the person's total giving is $99.99, the total giving in the report will just show blank
*
*
* This display logic is done in the GetGridField method
*/
DateRange dateRange;
if (selectionValues.Length >= 7)
{
string slidingDelimitedValues = selectionValues[6].Replace(',', '|');
dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(slidingDelimitedValues);
}
else
{
// if converting from a previous version of the selection
DateTime?startDate = selectionValues[2].AsDateTime();
DateTime?endDate = selectionValues[3].AsDateTime();
dateRange = new DateRange(startDate, endDate);
if (dateRange.End.HasValue)
{
// the DateRange picker doesn't automatically add a full day to the end date
dateRange.End.Value.AddDays(1);
}
}
var accountIdList = new List <int>();
if (selectionValues.Length >= 5)
{
var accountGuids = selectionValues[4].Split(',').Select(a => a.AsGuid()).Where(a => a != Guid.Empty).ToList();
accountIdList = new FinancialAccountService(context).GetByGuids(accountGuids).Select(a => a.Id).ToList();
}
bool combineGiving = false;
if (selectionValues.Length >= 6)
{
combineGiving = selectionValues[5].AsBooleanOrNull() ?? false;
}
bool useAnalytics = false;
if (selectionValues.Length >= 8)
{
useAnalytics = selectionValues[7].AsBooleanOrNull() ?? false;
}
int transactionTypeContributionId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid()).Id;
IQueryable <decimal> personTotalAmountQry;
if (useAnalytics)
{
/* 2020-05-20 MDP
* Analytics tables don't have a reference between a transaction and it's refund (unless we join the analytics tables to the TransactionRefund table).
* That isn't a problem unless the refund for a transaction is later than the specified date range.
* We discussed this and decided to not worry abou the late refund problem right now.
*
* Also, the total giving will be correct even when factoring in refunds
* because the Analytics tables will have a negative amount for refund transactions
*
*/
var analyticsFinancialTransactionQry = new AnalyticsSourceFinancialTransactionService(context).Queryable()
.Where(xx => xx.TransactionTypeValueId == transactionTypeContributionId)
.Where(xx => xx.AuthorizedPersonAliasId.HasValue);
if (dateRange.Start.HasValue)
{
analyticsFinancialTransactionQry = analyticsFinancialTransactionQry.Where(xx => xx.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
analyticsFinancialTransactionQry = analyticsFinancialTransactionQry.Where(xx => xx.TransactionDateTime < dateRange.End.Value);
}
bool limitToAccounts = accountIdList.Any();
if (limitToAccounts)
{
analyticsFinancialTransactionQry = analyticsFinancialTransactionQry.Where(xx => xx.AccountId.HasValue && accountIdList.Contains(xx.AccountId.Value));
}
if (combineGiving)
{
var analyticsPersonAmountQry = new AnalyticsDimPersonCurrentService(context).Queryable()
.Join(analyticsFinancialTransactionQry, p => p.GivingId, f => f.GivingId, (p, f) => new
{
p.PersonId,
f.Amount
});
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => analyticsPersonAmountQry
.Where(ww => ww.PersonId == p.Id)
.Sum(ww => ww.Amount));
}
else
{
var analyticsPersonAmountQry = new AnalyticsDimPersonCurrentService(context).Queryable()
.Join(analyticsFinancialTransactionQry, p => p.Id, f => f.AuthorizedPersonKey, (p, f) => new
{
p.PersonId,
f.Amount
});
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => analyticsPersonAmountQry
.Where(ww => ww.PersonId == p.Id)
.Sum(ww => ww.Amount));
}
}
else
{
var financialTransactionQry = new FinancialTransactionDetailService(context).Queryable()
.Where(xx => xx.Transaction.TransactionTypeValueId == transactionTypeContributionId)
.Where(xx => xx.Transaction.AuthorizedPersonAliasId.HasValue);
if (dateRange.Start.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime < dateRange.End.Value);
}
bool limitToAccounts = accountIdList.Any();
if (limitToAccounts)
{
financialTransactionQry = financialTransactionQry.Where(xx => accountIdList.Contains(xx.AccountId));
}
// exclude the financial transactions that were used for refunds.
// This is because we'll get the refund transactions of each non-refund transaction when getting the total amount
var refundsQry = new FinancialTransactionRefundService(context).Queryable();
financialTransactionQry = financialTransactionQry.Where(xx => !refundsQry.Any(r => r.Id == xx.TransactionId));
/* 2020-05-02 MDP
* To factor in Refunds, subtract (but actually add since the amount will be negative)
* the refund amount if there is a refund associated with that transaction.
*
* Also, don't apply a date filter on the refund since we want to factor in refunds
* that might have occurred after the date range
*
* The Linq is written in a way to avoid the RefundAmount getting queried twice (once if it is null and another if it not null)
*/
if (combineGiving)
{
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p =>
financialTransactionQry.Where(ww => p.GivingId == ww.Transaction.AuthorizedPersonAlias.Person.GivingId
)
.Select(x => new
{
x.Amount,
RefundAmount = (x.Transaction.RefundDetails.FinancialTransaction
.TransactionDetails
.Where(r => r.AccountId == x.AccountId)
.Sum(r => ( decimal? )r.Amount)
)
})
.Sum
(
aa => aa.Amount + (aa.RefundAmount ?? 0.00M)
)
);
}
else
{
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p =>
financialTransactionQry.Where(ww => ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id
)
.Select(x => new
{
x.Amount,
RefundAmount = (x.Transaction.RefundDetails.FinancialTransaction
.TransactionDetails
.Where(r => r.AccountId == x.AccountId)
.Sum(r => ( decimal? )r.Amount)
)
})
.Sum
(
aa => aa.Amount + (aa.RefundAmount ?? 0.00M)
)
);
}
}
var selectExpression = SelectExpressionExtractor.Extract(personTotalAmountQry, entityIdProperty, "p");
return(selectExpression);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override System.Linq.Expressions.Expression GetExpression(Data.RockContext context, System.Linq.Expressions.MemberExpression entityIdProperty, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length < 4)
{
return(null);
}
ComparisonType comparisonType = selectionValues[0].ConvertToEnum <ComparisonType>(ComparisonType.GreaterThanOrEqualTo);
decimal amount = selectionValues[1].AsDecimalOrNull() ?? 0.00M;
DateTime? startDate = selectionValues[2].AsDateTime();
DateTime? endDate = selectionValues[3].AsDateTime();
var accountIdList = new List <int>();
if (selectionValues.Length >= 5)
{
var accountGuids = selectionValues[4].Split(',').Select(a => a.AsGuid()).ToList();
accountIdList = new FinancialAccountService(context).GetByGuids(accountGuids).Select(a => a.Id).ToList();
}
bool combineGiving = false;
if (selectionValues.Length >= 6)
{
combineGiving = selectionValues[5].AsBooleanOrNull() ?? false;
}
int transactionTypeContributionId = Rock.Web.Cache.DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid()).Id;
var financialTransactionQry = new FinancialTransactionDetailService(context).Queryable()
.Where(xx => xx.Transaction.TransactionTypeValueId == transactionTypeContributionId)
.Where(xx => xx.Transaction.AuthorizedPersonAliasId.HasValue);
if (startDate.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime >= startDate.Value);
}
if (endDate.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime < endDate.Value);
}
bool limitToAccounts = accountIdList.Any();
if (limitToAccounts)
{
financialTransactionQry = financialTransactionQry.Where(xx => accountIdList.Contains(xx.AccountId));
}
if (comparisonType == ComparisonType.LessThan)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount < amount);
}
else if (comparisonType == ComparisonType.EqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount == amount);
}
else if (comparisonType == ComparisonType.GreaterThanOrEqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount >= amount);
}
IQueryable <decimal> personTotalAmountQry;
if (combineGiving)
{
//// if combineGiving..
// if they aren't in a giving group, sum up transactions amounts by the person
// if they are in a giving group, sum up transactions amounts by the persons that are in the person's giving group
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => financialTransactionQry
.Where(ww =>
(!p.GivingGroupId.HasValue && ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id)
||
(p.GivingGroupId.HasValue && ww.Transaction.AuthorizedPersonAlias.Person.GivingGroupId == p.GivingGroupId))
.Sum(aa => aa.Amount));
}
else
{
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => financialTransactionQry
.Where(ww => ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id)
.Sum(aa => aa.Amount));
}
var selectExpression = SelectExpressionExtractor.Extract(personTotalAmountQry, entityIdProperty, "p");
return(selectExpression);
}
/// <summary>
/// Gets the expression.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="entityIdProperty">The entity identifier property.</param>
/// <param name="selection">The selection.</param>
/// <returns></returns>
public override System.Linq.Expressions.Expression GetExpression(Data.RockContext context, System.Linq.Expressions.MemberExpression entityIdProperty, string selection)
{
string[] selectionValues = selection.Split('|');
if (selectionValues.Length < 4)
{
return(null);
}
ComparisonType comparisonType = selectionValues[0].ConvertToEnum <ComparisonType>(ComparisonType.GreaterThanOrEqualTo);
decimal amount = selectionValues[1].AsDecimalOrNull() ?? 0.00M;
DateRange dateRange;
if (selectionValues.Length >= 7)
{
string slidingDelimitedValues = selectionValues[6].Replace(',', '|');
dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(slidingDelimitedValues);
}
else
{
// if converting from a previous version of the selection
DateTime?startDate = selectionValues[2].AsDateTime();
DateTime?endDate = selectionValues[3].AsDateTime();
dateRange = new DateRange(startDate, endDate);
if (dateRange.End.HasValue)
{
// the DateRange picker doesn't automatically add a full day to the end date
dateRange.End.Value.AddDays(1);
}
}
var accountIdList = new List <int>();
if (selectionValues.Length >= 5)
{
var accountGuids = selectionValues[4].Split(',').Select(a => a.AsGuid()).ToList();
accountIdList = new FinancialAccountService(context).GetByGuids(accountGuids).Select(a => a.Id).ToList();
}
bool combineGiving = false;
if (selectionValues.Length >= 6)
{
combineGiving = selectionValues[5].AsBooleanOrNull() ?? false;
}
bool useAnalytics = false;
if (selectionValues.Length >= 8)
{
useAnalytics = selectionValues[7].AsBooleanOrNull() ?? false;
}
int transactionTypeContributionId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid()).Id;
IQueryable <decimal> personTotalAmountQry;
if (useAnalytics)
{
var financialTransactionQry = new AnalyticsSourceFinancialTransactionService(context).Queryable()
.Where(xx => xx.TransactionTypeValueId == transactionTypeContributionId)
.Where(xx => xx.AuthorizedPersonAliasId.HasValue);
if (dateRange.Start.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.TransactionDateTime < dateRange.End.Value);
}
bool limitToAccounts = accountIdList.Any();
if (limitToAccounts)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.AccountId.HasValue && accountIdList.Contains(xx.AccountId.Value));
}
if (comparisonType == ComparisonType.LessThan)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount < amount);
}
else if (comparisonType == ComparisonType.EqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount == amount);
}
else if (comparisonType == ComparisonType.GreaterThanOrEqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount >= amount);
}
if (combineGiving)
{
var personAmount = new AnalyticsDimPersonCurrentService(context).Queryable()
.Join(financialTransactionQry, p => p.GivingId, f => f.GivingId, (p, f) => new
{
p.PersonId,
f.Amount
});
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => personAmount
.Where(ww => ww.PersonId == p.Id)
.Sum(ww => ww.Amount));
}
else
{
var personAmount = new AnalyticsDimPersonCurrentService(context).Queryable()
.Join(financialTransactionQry, p => p.Id, f => f.AuthorizedPersonKey, (p, f) => new
{
p.PersonId,
f.Amount
});
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => personAmount
.Where(ww => ww.PersonId == p.Id)
.Sum(ww => ww.Amount));
}
}
else
{
var financialTransactionQry = new FinancialTransactionDetailService(context).Queryable()
.Where(xx => xx.Transaction.TransactionTypeValueId == transactionTypeContributionId)
.Where(xx => xx.Transaction.AuthorizedPersonAliasId.HasValue);
if (dateRange.Start.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime >= dateRange.Start.Value);
}
if (dateRange.End.HasValue)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Transaction.TransactionDateTime < dateRange.End.Value);
}
bool limitToAccounts = accountIdList.Any();
if (limitToAccounts)
{
financialTransactionQry = financialTransactionQry.Where(xx => accountIdList.Contains(xx.AccountId));
}
if (comparisonType == ComparisonType.LessThan)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount < amount);
}
else if (comparisonType == ComparisonType.EqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount == amount);
}
else if (comparisonType == ComparisonType.GreaterThanOrEqualTo)
{
financialTransactionQry = financialTransactionQry.Where(xx => xx.Amount >= amount);
}
if (combineGiving)
{
//// if combineGiving..
// if they aren't in a giving group, sum up transactions amounts by the person
// if they are in a giving group, sum up transactions amounts by the persons that are in the person's giving group
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => financialTransactionQry
.Where(ww =>
(!p.GivingGroupId.HasValue && ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id)
||
(p.GivingGroupId.HasValue && ww.Transaction.AuthorizedPersonAlias.Person.GivingGroupId == p.GivingGroupId))
.Sum(aa => aa.Amount));
}
else
{
personTotalAmountQry = new PersonService(context).Queryable()
.Select(p => financialTransactionQry
.Where(ww => ww.Transaction.AuthorizedPersonAlias.PersonId == p.Id)
.Sum(aa => aa.Amount));
}
}
var selectExpression = SelectExpressionExtractor.Extract(personTotalAmountQry, entityIdProperty, "p");
return(selectExpression);
}
/// <summary>
/// Creates the table controls.
/// </summary>
/// <param name="batchId">The batch identifier.</param>
private void CreateTableControls(int?batchId)
{
RockContext rockContext = new RockContext();
nbSaveSuccess.Visible = false;
btnSave.Visible = false;
if (_transactionEntityType != null)
{
lEntityHeaderText.Text = _transactionEntityType.FriendlyName;
}
if (batchId.HasValue)
{
var financialTransactionDetailQuery = new FinancialTransactionDetailService(rockContext).Queryable();
var financialTransactionDetailList = financialTransactionDetailQuery.Where(a => a.Transaction.BatchId == batchId.Value).OrderByDescending(a => a.Transaction.TransactionDateTime).ToList();
phTableRows.Controls.Clear();
btnSave.Visible = financialTransactionDetailList.Any();
foreach (var financialTransactionDetail in financialTransactionDetailList)
{
var tr = new HtmlGenericContainer("tr");
tr.Controls.Add(new LiteralControl {
Text = string.Format("<td>{0}</td>", financialTransactionDetail.Transaction.AuthorizedPersonAlias)
});
tr.Controls.Add(new LiteralControl {
Text = string.Format("<td>{0}</td>", financialTransactionDetail.Amount.FormatAsCurrency())
});
tr.Controls.Add(new LiteralControl {
Text = string.Format("<td>{0}</td>", financialTransactionDetail.Account)
});
tr.Controls.Add(new LiteralControl
{
ID = "lTransactionType_" + financialTransactionDetail.Id.ToString(),
Text = string.Format("<td>{0}</td>", financialTransactionDetail.Transaction.TransactionTypeValue)
});
var tdEntityControls = new HtmlGenericContainer("td")
{
ID = "pnlEntityControls_" + financialTransactionDetail.Id.ToString()
};
tr.Controls.Add(tdEntityControls);
if (_transactionEntityType != null)
{
if (_transactionEntityType.Id == EntityTypeCache.GetId <GroupMember>())
{
var ddlGroup = new RockDropDownList {
ID = "ddlGroup_" + financialTransactionDetail.Id.ToString()
};
ddlGroup.Label = "Group";
ddlGroup.AutoPostBack = true;
ddlGroup.SelectedIndexChanged += ddlGroup_SelectedIndexChanged;
tdEntityControls.Controls.Add(ddlGroup);
var ddlGroupMember = new RockDropDownList {
ID = "ddlGroupMember_" + financialTransactionDetail.Id.ToString(), Visible = false
};
ddlGroupMember.Label = "Group Member";
tdEntityControls.Controls.Add(ddlGroupMember);
}
else if (_transactionEntityType.Id == EntityTypeCache.GetId <Group>())
{
var ddlGroup = new RockDropDownList {
ID = "ddlGroup_" + financialTransactionDetail.Id.ToString()
};
ddlGroup.AutoPostBack = true;
ddlGroup.SelectedIndexChanged += ddlGroup_SelectedIndexChanged;
tdEntityControls.Controls.Add(ddlGroup);
}
else if (_transactionEntityType.Id == EntityTypeCache.GetId <DefinedValue>())
{
var ddlDefinedValue = new DefinedValuePicker {
ID = "ddlDefinedValue_" + financialTransactionDetail.Id.ToString()
};
tdEntityControls.Controls.Add(ddlDefinedValue);
}
else if (_transactionEntityType.SingleValueFieldType != null)
{
var entityPicker = _transactionEntityType.SingleValueFieldType.Field.EditControl(new Dictionary <string, Rock.Field.ConfigurationValue>(), "entityPicker_" + financialTransactionDetail.Id.ToString());
tdEntityControls.Controls.Add(entityPicker);
}
}
phTableRows.Controls.Add(tr);
pnlTransactions.Visible = true;
}
}
else
{
pnlTransactions.Visible = false;
}
}