dbo.InsertDemoLicenseAccount.StoredProcedure.sql
13.4 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
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[InsertDemoLicenseAccount] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertDemoLicenseAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertDemoLicenseAccount]
GO
CREATE PROCEDURE [dbo].[InsertDemoLicenseAccount]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @sAccountNumber varchar(50)
DECLARE @sLoginId varchar(50)
DECLARE @iLicenseId int
DECLARE @iLicenseEditionId int
DECLARE @iAIAUserId int
DECLARE @dtStartDate datetime
DECLARE @dtEndDate datetime
DECLARE @iLastAIAUserId int
DECLARE @sMailBody varchar(1000)
DECLARE @sLicenseeFname varchar(50) = 'Guest'
DECLARE @sLicenseeLname varchar(50) = 'Account'
DECLARE @sEmailId varchar(50) = 'edsales@adamcorp.com'
-- license type of test account
DECLARE @iLicenseTypeId tinyint = 5
-- user type of test account
DECLARE @iUserTypeId tinyint = 8
-- set account type to High School
DECLARE @iAccountTypeId tinyint = 1
DECLARE @iAmount tinyint = 0
DECLARE @iTotalLogins tinyint = 100
DECLARE @iActive tinyint = 1
DECLARE @iModesty tinyint = 0
DECLARE @dtCurrentDate datetime = getdate()
DECLARE @iCountryId int = 233
DECLARE @iStateId int = 51
-- set edition to Instructor's Edition
DECLARE @iEditionId tinyint = 1
DECLARE @iCreatorId int = 1
DECLARE @iLicenseExists tinyint = 0
DECLARE @iLicenseCounter int
DECLARE @sErrorStatus char(2) = 'ok'
-- set the startdate to current date & enddate to seven days ahead of current date
SELECT @dtStartDate = @dtCurrentDate
SELECT @dtEndDate = DATEADD(ms,-3,DATEADD(DAY,7,CONVERT(DATETIME,(CONVERT(VARCHAR,@dtCurrentDate,101)))))
-- create a new guest license account
INSERT INTO License(LicenseeFirstName, LicenseeLastName, LicenseTypeId, AccountTypeId,
EmailId, Zip, StateId, CountryId, TotalLogins, IsActive, IsDistrictSiteLicense,
CreationDate) VALUES (@sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, @iAccountTypeId,
@sEmailId, '', @iStateId, @iCountryId, @iTotalLogins, @iActive, 0, @dtCurrentDate )
-- to get the last inserted identity value in the current session
SET @iLicenseId = SCOPE_IDENTITY()
SET @sAccountNumber = 'AIAGA'+RIGHT('000000'+CONVERT(varchar(10),@iLicenseId),6)
-- check if the created account number already exists if yes then create a new one
SET @iLicenseExists = (SELECT 1 FROM License WHERE AccountNumber = @sAccountNumber)
SET @iLicenseCounter = @iLicenseId
WHILE @iLicenseExists > 0
BEGIN
SET @iLicenseCounter = @iLicenseCounter + 1
SET @sAccountNumber = 'AIAGA'+RIGHT('000000'+CONVERT(varchar(10),@iLicenseCounter),6)
SET @iLicenseExists = (SELECT 1 FROM License WHERE AccountNumber = @sAccountNumber)
END
UPDATE License SET AccountNumber = @sAccountNumber WHERE Id = @iLicenseId
INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
TotalAmount, AmountPaid, AmountPending) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @iAmount, @iAmount, @iAmount)
-- insert resource module of license
INSERT INTO ModuleToLicense SELECT @iLicenseId as LicenseId,ResourceModule.Id as ModuleId,1 as Status
FROM ResourceModule
INSERT INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty)
VALUES(@iLicenseId, @iEditionId, @iTotalLogins, @iModesty)
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 = 'GA'+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)
INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive,
CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sLoginId, @sLicenseeFname, @sLicenseeLname,
@iUserTypeId, @sEmailId, @iActive, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
SET @iAIAUserId = SCOPE_IDENTITY()
INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId)
SET @sMailBody = 'Daily demo Account has been created:<br><br>
<b>User Id</b>: '+@sLoginId+
'<br><b>Password</b>: '+@sLoginId+
'<br><br> This demo account will be active for 7 days effective '+CONVERT(VARCHAR,@dtStartDate,101)+' to '+CONVERT(VARCHAR,@dtEndDate,101);
COMMIT TRANSACTION
EXEC msdb..sp_send_dbmail
@profile_name='MailProfile',
@recipients='info@tfei.org.uk;rahulr@Ebix.com;kpaul@ebix.com;digitalproducts@flr.follett.com;jennifer.hickey@ebix.com;debora.durazzo@ebix.com;renata.nascimento@ebix.com;thaisa.braguim@ebix.com;DigitalContentTrials@FollettSoftware.com; timothy.spaid@ebix.com; lori.byro@ebix.com; luis.maida@rackspace.ebix.com; Daniela.Laiati@rackspace.ebix.com',
@body = @sMailBody,
@body_format = 'HTML',
@subject = 'Demo Account Created.'
SELECT @sErrorStatus as SPStatus
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT Error_Message() as SPStatus
END CATCH
END
GO