private List <PortalUsers> FetchData(SqlCommand cmd)
{
SqlConnection con = cmd.Connection;
List <PortalUsers> portalusers = null;
con.Open();
using (con)
{
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
portalusers = new List <PortalUsers>();
while (dr.Read())
{
PortalUsers uPortalUsers = new PortalUsers();
uPortalUsers.Id = Convert.ToString(dr["id"]);
uPortalUsers.Username = Convert.ToString(dr["username"]);
uPortalUsers.Password = Convert.ToString(dr["pass_word"]);
uPortalUsers.UserType = Convert.ToString(dr["user_type"]);
portalusers.Add(uPortalUsers);
}
portalusers.TrimExcess();
}
}
return(portalusers);
}
/// <summary>
/// Return All Users From The Database
/// </summary>
/// <returns></returns>
public static PortalUsers getAllUsers()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "getAllUsers";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader reader = cmd.ExecuteReader();
PortalUsers users = new PortalUsers();
while (reader.Read())
{
PortalUser user = new PortalUser();
user.ID = (Int32)reader["Id"];
user.UserName = (String)reader["username"];
user.Password = (String)reader["password"];
user.FirstName = (String)reader["firstname"];
user.LastName = (String)reader["lastname"];
users.Add(user);
}
return(users);
}
public void AddPortalUser(PortalUsers pu)
{
SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[portal_users] ([username],[pass_word],[user_type]) VALUES (@Username,@Password,@UserType)", DALUtil.getConnection());
cmd.Parameters.AddWithValue("@Username", (pu.Username == null) ? Convert.DBNull : pu.Username);
cmd.Parameters.AddWithValue("@Password", (pu.Password == null) ? Convert.DBNull : pu.Password);
cmd.Parameters.AddWithValue("@UserType", (pu.UserType == null) ? Convert.DBNull : pu.UserType);
executeCommand(cmd);
}
public ActionResult AddPU(AllClasses ac)
{
PortalUsers pU = new PortalUsers();
pU.Username = ac.PortalUser.Username;
pU.Password = CryptoUtility.Encrypt(ac.PortalUser.Password);
pU.UserType = ac.PortalUser.UserType;
new BusinessLogic().AddPU(pU);
return(RedirectToAction("Portalusers", "Home"));
}
/// <summary>
/// Maps the users.
/// </summary>
/// <param name="tableData">The table data.</param>
/// <param name="totalRows">The total rows.</param>
public void MapUsers(IQueryable <Row> tableData, long totalRows = 0)
{
var lookupContext = new RockContext();
var personService = new PersonService(lookupContext);
var rockAuthenticatedTypeId = EntityTypeCache.Get(typeof(Rock.Security.Authentication.Database)).Id;
var staffGroupId = new GroupService(lookupContext).GetByGuid(new Guid(Rock.SystemGuid.Group.GROUP_STAFF_MEMBERS)).Id;
var memberGroupRoleId = new GroupTypeRoleService(lookupContext).Queryable()
.Where(r => r.Guid.Equals(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_SECURITY_GROUP_MEMBER)))
.Select(r => r.Id).FirstOrDefault();
var userLoginService = new UserLoginService(lookupContext);
var importedUserCount = userLoginService.Queryable().Count(u => u.ForeignId != null);
var newUserLogins = new List <UserLogin>();
var newStaffMembers = new List <GroupMember>();
var updatedPersonList = new List <Person>();
if (totalRows == 0)
{
totalRows = tableData.Count();
}
var completedItems = 0;
var percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, $"Verifying user import ({totalRows:N0} found, {importedUserCount:N0} already exist).");
foreach (var row in tableData.Where(r => r != null))
{
var individualId = row["LinkedIndividualID"] as int?;
var userName = row["UserLogin"] as string;
var userId = row["UserID"] as int?;
if (userId.HasValue && individualId.HasValue && !string.IsNullOrWhiteSpace(userName))
{
var personKeys = GetPersonKeys(individualId, null);
if (personKeys != null)
{
var createdDate = row["UserCreatedDate"] as DateTime?;
var userEmail = row["UserEmail"] as string;
var userTitle = row["UserTitle"] as string;
var userPhone = row["UserPhone"] as string;
var isEnabled = row["IsUserEnabled"] as bool?;
var isStaff = row["IsStaff"] as bool?;
var isActive = isEnabled ?? false;
var user = AddUserLogin(lookupContext, rockAuthenticatedTypeId, personKeys.PersonId, userName.Trim(), null, isEnabled, false, createdDate, userId.ToString(), ImportPersonAliasId);
if (user != null)
{
// track the user's id and person alias for use with notes
PortalUsers.AddOrReplace(( int )userId, personKeys.PersonAliasId);
if (isStaff == true)
{
// add this user to the staff group
var staffMember = new GroupMember
{
GroupId = staffGroupId,
PersonId = personKeys.PersonId,
GroupRoleId = memberGroupRoleId,
CreatedDateTime = createdDate,
CreatedByPersonAliasId = ImportPersonAliasId,
GroupMemberStatus = isActive ? GroupMemberStatus.Active : GroupMemberStatus.Inactive
};
newStaffMembers.Add(staffMember);
}
// set user login email to person's primary email if one isn't set
if (!string.IsNullOrWhiteSpace(userEmail) && userEmail.IsEmail())
{
var person = !updatedPersonList.Any(p => p.Id == personKeys.PersonId)
? personService.Queryable(includeDeceased: true).FirstOrDefault(p => p.Id == personKeys.PersonId)
: updatedPersonList.FirstOrDefault(p => p.Id == personKeys.PersonId);
if (person != null && string.IsNullOrWhiteSpace(person.Email))
{
person.Email = userEmail.Left(75);
person.EmailNote = userTitle;
person.IsEmailActive = isEnabled != false;
person.EmailPreference = EmailPreference.EmailAllowed;
lookupContext.SaveChanges(DisableAuditing);
updatedPersonList.Add(person);
}
}
newUserLogins.Add(user);
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, $"{completedItems:N0} users imported ({percentComplete}% complete).");
}
if (completedItems % ReportingNumber < 1)
{
SaveUsers(newUserLogins, newStaffMembers);
updatedPersonList.Clear();
newUserLogins.Clear();
newStaffMembers.Clear();
ReportPartialProgress();
}
}
}
}
else
{
LogException("User Import", $"User: {userId} - UserName: {userName} is not linked to a person or already exists.");
}
}
if (newUserLogins.Any())
{
SaveUsers(newUserLogins, newStaffMembers);
}
ReportProgress(100, $"Finished user import: {completedItems:N0} users imported.");
}
/// <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 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>
/// 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>
/// 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.");
}
public void AddPU(PortalUsers p)
{
new PortalUsersDAL().AddPortalUser(p);
}
