USP_MKTSEGMENTATIONACTIVATEPROCESS_GETOPTIONS

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATEPROCESS_GETOPTIONS]
(
  @ID uniqueidentifier
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @MAILINGTYPECODE tinyint;
  declare @SOURCEANALYSISRULEDATAEXISTS bit;
  declare @USECHECKDIGIT bit;
  declare @LASTFINDERNUMBER bigint;
  declare @CALCULATEPROCESSREQUIRED bit;
  declare @APPEALLOCKED bit = 0;
  declare @ACTIVATEOPTIONSLOCKED bit = 0;
  declare @KPISLOCKED bit = 0;
  declare @FROMCOMMUNICATIONTEMPLATE bit = 0;
  declare @CACHESOURCEANALYSISRULEDATAACTIVATELOCKED bit = 0;
  declare @EXPORTAFTERACTIVATELOCKED bit = 0;

  select
    @USECHECKDIGIT = [CHECKDIGIT],
    @LASTFINDERNUMBER = [LAST_FINDERNUMBER]
  from dbo.[MKTFINDERNUMBER];

  select
    @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @CALCULATEPROCESSREQUIRED = (case when dbo.[UFN_MKTSEGMENTATION_ISRECORDCOUNTCACHECURRENT]([MKTSEGMENTATION].[ID], 0) = 1 then 0 else 1 end)
  from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID]
  where [MKTSEGMENTATIONACTIVATEPROCESS].[ID] = @ID;

  exec dbo.[USP_MKTSEGMENTATION_SOURCEANALYSISRULEDATAEXISTS] @SEGMENTATIONID, @SOURCEANALYSISRULEDATAEXISTS output;

  if @CALCULATEPROCESSREQUIRED = 0 and @MAILINGTYPECODE = 0
    begin
      --Because vendor managed list segments always report as being current in the cache, we have to do some extra work here because we

      --may have to force the segment calculate process to run so that tables and everything get setup correctly during activation.

      declare @HASVENDORMANAGEDLISTSEGMENTS bit;
      declare @HASOTHERSEGMENTS bit;

      set @HASVENDORMANAGEDLISTSEGMENTS =
        (case when exists(
           select *
           from dbo.[MKTSEGMENTATIONSEGMENT]
           inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
           inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
           where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
           and [MKTSEGMENTLIST].[TYPECODE] = 1)  --vendor managed only

         then 1 else 0 end);

      set @HASOTHERSEGMENTS =
        (case when exists(
           select *
           from dbo.[MKTSEGMENTATIONSEGMENT]
           inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
           left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
           where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
           and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[TYPECODE] = 0))  --all other segments including imported lists

         then 1 else 0 end);

      --Check if the mailing has only vendor managed list segments, and if so make the calculate process required so that everything gets setup correctly...

      set @CALCULATEPROCESSREQUIRED = (case when @HASVENDORMANAGEDLISTSEGMENTS = 1 and @HASOTHERSEGMENTS = 0 then 1 else 0 end);

      if @CALCULATEPROCESSREQUIRED = 0 and @HASVENDORMANAGEDLISTSEGMENTS = 1 and @HASOTHERSEGMENTS = 1
        begin
          --Mailing has a mixture of segments, so check if any of the vendor managed list segments were added/modified since the last segment

          --count calculation process, if so then we need to require the calculate process to run again so that everything gets setup correctly...

          declare @ENDEDON datetime;
          declare @STATUSCODE tinyint;

          select
            @ENDEDON = [BUSINESSPROCESSSTATUS].[ENDEDON],
            @STATUSCODE = [BUSINESSPROCESSSTATUS].[STATUSCODE]
          from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
          inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID]
          where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = @SEGMENTATIONID
          order by [BUSINESSPROCESSSTATUS].[ENDEDON] desc;

          if @STATUSCODE is null or @STATUSCODE <> 0
            set @CALCULATEPROCESSREQUIRED = 1;
          else if exists(select *
                         from dbo.[MKTSEGMENTATIONSEGMENT]
                         inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                         inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                         where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                         and [MKTSEGMENTLIST].[TYPECODE] = 1  --vendor managed only

                         and ([MKTSEGMENTATIONSEGMENT].[DATECHANGED] > @ENDEDON or [MKTSEGMENT].[DATECHANGED] > @ENDEDON or [MKTSEGMENTLIST].[DATECHANGED] > @ENDEDON))
            set @CALCULATEPROCESSREQUIRED = 1;
        end
    end

  -- See if any segments use a name format containing an advanced spouse option.

  declare @ADVANCEDNAMEFORMATOPTIONUSED bit = 0;
  if exists (select top 1 1
              from dbo.[MKTSEGMENTATIONSEGMENT]
              inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
              left join dbo.[NAMEFORMATPARAMETER] as [NAMEFORMATPARAMETEREFFORT] on [NAMEFORMATPARAMETEREFFORT].[ID] = [MKTSEGMENTATION].[NAMEFORMATPARAMETERID]
              left join dbo.[NAMEFORMATPARAMETER] as [NAMEFORMATPARAMETERSEGMENT] on [NAMEFORMATPARAMETERSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[NAMEFORMATPARAMETERID]
              where 
                [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                -- advanced name format on the segment level

                and (([MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 
                      and [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] = 1 
                      and ([NAMEFORMATPARAMETERSEGMENT].[JOINTEXCLUDESPOUSE] = 1 or [NAMEFORMATPARAMETERSEGMENT].[JOINTSPOUSESMAILEDSEPARATELY] = 1)) 
                -- advanced name format on the mailing level

                or ([MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 0 
                    and [MKTSEGMENTATION].[USEADDRESSPROCESSING] = 1
                    and ([NAMEFORMATPARAMETEREFFORT].[JOINTEXCLUDESPOUSE] = 1 or [NAMEFORMATPARAMETEREFFORT].[JOINTSPOUSESMAILEDSEPARATELY] = 1)))
            )

    set @ADVANCEDNAMEFORMATOPTIONUSED = 1;
  else
    set @ADVANCEDNAMEFORMATOPTIONUSED = 0;

  /* If using a communication template, load the lock settings from the template */
  set @FROMCOMMUNICATIONTEMPLATE = (case when exists(select top 1 1 from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @SEGMENTATIONID) then 1 else 0 end);

  if @FROMCOMMUNICATIONTEMPLATE = 1
    begin

      select
        @APPEALLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED],
        @ACTIVATEOPTIONSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ACTIVATEOPTIONSLOCKED],
        @KPISLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[KPISLOCKED],
        @CACHESOURCEANALYSISRULEDATAACTIVATELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATAACTIVATELOCKED],
        @EXPORTAFTERACTIVATELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTAFTERACTIVATELOCKED]
      from dbo.[MKTCOMMUNICATIONTEMPLATE]
      inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
      where [MKTSEGMENTATIONID] = @SEGMENTATIONID;

    end

  select 
    @SEGMENTATIONID as [SEGMENTATIONID],
    [MKTSEGMENTATION].[SITEID],
    @MAILINGTYPECODE as [MAILINGTYPECODE],
    [MKTSEGMENTATION].[ACTIVE],
    cast((case when [APPEALMAILING].[ID] is null then 0 else 1 end) as bit) as [ISAPPEALMAILING],
    [MKTSEGMENTATIONEXPORTPROCESS].[ID] as [SEGMENTATIONEXPORTPROCESSID],
    [MKTSEGMENTATION].[CREATEOUTPUTIDSET],
    [MKTSEGMENTATION].[OUTPUTIDSETNAME],
    [MKTSEGMENTATION].[OVERWRITEOUTPUTIDSET],
    [MKTSEGMENTATION].[RUNACTIVATEANDEXPORT],
    [MKTSEGMENTATION].[SOURCECODEID],
    cast([MKTSEGMENTATIONACTIVATEPROCESS].[RUNSEGMENTATIONSEGMENTCALCULATEPROCESS] as bit) as [RUNSEGMENTATIONSEGMENTCALCULATEPROCESS],
    [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] as [SEGMENTATIONSEGMENTCALCULATEPROCESSID],
    @CALCULATEPROCESSREQUIRED as [SEGMENTATIONSEGMENTCALCULATEPROCESSREQUIRED],
    cast([MKTSEGMENTATIONACTIVATEPROCESS].[RUNSEGMENTATIONSEGMENTREFRESHPROCESS] as bit) as [RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
    cast([MKTSEGMENTATIONACTIVATEPROCESS].[CACHESOURCEANALYSISRULEDATA] as bit) as [CACHESOURCEANALYSISRULEDATA],
    cast(@SOURCEANALYSISRULEDATAEXISTS as bit) as [SOURCEANALYSISRULEDATAEXISTS],
    [LASTRUN].[SEGMENTATIONSEGMENTCALCULATEPROCESSLASTRUN],
    [LASTRUN].[SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN],
    [LASTRUN].[CACHESOURCEANALYSISRULEDATALASTRUN] as [SOURCEANALYSISRULEDATALASTCACHED],
    cast((case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end) as bit) as [ISBBEC],
    dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE]([MKTSEGMENTATION].[ID]) as [DONORIDDATATYPE],
    @USECHECKDIGIT as [USECHECKDIGIT],
    @LASTFINDERNUMBER as [LASTFINDERNUMBER],
    [MKTSEGMENTATION].[COMMUNICATIONTYPECODE],
    [MKTSEGMENTATION].[BASECURRENCYID],
    [MKTSEGMENTATION].[ISHISTORICAL],
    isnull([MKTSOURCECODE].[ISHISTORICAL], 0) as [SOURCECODEISHISTORICAL],
    [MKTSEGMENTATIONACTIVATEPROCESS].[RUNMARKETINGEXCLUSIONSREPORT],
    [LASTRUN].[MARKETINGEXCLUSIONSREPORTLASTRUN],
    @ADVANCEDNAMEFORMATOPTIONUSED as [ADVANCEDNAMEFORMATOPTIONUSED],
    @APPEALLOCKED as [APPEALLOCKED],
    @ACTIVATEOPTIONSLOCKED as [ACTIVATEOPTIONSLOCKED],
    @KPISLOCKED as [KPISLOCKED],
    @FROMCOMMUNICATIONTEMPLATE as [FROMCOMMUNICATIONTEMPLATE],
    @CACHESOURCEANALYSISRULEDATAACTIVATELOCKED as [CACHESOURCEANALYSISRULEDATAACTIVATELOCKED],
    @EXPORTAFTERACTIVATELOCKED as [EXPORTAFTERACTIVATELOCKED],
    [LASTRUN].[EXPORTPROCESSLASTRUN] as [MARKETINGEFFORTLASTEXPORTED]
  from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID]
  left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
  left outer join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID]
  left outer join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MKTSEGMENTATION].[ID]
  left join dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETLASTRUNDATE_BULK]() as [LASTRUN] on [LASTRUN].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  where [MKTSEGMENTATIONACTIVATEPROCESS].[ID] = @ID;

  return 0;