cust_CCIW_online_registrations_summary_hfsp

USE [ArenaDB]
GO
/****** Object:  StoredProcedure [dbo].[cust_CCIW_online_registrations_summary_hfsp]    Script Date: 7/29/2019 10:52:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[cust_CCIW_online_registrations_summary_hfsp]
@OrganizationId INT = 1, -- this param name is specific to the HTML From SPROC Module
@NewRegistrantsLookback INT = 7, -- number of days range to create sum of new registrants for
@FilterOnOwner bit = 0,
@CurrentUser  INT, -- this param name is specific to the HTML From SPROC Module
@FilterOnStatus INT = -1,
@FilterOnEventType INT = -1
AS


-- Original Author: Erik Peterson
-- Source: http://community.shelbysystems.com/arena/f/arena-administration/14861/how-to-hyperlink-event-tag-name/42256#42256
-- Updates By: Nick Hilbelink, Tony Visconti
-- 6/25/19 - changing inputs to bits from varchar, fixed event fees to show quantity and fee $ correctly
-- 7/29/19 - corrected status for subevents which were not displaying correctly

-- Arena will pass in a '' value when the value is not specified
IF @FilterOnStatus = '' SET @FilterOnStatus = -1
IF @FilterOnEventType = '' SET @FilterOnEventType = -1

DECLARE @EmptyDate DATETIME = '1900-01-01'

DECLARE @html1 varchar(MAX) = '
<style>
.dot
{
  height: 10px;
  width: 10px;
  background-color: #bbb;
  border-radius: 50%;
  display: inline-block;
}
.registration_block
{
  */border: 1px solid black;*/
}

.registration_block img
{
  display: block;
  margin-left: auto;
  margin-right: auto;
}

</style>
<table style="width: 100%;">';

DECLARE @html3 varchar(MAX) = '', @html4 varchar(MAX) = '</table>';

--I found out that when you don't specify the parameter in the url HTML From Stored Procedure is sending in 0 ints and bits and a '' for varchar
DECLARE @html2 varchar(MAX) ='';
DECLARE @PortalDomain varchar(200) = (SELECT DEFAULT_DOMAIN from port_portal where portal_id = 2); --my college church portal


WITH registration_summary AS (

SELECT CASE
         WHEN EP.image_id = '' THEN ''
         ELSE dbo.cust_NHLC_funct_image_blob(EP.image_id,90,90)
         END AS widget_image,
       '/default.aspx?page=376&profile=' + CONVERT(varchar(8),EP.profile_id) as event_edit_link,
       CASE
         WHEN EP.[end] = @EmptyDate THEN ''
       ELSE CASE
              WHEN DATEDIFF(day,EP.start,EP.[end]) = 0
              THEN (CONVERT(varchar(20), EP.start, 100))
              ELSE (CONVERT(varchar(20),EP.start, 107) + ' - ' + CONVERT(varchar(20), EP.[end], 107))
              END
       END as event_start_end,
       (SELECT COUNT(person_id) FROM core_profile_member TM
        WHERE TM.profile_id = EP.profile_id
          AND TM.status_luid = 255) -- 255 = Status Connected
       AS registrant_count_connected,
       (SELECT COUNT(person_id)
          FROM core_profile_member TM
         WHERE TM.profile_id = EP.profile_id
           AND TM.status_luid = 255
           AND TM.date_created > getdate() - @NewRegistrantsLookback)
       As registrant_count_connected_new,
       EP.image_id,EP.[end],EP.start,EP.profile_id,EP.registration_end,EP.registration_start,EP.approved_date,
       CASE
            WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.registration_end ELSE EP.registration_end END) < getdate() THEN 4 -- 'Expired'
            WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.registration_start ELSE EP.registration_start END) > getdate() THEN 3 -- 'Not Open Yet'
            WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.approved_date ELSE EP.approved_date END) = @EmptyDate THEN 2 -- 'Approval Needed'
            ELSE 1 -- Live
       END AS reg_status,
       EP.registration_enabled,
       EP.registration_maximum_individuals,
       EP.registration_maximum_individuals -
         (SELECT COUNT(person_id) FROM core_profile_member TM WHERE TM.profile_id = EP.profile_id AND TM.status_luid = 255)
         AS SPOTS_REMAINING,
       SUBSTRING( -- select statement below will start with a <br> tag, hence the substring
         (SELECT DISTINCT '<br>' + F.title + ': $' + CONVERT(varchar(9),F.amount) + ' / ' + ISNULL(CONVERT(varchar(3),SUM(RF.quantity) OVER (PARTITION BY F.fee_id)),'0') + ' Purchased'  -- lists additional fees and the quantity for each
            FROM evnt_event_profile ET
            LEFT JOIN evnt_fee F ON F.profile_id = ET.profile_id
            LEFT JOIN evnt_registrant_fee RF ON RF.fee_id = F.fee_id
           WHERE ET.profile_id = EP.profile_id
           --GROUP BY F.title
             FOR XML PATH ( '' ), type).value('.','varchar(max)'), -- Performs an XQuery against the XML and returns a value of SQL type
                                                                   -- The dot points to the current element
                                                                   -- select statement without .value function applied: &lt;br&gt;Suggested Donation for Spring: 52&lt;br&gt;P
                                                                   -- with .value applied: <br>Suggested Donation for Spring: 52<br>P
                                                                   -- http://blogs.lobsterpot.com.au/2010/04/15/handling-special-characters-with-for-xml-path/
       5,999999) as additional_fees,
        ep.type_luid,
       dbo.cust_AJV_funct_luid_to_value(ep.type_luid) as event_type,
        isSubEvent = EP_Parent.subevents_enabled
  FROM evnt_event_profile EP
  JOIN core_profile T ON T.profile_id = EP.profile_id
  LEFT JOIN evnt_event_profile EP_Parent ON EP_Parent.profile_id = T.parent_profile_id
 WHERE (@FilterOnOwner = 0 OR T.owner_id = @CurrentUser)
   AND EP.registration_enabled = 1
   AND EP.[end] > getdate() -7 -- only include events for 7 days after they have passed
    AND (@FilterOnEventType = -1  OR ep.type_luid = @FilterOnEventType)
    AND (@FilterOnStatus = -1 OR
            (CASE
                WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.registration_end ELSE EP.registration_end END) < getdate() THEN 4 -- 'Expired'
                WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.registration_start ELSE EP.registration_start END) > getdate() THEN 3 -- 'Not Open Yet'
                WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.approved_date ELSE EP.approved_date END) = @EmptyDate THEN 2 -- 'Approval Needed'
                ELSE 1 -- Live
            END) = @FilterOnStatus)
)


