CREATE PROCEDURE [dbo].[GetSchedulerExpressDataWithPAX2] @ADateTime DATETIME, @AResourceID INT,  @AFound BIT OUTPUT, @APAX INT OUTPUT, @AOpeningDateTime DATETIME OUTPUT, @ACloseDateTime DATETIME OUTPUT, @APAX2 INT OUTPUT  AS

--Created AV 2009 february. Used with The Express scheduler grid
--Last Changed: AV 2009 0218. some bugfixes
--Last Changed: AV 2009 0217. Added call to udf isRecurringDayInException
--Last Changed: RN 2009 0706. @CurrentWeekDay calculation fixed.
--Last Changed: RN 2014 0403. Copy of GetSchedulerExpressData but with an extra PAX field as output parameter
--SG 2016-11-08: Error corrected in 8.1 missing in 8.3: Fixed wrong pax count on Climbing availability
--SG 2023-03-14: Changed the way time is removed from DateTime

--TcxRecurrence = (cxreDaily, cxreWeekly, cxreMonthly, cxreYearly);
--TcxEventType = (etNone, etPattern, etOccurrence, etException, etCustom);

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

  SET @Found = 0
  SET @AFound = 0
  SET @APax = 0 
  SET @APAX2 = 0
  SET @AOpeningDateTime = 0
  SET @ACloseDateTime = 0 
  SET @ADateTime = CONVERT(DATETIME,CONVERT(DATE, @ADateTime)) --Remove time-part

  SELECT @CurrentWeekDay = CONVERT(CHAR(1), DATEPART(WEEKDAY, @ADateTime)-1)

  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), ISNULL(Pax2, 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, @Pax2, @RecurrenceInfoTranslated  
 
  WHILE(@@Fetch_status = 0) AND @Found = 0
  BEGIN
    
	--print '@EventType=' + convert(varchar, @EventType) 
	
	IF @EventType = 0 -- Just A normal one (None Recurring)
    BEGIN
      SET @APax = ISNULL(@Pax, 0)
      SET @APAX2 = ISNULL(@Pax2, 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 'Pax2: '+ convert(varchar(3), @Pax2)
      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 @APAX2 = ISNULL(@Pax2, 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 @APAX2 = ISNULL(@Pax2, 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 @APAX2 = ISNULL(@Pax2, 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

    FETCH NEXT FROM ScheduleCur INTO @ScheduleID, @ParentID, @EventType, @Start, @Finish, @Options, @Caption, @RecurrenceIndex, @ResourceID, @ActualStart, @ActualFinish, @Type, @Pax, @Pax2, @RecurrenceInfoTranslated  
  END

  SET @AFound = @Found

  CLOSE ScheduleCur
  DEALLOCATE ScheduleCur
  