USP_DATALIST_CONSTITUENTCORRESPONDENCERECENT

Returns a list of recent correspondence a constituent has received.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@MAXROWS	int	IN	Input parameter indicating the maximum number of rows to return.
@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_DATALIST_CONSTITUENTCORRESPONDENCERECENT
(
  @CURRENTAPPUSERID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @MAXROWS int,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as begin
    set nocount on;

    declare @CORRESPONDENCETYPECODE smallint = 99; --all types

    declare @SITEFILTERMODE tinyint = 0;
    declare @SITESSELECTED xml = null;
    declare @CHANNEL smallint = 99; --all channels

    declare @CURRENTDATE datetime = getdate();

    declare @DATEFILTER tinyint = 9;  -- last 12 months;

    declare @STARTDATE datetime;
    declare @ENDDATE datetime;

    /*
     Set the start date to 12 months ago and the end date to 12 months from now.
     Only "output" the start date on the first call and the end date on the second.
     It may be worth adding a "Within 12 months" option to date filters and making just
     one call to USP_RESOLVEDATEFILTER, but that is outside the scope of this fix.
    */
    exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER=@DATEFILTER, @STARTDATE=@STARTDATE output, @ENDDATE=@ENDDATE;
    set @DATEFILTER = 8; -- next 12 months

    exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER=@DATEFILTER, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE output;

    /*
     MAXROWS is passed in by the server as MAXROWS + 1. That means that the server will 
     always use one fewer row than the data list returns. This is used to determine if  
     the data list would have returned more rows if allowed. Unfortunately, if the  
     display order is different from the selection order, the row removed might be one 
     of the ten selected rows. In order to handle this, this data list will subtract 
     one from the MAXROWS if the value is not the default (501).
    */
    if @MAXROWS <> 501 and @MAXROWS > 0
        set @MAXROWS = @MAXROWS - 1;

    select
        [ID],
        [DATE],
        [TYPE],
        [DETAILS]
    from
        (
            select top(@MAXROWS)
                ID,
                DATESENT as [DATE],
                CORRESPONDENCETYPE as [TYPE],
                DETAILS as [DETAILS]
            from
                dbo.UFN_CONSTITUENT_CORRESPONDENCE(@CONSTITUENTID, @CURRENTAPPUSERID, @SITEFILTERMODE,
                    @SITESSELECTED, @SECURITYFEATUREID, @SECURITYFEATURETYPE, @CORRESPONDENCETYPECODE,
                    @STARTDATE, @ENDDATE, @CHANNEL)
            order by
                abs(datediff(dd, DATESENT, @CURRENTDATE)), DATESENT desc
        ) CONSTITUENTCORRESPONDENCERECENT
    order by 
        DATE desc

    return 0;
    end