CREATE PROCEDURE dbo.AllotmentCurrentCount @AllotmentNumber INT, @StartDate DATETIME, @EndDate DATETIME, @SpecificRoomTypeNumber INT = -1, @OverBookCheck TINYINT = 0, @GroupByDate BIT = 0, @AHotelNumberStr VARCHAR(200) = '', @AExcludeWithoutCRSConf BIT = 0 , @AWantedRooms INT = 0 AS
SET NOCOUNT ON
/*
  Created by Jacob Pedersen 2005 - Purpose: Removing redundant data from database, solving lock conflict in bookingstatus

  Last Changed:
  KJ 20140618: New: Only count reservations with a CRS Confirmation Number. TW BR 2.5.1.1
  BA20101203 CR018235/SmallDateTime is changed to DateTime due to the following error:
             "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
             Don't know why this happens. See CR018235 for further details.
  KJ 20070719: Added HotelNumberStr used together with: @AllotmentNumber = -1 means all all allotments (Only if @SpecificRoomTypeNumber = -1 and @OverBookCheck = 0) To get total allotment count for selected hotel
  KJ 20070717: Added GroupBy param.
  JP20070629 EXT: use WITH ROWLOCK for inserts in AllotmentLines
  JP20060627 OPTIMIZE: prefixed tables with .dbo
  JP20051229 BUG: removed debug info as field was too small in some cases
  JP20050919 BUG: AV's 20050811 fix did not take subreservations with matching periods but different rooms into account. Fixed by using LineNumber also
  JP20050909 BUG: allocation of roomtype where no allotment is set gives problems
  2005-08-11 AV: Distinct inserted to sql statement. Needed to avoid duclicate rows when namelist per person and more guests on same room

  Description: Returns currently booked rooms of a given type for a given day against a given allotment

  @OverBookCheck: 	0 = Do not check for overbooking
			1 = Check for Overbooking on Type
			2 = Check for Overbooking on All Types

  Notes: Can be called with or without a RoomTypeNumber

  TODO: 	- create combi clustered primary key with a unique name
		- remove debug field when routine is stable

*/

SET @AExcludeWithoutCRSConf = ISNULL(@AExcludeWithoutCRSConf, 0)

CREATE TABLE #CurrentCounts (
	RoomTypeNumber INT,
	DateStamp DATETIME,
	CurrentCount SMALLINT
--,debug varchar(255)
/*
	CONSTRAINT [#PK_CURRENTCOUNTS] PRIMARY KEY  CLUSTERED 
	(
		[RoomTypeNumber],
		[DateStamp]
	) ON [PRIMARY]
*/

)


DECLARE
@LineNumber		INT,
@RoomTypeNumber	INT,
@ArrivalDate		DATETIME,
@DepartureDate	DATETIME,
@NumberOfRooms	SMALLINT,
@RoomNumber		INT,
@CurrentDate		DATETIME,
@MaxDate		DATETIME,
@Exists 		INT,
@Count			INT,
@FormattedStartDate VARCHAR(8),
@FormattedEndDate VARCHAR(8),
@FormattedEndDateMinusOne VARCHAR(8)

SET @FormattedStartDate = CONVERT(VARCHAR(8), @StartDate,112)
SET @FormattedEndDate = CONVERT(VARCHAR(8), @EndDate,112)
SET @FormattedEndDateMinusOne = CONVERT(VARCHAR(8), @EndDate - 1,112)

IF (@SpecificRoomTypeNumber = -1) OR (@OverBookCheck = 2)
BEGIN
  DECLARE #AllotmentCur CURSOR LOCAL FAST_FORWARD FOR

  SELECT DISTINCT RL.LineNumber, RL.BookedTypeNumber,CONVERT(DATETIME,RL.ArrivalDate,112),CONVERT(DATETIME,RL.DepartureDate,112),RL.NumberOfRooms,RL.RoomNumber 
  FROM dbo.ReservationRoomLine RL WITH (NOLOCK)
  INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RL.DepartureDate >@FormattedStartDate AND RL.ArrivalDate <= @FormattedEndDate
  INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status<=6
  INNER JOIN dbo.ReservationMain RM WITH (NOLOCK) ON RM.ReferenceNumber=R.ReferenceNumber AND ((@AllotmentNumber = -1 AND RM.AllotmentNumber >= 0) OR (RM.AllotmentNumber=@AllotmentNumber))
  INNER JOIN dbo.RoomTypes RT WITH (NOLOCK) ON RL.BookedTypeNumber = RT.RoomTypeNumber AND (@AHotelNumberStr  = '' OR rt.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr)))
  WHERE (@AExcludeWithoutCRSConf = 0 OR (dbo.GetReservationExternalReference(R.ReferenceNumber, R.GuestNumber, 'CONFIRMATION') <> ''))
      
