/// <summary> /// Maps the contribution. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="selectedColumns">The selected columns.</param> private void MapContribution(IQueryable <Row> tableData, List <string> selectedColumns = null) { var lookupContext = new RockContext(); int transactionEntityTypeId = EntityTypeCache.Read("Rock.Model.FinancialTransaction").Id; var transactionTypeContributionId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION), lookupContext).Id; var transactionTypeEventRegistrationId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_EVENT_REGISTRATION), lookupContext).Id; int currencyTypeACH = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH), lookupContext).Id; int currencyTypeCash = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH), lookupContext).Id; int currencyTypeCheck = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK), lookupContext).Id; int currencyTypeCreditCard = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD), lookupContext).Id; var refundReasons = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON), lookupContext).DefinedValues; List <FinancialPledge> pledgeList = new FinancialPledgeService(lookupContext).Queryable().ToList(); List <FinancialAccount> accountList = new FinancialAccountService(lookupContext).Queryable().ToList(); // Get all imported contributions var importedContributions = new FinancialTransactionService(lookupContext).Queryable() .Select(t => new { ContributionId = t.ForeignId, TransactionId = t.Id }) .ToDictionary(t => t.ContributionId.AsType <int?>(), t => (int?)t.TransactionId); var householdAVList = new AttributeValueService(lookupContext).Queryable().Where(av => av.AttributeId == HouseholdAttributeId).ToList(); // List for batching new contributions var newTransactions = new List <FinancialTransaction>(); int completed = 0; int totalRows = tableData.Count(); int percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying contribution import ({0:N0} found, {1:N0} already exist).", totalRows, importedContributions.Count())); foreach (var row in tableData) { int?individualId = row["Individual_ID"] as int?; int?householdId = row["Household_ID"] as int?; int?contributionId = row["ContributionID"] as int?; if (contributionId != null && !importedContributions.ContainsKey(contributionId)) { var transaction = new FinancialTransaction(); string fundName = row["Fund_Name"] as string; //Crossroads - Anything under a fund name that starts with Receipt - is an Event Registration. if (fundName.StartsWith("Receipt -")) { transaction.TransactionTypeValueId = transactionTypeEventRegistrationId; } else { transaction.TransactionTypeValueId = transactionTypeContributionId; } int?associatedPersonId; if (individualId != null) { associatedPersonId = GetPersonAliasId(individualId, householdId); } //will get the exact person if Individual Id is not null. else { associatedPersonId = GetPersonId(householdAVList, householdId); } //Will attempt to get the Head first, then Spouse, then Child. Will exclude Other and Visitor if (associatedPersonId != null) { transaction.AuthorizedPersonAliasId = associatedPersonId; transaction.CreatedByPersonAliasId = ImportPersonAlias.Id; transaction.ProcessedByPersonAliasId = associatedPersonId; transaction.ForeignId = contributionId.ToString(); string summary = row["Memo"] as string; if (summary != null) { transaction.Summary = summary; } int?batchId = row["BatchID"] as int?; if (batchId != null && ImportedBatches.Any(b => b.Key == batchId)) { transaction.BatchId = ImportedBatches.FirstOrDefault(b => b.Key == batchId).Value; } DateTime?receivedDate = row["Received_Date"] as DateTime?; if (receivedDate != null) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; } bool isTypeNonCash = false; string contributionType = row["Contribution_Type_Name"].ToString().ToLower(); if (contributionType != null) { if (contributionType == "ach") { transaction.CurrencyTypeValueId = currencyTypeACH; } else if (contributionType == "cash") { transaction.CurrencyTypeValueId = currencyTypeCash; } else if (contributionType == "check") { transaction.CurrencyTypeValueId = currencyTypeCheck; } else if (contributionType == "credit card") { transaction.CurrencyTypeValueId = currencyTypeCreditCard; } else { isTypeNonCash = true; } } string checkNumber = row["Check_Number"] as string; if (checkNumber != null && checkNumber.AsType <int?>() != null) { // routing & account set to zero transaction.CheckMicrEncrypted = Encryption.EncryptString(string.Format("{0}_{1}_{2}", 0, 0, checkNumber)); } decimal?amount = row["Amount"] as decimal?; if (fundName != null & amount != null) { FinancialAccount matchingAccount = null; int? parentAccountId = null; string parentAccountName = String.Empty; int? fundCampusId = null; fundName = fundName.Trim(); string subFund = row["Sub_Fund_Name"] as string; if (subFund != null) { subFund = subFund.Trim(); // Check if subfund was used to mark a multi-site campus fundCampusId = CampusList.Where(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode)) .Select(c => (int?)c.Id).FirstOrDefault(); // Matched a campus, check to see if an account exists for that campus already if (fundCampusId != null) { matchingAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId != null && a.CampusId.Equals(fundCampusId)); } else { // No campus match, look for an account that matches parent name and subfund name matchingAccount = accountList.FirstOrDefault(a => a.ParentAccountId != null && a.ParentAccount.Name.Equals(fundName) && a.Name.Equals(subFund)); if (matchingAccount == null) { // Check if a parent account exists already FinancialAccount parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName)); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, fundCampusId); accountList.Add(parentAccount); } // set data for subfund to be created parentAccountId = parentAccount.Id; fundName = subFund; parentAccountName = parentAccount.Name; } } } else { matchingAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null); } if (matchingAccount == null) { // No account matches, create the new account with campus Id and parent Id if they were set matchingAccount = AddAccount(lookupContext, fundName, fundCampusId, parentAccountName, parentAccountId); accountList.Add(matchingAccount); } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.AccountId = matchingAccount.Id; transactionDetail.IsNonCash = isTypeNonCash; transaction.TransactionDetails.Add(transactionDetail); if (amount < 0) { var transactionRefund = new FinancialTransactionRefund(); transactionRefund.CreatedDateTime = receivedDate; transactionRefund.RefundReasonSummary = summary; transactionRefund.RefundReasonValueId = refundReasons.Where(dv => summary != null && dv.Value.Contains(summary)) .Select(dv => (int?)dv.Id).FirstOrDefault(); transaction.Refund = transactionRefund; } } newTransactions.Add(transaction); completed++; if (completed % percentage < 1) { int percentComplete = completed / percentage; ReportProgress(percentComplete, string.Format("{0:N0} contributions imported ({1}% complete).", completed, percentComplete)); } else if (completed % ReportingNumber < 1) { SaveContributions(newTransactions); newTransactions.Clear(); ReportPartialProgress(); } } } } if (newTransactions.Any()) { SaveContributions(newTransactions); } ReportProgress(100, string.Format("Finished contribution import: {0:N0} contributions imported.", completed)); }