USP_FAF_SPONSOR_REPORT

CREATE PROCEDURE [dbo].[USP_FAF_SPONSOR_REPORT]
(
    @EVENTID uniqueidentifier,
  @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_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,'1906c640-5085-4f2e-85d8-d11421dfc37e', 21)
  --where SITEID is not null


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


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


declare @SPONSORREPORT table
(
  ConstituentID uniqueidentifier,
    EventID uniqueidentifier,
    EventSponsorID uniqueidentifier,
    RegistrantID uniqueidentifier,
    CompanyName nvarchar(200),
    LastName nvarchar(200),
    FirstName nvarchar(200),
    AddressBlock nvarchar(512),
    SponsorshipType nvarchar(50),
    TotalFees money,
    ReturningSponsor bit,
    IsFundraiser bit,
    TotalRaised money,
    DonationAmount money,
    Benefits nvarchar(512),
    SponsorGoal int,
    ReturningGoal float
)

insert into @SPONSORREPORT
select 
cont.ID,
@EVENTID,
ES.ID,
R.ID,
c.KEYNAME,
cont.KEYNAME,
cont.FIRSTNAME,
dbo.UFN_BUILDFULLADDRESS(addr.ID, addr.ADDRESSBLOCK, addr.CITY, addr.STATEID, addr.POSTCODE, addr.COUNTRYID) 
     As AddressBlock,
ST.NAME,
ST.FEESAMOUNT,
case when ESP.ID is null then 0 else 1 end as Returning,
case when isnull(R.ID, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000' then 0 else 1 end 
   as IsFundraiser,
0.0 as TotalRaised,
'' as DonationAmount,
'' as Benefits,
EVG.RECRUITMENTSPONSOR, 
EVG.RECRUITMENTSPONSORRETENTION
from dbo.EVENTSPONSOR ES
INNER JOIN dbo.EVENTSPONSORSHIPTYPE ST ON ES.EVENTSPONSORSHIPTYPEID = ST.ID  
inner join dbo.CONSTITUENT CONT on CONT.ID = ES.CONSTITUENTID
inner join dbo.EVENTGOAL EVG on EVG.EVENTID = ES.EVENTID
left join dbo.EVENTSPONSOR ESP on ESP.CONSTITUENTID = CONT.ID and ESP.EVENTID = @EventIDP
left join dbo.CONSTITUENT C on C.ID = ES.ORGANIZATIONCONSTITUENTID
left join dbo.ADDRESS addr on addr.CONSTITUENTID = C.ID and addr.ISPRIMARY = 1
left join dbo.REGISTRANT R on R.CONSTITUENTID = CONT.ID and R.EVENTID = @EventID
where ES.EVENTID = @eventid     
-- site filter

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

declare @bentable table
(
    beventsponsorid uniqueidentifier,
    benefits varchar(500)
)
-- get list of benefits for sponsor

insert into @bentable
SELECT SR.EventSponsorID,
(
    SELECT name + ',' 
    FROM dbo.BENEFIT BEN              
    WHERE BEN.ID in 
    (select benefitid from 
  dbo.SPONSORBENEFITEXTENSION where SPONSORID = SR.EventSponsorID)
    for XML PATH('')        
) As Benefits 
FROM @SPONSORREPORT SR
group by SR.EventSponsorID

update @SPONSORREPORT
set Benefits = bt.Benefits from @bentable bt where beventsponsorid = EventSponsorID

update @SPONSORREPORT
set TotalRaised = dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(RegistrantID, EventID)

update @SPONSORREPORT
set DonationAmount = VOLUNTARYDONATION from SPONSORDONATION where SPONSORID = EventSponsorID

select distinct 
'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36), ConstituentID) as CONSTITUENTID,
EventID,
CompanyName,
LastName,
FirstName,
AddressBlock,
SponsorshipType,
TotalFees,
ReturningSponsor,
IsFundraiser,
TotalRaised,
DonationAmount,
case when LEN(Benefits)>0 then LEFT(Benefits, LEN(Benefits)-1) ELSE '' END As Benefits,
SponsorGoal,
ReturningGoal
from @SPONSORREPORT


-- END OF SP LOGIC



END