USP_CONTACTPREFERENCES_GETFORMATS

CREATE procedure dbo.[USP_CONTACTPREFERENCES_GETFORMATS]
(
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @MAILTYPE tinyint,
  @PARAMETERSETID uniqueidentifier,
  @SEASONALDATE datetime,
  @INCLUDEHOUSEHOLDPROCESSING bit,
  @CONSTITUENTIDSETTABLENAME nvarchar(128),                     --Table or view that contains constituent IDs

  @CONSTITUENTIDSETJOINCOLUMNNAME nvarchar(128),                --Column to use when joining to the table or view

  @CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME nvarchar(128) = '',  --Column to use with exclude spouse options

  @IGNORECHANNELPREFERENCEFORSUPPRESSION bit = 0,               --0=Respect channel preferences when suppressing addresses, 1=Ignore channel preferences and do not let other channels (ie: email) cause addresses to be suppressed

  @IGNOREADDRESSSUPPRESSION bit = 0                             --0=Suppress records that do not have a valid address, 1=Ignore address suppression and return all original records with possibly null data

)
as
  set nocount on;

  --Returns results in the following temp table named #TEMP_CONTACTPREFERENCES_GETFORMATS.  This temp table must exist

  --outside of this stored procedure.  Use the statement below to create the temp table before calling this stored

  --procedure.  The temp table will be indexed on the CONSTITUENTID column.

  --

  --create table #TEMP_CONTACTPREFERENCES_GETFORMATS (

  --  [CONSTITUENTID] uniqueidentifier not null,

  --  [ADDRESSEE] nvarchar(700) collate database_default,

  --  [SALUTATION] nvarchar(700) collate database_default,

  --  [CONTACT] nvarchar(700) collate database_default,

  --  [ADDRESSID] uniqueidentifier,

  --  [HOUSEHOLDID] uniqueidentifier,

  --  [RETURNEDASHOUSEHOLDMEMBER] bit not null,

  --  [GROUPCONTACTID] uniqueidentifier,

  --  [POSITION] nvarchar(100) collate database_default,

  --  [MAILTOCONSTITUENTID] uniqueidentifier,

  --  [CONTACTID] uniqueidentifier,

  --  [SPOUSEID] uniqueidentifier,

  --  [CONTACTORADDRESSEE] nvarchar(700) collate database_default

  --);


  declare @OBJID int;
  declare @ISTEMPTABLE bit;
  declare @SQL nvarchar(max);

  --Debug only

  --declare @STARTDATE datetime;

  --declare @STATUS nvarchar(100);



  begin try
    --Validate the required results temp table exists and that it does not have indexes defined on it...

    set @OBJID = object_id('tempdb..#TEMP_CONTACTPREFERENCES_GETFORMATS');
    if @OBJID is null
      raiserror('The temp table #TEMP_CONTACTPREFERENCES_GETFORMATS is required to hold the results of this stored procedure but it does not exist.  Please create the temp table before calling this stored procedure.', 13, 1);
    if exists(select * from tempdb.sys.indexes where object_id = @OBJID and type = 2)  --only concerned with nonclustered indexes

      raiserror('The temp table #TEMP_CONTACTPREFERENCES_GETFORMATS already has indexes defined on it.  Please remove these indexes before calling this stored procedure.', 13, 1);

    --Validate that none of the temp tables used in this stored procedure exist already...

    if object_id('tempdb..#TEMP_ADDRESSPROCESS_ADDRESSES') is not null
      raiserror('The temp table #TEMP_ADDRESSPROCESS_ADDRESSES is used by this stored procedure but it already exists in this session.  Please make sure the temp table does not exist before calling this stored procedure.', 13, 1);
    if object_id('tempdb..#TEMP_MAILING_NAMEFORMATS') is not null
      raiserror('The temp table #TEMP_MAILING_NAMEFORMATS is used by this stored procedure but it already exists in this session.  Please make sure the temp table does not exist before calling this stored procedure.', 13, 1);

    --Validate the constituent IDSet table exists and is defined appropriately...

    if len(@CONSTITUENTIDSETTABLENAME) = 0 or len(@CONSTITUENTIDSETJOINCOLUMNNAME) = 0
      raiserror('The parameters @CONSTITUENTIDSETTABLENAME and @CONSTITUENTIDSETJOINCOLUMNNAME are required to execute this stored procedure.', 13, 1);
    set @ISTEMPTABLE = (case when left(@CONSTITUENTIDSETTABLENAME, 1) = '#' then 1 else 0 end);
    set @OBJID = object_id((case when @ISTEMPTABLE = 1 then 'tempdb..' else 'dbo.' end) + '[' + @CONSTITUENTIDSETTABLENAME + ']');
    if @OBJID is null
      raiserror('The constituent IDSet table specified in the @CONSTITUENTIDSETTABLENAME parameter does not exist.', 13, 1);
    if (@ISTEMPTABLE = 1 and not exists(select * from tempdb.sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETJOINCOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from tempdb.sys.types t where t.[name] = 'uniqueidentifier'))) or
       (@ISTEMPTABLE = 0 and not exists(select * from sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETJOINCOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from sys.types t where t.[name] = 'uniqueidentifier')))
      raiserror('The constituent IDSet column specified in the @CONSTITUENTIDSETJOINCOLUMNNAME parameter does not exist or is not defined properly as a uniqueidentifier.', 13, 1);
    if len(@CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME) > 0 and
       ((@ISTEMPTABLE = 1 and not exists(select * from tempdb.sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from tempdb.sys.types t where t.[name] = 'bit'))) or
        (@ISTEMPTABLE = 0 and not exists(select * from sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from sys.types t where t.[name] = 'bit'))))
      raiserror('The constituent IDSet column specified in the @CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME parameter does not exist or is not defined properly as a bit.', 13, 1);







    /****************************************************/
    /* Addresses                                        */
    /****************************************************/
    --Debug only

    --raiserror('Addresses...', 0, 1) with nowait;

    --set @STARTDATE = getdate();


    create table #TEMP_ADDRESSPROCESS_ADDRESSES (
      [CONSTITUENTID] uniqueidentifier not null,
      [ADDRESSID] uniqueidentifier,
      [CONTACTID] uniqueidentifier,
      [POSITION] nvarchar(100) collate database_default,
      [HOUSEHOLDID] uniqueidentifier,
      [RETURNEDASHOUSEHOLDMEMBER] bit not null,
      [GROUPCONTACTID] uniqueidentifier,
      [EXCLUDESPOUSE] bit  --Adding this extra EXCLUDESPOUSE column so we don't have to copy the results to another temp table for nameformat processing.

    );

    --This USP will insert into the above temp table...

    exec dbo.[USP_ADDRESSPROCESS_ADDRESSES]
      @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
      @MAILTYPE = @MAILTYPE,
      @PARAMETERSETID = @PARAMETERSETID,
      @DATE = @SEASONALDATE,
      @INCLUDEHOUSEHOLDPROCESSING = @INCLUDEHOUSEHOLDPROCESSING,
      @CONSTITUENTIDSETTABLENAME = @CONSTITUENTIDSETTABLENAME,
      @CONSTITUENTIDSETJOINCOLUMNNAME = @CONSTITUENTIDSETJOINCOLUMNNAME,
      @IGNORECHANNELPREFERENCEFORSUPPRESSION = @IGNORECHANNELPREFERENCEFORSUPPRESSION,
      @IGNOREADDRESSSUPPRESSION = @IGNOREADDRESSSUPPRESSION;

    --For the exclude spouse options, just update the column in the address processing temp table...

    if len(@CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME) > 0
      begin
        set @SQL = 'update #TEMP_ADDRESSPROCESS_ADDRESSES set' + char(13) +
                   '  [EXCLUDESPOUSE] = [IDSET].' + quotename(@CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME) + char(13) +
                   'from #TEMP_ADDRESSPROCESS_ADDRESSES as [AP]' + char(13) +
                   'inner join dbo.' + quotename(@CONSTITUENTIDSETTABLENAME) + ' as [IDSET] on [IDSET].' + quotename(@CONSTITUENTIDSETJOINCOLUMNNAME) + ' = [AP].[CONSTITUENTID]';
        exec (@SQL);
