USP_DATALIST_ADVOCACY_DISPLAY

create procedure dbo.USP_DATALIST_ADVOCACY_DISPLAY( @CONSTITUENT_ID VARCHAR(256), 
    @ACTIONITEMID  uniqueidentifier,@SITECONTENTID AS INT )
as
    set nocount on;


    DECLARE @ACTIONSTAKEN AS INT
    DECLARE @MESSAGESSENT AS INT


     SET @ACTIONSTAKEN = (  
          SELECT COUNT(*)   
          FROM dbo.ADVOCACYACTIVITY  (NOLOCK)
          WHERE ACTIONITEMID = @ACTIONITEMID  
          AND SITECONTENTID = @SITECONTENTID 
      )

      SET @MESSAGESSENT =   
     (  
          SELECT COUNT(*)  
          FROM dbo.ADVOCACYLOG (NOLOCK)  
          WHERE ACTIONITEMID = @ACTIONITEMID   
          AND SITECONTENTID = @SITECONTENTID   
          AND [STATUS] = 'Delivered'  
     )    

    SELECT ISNULL(CONSTITUENT.KEYNAME, ClientUsers.LastName) AS LASTNAME,
         ISNULL(CONSTITUENT.FIRSTNAME,ClientUsers.FirstName) AS FIRSTNAME, 
         ISNULL(CONSTITUENT.MiddleName, ClientUsers.MiddleInitial) as MIDDLEINITIAL,
         TITLECODE.DESCRIPTION AS TITLE, 
         CONSTITUENT.NAME,
        EMAILADDRESS.EMAILADDRESS, 
        [ADDRESS].ADDRESSBLOCK,
        [ADDRESS].CITY, 
        [STATE].DESCRIPTION as [STATE], 
        [STATE].ABBREVIATION,
        [ADDRESS].POSTCODE,
        COUNTRY.DESCRIPTION AS COUNTRY,
        COUNTRY.ABBREVIATION COUNTRY_ABBREVIATION,
        PHONE.NUMBER, 
        @ACTIONSTAKEN AS ACTIONSTAKEN, 
        @MESSAGESSENT AS MESSAGESSENT, 
        ClientUsers.UserName as USERNAME,
        ClientUsers.Password as PASSWORD 
    FROM CONSTITUENT  (NOLOCK)
    LEFT OUTER JOIN TITLECODE (NOLOCK)
        ON CONSTITUENT.TITLECODEID = TITLECODE.ID
    LEFT OUTER JOIN EMAILADDRESS  (NOLOCK)
        ON CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID AND EMAILADDRESS.ISPRIMARY = 1
    LEFT OUTER JOIN [ADDRESS] (NOLOCK)
        ON CONSTITUENT.ID = [ADDRESS].CONSTITUENTID AND [ADDRESS].ISPRIMARY = 1
    LEFT OUTER JOIN [STATE]  (NOLOCK)
        ON [ADDRESS].STATEID = [STATE].ID 
    LEFT OUTER JOIN COUNTRY (NOLOCK)
        ON COUNTRY.ID = [ADDRESS].COUNTRYID 
    LEFT OUTER JOIN PHONE (NOLOCK)
        ON CONSTITUENT.ID = PHONE.CONSTITUENTID AND PHONE.ISPRIMARY = 1
    LEFT OUTER JOIN BackOfficeSystemPeople (NOLOCK)
        on BackOfficeSystemPeople.BackofficeRecordID = CONSTITUENT.SEQUENCEID AND BackOfficeSystemPeople.BackOfficeSystemID = 0
    LEFT OUTER JOIN BackOfficeSystemUsers (NOLOCK)
        ON BackOfficeSystemUsers.BackofficePeopleID = BackOfficeSystemPeople.ID AND BackOfficeSystemUsers.[Current] = 1 
    LEFT OUTER JOIN ClientUsers (NOLOCK)
        ON ClientUsers.ID = BackOfficeSystemUsers.ClientUsersID AND  ClientUsers.Deleted = 0
    WHERE CONSTITUENT.ID = @CONSTITUENT_ID