/// <summary> /// Maps the contribution. /// </summary> /// <param name="csvData">The table data.</param> private int MapContribution(CSVInstance csvData) { var lookupContext = new RockContext(); var currencyTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE)); var currencyTypeACH = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH))).Id; var currencyTypeCash = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH))).Id; var currencyTypeCheck = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK))).Id; var currencyTypeCreditCard = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD))).Id; var currencyTypeNonCash = currencyTypes.DefinedValues.Where(dv => dv.Value.Equals("Non-Cash")).Select(dv => (int?)dv.Id).FirstOrDefault(); if (currencyTypeNonCash == null) { var newTenderNonCash = new DefinedValue { Value = "Non-Cash", Description = "Non-Cash", DefinedTypeId = currencyTypes.Id }; lookupContext.DefinedValues.Add(newTenderNonCash); lookupContext.SaveChanges(); currencyTypeNonCash = newTenderNonCash.Id; } var creditCardTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CREDIT_CARD_TYPE)).DefinedValues; var sourceTypeOnsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_ONSITE_COLLECTION), lookupContext).Id; var sourceTypeWebsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_WEBSITE), lookupContext).Id; var sourceTypeKiosk = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_KIOSK), lookupContext).Id; var refundReasons = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON), lookupContext).DefinedValues; var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); int?defaultBatchId = null; if (ImportedBatches.ContainsKey(0)) { defaultBatchId = ImportedBatches[0]; } // Look for custom attributes in the Contribution file var allFields = csvData.TableNodes.FirstOrDefault().Children.Select((node, index) => new { node = node, index = index }).ToList(); var customAttributes = allFields .Where(f => f.index > ContributionCreditCardType) .ToDictionary(f => f.index, f => f.node.Name); // Get all imported contributions var importedContributions = new FinancialTransactionService(lookupContext).Queryable().AsNoTracking() .Where(c => c.ForeignId != null) .Select(t => (int)t.ForeignId) .OrderBy(t => t).ToList(); // List for batching new contributions var newTransactions = new List <FinancialTransaction>(); var completed = 0; ReportProgress(0, $"Verifying contribution import ({importedContributions.Count:N0} already exist)."); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { var individualIdKey = row[IndividualID]; var contributionIdKey = row[ContributionID]; var contributionId = contributionIdKey.AsType <int?>(); if (contributionId != null && !importedContributions.Contains((int)contributionId)) { var transaction = new FinancialTransaction { CreatedByPersonAliasId = ImportPersonAliasId, ModifiedByPersonAliasId = ImportPersonAliasId, TransactionTypeValueId = TransactionTypeContributionId, ForeignKey = contributionId.ToString(), ForeignId = contributionId }; int?giverAliasId = null; var personKeys = GetPersonKeys(individualIdKey); if (personKeys != null && personKeys.PersonAliasId > 0) { giverAliasId = personKeys.PersonAliasId; transaction.CreatedByPersonAliasId = giverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } else if (AnonymousGiverAliasId != null && AnonymousGiverAliasId > 0) { giverAliasId = AnonymousGiverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } var summary = row[Memo] as string; if (!string.IsNullOrWhiteSpace(summary)) { transaction.Summary = summary; } var batchIdKey = row[ContributionBatchID]; var batchId = batchIdKey.AsType <int?>(); if (batchId != null && ImportedBatches.Any(b => b.Key.Equals(batchId))) { transaction.BatchId = ImportedBatches.FirstOrDefault(b => b.Key.Equals(batchId)).Value; } else { // use the default batch for any non-matching transactions transaction.BatchId = defaultBatchId; } var receivedDateKey = row[ReceivedDate]; var receivedDate = receivedDateKey.AsType <DateTime?>(); if (receivedDate != null) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } var contributionType = row[ContributionTypeName]; var creditCardType = row[ContributionCreditCardType]; if (!string.IsNullOrWhiteSpace(contributionType)) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = sourceTypeOnsite; int?paymentCurrencyTypeId = null, creditCardTypeId = null; if (contributionType.Equals("cash", StringComparison.CurrentCultureIgnoreCase)) { paymentCurrencyTypeId = currencyTypeCash; } else if (contributionType.Equals("check", StringComparison.CurrentCultureIgnoreCase)) { paymentCurrencyTypeId = currencyTypeCheck; } else if (contributionType.Equals("ach", StringComparison.CurrentCultureIgnoreCase)) { paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = sourceTypeWebsite; } else if (contributionType.Equals("credit card", StringComparison.CurrentCultureIgnoreCase)) { paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = sourceTypeWebsite; // Determine CC Type if (!string.IsNullOrWhiteSpace(creditCardType)) { creditCardTypeId = creditCardTypes.Where(c => c.Value.StartsWith(creditCardType, StringComparison.CurrentCultureIgnoreCase) || c.Description.StartsWith(creditCardType, StringComparison.CurrentCultureIgnoreCase)) .Select(c => c.Id).FirstOrDefault(); } } else { paymentCurrencyTypeId = currencyTypeNonCash; } var paymentDetail = new FinancialPaymentDetail { CreatedDateTime = receivedDate, CreatedByPersonAliasId = giverAliasId, ModifiedDateTime = ImportDateTime, ModifiedByPersonAliasId = giverAliasId, CurrencyTypeValueId = paymentCurrencyTypeId, CreditCardTypeValueId = creditCardTypeId, ForeignKey = contributionId.ToString(), ForeignId = contributionId }; transaction.FinancialPaymentDetail = paymentDetail; } var transactionCode = row[CheckNumber] as string; // if transaction code provided, put it in the transaction code if (!string.IsNullOrEmpty(transactionCode)) { transaction.TransactionCode = transactionCode; // check for SecureGive kiosk transactions if (transactionCode.StartsWith("SG")) { transaction.SourceTypeValueId = sourceTypeKiosk; } } var fundName = row[FundName] as string; var subFund = row[SubFundName] as string; var fundGLAccount = row[FundGLAccount] as string; var subFundGLAccount = row[SubFundGLAccount] as string; var isFundActiveKey = row[FundIsActive]; var isFundActive = isFundActiveKey.AsType <bool?>(); var isSubFundActiveKey = row[SubFundIsActive]; var isSubFundActive = isSubFundActiveKey.AsType <bool?>(); var statedValueKey = row[StatedValue]; var statedValue = statedValueKey.AsType <decimal?>(); var amountKey = row[Amount]; var amount = amountKey.AsType <decimal?>(); if (!string.IsNullOrWhiteSpace(fundName) & amount != null) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName.Truncate(50))); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, fundGLAccount, null, null, isFundActive, null, null, null, null, "", "", null); accountList.Add(parentAccount); } if (!string.IsNullOrWhiteSpace(subFund)) { int?campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault(c => subFund.Contains(c.Name) || subFund.Contains(c.ShortCode)); if (campusFund != null) { campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = $"{fundName} {subFund}"; var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund.Truncate(50)) && c.ParentAccountId == parentAccount.Id); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddAccount(lookupContext, subFund, subFundGLAccount, campusFundId, parentAccount.Id, isSubFundActive, null, null, null, null, "", "", null); accountList.Add(childAccount); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if (amount == 0 && statedValue != null && statedValue != 0) { amount = statedValue; } var transactionDetail = new FinancialTransactionDetail { Amount = (decimal)amount, CreatedDateTime = receivedDate, AccountId = transactionAccountId }; transaction.TransactionDetails.Add(transactionDetail); if (amount < 0) { transaction.RefundDetails = new FinancialTransactionRefund(); transaction.RefundDetails.CreatedDateTime = receivedDate; transaction.RefundDetails.RefundReasonValueId = refundReasons.Where(dv => summary != null && dv.Value.Contains(summary)) .Select(dv => (int?)dv.Id).FirstOrDefault(); transaction.RefundDetails.RefundReasonSummary = summary; } } newTransactions.Add(transaction); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, $"{completed:N0} contributions imported."); } else if (completed % ReportingNumber < 1) { SaveContributions(newTransactions); newTransactions.Clear(); ReportPartialProgress(); } } } if (newTransactions.Any()) { SaveContributions(newTransactions); } ReportProgress(100, $"Finished contribution import: {completed:N0} contributions imported."); return(completed); }