END
ELSE
BEGIN
  DECLARE #AllotmentCur CURSOR LOCAL FAST_FORWARD FOR

  SELECT DISTINCT RL.LineNumber, RL.BookedTypeNumber,CONVERT(DATETIME,RL.ArrivalDate,112),CONVERT(DATETIME,RL.DepartureDate,112),RL.NumberOfRooms,RL.RoomNumber 
  FROM dbo.ReservationRoomLine RL WITH (NOLOCK)
  INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RL.DepartureDate > @FormattedStartDate AND RL.ArrivalDate <= @FormattedEndDate
  INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber=RLX.ReferenceNumber AND R.GuestNumber=RLX.GuestNumber AND R.Status<=6
  INNER JOIN dbo.ReservationMain RM WITH (NOLOCK) ON RM.ReferenceNumber=R.ReferenceNumber AND RM.AllotmentNumber=@AllotmentNumber
  LEFT OUTER JOIN dbo.Rooms WITH (NOLOCK) ON Rooms.RoomNumber=RL.RoomNumber
  WHERE (RL.RoomNumber IS NULL AND RL.BookedTypeNumber=@SpecificRoomTypeNumber) OR (RL.RoomNumber>0 AND Rooms.RoomTypeNumber=@SpecificRoomTypeNumber)
  AND (@AExcludeWithoutCRSConf = 0 OR (dbo.GetReservationExternalReference(R.ReferenceNumber, R.GuestNumber, 'CONFIRMATION') <> ''))
END

OPEN #AllotmentCur

FETCH NEXT FROM #AllotmentCur
INTO @LineNumber, @RoomTypeNumber,@ArrivalDate, @DepartureDate, @NumberOfRooms, @RoomNumber

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF @ArrivalDate < @StartDate  -- highest value
    SET @CurrentDate = @StartDate
  ELSE
    SET @CurrentDate = @ArrivalDate

  IF @DepartureDate > @EndDate  -- lowest value
    SET @MaxDate = @EndDate
  ELSE
    SET @MaxDate = @DepartureDate - 1

  IF @RoomNumber > 0  -- use allocatedtype
    SELECT @RoomTypeNumber = RoomTypeNumber FROM dbo.Rooms WITH (NOLOCK) WHERE RoomNumber = @RoomNumber

  WHILE @CurrentDate <= @MaxDate
  BEGIN
    UPDATE #CurrentCounts
      SET CurrentCount = CurrentCount + @NumberOfRooms
--,debug=debug + '+'+convert(varchar,@LineNumber)
    WHERE RoomTypeNumber = @RoomTypeNumber AND DateStamp = @CurrentDate
    IF @@rowcount = 0
--      INSERT INTO #CurrentCounts (RoomTypeNumber, DateStamp, CurrentCount, debug)
--      VALUES (@RoomTypeNumber, @CurrentDate, @NumberOfRooms, @LineNumber)
      INSERT INTO #CurrentCounts (RoomTypeNumber, DateStamp, CurrentCount)
      VALUES (@RoomTypeNumber, @CurrentDate, @NumberOfRooms)

    IF @OverBookCheck=2 -- we need to insert rows in allotmentlines where I+T is 0 because we have currentcount>0
    BEGIN
      SELECT @Exists=COUNT(*) FROM dbo.AllotmentLines WITH (NOLOCK) WHERE AllotmentNumber=@AllotmentNumber AND RoomTypeNumber=@RoomTypeNumber AND DateStamp=CONVERT(CHAR(8), @CurrentDate, 112)
      IF @Exists=0
        INSERT INTO dbo.AllotmentLines WITH (ROWLOCK) (AllotmentNumber, RoomTypeNumber, DateStamp, InitialCount, TotalChange) 
		VALUES (@AllotmentNumber, @RoomTypeNumber, CONVERT(CHAR(8), @CurrentDate, 112), 0, 0)
    END

    SET @CurrentDate = @CurrentDate + 1
  END

  FETCH NEXT FROM #AllotmentCur
  INTO @LineNumber, @RoomTypeNumber,@ArrivalDate, @DepartureDate, @NumberOfRooms, @RoomNumber
