CREATE FUNCTION dbo.DayColor (@HotelNumber VARCHAR(100), @Date DATETIME, @VacantRooms INT = 0)
RETURNS INT AS  
BEGIN 
/*
  Created by Jacob Pedersen June 7th, 2007.

  Description: Returns the color of the day. Only needed to pass @VacantRooms if any of the auto colors are set

  Last Changed:
  N/A

  Notes:
  @VacantRooms is needed when we have AutoDayProfile coloring, since GenerateAvailability cannot be called from here and currently we do not have any other way of calculating a correct bookingstatus
*/


  IF @HotelNumber = ''
    SET @HotelNumber = '0' -- Fix for Product Search

  DECLARE @ColorID INT

  SELECT @ColorID =  FilterColorID from RoomBookingStatusYield rsy (NOLOCK)
  INNER JOIN Yield y on rsy.YieldFilter = y.YieldNumber
  WHERE HotelNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@HotelNumber)) AND DateStamp=@Date

  SET @ColorID = ISNULL(@ColorID, -1)

  IF @ColorID = -1
  BEGIN
	  SELECT @ColorID=ColorID 
	  FROM RoomBookingStatusYield WITH (NOLOCK)
	  INNER JOIN ColorRGB (NOLOCK) ON ISNULL(DayColor, 0) = ((256 * 256 * BLUE) + (256 * GREEN) + RED)
	  INNER JOIN Color (NOLOCK) ON ColorRGBID = FK_ColorRGBID  
	  WHERE HotelNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@HotelNumber)) AND DateStamp=@Date

	  SET @ColorID = ISNULL(@ColorID, -1)

	  IF @ColorID = -1
	  BEGIN
		IF EXISTS(SELECT * FROM SalesChannelExtraFields (NOLOCK) WHERE Availability > 0)
		BEGIN
		  SELECT TOP 1  @ColorID = FK_ColorID from SalesChannelExtraFields (NOLOCK)
			INNER JOIN Customer WITH (NOLOCK) ON SalesChannelExtraFields.CustomerNumber=Customer.CustomerNumber AND HotelNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@HotelNumber))
		  AND availability < @VacantRooms
		  ORDER BY availability DESC

		  SET @ColorID = ISNULL(@ColorID, 0)
		END
	 END
 END

 IF @ColorID = -1
	SET @ColorID = 0
   
  RETURN @ColorID
END
