USP_DATALIST_FAF_COMMENTS

Returns all FAF Comments records.
Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	EventID
@REGISTRANTID	uniqueidentifier	IN	RegistrantID
@TEAMFUNDRAISINGTEAMID	uniqueidentifier	IN	WidgetID
@ISEVENTONLY	bit	IN	Iseventonly
@COMMENTID	uniqueidentifier	IN	Commentid
@CLIENTUSERSID	int	IN	Clientusers
Definition

Copy


CREATE procedure dbo.USP_DATALIST_FAF_COMMENTS
                (    @EVENTID uniqueidentifier, 
                    @REGISTRANTID uniqueidentifier = null,
                    @TEAMFUNDRAISINGTEAMID uniqueidentifier = NULL,
          @ISEVENTONLY bit = 0,
          @COMMENTID uniqueidentifier = null,
          @CLIENTUSERSID int = null
                )with execute as caller
as
    set nocount on;

  IF @ISEVENTONLY = 0
      select 
          FAFCOMMENTS.[ID],
          FAFCOMMENTS.[EVENTID],
          FAFCOMMENTS.[TEAMFUNDRAISINGTEAMID],
          FAFCOMMENTS.[REGISTRANTID],
          FAFCOMMENTS.[CLIENTUSERSID],
          ISNULL(FAFCOMMENTS.[AUTHORNAME],(ISNULL(CU.firstname,'') + ' '+ ISNULL(CU.lastname,''))) as AUTHORNAME,
          FAFCOMMENTS.[COMMENTTEXT],
          FAFCOMMENTS.[LIKECOUNT],
          FAFCOMMENTS.[DISLIKECOUNT],
          FAFCOMMENTS.[ROOTCOMMENTID],
              FAFCOMMENTS.[DATEADDED],
          (SELECT ID FROM FAFCOMMENTSAPPROVAL WHERE clientusersid = @CLIENTUSERSID and COMMENTID = FAFCOMMENTS.[ID]) as FAFCOMMENTSAPPROVALID,
          (SELECT ISAPPROVED FROM FAFCOMMENTSAPPROVAL WHERE clientusersid = @CLIENTUSERSID and COMMENTID = FAFCOMMENTS.[ID]) as ISAPPROVED
      from 
          dbo.FAFCOMMENTS (NOLOCK)
      Left outer join dbo.CLIENTUSERS CU (NOLOCK)
      ON CU.ID = FAFCOMMENTS.CLIENTUSERSID
    left outer join dbo.FAFCOMMENTS PC (NOLOCK)
    ON FAFCOMMENTS.ROOTCOMMENTID = PC.ID
      where
          FAFCOMMENTS.EVENTID = @EVENTID
          AND ISNULL(FAFCOMMENTS.REGISTRANTID,'00000000-0000-0000-0000-000000000000') = ISNULL(ISNULL(@REGISTRANTID,FAFCOMMENTS.REGISTRANTID),'00000000-0000-0000-0000-000000000000')
          AND ISNULL(FAFCOMMENTS.TEAMFUNDRAISINGTEAMID,'00000000-0000-0000-0000-000000000000') = ISNULL(ISNULL(@TEAMFUNDRAISINGTEAMID,FAFCOMMENTS.TEAMFUNDRAISINGTEAMID),'00000000-0000-0000-0000-000000000000')
      AND FAFCOMMENTS.ID = ISNULL(@COMMENTID,FAFCOMMENTS.ID)
      ORDER BY PC.DATEADDED desc, FAFCOMMENTS.DATEADDED asc
      --  order by FAFCOMMENTS.ROOTCOMMENTID desc, FAFCOMMENTS.ID asc

  ELSE
    select 
          FAFCOMMENTS.[ID],
          FAFCOMMENTS.[EVENTID],
          FAFCOMMENTS.[TEAMFUNDRAISINGTEAMID],
          FAFCOMMENTS.[REGISTRANTID],
          FAFCOMMENTS.[CLIENTUSERSID],
          ISNULL(FAFCOMMENTS.[AUTHORNAME],(ISNULL(CU.firstname,'') + ' '+ ISNULL(CU.lastname,''))) as AUTHORNAME,
          FAFCOMMENTS.[COMMENTTEXT],
          FAFCOMMENTS.[LIKECOUNT],
          FAFCOMMENTS.[DISLIKECOUNT],
          FAFCOMMENTS.[ROOTCOMMENTID],
              FAFCOMMENTS.[DATEADDED],
          (SELECT ID FROM dbo.FAFCOMMENTSAPPROVAL WHERE clientusersid = @CLIENTUSERSID and COMMENTID = FAFCOMMENTS.[ID]) as FAFCOMMENTSAPPROVALID,
          (SELECT ISAPPROVED FROM dbo.FAFCOMMENTSAPPROVAL WHERE clientusersid = @CLIENTUSERSID and COMMENTID = FAFCOMMENTS.[ID]) as ISAPPROVED
      from 
          dbo.FAFCOMMENTS (NOLOCK)
      Left outer join dbo.CLIENTUSERS CU (NOLOCK)
      ON CU.ID = FAFCOMMENTS.CLIENTUSERSID
    left outer join dbo.FAFCOMMENTS PC (NOLOCK)
    ON FAFCOMMENTS.ROOTCOMMENTID = PC.ID
      where
          FAFCOMMENTS.EVENTID = @EVENTID
          AND FAFCOMMENTS.REGISTRANTID IS NULL
          AND FAFCOMMENTS.TEAMFUNDRAISINGTEAMID IS NULL
      AND FAFCOMMENTS.ID = ISNULL(@COMMENTID,FAFCOMMENTS.ID)
      ORDER BY PC.DATEADDED desc, FAFCOMMENTS.DATEADDED asc
  --      order by FAFCOMMENTS.ROOTCOMMENTID desc, FAFCOMMENTS.ID asc