USP_DATALIST_FAFADDRESSBOOKCONTACTDETAILSSEARCH

CREATE procedure dbo.USP_DATALIST_FAFADDRESSBOOKCONTACTDETAILSSEARCH  
(    
     @CLIENTUSERID int,    
     @EVENTID uniqueidentifier,    
     @SEARCHSTRING varchar(50) = '',
     @IsUnittest bit = 0
) 

as    
set nocount on;    

DECLARE @EMPTYGUID uniqueidentifier;    
SET @EMPTYGUID = '00000000-0000-0000-0000-000000000000';    

DECLARE @CONSTITUENTID uniqueidentifier    
SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERID)    

DECLARE @PREVIOUSEVENTID uniqueidentifier    
select @PREVIOUSEVENTID = prioryeareventid from dbo.EVENTEXTENSION (NOLOCK) WHERE EVENTID = @EVENTID    

IF ISNULL(@SEARCHSTRING,'') > ''    
SET @SEARCHSTRING = '%'+@SEARCHSTRING+'%'    

 declare @TEAMID uniqueidentifier, @COMPANYID uniqueidentifier    

  DECLARE @MYADDRESSBOOK table (    
             GROUPNAME        nvarchar(100),    
             CATEGORYSTATUS   nvarchar(100),    
             ADDRESSBOOKID    uniqueidentifier null,    
             NAME             varchar(256) null,    
             CONSTITUENTID    uniqueidentifier null,    
             EMAILADDRESS     varchar(200) null,    
             REGISTRANTID     uniqueidentifier null,
             ALLOWOTHERPARTICIPANTSCONTACTME bit  default(1)
      ) 

  Insert Into @MYADDRESSBOOK Exec USP_FAFADDRESSBOOK_GETALLGROUPMEMBERS @TYPE='Current', @EVENTID = @EVENTID, @CONSTITUENTID = @CONSTITUENTID

 -- add previous members    

  IF @PREVIOUSEVENTID IS NOT NULL  Begin  
        DECLARE @MYADDRESSBOOKPREVIOUS table (    
            GROUPNAME        nvarchar(100),    
            CATEGORYSTATUS   nvarchar(100),    
            ADDRESSBOOKID    uniqueidentifier null,    
            NAME             varchar(256) null,    
            CONSTITUENTID    uniqueidentifier null,    
            EMAILADDRESS     varchar(200) null,    
            REGISTRANTID     uniqueidentifier null,
            ALLOWOTHERPARTICIPANTSCONTACTME bit  default(1)
       )    

       Insert Into @MYADDRESSBOOKPREVIOUS Exec USP_FAFADDRESSBOOK_GETALLGROUPMEMBERS @TYPE='Previous', @EVENTID = @PREVIOUSEVENTID, @CONSTITUENTID = @CONSTITUENTID

       INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
          SELECT PA.GROUPNAME,PA.NAME,PA.CONSTITUENTID    
                FROM @MYADDRESSBOOKPREVIOUS PA    
                LEFT OUTER JOIN @MYADDRESSBOOK MA    
                ON MA.CONSTITUENTID = PA.CONSTITUENTID    
          WHERE MA.CONSTITUENTID IS NULL    
   End    

   --final modification of book   

 /*    
  INSERT INTO @MYADDRESSBOOK (GROUPNAME, ADDRESSBOOKID, NAME, CONSTITUENTID, EMAILADDRESS)     
  SELECT    
 (CASE ISNULL(D.STATUS,'S')   
  WHEN 'A' THEN 'Donors - current'      
  WHEN 'P' THEN 'Donors - previous'    
 END), D.CONTACTID, ltrim(rtrim(replace(isnull(A.FIRSTNAME+' ', '') + isnull(A.LASTNAME, ''), '  ', ' '))), D.CONSTITUENTID, A.EMAILADDRESS  
  FROM dbo.UFN_FAFADDRESSBOOK_DONORS_LIST(@EVENTID, @CLIENTUSERID) D  
  INNER JOIN dbo.ADDRESSBOOKFAF A (NOLOCK) ON A.ID = D.CONTACTID      
*/    

  INSERT INTO @MYADDRESSBOOK (GROUPNAME, ADDRESSBOOKID, NAME, CONSTITUENTID, EMAILADDRESS)     
  select 'Donors - current', ABF.ID, ltrim(rtrim(replace(isnull(ABF.FIRSTNAME+' ', '') + isnull(ABF.LASTNAME, ''), '  ', ' '))), ABF.CONSTITUENTID, ABF.EMAILADDRESS 
  from REVENUERECOGNITION RR
  inner join FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = RR.REVENUESPLITID 
  INNER JOIN REVENUESPLIT_EXT RS_EXT on FTLI.ID = RS_EXT.ID 
  inner join FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID 
  inner join REVENUE_EXT R_EXT on R_EXT.ID = FT.ID 
  inner join EVENT E on R_EXT.APPEALID = E.APPEALID 
  inner join ADDRESSBOOKFAF ABF on ABF.CONSTITUENTID = FT.CONSTITUENTID and ABF.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID)) 
  where 
      RR.CONSTITUENTID in 
