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
		)


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