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 (
  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) AS 
BEGIN 

-- OF 20110323: BonusEarned,BonusSpent 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
DECLARE @BonusPointsMonthBack int
DECLARE @BonusPointsExpiredDate char(8)
DECLARE @SystemDateStr char(8)

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

SELECT @UseBonusPointsExpired	= CONVERT(bit, dbo.GetHotelSetupKeyValue ('UseBonusPointsExpired', -1))
SELECT @BonusPointsExpiredDate	= dbo.GetHotelSetupKeyValue ('BonusPointsExpiredDate', -1)

if (@UseBonusPointsExpired = 1 and @IncludeExpiredBonusPoints = 0)
begin
	if (@ADateFrom < @BonusPointsExpiredDate)
		Set @ADateFrom = @BonusPointsExpiredDate
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 money
  SET @BonusRatio = dbo.GetTurnOverToBonusPointRatio()

  /*************************************************************************************************/
  /*                                                                                               */ 
  /* Find manual bonus points if any!                                                             */
  /*                                                                                               */ 
  /*************************************************************************************************/
  if @ManuelBonusWaresOnly = 0
  BEGIN
	  INSERT INTO @Result
		SELECT 
		  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 BonusSpent,
		  0 AS BonusTotal,
		  BPM.HotelCustomerNumber,
		  0 AS SegmentNumber,
		  0
		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                                                */
  /*                                                                                               */
  /*************************************************************************************************/
  INSERT INTO @Result
    SELECT DISTINCT
      T.DateStamp,
      C.CustomerNumber,
      CASE WHEN LTRIM(RTRIM(IsNull(C.FirstName, ''))) = '' THEN
        IsNull(C.LastName, '')
      ELSE
        LTRIM(RTRIM(IsNull(C.FirstName, '') + ' ' + IsNull(C.LastName, ''))) END AS CustomerName,
      T.FolioNumber,
      FolioGuest.ReferenceNumber,
      CASE WHEN FolioGuest.ReferenceNumber IS NULL THEN NULL ELSE FolioGuest.GuestNumber END AS GuestNumber,
      CASE IsNull(T.Note, 0) 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 ELSE T.PriceOverwrite END AS TotalPrice,
      CASE T.Debit WHEN 0 THEN -T.PriceOverwrite ELSE T.PriceOverwrite END AS BonusEarned, 
      CASE WHEN ((Ware.BonusPointsPlus <> 0) OR (Ware.BonusPointsMinus <> 0)) AND T.PriceOverwrite = 0 THEN T.Pieces * (Ware.BonusPointsPlus - Ware.BonusPointsMinus)
		ELSE 0 END AS BonusSpent,      
      CASE WHEN (Ware.BonusPointsPlus <> 0) OR (Ware.BonusPointsMinus <> 0) THEN T.Pieces * (Ware.BonusPointsPlus - Ware.BonusPointsMinus)
		ELSE (CASE T.Debit WHEN 0 THEN -T.PriceOverwrite ELSE T.PriceOverwrite END) * @BonusRatio END AS BonusTotal,
      T.HotelCustomerNumber,
      ISNULL(C.SegmentNumber, 0),
      T.OriginalDate

    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

    LEFT OUTER JOIN Folios FolioMain WITH (NOLOCK) ON  
      FCX.FolioNumber <> FolioMain.FolioNumber AND
      FolioMain.ReferenceNumber = FolioGuest.ReferenceNumber  AND 
      FolioMain.GuestNumber = 0
    LEFT 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 ((@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 = 0
      AND T.MovedDeleted = 0
      AND ((DataLength(@AHotelCustomerNumbers) = 0) OR 
           T.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_no_dummy(@AHotelCustomerNumbers)))
    INNER JOIN Ware WITH (NOLOCK) ON
      T.WareNumber = Ware.WareNumber
    INNER JOIN WareGroups WITH (NOLOCK) ON
      Ware.WareGroupNumber = WareGroups.WareGroupNumber
    WHERE
	  (@ManuelBonusWaresOnly = 0) OR ((@ManuelBonusWaresOnly = 1) AND ((Ware.BonusPointsPlus <> 0) OR (Ware.BonusPointsMinus <> 0)))
    ORDER BY 
      C.CustomerNumber,
      T.DateStamp

  RETURN
END