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