UFN_COMMUNICATIONS_GETCONSTITUENTSWITHRECENTCOMMUNICATIONS

CREATE function dbo.[UFN_COMMUNICATIONS_GETCONSTITUENTSWITHRECENTCOMMUNICATIONS]
(
    @PERIODTYPECODE tinyint,
    @NUMPERIODS integer,
    @STARTDATE datetime,
    @COMMUNICATIONTYPES xml
)
returns @CONSTITUENTS table ([CONSTITUENTID] uniqueidentifier) 
as 
begin
    -- @PERIODTYPECODE

    --        0 - week

    --        1 - month

    --        2 - quarter

    --        3 - year


    -- @COMMUNICATIONTYPES collection of communication types to be considered

    --        <COMMUNICATIONTYPES><ITEM><COMMUNICATIONTYPECODE>?</COMMUNICATIONTYPECODE></ITEM></COMMUNICATIONTYPES>

    --    0 - Appeal communications

    --    1 - Event invitations

    --    2 - Membership renewal

    --    3 - Pledge reminder

    --    4 - Sponsorship effort

    --    5 - Marketing effort


    declare @INCLUDEAPPEALCOMMUNICATIONS bit = 0;
    declare @INCLUDEEVENTINVITATIONS bit = 0;
    declare @INCLUDEMEMBERSHIPRENEWAL bit = 0;
    declare @INCLUDEPLEDGEREMINDER bit = 0;
    declare @INCLUDESPONSORSHIPEFFORT bit = 0;
    declare @INCLUDEMARKETINGEFFORT bit = 0;

    -- Parse communication types

    declare @COMMUNICATIONTYPECODE tinyint;

    declare TYPESCURSOR cursor local fast_forward for
        select
            COMMUNICATIONTYPECODE
        from dbo.UFN_COMMUNICATIONLETTERACTIVITYEXCLUSION_GETCOMMUNICATIONTYPES_FROMITEMLISTXML(@COMMUNICATIONTYPES);

    open TYPESCURSOR;
    fetch next from TYPESCURSOR into @COMMUNICATIONTYPECODE;

    while (@@FETCH_STATUS = 0)
    begin
        if @COMMUNICATIONTYPECODE = 0
            set @INCLUDEAPPEALCOMMUNICATIONS = 1;

        else if @COMMUNICATIONTYPECODE = 1
            set @INCLUDEEVENTINVITATIONS = 1;

        else if @COMMUNICATIONTYPECODE = 2
            set @INCLUDEMEMBERSHIPRENEWAL = 1;

        else if @COMMUNICATIONTYPECODE = 3
            set @INCLUDEPLEDGEREMINDER = 1;

        else if @COMMUNICATIONTYPECODE = 4
            set @INCLUDESPONSORSHIPEFFORT = 1;

        else if @COMMUNICATIONTYPECODE = 5
            set @INCLUDEMARKETINGEFFORT = 1;

        fetch next from TYPESCURSOR into @COMMUNICATIONTYPECODE;
    end

    close TYPESCURSOR;
    deallocate TYPESCURSOR;

    insert into @CONSTITUENTS    
        select 
            CONSTITUENT.ID as CONSTITUENTID 
        from dbo.CONSTITUENT
        where exists (
                select 
                    CONSTITUENTSEGMENT.CONSTITUENTID
                from dbo.CONSTITUENTSEGMENT
                inner join dbo.MKTSEGMENTATIONSEGMENT
                    on CONSTITUENTSEGMENT.SEGMENTID = MKTSEGMENTATIONSEGMENT.ID
                inner join dbo.MKTSEGMENTATION
                    on MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = MKTSEGMENTATION.ID    
                where
                    CONSTITUENTSEGMENT.CONSTITUENTID = CONSTITUENT.ID
                    -- Exclude Marketing acknowledgments

                    and MKTSEGMENTATION.MAILINGTYPECODE <> 1
                    and (@INCLUDEAPPEALCOMMUNICATIONS = 1 or not (MKTSEGMENTATION.MAILINGTYPECODE = 0 and MKTSEGMENTATION.COMMUNICATIONTYPECODE = 1))
                    and (@INCLUDEEVENTINVITATIONS = 1 or not (MKTSEGMENTATION.MAILINGTYPECODE = 0 and MKTSEGMENTATION.COMMUNICATIONTYPECODE = 2))
                    and (@INCLUDEMEMBERSHIPRENEWAL = 1 or MKTSEGMENTATION.MAILINGTYPECODE <> 2)
                    and (@INCLUDESPONSORSHIPEFFORT = 1 or MKTSEGMENTATION.MAILINGTYPECODE <> 3)
                    and (@INCLUDEMARKETINGEFFORT = 1 or not (MKTSEGMENTATION.MAILINGTYPECODE = 0 and MKTSEGMENTATION.COMMUNICATIONTYPECODE = 0))
                    and    (
                        (@PERIODTYPECODE = 0 and MKTSEGMENTATION.MAILDATE between DATEADD(ww, @NUMPERIODS * @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                        or (@PERIODTYPECODE = 1 and MKTSEGMENTATION.MAILDATE between DATEADD(mm, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                        or (@PERIODTYPECODE = 2 and MKTSEGMENTATION.MAILDATE between DATEADD(qq, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                        or (@PERIODTYPECODE = 3 and MKTSEGMENTATION.MAILDATE between DATEADD(yy, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                    )
                )
            or exists (    
                    -- Pledge reminder    

                    select
                        REVENUE.CONSTITUENTID,
                        dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID) as NAME
                    from dbo.PLEDGEREMINDERSENT
                    inner join dbo.REVENUE    
                        on PLEDGEREMINDERSENT.REVENUEID = REVENUE.ID        
                    where                 
                        @INCLUDEPLEDGEREMINDER = 1
                        and REVENUE.CONSTITUENTID = CONSTITUENT.ID
                        and (
                                (@PERIODTYPECODE = 0 and PLEDGEREMINDERSENT.SENTDATE between DATEADD(ww, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                                or (@PERIODTYPECODE = 1 and PLEDGEREMINDERSENT.SENTDATE between DATEADD(mm, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                                or (@PERIODTYPECODE = 2 and PLEDGEREMINDERSENT.SENTDATE between DATEADD(qq, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                                or (@PERIODTYPECODE = 3 and PLEDGEREMINDERSENT.SENTDATE between DATEADD(yy, @NUMPERIODS * -1, @STARTDATE) and @STARTDATE)
                        )
                );

    return;
end