CREATE PROCEDURE [dbo].[GetColorRGBForPeriod] @AStartDate DateTime, @AEndDate DateTime, @AHotelNumber INT, @AColorType INT = 1 AS

/*
	TBU 20180219: SET @AHotelNumber = 0
	KC 20170919: BUF fix for wrong colors send to online
	KC 20170524: Rewritten so fit new color structure
	KC 20170518: Fix for wrong 'empty' color
	KC 20170516: Fix for missing online color
*/

SET NOCOUNT ON

DECLARE @SystemDate CHAR(8)
DECLARE @NumberOfRooms INT
DECLARE @InclAllotment BIT
DECLARE @OOSDecreaseVacantRooms BIT
DECLARE @OnlineUseRoomBookingStatusForCalendar BIT
DECLARE @DateStamp DateTime
DECLARE @VacantRooms INT
DECLARE @tmpVacantRooms INT
DECLARE @Red INT
DECLARE @Green INT
DECLARE @Blue INT
DECLARE @DefaultRed INT
DECLARE @DefaultGreen INT
DECLARE @DefaultBlue INT
DECLARE @ColorID INT
DECLARE @HotelCustomerNumber INT
DECLARE @IsOnlineColor BIT

SELECT @InclAllotment = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VacantRoomsInclAllotment'
SELECT @OOSDecreaseVacantRooms = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='OOSDecreaseVacantRooms'
SELECT @OnlineUseRoomBookingStatusForCalendar = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='OnlineUseRoomBookingStatusForCalendar'
SET @OnlineUseRoomBookingStatusForCalendar = ISNULL(@OnlineUseRoomBookingStatusForCalendar, 0)

IF @AHotelNumber = -1
	SET @AHotelNumber = 0

select @SystemDate = SystemDate from System (nolock) where number = 1

select @NumberOfRooms = Count(*) FROM Rooms (Nolock) where HotelNumber = @AHotelNumber

SELECT @HotelCustomerNumber = CustomerNumber FROM Customer (NOLOCK) WHERE HotelNumber = @AHotelNumber

IF NOT EXISTS (SELECT * FROM ColorHotelXTAB WHERE HotelCustomerNumber <> -1)
	SET @HotelCustomerNumber = -1

SELECT @IsOnlineColor = OnlineColor FROM ColorHotelXTAB (Nolock) WHERE HotelCustomerNumber = @HotelCustomerNumber



IF ISNULL(@IsOnlineColor, 0) = 1 AND @AColorType = 2
	SET @IsOnlineColor = 1
ELSE
	SET @IsOnlineColor = 0

CREATE TABLE #tmpAvailability (
		[DateStamp] [char] (8) COLLATE DATABASE_DEFAULT NOT NULL ,
		[RoomTypeNumber] [int] NOT NULL ,
		[HotelNumber] [int] NOT NULL ,
		[ReservationGroup] [int] NOT NULL,
		[SoldRooms] [smallint] NULL ,
		[Adults] [smallint] NULL ,
		[Children1] [smallint] NULL ,
		[Children2] [smallint] NULL ,
		[NumberOfConfirmed] [smallint] NULL ,
		[NumberOfProvisionals] [smallint] NULL ,
		[NumberOfGuaranteed] [smallint] NULL ,
		[NumberOfTentative] [smallint] NULL ,
		[NumberOfWaitinglists] [smallint] NULL ,
		[NumberOfDayrooms] [smallint] NULL ,
		[FreeRoomsAllotment] [smallint] NULL ,
		[TotalRoomsAllotment] [smallint] NULL ,
		[PaxAllotment] [smallint] NULL,
		[NumberOfRoomsSoldToday] [smallint] NULL,
		[NumberOfOutOfOrder] [smallint] NULL,
		[NumberOfOutForService] [smallint] NULL,
		[NumberOfOccupied] [smallint] NULL ,
		[NummberOfOccupiedGroup] [smallint] NULL,
		[SoldPrice] [MONEY] NULL,
		RequestorID INT NULL,
		NumberOfRoomsSoldCourse smallint NULL,
		NumberOfRoomsSoldAttendee smallint NULL,
		NumberOfArrival SMALLINT NULL,
		NumberOfInhouse SMALLINT NULL,
		NumberOfStay SMALLINT NULL,
		NumberOfDeparture SMALLINT NULL,
		ArrivalPax SMALLINT NULL,
		InhousePax SMALLINT NULL,
		StayPax SMALLINT NULL,
		DeparturePax SMALLINT NULL,
		NumberOfWaitingArrival SMALLINT NULL,
		NumberOfTentativeArrival SMALLINT NULL,
		WaitingArrivalPax SMALLINT NULL, 
		TentativeArrivalPax SMALLINT NULL
		)


