USP_MKTSEGMENTATIONACTIVATE_SETASKLADDERINFO

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_SETASKLADDERINFO]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @DATATABLE nvarchar(128);
  declare @DBOBJECTNAME sysname;
  declare @DONORIDDATATYPE nvarchar(128);
  declare @IDSETIDDATATYPE nvarchar(128);
  declare @ASKLADDERID uniqueidentifier;
  declare @IDSETREGISTERID uniqueidentifier;
  declare @ENTRYAMOUNTFIELD nvarchar(128);
  declare @ENTRYAMOUNTJOINSQL nvarchar(max);
  declare @JOINIDFIELD nvarchar(128);
  declare @TABLENAME sysname;
  declare @PKFIELDNAME sysname;
  declare @FIELDNAME sysname
  declare @ACTIVE bit;

  declare @ASKLADDERS table(
    [ASKLADDERID] uniqueidentifier not null,
    [RECORDSOURCEID] uniqueidentifier not null,
    [ISOVERRIDE] bit not null,
    [ISSEGMENT] bit not null,
    [ISTESTSEGMENT] bit not null
  );

  declare @MONEYFIELDS table(
    [RECORDSOURCEID] uniqueidentifier not null,
    [RECORDTYPE] nvarchar(100),
    [PARENTTYPE] nvarchar(100),
    [NAME] nvarchar(128),
    [DISPLAYNAME] nvarchar(128),
    [OBJECTNAME] nvarchar(128),
    [OBJECTTYPE] nvarchar(100),
    [PRIMARYKEYFIELD] nvarchar(128),
    [OBJECTKEY] nvarchar(257)
  );

  begin try
    select
      @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID]),
      @DONORIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE]([ID]),
      @JOINIDFIELD = (case [MAILINGTYPECODE]
                        when 1 then 'REVENUEID'
                        when 2 then 'MEMBERSHIPID'
                        when 3 then 'SPONSORSHIPID'
                        when 5 then 'REVENUEID'
                        else 'DONORID'
                      end),
      @ACTIVE = [ACTIVE]
    from dbo.[MKTSEGMENTATION]
    where [ID] = @SEGMENTATIONID;

    --Get all the combinations of ask ladders for this mailing...

    insert into @ASKLADDERS ([ASKLADDERID], [RECORDSOURCEID], [ISOVERRIDE], [ISSEGMENT], [ISTESTSEGMENT])
      --Ask ladder overrides

      select
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[ASKLADDERID],
        coalesce([MKTSEGMENT].[QUERYVIEWCATALOGID], [MKTRECORDSOURCE].[ID], [MKTGIFTRECORDSOURCE].[ID], [MKTMEMBERSHIPRECORDSOURCE].[ID], [MKTSPONSORSHIPRECORDSOURCE].[ID]) as [RECORDSOURCEID],
        1 as [ISOVERRIDE],
        0 as [ISSEGMENT],
        0 as [ISTESTSEGMENT]
      from dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE]
      inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTATIONASKLADDEROVERRIDE].[IDSETREGISTERID]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[RECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID]
      left join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
      left join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
      left join dbo.[MKTMEMBERSHIPRECORDSOURCE] on [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
      left join dbo.[MKTSPONSORSHIPRECORDSOURCE] on [MKTSPONSORSHIPRECORDSOURCE].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
      left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
      left join dbo.[MKTSEGMENT] on [MKTSEGMENT].[CURRENTSEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
      where [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEGMENTATIONID] = @SEGMENTATIONID
      and ([MKTSEGMENTLIST].[ID] is null or [MKTSEGMENTLIST].[STATUSCODE] = 3)
      and coalesce([MKTSEGMENT].[QUERYVIEWCATALOGID], [MKTRECORDSOURCE].[ID], [MKTGIFTRECORDSOURCE].[ID], [MKTMEMBERSHIPRECORDSOURCE].[ID], [MKTSPONSORSHIPRECORDSOURCE].[ID]) is not null

      union

      --Segments

      select
        [MKTSEGMENTATIONSEGMENT].[ASKLADDERID],
        [MKTSEGMENT].[QUERYVIEWCATALOGID] as [RECORDSOURCEID],
        0 as [ISOVERRIDE],
        1 as [ISSEGMENT],
        0 as [ISTESTSEGMENT]
      from dbo.[MKTSEGMENTATIONSEGMENT]
 inner join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
      inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
      left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
      left join dbo.[MKTSEGMENTLIST] on ([MKTSEGMENTLIST].[ID] = case when @ACTIVE = 1 then [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] else [MKTSEGMENT].[CURRENTSEGMENTLISTID] end)
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
      and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[TYPECODE] = 0)  --Exclude vendor managed segments

      and [MKTSEGMENTATIONSEGMENT].[ASKLADDERID] is not null

      union

      --Test segments

      select
        [MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID],
        [MKTSEGMENT].[QUERYVIEWCATALOGID] as [RECORDSOURCEID],
        0 as [ISOVERRIDE],
        0 as [ISSEGMENT],
        1 as [ISTESTSEGMENT]
      from dbo.[MKTSEGMENTATIONTESTSEGMENT]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
      inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
      left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
      left join dbo.[MKTSEGMENTLIST] on ([MKTSEGMENTLIST].[ID] = case when @ACTIVE = 1 then [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] else [MKTSEGMENT].[CURRENTSEGMENTLISTID] end)
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
      and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[TYPECODE] = 0)  --Exclude vendor managed segments

      and [MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID] is not null;

    --Get all the money fields for distinct record sources...

    insert into @MONEYFIELDS ([RECORDSOURCEID], [RECORDTYPE], [PARENTTYPE], [NAME], [DISPLAYNAME], [OBJECTNAME], [OBJECTTYPE], [PRIMARYKEYFIELD], [OBJECTKEY])
      select
        [ASKLADDERS].[RECORDSOURCEID],
        [MF].[RECORDTYPE],
        [MF].[PARENTTYPE],
        [MF].[NAME],
        [MF].[DISPLAYNAME],
        [MF].[OBJECTNAME],
        [MF].[OBJECTTYPE],
        [MF].[PRIMARYKEYFIELD],
        [MF].[OBJECTKEY]
      from (select distinct [RECORDSOURCEID] from @ASKLADDERS) as [ASKLADDERS]
      outer apply dbo.[UFN_MKTASKLADDER_GETMONEYFIELDS]([ASKLADDERS].[RECORDSOURCEID]) as [MF];

    --Overrides:  Update the ASKLADDERID and ENTRYAMOUNT...

    declare OVERRIDECURSOR cursor local fast_forward for
      select
        [ASKLADDERS].[ASKLADDERID],
        [MF].[OBJECTNAME],
        [MF].[PRIMARYKEYFIELD],
        [MF].[NAME],
        [IDSETREGISTER].[ID],
        '[' + [IDSETREGISTER].[DBOBJECTNAME] + ']' + (case [IDSETREGISTER].[OBJECTTYPE] when 1 then '()' when 2 then '(@IDSETREGISTERID)' else '' end),
        (select t.[name] from sys.types t inner join sys.columns c on c.system_type_id = t.system_type_id where c.[object_id] = object_id('dbo.[' + [IDSETREGISTER].[DBOBJECTNAME] + ']') and c.[name] = 'ID')
      from @ASKLADDERS as [ASKLADDERS]
      inner join dbo.[MKTASKLADDER] on [MKTASKLADDER].[ID] = [ASKLADDERS].[ASKLADDERID]
      left join @MONEYFIELDS as [MF] on [MF].[RECORDSOURCEID] = [ASKLADDERS].[RECORDSOURCEID] and [MF].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
      inner join dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE] on [MKTSEGMENTATIONASKLADDEROVERRIDE].[ASKLADDERID] = [ASKLADDERS].[ASKLADDERID]
      inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTATIONASKLADDEROVERRIDE].[IDSETREGISTERID]
      where [ASKLADDERS].[ISOVERRIDE] = 1
      and [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEGMENTATIONID] = @SEGMENTATIONID
      order by [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEQUENCE] asc;

    open OVERRIDECURSOR;
    fetch next from OVERRIDECURSOR into @ASKLADDERID, @TABLENAME, @PKFIELDNAME, @FIELDNAME, @IDSETREGISTERID, @DBOBJECTNAME, @IDSETIDDATATYPE;

    while (@@FETCH_STATUS = 0)
      begin
        set @ENTRYAMOUNTFIELD = dbo.[UFN_MKTSEGMENTATIONASKLADDER_ENTRYAMOUNT](@ASKLADDERID, 'EA', @FIELDNAME);
        set @ENTRYAMOUNTJOINSQL = dbo.[UFN_MKTSEGMENTATIONASKLADDER_JOINSQL](@ASKLADDERID, @DATATABLE, 'DONORID', 'DT', 'EA', @TABLENAME, @PKFIELDNAME);

        set @SQL = 'update dbo.[' + @DATATABLE + '] set' + char(13) +
                   '  [ASKLADDERID] = @ASKLADDERID,' + char(13) +
                   '  [ENTRYAMOUNT] = ' + @ENTRYAMOUNTFIELD + char(13) +
                   'from dbo.[' + @DATATABLE + '] as [DT]' + char(13) +
                   'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DT].[SEGMENTID]' + char(13);

        if @DONORIDDATATYPE = @IDSETIDDATATYPE
          set @SQL += 'inner join dbo.' + @DBOBJECTNAME + ' as [IT] on [IT].[ID] = [DT].[' + @JOINIDFIELD + ']' + char(13); 
        else
          set @SQL += 'inner join dbo.' + @DBOBJECTNAME + ' as [IT] on convert(varchar(36), [DT].[' + @JOINIDFIELD + ']) = convert(varchar(36), [IT].[ID])' + char(13); 

        if @ENTRYAMOUNTJOINSQL is not null
          set @SQL += @ENTRYAMOUNTJOINSQL + char(13);

        set @SQL += 'where [DT].[ASKLADDERID] is null' + char(13) +
                    'and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0;';

        exec sp_executesql @SQL, N'@ASKLADDERID uniqueidentifier, @IDSETREGISTERID uniqueidentifier', @ASKLADDERID = @ASKLADDERID, @IDSETREGISTERID = @IDSETREGISTERID;

        fetch next from OVERRIDECURSOR into @ASKLADDERID, @TABLENAME, @PKFIELDNAME, @FIELDNAME, @IDSETREGISTERID, @DBOBJECTNAME, @IDSETIDDATATYPE;
      end

    close OVERRIDECURSOR;
    deallocate OVERRIDECURSOR;

    --Segments:  Update the ASKLADDERID and ENTRYAMOUNT...

    declare SEGMENTCURSOR cursor local fast_forward for
      select distinct
        [ASKLADDERS].[ASKLADDERID],
        [MF].[OBJECTNAME],
        [MF].[PRIMARYKEYFIELD],
        [MF].[NAME]
      from @ASKLADDERS as [ASKLADDERS]
      inner join dbo.[MKTASKLADDER] on [MKTASKLADDER].[ID] = [ASKLADDERS].[ASKLADDERID]
      left join @MONEYFIELDS as [MF] on [MF].[RECORDSOURCEID] = [ASKLADDERS].[RECORDSOURCEID] and [MF].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ASKLADDERID] = [ASKLADDERS].[ASKLADDERID]
      where [ASKLADDERS].[ISSEGMENT] = 1
      and [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @ASKLADDERID, @TABLENAME, @PKFIELDNAME, @FIELDNAME;

    while (@@FETCH_STATUS = 0)
      begin
        set @ENTRYAMOUNTFIELD = dbo.[UFN_MKTSEGMENTATIONASKLADDER_ENTRYAMOUNT](@ASKLADDERID, 'EA', @FIELDNAME);
        set @ENTRYAMOUNTJOINSQL = dbo.[UFN_MKTSEGMENTATIONASKLADDER_JOINSQL](@ASKLADDERID, @DATATABLE, 'DONORID', 'DT', 'EA', @TABLENAME, @PKFIELDNAME);

        set @SQL = 'update dbo.[' + @DATATABLE + '] set' + char(13) +
                   '  [ASKLADDERID] = @ASKLADDERID,' + char(13) +
                   '  [ENTRYAMOUNT] = ' + @ENTRYAMOUNTFIELD + char(13) +
                   'from dbo.[' + @DATATABLE + '] as [DT]' + char(13) +
                   'inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DT].[SEGMENTID]' + char(13);

        if @ENTRYAMOUNTJOINSQL is not null
          set @SQL += @ENTRYAMOUNTJOINSQL + char(13);

        set @SQL += 'where [MKTSEGMENTATIONSEGMENT].[ASKLADDERID] = @ASKLADDERID' + char(13) +
                    'and [DT].[TESTSEGMENTID] is null' + char(13) +
                    'and [DT].[ASKLADDERID] is null' + char(13);

        exec sp_executesql @SQL, N'@ASKLADDERID uniqueidentifier', @ASKLADDERID = @ASKLADDERID;

        fetch next from SEGMENTCURSOR into @ASKLADDERID, @TABLENAME, @PKFIELDNAME, @FIELDNAME;
      end

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;

    --Test segments:  Update the ASKLADDERID and ENTRYAMOUNT...

    declare TESTSEGMENTCURSOR cursor local fast_forward for
      select distinct
        [ASKLADDERS].[ASKLADDERID],
        [MF].[OBJECTNAME],
        [MF].[PRIMARYKEYFIELD],
        [MF].[NAME]
      from @ASKLADDERS as [ASKLADDERS]
      inner join dbo.[MKTASKLADDER] on [MKTASKLADDER].[ID] = [ASKLADDERS].[ASKLADDERID]
      left join @MONEYFIELDS as [MF] on [MF].[RECORDSOURCEID] = [ASKLADDERS].[RECORDSOURCEID] and [MF].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
      inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID] = [ASKLADDERS].[ASKLADDERID]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
      where [ASKLADDERS].[ISTESTSEGMENT] = 1
      and [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
      and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0;

    open TESTSEGMENTCURSOR;
    fetch next from TESTSEGMENTCURSOR into @ASKLADDERID, @TABLENAME, @PKFIELDNAME, @FIELDNAME;

    while (@@FETCH_STATUS = 0)
      begin
        set @ENTRYAMOUNTFIELD = dbo.[UFN_MKTSEGMENTATIONASKLADDER_ENTRYAMOUNT](@ASKLADDERID, 'EA', @FIELDNAME);
        set @ENTRYAMOUNTJOINSQL = dbo.[UFN_MKTSEGMENTATIONASKLADDER_JOINSQL](@ASKLADDERID, @DATATABLE, 'DONORID', 'DT', 'EA', @TABLENAME, @PKFIELDNAME);

        set @SQL = 'update dbo.[' + @DATATABLE + '] set' + char(13) +
                   ' [ASKLADDERID] = @ASKLADDERID,' + char(13) +
                   ' [ENTRYAMOUNT] = ' + @ENTRYAMOUNTFIELD + char(13) +
                   'from dbo.[' + @DATATABLE + '] as [DT]' + char(13) +
                   'inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [DT].[TESTSEGMENTID]' + char(13);

        if @ENTRYAMOUNTJOINSQL is not null
          set @SQL += @ENTRYAMOUNTJOINSQL + char(13);

        set @SQL += 'where [MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID] = @ASKLADDERID' + char(13) +
                    'and [DT].[ASKLADDERID] is null' + char(13);

        exec sp_executesql @SQL, N'@ASKLADDERID uniqueidentifier', @ASKLADDERID = @ASKLADDERID;

        fetch next from TESTSEGMENTCURSOR into @ASKLADDERID, @TABLENAME, @PKFIELDNAME, @FIELDNAME;
      end

    close TESTSEGMENTCURSOR;
    deallocate TESTSEGMENTCURSOR;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;