USP_DATALIST_PROSPECTRESEARCHREQUEST

This datalist returns information about prospect research requests.

Parameters

Parameter	Parameter Type	Mode	Description
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@STATUSCODE	smallint	IN	Status
@NUMBERTOSHOWCODE	tinyint	IN	Request date
@INCLUDECANCELED	bit	IN	Include canceled
@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_PROSPECTRESEARCHREQUEST(
    @CONTEXTID uniqueidentifier,
    @STATUSCODE smallint = 99,
    @NUMBERTOSHOWCODE tinyint = 0,
    @INCLUDECANCELED bit = 0,

    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,

    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null
)
as
    set nocount on;

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

    declare @STARTDATE datetime;
    if @NUMBERTOSHOWCODE = 1 --Today

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);
    else if @NUMBERTOSHOWCODE = 2 --Last 7 Days

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-6, @CURRENTDATE));
    else if @NUMBERTOSHOWCODE = 3 --Last 30 Days

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
    else if @NUMBERTOSHOWCODE = 4 --Last 90 Days

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
    else if @NUMBERTOSHOWCODE = 5 --Last 6 Months

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(month,-6,@CURRENTDATE)));
    else if @NUMBERTOSHOWCODE = 6 --Last Year

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)))
    else
        set @STARTDATE = @CURRENTDATE;

    declare @ENDDATE datetime;
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

    select top(500)
        PRR.ID,
        PRR.REQUESTNO,
        PRR.DUEDATE,
        PRR.STATUS,
        PRR.RECORDTYPE,
        (select count(ID) from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where PROSPECTRESEARCHREQUESTID = PRR.ID and PROSPECTRESEARCHREQUESTCONSTITUENT.STATUSCODE <> 6) as NUMBEROFCONSTITUENTS,
        PRRT.DESCRIPTION as REQUESTTYPE,
        NF.NAME as RESEARCHER,
        PRR.STATUSCODE,
        (
            select dbo.UDA_BUILDLIST(SITE.NAME) 
            from dbo.PROSPECTRESEARCHREQUESTSITE
            inner join dbo.SITE on SITE.ID = PROSPECTRESEARCHREQUESTSITE.SITEID
            where PROSPECTRESEARCHREQUESTID = PRR.ID
        ) as [SITES]
    from 
        dbo.PROSPECTRESEARCHREQUEST PRR
    left join
        dbo.PROSPECTRESEARCHREQUESTTYPECODE PRRT on PRRT.ID = PRR.PROSPECTRESEARCHREQUESTTYPECODEID
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(PRR.ASSIGNEDTOID) NF
    where
        (@NUMBERTOSHOWCODE = 0 or PRR.DATEADDED between @STARTDATE and @ENDDATE) and
        (@STATUSCODE = 99 or PRR.STATUSCODE = @STATUSCODE) and
        ((PRR.STATUSCODE <> 6) or @INCLUDECANCELED = 1) and
        (@CONTEXTID = PRR.REQUESTEDBYID or @CONTEXTID = PRR.SUBMITTEDBYID) and
        dbo.UFN_PROSPECTRESEARCHREQUEST_USERHASSITEACCESS(PRR.ID, @CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1 and
        (--Apply user-defined site filtering 

            @SITEFILTERMODE = 0 
            or 
            exists (select ID from dbo.PROSPECTRESEARCHREQUESTSITE PRRS 
                    inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) S on S.SITEID = PRRS.SITEID
                    where PRRS.PROSPECTRESEARCHREQUESTID = PRR.ID) 
        )
    order by PRR.DATEADDED desc