/// <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 currencyTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE ) ); int currencyTypeACH = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH ) ) ).Id; int currencyTypeCash = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH ) ) ).Id; int currencyTypeCheck = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK ) ) ).Id; int currencyTypeCreditCard = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD ) ) ).Id; int? currencyTypeNonCash = currencyTypes.DefinedValues.Where( dv => dv.Value.Equals( "Non-Cash" ) ).Select( dv => (int?)dv.Id ).FirstOrDefault(); if ( currencyTypeNonCash == null ) { var newTenderNonCash = new DefinedValue(); newTenderNonCash.Value = "Non-Cash"; newTenderNonCash.Description = "Non-Cash"; newTenderNonCash.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; int sourceTypeOnsite = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_ONSITE_COLLECTION ), lookupContext ).Id; int sourceTypeWebsite = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_WEBSITE ), lookupContext ).Id; int 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]; } // Get all imported contributions var importedContributions = new FinancialTransactionService( lookupContext ).Queryable().AsNoTracking() .Where( c => c.ForeignId != null ) .ToDictionary( t => (int)t.ForeignId, t => (int?)t.Id ); // 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.Where( r => r != null ) ) { 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( (int)contributionId ) ) { var transaction = new FinancialTransaction(); transaction.CreatedByPersonAliasId = ImportPersonAliasId; transaction.ModifiedByPersonAliasId = ImportPersonAliasId; transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.ForeignKey = contributionId.ToString(); transaction.ForeignId = contributionId; int? giverAliasId = null; var personKeys = GetPersonKeys( individualId, householdId ); if ( personKeys != null && personKeys.PersonAliasId > 0 ) { giverAliasId = personKeys.PersonAliasId; transaction.CreatedByPersonAliasId = giverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } 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.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; } DateTime? receivedDate = row["Received_Date"] as DateTime?; if ( receivedDate != null ) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } string cardType = row["Card_Type"] as string; string cardLastFour = row["Last_Four"] as string; string contributionType = row["Contribution_Type_Name"].ToStringSafe().ToLower(); if ( contributionType != null ) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = sourceTypeOnsite; int? paymentCurrencyTypeId = null, creditCardTypeId = null; if ( contributionType == "cash" ) { paymentCurrencyTypeId = currencyTypeCash; } else if ( contributionType == "check" ) { paymentCurrencyTypeId = currencyTypeCheck; } else if ( contributionType == "ach" ) { paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = sourceTypeWebsite; } else if ( contributionType == "credit card" ) { paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = sourceTypeWebsite; if ( cardType != null ) { creditCardTypeId = creditCardTypes.Where( t => t.Value.Equals( cardType ) ).Select( t => (int?)t.Id ).FirstOrDefault(); } } else { paymentCurrencyTypeId = currencyTypeNonCash; } var paymentDetail = new FinancialPaymentDetail(); paymentDetail.CreatedDateTime = receivedDate; paymentDetail.CreatedByPersonAliasId = giverAliasId; paymentDetail.ModifiedDateTime = ImportDateTime; paymentDetail.ModifiedByPersonAliasId = giverAliasId; paymentDetail.CurrencyTypeValueId = paymentCurrencyTypeId; paymentDetail.CreditCardTypeValueId = creditCardTypeId; paymentDetail.AccountNumberMasked = cardLastFour; paymentDetail.ForeignKey = contributionId.ToString(); paymentDetail.ForeignId = contributionId; transaction.FinancialPaymentDetail = paymentDetail; } string checkNumber = row["Check_Number"] as string; // if the check number is valid, put it in the transaction code if ( checkNumber.AsType<int?>() != null ) { transaction.TransactionCode = checkNumber; } // check for SecureGive kiosk transactions else if ( !string.IsNullOrEmpty( checkNumber ) && checkNumber.StartsWith( "SG" ) ) { transaction.SourceTypeValueId = sourceTypeKiosk; } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; string fundGLAccount = row["Fund_GL_Account"] as string; string subFundGLAccount = row["Sub_Fund_GL_Account"] as string; Boolean? isFundActive = row["Fund_Is_active"] as Boolean?; decimal? statedValue = row["Stated_Value"] as decimal?; decimal? amount = row["Amount"] as decimal?; if ( fundName != null & amount != null ) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName ) && a.CampusId == null ); if ( parentAccount == null ) { parentAccount = AddAccount( lookupContext, fundName, fundGLAccount, null, null, isFundActive ); accountList.Add( parentAccount ); } if ( subFund != null ) { int? campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault( c => subFund.StartsWith( c.Name ) || subFund.StartsWith( c.ShortCode ) ); if ( campusFund != null ) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format( "{0} {1}", subFund, fundName ); var childAccount = accountList.FirstOrDefault( c => c.Name.Equals( subFund ) && 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, isFundActive ); accountList.Add( childAccount ); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if ( amount == 0 && statedValue != null && statedValue != 0 ) { amount = statedValue; } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.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 % 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 ) ); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge( IQueryable<Row> tableData ) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService( lookupContext ).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService( lookupContext ).Queryable().AsNoTracking().ToList(); var pledgeFrequencies = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY ), lookupContext ).DefinedValues; int oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault( f => f.Guid == new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME ) ).Id; var newPledges = new List<FinancialPledge>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying pledge import ({0:N0} found).", totalRows ) ); foreach ( var row in tableData.Where( r => r != null ) ) { decimal? amount = row["Total_Pledge"] as decimal?; DateTime? startDate = row["Start_Date"] as DateTime?; DateTime? endDate = row["End_Date"] as DateTime?; if ( amount != null && startDate != null && endDate != null ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; var personKeys = GetPersonKeys( individualId, householdId, includeVisitors: false ); if ( personKeys != null && personKeys.PersonAliasId > 0 ) { var pledge = new FinancialPledge(); pledge.PersonAliasId = personKeys.PersonAliasId; pledge.CreatedByPersonAliasId = ImportPersonAliasId; pledge.ModifiedDateTime = ImportDateTime; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; pledge.CreatedDateTime = ImportDateTime; pledge.ModifiedDateTime = ImportDateTime; pledge.ModifiedByPersonAliasId = ImportPersonAliasId; string frequency = row["Pledge_Frequency_Name"].ToString().ToLower(); if ( frequency != null ) { frequency = frequency.ToLower(); if ( frequency.Equals( "one time" ) || frequency.Equals( "as can" ) ) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where( f => f.Value.ToLower().StartsWith( frequency ) || f.Description.ToLower().StartsWith( frequency ) ) .Select( f => f.Id ).FirstOrDefault(); } } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; if ( fundName != null ) { var parentAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName ) && a.CampusId == null ); if ( parentAccount == null ) { parentAccount = AddAccount( lookupContext, fundName, string.Empty, null, null, null ); accountList.Add( parentAccount ); } if ( subFund != null ) { int? campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault( c => subFund.StartsWith( c.Name ) || subFund.StartsWith( c.ShortCode ) ); if ( campusFund != null ) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format( "{0} {1}", subFund, fundName ); var childAccount = accountList.FirstOrDefault( c => c.Name.Equals( subFund ) && c.ParentAccountId == parentAccount.Id ); if ( childAccount == null ) { // create a child account with a campusId if it was set childAccount = AddAccount( lookupContext, subFund, string.Empty, campusFundId, parentAccount.Id, null ); accountList.Add( childAccount ); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add( pledge ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} pledges imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { SavePledges( newPledges ); ReportPartialProgress(); newPledges.Clear(); } } } } if ( newPledges.Any() ) { SavePledges( newPledges ); } ReportProgress( 100, string.Format( "Finished pledge import: {0:N0} pledges imported.", completed ) ); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private int MapPledge(CSVInstance csvData) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking().ToList(); var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues; int oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id; var newPledges = new List <FinancialPledge>(); int completed = 0; ReportProgress(0, string.Format("Verifying pledge import ({0:N0} already exist).", importedPledges.Count)); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { string amountKey = row[TotalPledge]; decimal? amount = amountKey.AsType <decimal?>(); string startDateKey = row[StartDate]; DateTime?startDate = startDateKey.AsType <DateTime?>(); string endDateKey = row[EndDate]; DateTime?endDate = endDateKey.AsType <DateTime?>(); if (amount != null && startDate != null && endDate != null) { string individualIdKey = row[IndividualID]; int? individualId = individualIdKey.AsType <int?>(); var personKeys = GetPersonKeys(individualId); if (personKeys != null && personKeys.PersonAliasId > 0) { var pledge = new FinancialPledge(); pledge.PersonAliasId = personKeys.PersonAliasId; pledge.CreatedByPersonAliasId = ImportPersonAliasId; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; string frequency = row[PledgeFrequencyName].ToString().ToLower(); if (frequency != null) { frequency = frequency.ToLower(); if (frequency.Equals("one time") || frequency.Equals("as can")) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency)) .Select(f => f.Id).FirstOrDefault(); } } string fundName = row[FundName] as string; string subFund = row[SubFundName] as string; string fundGLAccount = row[FundGLAccount] as string; string subFundGLAccount = row[SubFundGLAccount] as string; string isFundActiveKey = row[FundIsActive]; Boolean?isFundActive = isFundActiveKey.AsType <Boolean?>(); if (fundName != null) { var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, string.Empty, null, null, isFundActive); accountList.Add(parentAccount); } if (subFund != null) { int?campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode)); if (campusFund != null) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format("{0} {1}", subFund, fundName); var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund) && c.ParentAccountId == parentAccount.Id); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddAccount(lookupContext, subFund, string.Empty, campusFundId, parentAccount.Id, isFundActive); accountList.Add(childAccount); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add(pledge); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} pledges imported.", completed)); } else if (completed % ReportingNumber < 1) { SavePledges(newPledges); ReportPartialProgress(); newPledges.Clear(); } } } } if (newPledges.Any()) { SavePledges(newPledges); } ReportProgress(100, string.Format("Finished pledge import: {0:N0} pledges imported.", completed)); return(completed); }
/// <summary>Process all transactions (payments) from Service Reef.</summary> /// <param name="message">The message that is returned depending on the result.</param> /// <param name="state">The state of the process.</param> /// <returns><see cref="WorkerResultStatus"/></returns> public void Execute(IJobExecutionContext context) { RockContext dbContext = new RockContext(); FinancialBatchService financialBatchService = new FinancialBatchService(dbContext); PersonService personService = new PersonService(dbContext); PersonAliasService personAliasService = new PersonAliasService(dbContext); FinancialAccountService financialAccountService = new FinancialAccountService(dbContext); FinancialAccountService accountService = new FinancialAccountService(dbContext); FinancialTransactionService financialTransactionService = new FinancialTransactionService(dbContext); FinancialGatewayService financialGatewayService = new FinancialGatewayService(dbContext); DefinedValueService definedValueService = new DefinedValueService(dbContext); DefinedTypeService definedTypeService = new DefinedTypeService(dbContext); TransactionService transactionService = new TransactionService(new PayPalReporting.Data.PayPalReportingContext()); // Get the datamap for loading attributes JobDataMap dataMap = context.JobDetail.JobDataMap; String warnings = string.Empty; FinancialBatch batch = null; Double totalAmount = 0; var total = 1; var processed = 0; try { DateRange dateRange = Rock.Web.UI.Controls.SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(dataMap.GetString("DateRange") ?? "-1||"); String SRApiKey = Encryption.DecryptString(dataMap.GetString("ServiceReefAPIKey")); String SRApiSecret = Encryption.DecryptString(dataMap.GetString("ServiceReefAPISecret")); String SRApiUrl = dataMap.GetString("ServiceReefAPIURL"); DefinedValueCache transactionSource = DefinedValueCache.Read(dataMap.GetString("TransactionSource").AsGuid(), dbContext); DefinedValueCache connectionStatus = DefinedValueCache.Read(dataMap.GetString("ConnectionStatus").AsGuid(), dbContext); DefinedValueCache contribution = DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION); // Setup some lookups DefinedTypeCache creditCards = DefinedTypeCache.Read(Rock.SystemGuid.DefinedType.FINANCIAL_CREDIT_CARD_TYPE.AsGuid(), dbContext); DefinedTypeCache tenderType = DefinedTypeCache.Get(Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE.AsGuid(), dbContext); FinancialAccount specialFund = accountService.Get(dataMap.GetString("Account").AsGuid()); FinancialGateway gateway = financialGatewayService.Get(dataMap.GetString("FinancialGateway").AsGuid()); List <FinancialAccount> trips = financialAccountService.Queryable().Where(fa => fa.ParentAccountId == specialFund.Id).OrderBy(fa => fa.Order).ToList(); // Get the trips DefinedValueCache serviceReefAccountType = DefinedValueCache.Get(dataMap.Get("ServiceReefAccountType").ToString().AsGuid()); // Setup the ServiceReef API Client var client = new RestClient(SRApiUrl); client.Authenticator = new HMACAuthenticator(SRApiKey, SRApiSecret); // Get all payments from ServiceReef var request = new RestRequest("v1/payments", Method.GET); request.AddParameter("pageSize", 100); if (dateRange.Start.HasValue) { request.AddParameter("startDate", dateRange.Start.Value.ToString("o")); } if (dateRange.End.HasValue) { request.AddParameter("endDate", dateRange.End.Value.ToString("o")); } request.AddParameter("page", 1); while (total > processed) { var response = client.Execute <Contracts.Payments>(request); if (response.StatusCode != System.Net.HttpStatusCode.OK) { throw new Exception("ServiceReef API Response: " + response.StatusDescription + " Content Length: " + response.ContentLength); } if (response.Data != null && response.Data.PageInfo != null) { total = response.Data.PageInfo.TotalRecords; foreach (Contracts.Payments.Result result in response.Data.Results) { // Process the transaction if (result.PaymentProcessorTransactionId != null) { if (result.FirstName == null || result.LastName == null) { warnings += "Missing Firstname/Lastname for ServiceReef transaction Id: " + result.TransactionId + Environment.NewLine; processed++; continue; } FinancialAccount trip = null; // Make sure we have a sub-account to go with this transaction if (result.EventId > 0) { trip = trips.Where(t => t.GlCode == result.EventCode && t.Url == result.EventUrl).FirstOrDefault(); } if (trip == null) { if (result.EventCode == null) { warnings += "Event Code is missing on the Service Reef Trip for ServiceReef transaction Id: " + result.TransactionId + Environment.NewLine; processed++; continue; } // Create the trip subaccount FinancialAccount tripFA = new FinancialAccount(); tripFA.Name = result.EventName; // Name is limited to 50 if (tripFA.Name.Length > 50) { tripFA.Name = tripFA.Name.Substring(0, 50); } tripFA.Description = "Service Reef Event. Name: " + result.EventName + " ID: " + result.EventId; tripFA.GlCode = result.EventCode; tripFA.Url = result.EventUrl; tripFA.PublicName = result.EventName; // Public Name is limited to 50 if (tripFA.PublicName.Length > 50) { tripFA.PublicName = tripFA.PublicName.Substring(0, 50); } tripFA.IsTaxDeductible = true; tripFA.IsPublic = false; tripFA.ParentAccountId = specialFund.Id; tripFA.Order = specialFund.Order + 1; tripFA.AccountTypeValueId = serviceReefAccountType.Id; // Figure out what order it should be; foreach (FinancialAccount tmpTrip in trips) { if (tmpTrip.Name.CompareTo(tripFA.Name) < 0) { tripFA.Order++; } } financialAccountService.Add(tripFA); // Now save the trip dbContext.SaveChanges(); // Increment all the rest of the Orders financialAccountService.Queryable().Where(fa => fa.Order >= tripFA.Order && fa.Id != tripFA.Id).ToList().ForEach(c => c.Order++); dbContext.SaveChanges(); trips = financialAccountService.Queryable().Where(fa => fa.ParentAccountId == specialFund.Id).OrderBy(fa => fa.Order).ToList(); trip = tripFA; } FinancialTransaction tran = financialTransactionService.Queryable().Where(tx => tx.TransactionCode == result.PaymentProcessorTransactionId).FirstOrDefault(); // We haven't processed this before so get busy! if (tran == null) { tran = new FinancialTransaction(); tran.FinancialPaymentDetail = new FinancialPaymentDetail(); if (result.Type == "CreditCard") { tran.FinancialPaymentDetail.CurrencyTypeValueId = tenderType.DefinedValues.Where(t => t.Value == "Credit Card").FirstOrDefault().Id; } else { tran.TransactionTypeValueId = tenderType.DefinedValues.Where(t => t.Value == "Credit Card").FirstOrDefault().Id; } Person person = null; // Find the person this transaction belongs to // 1. First start by determining whether this was a person // paying their application fee or contributing to themselves // because then we can just use their member info if (result.UserId > 0 && result.DonatedToUserId == result.UserId && result.DonatedToFirstName == result.FirstName && result.DonatedToLastName == result.LastName) { var memberRequest = new RestRequest("v1/members/{userId}", Method.GET); memberRequest.AddUrlSegment("userId", result.UserId.ToString()); var memberResult = client.Execute <Contracts.Member>(memberRequest); if (memberResult.Data != null && memberResult.Data.ArenaId > 0) { try { Person personMatch = personAliasService.Queryable().Where(pa => pa.AliasPersonId == memberResult.Data.ArenaId).Select(pa => pa.Person).FirstOrDefault(); if (personMatch == null) { throw new Exception("Person not found: " + memberResult.Data.ArenaId); } person = personMatch; } catch (Exception e) { warnings += "Loading the person failed transaction id " + result.TransactionId + " for " + result.FirstName + " " + result.LastName + " with the following error: " + e.Message + Environment.NewLine; processed++; continue; } } } // 2. If we didn't get a person match via their Alias Id // then just use the standard person match logic if (person == null) { String street1 = null; String postalCode = null; if (result.Address != null) { street1 = result.Address.Address1; postalCode = result.Address.Zip; } List <Person> matches = personService.GetByMatch(result.FirstName.Trim(), result.LastName.Trim(), null, result.Email, null, street1, postalCode).ToList(); if (matches.Count > 1) { // Find the oldest member record in the list person = matches.Where(p => p.ConnectionStatusValue.Value == "Member").OrderBy(p => p.Id).FirstOrDefault(); if (person == null) { // Find the oldest attendee record in the list person = matches.Where(p => p.ConnectionStatusValue.Value == "Attendee").OrderBy(p => p.Id).FirstOrDefault(); if (person == null) { person = matches.OrderBy(p => p.Id).First(); } } } else if (matches.Count == 1) { person = matches.First(); } else { // Create the person person = new Person(); person.FirstName = result.FirstName.Trim(); person.LastName = result.LastName.Trim(); if (result.Email.IsValidEmail()) { person.Email = result.Email.Trim(); } person.RecordTypeValueId = DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON.AsGuid()).Id; person.ConnectionStatusValueId = connectionStatus.Id; person.RecordStatusValueId = DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE.AsGuid()).Id; Group family = PersonService.SaveNewPerson(person, dbContext); GroupLocation location = new GroupLocation(); location.GroupLocationTypeValueId = DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME).Id; location.Location = new Location() { Street1 = result.Address.Address1, Street2 = result.Address.Address2, City = result.Address.City, State = result.Address.State, PostalCode = result.Address.Zip, Country = result.Address.Country }; family.CampusId = CampusCache.All().FirstOrDefault().Id; family.GroupLocations.Add(location); dbContext.SaveChanges(); } } // Get details about the transaction from our PayPal report table Transaction tx = transactionService.Get(result.PaymentProcessorTransactionId); if (tx != null) { if (tx.TenderType.Contains("ACH")) { result.Type = "ACH"; result.Method = null; } else { result.Type = "Credit Card"; result.Method = tx.TenderType; } } else { // Defaults result.Type = "Credit Card"; result.Method = "Visa"; warnings += "Unable to find transaction in _org_secc_PaypalReporting_Transaction table: " + result.TransactionId + Environment.NewLine; } // If we don't have a batch, create one if (batch == null) { batch = new FinancialBatch(); batch.BatchStartDateTime = result.Date; batch.BatchEndDateTime = DateTime.Now; batch.Name = "Service Reef Payments"; batch.Status = BatchStatus.Open; financialBatchService.Add(batch); dbContext.SaveChanges(); } // Complete the FinancialTransaction tran.AuthorizedPersonAliasId = person.PrimaryAliasId; tran.BatchId = batch.Id; tran.Summary = "F" + specialFund.Id + ":$" + result.Amount.ToString(); tran.TransactionDateTime = result.Date; tran.FinancialGatewayId = gateway.Id; FinancialTransactionDetail financialTransactionDetail = new FinancialTransactionDetail(); financialTransactionDetail.AccountId = trip.Id; financialTransactionDetail.Amount = result.Amount.ToString().AsDecimal(); tran.TransactionDetails.Add(financialTransactionDetail); tran.TransactionTypeValueId = contribution.Id; tran.FinancialPaymentDetail = new FinancialPaymentDetail(); tran.FinancialPaymentDetail.CurrencyTypeValueId = tenderType.DefinedValues.Where(type => type.Value.ToLower() == result.Type.ToLower()).FirstOrDefault().Id; if (result.Method != null) { tran.FinancialPaymentDetail.CreditCardTypeValueId = creditCards.DefinedValues.Where(card => card.Value.ToLower() == result.Method.ToLower()).FirstOrDefault().Id; } tran.TransactionCode = result.PaymentProcessorTransactionId; tran.SourceTypeValueId = transactionSource.Id; financialTransactionService.Add(tran); dbContext.SaveChanges(); totalAmount += result.Amount; } } processed++; } } else { total = 0; } // Update the page number for the next request var pageParam = request.Parameters.Where(p => p.Name == "page").FirstOrDefault(); pageParam.Value = (int)pageParam.Value + 1; } } catch (Exception ex) { throw new Exception("ServiceReef Job Failed", ex); } finally { if (batch != null && totalAmount > 0) { batch.ControlAmount = (Decimal)totalAmount; } dbContext.SaveChanges(); } if (warnings.Length > 0) { throw new Exception(warnings); } context.Result = "Successfully imported " + processed + " transactions."; }
/// <summary> /// Maps the contribution. /// </summary> /// <param name="csvData">The table data.</param> private int MapContribution( CSVInstance csvData ) { 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 currencyTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE ) ); int currencyTypeACH = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH ) ) ).Id; int currencyTypeCash = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH ) ) ).Id; int currencyTypeCheck = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK ) ) ).Id; int currencyTypeCreditCard = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD ) ) ).Id; int? currencyTypeNonCash = currencyTypes.DefinedValues.Where( dv => dv.Value.Equals( "Non-Cash" ) ).Select( dv => ( int? )dv.Id ).FirstOrDefault(); if ( currencyTypeNonCash == null ) { var newTenderNonCash = new DefinedValue(); newTenderNonCash.Value = "Non-Cash"; newTenderNonCash.Description = "Non-Cash"; newTenderNonCash.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; int sourceTypeOnsite = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_ONSITE_COLLECTION ), lookupContext ).Id; int sourceTypeWebsite = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_WEBSITE ), lookupContext ).Id; int 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]; } // Get all imported contributions var importedContributions = new FinancialTransactionService( lookupContext ).Queryable().AsNoTracking() .Where( c => c.ForeignId != null ) .ToDictionary( t => ( int )t.ForeignId, t => ( int? )t.Id ); // List for batching new contributions var newTransactions = new List<FinancialTransaction>(); int completed = 0; ReportProgress( 0, string.Format( "Verifying contribution import ({0:N0} already exist).", importedContributions.Count ) ); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ( (row = csvData.Database.FirstOrDefault()) != null ) { string individualIdKey = row[IndividualID]; int? individualId = individualIdKey.AsType<int?>(); string contributionIdKey = row[ContributionID]; int? contributionId = contributionIdKey.AsType<int?>(); if ( contributionId != null && !importedContributions.ContainsKey( ( int )contributionId ) ) { var transaction = new FinancialTransaction(); transaction.CreatedByPersonAliasId = ImportPersonAliasId; transaction.ModifiedByPersonAliasId = ImportPersonAliasId; transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.ForeignKey = contributionId.ToString(); transaction.ForeignId = contributionId; int? giverAliasId = null; var personKeys = GetPersonKeys( individualId ); 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; } string summary = row[Memo] as string; if ( !String.IsNullOrWhiteSpace( summary ) ) { transaction.Summary = summary; } string batchIdKey = row[ContributionBatchID]; int? 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; } string receivedDateKey = row[ReceivedDate]; DateTime? receivedDate = receivedDateKey.AsType<DateTime?>(); if ( receivedDate != null ) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } string contributionType = row[ContributionTypeName].ToStringSafe().ToLower(); if ( !String.IsNullOrWhiteSpace( contributionType ) ) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = sourceTypeOnsite; int? paymentCurrencyTypeId = null, creditCardTypeId = null; if ( contributionType == "cash" ) { paymentCurrencyTypeId = currencyTypeCash; } else if ( contributionType == "check" ) { paymentCurrencyTypeId = currencyTypeCheck; } else if ( contributionType == "ach" ) { paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = sourceTypeWebsite; } else if ( contributionType == "credit card" ) { paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = sourceTypeWebsite; } else { paymentCurrencyTypeId = currencyTypeNonCash; } var paymentDetail = new FinancialPaymentDetail(); paymentDetail.CreatedDateTime = receivedDate; paymentDetail.CreatedByPersonAliasId = giverAliasId; paymentDetail.ModifiedDateTime = ImportDateTime; paymentDetail.ModifiedByPersonAliasId = giverAliasId; paymentDetail.CurrencyTypeValueId = paymentCurrencyTypeId; paymentDetail.CreditCardTypeValueId = creditCardTypeId; paymentDetail.ForeignKey = contributionId.ToString(); paymentDetail.ForeignId = contributionId; transaction.FinancialPaymentDetail = paymentDetail; } string checkNumber = row[CheckNumber] as string; // if the check number is valid, put it in the transaction code if ( checkNumber.AsType<int?>() != null ) { transaction.TransactionCode = checkNumber; } // check for SecureGive kiosk transactions else if ( !string.IsNullOrEmpty( checkNumber ) && checkNumber.StartsWith( "SG" ) ) { transaction.SourceTypeValueId = sourceTypeKiosk; } string fundName = row[FundName] as string; string subFund = row[SubFundName] as string; string fundGLAccount = row[FundGLAccount] as string; string subFundGLAccount = row[SubFundGLAccount] as string; string isFundActiveKey = row[FundIsActive]; Boolean? isFundActive = isFundActiveKey.AsType<Boolean?>(); string isSubFundActiveKey = row[SubFundIsActive]; Boolean? isSubFundActive = isSubFundActiveKey.AsType<Boolean?>(); string statedValueKey = row[StatedValue]; decimal? statedValue = statedValueKey.AsType<decimal?>(); string amountKey = row[Amount]; decimal? amount = amountKey.AsType<decimal?>(); if ( !String.IsNullOrWhiteSpace( fundName ) & amount != null ) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName.Truncate( 50 ) ) && a.CampusId == null ); if ( parentAccount == null ) { parentAccount = AddAccount( lookupContext, fundName, fundGLAccount, null, null, isFundActive ); 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.StartsWith( c.Name ) || subFund.StartsWith( c.ShortCode ) ); if ( campusFund != null ) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format( "{0} {1}", subFund, fundName ); 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 ); accountList.Add( childAccount ); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if ( amount == 0 && statedValue != null && statedValue != 0 ) { amount = statedValue; } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = ( decimal )amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.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, string.Format( "{0:N0} contributions imported.", completed ) ); } 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 ) ); return completed; }
/// <summary> /// Maps the contribution. /// </summary> /// <param name="csvData">The table data.</param> private int MapContribution(CSVInstance csvData) { 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 currencyTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE)); int currencyTypeACH = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH))).Id; int currencyTypeCash = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH))).Id; int currencyTypeCheck = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK))).Id; int currencyTypeCreditCard = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD))).Id; int?currencyTypeNonCash = currencyTypes.DefinedValues.Where(dv => dv.Value.Equals("Non-Cash")).Select(dv => (int?)dv.Id).FirstOrDefault(); if (currencyTypeNonCash == null) { var newTenderNonCash = new DefinedValue(); newTenderNonCash.Value = "Non-Cash"; newTenderNonCash.Description = "Non-Cash"; newTenderNonCash.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; int sourceTypeOnsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_ONSITE_COLLECTION), lookupContext).Id; int sourceTypeWebsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_WEBSITE), lookupContext).Id; int 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]; } // Get all imported contributions var importedContributions = new FinancialTransactionService(lookupContext).Queryable().AsNoTracking() .Where(c => c.ForeignId != null) .ToDictionary(t => (int)t.ForeignId, t => (int?)t.Id); // List for batching new contributions var newTransactions = new List <FinancialTransaction>(); int completed = 0; ReportProgress(0, string.Format("Verifying contribution import ({0:N0} already exist).", importedContributions.Count)); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { string individualIdKey = row[IndividualID]; int? individualId = individualIdKey.AsType <int?>(); string contributionIdKey = row[ContributionID]; int? contributionId = contributionIdKey.AsType <int?>(); if (contributionId != null && !importedContributions.ContainsKey((int)contributionId)) { var transaction = new FinancialTransaction(); transaction.CreatedByPersonAliasId = ImportPersonAliasId; transaction.ModifiedByPersonAliasId = ImportPersonAliasId; transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.ForeignKey = contributionId.ToString(); transaction.ForeignId = contributionId; int?giverAliasId = null; var personKeys = GetPersonKeys(individualId); if (personKeys != null && personKeys.PersonAliasId > 0) { giverAliasId = personKeys.PersonAliasId; transaction.CreatedByPersonAliasId = giverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } string summary = row[Memo] as string; if (summary != null) { transaction.Summary = summary; } string batchIdKey = row[ContributionBatchID]; int? 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; } string receivedDateKey = row[ReceivedDate]; DateTime?receivedDate = receivedDateKey.AsType <DateTime?>(); if (receivedDate != null) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } string contributionType = row[ContributionTypeName].ToStringSafe().ToLower(); if (contributionType != null) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = sourceTypeOnsite; int?paymentCurrencyTypeId = null, creditCardTypeId = null; if (contributionType == "cash") { paymentCurrencyTypeId = currencyTypeCash; } else if (contributionType == "check") { paymentCurrencyTypeId = currencyTypeCheck; } else if (contributionType == "ach") { paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = sourceTypeWebsite; } else if (contributionType == "credit card") { paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = sourceTypeWebsite; } else { paymentCurrencyTypeId = currencyTypeNonCash; } var paymentDetail = new FinancialPaymentDetail(); paymentDetail.CreatedDateTime = receivedDate; paymentDetail.CreatedByPersonAliasId = giverAliasId; paymentDetail.ModifiedDateTime = ImportDateTime; paymentDetail.ModifiedByPersonAliasId = giverAliasId; paymentDetail.CurrencyTypeValueId = paymentCurrencyTypeId; paymentDetail.CreditCardTypeValueId = creditCardTypeId; paymentDetail.ForeignKey = contributionId.ToString(); paymentDetail.ForeignId = contributionId; transaction.FinancialPaymentDetail = paymentDetail; } string checkNumber = row[CheckNumber] as string; // if the check number is valid, put it in the transaction code if (checkNumber.AsType <int?>() != null) { transaction.TransactionCode = checkNumber; } // check for SecureGive kiosk transactions else if (!string.IsNullOrEmpty(checkNumber) && checkNumber.StartsWith("SG")) { transaction.SourceTypeValueId = sourceTypeKiosk; } string fundName = row[FundName] as string; string subFund = row[SubFundName] as string; string fundGLAccount = row[FundGLAccount] as string; string subFundGLAccount = row[SubFundGLAccount] as string; string isFundActiveKey = row[FundIsActive]; Boolean?isFundActive = isFundActiveKey.AsType <Boolean?>(); string statedValueKey = row[StatedValue]; decimal?statedValue = statedValueKey.AsType <decimal?>(); string amountKey = row[Amount]; decimal?amount = amountKey.AsType <decimal?>(); if (fundName != null & amount != null) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, fundGLAccount, null, null, isFundActive); 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.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode)); if (campusFund != null) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format("{0} {1}", subFund, fundName); var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund) && 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, isFundActive); accountList.Add(childAccount); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if (amount == 0 && statedValue != null && statedValue != 0) { amount = statedValue; } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.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, string.Format("{0:N0} contributions imported.", completed)); } 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)); return(completed); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge( IQueryable<Row> tableData ) { var accountService = new FinancialAccountService(); List<FinancialAccount> importedAccounts = accountService.Queryable().ToList(); List<DefinedValue> pledgeFrequencies = new DefinedValueService().GetByDefinedTypeGuid( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY ) ).ToList(); List<FinancialPledge> importedPledges = new FinancialPledgeService().Queryable().ToList(); var newPledges = new List<FinancialPledge>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Checking pledge import ({0:N0} found).", totalRows ) ); foreach ( var row in tableData ) { decimal? amount = row["Total_Pledge"] as decimal?; DateTime? startDate = row["Start_Date"] as DateTime?; DateTime? endDate = row["End_Date"] as DateTime?; if ( amount != null && startDate != null && endDate != null ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; int? personId = GetPersonId( individualId, householdId ); if ( personId != null && !importedPledges.Any( p => p.PersonId == personId && p.TotalAmount == amount && p.StartDate.Equals( startDate ) ) ) { var pledge = new FinancialPledge(); pledge.CreatedByPersonAliasId = ImportPersonAlias.Id; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; string frequency = row["Pledge_Frequency_Name"] as string; if ( frequency != null ) { if ( frequency == "One Time" || frequency == "As Can" ) { pledge.PledgeFrequencyValueId = pledgeFrequencies.FirstOrDefault( f => f.Guid == new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME ) ).Id; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where( f => f.Name.StartsWith( frequency ) || f.Description.StartsWith( frequency ) ) .Select( f => f.Id ).FirstOrDefault(); } } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; if ( fundName != null ) { FinancialAccount matchingAccount = null; int? fundCampusId = null; if ( subFund != null ) { // match by campus if the subfund appears to be a campus fundCampusId = CampusList.Where( c => c.Name.StartsWith( subFund ) || c.ShortCode == subFund ) .Select( c => (int?)c.Id ).FirstOrDefault(); if ( fundCampusId != null ) { matchingAccount = importedAccounts.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.CampusId != null && a.CampusId.Equals( fundCampusId ) ); } else { matchingAccount = importedAccounts.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.Name.StartsWith( subFund ) ); } } else { matchingAccount = importedAccounts.FirstOrDefault( a => a.Name.StartsWith( fundName ) ); } if ( matchingAccount == null ) { matchingAccount = new FinancialAccount(); matchingAccount.Name = fundName; matchingAccount.PublicName = fundName; matchingAccount.IsTaxDeductible = true; matchingAccount.IsActive = true; matchingAccount.CampusId = fundCampusId; matchingAccount.CreatedByPersonAliasId = ImportPersonAlias.Id; accountService.Add( matchingAccount ); accountService.Save( matchingAccount ); importedAccounts.Add( matchingAccount ); pledge.AccountId = matchingAccount.Id; } } // Attributes to add? // Pledge_Drive_Name newPledges.Add( pledge ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} pledges imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { RockTransactionScope.WrapTransaction( () => { var pledgeService = new FinancialPledgeService(); pledgeService.RockContext.FinancialPledges.AddRange( newPledges ); pledgeService.RockContext.SaveChanges(); } ); ReportPartialProgress(); } } } } if ( newPledges.Any() ) { RockTransactionScope.WrapTransaction( () => { var pledgeService = new FinancialPledgeService(); pledgeService.RockContext.FinancialPledges.AddRange( newPledges ); pledgeService.RockContext.SaveChanges(); } ); } ReportProgress( 100, string.Format( "Finished pledge import: {0:N0} pledges imported.", completed ) ); }
/// <summary> /// Maps the contribution. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="totalRows">The total rows.</param> private void MapContribution(IQueryable <Row> tableData, long totalRows = 0) { var lookupContext = new RockContext(); var transactionTypeContributionId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid(), lookupContext).Id; var currencyTypes = DefinedTypeCache.Get(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.Get(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CREDIT_CARD_TYPE)).DefinedValues; var refundReasons = DefinedTypeCache.Get(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]; } // Get all imported contributions var importedContributions = new FinancialTransactionService(lookupContext).Queryable().AsNoTracking() .Where(c => c.ForeignId != null) .ToDictionary(t => ( int )t.ForeignId, t => ( int? )t.Id); // List for batching new contributions var newTransactions = new List <FinancialTransaction>(); if (totalRows == 0) { totalRows = tableData.Count(); } var completedItems = 0; var percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, $"Verifying contribution import ({totalRows:N0} found, {importedContributions.Count:N0} already exist)."); foreach (var row in tableData.Where(r => r != null)) { var individualId = row["Individual_ID"] as int?; var householdId = row["Household_ID"] as int?; var contributionId = row["ContributionID"] as int?; if (contributionId.HasValue && !importedContributions.ContainsKey(( int )contributionId)) { var transaction = new FinancialTransaction { CreatedByPersonAliasId = ImportPersonAliasId, ModifiedByPersonAliasId = ImportPersonAliasId, TransactionTypeValueId = transactionTypeContributionId, ForeignKey = contributionId.ToString(), ForeignId = contributionId }; int?giverAliasId = null; var personKeys = GetPersonKeys(individualId, householdId); if (personKeys != null && personKeys.PersonAliasId > 0) { giverAliasId = personKeys.PersonAliasId; transaction.CreatedByPersonAliasId = giverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } var summary = row["Memo"] as string; if (!string.IsNullOrWhiteSpace(summary)) { transaction.Summary = summary; } var batchId = row["BatchID"] as int?; if (batchId.HasValue && 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 receivedDate = row["Received_Date"] as DateTime?; if (receivedDate.HasValue) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } var contributionFields = row.Columns.Select(c => c.Name).ToList(); var cardType = contributionFields.Contains("Card_Type") ? row["Card_Type"] as string : string.Empty; var cardLastFour = contributionFields.Contains("Last_Four") ? row["Last_Four"] as string : string.Empty; var contributionType = contributionFields.Contains("Contribution_Type_Name") ? row["Contribution_Type_Name"] as string : string.Empty; if (!string.IsNullOrWhiteSpace(contributionType)) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = TransactionSourceTypeOnsiteId; int?paymentCurrencyTypeId = null, creditCardTypeId = null; switch (contributionType.ToLower()) { case "cash": paymentCurrencyTypeId = currencyTypeCash; break; case "check": paymentCurrencyTypeId = currencyTypeCheck; break; case "ach": paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = TransactionSourceTypeWebsiteId; break; case "credit card": paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = TransactionSourceTypeWebsiteId; if (!string.IsNullOrWhiteSpace(cardType)) { creditCardTypeId = creditCardTypes.Where(t => t.Value.Equals(cardType, StringComparison.OrdinalIgnoreCase)) .Select(t => ( int? )t.Id).FirstOrDefault(); } break; default: paymentCurrencyTypeId = currencyTypeNonCash; break; } var paymentDetail = new FinancialPaymentDetail { CreatedDateTime = receivedDate, CreatedByPersonAliasId = giverAliasId, ModifiedDateTime = ImportDateTime, ModifiedByPersonAliasId = giverAliasId, CurrencyTypeValueId = paymentCurrencyTypeId, CreditCardTypeValueId = creditCardTypeId, AccountNumberMasked = cardLastFour, ForeignKey = contributionId.ToString(), ForeignId = contributionId }; transaction.FinancialPaymentDetail = paymentDetail; } var checkNumber = row["Check_Number"] as string; // if the check number is valid, put it in the transaction code if (checkNumber.AsType <int?>().HasValue) { transaction.TransactionCode = checkNumber; } // check for SecureGive kiosk transactions else if (!string.IsNullOrWhiteSpace(checkNumber) && checkNumber.StartsWith("SG")) { transaction.SourceTypeValueId = TransactionSourceTypeKioskId; } var fundName = contributionFields.Contains("Fund_Name") ? row["Fund_Name"] as string : string.Empty; var fundType = contributionFields.Contains("Fund_type") ? row["Fund_type"] as string : string.Empty; var subFund = contributionFields.Contains("Sub_Fund_Name") ? row["Sub_Fund_Name"] as string : string.Empty; var fundGLAccount = contributionFields.Contains("Fund_GL_Account") ? row["Fund_GL_Account"] as string : string.Empty; var subFundGLAccount = contributionFields.Contains("Sub_Fund_GL_Account") ? row["Sub_Fund_GL_Account"] as string : string.Empty; var isFundActive = contributionFields.Contains("Fund_Is_active") ? row["Fund_Is_active"] as string : null; var statedValue = row["Stated_Value"] as decimal?; var amount = row["Amount"] as decimal?; if (!string.IsNullOrWhiteSpace(fundName) && amount.HasValue) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault(a => !a.CampusId.HasValue && a.Name.Equals(fundName.Truncate(50), StringComparison.OrdinalIgnoreCase)); if (parentAccount == null) { int?accountTypeDefinedTypeId = null; if (!string.IsNullOrWhiteSpace(fundType)) { accountTypeDefinedTypeId = LoadAccountTypeDefinedValue(lookupContext, fundType); } parentAccount = AddFinancialAccount(lookupContext, fundName, $"{fundName} imported {ImportDateTime}", fundGLAccount, null, null, isFundActive.AsBooleanOrNull(), receivedDate, fundName.RemoveSpecialCharacters(), accountTypeValueId: accountTypeDefinedTypeId); 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.StartsWith(c.Name, StringComparison.OrdinalIgnoreCase) || subFund.StartsWith(c.ShortCode, StringComparison.OrdinalIgnoreCase)); if (campusFund != null) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = $"{subFund} {fundName}"; var childAccount = accountList.FirstOrDefault(c => c.ParentAccountId == parentAccount.Id && c.Name.Equals(subFund.Truncate(50), StringComparison.OrdinalIgnoreCase)); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddFinancialAccount(lookupContext, subFund, $"{subFund} imported {ImportDateTime}", subFundGLAccount, campusFundId, parentAccount.Id, isFundActive.AsBooleanOrNull(), receivedDate, subFund.RemoveSpecialCharacters(), accountTypeValueId: parentAccount.AccountTypeValueId); accountList.Add(childAccount); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if (amount == 0 && statedValue.HasValue && 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); completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, $"{completedItems:N0} contributions imported ({percentComplete}% complete)."); } if (completedItems % ReportingNumber < 1) { SaveContributions(newTransactions); newTransactions.Clear(); ReportPartialProgress(); } } } if (newTransactions.Any()) { SaveContributions(newTransactions); } ReportProgress(100, $"Finished contribution import: {completedItems:N0} contributions imported."); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="csvData">todo: describe csvData parameter on MapPledge</param> /// <returns></returns> /// <exception cref="System.NotImplementedException"></exception> private int MapPledge(CSVInstance csvData) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking() .Where(p => p.ForeignId != null) .ToDictionary(t => (int)t.ForeignId, t => (int?)t.Id); var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues; var oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id; var newPledges = new List <FinancialPledge>(); var completed = 0; ReportProgress(0, $"Verifying pledge import ({importedPledges.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 amountKey = row[TotalPledge]; var amount = amountKey.AsType <decimal?>(); var startDateKey = row[StartDate]; if (string.IsNullOrWhiteSpace(startDateKey)) { startDateKey = "01/01/0001"; } var startDate = startDateKey.AsType <DateTime?>(); var endDateKey = row[EndDate]; if (string.IsNullOrWhiteSpace(endDateKey)) { endDateKey = "12/31/9999"; } var endDate = endDateKey.AsType <DateTime?>(); var createdDateKey = row[PledgeCreatedDate]; if (string.IsNullOrWhiteSpace(createdDateKey)) { createdDateKey = ImportDateTime.ToString(); } var createdDate = createdDateKey.AsType <DateTime?>(); var modifiedDateKey = row[PledgeModifiedDate]; if (string.IsNullOrWhiteSpace(modifiedDateKey)) { modifiedDateKey = ImportDateTime.ToString(); } var modifiedDate = modifiedDateKey.AsType <DateTime?>(); var pledgeIdKey = row[PledgeId]; var pledgeId = pledgeIdKey.AsType <int?>(); if (amount != null && !importedPledges.ContainsKey((int)pledgeId)) { var individualIdKey = row[IndividualID]; var personKeys = GetPersonKeys(individualIdKey); if (personKeys != null && personKeys.PersonAliasId > 0) { var pledge = new FinancialPledge { PersonAliasId = personKeys.PersonAliasId, CreatedByPersonAliasId = ImportPersonAliasId, StartDate = (DateTime)startDate, EndDate = (DateTime)endDate, TotalAmount = (decimal)amount, CreatedDateTime = createdDate, ModifiedDateTime = modifiedDate, ModifiedByPersonAliasId = ImportPersonAliasId, ForeignKey = pledgeIdKey, ForeignId = pledgeId }; var frequency = row[PledgeFrequencyName].ToString().ToLower(); if (!string.IsNullOrWhiteSpace(frequency)) { frequency = frequency.ToLower(); if (frequency.Equals("one time") || frequency.Equals("one-time") || frequency.Equals("as can")) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency)) .Select(f => f.Id).FirstOrDefault(); } } 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?>(); if (!string.IsNullOrWhiteSpace(fundName)) { var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName.Truncate(50))); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, string.Empty, 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, string.Empty, campusFundId, parentAccount.Id, isSubFundActive, null, null, null, null, "", "", null); accountList.Add(childAccount); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add(pledge); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, $"{completed:N0} pledges imported."); } else if (completed % ReportingNumber < 1) { SavePledges(newPledges); ReportPartialProgress(); newPledges.Clear(); } } } } if (newPledges.Any()) { SavePledges(newPledges); } ReportProgress(100, $"Finished pledge import: {completed:N0} pledges imported."); return(completed); }
/// <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 ) { int transactionEntityTypeId = EntityTypeCache.Read( "Rock.Model.FinancialTransaction" ).Id; var accountService = new FinancialAccountService(); var attributeService = new AttributeService(); var transactionTypeContributionId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION ) ).Id; int currencyTypeACH = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH ) ).Id; int currencyTypeCash = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH ) ).Id; int currencyTypeCheck = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK ) ).Id; int currencyTypeCreditCard = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD ) ).Id; List<DefinedValue> refundReasons = new DefinedValueService().Queryable().Where( dv => dv.DefinedType.Guid == new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON ) ).ToList(); List<FinancialPledge> pledgeList = new FinancialPledgeService().Queryable().ToList(); List<FinancialAccount> accountList = accountService.Queryable().ToList(); // Add an Attribute for the unique F1 Contribution Id int contributionAttributeId = attributeService.Queryable().Where( a => a.EntityTypeId == transactionEntityTypeId && a.Key == "F1ContributionId" ).Select( a => a.Id ).FirstOrDefault(); if ( contributionAttributeId == 0 ) { var newContributionAttribute = new Rock.Model.Attribute(); newContributionAttribute.Key = "F1ContributionId"; newContributionAttribute.Name = "F1 Contribution Id"; newContributionAttribute.FieldTypeId = IntegerFieldTypeId; newContributionAttribute.EntityTypeId = transactionEntityTypeId; newContributionAttribute.EntityTypeQualifierValue = string.Empty; newContributionAttribute.EntityTypeQualifierColumn = string.Empty; newContributionAttribute.Description = "The FellowshipOne identifier for the contribution that was imported"; newContributionAttribute.DefaultValue = string.Empty; newContributionAttribute.IsMultiValue = false; newContributionAttribute.IsRequired = false; newContributionAttribute.Order = 0; attributeService.Add( newContributionAttribute, ImportPersonAlias ); attributeService.Save( newContributionAttribute, ImportPersonAlias ); contributionAttributeId = newContributionAttribute.Id; } var contributionAttribute = AttributeCache.Read( contributionAttributeId ); // Get all imported contributions var importedContributions = new AttributeValueService().GetByAttributeId( contributionAttributeId ) .Select( av => new { ContributionId = av.Value.AsType<int?>(), TransactionId = av.EntityId } ) .ToDictionary( t => t.ContributionId, t => t.TransactionId ); // 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( "Checking 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(); transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.AuthorizedPersonId = GetPersonId( individualId, householdId ); transaction.CreatedByPersonAliasId = ImportPersonAlias.Id; transaction.AuthorizedPersonId = GetPersonId( individualId, householdId ); 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"] as string; 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 ) ); } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; decimal? amount = row["Amount"] as decimal?; if ( fundName != null & amount != null ) { FinancialAccount matchingAccount = null; fundName = fundName.Trim(); int? fundCampusId = null; if ( subFund != null ) { subFund = subFund.Trim(); fundCampusId = CampusList.Where( c => c.Name.StartsWith( subFund ) || c.ShortCode == subFund ) .Select( c => (int?)c.Id ).FirstOrDefault(); if ( fundCampusId != null ) { matchingAccount = accountList.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.CampusId != null && a.CampusId.Equals( fundCampusId ) ); } else { matchingAccount = accountList.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.Name.StartsWith( subFund ) ); } } else { matchingAccount = accountList.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.CampusId == null ); } if ( matchingAccount == null ) { matchingAccount = new FinancialAccount(); matchingAccount.Name = fundName; matchingAccount.PublicName = fundName; matchingAccount.IsTaxDeductible = true; matchingAccount.IsActive = true; matchingAccount.CampusId = fundCampusId; matchingAccount.CreatedByPersonAliasId = ImportPersonAlias.Id; accountService.Add( matchingAccount ); accountService.Save( matchingAccount ); 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.Name.Contains( summary ) ) .Select( dv => (int?)dv.Id ).FirstOrDefault(); transaction.Refund = transactionRefund; } } // Other Attributes to create: // Pledge_Drive_Name // Stated_Value // True_Value // Liquidation_cost transaction.Attributes = new Dictionary<string, AttributeCache>(); transaction.AttributeValues = new Dictionary<string, List<AttributeValue>>(); transaction.Attributes.Add( contributionAttribute.Key, contributionAttribute ); transaction.AttributeValues.Add( contributionAttribute.Key, new List<AttributeValue>() ); transaction.AttributeValues[contributionAttribute.Key].Add( new AttributeValue() { AttributeId = contributionAttribute.Id, Value = contributionId.ToString(), Order = 0 } ); 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 ) { RockTransactionScope.WrapTransaction( () => { var transactionService = new FinancialTransactionService(); transactionService.RockContext.FinancialTransactions.AddRange( newTransactions ); transactionService.RockContext.SaveChanges(); var attributeValueService = new AttributeValueService(); foreach ( var contribution in newTransactions.Where( c => c.Attributes.Any() ) ) { var attributeValue = contribution.AttributeValues[contributionAttribute.Key].FirstOrDefault(); if ( attributeValue != null ) { attributeValue.EntityId = contribution.Id; attributeValueService.RockContext.AttributeValues.Add( attributeValue ); } } attributeValueService.RockContext.SaveChanges(); } ); newTransactions.Clear(); ReportPartialProgress(); } } } if ( newTransactions.Any() ) { RockTransactionScope.WrapTransaction( () => { var transactionService = new FinancialTransactionService(); transactionService.RockContext.FinancialTransactions.AddRange( newTransactions ); transactionService.RockContext.SaveChanges(); var attributeValueService = new AttributeValueService(); foreach ( var contribution in newTransactions.Where( c => c.Attributes.Any() ) ) { var attributeValue = contribution.AttributeValues[contributionAttribute.Key].FirstOrDefault(); if ( attributeValue != null ) { attributeValue.EntityId = contribution.Id; attributeValueService.RockContext.AttributeValues.Add( attributeValue ); } } attributeValueService.RockContext.SaveChanges(); } ); } ReportProgress( 100, string.Format( "Finished contribution import: {0:N0} contributions imported.", completed ) ); }
/// <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); }
/// <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 ) ); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private int MapPledge( CSVInstance csvData ) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService( lookupContext ).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService( lookupContext ).Queryable().AsNoTracking() .Where( p => p.ForeignId != null ) .ToDictionary( t => ( int )t.ForeignId, t => ( int? )t.Id ); var pledgeFrequencies = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY ), lookupContext ).DefinedValues; int oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault( f => f.Guid == new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME ) ).Id; var newPledges = new List<FinancialPledge>(); int completed = 0; ReportProgress( 0, string.Format( "Verifying pledge import ({0:N0} already exist).", importedPledges.Count ) ); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ( (row = csvData.Database.FirstOrDefault()) != null ) { string amountKey = row[TotalPledge]; decimal? amount = amountKey.AsType<decimal?>(); string startDateKey = row[StartDate]; if ( String.IsNullOrWhiteSpace( startDateKey ) ) { startDateKey = "01/01/0001"; } DateTime? startDate = startDateKey.AsType<DateTime?>(); string endDateKey = row[EndDate]; if ( String.IsNullOrWhiteSpace( endDateKey ) ) { endDateKey = "12/31/9999"; } DateTime? endDate = endDateKey.AsType<DateTime?>(); string pledgeIdKey = row[PledgeId]; int? pledgeId = pledgeIdKey.AsType<int?>(); if ( amount != null && !importedPledges.ContainsKey( ( int )pledgeId ) ) { string individualIdKey = row[IndividualID]; int? individualId = individualIdKey.AsType<int?>(); var personKeys = GetPersonKeys( individualId ); if ( personKeys != null && personKeys.PersonAliasId > 0 ) { var pledge = new FinancialPledge(); pledge.PersonAliasId = personKeys.PersonAliasId; pledge.CreatedByPersonAliasId = ImportPersonAliasId; pledge.StartDate = ( DateTime )startDate; pledge.EndDate = ( DateTime )endDate; pledge.TotalAmount = ( decimal )amount; pledge.CreatedDateTime = ImportDateTime; pledge.ModifiedDateTime = ImportDateTime; pledge.ModifiedByPersonAliasId = ImportPersonAliasId; pledge.ForeignKey = pledgeIdKey; pledge.ForeignId = pledgeId; string frequency = row[PledgeFrequencyName].ToString().ToLower(); if ( !String.IsNullOrWhiteSpace( frequency ) ) { frequency = frequency.ToLower(); if ( frequency.Equals( "one time" ) || frequency.Equals( "one-time" ) || frequency.Equals( "as can" ) ) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where( f => f.Value.ToLower().StartsWith( frequency ) || f.Description.ToLower().StartsWith( frequency ) ) .Select( f => f.Id ).FirstOrDefault(); } } string fundName = row[FundName] as string; string subFund = row[SubFundName] as string; string fundGLAccount = row[FundGLAccount] as string; string subFundGLAccount = row[SubFundGLAccount] as string; string isFundActiveKey = row[FundIsActive]; Boolean? isFundActive = isFundActiveKey.AsType<Boolean?>(); string isSubFundActiveKey = row[SubFundIsActive]; Boolean? isSubFundActive = isSubFundActiveKey.AsType<Boolean?>(); if ( !String.IsNullOrWhiteSpace( fundName ) ) { var parentAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName.Truncate( 50 ) ) && a.CampusId == null ); if ( parentAccount == null ) { parentAccount = AddAccount( lookupContext, fundName, string.Empty, null, null, isFundActive ); 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.StartsWith( c.Name ) || subFund.StartsWith( c.ShortCode ) ); if ( campusFund != null ) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format( "{0} {1}", subFund, fundName ); 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, string.Empty, campusFundId, parentAccount.Id, isSubFundActive ); accountList.Add( childAccount ); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add( pledge ); completed++; if ( completed % (ReportingNumber * 10) < 1 ) { ReportProgress( 0, string.Format( "{0:N0} pledges imported.", completed ) ); } else if ( completed % ReportingNumber < 1 ) { SavePledges( newPledges ); ReportPartialProgress(); newPledges.Clear(); } } } } if ( newPledges.Any() ) { SavePledges( newPledges ); } ReportProgress( 100, string.Format( "Finished pledge import: {0:N0} pledges imported.", completed ) ); return completed; }
/// <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 currencyTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE)); int currencyTypeACH = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH))).Id; int currencyTypeCash = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH))).Id; int currencyTypeCheck = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK))).Id; int currencyTypeCreditCard = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD))).Id; int?currencyTypeNonCash = currencyTypes.DefinedValues.Where(dv => dv.Value.Equals("Non-Cash")).Select(dv => (int?)dv.Id).FirstOrDefault(); if (currencyTypeNonCash == null) { var newTenderNonCash = new DefinedValue(); newTenderNonCash.Value = "Non-Cash"; newTenderNonCash.Description = "Non-Cash"; newTenderNonCash.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; int sourceTypeOnsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_ONSITE_COLLECTION), lookupContext).Id; int sourceTypeWebsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_WEBSITE), lookupContext).Id; int 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]; } // Get all imported contributions var importedContributions = new FinancialTransactionService(lookupContext).Queryable().AsNoTracking() .Where(c => c.ForeignId != null) .ToDictionary(t => (int)t.ForeignId, t => (int?)t.Id); // 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.Where(r => r != null)) { 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((int)contributionId)) { var transaction = new FinancialTransaction(); transaction.CreatedByPersonAliasId = ImportPersonAliasId; transaction.ModifiedByPersonAliasId = ImportPersonAliasId; transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.ForeignKey = contributionId.ToString(); transaction.ForeignId = contributionId; int?giverAliasId = null; var personKeys = GetPersonKeys(individualId, householdId); if (personKeys != null && personKeys.PersonAliasId > 0) { giverAliasId = personKeys.PersonAliasId; transaction.CreatedByPersonAliasId = giverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } 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.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; } DateTime?receivedDate = row["Received_Date"] as DateTime?; if (receivedDate != null) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } string cardType = row["Card_Type"] as string; string cardLastFour = row["Last_Four"] as string; string contributionType = row["Contribution_Type_Name"].ToStringSafe().ToLower(); if (contributionType != null) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = sourceTypeOnsite; int?paymentCurrencyTypeId = null, creditCardTypeId = null; if (contributionType == "cash") { paymentCurrencyTypeId = currencyTypeCash; } else if (contributionType == "check") { paymentCurrencyTypeId = currencyTypeCheck; } else if (contributionType == "ach") { paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = sourceTypeWebsite; } else if (contributionType == "credit card") { paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = sourceTypeWebsite; if (cardType != null) { creditCardTypeId = creditCardTypes.Where(t => t.Value.Equals(cardType)).Select(t => (int?)t.Id).FirstOrDefault(); } } else { paymentCurrencyTypeId = currencyTypeNonCash; } var paymentDetail = new FinancialPaymentDetail(); paymentDetail.CreatedDateTime = receivedDate; paymentDetail.CreatedByPersonAliasId = giverAliasId; paymentDetail.ModifiedDateTime = ImportDateTime; paymentDetail.ModifiedByPersonAliasId = giverAliasId; paymentDetail.CurrencyTypeValueId = paymentCurrencyTypeId; paymentDetail.CreditCardTypeValueId = creditCardTypeId; paymentDetail.AccountNumberMasked = cardLastFour; paymentDetail.ForeignKey = contributionId.ToString(); paymentDetail.ForeignId = contributionId; transaction.FinancialPaymentDetail = paymentDetail; } string checkNumber = row["Check_Number"] as string; // if the check number is valid, put it in the transaction code if (checkNumber.AsType <int?>() != null) { transaction.TransactionCode = checkNumber; } // check for SecureGive kiosk transactions else if (!string.IsNullOrEmpty(checkNumber) && checkNumber.StartsWith("SG")) { transaction.SourceTypeValueId = sourceTypeKiosk; } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; string fundGLAccount = row["Fund_GL_Account"] as string; string subFundGLAccount = row["Sub_Fund_GL_Account"] as string; Boolean?isFundActive = row["Fund_Is_active"] as Boolean?; decimal?statedValue = row["Stated_Value"] as decimal?; decimal?amount = row["Amount"] as decimal?; if (fundName != null & amount != null) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, fundGLAccount, null, null, isFundActive); accountList.Add(parentAccount); } if (subFund != null) { int?campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode)); if (campusFund != null) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format("{0} {1}", subFund, fundName); var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund) && 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, isFundActive); accountList.Add(childAccount); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if (amount == 0 && statedValue != null && statedValue != 0) { amount = statedValue; } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.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 % 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)); }
/// <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)); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge(IQueryable <Row> tableData) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking().ToList(); var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues; int oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id; var newPledges = new List <FinancialPledge>(); int completed = 0; int totalRows = tableData.Count(); int percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying pledge import ({0:N0} found).", totalRows)); foreach (var row in tableData.Where(r => r != null)) { decimal? amount = row["Total_Pledge"] as decimal?; DateTime?startDate = row["Start_Date"] as DateTime?; DateTime?endDate = row["End_Date"] as DateTime?; if (amount != null && startDate != null && endDate != null) { int?individualId = row["Individual_ID"] as int?; int?householdId = row["Household_ID"] as int?; var personKeys = GetPersonKeys(individualId, householdId, includeVisitors: false); if (personKeys != null && personKeys.PersonAliasId > 0) { var pledge = new FinancialPledge(); pledge.PersonAliasId = personKeys.PersonAliasId; pledge.CreatedByPersonAliasId = ImportPersonAliasId; pledge.ModifiedDateTime = ImportDateTime; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; string frequency = row["Pledge_Frequency_Name"].ToString().ToLower(); if (frequency != null) { frequency = frequency.ToLower(); if (frequency.Equals("one time") || frequency.Equals("as can")) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency)) .Select(f => f.Id).FirstOrDefault(); } } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; if (fundName != null) { var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, string.Empty, null, null, null); accountList.Add(parentAccount); } if (subFund != null) { int?campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode)); if (campusFund != null) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format("{0} {1}", subFund, fundName); var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund) && c.ParentAccountId == parentAccount.Id); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddAccount(lookupContext, subFund, string.Empty, campusFundId, parentAccount.Id, null); accountList.Add(childAccount); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add(pledge); completed++; if (completed % percentage < 1) { int percentComplete = completed / percentage; ReportProgress(percentComplete, string.Format("{0:N0} pledges imported ({1}% complete).", completed, percentComplete)); } else if (completed % ReportingNumber < 1) { SavePledges(newPledges); ReportPartialProgress(); newPledges.Clear(); } } } } if (newPledges.Any()) { SavePledges(newPledges); } ReportProgress(100, string.Format("Finished pledge import: {0:N0} pledges imported.", completed)); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge(IQueryable <Row> tableData) { var lookupContext = new RockContext(); List <FinancialAccount> accountList = new FinancialAccountService(lookupContext).Queryable().ToList(); List <FinancialPledge> importedPledges = new FinancialPledgeService(lookupContext).Queryable().ToList(); var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues; var newPledges = new List <FinancialPledge>(); int completed = 0; int totalRows = tableData.Count(); int percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying pledge import ({0:N0} found).", totalRows)); foreach (var row in tableData) { decimal? amount = row["Total_Pledge"] as decimal?; DateTime?startDate = row["Start_Date"] as DateTime?; DateTime?endDate = row["End_Date"] as DateTime?; if (amount != null && startDate != null && endDate != null) { int?individualId = row["Individual_ID"] as int?; int?householdId = row["Household_ID"] as int?; int?personId = GetPersonAliasId(individualId, householdId); if (personId != null && !importedPledges.Any(p => p.PersonAliasId == personId && p.TotalAmount == amount && p.StartDate.Equals(startDate))) { var pledge = new FinancialPledge(); pledge.CreatedByPersonAliasId = ImportPersonAlias.Id; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; string frequency = row["Pledge_Frequency_Name"].ToString().ToLower(); if (frequency != null) { if (frequency == "one time" || frequency == "as can") { pledge.PledgeFrequencyValueId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency)) .Select(f => f.Id).FirstOrDefault(); } } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; if (fundName != null) { FinancialAccount matchingAccount = null; int?fundCampusId = null; if (subFund != null) { // match by campus if the subfund appears to be a campus fundCampusId = CampusList.Where(c => c.Name.StartsWith(subFund) || c.ShortCode == subFund) .Select(c => (int?)c.Id).FirstOrDefault(); if (fundCampusId != null) { matchingAccount = accountList.FirstOrDefault(a => a.Name.StartsWith(fundName) && a.CampusId != null && a.CampusId.Equals(fundCampusId)); } else { matchingAccount = accountList.FirstOrDefault(a => a.Name.StartsWith(fundName) && a.Name.StartsWith(subFund)); } } else { matchingAccount = accountList.FirstOrDefault(a => a.Name.StartsWith(fundName)); } if (matchingAccount == null) { matchingAccount = AddAccount(lookupContext, fundName, fundCampusId); accountList.Add(matchingAccount); } pledge.AccountId = matchingAccount.Id; } // Attributes to add? // Pledge_Drive_Name newPledges.Add(pledge); completed++; if (completed % percentage < 1) { int percentComplete = completed / percentage; ReportProgress(percentComplete, string.Format("{0:N0} pledges imported ({1}% complete).", completed, percentComplete)); } else if (completed % ReportingNumber < 1) { SavePledges(newPledges); ReportPartialProgress(); } } } } if (newPledges.Any()) { SavePledges(newPledges); } ReportProgress(100, string.Format("Finished pledge import: {0:N0} pledges imported.", completed)); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="totalRows">The total rows.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge(IQueryable <Row> tableData, long totalRows = 0) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking().ToList(); var pledgeFrequencies = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues; var oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id; var newPledges = new List <FinancialPledge>(); if (totalRows == 0) { totalRows = tableData.Count(); } var completedItems = 0; var percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, $"Verifying pledge import ({totalRows:N0} found)."); foreach (var row in tableData.Where(r => r != null)) { var amount = row["Total_Pledge"] as decimal?; var startDate = row["Start_Date"] as DateTime?; var endDate = row["End_Date"] as DateTime?; if (amount.HasValue && startDate.HasValue && endDate.HasValue) { var individualId = row["Individual_ID"] as int?; var householdId = row["Household_ID"] as int?; var personKeys = GetPersonKeys(individualId, householdId, includeVisitors: false); if (personKeys != null && personKeys.PersonAliasId > 0) { var pledge = new FinancialPledge { PersonAliasId = personKeys.PersonAliasId, CreatedByPersonAliasId = ImportPersonAliasId, ModifiedDateTime = ImportDateTime, StartDate = ( DateTime )startDate, EndDate = ( DateTime )endDate, TotalAmount = ( decimal )amount }; var frequency = row["Pledge_Frequency_Name"].ToString(); if (!string.IsNullOrWhiteSpace(frequency)) { if (frequency.Equals("one time", StringComparison.OrdinalIgnoreCase) || frequency.Equals("as can", StringComparison.OrdinalIgnoreCase)) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where(f => f.Value.StartsWith(frequency, StringComparison.OrdinalIgnoreCase) || f.Description.StartsWith(frequency, StringComparison.OrdinalIgnoreCase)) .Select(f => f.Id).FirstOrDefault(); } } var fundName = row["Fund_Name"] as string; var subFund = row["Sub_Fund_Name"] as string; if (!string.IsNullOrWhiteSpace(fundName)) { var parentAccount = accountList.FirstOrDefault(a => !a.CampusId.HasValue && a.Name.Equals(fundName.Truncate(50), StringComparison.OrdinalIgnoreCase)); if (parentAccount == null) { parentAccount = AddFinancialAccount(lookupContext, fundName, $"{fundName} imported {ImportDateTime}", string.Empty, null, null, null, startDate, fundName.RemoveSpecialCharacters()); 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.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode)); if (campusFund != null) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = $"{subFund} {fundName}"; var childAccount = accountList.FirstOrDefault(c => c.ParentAccountId == parentAccount.Id && c.Name.Equals(subFund.Truncate(50), StringComparison.OrdinalIgnoreCase)); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddFinancialAccount(lookupContext, subFund, $"{subFund} imported {ImportDateTime}", string.Empty, campusFundId, parentAccount.Id, null, startDate, subFund.RemoveSpecialCharacters(), accountTypeValueId: parentAccount.AccountTypeValueId); accountList.Add(childAccount); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add(pledge); completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, $"{completedItems:N0} pledges imported ({percentComplete}% complete)."); } if (completedItems % ReportingNumber < 1) { SavePledges(newPledges); ReportPartialProgress(); newPledges.Clear(); } } } } if (newPledges.Any()) { SavePledges(newPledges); } ReportProgress(100, $"Finished pledge import: {completedItems:N0} pledges imported."); }
/// <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; 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; var accountList = new FinancialAccountService( lookupContext ).Queryable().ToList(); // Get all imported contributions var importedContributions = new FinancialTransactionService( lookupContext ).Queryable() .Where( c => c.ForeignId != null ) .ToDictionary( t => t.ForeignId.AsType<int>(), t => (int?)t.Id ); // 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.Where( r => r != null ) ) { 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( (int)contributionId ) ) { var transaction = new FinancialTransaction(); transaction.CreatedByPersonAliasId = ImportPersonAlias.Id; transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.ForeignId = contributionId.ToString(); var personKeys = GetPersonKeys( individualId, householdId, includeVisitors: false ); if ( personKeys != null ) { transaction.AuthorizedPersonAliasId = personKeys.PersonAliasId; transaction.ProcessedByPersonAliasId = personKeys.PersonAliasId; } 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.Equals( batchId ) ) ) { transaction.BatchId = ImportedBatches.FirstOrDefault( b => b.Key.Equals( 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 ) { // set the transaction code to the check number transaction.TransactionCode = checkNumber; } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; decimal? amount = row["Amount"] as decimal?; if ( fundName != null & amount != null ) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName ) && a.CampusId == null ); if ( parentAccount == null ) { parentAccount = AddAccount( lookupContext, fundName, null, null ); accountList.Add( parentAccount ); } if ( subFund != null ) { int? campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault( c => subFund.StartsWith( c.Name ) || subFund.StartsWith( c.ShortCode ) ); if ( campusFund != null ) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format( "{0} {1}", subFund, fundName ); var childAccount = accountList.FirstOrDefault( c => c.Name.Equals( subFund ) && c.ParentAccountId == parentAccount.Id ); if ( childAccount == null ) { // create a child account with a campusId if it was set childAccount = AddAccount( lookupContext, subFund, campusFundId, parentAccount.Id ); accountList.Add( childAccount ); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.AccountId = transactionAccountId; 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 ) ); }