USP_CONSTITUENTSOLICITCODES_DATALIST

Constituent solicit code list

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@SITEID	uniqueidentifier	IN	Site
@SHOWEXPIRED	bit	IN	Show expired codes for
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATERANGE	smallint	IN	Date range
@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.

Definition

Copy



CREATE procedure dbo.USP_CONSTITUENTSOLICITCODES_DATALIST(
    @CONSTITUENTID uniqueidentifier, 
    @SITEID uniqueidentifier=null, 
    @SHOWEXPIRED bit=0, 
    @CURRENTAPPUSERID uniqueidentifier=null,
    @DATERANGE smallint=4,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    declare @CUTOFFDATE datetime
    if @DATERANGE = 1 -- Last 30 days

        set @CUTOFFDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,getdate()));
    else if @DATERANGE = 2 -- Last 60 days

        set @CUTOFFDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-59,getdate()));
    else if @DATERANGE = 3 -- Last 90 days

        set @CUTOFFDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,getdate()))
    else if @DATERANGE = 4 -- Last 6 months

        set @CUTOFFDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-6,getdate()));
    else if @DATERANGE = 5 -- Last year

        set @CUTOFFDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-1,getdate()));
    else if @DATERANGE = 6 -- Last 2 years

        set @CUTOFFDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(year,-2,getdate()));

    select 
        CONSTITUENTSOLICITCODE.ID,
        SOLICITCODE.DESCRIPTION,
        coalesce(SITE.NAME, 'All sites'),
        CONSTITUENTSOLICITCODE.STARTDATE,
        CONSTITUENTSOLICITCODE.ENDDATE,
        CONSTITUENTSOLICITCODE.COMMENTS,
        case when (CONSTITUENTSOLICITCODE.ENDDATE is null or CONSTITUENTSOLICITCODE.ENDDATE>getdate()) then 0 else 1 end "EXPIRED",
        1 EDITABLE,
        SOLICITCODE.ID as SOLICITCODEID,
        case
            when SOLICITCODE.CONSENTCODE = 0 then 255
            else CONSENTPREFERENCECODE
        end CONSENTPREFERENCECODE,
        DATAPROTECTIONEVIDENCESOURCECODE.[DESCRIPTION] SOURCE,
        case
            when SOLICITCODE.CONSENTCODE = 0 then null
            when LEN(SOURCEFILEPATH) <> 0 then 1 
            else 0 
        end  SOURCEFILEPATH,
        case 
            when SOLICITCODE.CONSENTCODE = 0 then null
            when LEN(PRIVACYPOLICYFILEPATH) <> 0 then 1 
            else 0 
        end  PRIVACYPOLICYFILEPATH,
        case 
            when SOLICITCODE.CONSENTCODE = 0 then null
            when LEN(SUPPORTINGINFORMATION) <> 0 then 1 
            else 0 
        end  SUPPORTINGINFORMATION,
        case 
            when SOLICITCODE.CONSENTCODE = 0 then null
            when LEN(CONSENTSTATEMENT) <> 0 then 1 
            else 0 
        end  CONSENTSTATEMENT,
     SOLICITCODE.CONSENTCODE as  CONSENTCODE
    from 
        dbo.CONSTITUENTSOLICITCODE 
        inner join dbo.SOLICITCODE on SOLICITCODE.ID=CONSTITUENTSOLICITCODE.SOLICITCODEID
        left join dbo.SITE on SOLICITCODE.SITEID=SITE.ID
        left join dbo.DATAPROTECTIONEVIDENCESOURCECODE on DATAPROTECTIONEVIDENCESOURCECODE.ID = CONSTITUENTSOLICITCODE.SOURCECODEID
    where
        (
            -- Special case.  Only copy this behavior if no site assignment means all sites.

            SOLICITCODE.SITEID is null 
            or 
            (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SOLICITCODE].[SITEID] or (SITEID is null and [SOLICITCODE].[SITEID] is null)))
        ) 
        and 
        (
            @SITEFILTERMODE = 0
            or 
            SOLICITCODE.SITEID in(select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
        )
        and 
        CONSTITUENTSOLICITCODE.CONSTITUENTID = @CONSTITUENTID
        and 
        (
            (
                @SHOWEXPIRED = 1 
                and 
                (
                    @CUTOFFDATE is null 
                    or 
                    datediff(day, ENDDATE, @CUTOFFDATE) <=0 
                )
            )
            or 
            ENDDATE is null 
            or 
            datediff(day, ENDDATE, getdate()) <= 0
        )
    order by 
        EXPIRED, CONSTITUENTSOLICITCODE.SEQUENCE