USP_DATALIST_FUNDRAISERCAMPAIGN

Returns all of the campaigns a fundraiser is assigned to.
Parameters

Parameter	Parameter Type	Mode	Description
@FUNDRAISERID	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	xml	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.
@VIEWINACTIVE	bit	IN	Show prior campaigns
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_FUNDRAISERCAMPAIGN (
                    @FUNDRAISERID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null,
                    @VIEWINACTIVE bit = 0
                ) as begin

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

                    declare @MINCOMPARERESULT int;
                    set @MINCOMPARERESULT = case @VIEWINACTIVE when 0 then 0 else 1 end;

                    with XMLNAMESPACES ('bb_appfx_dataforms' as DFI)
                    select
                        CAMPAIGNFUNDRAISER.ID,
                        CAMPAIGN.NAME [CAMPAIGNNAME],
                        (
                            select top 1 
                                KPIINSTANCE.ID
                            from 
                                dbo.KPIINSTANCE 
                            where 
                                KPIINSTANCE.KPICATALOGID = '3ac33006-0c14-4227-b375-3bf2e61da1e1'
                            and
                                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CAMPAIGNID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNFUNDRAISER.CAMPAIGNID as varchar(36))
                            and
                                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="FUNDRAISERID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNFUNDRAISER.CONSTITUENTID as varchar(36))
                        ) as KPIINSTANCEID,
                        CAMPAIGNFUNDRAISER.CONSTITUENTID as KPICONTEXTID,
                        CAMPAIGNFUNDRAISERPOSITIONCODE.DESCRIPTION as POSITION,
                        CAMPAIGNFUNDRAISERTASKCODE.DESCRIPTION as TASK,
                        (
                            select dbo.UDA_BUILDLIST(SITE.NAME)
                            from dbo.SITE
                                inner join dbo.CAMPAIGNSITE on CAMPAIGNSITE.SITEID = SITE.ID
                            where CAMPAIGNSITE.CAMPAIGNID = CAMPAIGN.ID
                        ) as SITES,
                        CAMPAIGNFUNDRAISER.DATEFROM,
                        CAMPAIGNFUNDRAISER.DATETO,
                        CAMPAIGN.ID as CAMPAIGNID,
                        CAMPAIGNFUNDRAISER.STATUS as TIMEFRAME
                    from
                        dbo.CAMPAIGNFUNDRAISER
                    left outer join
                        dbo.CAMPAIGN on CAMPAIGNFUNDRAISER.CAMPAIGNID = CAMPAIGN.ID
                    left join
                        dbo.CAMPAIGNFUNDRAISERPOSITIONCODE on CAMPAIGNFUNDRAISER.CAMPAIGNFUNDRAISERPOSITIONCODEID = CAMPAIGNFUNDRAISERPOSITIONCODE.ID
                    left join 
                        dbo.CAMPAIGNFUNDRAISERTASKCODE on CAMPAIGNFUNDRAISER.CAMPAIGNFUNDRAISERTASKCODEID = CAMPAIGNFUNDRAISERTASKCODE.ID
                    where
                        CAMPAIGNFUNDRAISER.CONSTITUENTID = @FUNDRAISERID
                        and (
                            select count(*)
                            from dbo.UFN_SITEID_MAPFROM_CAMPAIGNID(CAMPAIGN.ID) as CAMPAIGNSITE
                            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CAMPAIGNSITE].[SITEID] or (SITEID is null and [CAMPAIGNSITE].[SITEID] is null)))
                        ) > 0
                        and (
                            @SITEFILTERMODE = 0
                                or CAMPAIGN.ID in ( 
                                    select CAMPAIGNSITE.CAMPAIGNID
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                        inner join dbo.CAMPAIGNSITE on CAMPAIGNSITE.SITEID = SITEFILTER.SITEID
                                )
                        )
                        and dbo.UFN_DATE_COMPARETODATERANGE(@CURRENTDATE, CAMPAIGNFUNDRAISER.DATEFROM, CAMPAIGNFUNDRAISER.DATETO) <= @MINCOMPARERESULT

                    order by CAMPAIGNNAME, POSITION;
                end