CREATE PROCEDURE dbo.RecurringPatternProductWare @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AProductNumber INT, @ACountryNumber INT = 0, @ARoomTypeNumber INT = 0 AS
/*
  Created by Kent Johansen july 2015
  Returns list of recurring wares with rule type Arrival, stay or departure.
  Changes:
    KJ 20161123: Bug: Fix for "some times no wares is returned". Case #CR116558

*/

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

DECLARE @CurrentDate DATETIME
DECLARE @WareNumber INT
DECLARE @FK_RecurringPatternID INT
DECLARE @WareName VARCHAR(50)
DECLARE @WareNote VARCHAR(500)
DECLARE @WarePrice MONEY
DECLARE @WareGroupNumber INT

SET @CurrentDate = @AStartDateTime

WHILE CONVERT(VARCHAR(8), @CurrentDate, 112) <= CONVERT(VARCHAR(8), @AEndDateTime, 112)
BEGIN
  DECLARE UpdateCur CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT RecurringPatternWareXTab.FK_RecurringPatternID, RecurringPatternWareXTab.FK_WareNumber, Ware.Price,
    CASE WHEN ISNULL(Translation.Name, '') = '' THEN Ware.WareName ELSE Translation.Name END,
    CASE WHEN ISNULL(Translation.Description1, '') = '' THEN CONVERT(VARCHAR(500), Ware.Note) ELSE Translation.Description1 END
	, Ware.WareGroupNumber
    FROM RecurringPatternSpecified WITH (NOLOCK) 
    INNER JOIN RecurringPatternWareXTab WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternID = RecurringPatternSpecified.FK_RecurringPatternID
    INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = RecurringPatternSpecified.FK_RecurringPatternId
    INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
      AND RecurringRule.RuleType IN (5,6,7) -- Arrival, stay, departure
    INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = RecurringPatternWareXTab.FK_WareNumber
    INNER JOIN ProductRecurringPatternXtab WITH (NOLOCK) ON ProductRecurringPatternXtab.FK_RecurringPatternId = RecurringPattern.Id
      AND ProductRecurringPatternXtab.FK_ProductNumber = @AProductNumber
    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 AND @CurrentDate > @AStartDateTime) -- Stay Day
    )
    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, @WarePrice, @WareName, @WareNote, @WareGroupNumber
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    INSERT INTO #ResultSet (WareNumber, WareName, WareNote, WareGroupNumber, Price, ADateTime) VALUES 
      (@WareNumber, @WareName, @WareNote, @WareGroupNumber, @WarePrice, @CurrentDate)

    FETCH NEXT FROM UpdateCur INTO @FK_RecurringPatternID, @WareNumber, @WarePrice, @WareName, @WareNote, @WareGroupNumber
  END
  CLOSE UpdateCur
  DEALLOCATE UpdateCur

  SET @CurrentDate = @CurrentDate + 1
END

SELECT * FROM #ResultSet

DROP TABLE #ResultSet