USP_DATALIST_HOUSEHOLD_RECOGNITIONSUMMARYEXPANDED_2

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

    declare
        @HOUSEHOLDID uniqueidentifier = null,
        @ISGROUP bit = 0,  
        @CURRENCYISOCURRENCYCODE nvarchar(3) = null,
        @CURRENCYDECIMALDIGITS tinyint = 0,
        @CURRENCYSYMBOL nvarchar(5) = null,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0,

        @RECOGNITIONTOTALNUMBER_HH int = null,
        @RECOGNITIONTOTALAMOUNT_HH money = null,
        @RECOGNITIONTOTALAMOUNT_HOUSEHOLD money = null,
        @RECOGNITIONTOTALAMOUNT_HOUSEHOLD_HH money = null,
        @RECOGNITIONTOTALYEARS_HH int = null,
        @RECOGNITIONCONSECUTIVEYEARS_HH int = null,
        @RECOGNITIONGIVENSINCEFISCALYEAR_HH nvarchar(24) = null,
        @TOTALRECOGNITIONWITHGIFTAID_HH money = null,

        @RECOGNITIONFIRSTID_HH uniqueidentifier = null,
        @RECOGNITIONFIRSTRECORDID_HH uniqueidentifier = null,
        @RECOGNITIONFIRSTDATE_HH datetime = null,
        @RECOGNITIONFIRSTTYPECODE_HH tinyint = null,
        @RECOGNITIONFIRSTTYPE_HH nvarchar(50) = null,
        @RECOGNITIONFIRSTAMOUNT_HH money = null,
        @RECOGNITIONLATESTID_HH uniqueidentifier = null,
        @RECOGNITIONLATESTRECORDID_HH uniqueidentifier = null,
        @RECOGNITIONLATESTDATE_HH datetime = null,
        @RECOGNITIONLATESTTYPECODE_HH tinyint = null,
        @RECOGNITIONLATESTTYPE_HH nvarchar(50) = null,
        @RECOGNITIONLATESTAMOUNT_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_RECOGNITIONSUMMARYEXPANDED_2
                @HOUSEHOLDID,
                0,
                null,
                @HOUSEHOLDRECOGNITIONFILTERID,
                @STARTDATE,
                @ENDDATE,
                @CURRENTAPPUSERID,
                @SITEFILTERMODE,
                @SITESSELECTED,
                @SECURITYFEATUREID,
                @SECURITYFEATURETYPE,
                @CURRENCYCODE,

                @RECOGNITIONTOTALNUMBER_HH output,
                @RECOGNITIONTOTALAMOUNT_HH output,
                @RECOGNITIONTOTALAMOUNT_HOUSEHOLD output,
                @RECOGNITIONTOTALYEARS_HH output,
                @RECOGNITIONCONSECUTIVEYEARS_HH output,
                @RECOGNITIONGIVENSINCEFISCALYEAR_HH output,
                @TOTALRECOGNITIONWITHGIFTAID_HH output,

                @RECOGNITIONFIRSTID_HH output,
                @RECOGNITIONFIRSTRECORDID_HH output,
                @RECOGNITIONFIRSTDATE_HH output,
                @RECOGNITIONFIRSTTYPECODE_HH output,
                @RECOGNITIONFIRSTTYPE_HH output,
                @RECOGNITIONFIRSTAMOUNT_HH output,
                @RECOGNITIONLATESTID_HH output,
                @RECOGNITIONLATESTRECORDID_HH output,
                @RECOGNITIONLATESTDATE_HH output,
                @RECOGNITIONLATESTTYPECODE_HH output,
                @RECOGNITIONLATESTTYPE_HH output,
                @RECOGNITIONLATESTAMOUNT_HH output,  

                @BREAKDOWNBY = @BREAKDOWNBY,
                @CAMPAIGNFILTERMODE = @CAMPAIGNFILTERMODE,
                @CAMPAIGNSSELECTED = @CAMPAIGNSSELECTED
             end

        select    
            @RECOGNITIONTOTALNUMBER_HH as RECOGNITIONTOTALNUMBER_HH,
            @RECOGNITIONTOTALAMOUNT_HH as RECOGNITIONTOTALAMOUNT_HH,
            @RECOGNITIONTOTALYEARS_HH as RECOGNITIONTOTALYEARS_HH,
            @RECOGNITIONGIVENSINCEFISCALYEAR_HH as RECOGNITIONGIVENSINCEFISCALYEAR_HH,
            @RECOGNITIONCONSECUTIVEYEARS_HH as RECOGNITIONCONSECUTIVEYEARS_HH,

            @RECOGNITIONFIRSTID_HH as RECOGNITIONFIRSTID_HH,
            @RECOGNITIONFIRSTRECORDID_HH as RECOGNITIONFIRSTRECORDID_HH,
            @RECOGNITIONFIRSTDATE_HH as RECOGNITIONFIRSTDATE_HH,
            @RECOGNITIONFIRSTTYPECODE_HH as RECOGNITIONFIRSTTYPECODE_HH,
            @RECOGNITIONFIRSTTYPE_HH as RECOGNITIONFIRSTTYPE_HH, 
            @RECOGNITIONFIRSTAMOUNT_HH as RECOGNITIONFIRSTAMOUNT_HH,
            @RECOGNITIONLATESTID_HH as RECOGNITIONLATESTID_HH,
            @RECOGNITIONLATESTRECORDID_HH as RECOGNITIONLATESTRECORDID_HH,
            @RECOGNITIONLATESTDATE_HH as RECOGNITIONLATESTDATE_HH,
            @RECOGNITIONLATESTTYPECODE_HH as RECOGNITIONLATESTTYPECODE_HH,
            @RECOGNITIONLATESTTYPE_HH as RECOGNITIONLATESTTYPE_HH,
            @RECOGNITIONLATESTAMOUNT_HH as RECOGNITIONLATESTAMOUNT_HH,
            @TOTALRECOGNITIONWITHGIFTAID_HH as TOTALRECOGNITIONWITHGIFTAID_HH               

        return 0;