USP_CONSTITUENCYDATERANGE_REFRESHMEMBERCONSTITUENCY

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@CONSTITUENCYREFRESHPROCESSID	uniqueidentifier	IN
@RECORDCOUNT	int	INOUT
Definition

Copy


CREATE procedure dbo.USP_CONSTITUENCYDATERANGE_REFRESHMEMBERCONSTITUENCY
(
    @CONSTITUENTID uniqueidentifier = null,
    @CONSTITUENCYREFRESHPROCESSID uniqueidentifier = null,
    @RECORDCOUNT int = 0 output
)
as
begin
    set nocount on;

    set @RECORDCOUNT = 0;

    declare @CURRENTDATE datetime = getdate();

    declare @MEMBERDEFINITIONID uniqueidentifier = '2D11326E-8F3B-4322-9797-57C1AACFA5DF';

    begin try
        if @CONSTITUENTID is not null and @CONSTITUENCYREFRESHPROCESSID is not null
        begin
            raiserror('ERR_REFRESH_CONSTITUENTANDSELECTION : A constituency refresh cannot be run with both a specific constituent and a selection.', 13, 1);
        end

        if @CONSTITUENCYREFRESHPROCESSID is null and @CONSTITUENTID is null
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                DATETO,
                REFRESHDATE
            )
            select
                newid(),
                MEMBER.CONSTITUENTID,
                @MEMBERDEFINITIONID,
                cast(min(MEMBERSHIP.JOINDATE) as date),
                cast(max(MEMBERSHIP.EXPIRATIONDATE) as date),
                @CURRENTDATE
            from
                dbo.MEMBER
                inner join dbo.MEMBERSHIP
                    on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                inner join dbo.MEMBERSHIPLEVELTERM
                    on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                left join dbo.MEMBERSHIPLEVEL
                    on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = MEMBER.CONSTITUENTID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MEMBERDEFINITIONID
            where
                CONSTITUENCYDATERANGE.ID is null
                and MEMBER.ISDROPPED = 0
                and (
                    getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
                    or MEMBERSHIPLEVELTERM.TERMCODE = 6
                )
                and MEMBERSHIP.STATUSCODE <> 1
            group by
                MEMBER.CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                CONSTITUENCYDATERANGE.DATEFROM = MEMBER.DATEFROM,
                CONSTITUENCYDATERANGE.DATETO = MEMBER.DATETO,
                CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
            from
                dbo.CONSTITUENCYDATERANGE
                inner join (
                    select
                        CONSTITUENTID,
                        cast(min(MEMBERSHIP.JOINDATE) as date) as DATEFROM,
                        cast(max(MEMBERSHIP.EXPIRATIONDATE) as date) as DATETO
                    from
                        dbo.MEMBER
                        inner join dbo.MEMBERSHIP
                            on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                        inner join dbo.MEMBERSHIPLEVELTERM
                            on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                        left join dbo.MEMBERSHIPLEVEL
                            on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                    where
                        MEMBER.ISDROPPED = 0
                        and (
                            getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
                            or MEMBERSHIPLEVELTERM.TERMCODE = 6
                        )
                        and MEMBERSHIP.STATUSCODE <> 1
                    group by
                        MEMBER.CONSTITUENTID
                ) MEMBER
                    on MEMBER.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        CONSTITUENTID
                    from
                        dbo.MEMBER
                        inner join dbo.MEMBERSHIP
                            on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                        inner join dbo.MEMBERSHIPLEVELTERM
                            on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                        left join dbo.MEMBERSHIPLEVEL
                            on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                    where
                        MEMBER.ISDROPPED = 0
                        and (
                            getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
                            or MEMBERSHIPLEVELTERM.TERMCODE = 6
                        )
                        and MEMBERSHIP.STATUSCODE <> 1
                ) MEMBER
                    on MEMBER.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                MEMBER.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else if @CONSTITUENCYREFRESHPROCESSID is null
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                DATETO,
                REFRESHDATE
            )
            select
                newid(),
                MEMBER.CONSTITUENTID,
                @MEMBERDEFINITIONID,
                cast(min(MEMBERSHIP.JOINDATE) as date),
                cast(max(MEMBERSHIP.EXPIRATIONDATE) as date),
                @CURRENTDATE
            from
                dbo.MEMBER
                inner join dbo.MEMBERSHIP
                    on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                inner join dbo.MEMBERSHIPLEVELTERM
                    on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                left join dbo.MEMBERSHIPLEVEL
                    on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = MEMBER.CONSTITUENTID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MEMBERDEFINITIONID
            where
                CONSTITUENCYDATERANGE.ID is null
                and MEMBER.ISDROPPED = 0
                and (
                    getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
                    or MEMBERSHIPLEVELTERM.TERMCODE = 6
                )
                and MEMBERSHIP.STATUSCODE <> 1
                and MEMBER.CONSTITUENTID = @CONSTITUENTID
            group by
                MEMBER.CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                CONSTITUENCYDATERANGE.DATEFROM = MEMBER.DATEFROM,
                CONSTITUENCYDATERANGE.DATETO = MEMBER.DATETO,
                CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
            from
                dbo.CONSTITUENCYDATERANGE
                inner join (
                    select
                        CONSTITUENTID,
                        cast(min(MEMBERSHIP.JOINDATE) as date) as DATEFROM,
                        cast(max(MEMBERSHIP.EXPIRATIONDATE) as date) as DATETO
                    from
                        dbo.MEMBER
                        inner join dbo.MEMBERSHIP
                            on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                        inner join dbo.MEMBERSHIPLEVELTERM
                            on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                        left join dbo.MEMBERSHIPLEVEL
                            on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                    where
                        MEMBER.ISDROPPED = 0
                        and (
                            getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
                            or MEMBERSHIPLEVELTERM.TERMCODE = 6
                        )
                        and MEMBERSHIP.STATUSCODE <> 1
                    group by
                        MEMBER.CONSTITUENTID
                ) MEMBER
                    on MEMBER.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MEMBERDEFINITIONID
                and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        CONSTITUENTID
                    from
                        dbo.MEMBER
                        inner join dbo.MEMBERSHIP
                            on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                        inner join dbo.MEMBERSHIPLEVELTERM
                            on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                        left join dbo.MEMBERSHIPLEVEL
                            on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                    where
                        MEMBER.ISDROPPED = 0
                        and (
                            getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
                            or MEMBERSHIPLEVELTERM.TERMCODE = 6
                        )
                        and MEMBERSHIP.STATUSCODE <> 1
                ) MEMBER
                    on MEMBER.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                MEMBER.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MEMBERDEFINITIONID
                and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                DATETO,
                REFRESHDATE
            )
            select
                newid(),
                MEMBER.CONSTITUENTID,
                @MEMBERDEFINITIONID,
                cast(min(MEMBERSHIP.JOINDATE) as date),
                cast(max(MEMBERSHIP.EXPIRATIONDATE) as date),
                @CURRENTDATE
            from
                dbo.MEMBER
                inner join dbo.MEMBERSHIP
                    on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                inner join dbo.MEMBERSHIPLEVELTERM
                    on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                left join dbo.MEMBERSHIPLEVEL
                    on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = MEMBER.CONSTITUENTID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MEMBERDEFINITIONID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = MEMBER.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYDATERANGE.ID is null
                and MEMBER.ISDROPPED = 0
                and (
                    getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
                    or MEMBERSHIPLEVELTERM.TERMCODE = 6
                )
                and MEMBERSHIP.STATUSCODE <> 1
            group by
                MEMBER.CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                CONSTITUENCYDATERANGE.DATEFROM = MEMBER.DATEFROM,
                CONSTITUENCYDATERANGE.DATETO = MEMBER.DATETO,
                CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
            from
                dbo.CONSTITUENCYDATERANGE
                inner join (
                    select
                        CONSTITUENTID,
                        cast(min(MEMBERSHIP.JOINDATE) as date) as DATEFROM,
                        cast(max(MEMBERSHIP.EXPIRATIONDATE) as date) as DATETO
                    from
                        dbo.MEMBER
                        inner join dbo.MEMBERSHIP
                            on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                        inner join dbo.MEMBERSHIPLEVELTERM
                            on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                        left join dbo.MEMBERSHIPLEVEL
                            on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                    where
                        MEMBER.ISDROPPED = 0
                        and (
                            getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
                            or MEMBERSHIPLEVELTERM.TERMCODE = 6
                        )
                        and MEMBERSHIP.STATUSCODE <> 1
                    group by
                        MEMBER.CONSTITUENTID
                ) MEMBER
                    on MEMBER.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        CONSTITUENTID
                    from
                        dbo.MEMBER
                        inner join dbo.MEMBERSHIP
                            on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                        inner join dbo.MEMBERSHIPLEVELTERM
                            on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
                        left join dbo.MEMBERSHIPLEVEL
                            on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                    where
                        MEMBER.ISDROPPED = 0
                        and (
                            getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
                            or MEMBERSHIPLEVELTERM.TERMCODE = 6
                        )
                        and MEMBERSHIP.STATUSCODE <> 1
                ) MEMBER
                    on MEMBER.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                MEMBER.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end