USP_EMAILLIST_REFRESH

CREATE procedure dbo.USP_EMAILLIST_REFRESH
(
    @ID int,
    @CHANGEAGENTID uniqueidentifier
)
with execute as owner
as begin

    declare @QUERYID int
    declare @BBSYSTEMID int

    create table #ACQUIREDCOUNT (ID int, COUNT int)
    insert into #ACQUIREDCOUNT
    select 1,0

    --Setup stuff

    declare @NOEMAILSOLICITCODEID uniqueidentifier 
    select top 1 @NOEMAILSOLICITCODEID=DONOTEMAILSOLICITCODEID
    from NETCOMMUNITYDEFAULTCODEMAP

    declare @EMAILPHONETYPE int
    select @EMAILPHONETYPE =  EmailPhoneType from dbo.clients

    declare QUERYCURSOR cursor local for
    select QueryID, BBSystem from dbo.EmailList_Query ELQ where ELQ.EmailListID = @ID

    --Clear out old data

    delete from EmailList_People where EmailListID = @ID

    open QUERYCURSOR
    fetch next from QUERYCURSOR into @QUERYID, @BBSYSTEMID

    while @@FETCH_STATUS = 0
    begin

        --Look up info about this selection

        declare @SELECTIONINFO table(DBOBJECTNAME nvarchar(255), OBJECTTYPE tinyint)
        insert into @SELECTIONINFO
        exec dbo.USP_BBNC_GETSELECTIONLISTINFO @QUERYID

        --Extract info about selection

        declare @DBOBJECTNAME nvarchar(255)
        declare @OBJECTTYPE int
        select @DBOBJECTNAME = DBOBJECTNAME, @OBJECTTYPE = OBJECTTYPE from @SELECTIONINFO

        --Build SQL for upating roles based on the selection

        if @OBJECTTYPE = 1
            set @DBOBJECTNAME = @DBOBJECTNAME + '()'

        declare @SQLSTRING nvarchar(4000)
      declare @NOEMAILSOLICITCODEIDSTRING    as nvarchar(50)=CAST(@NOEMAILSOLICITCODEID as nvarchar(50))

        --Select the role members data into a temporary table

        set @SQLSTRING  = ' declare @RECORD table(ID int, NAME nvarchar(154), EMAILADDRESS nvarchar(100), DONOTMAILCODE uniqueidentifier);'
        set @SQLSTRING += ' insert into @RECORD'
        set @SQLSTRING += ' select C.SEQUENCEID, C.Name, ISNULL(EA.EMAILADDRESS,'''') as EMAILADDRESS, CSC.ID as DONOTMAILCODE'
        set @SQLSTRING += ' from dbo.' + @DBOBJECTNAME + ' as R'
        set @SQLSTRING += ' inner join dbo.CONSTITUENT C on C.ID = R.ID'
        set @SQLSTRING += ' left outer join ('
        set @SQLSTRING += '                        select ID,CONSTITUENTID from dbo.CONSTITUENTSOLICITCODE CS'
        set @SQLSTRING += '                        where SOLICITCODEID = ''' + @NOEMAILSOLICITCODEIDSTRING + ''' and'
        set @SQLSTRING += '                        ((STARTDATE is null) or (datediff(day, STARTDATE, getdate())>=0)) and'
        set @SQLSTRING += '                        ((ENDDATE is null) or (datediff(day, getdate(), ENDDATE)>=0)) and 
                                    CONSENTPREFERENCECODE=case when (select CONSENT from SOLICITCODE where id='''+@NOEMAILSOLICITCODEIDSTRING+''' ) <>''None'' then 1 else CONSENTPREFERENCECODE end'
        set @SQLSTRING += '                      ) CSC on C.ID = CSC.CONSTITUENTID'                 
      set @SQLSTRING += ' left outer join dbo.EMAILADDRESS EA on EA.CONSTITUENTID = C.ID'
        set @SQLSTRING += ' where C.ISORGANIZATION = 0 and C.ISGROUP = 0 and ISNULL(EA.ISPRIMARY,1) = 1'

        set @SQLSTRING += 'update #ACQUIREDCOUNT set count = count + (select count(1) from @RECORD) where ID=1'

        -- First Make sure that all of our recipients exist in the 

        -- Backoffice people table

        set @SQLSTRING += ' merge dbo.BACKOFFICESYSTEMPEOPLE as T'
        set @SQLSTRING += ' using @RECORD as S'
        set @SQLSTRING += ' on (T.BACKOFFICESYSTEMID = ' + CAST(@BBSYSTEMID as nvarchar(10)) + ' and T.BACKOFFICERECORDID = S.ID)'
        set @SQLSTRING += ' when not matched by target'
        set @SQLSTRING += '     then insert (BackOfficeSystemID, BackofficeRecordID) values (' + CAST(@BBSYSTEMID as nvarchar(10)) + ', ID);'

        --Insert new rows

        set @SQLSTRING += ' insert into EmailList_People(EmailListID, PeopleID,EmailDisplayName, EmailAddress, QueryID, GlobalOptOut)'
        set @SQLSTRING += ' select ' + CAST(@ID as nvarchar(10)) + ', P.ID, R.NAME, R.EMAILADDRESS, ' + CAST(@QUERYID as nvarchar(10)) + ', case when R.DONOTMAILCODE is null then 0 else 1 end'
        set @SQLSTRING += ' from @RECORD R'
        set @SQLSTRING += ' left outer join dbo.BackOfficeSystemPeople P on P.BACKOFFICESYSTEMID = ' + CAST(@BBSYSTEMID as nvarchar(10)) + ' and P.BACKOFFICERECORDID = R.ID'

        execute(@SQLSTRING)

        fetch next from QUERYCURSOR into @QUERYID, @BBSYSTEMID
    end

    close QUERYCURSOR
    deallocate QUERYCURSOR    

    update dbo.EmailList set
    AcquiredRecordCount = (select count from #ACQUIREDCOUNT where ID = 1),
    AcquiredStatus = 1,
    AcquiredMsg = 'Success',
    AcquiredDate = GETUTCDATE()
    where ID = @ID

    return 0;

end