CREATE PROCEDURE DBO.SetYieldFilterInPeriod @YieldFilterNumber INT, @StartDate DATETIME, @EndDate DATETIME, @HotelNumber INT=0, @Result BIT OUTPUT AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen 2004-01-05

    Changed: 
    JP20070601 EXT: support for OnlineHalls
    JP20060915 BUG: fix for negative HotelNumber
    JP20050919 BUG: did not take HotelNumber into account
    JP20040113 EXT: check for MatchDayColor

    NOTES: 

*/
IF @HotelNumber < 0
  SET @HotelNumber = 0
--find filters type
DECLARE @YieldFilterType TINYINT  --  TYieldFilter = (yfAvailability, yfPicassoOnline, yfCustomerNumber, yfCustomerGroup, yfOtherBookSystem);
DECLARE @DayColorID INT
DECLARE @YieldEnabled BIT
DECLARE @Setting VARCHAR(10)

SELECT @Setting = KeyValue FROM HotelSetup
WHERE SearchKey = 'yieldenabled'

SET @YieldEnabled = 0
IF ISNULL(@Setting, '') = '1'
  SET @YieldEnabled = 1

SET @Result = 1

SELECT @YieldFilterType=FilterTypeNumber FROM Yield WITH (NOLOCK) WHERE YieldNumber=@YieldFilterNumber
IF @@rowcount = 1
BEGIN
  IF IsNull (@YieldFilterType, 0) = 0
    SET @YieldFilterType = 0
  DECLARE @Found INT
  DECLARE @FormatedEndDate CHAR (8)
  SET @FormatedEndDate =  Convert (CHAR (8), @EndDate, 112)
  DECLARE @CurrentDateStr CHAR (8)
  SET @CurrentDateStr = Convert (CHAR (8), @StartDate, 112)
  IF @YieldFilterType IN (0, 1, 2) -- yfAvailability and yfPicassoOnline + yfPicassoOnlineHalls
  BEGIN
    -- read through all dates in period and edit/insert

    WHILE @CurrentDateStr <= @FormatedEndDate
    BEGIN
      SELECT @found = COUNT (*) FROM RoomBookingStatusYield WITH (NOLOCK) WHERE DateStamp = @CurrentDateStr AND HotelNumber=@HotelNumber

      IF @found = 0 -- do insert
      BEGIN
        IF @YieldFilterType = 0
          INSERT INTO RoomBookingStatusYield (DateStamp, YieldFilter, HotelNumber) VALUES (@CurrentDateStr, @YieldFilterNumber, @HotelNumber)
        ELSE IF @YieldFilterType = 1
          INSERT INTO RoomBookingStatusYield (DateStamp, OnlineYieldFilter, HotelNumber) VALUES (@CurrentDateStr, @YieldFilterNumber, @HotelNumber)
        ELSE IF @YieldFilterType = 2
          INSERT INTO RoomBookingStatusYield (DateStamp, OnlineYieldFilterHall, HotelNumber) VALUES (@CurrentDateStr, @YieldFilterNumber, @HotelNumber)
      END
      ELSE IF @found = 1 -- do edit
      BEGIN
        IF @YieldFilterType = 0
          UPDATE RoomBookingStatusYield SET YieldFilter = @YieldFilterNumber WHERE DateStamp=@CurrentDateStr AND HotelNumber=@HotelNumber
        ELSE
        BEGIN
          DECLARE @DayColor INT
          SELECT @DayColor=DayColor FROM RoomBookingStatusYield WITH (NOLOCK) WHERE DateStamp=@CurrentDateStr AND HotelNumber=@HotelNumber
		  IF IsNull (@DayColor, 0) = 0
            SET @DayColor = 0

		  SELECT @DayColorID=ColorID FROM Color C (NOLOCK)
		  INNER JOIN ColorRGB CRGB (NOLOCK) ON C.FK_ColorRGBID = CRGB.ColorRGBID AND 
		            @DayColor = ((256 * 256 * BLUE) + (256 * GREEN) + RED)
          

          IF @YieldFilterType = 1
          BEGIN
			IF @DayColorID = 0 OR @YieldEnabled = 1 OR EXISTS (SELECT * FROM YieldColorXTAB (NOLOCK) WHERE ColorID = @DayColorID AND YieldNumber = @YieldFilterNumber)
              UPDATE RoomBookingStatusYield SET OnlineYieldFilter = @YieldFilterNumber WHERE DateStamp=@CurrentDateStr AND HotelNumber=@HotelNumber
			ELSE 
			  SET @Result = 0
          END
          ELSE IF @YieldFilterType = 2
          BEGIN
            IF @DayColorID = 0 OR EXISTS (SELECT * FROM YieldColorXTAB (NOLOCK) WHERE ColorID = @DayColorID AND YieldNumber = @YieldFilterNumber)
              UPDATE RoomBookingStatusYield SET OnlineYieldFilterHall = @YieldFilterNumber WHERE DateStamp=@CurrentDateStr AND HotelNumber=@HotelNumber
			ELSE 
			  SET @Result = 0
          END
        END
      END
--      ELSE IF @found > 1 -- should never happen
--      BEGIN
--      END

      SET @StartDate = @StartDate +1
      SET @CurrentDateStr = Convert (CHAR (8), @StartDate, 112)
    END

  END
  ELSE
  BEGIN
-- TODO  yfCustomerNumber, yfCustomerGroup, yfOtherBookSystem
    DECLARE @lalalalalalalala INT
  END
END

