USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[InsertTestLicenseAccount]    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].[InsertTestLicenseAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertTestLicenseAccount]
GO

CREATE PROCEDURE [dbo].[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


GO