/// <summary>
/// Translates the contact form data.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
public void TranslateContactFormData(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);
// Involvement Connection Type
var defaultConnectionType = new ConnectionTypeService(lookupContext).Get("DD565087-A4BE-4943-B123-BF22777E8426".AsGuid());
var opportunities = new ConnectionOpportunityService(lookupContext).Queryable().ToList();
var statuses = new ConnectionStatusService(lookupContext).Queryable().ToList();
var noContactStatus = statuses.FirstOrDefault(s => s.Name.Equals("No Contact", StringComparison.InvariantCultureIgnoreCase));
var prayerRequestors = new Dictionary <int, Person>();
var communicationList = new List <Communication>();
var connectionList = new List <ConnectionRequest>();
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 hasCaption = !string.IsNullOrWhiteSpace(itemCaption);
var personKeys = GetPersonKeys(itemIndividualId ?? individualId, householdId);
if (personKeys != null && (hasCaption || !string.IsNullOrWhiteSpace(itemText)))
{
var assignedUserId = itemUserId ?? contactUserId ?? initialContactUserId ?? 0;
var userPersonAliasId = PortalUsers.ContainsKey(assignedUserId) ? (int?)PortalUsers[assignedUserId] : null;
// 99% of the Email types have no other info
if (itemType.Equals("Email", StringComparison.CurrentCultureIgnoreCase))
{
// create the recipient list for this contact
var recipients = new List <CommunicationRecipient> {
new CommunicationRecipient {
SendDateTime = createdDate ?? modifiedDate,
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 (itemType.EndsWith("Connection Card", StringComparison.CurrentCultureIgnoreCase))
{
// lookup connection opportunity
var opportunity = opportunities.FirstOrDefault(o => o.Name.Equals(itemType, StringComparison.InvariantCultureIgnoreCase) || (o.ForeignKey != null && o.ForeignKey.Equals(itemType, StringComparison.InvariantCultureIgnoreCase)));
if (opportunity == null)
{
opportunity = AddConnectionOpportunity(lookupContext, defaultConnectionType.Id, createdDate, itemType, string.Empty, true, itemForeignKey.ToString());
opportunities.Add(opportunity);
}
// create a connection request
var requestStatus = statuses.FirstOrDefault(s => s.Name.Equals(itemStatus, StringComparison.InvariantCultureIgnoreCase)) ?? noContactStatus;
var requestState = itemStatus.Equals("Closed", StringComparison.InvariantCultureIgnoreCase) ? ConnectionState.Connected : ConnectionState.Active;
var request = AddConnectionRequest(opportunity, itemForeignKey.ToString(), createdDate, modifiedDate, requestStatus.Id, requestState, !string.IsNullOrWhiteSpace(itemText) ? $"{itemCaption} - {itemText}" : itemCaption ?? string.Empty, approvalDate, personKeys.PersonAliasId, userPersonAliasId);
connectionList.Add(request);
}
else if (hasCaption && 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);
SaveConnectionRequests(connectionList);
SavePrayerRequests(prayerList);
SaveNotes(noteList);
ReportPartialProgress();
communicationList.Clear();
connectionList.Clear();
prayerList.Clear();
noteList.Clear();
}
}
}
if (communicationList.Any() || connectionList.Any() || noteList.Any())
{
SaveCommunications(communicationList);
SaveConnectionRequests(connectionList);
SavePrayerRequests(prayerList);
SaveNotes(noteList);
}
ReportProgress(100, $"Finished contact item import: {completedItems:N0} items imported.");
}
/// <summary>
/// Loads the ConnectionRequests.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadConnectionRequest(CSVInstance csvData)
{
var lookupContext = new RockContext();
var connectionTypes = new ConnectionTypeService(lookupContext).Queryable().ToList();
var activityTypes = new ConnectionActivityTypeService(lookupContext).Queryable().ToList();
var opportunities = new ConnectionOpportunityService(lookupContext).Queryable().ToList();
var statuses = new ConnectionStatusService(lookupContext).Queryable().ToList();
var requests = new ConnectionRequestService(lookupContext).Queryable().ToList();
var newRequests = new List <ConnectionRequest>();
var newActivities = new List <ConnectionRequestActivity>();
var completedItems = 0;
ReportProgress(0, string.Format("Verifying connection request import ({0:N0} already imported).", requests.Count(n => n.ForeignKey != null)));
ConnectionType connectionType = null;
ConnectionOpportunity opportunity = null;
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
var oForeignKey = row[OpportunityForeignKey] as string;
var oName = row[OpportunityName] as string;
var cType = row[ConnectionType] as string;
var oDescription = row[OpportunityDescription] as string;
var oActive = row[OpportunityActive].AsBoolean();
var oCreatedDate = row[OpportunityCreated].AsDateTime();
var oModifiedDate = row[OpportunityModified].AsDateTime();
var rForeignKey = row[RequestForeignKey] as string;
var rPersonId = row[RequestPersonId].AsIntegerOrNull();
var rConnectorId = row[RequestConnectorId].AsIntegerOrNull();
var rCreatedDate = row[RequestCreated].AsDateTime();
var rModifiedDate = row[RequestModified].AsDateTime();
var rStatus = row[RequestStatus] as string;
var rState = row[RequestState].AsIntegerOrNull();
var rComments = row[RequestComments] as string;
var rFollowUp = row[RequestFollowUp].AsDateTime();
var aType = row[ActivityType] as string;
var aNote = row[ActivityNote] as string;
var aCreatedDate = row[ActivityDate].AsDateTime();
var aConnectorId = row[ActivityConnectorId].AsIntegerOrNull();
// lookup or reuse connection type
if (connectionType == null || !connectionType.Name.Equals(cType, StringComparison.OrdinalIgnoreCase))
{
connectionType = connectionTypes.FirstOrDefault(t => t.Name.Equals(cType, StringComparison.OrdinalIgnoreCase));
}
if (connectionType == null)
{
connectionType = AddConnectionType(lookupContext, cType);
connectionTypes.Add(connectionType);
}
if (connectionType != null && !string.IsNullOrWhiteSpace(oName) && GetPersonKeys(rPersonId) != null)
{
// lookup, reuse, or create connection opportunity
if (opportunity == null || !opportunity.ForeignKey.Equals(oForeignKey, StringComparison.OrdinalIgnoreCase))
{
opportunity = opportunities.FirstOrDefault(o => (o.ForeignKey != null && o.ForeignKey.Equals(oForeignKey, StringComparison.OrdinalIgnoreCase)) || o.Name.Equals(oName, StringComparison.OrdinalIgnoreCase));
}
if (opportunity == null)
{
opportunity = AddConnectionOpportunity(lookupContext, connectionType.Id, oCreatedDate, oName, oDescription, oActive, oForeignKey);
opportunities.Add(opportunity);
}
else if (opportunity.ForeignKey == null)
{
opportunity.ForeignKey = oForeignKey;
opportunity.ForeignId = oForeignKey.AsIntegerOrNull();
lookupContext.SaveChanges();
}
// lookup, reuse, or create connection request
var requestStatus = statuses.FirstOrDefault(s => s.Name.Equals(rStatus, StringComparison.OrdinalIgnoreCase) && s.ConnectionTypeId.HasValue && s.ConnectionTypeId.Value == connectionType.Id);
if (requestStatus == null)
{
requestStatus = AddConnectionStatus(lookupContext, rStatus, connectionType.Id);
statuses.Add(requestStatus);
}
var requestor = GetPersonKeys(rPersonId);
var requestConnector = rConnectorId.HasValue ? GetPersonKeys(rConnectorId) : null;
var request = requests.FirstOrDefault(r => r.ForeignKey != null && r.ForeignKey.Equals(rForeignKey, StringComparison.OrdinalIgnoreCase))
?? newRequests.FirstOrDefault(r => r.ForeignKey != null && r.ForeignKey.Equals(rForeignKey, StringComparison.OrdinalIgnoreCase));
if (request == null && requestor != null && requestStatus != null)
{
request = AddConnectionRequest(opportunity, rForeignKey, rCreatedDate, rModifiedDate, requestStatus.Id, ( ConnectionState )rState, rComments, rFollowUp, requestor.PersonAliasId, requestConnector?.PersonAliasId);
newRequests.Add(request);
}
// create activity
if (!string.IsNullOrWhiteSpace(aType))
{
var activityConnector = aConnectorId.HasValue ? GetPersonKeys(aConnectorId) : null;
var activityType = activityTypes.FirstOrDefault(t => t.Name.Equals(aType, StringComparison.OrdinalIgnoreCase));
if (request != null && activityType != null)
{
var activity = AddConnectionActivity(opportunity.Id, aNote, aCreatedDate, activityConnector?.PersonAliasId, activityType.Id, rForeignKey);
if (request.Id > 0)
{
activity.ConnectionRequestId = request.Id;
newActivities.Add(activity);
}
else
{
request.ConnectionRequestActivities.Add(activity);
}
}
}
completedItems++;
if (completedItems % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} requests processed.", completedItems));
}
if (completedItems % ReportingNumber < 1)
{
SaveConnectionRequests(newRequests, newActivities);
ReportPartialProgress();
requests.AddRange(newRequests);
newRequests.Clear();
newActivities.Clear();
}
}
}
if (newRequests.Count > 0 || newActivities.Count > 0)
{
SaveConnectionRequests(newRequests, newActivities);
}
ReportProgress(100, string.Format("Finished connection request import: {0:N0} requests imported.", completedItems));
return(completedItems);
}