USP_DATALIST_HOUSEHOLD_REVENUESUMMARYEXPANDED

CREATE procedure dbo.USP_DATALIST_HOUSEHOLD_REVENUESUMMARYEXPANDED
(
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @CURRENCYCODE tinyint = 1,
    @DATEFILTER tinyint = 16,
    @REVENUEFILTERID uniqueidentifier = null,
    @BREAKDOWNBY tinyint = 0,
    @CAMPAIGNFILTERMODE tinyint = 0,
    @CAMPAIGNSSELECTED xml = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null
) as
    set nocount on;

declare
    @ISGROUP bit = 0,
    @HOUSEHOLDID uniqueidentifier = null,
    @CURRENCYISOCURRENCYCODE nvarchar(3) = null,
    @CURRENCYDECIMALDIGITS tinyint = 0,
    @CURRENCYSYMBOL nvarchar(5) = null,
    @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0,
    @TOTALNUMBER_HH int = null,
    @TOTALAMOUNT_HH money = null,
    @TOTALAMOUNT_HOUSEHOLD money = null,
    @TOTALYEARS_HH int = null,
    @CONSECUTIVEYEARS_HH int = null,
    @GIVENSINCEFISCALYEAR_HH nvarchar(24) = null,
    @TOTALREVENUEWITHGIFTAID_HH money = null,
    @FIRSTID_HH uniqueidentifier = null,
    @FIRSTRECORDID_HH uniqueidentifier = null,
    @FIRSTDATE_HH datetime = null,
    @FIRSTTYPECODE_HH tinyint = null,
    @FIRSTTYPE_HH nvarchar(50) = null,
    @FIRSTAMOUNT_HH money = null,
    @LATESTID_HH uniqueidentifier = null,
    @LATESTRECORDID_HH uniqueidentifier = null,
    @LATESTDATE_HH datetime = null,
    @LATESTTYPECODE_HH tinyint = null,
    @LATESTTYPE_HH nvarchar(50) = null,
    @LATESTAMOUNT_HH money = null

    if @STARTDATE is null and @ENDDATE is null
    exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;
    else
    begin
        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
    end

    /*Get Household ID*/
    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 = @CONSTITUENTID and
        GD.GROUPTYPECODE = 0 and
        dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1

    /* see if this is a household */
    if @HOUSEHOLDID is null
        select 
            @ISGROUP = 1
        from 
            dbo.GROUPDATA 
        where 
            GROUPDATA.ID = @CONSTITUENTID;

    if @HOUSEHOLDID is not null
    begin
    exec dbo.USP_CONSTITUENT_REVENUESUMMARYEXPANDED_2 
        @HOUSEHOLDID,
        1,
        null,
        @REVENUEFILTERID,
        @STARTDATE,
        @ENDDATE,
        @CURRENTAPPUSERID,
        @SITEFILTERMODE,
        @SITESSELECTED,
        @SECURITYFEATUREID,
        @SECURITYFEATURETYPE,
        @CURRENCYCODE,

        @TOTALNUMBER_HH output,
        @TOTALAMOUNT_HH output,
        @TOTALAMOUNT_HOUSEHOLD output,
        @TOTALYEARS_HH output,
        @CONSECUTIVEYEARS_HH output,
        @GIVENSINCEFISCALYEAR_HH output,
        @TOTALREVENUEWITHGIFTAID_HH output,

        @FIRSTID_HH output,
        @FIRSTRECORDID_HH output,
        @FIRSTDATE_HH output,
        @FIRSTTYPECODE_HH output,
        @FIRSTTYPE_HH output,
        @FIRSTAMOUNT_HH output,
        @LATESTID_HH output,
        @LATESTRECORDID_HH output,
        @LATESTDATE_HH output,
        @LATESTTYPECODE_HH output,
        @LATESTTYPE_HH output,
        @LATESTAMOUNT_HH output,  
        @ONLYGETSUMMARY = 0,
        @BREAKDOWNBY = @BREAKDOWNBY,
        @CAMPAIGNFILTERMODE = @CAMPAIGNFILTERMODE,
        @CAMPAIGNSSELECTED = @CAMPAIGNSSELECTED
    end

    select
        @TOTALNUMBER_HH as TOTALNUMBER_HH,
        @TOTALAMOUNT_HH as TOTALAMOUNT_HH,
        @TOTALYEARS_HH as TOTALYEARS_HH,
        @GIVENSINCEFISCALYEAR_HH as GIVENSINCEFISCALYEAR_HH,
        @CONSECUTIVEYEARS_HH as CONSECUTIVEYEARS_HH,
        @TOTALREVENUEWITHGIFTAID_HH as TOTALREVENUEWITHGIFTAID_HH,                                             

        @FIRSTID_HH as FIRSTID_HH,
        @FIRSTRECORDID_HH as FIRSTRECORDID_HH,
        @FIRSTDATE_HH as FIRSTDATE_HH,
        @FIRSTTYPECODE_HH as FIRSTTYPECODE_HH,
        @FIRSTTYPE_HH as FIRSTTYPE_HH,
        @FIRSTAMOUNT_HH as FIRSTAMOUNT_HH,
        @LATESTID_HH as LATESTID_HH,
        @LATESTRECORDID_HH as LATESTRECORDID_HH,
        @LATESTDATE_HH as LATESTDATE_HH,
        @LATESTTYPECODE_HH as LATESTTYPECODE_HH,
        @LATESTTYPE_HH as LATESTTYPE_HH,
        @LATESTAMOUNT_HH as LATESTAMOUNT_HH

    return 0;