dbo.InsertNewLicenseAccount.StoredProcedure.sql
15.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
167
168
169
170
171
172
173
174
175
176
177
178
179
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[InsertNewLicenseAccount] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Magic Software
-- Create date: 22-Apr-2009
-- Description: To create a new license account
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertNewLicenseAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertNewLicenseAccount]
GO
CREATE PROCEDURE [dbo].[InsertNewLicenseAccount]
-- Add the parameters for the stored procedure here
@sAccountNumber varchar(50), @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), @iTotalLogins int, @sStartDate varchar(20),
@sEndDate 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 @iLicenseId INT
DECLARE @iSiteId INT
DECLARE @iLicenseEditionId INT
DECLARE @iIsDistrictSiteAccount TINYINT
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 @iIsMasterIP = 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
-- 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)))
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)
-- check if license is site license
IF @iLicenseTypeId = 3
BEGIN
INSERT INTO Site (SiteIP, Title, InstituteName, Address1, Address2, City, Zip, Phone,
StateId, CountryId, IsMaster, IsActive, CreationDate, SiteIPTo, SiteMasterIpTo)
VALUES(@sMasterIP, @sMasterIP, @sInstitutionName, @sAddress1, @sAddress2, @sCity, @sZip, @sPhone,
@iStateId, @iCountryId, @iIsMasterIP, @iActive, @dtCurrentDate,@sSiteIPTo, @sSiteMasterIPTo)
-- to get the last inserted site id identity value in the current session
SET @iSiteId = SCOPE_IDENTITY()
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
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
-- 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
FETCH NEXT FROM @cEditionLogins INTO @sitem
END
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
GO