USP_DATALIST_ACTIONITEMS

This datalist returns action items for a constituent.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DateRange	int	IN	Show
@TypeCode	tinyint	IN	Type
Definition

Copy


CREATE procedure dbo.USP_DATALIST_ACTIONITEMS 
(
    @CONSTITUENTID uniqueidentifier,
  @DateRange int = 10,
  @TypeCode tinyint = 10

)
as
    set nocount on;    

  declare @CurrentDate  datetime
  declare @StartDate    datetime

  if @DateRange = 10 Or @DateRange = 5
  begin
      SET ROWCOUNT @DateRange
  end

  Set @CurrentDate = getdate()
  If @DateRange < 30
    begin
     set @StartDate = '01/01/1980';  
    end
  else
    begin  
     Set @StartDate = DATEADD([day], -@DateRange, @CurrentDate) 
    end


if @TypeCode = 0 
   Begin
         Select 
            AA.ID as ID,
            Convert(varchar, AA.DATEADDED, 101) As ActionDate,
            AI.Type As ActionType,
            AI.NAME As ActionName, 
            SC.Title As ActionCenter,
            AI.TypeCode as ActionTypeCode
       From dbo.ADVOCACYACTIVITY (Nolock) AA
            Inner Join dbo.ACTION_ITEM (nolock) AI On AA.ACTIONITEMID = AI.ID
            Inner Join dbo.SiteContent (nolock) SC On AA.SiteContentID = SC.ID
       Where AA.CONSTITUENTID = @CONSTITUENTID
            And AA.DATEADDED between @StartDate and @CurrentDate
            and AI.Type = 'Action'
       Order By AA.DateAdded desc 
   End
else if @TypeCode = 1 
   Begin
         Select 
            AA.ID as ID,
            Convert(varchar, AA.DATEADDED, 101) As ActionDate,
            AI.Type As ActionType,
            AI.NAME As ActionName, 
            SC.Title As ActionCenter,
            AI.TypeCode as ActionTypeCode
       From dbo.ADVOCACYACTIVITY (Nolock) AA
            Inner Join dbo.ACTION_ITEM (nolock) AI On AA.ACTIONITEMID = AI.ID
            Inner Join dbo.SiteContent (nolock) SC On AA.SiteContentID = SC.ID
       Where AA.CONSTITUENTID = @CONSTITUENTID
            And AA.DATEADDED between @StartDate and @CurrentDate
            and AI.Type = 'Petition'
       Order By AA.DateAdded desc  
   End
else 
   Begin
       Select
            AA.ID as ID,
            Convert(varchar, AA.DATEADDED, 101) As ActionDate,
            AI.Type As ActionType,
            AI.NAME As ActionName, 
            SC.Title As ActionCenter,
            AI.TypeCode as ActionTypeCode
       From dbo.ADVOCACYACTIVITY (Nolock) AA
            Inner Join dbo.ACTION_ITEM (nolock) AI On AA.ACTIONITEMID = AI.ID
            Inner Join dbo.SiteContent (nolock) SC On AA.SiteContentID = SC.ID
       Where AA.CONSTITUENTID = @CONSTITUENTID
            And AA.DATEADDED between @StartDate and @CurrentDate
       Order By AA.DateAdded desc 
   End