CREATE FUNCTION [dbo].[BonusPointReport] (@ACustomerNumber ntext, @ADateFrom char(8), @ADateTo char(8), @ASegment varchar(128), @AHotelCustomerNumbers varchar(128), @ManuelBonusWaresOnly bit = 0, @IncludeExpiredBonusPoints bit = 0)  
RETURNS @Result TABLE (
  TransactionNumber int,
  DateStamp datetime NOT NULL,
  CustomerNumber int NOT NULL,
  CustomerName varchar(255) NOT NULL,
  FolioNumber int,
  ReferenceNumber int,
  GuestNumber int,
  RoomNumber int,
  WareName varchar(255),
  Pieces int not null default 1.0, 
  Price money not null default 0.0,
  TotalPrice money not null default 0.0,
  BonusPointsEarned money not null,
  BonusPointsSpent money not null,
  BonusPointsTotal money not null,
  HotelCustomerNumber int not null,
  SegmentNumber int not null,
  OriginalDate datetime NOT NULL,
  PointType int not null) AS  --1: Earned, 2: Spend, 3: Manual, 4: Expired (system2019)
BEGIN 
-- SG  2021-02-22 Error in converting non numeric Note to integer
-- MLJ 2020-11-23 Changed calculation of SPENT bonus point so that Ware.BonusPointMinus is NOT multiplied. 
--                Solves problem on hotels where BonusPointMinus has not been setup for the "Bonus" ware.
-- SG 20200714: Added expired bonus points (BonusPointSystem2019)
-- SG 20190829: Changed spended points
-- SG 20190819: Spended bonus point in Bonus Point 2019 calculated separately
-- SG 20190801: Added rounding methods, changed @BonusRatio and @BonusRatioSpend to decimal. Don't use dbo.GetTurnOverToBonusPointRatio()
-- SG 20190725: Added new BonusPoint-system
-- SG 20190624: Manuel points also in BonusPointsTotal
-- OF 20110323: BonusEarned, BonusSpend and BonusTotal extracted from this sp.
-- OF 20110307: Cancelled customers exluded from report.
-- OF 20101101: Manual bonus point will return 0 as pieces. To distinguish between manual and folio points later in reports.
-- OF 20101028: Removal of BonusPoint expired date autoincrement. Nightaudit will take care of the incrementation.
-- OF 20101027: BonusPoint expired date autoincrement added.
-- OF 20101020: Distinct removed.
-- OF 20101015: Implementation of expired bonus poins system.
-- OF 20091019: Bug: Transactions moved from folio to another was not returned when Transaction.DateStamp was the same. Transaction.OriginalDate introduced to distinguish the transactions.
-- KJ 20090331: ReservationStatus added
-- KJ 20080228: Bug: Fix for "Can not insert the value NULL into column SegmentNumber"
-- TL 20070525: Added DISTINCT to avoid duplicate transactions
-- TL 20070523: Fix problem when NULL in Customer.Firstname results in NULL in CustomerName
-- TL 20070523: Created

DECLARE @UseBonusPointsExpired bit = 0     --Old bonuspointsystem
DECLARE @BonusPointsExpiredDate char(8) --Old bonuspointsystem
DECLARE @SystemDateStr char(8)
DECLARE @UseBonusPointSystem2019 int = 0

SELECT @SystemDateStr = SYSTEMDATE FROM System WITH (NOLOCK)  WHERE NUMBER = 1

SELECT @UseBonusPointsExpired	= CONVERT(bit, dbo.GetHotelSetupKeyValue ('UseBonusPointsExpired', -1))
SELECT @BonusPointsExpiredDate	= dbo.GetHotelSetupKeyValue ('BonusPointsExpiredDate', -1)
SELECT @UseBonusPointSystem2019 = Convert(int, IsNull(KeyValue, '0')) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'UseBonusPointSystem2019' --0: Original system, 1: System2019

if @UseBonusPointSystem2019 = 0
Begin
  if (@UseBonusPointsExpired = 1 and @IncludeExpiredBonusPoints = 0)
  begin
	if (@ADateFrom < @BonusPointsExpiredDate)
		Set @ADateFrom = @BonusPointsExpiredDate
  end
