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 user's permitted list of parent stations
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE [seclyr].[uspGetUserParentStations]
-- Add the parameters for the stored procedure here
@userId int,
@moduleIds varchar(max)
AS
DECLARE @ACCESS_LEVEL_REGION tinyint,
@ACCESS_LEVEL_CCNREGION tinyint,
@ACCESS_LEVEL_VISN tinyint,
@ACCESS_LEVEL_STATION tinyint,
@ACCESS_STATUS_APPROVED varchar(10),
@isNationalUser bit,
@minModuleID tinyint
DECLARE @resultsTable table
(
ModuleID tinyint,
RegionID tinyint, --this is VHA Region
CCNRegionID tinyint, --this is CCN Region
VisnID tinyint,
StationID smallint,
ParentStationID smallint,
Station3N varchar(10),
Station6A varchar(10),
StationDisplayName varchar(100),
StationName varchar(100),
IsParent bit,
Inactive bit
)
DECLARE @modulesTable table
(
ModuleID tinyint
)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN
--Set necessary variables
SET @ACCESS_STATUS_APPROVED = 'APPROVED';
SELECT @ACCESS_LEVEL_REGION = seclyr.ufnGetAccessLevelConstant('VHA Region');
SELECT @ACCESS_LEVEL_CCNREGION = seclyr.ufnGetAccessLevelConstant('CCN Region');
SELECT @ACCESS_LEVEL_VISN = seclyr.ufnGetAccessLevelConstant('Visn');
SELECT @ACCESS_LEVEL_STATION = seclyr.ufnGetAccessLevelConstant('Station');
--will need to start the wrapper here for all permutations of appID--
--build a table of various appId's--
IF @moduleIds IS NULL
BEGIN
INSERT INTO @modulesTable (ModuleID)
SELECT ModuleID FROM seclyr.Modules;
END
ELSE
BEGIN
INSERT INTO @modulesTable (ModuleID)
SELECT [Value] FROM ufnSplit(',', @moduleIds);
END
--Set the min rowId to start the while loop
SELECT @minModuleID = MIN(ModuleID)
FROM @modulesTable;
WHILE @minModuleID IS NOT NULL
BEGIN
SELECT @isNationalUser = seclyr.ufnIsNationalUser(@userId, @minModuleID);
--If is a national user or no user id is returned, then return all
IF @userId Is Null OR @userId = 0 OR @isNationalUser = 1
BEGIN
WITH LookupTable (RegionID, CCNRegionID, VisnID, StationID, ParentStationID, Station3N, Station6A,
StationDisplayName, StationName, IsParent, Inactive) AS
(
SELECT DISTINCT t.RegionID, t.CCNRegionID, t.VisnID, t.StationID, t.ParentStationID, t.Station3N, t.Station6A,
'V' + VisnCode + ': ' + t.Station3N + ': ' + t.StationName,
t.StationName, t.ParentStation, t.Inactive
FROM Stations t
INNER JOIN Visns ON t.VisnID = Visns.VisnID
WHERE ParentStation = 1
)
INSERT INTO @resultsTable (ModuleID, RegionID, CCNRegionID, VisnID, StationID, ParentStationID,
Station3N, Station6A, StationDisplayName, StationName, IsParent, Inactive)
SELECT @minModuleID, RegionID, CCNRegionID, VisnID, StationID, ParentStationID,
Station3N, Station6A, StationDisplayName, StationName, IsParent, Inactive
FROM LookupTable;
END
ELSE
BEGIN
WITH LookupTable (RegionID, CCNRegionID, VisnID, StationID, ParentStationID, Station3N, Station6A,
StationDisplayName, StationName, IsParent, Inactive) AS
(
--check VHA Region permissions
SELECT DISTINCT t.RegionID, t.CCNRegionID, t.VisnID, t.StationID, t.ParentStationID, t.Station3N, t.Station6A,
'V' + VisnCode + ': ' + t.Station3N + ': ' + t.StationName,
t.StationName, t.ParentStation, t.Inactive
FROM Stations t
INNER JOIN Visns ON t.VisnID = Visns.VisnID
WHERE EXISTS(SELECT DISTINCT 1 FROM seclyr.UserRoles
WHERE t.RegionID = seclyr.UserRoles.RegionID
AND UserID = @userId
AND ModuleID = @minModuleID
AND AccessLevelID = @ACCESS_LEVEL_REGION
AND AccessStatus = @ACCESS_STATUS_APPROVED
AND ParentStation = 1)
--check CCN Region permissions
UNION
SELECT DISTINCT t.RegionID, t.CCNRegionID, t.VisnID, t.StationID, t.ParentStationID, t.Station3N, t.Station6A,
'V' + VisnCode + ': ' + t.Station3N + ': ' + t.StationName,
t.StationName, t.ParentStation, t.Inactive
FROM Stations t
INNER JOIN Visns ON t.VisnID = Visns.VisnID
WHERE EXISTS(SELECT DISTINCT 1 FROM seclyr.UserRoles
WHERE t.CCNRegionID = seclyr.UserRoles.CCNRegionID
AND UserID = @userId
AND ModuleID = @minModuleID
AND AccessLevelID = @ACCESS_LEVEL_CCNREGION
AND AccessStatus = @ACCESS_STATUS_APPROVED
AND ParentStation = 1)
--check Visn permissions
UNION
SELECT DISTINCT t.RegionID, t.CCNRegionID, t.VisnID, t.StationID, t.ParentStationID, t.Station3N, t.Station6A,
'V' + VisnCode + ': ' + t.Station3N + ': ' + t.StationName,
t.StationName, t.ParentStation, t.Inactive
FROM Stations t
INNER JOIN Visns ON t.VisnID = Visns.VisnID
INNER JOIN seclyr.UserRoles ON t.VisnID = seclyr.UserRoles.VisnID
WHERE UserID = @userId
AND ModuleID = @minModuleID
AND AccessLevelID = @ACCESS_LEVEL_VISN
AND AccessStatus = @ACCESS_STATUS_APPROVED
AND ParentStation = 1
--check Station permissions
UNION
SELECT DISTINCT t.RegionID, t.CCNRegionID, t.VisnID, t.StationID, t.ParentStationID, t.Station3N, t.Station6A,
'V' + VisnCode + ': ' + t.Station3N + ': ' + t.StationName,
t.StationName, t.ParentStation, t.Inactive
FROM Stations t
INNER JOIN Visns ON t.VisnID = Visns.VisnID
INNER JOIN seclyr.UserRoles ON t.StationID = seclyr.UserRoles.StationID
WHERE UserID = @userId
AND ModuleID = @minModuleID
AND AccessLevelID = @ACCESS_LEVEL_STATION
AND AccessStatus = @ACCESS_STATUS_APPROVED
AND ParentStation = 1
)
--same insertion to appresults
INSERT INTO @resultsTable (ModuleID, RegionID, CCNRegionID, VisnID, StationID, ParentStationID,
Station3N, Station6A, StationDisplayName, StationName, IsParent, Inactive)
SELECT @minModuleID, RegionID, CCNRegionID, VisnID, StationID, ParentStationID,
Station3N, Station6A, StationDisplayName, StationName, IsParent, Inactive
FROM LookupTable;
END
--Iterate at the end of the loop to the next RowID
SELECT @minModuleID = MIN(ModuleID)
FROM @modulesTable
WHERE ModuleID > @minModuleID;
END
END
--Return results
SELECT DISTINCT CAST(ROW_NUMBER() OVER(ORDER BY StationID) AS int) AS 'RowID'
, ModuleID
, RegionID
, CCNRegionID
, VisnID
, StationID
, ParentStationID
, Station3N
, Station6A
, StationDisplayName
, StationName
, IsParent
, Inactive
FROM @resultsTable
ORDER BY ModuleID, VisnID, Station3N, Station6A;
END TRY
BEGIN CATCH
PRINT 'There was an error processing the user parent stations' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END
GO