/// <summary>
/// Adds the missing relationship groups.
/// Copied from Rock Cleanup Job
/// </summary>
/// <param name="relationshipGroupType">Type of the relationship group.</param>
/// <param name="ownerRoleGuid">The owner role unique identifier.</param>
private static void AddMissingRelationshipGroups()
{
var relationshipGroupType = CachedTypes.KnownRelationshipGroupType;
var ownerRoleGuid = CachedTypes.KnownRelationshipOwnerRoleGuid;
if ( relationshipGroupType != null )
{
var ownerRoleId = relationshipGroupType.Roles
.Where( r => r.Guid.Equals( ownerRoleGuid ) ).Select( a => ( int? ) a.Id ).FirstOrDefault();
if ( ownerRoleId.HasValue )
{
var rockContext = new RockContext();
var personService = new PersonService( rockContext );
var memberService = new GroupMemberService( rockContext );
var qryGroupOwnerPersonIds = memberService.Queryable( true )
.Where( m => m.GroupRoleId == ownerRoleId.Value ).Select( a => a.PersonId );
var personIdsWithoutKnownRelationshipGroup = personService.Queryable( true, true ).Where( p => !qryGroupOwnerPersonIds.Contains( p.Id ) ).Select( a => a.Id ).ToList();
var groupsToInsert = new List<Group>();
var groupGroupMembersToInsert = new Dictionary<Guid, GroupMember>();
foreach ( var personId in personIdsWithoutKnownRelationshipGroup )
{
var groupMember = new GroupMember();
groupMember.PersonId = personId;
groupMember.GroupRoleId = ownerRoleId.Value;
var group = new Group();
group.Name = relationshipGroupType.Name;
group.Guid = Guid.NewGuid();
group.GroupTypeId = relationshipGroupType.Id;
groupGroupMembersToInsert.Add( group.Guid, groupMember );
groupsToInsert.Add( group );
}
if ( groupsToInsert.Any() )
{
// use BulkInsert just in case there are a large number of groups and group members to insert
rockContext.BulkInsert( groupsToInsert );
Dictionary<Guid, int> groupIdLookup = new GroupService( rockContext ).Queryable().Where( a => a.GroupTypeId == relationshipGroupType.Id ).Select( a => new { a.Id, a.Guid } ).ToDictionary( k => k.Guid, v => v.Id );
var groupMembersToInsert = new List<GroupMember>();
foreach ( var groupGroupMember in groupGroupMembersToInsert )
{
var groupMember = groupGroupMember.Value;
groupMember.GroupId = groupIdLookup[groupGroupMember.Key];
groupMembersToInsert.Add( groupMember );
}
rockContext.BulkInsert( groupMembersToInsert );
}
rockContext.Dispose();
}
}
}
/// <summary>
/// Loads the named location data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadNamedLocation(CSVInstance csvData)
{
// Required variables
var lookupContext = new RockContext();
var locationTypes = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.LOCATION_TYPE), lookupContext).DefinedValues;
int numImportedNamedLocations = ImportedLocations.Count(c => c.Name != null);
var newNamedLocationList = new List <Location>();
int completed = 0;
ReportProgress(0, string.Format("Starting named location import ({0:N0} already exist).", numImportedNamedLocations));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
string rowNamedLocationName = row[NamedLocationName];
string rowNamedLocationKey = row[NamedLocationId];
// Check that this location isn't already in our data
bool locationExists = false;
if (ImportedLocations.Count() > 0)
{
locationExists = ImportedLocations.Any(l => l.ForeignKey.Equals(rowNamedLocationKey));
}
// Check if this was an existing location that needs foreign id added
if (!locationExists)
{
var location = new LocationService(lookupContext).Queryable().FirstOrDefault(l => (l.ForeignKey == null || l.ForeignKey.Trim() == "") && l.Name.Equals(rowNamedLocationName, StringComparison.OrdinalIgnoreCase));
if (location != null)
{
location.ForeignKey = rowNamedLocationKey;
location.ForeignId = rowNamedLocationKey.AsIntegerOrNull();
location.ForeignGuid = rowNamedLocationKey.AsGuidOrNull();
lookupContext.SaveChanges();
locationExists = true;
ImportedLocations.Add(location);
completed++;
}
}
if (!string.IsNullOrWhiteSpace(rowNamedLocationKey) && !locationExists)
{
string rowNamedLocationCreatedDate = row[NamedLocationCreatedDate];
string rowNamedLocationType = row[NamedLocationType];
string rowNamedLocationParent = row[NamedLocationParent];
string rowNamedLocationSoftRoomThreshold = row[NamedLocationSoftRoomThreshold];
string rowNamedLocationFirmRoomThreshold = row[NamedLocationFirmRoomThreshold];
int? rowSoftThreshold = rowNamedLocationSoftRoomThreshold.AsType <int?>();
int? rowFirmThreshold = rowNamedLocationFirmRoomThreshold.AsType <int?>();
int? rowNamedLocationId = rowNamedLocationKey.AsType <int?>();
Location newLocation = new Location();
newLocation.Name = rowNamedLocationName;
newLocation.CreatedDateTime = ParseDateOrDefault(rowNamedLocationCreatedDate, ImportDateTime);
newLocation.ModifiedDateTime = ImportDateTime;
newLocation.CreatedByPersonAliasId = ImportPersonAliasId;
newLocation.ModifiedByPersonAliasId = ImportPersonAliasId;
newLocation.ForeignKey = rowNamedLocationKey;
newLocation.ForeignId = rowNamedLocationId;
if (rowSoftThreshold != null && rowSoftThreshold > 0)
{
newLocation.SoftRoomThreshold = rowSoftThreshold;
}
if (rowFirmThreshold != null && rowFirmThreshold > 0)
{
newLocation.FirmRoomThreshold = rowFirmThreshold;
}
if (!string.IsNullOrWhiteSpace(rowNamedLocationType))
{
var locationTypeId = locationTypes.Where(v => v.Value.Equals(rowNamedLocationType) || v.Id.Equals(rowNamedLocationType) || v.Guid.ToString().ToLower().Equals(rowNamedLocationType.ToLower()))
.Select(v => ( int? )v.Id).FirstOrDefault();
newLocation.LocationTypeValueId = locationTypeId;
}
if (!string.IsNullOrWhiteSpace(rowNamedLocationParent))
{
int?parentLocationId = ImportedLocations.FirstOrDefault(l => l.ForeignKey.Equals(rowNamedLocationParent) || (l.Name != null && l.Name.Equals(rowNamedLocationParent))).Id;
newLocation.ParentLocationId = parentLocationId;
}
newNamedLocationList.Add(newLocation);
//
// Save Every Loop
//
SaveNamedLocation(newNamedLocationList);
newNamedLocationList.Clear();
//
// Keep the user informed as to what is going on and save in batches.
//
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} groups imported.", completed));
}
if (completed % ReportingNumber < 1)
{
ReportPartialProgress();
}
}
}
//
// Check to see if any rows didn't get saved to the database
//
if (newNamedLocationList.Any())
{
SaveNamedLocation(newNamedLocationList);
}
lookupContext.SaveChanges();
DetachAllInContext(lookupContext);
lookupContext.Dispose();
ReportProgress(0, string.Format("Finished named location import: {0:N0} named locations added or updated.", completed));
return(completed);
}
/// <summary>
/// Translates the attendance data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void TranslateAttendance(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var newAttendances = new List <Attendance>();
var importedAttendancesCount = lookupContext.Attendances.AsNoTracking()
.Count(a => a.ForeignKey != null);
var importedCodes = lookupContext.AttendanceCodes.AsNoTracking()
.Where(c => c.ForeignKey != null).ToList();
var importedDevices = lookupContext.Devices.AsNoTracking()
.Where(d => d.DeviceTypeValueId == DeviceTypeCheckinKioskId).ToList();
var archivedScheduleName = "Archived Attendance";
var archivedSchedule = new ScheduleService(lookupContext).Queryable()
.FirstOrDefault(s => s.Name.Equals(archivedScheduleName));
if (archivedSchedule == null)
{
archivedSchedule = AddNamedSchedule(lookupContext, archivedScheduleName, null, null, null,
ImportDateTime, archivedScheduleName.RemoveSpecialCharacters(), true, ImportPersonAliasId);
}
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, $"Verifying attendance import ({importedAttendancesCount:N0} already exist).");
foreach (var row in tableData.Where(r => r != null))
{
var rlcId = row["RLC_ID"] as int?;
var individualId = row["Individual_ID"] as int?;
var startDate = row["Start_Date_Time"] as DateTime?;
var attendanceCode = row["Tag_Code"] as string;
var attendanceNote = row["BreakoutGroup_Name"] as string;
var checkinDate = row["Check_In_Time"] as DateTime?;
var checkoutDate = row["Check_Out_Time"] as DateTime?;
var machineName = row["Checkin_Machine_Name"] as string;
// at minimum, attendance needs a person and a date
var personKeys = GetPersonKeys(individualId, null);
if (personKeys != null && personKeys.PersonAliasId > 0 && startDate.HasValue)
{
// create the initial attendance
var attendance = new Attendance
{
PersonAliasId = personKeys.PersonAliasId,
DidAttend = true,
Note = attendanceNote,
StartDateTime = (DateTime)startDate,
EndDateTime = checkoutDate,
CreatedDateTime = checkinDate,
ForeignKey = $"Attendance imported {ImportDateTime}"
};
// add the RLC info
var rlcGroup = ImportedGroups.FirstOrDefault(g => g.ForeignId.Equals(rlcId));
if (rlcGroup != null && rlcGroup.Id > 0)
{
attendance.CampusId = rlcGroup.CampusId;
attendance.Occurrence = new AttendanceOccurrence
{
OccurrenceDate = (DateTime)startDate,
GroupId = rlcGroup.Id,
ScheduleId = archivedSchedule.Id,
LocationId = rlcGroup.GroupLocations.Select(gl => (int?)gl.LocationId).FirstOrDefault(),
};
}
// add the tag code
//if ( !string.IsNullOrWhiteSpace( attendanceCode ) )
//{
//var issueDatetime = checkinDate ?? (DateTime)startDate;
//var code = importedCodes.FirstOrDefault( c => c.Code.Equals( attendanceCode ) && c.IssueDateTime.Equals( issueDatetime ) );
//if ( code == null )
//{
// code = new AttendanceCode
// {
// Code = attendanceCode,
// IssueDateTime = issueDatetime,
// ForeignKey = string.Format( "Attendance imported {0}", ImportDateTime )
// };
// lookupContext.AttendanceCodes.Add( code );
// lookupContext.SaveChanges();
// importedCodes.Add( code );
//}
//attendance.AttendanceCodeId = code.Id;
//}
// add the device
if (!string.IsNullOrWhiteSpace(machineName))
{
var device = importedDevices.FirstOrDefault(d => d.Name.Equals(machineName, StringComparison.CurrentCultureIgnoreCase));
if (device == null)
{
device = AddDevice(lookupContext, machineName, null, DeviceTypeCheckinKioskId, null, null, ImportDateTime,
$"{machineName} imported {ImportDateTime}", true, ImportPersonAliasId);
importedDevices.Add(device);
}
attendance.DeviceId = device.Id;
}
newAttendances.Add(attendance);
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, $"{completedItems:N0} attendances imported ({percentComplete}% complete).");
}
else if (completedItems % ReportingNumber < 1)
{
SaveAttendances(newAttendances, false);
ReportPartialProgress();
// Reset lists and context
lookupContext.Dispose();
lookupContext = new RockContext();
newAttendances.Clear();
}
}
}
if (newAttendances.Any())
{
SaveAttendances(newAttendances, false);
}
lookupContext.Dispose();
ReportProgress(100, $"Finished attendance import: {completedItems:N0} attendances imported.");
}
/// <summary>
/// Translates the groups attendance data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void TranslateGroupsAttendance(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var newAttendances = new List <Attendance>();
var newOccurrences = new List <AttendanceOccurrence>();
var importedAttendancesCount = lookupContext.Attendances.AsNoTracking()
.Count(a => a.ForeignKey != null && a.Occurrence.GroupId.HasValue && a.Occurrence.Group.GroupTypeId == GeneralGroupTypeId);
var archivedScheduleName = "Archived Attendance";
var archivedSchedule = new ScheduleService(lookupContext).Queryable()
.FirstOrDefault(s => s.Name.Equals(archivedScheduleName));
if (archivedSchedule == null)
{
archivedSchedule = AddNamedSchedule(lookupContext, archivedScheduleName, null, null, null,
ImportDateTime, archivedScheduleName.RemoveSpecialCharacters(), true, ImportPersonAliasId);
}
// Get list of existing attendance occurrences
var existingOccurrences = new HashSet <ImportOccurrence>(new AttendanceOccurrenceService(lookupContext).Queryable()
.Select(o => new ImportOccurrence
{
Id = o.Id,
GroupId = o.GroupId,
LocationId = o.LocationId,
ScheduleId = o.ScheduleId,
OccurrenceDate = o.OccurrenceDate
}));
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, $"Verifying group attendance import, ({totalRows:N0} found, {importedAttendancesCount:N0} already exist).");
foreach (var row in tableData.Where(r => r != null))
{
var groupId = row["GroupID"] as int?;
var startDate = row["StartDateTime"] as DateTime?;
var endDate = row["EndDateTime"] as DateTime?;
var attendanceNote = row["Comments"] as string;
var wasPresent = row["Individual_Present"] as int?;
var individualId = row["IndividualID"] as int?;
var checkinDate = row["CheckinDateTime"] as DateTime?;
var checkoutDate = row["CheckoutDateTime"] as DateTime?;
var createdDate = row["AttendanceCreatedDate"] as DateTime?;
var personKeys = GetPersonKeys(individualId, null);
var peopleGroup = groupId.HasValue ? ImportedGroups.FirstOrDefault(g => g.ForeignId.Equals(groupId)) : null;
if (personKeys != null && personKeys.PersonAliasId > 0 && startDate.HasValue)
{
// create the initial attendance
var attendance = new Attendance
{
PersonAliasId = personKeys.PersonAliasId,
DidAttend = wasPresent != 0,
Note = attendanceNote,
StartDateTime = (DateTime)startDate,
EndDateTime = checkoutDate,
CreatedDateTime = checkinDate,
ForeignKey = $"Group Attendance imported {ImportDateTime}"
};
// add the group info
if (peopleGroup != null && peopleGroup.Id > 0)
{
attendance.CampusId = peopleGroup.CampusId;
var groupLocationId = peopleGroup.GroupLocations.Select(gl => (int?)gl.LocationId).FirstOrDefault();
var existingOccurrence = existingOccurrences
.FirstOrDefault(o =>
o.GroupId == peopleGroup.Id &&
o.OccurrenceDate == (DateTime)startDate &&
o.ScheduleId == archivedSchedule.Id &&
GroupTypeMeetingLocationId == groupLocationId
);
if (existingOccurrence == null)
{
attendance.Occurrence = new AttendanceOccurrence
{
OccurrenceDate = (DateTime)startDate,
GroupId = peopleGroup.Id,
ScheduleId = archivedSchedule.Id,
LocationId = peopleGroup.GroupLocations.Select(gl => (int?)gl.LocationId).FirstOrDefault(),
};
newOccurrences.Add(attendance.Occurrence);
}
else
{
attendance.OccurrenceId = existingOccurrence.Id;
}
}
newAttendances.Add(attendance);
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, $"{completedItems:N0} group attendances imported ({percentComplete}% complete).");
}
else if (completedItems % ReportingNumber < 1)
{
SaveAttendances(newAttendances);
ReportPartialProgress();
foreach (var o in newOccurrences)
{
existingOccurrences.Add(new ImportOccurrence
{
Id = o.Id,
GroupId = o.GroupId,
LocationId = o.LocationId,
ScheduleId = o.ScheduleId,
OccurrenceDate = o.OccurrenceDate
});
}
// Reset lists and context
lookupContext.Dispose();
lookupContext = new RockContext();
newAttendances.Clear();
}
}
}
if (newAttendances.Any())
{
SaveAttendances(newAttendances);
}
lookupContext.Dispose();
ReportProgress(100, $"Finished group attendance import: {completedItems:N0} attendances imported.");
}
/// <summary>
/// Loads the Content Channel data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadContentChannel(CSVInstance csvData)
{
var lookupContext = new RockContext();
var contentChannelService = new ContentChannelService(lookupContext);
var contentChannelTypeService = new ContentChannelTypeService(lookupContext);
var groupService = new GroupService(lookupContext);
// Look for custom attributes in the Content Channel file
var allFields = csvData.TableNodes.FirstOrDefault().Children.Select((node, index) => new { node = node, index = index }).ToList();
var customAttributes = allFields
.Where(f => f.index > ContentChannelParentId)
.ToDictionary(f => f.index, f => f.node.Name);
var completed = 0;
var importedCount = 0;
var alreadyImportedCount = contentChannelService.Queryable().AsNoTracking().Count(c => c.ForeignKey != null);
ReportProgress(0, $"Starting Content Channel import ({alreadyImportedCount: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 rowContentChannelName = row[ContentChannelName];
var rowContentChannelTypeName = row[ContentChannelTypeName];
var rowContentChannelDescription = row[ContentChannelDescription];
var rowContentChannelId = row[ContentChannelId];
var rowContentChannelRequiresApproval = row[ContentChannelRequiresApproval];
var rowContentChannelParentId = row[ContentChannelParentId];
var rowChannelId = rowContentChannelId.AsType <int?>();
var requiresApproval = ( bool )ParseBoolOrDefault(rowContentChannelRequiresApproval, false);
var contentChannelTypeId = 0;
if (contentChannelTypeService.Queryable().AsNoTracking().FirstOrDefault(t => t.Name.ToLower() == rowContentChannelTypeName.ToLower()) != null)
{
contentChannelTypeId = contentChannelTypeService.Queryable().AsNoTracking().FirstOrDefault(t => t.Name.ToLower() == rowContentChannelTypeName.ToLower()).Id;
}
//
// Verify the Content Channel Type Exists
//
if (contentChannelTypeId < 1)
{
var newConentChannelType = new ContentChannelType
{
Name = rowContentChannelTypeName,
DateRangeType = ContentChannelDateType.DateRange,
IncludeTime = true,
DisablePriority = true,
CreatedDateTime = ImportDateTime
};
lookupContext.ContentChannelTypes.Add(newConentChannelType);
lookupContext.SaveChanges(DisableAuditing);
contentChannelTypeId = lookupContext.ContentChannelTypes.FirstOrDefault(t => t.Name == rowContentChannelTypeName).Id;
}
//
// Check that this Content Channel doesn't already exist.
//
var exists = false;
if (alreadyImportedCount > 0)
{
exists = contentChannelService.Queryable().AsNoTracking().Any(c => c.ForeignKey == rowContentChannelId);
}
if (!exists)
{
//
// Create and populate the new Content Channel.
//
var contentChannel = new ContentChannel
{
Name = rowContentChannelName,
Description = rowContentChannelDescription,
ContentChannelTypeId = contentChannelTypeId,
ForeignKey = rowContentChannelId,
ForeignId = rowChannelId,
ContentControlType = ContentControlType.HtmlEditor,
RequiresApproval = requiresApproval
};
//
// Look for Parent Id and create appropriate objects.
//
if (!string.IsNullOrWhiteSpace(rowContentChannelParentId))
{
var ParentChannels = new List <ContentChannel>();
var parentChannel = contentChannelService.Queryable().FirstOrDefault(p => p.ForeignKey == rowContentChannelParentId);
if (parentChannel.ForeignKey == rowContentChannelParentId)
{
ParentChannels.Add(parentChannel);
contentChannel.ParentContentChannels = ParentChannels;
}
}
// Save changes for context
lookupContext.WrapTransaction(() =>
{
lookupContext.ContentChannels.Add(contentChannel);
lookupContext.SaveChanges(DisableAuditing);
});
// Set security if needed
if (contentChannel.RequiresApproval)
{
var rockAdmins = groupService.Get(Rock.SystemGuid.Group.GROUP_ADMINISTRATORS.AsGuid());
contentChannel.AllowSecurityRole(Authorization.APPROVE, rockAdmins, lookupContext);
var communicationAdmins = groupService.Get(Rock.SystemGuid.Group.GROUP_COMMUNICATION_ADMINISTRATORS.AsGuid());
contentChannel.AllowSecurityRole(Authorization.APPROVE, communicationAdmins, lookupContext);
// Save security changes
lookupContext.WrapTransaction(() =>
{
lookupContext.SaveChanges(DisableAuditing);
});
}
//
// Process Attributes for Content Channels
//
if (customAttributes.Any())
{
// create content channel attributes
foreach (var newAttributePair in customAttributes)
{
var pairs = newAttributePair.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);
if (string.IsNullOrEmpty(attributeName))
{
LogException("Content Channel Type", $"Content Channel Type Channel Attribute Name cannot be blank '{newAttributePair.Value}'.");
}
else
{
var fk = string.Empty;
if (string.IsNullOrWhiteSpace(attributeForeignKey))
{
fk = $"Bulldozer_ContentChannelType_{contentChannelTypeId}_{categoryName.RemoveWhitespace()}_{attributeName.RemoveWhitespace()}".Left(100);
}
else
{
fk = attributeForeignKey;
}
AddEntityAttribute(lookupContext, contentChannel.TypeId, "ContentChannelTypeId", contentChannelTypeId.ToString(), fk, categoryName, attributeName, string.Empty, fieldTypeId, true, definedValueForeignId, definedValueForeignKey, attributeTypeString: attributeTypeString);
}
}
//
// Add any Content Channel attribute values
//
foreach (var attributePair in customAttributes)
{
var newValue = row[attributePair.Key];
if (!string.IsNullOrWhiteSpace(newValue))
{
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;
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];
}
}
if (!string.IsNullOrEmpty(attributeName))
{
string fk = string.Empty;
if (string.IsNullOrWhiteSpace(attributeForeignKey))
{
fk = $"Bulldozer_ContentChannelType_{contentChannelTypeId}_{categoryName.RemoveWhitespace()}_{attributeName.RemoveWhitespace()}".Left(100);
}
else
{
fk = attributeForeignKey;
}
var attribute = FindEntityAttribute(lookupContext, categoryName, attributeName, contentChannel.TypeId, fk);
AddEntityAttributeValue(lookupContext, attribute, contentChannel, newValue, null, true);
}
}
}
}
importedCount++;
}
//
// Notify user of our status.
//
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, $"{completed:N0} Content Channel records processed, {importedCount:N0} imported.");
}
if (completed % ReportingNumber < 1)
{
lookupContext.SaveChanges();
ReportPartialProgress();
// Clear out variables
contentChannelService = new ContentChannelService(lookupContext);
}
}
//
// Save any other changes to existing items.
//
lookupContext.SaveChanges();
lookupContext.Dispose();
ReportProgress(0, $"Finished Content Channel import: {importedCount:N0} records added.");
return(completed);
}
/// <summary>
/// Loads the group membership data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadGroupMember( CSVInstance csvData )
{
var lookupContext = new RockContext();
var groupTypeRoleService = new GroupTypeRoleService( lookupContext );
var groupMemberService = new GroupMemberService( lookupContext );
Dictionary<string, int> importedMembers = groupMemberService.Queryable( true ).AsNoTracking()
.Where( m => m.ForeignKey != null )
.ToDictionary( m => m.ForeignKey, m => m.Id );
var groupTypeRoles = new Dictionary<int?, Dictionary<string, int>>();
foreach ( var role in groupTypeRoleService.Queryable().AsNoTracking().GroupBy( r => r.GroupTypeId ) )
{
groupTypeRoles.Add( role.Key, role.ToDictionary( r => r.Name, r => r.Id, StringComparer.OrdinalIgnoreCase ) );
}
var currentGroup = new Group();
var newMemberList = new List<GroupMember>();
int completed = 0;
int imported = 0;
ReportProgress( 0, string.Format( "Starting group member import ({0:N0} already exist).", importedMembers.Count ) );
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ( ( row = csvData.Database.FirstOrDefault() ) != null )
{
string rowGroupMemberKey = row[GroupMemberId];
string rowGroupKey = row[GroupMemberGroupId];
string rowPersonKey = row[GroupMemberPersonId];
string rowCreatedDate = row[GroupMemberCreatedDate];
string rowMemberRole = row[GroupMemberRole];
string rowMemberActive = row[GroupMemberActive];
int? rowGroupMemberId = rowGroupMemberKey.AsType<int?>();
//
// Find this person in the database.
//
var personKeys = GetPersonKeys( rowPersonKey );
if ( personKeys == null || personKeys.PersonId == 0 )
{
LogException( "InvalidPersonKey", string.Format( "Person key {0} not found", rowPersonKey ) );
ReportProgress( 0, string.Format( "Person key {0} not found", rowPersonKey ) );
}
//
// Check that this member isn't already in our data
//
bool memberExists = false;
if ( importedMembers.Count > 0 )
{
memberExists = importedMembers.ContainsKey( rowGroupMemberKey );
}
if ( !memberExists && ( personKeys != null && personKeys.PersonId != 0 ) )
{
if ( currentGroup == null || rowGroupKey != currentGroup.ForeignKey )
{
currentGroup = ImportedGroups.FirstOrDefault( g => g.ForeignKey.Equals( rowGroupKey ) );
}
if ( currentGroup != null )
{
GroupMember groupMember = new GroupMember();
groupMember.PersonId = personKeys.PersonId;
groupMember.GroupId = currentGroup.Id;
groupMember.CreatedDateTime = ParseDateOrDefault( rowCreatedDate, ImportDateTime );
groupMember.ModifiedDateTime = ImportDateTime;
groupMember.CreatedByPersonAliasId = ImportPersonAliasId;
groupMember.ForeignKey = rowGroupMemberKey;
groupMember.ForeignId = rowGroupMemberId;
groupMember.GroupMemberStatus = GetGroupMemberStatus( rowMemberActive );
//
// Find and set the group role id.
//
if ( !string.IsNullOrEmpty( rowMemberRole ) )
{
var typeExists = groupTypeRoles.ContainsKey( currentGroup.GroupTypeId );
if ( typeExists && groupTypeRoles[currentGroup.GroupTypeId].ContainsKey( rowMemberRole ) )
{
groupMember.GroupRoleId = groupTypeRoles[currentGroup.GroupTypeId][rowMemberRole];
}
else
{
var newRoleId = AddGroupRole( lookupContext, currentGroup.GroupType.Guid.ToString(), rowMemberRole );
// check if adding an additional role for this grouptype or creating the first one
if ( typeExists )
{
groupTypeRoles[currentGroup.GroupType.Id].Add( rowMemberRole, newRoleId );
}
else
{
groupTypeRoles.Add( currentGroup.GroupType.Id, new Dictionary<string, int> { { rowMemberRole, newRoleId } } );
}
groupMember.GroupRoleId = newRoleId;
}
}
else
{
if ( currentGroup.GroupType.DefaultGroupRoleId != null )
{
groupMember.GroupRoleId = ( int ) currentGroup.GroupType.DefaultGroupRoleId;
}
else
{
groupMember.GroupRoleId = currentGroup.GroupType.Roles.First().Id;
}
}
//
// Add member to the group.
//
currentGroup.Members.Add( groupMember );
newMemberList.Add( groupMember );
imported++;
}
else
{
LogException( "InvalidGroupKey", string.Format( "Group key {0} not found", rowGroupKey ) );
}
}
//
// Notify user of our status.
//
completed++;
if ( completed % ( ReportingNumber * 10 ) < 1 )
{
ReportProgress( 0, string.Format( "{0:N0} rows processed, {1:N0} members imported.", completed, imported ) );
}
if ( completed % ReportingNumber < 1 )
{
SaveGroupMembers( newMemberList );
lookupContext.SaveChanges();
ReportPartialProgress();
// Clear out variables
currentGroup = new Group();
newMemberList.Clear();
}
}
//
// Save any final changes to new groups
//
if ( newMemberList.Any() )
{
SaveGroupMembers( newMemberList );
}
//
// Save any changes to existing groups
//
lookupContext.SaveChanges();
lookupContext.Dispose();
ReportProgress( 0, string.Format( "Finished group member import: {0:N0} members added.", imported ) );
return completed;
}
/// <summary>
/// Maps the requirement data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapRequirement(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var importedAttributeCount = lookupContext.AttributeValues.Count(v => v.Attribute.EntityTypeId == PersonEntityTypeId && v.ForeignKey != null);
var personAttributes = new AttributeService(lookupContext).GetByEntityTypeId(PersonEntityTypeId)
.Include("Categories").Include("AttributeQualifiers").AsNoTracking().ToList();
var backgroundCheckedAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals("BackgroundChecked", StringComparison.CurrentCultureIgnoreCase));
var peopleToUpdate = new Dictionary <int, Person>();
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying requirement import ({0:N0} found, {1:N0} already exist).", totalRows, importedAttributeCount));
foreach (var row in tableData.OrderBy(r => r["Requirement_Date"]).ThenByDescending(r => r["Individual_ID"]).Where(r => r != null))
{
var individualId = row["Individual_ID"] as int?;
var requirementName = row["Requirement_Name"] as string;
var requirementDateString = row["Requirement_Date"] as string;
var requirementStatus = row["Requirement_Status_Name"] as string;
var isConfidential = row["Is_Confidential"] as bool?;
var requirementDate = requirementDateString.AsDateTime();
var confidentialCategory = isConfidential == true ? "Confidential" : string.Empty;
// create the requirement date
var attributeName = string.Format("{0} Date", requirementName);
var requirementDateAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals(attributeName.RemoveSpecialCharacters()) && a.FieldTypeId == DateFieldTypeId);
if (requirementDateAttribute == null)
{
requirementDateAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, string.Format("{0} imported {1}", attributeName, ImportDateTime),
confidentialCategory, attributeName, attributeName.RemoveSpecialCharacters(), DateFieldTypeId, importPersonAliasId: ImportPersonAliasId);
personAttributes.Add(requirementDateAttribute);
}
// create the requirement status
attributeName = string.Format("{0} Result", requirementName);
var requirementResultAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals(attributeName.RemoveSpecialCharacters()) && a.FieldTypeId == SingleSelectFieldTypeId);
if (requirementResultAttribute == null)
{
requirementResultAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, string.Format("{0} imported {1}", attributeName, ImportDateTime),
confidentialCategory, attributeName, attributeName.RemoveSpecialCharacters(), SingleSelectFieldTypeId, importPersonAliasId: ImportPersonAliasId);
personAttributes.Add(requirementResultAttribute);
}
// add any custom qualifiers
var valuesQualifier = requirementResultAttribute.AttributeQualifiers.FirstOrDefault(q => q.Key.Equals("values", StringComparison.CurrentCultureIgnoreCase));
if (valuesQualifier != null && !valuesQualifier.Value.Contains(requirementStatus))
{
valuesQualifier = AddAttributeQualifier(lookupContext, requirementResultAttribute.Id, requirementStatus);
}
// make sure we have a valid person to assign to
var matchingPerson = GetPersonKeys(individualId, null, includeVisitors: false);
if (matchingPerson != null)
{
var person = !peopleToUpdate.ContainsKey(matchingPerson.PersonId)
? lookupContext.People.AsQueryable().AsNoTracking().FirstOrDefault(p => p.Id == matchingPerson.PersonId)
: peopleToUpdate[matchingPerson.PersonId];
if (person != null)
{
// 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>();
}
AddEntityAttributeValue(lookupContext, requirementResultAttribute, person, requirementStatus);
if (requirementDate.HasValue)
{
AddEntityAttributeValue(lookupContext, requirementDateAttribute, person, requirementDate.Value.ToString("yyyy-MM-dd"));
}
if (requirementName.StartsWith("Background Check", StringComparison.CurrentCultureIgnoreCase))
{
AddEntityAttributeValue(lookupContext, backgroundCheckedAttribute, person, "True");
}
// add all the person changes to the 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} requirements imported ({percentComplete}% complete).");
}
if (completedItems % ReportingNumber < 1)
{
SaveAttributes(peopleToUpdate);
// reset so context doesn't bloat
lookupContext.Dispose();
lookupContext = new RockContext();
peopleToUpdate.Clear();
ReportPartialProgress();
}
}
SaveAttributes(peopleToUpdate);
ReportProgress(100, $"Finished requirement import: {completedItems:N0} requirements imported.");
}
/// <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 groups attendance data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapGroupsAttendance(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var newAttendances = new List <Attendance>();
var importedAttendancesCount = lookupContext.Attendances.AsNoTracking()
.Count(a => a.ForeignKey != null && a.Occurrence.GroupId.HasValue && a.Occurrence.Group.GroupTypeId == GeneralGroupTypeId);
var archivedScheduleName = "Archived Attendance";
var archivedSchedule = new ScheduleService(lookupContext).Queryable()
.FirstOrDefault(s => s.Name.Equals(archivedScheduleName));
if (archivedSchedule == null)
{
archivedSchedule = AddNamedSchedule(lookupContext, archivedScheduleName, null, null, null,
ImportDateTime, archivedScheduleName.RemoveSpecialCharacters(), true, ImportPersonAliasId);
}
var existingOccurrences = new AttendanceOccurrenceService(lookupContext).Queryable().AsNoTracking()
.Select(o => new
{
o.Id,
o.GroupId,
o.LocationId,
o.ScheduleId,
o.OccurrenceDate
}).ToDictionary(k => $"{k.GroupId}|{k.LocationId}|{k.ScheduleId}|{k.OccurrenceDate}", v => v.Id);
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, $"Verifying group attendance import, ({totalRows:N0} found, {importedAttendancesCount:N0} already exist).");
foreach (var row in tableData.Where(r => r != null))
{
var groupId = row["GroupID"] as int?;
var startDate = row["StartDateTime"] as DateTime?;
var endDate = row["EndDateTime"] as DateTime?;
var attendanceNote = row["Comments"] as string;
var wasPresent = row["Individual_Present"] as int?;
var individualId = row["IndividualID"] as int?;
var checkinDate = row["CheckinDateTime"] as DateTime?;
var checkoutDate = row["CheckoutDateTime"] as DateTime?;
var createdDate = row["AttendanceCreatedDate"] as DateTime?;
var scheduleForeignKey = "F1GD_" + groupId.Value.ToString();
int?scheduleId = null;
var personKeys = GetPersonKeys(individualId, null);
if (personKeys != null && personKeys.PersonAliasId > 0 && startDate.HasValue)
{
// create the initial attendance
var attendance = new Attendance
{
PersonAliasId = personKeys.PersonAliasId,
DidAttend = wasPresent != 0,
Note = attendanceNote,
StartDateTime = ( DateTime )startDate,
EndDateTime = checkoutDate,
CreatedDateTime = checkinDate,
ForeignKey = $"Group Attendance imported {ImportDateTime}"
};
// add the group info if it exists
int?rockGroupId = null;
int?locationId = null;
var startDateString = (( DateTime )startDate).Date;
if (groupId.HasValue)
{
var peopleGroup = ImportedGroups.FirstOrDefault(g => g.ForeignId.Equals(groupId));
rockGroupId = peopleGroup?.Id;
locationId = peopleGroup?.GroupLocations.Select(gl => ( int? )gl.LocationId).FirstOrDefault();
attendance.CampusId = peopleGroup?.CampusId;
scheduleId = peopleGroup?.ScheduleId;
}
else if (lookupContext.Schedules.AsNoTracking().AsQueryable().Any(s => s.ForeignKey == scheduleForeignKey))
{
scheduleId = lookupContext.Schedules.AsNoTracking().AsQueryable().FirstOrDefault(s => s.ForeignKey == scheduleForeignKey).Id;
}
if (!scheduleId.HasValue || scheduleId.Value == 0)
{
scheduleId = archivedSchedule.Id;
}
// occurrence is required for attendance
int?occurrenceId = existingOccurrences.GetValueOrNull($"{rockGroupId}|{locationId}|{scheduleId}|{startDateString}");
if (occurrenceId.HasValue)
{
attendance.OccurrenceId = occurrenceId.Value;
}
else
{
var newOccurrence = AddOccurrence(null, ( DateTime )startDate, rockGroupId, scheduleId, locationId, true);
if (newOccurrence != null)
{
attendance.OccurrenceId = newOccurrence.Id;
existingOccurrences.Add($"{rockGroupId}|{locationId}|{scheduleId}|{startDateString}", newOccurrence.Id);
}
}
newAttendances.Add(attendance);
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, $"{completedItems:N0} group attendances imported ({percentComplete}% complete).");
}
if (completedItems % ReportingNumber < 1)
{
SaveAttendances(newAttendances);
ReportPartialProgress();
// Reset lists and context
lookupContext.Dispose();
lookupContext = new RockContext();
newAttendances.Clear();
}
}
}
if (newAttendances.Any())
{
SaveAttendances(newAttendances);
}
lookupContext.Dispose();
ReportProgress(100, $"Finished group attendance import: {completedItems:N0} attendances imported.");
}
/// <summary>
/// Loads the attendance data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadAttendance(CSVInstance csvData)
{
var lookupContext = new RockContext();
var groupService = new GroupService(lookupContext);
var locationService = new LocationService(lookupContext);
var attendanceService = new AttendanceService(lookupContext);
var currentGroup = new Group();
int?currentGroupId = null;
var location = new Location();
int?locationId = null;
int?campusId = null;
var newAttendanceList = new List <Attendance>();
var newOccurrences = new List <AttendanceOccurrence>();
var existingOccurrences = new AttendanceOccurrenceService(lookupContext).Queryable().AsNoTracking()
.Select(o => new
{
o.Id,
o.GroupId,
o.LocationId,
o.ScheduleId,
o.OccurrenceDate
}).ToDictionary(k => $"{k.GroupId}|{k.LocationId}|{k.ScheduleId}|{k.OccurrenceDate}", v => v.Id);
var archivedScheduleName = "Archived Attendance";
var archivedSchedule = new ScheduleService(lookupContext).Queryable()
.FirstOrDefault(s => s.Name.Equals(archivedScheduleName));
if (archivedSchedule == null)
{
archivedSchedule = AddNamedSchedule(lookupContext, archivedScheduleName, null, null, null,
ImportDateTime, archivedScheduleName.RemoveSpecialCharacters(), true, ImportPersonAliasId);
}
var completed = 0;
var importedCount = 0;
var alreadyImportedCount = attendanceService.Queryable().AsNoTracking().Count(a => a.ForeignKey != null);
ReportProgress(0, string.Format("Starting attendance import ({0:N0} already exist).", alreadyImportedCount));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
var rowAttendanceKey = row[AttendanceId];
var rowGroupKey = row[AttendanceGroupId];
var rowPersonKey = row[AttendancePersonId];
var rowDate = row[AttendanceDate];
var rowCreatedDate = row[AttendanceCreatedDate];
var rowAttended = row[AttendanceAttended];
var rowLocationKey = row[AttendanceLocationId];
var rowAttendanceId = rowAttendanceKey.AsType <int?>();
//
// Find this person in the database.
//
var personKeys = GetPersonKeys(rowPersonKey);
if (personKeys == null || personKeys.PersonId == 0)
{
ReportProgress(0, string.Format("Person key {0} not found", rowPersonKey));
}
//
// Check that this attendance record doesn't already exist.
//
var attendanceExists = false;
if (ImportedGroups.Count > 0 && rowGroupKey != currentGroup?.ForeignKey)
{
currentGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey == rowGroupKey);
currentGroupId = currentGroup?.Id;
}
//
// If we have a valid matching location, set the location and campus.
//
if (!string.IsNullOrEmpty(rowLocationKey))
{
location = locationService.Queryable().FirstOrDefault(l => l.ForeignKey == rowLocationKey);
locationId = location.Id;
campusId = location.CampusId;
}
if (alreadyImportedCount > 0)
{
attendanceExists = attendanceService.Queryable().AsNoTracking().Any(a => a.ForeignKey == rowAttendanceKey);
}
if (!attendanceExists && (personKeys != null && personKeys.PersonId != 0))
{
//
// Create and populate the new attendance record.
//
var attendance = new Attendance
{
PersonAliasId = personKeys.PersonAliasId,
ForeignKey = rowAttendanceKey,
ForeignId = rowAttendanceId,
DidAttend = ParseBoolOrDefault(rowAttended, true),
StartDateTime = ( DateTime )ParseDateOrDefault(rowDate, ImportDateTime),
CreatedDateTime = ParseDateOrDefault(rowCreatedDate, ImportDateTime),
ModifiedDateTime = ImportDateTime,
CreatedByPersonAliasId = ImportPersonAliasId,
ModifiedByPersonAliasId = ImportPersonAliasId,
CampusId = campusId
};
var startDateString = (( DateTime )ParseDateOrDefault(rowDate, ImportDateTime)).Date;
// occurrence is required for attendance
int?occurrenceId = existingOccurrences.GetValueOrNull($"{currentGroupId}|{locationId}|{archivedSchedule.Id}|{startDateString}");
if (occurrenceId.HasValue)
{
attendance.OccurrenceId = occurrenceId.Value;
}
else
{
var newOccurrence = AddOccurrence(null, ( DateTime )ParseDateOrDefault(rowDate, ImportDateTime), currentGroupId, archivedSchedule.Id, locationId, true);
if (newOccurrence != null)
{
attendance.OccurrenceId = newOccurrence.Id;
existingOccurrences.Add($"{currentGroupId}|{locationId}|{archivedSchedule.Id}|{startDateString}", newOccurrence.Id);
}
}
//
// Add the attendance record for delayed saving.
//
newAttendanceList.Add(attendance);
importedCount++;
}
//
// Notify user of our status.
//
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} attendance records processed, {1:N0} imported.", completed, importedCount));
}
if (completed % ReportingNumber < 1)
{
SaveAttendance(newAttendanceList);
lookupContext.SaveChanges();
ReportPartialProgress();
// Clear out variables
currentGroup = new Group();
newAttendanceList.Clear();
groupService = new GroupService(lookupContext);
locationService = new LocationService(lookupContext);
attendanceService = new AttendanceService(lookupContext);
}
}
//
// Save any final changes to new groups
//
if (newAttendanceList.Any())
{
SaveAttendance(newAttendanceList);
}
//
// Save any changes to existing groups
//
lookupContext.SaveChanges();
lookupContext.Dispose();
ReportProgress(0, string.Format("Finished attendance import: {0:N0} records added.", importedCount));
return(completed);
}
/// <summary>
/// Loads the individual data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadIndividuals(CSVInstance csvData)
{
var lookupContext = new RockContext();
var groupTypeRoleService = new GroupTypeRoleService(lookupContext);
var groupMemberService = new GroupMemberService(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;
int memberConnectionStatusId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_MEMBER)).Id;
int visitorConnectionStatusId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_VISITOR)).Id;
int attendeeConnectionStatusId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_ATTENDEE)).Id;
// Suffix type: Dr., Jr., II, etc
var suffixTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_SUFFIX), lookupContext).DefinedValues;
// Title type: Mr., Mrs. Dr., etc
var titleTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_TITLE), 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;
// Deceased record status reason (others available: No Activity, Moved, etc)
var recordStatusDeceasedId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_REASON_DECEASED)).Id;
// Record type: Person
int?personRecordTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON), lookupContext).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;
// Phone types: Home, Work, Mobile
var numberTypeValues = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE), lookupContext).DefinedValues;
// Personal note type id
var personalNoteTypeId = new NoteTypeService(lookupContext).Get(new Guid(Rock.SystemGuid.NoteType.PERSON_TIMELINE_NOTE)).Id;
// School defined type
var schoolDefinedType = DefinedTypeCache.Read(new Guid("576FF1E2-6225-4565-A16D-230E26167A3D"));
// Look up existing Person attributes
var personAttributes = new AttributeService(lookupContext).GetByEntityTypeId(PersonEntityTypeId).ToList();
var schoolAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "School"));
// Text field type id
int textFieldTypeId = FieldTypeCache.Read(new Guid(Rock.SystemGuid.FieldType.TEXT), lookupContext).Id;
int dateFieldTypeId = FieldTypeCache.Read(new Guid(Rock.SystemGuid.FieldType.DATE), lookupContext).Id;
// Attribute entity type id
int attributeEntityTypeId = EntityTypeCache.Read("Rock.Model.Attribute").Id;
// Visit info category
var visitInfoCategory = new CategoryService(lookupContext).GetByEntityTypeId(attributeEntityTypeId)
.Where(c => c.Name == "Visit Information").FirstOrDefault();
// Look for custom attributes in the Individual file
var allFields = csvData.TableNodes.FirstOrDefault().Children.Select((node, index) => new { node = node, index = index }).ToList();
Dictionary <int, string> customAttributes = allFields
.Where(f => f.index > SecurityNote)
.ToDictionary(f => f.index, f => f.node.Name.RemoveWhitespace());
// Add any attributes if they don't already exist
if (customAttributes.Any())
{
var newAttributes = new List <Rock.Model.Attribute>();
foreach (var newAttributePair in customAttributes.Where(ca => !personAttributes.Any(a => a.Key == ca.Value)))
{
var newAttribute = new Rock.Model.Attribute();
newAttribute.Name = newAttributePair.Value;
newAttribute.Key = newAttributePair.Value.RemoveWhitespace();
newAttribute.Description = newAttributePair.Value + " created by CSV import";
newAttribute.EntityTypeQualifierValue = string.Empty;
newAttribute.EntityTypeQualifierColumn = string.Empty;
newAttribute.EntityTypeId = PersonEntityTypeId;
newAttribute.FieldTypeId = textFieldTypeId;
newAttribute.DefaultValue = string.Empty;
newAttribute.IsMultiValue = false;
newAttribute.IsGridColumn = false;
newAttribute.IsRequired = false;
newAttribute.Order = 0;
newAttributes.Add(newAttribute);
}
lookupContext.Attributes.AddRange(newAttributes);
lookupContext.SaveChanges(DisableAuditing);
personAttributes.AddRange(newAttributes);
}
// Set the supported date formats
var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" };
var currentFamilyGroup = new Group();
var newFamilyList = new List <Group>();
var newVisitorList = new List <Group>();
var newNoteList = new List <Note>();
int completed = 0;
int newFamilies = 0;
int newPeople = 0;
ReportProgress(0, string.Format("Starting Individual import ({0:N0} already exist).", ImportedPeopleKeys.Count()));
string[] row;
row = csvData.Database.FirstOrDefault();
while (row != null)
{
int groupRoleId = adultRoleId;
bool isFamilyRelationship = true;
string rowFamilyName = row[FamilyName];
string rowFamilyKey = row[FamilyId];
string rowPersonKey = row[PersonId];
int? rowFamilyId = rowFamilyKey.AsType <int?>();
int? rowPersonId = rowPersonKey.AsType <int?>();
// Check that this person isn't already in our data
var personExists = ImportedFamilies.Any(g => g.Members.Any(m => m.Person.ForeignKey == rowPersonKey));
if (!personExists)
{
#region person create
var person = new Person();
person.ForeignKey = rowPersonKey;
person.ForeignId = rowPersonId;
person.SystemNote = string.Format("Imported via Excavator on {0}", ImportDateTime);
person.RecordTypeValueId = personRecordTypeId;
person.CreatedByPersonAliasId = ImportPersonAliasId;
string firstName = row[FirstName].Left(50);
string nickName = row[NickName].Left(50);
person.FirstName = firstName;
person.NickName = string.IsNullOrWhiteSpace(nickName) ? firstName : nickName;
person.MiddleName = row[MiddleName].Left(50);
person.LastName = row[LastName].Left(50);
DateTime createdDateValue;
if (DateTime.TryParseExact(row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue))
{
person.CreatedDateTime = createdDateValue;
person.ModifiedDateTime = ImportDateTime;
}
else
{
person.CreatedDateTime = ImportDateTime;
person.ModifiedDateTime = ImportDateTime;
}
DateTime birthDate;
if (DateTime.TryParseExact(row[DateOfBirth], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out birthDate))
{
person.BirthDay = birthDate.Day;
person.BirthMonth = birthDate.Month;
person.BirthYear = birthDate.Year;
}
DateTime graduationDate;
if (DateTime.TryParseExact(row[GraduationDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out graduationDate))
{
person.GraduationYear = graduationDate.Year;
}
DateTime anniversary;
if (DateTime.TryParseExact(row[Anniversary], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out anniversary))
{
person.AnniversaryDate = anniversary;
}
string gender = row[Gender];
if (gender != null)
{
switch (gender.Trim().ToLower())
{
case "m":
case "male":
person.Gender = Rock.Model.Gender.Male;
break;
case "f":
case "female":
person.Gender = Rock.Model.Gender.Female;
break;
default:
person.Gender = Rock.Model.Gender.Unknown;
break;
}
}
string prefix = row[Prefix];
if (!string.IsNullOrWhiteSpace(prefix))
{
prefix = prefix.RemoveSpecialCharacters().Trim();
person.TitleValueId = titleTypes.Where(s => prefix == s.Value.RemoveSpecialCharacters())
.Select(s => (int?)s.Id).FirstOrDefault();
}
string suffix = row[Suffix];
if (!string.IsNullOrWhiteSpace(suffix))
{
suffix = suffix.RemoveSpecialCharacters().Trim();
person.SuffixValueId = suffixTypes.Where(s => suffix == s.Value.RemoveSpecialCharacters())
.Select(s => (int?)s.Id).FirstOrDefault();
}
string maritalStatus = row[MaritalStatus];
if (!string.IsNullOrWhiteSpace(maritalStatus))
{
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[FamilyRole];
if (!string.IsNullOrWhiteSpace(familyRole))
{
if (familyRole == "Visitor")
{
isFamilyRelationship = false;
}
if (familyRole == "Child" || person.Age < 18)
{
groupRoleId = childRoleId;
}
}
string connectionStatus = row[ConnectionStatus];
if (!string.IsNullOrWhiteSpace(connectionStatus))
{
if (connectionStatus == "Member")
{
person.ConnectionStatusValueId = memberConnectionStatusId;
}
else if (connectionStatus == "Visitor")
{
person.ConnectionStatusValueId = visitorConnectionStatusId;
}
else
{
// look for user-defined connection type or default to Attendee
var customConnectionType = connectionStatusTypes.Where(dv => dv.Value == connectionStatus)
.Select(dv => (int?)dv.Id).FirstOrDefault();
person.ConnectionStatusValueId = customConnectionType ?? attendeeConnectionStatusId;
person.RecordStatusValueId = recordStatusActiveId;
}
}
string recordStatus = row[RecordStatus];
if (!string.IsNullOrWhiteSpace(recordStatus))
{
switch (recordStatus.Trim().ToLower())
{
case "active":
person.RecordStatusValueId = recordStatusActiveId;
break;
case "inactive":
person.RecordStatusValueId = recordStatusInactiveId;
break;
default:
person.RecordStatusValueId = recordStatusPendingId;
break;
}
}
string isDeceasedValue = row[IsDeceased];
if (!string.IsNullOrWhiteSpace(isDeceasedValue))
{
switch (isDeceasedValue.Trim().ToLower())
{
case "y":
case "yes":
person.IsDeceased = true;
person.RecordStatusReasonValueId = recordStatusDeceasedId;
person.RecordStatusValueId = recordStatusInactiveId;
break;
default:
person.IsDeceased = false;
break;
}
}
var personNumbers = new Dictionary <string, string>();
personNumbers.Add("Home", row[HomePhone]);
personNumbers.Add("Mobile", row[MobilePhone]);
personNumbers.Add("Work", row[WorkPhone]);
string smsAllowed = row[AllowSMS];
foreach (var numberPair in personNumbers.Where(n => !string.IsNullOrWhiteSpace(n.Value)))
{
var extension = string.Empty;
var countryCode = Rock.Model.PhoneNumber.DefaultCountryCode();
var normalizedNumber = string.Empty;
var countryIndex = numberPair.Value.IndexOf('+');
int extensionIndex = numberPair.Value.LastIndexOf('x') > 0 ? numberPair.Value.LastIndexOf('x') : numberPair.Value.Length;
if (countryIndex >= 0)
{
countryCode = numberPair.Value.Substring(countryIndex, countryIndex + 3).AsNumeric();
normalizedNumber = numberPair.Value.Substring(countryIndex + 3, extensionIndex - 3).AsNumeric().TrimStart(new Char[] { '0' });
extension = numberPair.Value.Substring(extensionIndex);
}
else if (extensionIndex > 0)
{
normalizedNumber = numberPair.Value.Substring(0, extensionIndex).AsNumeric();
extension = numberPair.Value.Substring(extensionIndex).AsNumeric();
}
else
{
normalizedNumber = numberPair.Value.AsNumeric();
}
if (!string.IsNullOrWhiteSpace(normalizedNumber))
{
var currentNumber = new PhoneNumber();
currentNumber.CountryCode = countryCode;
currentNumber.CreatedByPersonAliasId = ImportPersonAliasId;
currentNumber.Extension = extension.Left(20);
currentNumber.Number = normalizedNumber.TrimStart(new Char[] { '0' }).Left(20);
currentNumber.NumberFormatted = PhoneNumber.FormattedNumber(currentNumber.CountryCode, currentNumber.Number);
currentNumber.NumberTypeValueId = numberTypeValues.Where(v => v.Value.Equals(numberPair.Key))
.Select(v => ( int? )v.Id).FirstOrDefault();
if (numberPair.Key == "Mobile")
{
switch (smsAllowed.Trim().ToLower())
{
case "y":
case "yes":
case "active":
currentNumber.IsMessagingEnabled = true;
break;
default:
currentNumber.IsMessagingEnabled = false;
break;
}
}
person.PhoneNumbers.Add(currentNumber);
}
}
// Map Person attributes
person.Attributes = new Dictionary <string, AttributeCache>();
person.AttributeValues = new Dictionary <string, AttributeValueCache>();
bool isEmailActive;
switch (row[IsEmailActive].Trim().ToLower())
{
case "n":
case "no":
case "inactive":
isEmailActive = false;
break;
default:
isEmailActive = true;
break;
}
EmailPreference emailPreference;
switch (row[AllowBulkEmail].Trim().ToLower())
{
case "n":
case "no":
case "inactive":
emailPreference = EmailPreference.NoMassEmails;
break;
default:
emailPreference = EmailPreference.EmailAllowed;
break;
}
person.EmailPreference = emailPreference;
string primaryEmail = row[Email].Trim().Left(75);
if (!string.IsNullOrWhiteSpace(primaryEmail))
{
if (primaryEmail.IsEmail())
{
person.Email = primaryEmail;
person.IsEmailActive = isEmailActive;
}
else
{
LogException("InvalidPrimaryEmail", string.Format("PersonId: {0} - Email: {1}", rowPersonKey, primaryEmail));
}
}
string schoolName = row[School];
if (!string.IsNullOrWhiteSpace(schoolName))
{
// Add school if it doesn't exist
Guid schoolGuid;
var schoolExists = lookupContext.DefinedValues.Any(s => s.DefinedTypeId == schoolDefinedType.Id && s.Value.Equals(schoolName));
if (!schoolExists)
{
var newSchool = new DefinedValue();
newSchool.DefinedTypeId = schoolDefinedType.Id;
newSchool.Value = schoolName;
newSchool.Order = 0;
lookupContext.DefinedValues.Add(newSchool);
lookupContext.SaveChanges();
schoolGuid = newSchool.Guid;
}
else
{
schoolGuid = lookupContext.DefinedValues.FirstOrDefault(s => s.Value.Equals(schoolName)).Guid;
}
AddPersonAttribute(schoolAttribute, person, schoolGuid.ToString().ToUpper());
}
foreach (var attributePair in customAttributes)
{
string newAttributeValue = row[attributePair.Key];
if (!string.IsNullOrWhiteSpace(newAttributeValue))
{
// check if this attribute value is a date
DateTime valueAsDateTime;
if (DateTime.TryParseExact(newAttributeValue, dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out valueAsDateTime))
{
newAttributeValue = valueAsDateTime.ToString("yyyy-MM-dd");
}
int?newAttributeId = personAttributes.Where(a => a.Key == attributePair.Value.RemoveWhitespace())
.Select(a => (int?)a.Id).FirstOrDefault();
if (newAttributeId != null)
{
var newAttribute = AttributeCache.Read((int)newAttributeId);
AddPersonAttribute(newAttribute, person, newAttributeValue);
}
}
}
// Add notes to timeline
var notePairs = new Dictionary <string, string>();
notePairs.Add("General", row[GeneralNote]);
notePairs.Add("Medical", row[MedicalNote]);
notePairs.Add("Security", row[SecurityNote]);
foreach (var notePair in notePairs.Where(n => !string.IsNullOrWhiteSpace(n.Value)))
{
var newNote = new Note();
newNote.NoteTypeId = personalNoteTypeId;
newNote.CreatedByPersonAliasId = ImportPersonAliasId;
newNote.CreatedDateTime = ImportDateTime;
newNote.Text = notePair.Value;
newNote.ForeignKey = rowPersonKey;
newNote.ForeignId = rowPersonId;
newNote.Caption = string.Format("{0} Note", notePair.Key);
if (!notePair.Key.Equals("General"))
{
newNote.IsAlert = true;
}
newNoteList.Add(newNote);
}
#endregion person create
var groupMember = new GroupMember();
groupMember.Person = person;
groupMember.GroupRoleId = groupRoleId;
groupMember.CreatedDateTime = ImportDateTime;
groupMember.ModifiedDateTime = ImportDateTime;
groupMember.CreatedByPersonAliasId = ImportPersonAliasId;
groupMember.GroupMemberStatus = GroupMemberStatus.Active;
if (rowFamilyKey != currentFamilyGroup.ForeignKey)
{
// person not part of the previous family, see if that family exists or create a new one
currentFamilyGroup = ImportedFamilies.FirstOrDefault(g => g.ForeignKey == rowFamilyKey);
if (currentFamilyGroup == null)
{
currentFamilyGroup = CreateFamilyGroup(row[FamilyName], rowFamilyKey);
newFamilyList.Add(currentFamilyGroup);
newFamilies++;
}
else
{
lookupContext.Groups.Attach(currentFamilyGroup);
lookupContext.Entry(currentFamilyGroup).State = EntityState.Modified;
}
currentFamilyGroup.Members.Add(groupMember);
}
else
{
// person is part of this family group, check if they're a visitor
if (isFamilyRelationship || currentFamilyGroup.Members.Count() < 1)
{
currentFamilyGroup.Members.Add(groupMember);
}
else
{
var visitorFamily = CreateFamilyGroup(person.LastName + " Family", rowFamilyKey);
visitorFamily.Members.Add(groupMember);
newFamilyList.Add(visitorFamily);
newVisitorList.Add(visitorFamily);
newFamilies++;
}
}
// look ahead 1 row
string rowNextFamilyKey = "-1";
if ((row = csvData.Database.FirstOrDefault()) != null)
{
rowNextFamilyKey = row[FamilyId];
}
newPeople++;
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} people imported.", completed));
}
if (newPeople >= ReportingNumber && rowNextFamilyKey != currentFamilyGroup.ForeignKey)
{
SaveIndividuals(newFamilyList, newVisitorList, newNoteList);
lookupContext.SaveChanges();
ReportPartialProgress();
// Clear out variables
currentFamilyGroup = new Group();
newFamilyList.Clear();
newVisitorList.Clear();
newNoteList.Clear();
newPeople = 0;
}
}
else
{
row = csvData.Database.FirstOrDefault();
}
}
// Save any changes to new families
if (newFamilyList.Any())
{
SaveIndividuals(newFamilyList, newVisitorList, newNoteList);
}
// Save any changes to existing families
lookupContext.SaveChanges();
DetachAllInContext(lookupContext);
lookupContext.Dispose();
ReportProgress(0, string.Format("Finished individual import: {0:N0} families and {1:N0} people added.", newFamilies, completed));
return(completed);
}
/// <summary>
/// Maps the specified folder.
/// </summary>
/// <param name="folder">The folder.</param>
/// <param name="personImageType">Type of the person image file.</param>
public void Map(ZipArchive folder, BinaryFileType personImageType)
{
// check for existing images
var lookupContext = new RockContext();
var existingImageList = new PersonService(lookupContext).Queryable().AsNoTracking()
.Where(p => p.Photo != null)
.ToDictionary(p => p.Id, p => p.Photo.CreatedDateTime);
var emptyJsonObject = "{}";
var newFileList = new Dictionary <int, Rock.Model.BinaryFile>();
var storageProvider = personImageType.StorageEntityTypeId == DatabaseProvider.EntityType.Id
? (ProviderComponent)DatabaseProvider
: (ProviderComponent)FileSystemProvider;
var completedItems = 0;
var totalEntries = folder.Entries.Count;
var percentage = (totalEntries - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying person images import ({0:N0} found.", totalEntries));
foreach (var file in folder.Entries)
{
var fileExtension = Path.GetExtension(file.Name);
if (FileTypeBlackList.Contains(fileExtension))
{
LogException("Binary File Import", string.Format("{0} filetype not allowed ({1})", fileExtension, file.Name));
continue;
}
var personForeignId = Path.GetFileNameWithoutExtension(file.Name).AsType <int?>();
var personKeys = ImportedPeople.FirstOrDefault(p => p.PersonForeignId == personForeignId);
if (personKeys != null)
{
// only import the most recent profile photo
if (!existingImageList.ContainsKey(personKeys.PersonId) || existingImageList[personKeys.PersonId].Value < file.LastWriteTime.DateTime)
{
var rockFile = new Rock.Model.BinaryFile
{
IsSystem = false,
IsTemporary = false,
FileName = file.Name,
BinaryFileTypeId = personImageType.Id,
MimeType = GetMIMEType(file.Name),
CreatedDateTime = file.LastWriteTime.DateTime,
Description = string.Format("Imported as {0}", file.Name)
};
rockFile.SetStorageEntityTypeId(personImageType.StorageEntityTypeId);
rockFile.StorageEntitySettings = emptyJsonObject;
if (personImageType.AttributeValues.Any())
{
rockFile.StorageEntitySettings = personImageType.AttributeValues
.ToDictionary(a => a.Key, v => v.Value.Value).ToJson();
}
// use base stream instead of file stream to keep the byte[]
// NOTE: if byte[] converts to a string it will corrupt the stream
using (var fileContent = new StreamReader(file.Open()))
{
rockFile.ContentStream = new MemoryStream(fileContent.BaseStream.ReadBytesToEnd());
}
newFileList.Add(personKeys.PersonId, rockFile);
}
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} person image files imported ({1}% complete).", completedItems, percentComplete));
}
else if (completedItems % ReportingNumber < 1)
{
SaveFiles(newFileList, storageProvider);
// add image keys to master list
foreach (var newFile in newFileList)
{
existingImageList.AddOrReplace(newFile.Key, newFile.Value.CreatedDateTime);
}
// Reset batch list
newFileList.Clear();
ReportPartialProgress();
}
}
}
if (newFileList.Any())
{
SaveFiles(newFileList, storageProvider);
}
lookupContext.Dispose();
ReportProgress(100, string.Format("Finished files import: {0:N0} person images imported.", completedItems));
}
/// <summary>
/// Loads the individual data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadIndividuals(CSVInstance csvData)
{
var lookupContext = new RockContext();
// 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;
// Suffix types: Dr., Jr., II, etc
var suffixTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_SUFFIX), lookupContext).DefinedValues;
// Title types: Mr., Mrs. Dr., etc
var titleTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_TITLE), lookupContext).DefinedValues;
// Group roles: Owner, Adult, Child, others
var familyRoles = GroupTypeCache.Read(new Guid(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY), lookupContext).Roles;
// Phone types: Home, Work, Mobile
var numberTypeValues = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE), lookupContext).DefinedValues;
// School Person attribute
var schoolAttribute = FindEntityAttribute(lookupContext, "Education", "School", PersonEntityTypeId);
// Visit info category
var visitInfoCategory = new CategoryService(lookupContext).GetByEntityTypeId(AttributeEntityTypeId)
.Where(c => c.Name == "Visit Information").FirstOrDefault();
// 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 > SecurityNote)
.ToDictionary(f => f.index, f => f.node.Name);
var personAttributes = new List <Rock.Model.Attribute>();
// Add any attributes if they don't already exist
if (customAttributes.Any())
{
foreach (var avp in customAttributes.Where(ca => !personAttributes.Any(a => a.Name.Equals(ca.Value, StringComparison.InvariantCultureIgnoreCase))))
{
var newAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, string.Empty,
string.Empty, avp.Value, string.Empty, TextFieldTypeId, true, null, null, ImportPersonAliasId
);
personAttributes.Add(newAttribute);
}
}
var currentFamilyGroup = new Group();
var newFamilyList = new List <Group>();
var newVisitorList = new List <Group>();
var newNoteList = new List <Note>();
var completed = 0;
var newFamilies = 0;
var newPeople = 0;
ReportProgress(0, string.Format("Starting Individual import ({0:N0} already exist).", ImportedPeopleKeys.Count()));
string[] row;
row = csvData.Database.FirstOrDefault();
while (row != null)
{
int?groupRoleId = null;
var isFamilyRelationship = true;
var rowFamilyName = row[FamilyName];
var rowFamilyKey = row[FamilyId];
var rowPersonKey = row[PersonId];
var rowFamilyId = rowFamilyKey.AsType <int?>();
var rowPersonId = rowPersonKey.AsType <int?>();
// Check that this person isn't already in our data
var newPerson = true;
if (ImportedPeopleKeys.Count() > 0)
{
var personKeys = GetPersonKeys(rowPersonKey);
if (personKeys != null)
{
newPerson = false;
}
}
if (newPerson)
{
#region person create
var person = new Person
{
ForeignKey = rowPersonKey,
ForeignId = rowPersonId,
SystemNote = string.Format("Imported via Excavator on {0}", ImportDateTime),
RecordTypeValueId = PersonRecordTypeId,
CreatedByPersonAliasId = ImportPersonAliasId
};
var firstName = row[FirstName].Left(50);
var nickName = row[NickName].Left(50);
person.FirstName = firstName;
person.NickName = string.IsNullOrWhiteSpace(nickName) ? firstName : nickName;
person.MiddleName = row[MiddleName].Left(50);
person.LastName = row[LastName].Left(50);
var createdDateValue = ParseDateOrDefault(row[CreatedDate], null);
if (createdDateValue.HasValue)
{
person.CreatedDateTime = createdDateValue;
person.ModifiedDateTime = ImportDateTime;
}
else
{
person.CreatedDateTime = ImportDateTime;
person.ModifiedDateTime = ImportDateTime;
}
var birthDate = ParseDateOrDefault(row[DateOfBirth], null);
if (birthDate.HasValue)
{
person.BirthDay = ((DateTime)birthDate).Day;
person.BirthMonth = ((DateTime)birthDate).Month;
person.BirthYear = ((DateTime)birthDate).Year;
}
var graduationDate = ParseDateOrDefault(row[GraduationDate], null);
if (graduationDate.HasValue)
{
person.GraduationYear = ((DateTime)graduationDate).Year;
}
var anniversary = ParseDateOrDefault(row[Anniversary], null);
if (anniversary.HasValue)
{
person.AnniversaryDate = anniversary;
}
var gender = row[Gender];
if (gender != null)
{
switch (gender.Trim().ToLower())
{
case "m":
case "male":
person.Gender = Rock.Model.Gender.Male;
break;
case "f":
case "female":
person.Gender = Rock.Model.Gender.Female;
break;
default:
person.Gender = Rock.Model.Gender.Unknown;
break;
}
}
var prefix = row[Prefix];
if (!string.IsNullOrWhiteSpace(prefix))
{
prefix = prefix.RemoveSpecialCharacters();
person.TitleValueId = titleTypes.Where(s => prefix.Equals(s.Value.RemoveSpecialCharacters(), StringComparison.CurrentCultureIgnoreCase))
.Select(s => (int?)s.Id).FirstOrDefault();
if (!person.TitleValueId.HasValue)
{
var newTitle = AddDefinedValue(lookupContext, Rock.SystemGuid.DefinedType.PERSON_TITLE, prefix);
if (newTitle != null)
{
titleTypes.Add(newTitle);
person.TitleValueId = newTitle.Id;
}
}
}
var suffix = row[Suffix];
if (!string.IsNullOrWhiteSpace(suffix))
{
suffix = suffix.RemoveSpecialCharacters();
person.SuffixValueId = suffixTypes.Where(s => suffix.Equals(s.Value.RemoveSpecialCharacters(), StringComparison.CurrentCultureIgnoreCase))
.Select(s => (int?)s.Id).FirstOrDefault();
if (!person.SuffixValueId.HasValue)
{
var newSuffix = AddDefinedValue(lookupContext, Rock.SystemGuid.DefinedType.PERSON_SUFFIX, suffix);
if (newSuffix != null)
{
suffixTypes.Add(newSuffix);
person.SuffixValueId = newSuffix.Id;
}
}
}
var maritalStatus = row[MaritalStatus];
if (!string.IsNullOrWhiteSpace(maritalStatus))
{
maritalStatus = maritalStatus.RemoveSpecialCharacters();
person.MaritalStatusValueId = maritalStatusTypes.Where(s => maritalStatus.Equals(s.Value.RemoveSpecialCharacters(), StringComparison.CurrentCultureIgnoreCase))
.Select(dv => (int?)dv.Id).FirstOrDefault();
if (!person.MaritalStatusValueId.HasValue)
{
var newMaritalStatus = AddDefinedValue(lookupContext, Rock.SystemGuid.DefinedType.PERSON_MARITAL_STATUS, maritalStatus);
if (newMaritalStatus != null)
{
maritalStatusTypes.Add(newMaritalStatus);
person.MaritalStatusValueId = newMaritalStatus.Id;
}
}
}
if (person.MaritalStatusValueId == null)
{
person.MaritalStatusValueId = maritalStatusTypes.Where(dv => dv.Value.Equals("Unknown", StringComparison.CurrentCultureIgnoreCase))
.Select(dv => (int?)dv.Id).FirstOrDefault();
}
var familyRole = row[FamilyRole];
if (!string.IsNullOrWhiteSpace(familyRole))
{
familyRole = familyRole.RemoveSpecialCharacters().Trim();
groupRoleId = familyRoles.Where(dv => string.Equals(dv.Name, familyRole, StringComparison.CurrentCultureIgnoreCase))
.Select(dv => (int?)dv.Id).FirstOrDefault();
if (!groupRoleId.HasValue)
{
AddGroupRole(lookupContext, Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY, familyRole);
familyRoles = GroupTypeCache.Read(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY).Roles;
groupRoleId = familyRoles.Where(dv => dv.Name == familyRole)
.Select(dv => (int?)dv.Id).FirstOrDefault();
}
if (familyRole.Equals("Visitor", StringComparison.CurrentCultureIgnoreCase))
{
isFamilyRelationship = false;
}
}
if (groupRoleId == null)
{
groupRoleId = FamilyAdultRoleId;
}
var recordStatus = row[RecordStatus];
if (!string.IsNullOrWhiteSpace(recordStatus))
{
switch (recordStatus.Trim().ToLower())
{
case "active":
person.RecordStatusValueId = ActivePersonRecordStatusId;
break;
case "inactive":
person.RecordStatusValueId = InactivePersonRecordStatusId;
break;
default:
person.RecordStatusValueId = PendingPersonRecordStatusId;
break;
}
}
else
{
person.RecordStatusValueId = ActivePersonRecordStatusId;
}
var connectionStatus = row[ConnectionStatus];
if (!string.IsNullOrWhiteSpace(connectionStatus))
{
if (connectionStatus.Equals("Member", StringComparison.CurrentCultureIgnoreCase))
{
person.ConnectionStatusValueId = MemberConnectionStatusId;
}
else if (connectionStatus.Equals("Visitor", StringComparison.CurrentCultureIgnoreCase))
{
person.ConnectionStatusValueId = VisitorConnectionStatusId;
}
else if (connectionStatus.Equals("Business", StringComparison.CurrentCultureIgnoreCase))
{
person.RecordTypeValueId = BusinessRecordTypeId;
}
else if (connectionStatus.Equals("Inactive", StringComparison.CurrentCultureIgnoreCase))
{
person.RecordStatusValueId = InactivePersonRecordStatusId;
}
else
{
// create user-defined connection type if it doesn't exist
person.ConnectionStatusValueId = connectionStatusTypes.Where(dv => dv.Value.Equals(connectionStatus, StringComparison.CurrentCultureIgnoreCase))
.Select(dv => (int?)dv.Id).FirstOrDefault();
if (!person.ConnectionStatusValueId.HasValue)
{
var newConnectionStatus = AddDefinedValue(lookupContext, Rock.SystemGuid.DefinedType.PERSON_CONNECTION_STATUS, connectionStatus);
if (newConnectionStatus != null)
{
connectionStatusTypes.Add(newConnectionStatus);
person.ConnectionStatusValueId = newConnectionStatus.Id;
}
}
}
}
else
{
person.ConnectionStatusValueId = VisitorConnectionStatusId;
}
var isDeceasedValue = row[IsDeceased];
if (!string.IsNullOrWhiteSpace(isDeceasedValue))
{
switch (isDeceasedValue.Trim().ToLower())
{
case "y":
case "yes":
case "true":
person.IsDeceased = true;
person.RecordStatusReasonValueId = DeceasedPersonRecordReasonId;
person.RecordStatusValueId = InactivePersonRecordStatusId;
break;
default:
person.IsDeceased = false;
break;
}
}
var personNumbers = new Dictionary <string, string>();
personNumbers.Add("Home", row[HomePhone]);
personNumbers.Add("Mobile", row[MobilePhone]);
personNumbers.Add("Work", row[WorkPhone]);
var smsAllowed = row[AllowSMS];
foreach (var numberPair in personNumbers.Where(n => !string.IsNullOrWhiteSpace(n.Value) && n.Value.AsNumeric().AsType <Int64>() > 0))
{
var extension = string.Empty;
var countryCode = PhoneNumber.DefaultCountryCode();
var normalizedNumber = string.Empty;
var countryIndex = numberPair.Value.IndexOf('+');
var extensionIndex = numberPair.Value.LastIndexOf('x') > 0 ? numberPair.Value.LastIndexOf('x') : numberPair.Value.Length;
if (countryIndex >= 0)
{
countryCode = numberPair.Value.Substring(countryIndex, countryIndex + 3);
normalizedNumber = numberPair.Value.Substring(countryIndex + 3, extensionIndex - 3).AsNumeric().TrimStart(new Char[] { '0' });
extension = numberPair.Value.Substring(extensionIndex);
}
else if (extensionIndex > 0)
{
normalizedNumber = numberPair.Value.Substring(0, extensionIndex).AsNumeric();
extension = numberPair.Value.Substring(extensionIndex).AsNumeric();
}
else
{
normalizedNumber = numberPair.Value.AsNumeric();
}
if (!string.IsNullOrWhiteSpace(normalizedNumber))
{
var currentNumber = new PhoneNumber();
currentNumber.CountryCode = countryCode;
currentNumber.CreatedByPersonAliasId = ImportPersonAliasId;
currentNumber.Extension = extension.Left(20);
currentNumber.Number = normalizedNumber.TrimStart(new char[] { '0' }).Left(20);
currentNumber.NumberFormatted = PhoneNumber.FormattedNumber(currentNumber.CountryCode, currentNumber.Number);
currentNumber.NumberTypeValueId = numberTypeValues.Where(v => v.Value.Equals(numberPair.Key, StringComparison.CurrentCultureIgnoreCase))
.Select(v => (int?)v.Id).FirstOrDefault();
if (numberPair.Key == "Mobile")
{
switch (smsAllowed.Trim().ToLower())
{
case "y":
case "yes":
case "active":
case "true":
currentNumber.IsMessagingEnabled = true;
break;
default:
currentNumber.IsMessagingEnabled = false;
break;
}
}
person.PhoneNumbers.Add(currentNumber);
}
}
// Map Person attributes
person.Attributes = new Dictionary <string, AttributeCache>();
person.AttributeValues = new Dictionary <string, AttributeValueCache>();
bool isEmailActive;
switch (row[IsEmailActive].Trim().ToLower())
{
case "n":
case "no":
case "inactive":
isEmailActive = false;
break;
default:
isEmailActive = true;
break;
}
EmailPreference emailPreference;
switch (row[AllowBulkEmail].Trim().ToLower())
{
case "n":
case "no":
case "inactive":
emailPreference = EmailPreference.NoMassEmails;
break;
default:
emailPreference = EmailPreference.EmailAllowed;
break;
}
person.EmailPreference = emailPreference;
var primaryEmail = row[Email].Trim().Left(75);
if (!string.IsNullOrWhiteSpace(primaryEmail))
{
if (primaryEmail.IsEmail())
{
person.Email = primaryEmail;
person.IsEmailActive = isEmailActive;
}
else
{
LogException("InvalidPrimaryEmail", string.Format("PersonId: {0} - Email: {1}", rowPersonKey, primaryEmail));
}
}
var schoolName = row[School];
if (!string.IsNullOrWhiteSpace(schoolName))
{
AddEntityAttributeValue(lookupContext, schoolAttribute, person, schoolName, null, true);
}
foreach (var attributePair in customAttributes)
{
string newAttributeValue = row[attributePair.Key];
if (!string.IsNullOrWhiteSpace(newAttributeValue))
{
// check if this attribute value is a date
var valueAsDateTime = ParseDateOrDefault(newAttributeValue, null);
if (valueAsDateTime.HasValue)
{
newAttributeValue = ((DateTime)valueAsDateTime).ToString("yyyy-MM-dd");
}
var newAttribute = personAttributes.Where(a => a.Key == attributePair.Value.RemoveWhitespace())
.FirstOrDefault();
if (newAttribute != null)
{
AddEntityAttributeValue(lookupContext, newAttribute, person, newAttributeValue, null, false);
}
}
}
// Add notes to timeline
var notePairs = new Dictionary <string, string>
{
{ "General", row[GeneralNote] },
{ "Medical", row[MedicalNote] },
{ "Security", row[SecurityNote] }
};
foreach (var notePair in notePairs.Where(n => !string.IsNullOrWhiteSpace(n.Value)))
{
var splitNotePair = notePair.Value.Split('^');
foreach (string noteValue in splitNotePair)
{
var newNote = new Note
{
NoteTypeId = PersonalNoteTypeId,
CreatedByPersonAliasId = ImportPersonAliasId,
CreatedDateTime = ImportDateTime,
Text = noteValue,
ForeignKey = rowPersonKey,
ForeignId = rowPersonId,
Caption = string.Format("{0} Note", notePair.Key)
};
if (noteValue.StartsWith("[ALERT]", StringComparison.CurrentCultureIgnoreCase))
{
newNote.IsAlert = true;
}
if (notePair.Key.Equals("Security"))
{
// Pastoral note type id
var securityNoteType = new NoteTypeService(lookupContext).Get(PersonEntityTypeId, "Secure Note", true);
if (securityNoteType != null)
{
newNote.NoteTypeId = securityNoteType.Id;
}
}
if (notePair.Key.Equals("Medical"))
{
newNote.IsAlert = true;
}
newNoteList.Add(newNote);
}
}
#endregion person create
var groupMember = new GroupMember
{
Person = person,
GroupRoleId = (int)groupRoleId,
CreatedDateTime = ImportDateTime,
ModifiedDateTime = ImportDateTime,
CreatedByPersonAliasId = ImportPersonAliasId,
GroupMemberStatus = GroupMemberStatus.Active
};
if (rowFamilyKey != currentFamilyGroup.ForeignKey)
{
// person not part of the previous family, see if that family exists or create a new one
currentFamilyGroup = ImportedFamilies.FirstOrDefault(g => g.ForeignKey == rowFamilyKey);
if (currentFamilyGroup == null)
{
currentFamilyGroup = CreateFamilyGroup(row[FamilyName], rowFamilyKey);
newFamilyList.Add(currentFamilyGroup);
newFamilies++;
}
else
{
lookupContext.Groups.Attach(currentFamilyGroup);
lookupContext.Entry(currentFamilyGroup).State = EntityState.Modified;
}
currentFamilyGroup.Members.Add(groupMember);
}
else
{
// person is part of this family group, check if they're a visitor
if (isFamilyRelationship || currentFamilyGroup.Members.Count() < 1)
{
currentFamilyGroup.Members.Add(groupMember);
}
else
{
var visitorFamily = CreateFamilyGroup(person.LastName + " Family", rowFamilyKey);
visitorFamily.Members.Add(groupMember);
newFamilyList.Add(visitorFamily);
newVisitorList.Add(visitorFamily);
newFamilies++;
}
}
// look ahead 1 row
var rowNextFamilyKey = "-1";
if ((row = csvData.Database.FirstOrDefault()) != null)
{
rowNextFamilyKey = row[FamilyId];
}
newPeople++;
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} people processed.", completed));
}
if (newPeople >= ReportingNumber && rowNextFamilyKey != currentFamilyGroup.ForeignKey)
{
SaveIndividuals(newFamilyList, newVisitorList, newNoteList);
lookupContext.SaveChanges();
ReportPartialProgress();
// Clear out variables
currentFamilyGroup = new Group();
newFamilyList.Clear();
newVisitorList.Clear();
newNoteList.Clear();
newPeople = 0;
}
}
else
{
row = csvData.Database.FirstOrDefault();
}
}
// Save any changes to new families
if (newFamilyList.Any())
{
SaveIndividuals(newFamilyList, newVisitorList, newNoteList);
}
// Save any changes to existing families
lookupContext.SaveChanges();
DetachAllInContext(lookupContext);
lookupContext.Dispose();
ReportProgress(0, string.Format("Finished individual import: {0:N0} families and {1:N0} people added.", newFamilies, completed));
return(completed);
}
/// <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>
/// 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 Entity Attribute data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadEntityAttributeValues(CSVInstance csvData)
{
var lookupContext = new RockContext();
var importedAttributeValues = new AttributeValueService(lookupContext).Queryable().Count(a => a.ForeignKey != null);
var entityTypes = EntityTypeCache.All().Where(e => e.IsEntity && e.IsSecured).ToList();
var attributeService = new AttributeService(lookupContext);
var attributeValues = new List <AttributeValue>();
var completedItems = 0;
var addedItems = 0;
int? entityTypeId = null;
var prevEntityTypeName = string.Empty;
var prevAttributeForeignKey = string.Empty;
var prevRockKey = string.Empty;
Attribute attribute = null;
Type contextModelType = null;
System.Data.Entity.DbContext contextDbContext = null;
IService contextService = null;
IHasAttributes entity = null;
var prevAttributeValueEntityId = string.Empty;
ReportProgress(0, string.Format("Verifying attribute value import ({0:N0} already imported).", importedAttributeValues));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
var entityTypeName = row[AttributeEntityTypeName];
var attributeForeignKey = row[AttributeId];
var rockKey = row[AttributeRockKey];
var attributeValueForeignKey = row[AttributeValueId];
var attributeValueEntityId = row[AttributeValueEntityId];
var attributeValue = row[AttributeValue];
if (!string.IsNullOrWhiteSpace(entityTypeName) &&
!string.IsNullOrWhiteSpace(attributeValueEntityId) &&
!string.IsNullOrWhiteSpace(attributeValue) &&
(!string.IsNullOrEmpty(attributeForeignKey) || !string.IsNullOrEmpty(rockKey)))
{
var findNewEntity = false;
if (!entityTypeId.HasValue || !prevEntityTypeName.Equals(entityTypeName, StringComparison.OrdinalIgnoreCase))
{
entityTypeId = entityTypes.FirstOrDefault(et => et.Name.Equals(entityTypeName)).Id;
prevEntityTypeName = entityTypeName;
findNewEntity = true;
contextModelType = entityTypes.FirstOrDefault(et => et.Name.Equals(entityTypeName)).GetEntityType();
contextDbContext = Reflection.GetDbContextForEntityType(contextModelType);
if (contextDbContext != null)
{
contextService = Reflection.GetServiceForEntityType(contextModelType, contextDbContext);
}
}
if (entityTypeId.HasValue && contextService != null)
{
if (!string.IsNullOrWhiteSpace(attributeForeignKey) && !prevAttributeForeignKey.Equals(attributeForeignKey, StringComparison.OrdinalIgnoreCase))
{
attribute = attributeService.GetByEntityTypeId(entityTypeId).FirstOrDefault(a => a.ForeignKey == attributeForeignKey);
prevAttributeForeignKey = attributeForeignKey;
prevRockKey = string.Empty;
}
else if (string.IsNullOrWhiteSpace(attributeForeignKey))
{
// if no FK provided force attribute to null so the rockKey is tested
attribute = null;
}
if (attribute == null && !string.IsNullOrWhiteSpace(rockKey) && !prevRockKey.Equals(rockKey, StringComparison.OrdinalIgnoreCase))
{
attribute = attributeService.GetByEntityTypeId(entityTypeId).FirstOrDefault(a => a.Key == rockKey);
prevRockKey = rockKey;
prevAttributeForeignKey = string.Empty;
}
if (attribute != null)
{
// set the fk if it wasn't for some reason
if (string.IsNullOrWhiteSpace(attribute.ForeignKey) && !string.IsNullOrWhiteSpace(attributeForeignKey))
{
var updatedAttributeRockContext = new RockContext();
var updatedAttributeService = new AttributeService(updatedAttributeRockContext);
var updatedAttribute = updatedAttributeService.GetByEntityTypeId(entityTypeId).FirstOrDefault(a => a.Id == attribute.Id);
updatedAttribute.ForeignKey = attributeForeignKey;
updatedAttribute.ForeignId = attributeForeignKey.AsIntegerOrNull();
updatedAttributeRockContext.SaveChanges(DisableAuditing);
}
if (entity == null || (findNewEntity || !prevAttributeValueEntityId.Equals(attributeValueEntityId, StringComparison.OrdinalIgnoreCase)))
{
MethodInfo qryMethod = contextService.GetType().GetMethod("Queryable", new Type[] { });
var entityQry = qryMethod.Invoke(contextService, new object[] { }) as IQueryable <IEntity>;
var entityResult = entityQry.Where(e => e.ForeignKey.Equals(attributeValueEntityId, StringComparison.OrdinalIgnoreCase));
entity = entityResult.FirstOrDefault() as IHasAttributes;
prevAttributeValueEntityId = attributeValueEntityId;
}
if (entity != null)
{
var av = CreateEntityAttributeValue(lookupContext, attribute, entity, attributeValue, attributeValueForeignKey);
if (av != null && !attributeValues.Where(e => e.EntityId == av.EntityId).Where(a => a.AttributeId == av.AttributeId).Any())
{
attributeValues.Add(av);
addedItems++;
}
}
}
}
}
completedItems++;
if (completedItems % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} attribute values processed.", completedItems));
}
if (completedItems % ReportingNumber < 1)
{
SaveAttributeValues(lookupContext, attributeValues);
attributeValues.Clear();
lookupContext.Dispose();
lookupContext = new RockContext();
attributeService = new AttributeService(lookupContext);
ReportPartialProgress();
}
}
if (attributeValues.Any())
{
SaveAttributeValues(lookupContext, attributeValues);
}
ReportProgress(100, string.Format("Finished attribute value import: {0:N0} attribute values imported.", addedItems));
return(completedItems);
}
/// <summary>
/// Loads the group membership data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadRelationshipGroupMember( CSVInstance csvData )
{
AddMissingRelationshipGroups();
var lookupContext = new RockContext();
var groupTypeRoleService = new GroupTypeRoleService( lookupContext );
var groupMemberService = new GroupMemberService( lookupContext );
var newMemberList = new List<GroupMember>();
int completed = 0;
int imported = 0;
ReportProgress( 0, "Starting relationship import." );
var knownRelationshipGroupType = CachedTypes.KnownRelationshipGroupType;
if ( knownRelationshipGroupType != null )
{
var relationshipGroupTypeRoles = groupTypeRoleService
.Queryable().AsNoTracking()
.Where( r => r.GroupTypeId == knownRelationshipGroupType.Id )
.ToDictionary( r => r.Name, r => r.Id );
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ( ( row = csvData.Database.FirstOrDefault() ) != null )
{
string rowGroupMemberKey = row[GroupMemberId];
string rowGroupKey = row[GroupMemberGroupId];
string rowPersonKey = row[GroupMemberPersonId];
string rowCreatedDate = row[GroupMemberCreatedDate];
string rowMemberRole = row[GroupMemberRole];
string rowMemberActive = row[GroupMemberActive];
int? rowGroupMemberId = rowGroupMemberKey.AsType<int?>();
//
// Find Owner
//
var ownerKeys = GetPersonKeys( rowGroupKey );
if ( ownerKeys == null || ownerKeys.PersonId == 0 )
{
LogException( "InvalidGroupKey", string.Format( "Owner person key {0} not found", rowGroupKey ) );
ReportProgress( 0, string.Format( "Owner person key {0} not found", rowGroupKey ) );
}
//
// Find this person in the database.
//
var personKeys = GetPersonKeys( rowPersonKey );
if ( personKeys == null || personKeys.PersonId == 0 )
{
LogException( "InvalidPersonKey", string.Format( "Person key {0} not found", rowPersonKey ) );
ReportProgress( 0, string.Format( "Person key {0} not found", rowPersonKey ) );
}
if ( ownerKeys != null && ownerKeys.PersonId != 0 )
{
var knownRelationshipGroup = new GroupMemberService( lookupContext ).Queryable( true )
.AsNoTracking()
.Where(
m => m.PersonId == ownerKeys.PersonId
&& m.GroupRoleId == CachedTypes.KnownRelationshipOwnerRoleId
&& m.Group.GroupTypeId == knownRelationshipGroupType.Id
)
.Select( m => m.Group )
.FirstOrDefault();
if ( knownRelationshipGroup != null && knownRelationshipGroup.Id > 0 )
{
if ( personKeys != null && personKeys.PersonId != 0 )
{
GroupMember groupMember = new GroupMember();
groupMember.PersonId = personKeys.PersonId;
groupMember.GroupId = knownRelationshipGroup.Id;
groupMember.CreatedDateTime = ParseDateOrDefault( rowCreatedDate, ImportDateTime );
groupMember.ModifiedDateTime = ImportDateTime;
groupMember.CreatedByPersonAliasId = ImportPersonAliasId;
groupMember.ForeignKey = rowGroupMemberKey;
groupMember.ForeignId = rowGroupMemberId;
groupMember.GroupMemberStatus = GetGroupMemberStatus( rowMemberActive );
//
// Find and set the group role id.
//
if ( !string.IsNullOrEmpty( rowMemberRole ) )
{
if ( relationshipGroupTypeRoles.ContainsKey( rowMemberRole ) )
{
groupMember.GroupRoleId = relationshipGroupTypeRoles[rowMemberRole];
}
else
{
var newRoleId = AddGroupRole( lookupContext, knownRelationshipGroupType.Guid.ToString(), rowMemberRole );
relationshipGroupTypeRoles.Add( rowMemberRole, newRoleId );
groupMember.GroupRoleId = newRoleId;
}
}
else
{
if ( knownRelationshipGroupType.DefaultGroupRoleId != null )
{
groupMember.GroupRoleId = ( int ) knownRelationshipGroupType.DefaultGroupRoleId;
}
else
{
groupMember.GroupRoleId = knownRelationshipGroupType.Roles.First().Id;
}
}
//
// Add member to the group.
//
knownRelationshipGroup.Members.Add( groupMember );
newMemberList.Add( groupMember );
imported++;
}
else
{
LogException( "InvalidPersonKey", string.Format( "Person with Foreign Id {0} not found", rowPersonKey ) );
}
}
else
{
LogException( "InvalidGroupKey", string.Format( "Relationship Group with Owner Person Foreign Id {0} not found", rowGroupKey ) );
}
}
else
{
LogException( "InvalidGroupKey", string.Format( "Relationship Group Owner with Person Foreign Id {0} not found", rowGroupKey ) );
}
//
// Notify user of our status.
//
completed++;
if ( completed % ( ReportingNumber * 10 ) < 1 )
{
ReportProgress( 0, string.Format( "{0:N0} rows processed, {1:N0} relationships imported.", completed, imported ) );
}
if ( completed % ReportingNumber < 1 )
{
SaveGroupMembers( newMemberList );
lookupContext.SaveChanges();
ReportPartialProgress();
// Clear out variables
newMemberList.Clear();
}
}
}
else
{
ReportProgress( 0, "Known Relationship Group Type Missing!" );
}
//
// Save any final changes to new groups
//
if ( newMemberList.Any() )
{
SaveGroupMembers( newMemberList );
}
//
// Save any changes to existing groups
//
lookupContext.SaveChanges();
lookupContext.Dispose();
ReportProgress( 0, string.Format( "Finished relationship import: {0:N0} relationships added.", imported ) );
return completed;
}
/// <summary>
/// Loads the UserLogin data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadUserLogin(CSVInstance csvData)
{
var lookupContext = new RockContext();
var userLoginService = new UserLoginService(lookupContext);
var newUserLoginList = new List <UserLogin>();
var existingUserLoginList = new List <UserLogin>();
existingUserLoginList.AddRange(userLoginService.Queryable().AsNoTracking().Where(a => a.ForeignId == null));
int completed = 0;
int importedCount = 0;
int alreadyImportedCount = userLoginService.Queryable().AsNoTracking().Count(a => a.ForeignKey != null);
ReportProgress(0, string.Format("Starting user login import ({0:N0} already exist).", alreadyImportedCount));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
string rowUserLoginId = row[UserLoginId];
string rowUserLoginPersonId = row[UserLoginPersonId];
string rowUserLoginUserName = row[UserLoginUserName];
string rowUserLoginPassword = row[UserLoginPassword];
string rowUserLoginDateCreated = row[UserLoginDateCreated];
string rowUserLoginAuthenticationType = row[UserLoginAuthenticationType];
string rowUserLoginIsConfirmed = row[UserLoginIsConfirmed];
int?rowLoginId = rowUserLoginId.AsType <int?>();
int authenticationTypeId = EntityTypeCache.Get(rowUserLoginAuthenticationType).Id;
//
// Find this person in the database.
//
var personKeys = GetPersonKeys(rowUserLoginPersonId);
if (personKeys == null || personKeys.PersonId == 0)
{
throw new System.Collections.Generic.KeyNotFoundException(string.Format("Person key {0} not found", rowUserLoginPersonId), null);
}
//
// Verify the authentication type exists.
//
if (authenticationTypeId < 1)
{
throw new System.Collections.Generic.KeyNotFoundException(string.Format("Authentication type {0} not found", rowUserLoginAuthenticationType), null);
}
//
// Check that this user login record doesn't already exist.
//
bool exists = false;
if (existingUserLoginList.Count > 0)
{
exists = userLoginService.Queryable().AsNoTracking().Any(a => a.UserName.ToLower() == rowUserLoginUserName.ToLower());
}
if (exists == false && alreadyImportedCount > 0)
{
exists = userLoginService.Queryable().AsNoTracking().Any(a => a.ForeignKey == rowUserLoginId);
}
if (!exists)
{
//
// Create and populate the new user login record.
//
UserLogin login = new UserLogin();
login.CreatedDateTime = ParseDateOrDefault(rowUserLoginDateCreated, DateTime.Now);
login.CreatedByPersonAliasId = ImportPersonAliasId;
login.EntityTypeId = authenticationTypeId;
login.IsConfirmed = ParseBoolOrDefault(rowUserLoginIsConfirmed, true);
login.UserName = rowUserLoginUserName;
login.Password = rowUserLoginPassword;
login.PersonId = personKeys.PersonId;
login.ForeignKey = rowUserLoginId;
login.ForeignId = rowLoginId;
//
// Force not confirmed if no password provided for database logins.
//
if (rowUserLoginAuthenticationType == "Rock.Security.Authentication.Database" && string.IsNullOrWhiteSpace(rowUserLoginPassword))
{
login.IsConfirmed = false;
}
//
// Add the record for delayed saving.
//
newUserLoginList.Add(login);
importedCount++;
}
//
// Notify user of our status.
//
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} user login records processed, {1:N0} imported.", completed, importedCount));
}
if (completed % ReportingNumber < 1)
{
SaveUserLogin(newUserLoginList);
lookupContext.SaveChanges();
ReportPartialProgress();
// Clear out variables
newUserLoginList.Clear();
userLoginService = new UserLoginService(lookupContext);
}
}
//
// Save any final changes to new records
//
if (newUserLoginList.Any())
{
SaveUserLogin(newUserLoginList);
}
//
// Save any other changes to existing items.
//
lookupContext.SaveChanges();
lookupContext.Dispose();
ReportProgress(0, string.Format("Finished user login import: {0:N0} records added.", importedCount));
return(completed);
}
/// <summary>
/// Loads the ContentChannelItem data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadContentChannelItem(CSVInstance csvData)
{
var lookupContext = new RockContext();
var contentChannelItemService = new ContentChannelItemService(lookupContext);
var contentChannelService = new ContentChannelService(lookupContext);
var contentChannelTypeService = new ContentChannelTypeService(lookupContext);
// Look for custom attributes in the Content Channel file
var allFields = csvData.TableNodes.FirstOrDefault().Children.Select((node, index) => new { node = node, index = index }).ToList();
var customAttributes = allFields
.Where(f => f.index > ItemParentId)
.ToDictionary(f => f.index, f => f.node.Name);
// Set the supported date formats
var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy",
"M/d/yyyy", "M/dd/yyyy",
"M/d/yyyy h:mm:ss tt", "M/d/yyyy h:mm tt",
"MM/dd/yyyy hh:mm:ss", "M/d/yyyy h:mm:ss",
"M/d/yyyy hh:mm tt", "M/d/yyyy hh tt",
"M/d/yyyy h:mm", "M/d/yyyy h:mm",
"MM/dd/yyyy hh:mm", "M/dd/yyyy hh:mm",
"yyyy-MM-dd HH:mm:ss" };
var importedChannelIds = new List <int>();
var completed = 0;
var importedCount = 0;
var alreadyImportedCount = contentChannelItemService.Queryable().AsNoTracking().Count(i => i.ForeignKey != null);
ReportProgress(0, $"Starting Content Channel Item import ({alreadyImportedCount: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 rowContentChannelName = row[ContentChannelName];
var rowContentChannelItemTitle = row[ItemTitle];
var rowContentChannelItemContent = row[ItemContent];
var rowContentChannelItemId = row[ItemId];
var rowContentChannelItemParentId = row[ItemParentId];
var rowChannelItemId = rowContentChannelItemId.AsType <int?>();
ContentChannel contentChannel = null;
if (contentChannelService.Queryable().AsNoTracking().FirstOrDefault(t => t.Name.ToLower() == rowContentChannelName.ToLower()) != null)
{
contentChannel = contentChannelService.Queryable().AsNoTracking().FirstOrDefault(c => c.Name.ToLower() == rowContentChannelName.ToLower());
}
//
// Verify the Content Channel exists.
//
if (contentChannel.Id < 1)
{
throw new System.Collections.Generic.KeyNotFoundException($"Content Channel {rowContentChannelName} not found", null);
}
//
// Get content channel type
//
var contentChannelTypeId = contentChannelService.Queryable().AsNoTracking().FirstOrDefault(c => c.Id == contentChannel.Id).ContentChannelTypeId;
//
// Check that this Content Channel Item doesn't already exist.
//
var exists = false;
if (alreadyImportedCount > 0)
{
exists = contentChannelItemService.Queryable().AsNoTracking().Any(i => i.ForeignKey == rowContentChannelItemId);
}
if (!exists)
{
//
// Create and populate the new Content Channel.
//
var contentChannelItem = new ContentChannelItem
{
Title = rowContentChannelItemTitle,
Status = ContentChannelItemStatus.Approved,
Content = rowContentChannelItemContent,
ForeignKey = rowContentChannelItemId,
ForeignId = rowChannelItemId,
ContentChannelId = contentChannel.Id,
ContentChannelTypeId = contentChannelTypeId
};
DateTime startDateValue;
if (DateTime.TryParseExact(row[ItemStart], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out startDateValue))
{
contentChannelItem.StartDateTime = startDateValue;
}
DateTime expireDateValue;
if (DateTime.TryParseExact(row[ItemExpire], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out expireDateValue) && expireDateValue != System.DateTime.MinValue)
{
contentChannelItem.ExpireDateTime = expireDateValue;
}
if (contentChannel.RequiresApproval)
{
contentChannelItem.Status = ContentChannelItemStatus.Approved;
contentChannelItem.ApprovedDateTime = ImportDateTime;
contentChannelItem.ApprovedByPersonAliasId = ImportPersonAliasId;
}
// Save changes for context
lookupContext.WrapTransaction(() =>
{
lookupContext.ContentChannelItems.Add(contentChannelItem);
lookupContext.SaveChanges(DisableAuditing);
});
//
// Look for Parent Id and create appropriate objects.
//
if (!string.IsNullOrWhiteSpace(rowContentChannelItemParentId))
{
var parentFound = false;
parentFound = contentChannelItemService.Queryable().AsNoTracking().Any(i => i.ForeignKey == rowContentChannelItemParentId);
if (parentFound)
{
var parentItem = contentChannelItemService.Queryable().FirstOrDefault(i => i.ForeignKey == rowContentChannelItemParentId);
var service = new ContentChannelItemAssociationService(lookupContext);
var order = service.Queryable().AsNoTracking()
.Where(a => a.ContentChannelItemId == parentItem.Id)
.Select(a => ( int? )a.Order)
.DefaultIfEmpty()
.Max();
var assoc = new ContentChannelItemAssociation();
assoc.ContentChannelItemId = parentItem.Id;
assoc.ChildContentChannelItemId = contentChannelItem.Id;
assoc.Order = order.HasValue ? order.Value + 1 : 0;
service.Add(assoc);
lookupContext.SaveChanges(DisableAuditing);
}
}
//
// Process Attributes for Content Channel Items
//
if (customAttributes.Any())
{
// create content channel item attributes, but only if not already processed in this csv file
if (!importedChannelIds.Contains(contentChannel.Id))
{
// add current content channel id to list so we don't process multiple times
importedChannelIds.Add(contentChannel.Id);
// create content channel item attributes
foreach (var newAttributePair in customAttributes)
{
var pairs = newAttributePair.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);
if (string.IsNullOrEmpty(attributeName))
{
LogException($"Content Channel {contentChannelItem.ContentChannel.Name}", $"Content Channel {contentChannelItem.ContentChannel.Name} Item Attribute Name cannot be blank '{newAttributePair.Value}'.");
}
else
{
//
// First try to find the existing attribute, if not found then add a new one.
//
var fk = string.Empty;
if (string.IsNullOrWhiteSpace(attributeForeignKey))
{
fk = $"Bulldozer_ContentChannelItem_{contentChannel.Name.RemoveWhitespace()}_{categoryName.RemoveWhitespace()}_{attributeName.RemoveWhitespace()}".Left(100);
}
else
{
fk = attributeForeignKey;
}
AddEntityAttribute(lookupContext, contentChannelItem.TypeId, "ContentChannelId", contentChannelItem.ContentChannelId.ToString(), fk, categoryName, attributeName, string.Empty, fieldTypeId, true, definedValueForeignId, definedValueForeignKey, attributeTypeString: attributeTypeString);
}
} // end add attributes
} // end test for first run
//
// Add any Content Channel Item attribute values
//
foreach (var attributePair in customAttributes)
{
var newValue = row[attributePair.Key];
if (!string.IsNullOrWhiteSpace(newValue))
{
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;
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];
}
}
if (!string.IsNullOrEmpty(attributeName))
{
string fk = string.Empty;
if (string.IsNullOrWhiteSpace(attributeForeignKey))
{
fk = $"Bulldozer_ContentChannelItem_{contentChannel.Name.RemoveWhitespace()}_{categoryName.RemoveWhitespace()}_{attributeName.RemoveWhitespace()}".Left(100);
}
else
{
fk = attributeForeignKey;
}
var attribute = FindEntityAttribute(lookupContext, categoryName, attributeName, contentChannelItem.TypeId, fk);
AddEntityAttributeValue(lookupContext, attribute, contentChannelItem, newValue, null, true);
}
}
} // end attribute value processing
} // end custom attribute processing
importedCount++;
}
//
// Notify user of our status.
//
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, $"{completed:N0} Content Channel records processed, {importedCount:N0} imported.");
}
if (completed % ReportingNumber < 1)
{
lookupContext.SaveChanges();
ReportPartialProgress();
// Clear out variables
contentChannelService = new ContentChannelService(lookupContext);
}
}
//
// Save any other changes to existing items.
//
lookupContext.SaveChanges();
lookupContext.Dispose();
ReportProgress(0, $"Finished Content Channel Item import: {importedCount:N0} records added.");
return(completed);
}
