USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[usp_InsertAIAUser]    Script Date: 1/31/2018 12:54:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Magic Software
-- Create date: 29-Apr-2009
-- Description:	To create a new AIA User
-- =============================================
ALTER PROCEDURE [dbo].[usp_InsertAIAUser] 
	-- Add the parameters for the stored procedure here
	@sLoginId varchar(50), @sPassword varchar(50), @sFirstname varchar(50), @sLastname varchar(50),
	@iUserTypeId tinyint, @sEmailId varchar(50), @iSecurityQuesId tinyint, @sSecurityAnswer varchar(50)='',
	@iCreatorId int, @iLicenseId int, @iEditionId tinyint,@Status int out 
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @iLicenseEditionId int

			DECLARE @iAIAUserId int
			DECLARE @iActive tinyint
			DECLARE @dtCurrentDate datetime
			DECLARE @sErrorStatus char(2)
			DECLARE @sInvalidLicenseToEdition varchar(100)
			-- to store the user type id of general admin
			DECLARE @iGAUserTypeId tinyint
			-- to store the role id of general admin
			DECLARE @iGARoleId tinyint
			set @Status = 0;
			-- set the parameters to default values
			SET @iActive = 1
			SET @dtCurrentDate = getdate()
			SET @sErrorStatus = 'oks'
			SET @sInvalidLicenseToEdition = 'Edition does not exists for this license.'

			-- fetch the usertype id of the general admin
			SELECT @iGAUserTypeId = Id FROM UserType WHERE Title = 'General Admin'
			-- fetch the role id of the general admin
			SELECT @iGARoleId = Id FROM Role WHERE Title = 'General Admin Role'
			
			IF @iSecurityQuesId = 0
			BEGIN
				SET @iSecurityQuesId = NULL
			END
			IF LEN(@sSecurityAnswer) = 0
			BEGIN
				SET @sSecurityAnswer = NULL
			END
			-- insert the user detail in AIAUser 
			if  (Select count(*) from AIAUser Where LoginId=@sLoginId)>0
			begin 
			set @Status=1 -- UserName Already Exist
			end
			else if  (Select count(*) from AIAUser Where EmailId=@sEmailId)>0
			begin 
			set @Status=2 -- Email Id Already Exist
			end
			else 
			begin
			INSERT INTO AIAUser(LoginId, Password, Firstname, Lastname, UserTypeId, EmailId, IsActive, SecurityQuestionId, 
			SecurityAnswer, CreatorId, CreationDate, ModifierId, ModifiedDate) VALUES(@sLoginId, @sPassword, @sFirstname, 
			@sLastname, @iUserTypeId, @sEmailId, @iActive, @iSecurityQuesId, @sSecurityAnswer,
			@iCreatorId, @dtCurrentDate, @iCreatorId, @dtCurrentDate)
			SET @iAIAUserId = SCOPE_IDENTITY()
			-- if user type is general admin then inserts map its role with newly generated UserId
			IF @iUserTypeId = @iGAUserTypeId
			BEGIN
				-- insert the mapping of user with role into AIAUserActivity
				INSERT INTO AIAUserActivity(UserId, RoleId) VALUES(@iAIAUserId, @iGARoleId)		
			END
			ELSE
			BEGIN
				-- select the id of edition mapped with the license id
				SELECT @iLicenseEditionId = LicenseToEdition.Id FROM LicenseToEdition
				WHERE LicenseToEdition.LicenseId = @iLicenseId AND LicenseToEdition.EditionId = @iEditionId
				IF @@ROWCOUNT = 0
				BEGIN
					RAISERROR(@sInvalidLicenseToEdition,16,61)
				END
				-- insert the mapping of user with license edition into AIAUserToLicenseEdition 
				INSERT INTO AIAUserToLicenseEdition(UserId, LicenseEditionId) VALUES(@iAIAUserId, @iLicenseEditionId)		
			END 
			set @Status=3	
			End
				
			COMMIT TRANSACTION
		--Print @Status
		--SELECT @sErrorStatus as SPStatus
		SELECT @Status as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			 ROLLBACK TRANSACTION
			--set @Status=4
		--SELECT @Status as SPStatus
		--SELECT Error_Message() as SPStatus
		
	END CATCH
    
END