USP_ADDRESSBOOKFAF_BULKADD_CONTACTBYGROUP

CREATE procedure dbo.USP_ADDRESSBOOKFAF_BULKADD_CONTACTBYGROUP
(
  @GROUPID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @REGISTRANTCONSTITUENTID uniqueidentifier,
  @ROLECODE tinyint  -- 0: company leader, 1: team leader, 2: team member, 3: head of household, 4: household member

)
as

declare @CURRENTDATE datetime,
    @CLIENTUSERSID int,
    @FIRSTNAME nvarchar(255),
        @LASTNAME nvarchar(255),
        @MIDDLENAME nvarchar(100),
        @TITLECODEID uniqueidentifier,
        @EMAILADDRESS dbo.UDT_EMAILADDRESS,
        @HOMEPHONE nvarchar(25),
        @ADDRESSBLOCK nvarchar(300),
        @CITY nvarchar(100),
        @STATEID uniqueidentifier,
        @COUNTRYID uniqueidentifier,
        @POSTCODE nvarchar(24),
    @EVENTID uniqueidentifier,
    @PARENTGROUPID uniqueidentifier

set @CURRENTDATE = getdate()

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

select @EVENTID=EVENTID from dbo.TEAMEXTENSION where TEAMFUNDRAISINGTEAMID = @GROUPID
select @PARENTGROUPID=PARENTTEAMID from dbo.TEAMFUNDRAISINGTEAM where ID = @GROUPID

select    
    @CLIENTUSERSID = dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0),
        @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 on E.CONSTITUENTID = C.ID and E.ISPRIMARY = 1
left outer join dbo.PHONE P on P.CONSTITUENTID = C.ID and P.ISPRIMARY = 1
left outer join dbo.ADDRESS A on A.CONSTITUENTID = C.ID and A.ISPRIMARY = 1
where C.ID = @REGISTRANTCONSTITUENTID;

begin try

  /* add members to registrant 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 as USERID,
      C.ID,
      C.FIRSTNAME,
      C.KEYNAME,
      C.MIDDLENAME,
      C.TITLECODEID,
      isnull(EA.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,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
  FROM dbo.CONSTITUENT C
  join dbo.UFN_ADDRESSBOOKFAF_GETEXPECTEDGROUPMEMBER(@GROUPID, @REGISTRANTCONSTITUENTID, @CLIENTUSERSID,  @ROLECODE) M on C.ID = M.ID 
  left join dbo.EMAILADDRESS EA on C.ID = EA.CONSTITUENTID and EA.ISPRIMARY = 1
  left join dbo.PHONE P on C.ID = P.CONSTITUENTID and P.ISPRIMARY = 1
  left join dbo.[ADDRESS] A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1
  where C.ID not in (select CONSTITUENTID from dbo.ADDRESSBOOKFAF where CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@REGISTRANTCONSTITUENTID)) and CONSTITUENTID is not null)

  /* add registrant information to members' 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(),
      M.USERID,
    @REGISTRANTCONSTITUENTID,
      @FIRSTNAME,
      @LASTNAME,
      @MIDDLENAME,
      @TITLECODEID,
      @EMAILADDRESS,
      @HOMEPHONE,
      @ADDRESSBLOCK,
      @CITY,
      @STATEID,
      @COUNTRYID,
      @POSTCODE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
  from dbo.CONSTITUENT C 
  join dbo.UFN_ADDRESSBOOKFAF_GETEXPECTEDGROUPMEMBER(@GROUPID, @REGISTRANTCONSTITUENTID, @CLIENTUSERSID,  @ROLECODE) M 
    on C.ID = M.ID and M.USERID is not null /* M.USERID = null is an exception if entry did not create correctly.  Instead fail miserable, skip that record*/
  join ( select * from dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, @GROUPID) MR 
          union
         select * from dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, @PARENTGROUPID) PMR where PMR.RoleCode in (0,1)
    ) MR
    on M.ID = MR.CONSTITUENTID
  where ((@ROLECODE <> 0 and MR.RoleCode <> 0) OR (@ROLECODE = 0 and MR.RoleCode = 0) )
  and (select COUNT(*) from dbo.ADDRESSBOOKFAF ADFAF where M.USERID = ADFAF.CLIENTUSERSID and ADFAF.CONSTITUENTID = @REGISTRANTCONSTITUENTID) = 0 

  /* add registrant information to company leader's address book if fundraising role is individual and fundraising group type is company */ 
  insert into dbo.ADDRESSBOOKFAF (ID, CLIENTUSERSID,CONSTITUENTID,
            FIRSTNAME,LASTNAME, MIDDLENAME, TITLECODEID, EMAILADDRESS,HOMEPHONE,ADDRESSBLOCK,CITY,STATEID,COUNTRYID,POSTCODE,
            ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED )
  select
    NEWID(),
    dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0) as USERID,
    @REGISTRANTCONSTITUENTID,
      @FIRSTNAME,
      @LASTNAME,
      @MIDDLENAME,
      @TITLECODEID,
      @EMAILADDRESS,
      @HOMEPHONE,
      @ADDRESSBLOCK,
      @CITY,
      @STATEID,
      @COUNTRYID,
      @POSTCODE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
  FROM dbo.TEAMFUNDRAISINGTEAM TFT
  join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = TFT.ID
  join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC on TFTC.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
  join dbo.CONSTITUENT C on C.ID = TFTC.CONSTITUENTID
  where TFT.ID = @GROUPID and TX.TYPECODE = 2
  and C.ID <> @REGISTRANTCONSTITUENTID and @ROLECODE = 5
  and (select COUNT(1) FROM dbo.ADDRESSBOOKFAF ADFAF where dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0) = ADFAF.CLIENTUSERSID and ADFAF.CONSTITUENTID = @REGISTRANTCONSTITUENTID) = 0 

  /* add donor to registrant address book if applicable */
  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 as USERID,
      C.ID,
      C.FIRSTNAME,
      C.KEYNAME,
      C.MIDDLENAME,
      C.TITLECODEID,
      isnull(EA.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,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
  from dbo.CONSTITUENT C
  join dbo.UFN_ADDRESSBOOKFAF_GETGENERALGROUPDONORS(@GROUPID, @REGISTRANTCONSTITUENTID, @CLIENTUSERSID,  @ROLECODE) DL on DL.ID = C.ID
  left join dbo.EMAILADDRESS EA on C.ID = EA.CONSTITUENTID and EA.ISPRIMARY = 1
  left join dbo.PHONE P on C.ID = P.CONSTITUENTID and P.ISPRIMARY = 1
  left join dbo.[ADDRESS] A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1

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

return 0;