USP_CMSINACTIVERECIPIENT_DATA_SYNC

CREATE procedure dbo.USP_CMSINACTIVERECIPIENT_DATA_SYNC
(
 @RECORDPROCESS int = 0 output,
 @NOOFERRORS int = 0 output
)
as
begin
     declare @CHANGEAGENTID as uniqueidentifier,
           @CURRENTDATETIME as datetime,
           @CURRENTDATE as datetime,
           @InactiveRecipientFromMonths as tinyint=12,
           @ERRORMESSAGE as nvarchar(1024) = '';

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

   set @CURRENTDATETIME = getutcdate();
   set @CURRENTDATE=getutcdate();

   if exists (select 1 from setting where id=148)
   begin
    set @InactiveRecipientFromMonths = (select value from setting where id=148)
   end

   begin try

     if OBJECT_ID('tempdb..#INACTIVERECIPIENT') IS NOT NULL
     begin
        drop table #INACTIVERECIPIENT
     end

      if OBJECT_ID('tempdb..#INACTIVERECIPIENTDTLS') IS NOT NULL
    begin
        drop table #INACTIVERECIPIENTDTLS
    end

    create table #INACTIVERECIPIENTDTLS
      (
      INACTIVERECIPIENTID bigint,
      LASTOPENEDDATE datetime,
      LASTUPDATEDDATE datetime
      ) 
    create table #INACTIVERECIPIENT
    (
    ID bigint identity(1,1),
    EMAILADDRESS nvarchar(510),
    CONSTITUENTID uniqueidentifier,
    LASTOPENEDDATE datetime,
    LASTCLICKEDDATE datetime,
    INACTIVEFROMMONTHS int
    )

    insert into #INACTIVERECIPIENT (EMAILADDRESS, CONSTITUENTID, INACTIVEFROMMONTHS)
    select EmailAddress,
           ConstituentID,
           (DATEDIFF(MONTH,sentdate,@CURRENTDATE)-(case when DATEPART(dd,@CURRENTDATE) < DATEPART(dd,sentdate) then 1 else 0 end)) as inactiveFromMonths
         from(
                        select 
                         EJR.EmailAddress
                        ,ConstituentID
                        ,Row_Number() over(partition by EJR.emailAddress order by sentdate desc) as row_num
                        ,sentdate
                        ,Opened
                        from EmailJob_Recipient EJR With (Nolock)
                        inner join dbo.Email E with (Nolock) on E.ID = EJR.EmailID
                        left join EMAILADDRESS EMA With (Nolock) on EJR.EmailAddress=EMA.EMAILADDRESS 
                        where E.type <> 16 and EJR.sent=1 and EJR.Dsned=0 -- Added an additional check to exclude BBDM emails while calculating the unengage emailaddress.Email type 16 denotes BBDM emails. 

                 )Result where Result.row_num=1 and Opened=0 and datediff(DAY,sentdate,@CURRENTDATE)>datediff(DAY,sentdate,DATEADD(MONTH,@InactiveRecipientFromMonths,sentdate))


    update EIR
    set LASTCLICKEDDATE=LCD.lastClickedDate
    from #INACTIVERECIPIENT EIR 
    join (
          select ID as RID
                ,requestDate as lastClickedDate
                   from (
                      select 
                      EIR.ID
                     ,ROW_NUMBER() over (partition by EJR.EmailAddress order by requestDate desc) row_num
                     ,requestDate
                                        from
                                        [EmailLink] EL with (nolock) 
                                        left join Stats ST with (nolock) on (ST.pageid = EL.pid and ST.sourceid = EL.emailid and ST.sourcetypeid = 1 and EL.PID is NOT NULL)
                                        or (ST.URL = EL.URL and ST.sourceid = EL.emailid  and ST.sourcetypeid = 1 and EL.[URL] is not null)
                                        or (ST.DocumentId = EL.DocumentID and ST.sourceid = EL.emailid  and ST.sourcetypeid = 0 and EL.[DocumentID] is not null)
                                        join EmailJob_Recipient EJR with (nolock) on EJR.id = ST.EmailJobRecipientID
                                        join #INACTIVERECIPIENT EIR on EIR.EMAILADDRESS COLLATE DATABASE_DEFAULT=EJR.EmailAddress 
                                     )Result where row_num=1
         ) LCD on LCD.RID = EIR.ID 

        --Fetching latest opened date and updated date for inactive recipients

         insert into #INACTIVERECIPIENTDTLS
        select max(EIR.ID) INACTIVERECIPIENTID , max(OpenedDate) LASTOPENEDDATE, max(UpdateDate) LASTUPDATEDDATE
        from dbo.EMAILJOB_RECIPIENT EJR with (nolock)
        inner join #INACTIVERECIPIENT EIR on EIR.EMAILADDRESS COLLATE DATABASE_DEFAULT=EJR.EMAILADDRESS
        where EJR.SENT=1 and EJR.DSNED=0 and EJR.OPENEDDATE is not null and EJR.OPENED = 1
        group by EJR.EMAILADDRESS


      update EIR
      set LASTOPENEDDATE=EIRD.LASTOPENEDDATE,
          INACTIVEFROMMONTHS = (DATEDIFF(MONTH,LASTUPDATEDDATE,@CURRENTDATE)-(case when DATEPART(dd,@CURRENTDATE) < DATEPART(dd,LASTUPDATEDDATE) then 1 else 0 end))
      from #INACTIVERECIPIENT EIR 
      join #INACTIVERECIPIENTDTLS EIRD on EIRD.INACTIVERECIPIENTID = EIR.ID

     --Delete active records from #INACTIVERECIPIENT table

     delete EIR 
       from #INACTIVERECIPIENT EIR 
       join (
          select Max(EIR.ID) as EID
          from dbo.EmailJob_Recipient EJR with (nolock)
          join #INACTIVERECIPIENT EIR on EIR.EMAILADDRESS COLLATE DATABASE_DEFAULT=EJR.EMAILADDRESS
          where EJR.Sent=1 and Opened=1
          group by EJR.EmailAddress having datediff(DAY,max(UpdateDate),@CURRENTDATE)<= datediff(DAY,max(SentDate),DATEADD(MONTH,@InactiveRecipientFromMonths,max(sentDate)))
       )source on source.EID=EIR.ID;


    merge dbo.EMAIL_INACTIVERECIPIENT as target
    using #INACTIVERECIPIENT as source on (target.EMAILADDRESS=source.EMAILADDRESS COLLATE DATABASE_DEFAULT)

       when matched then
    update
    set CHANGEDBYID = @CHANGEAGENTID, 
        DATECHANGED = @CURRENTDATETIME,
        DATEOFLASTOPENED = source.LASTOPENEDDATE,
        DATEOFLASTCLICKED = source.LASTCLICKEDDATE,
        INACTIVEFROMMONTHS = source.INACTIVEFROMMONTHS

    when not matched then
    insert (EMAILADDRESS, ISINACTIVE, CONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DATEOFLASTOPENED, DATEOFLASTCLICKED, INACTIVEFROMMONTHS)
    values (source.EmailAddress, 1 ,source.constituentId, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME, source.LASTOPENEDDATE,source.LASTCLICKEDDATE, source.INACTIVEFROMMONTHS);

    --delete active records from EMAIL_INACTIVERECIPIENT table.



     delete EJR 
     from dbo.EMAIL_INACTIVERECIPIENT EJR with (nolock)
     left join #INACTIVERECIPIENT EIR on EIR.EMAILADDRESS=EJR.EMAILADDRESS COLLATE DATABASE_DEFAULT
     where EIR.EMAILADDRESS is null;


     set @RECORDPROCESS = @RECORDPROCESS + 1

     end try
     begin catch
      set @NOOFERRORS = @NOOFERRORS + 1
      set @ERRORMESSAGE = ERROR_MESSAGE();
      exec dbo.spAddUpdate_Error 0, 'CMS Inactive Recipient Sync Business Process', @ERRORMESSAGE, null
     end catch

     if OBJECT_ID('tempdb..#INACTIVERECIPIENT') IS NOT NULL
     begin
        drop table #INACTIVERECIPIENT
     end

      if OBJECT_ID('tempdb..#INACTIVERECIPIENTDTLS') IS NOT NULL
     begin
        drop table #INACTIVERECIPIENTDTLS
     end
end