· 7 years ago · Sep 10, 2018, 08:02 AM
1SET NOCOUNT ON;
2 SET ANSI_NULLS ON
3 SET QUOTED_IDENTIFIER ON
4 SET ANSI_WARNINGS ON
5 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
6
7DECLARE @Person VARCHAR(50) SET @PERSON ='wongbr'
8DECLARE @ApplicationID INT SET @ApplicationID= 4
9DECLARE @PeopleID INT
10DECLARE @GlCode VARCHAR(6)
11DECLARE @Count INT -- total Count
12DECLARE @Color VARCHAR(20)
13DECLARE @TOOLTIP VARCHAR(1500)
14DECLARE @Cache TABLE(RID INT IDENTITY(1,1),[RowCount] INT, Name VARCHAR(100),COLOR VARCHAR(10) DEFAULT 'GREEN',Priority VARCHAR(100))
15DECLARE @Roles TABLE (RoleName VARCHAR(100))
16DECLARE @Role AS VARCHAR(100)
17
18IF OBJECT_id('TEMPDB..#LOCATIONS') IS NOT NULL
19DROP TABLE #LOCATIONS
20
21CREATE TABLE #Locations
22(
23 Region VARCHAR(10)
24 ,Clinic_ID INT
25 ,Location_ID VARCHAR(4)
26)
27DECLARE @Notifications TABLE
28(
29 NotificationCategory VARCHAR(50),
30 CredentialDocumentStatusTypeCode CHAR(1),
31 DaysToExpire INT
32)
33---------------------------------------------------------------------------
34-- Variables Initiate
35---------------------------------------------------------------------------
36-- Is @Person a PeopleID or UserName?
37IF ISNUMERIC(@Person)=0 -- @Person not numeric, grab the PeopleID using function
38SELECT @PeopleID=dbo.fn_getPeopleIDForUserNameScalar(@Person)
39ELSE
40SET @PeopleID=@Person
41INSERT INTO @Roles
42SELECT RoleName FROM dbo.fn_getRoleInfoForUserName ( @PeopleID,@ApplicationID)
43-- pending items awaiting approval.
44IF EXISTS ( SELECT 1 FROM @Roles WHERE RoleName IN ('Credential-GDUWorker','Credential-CredentialingWorker'))
45BEGIN
46--SELECT @Role=RoleName FROM @Roles WHERE RoleName IN ('Credential-GDUWorker','Credential-CredentialingWorker')
47INSERT INTO @Cache (NAME,[ROWCOUNT])
48SELECT 'Pending Approval',COUNT(1)
49FROM Credentialing..vw_GetCredentialDetails WITH (NOLOCK)
50WHERE CredentialDocumentStatusTypeCode = 'P'
51AND EXISTS (SELECT 1 FROM @Roles where AppRole = SUBSTRING(RoleName ,12,LEN(RoleName)-11) AND RoleName IN ('Credential-GDUWorker','Credential-CredentialingWorker'))
52IF (@@ROWCOUNT = 0)
53INSERT INTO @Cache (NAME,[ROWCOUNT])
54SELECT 'Pending Approval',0
55---------------------------------------------------------------------------
56-- Update color
57---------------------------------------------------------------------------
58UPDATE @Cache SET COLOR= CASE WHEN [RowCount] BETWEEN 1 AND 5 THEN 'YELLOW'
59 WHEN [RowCount] >= 6 THEN 'RED'
60 ELSE 'GREEN' END
61 END
62-- Rejected and About to Expire Credential Documents
63ELSE IF EXISTS (SELECT 1 FROM @Roles WHERE RoleName LIKE 'Credential-Home%')
64BEGIN
65 INSERT INTO #Locations
66 SELECT DISTINCT Region,Clinic_ID ,Location_ID
67 FROM [Appframework].[dbo].[fn_GetLocationForPerson](@ApplicationID,@PeopleID)
68
69SET @Role ='Credential-HomeOfficeManager'
70
71IF OBJECT_ID('tempdb..#a') IS NOT NULL
72DROP TABLE #a
73SELECT Credentialid ,CredentialDocumentID INTO #a
74 FROM Credentialing..vw_GetInValidCredentialMultipleDocuments A WITH (NOLOCK)
75
76IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
77
78
79
80SELECT TypeName
81 ,CredentialDocumentStatusTypeCode
82 ,DaysToExpire ,v.Region ,v.Clinic_Id ,v.Location_ID
83 INTO #Results
84FROM Credentialing..vw_GetCredentialDetails V WITH (NOLOCK)
85WHERE CredentialDocumentStatusTypeCode IN ('A','R')
86AND DaysToExpire <= CASE WHEN CredentialDocumentStatusTypeCode ='R' THEN DaysToExpire
87 WHEN CredentialDocumentStatusTypeCode ='A' THEN 90
88 END
89AND NOT EXISTS ( SELECT 1
90 FROM #a A WITH (NOLOCK)
91 WHERE V.credentialid = A.Credentialid
92 AND V.CredentialDocumentID = A.CredentialDocumentID ) -- Task #270
93
94
95INSERT INTO @Notifications
96SELECT TypeName
97 ,CredentialDocumentStatusTypeCode
98 ,DaysToExpire
99FROM #Results V WITH (NOLOCK)
100 WHERE exists (select 1 from #Locations DV WHERE V.Region = DV.Region
101AND V.Clinic_ID=DV.Clinic_ID
102AND V.Location_ID=DV.Location_ID )
103UNION ALL -- Task #362
104 SELECT TypeName
105 ,CredentialDocumentStatusTypeCode
106 ,DaysToExpire
107 FROM Credentialing..vw_GetCredentialDetails V WITH (NOLOCK)
108 -- find glcodes tied to the users group
109 INNER JOIN amazon..tblGLCodes gl WITH (NOLOCK)
110 ON gl.glcode=v.glcode
111 AND gl.[Description] LIKE '%Traveling Assistant%'
112 INNER JOIN Appframework..tblGroup g WITH (NOLOCK)
113 ON LEFT(g.GroupName,LEN('Credentialing-HomeTraveling-'))='Credentialing-HomeTraveling-'
114 AND RIGHT(g.GroupName,3)=gl.Region
115 INNER JOIN Appframework..tblGroupPeople gp WITH (NOLOCK)
116 ON gp.GroupID=g.GroupID
117 AND gp.PeopleID=@PeopleID
118 WHERE CredentialDocumentStatusTypeCode IN ('A','R')
119 AND DaysToExpire <= CASE WHEN CredentialDocumentStatusTypeCode ='R' THEN DaysToExpire
120 WHEN CredentialDocumentStatusTypeCode ='A' THEN 90
121 END
122 AND NOT EXISTS ( SELECT 1
123 FROM #a A WITH (NOLOCK)
124 WHERE V.credentialid = A.Credentialid
125 AND V.CredentialDocumentID = A.CredentialDocumentID ) -- Task #270
126
127-- Task #338
128-- No Document Credentials
129INSERT INTO @Notifications
130SELECT 'No Document'
131 ,'D'
132 ,0
133FROM Credentialing..vw_GetNoDocumentsCredentials V WITH (NOLOCK)
134INNER JOIN #Locations DV
135ON V.Region = DV.Region
136AND V.Clinic_ID=DV.Clinic_ID
137AND V.Location_ID=DV.Location_ID
138UNION ALL
139 SELECT 'No Document'
140 ,'D'
141 ,0
142 FROM Credentialing..vw_GetNoDocumentsCredentials V WITH (NOLOCK)
143 -- glcode-based: filter by glcodes tied to traveling assistants
144 INNER JOIN amazon..tblGLCodes gl WITH (NOLOCK)
145 ON gl.glcode=v.glcode
146 AND gl.[Description] LIKE '%Traveling Assistant%'
147 INNER JOIN Appframework..tblGroup g WITH (NOLOCK)
148 ON LEFT(g.GroupName,LEN('Credentialing-HomeTraveling-'))='Credentialing-HomeTraveling-'
149 AND RIGHT(g.GroupName,3)=gl.Region
150 INNER JOIN Appframework..tblGroupPeople gp WITH (NOLOCK)
151 ON gp.GroupID=g.GroupID
152 AND gp.PeopleID=@PeopleID
153
154INSERT @Cache ( Name, [RowCount])
155SELECT CASE WHEN c.NameCode=90 THEN 'Expire within 90 days'
156 WHEN c.NameCode=60 THEN 'Expire within 60 days'
157 WHEN c.NameCode=30 THEN 'Expire within 30 days'
158 WHEN c.NameCode=0 THEN 'Expired'
159 WHEN c.NameCode=-1 THEN 'No Document'
160 WHEN c.NameCode=-99 THEN 'Rejected Credentials'
161 END
162 ,ISNULL(b.[RowCount],0)
163FROM (
164 SELECT -99 AS 'NameCode'
165 UNION ALL
166 SELECT 90 AS 'NameCode'
167 UNION ALL
168 SELECT 60 AS 'NameCode'
169 UNION ALL
170 SELECT 30 AS 'NameCode'
171 UNION ALL
172 SELECT 0 AS 'NameCode'
173 UNION ALL
174 SELECT -1 AS 'NameCode'
175 ) c
176LEFT JOIN (
177 SELECT NameCode
178 ,COUNT(1) AS 'RowCount'
179 FROM (
180 SELECT CASE WHEN CredentialDocumentStatusTypeCode = 'A' AND DaysToExpire BETWEEN 61 AND 90 THEN 90
181 WHEN CredentialDocumentStatusTypeCode = 'A' AND DaysToExpire BETWEEN 31 AND 60 THEN 60
182 WHEN CredentialDocumentStatusTypeCode = 'A' AND DaysToExpire BETWEEN 1 AND 30 THEN 30
183 WHEN CredentialDocumentStatusTypeCode = 'A' AND DaysToExpire<=0 THEN 0
184 WHEN CredentialDocumentStatusTypeCode='D' THEN -1 -- no document
185 WHEN CredentialDocumentStatusTypeCode='R' THEN -99 -- rejected
186 END AS 'NameCode'
187 FROM @Notifications
188 ) a
189 GROUP BY a.NameCode
190 ) b
191ON b.NameCode=c.NameCode
192
193 ---------------------------------------------------------------------------
194 -- Update color
195 ---------------------------------------------------------------------------
196 UPDATE @Cache
197 SET COLOR=CASE WHEN b.MaxColorNumber=1 THEN 'GREEN'
198 WHEN b.MaxColorNumber=2 THEN 'YELLOW'
199 WHEN b.MaxColorNumber=3 THEN 'RED'
200 ELSE 'GREEN'
201 END
202 FROM
203 (
204 SELECT MAX(a.ColorNumber) AS MaxColorNumber
205 FROM (
206 SELECT CASE WHEN [RowCount]>0 AND Name IN ('Expire within 90 days') THEN 1
207 WHEN [RowCount]>0 AND Name IN ('Expire within 60 days','No Document' ) THEN 2
208 WHEN [RowCount]>0 AND Name IN ('Expired','Expire within 30 days','Rejected Credentials') THEN 3
209 END AS 'ColorNumber'
210 FROM @Cache
211 ) a
212 ) b
213
214END
215-- Credential documents expired or about to expire
216ELSE IF EXISTS ( SELECT 1 FROM @Roles WHERE RoleName = 'Credential-CertificateHolder' )
217BEGIN
218
219SELECT @Role='Credential-CertificateHolder'
220
221INSERT INTO @Notifications
222SELECT TypeName
223 ,CredentialDocumentStatusTypeCode
224 ,DaysToExpire
225FROM Credentialing..vw_GetCredentialDetails V WITH (NOLOCK)
226WHERE CredentialDocumentStatusTypeCode IN ('A','P')
227AND PeopleID=@PeopleID
228AND NOT EXISTS ( SELECT 1
229 FROM Credentialing..vw_GetInValidCredentialMultipleDocuments A WITH (NOLOCK)
230 WHERE V.credentialid = A.Credentialid
231 AND V.CredentialDocumentID = A.CredentialDocumentID ) -- Task #270
232
233INSERT @Cache ( Name, [RowCount])
234SELECT CASE WHEN c.NameCode=90 THEN 'Expire within 90 days'
235 WHEN c.NameCode=60 THEN 'Expire within 60 days'
236 WHEN c.NameCode=30 THEN 'Expire within 30 days'
237 WHEN c.NameCode=0 THEN 'Expired'
238 WHEN c.NameCode=-1 THEN 'No Document'
239 WHEN c.NameCode=-99 THEN 'Rejected Credentials'
240 END
241 ,ISNULL(b.[RowCount],0)
242FROM (
243 SELECT 90 AS 'NameCode'
244 UNION ALL
245 SELECT 60 AS 'NameCode'
246 UNION ALL
247 SELECT 30 AS 'NameCode'
248 UNION ALL
249 SELECT 0 AS 'NameCode'
250 UNION ALL
251 SELECT -1 AS 'NameCode'
252 ) c
253LEFT JOIN (
254 SELECT NameCode
255 ,COUNT(1) AS 'RowCount'
256 FROM (
257 SELECT CASE WHEN CredentialDocumentStatusTypeCode = 'A' AND DaysToExpire BETWEEN 61 AND 90 THEN 90
258 WHEN CredentialDocumentStatusTypeCode = 'A' AND DaysToExpire BETWEEN 31 AND 60 THEN 60
259 WHEN CredentialDocumentStatusTypeCode = 'A' AND DaysToExpire BETWEEN 1 AND 30 THEN 30
260 WHEN CredentialDocumentStatusTypeCode = 'A' AND DaysToExpire<=0 THEN 0
261 WHEN CredentialDocumentStatusTypeCode='D' THEN -1 -- no document
262 END AS 'NameCode'
263 FROM @Notifications
264 ) a
265 GROUP BY a.NameCode
266 ) b
267ON b.NameCode=c.NameCode
268
269
270 ---------------------------------------------------------------------------
271 -- Update color
272 ---------------------------------------------------------------------------
273 UPDATE @Cache
274 SET COLOR=CASE WHEN b.MaxColorNumber=1 THEN 'GREEN'
275 WHEN b.MaxColorNumber=2 THEN 'YELLOW'
276 WHEN b.MaxColorNumber=3 THEN 'RED'
277 ELSE 'GREEN'
278 END
279 FROM
280 (
281 SELECT MAX(a.ColorNumber) AS MaxColorNumber
282 FROM (
283 SELECT CASE WHEN [RowCount]>0 AND Name IN ('Expire within 90 days') THEN 1
284 WHEN [RowCount]>0 AND Name IN ('Expire within 60 days') THEN 2
285 WHEN [RowCount]>0 AND Name IN ('Expired','Expire within 30 days') THEN 3
286 ELSE 0
287 END AS 'ColorNumber'
288 FROM @Cache
289 ) a
290 ) b
291
292END
293
294 --Retrieve result
295 SELECT RID
296 ,ISNULL(@TOOLTIP,'')+ISNULL([Name],'') +' ('+CAST([RowCount] AS VARCHAR)+') </br>' AS DataNotification
297 ,(SELECT SUM([RowCount]) FROM @Cache ) AS [Count] -- It Returns total notification Count
298 ,[COLOR] AS COLOR , @Role as ROLE ,CAST(1 AS BIT) AS BLINK
299 FROM @Cache
300 ORDER BY RID
301
302 --SET ANSI_WARNINGS ON;
303 SET NOCOUNT OFF;
304 SET TRANSACTION ISOLATION LEVEL READ COMMITTED