-
Notifications
You must be signed in to change notification settings - Fork 47
/
app.sp_GetDatasetQueries.StoredProcedure.sql
96 lines (89 loc) · 2.58 KB
/
app.sp_GetDatasetQueries.StoredProcedure.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
-- Copyright (c) 2022, UW Medicine Research IT, University of Washington
-- Developed by Nic Dobbins and Cliff Spital, CRIO Sean Mooney
-- This Source Code Form is subject to the terms of the Mozilla Public
-- License, v. 2.0. If a copy of the MPL was not distributed with this
-- file, You can obtain one at http://mozilla.org/MPL/2.0/.
USE [LeafDB]
GO
/****** Object: StoredProcedure [app].[sp_GetDatasetQueries] Script Date: ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================
-- Author: Cliff Spital
-- Create date: 2018/12/21
-- Description: Retrieves all DatasetQuery records to which the user is authorized.
-- =======================================
CREATE PROCEDURE [app].[sp_GetDatasetQueries]
@user auth.[User],
@groups auth.GroupMembership READONLY,
@admin bit = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @ids TABLE (
Id UNIQUEIDENTIFIER NOT NULL
);
IF (@admin = 1)
BEGIN;
-- user is an admin, load them all
INSERT INTO @ids
SELECT dq.Id
FROM app.DatasetQuery dq
END;
ELSE
BEGIN;
-- user is not an admin, assess their privilege
INSERT INTO @ids (Id)
SELECT
dq.Id
FROM app.DatasetQuery dq
WHERE EXISTS (
SELECT 1
FROM auth.DatasetQueryConstraint
WHERE DatasetQueryId = dq.Id AND
ConstraintId = 1 AND
ConstraintValue = @user
)
OR EXISTS (
SELECT 1
FROM auth.DatasetQueryConstraint
WHERE DatasetQueryId = dq.Id AND
ConstraintId = 2 AND
ConstraintValue in (SELECT [Group] FROM @groups)
)
OR NOT EXISTS (
SELECT 1
FROM auth.DatasetQueryConstraint
WHERE DatasetQueryId = dq.Id
);
END;
-- produce the hydrated records
SELECT
i.Id,
dq.UniversalId,
dq.IsDefault,
dq.Shape,
dq.[Name],
dqc.Category,
dq.[Description],
dq.SqlStatement,
IsEncounterBased = ISNULL(ddq.IsEncounterBased, 1),
dq.IsText,
ddq.[Schema],
ddq.SqlFieldDate,
ddq.SqlFieldValueString,
ddq.SqlFieldValueNumeric
FROM @ids i
JOIN app.DatasetQuery dq ON i.Id = dq.Id
LEFT JOIN app.DynamicDatasetQuery ddq ON dq.Id = ddq.Id
LEFT JOIN app.DatasetQueryCategory dqc ON dq.CategoryId = dqc.Id
-- produce the tags for each record
SELECT
i.Id,
Tag
FROM @ids i
JOIN app.DatasetQueryTag t on i.Id = t.DatasetQueryId
END
GO