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
  @HotelSetup_CentralBookingType TINYINT,
  @AutoDayProfileColorsNONE INT,
  @AutoDayProfileColorsBLUE INT,
  @AutoDayProfileColorsGREEN INT,
  @AutoDayProfileColorsYELLOW INT,
  @AutoDayProfileColorsRED INT

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

  IF @HotelSetup_CentralBookingType = 7 -- MPMS
  BEGIN
    SELECT @AutoDayProfileColorsNONE = IsNull(AutoDayProfileColorsNONE, 0), @AutoDayProfileColorsBLUE = IsNull(AutoDayProfileColorsBLUE, 0), @AutoDayProfileColorsGREEN = IsNull(AutoDayProfileColorsGREEN, 0), @AutoDayProfileColorsYELLOW = IsNull(AutoDayProfileColorsYELLOW, 0), 
	@AutoDayProfileColorsRED = IsNull(AutoDayProfileColorsRED, 0) FROM SalesChannelExtraFields WITH (NOLOCK)
    INNER JOIN Customer WITH (NOLOCK) ON SalesChannelExtraFields.CustomerNumber=Customer.CustomerNumber AND HotelNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@HotelNumber))
    IF @@rowcount = 0
    BEGIN
      SELECT @AutoDayProfileColorsNONE = 0
      SELECT @AutoDayProfileColorsBLUE = 0
      SELECT @AutoDayProfileColorsGREEN = 0
      SELECT @AutoDayProfileColorsYELLOW = 0
      SELECT @AutoDayProfileColorsRED = 0
    END
  END
  ELSE -- not mpms read from hotelsetup
  BEGIN
    SELECT @AutoDayProfileColorsNONE = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AutoDayProfileColorsNONE'
    SELECT @AutoDayProfileColorsBLUE = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AutoDayProfileColorsBLUE'
    SELECT @AutoDayProfileColorsGREEN = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AutoDayProfileColorsGREEN'
    SELECT @AutoDayProfileColorsYELLOW = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AutoDayProfileColorsYELLOW'
    SELECT @AutoDayProfileColorsRED = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AutoDayProfileColorsRED'
  END

  DECLARE @RoomBookingStatusYieldMatchDayColor INT

  IF (@AutoDayProfileColorsNONE = 0) AND (@AutoDayProfileColorsBLUE = 0) AND(@AutoDayProfileColorsGREEN = 0) AND (@AutoDayProfileColorsYELLOW = 0) AND (@AutoDayProfileColorsRED = 0) -- NOTE! They cannot just be added together as some can be minus!
  BEGIN
    SELECT @RoomBookingStatusYieldMatchDayColor=ISNULL(DayColor, 0) FROM RoomBookingStatusYield WITH (NOLOCK) WHERE HotelNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@HotelNumber)) AND DateStamp=@Date
  END
  ELSE -- Check AutoDayColors
  BEGIN
    SET @RoomBookingStatusYieldMatchDayColor = 0

    IF @VacantRooms >= @AutoDayProfileColorsRED
      SET @RoomBookingStatusYieldMatchDayColor = 255 -- clRed
    IF (@VacantRooms >= @AutoDayProfileColorsYELLOW) AND (@AutoDayProfileColorsYELLOW <> 0)
      SET @RoomBookingStatusYieldMatchDayColor = 65535 -- clYellow
    IF (@VacantRooms >= @AutoDayProfileColorsGREEN) AND (@AutoDayProfileColorsGREEN <> 0)
      SET @RoomBookingStatusYieldMatchDayColor = 32768 -- clGreen
    IF (@VacantRooms >= @AutoDayProfileColorsBLUE) AND (@AutoDayProfileColorsBLUE <> 0)
      SET @RoomBookingStatusYieldMatchDayColor = 16711680 -- clBlue
    IF (@VacantRooms >= @AutoDayProfileColorsNONE) AND (@AutoDayProfileColorsNONE <> 0)
      SET @RoomBookingStatusYieldMatchDayColor = 0
  END

  RETURN @RoomBookingStatusYieldMatchDayColor
END
