USP_CONSTITUENCYDATERANGE_REFRESHCOMMITTEEMEMBERCONSTITUENCY

Parameters

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

Copy


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

    set @RECORDCOUNT = 0;

    declare @CURRENTDATE datetime = getdate();

    declare @COMMITTEEMEMBERDEFINITIONID uniqueidentifier = '093A3D4F-2974-447F-AD92-870EB4A04593';

    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(),
                GROUPMEMBER.MEMBERID,
                @COMMITTEEMEMBERDEFINITIONID,
                min(GROUPMEMBERDATERANGE.DATEFROM),
                case
                    when min(coalesce(convert(nchar(8), GROUPMEMBERDATERANGE.DATETO, 112), '00000000')) = '00000000' then
                        null
                    else
                        max(GROUPMEMBERDATERANGE.DATETO)
                end,
                @CURRENTDATE
            from
                dbo.GROUPMEMBER
                inner join dbo.COMMITTEEDATERANGE
                    on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
                    and (COMMITTEEDATERANGE.DATEFROM <= convert(date, getdate()) or COMMITTEEDATERANGE.DATEFROM is null)
                    and (COMMITTEEDATERANGE.DATETO >= convert(date, getdate()) or COMMITTEEDATERANGE.DATETO is null)
                left join dbo.GROUPMEMBERDATERANGE
                    on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = GROUPMEMBER.MEMBERID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @COMMITTEEMEMBERDEFINITIONID
            where
                CONSTITUENCYDATERANGE.ID is null
            group by
                GROUPMEMBER.MEMBERID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                DATEFROM = COMMITTEEMEMBER.DATEFROM,
                DATETO = COMMITTEEMEMBER.DATETO,
                REFRESHDATE = @CURRENTDATE
            from
                dbo.CONSTITUENCYDATERANGE
                inner join (
                    select
                        GROUPMEMBER.MEMBERID,
                        min(GROUPMEMBERDATERANGE.DATEFROM) as DATEFROM,
                        case
                            when min(coalesce(convert(nchar(8), GROUPMEMBERDATERANGE.DATETO, 112), '00000000')) = '00000000' then
                                null
                            else
                                max(GROUPMEMBERDATERANGE.DATETO)
                        end as DATETO
                    from
                        dbo.GROUPMEMBER
                        inner join dbo.COMMITTEEDATERANGE
                            on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
                            and (COMMITTEEDATERANGE.DATEFROM <= convert(date, getdate()) or COMMITTEEDATERANGE.DATEFROM is null)
                            and (COMMITTEEDATERANGE.DATETO >= convert(date, getdate()) or COMMITTEEDATERANGE.DATETO is null)
                        left join dbo.GROUPMEMBERDATERANGE
                            on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                    group by
                        GROUPMEMBER.MEMBERID
                    ) COMMITTEEMEMBER
                    on COMMITTEEMEMBER.MEMBERID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @COMMITTEEMEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        GROUPMEMBER.MEMBERID
                    from
                        dbo.GROUPMEMBER
                        inner join dbo.COMMITTEEDATERANGE
                            on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
                            and (COMMITTEEDATERANGE.DATEFROM <= convert(date, getdate()) or COMMITTEEDATERANGE.DATEFROM is null)
                            and (COMMITTEEDATERANGE.DATETO >= convert(date, getdate()) or COMMITTEEDATERANGE.DATETO is null)
                        left join dbo.GROUPMEMBERDATERANGE
                            on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                    ) COMMITTEEMEMBER
                    on COMMITTEEMEMBER.MEMBERID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                COMMITTEEMEMBER.MEMBERID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @COMMITTEEMEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else if @CONSTITUENCYREFRESHPROCESSID is null
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                DATETO,
                REFRESHDATE
            )
            select
                newid(),
                GROUPMEMBER.MEMBERID,
                @COMMITTEEMEMBERDEFINITIONID,
                min(GROUPMEMBERDATERANGE.DATEFROM),
                case
                    when min(coalesce(convert(nchar(8), GROUPMEMBERDATERANGE.DATETO, 112), '00000000')) = '00000000' then
                        null
                    else
                        max(GROUPMEMBERDATERANGE.DATETO)
                end,
                @CURRENTDATE
            from
                dbo.GROUPMEMBER
                inner join dbo.COMMITTEEDATERANGE
                    on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
                    and (COMMITTEEDATERANGE.DATEFROM <= convert(date, getdate()) or COMMITTEEDATERANGE.DATEFROM is null)
                    and (COMMITTEEDATERANGE.DATETO >= convert(date, getdate()) or COMMITTEEDATERANGE.DATETO is null)
                left join dbo.GROUPMEMBERDATERANGE
                    on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = GROUPMEMBER.MEMBERID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @COMMITTEEMEMBERDEFINITIONID
            where
                CONSTITUENCYDATERANGE.ID is null
                and GROUPMEMBER.MEMBERID = @CONSTITUENTID
            group by
                GROUPMEMBER.MEMBERID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                DATEFROM = COMMITTEEMEMBER.DATEFROM,
                DATETO = COMMITTEEMEMBER.DATETO,
                REFRESHDATE = @CURRENTDATE
            from
                dbo.CONSTITUENCYDATERANGE
                inner join (
                    select
                        GROUPMEMBER.MEMBERID,
                        min(GROUPMEMBERDATERANGE.DATEFROM) as DATEFROM,
                        case
                            when min(coalesce(convert(nchar(8), GROUPMEMBERDATERANGE.DATETO, 112), '00000000')) = '00000000' then
                                null
                            else
                                max(GROUPMEMBERDATERANGE.DATETO)
                        end as DATETO
                    from
                        dbo.GROUPMEMBER
                        inner join dbo.COMMITTEEDATERANGE
                            on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
                            and (COMMITTEEDATERANGE.DATEFROM <= convert(date, getdate()) or COMMITTEEDATERANGE.DATEFROM is null)
                            and (COMMITTEEDATERANGE.DATETO >= convert(date, getdate()) or COMMITTEEDATERANGE.DATETO is null)
                        left join dbo.GROUPMEMBERDATERANGE
                            on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                    group by
                        GROUPMEMBER.MEMBERID
                    ) COMMITTEEMEMBER
                    on COMMITTEEMEMBER.MEMBERID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @COMMITTEEMEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        GROUPMEMBER.MEMBERID
                    from
                        dbo.GROUPMEMBER
                        inner join dbo.COMMITTEEDATERANGE
                            on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
                            and (COMMITTEEDATERANGE.DATEFROM <= convert(date, getdate()) or COMMITTEEDATERANGE.DATEFROM is null)
                            and (COMMITTEEDATERANGE.DATETO >= convert(date, getdate()) or COMMITTEEDATERANGE.DATETO is null)
                        left join dbo.GROUPMEMBERDATERANGE
                            on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                    ) COMMITTEEMEMBER
                    on COMMITTEEMEMBER.MEMBERID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                COMMITTEEMEMBER.MEMBERID is null
                and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @COMMITTEEMEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                DATETO,
                REFRESHDATE
            )
            select
                newid(),
                GROUPMEMBER.MEMBERID,
                @COMMITTEEMEMBERDEFINITIONID,
                min(GROUPMEMBERDATERANGE.DATEFROM),
                case
                    when min(coalesce(convert(nchar(8), GROUPMEMBERDATERANGE.DATETO, 112), '00000000')) = '00000000' then
                        null
                    else
                        max(GROUPMEMBERDATERANGE.DATETO)
                end,
                @CURRENTDATE
            from
                dbo.GROUPMEMBER
                inner join dbo.COMMITTEEDATERANGE
                    on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
                    and (COMMITTEEDATERANGE.DATEFROM <= convert(date, getdate()) or COMMITTEEDATERANGE.DATEFROM is null)
                    and (COMMITTEEDATERANGE.DATETO >= convert(date, getdate()) or COMMITTEEDATERANGE.DATETO is null)
                left join dbo.GROUPMEMBERDATERANGE
                    on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = GROUPMEMBER.MEMBERID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @COMMITTEEMEMBERDEFINITIONID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = GROUPMEMBER.MEMBERID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYDATERANGE.ID is null
            group by
                GROUPMEMBER.MEMBERID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                DATEFROM = COMMITTEEMEMBER.DATEFROM,
                DATETO = COMMITTEEMEMBER.DATETO,
                REFRESHDATE = @CURRENTDATE
            from
                dbo.CONSTITUENCYDATERANGE
                inner join (
                    select
                        GROUPMEMBER.MEMBERID,
                        min(GROUPMEMBERDATERANGE.DATEFROM) as DATEFROM,
                        case
                            when min(coalesce(convert(nchar(8), GROUPMEMBERDATERANGE.DATETO, 112), '00000000')) = '00000000' then
                                null
                            else
                                max(GROUPMEMBERDATERANGE.DATETO)
                        end as DATETO
                    from
                        dbo.GROUPMEMBER
                        inner join dbo.COMMITTEEDATERANGE
                            on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
                            and (COMMITTEEDATERANGE.DATEFROM <= convert(date, getdate()) or COMMITTEEDATERANGE.DATEFROM is null)
                            and (COMMITTEEDATERANGE.DATETO >= convert(date, getdate()) or COMMITTEEDATERANGE.DATETO is null)
                        left join dbo.GROUPMEMBERDATERANGE
                            on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                    group by
                        GROUPMEMBER.MEMBERID
                    ) COMMITTEEMEMBER
                    on COMMITTEEMEMBER.MEMBERID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @COMMITTEEMEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        GROUPMEMBER.MEMBERID
                    from
                        dbo.GROUPMEMBER
                        inner join dbo.COMMITTEEDATERANGE
                            on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
                            and (COMMITTEEDATERANGE.DATEFROM <= convert(date, getdate()) or COMMITTEEDATERANGE.DATEFROM is null)
                            and (COMMITTEEDATERANGE.DATETO >= convert(date, getdate()) or COMMITTEEDATERANGE.DATETO is null)
                        left join dbo.GROUPMEMBERDATERANGE
                            on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                    ) COMMITTEEMEMBER
                    on COMMITTEEMEMBER.MEMBERID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                COMMITTEEMEMBER.MEMBERID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @COMMITTEEMEMBERDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end