avanrossum

Imminent Events List

Mar 27th, 2019
247
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 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
  2. IF (@UserID = 0 AND @ShowForGuests = 1) OR (@UserID > 0)
  3. BEGIN
  4.   IF (@Latitude != 0 AND @Longitude != 0)
  5.   BEGIN
  6.     DECLARE @EVUserPoint GEOGRAPHY = GEOGRAPHY::Point(@Latitude, @Longitude, 4326)
  7.         ,@EVFutureEventQty INT = 3                              -- #ATT the number of upcoming events to load if a user isn't registered for anything.
  8.         ,@EVGeofenceDistance FLOAT = .5                         -- #ATT distance from the campus lat/long that you consider "on campus" (miles)
  9.         ,@EVCampusLatitude DECIMAL(12,9) = 34.0065         -- #ATT This is your Campus Latitude
  10.         ,@EVCampusLongitude DECIMAL(12,9) = -84.1514      -- #ATT This is your Campus Longitude
  11.         ,@EVCampusPoint GEOGRAPHY
  12.         ,@EVUserDistance FLOAT
  13.         ,@EVUserParticipantID INT
  14.         ,@EVEventID INT
  15.         ,@EVEventDate DATETIME
  16.         ,@EVEventStatusMessage NVARCHAR(150)
  17.         ,@EVEventTitle NVARCHAR(150)
  18.         ,@EVNow DATETIME = GETDATE()
  19.         ,@EVWelcomeMessage NVARCHAR(75) = 'Welcome to PureHeart';  -- #ATT Change (church) to your church name!
  20.  
  21.     SET @EVCampusPoint = GEOGRAPHY::Point(@EVCampusLatitude, @EVCampusLongitude, 4326);
  22.     SET @EVUserDistance = (@EVUserPoint.STDistance(@EVCampusPoint)/1000)*0.62137119
  23.  
  24.     IF (@EVUserDistance <= @EVGeofenceDistance)
  25.     BEGIN
  26.  
  27.       SELECT @EVUserParticipantID = Participant_Record FROM Contacts WHERE Contacts.User_Account = @UserID
  28.  
  29.  
  30.       SELECT TOP 1 @EVEventID = E.Event_ID
  31.            ,@EVEventTitle = E.Event_Title
  32.        ,@EVEventDate = E.Event_Start_Date
  33.       FROM Events E
  34.       INNER JOIN Event_Participants EP
  35.         ON EP.Participant_ID = @EVUserParticipantID
  36.       WHERE @EVNow BETWEEN DATEADD(MINUTE, -30, E.Event_Start_Date) AND DATEADD(MINUTE, 10, E.Event_Start_Date)
  37.       AND EP.Participation_Status_ID IN (2, 4)
  38.       AND E._Approved = 1
  39.       ORDER BY E.Event_Start_Date DESC
  40.  
  41.  
  42.       IF @EVEventID IS NOT NULL
  43.       BEGIN
  44.     IF DATEDIFF(mi, @EVNow, @EVEventDate) <= 0
  45.       SET @EVEventStatusMessage = 'Your event "' +@EVEventTitle + '" has started';
  46.     ELSE
  47.       SET @EVEventStatusMessage = 'Your event "' + @EVEventTitle + '" starts in ' + CAST(DATEDIFF(mi, @EVNow, @EVEventDate) AS VARCHAR) + ' minutes!';
  48.         INSERT INTO @Items (ID, Parent_ID, [Type], [Data], [URL]) VALUES
  49.           ('curr-event-card', NULL, 'card', NULL, NULL)
  50.           ,('curr-event-header', 'curr-event-card', 'heading', @EVWelcomeMessage, NULL)
  51.           ,('curr-event-body', 'curr-event-card', 'html', @EVEventStatusMessage, NULL)
  52.           ,('curr-event-spc1', 'curr-event-card', 'vertical-spacer', NULL, NULL)
  53.           ,('curr-event-button', 'curr-event-card', 'button', 'Event Details', '[[APP_PREFIX]]event/' + CAST(@EVEventID AS VARCHAR) )
  54.       END
  55.       ELSE
  56.       BEGIN
  57.         DECLARE @EVCursor CURSOR
  58.             ,@EVTempEventID INT
  59.             ,@EVVCTempEventID VARCHAR(12)
  60.             ,@EVTempEventTitle NVARCHAR(150)
  61.             ,@EVStartDate DATETIME
  62.             ,@EVInitial BIT = 1
  63.             ,@EVTempStartText NVARCHAR(150)
  64.             ,@EVTotalEvents INT;
  65.  
  66.         SET @EVTotalEvents = (SELECT COUNT(Event_ID) FROM Events E
  67.                     WHERE E.Visibility_Level_ID >= 4
  68.                     AND @EVNow BETWEEN DATEADD(MINUTE, -30, E.Event_Start_Date)
  69.                       AND E.Event_Start_Date
  70.                     AND E._Approved = 1
  71.                     AND E.Featured_On_Calendar = 1
  72.                     );
  73.  
  74.         IF  @EVTotalEvents > 0
  75.         BEGIN
  76.           INSERT INTO @Items (ID, Parent_ID, [Type], [Data], [URL]) VALUES
  77.             ('curr-event-group', NULL, 'group', NULL, NULL)
  78.             ,('curr-event-card', 'curr-event-group', 'card', NULL, NULL)
  79.             ,('curr-event-header', 'curr-event-card', 'heading', @EVWelcomeMessage, NULL)
  80.             ,('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!
  81.         END
  82.  
  83.         SET @EVCursor = CURSOR FOR
  84.         SELECT TOP (@EVFutureEventQty) E.Event_Title, E.Event_ID, E.Event_Start_Date FROM Events E WHERE E.Visibility_Level_ID >= 4
  85.         AND  @EVNow BETWEEN DATEADD(MINUTE, -30, E.Event_Start_Date) AND E.Event_Start_Date
  86.         AND E._Approved = 1
  87.         AND E.Featured_On_Calendar = 1
  88.  
  89.         OPEN @EVCursor
  90.         FETCH NEXT
  91.         FROM @EVCursor INTO @EVTempEventTitle, @EVTempEventID, @EVStartDate
  92.         WHILE @@FETCH_STATUS = 0
  93.         BEGIN
  94.  
  95.           SET @EVVCTempEventID = CAST(@EVTempEventID AS VARCHAR);
  96.           IF DATEDIFF(mi, @EVNow, @EVStartDate) = 0
  97.             SET @EVTempStartText = 'Starting now!';
  98.           ELSE
  99.             SET @EVTempStartText = 'Starting in ' + CAST(DATEDIFF(mi, @EVNow, @EVStartDate) AS VARCHAR) + ' minutes!';
  100.  
  101.           INSERT INTO @Items (ID, Parent_ID, [Type], [Data], [URL]) VALUES
  102.             ('curr-event-card-' + @EVVCTempEventID, 'curr-event-group', 'card', NULL, '[[APP_PREFIX]]event/' + @EVVCTempEventID)
  103.             ,('curr-event-arrow-' + @EVVCTempEventID, 'curr-event-card-' + @EVVCTempEventID, 'arrow-action', NULL, NULL)
  104.             ,('curr-event-event-heading-' + @EVVCTempEventID, 'curr-event-arrow-' + @EVVCTempEventID, 'heading', @EVTempEventTitle, NULL)
  105.             ,('curr-event-event-time-' + @EVVCTempEventID, 'curr-event-card-' + @EVVCTempEventID, 'html', @EVTempStartText, NULL);
  106.  
  107.  
  108.           FETCH NEXT
  109.           FROM @EVCursor INTO @EVTempEventTitle, @EVTempEventID, @EVStartDate
  110.         END
  111.         CLOSE @EVCursor;
  112.         DEALLOCATE @EVCursor;
  113.  
  114.         IF @EVTotalEvents > 0
  115.         INSERT INTO @Items(ID, Parent_ID, [Type], [Data], [URL]) VALUES
  116.           ('curr-event-end-card', 'curr-event-group','card', NULL, NULL)
  117.           ,('curr-event-button', 'curr-event-end-card', 'button', 'See All Events', '[[APP_PREFIX]]app/events');
  118.  
  119.       END
  120.     END
  121.   END
  122. END
RAW Paste Data