USP_GET_INACTIVERECIPIENT_BYEMAILID

CREATE procedure dbo.USP_GET_INACTIVERECIPIENT_BYEMAILID 
 @EMAILID int,
 @INACTIVERECIPIENTFROMMONTHS int=0
as
begin
    declare @EMAILLISTID as int =0,
              @EMAILLIST_DATASOURCE as int =0,
                @EMAILADDRESSTYPEID as uniqueidentifier =null,
              @COUNT int=0,
              @ACQUIREDRECORDCOUNT int=0

    create table #EmailDataSource 
    (
    emailListID int,
    emailListDatasource int,
    emailAddressTypeID uniqueidentifier
    )
    insert into #EmailDataSource
    select EL.ID, EL.DataSourceID, EM.EmailAddressTypeID
    from dbo.Email EM 
    join dbo.Email_EmailList EEL on EEL.EmailID= EM.ID
    join dbo.EmailList EL on EL.ID = EEL.EmailListID where EM.id =@EMAILID and EEL.IsTest = 0


    declare EMAILLIST_CURSOR CURSOR  
    local  forward_only  for select emailListID, emailListDatasource, emailAddressTypeID from #EmailDataSource

    open EMAILLIST_CURSOR  
    fetch next from EMAILLIST_CURSOR INTO  @EMAILLISTID ,@EMAILLIST_DATASOURCE, @EMAILADDRESSTYPEID
    while @@FETCH_STATUS = 0  
    begin  
            if @EMAILLIST_DATASOURCE =1          --- Constituents List

            begin
                if (@EMAILADDRESSTYPEID is null)
                  begin
                     select @COUNT=count(1) 
                     from dbo.EmailList_People EQP 
                     join dbo.EMAIL_INACTIVERECIPIENT EIR on EQP.EmailAddress = EIR.emailaddress 
                     where EmailListID = @EMAILLISTID and EIR.INACTIVEFROMMONTHS >=@INACTIVERECIPIENTFROMMONTHS
                  end
                 else
                    begin
                  select @COUNT=count(1) from (
                            select OQ.EMAILADDRESS from(
                                                             select IQ.EMAILADDRESS, ROW_NUMBER() over(partition by IQ.ID order by IQ.isprimary) as rowNumber
                                                             from(
                                                                    select CS.ID, ROW_NUMBER() over(partition by CS.ID, E.EMAILADDRESSTYPECODEID order by E.sequence) as row_num,
                                                                           ISPRIMARY,E.EMAILADDRESSTYPECODEID,E.EMAILADDRESS
                                                                    from dbo.EmailList_People EQP
                                                                    join dbo.BackOfficeSystemPeople BOSP on BOSP.ID =EQP.PeopleID
                                                                    join dbo.constituent CS on CS.SEQUENCEID=BOSP.BackofficeRecordID
                                                                    join dbo.EMAILADDRESS E on E.CONSTITUENTID=CS.ID
                                                                    where EQP.EmailListID = @EMAILLISTID
                                                                )IQ where IQ.row_num=1 and (IQ.EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPEID or IQ.ISPRIMARY=1) 
                                                            )OQ where OQ.rowNumber=1 
                                      )FQ join EMAIL_INACTIVERECIPIENT EIR on EIR.EMAILADDRESS=FQ.EMAILADDRESS
                                                    where  EIR.INACTIVEFROMMONTHS >=@INACTIVERECIPIENTFROMMONTHS
                   end
            end
            else if @EMAILLIST_DATASOURCE =101     --- Registered Users

            begin
                select @COUNT=count(1) 
                from dbo.ClientUsers CU 
                join dbo.EMAIL_INACTIVERECIPIENT EIR on CU.EMail = EIR.emailaddress
                where Deleted=0 and EIR.INACTIVEFROMMONTHS >=@INACTIVERECIPIENTFROMMONTHS
            end
            else if @EMAILLIST_DATASOURCE=300      --- Imported List

            begin
                select @COUNT=count(1) 
                from dbo.EmailListUploadMasterList EUM
                join dbo.EMAIL_INACTIVERECIPIENT EIR on EUM.EmailAddress = EIR.emailaddress 
                where EUM.EmailListID = @EMAILLISTID and EIR.INACTIVEFROMMONTHS >=@INACTIVERECIPIENTFROMMONTHS
            end
            else if @EMAILLIST_DATASOURCE=303      --- User Defined

            begin 
                select @COUNT=count(1) 
                from dbo.EmailList_UserDefined EQP 
                join dbo.EMAIL_INACTIVERECIPIENT EIR on EQP.EmailAddress = EIR.emailaddress 
                where EQP.EmailListID = @EMAILLISTID and EIR.INACTIVEFROMMONTHS >=@INACTIVERECIPIENTFROMMONTHS
            end    
      else if @EMAILLIST_DATASOURCE=103      --- Chapter

            begin
            declare @QueryID as int
            select @QueryID=QueryID from EmailList_Query 
      where EmailListID=@EMAILLISTID

      select @COUNT=count(1) 
                from dbo.EmailList_People EQP 
                join dbo.EMAIL_INACTIVERECIPIENT EIR on EQP.EmailAddress = EIR.emailaddress 
                where QueryID = @QueryID and EIR.INACTIVEFROMMONTHS >=@INACTIVERECIPIENTFROMMONTHS
            end
            set @AcquiredRecordCount=@AcquiredRecordCount + @COUNT 

    fetch next from EMAILLIST_CURSOR INTO  @EMAILLISTID ,@EMAILLIST_DATASOURCE, @EMAILADDRESSTYPEID
    end  

    close EMAILLIST_CURSOR  
    deallocate EMAILLIST_CURSOR  

    select @ACQUIREDRECORDCOUNT as AcquiredRecordCount

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