End

  DECLARE 
    @BonusDate datetime,
    @BonusCustNo int,
    @BonusCustName varchar(255),
    @BonusFolioNo int,
    @BonusRefNo int,
    @BonusGuestNo int,
    @BonusRoomNo int,
    @BonusWareName varchar(255),
    @BonusPieces int, 
    @BonusPrice money,
    @BonusTotalPrice money,
    @BonusSumBonus money,
    @BonusHotelCustNo int,
    @BonusSegment int

  DECLARE @DefaultOrderBookSegment int
  SET @DefaultOrderBookSegment = dbo.GetDefaultOrderBookSegment()

  DECLARE @BonusRatio decimal(7,3) = 1
  DECLARE @BonusRatioSpend decimal(7,3) = 1
  DECLARE @BPEarningRound varchar(10) = 'None';
  DECLARE @BPSpendRound varchar(10) ='None';;
  DECLARE @TempHotelSetup varchar(20);

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

  /*************************************************************************************************/
  /*                                                                                               */ 
  /* Find manual bonus points if any!                                                             */
  /*                                                                                               */ 
  /*************************************************************************************************/
  if @ManuelBonusWaresOnly = 0
  BEGIN
	  INSERT INTO @Result
		SELECT 
		  0,
		  BPM.DateStamp AS DateValue, 
		  BPM.CustomerNumber,
      CASE WHEN LTRIM(RTRIM(IsNull(C.FirstName, ''))) = '' THEN
        IsNull(C.LastName, '')
      ELSE
        LTRIM(RTRIM(IsNull(C.FirstName, '') + ' ' + IsNull(C.LastName, ''))) END AS CustomerName,
		  NULL AS FolioNumber,
		  NULL AS ReferenceNumber,
		  NULL AS GuestNumber,
		  NULL AS RoomNumber,
		  BPM.Text AS WareName,
		  0 AS Pieces,
		  0.0 AS Price,
          0.0 AS TotalPrice,
		  BPM.BonusPoints AS BonusEarned,
		  0 AS BonusSpend,
		  BPM.BonusPoints AS BonusTotal,
		  BPM.HotelCustomerNumber,
		  0 AS SegmentNumber,
		  BPM.DateStamp AS OriginalDate,
		  3 --manual
		FROM BonusPointsManual BPM WITH (NOLOCK) 
		INNER JOIN Customer C WITH (NOLOCK) ON BPM.CustomerNumber = C.CustomerNumber AND C.MarkAsDeleted <> 1
		WHERE (1=1)
		  AND ((DataLength(@ACustomerNumber) = 0) OR 
			   ((DataLength(@ACustomerNumber) <> 0) AND 
				BPM.CustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ACustomerNumber)))) 
		  AND ((DataLength(@AHotelCustomerNumbers) = 0) OR 
			   ((DataLength(@AHotelCustomerNumbers) <> 0) AND 
				 BPM.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumbers))))
		  AND ((@IncludeExpiredBonusPoints = 1) OR (((@ADateFrom = '') OR (CONVERT(char(8), BPM.DateStamp, 112) >= @ADateFrom)) AND ((@ADateTo = '') OR (CONVERT(char(8), BPM.DateStamp, 112) <= @ADateTo))))
   END
  /*************************************************************************************************/
  /*                                                                                               */
  /* Find transactions which generates bonus points                                                */
  /*                                                                                               */
  /*************************************************************************************************/
	;
	WITH FG
	AS
	(
		SELECT FolioGuest.FolioNumber, FolioGuest.ReferenceNumber, FolioGuest.GuestNumber, C.CustomerNumber, C.FIRSTNAME, C.LASTNAME, C.SegmentNumber
		FROM CustomerCardXTab CCX WITH (NOLOCK)
		INNER JOIN Customer C WITH (NOLOCK) ON CCX.CustomerNumber = C.CustomerNumber AND CCX.LogNumberDelete IS NULL
            AND ((DataLength(@ACustomerNumber) = 0) OR 
                 C.CustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_no_dummy(@ACustomerNumber))) 
            AND ((DataLength(@ASegment) = 0) OR 
                 C.SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_no_dummy(@ASegment) WHERE Number > 0))
			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 FG.CustomerNumber, FG.FIRSTNAME, FG.LASTNAME, FG.SegmentNumber, FG.ReferenceNumber, FG.GuestNumber FolioGuestNumber, T.*
		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
		UNION
		SELECT FG.CustomerNumber, FG.FIRSTNAME, FG.LASTNAME, FG.SegmentNumber, FG.ReferenceNumber, FG.GuestNumber FolioGuestNumber , T.*
		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
	)
  INSERT INTO @Result
    select TransactionNumber, DateStamp, CustomerNumber, CustomerName, FolioNumber, ReferenceNumber, GuestNumber, RoomNumber, WareName, 
	CASE WHEN BonusSpend > 0 THEN 0 ELSE Pieces END as Pieces, 
	Price, TotalPrice, 
	  CASE 
	       WHEN @BPEarningRound = 'Normal' THEN Round(BonusEarned, 0)
	       WHEN @BPEarningRound = 'Floor' THEN Floor(BonusEarned)
	       WHEN @BPEarningRound = 'Ceiling' THEN Ceiling(BonusEarned) 
		   ELSE BonusEarned
	  END as BonusEarned, 
	  CASE 
	       WHEN @BPSpendRound = 'Normal' THEN Round(BonusSpend, 0)
	       WHEN @BPSpendRound = 'Floor' THEN Floor(BonusSpend)
	       WHEN @BPSpendRound = 'Ceiling' THEN Ceiling(BonusSpend) 
		   ELSE BonusSpend
	  END as BonusSpend, 
	  CASE 
	       WHEN @BPEarningRound = 'Normal' THEN Round(BonusEarned, 0)
	       WHEN @BPEarningRound = 'Floor' THEN Floor(BonusEarned)
	       WHEN @BPEarningRound = 'Ceiling' THEN Ceiling(BonusEarned) 
		   ELSE BonusEarned
	  END - 
	  CASE 
	       WHEN @BPSpendRound = 'Normal' THEN Round(BonusSpend, 0)
	       WHEN @BPSpendRound = 'Floor' THEN Floor(BonusSpend)
	       WHEN @BPSpendRound = 'Ceiling' THEN Ceiling(BonusSpend) 
		   ELSE BonusSpend
	  END as BonusTotal,
	  HotelCustomerNumber, SegmentNumber, OriginalDate,
	  CASE WHEN BonusSpend > 0 THEN 2 ELSE 1 END as PointType
	FROM
	(SELECT DISTINCT
	  T.TransactionNumber,
      T.DateStamp,
      T.CustomerNumber,
      CASE WHEN LTRIM(RTRIM(IsNull(FirstName, ''))) = '' THEN
        IsNull(LastName, '')
      ELSE
        LTRIM(RTRIM(IsNull(FirstName, '') + ' ' + IsNull(LastName, ''))) END AS CustomerName,
      T.FolioNumber,
      ReferenceNumber,
      CASE WHEN ReferenceNumber IS NULL THEN NULL ELSE FolioGuestNumber END AS GuestNumber,
      CASE IsNumeric(T.Note) WHEN 0 THEN NULL ELSE CONVERT(int, T.Note) END AS RoomNumber,
      T.TextOverwrite AS WareName,
      CASE T.Debit WHEN 0 THEN -T.Pieces ELSE T.Pieces END AS Pieces,
      (T.PriceOverwrite / T.Pieces) AS Price,
      CASE T.Debit WHEN 0 THEN -T.PriceOverwrite * Ware.BonusPointsPlus ELSE T.PriceOverwrite * Ware.BonusPointsPlus END AS TotalPrice,
      
	  CASE @UseBonusPointSystem2019 WHEN 0 THEN  --Original system
	     CASE T.Debit WHEN 0 THEN -T.PriceOverwrite ELSE T.PriceOverwrite END 
	  ELSE                               -- system 2019
	     CASE T.Debit WHEN 0 THEN -T.PriceOverwrite * Ware.BonusPointsPlus / @BonusRatio
		 ELSE                      T.PriceOverwrite * Ware.BonusPointsPlus / @BonusRatio END 
      END
	  AS BonusEarned, 
      
	  CASE @UseBonusPointSystem2019 WHEN 0 THEN  --Original system
	    CASE WHEN ((Ware.BonusPointsPlus <> 0) OR (Ware.BonusPointsMinus <> 0)) AND T.PriceOverwrite = 0 THEN T.Pieces * (Ware.BonusPointsPlus - Ware.BonusPointsMinus)
	         ELSE 0 END	
	  ELSE -- System 2019
		 CASE WHEN t.Payment = 21 THEN CASE T.Debit WHEN 0 THEN -T.PriceOverwrite ELSE T.PriceOverwrite END / @BonusRatioSpend ELSE 0 END
	  END AS BonusSpend,      
	  T.HotelCustomerNumber,
      ISNULL(SegmentNumber, 0) as SegmentNumber, 
      T.OriginalDate

    FROM T
    
    INNER JOIN Ware WITH (NOLOCK) ON T.WareNumber = Ware.WareNumber
    WHERE
	  (@ManuelBonusWaresOnly = 0) OR ((@ManuelBonusWaresOnly = 1) AND ((Ware.BonusPointsPlus <> 0) OR (Ware.BonusPointsMinus <> 0)))
      AND ((@ADateFrom = '') OR 
          (CONVERT(char(8), T.DateStamp, 112) >= @ADateFrom))
      AND ((@ADateTo = '') OR
          (CONVERT(char(8), T.DateStamp, 112) <= @ADateTo))
      AND T.ShowOnBillFlag = 1
      AND T.Payment in (0, 21)
      AND T.MovedDeleted = 0
      AND ((DataLength(@AHotelCustomerNumbers) = 0) OR 
           T.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_no_dummy(@AHotelCustomerNumbers)))
    ) x

