USP_FAF_LAPSED_PARTICIPANT_REPORT

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

-- BEGIN SP LOGIC

set nocount on

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,'97ec8744-726c-4ddb-8a7e-35379aa4a8b2', 21)

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


declare @CURRENTEVENTID as uniqueidentifier 
 set @CURRENTEVENTID = @EVENTID;

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

declare @RegistrantID uniqueidentifier
declare @ConstituentID uniqueidentifier

declare @REGSREPORT table
(
  RegistrantID uniqueidentifier,
    LASTNAME nvarchar(200),
    FIRSTNAME nvarchar(200),
    DONOTEMAIL bit,
    DONOTMAIL bit,
    DONOTPHONE bit,
    AddressBlock nvarchar(512),
    EMAILADDRESS nvarchar(100),
    NUMBER nvarchar(100),    
    TOTALRAISED money,
    NUMGIFTSRECEIVED int,
    TOTALCOMMUNICATIONS int,
    FUNDRAISINGGOAL money,
    TARGETFUNDRAISINGGOAL money,
    RegisteredDate datetime,
    RegistrationFee money,
    GroupName nvarchar(512),
    Role nvarchar(50),
    Grouptype  nvarchar(50),
    RegistrationOption nvarchar(50),
    ReturnedParticipant int,
    Benefits nvarchar(512),
    PreviousEventName nvarchar(200),
  RoleCode int,
  TypeCode int,
  GROUPID uniqueidentifier
)

-- get previous linked event

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

-- if no linkage, nothing to query

if @EVENTIDP is not NULL
begin

    declare @PreviousEventName nvarchar(200)
    Set @PreviousEventName = (Select NAME from Event where ID = @EventIDP)

    declare @PAYMENTCOUNT int
    DECLARE @CONSTITUENTSEQUENCEID int

    declare @REGS table
    (
        countern int identity,
        registrantid uniqueidentifier,
        constituentid uniqueidentifier        
    )

    declare @bentable table
    (
        registrantid uniqueidentifier,
        names varchar(500)
    )

    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(@eventidp, null) AG ON R.ID = AG.REGISTRANTID  
    where R.EVENTID = @EventIDP

insert into @REGS(registrantid, constituentid)
select R.ID, C.ID--, AG.Role, AG.RoleCode

from REGISTRANT R
inner join CONSTITUENT C on C.ID = R.CONSTITUENTID
left join dbo.UFN_REGISTRANT_GETFAFROLE(@EventIDP, NULL) AG ON r.ID = AG.REGISTRANTID 
where R.EVENTID = @EventIDP
and C.ID not in (SELECT R2.CONSTITUENTID FROM REGISTRANT R2 where R2.EVENTID = @EVENTID)
union
select R.ID, C.ID--, AG.Role, AG.RoleCode

from REGISTRANT R
inner join CONSTITUENT C on C.ID = R.CONSTITUENTID
left join dbo.UFN_REGISTRANT_GETFAFROLE(@EventIDP, NULL) AG ON r.ID = AG.REGISTRANTID 
outer apply UFN_FAF_GETTEAMINFO_BY_REGISTRANT(R.CONSTITUENTID, @EventID) H
outer apply UFN_FAF_GETTEAMINFO_BY_REGISTRANT(R.CONSTITUENTID, @EventIDP) HP
where R.EVENTID = @EventIDP
and AG.RoleCode = 3
and isnull(H.TEAMCONSTITUENTID,'00000000-0000-0000-0000-000000000000') <> isnull(HP.TEAMCONSTITUENTID,'00000000-0000-0000-0000-000000000000')


