UFN_MKTSEGMENTATIONACTIVATE_VALIDATESOURCECODEUNIQUENESS

CREATE function dbo.[UFN_MKTSEGMENTATIONACTIVATE_VALIDATESOURCECODEUNIQUENESS]
(
  @SEGMENTATIONID uniqueidentifier
)
returns tinyint
as
begin
  declare @MAILINGTYPECODE tinyint;
  declare @HASVENDORMANAGEDSEGMENTS bit = 0;
  declare @SEGMENTATIONISHISTORICAL bit;
  declare @SOURCECODEISHISTORICAL bit;
  declare @SOURCECODESTABLE table ([SEGMENTID] uniqueidentifier not null, [TESTSEGMENTID] uniqueidentifier, [SOURCECODE] nvarchar(50));
  declare @SEGMENTID uniqueidentifier;
  declare @TESTSEGMENTID uniqueidentifier;
  declare @SOURCECODE nvarchar(50);
  declare @ERRORCODE tinyint;

  set @ERRORCODE = 0;

  select
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @SEGMENTATIONISHISTORICAL = [MKTSEGMENTATION].[ISHISTORICAL],
    @SOURCECODEISHISTORICAL = isnull([MKTSOURCECODE].[ISHISTORICAL], 0)
  from dbo.[MKTSEGMENTATION]
  left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  -- get all the segments/test segments for the mailing so we can compare all of their source codes

  insert into @SOURCECODESTABLE
    select
      [SEGMENTID],
      [TESTSEGMENTID],
      dbo.[UFN_MKTSOURCECODE_BUILDCODE](isnull([TESTSEGMENTID], [SEGMENTID]), default, default)
    from dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETSEGMENTS](@SEGMENTATIONID) as [SEGMENTS]
    where [SEGMENTS].[EXCLUDE] = 0;

  if @MAILINGTYPECODE = 0
    -- (note that associating white mail segments with marketing efforts was ultimately scrapped and thus this will never return a row)

    insert into @SOURCECODESTABLE
      select
        [ID],
        null,
        dbo.[UFN_MKTSOURCECODE_BUILDCODE]([ID], default, default)
      from dbo.[MKTSEGMENTWHITEMAIL]
      where [SEGMENTATIONID] = @SEGMENTATIONID;

  if exists(select top 1 1
            from @SOURCECODESTABLE as [SCT]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [SCT].[SEGMENTID]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
            where [MKTSEGMENT].[SEGMENTTYPECODE] = 2 and dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]) = 1)
    set @HASVENDORMANAGEDSEGMENTS = 1;

  -- if there is at least one vendor managed segment

  -- or if this is a public media marketing effort

  -- or if this is a historical marketing effort

  if @HASVENDORMANAGEDSEGMENTS = 1 or @MAILINGTYPECODE = 4 or @SEGMENTATIONISHISTORICAL = 1
    begin
      -- it doesn't make sense to allow a historical marketing effort, or any marketing effort with vendor managed segments, to be activated without a source code

      -- returning an error code so that we can provide a better error message

      if @HASVENDORMANAGEDSEGMENTS = 1 or @SEGMENTATIONISHISTORICAL = 1
        if (select [SOURCECODEID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID) is null
          set @ERRORCODE = 2;

      if @ERRORCODE = 0
        begin
          -- loop through each applicable segment and make sure its source code is unique amongst all the other segments and test segments

          if @MAILINGTYPECODE <> 4 and @SOURCECODEISHISTORICAL = 0
            declare SEGMENTCURSOR cursor local fast_forward for
              select 
                [TEMP].[SEGMENTID], 
                [TEMP].[TESTSEGMENTID],
                dbo.[UFN_MKTSOURCECODE_BUILDCODE](isnull([TEMP].[TESTSEGMENTID], [TEMP].[SEGMENTID]), default, default)
              from (
                select [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID], [MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENTSEQUENCE], null as [TESTSEGMENTID], null as [TESTSEGMENTSEQUENCE]
                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
                union
                select [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID], [MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENTSEQUENCE], [MKTSEGMENTATIONTESTSEGMENT].[ID] as [TESTSEGMENTID], [MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE] as [TESTSEGMENTSEQUENCE]
                from [MKTSEGMENTATIONTESTSEGMENT]
                inner join [MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
                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
                union
                select [MKTSEGMENTWHITEMAIL].[ID] as [SEGMENTID], null as [SEGMENTSEQUENCE], null as [TESTSEGMENTID], null as [TESTSEGMENTSEQUENCE]
                from dbo.[MKTSEGMENTWHITEMAIL]
                where [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID] = @SEGMENTATIONID
              ) as [TEMP]
              group by [TEMP].[SEGMENTID], [TEMP].[TESTSEGMENTID], [TEMP].[SEGMENTSEQUENCE], [TEMP].[TESTSEGMENTSEQUENCE]
              order by [TEMP].[SEGMENTSEQUENCE], [TEMP].[TESTSEGMENTSEQUENCE];
          else
            -- for public media marketing efforts or marketing efforts with historical source codes, check all of the segments

            declare SEGMENTCURSOR cursor local fast_forward for
              select 
                [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID], 
                null as [TESTSEGMENTID],
                dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONSEGMENT].[ID], default, default)
              from dbo.[MKTSEGMENTATIONSEGMENT]
              where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
              union
              select 
                [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID], 
                [MKTSEGMENTATIONTESTSEGMENT].[ID] as [TESTSEGMENTID],
                dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTATIONTESTSEGMENT].[ID], default, default)
              from dbo.[MKTSEGMENTATIONTESTSEGMENT]
              inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
              where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID;

          open SEGMENTCURSOR;
          fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @SOURCECODE;

          while (@@FETCH_STATUS = 0)
            begin
              -- check to make sure this segment's source code is unique amongst all the other segments and test segments

              select @ERRORCODE = (case when count(1) = 0 then 0 else 1 end)
              from @SOURCECODESTABLE
              where (
                [SEGMENTID] <> @SEGMENTID or
                ([SEGMENTID] = @SEGMENTID and
                  (
                    (@TESTSEGMENTID is null and [TESTSEGMENTID] is not null)
                     or 
                    (@TESTSEGMENTID is not null and ([TESTSEGMENTID] is null or [TESTSEGMENTID] <> @TESTSEGMENTID))
                  )
                )
              )
              and [SOURCECODE] = @SOURCECODE;

              if @ERRORCODE <> 0
                break;

              fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @SOURCECODE;
            end;

          close SEGMENTCURSOR;
          deallocate SEGMENTCURSOR;
        end;
    end;

  -- if there are white mail segments in the mailing, 

  -- all of their source codes must be unique 

  -- and none of their codes can match any of the segment's codes

  -- (note that associating white mail segments with marketing efforts was ultimately scrapped and thus this will never return a row)

  if exists (select top 1 1 from @SOURCECODESTABLE as [SCT] inner join dbo.[MKTSEGMENTWHITEMAIL] on [MKTSEGMENTWHITEMAIL].[ID] = [SCT].[SEGMENTID])
    begin
      if exists (select top 1 1
                 from @SOURCECODESTABLE as [SCT]
                 inner join dbo.[MKTSEGMENTWHITEMAIL] on [MKTSEGMENTWHITEMAIL].[ID] = [SCT].[SEGMENTID]
                 group by [SCT].[SOURCECODE]
                 having count([SOURCECODE]) > 1)
        set @ERRORCODE = 3;
      else
        begin
          declare SEGMENTCURSOR cursor local fast_forward for
            select 
              [TEMP].[SEGMENTID], 
              [TEMP].[TESTSEGMENTID],
              dbo.UFN_MKTSOURCECODE_BUILDCODE(isnull([TEMP].[TESTSEGMENTID], [TEMP].[SEGMENTID]), default, default)
            from (
              select [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID], [MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENTSEQUENCE], null as [TESTSEGMENTID], null as [TESTSEGMENTSEQUENCE]
              from dbo.[MKTSEGMENTATIONSEGMENT]
              where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
              union
              select [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID], [MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGMENTSEQUENCE], [MKTSEGMENTATIONTESTSEGMENT].[ID] as [TESTSEGMENTID], [MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE] as [TESTSEGMENTSEQUENCE]
              from [MKTSEGMENTATIONTESTSEGMENT]
              inner join [MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
              where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
            ) as [TEMP]

          open SEGMENTCURSOR;
          fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @SOURCECODE;

          while (@@FETCH_STATUS = 0)
            begin
              -- check to make sure each segment's source code does not match a white mail segment's

              select @ERRORCODE = (case when count(1) = 0 then 0 else 3 end)
              from @SOURCECODESTABLE as [SCT]
              inner join dbo.[MKTSEGMENTWHITEMAIL] on [MKTSEGMENTWHITEMAIL].[ID] = [SCT].[SEGMENTID]
              where [SOURCECODE] = @SOURCECODE;

              if @ERRORCODE <> 0
                break;

              fetch next from SEGMENTCURSOR into @SEGMENTID, @TESTSEGMENTID, @SOURCECODE;
            end;

          close SEGMENTCURSOR;
          deallocate SEGMENTCURSOR;
        end;
    end;

  return @ERRORCODE;
end