/// <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); }