/// <summary>
/// Maps the batch data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <exception cref="System.NotImplementedException"></exception>
private void MapBatch(IQueryable <Row> tableData)
{
var batchStatusClosed = Rock.Model.BatchStatus.Closed;
var newBatches = new List <FinancialBatch>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying batch import ({0:N0} found, {1:N0} already exist).", totalRows, ImportedBatches.Count));
foreach (var row in tableData.Where(r => r != null))
{
int?batchId = row["BatchID"] as int?;
if (batchId != null && !ImportedBatches.ContainsKey((int)batchId))
{
var batch = new FinancialBatch();
batch.CreatedByPersonAliasId = ImportPersonAliasId;
batch.ForeignKey = batchId.ToString();
batch.ForeignId = batchId;
batch.Note = string.Empty;
batch.Status = batchStatusClosed;
batch.AccountingSystemCode = string.Empty;
string name = row["BatchName"] as string;
if (name != null)
{
name = name.Trim();
batch.Name = name.Left(50);
batch.CampusId = CampusList.Where(c => name.StartsWith(c.Name) || name.StartsWith(c.ShortCode))
.Select(c => (int?)c.Id).FirstOrDefault();
}
DateTime?batchDate = row["BatchDate"] as DateTime?;
if (batchDate != null)
{
batch.BatchStartDateTime = batchDate;
batch.BatchEndDateTime = batchDate;
}
decimal?amount = row["BatchAmount"] as decimal?;
if (amount != null)
{
batch.ControlAmount = amount.HasValue ? amount.Value : new decimal();
}
newBatches.Add(batch);
completed++;
if (completed % percentage < 1)
{
int percentComplete = completed / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} batches imported ({1}% complete).", completed, percentComplete));
}
else if (completed % ReportingNumber < 1)
{
SaveFinancialBatches(newBatches);
newBatches.ForEach(b => ImportedBatches.Add((int)b.ForeignId, (int?)b.Id));
newBatches.Clear();
ReportPartialProgress();
}
}
}
// add a default batch to use with contributions
if (!ImportedBatches.ContainsKey(0))
{
var defaultBatch = new FinancialBatch();
defaultBatch.CreatedDateTime = ImportDateTime;
defaultBatch.CreatedByPersonAliasId = ImportPersonAliasId;
defaultBatch.Status = Rock.Model.BatchStatus.Closed;
defaultBatch.Name = string.Format("Default Batch (Imported {0})", ImportDateTime);
defaultBatch.ControlAmount = 0.0m;
defaultBatch.ForeignKey = "0";
defaultBatch.ForeignId = 0;
newBatches.Add(defaultBatch);
}
if (newBatches.Any())
{
SaveFinancialBatches(newBatches);
newBatches.ForEach(b => ImportedBatches.Add((int)b.ForeignId, (int?)b.Id));
}
ReportProgress(100, string.Format("Finished batch import: {0:N0} batches 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 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 batch data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
/// <exception cref="System.NotImplementedException"></exception>
private void MapBatch(IQueryable <Row> tableData, long totalRows = 0)
{
var newBatches = new List <FinancialBatch>();
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var earliestBatchDate = ImportDateTime;
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, $"Verifying batch import ({totalRows:N0} found, {ImportedBatches.Count:N0} already exist).");
foreach (var row in tableData.Where(r => r != null))
{
var batchId = row["BatchID"] as int?;
if (batchId.HasValue && !ImportedBatches.ContainsKey(( int )batchId))
{
var batch = new FinancialBatch
{
CreatedByPersonAliasId = ImportPersonAliasId,
ForeignKey = batchId.ToString(),
ForeignId = batchId,
Note = string.Empty,
Status = BatchStatus.Closed,
AccountingSystemCode = string.Empty
};
var name = row["BatchName"] as string;
if (!string.IsNullOrWhiteSpace(name))
{
name = name.Trim();
batch.Name = name.Truncate(50);
batch.CampusId = GetCampusId(name);
}
var batchDate = row["BatchDate"] as DateTime?;
if (batchDate.HasValue)
{
batch.BatchStartDateTime = batchDate;
batch.BatchEndDateTime = batchDate;
if (batchDate < earliestBatchDate)
{
earliestBatchDate = ( DateTime )batchDate;
}
}
var amount = row["BatchAmount"] as decimal?;
if (amount.HasValue)
{
batch.ControlAmount = amount.Value;
}
newBatches.Add(batch);
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, $"{completedItems:N0} batches imported ({percentComplete}% complete).");
}
if (completedItems % ReportingNumber < 1)
{
SaveFinancialBatches(newBatches);
newBatches.ForEach(b => ImportedBatches.Add(( int )b.ForeignId, ( int? )b.Id));
newBatches.Clear();
ReportPartialProgress();
}
}
}
// add a default batch to use with contributions
if (!ImportedBatches.ContainsKey(0))
{
var defaultBatch = new FinancialBatch
{
CreatedDateTime = ImportDateTime,
CreatedByPersonAliasId = ImportPersonAliasId,
BatchStartDateTime = earliestBatchDate,
Status = BatchStatus.Closed,
Name = $"Default Batch (Imported {ImportDateTime})",
ControlAmount = 0.0m,
ForeignKey = "0",
ForeignId = 0
};
newBatches.Add(defaultBatch);
}
if (newBatches.Any())
{
SaveFinancialBatches(newBatches);
newBatches.ForEach(b => ImportedBatches.Add(( int )b.ForeignId, ( int? )b.Id));
}
ImportedBatches = new FinancialBatchService(new RockContext()).Queryable().AsNoTracking()
.Where(b => b.ForeignId.HasValue)
.ToDictionary(t => ( int )t.ForeignId, t => ( int? )t.Id);
ReportProgress(100, $"Finished batch import: {completedItems:N0} batches imported.");
}
/// <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 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)
{
parentAccount = AddFinancialAccount(lookupContext, fundName, $"{fundName} imported {ImportDateTime}", fundGLAccount, null, null, isFundActive.AsBooleanOrNull(), receivedDate, 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, 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());
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 batch data.
/// </summary>
/// <param name="csvData">The table data.</param>
/// <exception cref="System.NotImplementedException"></exception>
private int MapBatch(CSVInstance csvData)
{
var batchStatusClosed = Rock.Model.BatchStatus.Closed;
var newBatches = new List <FinancialBatch>();
int completed = 0;
ReportProgress(0, string.Format("Verifying batch import ({0:N0} already exist).", ImportedBatches.Count));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
string batchIdKey = row[BatchID];
int? batchId = batchIdKey.AsType <int?>();
if (batchId != null && !ImportedBatches.ContainsKey((int)batchId))
{
var batch = new FinancialBatch();
batch.CreatedByPersonAliasId = ImportPersonAliasId;
batch.ForeignKey = batchId.ToString();
batch.ForeignId = batchId;
batch.Note = string.Empty;
batch.Status = batchStatusClosed;
batch.AccountingSystemCode = string.Empty;
string name = row[BatchName] as string;
if (name != null)
{
name = name.Trim();
batch.Name = name.Left(50);
batch.CampusId = CampusList.Where(c => name.StartsWith(c.Name) || name.StartsWith(c.ShortCode))
.Select(c => (int?)c.Id).FirstOrDefault();
}
string batchDateKey = row[BatchDate];
DateTime?batchDate = batchDateKey.AsType <DateTime?>();
if (batchDate != null)
{
batch.BatchStartDateTime = batchDate;
batch.BatchEndDateTime = batchDate;
}
string amountKey = row[BatchAmount];
decimal?amount = amountKey.AsType <decimal?>();
if (amount != null)
{
batch.ControlAmount = amount.HasValue ? amount.Value : new decimal();
}
newBatches.Add(batch);
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} batches imported.", completed));
}
else if (completed % ReportingNumber < 1)
{
SaveFinancialBatches(newBatches);
newBatches.ForEach(b => ImportedBatches.Add((int)b.ForeignId, (int?)b.Id));
newBatches.Clear();
ReportPartialProgress();
}
}
}
// add a default batch to use with contributions
if (!ImportedBatches.ContainsKey(0))
{
var defaultBatch = new FinancialBatch();
defaultBatch.CreatedDateTime = ImportDateTime;
defaultBatch.CreatedByPersonAliasId = ImportPersonAliasId;
defaultBatch.Status = Rock.Model.BatchStatus.Closed;
defaultBatch.Name = string.Format("Default Batch (Imported {0})", ImportDateTime);
defaultBatch.ControlAmount = 0.0m;
defaultBatch.ForeignKey = "0";
defaultBatch.ForeignId = 0;
newBatches.Add(defaultBatch);
}
if (newBatches.Any())
{
SaveFinancialBatches(newBatches);
newBatches.ForEach(b => ImportedBatches.Add((int)b.ForeignId, (int?)b.Id));
}
ReportProgress(100, string.Format("Finished batch import: {0:N0} batches 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 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 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 batch data.
/// </summary>
/// <param name="csvData">The table data.</param>
/// <exception cref="System.NotImplementedException"></exception>
private int MapBatch(CSVInstance csvData)
{
var newBatches = new List <FinancialBatch>();
var earliestBatchDate = ImportDateTime;
var completed = 0;
ReportProgress(0, $"Verifying batch import ({ImportedBatches.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 batchIdKey = row[BatchID];
var batchId = batchIdKey.AsType <int?>();
if (batchId != null && !ImportedBatches.ContainsKey((int)batchId))
{
var batch = new FinancialBatch
{
CreatedByPersonAliasId = ImportPersonAliasId,
ForeignKey = batchId.ToString(),
ForeignId = batchId,
Note = string.Empty,
Status = BatchStatus.Closed,
AccountingSystemCode = string.Empty
};
var name = row[BatchName] as string;
if (!string.IsNullOrWhiteSpace(name))
{
name = name.Trim();
batch.Name = name.Left(50);
batch.CampusId = CampusList.Where(c => name.StartsWith(c.Name) || name.StartsWith(c.ShortCode))
.Select(c => (int?)c.Id).FirstOrDefault();
}
var batchDate = ParseDateOrDefault(row[BatchDate], null);
if (batchDate.HasValue)
{
batch.BatchStartDateTime = batchDate;
batch.BatchEndDateTime = batchDate;
if (earliestBatchDate > batchDate)
{
earliestBatchDate = (DateTime)batchDate;
}
}
var amountKey = row[BatchAmount];
var amount = amountKey.AsType <decimal?>();
if (amount != null)
{
batch.ControlAmount = amount.HasValue ? amount.Value : new decimal();
}
newBatches.Add(batch);
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, $"{completed:N0} batches imported.");
}
else if (completed % ReportingNumber < 1)
{
SaveFinancialBatches(newBatches);
foreach (var b in newBatches)
{
if (!ImportedBatches.ContainsKey((int)b.ForeignId))
{
ImportedBatches.Add((int)b.ForeignId, b.Id);
}
else
{
LogException("Duplicate Batch", string.Format("Batch #{0} is a duplicate and will be skipped. Please check the source data.", b.ForeignId));
}
}
newBatches.Clear();
ReportPartialProgress();
}
}
}
// add a default batch to use with contributions
if (!ImportedBatches.ContainsKey(0))
{
var defaultBatch = new FinancialBatch
{
CreatedDateTime = ImportDateTime,
CreatedByPersonAliasId = ImportPersonAliasId,
Status = BatchStatus.Closed,
BatchStartDateTime = earliestBatchDate,
Name = $"Default Batch {ImportDateTime}",
ControlAmount = 0.0m,
ForeignKey = "0",
ForeignId = 0
};
newBatches.Add(defaultBatch);
}
if (newBatches.Any())
{
SaveFinancialBatches(newBatches);
newBatches.ForEach(b => ImportedBatches.Add((int)b.ForeignId, (int?)b.Id));
}
ReportProgress(100, $"Finished batch import: {completed:N0} batches imported.");
return(completed);
}