(
          select @CONSTITUENTID
                  union
          select TE.TEAMCONSTITUENTID from TEAMFUNDRAISINGTEAMCAPTAIN  TFTC
          inner join TEAMFUNDRAISINGTEAM TFT on TFTC.TEAMFUNDRAISINGTEAMID = TFT.ID 
          inner join TEAMEXTENSION TE on TFT.ID = TE.TEAMFUNDRAISINGTEAMID 
          where TE.EVENTID = @EVENTID 
          and TFTC.CONSTITUENTID = @CONSTITUENTID 
      )
    and E.ID = @EVENTID
      and not (FT.TYPECODE = 0 and RS_EXT.APPLICATIONCODE = 1)

  if @PREVIOUSEVENTID is not null
  begin
    INSERT INTO @MYADDRESSBOOK (GROUPNAME, ADDRESSBOOKID, NAME, CONSTITUENTID, EMAILADDRESS)     
    select 'Donors - previous', ABF.ID, ltrim(rtrim(replace(isnull(ABF.FIRSTNAME+' ', '') + isnull(ABF.LASTNAME, ''), '  ', ' '))), ABF.CONSTITUENTID, ABF.EMAILADDRESS 
    from REVENUERECOGNITION RR
    inner join FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = RR.REVENUESPLITID 
    INNER JOIN REVENUESPLIT_EXT RS_EXT on FTLI.ID = RS_EXT.ID 
    inner join FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID 
    inner join REVENUE_EXT R_EXT on R_EXT.ID = FT.ID 
    inner join EVENT E on R_EXT.APPEALID = E.APPEALID 
    inner join ADDRESSBOOKFAF ABF on ABF.CONSTITUENTID = FT.CONSTITUENTID and ABF.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID)) 
    where 
        RR.CONSTITUENTID in
      (
            select @CONSTITUENTID
                    union
            select TE.TEAMCONSTITUENTID from TEAMFUNDRAISINGTEAMCAPTAIN  TFTC
            inner join TEAMFUNDRAISINGTEAM TFT on TFTC.TEAMFUNDRAISINGTEAMID = TFT.ID 
            inner join TEAMEXTENSION TE on TFT.ID = TE.TEAMFUNDRAISINGTEAMID 
            where TE.EVENTID = @PREVIOUSEVENTID 
            and TFTC.CONSTITUENTID = @CONSTITUENTID 
        )
        and E.ID = @PREVIOUSEVENTID
        and not (FT.TYPECODE = 0 and RS_EXT.APPLICATIONCODE = 1)
  end


  INSERT INTO @MYADDRESSBOOK (GROUPNAME, ADDRESSBOOKID, NAME, CONSTITUENTID, EMAILADDRESS)    
        select 'Contacts', ID, ltrim(rtrim(replace(isnull(FIRSTNAME+' ', '') + isnull(MIDDLENAME+' ', '') + isnull(LASTNAME, ''), '  ', ' '))) as KEYNAME,    
        CONSTITUENTID, EMAILADDRESS    
        FROM dbo.ADDRESSBOOKFAF WITH (NOLOCK)    
        where CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID) )   
        AND ISNULL(CONSTITUENTID,@EMPTYGUID) NOT IN (SELECT CONSTITUENTID FROM @MYADDRESSBOOK)    
       -- AND ISNULL(CONSTITUENTID,@EMPTYGUID) NOT IN (SELECT CONSTITUENTID FROM dbo.REGISTRANT (NOLOCK) WHERE EVENTID = @EVENTID)    

        AND ISNULL(CONSTITUENTID,@EMPTYGUID) NOT IN (select ID AS CONSTITUENTID from dbo.UFN_FAF_COMPANYLEADERLIST(@PREVIOUSEVENTID))

  UPDATE MA    
        SET EMAILADDRESS = E.EMAILADDRESS    
        FROM @MYADDRESSBOOK MA    
        INNER JOIN dbo.EMAILADDRESS E WITH (NOLOCK)    
        ON MA.CONSTITUENTID = E.CONSTITUENTID    
        AND E.ISPRIMARY = 1    
  WHERE MA.GROUPNAME <> 'Contacts'    

 UPDATE MA    
       SET EMAILADDRESS = (CASE WHEN ISNULL(AB.EMAILADDRESS,'') > '' THEN AB.EMAILADDRESS ELSE MA.EMAILADDRESS END),    
          ADDRESSBOOKID = AB.ID    
       FROM @MYADDRESSBOOK MA    
       INNER JOIN dbo.ADDRESSBOOKFAF AB WITH (NOLOCK)    
       ON MA.CONSTITUENTID = AB.CONSTITUENTID    
  where AB.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID) )     

  UPDATE MA    
      SET REGISTRANTID = R.ID, ALLOWOTHERPARTICIPANTSCONTACTME = RE.ALLOWOTHERPARTICIPANTSCONTACTME    
      FROM @MYADDRESSBOOK MA    
      INNER JOIN dbo.REGISTRANT R WITH (NOLOCK)  
      ON R.CONSTITUENTID = MA.CONSTITUENTID  
      INNER JOIN dbo.REGISTRANTEXTENSION RE WITH (NOLOCK)   
      ON R.ID = RE.REGISTRANTID  
      AND R.EVENTID = @EVENTID 
  WHERE MA.CONSTITUENTID IS NOT NULL    

  UPDATE MA      
      SET ADDRESSBOOKID = NULL      
      FROM @MYADDRESSBOOK MA     
  WHERE ISNULL(MA.CONSTITUENTID, @EMPTYGUID) = @CONSTITUENTID  

 if @IsUnittest = 1
      Set RowCount 1

 IF ISNULL(@SEARCHSTRING,'') > ''      
   BEGIN      
     SELECT      
        A.GROUPNAME,      
        CASE   
             WHEN CATEGORYSTATUS IS NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(GROUPNAME)  
             WHEN CATEGORYSTATUS IS NOT NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(CATEGORYSTATUS)  
        END AS CATEGORYSTATUS,      
        A.ADDRESSBOOKID,      
        A.NAME,      
        A.CONSTITUENTID,      
        A.EMAILADDRESS,      
        A.REGISTRANTID,
        AD.ID,AD.FIRSTNAME,AD.LASTNAME, 
        AD.DONDATE, AD.REGDATE, AD.DONORS, AD.DONATIONAMOUNT,
        AD.GOAL, AD.REGISTRANTRAISED, AD.NUMOFSENTMESSAGES,
        AD.ISTEAMMEMBER, AD.ISDONOR, AD.ISPROSPECT,
        AD.DONORSTATUS, AD.TEAMSTATUS, 
        AD.MIDDLENAME,AD.PREFERREDCOMMUNICATION,AD.SALUTATION,AD.ADDRESSBLOCK,
        AD.CITY,AD.STATEID,AD.POSTCODE,AD.COUNTRYID,AD.CELLPHONE,AD.TEXTMESSAGESEND,  
        AD.HOMEPHONE,AD.BUSINESSPHONE,AD.BUSINESSADDRESS,AD.SECONDARYEMAILADDRESS,AD.SECONDARYCITY,
        AD.SECONDARYSTATEID,AD.SECONDARYPOSTCODE,AD.SECONDARYCOUNTRYID,   
        AD.ISTEAMCAPTAIN, AD.FAFIMAGEID,
        A.ALLOWOTHERPARTICIPANTSCONTACTME
     FROM @MYADDRESSBOOK A
           Left Join dbo.UFN_FAFADDRESSBOOK_CONTACTDETAILS(@EVENTID, @CLIENTUSERID) AD
           On A.ADDRESSBOOKID = AD.ID
     WHERE A.NAME LIKE @SEARCHSTRING 
   END      
 ELSE      
   BEGIN      
     SELECT 
        A.GROUPNAME,      
        CASE   
             WHEN CATEGORYSTATUS IS NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(GROUPNAME) 
             WHEN CATEGORYSTATUS IS NOT NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(CATEGORYSTATUS)  
        END AS CATEGORYSTATUS,      
        A.ADDRESSBOOKID,      
        A.NAME,      
        A.CONSTITUENTID,      
        A.EMAILADDRESS,      
        A.REGISTRANTID,
        AD.ID, AD.FIRSTNAME,AD.LASTNAME,
        AD.DONDATE, AD.REGDATE, AD.DONORS, AD.DONATIONAMOUNT,
        AD.GOAL, AD.REGISTRANTRAISED, AD.NUMOFSENTMESSAGES,
        AD.ISTEAMMEMBER, AD.ISDONOR, AD.ISPROSPECT,
        AD.DONORSTATUS, AD.TEAMSTATUS, 
        AD.MIDDLENAME,AD.PREFERREDCOMMUNICATION,AD.SALUTATION,AD.ADDRESSBLOCK,
        AD.CITY,AD.STATEID,AD.POSTCODE,AD.COUNTRYID,AD.CELLPHONE,AD.TEXTMESSAGESEND,  
        AD.HOMEPHONE,AD.BUSINESSPHONE,AD.BUSINESSADDRESS,AD.SECONDARYEMAILADDRESS,AD.SECONDARYCITY,
        AD.SECONDARYSTATEID,AD.SECONDARYPOSTCODE,AD.SECONDARYCOUNTRYID,   
        AD.ISTEAMCAPTAIN, AD.FAFIMAGEID,
        A.ALLOWOTHERPARTICIPANTSCONTACTME
     FROM @MYADDRESSBOOK A
        Left Join dbo.UFN_FAFADDRESSBOOK_CONTACTDETAILS(@EVENTID, @CLIENTUSERID) AD
        On A.ADDRESSBOOKID = AD.ID
 END