-- look back now, nothing but retrospective

    set @EVENTID = @EventIDP

    declare @acounter int, 
            @totalbeans int
    set @acounter = 0
    select @totalbeans = COUNT(countern) from @REGS

    while @acounter < @totalbeans
    begin
    set @acounter = @acounter + 1

    -- get a registrant

    select @RegistrantID = registrantid, @ConstituentID = constituentid 
    from @REGS where countern = @acounter;

    -- get list of benefits for registrant

    insert into @bentable
        SELECT r1.ID,
        (
            SELECT name + ',' 
            FROM dbo.BENEFIT BEN              
            WHERE BEN.ID in 
      (select RB.benefitid from 
                dbo.REGISTRANTBENEFIT RB
                where RB.REGISTRANTID = r1.id
             union
             select EPB.BENEFITID from 
                dbo.REGISTRANTREGISTRATION rr 
                JOIN dbo.EVENTPRICE    EP ON RR.EVENTPRICEID = EP.ID
                LEFT JOIN dbo.EVENTPRICEBENEFIT    EPB ON EP.ID = EPB.EVENTPRICEID
                where rr.REGISTRANTID = r1.id
      )
            for XML PATH('')        
        ) As Benefits 
        FROM REGISTRANT r1
        where r1.ID = @registrantid
        group by r1.ID




    insert into @REGSREPORT
    --  main registrant detail query 

    select 
  @RegistrantID,
    c.KEYNAME,
    c.FIRSTNAME,
  isnull(EM.DONOTEMAIL,0) as DONOTEMAIL,
  isnull(ADDR.DONOTMAIL,0) as DONOTMAIL,
  isnull(PHO.DONOTCALL,0) as DONOTPHONE,
    dbo.UFN_BUILDFULLADDRESS(addr.ID, addr.ADDRESSBLOCK, addr.CITY, addr.STATEID, addr.POSTCODE, addr.COUNTRYID) 
         As AddressBlock,
    EM.EMAILADDRESS,
    PHO.NUMBER,
    --dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(@RegistrantID, @EVENTID) as TOTALRAISED,

    case when TEX.TypeCode = 3 then 
      isnull(dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(TEAM.TEAMID, @EVENTID),0)
  else 
      isnull(dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(@RegistrantID, @EVENTID),0)
  end as TOTALRAISED,
    --RFT.PERCENTAGEOFDONORSRETAINED,

    -- Correct column for gifts received

    --RFT.NumberGifReceived as NumGiftsReceived,

    -- RFT.DONORNEWNUMBER as NumGiftsReceived,

    --RFT.TOTALCOMMUNICATIONS,

    --rex.FUNDRAISINGGOAL, 

    --rex.TARGETFUNDRAISINGGOAL, 

    --RFT.NUMBERGIFRECEIVED as NumGiftsReceived,  

    --RFT.TOTALCOMMUNICATIONS,  

    dbo.UFN_FAFGETTOTALCOMMUNICATIONS(@EVENTID, dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(R.CONSTITUENTID)) AS TOTALCOMMUNICATIONS,
    REVTOTAL.GIFTCOUNT as NumGiftsReceived,
    case when TEX.TypeCode = 3 then TFT.Goal else rex.FUNDRAISINGGOAL end as FUNDRAISINGGOAL, 
    case when TEX.TypeCode = 3 then TEX.TARGETFUNDRAISINGGOAL else rex.TARGETFUNDRAISINGGOAL end as TARGETFUNDRAISINGGOAL,   
    r.DATEADDED as RegisteredDate,
    rr.AMOUNT as RegistrationFee,
    TFT.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 NumRegs.eventid is null then 0 else 1 end as ReturnedParticipant,
    case when LEN(bent.names) > 0 then LEFT(bent.names, LEN(bent.names) -1) else '' end as Benefits,
    @PreviousEventName,
    R.RoleCode,
    isnull(TEX.TYPECODE, 0) as TypeCode,
    case when R.Role = 'Head of household' then TFT.ID end as GROUPID
    from 
    --dbo.REGISTRANT r  

    @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 (
        select eventid, constituentid
        from dbo.registrant r    
        where r.eventid = @EventIDP
    ) NumRegs on NumRegs.constituentid = c.id
    join @bentable bent on bent.registrantid = r.ID
    left join dbo.ADDRESS addr on addr.CONSTITUENTID = c.ID and addr.ISPRIMARY = 1
    left join dbo.EMAILADDRESS EM on EM.CONSTITUENTID = C.ID and EM.ISPRIMARY = 1
    left join dbo.PHONE PHO on PHO.CONSTITUENTID = C.ID and PHO.ISPRIMARY = 1
    left join dbo.REGISTRANTEXTENSION rex on rex.REGISTRANTID = r.ID
    left join dbo.EVENTEXTENSION eex ON r.EVENTID = eex.EVENTID  
    left join dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT (@ConstituentID, @eventid) TEAM on r.ID = TEAM.RegistrantID
    left join dbo.TEAMFUNDRAISINGTEAM TFT on TFT.ID = TEAM.TEAMID
    left join dbo.TEAMEXTENSION TEX on TFT.ID = TEX.TEAMFUNDRAISINGTEAMID
    LEFT JOIN dbo.FAFREGISTRATIONTYPE rgtype ON EP.ID = rgtype.EVENTPRICEID
    LEFT JOIN dbo.EVENTPRICEBENEFIT    EPB ON EP.ID = EPB.EVENTPRICEID
    --LEFT JOIN dbo.UFN_REGISTRANT_GETFAFROLE(@eventid, NULL) AG ON r.ID = AG.REGISTRANTID

    --LEFT JOIN dbo.V_QUERY_REGISTRANT_FUNDRAISINGTOTAL RFT on RFT.ID = r.ID

    where r.ID = @RegistrantID  
    and rex.STATUS = 'Active'
    -- site filter  

 and  
      (  
        @SITEFILTERMODE = 0  
        or exists(  
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(@CURRENTEVENTID) 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(@CURRENTEVENTID) 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))  
  ))  
    end

end 


-- 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 
CASE when GroupType = 'Household' THEN

        CASE WHEN @WEBURLGROUP = '' THEN 'http://www.blackbaud.com/GROUPID?GROUPID=' + convert(nvarchar(36), GROUPID)
        ELSE @WEBURLGROUP +  convert(nvarchar(36), GROUPID)  END

--         'http://www.blackbaud.com/GROUPID?GROUPID=' + convert(nvarchar(36), GROUPID) 

ELSE

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


-- 'http://www.blackbaud.com/REGISTRANTID?REGISTRANTID=' + convert(nvarchar(36), RegistrantID) END as REGISTRANTID,

LASTNAME,
FIRSTNAME,
DONOTEMAIL,
DONOTMAIL,
DONOTPHONE,
AddressBlock,
EMAILADDRESS,
NUMBER,
TOTALRAISED,
NUMGIFTSRECEIVED,
TOTALCOMMUNICATIONS,
FUNDRAISINGGOAL,
TARGETFUNDRAISINGGOAL,
RegisteredDate,
RegistrationFee,
GroupName,
Role,
Grouptype,
RegistrationOption,
ReturnedParticipant,
Benefits,
PreviousEventName
from @REGSREPORT
where RegistrationOption like ISNULL(@REGISTRATIONOPTION,'%') 
and RegistrationFee >= ISNULL(@REGISTRATIONFEE, 0.0) 

-- END OF SP LOGIC

END