· 5 years ago · Feb 13, 2020, 10:04 AM
1CREATE PROCEDURE [dbo].[stp_GetProductAccess]
2 @UserID INT,
3 @EmployerID INT
4AS
5BEGIN
6
7 DECLARE @LmsDbName VARCHAR(10) = 'lms'
8
9 DECLARE @IsEmployer_EnableInsight BIT = 'false'
10 DECLARE @IsEmployer_EnablePerform BIT = 'false'
11 DECLARE @IsEmployer_EnableOnboard BIT = 'false'
12 DECLARE @IsEmployer_EnableLearn BIT = 'false'
13 DECLARE @IsEmployer_EnableAdminCenter BIT = 'false'
14 DECLARE @IsEmployer_EnablePositionManagement BIT = 'false'
15 DECLARE @IsEmployer_EnableAnalytics BIT = 'false'
16 DECLARE @IsEmployer_EnableHighline BIT = 'false'
17 DECLARE @IsEmployer_EnableEForms BIT = 'false'
18 DECLARE @IsEmployer_EnablePreboarding BIT = 'false'
19 DECLARE @IsEmployer_EnablePreboardingFromInsight BIT = 'false'
20 DECLARE @IsEmployer_EnableEFormsFreemium BIT = 'false'
21
22 DECLARE @IsUser_EnableInsight BIT = 'false'
23 DECLARE @IsUser_EnableOhc BIT = 'false'
24 DECLARE @IsUser_EnablePerform BIT = 'false'
25 DECLARE @IsUser_EnableOnboard BIT = 'false'
26 DECLARE @IsUser_EnableLearn BIT = 'false'
27 DECLARE @IsUser_EnableAdminCenter BIT = 'false'
28 DECLARE @IsUser_EnableAnalytics BIT = 'false'
29 DECLARE @IsUser_EnableEForms BIT = 'false'
30 DECLARE @IsUser_EnableSalaryStudy BIT = 'false'
31
32 DECLARE @IsUser_EnablePreboarding BIT = 'false'
33 DECLARE @IsUser_InsightAgencyAdmin BIT = 'false'
34 DECLARE @IsUser_EFormsHRAdmin BIT = 'false'
35 DECLARE @IsUser_LmsAdmin BIT = 0
36 DECLARE @IsUser_EFormsAdmin BIT = 0
37
38 DECLARE @IsUser_CommunityEnabled BIT = 0
39
40 DECLARE @IsLiteEnabled BIT = 'false'
41
42 DECLARE @DbExists_Eforms BIT = 'false'
43
44 DECLARE @IsUser_EnableHR BIT = 0
45 DECLARE @IsUser_EnableBenefits BIT = 0
46 DECLARE @IsUser_EnablePayroll BIT = 0
47 DECLARE @IsUser_EnableTimeAttendance BIT = 0
48 DECLARE @IsUser_EnableEmployeeSS BIT = 0
49
50
51 ----------------------------------------------------------------------------------------
52 -- ################### Checking if db exists to avoid dependency errors ######################
53 IF DB_ID('eforms') IS NOT NULL
54 BEGIN
55 SET @DbExists_Eforms = 'true'
56 END
57
58 ----------------------------------------------------------------------------------------
59 -- ################### Checking Employer Level Access To Products ######################
60 SELECT @IsEmployer_EnableInsight = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
61 FROM ApplicationSettings
62 WHERE Code = 'EnableInsight' AND (EmployerID = @EmployerID or EmployerID is null)
63 order by isnull(EmployerID, -1)
64 SET @IsUser_EnableInsight = @IsEmployer_EnableInsight
65
66 SELECT @IsEmployer_EnablePerform = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
67 FROM ApplicationSettings
68 WHERE Code = 'EnablePerformForEmployer' AND (EmployerID = @EmployerID or EmployerID is null)
69 order by isnull(EmployerID, -1)
70 SET @IsUser_EnablePerform = @IsEmployer_EnablePerform
71
72 SELECT @IsEmployer_EnableOnboard = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
73 FROM ApplicationSettings
74 WHERE Code = 'EnableOnBoardingForEmployer' AND (EmployerID = @EmployerID or EmployerID is null)
75 order by isnull(EmployerID, -1)
76 SET @IsUser_EnableOnboard = @IsEmployer_EnableOnboard
77
78 SELECT @IsEmployer_EnableLearn = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
79 FROM ApplicationSettings
80 WHERE Code = 'EnableLearningManagementSystem' AND (EmployerID = @EmployerID or EmployerID is null)
81 order by isnull(EmployerID, -1)
82 SET @IsUser_EnableLearn = @IsEmployer_EnableLearn
83
84 SELECT @IsEmployer_EnableAnalytics = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
85 FROM ApplicationSettings
86 WHERE Code = 'EnableAnalytics' AND (EmployerID = @EmployerID or EmployerID is null)
87 order by isnull(EmployerID, -1)
88
89 SELECT @IsEmployer_EnableAdminCenter = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
90 FROM ApplicationSettings
91 WHERE Code IN ('EnableAdminCenter') AND (EmployerID = @EmployerID or EmployerID is null)
92 order by isnull(EmployerID, -1)
93
94 SELECT @IsEmployer_EnablePositionManagement = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
95 FROM ApplicationSettings
96 WHERE Code IN ('EnablePositionManagementSystem') AND (EmployerID = @EmployerID or EmployerID is null)
97 order by isnull(EmployerID, -1)
98
99 SELECT @IsEmployer_EnableHighline = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
100 FROM ApplicationSettings
101 WHERE Code = 'EnableHighline' AND (EmployerID = @EmployerID or EmployerID is null)
102 order by isnull(EmployerID, -1)
103
104 SELECT @IsEmployer_EnableEForms = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
105 FROM ApplicationSettings
106 WHERE Code = 'EnableEFormsForEmployer' AND (EmployerID = @EmployerID or EmployerID is null)
107 order by isnull(EmployerID, -1)
108 ----------------------------------------------------------------------------------------
109
110 SELECT @IsUser_InsightAgencyAdmin = CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END
111 FROM gj_mainsite1.dbo.AdministratorDetails a
112 INNER JOIN coredata.dbo.Users u ON u.UserID = @UserId AND u.UserID = a.adminID
113 WHERE a.AgencyAdmin = 1
114
115 SELECT @IsEmployer_EnablePreboarding = CASE WHEN Setting = 'Y' THEN 1 ELSE 0 END
116 FROM dbo.ApplicationSettings
117 WHERE Code = 'EnablePreboarding' AND (EmployerID = @EmployerID OR EmployerID IS NULL)
118 order by isnull(EmployerID, -1)
119
120 SELECT @IsEmployer_EnablePreboardingFromInsight = CASE WHEN Setting = 'Y' THEN 1 ELSE 0 END
121 FROM dbo.ApplicationSettings
122 WHERE Code = 'EnablePreboardingFromInsight' AND (EmployerID = @EmployerID OR EmployerID IS NULL)
123 order by isnull(EmployerID, -1)
124
125 SET @IsUser_EnablePreboarding = CASE WHEN @IsEmployer_EnablePreboarding = 1 OR (@IsUser_InsightAgencyAdmin = 1 AND @IsEmployer_EnablePreboardingFromInsight = 1) THEN 1 ELSE 0 END
126
127 SELECT @IsEmployer_EnableEFormsFreemium = CASE Setting WHEN 'Y' THEN 1 ELSE 0 END
128 FROM ApplicationSettings
129 WHERE Code = 'EnableEFormsFreemium' AND (EmployerID = @EmployerID or EmployerID is null)
130 order by isnull(EmployerID, -1)
131
132 IF(@IsEmployer_EnableEformsFreemium = 1)
133 BEGIN
134 SET @IsEmployer_EnableEformsFreemium = CASE WHEN EXISTS(SELECT 1 FROM ApplicationSettings WHERE Code = 'EnableEFormsForEmployer' AND EmployerID = @EmployerID) THEN 0 ELSE 1 END
135 END
136
137 SET @IsEmployer_EnableEForms = CASE WHEN @IsEmployer_EnableEForms = 1 OR @IsEmployer_EnableEFormsFreemium = 1 THEN 1 ELSE 0 END
138
139 IF DB_ID(@LmsDbName) IS NOT NULL
140 BEGIN
141 IF EXISTS (
142 SELECT TOP 1 * FROM lms.security.xUsers_SecurityProfile xusp
143 INNER JOIN coredata.dbo.Users u ON xusp.UserId = u.UserID AND u.UserID = @UserID
144 INNER JOIN lms.security.SecurityProfiles sp ON xusp.ProfileId = sp.ProfileId
145 WHERE u.EmployerID = @EmployerID AND sp.DerivedFromProfileId IN (1, 4)
146 )
147 BEGIN
148 SET @IsUser_LmsAdmin = 1
149 END
150 END
151
152 IF @IsEmployer_EnablePositionManagement = 1
153 SET @IsEmployer_EnableAdminCenter = 1
154
155 IF @IsEmployer_EnableAdminCenter = 'true' AND EXISTS (SELECT 1 FROM [coredata].[Security].[xUsers_SecurityProfile] xus WHERE xus.UserId = @UserID AND xus.EmployerId = @EmployerID)
156 SET @IsUser_EnableAdminCenter = 1
157
158 IF @IsEmployer_EnableAnalytics = 'true'
159 AND EXISTS(SELECT ur.UserID
160 FROM [gj_mainsite1].[access].[UserRoles] ur
161 INNER JOIN [gj_mainsite1].[access].roleProductCategoryGroupActions rpcga ON rpcga.roleId = ur.roleId
162 INNER JOIN [gj_mainsite1].[access].productCategoryGroupActions pcga ON pcga.productCategoryGroupActionId = rpcga.productCategoryGroupActionId
163 INNER JOIN [gj_mainsite1].[access].actions a ON pcga.actionID = a.actionID AND a.code='enableAnalytics'
164 WHERE ur.UserId = @UserID)
165 BEGIN
166 SET @IsUser_EnableAnalytics = 'true'
167 END
168
169 IF @DbExists_Eforms = 'true'
170 BEGIN
171 IF @IsEmployer_EnableEForms = 'true'
172 BEGIN
173 SET @IsUser_EnableEforms = 'true'
174
175 IF @IsUser_EnableOnboard = 'false' AND EXISTS(
176 SELECT 1
177 FROM [eforms].[security].[xUsers_SecurityProfile] USP
178 INNER JOIN [eforms].[security].[SecurityProfiles] SP
179 ON USP.ProfileId = SP.ProfileId
180 AND USP.UserId = @UserID
181 AND SP.DerivedFromProfileId = 1)
182 BEGIN
183 SET @IsUser_EFormsHRAdmin = 'true'
184 END
185 END
186
187 IF EXISTS (
188 SELECT TOP 1 * FROM eforms.security.xUsers_SecurityProfile xusp
189 INNER JOIN eforms.security.SecurityProfiles sp ON xusp.ProfileId = sp.ProfileId AND xusp.UserID = @UserID
190 WHERE xusp.EmployerID = @EmployerID AND sp.DerivedFromProfileId IN (1, 4)
191 )
192 BEGIN
193 SET @IsUser_EFormsAdmin = 1
194 END
195 END
196
197 SELECT TOP 1
198 @IsUser_EnableOhc = CAST(CASE WHEN @IsEmployer_EnableInsight = 'true' AND OHCEnabled = 'true' THEN 1 ELSE 0 END AS BIT),
199 @IsUser_EnableInsight = CAST(CASE WHEN @IsEmployer_EnableInsight = 'true' AND InsightEnable = 'true' THEN 1 ELSE 0 END AS BIT),
200 @IsUser_EnablePerform = CAST(CASE WHEN @IsEmployer_EnablePerform = 'true' AND PlatformEnabled = 'true' THEN 1 ELSE 0 END AS BIT),
201 @IsUser_EnableOnboard = CAST(CASE WHEN ((@IsEmployer_EnableOnboard = 'true' OR @IsUser_EnablePreboarding = 'true')
202 AND OnboardEnabled = 'true')
203 OR @IsUser_EFormsHRAdmin = 'true' THEN 1 ELSE 0 END AS BIT),
204 @IsUser_EnableLearn = CAST(CASE WHEN @IsEmployer_EnableLearn = 'true' AND LmsEnabled = 'true' THEN 1 ELSE 0 END AS BIT)
205 FROM UserProductStatus
206 WHERE UserID = @UserID
207
208 SELECT @IsLiteEnabled = CASE WHEN licenseType = 'lite' THEN 1 ELSE 0 END
209 FROM [gj_mainsite1].[dbo].[EmployerDetails] ed JOIN [gj_mainsite1].[dbo].[ReqUsers] ru ON ru.EmployerID = ed.EmployerId
210 WHERE UserID = @UserID
211
212 IF @IsLiteEnabled = 1 AND @IsUser_EnableOhc = 1
213 BEGIN
214 SET @IsUser_EnableInsight = 1
215 END
216
217 IF (@IsUser_EnableInsight = 1 AND @IsUser_InsightAgencyAdmin = 1) OR
218 (@IsUser_EnableLearn = 1 AND @IsUser_LmsAdmin = 1) OR
219 (@IsUser_EnableEForms = 1 AND @IsUser_EFormsAdmin = 1)
220 BEGIN
221 SET @IsUser_CommunityEnabled = 1
222 END
223
224 IF @IsUser_EnableInsight = 1
225 BEGIN
226 SELECT @IsUser_EnableSalaryStudy = CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END
227 FROM gj_mainsite1.access.userRoles ur
228 INNER JOIN dbo.userTypes ut ON ur.userTypeID = ut.userTypeID
229 INNER JOIN gj_mainsite1.access.roles r ON ur.roleID = r.roleID
230 WHERE ut.code = 'insight'
231 AND ur.userID = @UserID
232 and r.EmployerID = @EmployerID
233 AND r.isActive = 1
234 END
235
236
237 CREATE TABLE #HRISAccess (Module varchar(50) ,HasAccess Bit)
238 IF @IsEmployer_EnableHighline = 1
239 BEGIN
240 INSERT INTO #HRISAccess
241 EXEC [dbo].[stp_GetHRISProductAccess] @UserId
242 END
243
244
245 SELECT @IsUser_EnableHR = ISNULL(HasAccess,0) FROM #HRISAccess h WHERE h.Module = 'HR'
246 SELECT @IsUser_EnableBenefits = ISNULL(HasAccess,0) FROM #HRISAccess h WHERE h.Module = 'BE'
247 SELECT @IsUser_EnableEmployeeSS = ISNULL(HasAccess,0) FROM #HRISAccess h WHERE h.Module = 'ESS'
248 SELECT @IsUser_EnablePayroll = ISNULL(HasAccess,0) FROM #HRISAccess h WHERE h.Module = 'PR'
249 SELECT @IsUser_EnableTimeAttendance = ISNULL(HasAccess,0) FROM #HRISAccess h WHERE h.Module = 'TA'
250
251 DECLARE @ProductAccess table ([Product] varchar(10), [UserStatus] bit, [EmployerStatus] bit)
252
253 INSERT INTO @ProductAccess ([Product], [UserStatus], [EmployerStatus])
254 SELECT 'AC', @IsUser_EnableAdminCenter, @IsEmployer_EnableAdminCenter UNION ALL
255 SELECT 'AN', @IsUser_EnableAnalytics, @IsEmployer_EnableAnalytics UNION ALL
256 SELECT 'BE' ,@IsUser_EnableBenefits,@IsEmployer_EnableHighline UNION ALL
257 SELECT 'ESS',@IsUser_EnableEmployeeSS,@IsEmployer_EnableHighline UNION ALL
258 SELECT 'HR' ,@IsUser_EnableHR,@IsEmployer_EnableHighline UNION ALL
259 SELECT 'IN', @IsUser_EnableInsight, @IsEmployer_EnableInsight UNION ALL
260 SELECT 'OHC', @IsUser_EnableOhc, @IsEmployer_EnableInsight UNION ALL
261 SELECT 'ON', @IsUser_EnableOnboard, CAST(CASE WHEN @IsEmployer_EnableOnboard = 1 OR @IsEmployer_EnablePreboarding = 1 THEN 1 ELSE 0 END AS BIT) UNION ALL
262 SELECT 'PE', @IsUser_EnablePerform, @IsEmployer_EnablePerform UNION ALL
263 SELECT 'PR' ,@IsUser_EnablePayroll,@IsEmployer_EnableHighline UNION ALL
264 SELECT 'TA' , @IsUser_EnableTimeAttendance,@IsEmployer_EnableHighline UNION ALL
265 SELECT 'LMS', @IsUser_EnableLearn, @IsEmployer_EnableLearn UNION ALL
266 SELECT 'CORE', @IsUser_EnableOnboard, @IsEmployer_EnableOnboard UNION ALL
267 SELECT 'EF', @IsUser_EnableEForms, @IsEmployer_EnableEForms UNION ALL
268 SELECT 'SS', @IsUser_EnableSalaryStudy, @IsUser_EnableSalaryStudy UNION ALL
269 SELECT 'CM', @IsUser_CommunityEnabled, 1
270
271 IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID AND EmployerID = @EmployerID AND isActive = 'true')
272 BEGIN
273 UPDATE @ProductAccess SET [UserStatus] = 'false'
274 END
275
276 IF EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID AND EmployerID = @EmployerID AND isSuperAdmin = 'true')
277 BEGIN
278 UPDATE @ProductAccess SET [UserStatus] = 'true'
279 END
280
281 SELECT *, [UserStatus] AS [Status] FROM @ProductAccess
282END