usp_InsertResellerLicenseAccount.sql
9.09 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
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[InsertResellerLicenseAccount] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Author: Magic Software
-- Create date: 20-May-2009
-- Description: To create new reseller license account
-- ====================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertResellerLicenseAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_InsertResellerLicenseAccount]
GO
CREATE PROCEDURE [dbo].[usp_InsertResellerLicenseAccount]
-- Add the parameters for the stored procedure here
@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), @sEditionList varchar(256),
@iTotalPrice numeric(14,2), @iCreatorId int, @sProductKey varchar(50),@iNoofImages int
AS
BEGIN
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- 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 @sAccountNumber VARCHAR(50)
DECLARE @iLicenseEditionId INT
DECLARE @iIsDistrictSiteAccount TINYINT
DECLARE @iActive 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 @iCount INT
DECLARE @iLicenseLogins INT
DECLARE @iPrice NUMERIC(14,2)
DECLARE @sPaymentMode VARCHAR(10)
DECLARE @iUserTypeId TINYINT
DECLARE @sLoginId VARCHAR(50)
DECLARE @iAIAUserId INT
DECLARE @iLastAIAUserId INT
DECLARE @iUserExists TINYINT
DECLARE @sCountryCode VARCHAR(10)
-- create temporary table to store newly created account number, loginid & password
CREATE TABLE #LicenseDetail
(
AccountNumber VARCHAR(50),
LoginId VARCHAR(50),
Password VARCHAR(50)
)
-- set the parameters to default values
SET @iCount = 1
SET @iLicenseLogins = 1;
SET @iActive = 1
SET @iIsDistrictSiteAccount = 0
SET @iModesty = 0
SET @sPaymentMode = 'CASH'
SET @sRecordDelimiter = '|'
SET @sEditionLoginDelimiter = '-'
SET @dtCurrentDate = getdate()
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 @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sEndDate)))
-- fetch the id for the Reseller user type
SELECT @iUserTypeId = Id FROM UserType WHERE Title = 'Reseller'
-- calculate the price of an individual reseller license
SET @iPrice = @iTotalPrice / @iTotalLogins
-- get the edition id and total number of logins
SET @cEditionLogins = CURSOR FAST_FORWARD FOR SELECT item FROM dbo.fnSplit(@sEditionList,@sRecordDelimiter)
-- loop through the cursor to insert the individual licenses for reseller
OPEN @cEditionLogins
FETCH NEXT FROM @cEditionLogins INTO @sitem
WHILE @@FETCH_STATUS = 0
BEGIN
set @iCount = 0;
set @iTotalLogins = SUBSTRING(@sitem,CHARINDEX(@sEditionLoginDelimiter,@sitem) + 1,len(@sitem)-CHARINDEX(@sEditionLoginDelimiter,@sitem));
WHILE(@iCount < @iTotalLogins)
BEGIN
SET @iUserExists = 0
-- create a new reseller license
INSERT INTO License(LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId,
InstitutionName, EmailId, Address1, Address2, City, Zip, StateId, CountryId, Phone, TotalLogins, IsActive,
IsDistrictSiteLicense, CreationDate, ProductId) VALUES (@sLicenseeFname, @sLicenseeLname, @iLicenseTypeId,
@iAccountTypeId, @sInstitutionName, @sEmailId, @sAddress1, @sAddress2, @sCity, @sZip, @iStateId, @iCountryId,
@sPhone, @iLicenseLogins, @iActive, @iIsDistrictSiteAccount, @dtCurrentDate, @sProductKey)
-- to get the last inserted license id identity value in the current session
SET @iLicenseId = SCOPE_IDENTITY()
-- get a unique account number
SET @sAccountNumber = 'AIARS'+RIGHT('000000'+CONVERT(varchar(10),@iLicenseId),6)
UPDATE License SET AccountNumber = @sAccountNumber WHERE Id = @iLicenseId
-- insert the subscription detail of license
INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
PaymentMode, TotalAmount, AmountPaid,NoofImages) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @sPaymentMode, @iPrice, @iPrice,@iNoofImages)
IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) <= 4
BEGIN
-- insert All resource module of license for Instructor Edition
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;
END
ELSE IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) = 8
BEGIN
-- insert All resource module of license for Library Edition
INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId, CASE when ResourceModule.id < 11 and ResourceModule.id <> 6 or ResourceModule.id = 13 then 0 else 1 end as Status FROM ResourceModule;
END
ELSE IF SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1) = 9
BEGIN
-- insert All resource module of license for Library Edition
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;
END
-- insert the mapping of license with edition
INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty)
SELECT @iLicenseId, SUBSTRING(@sitem,1,CHARINDEX(@sEditionLoginDelimiter,@sitem)-1),
@iLicenseLogins, @iModesty
-- to get the last inserted licenseedition id identity value in the current session
SET @iLicenseEditionId = SCOPE_IDENTITY()
-- fetch the last auto incremented id of aiauser table
SET @iLastAIAUserId = (SELECT MAX(Id) FROM AIAUser)
-- create a loginid for user
SET @sLoginId = 'RS'+CAST(convert(varchar,@dtCurrentDate,12)as varchar)+char(@iLastAIAUserId/260000%26+65)+
char(@iLastAIAUserId/10000%26+65)+ char(@iLastAIAUserId/1000%10+48)+char(@iLastAIAUserId/100%10+48)+
char(@iLastAIAUserId/10%10+48)+char(@iLastAIAUserId%10+48)
-- check if the created loginid already exists if yes then create a new one
SET @iUserExists = (SELECT 1 FROM AIAUser WHERE LoginId = @sLoginId)
WHILE @iUserExists > 0
BEGIN
SET @iLastAIAUserId = @iLastAIAUserId + 1
SET @sLoginId = 'RS'+CAST(convert(varchar,@dtCurrentDate,12)as varchar)+char(@iLastAIAUserId/260000%26+65)+
char(@iLastAIAUserId/10000%26+65)+ char(@iLastAIAUserId/1000%10+48)+char(@iLastAIAUserId/100%10+48)+
char(@iLastAIAUserId/10%10+48)+char(@iLastAIAUserId%10+48)
SET @iUserExists = (SELECT 1 FROM AIAUser WHERE LoginId = @sLoginId)
END
-- create a user for reseller account
INSERT INTO AIAUser (LoginId, Password, UserTypeId, IsActive, CreatorId, CreationDate, ModifierId, ModifiedDate)
VALUES(@sLoginId, @sLoginId, @iUserTypeId, @iActive, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
-- to get the last inserted aiauser id identity value in the current session
SET @iAIAUserId = SCOPE_IDENTITY()
-- insert the mapping of user with license edition
INSERT INTO AIAUserToLicenseEdition (UserId, LicenseEditionId) VALUES (@iAIAUserId, @iLicenseEditionId)
-- insert newly created account number, loginid & password into temporary table
INSERT INTO #LicenseDetail (AccountNumber,LoginId,Password) VALUES(@sAccountNumber, @sLoginId, @sLoginId)
SET @iCount = @iCount+1
END
FETCH NEXT FROM @cEditionLogins INTO @sitem
END
CLOSE @cEditionLogins
DEALLOCATE @cEditionLogins
--------------------------------------------------------------------------
COMMIT TRANSACTION
SELECT @sErrorStatus as SPStatus,AccountNumber,LoginId,Password FROM #LicenseDetail
-- drop the temporary table
DROP TABLE #LicenseDetail
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT Error_Message() as SPStatus,'' as AccountNumber,'' as LoginId,'' as Password
END CATCH
END
GO