USP_FAFADDRESSBOOK_REGISTRANT_ADD

CREATE procedure dbo.USP_FAFADDRESSBOOK_REGISTRANT_ADD (    
    @TEAMFUNDRAISINGTEAMID uniqueidentifier,  
    @REGISTRANTCONSTITUENTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
  @NEWTEAMFUNDRAISINGTEAMID uniqueidentifier = null --only used in the event of a restart

) 

as

set nocount on;

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

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

declare @SequenceID int;
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 @SequenceID = C.SEQUENCEID,
        @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 = @REGISTRANTCONSTITUENTID;  

declare @ClientUsersID int;
set @ClientUsersID = dbo.fnGetUserIDFromLinkedRecordID(@SequenceID, 0);

DECLARE @ISTEAMCAPTAINOFRESTARTEDTEAM bit
SET @ISTEAMCAPTAINOFRESTARTEDTEAM = CASE WHEN @NEWTEAMFUNDRAISINGTEAMID is null THEN 0 Else 1 END

begin try

    create table #TEMP_TEAMPEOPLE
    (        
        CLIENTUSERSID int,
        CONSTITUENTID uniqueidentifier,
        FIRSTNAME nvarchar(255),
        LASTNAME nvarchar(255),
        MIDDLENAME nvarchar(100),
        TITLECODEID uniqueidentifier,
        EMAILADDRESS nvarchar(200),
        HOMEPHONE nvarchar(25),
        ADDRESSBLOCK nvarchar(300),
        CITY nvarchar(100),
        STATEID uniqueidentifier,
        COUNTRYID uniqueidentifier,
        POSTCODE nvarchar(24),
        FOUNDREGISTRANTADDRESSBOOKID uniqueidentifier,
        FOUNDMEMBERADDRESSBOOKID uniqueidentifier,
        RECIPROCAL tinyint -- 0 = both way; 1 = only to team captain; 2 = only to registrant

    );

    insert into #TEMP_TEAMPEOPLE
    (        
        CLIENTUSERSID,
        CONSTITUENTID,
        FIRSTNAME,
        LASTNAME,
        MIDDLENAME,
        TITLECODEID,
        EMAILADDRESS,
        HOMEPHONE,
        ADDRESSBLOCK,
        CITY,
        STATEID,
        COUNTRYID,
        POSTCODE,
        FOUNDREGISTRANTADDRESSBOOKID,
        FOUNDMEMBERADDRESSBOOKID,
        RECIPROCAL
    )
    select
        dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0),
        C.ID,
        C.FIRSTNAME,
        C.KEYNAME,
        C.MIDDLENAME,
        C.TITLECODEID,
        isnull(E.EMAILADDRESS, '') as EMAILADDRESS,
        isnull(P.NUMBER, '') as NUMBER,
        isnull(A.ADDRESSBLOCK, '') as ADDRESSBLOCK,
        isnull(A.CITY, '') as CITY,
        A.STATEID,
        A.COUNTRYID,
        isnull(A.POSTCODE, '') as POSTCODE,
        dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0), @REGISTRANTCONSTITUENTID),
        dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(@ClientUsersID, C.ID),
        0 -- bothway

    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
    inner join
        dbo.TEAMFUNDRAISER TR(nolock) on TR.CONSTITUENTID = C.ID
    inner join
        dbo.TEAMFUNDRAISINGTEAMMEMBER TM(nolock) on TM.TEAMFUNDRAISERID = TR.ID
    inner join
        dbo.TEAMEXTENSION TE(nolock) on TE.TEAMFUNDRAISINGTEAMID = TM.TEAMFUNDRAISINGTEAMID
    where TE.TYPECODE in (1, 3) -- team and household

        and TE.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID
        and C.ID <> @REGISTRANTCONSTITUENTID

    union
    -- adding team child household leader

    select
        dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0),
        C.ID,
        C.FIRSTNAME,
        C.KEYNAME,
        C.MIDDLENAME,
        C.TITLECODEID,
        isnull(E.EMAILADDRESS, '') as EMAILADDRESS,
        isnull(P.NUMBER, '') as NUMBER,
        isnull(A.ADDRESSBLOCK, '') as ADDRESSBLOCK,
        isnull(A.CITY, '') as CITY,
        A.STATEID,
        A.COUNTRYID,
        isnull(A.POSTCODE, '') as POSTCODE,
        dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0), @REGISTRANTCONSTITUENTID),
        dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(@ClientUsersID, C.ID),
        0 -- bothway

    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
    inner join
        dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC(nolock) on TC.CONSTITUENTID = C.ID
    inner join
        dbo.TEAMEXTENSION TE(nolock) on TE.TEAMFUNDRAISINGTEAMID = TC.TEAMFUNDRAISINGTEAMID
    inner join
        dbo.TEAMFUNDRAISINGTEAM T(nolock) on T.ID = TE.TEAMFUNDRAISINGTEAMID
    inner join
        dbo.TEAMEXTENSION PTE(nolock) on PTE.TEAMFUNDRAISINGTEAMID = T.PARENTTEAMID
    where TE.TYPECODE = 3 -- household

        and PTE.TYPECODE = 1 -- team

        and PTE.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID
        and C.ID <> @REGISTRANTCONSTITUENTID

    union
    -- adding to company leader

    select dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0),
        null, null, null, null, null, null, null, null, null, null, null, null,
        dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0), @REGISTRANTCONSTITUENTID),
        null,
        1 -- one way

    from dbo.CONSTITUENT C(nolock)
    inner join TEAMFUNDRAISINGTEAMCAPTAIN TC(nolock) 
        on C.ID = TC.CONSTITUENTID
    inner join dbo.TEAMEXTENSION TE(nolock) 
        on TE.TEAMFUNDRAISINGTEAMID = TC.TEAMFUNDRAISINGTEAMID
    where TE.TYPECODE = 2 -- company

        and TE.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID
        and C.ID <> @REGISTRANTCONSTITUENTID;

    -- adding household donor to registrant

    insert into #TEMP_TEAMPEOPLE
    (        
        CLIENTUSERSID,
        CONSTITUENTID,
        FIRSTNAME,
        LASTNAME,
        MIDDLENAME,
        TITLECODEID,
        EMAILADDRESS,
        HOMEPHONE,
        ADDRESSBLOCK,
        CITY,
        STATEID,
        COUNTRYID,
        POSTCODE,
        FOUNDREGISTRANTADDRESSBOOKID,
        FOUNDMEMBERADDRESSBOOKID,
        RECIPROCAL
    )
    select
        dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0),
        C.ID,
        C.FIRSTNAME,
        C.KEYNAME,
        C.MIDDLENAME,
        C.TITLECODEID,
        isnull(E.EMAILADDRESS, '') as EMAILADDRESS,
        isnull(P.NUMBER, '') as NUMBER,
        isnull(A.ADDRESSBLOCK, '') as ADDRESSBLOCK,
        isnull(A.CITY, '') as CITY,
        A.STATEID,
        A.COUNTRYID,
        isnull(A.POSTCODE, '') as POSTCODE,
        dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0), @REGISTRANTCONSTITUENTID),
        dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(@ClientUsersID, C.ID),
        2 -- add to registrant

    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
    inner join
        dbo.REVENUE R(nolock) on R.CONSTITUENTID = C.ID
    inner join 
        dbo.REVENUESPLIT RS(nolock) on RS.REVENUEID = R.ID
    inner join 
        dbo.REVENUERECOGNITION RR(nolock) on RR.REVENUESPLITID = RS.ID
    inner join
        dbo.TEAMEXTENSION TE(nolock) on TE.TEAMCONSTITUENTID = RR.CONSTITUENTID
    where TE.TYPECODE = 3 -- household

        and TE.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID
        and C.ID <> @REGISTRANTCONSTITUENTID
        and C.ID not in    (select TP.CONSTITUENTID from #TEMP_TEAMPEOPLE TP(nolock) where TP.RECIPROCAL = 0);

  --add in general team donors from old team (if user is a team captain of a restarted team)

  If @ISTEAMCAPTAINOFRESTARTEDTEAM=1
  BEGIN
  insert into #TEMP_TEAMPEOPLE  
   (          
    CLIENTUSERSID,  
    CONSTITUENTID,  
    FIRSTNAME,  
    LASTNAME,  
    MIDDLENAME,  
    TITLECODEID,  
    EMAILADDRESS,  
    HOMEPHONE,  
    ADDRESSBLOCK,  
    CITY,  
    STATEID,  
    COUNTRYID,  
    POSTCODE,  
    FOUNDREGISTRANTADDRESSBOOKID,  
    FOUNDMEMBERADDRESSBOOKID,  
    RECIPROCAL  
   )  
   select  
    dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0),  
    C.ID,  
    C.FIRSTNAME,  
    C.KEYNAME,  
    C.MIDDLENAME,  
    C.TITLECODEID,  
    isnull(E.EMAILADDRESS, '') as EMAILADDRESS,  
    isnull(P.NUMBER, '') as NUMBER,  
    isnull(A.ADDRESSBLOCK, '') as ADDRESSBLOCK,  
    isnull(A.CITY, '') as CITY,  
    A.STATEID,  
    A.COUNTRYID,  
    isnull(A.POSTCODE, '') as POSTCODE,  
    dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0), @REGISTRANTCONSTITUENTID),  
    dbo.UFN_FAFADDRESSBOOK_CONSTITUENT_LOOKUP(@ClientUsersID, C.ID),  
    2 -- add to registrant  

   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  
   inner join  
    dbo.REVENUE R(nolock) on R.CONSTITUENTID = C.ID  
   inner join   
    dbo.REVENUESPLIT RS(nolock) on RS.REVENUEID = R.ID  
   inner join   
    dbo.REVENUERECOGNITION RR(nolock) on RR.REVENUESPLITID = RS.ID  
   inner join  
    dbo.TEAMEXTENSION TE(nolock) on TE.TEAMCONSTITUENTID = RR.CONSTITUENTID  
   where TE.TYPECODE = 1 -- team

    and TE.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID  
    and C.ID <> @REGISTRANTCONSTITUENTID  
    and C.ID not in (select TP.CONSTITUENTID from #TEMP_TEAMPEOPLE TP(nolock) where TP.RECIPROCAL = 0);
  END

    --start processing address book


    --update the registrant's constituent id in member's address book

    update
        AB
    set
        CONSTITUENTID = @REGISTRANTCONSTITUENTID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from
        dbo.ADDRESSBOOKFAF AB
    inner join
        #TEMP_TEAMPEOPLE TP    on TP.CLIENTUSERSID = AB.CLIENTUSERSID
        and TP.FOUNDREGISTRANTADDRESSBOOKID = 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 = @REGISTRANTCONSTITUENTID
        )
        and TP.RECIPROCAL in (0, 1)
    where AB.CONSTITUENTID is null;

    --insert the registrant's information to member's address book

    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,
        @REGISTRANTCONSTITUENTID,
        @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 = @REGISTRANTCONSTITUENTID
        )
        and TP.CLIENTUSERSID is not null
        and TP.RECIPROCAL in (0, 1);

    if @ClientUsersID is not null
    begin
        --update the member's constituent id in registrant's address book

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


        --insert the member's information to registrant's address book

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

  drop table #TEMP_TEAMPEOPLE;

end try

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

return 0;