Adding/Removing staff to school

Owned by Robert Adach, created
Last updated: Mar 29, 2022
5 min read

This article will provide the instructions to add a staff member to an existing school.

 Instructions

Check if individual exists

  1. Query the ASWBCentral database for the staff member to see if an account exists for the user. You can use the query

    1. DECLARE @Email VARCHAR(256) = '<Replace with email>' SELECT * FROM dev_person_expanded dpe LEFT JOIN EmailAddresses ea ON ea.ContactID = dpe.ContactID WHERE dpe.[Role Email] = @Email or ea.[Address] = @Email

  2. If the query returns a result,

    1. AND one of the records returned has the RoleID listed is '54A2ED95-1740-4C27-B8B0-66D1193667BB', go to the “Add Staff to School” section below

    2. AND NONE of the records returned has the RoleID listed is '54A2ED95-1740-4C27-B8B0-66D1193667BB', go to the “Add Role to user” section below

  3. Go to the “Add Staff to School” section below

Create Person Record

  1. Gather contact information for the individual

    1. Usually we are not provided with address or phone number. Go to the schools website and obtain this information, if it is not easily accessible, use the ASWB phone number and address.

  2. Log into the ASWBCentral site (https://application.aswb.org)

  3. Navigate to the Create User URL https://application.aswb.org/SiteAdministrator/CreateUser

  4. Enter the necessary contact information

    1. The username and email field should be the email that was provided

    2. You may use any password as the individual will not receive a welcome email

    3. Make sure you select the “Educator - Admin” and “Social Worker - Admin” roles for the individual

  5. Press the Save button

  6. Go to the “Add Staff to School” section below

Add Role to User

  1. Run the following script against the ASWBCentral database to add the proper role to the user

    1. DECLARE @Email VARCHAR(256) = '<Replace with email>' DECLARE @StaffUserID VARCHAR(256) = '<Replace with your username (radach,brutherford, etc>' DECLARE @UserID VARCHAR(50) = (SELECT TOP 1 UserID FROM dev_person_expanded dpe LEFT JOIN EmailAddresses ea ON ea.ContactID = dpe.ContactID WHERE dpe.[Role Email] = @Email or ea.[Address] = @Email) INSERT INTO ASPNetUserRoles (UserID, RoleID, StartDate,IsActive,LastUpdatedBy,LastUpdatedDate) VALUES (@userID,'54A2ED95-1740-4C27-B8B0-66D1193667BB',GETDATE(),1,@StaffUserID,GETDATE())

  2. If the script completes successfully, go to the “Add Staff to School” section.

Add Staff to School

  1. Copy and paste the script below to SSMS

    1. DECLARE @SchoolID INT = 0 DECLARE @StaffEmail VARCHAR(255) = '' DECLARE @JIRAID varchar(10) = '' DECLARE @Continue INT = 0 DECLARE @SchoolBUID INT DECLARE @SchoolName VARCHAR(255) DECLARE @IndBUID INT DECLARE @IndCID INT DECLARE @StaffName VARCHAR(255) DECLARE @StaffActive VARCHAR(1) -- ***** SECTION A ***** BEGIN SELECT TOP 1 @IndBUID = [BasicUnitID], @IndCID = [ContactID], @StaffName = [FirstName] + ' ' + [LastName] FROM dev_Person_Expanded WHERE [Role Email] = @StaffEmail AND [RoleID] = '54A2ED95-1740-4C27-B8B0-66D1193667BB' -- ***** SECTION B ***** SELECT TOP 1 @SchoolName = SchoolName, @SchoolBUID = BasicUnitID FROM Schools WHERE SchoolCode = @SchoolID -- ***** SECTION C ***** SELECT @StaffActive = CASE IsActive WHEN 1 THEN 'Y' WHEN 0 THEN 'N' ELSE 'M' END FROM OrganizationContacts WHERE OrganizationID = @SchoolBUID AND RelatedEntityID = @IndBUID IF (@Continue = 0) BEGIN -- ***** SECTION D ***** IF (@IndBUID > 0 AND @IndCID > 0 AND (LEN(@StaffName) > 0 AND @StaffName IS NOT NULL)) BEGIN PRINT FORMATMESSAGE('[%s] :: A matching user account HAS been found. OK to continue.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss')) END ELSE BEGIN PRINT FORMATMESSAGE('[%s] :: A matching user account HAS NOT been found. Please correct information and try again.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss')) END -- ***** SECTION E ***** IF (LEN(@SchoolName) > 0 AND @SchoolName IS NOT NULL AND @SchoolID > 0) BEGIN PRINT FORMATMESSAGE('[%s] :: A matching school record HAS been found. OK to continue.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss')) END ELSE BEGIN PRINT FORMATMESSAGE('[%s] :: A matching school record HAS NOT been found. Please correct information and try again.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss')) END -- -- ***** SECTION F ***** IF EXISTS (SELECT 1 FROM OrganizationContacts WHERE OrganizationID = @SchoolBUID AND RelatedEntityID = @IndBUID AND IsActive = 1) BEGIN PRINT FORMATMESSAGE('[%s] :: The user is an active member of the school at this time. DO NOT continue.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss')) END ELSE IF EXISTS (SELECT 1 FROM OrganizationContacts WHERE OrganizationID = @SchoolBUID AND RelatedEntityID = @IndBUID AND IsActive = 0) BEGIN PRINT FORMATMESSAGE('[%s] :: The user IS NOT A active member of the school. OK to continue.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss')) END ELSE BEGIN PRINT FORMATMESSAGE('[%s] :: The user has never been a member of the school. OK to continue.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss')) END END IF (@Continue = 1) BEGIN -- ***** SECTION G ***** IF (@StaffActive = 'Y') BEGIN PRINT FORMATMESSAGE('[%s] :: The user is an active member of the school at this time. DO NOT continue.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss')) END -- ***** SECTION H ***** ELSE IF (@StaffActive = 'N') BEGIN TRY UPDATE OrganizationContacts SET IsActive = 1, RevokedOn = NULL, Notes = ISNULL(Notes,'') + @JIRAID WHERE OrganizationID = @SchoolBUID AND RelatedEntityID = @IndBUID AND IsActive = 0 END TRY BEGIN CATCH PRINT FORMATMESSAGE('[%s] :: Unable to update existing record for the following reason(s): [%s]: %s.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss'),CAST(ERROR_NUMBER() AS VARCHAR), ERROR_MESSAGE()) END CATCH -- ***** SECTION I ***** ELSE BEGIN TRY INSERT INTO OrganizationContacts ([OrganizationID], [RelatedEntityID], [RoleID], [IsActive], [CreatedOn], [Notes], [ContactID]) VALUES (@SchoolBUID, @IndBUID,'54A2ED95-1740-4C27-B8B0-66D1193667BB',1,GETDATE(),@JIRAID,@IndCID) PRINT FORMATMESSAGE('The faculty member %s has been added to the %s (%d) school. The individual will need to use the "I forgot my password" link on the login page to reset their password',@StaffName, @SchoolName, @SchoolID) END TRY BEGIN CATCH PRINT FORMATMESSAGE('[%s] :: Unable to insert organization contact role due to the following error. [%s]: %s.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss'),CAST(ERROR_NUMBER() AS VARCHAR), ERROR_MESSAGE()) END CATCH END END

  2. Update the @SchoolID, @StaffEmail, AND @JiraID variables in the script you just copied into SSMS

  3. Run the script

  4. If the message outputted by the script indicates that

    1. matching records can be found, update the @Continue variable to a value of 1

    2. no matching records can be found,

      1. verify the information added to the the @SchoolID, @StaffEmail, AND @JiraID variables are correct.

      2. if data matches what was provided in the Jira ticket

        1. Set ticket to Pending status

        2. As requester to provide the incorrect information

Remove Staff from School

  1. Run the following Script

Related articles