USP_FAF_PARTICIPANT_REPORT

CREATE PROCEDURE [dbo].[USP_FAF_PARTICIPANT_REPORT]
(
    @EVENTID uniqueidentifier,
    @REGISTRATIONOPTION nvarchar(50) = null,
    @REGISTRATIONFEE money = null,
    @WEBURL nvarchar(1200) = '',
    @WEBURLGROUP nvarchar(1200) = '',
    @STATUS int = 2 , -- 0 = Active , 1= Canceled, 2= 'All'

  @REPORTUSERID nvarchar(128) = null,
  @ALTREPORTUSERID nvarchar(128) = null,
  @SITEFILTERMODE tinyint = 0,     
  @SITESSELECTED nvarchar(max) = null 
)
AS
BEGIN
    SET NOCOUNT ON;

-- BEGIN SP LOGIC

set nocount on


declare @CURRENTAPPUSERID as uniqueidentifier 
--set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETFROMLOGIN(@REPORTUSERID);

 set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

declare @ISADMIN bit;
  declare @SITESGRANTED table(
    SITEID uniqueidentifier 
  )
  insert into @SITESGRANTED
  select SITEID
  from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'7a6794e2-6287-4fe6-ae85-0b660d640995', 21)
  where SITEID is not null

  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

if @REGISTRATIONOPTION = 'All' set @REGISTRATIONOPTION = null

-- get previous linked event

declare @EventIDP as uniqueidentifier 
set @EventIDP = (select PRIORYEAREVENTID from EVENTEXTENSION where EVENTID = @EventID);

declare @REGSREPORT table
(
    RegistrantID uniqueidentifier,
    LASTNAME nvarchar(200),
    FIRSTNAME nvarchar(200),
    AddressBlock nvarchar(512),
    TOTALRAISED money,
    FUNDRAISINGGOAL money,
    TARGETFUNDRAISINGGOAL money,
    RegisteredDate datetime,
    RegistrationFee money,
    GroupName nvarchar(512),
    RegistrationOption nvarchar(50),
    ReturnedParticipant int,
    Benefits nvarchar(512),
    GroupType int,
    RoleCode int,
    ISCANCELLED int
)

declare @registrantsbyevent table
(
    ID uniqueidentifier,
    DATEADDED datetime,
    CONSTITUENTID uniqueidentifier,
    EVENTID uniqueidentifier,
    ROLE varchar(17),
    TEAMFUNDRAISINGTEAMID uniqueidentifier,
    ROLECODE int,
  ISCANCELLED bit
)

insert into @registrantsbyevent(ID, DATEADDED, CONSTITUENTID, EVENTID, ROLE, TEAMFUNDRAISINGTEAMID, ROLECODE, ISCANCELLED)
select R.id, R.dateadded, R.CONSTITUENTID, R.EVENTID, AG.Role, AG.TEAMFUNDRAISINGTEAMID, AG.RoleCode, R.ISCANCELLED 
from dbo.REGISTRANT R
JOIN dbo.UFN_REGISTRANT_GETFAFROLE(@eventid, null) AG ON R.ID = AG.REGISTRANTID  
where R.EVENTID = @EventID

insert into @REGSREPORT
--  main registrant detail query 

select 
    R.ID,
    C.KEYNAME,
    C.FIRSTNAME,
    dbo.UFN_BUILDFULLADDRESS(addr.ID, addr.ADDRESSBLOCK, addr.CITY, addr.STATEID, addr.POSTCODE, addr.COUNTRYID) As AddressBlock,
    -- case when TX.TypeCode = 3 then T.Goal else RX.FUNDRAISINGGOAL end as FUNDRAISINGGOAL,  

    REVTOTAL.AMOUNTRAISED as TOTALRAISED,    
    RX.FUNDRAISINGGOAL,
    -- case when TX.TypeCode = 3 then TX.TARGETFUNDRAISINGGOAL else RX.TARGETFUNDRAISINGGOAL end as TARGETFUNDRAISINGGOAL, 

    RX.TARGETFUNDRAISINGGOAL,
    R.DATEADDED as RegisteredDate,
    RR.AMOUNT as RegistrationFee,
    T.Name AS GroupName,
    EP.NAME as RegistrationOption,
  case when REG.ID is null then 0 else 1 end as ReturnedParticipant,
    dbo.UFN_REGISTRANT_GETBENEFITLIST_BYREGISTRANTID(R.ID) as Benefits,
    case when R.Role = 'Head of household' then 3  
        when R.Role = 'Household member' then 3  
        when R.Role = 'Team leader' then 1  
        when R.Role = 'Team member' then 1  
        when R.Role = 'Company leader' then 2  
        when R.Role = 'Individual' and R.TEAMFUNDRAISINGTEAMID is null then 0   
        when R.Role = 'Individual' and R.TEAMFUNDRAISINGTEAMID is not null then 2
        when R.Role is null then 0 end   
        as GroupType,
    R.RoleCode,
    R.ISCANCELLED
