USP_FAF_PARTICIPANTFUNDRAISING_REPORT

CREATE PROCEDURE [dbo].[USP_FAF_PARTICIPANTFUNDRAISING_REPORT]  
(  
    @EVENTID uniqueidentifier,  
    @REGISTRATIONOPTION nvarchar(50) = null  ,
    @WEBURL nvarchar(1200) = '',
    @WEBURLGROUP nvarchar(1200) = '',
  @REPORTUSERID nvarchar(128) = null,
  @ALTREPORTUSERID nvarchar(128) = null,
  @SITEFILTERMODE tinyint = 0,     
  @SITESSELECTED nvarchar(max) = null 
)  
AS  
BEGIN  

set nocount on;  

-- BEGIN SP LOGIC  

declare @CURRENTAPPUSERID as uniqueidentifier 
 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,'825a8f05-a46a-4813-a329-93a2493b324e', 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),  
    TOTALRAISED money,  
    FUNDRAISINGGOAL money,  
    TARGETFUNDRAISINGGOAL money,  
    RegisteredDate datetime,  
    RegistrationFee money,  
    GroupName nvarchar(512),  
    [Role] nvarchar(50),  
    GroupType nvarchar(512),  
    RegistrationOption nvarchar(50),  
    ReturnedParticipant int,  
    PercentageOfDonorsRetained float,  
    NumGiftsReceived int,  
    TotalCommunicationsSent int,  
    ImagePersonalized bit,  
    StoryPersonalized bit,  
    Benefits nvarchar(512),
    RoleCode int,
    TypeCode int
)  

declare @PAYMENTCOUNT int;
DECLARE @CONSTITUENTSEQUENCEID int;

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

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

-- 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))
  ))


--  main registrant detail query   

insert into @REGSREPORT  
select   
    R.ID,
    C.KEYNAME,  
    C.FIRSTNAME,  
    REVTOTAL.AMOUNTRAISED as TOTALRAISED,    
    rex.FUNDRAISINGGOAL, 
    rex.TARGETFUNDRAISINGGOAL, 
    r.DATEADDED as RegisteredDate,
    rr.AMOUNT as RegistrationFee, 
    T.NAME as GroupName,  
    R.Role AS Role,  
    case when R.Role = 'Head of household' then 'Household'  
        when R.Role = 'Household member' then 'Household'  
        when R.Role = 'Team leader' then 'Team'  
        when R.Role = 'Team member' then 'Team'  
        when R.Role = 'Company leader' then 'Company'  
        when R.Role = 'Individual' and R.TEAMFUNDRAISINGTEAMID is null then 'Individual'    
        when R.Role = 'Individual' and R.TEAMFUNDRAISINGTEAMID is not null then 'Company' 
        when R.Role is null then 'Individual' end   
        as GroupType,  
    EP.NAME as RegistrationOption,  
    case when REG.ID is null then 0 else 1 end as ReturnedParticipant,
    (select dbo.UFN_REVENUE_GETDONORRETENTION(C.ID, @EVENTID)) as PERCENTAGEOFDONORSRETAINED,
  REVTOTAL.GIFTCOUNT as NumGiftsReceived,
    dbo.UFN_FAFGETTOTALCOMMUNICATIONS(@EVENTID, dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(R.CONSTITUENTID)) AS TOTALCOMMUNICATIONS,
    case when isnull(STO.FAFIMAGESID, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000' then 0 else 1 end as ImagePersonalized,  
    case when isnull(RTRIM(STO.STORYTEXT), '') = '' then 0 else 1 end as StoryPersonalized,  
    dbo.UFN_REGISTRANT_GETBENEFITLIST_BYREGISTRANTID(R.ID) as Benefits,
    R.RoleCode,
    isnull(TEX.TYPECODE, 0) as TypeCode
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.TEAMFUNDRAISER TR on TR.CONSTITUENTID = r.CONSTITUENTID 
left join dbo.TEAMFUNDRAISINGTEAM T on R.TEAMFUNDRAISINGTEAMID = T.ID
left join dbo.TEAMEXTENSION TEX on R.TEAMFUNDRAISINGTEAMID = TEX.TEAMFUNDRAISINGTEAMID
left join dbo.REGISTRANTEXTENSION rex on rex.REGISTRANTID = r.ID  
left join dbo.EVENTEXTENSION eex ON r.EVENTID = eex.EVENTID 
LEFT JOIN STORY STO on STO.REGISTRANTID = R.ID
left join dbo.REGISTRANT REG on REG.CONSTITUENTID = C.ID and REG.EVENTID = @EventIDP  
where rex.STATUS = 'Active'  

update @REGSREPORT 
set TOTALRAISED = 0.0
where isnull(TOTALRAISED,'')='';

-- remove all household members

-- delete from @REGSREPORT

-- where TypeCode = 3 and RoleCode <> 3


-- Remove any extra head of households.

declare @MULTIPLEHEADS table    
(    
    registrantid uniqueidentifier,    
    count int  
)    

/* 
INSERT INTO @MULTIPLEHEADS
select RegistrantID , rn
FROM  
(  
 select RegistrantID, row_number() over  
 (  
  partition by GroupName  
  order by RegisteredDate  
 ) as rn  
 FROM @REGSREPORT WHERE TypeCode = 3 AND RoleCode = 3)  as a  
WHERE rn > 1 


DELETE A 
FROM @REGSREPORT A
INNER JOIN @MULTIPLEHEADS B
    ON A.RegistrantID = B.REGISTRANTID
*/

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,  
    TOTALRAISED,  
    FUNDRAISINGGOAL,  
    TARGETFUNDRAISINGGOAL,  
    RegisteredDate,  
    RegistrationFee,  
    GroupName,  
    [Role],  
    GroupType,  
    RegistrationOption,  
    ReturnedParticipant,  
    PercentageOfDonorsRetained,  
    NumGiftsReceived,  
    TotalCommunicationsSent,  
    ImagePersonalized,  
    StoryPersonalized,  
    Benefits  
from @REGSREPORT  
where RegistrationOption like isnull(@REGISTRATIONOPTION,'%')   

END