/// <summary> /// Maps the notes. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="totalRows">The total rows.</param> public void MapNotes(IQueryable <Row> tableData, long totalRows = 0) { var lookupContext = new RockContext(); var importedUsers = new UserLoginService(lookupContext).Queryable().AsNoTracking() .Where(u => u.ForeignId != null) .ToDictionary(t => t.ForeignId, t => t.PersonId); var noteList = new List <Note>(); if (totalRows == 0) { totalRows = tableData.Count(); } var completedItems = 0; var percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, $"Verifying note import ({totalRows:N0} found)."); foreach (var row in tableData.Where(r => r != null)) { var noteType = row["Note_Type_Name"] as string; var text = row["Note_Text"] as string; var individualId = row["Individual_ID"] as int?; var householdId = row["Household_ID"] as int?; var noteTypeActive = row["NoteTypeActive"] as bool?; var noteArchived = row["NoteArchived"] as bool?; var noteTextArchived = row["NoteTextArchived"] as bool?; var dateCreated = row["NoteCreated"] as DateTime?; // see if pre-import helper fix is present var noteArchivedFlag = row["NoteArchived"] as int?; var noteTextArchivedFlag = row["NoteTextArchived"] as int?; noteArchived = noteArchived.HasValue ? noteArchived : noteArchivedFlag > 0; noteTextArchived = noteTextArchived.HasValue ? noteTextArchived : noteTextArchivedFlag > 0; var noteExcluded = noteArchived == true || noteTextArchived == true; var personKeys = GetPersonKeys(individualId, householdId); if (personKeys != null && !string.IsNullOrWhiteSpace(text) && noteTypeActive == true && !noteExcluded) { int?creatorAliasId = null; var userId = row["NoteCreatedByUserID"] as int?; if (userId.HasValue && PortalUsers.ContainsKey((int)userId)) { creatorAliasId = PortalUsers[(int)userId]; } var noteTypeId = noteType.StartsWith("General", StringComparison.InvariantCultureIgnoreCase) ? (int?)PersonalNoteTypeId : null; var note = AddEntityNote(lookupContext, PersonEntityTypeId, personKeys.PersonId, string.Empty, text, false, false, noteType, noteTypeId, false, dateCreated, $"Note imported {ImportDateTime}", creatorAliasId); noteList.Add(note); completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, $"{completedItems:N0} notes imported ({percentComplete}% complete)."); } else if (completedItems % ReportingNumber < 1) { SaveNotes(noteList); ReportPartialProgress(); noteList.Clear(); } } } if (noteList.Any()) { SaveNotes(noteList); } ReportProgress(100, $"Finished note import: {completedItems:N0} notes imported."); }
/// <summary> /// Maps the individual contact notes. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="totalRows">The total rows.</param> public void MapIndividualContactNotes(IQueryable <Row> tableData, long totalRows = 0) { var lookupContext = new RockContext(); var importedNotes = new NoteService(lookupContext).Queryable().Where(n => n.ForeignId != null) .ToDictionary(n => n.ForeignId, n => n.Id); var importedRequests = new PrayerRequestService(lookupContext).Queryable().Where(r => r.ForeignId != null) .ToDictionary(r => r.ForeignId, r => r.Id); var noteList = new List <Note>(); int?confidentialNoteTypeId = null; if (totalRows == 0) { totalRows = tableData.Count(); } var completedItems = 0; var percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, $"Verifying contact notes ({totalRows:N0} found, {importedNotes.Count:N0} already exist)."); foreach (var row in tableData.Where(r => r != null)) { var userId = row["UserID"] as int?; var individualId = row["IndividualID"] as int?; var itemForeignKey = row["ContactInstItemID"] as int?; var createdDate = row["IndividualContactDatetime"] as DateTime?; var noteText = row["IndividualContactNote"] as string; var confidentialText = row["ConfidentialNote"] as string; var personKeys = GetPersonKeys(individualId, null); if (personKeys != null && (!string.IsNullOrWhiteSpace(noteText) || !string.IsNullOrWhiteSpace(confidentialText))) { int?creatorAliasId = null; if (userId.HasValue && PortalUsers.ContainsKey((int)userId)) { creatorAliasId = PortalUsers[(int)userId]; } var noteId = 0; var noteEntityId = personKeys.PersonId; var noteEntityTypeId = PersonEntityTypeId; var noteTypeId = PersonalNoteTypeId; // add a confidential note if (!string.IsNullOrWhiteSpace(confidentialText)) { var confidential = AddEntityNote(lookupContext, noteEntityTypeId, noteEntityId, string.Empty, confidentialText, false, false, "Confidential Note", confidentialNoteTypeId, false, createdDate, itemForeignKey.ToString()); confidentialNoteTypeId = confidential.NoteTypeId; noteList.Add(confidential); } // this is new or an update to timeline note if (importedNotes.ContainsKey(itemForeignKey)) { noteId = importedNotes[itemForeignKey]; } // note this as a prayer request comment else if (importedRequests.ContainsKey(itemForeignKey)) { noteEntityTypeId = PrayerRequestTypeId; noteEntityId = importedRequests[itemForeignKey]; noteTypeId = PrayerNoteTypeId; } // add the note text if (!string.IsNullOrWhiteSpace(noteText)) { var note = AddEntityNote(lookupContext, noteEntityTypeId, noteEntityId, string.Empty, noteText, false, false, null, noteTypeId, false, createdDate, itemForeignKey.ToString()); note.Id = noteId; noteList.Add(note); } completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, $"{completedItems:N0} notes imported ({percentComplete}% complete)."); } else if (completedItems % ReportingNumber < 1) { SaveNotes(noteList); ReportPartialProgress(); noteList.Clear(); } } } if (noteList.Any()) { SaveNotes(noteList); } ReportProgress(100, $"Finished contact note import: {completedItems:N0} notes imported."); }
/// <summary> /// Maps the contact form data. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="totalRows">The total rows.</param> public void MapContactFormData(IQueryable <Row> tableData, long totalRows = 0) { var lookupContext = new RockContext(); var importedCommunicationCount = new CommunicationService(lookupContext).Queryable().Count(c => c.ForeignKey != null); var importedNoteCount = new NoteService(lookupContext).Queryable().Count(n => n.ForeignKey != null); var prayerRequestors = new Dictionary <int, Person>(); var communicationList = new List <Communication>(); var prayerList = new List <PrayerRequest>(); var noteList = new List <Note>(); if (totalRows == 0) { totalRows = tableData.Count(); } var completedItems = 0; var percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, $"Verifying contact items ({totalRows:N0} found, {importedNoteCount + importedCommunicationCount:N0} already exist)."); foreach (var row in tableData.Where(r => r != null)) { // ContactFormData joins to IndividualContactNotes on ContactInstItemID var itemForeignKey = row["ContactInstItemID"] as int?; var householdId = row["HouseholdID"] as int?; var itemIndividualId = row["ContactItemIndividualID"] as int?; var individualId = row["ContactIndividualID"] as int?; var createdDate = row["ContactActivityDate"] as DateTime?; var modifiedDate = row["ContactDatetime"] as DateTime?; var approvalDate = row["ContactFormLastUpdatedDate"] as DateTime?; var itemType = row["ContactFormName"] as string; var itemStatus = row["ContactStatus"] as string; var itemCaption = row["ContactItemName"] as string; var noteText1 = row["ContactNote"] as string; var noteText2 = row["ContactItemNote"] as string; var itemUserId = row["ContactItemAssignedUserID"] as int?; var contactUserId = row["ContactAssignedUserID"] as int?; var initialContactUserId = row["InitialContactCreatedByUserID"] as int?; var isConfidential = row["IsContactItemConfidential"] as int?; var itemText = !string.IsNullOrWhiteSpace(noteText1) ? $"{noteText1}<br>{noteText2}" : noteText2 ?? string.Empty; // look up the person this contact form is for var personKeys = GetPersonKeys(itemIndividualId ?? individualId, householdId); if (personKeys != null && (!string.IsNullOrWhiteSpace(itemCaption) || !string.IsNullOrWhiteSpace(itemText))) { var assignedUserId = itemUserId ?? contactUserId ?? initialContactUserId ?? 0; var userPersonAliasId = PortalUsers.ContainsKey(assignedUserId) ? (int?)PortalUsers[assignedUserId] : null; if (itemType.Equals("Email", StringComparison.CurrentCultureIgnoreCase)) { // create the recipient list for this contact var recipients = new List <CommunicationRecipient> { new CommunicationRecipient { Status = CommunicationRecipientStatus.Delivered, PersonAliasId = personKeys.PersonAliasId, CreatedDateTime = createdDate ?? modifiedDate, CreatedByPersonAliasId = userPersonAliasId, ModifiedByPersonAliasId = userPersonAliasId, ForeignKey = personKeys.PersonForeignId.ToString(), ForeignId = personKeys.PersonForeignId } }; // create an email record for this contact form var emailSubject = !string.IsNullOrWhiteSpace(itemCaption) ? itemCaption.Left(100) : itemText.Left(100); var communication = AddCommunication(lookupContext, EmailCommunicationMediumTypeId, emailSubject, itemText, false, CommunicationStatus.Approved, recipients, false, createdDate ?? modifiedDate, itemForeignKey.ToString(), userPersonAliasId); communicationList.Add(communication); } else if (!string.IsNullOrWhiteSpace(itemCaption) && itemCaption.EndsWith("Prayer Request", StringComparison.CurrentCultureIgnoreCase)) { // create a prayer request Person requestor = null; prayerRequestors.TryGetValue(personKeys.PersonId, out requestor); if (requestor == null) { requestor = lookupContext.People.FirstOrDefault(p => p.Id.Equals(personKeys.PersonId)); prayerRequestors.Add(personKeys.PersonId, requestor); } var request = AddPrayerRequest(lookupContext, null, personKeys.PersonAliasId, requestor.FirstName, requestor.LastName, requestor.Email, itemText ?? itemCaption, string.Empty, !itemStatus.Equals("Closed", StringComparison.CurrentCultureIgnoreCase), false, createdDate ?? modifiedDate, approvalDate, itemForeignKey.ToString(), userPersonAliasId); if (request != null) { prayerList.Add(request); } } else { //strip campus from type var campusId = GetCampusId(itemType); if (campusId.HasValue) { itemType = StripPrefix(itemType, campusId); } // create a note for this contact form var note = AddEntityNote(lookupContext, PersonEntityTypeId, personKeys.PersonId, itemCaption, itemText, false, false, itemType, null, false, createdDate ?? modifiedDate, itemForeignKey.ToString(), userPersonAliasId); noteList.Add(note); } completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, $"{completedItems:N0} contact items imported ({percentComplete}% complete)."); } else if (completedItems % ReportingNumber < 1) { SaveCommunications(communicationList); SavePrayerRequests(prayerList); SaveNotes(noteList); ReportPartialProgress(); communicationList.Clear(); prayerList.Clear(); noteList.Clear(); } } } if (communicationList.Any() || noteList.Any()) { SaveCommunications(communicationList); SavePrayerRequests(prayerList); SaveNotes(noteList); } ReportProgress(100, $"Finished contact item import: {completedItems:N0} items imported."); }
/// <summary> /// Translates the individual contact notes. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="totalRows">The total rows.</param> public void TranslateIndividualContactNotes(IQueryable <Row> tableData, long totalRows = 0) { var lookupContext = new RockContext(); var activityTypeService = new ConnectionActivityTypeService(lookupContext); var connectedActivityType = activityTypeService.Get(Rock.SystemGuid.ConnectionActivityType.CONNECTED.AsGuid()); var assignedActivityType = activityTypeService.Get(Rock.SystemGuid.ConnectionActivityType.ASSIGNED.AsGuid()); var importedNotes = new NoteService(lookupContext).Queryable().Where(n => n.ForeignId != null) .ToDictionary(n => n.ForeignId, n => n.Id); var importedConnectionRequests = new ConnectionRequestService(lookupContext).Queryable().Where(r => r.ForeignId != null) .ToDictionary(r => r.ForeignId, r => new { Id = r.Id, OpportunityId = r.ConnectionOpportunityId, ConnectorId = r.ConnectorPersonAliasId, State = r.ConnectionState }); var importedPrayerRequests = new PrayerRequestService(lookupContext).Queryable().Where(r => r.ForeignId != null) .ToDictionary(r => r.ForeignId, r => r.Id); int?confidentialNoteTypeId = null; var noteList = new List <Note>(); var activityList = new List <ConnectionRequestActivity>(); if (totalRows == 0) { totalRows = tableData.Count(); } var completedItems = 0; var percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, $"Verifying contact notes ({totalRows:N0} found, {importedNotes.Count:N0} already exist)."); foreach (var row in tableData.Where(r => r != null)) { var userId = row["UserID"] as int?; var individualId = row["IndividualID"] as int?; var relatedForeignKey = row["ContactInstItemID"] as int?; var itemForeignKey = row["IndividualContactID"] as int?; var createdDate = row["IndividualContactDatetime"] as DateTime?; var noteText = row["IndividualContactNote"] as string; var confidentialText = row["ConfidentialNote"] as string; var personKeys = GetPersonKeys(individualId, null); int?creatorAliasId = null; if (userId.HasValue && PortalUsers.ContainsKey((int)userId)) { creatorAliasId = PortalUsers[(int)userId]; } if (personKeys != null && (!string.IsNullOrWhiteSpace(noteText) || !string.IsNullOrWhiteSpace(confidentialText))) { // this is a connection request comment if (importedConnectionRequests.ContainsKey(relatedForeignKey)) { var parentRequest = importedConnectionRequests[relatedForeignKey]; var activityType = parentRequest.State == ConnectionState.Active ? assignedActivityType : connectedActivityType; if (parentRequest != null && activityType != null) { var requestActivity = AddConnectionActivity(parentRequest.OpportunityId, noteText, createdDate, creatorAliasId ?? parentRequest.ConnectorId, activityType.Id, relatedForeignKey.ToString()); requestActivity.ConnectionRequestId = parentRequest.Id; activityList.Add(requestActivity); } } else // this is a new note or prayer request comment { var noteId = 0; var noteEntityId = personKeys.PersonId; var noteEntityTypeId = PersonEntityTypeId; var noteTypeId = PersonalNoteTypeId; // add a confidential note if (!string.IsNullOrWhiteSpace(confidentialText)) { var confidential = AddEntityNote(lookupContext, noteEntityTypeId, noteEntityId, string.Empty, confidentialText, false, false, "Confidential Note", confidentialNoteTypeId, false, createdDate, relatedForeignKey.ToString()); confidentialNoteTypeId = confidential.NoteTypeId; noteList.Add(confidential); } // this is new or an update to timeline note if (importedNotes.ContainsKey(relatedForeignKey)) { noteId = importedNotes[relatedForeignKey]; } // this is a prayer request comment else if (importedPrayerRequests.ContainsKey(relatedForeignKey)) { noteEntityTypeId = PrayerRequestTypeId; noteEntityId = importedPrayerRequests[relatedForeignKey]; noteTypeId = PrayerNoteTypeId; } // add the note text if (!string.IsNullOrWhiteSpace(noteText)) { var note = AddEntityNote(lookupContext, noteEntityTypeId, noteEntityId, string.Empty, noteText, false, false, null, noteTypeId, false, createdDate, itemForeignKey.ToString()); note.Id = noteId; noteList.Add(note); } } completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, $"{completedItems:N0} notes imported ({percentComplete}% complete)."); } else if (completedItems % ReportingNumber < 1) { SaveNotes(noteList); SaveActivities(activityList); ReportPartialProgress(); noteList.Clear(); activityList.Clear(); } } } if (noteList.Any() || activityList.Any()) { SaveNotes(noteList); SaveActivities(activityList); } ReportProgress(100, $"Finished contact note import: {completedItems:N0} notes imported."); }