end

    --Debug only

    --set @STATUS = 'Addresses... ' + cast((select count(*) from #TEMP_ADDRESSPROCESS_ADDRESSES) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';

    --raiserror(@STATUS, 0, 1) with nowait;









    /****************************************************/
    /* Name formats                                     */
    /****************************************************/
    --Debug only

    --raiserror('Name formats...', 0, 1) with nowait;

    --set @STARTDATE = getdate();


    create table #TEMP_MAILING_NAMEFORMATS (
      [CONSTITUENTID] uniqueidentifier not null,
      [MAILTOCONSTITUENTID] uniqueidentifier not null,
      [ADDRESSEE] nvarchar(700) collate database_default,
      [SALUTATION] nvarchar(700) collate database_default,
      [CONTACT] nvarchar(700) collate database_default
    );

    if @NAMEFORMATPARAMETERID is not null and @NAMEFORMATPARAMETERID <> '00000000-0000-0000-0000-000000000000'
      exec dbo.[USP_MAILING_GETNAMEFORMATS]
        @NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
        @CONSTITUENTIDSETTABLENAME = '#TEMP_ADDRESSPROCESS_ADDRESSES',
        @CONSTITUENTIDSETJOINCOLUMNNAME = 'CONSTITUENTID',
        @CONSTITUENTIDSETCONTACTCOLUMNNAME = 'CONTACTID',
        @CONSTITUENTIDSETGROUPCONTACTCOLUMNNAME = 'GROUPCONTACTID',
        @CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME = 'EXCLUDESPOUSE';

    --Debug only

    --set @STATUS = 'Name formats... ' + cast((select count(*) from #TEMP_MAILING_NAMEFORMATS) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';

    --raiserror(@STATUS, 0, 1) with nowait;









    /****************************************************/
    /* Final results                                    */
    /****************************************************/
    --Debug only

    --raiserror('Final results...', 0, 1) with nowait;

    --set @STARTDATE = getdate();


    with CONSTIT_CTE as
    (
      select
        AP.CONSTITUENTID,
        AP.ADDRESSID,
        AP.CONTACTID,
        AP.GROUPCONTACTID,
        AP.HOUSEHOLDID,
        AP.POSITION,
        AP.RETURNEDASHOUSEHOLDMEMBER,
        isnull(MEMBERCONSTIT.ID, GROUPMEMBER.MEMBERID) as [MEMBERID],
        CONSTITUENT.ISORGANIZATION,
        CONSTITUENT.ISGROUP
      from #TEMP_ADDRESSPROCESS_ADDRESSES as AP
      inner join dbo.CONSTITUENT on CONSTITUENT.ID = AP.CONSTITUENTID
      left join dbo.CONSTITUENT as MEMBERCONSTIT on MEMBERCONSTIT.ID = AP.GROUPCONTACTID and MEMBERCONSTIT.ISGROUP = 0
      left join dbo.GROUPMEMBER on CONSTITUENT.ISGROUP = 1 and GROUPMEMBER.GROUPID = CONSTITUENT.ID and GROUPMEMBER.ISPRIMARY = 1
    )
    insert into #TEMP_CONTACTPREFERENCES_GETFORMATS ([CONSTITUENTID], [ADDRESSEE], [SALUTATION], [CONTACT], [ADDRESSID], [HOUSEHOLDID], [RETURNEDASHOUSEHOLDMEMBER], [GROUPCONTACTID], [POSITION], [MAILTOCONSTITUENTID], [CONTACTID], [SPOUSEID], [CONTACTORADDRESSEE])
      select
        CONSTIT_CTE.[CONSTITUENTID],  
        NF.ADDRESSEE,
        NF.SALUTATION,
        NF.CONTACT,
        CONSTIT_CTE.ADDRESSID,
        CONSTIT_CTE.HOUSEHOLDID,
        CONSTIT_CTE.RETURNEDASHOUSEHOLDMEMBER,
        CONSTIT_CTE.GROUPCONTACTID,
        CONSTIT_CTE.POSITION,
        coalesce(CONSTIT_CTE.CONTACTID, CONSTIT_CTE.MEMBERID, CONSTIT_CTE.CONSTITUENTID) as MAILTOCONSTITUENTID,
        CONSTIT_CTE.CONTACTID,
        case when CONSTIT_CTE.ISGROUP = 0 and CONSTIT_CTE.ISORGANIZATION = 0 then SPOUSECONSTIT.ID else null end as SPOUSEID,
        case when CONSTIT_CTE.ISORGANIZATION = 1 or CONSTIT_CTE.ISGROUP = 1 then NF.CONTACT else NF.ADDRESSEE end as CONTACTORADDRESSEE
      from CONSTIT_CTE
      left join #TEMP_MAILING_NAMEFORMATS as NF on NF.[CONSTITUENTID] = CONSTIT_CTE.[CONSTITUENTID] and NF.[MAILTOCONSTITUENTID] = (case when CONSTIT_CTE.ISGROUP = 1 then isnull(CONSTIT_CTE.MEMBERID, CONSTIT_CTE.CONSTITUENTID) when CONSTIT_CTE.ISORGANIZATION = 1 then isnull(CONSTIT_CTE.CONTACTID, CONSTIT_CTE.CONSTITUENTID) else CONSTIT_CTE.CONSTITUENTID end)
      left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = (case when CONSTIT_CTE.ISGROUP = 1 then CONSTIT_CTE.MEMBERID when CONSTIT_CTE.ISORGANIZATION = 1 then CONSTIT_CTE.CONTACTID else CONSTIT_CTE.CONSTITUENTID end) and RELATIONSHIP.ISSPOUSE = 1
      left join dbo.CONSTITUENT as SPOUSECONSTIT on SPOUSECONSTIT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID;


    --Create an index on the final results...

    create nonclustered index [IX_TEMP_CONTACTPREFERENCES_GETFORMATS_CONSTITUENTID] on #TEMP_CONTACTPREFERENCES_GETFORMATS ([CONSTITUENTID])
      include ([ADDRESSEE], [SALUTATION], [CONTACT], [ADDRESSID], [HOUSEHOLDID], [RETURNEDASHOUSEHOLDMEMBER], [GROUPCONTACTID], [POSITION], [MAILTOCONSTITUENTID], [CONTACTID], [SPOUSEID]);


    --Debug only

    --set @STATUS = 'Final results... ' + cast((select count(*) from #TEMP_CONTACTPREFERENCES_GETFORMATS) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';

    --raiserror(@STATUS, 0, 1) with nowait;







    /****************************************************/
    /* Clean up                                         */
    /****************************************************/
    drop table #TEMP_ADDRESSPROCESS_ADDRESSES;
    drop table #TEMP_MAILING_NAMEFORMATS;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];

    if object_id('tempdb..#TEMP_ADDRESSPROCESS_ADDRESSES') is not null
      drop table #TEMP_ADDRESSPROCESS_ADDRESSES;
    if object_id('tempdb..#TEMP_MAILING_NAMEFORMATS') is not null
      drop table #TEMP_MAILING_NAMEFORMATS;

    return 1;
  end catch

  return 0;