dbo.usp_InsertTestLicenseAccount.sql
4.8 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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertTestLicenseAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_InsertTestLicenseAccount]
GO
CREATE PROCEDURE [dbo].[usp_InsertTestLicenseAccount]
-- Add the parameters for the stored procedure here
@sAccountNumber varchar(50), @sLicenseeFname varchar(50), @sLicenseeLname varchar(50), @sLoginId varchar(50), @sPassword varchar(50),
@sEmailId varchar(50), @iAccountTypeId tinyint, @iEditionId tinyint, @sAddress varchar(100)='', @sCity varchar(50)='',
@sZip varchar(20)='', @iStateId int, @iCountryId int, @sPhone varchar(30)='', @sStartDate varchar(20), @sEndDate varchar(20), @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 @iLicenseId int
DECLARE @iLicenseEditionId int
DECLARE @iAIAUserId int
DECLARE @iLicenseTypeId tinyint
DECLARE @iUserTypeId tinyint
DECLARE @iAmount tinyint
DECLARE @iTotalLogins tinyint
DECLARE @iActive tinyint
DECLARE @iModesty tinyint
DECLARE @dtStartDate datetime
DECLARE @dtEndDate datetime
DECLARE @sErrorStatus char(2)
DECLARE @dtCurrentDate datetime
DECLARE @sCountryCode VARCHAR(10)
-- set the parameters to default values
SET @iTotalLogins = 1
SET @iActive = 1
SET @iAmount = 0
SET @iModesty = 0
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
-- fetch the licensetypeid of the test account license
SELECT @iLicenseTypeId = Id from LicenseType WHERE Title = 'Test Account License'
-- fetch the usertypeid of the test account user
SELECT @iUserTypeId = Id from UserType WHERE Title = 'Test Account'
-- 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,
EmailId, Address1, City, Zip, StateId, CountryId, Phone, TotalLogins, IsActive, IsDistrictSiteLicense,
CreationDate) VALUES (@sAccountNumber, @sLicenseeFname, @sLicenseeLname, @iLicenseTypeId, @iAccountTypeId,
@sEmailId, @sAddress, @sCity, @sZip, @iStateId, @iCountryId, @sPhone, @iTotalLogins, @iActive, 0, @dtCurrentDate )
-- to get the last inserted identity value in the current session
SET @iLicenseId = SCOPE_IDENTITY()
INSERT INTO LicenseSubscriptionDetail(LicenseId, SubscriptionValidFrom, SubscriptionValidThrough,
TotalAmount, AmountPaid, AmountPending ,NoofImages) VALUES(@iLicenseId, @dtStartDate, @dtEndDate, @iAmount, @iAmount, @iAmount ,@iNoofImages)
IF @iEditionId <= 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 @iEditionId = 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 @iEditionId = 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 INTO LicenseToEdition(LicenseId, EditionId, TotalLogins, IsModesty)
VALUES(@iLicenseId, @iEditionId, @iTotalLogins, @iModesty)
SET @iLicenseEditionId = SCOPE_IDENTITY()
INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive,
CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sPassword, @sLicenseeFname, @sLicenseeLname,
@iUserTypeId, @sEmailId, @iActive, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
SET @iAIAUserId = SCOPE_IDENTITY()
INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId)
COMMIT TRANSACTION
SELECT @sErrorStatus as SPStatus
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT Error_Message() as SPStatus
END CATCH
END