/// <summary>
/// Load in the basic group information passed in by the caller. Group is not saved
/// unless the caller explecitely save the group.
/// </summary>
/// <param name="lookupContext">The lookup context.</param>
/// <param name="groupKey">The group key.</param>
/// <param name="name">The name.</param>
/// <param name="createdDate">The created date.</param>
/// <param name="type">The type.</param>
/// <param name="parentGroupKey">The parent group key.</param>
/// <param name="active">The active.</param>
/// <returns></returns>
private Group LoadGroupBasic(RockContext lookupContext, string groupKey, string name, string createdDate, string type, string parentGroupKey, string active, string description = "")
{
var groupTypeId = LoadGroupTypeId(lookupContext, type);
var groupId = groupKey.AsType <int?>();
Group group, parent;
//
// See if we have already imported it previously. Otherwise
// create it as a new group.
//
group = ImportedGroups.FirstOrDefault(g => g.ForeignKey == groupKey);
if (group == null)
{
group = new Group
{
ForeignKey = groupKey,
ForeignId = groupId,
Name = name,
CreatedByPersonAliasId = ImportPersonAliasId,
GroupTypeId = groupTypeId,
Description = description
};
lookupContext.Groups.Add(group);
ImportedGroups.Add(group);
}
else
{
lookupContext.Groups.Attach(group);
lookupContext.Entry(group).State = EntityState.Modified;
}
//
// Find and set the parent group. If not found it becomes a root level group.
//
parent = ImportedGroups.FirstOrDefault(g => g.ForeignKey == parentGroupKey);
if (parent != null)
{
group.ParentGroupId = parent.Id;
}
//
// Setup the date created/modified values from the data, if we have them.
//
group.CreatedDateTime = ParseDateOrDefault(createdDate, ImportDateTime);
group.ModifiedDateTime = ImportDateTime;
//
// Set the active state of this group.
//
if (active.ToUpper() == "NO")
{
group.IsActive = false;
}
else
{
group.IsActive = true;
}
return(group);
}
/// <summary>
/// Maps the RLC data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapRLC(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var importedLocations = lookupContext.Locations.AsNoTracking().Where(l => l.ForeignKey != null).ToList();
var newGroups = new List <Group>();
var archivedScheduleName = "Archived Attendance";
var archivedScheduleId = new ScheduleService(lookupContext).Queryable()
.Where(s => s.Name.Equals(archivedScheduleName, StringComparison.OrdinalIgnoreCase))
.Select(s => ( int? )s.Id).FirstOrDefault();
if (!archivedScheduleId.HasValue)
{
var archivedSchedule = AddNamedSchedule(lookupContext, archivedScheduleName, null, null, null,
ImportDateTime, archivedScheduleName.RemoveSpecialCharacters(), true, ImportPersonAliasId);
archivedScheduleId = archivedSchedule.Id;
}
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying group location import ({0:N0} found, {1:N0} already exist).", totalRows, importedLocations.Count));
foreach (var row in tableData.Where(r => r != null))
{
// get the group and location data
var rlcId = row["RLC_ID"] as int?;
var activityId = row["Activity_ID"] as int?;
var rlcName = row["RLC_Name"] as string;
var activityGroupId = row["Activity_Group_ID"] as int?;
var startAgeAttribute = row["Start_Age_Date"] as DateTime?;
var endAgeAttribute = row["End_Age_Date"] as DateTime?;
var rlcActive = row["Is_Active"] as Boolean?;
var roomCode = row["Room_Code"] as string;
var roomDescription = row["Room_Desc"] as string;
var roomName = row["Room_Name"] as string;
var roomCapacity = row["Max_Capacity"] as int?;
var buildingName = row["Building_Name"] as string;
// get the parent group
if (activityId.HasValue && !rlcName.Equals("Delete", StringComparison.OrdinalIgnoreCase))
{
// get the mid-level activity if exists, otherwise the top-level activity
var lookupParentId = activityGroupId ?? activityId;
// add the child RLC group and locations
var parentGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(lookupParentId.ToStringSafe()));
if (parentGroup != null)
{
if (rlcId.HasValue && !string.IsNullOrWhiteSpace(rlcName))
{
int? parentLocationId = null;
Location campusLocation = null;
// get the campus from the room, building, or parent
var rlcCampusId = GetCampusId(rlcName, false) ?? GetCampusId(buildingName, false) ?? parentGroup.CampusId;
if (rlcCampusId.HasValue)
{
var campus = lookupContext.Campuses.FirstOrDefault(c => c.Id == rlcCampusId);
if (campus != null)
{
campusLocation = campus.Location ?? importedLocations.FirstOrDefault(l => l.ForeignKey.Equals(campus.ShortCode));
if (campusLocation == null)
{
campusLocation = AddNamedLocation(lookupContext, null, campus.Name, campus.IsActive, null, ImportDateTime, campus.ShortCode, true, ImportPersonAliasId);
importedLocations.Add(campusLocation);
campus.LocationId = campusLocation.Id;
lookupContext.SaveChanges();
}
parentLocationId = campusLocation.Id;
}
}
// set the location structure
Location roomLocation = null;
if (!string.IsNullOrWhiteSpace(roomName))
{
// get the building if it exists
Location buildingLocation = null;
if (!string.IsNullOrWhiteSpace(buildingName))
{
buildingLocation = importedLocations.FirstOrDefault(l => l.ForeignKey.Equals(buildingName) && l.ParentLocationId == parentLocationId);
if (buildingLocation == null)
{
buildingLocation = AddNamedLocation(lookupContext, parentLocationId, buildingName, rlcActive, null, ImportDateTime, buildingName, true, ImportPersonAliasId);
importedLocations.Add(buildingLocation);
}
parentLocationId = buildingLocation.Id;
}
// get the room if it exists in the current structure
roomLocation = importedLocations.FirstOrDefault(l => l.ForeignKey.Equals(roomName) && l.ParentLocationId == parentLocationId);
if (roomLocation == null)
{
roomLocation = AddNamedLocation(null, parentLocationId, roomName, rlcActive, roomCapacity, ImportDateTime, roomName, true, ImportPersonAliasId);
importedLocations.Add(roomLocation);
}
}
// create the rlc group
var rlcGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(rlcId.ToString()));
if (rlcGroup == null)
{
// don't save immediately, we'll batch add later
rlcGroup = AddGroup(null, parentGroup.GroupTypeId, parentGroup.Id, rlcName, rlcActive ?? true, rlcCampusId, null, rlcId.ToString(), false, ImportPersonAliasId, archivedScheduleId);
if (roomLocation != null)
{
rlcGroup.GroupLocations.Add(new GroupLocation {
LocationId = roomLocation.Id
});
}
newGroups.Add(rlcGroup);
}
}
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} location groups imported ({1}% complete).", completedItems, percentComplete));
}
if (completedItems % ReportingNumber < 1)
{
SaveGroups(newGroups);
ImportedGroups.AddRange(newGroups);
ReportPartialProgress();
// Reset lists and context
lookupContext = new RockContext();
newGroups.Clear();
}
}
}
}
if (newGroups.Any())
{
SaveGroups(newGroups);
ImportedGroups.AddRange(newGroups);
}
lookupContext.Dispose();
ReportProgress(100, string.Format("Finished group location import: {0:N0} locations imported.", completedItems));
}
/// <summary>
/// Maps the volunteer assignment data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapStaffingAssignment(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var excludedGroupTypes = new List <int> {
FamilyGroupTypeId, SmallGroupTypeId, GeneralGroupTypeId
};
var importedGroupMembers = lookupContext.GroupMembers.Count(gm => gm.ForeignKey != null && !excludedGroupTypes.Contains(gm.Group.GroupTypeId));
var skippedGroups = new Dictionary <int, string>();
var newGroupMembers = new List <GroupMember>();
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying volunteer assignment import ({0:N0} found, {1:N0} already exist).", totalRows, importedGroupMembers));
foreach (var row in tableData.Where(r => r != null))
{
// get the group and role data
var individualId = row["Individual_ID"] as int?;
var roleTitle = row["Job_Title"] as string;
var isActive = row["Is_Active"] as bool?;
var ministryId = row["Ministry_ID"] as int?;
var activityId = row["Activity_ID"] as int?;
var activityGroupId = row["Activity_Group_ID"] as int?;
var activityTimeName = row["Activity_Time_Name"] as string;
var rlcId = row["RLC_ID"] as int?;
var jobId = row["JobID"] as int?;
var groupLookupId = rlcId ?? activityGroupId ?? activityId ?? ministryId;
var volunteerGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(groupLookupId.ToString()) && !excludedGroupTypes.Contains(g.GroupTypeId));
if (volunteerGroup != null)
{
var personKeys = GetPersonKeys(individualId, null);
if (personKeys != null)
{
var campusId = GetCampusId(roleTitle);
if (campusId.HasValue)
{
// strip the campus from the role
roleTitle = StripPrefix(roleTitle, campusId);
}
var isLeaderRole = !string.IsNullOrWhiteSpace(roleTitle) ? roleTitle.ToStringSafe().EndsWith("Leader") : false;
var groupTypeRole = GetGroupTypeRole(lookupContext, volunteerGroup.GroupTypeId, roleTitle, string.Format("{0} imported {1}", activityTimeName, ImportDateTime), isLeaderRole, 0, true, null, jobId.ToStringSafe(), ImportPersonAliasId);
newGroupMembers.Add(new GroupMember
{
IsSystem = false,
GroupId = volunteerGroup.Id,
PersonId = personKeys.PersonId,
GroupRoleId = groupTypeRole.Id,
GroupMemberStatus = isActive != false ? GroupMemberStatus.Active : GroupMemberStatus.Inactive,
ForeignKey = string.Format("Membership imported {0}", ImportDateTime)
});
completedItems++;
}
}
else
{
skippedGroups.AddOrIgnore(( int )groupLookupId, string.Empty);
}
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} assignments imported ({1}% complete).", completedItems, percentComplete));
}
if (completedItems % ReportingNumber < 1)
{
SaveGroupMembers(newGroupMembers);
ReportPartialProgress();
// Reset lists and context
lookupContext = new RockContext();
newGroupMembers.Clear();
}
}
if (newGroupMembers.Any())
{
SaveGroupMembers(newGroupMembers);
}
if (skippedGroups.Any())
{
ReportProgress(0, "The following volunteer groups could not be found and were skipped:");
foreach (var key in skippedGroups)
{
ReportProgress(0, string.Format("{0}Assignments for group ID {1}.", key.Value, key));
}
}
lookupContext.Dispose();
ReportProgress(100, string.Format("Finished volunteer assignment import: {0:N0} assignments imported.", completedItems));
}
/// <summary>
/// Maps the home group membership data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapGroups(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var newGroupMembers = new List <GroupMember>();
var importedGroupMembers = lookupContext.GroupMembers.Count(gm => gm.ForeignKey != null && gm.Group.GroupTypeId == GeneralGroupTypeId);
var groupRoleMember = GroupTypeCache.Get(GeneralGroupTypeId).Roles.FirstOrDefault(r => r.Name.Equals("Member"));
var archivedScheduleName = "Archived Attendance";
var archivedScheduleId = new ScheduleService(lookupContext).Queryable()
.Where(s => s.Name.Equals(archivedScheduleName, StringComparison.OrdinalIgnoreCase))
.Select(s => ( int? )s.Id).FirstOrDefault();
if (!archivedScheduleId.HasValue)
{
var archivedSchedule = AddNamedSchedule(lookupContext, archivedScheduleName, null, null, null,
ImportDateTime, archivedScheduleName.RemoveSpecialCharacters(), true, ImportPersonAliasId);
archivedScheduleId = archivedSchedule.Id;
}
var groupsParentName = "Archived Groups";
var archivedGroups = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(groupsParentName.RemoveWhitespace()));
if (archivedGroups == null)
{
archivedGroups = AddGroup(lookupContext, GeneralGroupTypeId, null, groupsParentName, true, null, ImportDateTime, groupsParentName.RemoveWhitespace(), true, ImportPersonAliasId);
ImportedGroups.Add(archivedGroups);
}
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying people groups import ({0:N0} found, {1:N0} already exist).", totalRows, importedGroupMembers));
foreach (var row in tableData.Where(r => r != null))
{
var groupId = row["Group_ID"] as int?;
var groupName = row["Group_Name"] as string;
var individualId = row["Individual_ID"] as int?;
var groupCreated = row["Created_Date"] as DateTime?;
var groupType = row["Group_Type_Name"] as string;
// require at least a group id and name
if (groupId.HasValue && !string.IsNullOrWhiteSpace(groupName) && !groupName.Equals("Delete", StringComparison.OrdinalIgnoreCase))
{
var peopleGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(groupId.ToString()));
if (peopleGroup == null)
{
int?campusId = null;
var parentGroupId = archivedGroups.Id;
var currentGroupTypeId = GeneralGroupTypeId;
if (!string.IsNullOrWhiteSpace(groupType))
{
// check for a campus on the grouptype
campusId = GetCampusId(groupType, true, SearchDirection.Ends);
if (campusId.HasValue)
{
groupType = StripSuffix(groupType, campusId);
}
// add the grouptype if it doesn't exist
var currentGroupType = ImportedGroupTypes.FirstOrDefault(t => t.ForeignKey.Equals(groupType, StringComparison.OrdinalIgnoreCase));
if (currentGroupType == null)
{
// save immediately so we can use the grouptype for a group
currentGroupType = AddGroupType(lookupContext, groupType, string.Format("{0} imported {1}", groupType, ImportDateTime), null,
null, null, true, true, true, true, typeForeignKey: groupType);
ImportedGroupTypes.Add(currentGroupType);
}
// create a placeholder group for the grouptype if it doesn't exist
var groupTypePlaceholder = ImportedGroups.FirstOrDefault(g => g.GroupTypeId == currentGroupType.Id && g.ForeignKey.Equals(groupType.RemoveWhitespace()));
if (groupTypePlaceholder == null)
{
groupTypePlaceholder = AddGroup(lookupContext, currentGroupType.Id, archivedGroups.Id, groupType, true, null, ImportDateTime,
groupType.RemoveWhitespace(), true, ImportPersonAliasId);
ImportedGroups.Add(groupTypePlaceholder);
}
parentGroupId = groupTypePlaceholder.Id;
currentGroupTypeId = currentGroupType.Id;
}
// put the current group under a campus parent if it exists
campusId = campusId ?? GetCampusId(groupName);
if (campusId.HasValue)
{
// create a campus level parent for the home group
var campus = CampusList.FirstOrDefault(c => c.Id == campusId);
var campusGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(campus.ShortCode) && g.ParentGroupId == parentGroupId);
if (campusGroup == null)
{
campusGroup = AddGroup(lookupContext, currentGroupTypeId, parentGroupId, campus.Name, true, campus.Id, ImportDateTime, campus.ShortCode, true, ImportPersonAliasId);
ImportedGroups.Add(campusGroup);
}
parentGroupId = campusGroup.Id;
}
// add the group, finally
peopleGroup = AddGroup(lookupContext, currentGroupTypeId, parentGroupId, groupName, true, campusId, null, groupId.ToString(), true, ImportPersonAliasId, archivedScheduleId);
ImportedGroups.Add(peopleGroup);
}
// add the group member
var personKeys = GetPersonKeys(individualId, null);
if (personKeys != null)
{
newGroupMembers.Add(new GroupMember
{
IsSystem = false,
GroupId = peopleGroup.Id,
PersonId = personKeys.PersonId,
GroupRoleId = groupRoleMember.Id,
GroupMemberStatus = GroupMemberStatus.Active,
ForeignKey = string.Format("Membership imported {0}", ImportDateTime)
});
completedItems++;
}
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} group members imported ({1}% complete).", completedItems, percentComplete));
}
if (completedItems % ReportingNumber < 1)
{
SaveGroupMembers(newGroupMembers);
ReportPartialProgress();
// Reset lists and context
lookupContext = new RockContext();
newGroupMembers.Clear();
}
}
}
if (newGroupMembers.Any())
{
SaveGroupMembers(newGroupMembers);
}
lookupContext.Dispose();
ReportProgress(100, string.Format("Finished people groups import: {0:N0} members imported.", completedItems));
}
/// <summary>
/// Maps the volunteer assignment data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapActivityAssignment(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var excludedGroupTypes = new List <int> {
FamilyGroupTypeId, SmallGroupTypeId, GeneralGroupTypeId
};
var importedGroupMembers = lookupContext.GroupMembers.Count(gm => gm.ForeignKey != null && !excludedGroupTypes.Contains(gm.Group.GroupTypeId));
var newGroupMembers = new List <GroupMember>();
var assignmentTerm = "Member";
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying participant assignment import ({0:N0} found).", totalRows));
foreach (var row in tableData.Where(r => r != null))
{
// get the group and role data
var ministryId = row["Ministry_ID"] as int?;
var activityId = row["Activity_ID"] as int?;
var rlcId = row["RLC_ID"] as int?;
var individualId = row["Individual_ID"] as int?;
var assignmentDate = row["AssignmentDateTime"] as DateTime?;
var membershipStart = row["Activity_Start_Time"] as DateTime?;
var membershipStop = row["Activity_End_Time"] as DateTime?;
var activityTimeName = row["Activity_Time_Name"] as string;
var groupLookupId = rlcId ?? activityId ?? ministryId;
var assignmentGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(groupLookupId.ToString()) && !excludedGroupTypes.Contains(g.GroupTypeId));
if (assignmentGroup != null)
{
var personKeys = GetPersonKeys(individualId, null);
if (personKeys != null)
{
var isActive = membershipStop.HasValue ? membershipStop > RockDateTime.Now : true;
var groupTypeRole = GetGroupTypeRole(lookupContext, assignmentGroup.GroupTypeId, assignmentTerm, string.Format("{0} imported {1}", activityTimeName, ImportDateTime), false, 0, true, null, string.Format("{0} {1}", activityTimeName, assignmentTerm), ImportPersonAliasId);
newGroupMembers.Add(new GroupMember
{
IsSystem = false,
DateTimeAdded = membershipStart,
GroupId = assignmentGroup.Id,
PersonId = personKeys.PersonId,
GroupRoleId = groupTypeRole.Id,
CreatedDateTime = assignmentDate,
ModifiedDateTime = membershipStop,
GroupMemberStatus = isActive != false ? GroupMemberStatus.Active : GroupMemberStatus.Inactive,
ForeignKey = string.Format("Membership imported {0}", ImportDateTime)
});
completedItems++;
}
}
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} assignments imported ({1}% complete).", completedItems, percentComplete));
}
if (completedItems % ReportingNumber < 1)
{
SaveGroupMembers(newGroupMembers);
ReportPartialProgress();
// Reset lists and context
lookupContext = new RockContext();
newGroupMembers.Clear();
}
}
if (newGroupMembers.Any())
{
SaveGroupMembers(newGroupMembers);
}
lookupContext.Dispose();
ReportProgress(100, string.Format("Finished participant assignment import: {0:N0} assignments imported.", completedItems));
}
/// <summary>
/// Maps the activity ministry data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapActivityMinistry(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var newGroups = new List <Group>();
const string attendanceTypeName = "Attendance History";
var groupTypeHistory = ImportedGroupTypes.FirstOrDefault(t => t.ForeignKey.Equals(attendanceTypeName));
if (groupTypeHistory == null)
{
groupTypeHistory = AddGroupType(lookupContext, attendanceTypeName, string.Format("{0} imported {1}", attendanceTypeName, ImportDateTime), null,
null, GroupTypeCheckinTemplateId, true, true, true, true, typeForeignKey: attendanceTypeName);
ImportedGroupTypes.Add(groupTypeHistory);
}
const string groupsParentName = "Archived Groups";
var archivedGroupsParent = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(groupsParentName.RemoveWhitespace()));
if (archivedGroupsParent == null)
{
archivedGroupsParent = AddGroup(lookupContext, GeneralGroupTypeId, null, groupsParentName, true, null, ImportDateTime, groupsParentName.RemoveWhitespace(), true, ImportPersonAliasId);
ImportedGroups.Add(archivedGroupsParent);
}
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying ministry import ({0:N0} found, {1:N0} already exist).", totalRows, ImportedGroupTypes.Count));
foreach (var row in tableData.OrderBy(r => r["Ministry_ID"] as int?).ThenBy(r => r["Activity_ID"] as int?))
{
// get the ministry data
var ministryId = row["Ministry_ID"] as int?;
var activityId = row["Activity_ID"] as int?;
var ministryName = row["Ministry_Name"] as string;
var activityName = row["Activity_Name"] as string;
var ministryActive = row["Ministry_Active"] as string;
var activityActive = row["Activity_Active"] as string;
int?campusId = null;
if (ministryId.HasValue && !string.IsNullOrWhiteSpace(ministryName) && !ministryName.Equals("Delete", StringComparison.OrdinalIgnoreCase))
{
// check for a ministry group campus context
if (ministryName.Any(n => ValidDelimiters.Contains(n)))
{
campusId = campusId ?? GetCampusId(ministryName);
if (campusId.HasValue)
{
// strip the campus from the ministry name to use for grouptype (use the original name on groups though)
ministryName = StripPrefix(ministryName, campusId);
}
}
// add the new grouptype if it doesn't exist
var currentGroupType = ImportedGroupTypes.FirstOrDefault(t => t.ForeignKey.Equals(ministryName));
if (currentGroupType == null)
{
// save immediately so we can use the grouptype for a group
currentGroupType = AddGroupType(lookupContext, ministryName, string.Format("{0} imported {1}", ministryName, ImportDateTime), groupTypeHistory.Id,
null, null, true, true, true, true, typeForeignKey: ministryName);
ImportedGroupTypes.Add(currentGroupType);
}
// create a campus level parent for the ministry group
var parentGroupId = archivedGroupsParent.Id;
if (campusId.HasValue)
{
var campus = CampusList.FirstOrDefault(c => c.Id == campusId);
var campusGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(campus.ShortCode) && g.ParentGroupId == parentGroupId);
if (campusGroup == null)
{
campusGroup = AddGroup(lookupContext, GeneralGroupTypeId, parentGroupId, campus.Name, true, campus.Id, ImportDateTime, campus.ShortCode, true, ImportPersonAliasId);
ImportedGroups.Add(campusGroup);
}
parentGroupId = campusGroup.Id;
}
// add a ministry group level if it doesn't exist
var ministryGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(ministryId.ToString()));
if (ministryGroup == null)
{
// save immediately so we can use the group as a parent
ministryGroup = AddGroup(lookupContext, currentGroupType.Id, parentGroupId, ministryName, ministryActive.AsBoolean(), campusId, null, ministryId.ToString(), true, ImportPersonAliasId);
ImportedGroups.Add(ministryGroup);
}
// check for an activity group campus context
if (!string.IsNullOrWhiteSpace(activityName) && activityName.Any(n => ValidDelimiters.Contains(n)))
{
campusId = campusId ?? GetCampusId(activityName);
if (campusId.HasValue)
{
activityName = StripPrefix(activityName, campusId);
}
}
// add the child activity group if it doesn't exist
var activityGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(activityId.ToString()));
if (activityGroup == null && activityId.HasValue && !string.IsNullOrWhiteSpace(activityName) && !activityName.Equals("Delete", StringComparison.OrdinalIgnoreCase))
{
// don't save immediately, we'll batch add later
activityGroup = AddGroup(lookupContext, currentGroupType.Id, ministryGroup.Id, activityName, activityActive.AsBoolean(), campusId, null, activityId.ToString(), false, ImportPersonAliasId);
newGroups.Add(activityGroup);
}
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} ministries imported ({1}% complete).", completedItems, percentComplete));
}
if (completedItems % ReportingNumber < 1)
{
SaveGroups(newGroups);
ReportPartialProgress();
ImportedGroups.AddRange(newGroups);
// Reset lists and context
lookupContext = new RockContext();
newGroups.Clear();
}
}
}
if (newGroups.Any())
{
SaveGroups(newGroups);
ImportedGroups.AddRange(newGroups);
}
lookupContext.Dispose();
ReportProgress(100, string.Format("Finished ministry import: {0:N0} ministries imported.", completedItems));
}
/// <summary>
/// Maps the activity group data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private void MapActivityGroup(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var newGroups = new List <Group>();
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var archivedScheduleName = "Archived Attendance";
var archivedScheduleId = new ScheduleService(lookupContext).Queryable()
.Where(s => s.Name.Equals(archivedScheduleName, StringComparison.OrdinalIgnoreCase))
.Select(s => ( int? )s.Id).FirstOrDefault();
if (!archivedScheduleId.HasValue)
{
var archivedSchedule = AddNamedSchedule(lookupContext, archivedScheduleName, null, null, null,
ImportDateTime, archivedScheduleName.RemoveSpecialCharacters(), true, ImportPersonAliasId);
archivedScheduleId = archivedSchedule.Id;
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying activity import ({0:N0} found, {1:N0} already exist).", totalRows, ImportedGroups.Count));
foreach (var row in tableData.Where(r => r != null))
{
// get the group data
var activityId = row["Activity_ID"] as int?;
var activityGroupId = row["Activity_Group_ID"] as int?;
var superGroupId = row["Activity_Super_Group_ID"] as int?;
var activityGroupName = row["Activity_Group_Name"] as string;
var superGroupName = row["Activity_Super_Group"] as string;
var balanceType = row["CheckinBalanceType"] as string;
// get the top-level activity group
if (activityId.HasValue && !activityGroupName.Equals("Delete", StringComparison.OrdinalIgnoreCase))
{
var parentGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(activityId.ToString()));
if (parentGroup != null)
{
// add a level for the super group activity if it exists
int?parentGroupId = parentGroup.Id;
if (superGroupId.HasValue && !string.IsNullOrWhiteSpace(superGroupName))
{
var superGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(superGroupId.ToString()));
if (superGroup == null)
{
superGroup = AddGroup(lookupContext, parentGroup.GroupTypeId, parentGroupId, superGroupName, parentGroup.IsActive, parentGroup.CampusId, null, superGroupId.ToString(), true, ImportPersonAliasId, archivedScheduleId);
ImportedGroups.Add(superGroup);
// set parent guid to super group
parentGroupId = superGroup.Id;
}
}
// add the child activity group
if (activityGroupId.HasValue && !string.IsNullOrWhiteSpace(activityGroupName))
{
var activityGroup = ImportedGroups.FirstOrDefault(g => g.ForeignKey.Equals(activityGroupId.ToString()));
if (activityGroup == null)
{
// don't save immediately, we'll batch add later
activityGroup = AddGroup(null, parentGroup.GroupTypeId, parentGroupId, activityGroupName, parentGroup.IsActive, parentGroup.CampusId, null, activityGroupId.ToString(), false, ImportPersonAliasId, archivedScheduleId);
newGroups.Add(activityGroup);
}
}
// #TODO: if Rock ever supports room balancing, check the F1 BalanceType
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} activities imported ({1}% complete).", completedItems, percentComplete));
}
if (completedItems % ReportingNumber < 1)
{
SaveGroups(newGroups);
ReportPartialProgress();
ImportedGroups.AddRange(newGroups);
// Reset lists and context
lookupContext = new RockContext();
newGroups.Clear();
}
}
}
}
if (newGroups.Any())
{
SaveGroups(newGroups);
ImportedGroups.AddRange(newGroups);
}
lookupContext.Dispose();
ReportProgress(100, string.Format("Finished activity group import: {0:N0} activities imported.", completedItems));
}
/// <summary>
/// Maps the headcount metrics.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
private int MapMetrics(IQueryable <Row> tableData, long totalRows = 0)
{
// Required variables
var lookupContext = new RockContext();
var metricService = new MetricService(lookupContext);
var metricCategoryService = new MetricCategoryService(lookupContext);
var categoryService = new CategoryService(lookupContext);
var metricSourceTypes = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.METRIC_SOURCE_TYPE)).DefinedValues;
var metricManualSource = metricSourceTypes.FirstOrDefault(m => m.Guid == new Guid(Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_MANUAL));
var archivedScheduleCategory = GetCategory(lookupContext, ScheduleEntityTypeId, null, "Archived Schedules");
var scheduleService = new ScheduleService(lookupContext);
var scheduleMetrics = scheduleService.Queryable().AsNoTracking().Where(s => s.Category.Guid == archivedScheduleCategory.Guid).ToList();
var allMetrics = metricService.Queryable().AsNoTracking().ToList();
var metricCategories = categoryService.Queryable().AsNoTracking()
.Where(c => c.EntityType.Guid == new Guid(Rock.SystemGuid.EntityType.METRICCATEGORY)).ToList();
var defaultCategoryName = "Metrics";
var defaultCategory = metricCategories.FirstOrDefault(c => c.Name == defaultCategoryName);
if (defaultCategory == null)
{
defaultCategory = GetCategory(lookupContext, MetricCategoryEntityTypeId, null, defaultCategoryName);
defaultCategory.ForeignKey = string.Format("Category imported {0}", ImportDateTime);
metricCategories.Add(defaultCategory);
}
var metricValues = new List <MetricValue>();
Metric currentMetric = null;
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Starting metrics import ({0:N0} already exist).", 0));
foreach (var row in tableData.Where(r => r != null))
{
var foreignId = row["Headcount_ID"] as int?;
var activityId = row["Activity_ID"] as int?;
var rlcId = row["RLC_ID"] as int?;
var metricName = row["RLC_name"] as string;
var valueDate = row["Start_Date_Time"] as DateTime?;
var value = row["Attendance"] as string;
var metricNote = row["Meeting_note"] as string;
int?metricCampusId = null;
if (!string.IsNullOrEmpty(metricName) && !string.IsNullOrWhiteSpace(value))
{
var categoryName = string.Empty;
var metricCategoryId = defaultCategory.Id;
if (activityId.HasValue)
{
var activityGroup = ImportedGroups.FirstOrDefault(g => g.ForeignId == activityId);
if (activityGroup != null && !string.IsNullOrWhiteSpace(activityGroup.Name))
{
metricCampusId = metricCampusId ?? GetCampusId(activityGroup.Name);
var activityCategory = metricCategories.FirstOrDefault(c => c.Name == activityGroup.Name && c.ParentCategoryId == metricCategoryId);
if (activityCategory == null)
{
activityCategory = GetCategory(lookupContext, MetricCategoryEntityTypeId, metricCategoryId, activityGroup.Name);
activityCategory.ForeignKey = string.Format("Category imported {0}", ImportDateTime);
metricCategories.Add(activityCategory);
}
metricCategoryId = activityCategory.Id;
}
}
if (rlcId.HasValue)
{
var rlcGroup = ImportedGroups.FirstOrDefault(g => g.ForeignId == rlcId);
if (rlcGroup != null && !string.IsNullOrWhiteSpace(rlcGroup.Name))
{
metricCampusId = metricCampusId ?? GetCampusId(rlcGroup.Name);
var rlcCategory = metricCategories.FirstOrDefault(c => c.Name == rlcGroup.Name && c.ParentCategoryId == metricCategoryId);
if (rlcCategory == null)
{
rlcCategory = GetCategory(lookupContext, MetricCategoryEntityTypeId, metricCategoryId, rlcGroup.Name);
rlcCategory.ForeignKey = string.Format("Category imported {0}", ImportDateTime);
metricCategories.Add(rlcCategory);
}
metricCategoryId = rlcCategory.Id;
}
}
// create metric if it doesn't exist
currentMetric = allMetrics.FirstOrDefault(m => m.Title == metricName && m.MetricCategories.Any(c => c.CategoryId == metricCategoryId));
if (currentMetric == null)
{
currentMetric = new Metric();
currentMetric.Title = metricName;
currentMetric.IsSystem = false;
currentMetric.IsCumulative = false;
currentMetric.SourceSql = string.Empty;
currentMetric.Subtitle = string.Empty;
currentMetric.Description = string.Empty;
currentMetric.IconCssClass = string.Empty;
currentMetric.SourceValueTypeId = metricManualSource.Id;
currentMetric.CreatedByPersonAliasId = ImportPersonAliasId;
currentMetric.CreatedDateTime = ImportDateTime;
currentMetric.ForeignId = foreignId;
currentMetric.ForeignKey = foreignId.ToStringSafe();
currentMetric.MetricPartitions = new List <MetricPartition>();
currentMetric.MetricPartitions.Add(new MetricPartition
{
Label = "Campus",
Metric = currentMetric,
EntityTypeId = CampusEntityTypeId,
EntityTypeQualifierColumn = string.Empty,
EntityTypeQualifierValue = string.Empty
});
currentMetric.MetricPartitions.Add(new MetricPartition
{
Label = "Service",
Metric = currentMetric,
EntityTypeId = ScheduleEntityTypeId,
EntityTypeQualifierColumn = string.Empty,
EntityTypeQualifierValue = string.Empty
});
metricService.Add(currentMetric);
lookupContext.SaveChanges();
if (currentMetric.MetricCategories == null || !currentMetric.MetricCategories.Any(a => a.CategoryId == metricCategoryId))
{
metricCategoryService.Add(new MetricCategory {
CategoryId = metricCategoryId, MetricId = currentMetric.Id
});
lookupContext.SaveChanges();
}
allMetrics.Add(currentMetric);
}
// create values for this metric
var metricValue = new MetricValue();
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.CreatedByPersonAliasId = ImportPersonAliasId;
metricValue.CreatedDateTime = ImportDateTime;
metricValue.MetricValueDateTime = valueDate;
metricValue.MetricId = currentMetric.Id;
metricValue.XValue = string.Empty;
metricValue.YValue = value.AsDecimalOrNull();
metricValue.ForeignKey = string.Format("Metric Value imported {0}", ImportDateTime);
metricValue.Note = metricNote ?? string.Empty;
if (valueDate.HasValue)
{
var metricPartitionScheduleId = currentMetric.MetricPartitions.FirstOrDefault(p => p.Label == "Service").Id;
var date = ( DateTime )valueDate;
var scheduleName = date.DayOfWeek.ToString();
if (date.TimeOfDay.TotalSeconds > 0)
{
scheduleName = scheduleName + string.Format(" {0}", date.ToString("hh:mm")) + string.Format("{0}", date.ToString("tt").ToLower());
}
var metricSchedule = scheduleMetrics.FirstOrDefault(s => s.Name == scheduleName);
if (metricSchedule == null)
{
metricSchedule = new Schedule();
metricSchedule.Name = scheduleName;
metricSchedule.iCalendarContent = CreateCalendarContent(date, "WEEKLY", ImportDateTime);
metricSchedule.CategoryId = archivedScheduleCategory.Id;
metricSchedule.EffectiveStartDate = ImportDateTime;
metricSchedule.CreatedByPersonAliasId = ImportPersonAliasId;
metricSchedule.CreatedDateTime = ImportDateTime;
metricSchedule.ForeignKey = string.Format("Metric Schedule imported {0}", ImportDateTime);
lookupContext.Schedules.Add(metricSchedule);
lookupContext.SaveChanges();
scheduleMetrics.Add(metricSchedule);
}
metricValue.MetricValuePartitions.Add(new MetricValuePartition
{
MetricPartitionId = metricPartitionScheduleId,
EntityId = metricSchedule.Id,
CreatedDateTime = valueDate,
ModifiedDateTime = valueDate,
CreatedByPersonAliasId = ImportPersonAliasId,
ModifiedByPersonAliasId = ImportPersonAliasId
});
}
if (metricCampusId.HasValue && CampusList.Any(c => c.Id == metricCampusId))
{
var metricPartitionCampusId = currentMetric.MetricPartitions.FirstOrDefault(p => p.Label == "Campus").Id;
metricValue.MetricValuePartitions.Add(new MetricValuePartition {
MetricPartitionId = metricPartitionCampusId, EntityId = metricCampusId
});
}
metricValues.Add(metricValue);
completedItems++;
if (completedItems % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} metrics imported.", completedItems));
}
if (completedItems % ReportingNumber < 1)
{
SaveMetrics(metricValues);
ReportPartialProgress();
metricValues.Clear();
}
}
}
// Check to see if any rows didn't get saved to the database
if (metricValues.Any())
{
SaveMetrics(metricValues);
}
ReportProgress(0, string.Format("Finished metrics import: {0:N0} metrics added or updated.", completedItems));
return(completedItems);
}
/// <summary>
/// Load in the basic group information passed in by the caller. Group is not saved
/// unless the caller explecitely save the group.
/// </summary>
/// <param name="lookupContext">The lookup context.</param>
/// <param name="groupKey">The group key.</param>
/// <param name="name">The name.</param>
/// <param name="createdDate">The created date.</param>
/// <param name="type">The type.</param>
/// <param name="parentGroupKey">The parent group key.</param>
/// <param name="active">The active.</param>
/// <returns></returns>
private Group LoadGroupBasic(RockContext lookupContext, string groupKey, string name, string createdDate, string type, string parentGroupKey, string active, string description = "")
{
var groupTypeId = LoadGroupTypeId(lookupContext, type);
var groupId = groupKey.AsType <int?>();
Group group, parent;
//
// See if we have already imported it previously. Otherwise
// create it as a new group.
//
group = ImportedGroups.FirstOrDefault(g => g.ForeignKey == groupKey);
// Check if this was an existing group that needs foreign id added
if (group == null)
{
var parentGroupId = ImportedGroups.FirstOrDefault(g => g.ForeignKey == parentGroupKey)?.Id;
group = new GroupService(lookupContext).Queryable().Where(g => g.ForeignKey == null && g.GroupTypeId == groupTypeId && g.Name.Equals(name, StringComparison.OrdinalIgnoreCase) && g.ParentGroupId == parentGroupId).FirstOrDefault();
}
if (group == null)
{
group = new Group
{
ForeignKey = groupKey,
ForeignId = groupId,
Name = name,
CreatedByPersonAliasId = ImportPersonAliasId,
GroupTypeId = groupTypeId,
Description = description
};
lookupContext.Groups.Add(group);
ImportedGroups.Add(group);
}
else
{
if (string.IsNullOrWhiteSpace(group.ForeignKey))
{
group.ForeignKey = groupKey;
group.ForeignId = groupId;
if (!ImportedGroups.Any(g => g.ForeignKey.Equals(groupKey, StringComparison.OrdinalIgnoreCase)))
{
ImportedGroups.Add(group);
}
}
lookupContext.Groups.Attach(group);
lookupContext.Entry(group).State = EntityState.Modified;
}
//
// Find and set the parent group. If not found it becomes a root level group.
//
parent = ImportedGroups.FirstOrDefault(g => g.ForeignKey == parentGroupKey);
if (parent != null)
{
group.ParentGroupId = parent.Id;
}
//
// Setup the date created/modified values from the data, if we have them.
//
group.CreatedDateTime = ParseDateOrDefault(createdDate, ImportDateTime);
group.ModifiedDateTime = ImportDateTime;
//
// Set the active state of this group.
//
if (active.ToUpper() == "NO")
{
group.IsActive = false;
}
else
{
group.IsActive = true;
}
return(group);
}
/// <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 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>
/// 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>
/// 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.");
}