CREATE FUNCTION dbo.BonusPointExpired(@ACustomerNumber int, 
									 @ADate Char(8)) --SystemDate if empty
RETURNS  Decimal(7,3)
AS 
Begin
--ExpiredBonusPoint (2020-07)
--Returns the sum of expired values at the date @ADate (default: Systemdata
--Only Bonus point system 2019 (new)
--Changes:

Declare @BonusPoint2019ExpireMonth int
Declare @ExpireLimitDate DateTime
Declare @BonusRatio decimal(7,3)
Declare @BonusRatioSpend decimal(7,3)
Declare @TempHotelSetup varchar(20)
Declare @ExpirableManuel decimal(7,3)
Declare @ExpirableEarned decimal(7,3)
Declare @SpendedPoints decimal(7,3)

Declare @BPEarningRound varchar(10);
Declare @BPSpendRound varchar(10);

if @ADate = ''
Begin
  Select top 1 @ADate = SystemDate from System
End
select @BonusPoint2019ExpireMonth = dbo.GetHotelSetupKeyValue('BonusPoint2019ExpireMonths', -1)
SET @BonusPoint2019ExpireMonth = IsNull(@BonusPoint2019ExpireMonth, 12)
Set @ExpireLimitDate = DateAdd(Month, @BonusPoint2019ExpireMonth * -1, @ADate)

SELECT @TempHotelSetup = IsNull(KeyValue, '1') FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BonusPointRatio'
SET @TempHotelSetup = REPLACE(@TempHotelSetup, ',', '.')
SET @BonusRatio = convert(decimal(7,3), @TempHotelSetup)
SELECT @TempHotelSetup = IsNull(KeyValue, '1') FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BonusPointRatioSpend'
SET @TempHotelSetup = REPLACE(@TempHotelSetup, ',', '.')
SET @BonusRatioSpend = convert(decimal(7,3), @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'

--	1. Der findes sum af optjente point, der er mere end xx måneder gamle (SumExpirable)
--	2. Der findes sum af brugte point (SumSpended)
--	3. Expired = SumExpirable - SumSpended

--***************************	
--SumExpirable
--First manuel points
--***************************
SELECT @ExpirableManuel = SUM(BPM.BonusPoints)  
FROM BonusPointsManual BPM WITH (NOLOCK) 
INNER JOIN Customer C WITH (NOLOCK) ON BPM.CustomerNumber = C.CustomerNumber AND C.MarkAsDeleted <> 1
WHERE (1=1)
  AND c.CustomerNumber = @ACustomerNumber
  AND BPM.DateStamp < @ExpireLimitDate

  Set @ExpirableManuel = IsNull(@ExpirableManuel, 0)

--***************************	
--Then earned
--***************************	
    SELECT @ExpirableEarned = 
	  SUM(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)
	FROM
	(SELECT DISTINCT T.TransactionNumber,
      CASE T.Debit WHEN 0 THEN -T.PriceOverwrite * Ware.BonusPointsPlus / @BonusRatio
		 ELSE                   T.PriceOverwrite * Ware.BonusPointsPlus / @BonusRatio END AS BonusEarned

    FROM CustomerCardXTab CCX WITH (NOLOCK)
    INNER JOIN Customer C WITH (NOLOCK) ON CCX.CustomerNumber = C.CustomerNumber AND CCX.LogNumberDelete IS NULL
            AND C.CustomerNumber = @ACustomerNumber 
			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

    LEFT OUTER JOIN Folios FolioMain WITH (NOLOCK) ON  
      FCX.FolioNumber <> FolioMain.FolioNumber AND
      FolioMain.ReferenceNumber = FolioGuest.ReferenceNumber  AND 
      FolioMain.GuestNumber = 0
    LEFT OUTER JOIN Transactions T WITH (NOLOCK) ON 
      (T.FolioNumber = FolioGuest.FolioNumber OR (T.FolioNumber = FolioMain.FolioNumber 
	  AND dbo.GetTransactionWareGroupPayment(T.TransactionNumber) = 2) 
	  AND T.GuestNumber = FolioGuest.GuestNumber)

      AND T.DateStamp < @ExpireLimitDate
      AND T.ShowOnBillFlag = 1
      AND T.Payment = 0
      AND T.MovedDeleted = 0
    INNER JOIN Ware WITH (NOLOCK) ON
      T.WareNumber = Ware.WareNumber
    WHERE
	  Ware.BonusPointsPlus <> 0
    ) x

  Set @ExpirableEarned = IsNull(@ExpirableEarned, 0)
	
--***************************	
-- Sum of spended point until @ADate
--***************************	
    select @SpendedPoints =
	  SUM(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) 
	FROM
	(SELECT DISTINCT T.TransactionNumber,
	     T.PriceOverwrite / @BonusRatioSpend AS BonusSpend     
    FROM CustomerCardXTab CCX WITH (NOLOCK)
    
    INNER JOIN Customer C WITH (NOLOCK) ON CCX.CustomerNumber = C.CustomerNumber AND CCX.LogNumberDelete IS NULL
            AND C.CustomerNumber = @ACustomerNumber 
			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

    LEFT OUTER JOIN Folios FolioMain WITH (NOLOCK) ON  
      FCX.FolioNumber <> FolioMain.FolioNumber AND
      FolioMain.ReferenceNumber = FolioGuest.ReferenceNumber  AND 
      FolioMain.GuestNumber = 0
    LEFT OUTER JOIN Transactions T WITH (NOLOCK) ON 
      (T.FolioNumber = FolioGuest.FolioNumber OR (T.FolioNumber = FolioMain.FolioNumber 
	  AND dbo.GetTransactionWareGroupPayment(T.TransactionNumber) = 2) 
	  AND T.GuestNumber = FolioGuest.GuestNumber)
      AND T.DateStamp <= @ADate
      AND T.ShowOnBillFlag = 1
      AND T.Payment = 21 --Spended bonuspoint
      AND T.MovedDeleted = 0
    INNER JOIN Ware WITH (NOLOCK) ON
      T.WareNumber = Ware.WareNumber
    WHERE
	  Ware.BonusPointsMinus <> 0
    ) x

  Set @SpendedPoints = IsNull(@SpendedPoints, 0)	

  Return @ExpirableManuel + @ExpirableEarned - @SpendedPoints
End
