Skip to end of banner
Go to start of banner

Adding/Removing staff to school

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

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

\uD83D\uDCD8 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 @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
SET
  UserID = @userID,
  RoleID = '54A2ED95-1740-4C27-B8B0-66D1193667BB',
  StartDate = GETDATE(),
  IsActive = 1,
  LastUpdatedBy = '<Replace with your username (radach,brutherford, etc>',
  LastUpdatedDate = GETDATE()
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 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) = 'LMR-'

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

  2. DECLARE @Email VARCHAR(256) = '<Replace with email>'
DECLARE @LMR INT = 0
DECLARE @Continue INT = 0

------ DO NOT TOUCH -----
DECLARE @EducatorRole VARCHAR(50) = N'54A2ED95-1740-4C27-B8B0-66D1193667BB'
IF (@Continue = 0)
BEGIN
	SELECT DISTINCT
		[Staff BUID] = dpe.BasicUnitID,
		[Staff ContactID] = dpe.ContactID,
		dpe.FirstName,
		dpe.LastName,
		dpe.[Role Email],
		dpe.Username,
		s.SchoolName,
		oc.*
	FROM
		dev_Person_Expanded dpe
		LEFT JOIN OrganizationContacts oc ON oc.RelatedEntityID = dpe.BasicUnitID and OC.ContactID = dpe.ContactID
		LEFT JOIN Schools s ON s.BasicUnitID = oc.OrganizationID
	WHERE
		dpe.[Role Email] = @Email
		AND dpe.[Role] LIKE '%educator%'
		AND oc.OrganizationContactID IS NOT NULL
END
IF (@Continue = 1)
BEGIN
	DECLARE @SchoolID INT
	DECLARE @StaffBUID INT
	DECLARE @StaffContactID INT
	DECLARE @UserID VARCHAR(255)
	DECLARE @SchoolName VARCHAR(255)
	DECLARE @StaffName VARCHAR(255)
	DECLARE @Counter INT

	SELECT TOP 1
		@SchoolID = ISNULL(s.BasicUnitID,0),
		@StaffBUID = ISNULL(dpe.BasicUnitID,0),
		@UserID = dpe.UserID,
		@SchoolName = s.SchoolName,
		@StaffName = dpe.FirstName + ' ' + dpe.LastName
	FROM
		dev_Person_Expanded dpe
		LEFT JOIN OrganizationContacts oc ON oc.RelatedEntityID = dpe.BasicUnitID and OC.ContactID = dpe.ContactID
		LEFT JOIN Schools s ON s.BasicUnitID = oc.OrganizationID
	WHERE
		dpe.[Role Email] = @Email
		AND dpe.[Role] LIKE '%educator%'
		AND oc.OrganizationContactID IS NOT NULL
	--Make sure we found a school and a user again
	IF (@SchoolID != 0 AND @StaffBUID != 0)
	BEGIN TRY
		UPDATE OrganizationContacts
		SET 
			IsActive = 0,
			RevokedOn = GETDATE(),
			Notes = FORMATMESSAGE('PER LMR-%d',@LMR)
		WHERE
			OrganizationID = @SchoolID
			AND RelatedEntityID = @StaffBUID
			AND RoleID = @EducatorRole
	END TRY
	BEGIN CATCH
		 PRINT FORMATMESSAGE('[%s] :: Unable to deactivate staff from organization due to the following error(s): [%s]: %s.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss'),CAST(ERROR_NUMBER() AS VARCHAR), ERROR_MESSAGE())
		 SET @Continue = 0
	END CATCH
	IF (@Continue = 1)
	SELECT 
		@Counter = COUNT(*)
	FROM
		OrganizationContacts oc
	WHERE
		RelatedEntityID = @StaffBUID
		AND RoleID = @EducatorRole
	IF (@Counter = 1)
	BEGIN TRY
		UPDATE anur
		SET
			IsActive = 0,
			RevokeDate = GETDATE()
		FROM
			AspNetUserRoles anur
		WHERE
			UserId = @UserID
			AND RoleID = @EducatorRole
	END TRY
	BEGIN CATCH
			PRINT FORMATMESSAGE('[%s] :: Unable to deactivate user role from ASPNetUserRoles table [%s]: %s.',FORMAT(GETDATE(),'MM-dd-yyyy HH:mm:ss'),CAST(ERROR_NUMBER() AS VARCHAR), ERROR_MESSAGE())
			SET @Continue = 0
	END CATCH
	ELSE IF (@Counter != 0 AND @Continue = 1)
	BEGIN
		PRINT FORMATMESSAGE('The staff member %s is associated to multiple organizations. The ASPNetUserRole has not been touched',@StaffName)
		SET @Continue = 0
	IF (@Continue = 1)
	BEGIN
		PRINT FORMATMESSAGE('We have removed %s from the school %s',@StaffName, @SchoolName)
	END
END
		
	
END

  • No labels