/// <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 specified folder. /// </summary> /// <param name="folder">The folder.</param> /// <param name="transactionImageType">Type of the transaction image file.</param> /// <param name="storageProvider">The storage provider.</param> public void Map( ZipArchive folder, BinaryFileType transactionImageType, ProviderComponent storageProvider ) { var lookupContext = new RockContext(); var emptyJsonObject = "{}"; var newFileList = new Dictionary<int, Rock.Model.BinaryFile>(); var transactionIdList = new FinancialTransactionService( lookupContext ) .Queryable().AsNoTracking().Where( t => t.ForeignId != null ) .ToDictionary( t => (int)t.ForeignId, t => t.Id ); int completed = 0; int totalRows = folder.Entries.Count; int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying files import ({0:N0} found.", totalRows ) ); foreach ( var file in folder.Entries ) { var fileExtension = Path.GetExtension( file.Name ); var fileMimeType = Extensions.GetMIMEType( file.Name ); if ( BinaryFileComponent.FileTypeBlackList.Contains( fileExtension ) ) { LogException( "Binary File Import", string.Format( "{0} filetype not allowed ({1})", fileExtension, file.Name ) ); continue; } else if ( fileMimeType == null ) { LogException( "Binary File Import", string.Format( "{0} filetype not recognized ({1})", fileExtension, file.Name ) ); continue; } int? transactionId = Path.GetFileNameWithoutExtension( file.Name ).AsType<int?>(); if ( transactionId != null && transactionIdList.ContainsKey( (int)transactionId ) ) { var rockFile = new Rock.Model.BinaryFile(); rockFile.IsSystem = false; rockFile.IsTemporary = false; rockFile.FileName = file.Name; rockFile.MimeType = fileMimeType; rockFile.BinaryFileTypeId = transactionImageType.Id; rockFile.CreatedDateTime = file.LastWriteTime.DateTime; rockFile.ModifiedDateTime = ImportDateTime; rockFile.Description = string.Format( "Imported as {0}", file.Name ); rockFile.SetStorageEntityTypeId( transactionImageType.StorageEntityTypeId ); rockFile.StorageEntitySettings = emptyJsonObject; if ( transactionImageType.AttributeValues.Any() ) { rockFile.StorageEntitySettings = transactionImageType.AttributeValues .ToDictionary( a => a.Key, v => v.Value.Value ).ToJson(); } // use base stream instead of file stream to keep the byte[] // NOTE: if byte[] converts to a string it will corrupt the stream using ( var fileContent = new StreamReader( file.Open() ) ) { rockFile.ContentStream = new MemoryStream( fileContent.BaseStream.ReadBytesToEnd() ); } newFileList.Add( transactionIdList[(int)transactionId], rockFile ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} files imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { SaveFiles( newFileList, storageProvider ); // Reset list newFileList.Clear(); ReportPartialProgress(); } } } if ( newFileList.Any() ) { SaveFiles( newFileList, storageProvider ); } ReportProgress( 100, string.Format( "Finished files import: {0:N0} addresses imported.", 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; } 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="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 ) ); }