USP_DATALIST_CONSTITUENT_REVENUESUMMARY_2

            create procedure dbo.USP_DATALIST_CONSTITUENT_REVENUESUMMARY_2
            (
                @CONSTITUENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @SITEFILTERMODE tinyint = 0,
                /*DanielCo [12/19/08] Dashboards don't support parameters that cannot 
                    be cast as strings like this collection so an error will occur if
                    user selects more sites than fits here (~73)*/
                @SITESSELECTED nvarchar(max) = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @CURRENCYCODE tinyint = 1,
                @DATEFILTER tinyint = 16,
                @REVENUEFILTERID uniqueidentifier = null,
                @BREAKDOWNBY tinyint = 0,
                @CAMPAIGNFILTERMODE tinyint = 0,
                @CAMPAIGNSSELECTED nvarchar(max) = null
            ) as
                set nocount on;

                declare
                  @STARTDATE datetime,
                  @ENDDATE datetime,
                  @HOUSEHOLDID uniqueidentifier = null,
                  @ISGROUP bit = 0,
                  @TOTALNUMBER int = null,
                  @TOTALAMOUNT money = null,
                  @TOTALAMOUNT_HOUSEHOLD money = null,
                  @TOTALYEARS int = null,
                  @CONSECUTIVEYEARS int = null,
                  @GIVENSINCEFISCALYEAR nvarchar(24) = null,
                  @TOTALREVENUEWITHGIFTAID money = null,

                  @FIRSTID uniqueidentifier = null,
                  @FIRSTRECORDID uniqueidentifier = null,
                  @FIRSTDATE datetime = null,
                  @FIRSTTYPECODE tinyint = null,
                  @FIRSTTYPE nvarchar(50) = null,
                  @FIRSTAMOUNT money = null,
                  @LATESTID uniqueidentifier = null,
                  @LATESTRECORDID uniqueidentifier = null,
                  @LATESTDATE datetime = null,
                  @LATESTTYPECODE tinyint = null,
                  @LATESTTYPE nvarchar(50) = null,
                  @LATESTAMOUNT money = null,

                  @CURRENCYISOCURRENCYCODE nvarchar(3) = null,
                  @CURRENCYDECIMALDIGITS tinyint = 0,
                  @CURRENCYSYMBOL nvarchar(5) = null,
                  @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0,

          --household specific fields

          @TOTALNUMBER_HH int = null,
                  @TOTALAMOUNT_HH 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                  


                  exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;

                  /*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;

                  exec dbo.USP_CONSTITUENT_REVENUESUMMARYEXPANDED_2
                      @CONSTITUENTID,
                      @ISGROUP,
                      @HOUSEHOLDID,
                      @REVENUEFILTERID,
                      @STARTDATE,
                      @ENDDATE,
                      @CURRENTAPPUSERID,
                      @SITEFILTERMODE,
                      @SITESSELECTED,
                    @SECURITYFEATUREID,
                      @SECURITYFEATURETYPE,
                      @CURRENCYCODE,

                      @TOTALNUMBER output,
                      @TOTALAMOUNT output,
                      @TOTALAMOUNT_HOUSEHOLD output,
                      @TOTALYEARS output,
                      @CONSECUTIVEYEARS output,
                      @GIVENSINCEFISCALYEAR output,
                      @TOTALREVENUEWITHGIFTAID output,

                      @FIRSTID output,
                      @FIRSTRECORDID output,
                      @FIRSTDATE output,
                      @FIRSTTYPECODE output,
                      @FIRSTTYPE output,
                      @FIRSTAMOUNT output,
                      @LATESTID output,
                      @LATESTRECORDID output,
                      @LATESTDATE output,
                      @LATESTTYPECODE output,
                      @LATESTTYPE output,
                      @LATESTAMOUNT output,
                      @CURRENCYISOCURRENCYCODE output,
                      @CURRENCYDECIMALDIGITS output,
                      @CURRENCYSYMBOL output,
                      @CURRENCYSYMBOLDISPLAYSETTINGCODE output,
                      0, --OnlyGetSummary

                      @BREAKDOWNBY,
                      @CAMPAIGNFILTERMODE,
                      @CAMPAIGNSSELECTED



                 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 = @TOTALNUMBER_HH output,
                      @TOTALAMOUNT = @TOTALAMOUNT_HH output,
                      @TOTALYEARS = @TOTALYEARS_HH output,
                      @CONSECUTIVEYEARS = @CONSECUTIVEYEARS_HH output,
                      @GIVENSINCEFISCALYEAR = @GIVENSINCEFISCALYEAR_HH output,
                      @TOTALREVENUEWITHGIFTAID = @TOTALREVENUEWITHGIFTAID_HH output,

                      @FIRSTID = @FIRSTID_HH output,
                      @FIRSTRECORDID = @FIRSTRECORDID_HH output,
                      @FIRSTDATE = @FIRSTDATE_HH output,
                      @FIRSTTYPECODE = @FIRSTTYPECODE_HH output,
                      @FIRSTTYPE = @FIRSTTYPE_HH output,
                      @FIRSTAMOUNT = @FIRSTAMOUNT_HH output,
                      @LATESTID = @LATESTID_HH output,
                      @LATESTRECORDID = @LATESTRECORDID_HH output,
                      @LATESTDATE = @LATESTDATE_HH output,
                      @LATESTTYPECODE = @LATESTTYPECODE_HH output,
                      @LATESTTYPE = @LATESTTYPE_HH output,
                      @LATESTAMOUNT = @LATESTAMOUNT_HH output,  
                      @ONLYGETSUMMARY = 0,
                      @BREAKDOWNBY = @BREAKDOWNBY,
                      @CAMPAIGNFILTERMODE = @CAMPAIGNFILTERMODE,
                      @CAMPAIGNSSELECTED = @CAMPAIGNSSELECTED
                   end     



                  select
                      @TOTALNUMBER as TOTALNUMBER,
                      @TOTALAMOUNT as TOTALAMOUNT,
                      @TOTALAMOUNT_HOUSEHOLD as TOTALAMOUNT_HOUSEHOLD,
                      @TOTALYEARS as TOTALYEARS,
                      @GIVENSINCEFISCALYEAR as GIVENSINCEFISCALYEAR,
                      @CONSECUTIVEYEARS as CONSECUTIVEYEARS,
                      @TOTALREVENUEWITHGIFTAID as TOTALREVENUEWITHGIFTAID,

                      @FIRSTID as FIRSTID,
                      @FIRSTRECORDID as FIRSTRECORDID,
                      @FIRSTDATE as FIRSTDATE,
                      @FIRSTTYPECODE as FIRSTTYPECODE,
                      @FIRSTTYPE as FIRSTTYPE,
                      @FIRSTAMOUNT as FIRSTAMOUNT,
                      @LATESTID as LATESTID,
                      @LATESTRECORDID as LATESTRECORDID,
                      @LATESTDATE as LATESTDATE,
                      @LATESTTYPECODE as LATESTTYPECODE,
                      @LATESTTYPE as LATESTTYPE,
                      @LATESTAMOUNT as LATESTAMOUNT,

                      @CURRENCYISOCURRENCYCODE as CURRENCYISOCURRENCYCODE,
                      @CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
                      @CURRENCYSYMBOL as CURRENCYSYMBOL,
                      @CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,

                      --household specific fields

                      @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;