· 7 years ago · Nov 14, 2018, 09:34 PM
1ID Name Details Email
21 abc d1 rob.b@gmail.com
32 def d2 pat@gmail.com
43 ghi d3 golf@gmail.com
54 dft d4 rob.b@gmail.com
6
7ALTER PROCEDURE [dbo].[RECORD_REVIEW_GENERATION]
8
9AS
10DECLARE @SUBSCRIPTION_ID AS VARCHAR(100) = (SELECT SETTING_VALUE FROM WORKING_STORAGE.dbo.SETTINGS WHERE SETTING_NAME = 'REVIEW_SUBSCRIPTION')
11DECLARE @SUBJECT VARCHAR(200)
12DECLARE @COMMENT VARCHAR(500)
13DECLARE @REVIEW_ID VARCHAR(50)
14DECLARE @EXTENSION_SETTINGS XML
15DECLARE @PARAMETERS XML
16DECLARE @RECORD_ID AS INT
17DECLARE @MAX_RECORD_ID AS INT
18DECLARE @END_DATE AS DATE = GETDATE() - DATEPART(WEEKDAY, GETDATE()) --PREVIOUS SUNDAY
19DECLARE @START_DATE AS DATE = DATEADD(DAY, -6, @END_DATE) --PREVIOUS MONDAY
20DECLARE @DATE VARCHAR(500) = CONVERT(VARCHAR(8), @END_DATE, 112)
21
22
23
24SELECT @EXTENSION_SETTINGS = ExtensionSettings, @PARAMETERS = [Parameters]
25FROM ReportServer.dbo.Subscriptions
26WHERE SubscriptionID = @SUBSCRIPTION_ID;
27
28
29
30IF OBJECT_ID('tempdb..#REVIEWS') IS NOT NULL DROP TABLE #REVIEWS
31
32CREATE TABLE #REVIEWS(ReviewID VARCHAR(50), ProviderName VARCHAR(250), SiteIDNo VARCHAR(20),
33 ReviewDate VARCHAR(12), UserName VARCHAR(250), VisitPurpose VARCHAR(250),
34 NoOfPhysicians INT, NoOfRecords INT, RECORD_ID INT IDENTITY(1,1)
35 )
36INSERT INTO #REVIEWS(ReviewID, ProviderName, SiteIDNo, ReviewDate, UserName, VisitPurpose, NoOfPhysicians, NoOfRecords)
37SELECT ReviewID, ProviderName, SiteIDNo, ReviewDate, UserFirstName + ' ' + UserLastName AS UserName, VisitPurpose, NoOfPhysicians, NoOfRecords
38FROM KHSSQLODSPRD.QI_SITE_REVIEW.dbo.RecordHeader
39WHERE ExportDate BETWEEN @START_DATE AND @END_DATE
40ORDER BY ReviewDate
41
42
43SET @RECORD_ID = 1
44SET @MAX_RECORD_ID = (SELECT ISNULL(MAX(RECORD_ID), 0) FROM #REVIEWS)
45
46
47 WHILE @RECORD_ID <= @MAX_RECORD_ID
48 BEGIN
49
50 SELECT @REVIEW_ID = ReviewID,
51 @SUBJECT = 'Review - ' + ProviderName,
52 @COMMENT = CAST(@RECORD_ID AS VARCHAR(10)) + ' of ' + CAST(@MAX_RECORD_ID AS VARCHAR(10))
53 + ' Review Surveys for the week ending ' + RTRIM(CONVERT(VARCHAR(12), @END_DATE, 110)) + '.'
54 + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ProviderName + ' [' + SiteIDNo + '] - Reviewed ' + RTRIM(CONVERT(VARCHAR(12), ReviewDate, 110))
55 + '. Review ID ' + ReviewID
56 + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + VisitPurpose + ' visit for ' + CAST(CAST(NoOfPhysicians AS INT) AS VARCHAR(20)) + ' physicians with '
57 + CAST(CAST(NoOfRecords AS INT) AS VARCHAR(20)) + ' records by ' + UserName + '.'
58 FROM #REVIEWS
59 WHERE RECORD_ID = @RECORD_ID
60
61
62 SET @REVIEW_ID = LOWER(@REVIEW_ID)
63
64 --SET EXTENSION OPTIONS
65 SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="Subject"]/Value/text())[1] with sql:variable("@SUBJECT")');
66 SET @EXTENSION_SETTINGS.modify('replace value of (/ParameterValues/ParameterValue[Name="Comment"]/Value/text())[1] with sql:variable("@COMMENT")');
67
68 --SET REPORT PARAMETERS
69 SET @PARAMETERS.modify('replace value of (/ParameterValues/ParameterValue[Name="SITE_ID"]/Value/text())[1] with sql:variable("@REVIEW_ID")');
70
71
72 --UPDATE SUBSCRIPTION PARAMETERS
73 UPDATE dbo.Subscriptions
74 SET ExtensionSettings = CAST(@EXTENSION_SETTINGS AS VARCHAR(8000)),
75 [Parameters] = CAST(@PARAMETERS AS VARCHAR(8000))
76 WHERE SubscriptionID = @SUBSCRIPTION_ID
77
78
79 EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SUBSCRIPTION_ID;
80
81
82 --WAIT WHILE REPORT GENERATES
83 WAITFOR DELAY '00:00:03.000';
84
85 --IF REPORT IS STILL EXECUTING, WAIT SOME MORE
86 WHILE EXISTS (SELECT TOP 1 'X' FROM dbo.Event WHERE EventData = @SUBSCRIPTION_ID ) OR EXISTS (SELECT TOP 1 'X' FROM dbo.Notifications WHERE SubscriptionID = @SUBSCRIPTION_ID)
87 BEGIN
88 WAITFOR DELAY '00:00:01.000';
89 PRINT 'Waiting for subscription to finish'
90 END
91
92
93 SET @RECORD_ID = @RECORD_ID + 1
94
95 END --END OF LOOP
96
97-- SET @FLAG = 1