CREATE PROCEDURE dbo.RecurringPatternWareAvailable @AWareNumber INT, @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AAvailable INT OUTPUT, @AArrangementlineID INT = -1 AS
SET NOCOUNT ON
--AV 2008 0701

/*Last Changed:
  RN 20210212: CR204839, Added DISTINCT ArrangementLineNumber
  KJ 20201208: Error in get number of sold.
  KJ 20201201: Total rewrite to handle custom period on Arr lines
  RN 2017 0908 Added ArrangementlineID as parameter and using it on SQL.
  AV 2008 0722 Return number of available units insted of true false
*/

DECLARE @PresetEnd INT
DECLARE @PresetStart INT
DECLARE @DayStarts VARCHAR(5)
SELECT @DayStarts = ISNULL(KeyValue, '07:00') FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Arr24Hoursstarts'

SET @PresetStart = 0
IF CONVERT(VARCHAR(5), @AStartDateTime, 108) < @DayStarts
  SET @PresetStart = 1
   
SET @PresetEnd = 0
IF CONVERT(VARCHAR(5), @AEndDateTime, 108) < @DayStarts
  SET @PresetEnd = 1
 
SELECT DISTINCT ArrangementLines.ArrangementLineNumber, DBO.GetPicassoDateTimeAsRealDateTime(ArrangementLines.Date, ArrangementLines.Time) AS StartDate,
DATEADD(millisecond, -3, DBO.GetPicassoDateTimeAsRealDateTime(ArrangementLines.Date, ArrangementLines.DepartureTime)) AS EndDate,
Pieces
INTO #Arrangement
FROM ArrangementReservationsXtab WITH (NOLOCK)
LEFT OUTER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ArrangementReservationsXtab.ReferenceNumber
  AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
LEFT OUTER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
WHERE LEN(ArrangementLines.DepartureTime) = 5 
AND LEN(ArrangementLines.Time) = 5
AND DBO.GetPicassoDateTimeAsRealDateTime(ArrangementLines.Date, ArrangementLines.Time) <= DATEADD(DAY, @PresetEnd, @AEndDateTime)
AND DBO.GetPicassoDateTimeAsRealDateTime(ArrangementLines.Date, ArrangementLines.DepartureTime) >= DATEADD(DAY, @PresetStart, @AStartDateTime)
AND Status < 7
AND ArrangementLines.ArrangementLinenumber <> @AArrangementlineID
AND ArrangementLines.WareNumber = @AWareNumber

SELECT RecurringPatternSpecified.StartDateTime, DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) AS EndDateTime, ISNULL(CONVERT(INT, RecurringRule.RuleValue), 0) AS ForSale
INTO #Recurring
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
WHERE RecurringPatternWareXTab.FK_WareNumber = @AWareNumber
AND RecurringPatternSpecified.StartDateTime <= DATEADD(DAY, @PresetEnd, @AEndDateTime) 
AND RecurringPatternSpecified.EndDateTime > DATEADD(DAY, @PresetStart, @AStartDateTime) 
AND RecurringRule.RuleType = 4 

SELECT TOP 1 
@AAvailable = (
  #Recurring.ForSale - 
  ISNULL((
    SELECT SUM(Pieces)
    FROM #Arrangement
    WHERE #Arrangement.StartDate <= #Recurring.EndDateTime
    AND  #Arrangement.EndDate >= #Recurring.StartDateTime
  ), 0)
)
FROM #Recurring
ORDER BY (
  #Recurring.ForSale - 
  ISNULL((
    SELECT SUM(Pieces)
    FROM #Arrangement
    WHERE #Arrangement.StartDate <= #Recurring.EndDateTime
    AND  #Arrangement.EndDate >= #Recurring.StartDateTime
  ), 0)
) ASC

DROP TABLE #Arrangement
DROP TABLE #Recurring