Page Comparison

...

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

Code Block

language	sql

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 @StaffUserID VARCHAR(256) = '<Replace with your username (radach,brutherford, etc>',
DECLARE @UserID LastUpdatedDateVARCHAR(50) = GETDATE()(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())

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

...

Copy and paste the script below to SSMS

Code Block

language	sql

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

Update the @SchoolID, @StaffEmail, AND @JiraID variables in the script you just copied into SSMS
Run the script
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

...

Versions Compared

Old Version 4

New Version 5

Key