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