CREATE FUNCTION dbo.BonusPointForWare(@WareNumber int, @Price money = 0, @Pieces int = 1) 

RETURNS @Result TABLE (
  BonusPointEarn decimal(9,2) Default 0,
  BonusPointEarnValue decimal(9,2) Default 0,
  BonusPointSpend decimal(9,2) Default 0,
  BonusPointSpendValue decimal(9,2) Default 0
) AS 
Begin

--SG 2019-09-02: BonusPointForWare
--This function asumes, that a valid Bonus Card Number has been entered.
--It is the responsability of the caller to assure that.
--When the Price parameter is 0, the price is taken from the ware. 
--When pieces > 1 the rounding is applied on the total points
--Changes:


  DECLARE @BonusPointSystemEnabled int 
  DECLARE @BonusPointSystemUsed int 
  DECLARE @BonusRatio decimal(9,2)
  DECLARE @BonusRatioSpend decimal(9,2) 
  DECLARE @BPEarningRound varchar(10);
  DECLARE @BPSpendRound varchar(10);
  DECLARE @TempHotelSetup varchar(20);
  
  Set @Price = IsNull(@Price, 0)
  Set @Pieces = IsNull(@Pieces, 1)

  Set @BPEarningRound ='None';
  Set @BPSpendRound ='None';
  
  SELECT @BonusPointSystemEnabled = Convert(int, IsNull(KeyValue, '0')) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BonusSystemEnabled'  
  If @BonusPointSystemEnabled = 0
  Begin
    Return;
  End
  SELECT @BonusPointSystemUsed = Convert(int, IsNull(KeyValue, '0')) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'UseBonusPointSystem2019' --0: Original system, 1: System2019
  SELECT @TempHotelSetup = IsNull(KeyValue, '1') FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BonusPointRatio'
  SET @TempHotelSetup = REPLACE(@TempHotelSetup, ',', '.')
  SET @BonusRatio = convert(decimal(9,2), @TempHotelSetup)
  --SET @BonusRatio = dbo.GetTurnOverToBonusPointRatio()
  SELECT @TempHotelSetup = IsNull(KeyValue, '0') FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BonusPointRatioSpend'
  SET @TempHotelSetup = REPLACE(@TempHotelSetup, ',', '.')
  SET @BonusRatioSpend = convert(decimal(9,2), @TempHotelSetup)

  -- 'None', 'Normal', 'Floor', 'Ceiling'
  SELECT @BPEarningRound = IsNull(KeyValue, 'None') FROM Hotelsetup With (Nolock) WHERE Searchkey = 'BonusPointEarningRounding'
  SELECT @BPSpendRound = IsNull(KeyValue, 'None') FROM Hotelsetup With (Nolock) WHERE Searchkey = 'BonusPointSpendingRounding'

  INSERT INTO @Result
    SELECT 
	  CASE WHEN @BPEarningRound = 'None' THEN BonusEarned
	       WHEN @BPEarningRound = 'Normal' THEN Round(BonusEarned, 0)
	       WHEN @BPEarningRound = 'Floor' THEN Floor(BonusEarned)
	       WHEN @BPEarningRound = 'Ceiling' THEN Ceiling(BonusEarned) 
	  END as BonusEarned, 
	  BonusEarnedValue,
	  CASE WHEN @BPSpendRound = 'None' THEN BonusSpend
	       WHEN @BPSpendRound = 'Normal' THEN Round(BonusSpend, 0)
	       WHEN @BPSpendRound = 'Floor' THEN Floor(BonusSpend)
	       WHEN @BPSpendRound = 'Ceiling' THEN Ceiling(BonusSpend) 
	  END as BonusSpend,
	  BonusSpendValue 
	FROM
	(SELECT DISTINCT
	  CASE @BonusPointSystemUsed WHEN 0 THEN  --Original system
	     CASE @Price WHEN 0 THEN Ware.Price ELSE @Price END * @Pieces
	  ELSE                               -- system 2019
	      CASE @Price WHEN 0 THEN Ware.Price ELSE @Price END * Ware.BonusPointsPlus * @Pieces / @BonusRatio
      END
	  AS BonusEarned, 
	  CASE @BonusPointSystemUsed WHEN 0 THEN  --Original system
	     CASE @Price WHEN 0 THEN Ware.Price ELSE @Price END * @Pieces
	  ELSE                               -- system 2019
	      CASE Ware.BonusPointsPlus WHEN 0 THEN 0
		  ELSE CASE @Price WHEN 0 THEN Ware.Price ELSE @Price END * @Pieces
		  END
      END
	  AS BonusEarnedValue, 
      
	  CASE @BonusPointSystemUsed WHEN 0 THEN  --Original system
	    CASE WHEN ((Ware.BonusPointsPlus <> 0) OR (Ware.BonusPointsMinus <> 0)) AND Ware.Price = 0 THEN (Ware.BonusPointsPlus - Ware.BonusPointsMinus) * @Pieces
	         ELSE 0 END	
	  ELSE -- System 2019
	    CASE @Price WHEN 0 THEN Ware.Price ELSE @Price END * Ware.BonusPointsMinus * @Pieces / @BonusRatioSpend
	  END AS BonusSpend,
	  CASE @BonusPointSystemUsed WHEN 0 THEN  --Original system
	    CASE WHEN ((Ware.BonusPointsPlus <> 0) OR (Ware.BonusPointsMinus <> 0)) AND Ware.Price = 0 THEN (Ware.BonusPointsPlus - Ware.BonusPointsMinus) * @Pieces
	         ELSE 0 END	
	  ELSE -- System 2019
	    CASE Ware.BonusPointsMinus WHEN 0 THEN 0
		  ELSE CASE @Price WHEN 0 THEN Ware.Price ELSE @Price END * @Pieces
       END
	  END AS BonusSpendValue
    FROM Ware WITH (NOLOCK)
	WHERE Ware.WareNumber = @WareNumber
	) x

	Return
End


