/// <summary>
/// Maps the activity ministry data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapActivityMinistry(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var newGroups = new List <Group>();
const string attendanceTypeName = "Attendance History";
var groupTypeHistory = ImportedGroupTypes.FirstOrDefault(t => t.ForeignKey.Equals(attendanceTypeName));
if (groupTypeHistory == null)
{
groupTypeHistory = AddGroupType(lookupContext, attendanceTypeName, string.Format("{0} imported {1}", attendanceTypeName, ImportDateTime), null,
null, GroupTypeCheckinTemplateId, true, true, true, true, typeForeignKey: attendanceTypeName);
ImportedGroupTypes.Add(groupTypeHistory);
}
const string groupsParentName = "Archived Groups";
var archivedGroupsParent = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(groupsParentName.RemoveWhitespace()));
if (archivedGroupsParent == null)
{
archivedGroupsParent = AddGroup(lookupContext, GeneralGroupTypeId, null, groupsParentName, true, null, ImportDateTime, groupsParentName.RemoveWhitespace(), true, ImportPersonAliasId);
ImportedGroups.Add(archivedGroupsParent);
}
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying ministry import ({0:N0} found, {1:N0} already exist).", totalRows, ImportedGroupTypes.Count));
foreach (var row in tableData.OrderBy(r => r["Ministry_ID"] as int?).ThenBy(r => r["Activity_ID"] as int?))
{
// get the ministry data
var ministryId = row["Ministry_ID"] as int?;
var activityId = row["Activity_ID"] as int?;
var ministryName = row["Ministry_Name"] as string;
var activityName = row["Activity_Name"] as string;
var ministryActive = row["Ministry_Active"] as string;
var activityActive = row["Activity_Active"] as string;
int?campusId = null;
if (ministryId.HasValue && !string.IsNullOrWhiteSpace(ministryName) && !ministryName.Equals("Delete", StringComparison.OrdinalIgnoreCase))
{
// check for a ministry group campus context
if (ministryName.Any(n => ValidDelimiters.Contains(n)))
{
campusId = campusId ?? GetCampusId(ministryName);
if (campusId.HasValue)
{
// strip the campus from the ministry name to use for grouptype (use the original name on groups though)
ministryName = StripPrefix(ministryName, campusId);
}
}
// add the new grouptype if it doesn't exist
var currentGroupType = ImportedGroupTypes.FirstOrDefault(t => t.ForeignKey.Equals(ministryName));
if (currentGroupType == null)
{
// save immediately so we can use the grouptype for a group
currentGroupType = AddGroupType(lookupContext, ministryName, string.Format("{0} imported {1}", ministryName, ImportDateTime), groupTypeHistory.Id,
null, null, true, true, true, true, typeForeignKey: ministryName);
ImportedGroupTypes.Add(currentGroupType);
}
// create a campus level parent for the ministry group
var parentGroupId = archivedGroupsParent.Id;
if (campusId.HasValue)
{
var campus = CampusList.FirstOrDefault(c => c.Id == campusId);
var campusGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(campus.ShortCode) && g.ParentGroupId == parentGroupId);
if (campusGroup == null)
{
campusGroup = AddGroup(lookupContext, GeneralGroupTypeId, parentGroupId, campus.Name, true, campus.Id, ImportDateTime, campus.ShortCode, true, ImportPersonAliasId);
ImportedGroups.Add(campusGroup);
}
parentGroupId = campusGroup.Id;
}
// add a ministry group level if it doesn't exist
var ministryGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(ministryId.ToString()));
if (ministryGroup == null)
{
// save immediately so we can use the group as a parent
ministryGroup = AddGroup(lookupContext, currentGroupType.Id, parentGroupId, ministryName, ministryActive.AsBoolean(), campusId, null, ministryId.ToString(), true, ImportPersonAliasId);
ImportedGroups.Add(ministryGroup);
}
// check for an activity group campus context
if (!string.IsNullOrWhiteSpace(activityName) && activityName.Any(n => ValidDelimiters.Contains(n)))
{
campusId = campusId ?? GetCampusId(activityName);
if (campusId.HasValue)
{
activityName = StripPrefix(activityName, campusId);
}
}
// add the child activity group if it doesn't exist
var activityGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(activityId.ToString()));
if (activityGroup == null && activityId.HasValue && !string.IsNullOrWhiteSpace(activityName) && !activityName.Equals("Delete", StringComparison.OrdinalIgnoreCase))
{
// don't save immediately, we'll batch add later
activityGroup = AddGroup(lookupContext, currentGroupType.Id, ministryGroup.Id, activityName, activityActive.AsBoolean(), campusId, null, activityId.ToString(), false, ImportPersonAliasId);
newGroups.Add(activityGroup);
}
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} ministries imported ({1}% complete).", completedItems, percentComplete));
}
if (completedItems % ReportingNumber < 1)
{
SaveGroups(newGroups);
ReportPartialProgress();
ImportedGroups.AddRange(newGroups);
// Reset lists and context
lookupContext = new RockContext();
newGroups.Clear();
}
}
}
if (newGroups.Any())
{
SaveGroups(newGroups);
ImportedGroups.AddRange(newGroups);
}
lookupContext.Dispose();
ReportProgress(100, string.Format("Finished ministry import: {0:N0} ministries imported.", completedItems));
}
/// <summary>
/// Loads the Prayer Requests data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadPrayerRequest(CSVInstance csvData)
{
var lookupContext = new RockContext();
var importedPrayerRequests = new PrayerRequestService(lookupContext).Queryable().Count(p => p.ForeignKey != null);
var prayerRequestList = new List <PrayerRequest>();
var completedItems = 0;
var addedItems = 0;
ReportProgress(0, string.Format("Verifying prayer request import ({0:N0} already imported).", importedPrayerRequests));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
var prayerRequestCategory = row[PrayerRequestCategory] as string;
var prayerRequestText = row[PrayerRequestText] as string;
var prayerRequestDate = row[PrayerRequestDate] as string;
var prayerRequestId = row[PrayerRequestId] as string;
var prayerRequestFirstName = row[PrayerRequestFirstName] as string;
var prayerRequestLastName = row[PrayerRequestLastName] as string;
var prayerRequestEmail = row[PrayerRequestEmail] as string;
var prayerRequestExpireDate = row[PrayerRequestExpireDate] as string;
var prayerRequestAllowComments = ParseBoolOrDefault(row[PrayerRequestAllowComments], true);
var prayerRequestIsPublic = ParseBoolOrDefault(row[PrayerRequestIsPublic], true);
var prayerRequestIsApproved = ParseBoolOrDefault(row[PrayerRequestIsApproved], true);
var prayerRequestApprovedDate = row[PrayerRequestApprovedDate] as string;
var prayerRequestApprovedById = row[PrayerRequestApprovedById] as string;
var prayerRequestCreatedById = row[PrayerRequestCreatedById] as string;
var prayerRequestRequestedById = row[PrayerRequestRequestedById] as string;
var prayerRequestAnswerText = row[PrayerRequestAnswerText] as string;
var prayerRequestCampusName = row[PrayerRequestCampus] as string;
if (!string.IsNullOrWhiteSpace(prayerRequestText))
{
var email = string.Empty;
if (prayerRequestEmail.IsEmail())
{
email = prayerRequestEmail;
}
int?approvedByAliasId = null;
var approvedByPersonKeys = GetPersonKeys(prayerRequestApprovedById);
if (approvedByPersonKeys != null)
{
approvedByAliasId = approvedByPersonKeys.PersonAliasId;
}
int?createdByAliasId = null;
var createdByPersonKeys = GetPersonKeys(prayerRequestCreatedById);
if (createdByPersonKeys != null)
{
createdByAliasId = createdByPersonKeys.PersonAliasId;
}
int?requestedByAliasId = null;
var requestedByPersonKeys = GetPersonKeys(prayerRequestRequestedById);
if (requestedByPersonKeys != null)
{
requestedByAliasId = requestedByPersonKeys.PersonAliasId;
}
var prayerRequest = AddPrayerRequest(lookupContext, prayerRequestCategory, prayerRequestText, prayerRequestDate, prayerRequestId,
prayerRequestFirstName, prayerRequestLastName, email, prayerRequestExpireDate, prayerRequestAllowComments, prayerRequestIsPublic,
prayerRequestIsApproved, prayerRequestApprovedDate, approvedByAliasId, createdByAliasId, requestedByAliasId, prayerRequestAnswerText,
false);
if (prayerRequest.Id == 0)
{
if (!string.IsNullOrWhiteSpace(prayerRequestCampusName))
{
var campus = CampusList.FirstOrDefault(c => c.Name.Equals(prayerRequestCampusName, StringComparison.OrdinalIgnoreCase) ||
c.ShortCode.Equals(prayerRequestCampusName, StringComparison.OrdinalIgnoreCase));
if (campus == null)
{
campus = new Campus
{
IsSystem = false,
Name = prayerRequestCampusName,
ShortCode = prayerRequestCampusName.RemoveWhitespace(),
IsActive = true
};
lookupContext.Campuses.Add(campus);
lookupContext.SaveChanges(DisableAuditing);
CampusList.Add(campus);
}
prayerRequest.CampusId = campus.Id;
}
prayerRequestList.Add(prayerRequest);
addedItems++;
}
completedItems++;
if (completedItems % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} prayer requests processed.", completedItems));
}
if (completedItems % ReportingNumber < 1)
{
SavePrayerRequests(prayerRequestList);
ReportPartialProgress();
prayerRequestList.Clear();
}
}
}
if (prayerRequestList.Any())
{
SavePrayerRequests(prayerRequestList);
}
ReportProgress(100, string.Format("Finished prayer request import: {0:N0} prayer requests processed, {1:N0} imported.", completedItems, addedItems));
return(completedItems);
}
/// <summary>
/// Loads the family data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadFamily(CSVInstance csvData)
{
// Required variables
var lookupContext = new RockContext();
var locationService = new LocationService(lookupContext);
var newGroupLocations = new Dictionary <GroupLocation, string>();
var currentFamilyGroup = new Group();
var newFamilyList = new List <Group>();
var updatedFamilyList = new List <Group>();
var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" };
var currentFamilyKey = string.Empty;
var completed = 0;
ReportProgress(0, $"Starting family import ({ImportedFamilies.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 rowFamilyKey = row[FamilyId];
var rowFamilyId = rowFamilyKey.AsType <int?>();
var rowFamilyName = row[FamilyName];
if (rowFamilyKey != null && rowFamilyKey != currentFamilyGroup.ForeignKey)
{
currentFamilyGroup = ImportedFamilies.FirstOrDefault(g => g.ForeignKey == rowFamilyKey);
if (currentFamilyGroup == null)
{
currentFamilyGroup = new Group
{
ForeignKey = rowFamilyKey,
ForeignId = rowFamilyId,
CreatedByPersonAliasId = ImportPersonAliasId,
GroupTypeId = FamilyGroupTypeId
};
newFamilyList.Add(currentFamilyGroup);
}
else if (!lookupContext.ChangeTracker.Entries <Group>().Any(g => g.Entity.ForeignKey == rowFamilyKey || (g.Entity.ForeignKey == null && g.Entity.ForeignId == rowFamilyId)))
{
// track changes if not currently tracking
lookupContext.Groups.Attach(currentFamilyGroup);
}
currentFamilyGroup.Name = row[FamilyName];
// Set the family campus
var campusName = row[Campus];
if (!string.IsNullOrWhiteSpace(campusName))
{
var familyCampus = CampusList.FirstOrDefault(c => c.Name.Equals(campusName, StringComparison.OrdinalIgnoreCase) ||
c.ShortCode.Equals(campusName, StringComparison.OrdinalIgnoreCase));
if (familyCampus == null)
{
familyCampus = new Campus
{
IsSystem = false,
Name = campusName,
ShortCode = campusName.RemoveWhitespace(),
IsActive = true
};
lookupContext.Campuses.Add(familyCampus);
lookupContext.SaveChanges(DisableAuditing);
CampusList.Add(familyCampus);
}
currentFamilyGroup.CampusId = familyCampus.Id;
lookupContext.SaveChanges(DisableAuditing);
}
// Add the family addresses since they exist in this file
var famAddress = row[Address];
var famAddress2 = row[Address2];
var famCity = row[City];
var famState = row[State];
var famZip = row[Zip];
var famCountry = row[Country];
var primaryAddress = locationService.Get(famAddress.Left(100), famAddress2.Left(100), famCity, famState, famZip, famCountry, verifyLocation: false);
if (primaryAddress != null && currentFamilyGroup.GroupLocations.Count == 0)
{
var primaryLocation = new GroupLocation
{
LocationId = primaryAddress.Id,
IsMailingLocation = true,
IsMappedLocation = true,
GroupLocationTypeValueId = HomeLocationTypeId
};
newGroupLocations.Add(primaryLocation, rowFamilyKey);
}
var famSecondAddress = row[SecondaryAddress];
var famSecondAddress2 = row[SecondaryAddress2];
var famSecondCity = row[SecondaryCity];
var famSecondState = row[SecondaryState];
var famSecondZip = row[SecondaryZip];
var famSecondCountry = row[SecondaryCountry];
var secondaryAddress = locationService.Get(famSecondAddress.Left(100), famSecondAddress2.Left(100), famSecondCity, famSecondState, famSecondZip, famSecondCountry, verifyLocation: false);
if (secondaryAddress != null && currentFamilyGroup.GroupLocations.Count < 2)
{
var secondaryLocation = new GroupLocation
{
LocationId = secondaryAddress.Id,
IsMailingLocation = true,
IsMappedLocation = false,
GroupLocationTypeValueId = PreviousLocationTypeId
};
newGroupLocations.Add(secondaryLocation, rowFamilyKey);
}
DateTime createdDateValue;
if (DateTime.TryParseExact(row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue))
{
currentFamilyGroup.CreatedDateTime = createdDateValue;
currentFamilyGroup.ModifiedDateTime = ImportDateTime;
}
else
{
currentFamilyGroup.CreatedDateTime = ImportDateTime;
currentFamilyGroup.ModifiedDateTime = ImportDateTime;
}
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, $"{completed:N0} families imported.");
}
if (completed % ReportingNumber < 1)
{
SaveFamilies(newFamilyList, newGroupLocations);
ReportPartialProgress();
// Reset lookup context
lookupContext.SaveChanges();
lookupContext.Dispose();
lookupContext = new RockContext();
locationService = new LocationService(lookupContext);
newFamilyList.Clear();
newGroupLocations.Clear();
}
}
}
// Check to see if any rows didn't get saved to the database
if (newGroupLocations.Any())
{
SaveFamilies(newFamilyList, newGroupLocations);
}
lookupContext.SaveChanges();
DetachAllInContext(lookupContext);
lookupContext.Dispose();
ReportProgress(0, $"Finished family import: {completed:N0} families added or updated.");
return(completed);
}
/// <summary>
/// Loads the family data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadMetrics(CSVInstance csvData)
{
// Required variables
var lookupContext = new RockContext();
var metricService = new MetricService(lookupContext);
var metricCategoryService = new MetricCategoryService(lookupContext);
var categoryService = new CategoryService(lookupContext);
var metricSourceTypes = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.METRIC_SOURCE_TYPE)).DefinedValues;
var metricManualSource = metricSourceTypes.FirstOrDefault(m => m.Guid == new Guid(Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_MANUAL));
var scheduleService = new ScheduleService(lookupContext);
var scheduleMetrics = scheduleService.Queryable().AsNoTracking()
.Where(s => s.Category.Guid == new Guid(Rock.SystemGuid.Category.SCHEDULE_SERVICE_TIMES)).ToList();
var scheduleCategoryId = categoryService.Queryable().AsNoTracking()
.Where(c => c.Guid == new Guid(Rock.SystemGuid.Category.SCHEDULE_SERVICE_TIMES)).FirstOrDefault().Id;
var metricEntityTypeId = EntityTypeCache.Get <MetricCategory>(false, lookupContext).Id;
var campusEntityTypeId = EntityTypeCache.Get <Campus>(false, lookupContext).Id;
var scheduleEntityTypeId = EntityTypeCache.Get <Schedule>(false, lookupContext).Id;
var allMetrics = metricService.Queryable().AsNoTracking().ToList();
var metricCategories = categoryService.Queryable().AsNoTracking()
.Where(c => c.EntityType.Guid == new Guid(Rock.SystemGuid.EntityType.METRICCATEGORY)).ToList();
var defaultMetricCategory = metricCategories.FirstOrDefault(c => c.Name == "Metrics");
if (defaultMetricCategory == null)
{
defaultMetricCategory = new Category();
defaultMetricCategory.Name = "Metrics";
defaultMetricCategory.IsSystem = false;
defaultMetricCategory.EntityTypeId = metricEntityTypeId;
defaultMetricCategory.EntityTypeQualifierColumn = string.Empty;
defaultMetricCategory.EntityTypeQualifierValue = string.Empty;
defaultMetricCategory.IconCssClass = string.Empty;
defaultMetricCategory.Description = string.Empty;
lookupContext.Categories.Add(defaultMetricCategory);
lookupContext.SaveChanges();
metricCategories.Add(defaultMetricCategory);
}
var metricValues = new List <MetricValue>();
Metric currentMetric = null;
int completed = 0;
ReportProgress(0, string.Format("Starting metrics import ({0:N0} already exist).", 0));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
string metricCampus = row[MetricCampus];
string metricName = row[MetricName];
string metricCategoryString = row[MetricCategory];
string metricNote = row[MetricNote];
if (!string.IsNullOrEmpty(metricName))
{
decimal? value = row[MetricValue].AsDecimalOrNull();
DateTime?valueDate = row[MetricService].AsDateTime();
var metricCategoryId = defaultMetricCategory.Id;
// create the category if it doesn't exist
Category newMetricCategory = null;
if (!string.IsNullOrEmpty(metricCategoryString))
{
newMetricCategory = metricCategories.FirstOrDefault(c => c.Name == metricCategoryString);
if (newMetricCategory == null)
{
newMetricCategory = new Category();
newMetricCategory.Name = metricCategoryString;
newMetricCategory.IsSystem = false;
newMetricCategory.EntityTypeId = metricEntityTypeId;
newMetricCategory.EntityTypeQualifierColumn = string.Empty;
newMetricCategory.EntityTypeQualifierValue = string.Empty;
newMetricCategory.IconCssClass = string.Empty;
newMetricCategory.Description = string.Empty;
lookupContext.Categories.Add(newMetricCategory);
lookupContext.SaveChanges();
metricCategories.Add(newMetricCategory);
}
metricCategoryId = newMetricCategory.Id;
}
// create metric if it doesn't exist
currentMetric = allMetrics.FirstOrDefault(m => m.Title == metricName && m.MetricCategories.Any(c => c.CategoryId == metricCategoryId));
if (currentMetric == null)
{
currentMetric = new Metric();
currentMetric.Title = metricName;
currentMetric.IsSystem = false;
currentMetric.IsCumulative = false;
currentMetric.SourceSql = string.Empty;
currentMetric.Subtitle = string.Empty;
currentMetric.Description = string.Empty;
currentMetric.IconCssClass = string.Empty;
currentMetric.SourceValueTypeId = metricManualSource.Id;
currentMetric.CreatedByPersonAliasId = ImportPersonAliasId;
currentMetric.CreatedDateTime = ImportDateTime;
currentMetric.ForeignKey = string.Format("Metric imported {0}", ImportDateTime);
currentMetric.MetricPartitions = new List <MetricPartition>();
currentMetric.MetricPartitions.Add(new MetricPartition {
Label = "Campus", EntityTypeId = campusEntityTypeId, Metric = currentMetric, Order = 0
});
currentMetric.MetricPartitions.Add(new MetricPartition {
Label = "Service", EntityTypeId = scheduleEntityTypeId, Metric = currentMetric, Order = 1
});
metricService.Add(currentMetric);
lookupContext.SaveChanges();
if (currentMetric.MetricCategories == null || !currentMetric.MetricCategories.Any(a => a.CategoryId == metricCategoryId))
{
metricCategoryService.Add(new MetricCategory {
CategoryId = metricCategoryId, MetricId = currentMetric.Id
});
lookupContext.SaveChanges();
}
allMetrics.Add(currentMetric);
}
// create values for this metric
var metricValue = new MetricValue();
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.CreatedByPersonAliasId = ImportPersonAliasId;
metricValue.CreatedDateTime = ImportDateTime;
metricValue.MetricValueDateTime = valueDate.Value.Date;
metricValue.MetricId = currentMetric.Id;
metricValue.Note = string.Empty;
metricValue.XValue = string.Empty;
metricValue.YValue = value;
metricValue.ForeignKey = string.Format("Metric Value imported {0}", ImportDateTime);
metricValue.Note = metricNote;
if (!string.IsNullOrWhiteSpace(metricCampus))
{
var campus = CampusList.Where(c => c.Name.Equals(metricCampus, StringComparison.OrdinalIgnoreCase) ||
c.ShortCode.Equals(metricCampus, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
if (campus == null)
{
var newCampus = new Campus();
newCampus.IsSystem = false;
newCampus.Name = metricCampus;
newCampus.ShortCode = metricCampus.RemoveWhitespace();
newCampus.IsActive = true;
lookupContext.Campuses.Add(newCampus);
lookupContext.SaveChanges(DisableAuditing);
CampusList.Add(newCampus);
campus = newCampus;
}
if (campus != null)
{
var metricPartitionCampusId = currentMetric.MetricPartitions.FirstOrDefault(p => p.Label == "Campus").Id;
metricValue.MetricValuePartitions.Add(new MetricValuePartition {
MetricPartitionId = metricPartitionCampusId, EntityId = campus.Id
});
}
}
if (valueDate.HasValue)
{
var metricPartitionScheduleId = currentMetric.MetricPartitions.FirstOrDefault(p => p.Label == "Service").Id;
var date = ( DateTime )valueDate;
var scheduleName = date.DayOfWeek.ToString();
if (date.TimeOfDay.TotalSeconds > 0)
{
scheduleName = scheduleName + string.Format(" {0}", date.ToString("hh:mm")) + string.Format("{0}", date.ToString("tt").ToLower());
}
if (!scheduleMetrics.Any(s => s.Name == scheduleName))
{
Schedule newSchedule = new Schedule();
newSchedule.Name = scheduleName;
newSchedule.CategoryId = scheduleCategoryId;
newSchedule.CreatedByPersonAliasId = ImportPersonAliasId;
newSchedule.CreatedDateTime = ImportDateTime;
newSchedule.ForeignKey = string.Format("Metric Schedule imported {0}", ImportDateTime);
scheduleMetrics.Add(newSchedule);
lookupContext.Schedules.Add(newSchedule);
lookupContext.SaveChanges();
}
var scheduleId = scheduleMetrics.FirstOrDefault(s => s.Name == scheduleName).Id;
metricValue.MetricValuePartitions.Add(new MetricValuePartition {
MetricPartitionId = metricPartitionScheduleId, EntityId = scheduleId
});
}
metricValues.Add(metricValue);
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} metrics imported.", completed));
}
if (completed % ReportingNumber < 1)
{
SaveMetrics(metricValues);
ReportPartialProgress();
metricValues.Clear();
}
}
}
// Check to see if any rows didn't get saved to the database
if (metricValues.Any())
{
SaveMetrics(metricValues);
}
ReportProgress(0, string.Format("Finished metrics import: {0:N0} metrics added or updated.", completed));
return(completed);
}
/// <summary>
/// Maps the contribution.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapContribution(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var transactionTypeContributionId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid(), lookupContext).Id;
var currencyTypes = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE));
var currencyTypeACH = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH))).Id;
var currencyTypeCash = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH))).Id;
var currencyTypeCheck = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK))).Id;
var currencyTypeCreditCard = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD))).Id;
var currencyTypeNonCash = currencyTypes.DefinedValues.Where(dv => dv.Value.Equals("Non-Cash")).Select(dv => ( int? )dv.Id).FirstOrDefault();
if (currencyTypeNonCash == null)
{
var newTenderNonCash = new DefinedValue
{
Value = "Non-Cash",
Description = "Non-Cash",
DefinedTypeId = currencyTypes.Id
};
lookupContext.DefinedValues.Add(newTenderNonCash);
lookupContext.SaveChanges();
currencyTypeNonCash = newTenderNonCash.Id;
}
var creditCardTypes = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CREDIT_CARD_TYPE)).DefinedValues;
var refundReasons = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON), lookupContext).DefinedValues;
var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList();
int?defaultBatchId = null;
if (ImportedBatches.ContainsKey(0))
{
defaultBatchId = ImportedBatches[0];
}
// Get all imported contributions
var importedContributions = new FinancialTransactionService(lookupContext).Queryable().AsNoTracking()
.Where(c => c.ForeignId != null)
.ToDictionary(t => ( int )t.ForeignId, t => ( int? )t.Id);
// List for batching new contributions
var newTransactions = new List <FinancialTransaction>();
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, $"Verifying contribution import ({totalRows:N0} found, {importedContributions.Count:N0} already exist).");
foreach (var row in tableData.Where(r => r != null))
{
var individualId = row["Individual_ID"] as int?;
var householdId = row["Household_ID"] as int?;
var contributionId = row["ContributionID"] as int?;
if (contributionId.HasValue && !importedContributions.ContainsKey(( int )contributionId))
{
var transaction = new FinancialTransaction
{
CreatedByPersonAliasId = ImportPersonAliasId,
ModifiedByPersonAliasId = ImportPersonAliasId,
TransactionTypeValueId = transactionTypeContributionId,
ForeignKey = contributionId.ToString(),
ForeignId = contributionId
};
int?giverAliasId = null;
var personKeys = GetPersonKeys(individualId, householdId);
if (personKeys != null && personKeys.PersonAliasId > 0)
{
giverAliasId = personKeys.PersonAliasId;
transaction.CreatedByPersonAliasId = giverAliasId;
transaction.AuthorizedPersonAliasId = giverAliasId;
transaction.ProcessedByPersonAliasId = giverAliasId;
}
var summary = row["Memo"] as string;
if (!string.IsNullOrWhiteSpace(summary))
{
transaction.Summary = summary;
}
var batchId = row["BatchID"] as int?;
if (batchId.HasValue && ImportedBatches.Any(b => b.Key.Equals(batchId)))
{
transaction.BatchId = ImportedBatches.FirstOrDefault(b => b.Key.Equals(batchId)).Value;
}
else
{
// use the default batch for any non-matching transactions
transaction.BatchId = defaultBatchId;
}
var receivedDate = row["Received_Date"] as DateTime?;
if (receivedDate.HasValue)
{
transaction.TransactionDateTime = receivedDate;
transaction.CreatedDateTime = receivedDate;
transaction.ModifiedDateTime = ImportDateTime;
}
var contributionFields = row.Columns.Select(c => c.Name).ToList();
var cardType = contributionFields.Contains("Card_Type") ? row["Card_Type"] as string : string.Empty;
var cardLastFour = contributionFields.Contains("Last_Four") ? row["Last_Four"] as string : string.Empty;
var contributionType = contributionFields.Contains("Contribution_Type_Name") ? row["Contribution_Type_Name"] as string : string.Empty;
if (!string.IsNullOrWhiteSpace(contributionType))
{
// set default source to onsite, exceptions listed below
transaction.SourceTypeValueId = TransactionSourceTypeOnsiteId;
int?paymentCurrencyTypeId = null, creditCardTypeId = null;
switch (contributionType.ToLower())
{
case "cash":
paymentCurrencyTypeId = currencyTypeCash;
break;
case "check":
paymentCurrencyTypeId = currencyTypeCheck;
break;
case "ach":
paymentCurrencyTypeId = currencyTypeACH;
transaction.SourceTypeValueId = TransactionSourceTypeWebsiteId;
break;
case "credit card":
paymentCurrencyTypeId = currencyTypeCreditCard;
transaction.SourceTypeValueId = TransactionSourceTypeWebsiteId;
if (!string.IsNullOrWhiteSpace(cardType))
{
creditCardTypeId = creditCardTypes.Where(t => t.Value.Equals(cardType, StringComparison.OrdinalIgnoreCase))
.Select(t => ( int? )t.Id).FirstOrDefault();
}
break;
default:
paymentCurrencyTypeId = currencyTypeNonCash;
break;
}
var paymentDetail = new FinancialPaymentDetail
{
CreatedDateTime = receivedDate,
CreatedByPersonAliasId = giverAliasId,
ModifiedDateTime = ImportDateTime,
ModifiedByPersonAliasId = giverAliasId,
CurrencyTypeValueId = paymentCurrencyTypeId,
CreditCardTypeValueId = creditCardTypeId,
AccountNumberMasked = cardLastFour,
ForeignKey = contributionId.ToString(),
ForeignId = contributionId
};
transaction.FinancialPaymentDetail = paymentDetail;
}
var checkNumber = row["Check_Number"] as string;
// if the check number is valid, put it in the transaction code
if (checkNumber.AsType <int?>().HasValue)
{
transaction.TransactionCode = checkNumber;
}
// check for SecureGive kiosk transactions
else if (!string.IsNullOrWhiteSpace(checkNumber) && checkNumber.StartsWith("SG"))
{
transaction.SourceTypeValueId = TransactionSourceTypeKioskId;
}
var fundName = contributionFields.Contains("Fund_Name") ? row["Fund_Name"] as string : string.Empty;
var fundType = contributionFields.Contains("Fund_type") ? row["Fund_type"] as string : string.Empty;
var subFund = contributionFields.Contains("Sub_Fund_Name") ? row["Sub_Fund_Name"] as string : string.Empty;
var fundGLAccount = contributionFields.Contains("Fund_GL_Account") ? row["Fund_GL_Account"] as string : string.Empty;
var subFundGLAccount = contributionFields.Contains("Sub_Fund_GL_Account") ? row["Sub_Fund_GL_Account"] as string : string.Empty;
var isFundActive = contributionFields.Contains("Fund_Is_active") ? row["Fund_Is_active"] as string : null;
var statedValue = row["Stated_Value"] as decimal?;
var amount = row["Amount"] as decimal?;
if (!string.IsNullOrWhiteSpace(fundName) && amount.HasValue)
{
int transactionAccountId;
var parentAccount = accountList.FirstOrDefault(a => !a.CampusId.HasValue && a.Name.Equals(fundName.Truncate(50), StringComparison.OrdinalIgnoreCase));
if (parentAccount == null)
{
int?accountTypeDefinedTypeId = null;
if (!string.IsNullOrWhiteSpace(fundType))
{
accountTypeDefinedTypeId = LoadAccountTypeDefinedValue(lookupContext, fundType);
}
parentAccount = AddFinancialAccount(lookupContext, fundName, $"{fundName} imported {ImportDateTime}", fundGLAccount, null, null, isFundActive.AsBooleanOrNull(), receivedDate, fundName.RemoveSpecialCharacters(), accountTypeValueId: accountTypeDefinedTypeId);
accountList.Add(parentAccount);
}
if (!string.IsNullOrWhiteSpace(subFund))
{
int?campusFundId = null;
// assign a campus if the subfund is a campus fund
var campusFund = CampusList.FirstOrDefault(c => subFund.StartsWith(c.Name, StringComparison.OrdinalIgnoreCase) || subFund.StartsWith(c.ShortCode, StringComparison.OrdinalIgnoreCase));
if (campusFund != null)
{
// use full campus name as the subfund
subFund = campusFund.Name;
campusFundId = campusFund.Id;
}
// add info to easily find/assign this fund in the view
subFund = $"{subFund} {fundName}";
var childAccount = accountList.FirstOrDefault(c => c.ParentAccountId == parentAccount.Id && c.Name.Equals(subFund.Truncate(50), StringComparison.OrdinalIgnoreCase));
if (childAccount == null)
{
// create a child account with a campusId if it was set
childAccount = AddFinancialAccount(lookupContext, subFund, $"{subFund} imported {ImportDateTime}", subFundGLAccount, campusFundId, parentAccount.Id, isFundActive.AsBooleanOrNull(), receivedDate, subFund.RemoveSpecialCharacters(), accountTypeValueId: parentAccount.AccountTypeValueId);
accountList.Add(childAccount);
}
transactionAccountId = childAccount.Id;
}
else
{
transactionAccountId = parentAccount.Id;
}
if (amount == 0 && statedValue.HasValue && statedValue != 0)
{
amount = statedValue;
}
var transactionDetail = new FinancialTransactionDetail
{
Amount = ( decimal )amount,
CreatedDateTime = receivedDate,
AccountId = transactionAccountId
};
transaction.TransactionDetails.Add(transactionDetail);
if (amount < 0)
{
transaction.RefundDetails = new FinancialTransactionRefund();
transaction.RefundDetails.CreatedDateTime = receivedDate;
transaction.RefundDetails.RefundReasonValueId = refundReasons.Where(dv => summary != null && dv.Value.Contains(summary))
.Select(dv => ( int? )dv.Id).FirstOrDefault();
transaction.RefundDetails.RefundReasonSummary = summary;
}
}
newTransactions.Add(transaction);
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, $"{completedItems:N0} contributions imported ({percentComplete}% complete).");
}
if (completedItems % ReportingNumber < 1)
{
SaveContributions(newTransactions);
newTransactions.Clear();
ReportPartialProgress();
}
}
}
if (newTransactions.Any())
{
SaveContributions(newTransactions);
}
ReportProgress(100, $"Finished contribution import: {completedItems:N0} contributions imported.");
}
/// <summary>
/// Maps the 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>
/// Loads the group data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadGroup(CSVInstance csvData)
{
// Required variables
var lookupContext = new RockContext();
var locationService = new LocationService(lookupContext);
var groupTypeService = new GroupTypeService(lookupContext);
var topicTypes = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.SMALL_GROUP_TOPIC), lookupContext).DefinedValues;
var numImportedGroups = ImportedGroups.Count();
var newGroupLocations = new Dictionary <GroupLocation, string>();
var currentGroup = new Group();
// Look for custom attributes in the Individual file
var allFields = csvData.TableNodes.FirstOrDefault().Children.Select((node, index) => new { node = node, index = index }).ToList();
var customAttributes = allFields
.Where(f => f.index > GroupCapacity)
.ToDictionary(f => f.index, f => f.node.Name);
var groupAttributes = new AttributeService(lookupContext).GetByEntityTypeId(new Group().TypeId).ToList();
var completed = 0;
ReportProgress(0, $"Starting group import ({numImportedGroups: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 rowGroupKey = row[GroupId];
//
// Determine if we are still working with the same group or not.
//
if (rowGroupKey != null && rowGroupKey != currentGroup.ForeignKey)
{
currentGroup = LoadGroupBasic(lookupContext, rowGroupKey, row[GroupName], row[GroupCreatedDate], row[GroupType], row[GroupParentGroupId], row[GroupActive], row[GroupDescription]);
//
// Set the group campus
//
var campusName = row[GroupCampus];
if (!string.IsNullOrWhiteSpace(campusName))
{
var groupCampus = CampusList.FirstOrDefault(c => c.Name.Equals(campusName, StringComparison.InvariantCultureIgnoreCase) ||
c.ShortCode.Equals(campusName, StringComparison.InvariantCultureIgnoreCase));
if (groupCampus == null)
{
groupCampus = new Campus
{
IsSystem = false,
Name = campusName,
ShortCode = campusName.RemoveWhitespace(),
IsActive = true
};
lookupContext.Campuses.Add(groupCampus);
lookupContext.SaveChanges(DisableAuditing);
CampusList.Add(groupCampus);
}
currentGroup.CampusId = groupCampus.Id;
}
//
// If the group type has one or more location types defined then import the
// primary address as the first location type.
//
var groupType = groupTypeService.Get(currentGroup.GroupTypeId);
if (groupType.LocationTypes.Count > 0 && (!string.IsNullOrWhiteSpace(row[GroupAddress]) || !string.IsNullOrWhiteSpace(row[GroupNamedLocation])) && currentGroup.GroupLocations.Count == 0)
{
var primaryLocationTypeId = groupType.LocationTypes.ToList()[0].LocationTypeValueId;
var grpAddress = row[GroupAddress];
var grpAddress2 = row[GroupAddress2];
var grpCity = row[GroupCity];
var grpState = row[GroupState];
var grpZip = row[GroupZip];
var grpCountry = row[GroupCountry];
var namedLocation = row[GroupNamedLocation];
if (string.IsNullOrWhiteSpace(namedLocation))
{
var primaryAddress = locationService.Get(grpAddress, grpAddress2, grpCity, grpState, grpZip, grpCountry, verifyLocation: false);
if (primaryAddress != null)
{
var primaryLocation = new GroupLocation
{
LocationId = primaryAddress.Id,
IsMailingLocation = true,
IsMappedLocation = true,
GroupLocationTypeValueId = primaryLocationTypeId
};
newGroupLocations.Add(primaryLocation, rowGroupKey);
}
}
else
{
var primaryAddress = locationService.Queryable().FirstOrDefault(l => l.Name.Equals(namedLocation) || l.ForeignKey.Equals(namedLocation));
if (primaryAddress != null)
{
var primaryLocation = new GroupLocation
{
LocationId = primaryAddress.Id,
IsMailingLocation = true,
IsMappedLocation = true,
GroupLocationTypeValueId = primaryLocationTypeId
};
newGroupLocations.Add(primaryLocation, rowGroupKey);
}
else
{
LogException("Group Import", string.Format("The named location {0} was not found and will not be mapped.", namedLocation));
}
}
}
//
// If the group type has two or more location types defined then import the
// secondary address as the group type's second location type.
//
if (groupType.LocationTypes.Count > 1 && !string.IsNullOrWhiteSpace(row[GroupSecondaryAddress]) && currentGroup.GroupLocations.Count < 2)
{
var secondaryLocationTypeId = groupType.LocationTypes.ToList()[1].LocationTypeValueId;
var grpSecondAddress = row[GroupSecondaryAddress];
var grpSecondAddress2 = row[GroupSecondaryAddress2];
var grpSecondCity = row[GroupSecondaryCity];
var grpSecondState = row[GroupSecondaryState];
var grpSecondZip = row[GroupSecondaryZip];
var grpSecondCountry = row[GroupSecondaryCountry];
var secondaryAddress = locationService.Get(grpSecondAddress, grpSecondAddress2, grpSecondCity, grpSecondState, grpSecondZip, grpSecondCountry, verifyLocation: false);
if (secondaryAddress != null)
{
var secondaryLocation = new GroupLocation
{
LocationId = secondaryAddress.Id,
IsMailingLocation = true,
IsMappedLocation = true,
GroupLocationTypeValueId = secondaryLocationTypeId
};
newGroupLocations.Add(secondaryLocation, rowGroupKey);
}
}
//
// Set the group's sorting order.
//
var groupOrder = 9999;
int.TryParse(row[GroupOrder], out groupOrder);
currentGroup.Order = groupOrder;
//
// Set the group's capacity
//
var capacity = row[GroupCapacity].AsIntegerOrNull();
if (capacity.HasValue)
{
currentGroup.GroupCapacity = capacity;
if (groupType.GroupCapacityRule == GroupCapacityRule.None)
{
groupType.GroupCapacityRule = GroupCapacityRule.Hard;
}
}
//
// Set the group's schedule
//
if (!string.IsNullOrWhiteSpace(row[GroupDayOfWeek]))
{
DayOfWeek dayEnum;
if (Enum.TryParse(row[GroupDayOfWeek], true, out dayEnum))
{
if (groupType.AllowedScheduleTypes != ScheduleType.Weekly)
{
groupType.AllowedScheduleTypes = ScheduleType.Weekly;
}
var day = dayEnum;
var time = row[GroupTime].AsDateTime();
currentGroup.ScheduleId = AddNamedSchedule(lookupContext, string.Empty, string.Empty, day, time, null, rowGroupKey).Id;
}
}
//
// Assign Attributes
//
if (customAttributes.Any())
{
lookupContext.SaveChanges();
foreach (var attributePair in customAttributes)
{
var pairs = attributePair.Value.Split('^');
var categoryName = string.Empty;
var attributeName = string.Empty;
var attributeTypeString = string.Empty;
var attributeForeignKey = string.Empty;
var definedValueForeignKey = string.Empty;
var fieldTypeId = TextFieldTypeId;
if (pairs.Length == 1)
{
attributeName = pairs[0];
}
else if (pairs.Length == 2)
{
attributeName = pairs[0];
attributeTypeString = pairs[1];
}
else if (pairs.Length >= 3)
{
categoryName = pairs[1];
attributeName = pairs[2];
if (pairs.Length >= 4)
{
attributeTypeString = pairs[3];
}
if (pairs.Length >= 5)
{
attributeForeignKey = pairs[4];
}
if (pairs.Length >= 6)
{
definedValueForeignKey = pairs[5];
}
}
var definedValueForeignId = definedValueForeignKey.AsType <int?>();
//
// Translate the provided attribute type into one we know about.
//
fieldTypeId = GetAttributeFieldType(attributeTypeString);
Rock.Model.Attribute currentAttribute = null;
if (string.IsNullOrEmpty(attributeName))
{
LogException("Group Attribute", string.Format("Group Attribute Name cannot be blank '{0}'.", attributePair.Value));
}
else
{
if (string.IsNullOrWhiteSpace(attributeForeignKey))
{
attributeForeignKey = string.Format("Bulldozer_{0}_{1}_{2}", groupType.Id, categoryName.RemoveWhitespace(), attributeName.RemoveWhitespace()).Left(100);
}
currentAttribute = groupAttributes.FirstOrDefault(a =>
a.Name.Equals(attributeName, StringComparison.OrdinalIgnoreCase) &&
a.FieldTypeId == fieldTypeId &&
a.EntityTypeId == currentGroup.TypeId &&
a.EntityTypeQualifierValue == groupType.Id.ToString()
);
if (currentAttribute == null)
{
currentAttribute = AddEntityAttribute(lookupContext, currentGroup.TypeId, "GroupTypeId", groupType.Id.ToString(), attributeForeignKey, categoryName, attributeName, string.Empty, fieldTypeId, true, definedValueForeignId, definedValueForeignKey, attributeTypeString: attributeTypeString);
groupAttributes.Add(currentAttribute);
}
var attributeValue = row[attributePair.Key];
if (!string.IsNullOrEmpty(attributeValue))
{
AddEntityAttributeValue(lookupContext, currentAttribute, currentGroup, row[attributePair.Key], null, true);
}
}
}
}
//
// Changes to groups need to be saved right away since one group
// will reference another group.
//
lookupContext.SaveChanges();
//
// Keep the user informed as to what is going on and save in batches.
//
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, $"{completed:N0} groups imported.");
}
if (completed % ReportingNumber < 1)
{
SaveGroupLocations(newGroupLocations);
ReportPartialProgress();
// Reset lookup context
lookupContext.SaveChanges();
lookupContext = new RockContext();
locationService = new LocationService(lookupContext);
groupTypeService = new GroupTypeService(lookupContext);
newGroupLocations.Clear();
}
}
}
//
// Check to see if any rows didn't get saved to the database
//
if (newGroupLocations.Any())
{
SaveGroupLocations(newGroupLocations);
}
lookupContext.SaveChanges();
lookupContext.Dispose();
ReportProgress(0, $"Finished group import: {completed:N0} groups added or updated.");
return(completed);
}
/// <summary>
/// Maps the pledge.
/// </summary>
/// <param name="queryable">The queryable.</param>
/// <exception cref="System.NotImplementedException"></exception>
private void MapPledge(IQueryable <Row> tableData)
{
var lookupContext = new RockContext();
List <FinancialAccount> accountList = new FinancialAccountService(lookupContext).Queryable().ToList();
List <FinancialPledge> importedPledges = new FinancialPledgeService(lookupContext).Queryable().ToList();
var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues;
var newPledges = new List <FinancialPledge>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying pledge import ({0:N0} found).", totalRows));
foreach (var row in tableData)
{
decimal? amount = row["Total_Pledge"] as decimal?;
DateTime?startDate = row["Start_Date"] as DateTime?;
DateTime?endDate = row["End_Date"] as DateTime?;
if (amount != null && startDate != null && endDate != null)
{
int?individualId = row["Individual_ID"] as int?;
int?householdId = row["Household_ID"] as int?;
int?personId = GetPersonAliasId(individualId, householdId);
if (personId != null && !importedPledges.Any(p => p.PersonAliasId == personId && p.TotalAmount == amount && p.StartDate.Equals(startDate)))
{
var pledge = new FinancialPledge();
pledge.CreatedByPersonAliasId = ImportPersonAlias.Id;
pledge.StartDate = (DateTime)startDate;
pledge.EndDate = (DateTime)endDate;
pledge.TotalAmount = (decimal)amount;
string frequency = row["Pledge_Frequency_Name"].ToString().ToLower();
if (frequency != null)
{
if (frequency == "one time" || frequency == "as can")
{
pledge.PledgeFrequencyValueId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id;
}
else
{
pledge.PledgeFrequencyValueId = pledgeFrequencies
.Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency))
.Select(f => f.Id).FirstOrDefault();
}
}
string fundName = row["Fund_Name"] as string;
string subFund = row["Sub_Fund_Name"] as string;
if (fundName != null)
{
FinancialAccount matchingAccount = null;
int?fundCampusId = null;
if (subFund != null)
{
// match by campus if the subfund appears to be a campus
fundCampusId = CampusList.Where(c => c.Name.StartsWith(subFund) || c.ShortCode == subFund)
.Select(c => (int?)c.Id).FirstOrDefault();
if (fundCampusId != null)
{
matchingAccount = accountList.FirstOrDefault(a => a.Name.StartsWith(fundName) && a.CampusId != null && a.CampusId.Equals(fundCampusId));
}
else
{
matchingAccount = accountList.FirstOrDefault(a => a.Name.StartsWith(fundName) && a.Name.StartsWith(subFund));
}
}
else
{
matchingAccount = accountList.FirstOrDefault(a => a.Name.StartsWith(fundName));
}
if (matchingAccount == null)
{
matchingAccount = AddAccount(lookupContext, fundName, fundCampusId);
accountList.Add(matchingAccount);
}
pledge.AccountId = matchingAccount.Id;
}
// Attributes to add?
// Pledge_Drive_Name
newPledges.Add(pledge);
completed++;
if (completed % percentage < 1)
{
int percentComplete = completed / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} pledges imported ({1}% complete).", completed, percentComplete));
}
else if (completed % ReportingNumber < 1)
{
SavePledges(newPledges);
ReportPartialProgress();
}
}
}
}
if (newPledges.Any())
{
SavePledges(newPledges);
}
ReportProgress(100, string.Format("Finished pledge import: {0:N0} pledges imported.", completed));
}
/// <summary>
/// Maps the activity ministry.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <returns></returns>
private void MapActivityMinistry(IQueryable <Row> tableData)
{
var lookupContext = new RockContext();
// Add an Attribute for the unique F1 Ministry Id
int groupEntityTypeId = EntityTypeCache.Read("Rock.Model.Group").Id;
var ministryAttributeId = new AttributeService(lookupContext).Queryable().Where(a => a.EntityTypeId == groupEntityTypeId &&
a.Key == "F1MinistryId").Select(a => a.Id).FirstOrDefault();
if (ministryAttributeId == 0)
{
var newMinistryAttribute = new Rock.Model.Attribute();
newMinistryAttribute.Key = "F1MinistryId";
newMinistryAttribute.Name = "F1 Ministry Id";
newMinistryAttribute.FieldTypeId = IntegerFieldTypeId;
newMinistryAttribute.EntityTypeId = groupEntityTypeId;
newMinistryAttribute.EntityTypeQualifierValue = string.Empty;
newMinistryAttribute.EntityTypeQualifierColumn = string.Empty;
newMinistryAttribute.Description = "The FellowshipOne identifier for the ministry that was imported";
newMinistryAttribute.DefaultValue = string.Empty;
newMinistryAttribute.IsMultiValue = false;
newMinistryAttribute.IsRequired = false;
newMinistryAttribute.Order = 0;
lookupContext.Attributes.Add(newMinistryAttribute);
lookupContext.SaveChanges(DisableAudit);
ministryAttributeId = newMinistryAttribute.Id;
}
// Get previously imported Ministries
var importedMinistries = new AttributeValueService(lookupContext).GetByAttributeId(ministryAttributeId)
.ToDictionary(t => t.Value.AsType <int?>(), t => t.EntityId);
var newGroups = new List <Group>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying ministry import ({0:N0} found).", totalRows));
foreach (var row in tableData)
{
int?ministryId = row["Ministry_ID"] as int?;
if (ministryId != null && !importedMinistries.ContainsKey(ministryId))
{
string ministryName = row["Ministry_Name"] as string;
bool? ministryIsActive = row["Ministry_Active"] as bool?;
int? activityId = row["Activity_ID"] as int?;
string activityName = row["Activity_Name"] as string;
bool? activityIsActive = row["Activity_Active"] as bool?;
if (ministryName != null)
{
var ministry = new Group();
ministry.Name = ministryName.Trim();
ministry.IsActive = ministryIsActive ?? false;
ministry.CampusId = CampusList.Where(c => ministryName.StartsWith(c.Name) || ministryName.StartsWith(c.ShortCode))
.Select(c => (int?)c.Id).FirstOrDefault();
// create new group for activity with ministry as parent group
newGroups.Add(ministry);
completed++;
if (completed % percentage < 1)
{
int percentComplete = completed / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} ministries imported ({1}% complete).", completed, percentComplete));
}
else if (completed % ReportingNumber < 1)
{
SaveActivityMinistry(newGroups);
ReportPartialProgress();
newGroups.Clear();
}
}
}
}
if (newGroups.Any())
{
SaveActivityMinistry(newGroups);
}
ReportProgress(100, string.Format("Finished ministry import: {0:N0} ministries imported.", completed));
}
/// <summary>
/// Maps the person.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="selectedColumns">The selected columns.</param>
private void MapPerson(IQueryable <Row> tableData, List <string> selectedColumns = null)
{
var lookupContext = new RockContext();
var groupTypeRoleService = new GroupTypeRoleService(lookupContext);
// Marital statuses: Married, Single, Separated, etc
var maritalStatusTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_MARITAL_STATUS), lookupContext).DefinedValues;
// Connection statuses: Member, Visitor, Attendee, etc
var connectionStatusTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_CONNECTION_STATUS), lookupContext).DefinedValues;
// Record status reasons: No Activity, Moved, Deceased, etc
var recordStatusReasons = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_RECORD_STATUS_REASON), lookupContext).DefinedValues;
// Record statuses: Active, Inactive, Pending
int?recordStatusActiveId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE), lookupContext).Id;
int?recordStatusInactiveId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE), lookupContext).Id;
int?recordStatusPendingId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_PENDING), lookupContext).Id;
// Record type: Person
int?personRecordTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON), lookupContext).Id;
// Suffix type: Dr., Jr., II, etc
var suffixTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_SUFFIX)).DefinedValues;
// Title type: Mr., Mrs. Dr., etc
var titleTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_TITLE), lookupContext).DefinedValues;
// Note type: Comment
int noteCommentTypeId = new NoteTypeService(lookupContext).Get(new Guid("7E53487C-D650-4D85-97E2-350EB8332763")).Id;
// Group roles: Owner, Adult, Child, others
GroupTypeRole ownerRole = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_OWNER));
int adultRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT)).Id;
int childRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD)).Id;
int inviteeRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_INVITED)).Id;
int invitedByRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_INVITED_BY)).Id;
int canCheckInRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_CAN_CHECK_IN)).Id;
int allowCheckInByRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_ALLOW_CHECK_IN_BY)).Id;
// Group type: Family
int familyGroupTypeId = new GroupTypeService(lookupContext).Get(new Guid(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY)).Id;
// Look up additional Person attributes (existing)
var personAttributes = new AttributeService(lookupContext).GetByEntityTypeId(PersonEntityTypeId).ToList();
// Cached F1 attributes: IndividualId, HouseholdId
// Core attributes: PreviousChurch, Position, Employer, School
var individualIdAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "F1IndividualId"));
var householdIdAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "F1HouseholdId"));
var previousChurchAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "PreviousChurch"));
var employerAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "Employer"));
var positionAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "Position"));
var firstVisitAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "FirstVisit"));
var schoolAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "School"));
var membershipDateAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "MembershipDate"));
var familyList = new List <Group>();
var visitorList = new List <Group>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying person import ({0:N0} found, {1:N0} already exist).", totalRows, ImportedPeople.Count()));
foreach (var groupedRows in tableData.GroupBy <Row, int?>(r => r["Household_ID"] as int?))
{
var familyGroup = new Group();
var householdCampusList = new List <string>();
foreach (var row in groupedRows)
{
bool isFamilyRelationship = true;
string currentCampus = string.Empty;
int? individualId = row["Individual_ID"] as int?;
int? householdId = row["Household_ID"] as int?;
if (GetPersonAliasId(individualId, householdId) == null)
{
var person = new Person();
person.FirstName = row["First_Name"] as string;
person.MiddleName = row["Middle_Name"] as string;
person.NickName = row["Goes_By"] as string ?? person.FirstName;
person.LastName = row["Last_Name"] as string;
person.BirthDate = row["Date_Of_Birth"] as DateTime?;
person.CreatedByPersonAliasId = ImportPersonAlias.Id;
person.RecordTypeValueId = personRecordTypeId;
person.ForeignId = individualId.ToString();
int groupRoleId = adultRoleId;
var gender = row["Gender"] as string;
if (gender != null)
{
person.Gender = (Gender)Enum.Parse(typeof(Gender), gender);
}
string prefix = row["Prefix"] as string;
if (prefix != null)
{
prefix = prefix.RemoveSpecialCharacters().Trim();
person.TitleValueId = titleTypes.Where(s => prefix == s.Value.RemoveSpecialCharacters())
.Select(s => (int?)s.Id).FirstOrDefault();
}
string suffix = row["Suffix"] as string;
if (suffix != null)
{
suffix = suffix.RemoveSpecialCharacters().Trim();
person.SuffixValueId = suffixTypes.Where(s => suffix == s.Value.RemoveSpecialCharacters())
.Select(s => (int?)s.Id).FirstOrDefault();
}
string maritalStatus = row["Marital_Status"] as string;
if (maritalStatus != null)
{
person.MaritalStatusValueId = maritalStatusTypes.Where(dv => dv.Value == maritalStatus)
.Select(dv => (int?)dv.Id).FirstOrDefault();
}
else
{
person.MaritalStatusValueId = maritalStatusTypes.Where(dv => dv.Value == "Unknown")
.Select(dv => (int?)dv.Id).FirstOrDefault();
}
string familyRole = row["Household_Position"].ToString().ToLower();
if (familyRole != null)
{
if (familyRole == "visitor")
{
isFamilyRelationship = false;
}
if (familyRole == "child" || person.Age < 18)
{
groupRoleId = childRoleId;
}
}
string memberStatus = row["Status_Name"].ToString().ToLower();
if (memberStatus == "member")
{
person.ConnectionStatusValueId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_MEMBER)).Id;
person.RecordStatusValueId = recordStatusActiveId;
}
else if (memberStatus == "visitor")
{
person.ConnectionStatusValueId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_VISITOR)).Id;
person.RecordStatusValueId = recordStatusActiveId;
}
else if (memberStatus == "deceased")
{
person.IsDeceased = true;
person.RecordStatusReasonValueId = recordStatusReasons.Where(dv => dv.Value == "Deceased")
.Select(dv => dv.Id).FirstOrDefault();
person.RecordStatusValueId = recordStatusInactiveId;
}
else
{
// F1 defaults are Member & Visitor; all others are user-defined
var customConnectionType = connectionStatusTypes.Where(dv => dv.Value == memberStatus)
.Select(dv => (int?)dv.Id).FirstOrDefault();
int attendeeId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_ATTENDEE)).Id;
person.ConnectionStatusValueId = customConnectionType ?? attendeeId;
person.RecordStatusValueId = recordStatusActiveId;
}
string campus = row["SubStatus_Name"] as string;
if (campus != null)
{
currentCampus = campus;
}
string status_comment = row["Status_Comment"] as string;
if (status_comment != null)
{
person.SystemNote = status_comment;
}
// Map F1 attributes
person.Attributes = new Dictionary <string, AttributeCache>();
person.AttributeValues = new Dictionary <string, AttributeValue>();
// individual_id already defined in scope
AddPersonAttribute(individualIdAttribute, person, individualId.ToString());
// household_id already defined in scope
AddPersonAttribute(householdIdAttribute, person, householdId.ToString());
string previousChurch = row["Former_Church"] as string;
AddPersonAttribute(previousChurchAttribute, person, previousChurch);
string employer = row["Employer"] as string;
AddPersonAttribute(employerAttribute, person, employer);
string position = row["Occupation_Name"] as string ?? row["Occupation_Description"] as string;
AddPersonAttribute(positionAttribute, person, position);
string school = row["School_Name"] as string;
AddPersonAttribute(schoolAttribute, person, school);
DateTime?membershipDate = row["Status_Date"] as DateTime?;
if (membershipDate != null)
{
person.CreatedDateTime = membershipDate;
AddPersonAttribute(membershipDateAttribute, person, membershipDate.Value.ToString("MM/dd/yyyy"));
}
DateTime?firstVisit = row["First_Record"] as DateTime?;
if (firstVisit != null)
{
person.CreatedDateTime = firstVisit;
AddPersonAttribute(firstVisitAttribute, person, firstVisit.Value.ToString("MM/dd/yyyy"));
}
// Other Attributes to create:
// former name
// bar_code
// member_env_code
// denomination_name
var groupMember = new GroupMember();
groupMember.Person = person;
groupMember.GroupRoleId = groupRoleId;
groupMember.GroupMemberStatus = GroupMemberStatus.Active;
if (isFamilyRelationship)
{
householdCampusList.Add(currentCampus);
familyGroup.Members.Add(groupMember);
familyGroup.ForeignId = householdId.ToString();
}
else
{
var visitorGroup = new Group();
visitorGroup.ForeignId = householdId.ToString();
visitorGroup.Members.Add(groupMember);
visitorGroup.GroupTypeId = familyGroupTypeId;
visitorGroup.Name = person.LastName + " Family";
visitorGroup.CampusId = CampusList.Where(c => c.Name.StartsWith(currentCampus) || c.ShortCode == currentCampus)
.Select(c => (int?)c.Id).FirstOrDefault();
familyList.Add(visitorGroup);
completed += visitorGroup.Members.Count;
visitorList.Add(visitorGroup);
}
}
}
if (familyGroup.Members.Any())
{
familyGroup.Name = familyGroup.Members.OrderByDescending(p => p.Person.Age)
.FirstOrDefault().Person.LastName + " Family";
familyGroup.GroupTypeId = familyGroupTypeId;
string primaryHouseholdCampus = householdCampusList.GroupBy(c => c).OrderByDescending(c => c.Count())
.Select(c => c.Key).FirstOrDefault();
if (primaryHouseholdCampus != null)
{
familyGroup.CampusId = CampusList.Where(c => c.Name.StartsWith(primaryHouseholdCampus) || c.ShortCode == primaryHouseholdCampus)
.Select(c => (int?)c.Id).FirstOrDefault();
}
familyList.Add(familyGroup);
completed += familyGroup.Members.Count;
if (completed % percentage < 1)
{
int percentComplete = completed / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} people imported ({1}% complete).", completed, percentComplete));
}
else if (completed % ReportingNumber < 1)
{
SavePeople(familyList, visitorList, ownerRole, childRoleId, inviteeRoleId, invitedByRoleId, canCheckInRoleId, allowCheckInByRoleId);
familyList.Clear();
visitorList.Clear();
ReportPartialProgress();
}
}
}
// Save any remaining families in the batch
if (familyList.Any())
{
SavePeople(familyList, visitorList, ownerRole, childRoleId, inviteeRoleId, invitedByRoleId, canCheckInRoleId, allowCheckInByRoleId);
}
ReportProgress(100, string.Format("Finished person import: {0:N0} people 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>
/// Loads the family data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadFamily(CsvDataModel csvData)
{
// Required variables
var lookupContext = new RockContext();
var locationService = new LocationService(lookupContext);
int familyGroupTypeId = GroupTypeCache.GetFamilyGroupType().Id;
int numImportedFamilies = ImportedPeople.Select(p => p.ForeignId).Distinct().Count();
int homeLocationTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME)).Id;
int workLocationTypeId = DefinedValueCache.Read(new Guid("E071472A-F805-4FC4-917A-D5E3C095C35C")).Id;
var currentFamilyGroup = new Group();
var newFamilyList = new List <Group>();
var newGroupLocations = new Dictionary <GroupLocation, string>();
string currentFamilyId = string.Empty;
int completed = 0;
ReportProgress(0, string.Format("Starting family import ({0:N0} already exist).", numImportedFamilies));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
string rowFamilyId = row[FamilyId];
string rowFamilyName = row[FamilyName];
if (!string.IsNullOrWhiteSpace(rowFamilyId) && rowFamilyId != currentFamilyGroup.ForeignId)
{
currentFamilyGroup = ImportedPeople.FirstOrDefault(p => p.ForeignId == rowFamilyId);
if (currentFamilyGroup == null)
{
currentFamilyGroup = new Group();
currentFamilyGroup.ForeignId = rowFamilyId;
currentFamilyGroup.Name = row[FamilyName];
currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAlias.Id;
currentFamilyGroup.GroupTypeId = familyGroupTypeId;
newFamilyList.Add(currentFamilyGroup);
}
// Set the family campus
string campusName = row[Campus];
if (!string.IsNullOrWhiteSpace(campusName))
{
var familyCampus = CampusList.Where(c => c.Name.StartsWith(campusName) || c.ShortCode.StartsWith(campusName)).FirstOrDefault();
if (familyCampus == null)
{
familyCampus = new Campus();
familyCampus.IsSystem = false;
familyCampus.Name = campusName;
lookupContext.Campuses.Add(familyCampus);
lookupContext.SaveChanges(true);
}
// This won't assign a campus if the family already exists because the context doesn't get saved
currentFamilyGroup.CampusId = familyCampus.Id;
}
// Add the family addresses since they exist in this file
string famAddress = row[Address];
string famAddress2 = row[Address2];
string famCity = row[City];
string famState = row[State];
string famZip = row[Zip];
string famCountry = row[Country];
// Use the core Rock location service to add or lookup an address
Location primaryAddress = locationService.Get(famAddress, famAddress2, famCity, famState, famZip, famCountry);
if (primaryAddress != null)
{
primaryAddress.Name = currentFamilyGroup.Name + " Home";
var primaryLocation = new GroupLocation();
primaryLocation.LocationId = primaryAddress.Id;
primaryLocation.IsMailingLocation = true;
primaryLocation.IsMappedLocation = true;
primaryLocation.GroupLocationTypeValueId = homeLocationTypeId;
newGroupLocations.Add(primaryLocation, rowFamilyId);
}
string famSecondAddress = row[SecondaryAddress];
string famSecondAddress2 = row[SecondaryAddress2];
string famSecondCity = row[SecondaryCity];
string famSecondState = row[SecondaryState];
string famSecondZip = row[SecondaryZip];
string famSecondCountry = row[SecondaryCountry];
Location secondaryAddress = locationService.Get(famSecondAddress, famSecondAddress2, famSecondCity, famSecondState, famSecondZip, famSecondCountry);
if (secondaryAddress != null)
{
secondaryAddress.Name = currentFamilyGroup.Name + " Work";
var secondaryLocation = new GroupLocation();
secondaryLocation.LocationId = primaryAddress.Id;
secondaryLocation.IsMailingLocation = true;
secondaryLocation.IsMappedLocation = true;
secondaryLocation.GroupLocationTypeValueId = workLocationTypeId;
newGroupLocations.Add(secondaryLocation, rowFamilyId);
}
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} families imported.", completed));
}
else if (completed % ReportingNumber < 1)
{
SaveFamilies(newFamilyList, newGroupLocations);
ReportPartialProgress();
// Reset lookup context
lookupContext = new RockContext();
locationService = new LocationService(lookupContext);
newFamilyList.Clear();
newGroupLocations.Clear();
}
}
}
// Check to see if any rows didn't get saved to the database
if (newGroupLocations.Any())
{
SaveFamilies(newFamilyList, newGroupLocations);
}
ReportProgress(0, string.Format("Finished family import: {0:N0} families imported.", completed));
return(completed);
}
/// <summary>
/// Maps the pledge.
/// </summary>
/// <param name="csvData">todo: describe csvData parameter on MapPledge</param>
/// <returns></returns>
/// <exception cref="System.NotImplementedException"></exception>
private int MapPledge(CSVInstance csvData)
{
var lookupContext = new RockContext();
var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList();
var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking()
.Where(p => p.ForeignId != null)
.ToDictionary(t => (int)t.ForeignId, t => (int?)t.Id);
var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues;
var oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id;
var newPledges = new List <FinancialPledge>();
var completed = 0;
ReportProgress(0, $"Verifying pledge import ({importedPledges.Count:N0} already exist).");
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
var amountKey = row[TotalPledge];
var amount = amountKey.AsType <decimal?>();
var startDateKey = row[StartDate];
if (string.IsNullOrWhiteSpace(startDateKey))
{
startDateKey = "01/01/0001";
}
var startDate = startDateKey.AsType <DateTime?>();
var endDateKey = row[EndDate];
if (string.IsNullOrWhiteSpace(endDateKey))
{
endDateKey = "12/31/9999";
}
var endDate = endDateKey.AsType <DateTime?>();
var createdDateKey = row[PledgeCreatedDate];
if (string.IsNullOrWhiteSpace(createdDateKey))
{
createdDateKey = ImportDateTime.ToString();
}
var createdDate = createdDateKey.AsType <DateTime?>();
var modifiedDateKey = row[PledgeModifiedDate];
if (string.IsNullOrWhiteSpace(modifiedDateKey))
{
modifiedDateKey = ImportDateTime.ToString();
}
var modifiedDate = modifiedDateKey.AsType <DateTime?>();
var pledgeIdKey = row[PledgeId];
var pledgeId = pledgeIdKey.AsType <int?>();
if (amount != null && !importedPledges.ContainsKey((int)pledgeId))
{
var individualIdKey = row[IndividualID];
var personKeys = GetPersonKeys(individualIdKey);
if (personKeys != null && personKeys.PersonAliasId > 0)
{
var pledge = new FinancialPledge
{
PersonAliasId = personKeys.PersonAliasId,
CreatedByPersonAliasId = ImportPersonAliasId,
StartDate = (DateTime)startDate,
EndDate = (DateTime)endDate,
TotalAmount = (decimal)amount,
CreatedDateTime = createdDate,
ModifiedDateTime = modifiedDate,
ModifiedByPersonAliasId = ImportPersonAliasId,
ForeignKey = pledgeIdKey,
ForeignId = pledgeId
};
var frequency = row[PledgeFrequencyName].ToString().ToLower();
if (!string.IsNullOrWhiteSpace(frequency))
{
frequency = frequency.ToLower();
if (frequency.Equals("one time") || frequency.Equals("one-time") || frequency.Equals("as can"))
{
pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId;
}
else
{
pledge.PledgeFrequencyValueId = pledgeFrequencies
.Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency))
.Select(f => f.Id).FirstOrDefault();
}
}
var fundName = row[FundName] as string;
var subFund = row[SubFundName] as string;
var fundGLAccount = row[FundGLAccount] as string;
var subFundGLAccount = row[SubFundGLAccount] as string;
var isFundActiveKey = row[FundIsActive];
var isFundActive = isFundActiveKey.AsType <bool?>();
var isSubFundActiveKey = row[SubFundIsActive];
var isSubFundActive = isSubFundActiveKey.AsType <bool?>();
if (!string.IsNullOrWhiteSpace(fundName))
{
var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName.Truncate(50)));
if (parentAccount == null)
{
parentAccount = AddAccount(lookupContext, fundName, string.Empty, null, null, isFundActive, null, null, null, null, "", "", null);
accountList.Add(parentAccount);
}
if (!string.IsNullOrWhiteSpace(subFund))
{
int?campusFundId = null;
// assign a campus if the subfund is a campus fund
var campusFund = CampusList.FirstOrDefault(c => subFund.Contains(c.Name) || subFund.Contains(c.ShortCode));
if (campusFund != null)
{
campusFundId = campusFund.Id;
}
// add info to easily find/assign this fund in the view
subFund = $"{fundName} {subFund}";
var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund.Truncate(50)) && c.ParentAccountId == parentAccount.Id);
if (childAccount == null)
{
// create a child account with a campusId if it was set
childAccount = AddAccount(lookupContext, subFund, string.Empty, campusFundId, parentAccount.Id, isSubFundActive, null, null, null, null, "", "", null);
accountList.Add(childAccount);
}
pledge.AccountId = childAccount.Id;
}
else
{
pledge.AccountId = parentAccount.Id;
}
}
newPledges.Add(pledge);
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, $"{completed:N0} pledges imported.");
}
else if (completed % ReportingNumber < 1)
{
SavePledges(newPledges);
ReportPartialProgress();
newPledges.Clear();
}
}
}
}
if (newPledges.Any())
{
SavePledges(newPledges);
}
ReportProgress(100, $"Finished pledge import: {completed:N0} pledges imported.");
return(completed);
}
/// <summary>
/// Maps the contribution.
/// </summary>
/// <param name="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 home group membership data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapGroups(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var newGroupMembers = new List <GroupMember>();
var importedGroupMembers = lookupContext.GroupMembers.Count(gm => gm.ForeignKey != null && gm.Group.GroupTypeId == GeneralGroupTypeId);
var groupRoleMember = GroupTypeCache.Get(GeneralGroupTypeId).Roles.FirstOrDefault(r => r.Name.Equals("Member"));
var archivedScheduleName = "Archived Attendance";
var archivedScheduleId = new ScheduleService(lookupContext).Queryable()
.Where(s => s.Name.Equals(archivedScheduleName, StringComparison.OrdinalIgnoreCase))
.Select(s => ( int? )s.Id).FirstOrDefault();
if (!archivedScheduleId.HasValue)
{
var archivedSchedule = AddNamedSchedule(lookupContext, archivedScheduleName, null, null, null,
ImportDateTime, archivedScheduleName.RemoveSpecialCharacters(), true, ImportPersonAliasId);
archivedScheduleId = archivedSchedule.Id;
}
var groupsParentName = "Archived Groups";
var archivedGroups = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(groupsParentName.RemoveWhitespace()));
if (archivedGroups == null)
{
archivedGroups = AddGroup(lookupContext, GeneralGroupTypeId, null, groupsParentName, true, null, ImportDateTime, groupsParentName.RemoveWhitespace(), true, ImportPersonAliasId);
ImportedGroups.Add(archivedGroups);
}
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying people groups import ({0:N0} found, {1:N0} already exist).", totalRows, importedGroupMembers));
foreach (var row in tableData.Where(r => r != null))
{
var groupId = row["Group_ID"] as int?;
var groupName = row["Group_Name"] as string;
var individualId = row["Individual_ID"] as int?;
var groupCreated = row["Created_Date"] as DateTime?;
var groupType = row["Group_Type_Name"] as string;
// require at least a group id and name
if (groupId.HasValue && !string.IsNullOrWhiteSpace(groupName) && !groupName.Equals("Delete", StringComparison.OrdinalIgnoreCase))
{
var peopleGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(groupId.ToString()));
if (peopleGroup == null)
{
int?campusId = null;
var parentGroupId = archivedGroups.Id;
var currentGroupTypeId = GeneralGroupTypeId;
if (!string.IsNullOrWhiteSpace(groupType))
{
// check for a campus on the grouptype
campusId = GetCampusId(groupType, true, SearchDirection.Ends);
if (campusId.HasValue)
{
groupType = StripSuffix(groupType, campusId);
}
// add the grouptype if it doesn't exist
var currentGroupType = ImportedGroupTypes.FirstOrDefault(t => t.ForeignKey.Equals(groupType, StringComparison.OrdinalIgnoreCase));
if (currentGroupType == null)
{
// save immediately so we can use the grouptype for a group
currentGroupType = AddGroupType(lookupContext, groupType, string.Format("{0} imported {1}", groupType, ImportDateTime), null,
null, null, true, true, true, true, typeForeignKey: groupType);
ImportedGroupTypes.Add(currentGroupType);
}
// create a placeholder group for the grouptype if it doesn't exist
var groupTypePlaceholder = ImportedGroups.FirstOrDefault(g => g.GroupTypeId == currentGroupType.Id && g.ForeignKey.Equals(groupType.RemoveWhitespace()));
if (groupTypePlaceholder == null)
{
groupTypePlaceholder = AddGroup(lookupContext, currentGroupType.Id, archivedGroups.Id, groupType, true, null, ImportDateTime,
groupType.RemoveWhitespace(), true, ImportPersonAliasId);
ImportedGroups.Add(groupTypePlaceholder);
}
parentGroupId = groupTypePlaceholder.Id;
currentGroupTypeId = currentGroupType.Id;
}
// put the current group under a campus parent if it exists
campusId = campusId ?? GetCampusId(groupName);
if (campusId.HasValue)
{
// create a campus level parent for the home group
var campus = CampusList.FirstOrDefault(c => c.Id == campusId);
var campusGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(campus.ShortCode) && g.ParentGroupId == parentGroupId);
if (campusGroup == null)
{
campusGroup = AddGroup(lookupContext, currentGroupTypeId, parentGroupId, campus.Name, true, campus.Id, ImportDateTime, campus.ShortCode, true, ImportPersonAliasId);
ImportedGroups.Add(campusGroup);
}
parentGroupId = campusGroup.Id;
}
// add the group, finally
peopleGroup = AddGroup(lookupContext, currentGroupTypeId, parentGroupId, groupName, true, campusId, null, groupId.ToString(), true, ImportPersonAliasId, archivedScheduleId);
ImportedGroups.Add(peopleGroup);
}
// add the group member
var personKeys = GetPersonKeys(individualId, null);
if (personKeys != null)
{
newGroupMembers.Add(new GroupMember
{
IsSystem = false,
GroupId = peopleGroup.Id,
PersonId = personKeys.PersonId,
GroupRoleId = groupRoleMember.Id,
GroupMemberStatus = GroupMemberStatus.Active,
ForeignKey = string.Format("Membership imported {0}", ImportDateTime)
});
completedItems++;
}
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} group members imported ({1}% complete).", completedItems, percentComplete));
}
if (completedItems % ReportingNumber < 1)
{
SaveGroupMembers(newGroupMembers);
ReportPartialProgress();
// Reset lists and context
lookupContext = new RockContext();
newGroupMembers.Clear();
}
}
}
if (newGroupMembers.Any())
{
SaveGroupMembers(newGroupMembers);
}
lookupContext.Dispose();
ReportProgress(100, string.Format("Finished people groups import: {0:N0} members imported.", completedItems));
}
/// <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 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 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 pledge.
/// </summary>
/// <param name="queryable">The queryable.</param>
/// <exception cref="System.NotImplementedException"></exception>
private void MapPledge(IQueryable <Row> tableData)
{
var lookupContext = new RockContext();
var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList();
var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking().ToList();
var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues;
int oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id;
var newPledges = new List <FinancialPledge>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying pledge import ({0:N0} found).", totalRows));
foreach (var row in tableData.Where(r => r != null))
{
decimal? amount = row["Total_Pledge"] as decimal?;
DateTime?startDate = row["Start_Date"] as DateTime?;
DateTime?endDate = row["End_Date"] as DateTime?;
if (amount != null && startDate != null && endDate != null)
{
int?individualId = row["Individual_ID"] as int?;
int?householdId = row["Household_ID"] as int?;
var personKeys = GetPersonKeys(individualId, householdId, includeVisitors: false);
if (personKeys != null && personKeys.PersonAliasId > 0)
{
var pledge = new FinancialPledge();
pledge.PersonAliasId = personKeys.PersonAliasId;
pledge.CreatedByPersonAliasId = ImportPersonAliasId;
pledge.ModifiedDateTime = ImportDateTime;
pledge.StartDate = (DateTime)startDate;
pledge.EndDate = (DateTime)endDate;
pledge.TotalAmount = (decimal)amount;
string frequency = row["Pledge_Frequency_Name"].ToString().ToLower();
if (frequency != null)
{
frequency = frequency.ToLower();
if (frequency.Equals("one time") || frequency.Equals("as can"))
{
pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId;
}
else
{
pledge.PledgeFrequencyValueId = pledgeFrequencies
.Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency))
.Select(f => f.Id).FirstOrDefault();
}
}
string fundName = row["Fund_Name"] as string;
string subFund = row["Sub_Fund_Name"] as string;
if (fundName != null)
{
var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null);
if (parentAccount == null)
{
parentAccount = AddAccount(lookupContext, fundName, string.Empty, null, null, null);
accountList.Add(parentAccount);
}
if (subFund != null)
{
int?campusFundId = null;
// assign a campus if the subfund is a campus fund
var campusFund = CampusList.FirstOrDefault(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode));
if (campusFund != null)
{
// use full campus name as the subfund
subFund = campusFund.Name;
campusFundId = campusFund.Id;
}
// add info to easily find/assign this fund in the view
subFund = string.Format("{0} {1}", subFund, fundName);
var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund) && c.ParentAccountId == parentAccount.Id);
if (childAccount == null)
{
// create a child account with a campusId if it was set
childAccount = AddAccount(lookupContext, subFund, string.Empty, campusFundId, parentAccount.Id, null);
accountList.Add(childAccount);
}
pledge.AccountId = childAccount.Id;
}
else
{
pledge.AccountId = parentAccount.Id;
}
}
newPledges.Add(pledge);
completed++;
if (completed % percentage < 1)
{
int percentComplete = completed / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} pledges imported ({1}% complete).", completed, percentComplete));
}
else if (completed % ReportingNumber < 1)
{
SavePledges(newPledges);
ReportPartialProgress();
newPledges.Clear();
}
}
}
}
if (newPledges.Any())
{
SavePledges(newPledges);
}
ReportProgress(100, string.Format("Finished pledge import: {0:N0} pledges imported.", completed));
}
/// <summary>
/// Maps the pledge.
/// </summary>
/// <param name="queryable">The queryable.</param>
/// <exception cref="System.NotImplementedException"></exception>
private int MapPledge(CSVInstance csvData)
{
var lookupContext = new RockContext();
var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList();
var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking().ToList();
var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues;
int oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id;
var newPledges = new List <FinancialPledge>();
int completed = 0;
ReportProgress(0, string.Format("Verifying pledge import ({0:N0} already exist).", importedPledges.Count));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
string amountKey = row[TotalPledge];
decimal? amount = amountKey.AsType <decimal?>();
string startDateKey = row[StartDate];
DateTime?startDate = startDateKey.AsType <DateTime?>();
string endDateKey = row[EndDate];
DateTime?endDate = endDateKey.AsType <DateTime?>();
if (amount != null && startDate != null && endDate != null)
{
string individualIdKey = row[IndividualID];
int? individualId = individualIdKey.AsType <int?>();
var personKeys = GetPersonKeys(individualId);
if (personKeys != null && personKeys.PersonAliasId > 0)
{
var pledge = new FinancialPledge();
pledge.PersonAliasId = personKeys.PersonAliasId;
pledge.CreatedByPersonAliasId = ImportPersonAliasId;
pledge.StartDate = (DateTime)startDate;
pledge.EndDate = (DateTime)endDate;
pledge.TotalAmount = (decimal)amount;
string frequency = row[PledgeFrequencyName].ToString().ToLower();
if (frequency != null)
{
frequency = frequency.ToLower();
if (frequency.Equals("one time") || frequency.Equals("as can"))
{
pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId;
}
else
{
pledge.PledgeFrequencyValueId = pledgeFrequencies
.Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency))
.Select(f => f.Id).FirstOrDefault();
}
}
string fundName = row[FundName] as string;
string subFund = row[SubFundName] as string;
string fundGLAccount = row[FundGLAccount] as string;
string subFundGLAccount = row[SubFundGLAccount] as string;
string isFundActiveKey = row[FundIsActive];
Boolean?isFundActive = isFundActiveKey.AsType <Boolean?>();
if (fundName != null)
{
var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null);
if (parentAccount == null)
{
parentAccount = AddAccount(lookupContext, fundName, string.Empty, null, null, isFundActive);
accountList.Add(parentAccount);
}
if (subFund != null)
{
int?campusFundId = null;
// assign a campus if the subfund is a campus fund
var campusFund = CampusList.FirstOrDefault(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode));
if (campusFund != null)
{
// use full campus name as the subfund
subFund = campusFund.Name;
campusFundId = campusFund.Id;
}
// add info to easily find/assign this fund in the view
subFund = string.Format("{0} {1}", subFund, fundName);
var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund) && c.ParentAccountId == parentAccount.Id);
if (childAccount == null)
{
// create a child account with a campusId if it was set
childAccount = AddAccount(lookupContext, subFund, string.Empty, campusFundId, parentAccount.Id, isFundActive);
accountList.Add(childAccount);
}
pledge.AccountId = childAccount.Id;
}
else
{
pledge.AccountId = parentAccount.Id;
}
}
newPledges.Add(pledge);
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} pledges imported.", completed));
}
else if (completed % ReportingNumber < 1)
{
SavePledges(newPledges);
ReportPartialProgress();
newPledges.Clear();
}
}
}
}
if (newPledges.Any())
{
SavePledges(newPledges);
}
ReportProgress(100, string.Format("Finished pledge import: {0:N0} pledges imported.", completed));
return(completed);
}
/// <summary>
/// Loads the polygon group data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadGroupPolygon(CSVInstance csvData)
{
// Required variables
var lookupContext = new RockContext();
var numImportedGroups = ImportedGroups.Count();
var newGroupLocations = new Dictionary <GroupLocation, string>();
var currentGroup = new Group();
var coordinateString = string.Empty;
var startCoordinate = string.Empty;
var endCoordinate = string.Empty;
var geographicAreaTypeId = DefinedValueCache.Get("44990C3F-C45B-EDA3-4B65-A238A581A26F").Id;
var completed = 0;
ReportProgress(0, $"Starting polygon group import ({numImportedGroups: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 rowGroupKey = row[GroupId];
var rowGroupId = rowGroupKey.AsType <int?>();
var rowLat = row[Latitude];
var rowLong = row[Longitude];
//
// Determine if we are still working with the same group or not.
//
if (!string.IsNullOrWhiteSpace(rowGroupKey) && rowGroupKey != currentGroup.ForeignKey)
{
if (!string.IsNullOrWhiteSpace(coordinateString))
{
if (startCoordinate != endCoordinate)
{
coordinateString = $"{coordinateString}|{startCoordinate}";
}
var coords = coordinateString.Split('|');
if (coords.Length > 3)
{
var polygon = CreatePolygonLocation(coordinateString, row[GroupCreatedDate], rowGroupKey, rowGroupId);
if (polygon != null)
{
var geographicArea = new GroupLocation
{
LocationId = polygon.Id,
IsMailingLocation = true,
IsMappedLocation = true,
GroupLocationTypeValueId = geographicAreaTypeId,
GroupId = currentGroup.Id
};
newGroupLocations.Add(geographicArea, currentGroup.ForeignKey);
}
}
}
currentGroup = LoadGroupBasic(lookupContext, rowGroupKey, row[GroupName], row[GroupCreatedDate], row[GroupType], row[GroupParentGroupId], row[GroupActive]);
// reset coordinateString
coordinateString = string.Empty;
if (!string.IsNullOrWhiteSpace(rowLat) && !string.IsNullOrWhiteSpace(rowLong) && rowLat.AsType <double>() != 0 && rowLong.AsType <double>() != 0)
{
coordinateString = $"{rowLat},{rowLong}";
startCoordinate = $"{rowLat},{rowLong}";
}
//
// Set the group campus
//
var campusName = row[GroupCampus];
if (!string.IsNullOrWhiteSpace(campusName))
{
var groupCampus = CampusList.FirstOrDefault(c => c.Name.Equals(campusName, StringComparison.InvariantCultureIgnoreCase) ||
c.ShortCode.Equals(campusName, StringComparison.InvariantCultureIgnoreCase));
if (groupCampus == null)
{
groupCampus = new Campus
{
IsSystem = false,
Name = campusName,
ShortCode = campusName.RemoveWhitespace(),
IsActive = true
};
lookupContext.Campuses.Add(groupCampus);
lookupContext.SaveChanges(DisableAuditing);
CampusList.Add(groupCampus);
}
currentGroup.CampusId = groupCampus.Id;
}
//
// Set the group's sorting order.
//
var groupOrder = 9999;
int.TryParse(row[GroupOrder], out groupOrder);
currentGroup.Order = groupOrder;
//
// Changes to groups need to be saved right away since one group
// will reference another group.
//
lookupContext.SaveChanges();
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, $"{completed:N0} groups imported.");
}
if (completed % ReportingNumber < 1)
{
ReportPartialProgress();
}
}
else if (rowGroupKey == currentGroup.ForeignKey && (!string.IsNullOrWhiteSpace(rowLat) && !string.IsNullOrWhiteSpace(rowLong) && rowLat.AsType <double>() != 0 && rowLong.AsType <double>() != 0))
{
coordinateString = $"{coordinateString}|{rowLat},{rowLong}";
endCoordinate = $"{rowLat},{rowLong}";
}
}
if (!string.IsNullOrWhiteSpace(coordinateString))
{
if (startCoordinate != endCoordinate)
{
coordinateString = coordinateString + $"|{startCoordinate}";
}
var coords = coordinateString.Split('|');
if (coords.Length > 3)
{
var polygon = CreatePolygonLocation(coordinateString, currentGroup.CreatedDateTime.ToString(), currentGroup.ForeignKey, currentGroup.ForeignId);
if (polygon != null)
{
var geographicArea = new GroupLocation
{
LocationId = polygon.Id,
IsMailingLocation = true,
IsMappedLocation = true,
GroupLocationTypeValueId = geographicAreaTypeId,
GroupId = currentGroup.Id
};
newGroupLocations.Add(geographicArea, currentGroup.ForeignKey);
}
}
}
//
// Save rows to the database
//
ReportProgress(0, $"Saving {newGroupLocations.Count} polygons.");
if (newGroupLocations.Any())
{
SaveGroupLocations(newGroupLocations);
}
lookupContext.SaveChanges();
DetachAllInContext(lookupContext);
lookupContext.Dispose();
ReportProgress(0, $"Finished polygon group import: {completed:N0} groups added or updated.");
return(completed);
}
/// <summary>
/// Maps the people attributes to date/text attributes.
/// Also converts attribute comments to person notes.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapAttribute(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var personService = new PersonService(lookupContext);
var personAttributes = new AttributeService(lookupContext).GetByEntityTypeId(PersonEntityTypeId).Include("Categories").AsNoTracking().ToList();
var importedAttributeCount = lookupContext.AttributeValues.Count(v => v.Attribute.EntityTypeId == PersonEntityTypeId && v.ForeignKey != null);
var baptizedHereAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals("BaptizedHere", StringComparison.InvariantCultureIgnoreCase));
var newBenevolences = new List <BenevolenceRequest>();
var peopleToUpdate = new Dictionary <int, Person>();
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, $"Verifying attribute import ({totalRows:N0} found, {importedAttributeCount:N0} already exist).");
foreach (var row in tableData.OrderBy(r => r["Attribute_Name"]).ThenByDescending(r => r["Start_Date"] != null).ThenBy(r => r["Start_Date"]).ThenBy(r => r["End_Date"]).Where(r => r != null))
{
// add the new attribute
var attributeGroupName = row["Attribute_Group_Name"] as string;
var attributeName = row["Attribute_Name"] as string;
var attributeDate = row["Start_Date"] as DateTime?;
attributeDate = attributeDate ?? row["End_Date"] as DateTime?;
var attributeComment = row["Comment"] as string;
var attributeCreator = row["Staff_Individual_ID"] as int?;
int?campusId = null;
if (attributeGroupName.IsNullOrWhiteSpace() && attributeName.IsNullOrWhiteSpace())
{
continue;
}
// strip attribute group name (will become a category)
if (attributeGroupName.Any(n => ValidDelimiters.Contains(n)))
{
campusId = campusId ?? GetCampusId(attributeGroupName);
if (campusId.HasValue)
{
attributeGroupName = StripPrefix(attributeGroupName, campusId);
}
}
// strip attribute name
if (attributeName.Any(n => ValidDelimiters.Contains(n)))
{
campusId = campusId ?? GetCampusId(attributeName);
if (campusId.HasValue)
{
attributeName = StripPrefix(attributeName, campusId);
}
}
var personBaptizedHere = false;
var isBenevolenceAttribute = false;
if (attributeName.StartsWith("Baptism", StringComparison.CurrentCultureIgnoreCase))
{ // match the core Baptism attribute
attributeName = "Baptism Date";
personBaptizedHere = attributeCreator.HasValue;
}
else if (attributeName.StartsWith("Benevolence", StringComparison.CurrentCultureIgnoreCase))
{ // set a flag to create benevolence items
isBenevolenceAttribute = true;
attributeName = attributeName.Replace("Benevolence", string.Empty).Trim();
}
else if (string.IsNullOrWhiteSpace(attributeName))
{
attributeName = attributeGroupName;
}
Attribute primaryAttribute = null, campusAttribute = null;
// don't create custom attributes for benevolence items
if (!isBenevolenceAttribute)
{
// create attributes if they don't exist
var attributeKey = attributeName.RemoveSpecialCharacters();
primaryAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals(attributeKey, StringComparison.CurrentCultureIgnoreCase));
if (primaryAttribute == null)
{
primaryAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, $"{attributeKey} imported {ImportDateTime}",
attributeGroupName, attributeName, attributeKey, attributeDate.HasValue ? DateFieldTypeId : TextFieldTypeId, importPersonAliasId: ImportPersonAliasId
);
personAttributes.Add(primaryAttribute);
}
// attribute already exists, add the new category
else if (!primaryAttribute.Categories.Any(c => c.Name.Equals(attributeGroupName)))
{
var attributeCategory = GetCategory(lookupContext, AttributeEntityTypeId, null, attributeGroupName, false, "EntityTypeId", PersonEntityTypeId.ToString());
primaryAttribute.Categories.Add(attributeCategory);
}
// only create a campus attribute if there was a campus prefix
campusAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals($"{attributeKey}Campus", StringComparison.CurrentCultureIgnoreCase));
if (campusAttribute == null && campusId.HasValue)
{
campusAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, $"{attributeKey}Campus imported {ImportDateTime}",
attributeGroupName, $"{attributeName} Campus", $"{attributeKey}Campus", CampusFieldTypeId
);
personAttributes.Add(campusAttribute);
}
}
// make sure we have a valid person to assign to
var individualId = row["Individual_Id"] as int?;
var matchingPerson = GetPersonKeys(individualId, null, includeVisitors: false);
if (matchingPerson != null)
{
var person = !peopleToUpdate.ContainsKey(matchingPerson.PersonId)
? personService.Queryable(includeDeceased: true).FirstOrDefault(p => p.Id == matchingPerson.PersonId)
: peopleToUpdate[matchingPerson.PersonId];
if (person != null)
{
int?creatorAliasId = null;
var noteCreator = GetPersonKeys(attributeCreator);
if (noteCreator != null)
{
creatorAliasId = noteCreator.PersonAliasId;
}
if (!isBenevolenceAttribute)
{
// could have multiple attributes assigned to this person, don't overwrite previous
if (person.Attributes == null || person.AttributeValues == null)
{
person.Attributes = new Dictionary <string, AttributeCache>();
person.AttributeValues = new Dictionary <string, AttributeValueCache>();
}
var attributeValue = attributeDate.HasValue ? attributeDate.Value.ToString("yyyy-MM-dd") : attributeComment;
if (string.IsNullOrWhiteSpace(attributeValue))
{
// add today's date so that the attribute at least gets a value
attributeValue = RockDateTime.Now.ToString("yyyy-MM-dd");
}
AddEntityAttributeValue(lookupContext, primaryAttribute, person, attributeValue);
if (personBaptizedHere)
{
AddEntityAttributeValue(lookupContext, baptizedHereAttribute, person, "Yes");
}
// Add the campus attribute value
if (campusAttribute != null && campusId.HasValue)
{
var campus = CampusList.FirstOrDefault(c => c.Id.Equals(campusId));
AddEntityAttributeValue(lookupContext, campusAttribute, person, campus.Guid.ToString());
}
// convert the attribute comment to a person note
if (!string.IsNullOrWhiteSpace(attributeComment))
{
// add the note to the person
AddEntityNote(lookupContext, PersonEntityTypeId, person.Id, attributeName, attributeComment, false, false,
attributeGroupName, null, true, attributeDate, $"Imported {ImportDateTime}", creatorAliasId);
}
}
// benevolences require a date
else if (attributeDate.HasValue)
{
var requestText = !string.IsNullOrWhiteSpace(attributeComment) ? attributeComment : "N/A";
var benevolence = new BenevolenceRequest
{
CampusId = campusId,
RequestDateTime = attributeDate.Value,
FirstName = person.FirstName,
LastName = person.LastName,
Email = person.Email,
RequestedByPersonAliasId = person.PrimaryAliasId,
ConnectionStatusValueId = person.ConnectionStatusValueId,
CaseWorkerPersonAliasId = creatorAliasId,
RequestStatusValueId = ParseBenevolenceStatus(attributeName),
RequestText = requestText,
CreatedDateTime = attributeDate.Value,
ModifiedDateTime = attributeDate.Value,
CreatedByPersonAliasId = creatorAliasId,
ModifiedByPersonAliasId = ImportPersonAliasId,
ForeignKey = $"Benevolence imported {ImportDateTime}"
};
newBenevolences.Add(benevolence);
}
// store the person lookup for this batch
if (!peopleToUpdate.ContainsKey(matchingPerson.PersonId))
{
peopleToUpdate.Add(matchingPerson.PersonId, person);
}
else
{
peopleToUpdate[matchingPerson.PersonId] = person;
}
}
}
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, $"{completedItems:N0} attributes imported ({percentComplete}% complete).");
}
if (completedItems % ReportingNumber < 1)
{
SaveAttributes(peopleToUpdate);
SaveBenevolenceRequests(newBenevolences);
// reset so context doesn't bloat
lookupContext.Dispose();
lookupContext = new RockContext();
personService = new PersonService(lookupContext);
peopleToUpdate.Clear();
newBenevolences.Clear();
ReportPartialProgress();
}
}
SaveAttributes(peopleToUpdate);
SaveBenevolenceRequests(newBenevolences);
ReportProgress(100, $"Finished attribute import: {completedItems:N0} attributes imported.");
}
/// <summary>
/// Loads the family data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadFamily(CSVInstance csvData)
{
// Required variables
var lookupContext = new RockContext();
var locationService = new LocationService(lookupContext);
int familyGroupTypeId = GroupTypeCache.GetFamilyGroupType().Id;
int numImportedFamilies = ImportedFamilies.Count();
int homeLocationTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME)).Id;
int workLocationTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_WORK)).Id;
var newGroupLocations = new Dictionary <GroupLocation, string>();
var currentFamilyGroup = new Group();
var newFamilyList = new List <Group>();
var updatedFamilyList = new List <Group>();
var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" };
string currentFamilyKey = string.Empty;
int completed = 0;
ReportProgress(0, string.Format("Starting family import ({0:N0} already exist).", numImportedFamilies));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
string rowFamilyKey = row[FamilyId];
int? rowFamilyId = rowFamilyKey.AsType <int?>();
string rowFamilyName = row[FamilyName];
if (rowFamilyKey != null && rowFamilyKey != currentFamilyGroup.ForeignKey)
{
currentFamilyGroup = ImportedFamilies.FirstOrDefault(g => g.ForeignKey == rowFamilyKey);
if (currentFamilyGroup == null)
{
currentFamilyGroup = new Group();
currentFamilyGroup.ForeignKey = rowFamilyKey;
currentFamilyGroup.ForeignId = rowFamilyId;
currentFamilyGroup.Name = row[FamilyName];
currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAliasId;
currentFamilyGroup.GroupTypeId = familyGroupTypeId;
newFamilyList.Add(currentFamilyGroup);
}
else
{
lookupContext.Groups.Attach(currentFamilyGroup);
}
// Set the family campus
string campusName = row[Campus];
if (!string.IsNullOrWhiteSpace(campusName))
{
var familyCampus = CampusList.Where(c => c.Name.Equals(campusName, StringComparison.InvariantCultureIgnoreCase) ||
c.ShortCode.Equals(campusName, StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault();
if (familyCampus == null)
{
familyCampus = new Campus();
familyCampus.IsSystem = false;
familyCampus.Name = campusName;
familyCampus.ShortCode = campusName.RemoveWhitespace();
lookupContext.Campuses.Add(familyCampus);
lookupContext.SaveChanges(DisableAuditing);
CampusList.Add(familyCampus);
}
currentFamilyGroup.CampusId = familyCampus.Id;
}
// Add the family addresses since they exist in this file
string famAddress = row[Address];
string famAddress2 = row[Address2];
string famCity = row[City];
string famState = row[State];
string famZip = row[Zip];
string famCountry = row[Country];
Location primaryAddress = locationService.Get(famAddress, famAddress2, famCity, famState, famZip, famCountry, verifyLocation: false);
if (primaryAddress != null)
{
var primaryLocation = new GroupLocation();
primaryLocation.LocationId = primaryAddress.Id;
primaryLocation.IsMailingLocation = true;
primaryLocation.IsMappedLocation = true;
primaryLocation.GroupLocationTypeValueId = homeLocationTypeId;
newGroupLocations.Add(primaryLocation, rowFamilyKey);
}
string famSecondAddress = row[SecondaryAddress];
string famSecondAddress2 = row[SecondaryAddress2];
string famSecondCity = row[SecondaryCity];
string famSecondState = row[SecondaryState];
string famSecondZip = row[SecondaryZip];
string famSecondCountry = row[SecondaryCountry];
Location secondaryAddress = locationService.Get(famSecondAddress, famSecondAddress2, famSecondCity, famSecondState, famSecondZip, famSecondCountry, verifyLocation: false);
if (secondaryAddress != null)
{
var secondaryLocation = new GroupLocation();
secondaryLocation.LocationId = secondaryAddress.Id;
secondaryLocation.IsMailingLocation = true;
secondaryLocation.IsMappedLocation = true;
secondaryLocation.GroupLocationTypeValueId = workLocationTypeId;
newGroupLocations.Add(secondaryLocation, rowFamilyKey);
}
DateTime createdDateValue;
if (DateTime.TryParseExact(row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue))
{
currentFamilyGroup.CreatedDateTime = createdDateValue;
currentFamilyGroup.ModifiedDateTime = ImportDateTime;
}
else
{
currentFamilyGroup.CreatedDateTime = ImportDateTime;
currentFamilyGroup.ModifiedDateTime = ImportDateTime;
}
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} families imported.", completed));
}
else if (completed % ReportingNumber < 1)
{
SaveFamilies(newFamilyList, newGroupLocations);
ReportPartialProgress();
// Reset lookup context
lookupContext.SaveChanges();
lookupContext = new RockContext();
locationService = new LocationService(lookupContext);
newFamilyList.Clear();
newGroupLocations.Clear();
}
}
}
// Check to see if any rows didn't get saved to the database
if (newGroupLocations.Any())
{
SaveFamilies(newFamilyList, newGroupLocations);
}
lookupContext.SaveChanges();
DetachAllInContext(lookupContext);
lookupContext.Dispose();
ReportProgress(0, string.Format("Finished family import: {0:N0} families added or updated.", completed));
return(completed);
}
/// <summary>
/// Maps the headcount metrics.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private int MapMetrics(IQueryable <Row> tableData, long totalRows = 0)
{
// Required variables
var lookupContext = new RockContext();
var metricService = new MetricService(lookupContext);
var metricCategoryService = new MetricCategoryService(lookupContext);
var categoryService = new CategoryService(lookupContext);
var metricSourceTypes = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.METRIC_SOURCE_TYPE)).DefinedValues;
var metricManualSource = metricSourceTypes.FirstOrDefault(m => m.Guid == new Guid(Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_MANUAL));
var archivedScheduleCategory = GetCategory(lookupContext, ScheduleEntityTypeId, null, "Archived Schedules");
var scheduleService = new ScheduleService(lookupContext);
var scheduleMetrics = scheduleService.Queryable().AsNoTracking().Where(s => s.Category.Guid == archivedScheduleCategory.Guid).ToList();
var allMetrics = metricService.Queryable().AsNoTracking().ToList();
var metricCategories = categoryService.Queryable().AsNoTracking()
.Where(c => c.EntityType.Guid == new Guid(Rock.SystemGuid.EntityType.METRICCATEGORY)).ToList();
var defaultCategoryName = "Metrics";
var defaultCategory = metricCategories.FirstOrDefault(c => c.Name == defaultCategoryName);
if (defaultCategory == null)
{
defaultCategory = GetCategory(lookupContext, MetricCategoryEntityTypeId, null, defaultCategoryName);
defaultCategory.ForeignKey = string.Format("Category imported {0}", ImportDateTime);
metricCategories.Add(defaultCategory);
}
var metricValues = new List <MetricValue>();
Metric currentMetric = null;
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Starting metrics import ({0:N0} already exist).", 0));
foreach (var row in tableData.Where(r => r != null))
{
var foreignId = row["Headcount_ID"] as int?;
var activityId = row["Activity_ID"] as int?;
var rlcId = row["RLC_ID"] as int?;
var metricName = row["RLC_name"] as string;
var valueDate = row["Start_Date_Time"] as DateTime?;
var value = row["Attendance"] as string;
var metricNote = row["Meeting_note"] as string;
int?metricCampusId = null;
if (!string.IsNullOrEmpty(metricName) && !string.IsNullOrWhiteSpace(value))
{
var categoryName = string.Empty;
var metricCategoryId = defaultCategory.Id;
if (activityId.HasValue)
{
var activityGroup = ImportedGroups.FirstOrDefault(g => g.ForeignId == activityId);
if (activityGroup != null && !string.IsNullOrWhiteSpace(activityGroup.Name))
{
metricCampusId = metricCampusId ?? GetCampusId(activityGroup.Name);
var activityCategory = metricCategories.FirstOrDefault(c => c.Name == activityGroup.Name && c.ParentCategoryId == metricCategoryId);
if (activityCategory == null)
{
activityCategory = GetCategory(lookupContext, MetricCategoryEntityTypeId, metricCategoryId, activityGroup.Name);
activityCategory.ForeignKey = string.Format("Category imported {0}", ImportDateTime);
metricCategories.Add(activityCategory);
}
metricCategoryId = activityCategory.Id;
}
}
if (rlcId.HasValue)
{
var rlcGroup = ImportedGroups.FirstOrDefault(g => g.ForeignId == rlcId);
if (rlcGroup != null && !string.IsNullOrWhiteSpace(rlcGroup.Name))
{
metricCampusId = metricCampusId ?? GetCampusId(rlcGroup.Name);
var rlcCategory = metricCategories.FirstOrDefault(c => c.Name == rlcGroup.Name && c.ParentCategoryId == metricCategoryId);
if (rlcCategory == null)
{
rlcCategory = GetCategory(lookupContext, MetricCategoryEntityTypeId, metricCategoryId, rlcGroup.Name);
rlcCategory.ForeignKey = string.Format("Category imported {0}", ImportDateTime);
metricCategories.Add(rlcCategory);
}
metricCategoryId = rlcCategory.Id;
}
}
// create metric if it doesn't exist
currentMetric = allMetrics.FirstOrDefault(m => m.Title == metricName && m.MetricCategories.Any(c => c.CategoryId == metricCategoryId));
if (currentMetric == null)
{
currentMetric = new Metric();
currentMetric.Title = metricName;
currentMetric.IsSystem = false;
currentMetric.IsCumulative = false;
currentMetric.SourceSql = string.Empty;
currentMetric.Subtitle = string.Empty;
currentMetric.Description = string.Empty;
currentMetric.IconCssClass = string.Empty;
currentMetric.SourceValueTypeId = metricManualSource.Id;
currentMetric.CreatedByPersonAliasId = ImportPersonAliasId;
currentMetric.CreatedDateTime = ImportDateTime;
currentMetric.ForeignId = foreignId;
currentMetric.ForeignKey = foreignId.ToStringSafe();
currentMetric.MetricPartitions = new List <MetricPartition>();
currentMetric.MetricPartitions.Add(new MetricPartition
{
Label = "Campus",
Metric = currentMetric,
EntityTypeId = CampusEntityTypeId,
EntityTypeQualifierColumn = string.Empty,
EntityTypeQualifierValue = string.Empty
});
currentMetric.MetricPartitions.Add(new MetricPartition
{
Label = "Service",
Metric = currentMetric,
EntityTypeId = ScheduleEntityTypeId,
EntityTypeQualifierColumn = string.Empty,
EntityTypeQualifierValue = string.Empty
});
metricService.Add(currentMetric);
lookupContext.SaveChanges();
if (currentMetric.MetricCategories == null || !currentMetric.MetricCategories.Any(a => a.CategoryId == metricCategoryId))
{
metricCategoryService.Add(new MetricCategory {
CategoryId = metricCategoryId, MetricId = currentMetric.Id
});
lookupContext.SaveChanges();
}
allMetrics.Add(currentMetric);
}
// create values for this metric
var metricValue = new MetricValue();
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.CreatedByPersonAliasId = ImportPersonAliasId;
metricValue.CreatedDateTime = ImportDateTime;
metricValue.MetricValueDateTime = valueDate;
metricValue.MetricId = currentMetric.Id;
metricValue.XValue = string.Empty;
metricValue.YValue = value.AsDecimalOrNull();
metricValue.ForeignKey = string.Format("Metric Value imported {0}", ImportDateTime);
metricValue.Note = metricNote ?? string.Empty;
if (valueDate.HasValue)
{
var metricPartitionScheduleId = currentMetric.MetricPartitions.FirstOrDefault(p => p.Label == "Service").Id;
var date = ( DateTime )valueDate;
var scheduleName = date.DayOfWeek.ToString();
if (date.TimeOfDay.TotalSeconds > 0)
{
scheduleName = scheduleName + string.Format(" {0}", date.ToString("hh:mm")) + string.Format("{0}", date.ToString("tt").ToLower());
}
var metricSchedule = scheduleMetrics.FirstOrDefault(s => s.Name == scheduleName);
if (metricSchedule == null)
{
metricSchedule = new Schedule();
metricSchedule.Name = scheduleName;
metricSchedule.iCalendarContent = CreateCalendarContent(date, "WEEKLY", ImportDateTime);
metricSchedule.CategoryId = archivedScheduleCategory.Id;
metricSchedule.EffectiveStartDate = ImportDateTime;
metricSchedule.CreatedByPersonAliasId = ImportPersonAliasId;
metricSchedule.CreatedDateTime = ImportDateTime;
metricSchedule.ForeignKey = string.Format("Metric Schedule imported {0}", ImportDateTime);
lookupContext.Schedules.Add(metricSchedule);
lookupContext.SaveChanges();
scheduleMetrics.Add(metricSchedule);
}
metricValue.MetricValuePartitions.Add(new MetricValuePartition
{
MetricPartitionId = metricPartitionScheduleId,
EntityId = metricSchedule.Id,
CreatedDateTime = valueDate,
ModifiedDateTime = valueDate,
CreatedByPersonAliasId = ImportPersonAliasId,
ModifiedByPersonAliasId = ImportPersonAliasId
});
}
if (metricCampusId.HasValue && CampusList.Any(c => c.Id == metricCampusId))
{
var metricPartitionCampusId = currentMetric.MetricPartitions.FirstOrDefault(p => p.Label == "Campus").Id;
metricValue.MetricValuePartitions.Add(new MetricValuePartition {
MetricPartitionId = metricPartitionCampusId, EntityId = metricCampusId
});
}
metricValues.Add(metricValue);
completedItems++;
if (completedItems % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} metrics imported.", completedItems));
}
if (completedItems % ReportingNumber < 1)
{
SaveMetrics(metricValues);
ReportPartialProgress();
metricValues.Clear();
}
}
}
// Check to see if any rows didn't get saved to the database
if (metricValues.Any())
{
SaveMetrics(metricValues);
}
ReportProgress(0, string.Format("Finished metrics import: {0:N0} metrics added or updated.", completedItems));
return(completedItems);
}
/// <summary>
/// Maps the pledge.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
/// <exception cref="System.NotImplementedException"></exception>
private void MapPledge(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList();
var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking().ToList();
var pledgeFrequencies = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues;
var oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id;
var newPledges = new List <FinancialPledge>();
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, $"Verifying pledge import ({totalRows:N0} found).");
foreach (var row in tableData.Where(r => r != null))
{
var amount = row["Total_Pledge"] as decimal?;
var startDate = row["Start_Date"] as DateTime?;
var endDate = row["End_Date"] as DateTime?;
if (amount.HasValue && startDate.HasValue && endDate.HasValue)
{
var individualId = row["Individual_ID"] as int?;
var householdId = row["Household_ID"] as int?;
var personKeys = GetPersonKeys(individualId, householdId, includeVisitors: false);
if (personKeys != null && personKeys.PersonAliasId > 0)
{
var pledge = new FinancialPledge
{
PersonAliasId = personKeys.PersonAliasId,
CreatedByPersonAliasId = ImportPersonAliasId,
ModifiedDateTime = ImportDateTime,
StartDate = ( DateTime )startDate,
EndDate = ( DateTime )endDate,
TotalAmount = ( decimal )amount
};
var frequency = row["Pledge_Frequency_Name"].ToString();
if (!string.IsNullOrWhiteSpace(frequency))
{
if (frequency.Equals("one time", StringComparison.OrdinalIgnoreCase) || frequency.Equals("as can", StringComparison.OrdinalIgnoreCase))
{
pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId;
}
else
{
pledge.PledgeFrequencyValueId = pledgeFrequencies
.Where(f => f.Value.StartsWith(frequency, StringComparison.OrdinalIgnoreCase) || f.Description.StartsWith(frequency, StringComparison.OrdinalIgnoreCase))
.Select(f => f.Id).FirstOrDefault();
}
}
var fundName = row["Fund_Name"] as string;
var subFund = row["Sub_Fund_Name"] as string;
if (!string.IsNullOrWhiteSpace(fundName))
{
var parentAccount = accountList.FirstOrDefault(a => !a.CampusId.HasValue && a.Name.Equals(fundName.Truncate(50), StringComparison.OrdinalIgnoreCase));
if (parentAccount == null)
{
parentAccount = AddFinancialAccount(lookupContext, fundName, $"{fundName} imported {ImportDateTime}", string.Empty, null, null, null, startDate, fundName.RemoveSpecialCharacters());
accountList.Add(parentAccount);
}
if (!string.IsNullOrWhiteSpace(subFund))
{
int?campusFundId = null;
// assign a campus if the subfund is a campus fund
var campusFund = CampusList.FirstOrDefault(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode));
if (campusFund != null)
{
// use full campus name as the subfund
subFund = campusFund.Name;
campusFundId = campusFund.Id;
}
// add info to easily find/assign this fund in the view
subFund = $"{subFund} {fundName}";
var childAccount = accountList.FirstOrDefault(c => c.ParentAccountId == parentAccount.Id && c.Name.Equals(subFund.Truncate(50), StringComparison.OrdinalIgnoreCase));
if (childAccount == null)
{
// create a child account with a campusId if it was set
childAccount = AddFinancialAccount(lookupContext, subFund, $"{subFund} imported {ImportDateTime}", string.Empty, campusFundId, parentAccount.Id, null, startDate, subFund.RemoveSpecialCharacters(), accountTypeValueId: parentAccount.AccountTypeValueId);
accountList.Add(childAccount);
}
pledge.AccountId = childAccount.Id;
}
else
{
pledge.AccountId = parentAccount.Id;
}
}
newPledges.Add(pledge);
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, $"{completedItems:N0} pledges imported ({percentComplete}% complete).");
}
if (completedItems % ReportingNumber < 1)
{
SavePledges(newPledges);
ReportPartialProgress();
newPledges.Clear();
}
}
}
}
if (newPledges.Any())
{
SavePledges(newPledges);
}
ReportProgress(100, $"Finished pledge import: {completedItems:N0} pledges imported.");
}
/// <summary>
/// Maps the 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);
}
