CREATE FUNCTION [dbo].[BonusPointMaxSpending](@FolioNumber int) 
RETURNS decimal(7,3)
AS
Begin
    --2020-11-23 MLJ Improved performance
	--               Fixed bugs: Manual bonus points was not included in the calculation.
	--                           Calculation was wrong, when there was already bonus payments on the folio.
	--2019-08-20 SG: Returns MAX amount to be spend using bonus points. Only Bonus point system 2019!
	--2019-08-02 SG: Returns MAX number of BonusPoints to be spend considering the HotelSetup 'BonusPointMinimumBeforeSpending'

	Declare @CustomerNumber int;
	Declare @BonusPointMinimumBeforeSpending int = 0
	Declare @BonusPointTotal money;
	DECLARE @BonusPointSystemUsed int = 0

    SELECT @BonusPointSystemUsed = Convert(int, IsNull(KeyValue, '0')) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'UseBonusPointSystem2019' --0: Original system, 1: System2019
    --Only System 2019!
    If @BonusPointSystemUsed = 0
      Return 0;
	
	SELECT @BonusPointMinimumBeforeSpending = Convert(int, IsNull(KeyValue, '0')) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BonusPointMinimumBeforeSpending'
	--Find CustomerNumber as in BonusPointReport
    Select top 1 @CustomerNumber = c.CustomerNumber 
	from Customer C WITH (NOLOCK)
    INNER JOIN CustomerCardXTab CCX WITH (NOLOCK) ON CCX.CustomerNumber = C.CustomerNumber AND CCX.LogNumberDelete IS NULL	
    INNER JOIN Card WITH (NOLOCK) ON Card.PK_Card = CCX.FK_Card
    INNER JOIN CardTypeXTab CTX WITH (NOLOCK) ON CTX.FK_Card = Card.PK_Card
    INNER JOIN CardType CT WITH (NOLOCK) ON CT.PK_CardType = CTX.FK_CardType AND CT.Type = 2 /* Bonus card */
    INNER JOIN FolioCardXTab FCX WITH (NOLOCK) ON Card.PK_Card = FCX.FK_Card AND FCX.LogNumberDelete IS NULL
    INNER JOIN Folios FolioGuest WITH (NOLOCK) ON FCX.FolioNumber = FolioGuest.FolioNumber
    LEFT OUTER JOIN Folios FolioMain WITH (NOLOCK) ON  
      FCX.FolioNumber <> FolioMain.FolioNumber AND
      FolioMain.ReferenceNumber = FolioGuest.ReferenceNumber  AND 
      FolioMain.GuestNumber = 0
    WHERE
    FolioGuest.FolioNumber = @FolioNumber or FolioMain.FolioNumber = @FolioNumber	

    If @CustomerNumber IS NULL
      Return 0;
	
	--Find BonusPoints balance and corresponding amount for the customer
	SELECT @BonusPointTotal = SUM(BonusPointsTotal)	FROM BonusPointReport(Convert(varchar, @CustomerNumber), '', '', '', '', 0, 0)  
	
	Set @BonusPointTotal = IsNull(@BonusPointTotal, 0);

	--**************************************************************
	--Enough point for spending at all?
	If @BonusPointTotal <= @BonusPointMinimumBeforeSpending
	Begin
	  RETURN 0
	End
	--**************************************************************
    
--Then calculate sum of bonus point it is possible to spend in folio
--and find the amounts of bonus points already spend in this folio

  DECLARE @BonusRatio decimal(9,2) = 1
  DECLARE @BonusRatioSpend decimal(9,2) = 1
  DECLARE @BPEarningRound varchar(10) ='None';
  DECLARE @BPSpendRound varchar(10) ='None';
  DECLARE @TempHotelSetup varchar(20);
  DECLARE @SumBonusPointSpendable decimal(9,2)   --BonusPoint
  DECLARE @SumBonusPointAlreadySpend decimal(9,2)--BonusPoint
  

  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'


