dbo.usp_InsertSingleLicenseAccount.sql
7.2 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
163
164
165
166
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertSingleLicenseAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_InsertSingleLicenseAccount]
GO
CREATE PROCEDURE [dbo].[usp_InsertSingleLicenseAccount]
-- Add the parameters for the stored procedure here
@sAccountNumber varchar(50), @sLicenseeFname varchar(50), @sLicenseeLname varchar(50), @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), @iTotalLogins int,
@sStartDate varchar(20), @sEndDate varchar(20), @sEditionList varchar(256), @iPrice numeric(14,2),@sProductKey varchar(50),
@sLoginId varchar(50), @sPassword varchar(50), @iSecurityQuesId tinyint, @sSecurityAnswer varchar(50), @iCreatorId int,@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 @iLicenseId INT
DECLARE @iSiteId INT
DECLARE @iLicenseEditionId INT
DECLARE @iIsDistrictSiteAccount TINYINT
DECLARE @iLicenseTypeId TINYINT
DECLARE @iUserTypeId TINYINT
DECLARE @iAIAUserId INT
DECLARE @iActive TINYINT
DECLARE @iIsMasterIP TINYINT
DECLARE @iModesty TINYINT
DECLARE @dtStartDate DATETIME
DECLARE @dtEndDate DATETIME
DECLARE @sErrorStatus CHAR(2)
DECLARE @dtCurrentDate DATETIME
DECLARE @sitem VARCHAR(100)
DECLARE @sRecordDelimiter CHAR(1)
DECLARE @sEditionLoginDelimiter CHAR(1)
DECLARE @sCountryCode VARCHAR(10)
DECLARE @iIsInsEditionSelected TINYINT
DECLARE @iIsLibEditionSelected TINYINT
DECLARE @iIsAcademicLibEditionSelected TINYINT
-- set the parameters to default values
SET @iActive = 1
SET @iIsDistrictSiteAccount = 0
SET @iModesty = 0
SET @sRecordDelimiter = '|'
SET @sEditionLoginDelimiter = '-'
SET @dtCurrentDate = getdate()
SET @sErrorStatus = 'ok'
SET @iIsInsEditionSelected = 0;
SET @iIsLibEditionSelected = 0;
SET @iIsAcademicLibEditionSelected = 0;
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
IF @iSecurityQuesId = 0
BEGIN
SET @iSecurityQuesId = NULL
END
IF LEN(@sSecurityAnswer) = 0
BEGIN
SET @sSecurityAnswer = NULL
END
-- convert the datatype of startdate & enddate parameter to datetime
SELECT @dtStartDate = CONVERT(DATETIME,@sStartDate)
SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
-- fetch the licensetypeid of the single license
SELECT @iLicenseTypeId = Id from LicenseType WHERE Title = 'Single License'
-- fetch the usertypeid of the single user
SELECT @iUserTypeId = Id from UserType WHERE Title = 'Single User'
INSERT INTO License(AccountNumber, LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId,
InstitutionName, EmailId, Address1, Address2, City, Zip, StateId, CountryId, Phone, TotalLogins, IsActive,
IsDistrictSiteLicense, CreationDate,ProductId) VALUES (@sAccountNumber, @sLicenseeFname, @sLicenseeLname, @iLicenseTypeId,
@iAccountTypeId, @sInstitutionName, @sEmailId, @sAddress1, @sAddress2, @sCity, @sZip, @iStateId, @iCountryId,
@sPhone, @iTotalLogins, @iActive, @iIsDistrictSiteAccount, @dtCurrentDate,@sProductKey)
-- to get the last inserted license id identity value in the current session
SET @iLicenseId = SCOPE_IDENTITY()
INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
TotalAmount, AmountPaid,NoofImages) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @iPrice, @iPrice ,@iNoofImages)
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
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
-- chekc if selected edition is instructor or library edition
IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) <= 4
BEGIN
SET @iIsInsEditionSelected = 1;
END
IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) > 4
BEGIN
SET @iIsLibEditionSelected = 1;
END
IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) = 9
BEGIN
SET @iIsAcademicLibEditionSelected = 1;
END
FETCH NEXT FROM @cEditionLogins INTO @sitem
END
SET @iLicenseEditionId = SCOPE_IDENTITY()
INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive, SecurityQuestionId, SecurityAnswer,
CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sPassword, @sLicenseeFname, @sLicenseeLname,
@iUserTypeId, @sEmailId, @iActive, @iSecurityQuesId, @sSecurityAnswer, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
SET @iAIAUserId = SCOPE_IDENTITY()
INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId)
IF @iIsInsEditionSelected = 1 AND @iIsLibEditionSelected = 1
BEGIN
-- insert All resource module of license
INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id in (8,9,10) then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
END
ELSE IF @iIsInsEditionSelected = 1 AND @iIsLibEditionSelected = 0
BEGIN
-- insert All resource module of license
INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id > 7 then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
END
ELSE IF @iIsInsEditionSelected = 0 AND @iIsLibEditionSelected = 1
BEGIN
-- insert All resource module of license
INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6 then 0 else 1 end as Status FROM ResourceModule WHERE ResourceModule.Id <> 13;
END
IF @iIsAcademicLibEditionSelected = 1
BEGIN
-- insert ADAM Image Resouce to license
INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, 1 as Status FROM ResourceModule WHERE ResourceModule.Id = 13;
END
ELSE
BEGIN
-- insert ADAM Image Resouce to license
INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, 0 as Status FROM ResourceModule WHERE ResourceModule.Id = 13;
END
COMMIT TRANSACTION
SELECT @sErrorStatus as SPStatus
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT Error_Message() as SPStatus
END CATCH
END