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