CREATE PROCEDURE DBO.SetYieldFilterInPeriod @YieldFilterNumber INT, @StartDate DATETIME, @EndDate DATETIME, @HotelNumber INT=0 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 @MatchDayColor INT
SELECT @YieldFilterType=FilterTypeNumber, @MatchDayColor=MatchDayColor 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
          IF IsNull (@MatchDayColor, 0) = 0
            SET @MatchDayColor = 0

          IF @YieldFilterType = 1
          BEGIN
            IF (@MatchDayColor = 0) OR ((@MatchDayColor=1 /*dcGreen*/) AND (@DayColor=32768)) OR ((@MatchDayColor=2 /*dcYellow*/) AND (@DayColor=65535)) OR ((@MatchDayColor=3 /*dcRed*/) AND (@DayColor=255)) OR ((@MatchDayColor=4 /*dcBlue*/) AND (@DayColor=16711680))  --   TDayColor = (dcNone, dcGreen, dcYellow, dcRed, dcBlue);
              UPDATE RoomBookingStatusYield SET OnlineYieldFilter = @YieldFilterNumber WHERE DateStamp=@CurrentDateStr AND HotelNumber=@HotelNumber
          END
          ELSE IF @YieldFilterType = 2
          BEGIN
            IF (@MatchDayColor = 0) OR ((@MatchDayColor=1 /*dcGreen*/) AND (@DayColor=32768)) OR ((@MatchDayColor=2 /*dcYellow*/) AND (@DayColor=65535)) OR ((@MatchDayColor=3 /*dcRed*/) AND (@DayColor=255)) OR ((@MatchDayColor=4 /*dcBlue*/) AND (@DayColor=16711680))  --   TDayColor = (dcNone, dcGreen, dcYellow, dcRed, dcBlue);
              UPDATE RoomBookingStatusYield SET OnlineYieldFilterHall = @YieldFilterNumber WHERE DateStamp=@CurrentDateStr AND HotelNumber=@HotelNumber
          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

