USE [AIADatabaseV5] GO /****** Object: StoredProcedure [dbo].[InsertSingleLicenseAccount] 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 test license account -- ============================================= if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertSingleLicenseAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[InsertSingleLicenseAccount] GO CREATE PROCEDURE [dbo].[InsertSingleLicenseAccount] -- Add the parameters for the stored procedure here @sAccountNumber varchar(50), @sLicenseeFname varchar(50), @sLicenseeLname varchar(50), @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), @iPrice numeric(14,2),@sProductKey varchar(50), @sLoginId varchar(50), @sPassword varchar(50), @iSecurityQuesId tinyint, @sSecurityAnswer varchar(50), @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 @cEditionLogins CURSOR DECLARE @iLicenseId INT DECLARE @iSiteId INT DECLARE @iLicenseEditionId INT DECLARE @iIsDistrictSiteAccount TINYINT DECLARE @iLicenseTypeId TINYINT DECLARE @iUserTypeId TINYINT DECLARE @iAIAUserId INT 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 @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 IF @iSecurityQuesId = 0 BEGIN SET @iSecurityQuesId = NULL END IF LEN(@sSecurityAnswer) = 0 BEGIN SET @sSecurityAnswer = NULL 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 licensetypeid of the single license SELECT @iLicenseTypeId = Id from LicenseType WHERE Title = 'Single License' -- fetch the usertypeid of the single user SELECT @iUserTypeId = Id from UserType WHERE Title = 'Single User' 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) 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 FETCH NEXT FROM @cEditionLogins INTO @sitem END SET @iLicenseEditionId = SCOPE_IDENTITY() INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive, SecurityQuestionId, SecurityAnswer, CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sPassword, @sLicenseeFname, @sLicenseeLname, @iUserTypeId, @sEmailId, @iActive, @iSecurityQuesId, @sSecurityAnswer, @iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate) SET @iAIAUserId = SCOPE_IDENTITY() INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId) 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