avanrossum

Imminent Events List

Mar 27th, 2019
321
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×