USP_SMARTFIELD_GETDONAORRAISEDNEW

CREATE procedure dbo.USP_SMARTFIELD_GETDONAORRAISEDNEW
(
    @ASOF datetime =null
)
as
;with [EVENTTOTAL_CTE]
as    
(
    select    EX.EVENTID AS ID, 
            sum(isnull([ORIGINAL amount],0)) as [ORIGINALAMOUNT]
            ,sum(isnull([APPLIED AMOUNT] ,0)) as [APPLIEDAMOUNT]
            ,TRANSACTIONTYPE                  as [REVENUETYPE]
    from    dbo.EVENTEXTENSION EX    
    outer apply [dbo].[UFN_REVENUE_EVENTREVENUEDETAILS](EX.EVENTID) R
    left join CONSTITUENT C on R.CONSTITUENTID = C.ID 
    left join (
        select FT1.CONSTITUENTID, E1.ID, E1.NAME  from dbo.FINANCIALTRANSACTION FT1 (nolock)
        join dbo.REVENUE_EXT RE1 (nolock) on FT1.ID = RE1.ID 
        join dbo.EVENT E1 (nolock) on E1.APPEALID = RE1.APPEALID 
        join dbo.EVENTEXTENSION EX1 (nolock) on EX1.EVENTID = E1.ID 
    ) P on P.CONSTITUENTID = R.CONSTITUENTID and P.ID = EX.PRIORYEAREVENTID        
    where P.CONSTITUENTID is null and TRANSACTIONTYPE is NOT null
    group by EX.EVENTID,transactiontype 
),
[TotalRaisedByNewDonors] as
(
    select ECTE.ID, [REVENUETYPE],
        case 
            when [REVENUETYPE] = 'Donation' THEN  [ORIGINALAMOUNT]
            when [REVENUETYPE] = 'Offline Donation' THEN 
                case when C.UNCONFIRMEDPARTICIPANTGIFTENTRY=1 then [ORIGINALAMOUNT] ELSE [APPLIEDAMOUNT] END
            when [REVENUETYPE] = 'Event registration'    then 
                case when C.ISREGISTRATIONREVENUE = 1 then [ORIGINALAMOUNT] else 0 end
            when [REVENUETYPE] = 'Pledge'                then 
                case when C.ISUNPAIDPLEDGES=1 then [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
            when [REVENUETYPE] = 'Event sponsorship'    then 
                case when C.ISSPONSORSHIPREVENUE = 0  then 0 else 
                    case when C.ISUNPAIDSPONSORSHIPREVENUE=1 then  [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
                end
            when [REVENUETYPE] = 'Matching gift'    then
                case when C.ISPENDINGMATCHINGGIFTS=1 then [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
            when [REVENUETYPE] = 'Recurring gift'    then 
                case when C.ISUNPAIDRECURRINGGIFTS=1 then [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
        end as [TOTAL]
            from [EVENTTOTAL_CTE] ECTE
            join FAFEVENTDONATIONOPTIONSCONFIG C on C.EVENTID = ECTE.ID
)
select    TR.ID, 
        SUM(isnull(TR.[TOTAL],0)) as VALUE
from    [TotalRaisedByNewDonors] TR
join EVENT E on E.ID = TR.ID 
where TR.REVENUETYPE not in ('Event registration', 'Event sponsorship')
group by TR.ID