from @registrantsbyevent R
join dbo.CONSTITUENT C on R.CONSTITUENTID = C.ID
join dbo.REGISTRANTREGISTRATION RR on rr.REGISTRANTID = R.ID
join dbo.EVENTPRICE    EP ON RR.EVENTPRICEID = EP.ID
left join dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2(@EVENTID) REVTOTAL on C.ID = REVTOTAL.CONSTITUENTID
left join dbo.[ADDRESS] addr on addr.CONSTITUENTID = C.ID and addr.ISPRIMARY = 1
left join dbo.REGISTRANTEXTENSION RX on RX.REGISTRANTID = R.ID
left join dbo.TEAMFUNDRAISER TR on TR.CONSTITUENTID = r.CONSTITUENTID 
left join dbo.TEAMFUNDRAISINGTEAMMEMBER TM on TM.TEAMFUNDRAISERID = TR.ID
left join dbo.TEAMFUNDRAISINGTEAM T on T.ID = TM.TEAMFUNDRAISINGTEAMID
left join dbo.TEAMEXTENSION TX on T.ID = TX.TEAMFUNDRAISINGTEAMID and TX.EVENTID = @EventID
left join dbo.EVENTEXTENSION EX ON r.EVENTID = EX.EVENTID  
left join dbo.REGISTRANT REG on REG.CONSTITUENTID = C.ID and REG.EVENTID = @EventIDP  
where RX.STATUS = 'Active'
-- site filter

 and
      (
        @SITEFILTERMODE = 0
        or exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(R.EVENTID) EVENTSITE
            where EVENTSITE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
           )
      )
 -- Check site security

    and
    exists(
         select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(R.EVENTID) EVENTSITE
        where (@ISADMIN = 1 or exists (select 1 from @SITESGRANTED SITESGRANTED  where SITESGRANTED.SITEID=[EVENTSITE].[SITEID] or (SITESGRANTED.SITEID is null and [EVENTSITE].[SITEID] is null))
  ))

IF @STATUS = 0
BEGIN
    -- Remove cancelled registrants

    DELETE FROM @REGSREPORT WHERE ISCANCELLED = 1
END
ELSE IF @STATUS = 1
BEGIN
    -- Remove active registrants

    DELETE FROM @REGSREPORT WHERE ISCANCELLED = 0

END

-- remove all household members

--delete from @REGSREPORT

--where GroupType = 3 and RoleCode <> 3


select DISTINCT
        CASE WHEN @WEBURL = '' THEN 'http://www.blackbaud.com/REGISTRANTID?REGISTRANTID='  + convert(nvarchar(36), RegistrantID)
        ELSE @WEBURL +  convert(nvarchar(36), RegistrantID) END as REGISTRANTID,
--    'http://www.blackbaud.com/REGISTRANTID?REGISTRANTID=' + convert(nvarchar(36), RegistrantID) as REGISTRANTID,

    LASTNAME,
    FIRSTNAME,
    AddressBlock,
    TOTALRAISED,
    FUNDRAISINGGOAL,
    TARGETFUNDRAISINGGOAL,
    RegisteredDate,
    RegistrationFee,
    GroupName,
    RegistrationOption,
    ReturnedParticipant,
    Benefits,
    GroupType
from @REGSREPORT
where RegistrationOption like isnull(@REGISTRATIONOPTION,'%') 
and (RegistrationFee = @REGISTRATIONFEE or @REGISTRATIONFEE is null)

-- END OF SP LOGIC


END