--ALTER FUNCTION dbo.DayColorMatches (@HotelNumber INT, @ProductNumber INT, @Date DATETIME, @VacantRooms INT = 0)
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,
  @Match BIT

  SELECT @HotelSetup_ProductMatchDayColor = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ProductMatchDayColor'

  IF @HotelSetup_ProductMatchDayColor = 1
  BEGIN
    DECLARE @ProductMatchDayColor INT

    SELECT @ProductMatchDayColor=MatchDayColor FROM Products WITH (NOLOCK) WHERE ProductNumber=@ProductNumber -- can contain multiple colors: bits 0:green 1:yellow 2:red 3:blue.. up to 9 colors.

    -- BA/20100806: CR013098/4.3+:  Extending with 5 new colors.
--    IF @ProductMatchDayColor = 1 + 2 + 4 + 8  -- OPTIMIZE: match with all colors
    IF @ProductMatchDayColor = 1 + 2 + 4 + 8 + 16 + 32 + 64 + 128 + 256  -- OPTIMIZE: match with all colors
      SET @Match = 1
    ELSE IF @ProductMatchDayColor > 0
    BEGIN
      DECLARE @RoomBookingStatusYieldMatchDayColor INT

      SELECT @RoomBookingStatusYieldMatchDayColor = dbo.DayColor (@HotelNumber, @Date, @VacantRooms)

      -- BA/20100806: CR013098/4.3+: Extending with 5 new colors. Changes here must also 
      --              be made to ProductKL.TProductColorScheme
      IF 
          ((@RoomBookingStatusYieldMatchDayColor =       -1) OR (IsNull(@RoomBookingStatusYieldMatchDayColor, 0) = 0)) OR  --no color = always match
          ((@RoomBookingStatusYieldMatchDayColor =    32768) AND (@ProductMatchDayColor &   1) =   1) OR  -- 1. clGreen
          ((@RoomBookingStatusYieldMatchDayColor =    65535) AND (@ProductMatchDayColor &   2) =   2) OR  -- 2. clYellow
          ((@RoomBookingStatusYieldMatchDayColor =      255) AND (@ProductMatchDayColor &   4) =   4) OR  -- 3. clRed
          ((@RoomBookingStatusYieldMatchDayColor = 16711680) AND (@ProductMatchDayColor &   8) =   8) OR  -- 4. clBlue
       -- BA/20100806: CR013098/4.3+: 5 new colors
          ((@RoomBookingStatusYieldMatchDayColor =  7013797) AND (@ProductMatchDayColor &  16) =  16) OR  -- 5. 6B05A5
          ((@RoomBookingStatusYieldMatchDayColor =  8388608) AND (@ProductMatchDayColor &  32) =  32) OR  -- 6. clNavy
          ((@RoomBookingStatusYieldMatchDayColor =    65280) AND (@ProductMatchDayColor &  64) =  64) OR  -- 7. clLime
          ((@RoomBookingStatusYieldMatchDayColor = 16711935) AND (@ProductMatchDayColor & 128) = 128) OR  -- 8. clFuchasia
          ((@RoomBookingStatusYieldMatchDayColor = 11038632) AND (@ProductMatchDayColor & 256) = 256)     -- 9. A86FA8
        SET @Match = 1
      ELSE
        SET @Match = 0
    END
    ELSE
      SET @Match = 1
  END
  ELSE
    SET @Match = 1

  RETURN @Match
END

