USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetDiscountedPrice]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:	MAGIC SOFTWARE PVT LTD
-- Create date:	22th Dec, 2009
-- Description:	This SP is used calculated discounted price and return discounted price
--		and Error or success code		
-- =============================================


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetDiscountedPrice]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetDiscountedPrice]
GO

CREATE PROCEDURE [dbo].[GetDiscountedPrice]

	@inDiscountCode VARCHAR(255),
	@outReturnCode TINYINT output,
	@outDiscountPercentage DECIMAL (5,2) output,
	@outDiscountCodeId int output

AS
BEGIN
	
	--Declaring variable to be used
	--DECLARE @iTotalPrice INT
	DECLARE @iReturnCode TINYINT,
		@iDiscountPercentage DECIMAL (5,2),
		@irActive bit,
		@brDiscountDatePassed bit,
		@brDiscountDateNotStarted bit,
		@iDiscountCodeId int,
		@iExist bit
	
	SET @iExist = 0;
	
	SELECT @irActive = IsActive, @iExist = 1,
		@brDiscountDatePassed = ( CASE WHEN EndDate < CAST( GETDATE() as DATE ) THEN 1 ELSE 0 END ),
		@brDiscountDateNotStarted = ( CASE WHEN StartDate > CAST( GETDATE() as DATE ) THEN 1 ELSE 0 END )
	FROM Discount 
	WHERE DiscountCode = @inDiscountCode
	
	SET NOCOUNT OFF

	IF ( @iExist = 0)
	BEGIN

		SET @outReturnCode = 4

	END
	
	ELSE IF ( @irActive = 0 OR @brDiscountDatePassed = 1 )
	BEGIN

		SET @outReturnCode = 1

	END
	
	-- Chekcing whether discount start date is still to come, if yes return with error code 6
	ELSE IF ( @brDiscountDateNotStarted = 1 )
	BEGIN

		SET @outReturnCode = 2

	END

	-- Checking Whether discount code is available for any of the Images
	ELSE
	BEGIN

		-- getting discount percentage for which discount code is valid
		SELECT @iDiscountPercentage = Percentage, @iDiscountCodeId = Id 
		FROM Discount 
		WHERE DiscountCode = @inDiscountCode 

		-- Setting all the out parameter
		SET @outDiscountPercentage = @iDiscountPercentage
		SET @outReturnCode = 3
		SET @outDiscountCodeId = @iDiscountCodeId

	END

	SELECT @outReturnCode, @outDiscountPercentage, @outDiscountCodeId
		
END
GO