Summary Table

Categories Total Count
PII 0
URL 0
DNS 0
EKL 0
IP 0
PORT 0
VsID 0
CF 0
AI 0
VPD 0
PL 0
Other 0

File Content

?-- =============================================================
-- Description: Returns a list of emails for administrators
-- Author: Steve Kopecky
-- Create date: 1/9/2019
-- Updated: 1/9/2019
--===============================================================
CREATE PROCEDURE [dbo].[uspGetAdministratorEmails]
-- Add the parameters for the stored procedure
@userId int,
@roleId tinyint,
@moduleId tinyint,
@accesslevelId tinyint,
@visnId tinyint,
@stationId int

AS
DECLARE @NATIONAL_LEVEL tinyint
DECLARE @REGION_LEVEL tinyint
DECLARE @VISN_LEVEL tinyint
DECLARE @STATION_LEVEL tinyint
DECLARE @STATION_VISN tinyint

-- Temp table for administrator emails for further refinement
DECLARE @adminTable table
(
UserRoleID int,
RoleID tinyint,
EmailAddress varchar(50),
[Name] varchar(50),
AccessLevelID tinyint,
VisnID tinyint,
StationID int,
RegionID int
)

-- Results table with email addresses for administrators
DECLARE @resultsTable table
(
UserRoleID int,
EmailAddress varchar(50),
[Name] varchar(50)
)


BEGIN
SET NOCOUNT ON;

BEGIN TRY

SET @NATIONAL_LEVEL = '1';
SET @REGION_LEVEL = '2';
SET @VISN_LEVEL = '3';
SET @STATION_LEVEL = '4';

DELETE FROM @adminTable;

-- Build temp table of active administrators for specified module
BEGIN
INSERT INTO @adminTable (UserRoleID, RoleID, EmailAddress, [Name], AccessLevelID, VisnID, StationID, RegionID)
SELECT seclyr.UserRoles.UserRoleID, seclyr.UserRoles.RoleID, seclyr.Users.EmailAddress, CONCAT(seclyr.Users.LastName, ', ', seclyr.Users.FirstName),
seclyr.UserRoles.AccessLevelID, seclyr.UserRoles.VisnID, seclyr.UserRoles.StationID, seclyr.UserRoles.StationID

FROM seclyr.UserRoles
INNER JOIN seclyr.Users ON seclyr.UserRoles.UserID = seclyr.Users.UserID
WHERE seclyr.Users.Inactive = 0
AND seclyr.UserRoles.RoleID = @roleId
AND AccessStatus = 'APPROVED'
AND seclyr.UserRoles.ModuleID = @moduleId
AND seclyr.UserRoles.PointOfContact = 1;
END

-- retrieve administrators from active table based on access level
BEGIN
INSERT INTO @resultsTable (UserRoleID, EmailAddress, [Name])
SELECT UserRoleID, EmailAddress, [Name]
FROM @adminTable
WHERE
(@accesslevelId = @NATIONAL_LEVEL AND AccessLevelID = @NATIONAL_LEVEL) -- National Level Access
OR -- Region level access
(@accesslevelId = @REGION_LEVEL AND AccessLevelID = @REGION_LEVEL)
OR -- VISN level access
(@accesslevelId = @VISN_LEVEL AND AccessLevelID = @VISN_LEVEL
AND (
VisnID = @visnId
OR VisnID = null
)
)
OR -- station level access
(@accesslevelId = @STATION_LEVEL AND AccessLevelID = @STATION_LEVEL
AND (
StationID = @stationId
OR StationID = null
)
)
END

BEGIN
-- Not checking for Region at present time, VISN/Staion are most likely to be used. Can be added as TODO
-- If no administrators seelcted for specified VISN level, return next level up administrators at national level
IF NOT EXISTS (SELECT 1 FROM @resultsTable) AND @accesslevelId = @VISN_LEVEL
BEGIN
INSERT INTO @resultsTable (UserRoleID, EmailAddress, Name)
SELECT UserRoleID, EmailAddress, Name
FROM @adminTable
WHERE AccessLevelID = @NATIONAL_LEVEL
END

-- If no administrators for specified station levle, return next level up administrators at VISN level
IF NOT EXISTS (SELECT 1 FROM @resultsTable) AND @accesslevelId = @STATION_LEVEL
BEGIN
BEGIN
SELECT @STATION_VISN = VisnID
FROM dbo.Stations
WHERE dbo.Stations.StationID = @stationId
END

INSERT INTO @resultsTable (UserRoleID, EmailAddress, Name)
SELECT UserRoleID, EmailAddress, Name
FROM @adminTable
WHERE VisnID = @STATION_VISN

-- If next level up (VISN) does not return any administrators, then default to national administrators
IF NOT EXISTS (SELECT 1 FROM @resultsTable)
BEGIN
INSERT INTO @resultsTable (UserRoleID, EmailAddress, Name)
SELECT UserRoleID, EmailAddress, Name
FROM @adminTable
WHERE AccessLevelID = @NATIONAL_LEVEL
END
END
END

-- return the administrators emails
SELECT DISTINCT UserRoleID, EmailAddress, [Name]
FROM @resultsTable

END TRY

BEGIN CATCH
PRINT 'There was an error processing the administrator emails' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH

END
GO