USP_DATAFORMTEMPLATE_PROSPECTPLANPRIMARYMANAGER_EDITSAVE

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_PROSPECTPLANPRIMARYMANAGER_EDITSAVE 
          (
            @ID uniqueidentifier,
            @PRIMARYMANAGERID uniqueidentifier,
            @PRIMARYMANAGERSTARTDATE datetime,
            @PRIMARYMANAGERENDDATE 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 @PREVIOUSPRIMARYMANAGERFUNDRAISERID uniqueidentifier, @PREVIOUSPRIMARYMANAGERENDDATE datetime, @PREVIOUSPRIMARYMANAGERSTARTDATE datetime, @PREVIOUSMANAGERHISTORYID uniqueidentifier, @PREVIOUSMANAGERHISTORYDATETO datetime, @PREVIOUSMANAGERHISTORYDATEFROM datetime;

                            /* cache current context information */
                declare @contextCache varbinary(128);
                set @contextCache = CONTEXT_INFO();

                            /* set CONTEXT_INFO to @CHANGEAGENTID */                        
                set CONTEXT_INFO @CHANGEAGENTID

              if @PRIMARYMANAGERID is not null
              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,
                  @PREVIOUSMANAGERHISTORYDATETO = DATETO,
                  @PREVIOUSMANAGERHISTORYDATEFROM = DATEFROM
                from PROSPECTPLANMANAGERHISTORY 
                where 
                  PROSPECTPLANID = @ID
                  and ISPRIMARYMANAGER = 1
                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)
                  update PROSPECTPLANMANAGERHISTORY
                  set DATETO = @PRIMARYMANAGERSTARTDATE 
                  where 
                    ID = @PREVIOUSMANAGERHISTORYID 
                    and DATETO > @PRIMARYMANAGERSTARTDATE

                select 
                  @PREVIOUSPRIMARYMANAGERFUNDRAISERID = PRIMARYMANAGERFUNDRAISERID,
                  @PREVIOUSPRIMARYMANAGERENDDATE = PRIMARYMANAGERENDDATE,
                  @PREVIOUSPRIMARYMANAGERSTARTDATE = PRIMARYMANAGERSTARTDATE
                from dbo.PROSPECTPLAN 
                where ID = @ID;

                update 
                  dbo.PROSPECTPLAN
                set 
                  PRIMARYMANAGERFUNDRAISERID = @PRIMARYMANAGERID,
                  PRIMARYMANAGERSTARTDATE = @PRIMARYMANAGERSTARTDATE,
                  PRIMARYMANAGERENDDATE = @PRIMARYMANAGERENDDATE,
                  DATECHANGED  = @CURRENTDATE,
                  CHANGEDBYID = @CHANGEAGENTID
                where 
                  ID = @ID

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

                select top 1 
                  @MANAGERHISTORYID = ID,
                  @HISTORYFUNDRAISERID = FUNDRAISERID,
                  @HISTORYDATEFROM = DATEFROM,
                  @HISTORYDATETO = DATETO
                from PROSPECTPLANMANAGERHISTORY 
                where 
                  PROSPECTPLANID  =  @ID and ISPRIMARYMANAGER = 1
                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(@PREVIOUSPRIMARYMANAGERFUNDRAISERID <> @PRIMARYMANAGERID 
                  and @PREVIOUSPRIMARYMANAGERENDDATE is null 
                  and ( @PREVIOUSPRIMARYMANAGERSTARTDATE is null or dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSPRIMARYMANAGERSTARTDATE) <= dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)))
                begin
                  --Calculate previous manager history end date
                  declare @HISTORYENDDATE as datetime = case
                      when @PRIMARYMANAGERSTARTDATE is not null  -- new manager start date is provided
                        then @PRIMARYMANAGERSTARTDATE            -- set it to new manager start date
                      when @PRIMARYMANAGERENDDATE < dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)   -- New manager end date is a past date
                        then @PRIMARYMANAGERENDDATE                                              -- 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 @PREVIOUSPRIMARYMANAGERSTARTDATE is null)  
                          then @HISTORYENDDATE  -- We need to set the startdate same as enddate
                        else @PREVIOUSPRIMARYMANAGERSTARTDATE 
                        end

                  insert into dbo.PROSPECTPLANMANAGERHISTORY(ID, PROSPECTPLANID, FUNDRAISERID,ISPRIMARYMANAGER, DATEFROM, DATETO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                  values(newid(), @ID, @PREVIOUSPRIMARYMANAGERFUNDRAISERID,1, @HISTORYSTARTDATE, @HISTORYENDDATE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)

                end


                if(@PRIMARYMANAGERID is not null and @MANAGERHISTORYID is not null and @PRIMARYMANAGERENDDATE is null and @HISTORYFUNDRAISERID = @PRIMARYMANAGERID and dbo.UFN_DATE_GETEARLIESTTIME(@PRIMARYMANAGERSTARTDATE) = 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 PROSPECTPLANMANAGERHISTORY 
                  where ID = @MANAGERHISTORYID

                  --update the prospect with the history's start date to make it a continuous active record
                  update dbo.PROSPECTPLAN
                  set PRIMARYMANAGERSTARTDATE = @HISTORYDATEFROM
                  where ID = @ID
                end     
                else if( @HISTORYFUNDRAISERID = @PRIMARYMANAGERID and dbo.UFN_DATE_GETEARLIESTTIME(@PRIMARYMANAGERSTARTDATE) = dbo.UFN_DATE_GETEARLIESTTIME(@HISTORYDATETO) and
                    @PRIMARYMANAGERID is not null and @MANAGERHISTORYID is not null and 
                    ((@PREVIOUSPRIMARYMANAGERSTARTDATE is null and  @PRIMARYMANAGERSTARTDATE is not null) or
                    (@PREVIOUSPRIMARYMANAGERSTARTDATE is not null and  @PRIMARYMANAGERSTARTDATE is null) or
                    dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSPRIMARYMANAGERENDDATE) <> dbo.UFN_DATE_GETEARLIESTTIME(@PRIMARYMANAGERENDDATE) or
                    dbo.UFN_DATE_GETEARLIESTTIME(@PREVIOUSPRIMARYMANAGERSTARTDATE) <> dbo.UFN_DATE_GETEARLIESTTIME(@PRIMARYMANAGERSTARTDATE))
                    )
                --if history is already present for this manager and end date or start date has changed and the start date overlaps with the history's end date then update the history . we don't require endates to check for null as no update is required for those
                begin
                  update PROSPECTPLANMANAGERHISTORY
                  set
                    DATEFROM = case when @PRIMARYMANAGERSTARTDATE < DATEFROM then @PRIMARYMANAGERSTARTDATE else DATEFROM end,
                    DATETO = @PRIMARYMANAGERENDDATE,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                  where ID = @MANAGERHISTORYID
                end  
                else if( @PRIMARYMANAGERID is not null and (@MANAGERHISTORYID is null or  @HISTORYFUNDRAISERID <> @PRIMARYMANAGERID) and @PRIMARYMANAGERENDDATE 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 PROSPECTPLANMANAGERHISTORY where PROSPECTPLANID =  @ID and ISPRIMARYMANAGER = 1) and @PRIMARYMANAGERSTARTDATE is null and @PRIMARYMANAGERENDDATE >=  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 PROSPECTPLANMANAGERHISTORY where PROSPECTPLANID =  @ID and ISPRIMARYMANAGER = 1) and @PRIMARYMANAGERSTARTDATE is null and @PRIMARYMANAGERENDDATE <  dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)  )  
                              then @PRIMARYMANAGERENDDATE
                            else @PRIMARYMANAGERSTARTDATE 
                            end

                  -- create history if history is not already present and end date is supplied
                  insert into dbo.PROSPECTPLANMANAGERHISTORY(ID, PROSPECTPLANID, FUNDRAISERID,ISPRIMARYMANAGER, DATEFROM, DATETO, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                  values(newid(), @ID, @PRIMARYMANAGERID,1, @NEWMANAGERHISTORYSTARTDATE, @PRIMARYMANAGERENDDATE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
                end                             
              end              
              else
              begin
                update 
                  dbo.PROSPECTPLAN
                set 
                  PRIMARYMANAGERFUNDRAISERID = null,
                  PRIMARYMANAGERSTARTDATE = null,
                  PRIMARYMANAGERENDDATE = null,
                  DATECHANGED  = @CURRENTDATE,
                  CHANGEDBYID = @CHANGEAGENTID
                where 
                  ID = @ID
              end

              if (@PREVIOUSPRIMARYMANAGERFUNDRAISERID <> @PRIMARYMANAGERID) or (@PRIMARYMANAGERID is null)
                  delete 
                      dbo.OPPORTUNITYSOLICITOR
                  from
                      dbo.OPPORTUNITYSOLICITOR
                      inner join dbo.OPPORTUNITY on OPPORTUNITYSOLICITOR.OPPORTUNITYID = OPPORTUNITY.ID
                      inner join dbo.PROSPECTPLAN on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
                  where
                      PROSPECTPLAN.ID = @ID
                      and FUNDRAISERTYPECODE = 0

              declare @PREVIOUSPRIMARYFUNDRAISERID uniqueidentifier;
              declare @PREVIOUSSECONDARYFUNDRAISERID uniqueidentifier; 

              select 
                @PREVIOUSPRIMARYFUNDRAISERID = PRIMARYMANAGERFUNDRAISERID,
                @PREVIOUSSECONDARYFUNDRAISERID = SECONDARYMANAGERFUNDRAISERID
              from
                dbo.PROSPECTPLAN
              where
                ID = @ID;

              exec dbo.USP_PROSPECTPLANASSIGNEDALERT_SEND @PREVIOUSPRIMARYFUNDRAISERID, @PREVIOUSSECONDARYFUNDRAISERID, @ID;

              update dbo.INTERACTION set
                FUNDRAISERID = @PRIMARYMANAGERID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
              from
                dbo.PLANOUTLINESTEP SI
              where
                SI.ID=PLANOUTLINESTEPID
                and SI.FUNDRAISERROLECODE = 1
                and FUNDRAISERID is null
                and PROSPECTPLANID = @ID;

                            /* reset CONTEXT_INFO to previous value */
                if not @contextCache is null
                  set CONTEXT_INFO @contextCache

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

            return 0;