CREATE FUNCTION dbo.DayColorMatches (@HotelNumber VARCHAR(100), @ProductNumber INT, @Date DATETIME, @VacantRooms INT = 0)
RETURNS BIT AS  
BEGIN 
/*  
  Created by Jacob Pedersen November 14th, 2005.

  Description: The purpose of this function is to match the selected DayColor on product with the yield day color found on roomtypes plan. We have both automatic settings for mpms and for normal hotels, and if these are not set we have the option of choosing the colors on the plan.

  Last Changed:
  4.3: 
  2010-08-16: CR016481: @ProductMatchDayColor is declared as TINYINT which cannot handle value > 127.
                        It must be declared as INT since the changes 2010-08-06/CR012098.
  2010-08-11: CR016393: DayColor Match did not work if @RoomBookingStatusYieldMatchDayColor was NULL.
  2010-08-06: CR013098: Extending with 5 new colors.
  3.39: 
  2007-06-07 EXT: split in two functions; one for color and one for logic here. Color can then be reused
  3.36:
  2006-03-16 BUG: if no records found in SalesChannelExtraFields then all matches
  2006-02-01 Optimized match when all 4 bits set. Also match when no color
  2005-12-16 Match up to 4 colors + new auto logic

  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
*/

  DECLARE @HotelSetup_ProductMatchDayColor BIT,
  @HotelSetup_NoColorMeansAllColors BIT,
  @Match BIT,
  @ColorCount INT,
  @DayColor INT

  SELECT @HotelSetup_ProductMatchDayColor = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ProductMatchDayColor'
  SELECT @HotelSetup_NoColorMeansAllColors = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NoColorMeansAllColors'

  SET @HotelSetup_ProductMatchDayColor = ISNULL(@HotelSetup_ProductMatchDayColor, 1)
  SET @HotelSetup_NoColorMeansAllColors = ISNULL(@HotelSetup_NoColorMeansAllColors, 1)

  SET @Match = 0
  
  IF @HotelSetup_ProductMatchDayColor = 1
  BEGIN
	SELECT @ColorCount = COUNT(*) FROM ProductColorXTAB (NOLOCK)
	WHERE ProductNumber = @ProductNumber
	SELECT @DayColor = dbo.DayColor (@HotelNumber, @Date, @VacantRooms)
	IF @ColorCount > 0
	BEGIN
	  
	  IF EXISTS(
		SELECT * FROM Products P
		INNER JOIN ProductColorXTAB PX ON P.ProductNumber = PX.ProductNumber 
		WHERE P.ProductNumber = @ProductNumber
		  AND (PX.ColorID = @DayColor OR @DayColor = 0)
	  )
		SET @Match = 1
	END ELSE
		IF @HotelSetup_NoColorMeansAllColors = 1
			SET @Match = 1
		ELSE 
		BEGIN	
			IF @DayColor = 0
				SET @Match = 1
			ELSE
				SET @Match = 0
		END
  END ELSE
	SET @Match = 1
  RETURN @Match
END

