-----------------------------------------------------------
----------DELETE PROCS IF EXIST:
-----------------------------------------------------------
IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_as_AddUpdateCustomerPrices') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _as_AddUpdateCustomerPrices
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================================================================================
-- Author:		Alexander Toufexis
-- Create date: 25/08/2015 (original: 22/03/2010), 
-- Update date: 19/11/2015 (added: supports customer groups now, as well as inclusive/excl support)
-- Description:	This SP adds and updates customer specific prices in the volume discount module.
-- ===================================================================================================================
CREATE PROCEDURE _as_AddUpdateCustomerPrices
	-- Add the parameters for the stored procedure here
 @CustomerCode varchar(20),
 @GroupCode varchar(20),
 @StockCode varchar(255),
 @Price float,
 @StartDate datetime,
 @EndDate datetime,
 @InclExcl char(1) = 'E'
AS
BEGIN
    --USAGE:
	--on Customer:
	--EXEC _as_AddUpdateCustomerPrices 'CASH', '', 'test', 21, '2015-01-01', '2020-12-31', 'E'
	--on group:
	--EXEC _as_AddUpdateCustomerPrices '', 'GROUP', 'test', 21, '2015-01-01', '2020-12-31', 'E'
	--inclusive vs exclusive: 'I' or 'E'


	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

--TO SET IN CODE: @AccountCode, @StockCode, @Price

DECLARE @CustID int
DECLARE @GroupID int
DECLARE @StockID int
DECLARE @VDID int
DECLARE @VDLnID int
DECLARE @AccountCode varchar(20)

SELECT @AccountCode = @CustomerCode
--SELECT * FROM _etblVDAR

--DEFINE CustomerID and Group ID.
IF (@CustomerCode <> '')
	BEGIN
	SELECT @CustID = (SELECT DCLink From Client WHERE Account = @AccountCode)
	SELECT @GroupID = 0
	END
ELSE IF (@GroupCode <> '')
	BEGIN
		SELECT @CustID = 0
		SELECT @GroupID = (SELECT IdCliClass FROM CliClass WHERE Code = @GroupCode)
	END
	--END of CustomerID and groupID selection

--EARLY OUT CHECK:
IF (COALESCE(@CustID,0) = 0 AND COALESCE(@GroupID,0) = 0) 
	BEGIN
		RaisError('Customer %s or Group %s not found!',16,2,@CustomerCode,@GroupCode)
		RETURN -1
	END
--END OF CHECK

	SELECT @StockID = (SELECT StockLink From StkItem WHERE Code = @StockCode)

	--CHECK IF DISCOUNT CONTRACT EXISTS FOR THE CUSTOMER. IF NOT, CREATE IT.
	IF NOT EXISTS (SELECT * FROM _etblVDAR WHERE COALESCE(iARAPID,0) = @CustID AND COALESCE(iGroupID,0) = @GroupID AND bIsTemplate = 0 AND bARAPAll = 0) 
	BEGIN
	 INSERT INTO [_etblVDAR] ([iARAPID],[iGroupID],[iCurrencyID],[cContractName],[bOnHold],[tDescription],[bARAPAll],[bIsTemplate])
		VALUES (@CustID,@GroupID,0,'',0,NULL,0,0)
	END

	--GET ID OF DISCOUNT CONTRACT OF CUSTOMER
	SELECT @VDID = (SELECT IDVD FROM _etblVDAR WHERE COALESCE(iARAPID,0) = @CustID AND COALESCE(iGroupID,0) = @GroupID AND bIsTemplate = 0 AND bARAPAll = 0)

	--IF LINES DON'T EXIST FOR THE ITEM, CREATE THEM, OTHERWISE UPDATE THEM.
	IF NOT EXISTS (SELECT * FROM _etblVDLnAR WHERE iVDID = @VDID AND iStockID = @StockID)
	 BEGIN
	  INSERT INTO [_etblVDLnAR] ([iVDID],[iStockID] ,[iStGroupID] ,[iCurrencyID] ,[dEffDate] ,[dExpDate] ,[bUseStockPrc] ,[cEnterInclExcl] ,[bIncremental] ,[bStockAll])
		VALUES (@VDID,@StockID,0,0,@StartDate,@EndDate,1,@InclExcl,0,0)
	 END
	ELSE 
	 BEGIN
	  --UPDATE
		UPDATE [_etblVDLnAR] SET
			dEffDate = @StartDate
			,dExpDate = @EndDate
			,[cEnterInclExcl] = @InclExcl
		WHERE
			iVDID = @VDID
			AND
			iStockID = @StockID
	 END
	--GET LINE ID OF THE VOLUME DISCOUNT CONTRACT LINE
	SELECT @VDLnID = (SELECT IDVDLn FROM _etblVDLnAR WHERE iVDID = @VDID and iStockID = @StockID)

	--UPDATE FIRST LEVEL OF VD LINE WITH THE ENTERED PRICE
	IF NOT EXISTS (SELECT * FROM _etblVDLnLvlAR WHERE iVDLnID = @VDLnID AND iLevel = 1)
	 BEGIN
	  --INSERT
	  INSERT INTO [_etblVDLnLvlAR] ([iVDLnID] ,[iLevel] ,[fQuantity] ,[fPriceDisc])
		 VALUES
			   (@VDLnID, 1,1,@Price)
	 END
	ELSE
	 BEGIN
	  -- UPDATE
	  UPDATE [_etblVDLnLvlAR] SET
		fPriceDisc = @Price
	  WHERE
		iVDLnID = @VDLnID
		AND
		iLevel = 1
	 END

END
GO