USP_DATALIST_CONSTITUENT_INTERACTIONS2

List of interactions for a given constituent.

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DATEFILTER	tinyint	IN	Date
@STARTDATE	datetime	IN	Start date
@ENDDATE	datetime	IN	End date
@TYPEFILTER	uniqueidentifier	IN	Contact method
@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.
@STATUS	tinyint	IN	Status

Definition

Copy



CREATE procedure dbo.USP_DATALIST_CONSTITUENT_INTERACTIONS2
(
    @CONSTITUENTID uniqueidentifier,
    @DATEFILTER tinyint = 0,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @TYPEFILTER uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @STATUS tinyint = null
)
as begin

    set nocount on;

    declare @DATE datetime = getdate();

    if @DATEFILTER = 0 or @DATEFILTER is null    -- all

    begin
        set @STARTDATE = null;
        set @ENDDATE = null;
    end

    if @DATEFILTER = 1        -- this year

    begin
        set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATE, 0);
    end

    if @DATEFILTER = 2        -- this quarter

    begin
        set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0);
    end

    if @DATEFILTER = 3        -- this month

    begin
        set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0);
    end

    if @DATEFILTER = 4        -- this week

    begin
        set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0);
    end

    if @DATEFILTER = 5        -- next week

    begin
        set @STARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@DATE, 0);
    end

    if @DATEFILTER = 6        -- next month

    begin
        set @STARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@DATE, 0);
    end

    if @DATEFILTER = 7        -- next quarter

    begin
        set @STARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@DATE, 0);
    end

    if @DATEFILTER = 8        -- next year

    begin
        set @STARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@DATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@DATE, 0);    
    end;

    select 
        ID,
        DATE,
        STATUS,
        OWNER,
        OWNERID,
        OBJECTIVE,
        TYPE,
        HASDOCUMENTATION,
        ISMOVE,
        EVENT,
        HASRESPONSES,
        CATEGORY,
        SUBCATEGORY,
        VIEWFORMID,
        SITES, 
        STARTTIME,
        ENDTIME,
        TIMEZONE,
        ISSTEWARDSHIPSTEP,
        ISINDIVIDUALSTEP
    from dbo.UFN_CONSTITUENT_INTERACTIONS(
        @CONSTITUENTID,
        @STARTDATE,
        @ENDDATE,
        @TYPEFILTER,
        @CURRENTAPPUSERID,
        @SITEFILTERMODE,
        @SITESSELECTED,
        @SECURITYFEATUREID,
        @SECURITYFEATURETYPE,
        @STATUS
    )
    order by [DATE] desc;

end