USP_DATALIST_GETCONTACTDETAILS

CREATE procedure dbo.USP_DATALIST_GETCONTACTDETAILS
(
    @EVENTID uniqueidentIFier = NULL,
    @CLIENTUSERID integer = NULL,
    @ADDRESSBOOKID uniqueidentIFier,
    --to be deleted

    @CONSTITUENTID uniqueidentIFier = NULL,
    @ROLECODE tinyint = 0,
    @REGISTRANTID uniqueidentIFier = NULL,
    @REGISTRANTTEAMID uniqueidentIFier = NULL,
    @FULLNAME nvarchar(100) = ''
    --to be deleted end


)
as
set nocount on;

---This datalist returns an address book contact by passing @ADDRESSBOOKID and CLIENTUSERID 


    DECLARE   
         @IsCompanyLeader tinyint   
    ,@IsCompanyIndividual tinyint

  select @CONSTITUENTID = CONSTITUENTID from ADDRESSBOOKFAF A WHERE  A.ID=@ADDRESSBOOKID
  EXEC dbo.USP_FAF_REGISTRANT_ROLE
        @CONSTITUENTID = @CONSTITUENTID
        ,@EVENTID =@EVENTID
        ,@IsCompanyLeader   = @IsCompanyLeader OUTPUT
    ,@IsCompanyIndividual = @IsCompanyIndividual OUTPUT

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 (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 
            (SELECT RE.TARGETFUNDRAISINGGOAL FROM REGISTRANTEXTENSION RE WHERE RE.REGISTRANTID=R.ID)
        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_CONSTITUENTRECOGNIZEDAMOUNT](@EVENTID, R.CONSTITUENTID) ELSE 0 END

      CASE WHEN R.ID IS NOT NULL THEN [dbo].[UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL](R.ID, @EVENTID) ELSE 0 END
    END AS REGISTRANTRAISED,
    (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,
    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 A.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,
    R.ID AS REGISTRANTID,
  CASE WHEN HH.CONSTITUENTID IS NOT NULL THEN 1 ELSE 0 END AS [ISHOUSEHOLD],
  HH.CURRENTHOUSEHOLDID,
  CASE WHEN HH.CONSTITUENTID IS NOT NULL THEN 1 ELSE 0 END AS [ISHOUSEHOLDMEMBER],
    CASE WHEN HH.ISHOUSEHOLDLEADER IS NOT NULL THEN 1 ELSE 0 END AS [ISHOUSEHOLDHEAD],
  @IsCompanyLeader AS [ISCOMPANYLEADER],
  @IsCompanyIndividual AS [ISCOMPANYINDIVIDUAL]

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 in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERID))
    AND A.ID=@ADDRESSBOOKID