USP_MKTRECORDSOURCE_EDITSAVE

CREATE procedure dbo.[USP_MKTRECORDSOURCE_EDITSAVE]
(
  @QUERYVIEWCATALOGID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @SELECTED bit,
  @MARKETINGINFOQUERYVIEWCATALOGID uniqueidentifier
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = GetDate();

      if @SELECTED = 0  /* Not selected, so remove the record source */
        begin
          declare @DUPLICATEID uniqueidentifier;
          declare @CONSTITUENTMARKETINGINFOQUERYVIEWCATALOGID uniqueidentifier;
          declare @REVENUEMARKETINGINFOQUERYVIEWCATALOGID uniqueidentifier;
          declare @SEARCHLISTCATALOGID uniqueidentifier;
          declare @TRANSLATIONFUNCTIONCATALOGID uniqueidentifier;
          declare @LOADSPECLOGID uniqueidentifier;
          declare @TABLENAME nvarchar(128);
          declare @VIEWNAME nvarchar(128);
          declare @TABLECATALOGID uniqueidentifier;


          /* Delete the duplicate criteria */
          select @DUPLICATEID = [ID] 
          from dbo.[MKTDUPLICATE]
          where [QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID;

          exec dbo.[USP_MKTDUPLICATE_DELETEBYID_WITHCHANGEAGENTID] @DUPLICATEID, @CHANGEAGENTID;


          /* Delete the list layouts */
          declare @LISTLAYOUTID uniqueidentifier;
          declare LAYOUTCURSOR cursor local fast_forward for
            select [ID]
            from dbo.[MKTLISTLAYOUT]
            where [RECORDSOURCEID] = @QUERYVIEWCATALOGID;

          open LAYOUTCURSOR;
          fetch next from LAYOUTCURSOR into @LISTLAYOUTID;

          while (@@FETCH_STATUS = 0)
          begin
            exec dbo.[USP_MKTLISTLAYOUT_DELETEBYID_WITHCHANGEAGENTID] @LISTLAYOUTID, @CHANGEAGENTID;
            fetch next from LAYOUTCURSOR into @LISTLAYOUTID;
          end

          close LAYOUTCURSOR;
          deallocate LAYOUTCURSOR;


          /* Drop the Source Analysis data */
          declare @SOURCEANALYSISRULEID uniqueidentifier;

          select @SOURCEANALYSISRULEID = [ID] 
          from dbo.[MKTSOURCEANALYSISRULES] 
          where [RECORDSOURCEID] = @QUERYVIEWCATALOGID;

          exec dbo.[USP_MKTSOURCEANALYSISRULES_DELETEBYID_WITHCHANGEAGENTID] @SOURCEANALYSISRULEID, @CHANGEAGENTID;

          set @TABLENAME = dbo.[UFN_MKTSOURCEANALYSISRULEEXCLUSION_MAKETABLENAME](@QUERYVIEWCATALOGID);
          if exists(select 1 from sys.tables where name = @TABLENAME)
            exec ('drop table dbo.[' + @TABLENAME + ']');

          set @TABLENAME = dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@QUERYVIEWCATALOGID);
          if exists(select 1 from sys.tables where name = @TABLENAME)
            exec ('drop table dbo.[' + @TABLENAME + ']');


          /* Delete any smart fields tied to this record source */
          declare @SMARTFIELDID uniqueidentifier;
          declare @SMARTFIELDCATALOGID uniqueidentifier;

          declare SMARTFIELDCURSOR cursor local fast_forward for
            select
              [SMARTFIELD].[ID] as [SMARTFIELDID],
              [SMARTFIELDCATALOG].[ID] as [SMARTFIELDCATALOGID],
              [SMARTFIELD].[TABLECATALOGID]
            from dbo.[SMARTFIELDCATALOG]
            inner join dbo.[RECORDTYPE] on [SMARTFIELDCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID]
            inner join dbo.[QUERYVIEWCATALOG] on [RECORDTYPE].[ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
            inner join dbo.[MKTRECORDSOURCE] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
            left outer join dbo.[SMARTFIELD] on [SMARTFIELDCATALOG].[ID] = [SMARTFIELD].[SMARTFIELDCATALOGID]
            where [MKTRECORDSOURCE].[ID] = @QUERYVIEWCATALOGID;

          open SMARTFIELDCURSOR;
          fetch next from SMARTFIELDCURSOR into @SMARTFIELDID, @SMARTFIELDCATALOGID, @TABLECATALOGID;

          while @@FETCH_STATUS = 0
       begin
            if @SMARTFIELDID is not null
              begin
                exec dbo.[USP_SMARTFIELD_DELETE] @SMARTFIELDID, @CHANGEAGENTID;
                exec dbo.[USP_SMARTFIELD_DELETEBYID_WITHCHANGEAGENTID] @SMARTFIELDID;
              end

            exec dbo.[USP_SMARTFIELDCATALOG_DELETEBYID_WITHCHANGEAGENTID] @SMARTFIELDCATALOGID;
            if @TABLECATALOGID is not null
              exec dbo.[USP_TABLECATALOG_DELETEBYID_WITHCHANGEAGENTID] @TABLECATALOGID, @CHANGEAGENTID;

            fetch next from SMARTFIELDCURSOR into @SMARTFIELDID, @SMARTFIELDCATALOGID, @TABLECATALOGID;
          end

          close SMARTFIELDCURSOR;
          deallocate SMARTFIELDCURSOR;


          /* Delete the record source, then we need to drop the code generated appeal search screen and translation function. */
          /* Since these are both CLR implementations, we don't need to worry about dropping any stored procedures. */
          /* Also need to delete the code generated marketing information query view specs and drop the views for each too. */
          select
            @CONSTITUENTMARKETINGINFOQUERYVIEWCATALOGID = [MKTRECORDSOURCE].[MARKETINGINFOQUERYVIEWCATALOGID],
            @REVENUEMARKETINGINFOQUERYVIEWCATALOGID = [MKTGIFTRECORDSOURCE].[MARKETINGINFOQUERYVIEWCATALOGID],
            @SEARCHLISTCATALOGID = [MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID],
            @TRANSLATIONFUNCTIONCATALOGID = [MKTAPPEALRECORDSOURCE].[TRANSLATIONFUNCTIONCATALOGID]
          from dbo.[MKTRECORDSOURCE]
          left join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [MKTRECORDSOURCE].[ID]
          left join dbo.[MKTAPPEALRECORDSOURCE] on [MKTAPPEALRECORDSOURCE].[ID] = [MKTRECORDSOURCE].[ID]
          where [MKTRECORDSOURCE].[ID] = @QUERYVIEWCATALOGID;

          exec dbo.[USP_MKTRECORDSOURCE_DELETEBYID_WITHCHANGEAGENTID] @QUERYVIEWCATALOGID, @CHANGEAGENTID;

          if @CONSTITUENTMARKETINGINFOQUERYVIEWCATALOGID is not null
            begin
              select
                @VIEWNAME = [OBJECTNAME],
                @LOADSPECLOGID = [LOADSPECLOG].[ID]
              from dbo.[QUERYVIEWCATALOG]
              inner join dbo.[LOADSPECLOG] on [LOADSPECLOG].[SPECID] = [QUERYVIEWCATALOG].[ID]
              where [QUERYVIEWCATALOG].[ID] = @CONSTITUENTMARKETINGINFOQUERYVIEWCATALOGID;

              --Delete the query view catalog item...

              exec dbo.[USP_QUERYVIEW_DELETEADDEDBYRELATIONSHIPS] @CONSTITUENTMARKETINGINFOQUERYVIEWCATALOGID, @CHANGEAGENTID;
              exec dbo.[USP_QUERYVIEWCATALOG_DELETEBYID_WITHCHANGEAGENTID] @CONSTITUENTMARKETINGINFOQUERYVIEWCATALOGID, @CHANGEAGENTID;
              exec dbo.[USP_LOADSPECLOG_DELETEBYID_WITHCHANGEAGENTID] @LOADSPECLOGID, @CHANGEAGENTID;

              --Drop the view

              exec ('drop view dbo.[' + @VIEWNAME + ']');
            end

          if @REVENUEMARKETINGINFOQUERYVIEWCATALOGID is not null
            begin
              select
                @VIEWNAME = [OBJECTNAME],
                @LOADSPECLOGID = [LOADSPECLOG].[ID]
              from dbo.[QUERYVIEWCATALOG]
              inner join dbo.[LOADSPECLOG] on [LOADSPECLOG].[SPECID] = [QUERYVIEWCATALOG].[ID]
              where [QUERYVIEWCATALOG].[ID] = @REVENUEMARKETINGINFOQUERYVIEWCATALOGID;

              --Delete the query view catalog item...

              exec dbo.[USP_QUERYVIEW_DELETEADDEDBYRELATIONSHIPS] @REVENUEMARKETINGINFOQUERYVIEWCATALOGID, @CHANGEAGENTID;
              exec dbo.[USP_QUERYVIEWCATALOG_DELETEBYID_WITHCHANGEAGENTID] @REVENUEMARKETINGINFOQUERYVIEWCATALOGID, @CHANGEAGENTID;
              exec dbo.[USP_LOADSPECLOG_DELETEBYID_WITHCHANGEAGENTID] @LOADSPECLOGID, @CHANGEAGENTID;

              --Drop the view

              exec ('drop view dbo.[' + @VIEWNAME + ']');
            end

          if @SEARCHLISTCATALOGID is not null
            begin
              select @LOADSPECLOGID = [ID]
              from dbo.[LOADSPECLOG]
              where [SPECID] = @SEARCHLISTCATALOGID;

              exec dbo.[USP_SEARCHLISTCATALOG_DELETEBYID_WITHCHANGEAGENTID] @SEARCHLISTCATALOGID, @CHANGEAGENTID;
              exec dbo.[USP_LOADSPECLOG_DELETEBYID_WITHCHANGEAGENTID] @LOADSPECLOGID, @CHANGEAGENTID;
            end

          if @TRANSLATIONFUNCTIONCATALOGID is not null
            begin
              select @LOADSPECLOGID = [ID]
              from dbo.[LOADSPECLOG]
              where [SPECID] = @TRANSLATIONFUNCTIONCATALOGID;

              exec dbo.[USP_TRANSLATIONFUNCTIONCATALOG_DELETEBYID_WITHCHANGEAGENTID] @TRANSLATIONFUNCTIONCATALOGID, @CHANGEAGENTID;
              exec dbo.[USP_LOADSPECLOG_DELETEBYID_WITHCHANGEAGENTID] @LOADSPECLOGID, @CHANGEAGENTID;
            end

          /* Drop the REVENUESEGMENT and CONSTITUENTSEGMENT tables for this record source */
          set @TABLENAME = dbo.[UFN_REVENUESEGMENT_MAKETABLENAME](@QUERYVIEWCATALOGID);
          if exists(select 1 from sys.tables where name = @TABLENAME)
            exec ('drop table dbo.[' + @TABLENAME + ']');

          set @TABLENAME = dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME](@QUERYVIEWCATALOGID);
          if exists(select 1 from sys.tables where name = @TABLENAME)
            exec ('drop table dbo.[' + @TABLENAME + ']');

          /* Make sure we don't drop BBEC's tables... */
          if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@QUERYVIEWCATALOGID) = 0
            begin
              /* Drop the MKTREVENUELETTER and MKTREVENUERECEIPT tables for this record source and update the views */
              set @TABLENAME = dbo.[UFN_MKTREVENUELETTER_MAKETABLENAME](@QUERYVIEWCATALOGID);
              if exists(select 1 from sys.tables where name = @TABLENAME)
                begin
                  select
                    @TABLECATALOGID = [ID]
                  from dbo.[TABLECATALOG]
                  where [TABLENAME] = @TABLENAME;

                  exec ('drop table dbo.[' + @TABLENAME + ']');
                  exec dbo.[USP_TABLECATALOG_DELETEBYID_WITHCHANGEAGENTID] @TABLECATALOGID, @CHANGEAGENTID;
                end

              set @TABLENAME = dbo.[UFN_MKTREVENUERECEIPT_MAKETABLENAME](@QUERYVIEWCATALOGID);
              if exists(select 1 from sys.tables where name = @TABLENAME)
                begin
                  select
                    @TABLECATALOGID = [ID]
                  from dbo.[TABLECATALOG]
                  where [TABLENAME] = @TABLENAME;

                  exec ('drop table dbo.[' + @TABLENAME + ']');
                  exec dbo.[USP_TABLECATALOG_DELETEBYID_WITHCHANGEAGENTID] @TABLECATALOGID, @CHANGEAGENTID;
                end
            end

          exec dbo.[USP_MKTREVENUELETTER_CREATEORUPDATEVIEWS];
          exec dbo.[USP_MKTREVENUERECEIPT_CREATEORUPDATEVIEWS];
        end
      else
        begin
          if not exists(select [ID] from dbo.[MKTRECORDSOURCE] where [ID] = @QUERYVIEWCATALOGID)
            begin
              /* Selected, but not in the table yet */
              insert into dbo.[MKTRECORDSOURCE] (
                [ID],
                [MARKETINGINFOQUERYVIEWCATALOGID],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED]
              ) values (
                @QUERYVIEWCATALOGID,
                @MARKETINGINFOQUERYVIEWCATALOGID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
              );

              /* create source analysis rule */
              declare @RULEID uniqueidentifier;
              set @RULEID = NewID();
              insert dbo.[MKTSOURCEANALYSISRULES] (
                [ID],
                [RECORDSOURCEID], 
                [ADDEDBYID], 
                [CHANGEDBYID]
              ) values (
                @RULEID,
                @QUERYVIEWCATALOGID,
   @CHANGEAGENTID,
                @CHANGEAGENTID
              );

              /* Create the source analysis rule data table for this record source */
              exec dbo.[USP_MKTSOURCEANALYSISRULE_CREATEDATATABLE] @QUERYVIEWCATALOGID;
            end
        end

  end try

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

  return 0;