USP_DATALIST_CONSTITUENT_RECOGNITIONSUMMARY

This datalist returns a summary of a constituent's recognition.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	nvarchar(max)	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@CURRENCYCODE	tinyint	IN	Currency
@DATEFILTER	tinyint	IN	Date range
@RECOGNITIONFILTERID	uniqueidentifier	IN
@BREAKDOWNBY	tinyint	IN	Breakdown
@HOUSEHOLDRECOGNITIONFILTERID	uniqueidentifier	IN
@CAMPAIGNFILTERMODE	tinyint	IN	Campaigns
@CAMPAIGNSSELECTED	nvarchar(max)	IN
Definition

Copy


        CREATE procedure dbo.USP_DATALIST_CONSTITUENT_RECOGNITIONSUMMARY
            (
                @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,
                @RECOGNITIONFILTERID uniqueidentifier = null,
                @BREAKDOWNBY tinyint = 0,
                @HOUSEHOLDRECOGNITIONFILTERID uniqueidentifier = null,
                @CAMPAIGNFILTERMODE tinyint = 0,
                @CAMPAIGNSSELECTED nvarchar(max) = null
            ) as
                set nocount on;

                declare
                  @STARTDATE datetime,
                  @ENDDATE datetime,
                  @HOUSEHOLDID uniqueidentifier = null,
                  @ISGROUP bit = 0,
                  @RECOGNITIONTOTALNUMBER int = null,
                  @RECOGNITIONTOTALAMOUNT money = null,
                  @RECOGNITIONTOTALAMOUNT_HOUSEHOLD money = null,
                  @RECOGNITIONTOTALYEARS int = null,
                  @RECOGNITIONCONSECUTIVEYEARS int = null,
                  @RECOGNITIONGIVENSINCEFISCALYEAR datetime = null,
                  @TOTALRECOGNITIONWITHGIFTAID money = null,

                  @RECOGNITIONFIRSTID uniqueidentifier = null,
                  @RECOGNITIONFIRSTRECORDID uniqueidentifier = null,
                  @RECOGNITIONFIRSTDATE datetime = null,
                  @RECOGNITIONFIRSTTYPECODE tinyint = null,
                  @RECOGNITIONFIRSTTYPE nvarchar(50) = null,
                  @RECOGNITIONFIRSTAMOUNT money = null,
                  @RECOGNITIONLATESTID uniqueidentifier = null,
                  @RECOGNITIONLATESTRECORDID uniqueidentifier = null,
                  @RECOGNITIONLATESTDATE datetime = null,
                  @RECOGNITIONLATESTTYPECODE tinyint = null,
                  @RECOGNITIONLATESTTYPE nvarchar(50) = null,
                  @RECOGNITIONLATESTAMOUNT money = null,         

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

          --household specific fields

          @RECOGNITIONTOTALNUMBER_HH int = null,
                  @RECOGNITIONTOTALAMOUNT_HH money = null,
          @RECOGNITIONTOTALAMOUNT_HOUSEHOLD_HH money = null,
                  @RECOGNITIONTOTALYEARS_HH int = null,
                  @RECOGNITIONCONSECUTIVEYEARS_HH int = null,
                  @RECOGNITIONGIVENSINCEFISCALYEAR_HH datetime = 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

          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_RECOGNITIONSUMMARYEXPANDED
              @CONSTITUENTID,
              @ISGROUP,
              @HOUSEHOLDID,
              @RECOGNITIONFILTERID,
              @STARTDATE,
              @ENDDATE,
              @CURRENTAPPUSERID,
              @SITEFILTERMODE,
              @SITESSELECTED,
              @SECURITYFEATUREID,
              @SECURITYFEATURETYPE,
              @CURRENCYCODE,

              @RECOGNITIONTOTALNUMBER output,
              @RECOGNITIONTOTALAMOUNT output,
              @RECOGNITIONTOTALAMOUNT_HOUSEHOLD output,
              @RECOGNITIONTOTALYEARS output,
              @RECOGNITIONCONSECUTIVEYEARS output,
              @RECOGNITIONGIVENSINCEFISCALYEAR output,
              @TOTALRECOGNITIONWITHGIFTAID output,

              @RECOGNITIONFIRSTID output,
              @RECOGNITIONFIRSTRECORDID output,
              @RECOGNITIONFIRSTDATE output,
              @RECOGNITIONFIRSTTYPECODE output,
              @RECOGNITIONFIRSTTYPE output,
              @RECOGNITIONFIRSTAMOUNT output,
              @RECOGNITIONLATESTID output,
              @RECOGNITIONLATESTRECORDID output,
              @RECOGNITIONLATESTDATE output,
              @RECOGNITIONLATESTTYPECODE output,
              @RECOGNITIONLATESTTYPE output,
              @RECOGNITIONLATESTAMOUNT output,
              @CURRENCYISOCURRENCYCODE output,
              @CURRENCYDECIMALDIGITS output,
              @CURRENCYSYMBOL output,
              @CURRENCYSYMBOLDISPLAYSETTINGCODE output,
              @BREAKDOWNBY,
              @CAMPAIGNFILTERMODE,
              @CAMPAIGNSSELECTED

          if @HOUSEHOLDID is not null
            begin
              exec dbo.USP_CONSTITUENT_RECOGNITIONSUMMARYEXPANDED
                @HOUSEHOLDID,
                0,
                null,
                @HOUSEHOLDRECOGNITIONFILTERID,
                @STARTDATE,
                @ENDDATE,
                @CURRENTAPPUSERID,
                @SITEFILTERMODE,
                @SITESSELECTED,
                @SECURITYFEATUREID,
                @SECURITYFEATURETYPE,
                @CURRENCYCODE,

                @RECOGNITIONTOTALNUMBER = @RECOGNITIONTOTALNUMBER_HH output,
                @RECOGNITIONTOTALAMOUNT = @RECOGNITIONTOTALAMOUNT_HH output,
                @RECOGNITIONTOTALYEARS = @RECOGNITIONTOTALYEARS_HH output,
                @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS_HH output,
                @RECOGNITIONGIVENSINCEFISCALYEAR = @RECOGNITIONGIVENSINCEFISCALYEAR_HH output,
                @TOTALRECOGNITIONWITHGIFTAID = @TOTALRECOGNITIONWITHGIFTAID_HH output,

                @RECOGNITIONFIRSTID = @RECOGNITIONFIRSTID_HH output,
                @RECOGNITIONFIRSTRECORDID = @RECOGNITIONFIRSTRECORDID_HH output,
                @RECOGNITIONFIRSTDATE = @RECOGNITIONFIRSTDATE_HH output,
                @RECOGNITIONFIRSTTYPECODE = @RECOGNITIONFIRSTTYPECODE_HH output,
                @RECOGNITIONFIRSTTYPE = @RECOGNITIONFIRSTTYPE_HH output,
                @RECOGNITIONFIRSTAMOUNT = @RECOGNITIONFIRSTAMOUNT_HH output,
                @RECOGNITIONLATESTID = @RECOGNITIONLATESTID_HH output,
                @RECOGNITIONLATESTRECORDID = @RECOGNITIONLATESTRECORDID_HH output,
                @RECOGNITIONLATESTDATE = @RECOGNITIONLATESTDATE_HH output,
                @RECOGNITIONLATESTTYPECODE = @RECOGNITIONLATESTTYPECODE_HH output,
                @RECOGNITIONLATESTTYPE = @RECOGNITIONLATESTTYPE_HH output,
                @RECOGNITIONLATESTAMOUNT = @RECOGNITIONLATESTAMOUNT_HH output,  

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

          select
              @RECOGNITIONTOTALNUMBER as RECOGNITIONTOTALNUMBER,
              @RECOGNITIONTOTALAMOUNT as RECOGNITIONTOTALAMOUNT,
              @RECOGNITIONTOTALAMOUNT_HOUSEHOLD as RECOGNITIONTOTALAMOUNT_HOUSEHOLD,
              @RECOGNITIONTOTALYEARS as RECOGNITIONTOTALYEARS,
              @RECOGNITIONGIVENSINCEFISCALYEAR as RECOGNITIONGIVENSINCEFISCALYEAR,
              @RECOGNITIONCONSECUTIVEYEARS as RECOGNITIONCONSECUTIVEYEARS,

              @RECOGNITIONFIRSTID as RECOGNITIONFIRSTID,
              @RECOGNITIONFIRSTRECORDID as RECOGNITIONFIRSTRECORDID,
              @RECOGNITIONFIRSTDATE as RECOGNITIONFIRSTDATE,
              @RECOGNITIONFIRSTTYPECODE as RECOGNITIONFIRSTTYPECODE,
              @RECOGNITIONFIRSTTYPE as RECOGNITIONFIRSTTYPE,
              @RECOGNITIONFIRSTAMOUNT as RECOGNITIONFIRSTAMOUNT,
              @RECOGNITIONLATESTID as RECOGNITIONLATESTID,
              @RECOGNITIONLATESTRECORDID as RECOGNITIONLATESTRECORDID,
              @RECOGNITIONLATESTDATE as RECOGNITIONLATESTDATE,
              @RECOGNITIONLATESTTYPECODE as RECOGNITIONLATESTTYPECODE,
              @RECOGNITIONLATESTTYPE as RECOGNITIONLATESTTYPE,
              @RECOGNITIONLATESTAMOUNT as RECOGNITIONLATESTAMOUNT,
              @TOTALRECOGNITIONWITHGIFTAID as TOTALRECOGNITIONWITHGIFTAID,

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

              --household specific fields

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