USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[InsertNewDiscount]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Author:		Magic Software
-- Create date: 23-Dec-2009
-- Description:	To create new discount
-- ====================================================

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

CREATE PROCEDURE [dbo].[InsertNewDiscount] 
	-- Add the parameters for the stored procedure here
	@dPercentage DECIMAL(5,2), @sStartDate VARCHAR(20), @sEndDate VARCHAR(20), @sDiscountCode VARCHAR(255)=''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	BEGIN TRY
		BEGIN TRANSACTION
			DECLARE @iDiscountId INT, @iDiscountExists INT
			DECLARE @iActive TINYINT
			DECLARE @dtStartDate DATETIME, @dtEndDate DATETIME
			DECLARE @sErrorStatus CHAR(2)
			
			SET @iActive = 1
			SET @sErrorStatus = 'ok'
			
			-- 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 Discount (Percentage, StartDate, EndDate, IsActive) 
				VALUES(@dPercentage, @dtStartDate, @dtEndDate, @iActive)
			-- to get the last inserted discount id identity value in the current session
			SET @iDiscountId = SCOPE_IDENTITY()
			
			IF @sDiscountCode = ''
			BEGIN
				SET @sDiscountCode = 'InteractiveAnatomy'+RIGHT('000'+CAST(@iDiscountId AS VARCHAR(10)), 3)
				SET @iDiscountExists = (SELECT Id FROM Discount WHERE DiscountCode = @sDiscountCode)
				IF @iDiscountExists > 0
				BEGIN
					UPDATE Discount SET IsActive = 0 WHERE Id = @iDiscountExists
				END
			END
			UPDATE Discount SET DiscountCode = @sDiscountCode WHERE Id = @iDiscountId
			
		COMMIT
		SELECT @sErrorStatus as SPStatus
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 
			ROLLBACK TRANSACTION
		SELECT Error_Message() as SPStatus
	END CATCH
   
END

GO