UFN_FAFADDRESSBOOK_DONORS_LIST

CREATE function [dbo].[UFN_FAFADDRESSBOOK_DONORS_LIST]  
(  
  @EVENTID            as uniqueidentifier  
 ,@CLIENTUSERID        as int  
)  
returns table  
as  
return  
    with 
     -- All current event donors

      [CURRENTEVENTDONORS]  
     as  
     (  
         select 
             CONSTITUENTID
            ,DONORID
            ,[DATE]
            ,case when [APPLIEDAMOUNT]> 0 then [APPLIEDAMOUNT] else [ORIGINALAMOUNT] end as [AMOUNT]        
         from    [dbo].[UFN_EVENT_DONORS](@EVENTID)
     ) 
     -- All previous event donors

     ,[PREVIOUSEVENTDONORS]  
     as  
     (  
         select 
             CONSTITUENTID
            ,DONORID
            ,[DATE]
            ,case when [APPLIEDAMOUNT]> 0 then [APPLIEDAMOUNT] else [ORIGINALAMOUNT] end as [AMOUNT]
         from    [dbo].[UFN_EVENT_DONORS]([dbo].[UFN_GETPREVIOUSEVENTID](@EVENTID))  
     )  
    -- All groups associated with this address book owner

    -- regardless which event

   ,[FAFGROUPS]
   as
   (
        select            
             TEAMCONSTITUENTID                as [GROUPCONSTITUENTID]
            ,TEAMEXTENSION.EVENTID                
        from    [dbo].[TEAMEXTENSION] (nolock)
        inner    join dbo.TEAMFUNDRAISINGTEAM (nolock)
        on        TEAMFUNDRAISINGTEAM.ID    = [TEAMEXTENSION].TEAMFUNDRAISINGTEAMID
        inner    join TEAMFUNDRAISINGTEAMMEMBER (nolock)
        on        TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
        inner    join TEAMFUNDRAISER (nolock)
        on        TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
        where    TEAMFUNDRAISER.CONSTITUENTID    = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERID) 
   )
    -- Current event groups (companies, team or household)

   ,[CURRENTEVENTGROUPS]
   as
   (
        select    FAFGROUPS.*        
        from    FAFGROUPS
        where    FAFGROUPS.EVENTID = @EVENTID  
    )
    -- Previous event groups (companies, team or household)

    ,[PREVIOUSEVENTGROUPS]
    as
   (
        select    FAFGROUPS.*        
        from    FAFGROUPS
        where    FAFGROUPS.EVENTID = [dbo].[UFN_GETPREVIOUSEVENTID](@EVENTID)  
    )
     -- Current event donors

    ,[CURRENTDONORS]
    as
    (
        -- Donors who donated the group of this captain who is the address book owner

        select    
             FAFDONORS.AMOUNT 
            ,FAFDONORS.DONORID
            ,FAFDONORS.[DATE]
        from    [CURRENTEVENTDONORS] as FAFDONORS
        inner    join [CURRENTEVENTGROUPS]
        on        [CURRENTEVENTGROUPS].GROUPCONSTITUENTID  = FAFDONORS.CONSTITUENTID

        union    all    

        -- Donors who donated to this address book owner

        select    
             FAFDONORS.AMOUNT 
            ,FAFDONORS.DONORID
            ,FAFDONORS.[DATE]
        from    [CURRENTEVENTDONORS] as FAFDONORS
        where    CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERID)                         
    )
    -- Previous event donors

    ,[PREVIOUSDONORS]
    as
    (
        -- Donors who donated the group of this captain who is the address book owner

        select    
             FAFDONORS.AMOUNT 
            ,FAFDONORS.DONORID
            ,FAFDONORS.[DATE]
        from    [PREVIOUSEVENTDONORS] as FAFDONORS
        inner    join [PREVIOUSEVENTGROUPS]
        on        [PREVIOUSEVENTGROUPS].[GROUPCONSTITUENTID]  = FAFDONORS.CONSTITUENTID

        union    all    

        -- Donors who donated to this address book owner

        select    
             FAFDONORS.AMOUNT 
            ,FAFDONORS.DONORID
            ,FAFDONORS.[DATE]
        from    [PREVIOUSEVENTDONORS] as FAFDONORS
        where    CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERID)                         
    )
      -- Address book contacts 

     ,[FAFAddressBook]  
     as  
     (  
          select   
                [ID]    AS [CONTACTID]  
               ,[CLIENTUSERSID]  
               ,[FIRSTNAME]  
               ,[LASTNAME]  
               ,[CONSTITUENTID]  
          from    [dbo].[ADDRESSBOOKFAF] (nolock)  
          where [CLIENTUSERSID] in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERID))
     ) 
    ,[ADDRESSBOOKDONORS]
    as
    (
      select    
         dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERID) AS [ABCID]
        ,A.CONTACTID  
        ,A.FIRSTNAME  
        ,A.LASTNAME  
        ,A.CONSTITUENTID  
        ,case   
            when CD.DONORID IS NOT NULL THEN 'A'  
            when PD.DONORID IS NOT NULL THEN 'P'  
         end as [STATUS]
        ,case  
            when CD.DONORID IS NOT NULL THEN CD.DATE  
            when PD.DONORID IS NOT NULL THEN PD.DATE  
         end as DATEADDED  
        ,  isnull(CD.AMOUNT,0) as CURRENTDONATIONAMOUNT
        ,  isnull(PD.AMOUNT,0) as PREVIOUSDONATIONAMOUNT
        ,(select APPEALID from [EVENT] where ID=@EVENTID) as APPEALID

      from    [FAFAddressBook] A (nolock)  
      left    join  CURRENTDONORS CD  
      on    A.CONSTITUENTID = CD.DONORID  
      left    join [PREVIOUSDONORS] PD  
      on    A.CONSTITUENTID = PD.DONORID  
     )  
    select  * from [ADDRESSBOOKDONORS] where [STATUS] is not null;