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