CREATE TABLE #Result (
	[DateStamp] DateTime NOT NULL ,
	[Red] INT,
	[Green] INT,
	[Blue] INT,
	[VacantRooms] INT
)
IF @IsOnlineColor = 1
BEGIN
	SELECT @DefaultRed = Red, @DefaultGreen = Green, @DefaultBlue = Blue
	FROM ColorRGB CR (NOLOCK)
	INNER JOIN ColorHotelXTAB CHX (NOLOCK) ON CR.ColorRGBID = CHX.FK_ColorRGBID AND CHX.HotelCustomerNumber = @HotelCustomerNumber AND FK_ColorID = 0

	IF @@ROWCOUNT = 0
		SELECT @DefaultRed = Red, @DefaultGreen = Green, @DefaultBlue = Blue
		FROM ColorRGB CR (NOLOCK)
		INNER JOIN ColorHotelXTAB CHX (NOLOCK) ON CR.ColorRGBID = CHX.FK_ColorRGBID AND CHX.HotelCustomerNumber = -1 AND FK_ColorID = 0
END
ELSE
	SELECT @DefaultRed = Red, @DefaultGreen = Green, @DefaultBlue = Blue
	FROM ColorRGB CR (NOLOCK)
	INNER JOIN Color C (NOLOCK) ON CR.ColorRGBID = C.FK_ColorRGBID AND ColorID = 0

SET @DefaultRed = ISNULL(@DefaultRed,0)
SET @DefaultGreen = ISNULL(@DefaultGreen,0)
SET @DefaultBlue =  ISNULL(@DefaultBlue,0)

IF @OnlineUseRoomBookingStatusForCalendar = 1
  DECLARE #CUR CURSOR LOCAL FAST_FORWARD FOR
  SELECT dtRange.IndividualDate,  
  CASE WHEN @InclAllotment = 0
	THEN
		CASE WHEN @OOSDecreaseVacantRooms = 0
			THEN dtRange.PacketNumber-sum(ISNULL(SoldRooms, 0)) - sum(ISNULL(NumberOfOutOfOrder, 0)) - sum(ISNULL(NumberOfOccupied,0))
			ELSE dtRange.PacketNumber-sum(ISNULL(SoldRooms,0)) - sum(ISNULL(NumberOfOutOfOrder,0)) - sum(ISNULL(NumberOfOccupied,0)) - sum(ISNULL(NumberOfOutForService,0))
		END
	ELSE
		CASE WHEN @OOSDecreaseVacantRooms = 0
			THEN dtRange.PacketNumber-sum(ISNULL(SoldRooms,0)) - sum(ISNULL(NumberOfOutOfOrder,0)) - sum(ISNULL(NumberOfOccupied,0)) - sum(ISNULL(TotalRoomsAllotment,0))
			ELSE dtRange.PacketNumber-sum(ISNULL(SoldRooms,0)) - sum(ISNULL(NumberOfOutOfOrder,0)) - sum(ISNULL(NumberOfOccupied,0)) - sum(ISNULL(TotalRoomsAllotment,0)) - sum(ISNULL(NumberOfOutForService,0))
		END
	END AS VacantRoom,
	ISNULL(MAX(CASE WHEN ISNULL(Y.FilterColorID, 0) = 0
			THEN C.ColorID
			ELSE FIlterColorID
		END), 0) As ColorID
  FROM dbo.DateRange(@AStartDate, @AEndDate, @NumberOfRooms) AS dtRange
  LEFT OUTER JOIN ROOMBOOKINGSTATUS rbs (NOLOCK) ON rbs.DateStamp = dtRange.IndividualDate AND rbs.HotelNumber = @AHotelNumber
  LEFT OUTER JOIN RoomBookingStatusYield SY (NOLOCK) ON dtRange.IndividualDate = SY.DateStamp AND SY.HotelNumber = @AHotelNumber
  LEFT OUTER JOIN colorRGB CR (NOLOCK) ON ((256*256*Blue) + (256*Green) + red) = SY.DayColor
  LEFT OUTER JOIN COlOR C (NOLOCK) ON C.FK_ColorRGBID = CR.ColorRGBID
  LEFT OUTER JOIN Yield Y (NOLOCK) on SY.YieldFilter = Y.YieldNumber
  Group by dtRange.IndividualDate,dtRange.PacketNumber
