USP_DATALIST_REVENUEMERGEDATA

CREATE procedure dbo.USP_DATALIST_REVENUEMERGEDATA
(
        @EVENTID uniqueidentifier,
      @REVENUEID uniqueidentifier,
      @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)
if @REGISTRANTID is not null
begin
  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 
   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
  ,TransactionDate=R.Date
  ,AdditionalDonationAmount = case when isnull(RD.VOLUNTARYDONATION, 0) = 0 then isnull(SD.VOLUNTARYDONATION,0) else isnull(RD.VOLUNTARYDONATION, 0) end
  ,GiftType = case when r.transactionTypecode=0 then RS.APPLICATION else R.TRANSACTIONTYPE END
  ,TotalGiftAmount = R.AMOUNT * case when S.ENDDATE is not null then 
  case LOWER(S.FREQUENCYCODE) when 0 then (DATEDIFF(year, S.STARTDATE, S.ENDDATE) + 1)
  when 1 then DATEDIFF(year, S.STARTDATE, S.ENDDATE) * 2
  when 2 then (DATEDIFF(quarter, S.STARTDATE, S.ENDDATE) + 1)
  when 3 then (DATEDIFF(month, S.STARTDATE, S.ENDDATE) + 1)
  when 6 then ((DATEDIFF(month, S.STARTDATE, S.ENDDATE) + 1) / 2 + 1)
  when 7 then DATEDIFF(month, S.STARTDATE, S.ENDDATE) * 2
  when 8 then ((DATEDIFF(week, S.STARTDATE, S.ENDDATE) + 1) / 2 + 1)
  when 9 then (DATEDIFF(week, S.STARTDATE, S.ENDDATE) + 1) 
    else 1
  end
  else 1 end
  ,PaymentID = R.LOOKUPID 
    ,PaymentName = RPM.PAYMENTMETHOD 
  --RS.DESCRIPTION 

    ,PaymentType = RPM.PAYMENTMETHOD 
    ,PaymentAmount = R.AMOUNT
    ,PaymentDate = R.DATE
    ,CreditCard = CPM.CREDITCARDPARTIALNUMBER
    ,CreditCardType = CTC.DESCRIPTION 
    ,CreditCardExpireson = CPM.EXPIRESON
    ,MatchingGiftCompayName = case when RELATIONSHIP.ID is not null then ORG.NAME else null end 
    ,MatchingGiftAmount = MGR.AMOUNT
  ,RecurringPaymentAmount =case when r.TRANSACTIONTYPECODE = 2 then  coalesce((select sum(RECURRINGGIFTINSTALLMENT.AMOUNT) from dbo.RECURRINGGIFTINSTALLMENT where RECURRINGGIFTINSTALLMENT.REVENUEID = R.ID), 0) else null end
  ,PledgePaymentAmount = case when r.TRANSACTIONTYPECODE=1 then  dbo.UFN_PLEDGE_GETAMOUNTPAID(R.ID) else null end 
  ,GiftFrequency = S.FREQUENCY
  ,GiftStartDate = S.STARTDATE
  ,OustandingGiftAmount =  dbo.UFN_PLEDGE_GETBALANCE(R.ID)

  ,SHOWRECURRINGPAYMENTSECTION = 0 -- Obsolete (DO NOT USE IT) 

  ,SHOWPLEDGEPAYMENTSECTION = 0 -- Obsolete (DO NOT USE IT)


  ,RR.RECEIPTNUMBER
  ,CS.VALUE+'Components/EReceipt.ashx?rid='+lower(convert(varchar(50),R.ID)) RECEIPTLINK
  ,RecipientConstituentName = case when TFT.NAME IS NULL OR Len(TFT.NAME)<0 then dbo.UFN_REGISTRANT_GETNAME(RGN.ID) else TFT.NAME end 
  ,ISNULL(D.VANITYNAME, '') AS DESIGNATIONNAME
from REVENUE R
inner join [EVENT] EV on R.APPEALID = EV.APPEALID 
LEFT outer join dbo.REVENUESCHEDULE S on S.ID = R.ID
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.CONSTITUENTID=R.CONSTITUENTID
left outer join dbo.SPONSORDONATION SD ON SD.SPONSORID = ES.ID 
left outer join dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
left outer join dbo.REVENUEPAYMENTMETHOD RPM on RPM.REVENUEID = R.ID 
left outer join dbo.REVENUERECEIPT RR ON RR.REVENUEID=R.ID 
left outer join dbo.EVENTEXTENSION EE ON EE.EVENTID=EV.ID
left outer join dbo.CMSSITESETTING CS ON CS.CLIENTSITESID=EE.CLIENTSITESID AND CS.SETTING='AppPathURL'
left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL CPM on RPM.ID = CPM.ID
left outer join dbo.CREDITTYPECODE CTC ON CTC.ID = CPM.CREDITTYPECODEID 
LEFT OUTER JOIN dbo.DESIGNATION D ON D.ID = RS.DESIGNATIONID AND RS.TYPECODE = 0

--Match gift

left join dbo.RELATIONSHIP on RELATIONSHIP.ISMATCHINGGIFTRELATIONSHIP =1 
                            and R.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                            and RELATIONSHIP.RELATIONSHIPTYPECODEID = (select PRIMARYBUSINESSRELATIONSHIPTYPECODEID from dbo.NETCOMMUNITYDEFAULTCODEMAP)
left join dbo.REVENUEMATCHINGGIFT RMG on RMG.MGSOURCEREVENUEID = R.ID and RMG.RELATIONSHIPID=RELATIONSHIP.ID 
left join dbo.MATCHINGGIFTCONDITION MGC on RMG.MATCHINGGIFTCONDITIONID = MGC.ID
left join dbo.REVENUE MGR on MGR.ID = RMG.ID 
left join dbo.CONSTITUENT ORG ON ORG.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID

--RecipientConstituentName

left outer join dbo.REVENUERECOGNITION RC on RC.REVENUESPLITID=RS.ID
left outer join dbo.TEAMEXTENSION TEH on TEH.HOUSEHOLDID = RC.CONSTITUENTID and TEH.EVENTID=EV.ID
left OUTER JOIN dbo.TEAMEXTENSION TET ON TET.TEAMCONSTITUENTID=RC.CONSTITUENTID and TET.EVENTID=EV.ID
left OUTER JOIN dbo.REGISTRANT RGN ON RGN.CONSTITUENTID = RC.CONSTITUENTID and RGN.EVENTID=EV.ID
left outer join dbo.TEAMFUNDRAISINGTEAM TFT on TFT.ID=TEH.TEAMFUNDRAISINGTEAMID or TFT.ID=TET.TEAMFUNDRAISINGTEAMID
where 
  R.ID = @REVENUEID
  and EV.ID = @EVENTID    
    ORDER BY D.NAME DESC
end 

if @revenueid is null
begin
  select TOP 1
  RegistrationOptionDetail = @RegistrationOptionDetail
end