--**************************************************************
-- Expired BonusPoint using BonusPointSystem2019
-- Calculates expired point per @ADateTo. If @ADateTo is empty, systemdate is used
--**************************************************************
    If @UseBonusPointSystem2019 = 1 and @IncludeExpiredBonusPoints = 1
	Begin
	  Declare @ExpiredBonusPoints Decimal(7,3)
	  Declare @CustomerNumberStr VARcHAR(10)
	  Declare @CustomerNumber int
	  Declare @CustomerName VarChar(255)
	  Declare @HotelNumber int
	  Declare @CalcDate char(8)
      If @ADateTo = ''
	  Begin
	    Set @CalcDate = @SystemDateStr
      End Else
	  Begin
	    Set @CalcDate = @ADateTo
      End
	  If Convert(varchar, @ACustomerNumber) <> '' --Specific customers
	  Begin
	    DECLARE CustomerCur CURSOR FAST_FORWARD READ_ONLY
        FOR Select value From STRING_SPLIT(Convert(varchar, @ACustomerNumber), ',')
      End Else
	  Begin
	    DECLARE CustomerCur CURSOR FAST_FORWARD READ_ONLY
        FOR --All customers having a bonus card
		  Select c.CustomerNumber FROM Customer c with (nolock)
		  INNER JOIN CustomerCardXTab CCX WITH (NOLOCK) ON CCX.CustomerNumber = C.CustomerNumber AND CCX.LogNumberDelete IS NULL
	  End
	  OPEN CustomerCur;
      FETCH NEXT FROM CustomerCur into @CustomerNumberStr
	  WHILE(@@FETCH_STATUS <> -1)
      Begin
	    Set @CustomerNumber = Convert(Int, @CustomerNumberStr)
		Select @ExpiredBonusPoints = dbo.BonusPointExpired(@CustomerNumber, @ADateTo) 
		Select @CustomerName = CASE WHEN LTRIM(RTRIM(IsNull(FirstName, ''))) = '' THEN IsNull(LastName, '')
                                    ELSE LTRIM(RTRIM(IsNull(FirstName, '') + ' ' + IsNull(LastName, ''))) END,
               @HotelNumber = HotelNumber
		FROM Customer Where CustomerNumber = @CustomerNumber
		If @ExpiredBonusPoints > 0.0
		Begin
          INSERT INTO @Result Values (
	      0,               --TransactionNumber int,
          @CalcDate,       --DateStamp datetime NOT NULL,
          @CustomerNumber, --CustomerNumber int NOT NULL,
          @CustomerName,   --CustomerName varchar(255) NOT NULL,
          NULL,            --FolioNumber int,
          NULL,            --ReferenceNumber int,
          NULL,            --GuestNumber int,
          NULL,            --RoomNumber int,
          'Expired bonus points',   --WareName varchar(255),
          0,               --Pieces int not null default 1.0,
          0,               --Price money not null default 0.0,
          0,               --TotalPrice money not null default 0.0,
          @ExpiredBonusPoints * -1, --BonusPointsEarned money not null,
          0,               --BonusPointsSpent money not null,
          @ExpiredBonusPoints * -1, --BonusPointsTotal money not null,
          IsNull((SELECT DISTINCT CustomerNumber FROM dbo.HotelCustomerNumbersFromHotelNumbers(@HotelNumber)), 0), --HotelCustomerNumber int not null,
          0,               --SegmentNumber int not null,
          @CalcDate,      --OriginalDate datetime NOT NULL
		  4)
        End

        FETCH NEXT FROM CustomerCur into @CustomerNumberStr
	  End

	  CLOSE CustomerCur;
      DEALLOCATE CustomerCur;
	End

  RETURN
END