ELSE BEGIN
  INSERT INTO #tmpAvailability 
  EXEC GenerateAvailability @AStartDate, @AEndDate, 0, @AHotelNumber, @SystemDate, 0     
  
  DECLARE #CUR CURSOR LOCAL FAST_FORWARD FOR
  SELECT dtRange.IndividualDate,  
  CASE WHEN @InclAllotment = 0
	THEN
		CASE WHEN @OOSDecreaseVacantRooms = 0
			THEN dtRange.PacketNumber-sum(ISNULL(SoldRooms, 0)) - sum(ISNULL(NumberOfOutOfOrder, 0)) - sum(ISNULL(NumberOfOccupied,0))
			ELSE dtRange.PacketNumber-sum(ISNULL(SoldRooms,0)) - sum(ISNULL(NumberOfOutOfOrder,0)) - sum(ISNULL(NumberOfOccupied,0)) - sum(ISNULL(NumberOfOutForService,0))
		END
	ELSE
		CASE WHEN @OOSDecreaseVacantRooms = 0
			THEN dtRange.PacketNumber-sum(ISNULL(SoldRooms,0)) - sum(ISNULL(NumberOfOutOfOrder,0)) - sum(ISNULL(NumberOfOccupied,0)) - sum(ISNULL(TotalRoomsAllotment,0))
			ELSE dtRange.PacketNumber-sum(ISNULL(SoldRooms,0)) - sum(ISNULL(NumberOfOutOfOrder,0)) - sum(ISNULL(NumberOfOccupied,0)) - sum(ISNULL(TotalRoomsAllotment,0)) - sum(ISNULL(NumberOfOutForService,0))
		END
	END AS VacantRoom,
	ISNULL(MAX(CASE WHEN ISNULL(Y.FilterColorID, 0) = 0
			THEN C.ColorID
			ELSE FIlterColorID
		END), 0) As ColorID
  FROM dbo.DateRange(@AStartDate, @AEndDate, @NumberOfRooms) AS dtRange
  LEFT OUTER JOIN #tmpAvailability  ON #tmpAvailability.DateStamp = dtRange.IndividualDate AND #tmpAvailability.HotelNumber = @AHotelNumber
  LEFT OUTER JOIN RoomBookingStatusYield SY (NOLOCK) ON dtRange.IndividualDate = SY.DateStamp AND SY.HotelNumber = @AHotelNumber
  LEFT OUTER JOIN colorRGB CR (NOLOCK) ON ((256*256*Blue) + (256*Green) + red) = SY.DayColor
  LEFT OUTER JOIN COlOR C (NOLOCK) ON C.FK_ColorRGBID = CR.ColorRGBID
  LEFT OUTER JOIN Yield Y (NOLOCK) on SY.YieldFilter = Y.YieldNumber
  Group by dtRange.IndividualDate,dtRange.PacketNumber
