dbo.UpdateLicenseStatus.StoredProcedure.sql
11.7 KB
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[UpdateLicenseStatus] Script Date: 02/06/2018 10:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Dany Ellement
-- Create date: 16-May-2013
-- Description: update the license status on the basis of subscription date
-- =============================================
CREATE PROCEDURE [dbo].[UpdateLicenseStatus]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @sRecipientsMail VARCHAR(255)
DECLARE @sDeploymentEnvironment VARCHAR(255)
DECLARE @sMessageSubject VARCHAR(1000)
SET @sRecipientsMail = 'edsales@adamcorp.com'
SET @sDeploymentEnvironment = 'PRD'
-- fetch the email id of recipient from the environment vairable
--EXEC GetSchedulerAlertEmail @sRecipientsMail OUTPUT, @sDeploymentEnvironment OUTPUT
SET @sMessageSubject = @sDeploymentEnvironment+': Job Scheduler - active/inactive license'
EXEC msdb..sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = @sRecipientsMail,
@body = 'Job Scheduler Started - active/inactive license',
@body_format = 'HTML',
@subject = @sMessageSubject
DECLARE @cLicenseToInactive CURSOR
DECLARE @cLicenseToActive CURSOR
DECLARE @dtCurrentDate DATETIME = GETDATE()
DECLARE @iLicenseId INT
DECLARE @dtSubscriptionValidFrom DATETIME
DECLARE @iDayDifference INT
DECLARE @sErrorStatus CHAR(2) = 'ok'
-- fetch the license which are currently active and whose subscription enddate is smaller than current date
SET @cLicenseToInactive = CURSOR FAST_FORWARD FOR
SELECT Distinct R1.Id FROM(
SELECT License.Id, SubscriptionValidFrom, (SELECT Max(SubscriptionValidThrough) FROM LicenseSubscriptionDetail WHERE LicenseId = License.Id GROUP BY LicenseId) AS MaxDateForLicense FROM License
INNER JOIN LicenseSubscriptionDetail ON ( License.Id = LicenseSubscriptionDetail.LicenseId )
WHERE License.IsActive = 1
)
AS R1 WHERE DATEDIFF(DAY,GETDATE(),MaxDateForLicense)<0
-- *** OLD CODE SUPPORT ONLY ONE SUBSCRIPTION BY LICENSE ***
--SELECT DISTINCT License.Id FROM License
-- INNER JOIN LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId
-- WHERE License.IsActive = 1
-- AND DATEDIFF(DAY,@dtCurrentDate,LicenseSubscriptionDetail.SubscriptionValidThrough)<0
-- --AND CONVERT(CHAR(10),LicenseSubscriptionDetail.SubscriptionValidThrough,101) < CONVERT(CHAR(10),@dtCurrentDate,101)
-- open & fetch the cursor variables into the local variables
OPEN @cLicenseToInactive
FETCH NEXT FROM @cLicenseToInactive INTO @iLicenseId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dtSubscriptionValidFrom = (SELECT Distinct R1.SubscriptionValidFrom FROM(
SELECT Distinct License.Id, SubscriptionValidFrom, (SELECT Max(SubscriptionValidThrough) FROM LicenseSubscriptionDetail WHERE LicenseId = @iLicenseId GROUP BY LicenseId) AS MaxDateForLicense FROM License
INNER JOIN LicenseSubscriptionDetail ON ( @iLicenseId = LicenseSubscriptionDetail.LicenseId )
WHERE License.IsActive = 1 AND License.Id = @iLicenseId)
AS R1 WHERE DATEDIFF(DAY,GETDATE(),MaxDateForLicense)>=0 AND R1.SubscriptionValidFrom = MaxDateForLicense )
-- *** OLD CODE SUPPORT ONLY ONE SUBSCRIPTION BY LICENSE ***
--(SELECT LicenseSubscriptionDetail.SubscriptionValidFrom FROM LicenseSubscriptionDetail
-- WHERE LicenseId = @iLicenseId
-- AND DATEDIFF(DAY,@dtCurrentDate,SubscriptionValidThrough)>=0)
-- --AND CONVERT(CHAR(10),SubscriptionValidThrough,101) >= CONVERT(CHAR(10),@dtCurrentDate,101))
IF @dtSubscriptionValidFrom IS NULL
BEGIN
UPDATE License SET IsActive=0, CancellationDate = @dtCurrentDate WHERE Id = @iLicenseId
END
ELSE
BEGIN
SET @iDayDifference = (SELECT DATEDIFF(DAY,@dtCurrentDate,@dtSubscriptionValidFrom))
IF @iDayDifference > 0
BEGIN
UPDATE License SET IsActive=0, CancellationDate = @dtCurrentDate WHERE Id = @iLicenseId
END
END
FETCH NEXT FROM @cLicenseToInactive INTO @iLicenseId
END
-- close the cursor to free up resources
CLOSE @cLicenseToInactive
DEALLOCATE @cLicenseToInactive
-- fetch the license which are currently inactive and whose subscription fromdate starts today
SET @cLicenseToActive = CURSOR FAST_FORWARD FOR
SELECT DISTINCT License.Id FROM License
INNER JOIN LicenseSubscriptionDetail ON License.Id = LicenseSubscriptionDetail.LicenseId
WHERE License.IsActive = 0
AND DATEDIFF(DAY,@dtCurrentDate,LicenseSubscriptionDetail.SubscriptionValidFrom) = 0
--AND CONVERT(CHAR(10),LicenseSubscriptionDetail.SubscriptionValidFrom,101) = CONVERT(CHAR(10),@dtCurrentDate,101)
-- open & fetch the cursor variables into the local variables
OPEN @cLicenseToActive
FETCH NEXT FROM @cLicenseToActive INTO @iLicenseId
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE License SET IsActive=1 WHERE Id = @iLicenseId
FETCH NEXT FROM @cLicenseToActive INTO @iLicenseId
END
-- close the cursor to free up resources
CLOSE @cLicenseToActive
DEALLOCATE @cLicenseToActive
COMMIT TRANSACTION
EXEC msdb..sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = @sRecipientsMail,
@body = 'Job Scheduler Ended - active/inactive license',
@body_format = 'HTML',
@subject = @sMessageSubject
SELECT @sErrorStatus as SPStatus
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT Error_Message() as SPStatus
END CATCH
END
GO