USP_DATALIST_GIFTAIDDISQUALIFICATIONRULES

Returns a list of Gift Aid disqualification rules.
Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_GIFTAIDDISQUALIFICATIONRULES
                (
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null
                )
                as
                    set nocount on;

                    select
                        GIFTAIDDISQUALIFIEDBYAPPEAL.ID,
                        'Appeal' as BASEDON,
                        APPEAL.NAME as SELECTEDCRITERIA,
                        SITE.NAME as SITE
                    from
                        dbo.GIFTAIDDISQUALIFIEDBYAPPEAL
                        inner join dbo.APPEAL on APPEAL.ID = GIFTAIDDISQUALIFIEDBYAPPEAL.ID
                        left join SITE on SITE.ID = APPEAL.SITEID
                    where
                        dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, APPEAL.SITEID)=1 and
                            (@SITEFILTERMODE = 0
                                or exists(
                                    select 1
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                    where APPEAL.SITEID = SITEFILTER.SITEID)
                                )

                    UNION ALL

                    select
                        GIFTAIDDISQUALIFIEDBYCAMPAIGN.ID,
                        'Campaign' as BASEDON,
                        CAMPAIGN.NAME as SELECTEDCRITERIA,
                        SITE.NAME as SITE
                    from
                        dbo.GIFTAIDDISQUALIFIEDBYCAMPAIGN
                        inner join dbo.CAMPAIGN on CAMPAIGN.ID = GIFTAIDDISQUALIFIEDBYCAMPAIGN.ID
                        left join dbo.CAMPAIGNSITE on CAMPAIGNSITE.CAMPAIGNID = CAMPAIGN.ID
                        left join SITE on SITE.ID = CAMPAIGNSITE.SITEID
                    where
                        SITE.NAME is null or
                        (select count(*) from dbo.UFN_SITEID_MAPFROM_CAMPAIGNID(CAMPAIGN.ID) as VALIDSITES where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[VALIDSITES].[SITEID] or (SITEID is null and [VALIDSITES].[SITEID] is null)))) > 0 and
                            (@SITEFILTERMODE = 0
                                or CAMPAIGN.ID in ( 
                                    select CAMPAIGNSITE.CAMPAIGNID
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                        where SITE.ID = SITEFILTER.SITEID) 
                            )

                    UNION ALL

                    select
                        GIFTAIDDISQUALIFIEDBYDESIGNATION.ID,
                        'Designation' as BASEDON,
                        DESIGNATION.NAME as SELECTEDCRITERIA,
                        dbo.UFN_TRANSLATIONFUNCTION_SITE_GETNAME(dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(DESIGNATION.ID)) as SITE
                    from
                        dbo.GIFTAIDDISQUALIFIEDBYDESIGNATION
                        inner join dbo.DESIGNATION on DESIGNATION.ID = GIFTAIDDISQUALIFIEDBYDESIGNATION.ID
                    where
                        dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(DESIGNATION.ID))=1 and
                            (@SITEFILTERMODE = 0
                                or exists(
                                    select 1
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                    where dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(DESIGNATION.ID) = SITEFILTER.SITEID)
                                )

                    UNION ALL

                    select
                        GIFTAIDDISQUALIFIEDBYEVENT.ID,
                        'Event' as BASEDON,
                        EVENT.NAME as SELECTEDCRITERIA,
                        SITE.NAME as SITE
                    from
                        dbo.GIFTAIDDISQUALIFIEDBYEVENT
                        inner join dbo.EVENT on EVENT.ID = GIFTAIDDISQUALIFIEDBYEVENT.ID
                        left join dbo.EVENTSITE on EVENTSITE.EVENTID = EVENT.ID
                        left join SITE on SITE.ID = EVENTSITE.SITEID
                    where
                        (select count(*) from dbo.UFN_SITEID_MAPFROM_EVENTID(EVENT.ID) as VALIDSITES where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[VALIDSITES].[SITEID] or (SITEID is null and [VALIDSITES].[SITEID] is null)))) > 0 and
                            (@SITEFILTERMODE = 0
                                or EVENT.ID in ( 
                                    select EVENTSITE.EVENTID
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                        where SITE.ID = SITEFILTER.SITEID)
                        )

                    UNION ALL

                    select
                        GIFTAIDDISQUALIFIEDBYMEMBERSHIPPROGRAM.ID,
                        'Membership program' as BASEDON,
                        MEMBERSHIPPROGRAM.NAME as SELECTEDCRITERIA,
                        SITE.NAME as SITE
                    from
                        dbo.GIFTAIDDISQUALIFIEDBYMEMBERSHIPPROGRAM
                        inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = GIFTAIDDISQUALIFIEDBYMEMBERSHIPPROGRAM.ID
                        left join SITE on SITE.ID = MEMBERSHIPPROGRAM.SITEID
                    where
                        dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MEMBERSHIPPROGRAM.SITEID)=1 and
                            (@SITEFILTERMODE = 0
                                or exists(
                                    select 1
                                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                                    where MEMBERSHIPPROGRAM.SITEID = SITEFILTER.SITEID)
                                )

                    order by
                        BASEDON, SELECTEDCRITERIA, SITE