CREATE PROCEDURE dbo.RecurringPatternGetWareUpdate @AStartDate DATETIME, @AEndDate DATETIME AS
/*
  Created by Kent Johansen, May 2017
  Calc the data for RecurringPatternGetWare sp. Casche the data to optimize the online booking

  Changes:
    Bug: Fix for "Column name or number of supplied values does not match table definition"
*/
SET NOCOUNT ON

CREATE TABLE #tmp
(
  WareNumber INT, 
  WareName VARCHAR(50),
  Price MONEY, 
  DefaultArrangementTime DATETIME, 
  MaxPieces INT, 
  DeliveryType TINYINT, 
  DeliveryChargeType TINYINT, 
  DeliveryChargeTypeText VARCHAR(200), 
  Duration INT, 
  Note VARCHAR(8000), 
  Sortnumber INT,
  tmp TINYINT,
  IsPlaceTextNeeded BIT,
  PlaceTextDescription VARCHAR(MAX)
)

DECLARE @WareNumber INT
DECLARE @AWareNumberTmp INT
SELECT @AWareNumberTmp = OnlineBookTypeNumber FROM OnlineBookType WITH (NOLOCK) WHERE Number = 29 -- 29 er en fast type (warewithrules)

INSERT INTO #tmp
  EXEC OnlineFindWares @AHotelNumber = -1, @AWareNumber = @AWareNumberTmp, @ALangSameAsPicasso = 1, @ACountryNumber = 1

SELECT db.WareNumber, db.StartDateTime, db.EndDateTime, MIN(db.Available) AS Available
INTO #RecurringWaretable
FROM
(
  SELECT Ware.WareNumber, Ware.WareName, RecurringPatternSpecified.StartDateTime, DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) AS EndDateTime, RecurringRule.RuleValue, 
  RecurringRule.RuleValue- 
  ISNULL((
    SELECT SUM(ISNULL(ArrangementLines.Pieces, 0))
    FROM ArrangementReservationsXtab WITH (NOLOCK) 
    INNER JOIN Reservations WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
      AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
      AND ArrangementLines.WareNumber = Ware.WareNumber
    WHERE Status < 6 
    AND (CONVERT(DATETIME, ArrangementLines.Date, 112) + CONVERT(DATETIME, ArrangementLines.Time, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) OR
    CONVERT(DATETIME, ArrangementLines.Date, 112) + CONVERT(DATETIME, ArrangementLines.DepartureTime, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime))
  ), 0) AS Available
  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
    AND RecurringRule.RuleType = 4 -- Number for sale
  INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = RecurringPatternSpecified.FK_RecurringPatternID
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = RecurringPatternWareXTab.FK_WareNumber
  WHERE RecurringPatternWareXTab.FK_WareNumber IN (SELECT DISTINCT WareNumber FROM #tmp) 
  AND RecurringPatternSpecified.StartDateTime BETWEEN @AStartDate AND @AEndDate
  AND ISNULL(RecurringPatternSpecified.Deleted, 0) = 0

  UNION

  SELECT Ware.WareNumber, Ware.WareName, RecurringPatternSpecified.StartDateTime, DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) AS EndDateTime, RecurringRule.RuleValue, 
  RecurringRule.RuleValue- 
  ISNULL((
    SELECT SUM(ISNULL(ArrangementLines.Pieces, 0))
    FROM ArrangementReservationsXtab WITH (NOLOCK) 
    INNER JOIN Reservations WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
      AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
    INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
    INNER JOIN Ware w1 WITH (NOLOCK) ON w1.WareLinkNumber = WareLinkGroup.Id
	  AND w1.WareNumber = ArrangementLines.WareNumber
    WHERE Status < 6 
    AND (CONVERT(DATETIME, ArrangementLines.Date, 112) + CONVERT(DATETIME, ArrangementLines.Time, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) OR
    CONVERT(DATETIME, ArrangementLines.Date, 112) + CONVERT(DATETIME, ArrangementLines.DepartureTime, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime))
  ), 0) AS Available
  FROM RecurringPatternSpecified WITH (NOLOCK) 
  INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.FK_RecurringPatternId = RecurringPatternSpecified.FK_RecurringPatternId
  INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
    AND RecurringRule.RuleType = 4 -- Number for sale
  INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = RecurringPatternSpecified.FK_RecurringPatternID
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
  WHERE 1=1
  AND Ware.WareNumber IN (SELECT DISTINCT WareNumber FROM #tmp) 
  AND RecurringPatternSpecified.StartDateTime BETWEEN @AStartDate AND @AEndDate
  AND ISNULL(RecurringPatternSpecified.Deleted, 0) = 0
) AS db
GROUP BY db.WareNumber, db.WareName, db.StartDateTime, db.EndDateTime

DELETE RecurringPatternWareCache

INSERT INTO RecurringPatternWareCache
SELECT *
FROM #RecurringWaretable

DROP TABLE #RecurringWaretable
DROP TABLE #tmp