Imminent Events List

DECLARE @ShowForGuests BIT = 1;  -- #ATT if you want to show these events to guest users, set to 1. For logged in users only, set to 0
IF (@UserID = 0 AND @ShowForGuests = 1) OR (@UserID > 0)
BEGIN
  IF (@Latitude != 0 AND @Longitude != 0)
  BEGIN
    DECLARE @EVUserPoint GEOGRAPHY = GEOGRAPHY::Point(@Latitude, @Longitude, 4326)
        ,@EVFutureEventQty INT = 3                              -- #ATT the number of upcoming events to load if a user isn't registered for anything.
        ,@EVGeofenceDistance FLOAT = .5                         -- #ATT distance from the campus lat/long that you consider "on campus" (miles)
        ,@EVCampusLatitude DECIMAL(12,9) = 34.0065         -- #ATT This is your Campus Latitude
        ,@EVCampusLongitude DECIMAL(12,9) = -84.1514      -- #ATT This is your Campus Longitude
        ,@EVCampusPoint GEOGRAPHY
        ,@EVUserDistance FLOAT
        ,@EVUserParticipantID INT
        ,@EVEventID INT
        ,@EVEventDate DATETIME
        ,@EVEventStatusMessage NVARCHAR(150)
        ,@EVEventTitle NVARCHAR(150)
        ,@EVNow DATETIME = GETDATE()
        ,@EVWelcomeMessage NVARCHAR(75) = 'Welcome to PureHeart';  -- #ATT Change (church) to your church name!

    SET @EVCampusPoint = GEOGRAPHY::Point(@EVCampusLatitude, @EVCampusLongitude, 4326);
    SET @EVUserDistance = (@EVUserPoint.STDistance(@EVCampusPoint)/1000)*0.62137119

    IF (@EVUserDistance <= @EVGeofenceDistance)
    BEGIN

      SELECT @EVUserParticipantID = Participant_Record FROM Contacts WHERE Contacts.User_Account = @UserID


      SELECT TOP 1 @EVEventID = E.Event_ID
           ,@EVEventTitle = E.Event_Title
       ,@EVEventDate = E.Event_Start_Date
      FROM Events E
      INNER JOIN Event_Participants EP
        ON EP.Participant_ID = @EVUserParticipantID
      WHERE @EVNow BETWEEN DATEADD(MINUTE, -30, E.Event_Start_Date) AND DATEADD(MINUTE, 10, E.Event_Start_Date)
      AND EP.Participation_Status_ID IN (2, 4)
      AND E._Approved = 1
      ORDER BY E.Event_Start_Date DESC


      IF @EVEventID IS NOT NULL
      BEGIN
    IF DATEDIFF(mi, @EVNow, @EVEventDate) <= 0
      SET @EVEventStatusMessage = 'Your event "' +@EVEventTitle + '" has started';
    ELSE
      SET @EVEventStatusMessage = 'Your event "' + @EVEventTitle + '" starts in ' + CAST(DATEDIFF(mi, @EVNow, @EVEventDate) AS VARCHAR) + ' minutes!';
        INSERT INTO @Items (ID, Parent_ID, [Type], [Data], [URL]) VALUES
          ('curr-event-card', NULL, 'card', NULL, NULL)
          ,('curr-event-header', 'curr-event-card', 'heading', @EVWelcomeMessage, NULL)
          ,('curr-event-body', 'curr-event-card', 'html', @EVEventStatusMessage, NULL)
          ,('curr-event-spc1', 'curr-event-card', 'vertical-spacer', NULL, NULL)
          ,('curr-event-button', 'curr-event-card', 'button', 'Event Details', '[[APP_PREFIX]]event/' + CAST(@EVEventID AS VARCHAR) )
      END
      ELSE
      BEGIN
        DECLARE @EVCursor CURSOR
            ,@EVTempEventID INT
            ,@EVVCTempEventID VARCHAR(12)
            ,@EVTempEventTitle NVARCHAR(150)
            ,@EVStartDate DATETIME
            ,@EVInitial BIT = 1
            ,@EVTempStartText NVARCHAR(150)
            ,@EVTotalEvents INT;

        SET @EVTotalEvents = (SELECT COUNT(Event_ID) FROM Events E
                    WHERE E.Visibility_Level_ID >= 4
                    AND @EVNow BETWEEN DATEADD(MINUTE, -30, E.Event_Start_Date)
                      AND E.Event_Start_Date
                    AND E._Approved = 1
                    AND E.Featured_On_Calendar = 1
                    );

        IF  @EVTotalEvents > 0
        BEGIN
          INSERT INTO @Items (ID, Parent_ID, [Type], [Data], [URL]) VALUES
            ('curr-event-group', NULL, 'group', NULL, NULL)
            ,('curr-event-card', 'curr-event-group', 'card', NULL, NULL)
            ,('curr-event-header', 'curr-event-card', 'heading', @EVWelcomeMessage, NULL)
            ,('curr-event-body', 'curr-event-card', 'html', 'There are events starting soon!', NULL); -- #ATT If you want to change the message, here's where you do it!
        END

        SET @EVCursor = CURSOR FOR
        SELECT TOP (@EVFutureEventQty) E.Event_Title, E.Event_ID, E.Event_Start_Date FROM Events E WHERE E.Visibility_Level_ID >= 4
        AND  @EVNow BETWEEN DATEADD(MINUTE, -30, E.Event_Start_Date) AND E.Event_Start_Date
        AND E._Approved = 1
        AND E.Featured_On_Calendar = 1

        OPEN @EVCursor
        FETCH NEXT
        FROM @EVCursor INTO @EVTempEventTitle, @EVTempEventID, @EVStartDate
        WHILE @@FETCH_STATUS = 0
        BEGIN

          SET @EVVCTempEventID = CAST(@EVTempEventID AS VARCHAR);
          IF DATEDIFF(mi, @EVNow, @EVStartDate) = 0
            SET @EVTempStartText = 'Starting now!';
          ELSE
            SET @EVTempStartText = 'Starting in ' + CAST(DATEDIFF(mi, @EVNow, @EVStartDate) AS VARCHAR) + ' minutes!';

          INSERT INTO @Items (ID, Parent_ID, [Type], [Data], [URL]) VALUES
            ('curr-event-card-' + @EVVCTempEventID, 'curr-event-group', 'card', NULL, '[[APP_PREFIX]]event/' + @EVVCTempEventID)
            ,('curr-event-arrow-' + @EVVCTempEventID, 'curr-event-card-' + @EVVCTempEventID, 'arrow-action', NULL, NULL)
            ,('curr-event-event-heading-' + @EVVCTempEventID, 'curr-event-arrow-' + @EVVCTempEventID, 'heading', @EVTempEventTitle, NULL)
            ,('curr-event-event-time-' + @EVVCTempEventID, 'curr-event-card-' + @EVVCTempEventID, 'html', @EVTempStartText, NULL);


          FETCH NEXT
          FROM @EVCursor INTO @EVTempEventTitle, @EVTempEventID, @EVStartDate
        END
        CLOSE @EVCursor;
        DEALLOCATE @EVCursor;

        IF @EVTotalEvents > 0
        INSERT INTO @Items(ID, Parent_ID, [Type], [Data], [URL]) VALUES
          ('curr-event-end-card', 'curr-event-group','card', NULL, NULL)
          ,('curr-event-button', 'curr-event-end-card', 'button', 'See All Events', '[[APP_PREFIX]]app/events');

      END
    END
  END
END