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); }
public IHttpActionResult ChannelItems(int contentChannelId, string tag = "", int take = 20, int page = 0, bool hideInactive = false, string orderby = "StartDateTime", bool reverse = false) { var cacheKey = Request.RequestUri.ToString(); RockContext rockContext = new RockContext(); ContentChannelItemService contentChannelItemService = new ContentChannelItemService(rockContext); ContentChannelService contentChannelService = new ContentChannelService(rockContext); var contentChannel = contentChannelService.Get(contentChannelId); if (!contentChannel.IsAuthorized(Rock.Security.Authorization.VIEW, GetPerson())) { throw new HttpResponseException(HttpStatusCode.Unauthorized); } var contentItems = contentChannelItemService.Queryable().Where(c => c.ContentChannelId == contentChannelId); if (hideInactive) { contentItems = contentItems.Where(i => i.Status == ContentChannelItemStatus.Approved); } if (tag.IsNotNullOrWhiteSpace()) { TagService tagService = new TagService(rockContext); var taggedItemGuids = tagService.Queryable().Where(i => i.EntityTypeId == contentChanelItemEntityTypeId && i.Name == tag) .SelectMany(t => t.TaggedItems).Select(ti => ti.EntityGuid); contentItems = contentItems.Where(c => taggedItemGuids.Contains(c.Guid)); } if (reverse) { contentItems = contentItems.OrderByDescending(orderby); } else { contentItems = contentItems.OrderBy(orderby); } var contentChannelTypeId = contentChannel.ContentChannelTypeId.ToString(); var contentChanelIdString = contentChannel.Id.ToString(); var attributeQry = new AttributeService(rockContext).Queryable() .Where(a => a.EntityTypeId == contentChanelItemEntityTypeId && ((a.EntityTypeQualifierColumn == "ContentChannelTypeId" && a.EntityTypeQualifierValue == contentChannelTypeId) || (a.EntityTypeQualifierColumn == "ContentChannelId" && a.EntityTypeQualifierValue == contentChanelIdString))) ; var attributeValueQry = new AttributeValueService(rockContext).Queryable() .Where(av => attributeQry.Select(a => a.Id).Contains(av.AttributeId)); //AttributeFiltering var ignoreKeys = new List <string> { "contentchannelid", "tag", "take", "page", "hideInactive", "orderby", "reverse" }; var urlKeyValues = ControllerContext.Request.GetQueryNameValuePairs(); foreach (var pair in urlKeyValues) { if (!ignoreKeys.Contains(pair.Key.ToLower())) { var filterQry = new AttributeValueService(rockContext).Queryable() .Where(av => attributeQry.Where(a => a.Key == pair.Key).Select(a => a.Id).Contains(av.AttributeId)) .Where(av => av.Value == pair.Value); contentItems = contentItems.Where(i => filterQry.Select(av => av.EntityId).Contains(i.Id)); } } //Pagination if (page > 0) { var skip = (page - 1) * take; contentItems = contentItems.Skip(skip); contentItems = contentItems.Take(take); } //Join attributes and other content channel items var complex = contentItems .GroupJoin(attributeValueQry, i => i.Id, av => av.EntityId, (i, av) => new { ContentChanelItem = i, Children = i.ChildItems.OrderBy(ci => ci.Order).Select(ci => ci.ChildContentChannelItemId), ParentItems = i.ParentItems.Select(ci => ci.ContentChannelItemId), AttributeValues = av }); var items = complex.ToList() .Select(i => new ChannelItem { Id = i.ContentChanelItem.Id, DateTime = i.ContentChanelItem.StartDateTime, Title = i.ContentChanelItem.Title, Content = i.ContentChanelItem.Content, Priority = i.ContentChanelItem.Priority, Order = i.ContentChanelItem.Order, Attributes = i.AttributeValues.ToDictionary(a => a.AttributeKey, a => a.Value), Tags = GetTags(i.ContentChanelItem, rockContext), Slug = i.ContentChanelItem.PrimarySlug, CreatedBy = i.ContentChanelItem.CreatedByPersonName, ChildItems = i.Children.ToList(), ParentItems = i.ParentItems.ToList() }).ToList(); return(Json(items)); }