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