USP_MKTSEGMENTLIST_UPDATEEXISTINGSELECTIONS

create procedure dbo.[USP_MKTSEGMENTLIST_UPDATEEXISTINGSELECTIONS]
(
  @SEGMENTID uniqueidentifier,
  @ROLLBACK bit = 0,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @NEWQUERYVIEWCATALOGID uniqueidentifier;
  declare @NEWRECORDTYPEID uniqueidentifier;
  declare @NEWRECORDTYPENAME nvarchar(50);
  declare @NEWVIEWNAME nvarchar(128);
  declare @OLDQUERYVIEWCATALOGID uniqueidentifier;
  declare @OLDRECORDTYPEID uniqueidentifier;
  declare @OLDVIEWNAME nvarchar(128);
  declare @ADHOCQUERYID uniqueidentifier;
  declare @QUERYDEFINITIONXML xml;
  declare @CURRENTDATE datetime;

  begin try
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();
    set @ROLLBACK = isnull(@ROLLBACK, 0);

    if @ROLLBACK = 0
      --Grab the new queryview info...

      select
        @NEWQUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
        @NEWRECORDTYPEID = [RECORDTYPE].[ID],
        @NEWRECORDTYPENAME = [RECORDTYPE].[NAME],
        @NEWVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME]
      from dbo.[MKTSEGMENT]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
      inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
      where [MKTSEGMENT].[ID] = @SEGMENTID;
    else
      --Rollback, so grab the previous query view info...

      with [PREV] ([ID]) as
      (
        select top 1 [MKTSEGMENTLIST].[ID]
        from dbo.[MKTSEGMENT]
        inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID]
        where [MKTSEGMENT].[ID] = @SEGMENTID
        and [MKTSEGMENT].[CURRENTSEGMENTLISTID] <> [MKTSEGMENTLIST].[ID]
        order by [MKTSEGMENTLIST].[DATEADDED] desc
      )
      select
        @NEWQUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
        @NEWRECORDTYPEID = [RECORDTYPE].[ID],
        @NEWRECORDTYPENAME = [RECORDTYPE].[NAME],
        @NEWVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME]
      from [PREV]
      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [PREV].[ID]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
      inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [QUERYVIEWCATALOG].[RECORDTYPEID];

    --Grab the old queryview info...

    select top 1
      @OLDQUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
      @OLDRECORDTYPEID = [QUERYVIEWCATALOG].[RECORDTYPEID],
      @OLDVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME]
    from dbo.[MKTSEGMENTLIST]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
    where [MKTSEGMENTLIST].[SEGMENTID] = @SEGMENTID
    and [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] <> @NEWQUERYVIEWCATALOGID
    order by [MKTSEGMENTLIST].[DATEADDED] desc;

    --Loop through all adhoc queries created against the old queryview and update them to use the new queryview...

    declare ADHOCQUERYCURSOR cursor local fast_forward for
      select [ID], [QUERYDEFINITIONXML]
      from dbo.[ADHOCQUERY]
      where [QUERYVIEWCATALOGID] = @OLDQUERYVIEWCATALOGID;

    open ADHOCQUERYCURSOR;
    fetch next from ADHOCQUERYCURSOR into @ADHOCQUERYID, @QUERYDEFINITIONXML;

    while (@@FETCH_STATUS = 0)
    begin
      --Replace the record type, QueryView ID, and all references to the old QueryView objectname...

      set @QUERYDEFINITIONXML.modify(N'declare namespace bbspec="Blackbaud.AppFx.WebService.API.1"; replace value of /bbspec:AdHocQuery[1]/@RecordType with sql:variable("@NEWRECORDTYPENAME")');
      set @QUERYDEFINITIONXML.modify(N'declare namespace bbspec="Blackbaud.AppFx.WebService.API.1"; replace value of /bbspec:AdHocQuery[1]/bbspec:QueryView[1]/@ID with sql:variable("@NEWQUERYVIEWCATALOGID")');
      set @QUERYDEFINITIONXML = replace(cast(@QUERYDEFINITIONXML as nvarchar(max)), @OLDVIEWNAME, @NEWVIEWNAME);

      --Update the Ad-hoc query record...

      update dbo.[ADHOCQUERY] set
        [QUERYVIEWCATALOGID] = @NEWQUERYVIEWCATALOGID,
        [QUERYDEFINITIONXML] = @QUERYDEFINITIONXML,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @ADHOCQUERYID;

      fetch next from ADHOCQUERYCURSOR into @ADHOCQUERYID, @QUERYDEFINITIONXML;
    end

    close ADHOCQUERYCURSOR;
    deallocate ADHOCQUERYCURSOR;

    --Update the record type for all ID sets that have the old record type.  This

    --includes the standard ID set, duplicate ID set, any selections, and any segments.

    if @OLDRECORDTYPEID <> @NEWRECORDTYPEID
      update dbo.[IDSETREGISTER] set
        [RECORDTYPEID] = @NEWRECORDTYPEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [RECORDTYPEID] = @OLDRECORDTYPEID;
  end try

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

  return 0;