SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[ConsumerInsert]
	@vchConsumerName varchar(50)
	,@vchAddress1 varchar(100)
	,@vchAddress2 varchar(100)
	,@vchEmail varchar(100)
	,@vchContact varchar(20)
	,@vchUserName varchar(50)
	,@vchPassword varchar(50)
	,@vchCurrencyCode varchar(3)
	,@intModifiedByID int
	,@bitIsActive bit
	,@tintStatus tinyint OUTPUT
	,@vchStatusMessage varchar(50) OUTPUT
AS     
BEGIN
	SET NOCOUNT ON;
	
	SET @vchStatusMessage = ''
	
	BEGIN
	
		IF EXISTS(
					SELECT 1 FROM [dbo].[Consumer]
					WHERE ConsumerName = @vchConsumerName
				 )
		BEGIN
			SET @tintStatus=1  -- 1 for Consumer name duplication
			SET @vchStatusMessage = 'Consumer name already exists'
			RETURN; -- Returns due to duplication 
		END
		ELSE IF EXISTS(
						SELECT 1 FROM [dbo].[Consumer]
						WHERE UserName = @vchUserName
					  )
		BEGIN
			SET @tintStatus=2 -- 2 for User name duplication 
			SET @vchStatusMessage = 'Username already exists'
			RETURN; -- Returns due to duplication 
		END
	END
	
	BEGIN TRY
	
		BEGIN TRANSACTION
		DECLARE	@intConsumerID int
		
		SELECT @intConsumerID = ISNULL(MAX(ConsumerID),0) + 1	--Primary Key auto generated
		FROM [dbo].[Consumer]
		
		INSERT INTO [dbo].[Consumer]  --first insertion only one ID generated whic is passed to the next table insertion below
			(
				ConsumerID--PK
				,ConsumerName
				,Address1
				,Address2
				,Email
				,Contact
				,UserName
				,[Password]
				,CurrencyCode
				,ModifiedByID
				,ModifiedDate
				,IsActive
			)
			VALUES
			(
				@intConsumerID
				,@vchConsumerName 
				,@vchAddress1 
				,@vchAddress2 
				,@vchEmail 
				,@vchContact 
				,@vchUserName 
				,@vchPassword 
				,@vchCurrencyCode 
				,@intModifiedByID 
				,GETDATE() 
				,@bitIsActive 
			 )
		
		BEGIN
			DECLARE	@intRoleConsumerID	int
			
			SELECT @intRoleConsumerID =	ISNULL(MAX(RoleConsumerID),0) 	--getting the maximum of Primary Key 
			FROM [dbo].[RoleConsumer]
			
			
		
			INSERT INTO [dbo].[RoleConsumer]   --for now two insertion are done so there is two PK Created.if there is three insertion then 3 PK created like that
			(                                              -- my problem how to get this 2 PK to be inserted in the next table as FK 
				RoleConsumerID --PK
				,RoleName
				,ConsumerID
				,IsActive
				,IsDefault	
			)
			SELECT
				(ROW_NUMBER() OVER(ORDER BY RoleID)) + @intRoleConsumerID 
				,RoleName
				,@intConsumerID
				,1 -- IsActive
				,1 -- IsDefault
			FROM [dbo].[tbl_OTM_WS_Role]
				
			DECLARE @intRoleConsumerID1 int
			SELECT @intRoleConsumerID1 =  RoleConsumerID FROM [dbo].[RoleConsumer] --only able to get the last ID
		
	
		
			INSERT INTO [dbo].[RolePrivilege]  --Two PK(RoleConsumerID) created in the above insert are FK to this table			I
			(                                           --want both fk to be inserted but not able to get the both the ID only able to get the last     
				RoleConsumerID --FK                           --Inserted PK .In this table the PK is IDENTITY. Don't want to use Trigger due to some other                                      
				,UrlID                                  --buisness logic    
				,[Read]
				,Write
				,Critical	
			)
			SELECT
				@intRoleConsumerID1
				,UrlID
				,[Read]
				,Write
				,Critical
			FROM [dbo].[RolePrivilegeDefault] 	
		END
		
		
	
		COMMIT TRANSACTION
		
		SET @tintStatus = 0 --success
		RETURN;
		
	END TRY	
	BEGIN CATCH
		--SET @tintStatus = -1 -- error
		
		-- Handling errors
		IF @@TRANCOUNT > 0
		BEGIN
			ROLLBACK
		END

		DECLARE @Message VARCHAR(1000)
		DECLARE @Severity int
		DECLARE @State int

		SET @Message = ERROR_MESSAGE()
		SET @Severity = ERROR_SEVERITY()
		SET @State = ERROR_STATE()

		RAISERROR (@Message,@Severity,@State)
		RETURN -1
	END CATCH
END