USP_FAFADDRESSBOOK_DONOR_ADD

CREATE procedure dbo.USP_FAFADDRESSBOOK_DONOR_ADD (    
    @RECIPIENTID uniqueidentifier,  
    @DONORCONSTITUENTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
) 

as

set nocount on;

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();

declare @FIRSTNAME nvarchar(255);
declare @LASTNAME nvarchar(255);
declare @MIDDLENAME nvarchar(100);
declare @TITLECODEID uniqueidentifier;
declare @EMAILADDRESS dbo.UDT_EMAILADDRESS;
declare @HOMEPHONE nvarchar(25);
declare @ADDRESSBLOCK nvarchar(300);
declare @CITY nvarchar(100);
declare @STATEID uniqueidentifier;
declare @COUNTRYID uniqueidentifier;
declare @POSTCODE nvarchar(24);

select @FIRSTNAME = C.FIRSTNAME,
        @LASTNAME = C.KEYNAME,
        @MIDDLENAME = C.MIDDLENAME,
        @TITLECODEID = C.TITLECODEID,
        @EMAILADDRESS = isnull(E.EMAILADDRESS, ''),
        @HOMEPHONE = isnull(P.NUMBER, ''),
        @ADDRESSBLOCK = isnull(A.ADDRESSBLOCK, ''),
        @CITY = isnull(A.CITY, ''),
        @STATEID = A.STATEID,
        @COUNTRYID = A.COUNTRYID,
        @POSTCODE = isnull(A.POSTCODE, '')
from dbo.CONSTITUENT C(nolock) 
left outer join dbo.EMAILADDRESS E(nolock) on E.CONSTITUENTID = C.ID and E.ISPRIMARY = 1
left outer join dbo.PHONE P(nolock) on P.CONSTITUENTID = C.ID and P.ISPRIMARY = 1
left outer join dbo.ADDRESS A(nolock) on A.CONSTITUENTID = C.ID and A.ISPRIMARY = 1
where C.ID = @DONORCONSTITUENTID;  

begin try

    create table #TEMP_TEAMPEOPLE
    (        
        CLIENTUSERSID int,
        FOUNDADDRESSBOOKID uniqueidentifier
    );

    insert into #TEMP_TEAMPEOPLE
    (        
        CLIENTUSERSID,
        FOUNDADDRESSBOOKID
    )
    select dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0),
        dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0), @DONORCONSTITUENTID)
    from dbo.CONSTITUENT C(nolock)
    inner join dbo.REGISTRANT R(nolock) on R.CONSTITUENTID = C.ID
    where R.ID = @RECIPIENTID

    -- all captains, includes company, household, and team

    union
    select dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0),
        dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0), @DONORCONSTITUENTID)
    from dbo.CONSTITUENT C(nolock)
    inner join TEAMFUNDRAISINGTEAMCAPTAIN TC(nolock) on C.ID = TC.CONSTITUENTID
    where TC.TEAMFUNDRAISINGTEAMID = @RECIPIENTID

    --update the donor's constituent id in people's address book

    update
        AB
    set
        CONSTITUENTID = @DONORCONSTITUENTID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from
        dbo.ADDRESSBOOKFAF AB
    inner join
        #TEMP_TEAMPEOPLE TP    on TP.CLIENTUSERSID = AB.CLIENTUSERSID
        and TP.FOUNDADDRESSBOOKID = AB.ID
        and    TP.CLIENTUSERSID not in
        (
            select TP.CLIENTUSERSID
            from #TEMP_TEAMPEOPLE TP(nolock)
            inner join ADDRESSBOOKFAF AB(nolock) on AB.CLIENTUSERSID = TP.CLIENTUSERSID
            and AB.CONSTITUENTID = @DONORCONSTITUENTID
        )
        and    TP.CLIENTUSERSID is not null
    where AB.CONSTITUENTID is null;


    insert into dbo.ADDRESSBOOKFAF 
    (
        ID,
        CLIENTUSERSID,
        CONSTITUENTID,
        FIRSTNAME,
        LASTNAME,
        MIDDLENAME,
        TITLECODEID,
        EMAILADDRESS,
        HOMEPHONE,
        ADDRESSBLOCK,
        CITY,
        STATEID,
        COUNTRYID,
        POSTCODE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select newid(),
        TP.CLIENTUSERSID,
        @DONORCONSTITUENTID,
        @FIRSTNAME,
        @LASTNAME,
        @MIDDLENAME,
        @TITLECODEID,
        @EMAILADDRESS,
        @HOMEPHONE,
        @ADDRESSBLOCK,
        @CITY,
        @STATEID,
        @COUNTRYID,
        @POSTCODE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from
        #TEMP_TEAMPEOPLE TP(nolock)
    where
        TP.CLIENTUSERSID not in
        (
            select TP.CLIENTUSERSID
            from #TEMP_TEAMPEOPLE TP(nolock)
            inner join ADDRESSBOOKFAF AB(nolock) on AB.CLIENTUSERSID = TP.CLIENTUSERSID
            and AB.CONSTITUENTID = @DONORCONSTITUENTID
        )
        and TP.CLIENTUSERSID is not null;

  drop table #TEMP_TEAMPEOPLE;

end try

begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;