dbo.InsertDemoLicenseAccount.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
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[InsertDemoLicenseAccount] Script Date: 02/06/2018 10:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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;renata.nascimento@ebix.com;thaisa.braguim@ebix.com;DigitalContentTrials@FollettSoftware.com;luis.maida@rackspace.ebix.com;Daniela.Laiati@rackspace.ebix.com;Deshawn.cousette@ebix.com;Melanie.foye@ebix.com;Deidre.Friday@Ebix.com;Ashish.jain@ebix.com;Amrita.vishnoi@ebix.com;ytyagi@ebix.com;naina.sehgal@ebix.com;',
@body = @sMailBody,
@body_format = 'HTML',
@subject = 'AIA - 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