USP_DATAFORMTEMPLATE_PROSPECTREPLACEPROSPECTMANAGER_EDITSAVE

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_PROSPECTREPLACEPROSPECTMANAGER_EDITSAVE 
          (
            @ID uniqueidentifier,
            @PREVIOUSMANAGERID uniqueidentifier,
            @PREVIOUSMANAGERSTARTDATE datetime,
            @PREVIOUSMANAGERENDDATE datetime,
            @NEWMANAGERID uniqueidentifier,
            @NEWMANAGERSTARTDATE datetime,
            @NEWMANAGERENDDATE datetime,
            @CHANGEAGENTID uniqueidentifier = null
          ) as
            set nocount on;

            declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

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

            begin try
              declare @ORIGINALPREVIOUSMANAGERID uniqueidentifier;
              declare @ORIGINALPREVIOUSMANAGERSTARTDATE datetime;
              declare @ORIGINALPREVIOUSMANAGERENDDATE datetime;

              /*
                If replacing prospect manager, insert the old record into the history table.
                Also, clear the record out to prevent constraint violations.
              */

              select 
                @ORIGINALPREVIOUSMANAGERID = PROSPECTMANAGERFUNDRAISERID,
                @ORIGINALPREVIOUSMANAGERSTARTDATE = PROSPECTMANAGERSTARTDATE,
                @ORIGINALPREVIOUSMANAGERENDDATE = PROSPECTMANAGERENDDATE
              from dbo.PROSPECT
              where ID = @ID;

              if @PREVIOUSMANAGERID is not null
              begin
                update dbo.PROSPECT set 
                  PROSPECTMANAGERFUNDRAISERID = null,
                  PROSPECTMANAGERSTARTDATE = null,
                  PROSPECTMANAGERENDDATE = null,
                  DATECHANGED  = @CURRENTDATE,
                  CHANGEDBYID = @CHANGEAGENTID
                where ID = @ID;

                declare @MANAGERHISTORYID uniqueidentifier;
                declare @HISTORYFUNDRAISERID uniqueidentifier;
                declare @HISTORYDATEFROM datetime;
                declare @HISTORYDATETO datetime;

                select top 1
                  @MANAGERHISTORYID = ID,
                  @HISTORYFUNDRAISERID = FUNDRAISERID,
                  @HISTORYDATEFROM = DATEFROM,
                  @HISTORYDATETO = DATETO
                from dbo.PROSPECTMANAGERHISTORY 
                where PROSPECTID = @ID 
                order by DATETO desc;

                declare @RANGESOVERLAP bit = dbo.UFN_DATES_AREDATESOVERLAPPING(@PREVIOUSMANAGERSTARTDATE, @PREVIOUSMANAGERENDDATE, @HISTORYDATEFROM, @HISTORYDATETO);

                --Insert in history only when such record is not already there or when the dates don't overlap

                if
                (
                  (
                    @MANAGERHISTORYID is null or
                    @HISTORYFUNDRAISERID <> @ORIGINALPREVIOUSMANAGERID
                  ) or
                  (
                    @HISTORYFUNDRAISERID = @PREVIOUSMANAGERID and
                    @RANGESOVERLAP = 0
                  )
                )
                begin
                  insert into dbo.PROSPECTMANAGERHISTORY
                  (
                    ID,
                    PROSPECTID,
                    FUNDRAISERID,
                    DATEFROM,
                    DATETO,
                    DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID
                  )
                  values
                  (
                    newid(),
                    @ID,
                    @PREVIOUSMANAGERID,
                    @PREVIOUSMANAGERSTARTDATE,
                    @PREVIOUSMANAGERENDDATE,
                    @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
                  )
                end
                -- update the history record if the dates overlap if it is the same fundraiser

                else if
                (
                  @HISTORYFUNDRAISERID = @ORIGINALPREVIOUSMANAGERID and @RANGESOVERLAP = 1
                )
                begin
                  update PROSPECTMANAGERHISTORY set
                    DATEFROM = case when @PREVIOUSMANAGERSTARTDATE > DATEFROM then DATEFROM else @PREVIOUSMANAGERSTARTDATE end,
                    DATETO = @PREVIOUSMANAGERENDDATE,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                  where ID = @MANAGERHISTORYID;
                end
              end

              if @NEWMANAGERID is not null
              begin
                update dbo.PROSPECT set 
                  PROSPECTMANAGERFUNDRAISERID = @NEWMANAGERID,
                  PROSPECTMANAGERSTARTDATE = @NEWMANAGERSTARTDATE,
                  PROSPECTMANAGERENDDATE = @NEWMANAGERENDDATE,
                  DATECHANGED  = @CURRENTDATE,
                  CHANGEDBYID = @CHANGEAGENTID
                where ID = @ID;
              end

              if @NEWMANAGERENDDATE is not null
              begin
                insert into dbo.PROSPECTMANAGERHISTORY
                (
                  ID,
                  PROSPECTID,
                  FUNDRAISERID,
                  DATEFROM,
                  DATETO,
                  DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID
                )
                values
                (
                  newid(),
                  @ID,
                  @NEWMANAGERID,
                  @NEWMANAGERSTARTDATE,
                  @NEWMANAGERENDDATE,
                  @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
                );
              end

              exec dbo.USP_PROSPECTASSIGNEDALERT_SEND @PREVIOUSMANAGERID, @ID;

              update dbo.INTERACTION set
                FUNDRAISERID = @PREVIOUSMANAGERID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
              from dbo.INTERACTION I
                inner join dbo.PROSPECTPLAN PP on PP.ID = I.PROSPECTPLANID
                left outer join dbo.PLANOUTLINESTEP SI on SI.ID=I.PLANOUTLINESTEPID
              where
                I.FUNDRAISERID is null
                and SI.FUNDRAISERROLECODE = 0
                and PP.PROSPECTID = @ID;
            end try
            begin catch
              exec dbo.USP_RAISE_ERROR;
              return 1;
            end catch

            return 0;