CREATE PROCEDURE [dbo].[GetSchedulerExpressData3] @ADateTime DATETIME, @AResourceID INT AS

--Created SG 2018-03 as a copy of GetSchedulerExpressData. 
--  Called from OnlineAvailableHallTimeSlots
--  Populates #SchedulerExpressData
--  Supports multiple schedules per day


--TcxRecurrence = (cxreDaily, cxreWeekly, cxreMonthly, cxreYearly);
--TcxEventType = (etNone, etPattern, etOccurrence, etException, etCustom); (EventType)
--TcxDayType = (cxdtDay, cxdtEveryDay, cxdtWeekDay, cxdtWeekEndDay, cxdtSunday, cxdtMonday, cxdtTuesday, cxdtWednesday, cxdtThursday, cxdtFriday, cxdtSaturday);
/*
Explanation of the above type. It is mostly used for the parent in the reoccurence.
cxdtEveryDay : If we have chosen daily and every day
cxdtWeekDay : if we have chosen daily. And this one means work days: Monday to friday.
*/

  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 @ResourceID INT
  DECLARE @ActualStart DATETIME
  DECLARE @ActualFinish DATETIME
  DECLARE @Type INT
  DECLARE @Pax INT
  DECLARE @RecurrenceInfoTranslated VARCHAR(100)

-- 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

  -- Temporary table which contains found open schedules.
  --IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'#SchedulerExpressData')
  --BEGIN
  --  CREATE TABLE #SchedulerExpressData (
  --    Pax int
  --    ,OpeningDateTime datetime
  --    ,CloseDateTime datetime
	 -- )

  --  Print 'CREATE TABLE #SchedulerExpressData';
  --END;

  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  ScheduleID, ParentID, EventType, Start, Finish, Options, Caption, RecurrenceIndex, ResourceID, CONVERT(DATETIME, ActualStart), CONVERT(DATETIME, ActualFinish), Type, ISNULL(Pax, 0), RecurrenceInfoTranslated FROM Schedule WITH (NOLOCK)
  WHERE ResourceID = @AResourceID
  AND
  (
  (EventType = 0 AND Finish >= @ADateTime AND CONVERT(VARCHAR(8),Start,112) <= CONVERT(VARCHAR(8), @ADateTime, 112) ) -- Just A normal one (None Recurring)
  OR
  (EventType = 1 AND (CONVERT(DATETIME, ActualFinish) -2 >= @ADateTime) AND (CONVERT(DATETIME, 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, @Type, @Pax, @RecurrenceInfoTranslated  
 
  WHILE(@@Fetch_status = 0) 
  BEGIN
    Set @Found = 0;
	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 WITH (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 WITH (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 WITH (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 
        (PAX, OpeningDateTime, CloseDateTime) 
      VALUES
        (@APAX, @AOpeningDateTime, @ACloseDateTime)
    END
  
    FETCH NEXT FROM ScheduleCur INTO @ScheduleID, @ParentID, @EventType, @Start, @Finish, @Options, @Caption, @RecurrenceIndex, @ResourceID, @ActualStart, @ActualFinish, @Type, @Pax, @RecurrenceInfoTranslated  
  END

  CLOSE ScheduleCur
  DEALLOCATE ScheduleCur

  --DROP TABLE #SchedulerExpressData used in [OnlineAvailableHallTimeSlots]