You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
59 lines
1.8 KiB
59 lines
1.8 KiB
-- Security Task Read By UserId Status |
|
-- Stored Procedure: ReadByUserIdStatus |
|
CREATE OR ALTER PROCEDURE [dbo].[SecurityTask_ReadByUserIdStatus] |
|
@UserId UNIQUEIDENTIFIER, |
|
@Status TINYINT = NULL |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
|
|
SELECT |
|
ST.Id, |
|
ST.OrganizationId, |
|
ST.CipherId, |
|
ST.Type, |
|
ST.Status, |
|
ST.CreationDate, |
|
ST.RevisionDate |
|
FROM |
|
[dbo].[SecurityTaskView] ST |
|
INNER JOIN |
|
[dbo].[OrganizationUserView] OU ON OU.[OrganizationId] = ST.[OrganizationId] |
|
INNER JOIN |
|
[dbo].[Organization] O ON O.[Id] = ST.[OrganizationId] |
|
LEFT JOIN |
|
[dbo].[CipherView] C ON C.[Id] = ST.[CipherId] |
|
LEFT JOIN |
|
[dbo].[CollectionCipher] CC ON CC.[CipherId] = C.[Id] AND C.[Id] IS NOT NULL |
|
LEFT JOIN |
|
[dbo].[CollectionUser] CU ON CU.[CollectionId] = CC.[CollectionId] AND CU.[OrganizationUserId] = OU.[Id] AND C.[Id] IS NOT NULL |
|
LEFT JOIN |
|
[dbo].[GroupUser] GU ON GU.[OrganizationUserId] = OU.[Id] AND CU.[CollectionId] IS NULL AND C.[Id] IS NOT NULL |
|
LEFT JOIN |
|
[dbo].[CollectionGroup] CG ON CG.[GroupId] = GU.[GroupId] AND CG.[CollectionId] = CC.[CollectionId] |
|
WHERE |
|
OU.[UserId] = @UserId |
|
AND OU.[Status] = 2 -- Ensure user is confirmed |
|
AND O.[Enabled] = 1 |
|
AND ( |
|
ST.[CipherId] IS NULL |
|
OR ( |
|
C.[Id] IS NOT NULL |
|
AND ( |
|
CU.[ReadOnly] = 0 |
|
OR CG.[ReadOnly] = 0 |
|
) |
|
) |
|
) |
|
AND ST.[Status] = COALESCE(@Status, ST.[Status]) |
|
GROUP BY |
|
ST.Id, |
|
ST.OrganizationId, |
|
ST.CipherId, |
|
ST.Type, |
|
ST.Status, |
|
ST.CreationDate, |
|
ST.RevisionDate |
|
ORDER BY ST.[CreationDate] DESC |
|
END |
|
GO
|
|
|