CREATE PROCEDURE DBO.AllotmentCheckForOverbooking @OLD_AllotmentNumber int, @RoomTypeNumber int, @StartDate datetime, @EndDate datetime, @Overbook tinyint OUTPUT, @WantedRooms INT = 0 AS
SET NOCOUNT ON
/*
    CREATED BY: Jacob Pedersen

    LAST UPDATE:
    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.
    JP20060627 OPTIMIZE: prefixed tables with dbo.
    JP20050909 BUG: since we don't have currentcount in allotmentlines anymore we cannot be sure we have any rows if moving res to a roomtype where no allotment exist. calling AllotmentCurrentCount takes this into account
    JP20050613 EXT: currentcount is now a function, plus fixed problem with @overbook=2 detection
    17/6-2002 Jacob Pedersen

    OUTPUT: @OverBook = 0 no overbooking
                                             1 overbooking on type
                                             2 overbooking on allotment total (all types)

    NOTE: This procedure has no problems with locking
*/

SET @OverBook = 0 -- default no overbooking

DECLARE
@Calc 			INT,
@SystemDateStr	Char (8),
@FormatedStartDate 	Char (8),
@FormatedEndDate 	Char (8)
SET @FormatedStartDate = Convert (Char (8), @StartDate, 112)
SET @FormatedEndDate = Convert (Char (8), @EndDate, 112)

-- Check if StartDate or EndDate before Systemdate -> change!
SELECT @SystemDateStr = SYSTEMDATE FROM dbo.System  WITH (NOLOCK) WHERE NUMBER = 1  -- Only one record

IF @FormatedStartDate < @SystemDateStr
  SET @FormatedStartDate = @SystemDateStr
IF @FormatedEndDate < @SystemDateStr
  SET @FormatedEndDate = @SystemDateStr

-- Check for total overbooking on allotment
CREATE TABLE #CALC(
		[Calc] [INT] NULL,
	)

INSERT INTO #Calc
EXEC dbo.AllotmentCurrentCount @OLD_AllotmentNumber, @StartDate, @EndDate, -1, 2, 0, '', 0 , @WantedRooms  /* Overbook all type */

SELECT @Calc=Calc FROM #Calc

SET @Calc = ISNULL(@Calc, 0)

IF @Calc > 0
  SET @OverBook = 2
ELSE IF @OverBook = 0 -- Check for overbooking of roomtype on Allotment
BEGIN
  DELETE FROM #Calc

  INSERT INTO #Calc
  EXEC dbo.AllotmentCurrentCount @OLD_AllotmentNumber, @StartDate, @EndDate, @RoomTypeNumber, 1, 0, '', 0 , @WantedRooms /* Overbook type */

  SELECT @Calc=Calc FROM #Calc

  SET @Calc = ISNULL(@Calc, 0)

  IF @Calc > 0
    SET @OverBook=1
END

DROP TABLE #Calc

