USP_DATAFORMTEMPLATE_PROSPECTMANAGER2_EDITSAVE

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_PROSPECTMANAGER2_EDITSAVE (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier = null,
            @PROSPECTMANAGERFUNDRAISERID uniqueidentifier,
            @CURRENTAPPUSERID uniqueidentifier = null,
            @PROSPECTMANAGERSTARTDATE date,
            @PROSPECTMANAGERENDDATE date
          ) as
            set nocount on;

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

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

            begin try
              declare @PREVIOUSMANAGERFUNDRAISERID uniqueidentifier, @PREVIOUSMANAGERENDDATE datetime, @PREVIOUSMANAGERSTARTDATE datetime, @PREVIOUSMANAGERHISTORYID uniqueidentifier;
              if @PROSPECTMANAGERFUNDRAISERID is null
                select 
                  @PROSPECTMANAGERSTARTDATE = null,
                  @PROSPECTMANAGERENDDATE = null

              if exists (select 1 from dbo.PROSPECT where ID = @ID) 
              begin
                --Bug#244263 - Arun Saini - Update latest history record enddate if new manager startdate is greater than that
                -- Get previous manager history so that we can update its end date if its greater than new manager start date
                select top 1 
                  @PREVIOUSMANAGERHISTORYID = ID
                from PROSPECTMANAGERHISTORY 
                where 
                  PROSPECTID =  @ID
                order by 
                  DATETO desc, 
                  DATECHANGED desc

                -- Update history record end date if that is greater than new manager start date
                if(@PREVIOUSMANAGERHISTORYID is not null)
                begin
                  update PROSPECTMANAGERHISTORY
                  set DATETO = @PROSPECTMANAGERSTARTDATE 
                  where 
                    ID = @PREVIOUSMANAGERHISTORYID 
                    and DATETO > @PROSPECTMANAGERSTARTDATE
                end

                select 
                  @PREVIOUSMANAGERFUNDRAISERID = PROSPECTMANAGERFUNDRAISERID,
                  @PREVIOUSMANAGERENDDATE = PROSPECTMANAGERENDDATE,
                  @PREVIOUSMANAGERSTARTDATE = PROSPECTMANAGERSTARTDATE
                from dbo.PROSPECT where ID = @ID;

                update dbo.PROSPECT set
                  PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
                  PROSPECTMANAGERSTARTDATE = @PROSPECTMANAGERSTARTDATE,
                  PROSPECTMANAGERENDDATE = @PROSPECTMANAGERENDDATE,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CURRENTDATE
                where
                  ID = @ID;

                declare @MANAGERHISTORYID as uniqueidentifier, @HISTORYFUNDRAISERID  as uniqueidentifier, @HISTORYFROM as datetime, @HISTORYDATETO as datetime;

                -- Select managerid and history table's id of the latest changed record 
                select top 1 @MANAGERHISTORYID = ID,
                  @HISTORYFUNDRAISERID = FUNDRAISERID,
                  @HISTORYFROM = DATEFROM,
                  @HISTORYDATETO = DATETO
                from PROSPECTMANAGERHISTORY 
                where 
                  PROSPECTID =  @ID 
                order by 
                  DATECHANGED desc

                --Create history for previous manager. 
                --This is required only when previous manager end date was not specified and previous manager start date was not a future date
                if(@PREVIOUSMANAGERFUNDRAISERID <> @PROSPECTMANAGERFUNDRAISERID 
                  and @PREVIOUSMANAGERENDDATE is null 
                  and ( @PREVIOUSMANAGERSTARTDATE is null or dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSMANAGERSTARTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)))
                begin

                  --Calculate previous manager history end date
                  declare @HISTORYENDDATE as datetime = case
                            when @PROSPECTMANAGERSTARTDATE is not null  -- new manager start date is provided
                              then @PROSPECTMANAGERSTARTDATE            -- set it to new manager start date
                            when @PROSPECTMANAGERENDDATE < dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)   -- New manager end date is a past date
                              then @PROSPECTMANAGERENDDATE                                              -- Set it to new manager end date
                            else                                          --For all other cases
                              dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)  -- set it to current date
                            end

                  --Calculate previous manager history start date
                  declare @HISTORYSTARTDATE as datetime = case
                            -- A record already exists in history table and previous manager start date  is not provided
                            when (@MANAGERHISTORYID is not null and @PREVIOUSMANAGERSTARTDATE is null)  
                              then @HISTORYENDDATE  -- We need to set the startdate same as enddate
                            else
                              @PREVIOUSMANAGERSTARTDATE 
                            end

                  insert into dbo.PROSPECTMANAGERHISTORY(ID, PROSPECTID, FUNDRAISERID, DATEFROM, DATETO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                  values(newid(), @ID, @PREVIOUSMANAGERFUNDRAISERID, @HISTORYSTARTDATE,  @HISTORYENDDATE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)

                end

                -- if the current manager's dates overlap with the latest historical manager, delete the historical row and update the start date of the current manager
                if(@PROSPECTMANAGERFUNDRAISERID is not null and @MANAGERHISTORYID is not null and @PROSPECTMANAGERENDDATE is null and @HISTORYFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID and dbo.UFN_DATE_GETEARLIESTTIME(@PROSPECTMANAGERSTARTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@HISTORYDATETO))
                begin
                -- if history is already present for this manager and user has cleared the end date delete history record
                  delete 
                  from PROSPECTMANAGERHISTORY 
                  where ID = @MANAGERHISTORYID

                  --update the prospect with the history's start date to make it a continuous active record
                  update dbo.PROSPECT
                  set PROSPECTMANAGERSTARTDATE = case when @HISTORYFROM is null then @PROSPECTMANAGERSTARTDATE else @HISTORYFROM end
                  where ID = @ID
                end     
                else if(
                    @HISTORYFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID and dbo.UFN_DATE_GETEARLIESTTIME(@PROSPECTMANAGERSTARTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@HISTORYDATETO) and
                    @PROSPECTMANAGERFUNDRAISERID is not null and @MANAGERHISTORYID is not null and 
                    ((@PREVIOUSMANAGERSTARTDATE is null and  @PROSPECTMANAGERSTARTDATE is not null) or
                    (@PREVIOUSMANAGERSTARTDATE is not null and  @PROSPECTMANAGERSTARTDATE is null) or
                    dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSMANAGERENDDATE) <> dbo.UFN_DATE_GETEARLIESTTIME(@PROSPECTMANAGERENDDATE) or
                    dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSMANAGERSTARTDATE) <> dbo.UFN_DATE_GETEARLIESTTIME(@PROSPECTMANAGERSTARTDATE))
                    )
                --if history is already present for this manager and the dates have changed and the start date is the same has the history's end date then update the history.
                begin
                  update PROSPECTMANAGERHISTORY
                  set
        DATEFROM = case when @PROSPECTMANAGERSTARTDATE < DATEFROM then @PROSPECTMANAGERSTARTDATE else DATEFROM end,
                    DATETO = @PROSPECTMANAGERENDDATE,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                  where ID = @MANAGERHISTORYID
                end  
                else if( @PROSPECTMANAGERFUNDRAISERID is not null and (@MANAGERHISTORYID is null or  @HISTORYFUNDRAISERID <> @PROSPECTMANAGERFUNDRAISERID) and @PROSPECTMANAGERENDDATE is not null) 
                begin
                  --Calculate new manager history start date
                  declare @NEWMANAGERHISTORYSTARTDATE as datetime = case
                                                                    -- A record already exists in history table and new manager start date  is not provided
                                                                    when (exists (select 1 from PROSPECTMANAGERHISTORY where PROSPECTID =  @ID) and @PROSPECTMANAGERSTARTDATE is null and @PROSPECTMANAGERENDDATE >=  dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)  )  
                                                                      then dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)  -- We need to set the startdate same as currentdate
                                                                    when (exists (select 1 from PROSPECTMANAGERHISTORY where PROSPECTID =  @ID) and @PROSPECTMANAGERSTARTDATE is null and @PROSPECTMANAGERENDDATE <  dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)  )  
                                                                      then @PROSPECTMANAGERENDDATE
                                                                    else
                                                                      @PROSPECTMANAGERSTARTDATE 
                                                                    end
                  -- create history if history is not already present and end date is supplied
                  insert into dbo.PROSPECTMANAGERHISTORY(ID, PROSPECTID, FUNDRAISERID, DATEFROM, DATETO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                  values(newid(), @ID, @PROSPECTMANAGERFUNDRAISERID, @NEWMANAGERHISTORYSTARTDATE, @PROSPECTMANAGERENDDATE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
                end
              end
            else
            begin
              select 
                @PREVIOUSMANAGERFUNDRAISERID = CONSTITUENTID 
              from dbo.APPUSER 
              where APPUSER.ID = @CURRENTAPPUSERID;

              insert into dbo.PROSPECT
              (
                ID,
                PROSPECTMANAGERFUNDRAISERID,
                PROSPECTMANAGERSTARTDATE,
                PROSPECTMANAGERENDDATE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
              )
              values
              (
                @ID,
                @PROSPECTMANAGERFUNDRAISERID,
                @PROSPECTMANAGERSTARTDATE,
                @PROSPECTMANAGERENDDATE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
              );
            end

            exec dbo.USP_PROSPECTASSIGNEDALERT_SEND @PREVIOUSMANAGERFUNDRAISERID, @ID;

            update dbo.INTERACTION set
              FUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
              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;