USP_EMAILLIST_PROCESSAPPEALLIST

CREATE procedure dbo.USP_EMAILLIST_PROCESSAPPEALLIST
(
    @ID int
)
as
begin

    ------------------------------------------------------------------------------------------------------------------------

    ------------------------Add index to ignore dup entries-----------------------------------------------------------------

    ------------------------------------------------------------------------------------------------------------------------

    CREATE UNIQUE NONCLUSTERED INDEX [APPEALEMAILLISTMEMBER_CONSTIT] on #APPEALEMAILLISTMEMBER
    (
        CONSTITUENTID,
        EMAILADDRESS
    )
    with (IGNORE_DUP_KEY = ON)

    ------------------------------------------------------------------------------------------------------------------------

    ------------------------Declare some variables for mail pref data-------------------------------------------------------

    ------------------------------------------------------------------------------------------------------------------------

    declare @APPEALID uniqueidentifier
    declare @APPEALBUSINESSUNITCODEIDS table ([ID] uniqueidentifier primary key);
    declare @APPEALCATEGORYCODEID uniqueidentifier
    declare @APPEALSITEID uniqueidentifier


    ------------------------------------------------------------------------------------------------------------------------

    ------------------------Populate mail pref data based on selected list--------------------------------------------------

    ------------------------------------------------------------------------------------------------------------------------

    select @APPEALID = IM.APPEALID
    from dbo.EmailList EL
    inner join dbo.BBNCAPPEALIDMAP IM on CAST(EL.OPTIONS as nvarchar(20)) = IM.ID
    where El.ID = @ID

    select
    @APPEALCATEGORYCODEID = APPEALCATEGORYCODEID,
    @APPEALSITEID = SITEID
    from dbo.APPEAL
    where ID = @APPEALID

    insert into @APPEALBUSINESSUNITCODEIDS ([ID])
    select BUSINESSUNITCODEID
    from dbo.APPEALBUSINESSUNIT
    where APPEALID = @APPEALID;    


    ------------------------------------------------------------------------------------------------------------------------

    ------------------------Select the valid mail preferences (ones that use emails for appeals)----------------------------

    ------------------------------------------------------------------------------------------------------------------------

    create table #TEMPAPPEALEMAILPREFERENCE (
    [ID] uniqueidentifier not null,
    [CONSTITUENTID] uniqueidentifier not null,
    [EMAILADDRESSID] uniqueidentifier,
    [USEPRIMARYEMAIL] bit not null,
    [CATEGORYCODEID] uniqueidentifier,
    [BUSINESSUNITCODEID] uniqueidentifier,
    [SITEID] uniqueidentifier
    );

    insert into #TEMPAPPEALEMAILPREFERENCE ([ID], [CONSTITUENTID], [EMAILADDRESSID], [USEPRIMARYEMAIL], [CATEGORYCODEID], [BUSINESSUNITCODEID], [SITEID])
    select
        MP.[ID],
        MP.[CONSTITUENTID],
        MP.[EMAILADDRESSID],
        MP.[USEPRIMARYEMAIL],
        MP.[CATEGORYCODEID],
        MP.[BUSINESSUNITCODEID],
        MP.[SITEID]
    from dbo.[MAILPREFERENCE] as MP 
    where MP.[SENDMAIL] = 1 and MP.[DELIVERYMETHODCODE] = 1 and MP.[MAILTYPECODE] = 1;

    create nonclustered index [IX_#TEMPAPPEALEMAILPREFERENCE_CONSTITUENTID] on #TEMPAPPEALEMAILPREFERENCE ([CONSTITUENTID])
    include ([ID], [EMAILADDRESSID], [USEPRIMARYEMAIL], [CATEGORYCODEID], [BUSINESSUNITCODEID], [SITEID]);


    ------------------------------------------------------------------------------------------------------------------------

    ------------------------Select the constits/email addresses that match this appeal--------------------------------------

    ------------------------------------------------------------------------------------------------------------------------


    -- TWG  07/17/2013

    -- Using a common table expression to break and a union of the joins to EMAILADDRESS so that the OR is not needed in the join to EMAILADDRESS

    with APPEALEMAILLISTCONSTITUENTS_CTE as (
        select
            MP.[CONSTITUENTID],
            MP.[EMAILADDRESSID],
            MP.[USEPRIMARYEMAIL]
        from #TEMPAPPEALEMAILPREFERENCE MP
        inner join dbo.CONSTITUENT as C on MP.[CONSTITUENTID] = C.[ID]
        where
        (
            (
                --Where the Mail Preference is for all appeals and we don't have a closer match

                MP.BUSINESSUNITCODEID is null and
                MP.CATEGORYCODEID is null and
                MP.SITEID is null and
                not exists(select *
                            from dbo.MAILPREFERENCE sub, dbo.APPEAL
                            left join dbo.APPEALBUSINESSUNIT AB on AB.APPEALID = APPEAL.ID
                            where APPEAL.ID = @APPEALID and sub.ID <> MP.ID
                            and sub.CONSTITUENTID = MP.CONSTITUENTID
                            and ((sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                            or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                            or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                            or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
                            or (sub.SITEID is null and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
                            or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
                            or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID is null)))
            )
            or
            (
                --Where we have a good match

                (MP.BUSINESSUNITCODEID in (select [ID] from @APPEALBUSINESSUNITCODEIDS) or MP.BUSINESSUNITCODEID is null) and
                (MP.CATEGORYCODEID = @APPEALCATEGORYCODEID or MP.CATEGORYCODEID is null) and
                (MP.SITEID = @APPEALSITEID or MP.SITEID is null) and
                not (MP.CATEGORYCODEID is null and MP.SITEID is null)
            )
        )
        and
        (
            isnull(
                    (
                        select SUPPRESSMAILING from
                        (
                            select top 1
                                case when (SENDMAIL = 0 or (SENDMAIL = 1 and DELIVERYMETHODCODE = 0)) then 1 else 0 end SUPPRESSMAILING,
                                case              
                                    when (SITEID is not null and BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null) then 3
                                    when (SITEID is not null and BUSINESSUNITCODEID is not null) then 2
                                    when (SITEID is not null and CATEGORYCODEID is not null) then 2
                                    when (BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null) then 2
                                    when (SITEID is not null or BUSINESSUNITCODEID is not null or CATEGORYCODEID is not null) then 1
                                    else 0
                                end as CRITERIAMATCHSCORE    
                            from dbo.MAILPREFERENCE
                            where
                                MAILTYPECODE = 1
                                and CONSTITUENTID = C.ID
                                and (SITEID is null or SITEID = @APPEALSITEID)
                                and (CATEGORYCODEID is null or CATEGORYCODEID = @APPEALCATEGORYCODEID)
                            order by CRITERIAMATCHSCORE desc, SUPPRESSMAILING desc
                        ) as MATCHEDPREFERENCES
                    )
            , 0) = 0
        )
    )
    insert into #APPEALEMAILLISTMEMBER (CONSTITUENTID, EMAILADDRESS)
    select
        AELC.CONSTITUENTID,
        EA.EMAILADDRESS
    from APPEALEMAILLISTCONSTITUENTS_CTE AELC
    inner join dbo.EMAILADDRESS EA on AELC.[EMAILADDRESSID] = EA.[ID]
    union all
    select
        AELC.CONSTITUENTID,
        EA.EMAILADDRESS
    from APPEALEMAILLISTCONSTITUENTS_CTE AELC
    inner join dbo.EMAILADDRESS EA on AELC.[CONSTITUENTID] = EA.[CONSTITUENTID] and AELC.[USEPRIMARYEMAIL] = 1 and EA.[ISPRIMARY] = 1 AND EA.[DONOTEMAIL] = 0;

    drop table #TEMPAPPEALEMAILPREFERENCE

    ------------------------------------------------------------------------------------------------------------------------

    ------------------------Select the anonymous subscriber email addresses that match this appeal---------------------------------------------------------

    ------------------------------------------------------------------------------------------------------------------------  

    insert into #APPEALEMAILLISTMEMBER (CONSTITUENTID, ANONYMOUSEMAILSUBSCRIBERID, EMAILADDRESS)
    select EA.CONSTITUENTID, AES.ID, AES.EMAILADDRESS
    from dbo.ANONYMOUSEMAILPREFERENCE AEP
    inner join dbo.ANONYMOUSEMAILSUBSCRIBER AES on AES.ID = AEP.ANONYMOUSEMAILSUBSCRIBERID
    left outer join @APPEALBUSINESSUNITCODEIDS BU on BU.ID = AEP.BUSINESSUNITCODEID
    left outer join dbo.EMAILADDRESS EA on EA.EMAILADDRESS = AES.EMAILADDRESS
    left outer join #APPEALEMAILLISTMEMBER AEM on AEM.CONSTITUENTID = EA.CONSTITUENTID
    where AEP.OPTEDOUT = 0
    and (AEP.APPEALCATEGORYCODEID is null OR AEP.APPEALCATEGORYCODEID = @APPEALCATEGORYCODEID)
    and (AEP.BUSINESSUNITCODEID is null or BU.ID is not null)
    and (AEP.SITEID is null or AEP.SITEID = @APPEALSITEID)
    and AEM.CONSTITUENTID is null


end