USP_COMMUNICATIONS_CREATEORUPDATESEGMENT

CREATE procedure dbo.USP_COMMUNICATIONS_CREATEORUPDATESEGMENT
(
  @ID uniqueidentifier output,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @SELECTIONS xml,
  @MAILTYPECODE tinyint,
  @DELIVERYMETHODCODE tinyint,
  @EXCLUDECONSTITSBASEDONPREFERENCE bit,
  @INCLUDECONSTITSWITHOUTPREFERENCE bit,
  @SEGMENTATIONID uniqueidentifier,
  @PACKAGEID as uniqueidentifier,
  @ASKLADDERID as uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONID as uniqueidentifier,
  @NAMEFORMATPARAMETERID as uniqueidentifier,
  @MAILDATE as datetime,
  @ESTIMATEDRESPONSERATE decimal(5, 2) = 0,
  @ESTIMATEDAVERAGEGIFTAMOUNT money = 0
)
with execute as owner
as
begin
  --This procedure creates a segment and a view that unions the selections together. 

  -- Currently, Appeal Mailings are the only form of communication allowing multiple selections, but this is subject to change


  -- @MAILTYPECODE pertains to the MailTypeCode field in dbo.MAILPREFERENCE for constituent communication preferences

  --    0 - Revenue Acknowledgements

  --    1 - Appeals

  --    2 - Events

  --    3 - General Correspondence

  --    4 - Reminders

  --    5 - Receipts

  --    6 - Planned Gift Acknowledgements

  --    7 - Tribute Acknowledgements

  --    8 - Stewardship

  --    etc...  This code should not need to change as new types are introduced...


  -- DELIVERYMETHODCODE also pertains the corresponding field in dbo.MAILPREFERENCE

  --    0 - Mail

  --    1 - Email


  set nocount on;

  declare @CURRENTDATE datetime = getDate();

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

  begin try

    declare @SEGMENTTYPE nvarchar(20);

    if @DELIVERYMETHODCODE = 1
      set @SEGMENTTYPE = 'Email';
    else
      set @SEGMENTTYPE = 'Mail';

    declare @UPDATE bit = 1;
    if @ID is null or @ID = '00000000-0000-0000-0000-000000000000'
      begin
        set @ID = newId();
        set @UPDATE = 0;
      end

    declare @BBECRECORDSOURCEID uniqueidentifier;
    set @BBECRECORDSOURCEID = dbo.UFN_MKTRECORDSOURCE_GETFIRSTBBECRECORDSOURCEID();

    declare @MKTSEGMENTNAME nvarchar(100) = dbo.UFN_MKTSEGMENT_GETUNIQUENAME(@ID, @SEGMENTTYPE + ' Segment: ' + @NAME, null);
    declare @MKTSEGMENTDESCRIPTION nvarchar(255) = 'Auto generated ' + lower(@SEGMENTTYPE) + ' segment for mailing "' + @NAME + '".';

    if @UPDATE = 0
      -- Save the segment

      insert into dbo.MKTSEGMENT
      (
        ID,
        NAME,
        DESCRIPTION,
        SEGMENTTYPECODE,
        SEGMENTCATEGORYCODEID,
        CODE,
        QUERYVIEWCATALOGID,
        PARTDEFINITIONVALUESID,
        ISSYSTEM,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      ) 
      values 
      (
        @ID,
        @MKTSEGMENTNAME,
        @MKTSEGMENTDESCRIPTION,
        1,
        null,
        '',
        @BBECRECORDSOURCEID,
        null,
        1,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );

    else
      update dbo.MKTSEGMENT
      set
        NAME = @MKTSEGMENTNAME,
        DESCRIPTION = @MKTSEGMENTDESCRIPTION,
        ISSYSTEM = 1,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @ID;

    if @UPDATE = 0
      -- Insert a parameter set for the refresh process for this segment

      insert into dbo.MKTSEGMENTREFRESHPROCESS
      (
        ID,
        SEGMENTID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      values
      (
        newid(),
        @ID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );

    -- Save all the selections

    if @UPDATE = 0
      exec dbo.USP_MKTSEGMENT_GETSELECTIONS_ADDFROMXML @ID, null, @SELECTIONS, @CHANGEAGENTID, @CURRENTDATE;
    else
      exec dbo.USP_MKTSEGMENT_GETSELECTIONS_UPDATEFROMXML @ID, null, @SELECTIONS, @CHANGEAGENTID, @CURRENTDATE;

    -- Create the VIEW and add it to the IDSETREGISTER

    declare @SEGSQL nvarchar(max);
    declare @VIEWSQL nvarchar(max);
    declare @SEGMENTVIEW nvarchar(60);
    declare @TABLECOUNT int;
    declare @BASETABLE nvarchar(255);
    declare @UNIONTABLE nvarchar(255);
    declare @SELECTIONNAME nvarchar(300);
    declare @SEGMENTNAME nvarchar(100);
    declare @SEGMENTDESCRIPTION nvarchar(255);
    declare @DATATYPE nvarchar(128);
    declare @SELECTIONID uniqueidentifier;
    declare @IDSETREGISTERID uniqueidentifier;
    declare @RECORDTYPEID uniqueidentifier;
    declare @RETVAL int;

    set @TABLECOUNT = 1;
    set @BASETABLE = null;

    -- Get the segment information

    select
      @SEGMENTVIEW = dbo.UFN_MKTSEGMENT_MAKEVIEWNAME(MKTSEGMENT.ID),
      @SEGMENTNAME = MKTSEGMENT.NAME + ' (' + @SEGMENTTYPE + ' Segment)',
      @SEGMENTDESCRIPTION = MKTSEGMENT.DESCRIPTION,
      @DATATYPE = QUERYVIEWCATALOG.PRIMARYKEYTYPENAME,
      @RECORDTYPEID = QUERYVIEWCATALOG.RECORDTYPEID
    from dbo.MKTSEGMENT
    inner join dbo.QUERYVIEWCATALOG 
      on QUERYVIEWCATALOG.ID = MKTSEGMENT.QUERYVIEWCATALOGID
    where MKTSEGMENT.ID = @ID;

    -- Segment selections

    declare SEGMENTCURSOR cursor local fast_forward for
      select
        MKTSEGMENTSELECTION.SELECTIONID
      from dbo.MKTSEGMENTSELECTION
      inner join dbo.IDSETREGISTER on MKTSEGMENTSELECTION.SELECTIONID = IDSETREGISTER.ID
      where MKTSEGMENTSELECTION.SEGMENTID = @ID;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SELECTIONID;

    while (@@FETCH_STATUS = 0)
    begin
      --Remove the CR and LF characters since we are inserting this name into a dash-dash (--) comment in the SQL we are building...

      select
        @SELECTIONNAME = replace(replace(NAME, char(13), ''), char(10), '')
      from dbo.IDSETREGISTER
      where ID = @SELECTIONID;

      if @BASETABLE is null
      begin
        set @BASETABLE = 'T' + convert(nvarchar(10), @TABLECOUNT) + '';
        set @SEGSQL = 'select '+ @BASETABLE +'.ID from dbo.' + dbo.UFN_MKTSELECTION_GETFUNCTIONNAME(@SELECTIONID) + ' as ' + @BASETABLE + '  --' + @SELECTIONNAME + char(13);

      end 
      else
      begin
        set @UNIONTABLE = 'T' + convert(nvarchar(10), @TABLECOUNT) + '';
        set @SEGSQL = isnull(@SEGSQL,'') + 'union select '+ @UNIONTABLE +'.ID from dbo.' + dbo.UFN_MKTSELECTION_GETFUNCTIONNAME(@SELECTIONID) + ' as ' + @UNIONTABLE + ' --' + @SELECTIONNAME + char(13);

      end

      set @TABLECOUNT = @TABLECOUNT + 1;
      fetch next from SEGMENTCURSOR into @SELECTIONID;
    end;

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;

    -- Create or alter the segment view

    declare @OBJID int;
    select @OBJID = object_id(@SEGMENTVIEW, N'V');
    if @OBJID is null
      set @VIEWSQL = 'create';
    else
      set @VIEWSQL = 'alter';

    set @VIEWSQL = @VIEWSQL + ' view dbo.' + @SEGMENTVIEW + '' + char(13) +
      'as' + char(13);


    if @BASETABLE is null
        set @VIEWSQL = @VIEWSQL + 'select top(0) cast(null as ' + @DATATYPE + ') as ID';
    else 
      begin
        set @SEGSQL = 'with [SELECTIONUNION] as (' + char(13) 
              + @SEGSQL 
              + ')' + char(13)

        if @MAILTYPECODE = 2
          begin
            set @SEGSQL = @SEGSQL
              + ',[PARAMETERSET] as (' + char(13)
              + '  select ' + char(13)
              + '    [EVENT].[EVENTCATEGORYCODEID] [ID]' + char(13)
              + '  from dbo.[INVITATION]' + char(13)
              + '  inner join dbo.[EVENT]' + char(13)
              + '    on [INVITATION].[EVENTID] = [EVENT].[ID]' + char(13)
              + '  where [INVITATION].[ID] = ''' + cast(@SEGMENTATIONID as nvarchar(36)) +  '''' + char(13)
              + '), ' + char(13)
              + '[MAILPREFERENCES] as (' + char(13)
              + '  select ' + char(13)
              + '    [MAILPREFERENCE].[CONSTITUENTID], ' + char(13)
              + '    [MAILPREFERENCE].[DELIVERYMETHODCODE], ' + char(13)
              + '    [MAILPREFERENCE].[SENDMAIL], ' + char(13)
              + '    [MAILPREFERENCE].[DONOTSENDOTHERCHANNEL], ' + char(13)
              + '    case ' + char(13)
              + '      when [MAILPREFERENCE].[EVENTCATEGORYCODEID] is not null and [MAILPREFERENCE].[EVENTCATEGORYCODEID] = (select [ID] from [PARAMETERSET]) then 1' + char(13)
              + '      when [MAILPREFERENCE].[EVENTCATEGORYCODEID] is null then 0 ' + char(13)
              + '      else -1 ' + char(13)
              + '    end [MATCHSCORE], ' + char(13)
              + '    [MAILPREFERENCE].[ID], ' + char(13)
              + '    [MAILPREFERENCE].[SITEID]' + char(13)
              + '  from dbo.[MAILPREFERENCE] ' + char(13)
              + '  inner join [SELECTIONUNION] on [MAILPREFERENCE].[CONSTITUENTID] = [SELECTIONUNION].[ID]' + char(13)
              + '  where [MAILPREFERENCE].[MAILTYPECODE] = ' + cast(@MAILTYPECODE as nvarchar) + char(13)
              + '  and ([MAILPREFERENCE].[EVENTCATEGORYCODEID] is null or [MAILPREFERENCE].[EVENTCATEGORYCODEID] in (select [ID] from [PARAMETERSET]))' + char(13)
              + '),' + char(13)
              + '[BESTMATCHMAILPREFERENCE] as (' + char(13)
              + '  select [MP].[CONSTITUENTID], [MP].[DELIVERYMETHODCODE], [MP].[SENDMAIL], [MP].[DONOTSENDOTHERCHANNEL], [MP].[MATCHSCORE], [MP].[ID], [MP].[SITEID] ' + char(13)
              + '  from [MAILPREFERENCES] [MP] ' + char(13)
              + '  where [MP].[MATCHSCORE] = (' + char(13)
              + '    select ' + char(13)
              + '      max([MATCHSCORE]) ' + char(13)
              + '    from [MAILPREFERENCES] ' + char(13)
              + '    where [MAILPREFERENCES].[CONSTITUENTID] = [MP].[CONSTITUENTID] ' + char(13)
              + '    group by [CONSTITUENTID] ' + char(13)
              + '  )' + char(13)
              + '), ' + char(13) +
              + '[EVENTSITEIDS] as (' + char(13)
              + '  select [E].[SITEID] from dbo.[EVENTSITE] [E]' + char(13)
              + '  inner join dbo.[INVITATION] [I] on [E].[EVENTID] = [I].[EVENTID]' + char(13)
              + '  where [I].[ID] = ''' + cast(@SEGMENTATIONID as nvarchar(36)) +  '''' + char(13)
              + ')' + char(13);
          end

        set @SEGSQL = @SEGSQL 
              + 'select distinct [SELECTIONUNION].[ID] ' + char(13)
              + 'from [SELECTIONUNION] ' + char(13);

        if @MAILTYPECODE = 2
            set @SEGSQL = @SEGSQL
              + ' left outer join [BESTMATCHMAILPREFERENCE] [MAILPREFERENCE] ' + char(13)
              + '   on [SELECTIONUNION].[ID] = [MAILPREFERENCE].[CONSTITUENTID] ' + char(13);
        else
            set @SEGSQL = @SEGSQL
              + 'left join dbo.[MAILPREFERENCE] ' + char(13)
              + '  on [SELECTIONUNION].[ID] = [MAILPREFERENCE].[CONSTITUENTID] ' + char(13)
              + '  and [MAILPREFERENCE].[MAILTYPECODE] = ' + cast(@MAILTYPECODE as nvarchar) + char(13);


        set @SEGSQL = @SEGSQL
          + 'where ' + char(13);

        -- Build the where clause to filter constits based on mail preferences

        if @INCLUDECONSTITSWITHOUTPREFERENCE = 1
      begin

        if @DELIVERYMETHODCODE = 1    -- Email

          begin
            set @SEGSQL = @SEGSQL 
                + '  ([MAILPREFERENCE].[ID] is null and exists (' + char(13)
                + '    select ID ' + char(13)
                + '    from dbo.EMAILADDRESS ' + char(13)
                + '    where CONSTITUENTID = [SELECTIONUNION].[ID] ' + char(13)
                + '    and DONOTEMAIL = 0 ' + char(13)
                + '    and (STARTDATE is null or STARTDATE <= ''' + cast(@MAILDATE as nvarchar) + ''') ' + char(13)
                + '    and (ENDDATE is null or ENDDATE >= ''' + cast(@MAILDATE as nvarchar) + ''') ' + char(13)
                + '    ) ' + char(13)
                + '  ) ' + char(13)
                + '  or (' + char(13);

          end

        else
          set @SEGSQL = @SEGSQL + 
            '  [MAILPREFERENCE].[ID] is null or (' + char(13);


        set @SEGSQL = @SEGSQL +
          '  [MAILPREFERENCE].[SENDMAIL] = 1 ' + char(13);

      end
    else
      set @SEGSQL = @SEGSQL +
          '  coalesce([MAILPREFERENCE].[SENDMAIL], 1) = 1 ' + char(13);

        if @EXCLUDECONSTITSBASEDONPREFERENCE = 1
          set @SEGSQL = @SEGSQL +
                '   and coalesce([MAILPREFERENCE].[DELIVERYMETHODCODE], ' + cast(@DELIVERYMETHODCODE as nvarchar) + ') = ' + cast(@DELIVERYMETHODCODE as nvarchar) + char(13);
        else
          -- Single channel

          -- Mail or Email only

          --  Include constits with no pref, that prefer this channel, 

          --    or that prefer another channel but are ok with receiving communications on other channels

          set @SEGSQL = @SEGSQL 
              + '    and (' + char(13)
              + '      coalesce([MAILPREFERENCE].[DELIVERYMETHODCODE], ' + cast(@DELIVERYMETHODCODE as nvarchar) + ')  = ' + cast(@DELIVERYMETHODCODE as nvarchar) + ' ' + char(13)
              + '      or ( ' + char(13)
              + '        coalesce([MAILPREFERENCE].[DELIVERYMETHODCODE], ' + cast(@DELIVERYMETHODCODE as nvarchar) + ')  <> ' + cast(@DELIVERYMETHODCODE as nvarchar) + ' ' + char(13)
              + '        and coalesce([MAILPREFERENCE].[DONOTSENDOTHERCHANNEL], 0) = 0 ' + char(13)
              + '      ) ' + char(13)
              + '    ) ' + char(13);

        if @INCLUDECONSTITSWITHOUTPREFERENCE = 1
          set @SEGSQL = @SEGSQL + ')' + char(13)

        if @MAILTYPECODE = 2
          set @SEGSQL = @SEGSQL
              + '    and (([SITEID] is null) or ([SITEID] in (select [SITEID] from [EVENTSITEIDS])))' + char(13);

        set @VIEWSQL = @VIEWSQL + @SEGSQL;
      end

    exec (@VIEWSQL);

    -- Grant rights for new views

    if @OBJID is null
      exec ('grant select on dbo.' + @SEGMENTVIEW + ' to BBAPPFXSERVICEROLE');

    -- Add to the ID Set Register

    -- Get the view row count

    declare @COUNTSQL nvarchar(max);
    declare @PARAMETERS nvarchar(max);
    declare @NUMROWS int;
    set @COUNTSQL = 'select @NUMROWS = count(1) from dbo.' + @SEGMENTVIEW + '';
    set @PARAMETERS = N'@NUMROWS int OUTPUT' 
    exec sp_executeSQL @COUNTSQL, @PARAMETERS, @NUMROWS OUTPUT;

    -- Get the ID set ID if this is an edit operation, else it will be null.  Also get the segment record type.

    select distinct
      @IDSETREGISTERID = MKTSEGMENT.IDSETREGISTERID,
      @RECORDTYPEID = isnull(IDSETREGISTER.RECORDTYPEID, @RECORDTYPEID)
    from dbo.MKTSEGMENT
    left join dbo.MKTSEGMENTSELECTION 
      on MKTSEGMENTSELECTION.SEGMENTID = MKTSEGMENT.ID
    left join dbo.IDSETREGISTER 
      on MKTSEGMENTSELECTION.SELECTIONID = IDSETREGISTER.ID
    where MKTSEGMENT.ID = @ID;

    -- Create the ID set...

    exec dbo.USP_IDSETREGISTER_CREATEORUPDATE 
      @IDSETREGISTERID output,
      @SEGMENTNAME,
      @SEGMENTDESCRIPTION,
      @SEGMENTVIEW,
      0,
      @RECORDTYPEID,
      0,
      1,
      @NUMROWS,
      @CHANGEAGENTID;

    -- Mark IDSET as inactive to filter from searches

    -- Make sure the RECORDTYPEID gets set.  The SP doesn't update it on an UPDATE.

    update dbo.IDSETREGISTER set
      RECORDTYPEID = @RECORDTYPEID,
      ACTIVE = 0,
      ISSYSTEM = 1,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where ID = @IDSETREGISTERID;

    -- Save the ID set ID on the segment table...

    update dbo.MKTSEGMENT set
      IDSETREGISTERID = @IDSETREGISTERID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE 
    where ID = @ID;

    -- Create or update the segmentation segment

    declare @MKTSEGMENTATIONSEGMENTID uniqueidentifier;
    declare @SEQUENCE integer;

    select 
      @MKTSEGMENTATIONSEGMENTID = ID
    from dbo.MKTSEGMENTATIONSEGMENT
    where SEGMENTID = @ID;

    set @SEQUENCE = (case when @INCLUDECONSTITSWITHOUTPREFERENCE = 0 then 2 else 1 end);

    if @MKTSEGMENTATIONSEGMENTID is null
      exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT] 
        @MKTSEGMENTATIONSEGMENTID output,
        @CHANGEAGENTID,
        @SEGMENTATIONID,                --@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE

        null,                           --@MARKETINGPLANBRIEFID

        @ID,                            --@SEGMENTID

        '',                             --@CODE

        '',                             --@TESTSEGMENTCODE

        @PACKAGEID,                     --@PACKAGEID

        '',                             --@PACKAGECODE

        @ESTIMATEDRESPONSERATE,         --@RESPONSERATE

        @ESTIMATEDAVERAGEGIFTAMOUNT,    --@GIFTAMOUNT

        100,                            --@SAMPLESIZE

        0,                              --@SAMPLESIZETYPECODE

        0,                              --@SAMPLESIZEMETHODCODE

        @SEQUENCE,
        @ASKLADDERID,                   --@MKTASKLADDERID

        1,                              --@SAMPLESIZEEXCLUDEREMAINDER

        0,                              --@OVERRIDEADDRESSPROCESSING

        1,                              --@USEADDRESSPROCESSING

        @ADDRESSPROCESSINGOPTIONID,
        1,                              --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE

        @MAILDATE,                      --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE

        @NAMEFORMATPARAMETERID,
        null,                           --@CODEVALUEID

        null,                           --@TESTSEGMENTCODEVALUEID

        null,                           --@PACKAGECODEVALUEID

        null,                           --@ITEMLIST

        '',                             --@CHANNELSOURCECODE

        null;                           --@CHANNELSOURCECODEVALUEID


    else
    begin
      exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEGMENT_6]
        @MKTSEGMENTATIONSEGMENTID,
        @CHANGEAGENTID,
        @ID,                            --@SEGMENTID

        '',                             --@CODE

        '',                             --@TESTSEGMENTCODE

        @PACKAGEID,                     --@PACKAGEID

        '',                             --@PACKAGECODE

        @ESTIMATEDRESPONSERATE,         --@RESPONSERATE

        @ESTIMATEDAVERAGEGIFTAMOUNT,    --@GIFTAMOUNT

        100,                            --@SAMPLESIZE

        0,                              --@SAMPLESIZETYPECODE

        0,                              --@SAMPLESIZEMETHODCODE

        1,                              --@SAMPLESIZEEXCLUDEREMAINDER

        @ASKLADDERID,                   --@MKTASKLADDERID

        0,                              --@OVERRIDEADDRESSPROCESSING

        1,                              --@USEADDRESSPROCESSING

        @ADDRESSPROCESSINGOPTIONID, 
        1,                              --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE

        @MAILDATE,                      --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE

        @NAMEFORMATPARAMETERID,
        null,                           --@CODEVALUEID

        null,                           --@PACKAGECODEVALUEID

        null,                           --@TESTSEGMENTCODEVALUEID

        null,                           --@ITEMLIST

        '',                             --@CHANNELSOURCECODE

        null,                           --@CHANNELSOURCECODEVALUEID

        0,                              --@EXCLUDESPOUSE

        0,                              --@OVERRIDEBUSINESSUNITS

        null;                     --@BUSINESSUNITS


      -- update the sequence field

      update dbo.MKTSEGMENTATIONSEGMENT set
        SEQUENCE = @SEQUENCE,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where ID = @MKTSEGMENTATIONSEGMENTID;

    end

  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    set @RETVAL = 1;
  end catch

  return isnull(@RETVAL, 0);
end