USP_DATALIST_CONSTITUENTPURPOSES

Summary of purposes to which this constituent has made donations.
Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@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.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_CONSTITUENTPURPOSES
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @CONSTITUENTID uniqueidentifier,
                    @SECURITYFEATUREID uniqueidentifier,
                    @SECURITYFEATURETYPE tinyint
              )
                as
                set nocount on;

                declare @TEMPTBL table 
                (
                   ID uniqueidentifier,
                   DESIGNATIONLEVEL nvarchar(100),
                   LOOKUPID nvarchar(100),
                   TOTAL money,
                   NUMBERGIFTS integer
                )

        insert into @TEMPTBL(ID, DESIGNATIONLEVEL, LOOKUPID, TOTAL, NUMBERGIFTS)
                    select 
                        DESIGNATIONLEVEL.ID,            
                        DESIGNATIONLEVEL.NAME as DESIGNATIONLEVEL,
                        DESIGNATIONLEVEL.USERID as LOOKUPID,    
                        sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, DESIGNATIONLEVEL.BASECURRENCYID)) as TOTAL,
                        count(DISTINCT REVENUE.ID) as NUMBERGIFTS
                    from dbo.REVENUE
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                    inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                    inner join dbo.DESIGNATIONLEVEL DESIGNATIONLEVEL
                        on (
                                DESIGNATION.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL.ID or
                                DESIGNATION.DESIGNATIONLEVEL2ID = DESIGNATIONLEVEL.ID or
                                DESIGNATION.DESIGNATIONLEVEL3ID = DESIGNATIONLEVEL.ID or             
                                DESIGNATION.DESIGNATIONLEVEL4ID = DESIGNATIONLEVEL.ID or
                                DESIGNATION.DESIGNATIONLEVEL5ID = DESIGNATIONLEVEL.ID
                            )
                    where REVENUE.CONSTITUENTID = @CONSTITUENTID     
                        and --JamesWill 2009-02-10 Work Item 22436 Only include certain types of revenue records

                        (
                            (REVENUE.TRANSACTIONTYPECODE in (1, 4)) --Pledges, Planned Gifts

                            or
                            (REVENUE.TRANSACTIONTYPECODE= 0 and 
                                (
                                    REVENUESPLIT.APPLICATIONCODE in (0, 1, 3, 4, 5, 7, 8) --Donations, Event registration payment, Recurring gift payment, Other, Membership payment, Matching gift payment, grant payment

                                )
                            )
                        )
                    group by DESIGNATIONLEVEL.ID, DESIGNATIONLEVEL.NAME, DESIGNATIONLEVEL.USERID


                select
                    T.ID,
                    DESIGNATIONLEVELTYPE.DESCRIPTION as [TYPE],
                    T.DESIGNATIONLEVEL,
                    T.LOOKUPID,
                    T.TOTAL,
                    T.NUMBERGIFTS,
                    case
                        when exists(
                                select ID 
                                from dbo.DESIGNATIONLEVELDONORINFORMATION                     
                                where DESIGNATIONLEVELID = T.ID 
                                        and CONSTITUENTID = @CONSTITUENTID
                                        and ISBENEFACTOR = 1
                            ) then 1
                        else 0            
                    end as FOUNDER,
          DESIGNATIONLEVEL.BASECURRENCYID as CURRENCYID                                    
                from @TEMPTBL T
                inner join dbo.DESIGNATIONLEVEL on DESIGNATIONLEVEL.ID = T.ID
                inner join dbo.DESIGNATIONLEVELTYPE on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = DESIGNATIONLEVELTYPE.ID

                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[DESIGNATIONLEVEL].[SITEID] or (SITEID is null and [DESIGNATIONLEVEL].[SITEID] is null)))

                order by DESIGNATIONLEVELTYPE.DESCRIPTION asc, T.DESIGNATIONLEVEL asc