UFN_FAFADDRESSBOOK_GETCONTACTDETAILS

CREATE function dbo.UFN_FAFADDRESSBOOK_GETCONTACTDETAILS(
    @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,  
        CASE WHEN D.DATEADDED IS NOT NULL THEN D.DATEADDED ELSE '' END As DONDATE,  
        ISNULL((SELECT COALESCE(  
                        CASE WHEN PART.CONSTITUENTID IS NOT NULL THEN PART.DATEADDED ELSE NULL END,    
                        CASE WHEN TM.CONSTITUENTID IS NOT NULL THEN TM.DATEADDED ELSE NULL END,  
                        CASE WHEN HH.CONSTITUENTID IS NOT NULL THEN HH.DATEADDED ELSE NULL END   
                   )),'') AS REGDATE,   

        (  
            case   
                when PART.CONSTITUENTID IS NOT NULL then (select count(*) from dbo.UFN_FAFPARTICIPANT_DONORS(@EVENTID ,PART.CONSTITUENTID))  
                when TM.CONSTITUENTID IS NOT NULL then (select count(*) from dbo.UFN_FAFPARTICIPANT_DONORS(@EVENTID ,TM.CONSTITUENTID))  
                when HH.CONSTITUENTID IS NOT NULL then (select count(*) from dbo.UFN_FAFPARTICIPANT_DONORS(@EVENTID ,HH.CONSTITUENTID))  
            else -1  
            end  
         )  AS DONORS,  

         D.CURRENTDONATIONAMOUNT AS DONATIONAMOUNT,      

      --   CASE WHEN R.ID IS NOT NULL OR (HH.CONSTITUENTID IS NOT NULL  AND HH.ISHOUSEHOLDLEADER = 'y') THEN   

            CASE WHEN R.ID IS NOT NULL THEN
         --    CASE WHEN (HH.CONSTITUENTID IS NOT NULL  AND HH.ISHOUSEHOLDLEADER = 'y') THEN   

         --        (SELECT TE.TARGETFUNDRAISINGGOAL FROM dbo.TEAMEXTENSION (NOLOCK) TE   

         --            INNER JOIN dbo.UFN_FAF_HOUSEHOLDLEADERLIST(HH.CONSTITUENTID,@EVENTID) HHL ON HHL.TEAMID = TE.TEAMFUNDRAISINGTEAMID WHERE HHL.ID = HH.CONSTITUENTID)  

         --    ELSE  

                 ISNULL( (SELECT RE.TARGETFUNDRAISINGGOAL FROM REGISTRANTEXTENSION RE WHERE RE.REGISTRANTID=R.ID), '-1') 
          --   END   

         ELSE -1 END  AS GOAL,  

   --      CASE WHEN HH.CONSTITUENTID IS NOT NULL THEN    

   --           CASE WHEN HH.ISHOUSEHOLDLEADER = 'y' THEN   

   --              (SELECT ([dbo].[UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNT](@EVENTID,(SELECT TE.TEAMCONSTITUENTID from dbo.TEAMEXTENSION (NOLOCK) TE   

   --                  INNER JOIN dbo.UFN_FAF_HOUSEHOLDLEADERLIST(HH.CONSTITUENTID,@EVENTID) HHL ON HHL.TEAMID = TE.TEAMFUNDRAISINGTEAMID WHERE HHL.ID = HH.CONSTITUENTID))))  

   --           ELSE 0 END  

   --       ELSE  


              CASE WHEN R.ID IS NOT NULL THEN [dbo].[UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL](R.ID, @EVENTID) ELSE 0 END  REGISTRANTRAISED,

   --      END AS REGISTRANTRAISED,  

         (SELECT COUNT(FCL.CLIENTUSERSID) FROM dbo.FAFCOMMUNICATIONSLOG FCL WHERE FCL.ADDRESSBOOKID=A.ID AND FCL.MESSAGETYPECODE=1 AND FCL.CLIENTUSERSID=@CLIENTUSERID AND FCL.EVENTID=@EVENTID)AS NUMOFSENTMESSAGES,  
             CASE WHEN TM.CONSTITUENTID IS NOT NULL THEN 1 ELSE 0 END AS [ISTEAMMEMBER],      
             CASE WHEN D.CONSTITUENTID IS NOT NULL THEN 1 ELSE 0 END AS [ISDONOR],    
             CASE WHEN D.CONSTITUENTID IS NULL AND TM.CONSTITUENTID IS NULL THEN 1 ELSE 0 END AS [ISPROSPECT],  
             CASE WHEN D.STATUS IS NULL THEN 'N/A' ELSE D.STATUS END AS DONORSTATUS,    
             CASE WHEN TM.STATUS IS NULL THEN 'N/A' ELSE TM.STATUS END AS TEAMSTATUS,  
             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,   
             CASE WHEN TM.ISTEAMCAPTAIN IS NOT NULL THEN 1 ELSE 0 END AS [ISTEAMCAPTAIN],  
             --CASE WHEN R.ID IS NOT NULL THEN (SELECT SIE.[SITEIMAGESID] from STORY S (NOLOCK) LEFT OUTER JOIN dbo.SITEIMAGESEXTENSION SIE (NOLOCK) ON S.FAFIMAGESID = SIE.ID WHERE S.REGISTRANTID = R.ID) ELSE NULL END AS FAFIMAGEID,  

             null AS FAFIMAGEID,
         R.ID AS REGISTRANTID  
   FROM [dbo].[ADDRESSBOOKFAF] A (NOLOCK)    
        LEFT JOIN [dbo].[UFN_FAFADDRESSBOOK_PARTICIPANTS_LIST](@EVENTID,@CLIENTUSERID) PART    
           ON A.CONSTITUENTID = PART.CONSTITUENTID        
        LEFT JOIN [dbo].[UFN_FAFADDRESSBOOK_HOUSEHOLDMEMBERS_LIST](@EVENTID,@CLIENTUSERID) HH  
           ON A.CONSTITUENTID = HH.CONSTITUENTID      
        LEFT JOIN [dbo].[UFN_FAFADDRESSBOOK_TEAMMEMBERS_LIST](@EVENTID,@CLIENTUSERID) TM      
           ON  A.CONSTITUENTID = TM.CONSTITUENTID            
        LEFT JOIN [dbo].UFN_FAFADDRESSBOOK_DONORS_LIST(@EVENTID,@CLIENTUSERID) D      
           ON  A.CONSTITUENTID = D.CONSTITUENTID    
     --LEFT JOIN dbo.UFN_FAFADDRESSBOOK_PROSPECTS_LIST(@EVENTID,@CLIENTUSERID) P   

     -- ON P.CONSTITUENTID=D.CONSTITUENTID  

        LEFT JOIN [dbo].REGISTRANT R (NOLOCK)  
           ON R.EVENTID= @EVENTID AND R.CONSTITUENTID = A.CONSTITUENTID  
    WHERE A.CLIENTUSERSID = @CLIENTUSERID