Adding/Removing staff to school

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

\uD83D\uDCD8 Instructions

Check if individual exists

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

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

If the query returns a result, copy the BasicUnitID from the results
1. If multiple BUID are returned, locate the BUID where the [Role Email] address is the address you searched for
Go to the “Add Staff to School” section below

Create Person Record

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.
Log into the ASWBCentral site (https://application.aswb.org)
Navigate to the Create User URL https://application.aswb.org/SiteAdministrator/CreateUser
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
Press the Save button

Add Staff to School

Locate the school BUID in ASWBCentral

SELECT BasicUnitID FROM Schools WHERE SchoolName like '%<Replace with part of school name>&'

Locate the BasicUnitID and ContactID for the individual

SELECT BasicUnitID, ContactID FROM dev_person_expanded WHERE [Role] = 'Educator' AND [Role Email] = '<Replace with email>'

Insert the OrganizationContact Record

DECLARE @SchoolBUID INT = <Replace with School BUID from 1.a>
DECLARE @IndBUID INT = <Replace with Individual BUID from 2.a>
DECLARE @IndCID INT = <Replace with Individual Contact ID from 2.a>
DECLARE @JIRAID varchar(10) = 'LMR-<Replace with Jira NUMBER>'
INSERT INTO OrganizationContacts ([OrganizationID], [RelatedEntityID], [RoleID], [IsActive], [CreatedOn], [Notes], [ContactID])
VALUES (@SchoolBUID, @IndBUID,'54A2ED95-1740-4C27-B8B0-66D1193667BB',1,GETDATE(),FORMATMESSAGE('Per %s',@JIRAID),@IndCID)

Remove Staff from School

Run the following Script

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