--select * from registration_summary

SELECT @html3 = SUBSTRING((SELECT + '
<tbody class="registration_block">
  <tr>
    <td rowspan="' + CASE additional_fees WHEN '' then '4' ELSE '5' END +'">' + EW.widget_image +
    '</td>
    <td colspan="1"><a href="' + event_edit_link + '"target="_blank">' + T.profile_name + '</a>
         ('+CASE reg_status
            WHEN 4 THEN '<span class="dot" style="background-color:#B62626"></span> Expired'
         WHEN 3 THEN '<span class="dot"></span> Not Open Yet'
         WHEN 2 THEN '<span class="dot" style="background-color:#E8D913"></span> Approval Needed'
         ELSE '<span class="dot" style="background-color:#26B639"></span><strong><a href="https://' + @PortalDomain + '/default.aspx?page=3389&event='+CONVERT(VARCHAR(10),CASE WHEN isSubEvent = 1 THEN T.parent_profile_id ELSE T.profile_id END)+'"> Live</a></strong>' END+')
    <td>
  </tr>
  <tr>
    <td colspan="2">' + event_start_end + '<br></td>
  </tr>
  <tr>
    <td colspan="2">Registered: ' + CONVERT(varchar(4),registrant_count_connected) + ' / ' + CONVERT(varchar(4),registration_maximum_individuals)
    + CASE WHEN registrant_count_connected_new>0 THEN '<strong><span> (' + CONVERT(varchar(4),registrant_count_connected_new) + ' new!)</span></strong>' ELSE '' END
    + '<br>
  </td>
  </tr>
  <tr>
  <td colspan="2"> Spots Remaining: '+CASE registration_maximum_individuals WHEN 0 THEN 'NA' ELSE CONVERT(varchar(10),SPOTS_REMAINING) END+'</td>
  </tr>
  '
    + CASE additional_fees
        WHEN '' then ''
        ELSE
  '<tr>
     <td colspan="2"> Additional Fees: <br><small>' + additional_fees +'</small>
    </td>
  </tr>'
END +
'</tbody><tr><td><br></td></tr>'
    AS [html]
  FROM registration_summary EW
  JOIN core_profile T ON T.profile_id = EW.profile_id
 ORDER BY EW.reg_status, EW.start
   FOR XML PATH ( '' ), type).value('.','varchar(max)'),0,999999)

IF @html3 is null SET @html3 = '';

DECLARE @intial_results_count smallint =
(SELECT count(*)
FROM (SELECT EP.*,
        CASE
            WHEN (CASE WHEN EP_Parent.subevents_enabled = 1 THEN EP_Parent.registration_end ELSE EP.registration_end END) < getdate() THEN 4 -- 'Expired'
            WHEN (CASE WHEN EP_Parent.subevents_enabled = 1 THEN EP_Parent.registration_start ELSE EP.registration_start END) > getdate() THEN 3 -- 'Not Open Yet'
            WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.approved_date ELSE EP.approved_date END) = @EmptyDate THEN 2 -- 'Approval Needed'
            ELSE 1 -- Live
        END AS reg_status
    FROM evnt_event_profile EP
    JOIN core_profile T ON T.profile_id = EP.profile_id
    LEFT JOIN evnt_event_profile EP_Parent ON EP_Parent.profile_id = T.parent_profile_id
    ) EP
JOIN core_profile T ON T.profile_id = EP.profile_id
LEFT JOIN evnt_event_profile EP_Parent ON EP_Parent.profile_id = T.parent_profile_id
WHERE (@FilterOnOwner = 0 OR T.owner_id = @CurrentUser)
    AND (@FilterOnStatus = -1 OR
            (CASE
            WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.registration_end ELSE EP.registration_end END) < getdate() THEN 4 -- 'Expired'
            WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.registration_start ELSE EP.registration_start END) > getdate() THEN 3 -- 'Not Open Yet'
            WHEN (CASE WHEN EP_Parent.subevents_enabled  = 1 THEN EP_Parent.approved_date ELSE EP.approved_date END) = @EmptyDate THEN 2 -- 'Approval Needed'
            ELSE 1 -- Live
            END) = @FilterOnStatus)
    AND (@FilterOnEventType = -1 OR ep.type_luid = @FilterOnEventType)
    AND EP.registration_enabled = 1
    AND EP.[end] > getdate() -7)

SET @html2 = '<p>'+Convert(varchar(max),@intial_results_count) + ' Results Found Based On Filters</p>';


SELECT @html1 + @html2 + @html3 + @html4 AS html
--SELECT @html1 + @html2 + @html3 AS html