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

  Last Changed:
  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

*/

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

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 CONVERT(DATETIME, RL.DepartureDate, 112)>@StartDate AND CONVERT(DATETIME, RL.ArrivalDate, 112) <= @EndDate
  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))
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 CONVERT(DATETIME, RL.DepartureDate, 112)>@StartDate AND CONVERT(DATETIME, RL.ArrivalDate, 112) <= @EndDate
  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)
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 
    INNER JOIN RoomTypes rt WITH (NOLOCK) ON rt.RoomTypeNumber = #CurrentCounts.RoomTypeNumber
    AND rt.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))
    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(*)/*#CurrentCounts.*, ISNULL(InitialCount,0) AS InitialCount, ISNULL(TotalChange,0) AS TotalChange*/ FROM #CurrentCounts LEFT OUTER JOIN dbo.AllotmentLines AL WITH (NOLOCK) ON AL.AllotmentNumber=@AllotmentNumber AND AL.RoomTypeNumber=#CurrentCounts.RoomTypeNumber AND AL.DateStamp=#CurrentCounts.DateStamp
  WHERE ISNULL(InitialCount,0) + ISNULL(TotalChange,0) - CurrentCount < 0 AND #CurrentCounts.RoomTypeNumber=@SpecificRoomTypeNumber
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(*)--AL.DateStamp,sum(InitialCount),sum(TotalChange),sum(CurrentCount)				-- small problem. returns one row per day, but still works but 1 row could be enough
  FROM dbo.AllotmentLines AL WITH (NOLOCK) LEFT OUTER JOIN #CurrentCounts ON AL.RoomTypeNumber=#CurrentCounts.RoomTypeNumber AND AL.DateStamp=#CurrentCounts.DateStamp
  WHERE AllotmentNumber = @AllotmentNumber AND CONVERT(DATETIME, AL.DateStamp, 112) BETWEEN @StartDate AND @EndDate - 1
  GROUP BY AL.DateStamp
  HAVING sum(InitialCount)+sum(TotalChange)-sum(CurrentCount) < 0
END

DROP TABLE #CurrentCounts -- not really needed

CLOSE #AllotmentCur
DEALLOCATE #AllotmentCur