;
	WITH FG
	AS
	(
		SELECT FolioGuest.FolioNumber, FolioGuest.ReferenceNumber, FolioGuest.GuestNumber
		FROM CustomerCardXTab CCX WITH (NOLOCK)
		INNER JOIN Customer C WITH (NOLOCK) ON CCX.CustomerNumber = C.CustomerNumber AND CCX.LogNumberDelete IS NULL AND C.CustomerNumber=@CustomerNumber
				AND C.MarkAsDeleted <> 1
		INNER JOIN Card WITH (NOLOCK) ON Card.PK_Card = CCX.FK_Card
		INNER JOIN CardTypeXTab CTX WITH (NOLOCK) ON CTX.FK_Card = Card.PK_Card
		INNER JOIN CardType CT WITH (NOLOCK) ON CT.PK_CardType = CTX.FK_CardType AND CT.Type = 2 /* Bonus card */
		INNER JOIN FolioCardXTab FCX WITH (NOLOCK) ON Card.PK_Card = FCX.FK_Card AND FCX.LogNumberDelete IS NULL
		INNER JOIN Folios FolioGuest WITH (NOLOCK) ON FCX.FolioNumber = FolioGuest.FolioNumber
	)
	,T
	AS
	(	
		SELECT T.TransactionNumber, T.FolioNumber, T.Payment, T.Debit, T.PriceOverwrite, T.WareNumber
		FROM FG
		INNER JOIN Transactions T WITH (NOLOCK) ON T.FolioNumber = FG.FolioNumber
		  AND T.ShowOnBillFlag = 1
		  AND T.Payment in (0, 21)
		  AND T.MovedDeleted = 0
		WHERE FG.FolioNumber=@FolioNumber
		UNION
		SELECT T.TransactionNumber, T.FolioNumber, T.Payment, T.Debit, T.PriceOverwrite, T.WareNumber
		FROM FG
		INNER JOIN Folios FolioMain WITH (NOLOCK) ON  
		  FG.FolioNumber <> FolioMain.FolioNumber AND
		  FolioMain.ReferenceNumber = FG.ReferenceNumber  AND 
		  FolioMain.GuestNumber = 0
		INNER JOIN Transactions T WITH (NOLOCK) ON 
			   T.FolioNumber = FolioMain.FolioNumber AND 
				dbo.GetTransactionWareGroupPayment(T.TransactionNumber) = 2 AND
				T.GuestNumber = FG.GuestNumber
				AND T.ShowOnBillFlag = 1
				AND T.Payment in (0, 21)
				AND T.MovedDeleted = 0
		WHERE FolioMain.FolioNumber=@FolioNumber
	)
	SELECT @SumBonusPointSpendable = 
      SUM(
	  CASE 
	       WHEN @BPSpendRound = 'Normal' THEN Round(BonusSpend, 0)
	       WHEN @BPSpendRound = 'Floor' THEN Floor(BonusSpend)
	       WHEN @BPSpendRound = 'Ceiling' THEN Ceiling(BonusSpend) 
		   ELSE BonusSpend
	  END),
      @SumBonusPointAlreadySpend = SUM(
	  CASE 
	       WHEN @BPSpendRound = 'Normal' THEN Round(BonusAlreadySpend, 0)
	       WHEN @BPSpendRound = 'Floor' THEN Floor(BonusAlreadySpend)
	       WHEN @BPSpendRound = 'Ceiling' THEN Ceiling(BonusAlreadySpend)
		   ELSE BonusAlreadySpend
	  END)
   FROM	   
   (SELECT  
	  CASE WHEN t.Payment = 0 Then
	    CASE T.Debit WHEN 0 THEN 
		  -T.PriceOverwrite ELSE T.PriceOverwrite END * Ware.BonusPointsMinus / @BonusRatioSpend
		ELSE 0 
	  END AS BonusSpend,
	  CASE WHEN t.Payment = 21 THEN CASE T.Debit WHEN 0 THEN -T.PriceOverwrite ELSE T.PriceOverwrite END / @BonusRatioSpend ELSE 0 END
      AS BonusAlreadySpend
	FROM T
    INNER JOIN Ware WITH (NOLOCK) ON T.WareNumber = Ware.WareNumber
	) x

	declare @RemainingBonusPointsOnFolio int = ISNULL(@SumBonusPointSpendable,0) - ISNULL(@SumBonusPointAlreadySpend,0)	
	If @RemainingBonusPointsOnFolio > @BonusPointTotal 
		Set @RemainingBonusPointsOnFolio = @BonusPointTotal 	
	return @RemainingBonusPointsOnFolio  * @BonusRatioSpend
End
