UFN_FAFADDRESSBOOK_CONTACTDETAILS

CREATE function dbo.UFN_FAFADDRESSBOOK_CONTACTDETAILS(
    @EVENTID uniqueidentIFier = NULL,  
    @CLIENTUSERID integer = NULL
)
returns table
as
    return 
     ---This datalist returns an address book contact details by passing CLIENTUSERID   


  SELECT   
        A.ID,A.FIRSTNAME,A.LASTNAME,A.EMAILADDRESS,  
        A.MIDDLENAME, A.PREFERREDCOMMUNICATION, A.SALUTATION, A.ADDRESSBLOCK,
        A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID, A.CELLPHONE, A.TEXTMESSAGESEND,  
        A.HOMEPHONE, A.BUSINESSPHONE, A.BUSINESSADDRESS,
        A.SECONDARYEMAILADDRESS, A.SECONDARYCITY, A.SECONDARYSTATEID,
        A.SECONDARYPOSTCODE, A.SECONDARYCOUNTRYID,   
        IsNull((select top 1 RV.DATEADDED from REVENUE RV where RV.ConstituentID= A.ConstituentID order by RV.DATEADDED desc),'') As DONDATE, 
        IsNull(R.DateAdded, '') AS REGDATE,   
        --(Select count(*) from dbo.UFN_FAFParticipant_Donors(@EventID, R.ConstituentID)) AS DONORS,  

        DONORS = (Select IsNull(Donorcount, 0) from dbo.FAFRAISEDTOTAL where ConstituentID = R.ConstituentID and EventID = @EventID and RECEIVEDAMOUNT > 0),

         -- get donation amounts

        DONATIONAMOUNT = dbo.UFN_REVENUE_GETDONORRAISEDTOTAL(A.ConstituentID, @EVENTID, @CLIENTUSERID),      

        /*
        GOAL = 
           Case TS.TypeCode
              When 3 Then
                  Case When IsNull(TRC.ID, '00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000'  then IsNull(RT.TARGETFUNDRAISINGGOAL, 0)
                       Else IsNull(TS.TARGETFUNDRAISINGGOAL, 0)
                  End
              Else IsNull(RT.TARGETFUNDRAISINGGOAL, 0)
           End, */
        Goal =  IsNull(RT.TARGETFUNDRAISINGGOAL, 0),

        REGISTRANTRAISED = IsNull(dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(R.ID, @EVENTID), 0),
        /*REGISTRANTRAISED = 
           Case TS.TypeCode
              When 3 then Isnull(dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(TS.TEAMFUNDRAISINGTEAMID, @EVENTID), 0)
              Else IsNull(dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(R.ID, @EVENTID), 0)
           End,*/

         -- number of email sent

        (SELECT COUNT(FCL.CLIENTUSERSID) 
          FROM dbo.FAFCOMMUNICATIONSLOG FCL 
          WHERE FCL.ADDRESSBOOKID = A.ID AND FCL.MESSAGETYPECODE=1 
            AND FCL.EVENTID = @EVENTID 
            AND FCL.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERID))
        ) AS NUMOFSENTMESSAGES,  

        ISTEAMMEMBER =
            Case IsNull(TR.ID, '00000000-0000-0000-0000-000000000000')
                When '00000000-0000-0000-0000-000000000000' then 0
                Else 1
            End,
        --not decided here for performance 

        0 As ISDONOR,

        -- change prospect to contacts

        ISPROSPECT =
            Case IsNull(A.ConstituentID, '00000000-0000-0000-0000-000000000000')
                When '00000000-0000-0000-0000-000000000000' then 1
                Else 0
            End,
         ISTEAMCAPTAIN = 
            Case IsNull(TRC.ID, '00000000-0000-0000-0000-000000000000')
               When '00000000-0000-0000-0000-000000000000' then 0
               Else 1
            End,
        'N/A' AS DONORSTATUS,    
        TEAMSTATUS =
            Case IsNull(TS.Status, '')
               When 'Active' then 'A'
               Else 'N/A'
            End,
        null AS FAFIMAGEID,
        R.ID AS REGISTRANTID

   FROM [dbo].[ADDRESSBOOKFAF] A (NOLOCK)    
              LEFT JOIN [dbo].REGISTRANT R (NOLOCK)  
                 ON R.EVENTID= @EVENTID AND R.CONSTITUENTID = A.CONSTITUENTID  
              LEFT JOIN [dbo].TEAMFUNDRAISER TR
                 ON TR.ConstituentID = R.CONSTITUENTID
              LEFT JOIN [dbo].TEAMFUNDRAISINGTEAMCAPTAIN TRC
                 ON TRC.CONSTITUENTID = R.CONSTITUENTID
              LEFT JOIN [dbo].TEAMEXTENSION TS
                 ON TS.TEAMFUNDRAISINGTEAMID = TRC.TEAMFUNDRAISINGTEAMID
              LEFT JOIN [dbo].REGISTRANTEXTENSION RT
                 ON RT.RegistrantID = R.ID
    WHERE A.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERID))