USP_CONSTITUENTUPDATEBATCH_APPLYPHONERULES

CREATE procedure dbo.USP_CONSTITUENTUPDATEBATCH_APPLYPHONERULES
(
  @ID uniqueidentifier, -- CUB row ID

  @PRIMARYRECORDID uniqueidentifier, -- Existing Constituent ID

  @CHANGEAGENTID uniqueidentifier,
  @ADDALLPHONES bit = 1,
  @DIFFERENTPHONECODE tinyint = 3,
  @NEWPHONEPRIMARYCODE tinyint = 1,
  @EXCLUDEPHONEID uniqueidentifier = null --this is used by manual reconciliation to not include in the cursor the incoming phone shown on the resolution screen;  

)
as
begin
  set nocount on;

  declare @INCOMINGPHONEROWID uniqueidentifier;
  declare @INCOMINGPHONETYPECODEID uniqueidentifier;
  declare @INCOMINGPHONENUMBER nvarchar(100); 
  declare @INCOMINGISPRIMARY bit;
  -- secondary fields

  declare @INCOMINGDONOTCALL bit;
  declare @INCOMINGCOUNTRYID uniqueidentifier;
  declare @INCOMINGSEASONALSTARTDATE dbo.UDT_MONTHDAY;
  declare @INCOMINGSEASONALENDDATE dbo.UDT_MONTHDAY;
  declare @INCOMINGSTARTTIME dbo.UDT_HOURMINUTE;
  declare @INCOMINGENDTIME dbo.UDT_HOURMINUTE;
  declare @INCOMINGSTARTDATE date;
  declare @INCOMINGENDDATE date;
  declare @INCOMINGINFOSOURCECODEID uniqueidentifier;

  declare @MAKENEWPRIMARYROWID uniqueidentifier;
  declare @EXISTINGPRIMARYPHONETYPECODEID uniqueidentifier;
  declare @EXISTINGPRIMARYPHONEID uniqueidentifier;

  declare @EXISTINGPHONEID uniqueidentifier;
  declare @EXISTINGPHONETYPECODEID uniqueidentifier;
  declare @EXISTINGISPRIMARY bit;
  declare @EXISTINGDONOTCALL bit;
  declare @EXISTINGCOUNTRYID uniqueidentifier;
  declare @EXISTINGSEASONALSTARTDATE dbo.UDT_MONTHDAY;
  declare @EXISTINGSEASONALENDDATE dbo.UDT_MONTHDAY;
  declare @EXISTINGSTARTTIME dbo.UDT_HOURMINUTE;
  declare @EXISTINGENDTIME dbo.UDT_HOURMINUTE;
  declare @EXISTINGSTARTDATE date;
  declare @EXISTINGENDDATE date;
  declare @EXISTINGINFOSOURCECODEID uniqueidentifier;
  declare @PRIMARYCOUNT int = dbo.UFN_BATCHCONSTITUENTUPDATEPHONES_PRIMARYCOUNT(@ID);  
  declare @UPDATESECONDARYDATA bit = 0;  
  declare @CURRENTDATE datetime = getdate();
  declare @NULLVALUE uniqueidentifier = newid();   

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

  --Get the existing is primary row

  select top 1
    @EXISTINGPRIMARYPHONEID = ID,
    @EXISTINGPRIMARYPHONETYPECODEID = case when ISPRIMARY = 1 then PHONETYPECODEID else null end
    from dbo.PHONE where CONSTITUENTID = @PRIMARYRECORDID 
    order by ISPRIMARY desc;

  if @EXISTINGPRIMARYPHONEID is not null -- only apply the rules if the constituent has at least one phone

    begin    
      declare INCOMINGPHONE_CURSOR cursor local fast_forward for
        select 
        ID,
        PHONETYPECODEID,
        NUMBER,
        ISPRIMARY,
        DONOTCALL,
        COUNTRYID,
        SEASONALSTARTDATE,
        SEASONALENDDATE,
        STARTTIME,
        ENDTIME,
        STARTDATE,
        ENDDATE,
        INFOSOURCECODEID
      from dbo.BATCHCONSTITUENTUPDATEPHONES
      where BATCHCONSTITUENTUPDATEID = @ID and PHONEID is null and BATCHCONSTITUENTUPDATEPHONES.ID <> coalesce(@EXCLUDEPHONEID, @NULLVALUE); -- exclude from the cursor the ID coming from the resolutions screen


      open INCOMINGPHONE_CURSOR;

      begin try
        fetch next from INCOMINGPHONE_CURSOR into
            @INCOMINGPHONEROWID,
            @INCOMINGPHONETYPECODEID,
            @INCOMINGPHONENUMBER,
            @INCOMINGISPRIMARY,
            @INCOMINGDONOTCALL,
            @INCOMINGCOUNTRYID,
            @INCOMINGSEASONALSTARTDATE,
            @INCOMINGSEASONALENDDATE,
            @INCOMINGSTARTTIME, 
            @INCOMINGENDTIME,
            @INCOMINGSTARTDATE, 
            @INCOMINGENDDATE, 
            @INCOMINGINFOSOURCECODEID;      

          while (@@FETCH_STATUS = 0)
          begin

          set @EXISTINGPHONEID = null;

          select top 1 @EXISTINGPHONEID = ID,
                 @EXISTINGPHONETYPECODEID = PHONETYPECODEID,
                 @EXISTINGISPRIMARY = ISPRIMARY,
                 @EXISTINGDONOTCALL = DONOTCALL,
                 @EXISTINGCOUNTRYID = COUNTRYID,
                 @EXISTINGSEASONALSTARTDATE = SEASONALSTARTDATE,
                 @EXISTINGSEASONALENDDATE = SEASONALENDDATE,
                 @EXISTINGSTARTTIME = STARTTIME,
                 @EXISTINGENDTIME = ENDTIME,
                 @EXISTINGSTARTDATE = STARTDATE,
                 @EXISTINGENDDATE = ENDDATE,
                 @EXISTINGINFOSOURCECODEID = INFOSOURCECODEID      
          from dbo.PHONE P
          where P.CONSTITUENTID = @PRIMARYRECORDID
          and P.NUMBERNOFORMAT = dbo.UFN_PHONE_REMOVEFORMATTING(@INCOMINGPHONENUMBER)
          and (P.PHONETYPECODEID is null or @INCOMINGPHONETYPECODEID is null or P.PHONETYPECODEID = @INCOMINGPHONETYPECODEID)
          and not exists(select PHONEID from dbo.BATCHCONSTITUENTUPDATEPHONES where BATCHCONSTITUENTUPDATEID = @ID and PHONEID = P.ID)
          order by case when P.PHONETYPECODEID = @INCOMINGPHONETYPECODEID then 1 else 2 end, ISPRIMARY desc, ENDDATE, DONOTCALL, DATECHANGED desc;

          if @EXISTINGPHONEID is not null -- incoming phone is identical to existing phone     

            begin
              if @DIFFERENTPHONECODE <> 0
                set @UPDATESECONDARYDATA = 1                
              else if not(@INCOMINGDONOTCALL=1 and @EXISTINGDONOTCALL=0) and
                  (@EXISTINGCOUNTRYID is null or @EXISTINGCOUNTRYID = @INCOMINGCOUNTRYID or @INCOMINGCOUNTRYID is null) and
                  (@EXISTINGSEASONALSTARTDATE = '0000' or @EXISTINGSEASONALSTARTDATE = @INCOMINGSEASONALSTARTDATE or @INCOMINGSEASONALSTARTDATE  = '0000') and
                  (@EXISTINGSEASONALENDDATE = '0000' or @EXISTINGSEASONALENDDATE = @INCOMINGSEASONALENDDATE or @INCOMINGSEASONALENDDATE = '0000') and
                  (@EXISTINGSTARTTIME = '0000' or @EXISTINGSTARTTIME = @INCOMINGSTARTTIME or @INCOMINGSTARTTIME  = '0000') and
                  (@EXISTINGENDTIME = '0000' or @EXISTINGENDTIME = @INCOMINGENDTIME or @INCOMINGENDTIME = '0000') and
                  (@EXISTINGSTARTDATE is null or @EXISTINGSTARTDATE = @INCOMINGSTARTDATE or @INCOMINGSTARTDATE is null) and
                  (@EXISTINGENDDATE is null or @EXISTINGENDDATE = @INCOMINGENDDATE) and                         
                  (@EXISTINGINFOSOURCECODEID is null or @EXISTINGINFOSOURCECODEID = @INCOMINGINFOSOURCECODEID or @INCOMINGINFOSOURCECODEID is null)
                set @UPDATESECONDARYDATA = 1
              else
                set @UPDATESECONDARYDATA = 0

              if @UPDATESECONDARYDATA = 1  --Update secondary fields  

                begin             
                  update dbo.BATCHCONSTITUENTUPDATEPHONES set
                    PHONEID = @EXISTINGPHONEID,    
                    ISPRIMARY = case when @PRIMARYCOUNT = 1 then ISPRIMARY else @EXISTINGISPRIMARY end,  -- set isprimary only when no primary exists already

                    PHONETYPECODEID = coalesce(PHONETYPECODEID, @EXISTINGPHONETYPECODEID),
                    COUNTRYID = coalesce(COUNTRYID,@EXISTINGCOUNTRYID),
                    SEASONALSTARTDATE = coalesce(NULLIF(SEASONALSTARTDATE,'0000'),@EXISTINGSEASONALSTARTDATE),
                    SEASONALENDDATE = coalesce(NULLIF(SEASONALENDDATE,'0000'),@EXISTINGSEASONALENDDATE),
                    STARTTIME = coalesce(NULLIF(STARTTIME,'0000'),@EXISTINGSTARTTIME),
                    ENDTIME = coalesce(NULLIF(ENDTIME,'0000'),@EXISTINGENDTIME),
                    STARTDATE = coalesce(STARTDATE,@EXISTINGSTARTDATE),
                    --WI # 481812.  If the incoming end date is blank, we want to reactivate the record on an update.

                    --ENDDATE = coalesce(ENDDATE,@EXISTINGENDDATE),

                    --DONOTCALL = coalesce(NULLIF(DONOTCALL,0),@EXISTINGDONOTCALL),

                    INFOSOURCECODEID = coalesce(INFOSOURCECODEID,@EXISTINGINFOSOURCECODEID),
            CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE                
                    where ID = @INCOMINGPHONEROWID; 
                end 
              else -- ignore secondary fields

                begin
                  update dbo.BATCHCONSTITUENTUPDATEPHONES set
                    PHONEID = @EXISTINGPHONEID,  
                    PHONETYPECODEID = @EXISTINGPHONETYPECODEID,
                    ISPRIMARY = case when @PRIMARYCOUNT = 1 then ISPRIMARY else @EXISTINGISPRIMARY end, 
                    DONOTCALL = @EXISTINGDONOTCALL,
                    COUNTRYID = @EXISTINGCOUNTRYID,
                    SEASONALSTARTDATE = @EXISTINGSEASONALSTARTDATE,
                    SEASONALENDDATE = @EXISTINGSEASONALENDDATE,
                    STARTTIME = @EXISTINGSTARTTIME,
                    ENDTIME = @EXISTINGENDTIME,
                    STARTDATE = @EXISTINGSTARTDATE, 
                    ENDDATE = @EXISTINGENDDATE,
                    INFOSOURCECODEID = @EXISTINGINFOSOURCECODEID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE                
                  where ID = @INCOMINGPHONEROWID;         
                end              
            end            
          else  -- Incoming PHONE is not similar

            begin
              if @DIFFERENTPHONECODE = 0 -- Ignore

                delete from dbo.BATCHCONSTITUENTUPDATEPHONES 
                where ID = @INCOMINGPHONEROWID;  
              else if @DIFFERENTPHONECODE = 3 -- Add

              begin
                if (@INCOMINGISPRIMARY = 1 or (@MAKENEWPRIMARYROWID is null and (@NEWPHONEPRIMARYCODE = 2 or (@NEWPHONEPRIMARYCODE = 1 and @INCOMINGPHONETYPECODEID = @EXISTINGPRIMARYPHONETYPECODEID))))                                       
                    and @INCOMINGENDDATE is null and @INCOMINGDONOTCALL = 0 -- Cannot make phone numbers with end dates primary under any condition

                      --Always make new phone primary/Old phone has the same type as the new phone

                      set @MAKENEWPRIMARYROWID = @INCOMINGPHONEROWID;                                     
              end    
            end

        fetch next from INCOMINGPHONE_CURSOR into
          @INCOMINGPHONEROWID,
          @INCOMINGPHONETYPECODEID, 
          @INCOMINGPHONENUMBER, 
          @INCOMINGISPRIMARY,
          @INCOMINGDONOTCALL,
          @INCOMINGCOUNTRYID,
          @INCOMINGSEASONALSTARTDATE,
          @INCOMINGSEASONALENDDATE,
          @INCOMINGSTARTTIME, 
          @INCOMINGENDTIME,
          @INCOMINGSTARTDATE, 
          @INCOMINGENDDATE, 
          @INCOMINGINFOSOURCECODEID;         
        end

        close INCOMINGPHONE_CURSOR;
        deallocate INCOMINGPHONE_CURSOR;
      end try
      begin catch

      close INCOMINGPHONE_CURSOR;
      deallocate INCOMINGPHONE_CURSOR;
      exec dbo.USP_RAISE_ERROR;
      end catch

      -- set all potential primary phones to non-primary, except for the MAKENEWPRIMARYROWID phone     

      if @MAKENEWPRIMARYROWID is not null 
      begin
          update dbo.BATCHCONSTITUENTUPDATEPHONES
          set ISPRIMARY = 0
          where ID <> @MAKENEWPRIMARYROWID and ISPRIMARY = 1
          and BATCHCONSTITUENTUPDATEID = @ID;
      end

      if @ADDALLPHONES = 1 -- Add all other phones for the selected constituent

      begin
        insert into dbo.BATCHCONSTITUENTUPDATEPHONES(
            ID,        
            BATCHCONSTITUENTUPDATEID,
            SEQUENCE,
            PHONETYPECODEID,
            NUMBER,
            ISPRIMARY,
            DONOTCALL,
            UPDATEHOUSEHOLD,
            ADDEDBYID,
            CHANGEDBYID,
            COUNTRYID,
            SEASONALSTARTDATE,
            SEASONALENDDATE,
            STARTTIME,
            ENDTIME,
            STARTDATE,
            ENDDATE,
            INFOSOURCECODEID,
            PHONEID,
   ROWORIGINCODE
        )
        select
            newid(),        
            @ID,
            P.SEQUENCE,
            P.PHONETYPECODEID,
            P.NUMBER,
            case when exists(select ISPRIMARY from dbo.BATCHCONSTITUENTUPDATEPHONES where BATCHCONSTITUENTUPDATEID = @ID and ISPRIMARY = 1) or @MAKENEWPRIMARYROWID is not null then 0 else ISPRIMARY end,
            P.DONOTCALL,
            cast(0 as bit) as UPDATEHOUSEHOLD,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            P.COUNTRYID,
            P.SEASONALSTARTDATE,
            P.SEASONALENDDATE,
            P.STARTTIME,
            P.ENDTIME,    
            P.STARTDATE,
            P.ENDDATE,
            P.INFOSOURCECODEID,
            P.ID as PHONEID,
            2 -- Existing record, automatically populated by the system during import

        from dbo.PHONE P
        where P.CONSTITUENTID = @PRIMARYRECORDID and P.ID not in (select PHONEID from dbo.BATCHCONSTITUENTUPDATEPHONES where BATCHCONSTITUENTUPDATEID = @ID and PHONEID is not null);
      end

      -- set the MAKENEWPRIMARYROWID phone as primary

      if @MAKENEWPRIMARYROWID is not null 
        begin
            update dbo.BATCHCONSTITUENTUPDATEPHONES
            set ISPRIMARY = 1
            where ID = @MAKENEWPRIMARYROWID;
        end
    end        
end