END

IF @OverBookCheck = 0
BEGIN
  IF @GroupByDate = 1
    SELECT DateStamp, SUM(CurrentCount) AS CurrentCount 
    FROM #CurrentCounts 
    GROUP BY DateStamp 
    ORDER BY DateStamp
  ELSE
    SELECT * FROM #CurrentCounts
END
ELSE IF @OverBookCheck = 1  -- Return a number if there are any days where I+T-C is less than 0 for a specific type = Overbook=1
BEGIN
  SELECT @Count = COUNT(*) FROM #CurrentCounts 
  RIGHT OUTER JOIN dbo.AllotmentLines AL WITH (NOLOCK) ON AL.AllotmentNumber=@AllotmentNumber AND AL.RoomTypeNumber=#CurrentCounts.RoomTypeNumber AND CONVERT(DATETIME,AL.DateStamp,112)=#CurrentCounts.DateStamp		

  IF @Count = 0
	SELECT @AWantedRooms
  ELSE
	SELECT COUNT(*) FROM #CurrentCounts 
	RIGHT OUTER JOIN dbo.AllotmentLines AL WITH (NOLOCK) ON AL.RoomTypeNumber=#CurrentCounts.RoomTypeNumber AND AL.DateStamp=#CurrentCounts.DateStamp
	WHERE AL.AllotmentNumber=@AllotmentNumber AND
	(ISNULL(InitialCount,0) + ISNULL(TotalChange,0)) - (ISNULL(CurrentCount, 0) + @AWantedRooms) < 0 
	AND AL.RoomTypeNumber=@SpecificRoomTypeNumber
	AND AL.DateStamp BETWEEN @FormattedStartDate AND @FormattedEndDateMinusOne
END
ELSE IF @OverBookCheck = 2  -- Return a number if there are any days where I+T-C is less than 0 = Overbook=2
BEGIN
--  SELECT AL.*, ISNULL(#CurrentCounts.CurrentCount, 0) FROM AllotmentLines AL (NOLOCK) LEFT OUTER JOIN #CurrentCounts ON AL.RoomTypeNumber=#CurrentCounts.RoomTypeNumber AND AL.DateStamp=#CurrentCounts.DateStamp
--  WHERE AL.AllotmentNumber=@AllotmentNumber AND CONVERT(SMALLDATETIME, AL.DateStamp, 112) BETWEEN @StartDate AND @EndDate - 1
  SELECT @Count = COUNT(*) FROM dbo.AllotmentLines AL WITH (NOLOCK) 
  LEFT OUTER JOIN #CurrentCounts ON AL.RoomTypeNumber=#CurrentCounts.RoomTypeNumber AND CONVERT(DATETIME,AL.DateStamp,112)=#CurrentCounts.DateStamp
  IF @Count = 0
    SELECT @AWantedRooms
  ELSE
    SELECT COUNT(*) FROM dbo.AllotmentLines AL WITH (NOLOCK) 
    LEFT OUTER JOIN #CurrentCounts ON AL.RoomTypeNumber=#CurrentCounts.RoomTypeNumber AND CONVERT(DATETIME,AL.DateStamp,112)=#CurrentCounts.DateStamp
    WHERE AllotmentNumber = @AllotmentNumber AND AL.DateStamp BETWEEN @FormattedStartDate AND @FormattedEndDateMinusOne
    GROUP BY AL.DateStamp
    HAVING (sum(InitialCount) + sum(TotalChange)) - (sum(ISNULL(CurrentCount, 0)) + @AWantedRooms) < 0
END

DROP TABLE #CurrentCounts -- not really needed

CLOSE #AllotmentCur
DEALLOCATE #AllotmentCur