USP_CONSTITUENCYDATERANGE_REFRESHMATCHINGGIFTORGANIZATIONCONSTITUENCY

Parameters

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

Copy


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

    set @RECORDCOUNT = 0;

    declare @CURRENTDATE datetime = getdate();

    declare @MATCHINGGIFTORGANIZATIONDEFINITIONID uniqueidentifier = '2D04A9C5-27D0-4646-BF0F-6826E4C12632';

    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,
                REFRESHDATE,
                DATEADDED,
                DATECHANGED,
                ADDEDBYAPPLICATIONNAME,
                ADDEDBYUSERNAME,
                CHANGEDBYAPPLICATIONNAME,
                CHANGEDBYUSERNAME,
                CONSTITUENCYTS
            )
            select
                newid(),
                MATCHFINDERCONSTITUENT.ID,
                @MATCHINGGIFTORGANIZATIONDEFINITIONID,
                MATCHFINDERCONSTITUENT.DATEADDED,
                @CURRENTDATE,
                MATCHFINDERCONSTITUENT.DATEADDED,
                MATCHFINDERCONSTITUENT.DATECHANGED,
                ADDEDBY.APPLICATIONNAME,
                ADDEDBY.USERNAME,
                CHANGEDBY.APPLICATIONNAME,
                CHANGEDBY.USERNAME,
                MATCHFINDERCONSTITUENT.TS
            from
                dbo.MATCHFINDERCONSTITUENT
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = MATCHFINDERCONSTITUENT.ID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MATCHINGGIFTORGANIZATIONDEFINITIONID
                inner join dbo.CHANGEAGENT as [ADDEDBY]
                    on ADDEDBY.ID = MATCHFINDERCONSTITUENT.ADDEDBYID
                inner join dbo.CHANGEAGENT as [CHANGEDBY]
                    on CHANGEDBY.ID = MATCHFINDERCONSTITUENT.CHANGEDBYID
            where
                CONSTITUENCYDATERANGE.ID is null
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                DATEFROM = MATCHFINDERCONSTITUENT.DATEADDED,
                REFRESHDATE = @CURRENTDATE,
                CHANGEDBYAPPLICATIONNAME = CHANGEDBY.APPLICATIONNAME,
                CHANGEDBYUSERNAME = CHANGEDBY.USERNAME,
                CONSTITUENCYTS = MATCHFINDERCONSTITUENT.TS
            from
                dbo.CONSTITUENCYDATERANGE
                inner join dbo.MATCHFINDERCONSTITUENT
                    on MATCHFINDERCONSTITUENT.ID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CHANGEAGENT as [CHANGEDBY]
                    on CHANGEDBY.ID = MATCHFINDERCONSTITUENT.CHANGEDBYID
            where
                MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MATCHINGGIFTORGANIZATIONDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select
                        ID
                    from
                        dbo.MATCHFINDERCONSTITUENT
                    where
                        MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
                        and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0
                    ) CONSTITUENT
                    on CONSTITUENT.ID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENT.ID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MATCHINGGIFTORGANIZATIONDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else if @CONSTITUENCYREFRESHPROCESSID is null
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                REFRESHDATE,
                DATEADDED,
                DATECHANGED,
                ADDEDBYAPPLICATIONNAME,
                ADDEDBYUSERNAME,
                CHANGEDBYAPPLICATIONNAME,
                CHANGEDBYUSERNAME,
                CONSTITUENCYTS
            )
            select
                newid(),
                MATCHFINDERCONSTITUENT.ID,
                @MATCHINGGIFTORGANIZATIONDEFINITIONID,
                MATCHFINDERCONSTITUENT.DATEADDED,
                @CURRENTDATE,
                MATCHFINDERCONSTITUENT.DATEADDED,
                MATCHFINDERCONSTITUENT.DATECHANGED,
                ADDEDBY.APPLICATIONNAME,
                ADDEDBY.USERNAME,
                CHANGEDBY.APPLICATIONNAME,
                CHANGEDBY.USERNAME,
                MATCHFINDERCONSTITUENT.TS
            from
                dbo.MATCHFINDERCONSTITUENT
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = MATCHFINDERCONSTITUENT.ID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MATCHINGGIFTORGANIZATIONDEFINITIONID
                inner join dbo.CHANGEAGENT as [ADDEDBY]
                    on ADDEDBY.ID = MATCHFINDERCONSTITUENT.ADDEDBYID
                inner join dbo.CHANGEAGENT as [CHANGEDBY]
                    on CHANGEDBY.ID = MATCHFINDERCONSTITUENT.CHANGEDBYID
            where
                MATCHFINDERCONSTITUENT.ID = @CONSTITUENTID
                and CONSTITUENCYDATERANGE.ID is null
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                DATEFROM = MATCHFINDERCONSTITUENT.DATEADDED,
                REFRESHDATE = @CURRENTDATE,
                CHANGEDBYAPPLICATIONNAME = CHANGEDBY.APPLICATIONNAME,
                CHANGEDBYUSERNAME = CHANGEDBY.USERNAME,
                CONSTITUENCYTS = MATCHFINDERCONSTITUENT.TS
            from
                dbo.CONSTITUENCYDATERANGE
                inner join dbo.MATCHFINDERCONSTITUENT
                    on MATCHFINDERCONSTITUENT.ID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CHANGEAGENT as [CHANGEDBY]
                    on CHANGEDBY.ID = MATCHFINDERCONSTITUENT.CHANGEDBYID
            where
                MATCHFINDERCONSTITUENT.ID = @CONSTITUENTID
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MATCHINGGIFTORGANIZATIONDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select
                        ID
                    from
                        dbo.MATCHFINDERCONSTITUENT
                    where
                        MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
                        and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0
                    ) CONSTITUENT
                    on CONSTITUENT.ID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID
                and CONSTITUENT.ID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MATCHINGGIFTORGANIZATIONDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                DATEFROM,
                REFRESHDATE,
                DATEADDED,
                DATECHANGED,
                ADDEDBYAPPLICATIONNAME,
                ADDEDBYUSERNAME,
                CHANGEDBYAPPLICATIONNAME,
                CHANGEDBYUSERNAME,
                CONSTITUENCYTS
            )
            select
                newid(),
                MATCHFINDERCONSTITUENT.ID,
                @MATCHINGGIFTORGANIZATIONDEFINITIONID,
                MATCHFINDERCONSTITUENT.DATEADDED,
                @CURRENTDATE,
                MATCHFINDERCONSTITUENT.DATEADDED,
                MATCHFINDERCONSTITUENT.DATECHANGED,
                ADDEDBY.APPLICATIONNAME,
                ADDEDBY.USERNAME,
                CHANGEDBY.APPLICATIONNAME,
                CHANGEDBY.USERNAME,
                MATCHFINDERCONSTITUENT.TS
            from
                dbo.MATCHFINDERCONSTITUENT
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = MATCHFINDERCONSTITUENT.ID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MATCHINGGIFTORGANIZATIONDEFINITIONID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = MATCHFINDERCONSTITUENT.ID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
                inner join dbo.CHANGEAGENT as [ADDEDBY]
                    on ADDEDBY.ID = MATCHFINDERCONSTITUENT.ADDEDBYID
                inner join dbo.CHANGEAGENT as [CHANGEDBY]
                    on CHANGEDBY.ID = MATCHFINDERCONSTITUENT.CHANGEDBYID
            where
                CONSTITUENCYDATERANGE.ID is null
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                DATEFROM = MATCHFINDERCONSTITUENT.DATEADDED,
                REFRESHDATE = @CURRENTDATE,
                CHANGEDBYAPPLICATIONNAME = CHANGEDBY.APPLICATIONNAME,
                CHANGEDBYUSERNAME = CHANGEDBY.USERNAME,
                CONSTITUENCYTS = MATCHFINDERCONSTITUENT.TS
            from
                dbo.CONSTITUENCYDATERANGE
                inner join dbo.MATCHFINDERCONSTITUENT
                    on MATCHFINDERCONSTITUENT.ID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
                inner join dbo.CHANGEAGENT as [CHANGEDBY]
                    on CHANGEDBY.ID = MATCHFINDERCONSTITUENT.CHANGEDBYID
            where
                MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
                and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MATCHINGGIFTORGANIZATIONDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select
                        ID
                    from
                        dbo.MATCHFINDERCONSTITUENT
                    where
                        MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
                        and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0
                    ) CONSTITUENT
                    on CONSTITUENT.ID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENT.ID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MATCHINGGIFTORGANIZATIONDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end