USP_DATALIST_REGISTRATIONOPTIONDETAIL

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


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 

 select 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 
   left join EVENTPRICE EP (nolock) on RO.EVENTPRICEID = EP.ID 
   left join EVENTREGISTRATIONTYPE ET on EP.EVENTREGISTRATIONTYPEID = ET.ID 
   left 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