END

OPEN #CUR

FETCH NEXT FROM #CUR
INTO @DateStamp, @VacantRooms, @ColorID

WHILE (@@FETCH_STATUS = 0)
BEGIN
	IF @VacantRooms <= 0
		SET @tmpVacantRooms = 0
	ELSE
		SET @tmpVacantRooms = @VacantRooms
	IF @ColorID = 0
	BEGIN		
		IF @tmpVacantRooms > 0
			SELECT TOP 1 @ColorID  = FK_ColorID FROM SalesChannelExtraFields SEF (NOLOCK)
			INNER JOIN CUSTOMER C (NOLOCK) ON SEF.CustomerNumber = C.CustomerNumber
			WHERE HotelNumber = @AHotelNumber
			AND EXISTS (SELECT * FROM SalesChannelExtraFields SEF2 (NOLOCK) WHERE SEF2.CustomerNumber = SEF.CustomerNumber and SEF2.Availability > 0)
			AND availability <= @tmpVacantRooms
			ORDER BY availability DESC
		ELSE
           SELECT TOP 1 @ColorID  = FK_ColorID FROM SalesChannelExtraFields SEF (NOLOCK)
			INNER JOIN CUSTOMER C (NOLOCK) ON SEF.CustomerNumber = C.CustomerNumber
			WHERE HotelNumber = @AHotelNumber
			AND EXISTS (SELECT * FROM SalesChannelExtraFields SEF2 (NOLOCK) WHERE SEF2.CustomerNumber = SEF.CustomerNumber and SEF2.Availability > 0)
			AND availability = @tmpVacantRooms
			ORDER BY availability DESC
		SET @ColorID = ISNULL(@ColorID, 0)
	END

	SET @Red = @DefaultRed
	SET @Green = @DefaultGreen
	SET @Blue =  @DefaultBlue

	IF @IsOnlineColor = 1
	BEGIN
		SELECT @Red = ISNULL(Red, 0), @Green = ISNULL(Green, 0), @Blue = ISNULL(Blue, 0) 
		FROM ColorRGB CR (NOLOCK)
		INNER JOIN ColorHotelXTAB CHX (NOLOCK) ON CR.ColorRGBID = CHX.FK_ColorRGBID
		WHERE CHX.FK_ColorID = @ColorID AND HotelCustomerNumber = @HotelCustomerNumber

		IF @@ROWCOUNT = 0
			SELECT @Red = ISNULL(Red, 0), @Green = ISNULL(Green, 0), @Blue = ISNULL(Blue, 0) 
			FROM ColorRGB CR (NOLOCK)
			INNER JOIN ColorHotelXTAB CHX (NOLOCK) ON CR.ColorRGBID = CHX.FK_ColorRGBID
			WHERE CHX.FK_ColorID = @ColorID AND HotelCustomerNumber = -1
	END
	ELSE
		SELECT @Red = ISNULL(Red, 0), @Green = ISNULL(Green, 0), @Blue = ISNULL(Blue, 0) 
		FROM ColorRGB CR (NOLOCK)
		INNER JOIN Color C (NOLOCK) ON CR.ColorRGBID = C.FK_ColorRGBID
		WHERE C.ColorID = @ColorID

	INSERT INTO #Result (DateStamp, Red, Green, Blue, VacantRooms)
	VALUES (@DateStamp, @Red, @Green, @Blue, @VacantRooms)

	FETCH NEXT FROM #CUR
	INTO @DateStamp, @VacantRooms, @ColorID
END

CLOSE #Cur
DEALLOCATE #Cur

SELECT * FROM #Result
ORDER BY DateStamp


  DROP TABLE #tmpAvailability
  DROP TABLE #Result