USP_FINDERNUMBER_ADDMEMBER

CREATE procedure dbo.[USP_FINDERNUMBER_ADDMEMBER]
(
  @ID uniqueidentifier output,
  @CHANGEAGENTID uniqueidentifier = null,
  @FINDERNUMBER bigint,
  @REVENUEBATCH bit = 0,
  @CURRENTAPPUSERID uniqueidentifier = null
)
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 @CURRENTDATE datetime = getdate();

  if isnull(@FINDERNUMBER, 0) > 0
    begin
      if dbo.[UFN_MKTFINDERNUMBER_VALIDATE_MOD10](@FINDERNUMBER) = 0
        begin
          --Batch will handle these errors, so skip them here...

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

      set @SEGMENTATIONID = dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER);
    end

  if @SEGMENTATIONID is null
    begin
      --Batch will handle these errors, so skip them here...

      if @REVENUEBATCH = 0
        raiserror('Invalid finder number.', 13, 1);
      return 1;
    end

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

  -- 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
      --Batch will handle these errors, so skip them here...

      if @REVENUEBATCH = 0
        raiserror('No member with this finder number was found in an acquisition list.', 13, 1);
      return 1;
    end

  -- if we are not in batch then update the temporary list constituent table

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

          begin try
            insert into dbo.[MKTFINDERNUMBERCONSTITUENT] (
              [ID],
              [FIRSTNAME],
              [MIDDLENAME],
              [KEYNAME],
              [TITLECODEID],
              [SUFFIXCODEID],
              [TITLE2CODEID],
              [SUFFIX2CODEID],
              [ADDRESSBLOCK],
              [CITY],
              [POSTCODE],
              [COUNTRYID],
              [STATEID],
              [CART],
              [DPC],
              [LOT]
            )
            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]),
              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], ''),
              (select isnull((select top 1 [ID] from dbo.[COUNTRY] where [ABBREVIATION] = [C].[COUNTRY] or [DESCRIPTION] = [C].[COUNTRY]), dbo.[UFN_COUNTRY_GETDEFAULT]())),
              (case when (select count(*) from dbo.[STATE] where [ABBREVIATION] = C.[STATE] or [DESCRIPTION] = C.[STATE]) <= 1 then
                 (select top 1 [ID] from dbo.[STATE] where [ABBREVIATION] = C.[STATE] or [DESCRIPTION] = C.[STATE])
               else
                 (select top 1 [ID] from dbo.[STATE] where ([ABBREVIATION] = C.[STATE] or [DESCRIPTION] = C.[STATE]) and ([COUNTRYID] = (select isnull((select top 1 [ID] from dbo.[COUNTRY] where [ABBREVIATION] = [C].[COUNTRY] or [DESCRIPTION] = [C].[COUNTRY]), dbo.[UFN_COUNTRY_GETDEFAULT]()))))
               end),
              isnull(C.[CART], ''),
              isnull(C.[DPC], ''),
              isnull(C.[LOT], '')
            from @CONSTITDATA C;
          end try
          begin catch
            raiserror('Could not insert member into temporary constituent table.', 13, 1);
            return 1;
          end catch

          /* Gather sites user to which user has access */
          declare @SITEIDS table ([ID] uniqueidentifier null);  
          declare @DATAFORMTEMPLATEID as uniqueidentifier = 'B4A8C2CB-FE50-4C50-A587-DC3C409B0E6A'; -- Individual.Add.xml


          insert into @SITEIDS
            select distinct  
              case [V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM].[SITESECURITYMODE]
                when 2 then [SYSTEMROLEAPPUSERSITE].[SITEID]
                when 3 then [SYSTEMROLEAPPUSER].[BRANCHSITEID] end
            from dbo.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM]
            inner join [DATAFORMINSTANCECATALOG] on [DATAFORMINSTANCECATALOG].[ID] = [DATAFORMINSTANCECATALOGID]
            inner join [SYSTEMROLEAPPUSER] on
              (
                [SYSTEMROLEAPPUSER].[APPUSERID] = [V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM].[APPUSERID]
                and
                [SYSTEMROLEAPPUSER].[SYSTEMROLEID] = [V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM].[SYSTEMROLEID]
              )  
            left join [SYSTEMROLEAPPUSERSITE] on [SYSTEMROLEAPPUSER].[ID] = [SYSTEMROLEAPPUSERSITE].[SYSTEMROLEAPPUSERID]
            where   
              [V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM].[APPUSERID] = @CURRENTAPPUSERID  
              and [DATAFORMINSTANCECATALOG].[DATAFORMTEMPLATECATALOGID] = @DATAFORMTEMPLATEID  
              and dbo.[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID,[DATAFORMINSTANCECATALOG].[ID])=1  
              and [V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM].[GRANTORDENY] = 1;

          /* Insert constituent site records into temporary site table */
          insert into dbo.[MKTFINDERNUMBERCONSTITUENTSITE]
          (
            [MKTFINDERNUMBERCONSTITUENTID],
            [SITEID]
          )  
          select
            @CONSTITUENTID,
            [SITEIDS].[ID]
          from @SITEIDS as [SITEIDS]
          where [SITEIDS].[ID] is not null;

        end

      --Create a record in the MKTFINDERNUMBERCONSTITUENTAPPEAL 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 * from dbo.[MKTFINDERNUMBERCONSTITUENTAPPEAL] where [CONSTITUENTID] = @CONSTITUENTID and [FINDERNUMBER] = @FINDERNUMBER) and not exists(select 1 from @CONSTITDATA where isnull([APPEALSYSTEMID], '') = '')
          insert into dbo.[MKTFINDERNUMBERCONSTITUENTAPPEAL] (
            [ID],
            [CONSTITUENTID],
            [APPEALID],
            [MKTSEGMENTATIONID],
            [SOURCECODE],
            [FINDERNUMBER],
            [DATESENT],
            [COMMENTS],
            [MKTPACKAGEID],
            [MKTSEGMENTATIONSEGMENTID],
            [MKTSEGMENTATIONTESTSEGMENTID]
          )
          select
            newid(),
            @CONSTITUENTID,
            C.[APPEALSYSTEMID],
            @SEGMENTATIONID,
            isnull(C.[SOURCECODE], ''),
            @FINDERNUMBER,
            C.[MAILDATE],
            '', --@COMMENTS

            isnull((select [PACKAGEID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = C.[SEGMENTID]), (select [PACKAGEID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = C.[SEGMENTID])), 
            (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = C.[SEGMENTID]), 
            (select [ID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = C.[SEGMENTID])
          from @CONSTITDATA C;
      end try
      begin catch
        raiserror('Unable to insert temporary constituent appeal information.', 13, 1);
        return 1;
      end catch
    end
  else  --@REVENUEBATCH = 1

    begin
      if exists(select 1 from dbo.[CONSTITUENT] where [ID] = @CONSTITUENTID)--check to see if the constituent was added some other method

        exec dbo.[USP_BATCHREVENUECONSTITUENT_DELETEBYID_WITHCHANGEAGENTID] @CONSTITUENTID, @CHANGEAGENTID;--remove existing entries as the user is now a constituent

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

          begin try
            insert into dbo.[BATCHREVENUECONSTITUENT] (
              ID,
              FIRSTNAME,
              MIDDLENAME,
              KEYNAME,
              TITLECODEID,
              SUFFIXCODEID,
              ADDRESSBLOCK,
              CITY,
              POSTCODE,
        COUNTRYID,
              STATEID,
              CART,
              DPC,
              LOT,
              ADDEDBYID,
              CHANGEDBYID,
              DATEADDED,
              DATECHANGED,
              CURRENTAPPUSERID,
              NUMBER,
              EMAILADDRESS
            )
            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]),
              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], ''),
              (select isnull((select top 1 [ID] from dbo.[COUNTRY] where [ABBREVIATION] = [C].[COUNTRY] or [DESCRIPTION] = [C].[COUNTRY]), dbo.[UFN_COUNTRY_GETDEFAULT]())),
              (case when (select count(*) from dbo.[STATE] where [ABBREVIATION] = C.[STATE] or [DESCRIPTION] = C.[STATE]) <= 1 then
                 (select top 1 [ID] from dbo.[STATE] where [ABBREVIATION] = C.[STATE] or [DESCRIPTION] = C.[STATE])
               else
                 (select top 1 [ID] from dbo.[STATE] where ([ABBREVIATION] = C.[STATE] or [DESCRIPTION] = C.[STATE]) and ([COUNTRYID] = (select isnull((select top 1 [ID] from dbo.[COUNTRY] where [ABBREVIATION] = [C].[COUNTRY] or [DESCRIPTION] = [C].[COUNTRY]), dbo.[UFN_COUNTRY_GETDEFAULT]()))))
               end),
              isnull(C.[CART], ''),
              isnull(C.[DPC], ''),
              isnull(C.[LOT], ''),
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE,
              @CURRENTAPPUSERID,
              isnull(C.[PHONENUMBER], ''),
              isnull(C.[EMAILADDRESS], '')
            from @CONSTITDATA C;
          end try
          begin catch
            raiserror('Could not insert member into batch revenue constituent table.', 13, 1);
            return 1;
          end catch
        end
      end

      --Create a record in the BATCHREVENUECONSTITUENTAPPEAL table.

      begin try
        if not exists(select * from dbo.[BATCHREVENUECONSTITUENTAPPEAL] where [CONSTITUENTID] = @CONSTITUENTID and [FINDERNUMBER] = @FINDERNUMBER)
          insert into dbo.[BATCHREVENUECONSTITUENTAPPEAL] (
            [ID],
            [CONSTITUENTID],
            [APPEALID],
            [MKTSEGMENTATIONID],
            [SOURCECODE],
            [FINDERNUMBER],
            [DATESENT],
            [COMMENTS],
            [MKTPACKAGEID],
            [MKTSEGMENTATIONSEGMENTID],
            [MKTSEGMENTATIONTESTSEGMENTID]
          )
          select
            newid(),
            @CONSTITUENTID,
            C.[APPEALSYSTEMID],
            @SEGMENTATIONID,
            isnull(C.[SOURCECODE], ''),
            @FINDERNUMBER,
            C.[MAILDATE],
            '', --@COMMENTS

            isnull((select [PACKAGEID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = C.[SEGMENTID]), (select [PACKAGEID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = C.[SEGMENTID])),
            (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = C.[SEGMENTID]),
            (select [ID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = C.[SEGMENTID])
          from @CONSTITDATA C;
      end try
      begin catch
        raiserror('Unable to insert batch revenue constituent appeal information.', 13, 1);
        return 1;
      end catch
    end

  set @ID = @CONSTITUENTID;

  return 0;