USP_DATALIST_REVENUEMERGEDATA_REG

CREATE procedure dbo.USP_DATALIST_REVENUEMERGEDATA_REG
(
        @EVENTID uniqueidentifier,
      @REVENUEID uniqueidentifier = null,
      @REGISTRANTID uniqueidentifier = null
)
  as
      set nocount on;

--get the benefit field


declare @BENEFITSWAIVED bit
declare @benefit nvarchar(max)
set @benefit = ''

select @BENEFITSWAIVED = BENEFITSWAIVED from REGISTRANT WHERE ID = @REGISTRANTID

if @BENEFITSWAIVED = 0 
begin
    declare @BENEFITTABLE table(BenefitID uniqueidentifier, BenefitName nvarchar(200))
    INSERT INTO @BENEFITTABLE
    SELECT RB.BENEFITID,
           CASE WHEN ISNULL(BO.NAME, '')= '' THEN ISNULL(dbo.UFN_BENEFIT_GETNAME (RB.BENEFITID),'')  
                 WHEN ISNULL(BO.NAME, '')<> '' THEN ISNULL(dbo.UFN_BENEFIT_GETNAME (RB.BENEFITID),'')  + '- ' + ISNULL(BO.NAME, '') + ':' + RBE.BENEFITPREFERENCE END
    FROM dbo.REGISTRANT REG
    INNER JOIN dbo.REGISTRANTBENEFITEXTENSION RBE    ON RBE.REGISTRANTID = REG.ID
    LEFT JOIN dbo.REGISTRANTBENEFIT    RB    ON RB.BENEFITID = RBE.BENEFITID AND RBE.REGISTRANTID = RB.REGISTRANTID
    LEFT JOIN dbo.BENEFITOPTION BO ON RBE.BENEFITOPTIONID= BO.ID
    WHERE REG.ID = @REGISTRANTID and REG.EVENTID = @EVENTID
    order by BO.NAME DESC

    INSERT INTO @BENEFITTABLE
    SELECT    RB.BENEFITID,B.NAME
    FROM dbo.REGISTRANT REG
    LEFT JOIN dbo.REGISTRANTBENEFIT   RB ON RB.REGISTRANTID = REG.ID
    LEFT JOIN dbo.BENEFIT B    ON RB.BENEFITID = B.ID
    INNER JOIN dbo.EVENT ON REG.EVENTID = EVENT.ID
    LEFT JOIN dbo.EVENTHIERARCHY ON EVENTHIERARCHY.ID = EVENT.ID
    WHERE REG.ID = @REGISTRANTID and REG.EVENTID = @EVENTID
    AND    RB.BENEFITID NOT IN (SELECT BENEFITID FROM @BENEFITTABLE)

  if (select count(*) from @BENEFITTABLE) > 0
  begin
      SELECT  @benefit=@benefit + '<div>' + BenefitName  + '</div>'
      FROM @BENEFITTABLE
  end
end 

--get the registration option field

declare @RegistrationOptionDetail nvarchar(2000)
declare @RegistrationOptionName nvarchar(100)
declare @RegistrationOptionMaxQuantity varchar(10)

if @REGISTRANTID is not null
begin
  select @RegistrationOptionName = EP.NAME,
  @RegistrationOptionMaxQuantity = (
  case when FAF.MAXQUANTITY=0 then 'Unlimited'
    else convert(varchar(10),FAF.MAXQUANTITY) 
    end),
  @RegistrationOptionDetail = '<table><tr><td>Name</td><td>' + EP.NAME + '</td></tr><tr><td>Description</td><td>' + case FAF.DESCRIPTION when '' then 'None' else FAF.DESCRIPTION end 
  + '</td></tr><tr><td>Amount</td><td>$' + convert(varchar(20), RO.AMOUNT) 
  + '</td></tr><tr><td>Benefits</td><td>' + case when @BENEFITSWAIVED=1 then 'Declined' when isnull(@benefit,'') = '' then 'None' else @benefit end
  + '</td></tr><tr><td>Fundraising Groups&nbsp;</td><td>' + RL.Role 
  + '</td></tr><tr><td>Number available</td><td>' + (
  case when FAF.MAXQUANTITY=0 then 'Unlimited' 
        else 
            convert(varchar(10), FAF.MAXQUANTITY-
                (select count(*) from dbo.REGISTRANT R1
               inner join REGISTRANTREGISTRATION RO1 (nolock) on R1.ID = RO1.REGISTRANTID 
               inner join EVENTPRICE EP1 (nolock) on RO1.EVENTPRICEID = EP1.ID 
               where EP1.ID=FAF.EVENTPRICEID)) end
                )
  + '</td></tr><tr><td>Active</td><td>' + case ET.ISACTIVE when 1 then 'Yes' else 'No' end + '</td></tr></table>'
  from REGISTRANT R (nolock)
   inner join REGISTRANTREGISTRATION RO (nolock) on R.ID = RO.REGISTRANTID 
   inner join EVENTPRICE EP (nolock) on RO.EVENTPRICEID = EP.ID 
   inner join EVENTREGISTRATIONTYPE ET on EP.EVENTREGISTRATIONTYPEID = ET.ID 
   inner join FAFREGISTRATIONTYPE FAF on FAF.EVENTPRICEID = EP.ID
   left join dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, NULL) RL ON RL.REGISTRANTID = R.ID
   where R.ID= @REGISTRANTID
end

If @REVENUEID is not null
  begin
  select TOP 1
    RegistrationOptionDetail = @RegistrationOptionDetail
    ,AdditionalDonationAmount = case when isnull(RD.VOLUNTARYDONATION, 0) = 0 then isnull(SD.VOLUNTARYDONATION,0) else isnull(RD.VOLUNTARYDONATION, 0) end
    ,RegistrationOptionName = @RegistrationOptionName
    ,RegistrationOptionMaxQuantity = @RegistrationOptionMaxQuantity
  from REVENUE R
  inner join [EVENT] EV on R.APPEALID = EV.APPEALID 
  LEFT outer join dbo.REGISTRANT RG on EV.ID = RG.EVENTID and RG.ID= @REGISTRANTID
  left outer join dbo.REGISTRANTDONATION RD on RD.REGISTRANTID = RG.ID
  left outer join dbo.EVENTSPONSOR ES ON ES.EVENTID = @EVENTID and ES.CONSTITUENTID=dbo.UFN_EVENTSPONSOR_GETSPONSORCONSTITUENTID_BYCONSTITUENTID(R.CONSTITUENTID, @EVENTID)
  left outer join dbo.SPONSORDONATION SD ON SD.SPONSORID = ES.ID 
  where 
    R.ID = @REVENUEID
    and EV.ID = @EVENTID   
  end
else
  begin
  select TOP 1
    RegistrationOptionDetail = @RegistrationOptionDetail
    ,AdditionalDonationAmount = 0
    ,RegistrationOptionName = @RegistrationOptionName
    ,RegistrationOptionMaxQuantity = @RegistrationOptionMaxQuantity
  end