dbo.UpdateLicenseAccount.StoredProcedure.sql
13.6 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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[UpdateLicenseAccount] Script Date: 02/06/2018 10:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Magic Software
-- Create date: 27-May-2009
-- Description: To update a license account
-- =============================================
CREATE PROCEDURE [dbo].[UpdateLicenseAccount]
-- Add the parameters for the stored procedure here
@iLicenseId int, @sLicenseeFname varchar(50), @sLicenseeLname varchar(50),
@iLicenseTypeId tinyint, @iAccountTypeId tinyint, @sInstitutionName varchar(100)='', @sAddress1 varchar(100)='',
@sAddress2 varchar(100)='', @sCity varchar(50)='', @sZip varchar(20)='', @iStateId int, @iCountryId int,
@sPhone varchar(30) = '', @sEmailId varchar(50), @iIsActive tinyint, @iTotalLogins int = 0, @iIsRennew tinyint,
@sStartDate varchar(20), @sEndDate varchar(20), @sRenewDate varchar(20), @sMasterIP varchar(100) = '',
@sEditionList varchar(256), @iPrice numeric(14,2), @sProductKey varchar(50), @sSiteIPTo varchar(100) = '', @sSiteMasterIPTo varchar(100) = '',
@iNoofImages int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @cEditionLogins CURSOR
DECLARE @iSiteId INT
DECLARE @iLicenseEditionId INT
DECLARE @iModesty TINYINT
DECLARE @dtStartDate DATETIME
DECLARE @dtEndDate DATETIME
DECLARE @dtRenewDate DATETIME
DECLARE @sErrorStatus CHAR(2)
DECLARE @dtCurrentDate DATETIME
DECLARE @sitem VARCHAR(100)
DECLARE @sRecordDelimiter CHAR(1)
DECLARE @sEditionLoginDelimiter CHAR(1)
DECLARE @sPaymentMode VARCHAR(10)
DECLARE @iLicenseSubscriptionId INT
DECLARE @iSubscriptionId SMALLINT
DECLARE @dtCancellationDate DATETIME
DECLARE @iEditionExists TINYINT
DECLARE @sCountryCode VARCHAR(10)
-- set the parameters to default values
SET @iModesty = 0
SET @sRecordDelimiter = '|'
SET @sEditionLoginDelimiter = '-'
SET @iSubscriptionId = NULL
SET @dtCancellationDate = NULL
SET @dtCurrentDate = getdate()
SET @sPaymentMode = 'CASH'
SET @sErrorStatus = 'ok'
IF @iStateId = 0
BEGIN
SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
END
-- set the state to Other if the country is Non-US
SET @sCountryCode = (SELECT CountryCode from Country WHERE Id = @iCountryId)
IF @sCountryCode != 'US'
BEGIN
SET @iStateId = (SELECT Id FROM State WHERE StateName='Other')
END
-- convert the datatype of startdate & enddate parameter to datetime
SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
SELECT @dtRenewDate = CONVERT(DATETIME,@sRenewDate)
SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
-- if user inactive the license then set the cancellation date to current date
IF @iIsActive = 0
BEGIN
SET @dtCancellationDate = @dtCurrentDate
END
UPDATE License SET LicenseeFirstName = @sLicenseeFname, LicenseeLastName = @sLicenseeLname,
AccountTypeId = @iAccountTypeId, InstitutionName = @sInstitutionName, EmailId = @sEmailId,
Address1 = @sAddress1, Address2 = @sAddress2, City = @sCity, Zip = @sZip, StateId = @iStateId,
CountryId = @iCountryId, Phone = @sPhone, TotalLogins = @iTotalLogins, IsActive = @iIsActive,
ModifiedDate = @dtCurrentDate, CancellationDate = @dtCancellationDate, ProductId = @sProductKey WHERE Id = @iLicenseId
SET @iLicenseSubscriptionId = (SELECT MAX(Id) FROM LicenseSubscriptionDetail WHERE LicenseId = @iLicenseId)
-- if the subscription of license is renew
IF @iIsRennew = 1
BEGIN
-- check if license is single license
IF @iLicenseTypeId = 2
BEGIN
SET @iSubscriptionId = (SELECT SubscriptionPlanId FROM LicenseSubscriptionDetail WHERE Id = @iLicenseSubscriptionId)
END
INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionPlanId, SubscriptionValidFrom,
SubscriptionValidThrough, RenewalDate, PaymentMode, TotalAmount, AmountPaid,NoofImages)
VALUES(@iLicenseId, @iSubscriptionId, @dtStartDate, @dtEndDate, @dtRenewDate, @sPaymentMode, @iPrice, @iPrice,@iNoofImages)
UPDATE License SET NoOfRenewals = NoOfRenewals + 1 WHERE Id = @iLicenseId
END
ELSE
BEGIN
UPDATE LicenseSubscriptionDetail SET SubscriptionValidFrom = @dtStartDate,
SubscriptionValidThrough = @dtEndDate, TotalAmount = @iPrice, AmountPaid = @iPrice , NoofImages =@iNoofImages
WHERE Id = @iLicenseSubscriptionId
END
-- check if license is site license
IF @iLicenseTypeId = 3
BEGIN
SET @iSiteId = (SELECT DISTINCT Max(Site.Id) FROM LicenseToEdition
INNER JOIN SiteToLicenseEdition ON LicenseToEdition.Id = SiteToLicenseEdition.LicenseEditionId
INNER JOIN Site ON SiteToLicenseEdition.SiteId = Site.Id
WHERE LicenseToEdition.LicenseId=@iLicenseId AND Site.IsMaster=1 AND Site.IsActive=1)
UPDATE Site SET SiteIP = @sMasterIP, Title = @sMasterIP, ModifiedDate = @dtCurrentDate,
SiteIPTo = @sSiteIPTo, SiteMasterIPTo = @sSiteMasterIPTo
WHERE Id = @iSiteId
END
SET @cEditionLogins = CURSOR FAST_FORWARD FOR SELECT item FROM dbo.fnSplit(@sEditionList,@sRecordDelimiter)
OPEN @cEditionLogins
FETCH NEXT FROM @cEditionLogins INTO @sitem
WHILE @@FETCH_STATUS = 0
BEGIN
SET @iEditionExists = (SELECT 1 FROM LicenseToEdition WHERE LicenseId = @iLicenseId AND EditionId = SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1))
IF @iEditionExists IS NULL OR @iEditionExists = 0
BEGIN
INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty)
SELECT @iLicenseId, SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1),
SUBSTRING(@sitem,CHARINDEX(@sEditionLoginDelimiter,@sitem)+1,LEN(@sitem)), @iModesty
-- check if license is site license
IF @iLicenseTypeId = 3
BEGIN
-- to get the last inserted licenseedition id identity value in the current session
SET @iLicenseEditionId = SCOPE_IDENTITY()
INSERT INTO SiteToLicenseEdition (SiteId, LicenseEditionId, IsModesty) VALUES (@iSiteId, @iLicenseEditionId, @iModesty)
END
END
ELSE
BEGIN
UPDATE LicenseToEdition SET TotalLogins = SUBSTRING(@sitem,CHARINDEX(@sEditionLoginDelimiter,@sitem)+1,LEN(@sitem))
WHERE LicenseId = @iLicenseId
AND EditionId = SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1)
END
FETCH NEXT FROM @cEditionLogins INTO @sitem
END
COMMIT TRANSACTION
SELECT @sErrorStatus as SPStatus
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT Error_Message() as SPStatus
END CATCH
END
GO