USP_ACQUISITIONLIST_ADDMEMBER

CREATE procedure dbo.[USP_ACQUISITIONLIST_ADDMEMBER]
(
  @ID uniqueidentifier output,
  @CHANGEAGENTID uniqueidentifier = null,
  @FINDERNUMBER bigint
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @SEGMENTID uniqueidentifier;
  declare @TESTSEGMENTID uniqueidentifier;
  declare @LISTMEMBERID uniqueidentifier;
  declare @CONSTITUENTID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @SOURCECODE nvarchar(50);
  declare @APPEALID uniqueidentifier;
  declare @DATESENT datetime;
  declare @COMMENTS nvarchar(50);
  declare @PACKAGEID uniqueidentifier;
  declare @CHECKDIGIT bit;

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

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

  select
    @SEGMENTATIONID = dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER),
    @CHECKDIGIT = dbo.[UFN_MKTFINDERNUMBER_VALIDATE_MOD10](@FINDERNUMBER);

  if @CHECKDIGIT = 0
    begin
      raiserror('Finder number failed check digit.', 13, 1);
      return 1;
    end

  if @SEGMENTATIONID is null
    begin
      raiserror('Invalid finder number.', 13, 1);
      return 1;
    end

  -- this is the record source ID for the standard BBEC/DM query view.

  set @RECORDSOURCEID = dbo.[UFN_MKTRECORDSOURCE_GETFIRSTBBECRECORDSOURCEID]();

  -- this table structure must match the one defined in the Marketing.Catalog\Segmentation\FinderNumber\FinderNumberLookup.DataList.xml

  declare @CONSTITDATA as table(
    [ID] nvarchar(36),
    [CONSTITUENTID] nvarchar(36),
    [FULLNAME] nvarchar(255),
    [FIRSTNAME] nvarchar(255),
    [MIDDLENAME] nvarchar(255),
    [LASTNAME] nvarchar(255),
    [TITLE] nvarchar(255),
    [SUFFIX] nvarchar(255),
    [COUNTRY] nvarchar(255),
    [ADDRESSLINE1] nvarchar(255),
    [ADDRESSLINE2] nvarchar(255),
    [ADDRESSLINE3] nvarchar(255),
    [ADDRESSLINE4] nvarchar(255),
    [ADDRESSLINE5] nvarchar(255),
    [CITY] nvarchar(255),
    [STATE] nvarchar(255),
    [POSTCODE] nvarchar(255),
    [CART] nvarchar(255),
    [DPC] nvarchar(255),
    [LOT] nvarchar(255),
    [PHONENUMBER] nvarchar(255),
    [EMAILADDRESS] nvarchar(255),
    [MAILINGIDINTEGER] int,
    [MAILDATE] datetime,
    [SOURCECODE] nvarchar(50),
    [APPEALSYSTEMID] nvarchar(36),
    [APPEALID] nvarchar(100),
    [APPEALDESCRIPTION] nvarchar(100),
    [PACKAGECODE] nvarchar(10),
    [PACKAGENAME] nvarchar(100),
    [PACKAGEDESCRIPTION] nvarchar(255),
    [SEGMENTID] uniqueidentifier,
    [SEGMENTNAME] nvarchar(203),
    [LISTNAME] nvarchar(100),
    [TITLE2] nvarchar(255),
    [SUFFIX2] nvarchar(255)
  );

  insert into @CONSTITDATA
    exec dbo.[USP_DATALIST_MKTFINDERNUMBERLOOKUP] @FINDERNUMBER, @RECORDSOURCEID;

  -- if the [CONSTITUENTID] field is blank, but we returned a row, then this

  --  person in a member of a List and needs to be added as a constituent.  If 

  --  no rows are returned, then we didn't find anyone with a matching finder number


  if exists(select * from @CONSTITDATA) 
    begin
      -- [CONSTITUENTID] will be the BBEC Constituent.ID if this is a person from the house file,

      --  or a list member who has become a donor.  If the [CONSTITUENTID] field is empty (null or '') then

      --  we want to create a new constituent using the [ID] that's returned by USP_DATALIST_MKTFINDERNUMBERLOOKUP.

      --  That ID maps to the MKTLIST_<guid> or MKTFINDERFILE_<guid> [ID] field.

      declare @TMPCONSTITUENTID nvarchar(36);

      select top(1)
        @LISTMEMBERID = [ID],
        @TMPCONSTITUENTID = [CONSTITUENTID]
      from @CONSTITDATA;

      if @TMPCONSTITUENTID is null
        set @CONSTITUENTID = @LISTMEMBERID;
      else if len(@TMPCONSTITUENTID) < 36 
        set @CONSTITUENTID = @LISTMEMBERID;
      else
        set @CONSTITUENTID = @TMPCONSTITUENTID;
    end
  else
    begin
    raiserror('No member with this finder number was found in an acquisition list.', 13, 1);
      return 1;
    end

  -- if the acquisition list member is not already in the CONSTITUENT table, add the member to the CONSTITUENT table

  if not exists(select * from dbo.[CONSTITUENT] where [ID] = @CONSTITUENTID)
    begin
      -- insert constituent information

      begin try
        insert into dbo.[CONSTITUENT] ([ID], [FIRSTNAME], [MIDDLENAME], [KEYNAME], [TITLECODEID], [SUFFIXCODEID], [TITLE2CODEID], [SUFFIX2CODEID], [DATEADDED], [DATECHANGED], [ADDEDBYID], [CHANGEDBYID])
          select
            @CONSTITUENTID,
            isnull([C].[FIRSTNAME], ''),
            isnull([C].[MIDDLENAME], ''),
            isnull([C].[LASTNAME], ''),
            (select [ID] from dbo.[TITLECODE] where [DESCRIPTION] = [C].[TITLE]),
            (select [ID] from dbo.[SUFFIXCODE] where [DESCRIPTION] = [C].[SUFFIX]),
            (select [ID] from dbo.[TITLECODE] where [DESCRIPTION] = [C].[TITLE2]),
            (select [ID] from dbo.[SUFFIXCODE] where [DESCRIPTION] = [C].[SUFFIX2]),
            @CURRENTDATE,
            @CURRENTDATE,
            @CHANGEAGENTID,
            @CHANGEAGENTID
          from @CONSTITDATA as [C];
      end try
      begin catch
        raiserror('Could not insert member into Constituent table.', 13, 1);
        return 1;
      end catch

      /* Insert default sites from the temp constituent site table */
      insert into dbo.[CONSTITUENTSITE]
      (
        [CONSTITUENTID],
        [SITEID],
        [DATEADDED],
        [DATECHANGED],
        [ADDEDBYID],
        [CHANGEDBYID]
      )
      Select
        @CONSTITUENTID,
        [SITEID],
        @CURRENTDATE,
        @CURRENTDATE,
        @CHANGEAGENTID,
        @CHANGEAGENTID
      from dbo.[MKTFINDERNUMBERCONSTITUENTSITE]
      where [MKTFINDERNUMBERCONSTITUENTSITE].[MKTFINDERNUMBERCONSTITUENTID] = @CONSTITUENTID;

      --remove from MKTFINDERNUMBERCONSTITUENT table

      delete dbo.[MKTFINDERNUMBERCONSTITUENT] where [ID] = @CONSTITUENTID;

      -- insert constituent name format defaults

      begin try
        insert into dbo.[NAMEFORMAT]
          ([CONSTITUENTID],
          [NAMEFORMATTYPECODEID],
          [NAMEFORMATFUNCTIONID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED],
          [PRIMARYADDRESSEE],
          [PRIMARYSALUTATION])
        select
          @CONSTITUENTID,
          [NFD].[NAMEFORMATTYPECODEID],
          [NFD].[NAMEFORMATFUNCTIONID],
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE,
          [NFD].[PRIMARYADDRESSEE],
          [NFD].[PRIMARYSALUTATION]
        from dbo.[NAMEFORMATDEFAULT] as [NFD]
        where [NFD].[APPLYTOCODE] = 0;
      end try
      begin catch
        raiserror('Could not insert into Name Formats table.', 13, 1);
        return 1;
      end catch

      -- Check to make sure state is valid for the given country.

      declare @COUNTRYID uniqueidentifier;
      declare @STATEID uniqueidentifier;

      select 
        @STATEID = case when (select count(1) from dbo.[STATE] where [ABBREVIATION] = [CONSTITDATA].[STATE] or [DESCRIPTION] = [CONSTITDATA].[STATE]) <= 1 then
          (select top 1 [ID] from dbo.[STATE] where [ABBREVIATION] = [CONSTITDATA].[STATE] or [DESCRIPTION] = [CONSTITDATA].[STATE])
        else
          (select top 1 [ID] from dbo.[STATE] where ([ABBREVIATION] = [CONSTITDATA].[STATE] or [DESCRIPTION] = [CONSTITDATA].[STATE]) 
           and ([COUNTRYID] = (select isnull((select top 1 [ID] from dbo.[COUNTRY]  
                               where [ABBREVIATION] = [CONSTITDATA].[COUNTRY] 
                                     or [DESCRIPTION] = [CONSTITDATA].[COUNTRY]), dbo.[UFN_COUNTRY_GETDEFAULT]())))) end,
        @COUNTRYID = (select isnull((select top(1) [ID] from dbo.[COUNTRY] where [ABBREVIATION] = [CONSTITDATA].[COUNTRY] or [DESCRIPTION] = [CONSTITDATA].[COUNTRY]), dbo.[UFN_COUNTRY_GETDEFAULT]()))
      from @CONSTITDATA as [CONSTITDATA];

      if dbo.[UFN_STATE_GETCOUNTRY](@STATEID, @COUNTRYID) != @COUNTRYID 
        raiserror('The state is not valid for the given country.', 13, 1);

      -- insert address information

      begin try
        insert into dbo.[ADDRESS] (
          [ID], 
          [CONSTITUENTID], 
          [ISPRIMARY], 
          [ADDRESSBLOCK], 
          [CITY], 
          [POSTCODE], 
          [COUNTRYID], 
          [STATEID], 
          [CART], 
          [DPC], 
          [LOT], 
          [DATEADDED], 
          [DATECHANGED], 
          [ADDEDBYID], 
          [CHANGEDBYID])
        select 
          newid(),
          @CONSTITUENTID,
          1,
          -- address block

          -- BTR CR301069-052008 5/21/2008

          -- was putting a space between line 1 and line 2, rather than a CRLF

          isnull(dbo.[UFN_ACQUISITIONLIST_CONCATENATEADDRESSLINES]([C].[ADDRESSLINE1],
            dbo.[UFN_ACQUISITIONLIST_CONCATENATEADDRESSLINES]([C].[ADDRESSLINE2],
              dbo.[UFN_ACQUISITIONLIST_CONCATENATEADDRESSLINES]([C].[ADDRESSLINE3],
                dbo.[UFN_ACQUISITIONLIST_CONCATENATEADDRESSLINES]([C].[ADDRESSLINE4], [C].[ADDRESSLINE5])))), ''),
          isnull([C].[CITY], ''),
          isnull([C].[POSTCODE], ''),
          @COUNTRYID,
          @STATEID,
          isnull([C].[CART], ''),
          isnull([C].[DPC], ''),
          isnull([C].[LOT], ''),
          @CURRENTDATE,
          @CURRENTDATE,
          @CHANGEAGENTID,
          @CHANGEAGENTID
        from @CONSTITDATA as [C];
      end try
      begin catch
        raiserror('Could not insert into Address table.', 13, 1);    
        return 1;
      end catch

      -- insert phone number

      declare @PHONE nvarchar(100);
      select @PHONE = [PHONENUMBER] from @CONSTITDATA;
      if len(@PHONE) > 0
        insert into dbo.[PHONE] (
          [ID],
          [CONSTITUENTID],
          [NUMBER],
          [ISPRIMARY],
          [COUNTRYID],
          [DATEADDED],
          [DATECHANGED],
          [ADDEDBYID],
          [CHANGEDBYID]
        ) values (
          newid(),
          @CONSTITUENTID,
          @PHONE,
          1,
          @COUNTRYID,
          @CURRENTDATE,
          @CURRENTDATE,
          @CHANGEAGENTID,
          @CHANGEAGENTID
        );

      -- insert email address

      declare @EMAIL nvarchar(100);
      select @EMAIL = [EMAILADDRESS] from @CONSTITDATA;
      if len(@EMAIL) > 0
        insert into dbo.[EMAILADDRESS] (
          [ID],
          [CONSTITUENTID],
          [EMAILADDRESS],
          [ISPRIMARY],
          [DATEADDED],
          [DATECHANGED],
          [ADDEDBYID],
          [CHANGEDBYID]
        ) values (
          newid(),
          @CONSTITUENTID,
          @EMAIL,
          1,
          @CURRENTDATE,
          @CURRENTDATE,
          @CHANGEAGENTID,
          @CHANGEAGENTID
        );
    end

  --Create a record in the CONSTITUENTAPPEAL table. If this were not an acquisition list, this row would have been created by BBDM's 'Update Constituent Appeal' business process before exporting the mailing

  begin try
    if not exists(select 1 from @CONSTITDATA where isnull([APPEALSYSTEMID], '') = '')
    begin

      select
        @APPEALID = [C].[APPEALSYSTEMID],
        @SOURCECODE = isnull([C].[SOURCECODE], ''),
        @DATESENT = [C].[MAILDATE],
        @PACKAGEID = isnull((select [PACKAGEID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = [C].[SEGMENTID]), (select [PACKAGEID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = [C].[SEGMENTID])),
        @SEGMENTID = (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = [C].[SEGMENTID]),
        @TESTSEGMENTID = (select [ID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = [C].[SEGMENTID])
      from @CONSTITDATA as [C];

      declare @CONSTITUENTAPPEALID uniqueidentifier;

      select 
        @CONSTITUENTAPPEALID = [CONSTITUENTAPPEAL].[ID]
      from dbo.[CONSTITUENTAPPEAL]
      where [CONSTITUENTAPPEAL].[CONSTITUENTID] = @CONSTITUENTID
        and [CONSTITUENTAPPEAL].[APPEALID] = @APPEALID
        and [CONSTITUENTAPPEAL].[FINDERNUMBER] = @FINDERNUMBER
        and [CONSTITUENTAPPEAL].[MKTPACKAGEID] = @PACKAGEID
        and ([CONSTITUENTAPPEAL].[MKTSEGMENTATIONSEGMENTID] = @SEGMENTID or [CONSTITUENTAPPEAL].[MKTSEGMENTATIONTESTSEGMENTID] = @TESTSEGMENTID);

      /* If record exists and the datesent value is different, update the datesent value. */
      if @CONSTITUENTAPPEALID is not null
      begin
        if @DATESENT is not null and exists (select 1 from dbo.[CONSTITUENTAPPEAL] where [ID] = @CONSTITUENTAPPEALID and ([DATESENT] is null or [DATESENT] <> @DATESENT))
          /* Only update if the datsent value is different. */
          update dbo.[CONSTITUENTAPPEAL] set
            [DATESENT] = @DATESENT
          where [ID] = @CONSTITUENTAPPEALID;
      end
      else 
        insert into dbo.[CONSTITUENTAPPEAL](
          [ID], 
          [CONSTITUENTID], 
          [APPEALID], 
          [MKTSEGMENTATIONID], 
          [SOURCECODE], 
          [FINDERNUMBER], 
          [DATESENT], 
          [COMMENTS], 
          [MKTPACKAGEID], 
          [MKTSEGMENTATIONSEGMENTID], 
          [MKTSEGMENTATIONTESTSEGMENTID], 
          [ADDEDBYID], 
          [CHANGEDBYID], 
          [DATEADDED], 
          [DATECHANGED]
        ) values (
            newid(),
            @CONSTITUENTID,
            @APPEALID,
            @SEGMENTATIONID,
            @SOURCECODE,
            @FINDERNUMBER,
            @DATESENT,
            '', --@COMMENTS

            @PACKAGEID, 
            @SEGMENTID, 
            @TESTSEGMENTID, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        );
    end
  end try
  begin catch
    raiserror('Run ''Update constituent appeal information'' process, then try again.', 13, 1);
    return 1;
  end catch

  set @ID = @CONSTITUENTID;

  return 0;