CREATE PROCEDURE dbo.BonusPointSpend @AReferenceNumber INT, @AGuestNumber INT AS
/*
  Created by Kent Johansen, September 2019
  Returns number of bonus point spend for payment on ref/guest
*/

DECLARE @BonusRatioSpend DECIMAL(9,2) 
DECLARE @TempHotelSetup VARCHAR(20)
DECLARE @BPSpendRound VARCHAR(10)
DECLARE @BonusAlreadySpend DECIMAL(9,2)

SELECT @TempHotelSetup = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BonusPointRatioSpend'
SET @TempHotelSetup = REPLACE(ISNULL(@TempHotelSetup, '0'), ',', '.')
SET @BonusRatioSpend = CONVERT(DECIMAL(9,2), @TempHotelSetup)
IF @BonusRatioSpend = 0
  SET @BonusRatioSpend = 1

SELECT @BPSpendRound = KeyValue FROM Hotelsetup With (NOLOCK) WHERE Searchkey = 'BonusPointSpendingRounding'
SET @BPSpendRound = ISNULL(@BPSpendRound, 'None')

SELECT @BonusAlreadySpend = 
  CASE WHEN Transactions.Debit = 0 THEN 
    -Transactions.PriceOverwrite 
  ELSE 
    Transactions.PriceOverwrite 
  END * Transactions.Pieces / @BonusRatioSpend
FROM Transactions WITH (NOLOCK)
INNER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = Transactions.FolioNumber
  AND Folios.ReferenceNumber = @AReferenceNumber
  AND Folios.GuestNumber = @AGuestNumber
WHERE Payment = 21 -- Bonus payment
AND Transactions.ShowOnBillFlag = 1
AND Transactions.MovedDeleted = 0

SELECT
  CASE 
    WHEN @BPSpendRound = 'None' THEN @BonusAlreadySpend
    WHEN @BPSpendRound = 'Normal' THEN ROUND(@BonusAlreadySpend, 0)
    WHEN @BPSpendRound = 'Floor' THEN FLOOR(@BonusAlreadySpend)
    WHEN @BPSpendRound = 'Ceiling' THEN CEILING(@BonusAlreadySpend)
 END AS PointSpend