CREATE PROCEDURE dbo.RecurringPatternReturnOptionalWares @AStartDateTime DATETIME, @AEndDateTime DATETIME, @ACountryNumber INT = 0, @AHotelNumber INT = -1, @ARoomTypeNumber INT = 0 AS

--Created AV 2008 0916
--Used Online for Feriebureau Bornholm

/*
Last updated:
  KJ/TB 20090930: Better handle of @ARoomTypeNumber
  TB 20090928 - Added @ARoomTypeNumber
  TB 20090929 - Added CONVERT to Dates
  AV 2008 0923 Price added to result set  

*/


  CREATE TABLE #ResultSet (
		WareNumber INT,
                          WareName VARCHAR(50),
                          WareNote VARCHAR(500), 
                          TranslatedWareName VARCHAR (50),  
                          TranslatedWareNote VARCHAR(500),
                          Price MONEY,
		ADateTime DATETIME,
		)


  DECLARE @CurrentDate DATETIME
  DECLARE @WareNumber INT
  DECLARE @FK_RecurringPatternID INT
  DECLARE @WareName VARCHAR(50)
  DECLARE @WareNote VARCHAR(500)
  DECLARE @TranslatedWareName VARCHAR(50)
  DECLARE @TranslatedWareNote VARCHAR(500)

  DECLARE @HotelCountryNumber INT
  DECLARE @WarePrice MONEY
  DECLARE @PatternPrice MONEY



  SELECT @HotelCountryNumber = CONVERT(INT,KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CountryCode'

  SET @CurrentDate = @AStartDateTime

  WHILE CONVERT(VARCHAR(12), @CurrentDate) <= CONVERT(VARCHAR(12), @AEndDateTime)
  BEGIN

      DECLARE UpdateCur CURSOR LOCAL FAST_FORWARD FOR
      SELECT DISTINCT  RecurringPatternWareXTab.FK_RecurringPatternID, RecurringPatternWareXTab.FK_WareNumber, Ware.WareName, CONVERT(VARCHAR(500),Ware.Note), Ware.Price, Translation.Name AS TranslatedWareName, Translation.Description1 AS TranslatedWareNote  FROM RecurringPatternSpecified WITH (NOLOCK) 
      INNER JOIN RecurringPatternWareXTab WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternID = RecurringPatternSpecified.FK_RecurringPatternID
      INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
      INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = RecurringPatternWareXTab.FK_WareNumber
      LEFT OUTER JOIN Translation ON Translation.ObjectNumber = Ware.WareNumber AND Translation.Type = 1 AND Translation.CountryNumber = @ACountryNumber
      LEFT OUTER JOIN WareRoomTypeXTab WITH (NOLOCK) ON WareRoomTypeXTab.FK_Warenumber = RecurringPatternWareXTab.FK_WareNumber
      WHERE 
      (
       (CONVERT(VARCHAR(12), @CurrentDate) = CONVERT(VARCHAR(12), @AStartDateTime) AND RecurringPatternSpecified.StartDateTime <= @AStartDateTime AND RecurringPatternSpecified.EndDateTime >= @AStartDateTime AND RecurringRule.RuleType = 5)     --Arrival Day
       OR (CONVERT(VARCHAR(12), @CurrentDate) = CONVERT(VARCHAR(12), @AEndDateTime) AND RecurringPatternSpecified.StartDateTime <= @AEndDateTime AND RecurringPatternSpecified.EndDateTime >= @AEndDateTime AND RecurringRule.RuleType = 7) -- Departure Day
       OR (RecurringPatternSpecified.StartDateTime <= @CurrentDate AND RecurringPatternSpecified.EndDateTime >= @CurrentDate AND RecurringRule.RuleType = 6 and @CurrentDate <@AEndDateTime) --Stay Day
      )
      AND RecurringPatternWareXTab.FK_RecurringPatternID IN (SELECT FK_RecurringPatternID FROM RecurringRule WITH (NOLOCK) WHERE RuleType IN  (17))  -- Only take the optional wares
      AND ((WareRoomTypeXTab.FK_RoomTypeNumber IS NULL) or ((ISNULL(@ARoomTypeNumber, 0) = 0) OR ((ISNULL(@ARoomTypeNumber, 0) > 0) AND WareRoomTypeXTab.FK_RoomTypeNumber = @ARoomTypeNumber)))

    OPEN UpdateCur
  
    FETCH NEXT FROM UpdateCur INTO @FK_RecurringPatternID, @WareNumber, @WareName, @WareNote, @WarePrice, @TranslatedWareName, @TranslatedWareNote
 
    WHILE(@@Fetch_status=0)
    BEGIN
        SET @PatternPrice = 0 
        IF @ACountryNumber = @HotelCountryNumber OR @ACountryNumber = 0
        BEGIN
          SET @TranslatedWareName = @WareName
          SET @TranslatedWareNote = @WareNote 
        END
        SELECT @PatternPrice = CONVERT(MONEY, DBO.RecurringPatternGetWareRuleValue(@WareNumber, @CurrentDate, 1))  -- 1 is ware price
        IF ISNULL(@PatternPrice, 0) <> 0 
          SET @WarePrice = @PatternPrice
        INSERT INTO #ResultSet (WareNumber, WareName, WareNote, TranslatedWareName, TranslatedWareNote, Price, ADateTime) VALUES (@WareNumber, @WareName, @WareNote, @TranslatedWareName, @TranslatedWareNote, @WarePrice, @CurrentDate)
        FETCH NEXT FROM UpdateCur INTO @FK_RecurringPatternID, @WareNumber, @WareName, @WareNote, @WarePrice, @TranslatedWareName, @TranslatedWareNote
    END  

    CLOSE UpdateCur    DEALLOCATE UpdateCur

    SET @CurrentDate = @CurrentDate + 1
  END

/*
  -- Check Optional wares from advanced NOT Recurring pattern
  IF @ARoomTypeNumber > 0
  BEGIN

    DELETE FROM #ResultSet 
    WHERE warenumber NOT IN (
    select warenumber from ware (nolock) 
    inner join wareroomtypextab (nolock) on ware.warenumber = wareroomtypextab.FK_Warenumber
    inner join roomtypes (nolock) ON wareroomtypextab.FK_Roomtypenumber = roomtypes.roomtypenumber	
    where roomtypes.Roomtypenumber = @ARoomTypeNumber) 
    
  END
*/

  SELECT * FROM #ResultSet WITH (NOLOCK) ORDER BY WareNumber, ADateTime
GO
