USP_CONSTITUENTUPDATEBATCH_APPLYEMAILRULES

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

  @PRIMARYRECORDID uniqueidentifier, -- Existing Constituent ID

  @CHANGEAGENTID uniqueidentifier,
  @ADDALLEMAILS bit = 1,
  @DIFFERENTEMAILCODE tinyint = 3,
  @NEWEMAILPRIMARYCODE tinyint = 1,
  @EXCLUDEEMAILID uniqueidentifier = null --this is used by manual reconciliation to not include in the cursor the incoming email shown on the resolution screen; 

)
as
begin
  set nocount on;

  declare @INCOMINGEMAILROWID uniqueidentifier;
  declare @INCOMINGEMAILADDRESSTYPECODEID uniqueidentifier;
  declare @INCOMINGEMAILADDRESS dbo.UDT_EMAILADDRESS; 
  declare @INCOMINGISPRIMARY bit; 
  -- secondary fields

  declare @INCOMINGDONOTEMAIL bit;
  declare @INCOMINGSTARTDATE date;
  declare @INCOMINGENDDATE date;
  declare @INCOMINGINFOSOURCECODEID uniqueidentifier;

  declare @MAKENEWPRIMARYROWID uniqueidentifier;
  declare @EXISTINGPRIMARYEMAILADDRESSTYPECODEID uniqueidentifier;  
  declare @EXISTINGISPRIMARYEMAILADDRESSID uniqueidentifier;

  declare @EXISTINGEMAILADDRESSID uniqueidentifier;
  declare @EXISTINGEMAILADDRESSTYPECODEID uniqueidentifier;
  declare @EXISTINGISPRIMARY bit;
  declare @EXISTINGDONOTEMAIL bit;
  declare @EXISTINGSTARTDATE date;
  declare @EXISTINGENDDATE date;
  declare @EXISTINGINFOSOURCECODEID uniqueidentifier;
  declare @PRIMARYCOUNT int = dbo.UFN_BATCHCONSTITUENTUPDATEEMAILADDRESSES_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
    @EXISTINGISPRIMARYEMAILADDRESSID = ID,
    @EXISTINGPRIMARYEMAILADDRESSTYPECODEID = case when ISPRIMARY = 1 then EMAILADDRESSTYPECODEID else null end
    from dbo.EMAILADDRESS where CONSTITUENTID = @PRIMARYRECORDID
    order by ISPRIMARY desc;
  if @EXISTINGISPRIMARYEMAILADDRESSID is not null -- only apply the rules if the constituent has at least one email address

    begin
      declare INCOMINGEMAIL_CURSOR cursor local fast_forward for
        select 
        ID,
        EMAILADDRESSTYPECODEID,
        EMAILADDRESS,
        ISPRIMARY,
        DONOTEMAIL,
        STARTDATE,
        ENDDATE,
        INFOSOURCECODEID    
      from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES
      where BATCHCONSTITUENTUPDATEID = @ID and EMAILADDRESSID is null and BATCHCONSTITUENTUPDATEEMAILADDRESSES.ID <> coalesce(@EXCLUDEEMAILID, @NULLVALUE); -- exclude from the cursor the ID coming from the resolutions screen


      open INCOMINGEMAIL_CURSOR;

      begin try
        fetch next from INCOMINGEMAIL_CURSOR into
          @INCOMINGEMAILROWID,
          @INCOMINGEMAILADDRESSTYPECODEID,
          @INCOMINGEMAILADDRESS,
          @INCOMINGISPRIMARY,
          @INCOMINGDONOTEMAIL,
          @INCOMINGSTARTDATE,
          @INCOMINGENDDATE,
          @INCOMINGINFOSOURCECODEID;

          while (@@FETCH_STATUS = 0)
          begin

          set @EXISTINGEMAILADDRESSID = null;

          select top 1 @EXISTINGEMAILADDRESSID = ID,
                 @EXISTINGEMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODEID,      
                 @EXISTINGISPRIMARY = ISPRIMARY,
                 @EXISTINGDONOTEMAIL = DONOTEMAIL,
                 @EXISTINGSTARTDATE = STARTDATE,
                 @EXISTINGENDDATE = ENDDATE,
                 @EXISTINGINFOSOURCECODEID = INFOSOURCECODEID      
          from dbo.EMAILADDRESS E
          where E.CONSTITUENTID = @PRIMARYRECORDID
          and E.EMAILADDRESS = @INCOMINGEMAILADDRESS
          and (E.EMAILADDRESSTYPECODEID is null or @INCOMINGEMAILADDRESSTYPECODEID is null or E.EMAILADDRESSTYPECODEID = @INCOMINGEMAILADDRESSTYPECODEID)
          and not exists(select EMAILADDRESSID from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES where BATCHCONSTITUENTUPDATEID = @ID and EMAILADDRESSID = E.ID)
          order by case when E.EMAILADDRESSTYPECODEID = @INCOMINGEMAILADDRESSTYPECODEID then 1 else 2 end, ISPRIMARY desc, ENDDATE, DONOTEMAIL, DATECHANGED desc;  

          if @EXISTINGEMAILADDRESSID is not null -- incoming email is identical to existing email     

            begin
              if @DIFFERENTEMAILCODE <> 0
                set @UPDATESECONDARYDATA = 1                
              else if not(@INCOMINGDONOTEMAIL=1 and @EXISTINGDONOTEMAIL=0) 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.BATCHCONSTITUENTUPDATEEMAILADDRESSES set
                    EMAILADDRESSID = @EXISTINGEMAILADDRESSID,    
                    ISPRIMARY = case when @PRIMARYCOUNT = 1 then ISPRIMARY else @EXISTINGISPRIMARY end, -- set isprimary only when no primary exists already

                    EMAILADDRESSTYPECODEID = coalesce(EMAILADDRESSTYPECODEID, @EXISTINGEMAILADDRESSTYPECODEID),
                    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),

                    --DONOTEMAIL = coalesce(NULLIF(DONOTEMAIL,0),@EXISTINGDONOTEMAIL),

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

                begin
                  update dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES set
                    EMAILADDRESSID = @EXISTINGEMAILADDRESSID,    
                    EMAILADDRESSTYPECODEID = @EXISTINGEMAILADDRESSTYPECODEID,
                    ISPRIMARY = case when @PRIMARYCOUNT = 1 then ISPRIMARY else @EXISTINGISPRIMARY end,
                    DONOTEMAIL = @EXISTINGDONOTEMAIL,
                    STARTDATE = @EXISTINGSTARTDATE, 
                    ENDDATE = @EXISTINGENDDATE,
                    INFOSOURCECODEID = @EXISTINGINFOSOURCECODEID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE                
                  where ID = @INCOMINGEMAILROWID;         
                end              
            end            
          else  -- Incoming email is not similar

            begin
              if @DIFFERENTEMAILCODE = 0 -- Ignore

                delete from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES 
                where ID = @INCOMINGEMAILROWID;  
              else if @DIFFERENTEMAILCODE = 3 -- Add

              begin
                if (@INCOMINGISPRIMARY = 1 or (@MAKENEWPRIMARYROWID is null and (@NEWEMAILPRIMARYCODE = 2 or (@NEWEMAILPRIMARYCODE = 1 and @INCOMINGEMAILADDRESSTYPECODEID = @EXISTINGPRIMARYEMAILADDRESSTYPECODEID))))                                           
                    and @INCOMINGENDDATE is null and @INCOMINGDONOTEMAIL = 0 -- Cannot make email with end dates primary under any condition

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

                      set @MAKENEWPRIMARYROWID = @INCOMINGEMAILROWID;                        
  end    
            end      

        fetch next from INCOMINGEMAIL_CURSOR into
          @INCOMINGEMAILROWID,
          @INCOMINGEMAILADDRESSTYPECODEID,
          @INCOMINGEMAILADDRESS,
          @INCOMINGISPRIMARY,
          @INCOMINGDONOTEMAIL,
          @INCOMINGSTARTDATE,
          @INCOMINGENDDATE,
          @INCOMINGINFOSOURCECODEID;
        end

        close INCOMINGEMAIL_CURSOR;
        deallocate INCOMINGEMAIL_CURSOR;
      end try
      begin catch

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

      -- set all potential primary emails to non-primary, except for the MAKENEWPRIMARYROWID email 

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

      if @ADDALLEMAILS = 1
      begin
        insert into dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES(
        ID,
        EMAILADDRESSID,
        BATCHCONSTITUENTUPDATEID,
        SEQUENCE,
        EMAILADDRESSTYPECODEID,
        EMAILADDRESS,
        ISPRIMARY,
        DONOTEMAIL,
        UPDATEHOUSEHOLD,
        STARTDATE,
        ENDDATE,
        INFOSOURCECODEID,
        ADDEDBYID,
        CHANGEDBYID,
      ROWORIGINCODE
        )
        select
        newid(),
        E.ID as EMAILADDRESSID,
        @ID,
        E.SEQUENCE,
        E.EMAILADDRESSTYPECODEID,
        E.EMAILADDRESS,
        case when exists(select ISPRIMARY from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES where BATCHCONSTITUENTUPDATEID = @ID and ISPRIMARY = 1) or @MAKENEWPRIMARYROWID is not null then 0 else ISPRIMARY end,
        E.DONOTEMAIL,
        cast(0 as bit) as UPDATEHOUSEHOLD,
      E.STARTDATE,
      E.ENDDATE,
        E.INFOSOURCECODEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        2 -- Existing record, automatically populated by the system during import

        from dbo.EMAILADDRESS E
        where E.CONSTITUENTID = @PRIMARYRECORDID and E.ID not in (select EMAILADDRESSID from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES where BATCHCONSTITUENTUPDATEID = @ID and EMAILADDRESSID is not null);
      end

      -- set the MAKENEWPRIMARYROWID email as primary

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