/****** Object:  StoredProcedure [dbo].[GetSchedulerExpressData2]    Script Date: 11/03/2011 15:17:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[GetSchedulerExpressData2]
  @ADateTime DATETIME
, @ATableSize INT = 0   -- not used!
, @AMealType INT -- 0: Eat (only one mealtype found); otherwise 1: Brunch, 2: Lunch, 3: Dinner.
, @ATableType VARCHAR(4)   -- ConferenceRoomTypes.Type, i.e. "1", "5", "B4".
  AS

-- LJE/2011.12.07: Fix, by accident only 'Eat' was checked.
-- LJE/2011.11.03: Find result using TableType.
-- LJE/2011.11.02: Call with TableType.
-- LJE/2011.11.02: Find several ResourceId with same pax; "ResourceID in".
-- LJE/2011.08.18: Multiple schedules may be found - result found in table - not as OUTPUT params.
-- LJE/2011.06.22: copy of GetSchedulerExpressData.

DECLARE @AIntervalBetweenArrivals int
DECLARE @ADefaultTableBookingMinutes int

select @AIntervalBetweenArrivals = KeyValue from HOTELSETUP where searchkey = 'OnlineIntervalBetweenArrivals'
select @AIntervalBetweenArrivals = ISNULL(@AIntervalBetweenArrivals,30)

select @ADefaultTableBookingMinutes = KeyValue from HOTELSETUP where searchkey = 'DefaultTablebookingMinutes'
select @ADefaultTableBookingMinutes = ISNULL(@ADefaultTableBookingMinutes,120)

  -- Temporary table which contains found open schedules.
  CREATE TABLE #SchedulerExpressData (
    Found int
    ,Pax int
    ,OpeningDateTime datetime
    ,CloseDateTime datetime
    ,IntervalBetweenArrivals int
    ,DefaultTableBookingMinutes int
    ,TableType varchar(4))

  -- Map MealType into string: Eat, Brunch, Lunch, Dinner.
  DECLARE @MealTypeString VARCHAR(50)
  SET @MealTypeString = ''
  IF @AMealType = 0
  BEGIN
    SET @MealTypeString = 'eat'
  END
  IF @AMealType = 1 
  BEGIN
    SET @MealTypeString = 'brunch'
  END
  ELSE IF @AMealType = 2
  BEGIN
    SET @MealTypeString = 'lunch'
  END
  ELSE IF @AMealType = 3 
  BEGIN
    SET @MealTypeString = 'dinner'
  END

  DECLARE @ResourceID INT
  SET @ResourceID = 0
 
  DECLARE @ScheduleID INT
  DECLARE @ParentID INT
  DECLARE @EventType INT
  DECLARE @Start DATETIME
  DECLARE @Finish DATETIME
  DECLARE @Options INT
  DECLARE @Caption VARCHAR(500)
  DECLARE @RecurrenceIndex INT
  DECLARE @ActualStart DATETIME
  DECLARE @ActualFinish DATETIME
  DECLARE @ScheduleType INT
  DECLARE @Pax INT
  DECLARE @RecurrenceInfoTranslated VARCHAR(100)
  DECLARE @TableType VARCHAR(4)

-- Variables for Recurenceinfo field

  DECLARE @Count INT
  DECLARE @DayNumber INT
  DECLARE @DayType INT
  DECLARE @RFinish VARCHAR(20) -- should be datetime
  DECLARE @OccurDays VARCHAR(7)
  DECLARE @Periodicity INT
  DECLARE @Recurrence INT
  DECLARE @RStart VARCHAR(20) --should be datetime
  DECLARE @YearPeriodicity INT 
  --Above variables are stored in the following way in @RecurrenceInfoTranslated variable:
   -- 50,1,0,29-12-1899,012,1,1,22-04-2007,1

  DECLARE @CurrentValue VARCHAR(20)
  DECLARE @CurrentWeekDay CHAR(1)
  DECLARE @Counter INT
  DECLARE @Found BIT
  DECLARE @FoundID INT 
  
  DECLARE @APAX int
  DECLARE @AOpeningDateTime datetime
  DECLARE @ACloseDateTime datetime

  SET @Found = 0
  SET @APax = 0 
  SET @AOpeningDateTime = 0
  SET @ACloseDateTime = 0 
  SET @ADateTime = CONVERT(DATETIME,@ADateTime,112)

  SELECT @CurrentWeekDay = CONVERT(CHAR(1), DATEPART(WEEKDAY, @ADateTime)-1)
  SELECT @ADateTime = CONVERT(INT, @ADateTime)
  DECLARE ScheduleCur CURSOR LOCAL FAST_FORWARD FOR

  SELECT s.ScheduleID, s.ParentID, s.EventType, s.Start, s.Finish, s.Options, s.Caption
    , s.RecurrenceIndex, s.ResourceID, CONVERT(DATETIME, s.ActualStart), CONVERT(DATETIME, s.ActualFinish)
    , s.Type ScheduleType, ISNULL(s.Pax, 0), s.RecurrenceInfoTranslated 
    , crt.Type TableType
  FROM Schedule s(NOLOCK)
    ,ConferenceRoomTypes crt(NOLOCK)
  WHERE s.ResourceID = crt.ConferenceRoomTypeNumber
  AND s.ResourceID in
  (
     select distinct s2.ResourceID from Schedule s2, ConferenceRoomTypes c2  
     where s2.ResourceId = c2.ConferenceRoomTypeNumber
        --AND CHARINDEX('Eat',s2.Caption) > 0
		AND CHARINDEX(@MealTypeString,s2.Caption) > 0  -- fix 2011.12.07.
        AND c2.Type = @ATableType
  )
  AND crt.Type = @ATableType
  AND CHARINDEX(@MealTypeString,s.Caption) > 0
  AND
  (
    (s.EventType = 0 AND s.Finish >= @ADateTime AND CONVERT(VARCHAR(8),s.Start,112) <= CONVERT(VARCHAR(8), @ADateTime, 112) ) -- Just A normal one (None Recurring)
    OR
    (s.EventType = 1 AND (CONVERT(DATETIME, s.ActualFinish) -2 >= @ADateTime) AND (CONVERT(DATETIME, s.ActualStart) -2 <= @ADateTime))   -- We are dealing with a parent and the dates are good
  )

  ORDER BY EventType, Start
  OPEN ScheduleCur
  
  FETCH NEXT FROM ScheduleCur INTO @ScheduleID, @ParentID, @EventType, @Start, @Finish, @Options, @Caption, @RecurrenceIndex, @ResourceID
    , @ActualStart, @ActualFinish, @ScheduleType, @Pax, @RecurrenceInfoTranslated, @TableType 
 
  WHILE(@@Fetch_status = 0) 
  BEGIN
    SET @Found = 0  -- As default no schedule is found.
    IF @EventType = 0 -- Just A normal one (None Recurring)
    BEGIN
      SET @APax = ISNULL(@Pax, 0)
      SET @AOpeningDateTime = @Start
      SET @ACloseDateTime = @Finish
      SET @Found = 1
    END
    ELSE IF @EventType = 1 -- We are dealing with a parent 
    BEGIN
      SET @Counter = 1
      WHILE @RecurrenceInfoTranslated <> '' -- We know that there is always 9 elements in the string
      BEGIN 
        EXEC DBO.ResolveNextToken @RecurrenceInfoTranslated,',', @CurrentValue OUTPUT, @RecurrenceInfoTranslated OUTPUT

        IF @Counter = 1
          SET @Count = CONVERT(INT, @CurrentValue)
        ELSE IF @Counter = 2
          SET @DayNumber = CONVERT(INT, @CurrentValue)
        ELSE IF @Counter = 3
          SET @DayType = CONVERT(INT, @CurrentValue)
        ELSE IF @Counter = 4
          SET @RFinish =  @CurrentValue
        ELSE IF @Counter = 5
          SET @OccurDays = @CurrentValue
        ELSE IF @Counter = 6
          SET @Periodicity = CONVERT(INT, @CurrentValue)
        ELSE IF @Counter = 7
          SET @Recurrence = CONVERT(INT, @CurrentValue)
        ELSE IF @Counter = 8
          SET @RStart =  @CurrentValue
        ELSE IF @Counter = 9
          SET @YearPeriodicity = CONVERT(INT, @CurrentValue)
        SET @Counter = @Counter + 1
      END

      PRINT 'ID: '+ convert(varchar(3), @ScheduleID)
      PRINT 'Count: '+ CONVERT(VARCHAR(3), @Count)
      PRINT 'Daynumber: '+ CONVERT(VARCHAR(3), @DayNumber)
      PRINT 'DayType: '+ CONVERT(VARCHAR(3), @DayType)
      PRINT 'Finish: '+ CONVERT(VARCHAR(12), @RFinish)
      PRINT 'OccurDays: '+ CONVERT(VARCHAR(12), @OccurDays)
      PRINT 'Periodicity: '+ CONVERT(VARCHAR(3), @Periodicity)
      PRINT 'Recurrence: '+ CONVERT(VARCHAR(3), @Recurrence)
      PRINT 'Start: '+ CONVERT(VARCHAR(12), @RStart)
      PRINT 'YearPeriodicity: '+ CONVERT(VARCHAR(3), @YearPeriodicity)
      PRINT 'Pax: '+ convert(varchar(3), @Pax)
      PRINT 'CurrentWeekday: ' + @CurrentWeekday
      
      IF @Recurrence = 0 -- Daily
      BEGIN
        IF @DayType = 1 -- Every day, but we still have to check the periodicity variable. It could be every 2,3.8... and so on   day
        BEGIN
          IF dbo.IsRecurringDayInException (@ScheduleID, @ADateTime) = 0 -- Okay we havent found an exception
          BEGIN
            IF @Periodicity > 1
            BEGIN
              print 'TODO: Make this'
            END
            ELSE
            BEGIN
              SELECT @FoundID = ISNULL(ScheduleID, 0), @Apax = ISNULL(Pax, 0), @AOpeningDateTime = Start, @ACloseDateTime = Finish FROM Schedule (NOLOCK) WHERE ParentID = @ScheduleID AND EventType = 4 AND  CONVERT(VARCHAR(8), Finish, 112) = CONVERT(VARCHAR(8), @ADateTime, 112) AND CONVERT(VARCHAR(8), Start, 112) = CONVERT(VARCHAR(8), @ADateTime, 112)
              SET @FoundID = ISNULL(@FoundID, 0 )
              IF @FoundID = 0
              BEGIN
                SET @APax = ISNULL(@Pax, 0)
                SET @AOpeningDateTime =  @ADateTime + CONVERT(DATETIME,CONVERT(CHAR(5),@Start,108),108) 
                SET @ACloseDateTime =  @ADateTime + CONVERT(DATETIME,CONVERT(CHAR(5),@Finish,108),108) 
              END
              SET @Found = 1
            END
          END
        END
        ELSE IF @DayType = 2 -- This is every workday: Monday to friday
        BEGIN
          IF dbo.IsRecurringDayInException (@ScheduleID, @ADateTime) = 0 -- Okay we havent found an exception
          BEGIN
            IF CHARINDEX(@CurrentWeekDay, '23456') > 0 -- monday to friday is '23456'
            BEGIN
              SELECT @FoundID = ISNULL(ScheduleID, 0), @Apax = ISNULL(Pax, 0), @AOpeningDateTime = Start, @ACloseDateTime = Finish FROM Schedule (NOLOCK) WHERE ParentID = @ScheduleID AND EventType = 4 AND  CONVERT(VARCHAR(8), Finish, 112) = CONVERT(VARCHAR(8), @ADateTime, 112) AND CONVERT(VARCHAR(8), Start, 112) = CONVERT(VARCHAR(8), @ADateTime, 112)
              SET @FoundID = ISNULL(@FoundID, 0 )
              IF @FoundID = 0
              BEGIN
                SET @APax = ISNULL(@Pax, 0)
                SET @AOpeningDateTime =  @ADateTime + CONVERT(DATETIME,CONVERT(CHAR(5),@Start,108),108) 
                SET @ACloseDateTime =  @ADateTime + CONVERT(DATETIME,CONVERT(CHAR(5),@Finish,108),108) 
              END
              SET @Found = 1
            END
          END 
        END 
      END
      ELSE IF @Recurrence = 1 AND CHARINDEX(@CurrentWeekDay, @OccurDays) > 0 -- Okay the parent series is weekly and has currentweekday in its Weekday pattern. Go look for possible exceptions (EventType 3) or possible customizations (EventType 4)
      BEGIN
        IF dbo.IsRecurringDayInException (@ScheduleID, @ADateTime) = 0
        BEGIN
          SELECT @FoundID = ISNULL(ScheduleID, 0), @Apax = ISNULL(Pax, 0), @AOpeningDateTime = Start, @ACloseDateTime = Finish FROM Schedule (NOLOCK) WHERE ParentID = @ScheduleID AND EventType = 4 AND  CONVERT(VARCHAR(8), Finish, 112) = CONVERT(VARCHAR(8), @ADateTime, 112) AND CONVERT(VARCHAR(8), Start, 112) = CONVERT(VARCHAR(8), @ADateTime, 112)
          SET @FoundID = ISNULL(@FoundID, 0 )
          IF @FoundID = 0
          BEGIN
            SET @APax = ISNULL(@Pax, 0)
            SET @AOpeningDateTime = @ADateTime + CONVERT(DATETIME,CONVERT(CHAR(5),@Start,108),108) 
            SET @ACloseDateTime =  @ADateTime + CONVERT(DATETIME,CONVERT(CHAR(5),@Finish,108),108) 
          END
          SET @Found = 1
        END
      END
    END
    
    IF @Found = 1  -- Only add schedule if found.
    BEGIN
      INSERT INTO #SchedulerExpressData 
        (Found,PAX,OpeningDateTime,CloseDateTime,IntervalBetweenArrivals,DefaultTableBookingMinutes,TableType) 
      VALUES
        (@Found,@APAX, @AOpeningDateTime,@ACloseDateTime,@AIntervalBetweenArrivals,@ADefaultTableBookingMinutes,@TableType)
    END
  
    FETCH NEXT FROM ScheduleCur INTO @ScheduleID, @ParentID, @EventType, @Start, @Finish, @Options
      , @Caption, @RecurrenceIndex, @ResourceID, @ActualStart, @ActualFinish, @ScheduleType, @Pax, @RecurrenceInfoTranslated, @TableType
  END

  CLOSE ScheduleCur
  DEALLOCATE ScheduleCur
  
  SELECT * from #SchedulerExpressData
  ORDER BY OpeningDateTime
  
  DROP TABLE #SchedulerExpressData

GO

