USP_DATAFORMTEMPLATE_VIEW_INDIVIDUALSUMMARY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_INDIVIDUALSUMMARY (
        @ID uniqueidentifier,
        @HOUSEHOLDID uniqueidentifier = null output,
        @TOTALGIVING money = null output,
        @TOTALHOUSEHOLDANDMEMBERGIVING money = null output,
        @ISREGISTRANT bit = null output,
        @ISVOLUNTEER bit = null output,
        @DATALOADED bit = 0 output,
        @CURRENTAPPUSERID uniqueidentifier,
        @MEMBERSHIPREVENUE money = null output,
        @EVENTREVENUE money = null output,
        @TICKETREVENUE money = null output,
        @FACILITYREVENUE money = null output,
        @MERCHANDISEREVENUE money = null output,
        @CURRENCYID uniqueidentifier = null output,
        @SECURITYFEATUREID uniqueidentifier = null,
        @SECURITYFEATURETYPE tinyint = null
    ) as begin
        set nocount on;

        declare @MULTICURRENCYENABLED bit;
        set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
        if @MULTICURRENCYENABLED = 1 set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

        declare @REVENUEFILTERID uniqueidentifier;
        select @REVENUEFILTERID = ID from dbo.REVENUEFILTER where ISDEFAULT = 1;

        select
            @ISREGISTRANT = dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID),
            @ISVOLUNTEER = dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID),
            @DATALOADED = 1
        from
            dbo.CONSTITUENT
        where
            CONSTITUENT.ID = @ID;

        --Now can be passed in, but for backwards compat set to the original defaults if either value is null.

        if (@SECURITYFEATUREID is null or @SECURITYFEATURETYPE is null)
            begin
                set @SECURITYFEATUREID = 'ce461c96-9a71-4279-a842-6f596956d470';
                set @SECURITYFEATURETYPE = 1; -- data form

            end

        select top(1) @HOUSEHOLDID = GM.GROUPID
        from dbo.GROUPMEMBER as GM
        left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
        where GM.MEMBERID = @ID
        and GD.GROUPTYPECODE = 0
        and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1

        exec dbo.USP_CONSTITUENT_REVENUESUMMARYEXPANDED
            @CONSTITUENTID = @ID,
            @ISGROUP = 0,
            @HOUSEHOLDID = @HOUSEHOLDID,
            @REVENUEFILTERID = @REVENUEFILTERID,
            @STARTDATE = null,
            @ENDDATE = null,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,
            @SITEFILTERMODE = 0,
            @SITESSELECTED = null,
            @SECURITYFEATUREID = @SECURITYFEATUREID,
            @SECURITYFEATURETYPE = @SECURITYFEATURETYPE,
            @CURRENCYCODE = 3, --BASE

            @TOTALNUMBER = null,
            @TOTALAMOUNT = @TOTALGIVING output,
            @TOTALAMOUNT_HOUSEHOLD = @TOTALHOUSEHOLDANDMEMBERGIVING output,
            @TOTALYEARS = null,
            @CONSECUTIVEYEARS = null,
            @GIVENSINCEFISCALYEAR = null,
            @TOTALREVENUEWITHGIFTAID = null,
            @FIRSTID = null,
            @FIRSTRECORDID = null,
            @FIRSTDATE = null,
            @FIRSTTYPECODE = null,
            @FIRSTTYPE = null,
            @FIRSTAMOUNT = null,
            @LATESTID = null,
            @LATESTRECORDID = null,
            @LATESTDATE = null,
            @LATESTTYPECODE = null,
            @LATESTTYPE = null,
            @LATESTAMOUNT = null,
            @CURRENCYISOCURRENCYCODE = null,
            @CURRENCYDECIMALDIGITS = 0,
            @CURRENCYSYMBOL = null,
            @CURRENCYSYMBOLDISPLAYSETTINGCODE = 0,
            @ONLYGETSUMMARY = 1;

        declare @CONSTITREVENUE as table
        (
            REVENUETOTAL money,
            TYPECODE int
        )

        insert into @CONSTITREVENUE
            select
                case @MULTICURRENCYENABLED
                    when 1 then sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FINANCIALTRANSACTIONLINEITEM.ID,@CURRENCYID)) 
                    -- Don't subtract refunds for multicurrency at this point since they're mutually exclusive

                    else sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT) - coalesce(sum(CREDITSPLIT.CREDITTOTAL), 0)
                end as REVENUETOTAL,
                REVENUESPLIT_EXT.TYPECODE
            from 
                dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.REVENUESPLIT_EXT
                on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID 
            inner join dbo.FINANCIALTRANSACTION
                on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            left outer join dbo.UFN_CREDIT_GETSPLITCREDITAMOUNT_BULK() CREDITSPLIT
                on CREDITSPLIT.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
            where 
                FINANCIALTRANSACTION.DELETEDON is null
            and
                FINANCIALTRANSACTION.CONSTITUENTID = @ID
            and 
                FINANCIALTRANSACTION.TYPECODE in (0,5)
            and
                REVENUESPLIT_EXT.TYPECODE in (1,2,5,14,16,18)
            and
                -- Don't count order payment splits

                REVENUESPLIT_EXT.APPLICATIONCODE <> 10
            and
                FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and
                FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
            and 
                exists (
                    select top 1 
                        RSSUB.ID 
                    from
                        dbo.FINANCIALTRANSACTIONLINEITEM RSSUB
                    inner join
                        dbo.REVENUESPLIT_EXT on RSSUB.ID = REVENUESPLIT_EXT.ID
                    cross apply
                        dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                    where
                        RSSUB.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                    and RSSUB.DELETEDON is null
                    and RSSUB.TYPECODE <> 1
                    and (
                        dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or 
                        exists (
                            select 1 
                            from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) 
                            where SITEID=[REVSITES].[SITEID] or 
                            (SITEID is null and [REVSITES].[SITEID] is null)
                        )
                    )
                )
            group by 
                REVENUESPLIT_EXT.TYPECODE

        select
            @MEMBERSHIPREVENUE = (coalesce((select sum(REVENUETOTAL) from @CONSTITREVENUE where TYPECODE in (2, 18)),0)),
            @EVENTREVENUE = (coalesce((select REVENUETOTAL from @CONSTITREVENUE where TYPECODE = 1),0)),
            @TICKETREVENUE = (coalesce((select REVENUETOTAL from @CONSTITREVENUE where TYPECODE = 5),0)),
            @FACILITYREVENUE = coalesce((select REVENUETOTAL from @CONSTITREVENUE where TYPECODE = 14),0),
            @MERCHANDISEREVENUE = (coalesce((select REVENUETOTAL from @CONSTITREVENUE where TYPECODE = 16),0))

    end