CREATE PROCEDURE [dbo].[GA_Insale] @TheHotelNumber INT AS
SET NOCOUNT ON

/* Declare Variables */
DECLARE 
@DateStamp CHAR(8),
@HotelNumber INT,
@SoldRooms SMALLINT,
@Adults SMALLINT,
@Children1 SMALLINT,
@Children2 SMALLINT,
@SoldPrice MONEY,
@TotalRoomsAllotment SMALLINT,
@IncludeAllotmentInInsale BIT

/* Fill Variables */
SELECT @IncludeAllotmentInInsale=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('IncludeAllotmentInInsale')
IF IsNull (@IncludeAllotmentInInsale, 0) = 0
  SET @IncludeAllotmentInInsale = 0

IF @TheHotelNumber < 1 -- only drop table when localhotel or all hotels
BEGIN
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[RoomBookingStatusInsale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[RoomBookingStatusInsale]

  CREATE TABLE [dbo].[RoomBookingStatusInsale] (
	[DateStamp] [char] (8) COLLATE DATABASE_DEFAULT NOT NULL ,
	[HotelNumber] [int] NOT NULL,
	[SoldRooms] [smallint] NULL ,
	[SoldPrice] [MONEY] NULL
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[RoomBookingStatusInsale] WITH NOCHECK ADD 
	CONSTRAINT [PK_RoomBookingStatusInsale] PRIMARY KEY  CLUSTERED 
	(
		[DateStamp],
		[HotelNumber]
	)  ON [PRIMARY] 
END
ELSE
  DELETE FROM RoomBookingStatusInsale WHERE HotelNumber=@TheHotelNumber

IF @TheHotelNumber > -1
BEGIN
  DECLARE #StatusCopyCur CURSOR FAST_FORWARD FOR

  SELECT DateStamp, IsNull(HotelNumber, 0),SoldRooms,Adults,Children1,Children2,SoldPrice,
                 TotalRoomsAllotment FROM #RoomBookingStatus WITH (NOLOCK)
  WHERE HotelNumber = @TheHotelNumber OPTION (KEEP PLAN)
END
ELSE
BEGIN
  DECLARE #StatusCopyCur CURSOR FAST_FORWARD FOR

  SELECT DateStamp, IsNull(HotelNumber, 0),SoldRooms,Adults,Children1,Children2,SoldPrice,
                 TotalRoomsAllotment FROM #RoomBookingStatus WITH (NOLOCK) OPTION (KEEP PLAN)
END

OPEN #StatusCopyCur
FETCH NEXT FROM #StatusCopyCur
INTO @DateStamp, @HotelNumber, @SoldRooms, @Adults, @Children1, @Children2, @SoldPrice, @TotalRoomsAllotment

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  IF @IncludeAllotmentInInsale = 0
    SET @TotalRoomsAllotment = 0

  IF (@SoldRooms+@TotalRoomsAllotment > 0) OR (@SoldPrice > 0)  -- no need to do anything if no number
  BEGIN
    UPDATE RoomBookingStatusInsale SET SoldRooms=SoldRooms+@SoldRooms+@TotalRoomsAllotment, SoldPrice=SoldPrice+@SoldPrice WHERE DateStamp=@DateStamp AND HotelNumber=@HotelNumber
    IF @@rowcount = 0
      INSERT INTO RoomBookingStatusInsale (DateStamp,HotelNumber,SoldRooms,SoldPrice) VALUES (@DateStamp, @HotelNumber, @SoldRooms+@TotalRoomsAllotment,@SoldPrice)
  END
	
  FETCH NEXT FROM #StatusCopyCur
  INTO @DateStamp, @HotelNumber, @SoldRooms, @Adults, @Children1, @Children2, @SoldPrice, @TotalRoomsAllotment
END

CLOSE #StatusCopyCur
DEALLOCATE #StatusCopyCur

/* Fixes problem in product search */
UPDATE Products SET MinimumDays=0 WHERE MinimumDays IS NULL
UPDATE Products SET MaximumDays=0 WHERE MaximumDays IS NULL









