USP_CONSTITUENCYDATERANGE_REFRESHVENDORCONSTITUENCY

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

    set @RECORDCOUNT = 0;

    declare @CURRENTDATE datetime = getdate();

    declare @VENDORDEFINITIONID uniqueidentifier = '5435C96D-8617-46C3-9A62-5AFF08451A53';

    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,
                REFRESHDATE
            )
            select
                newid(),
                VENDOR.VENDORID,
                @VENDORDEFINITIONID,
                @CURRENTDATE
            from (
                select distinct
                    EVENTEXPENSE.VENDORID
                from
                    dbo.EVENTEXPENSE
                where
                    EVENTEXPENSE.VENDORID is not null
                ) VENDOR
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = VENDOR.VENDORID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @VENDORDEFINITIONID
            where
                CONSTITUENCYDATERANGE.ID is null;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
            from (
                select distinct
                    EVENTEXPENSE.VENDORID
                from
                    dbo.EVENTEXPENSE
                where
                    EVENTEXPENSE.VENDORID is not null
                ) VENDOR
                inner join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = VENDOR.VENDORID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @VENDORDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        EVENTEXPENSE.VENDORID
                    from
                        dbo.EVENTEXPENSE
                    where
                        EVENTEXPENSE.VENDORID is not null
                    ) VENDOR
                    on VENDOR.VENDORID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                VENDOR.VENDORID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @VENDORDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else if @CONSTITUENCYREFRESHPROCESSID is null
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                REFRESHDATE
            )
            select
                newid(),
                VENDOR.VENDORID,
                @VENDORDEFINITIONID,
                @CURRENTDATE
            from (
                select distinct
                    EVENTEXPENSE.VENDORID
                from
                    dbo.EVENTEXPENSE
                where
                    EVENTEXPENSE.VENDORID is not null
                ) VENDOR
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = VENDOR.VENDORID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @VENDORDEFINITIONID
            where
                CONSTITUENCYDATERANGE.ID is null
                and VENDOR.VENDORID = @CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
            from (
                select distinct
                    EVENTEXPENSE.VENDORID
                from
                    dbo.EVENTEXPENSE
                where
                    EVENTEXPENSE.VENDORID is not null
                ) VENDOR
                inner join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = VENDOR.VENDORID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @VENDORDEFINITIONID
                and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        EVENTEXPENSE.VENDORID
                    from
                        dbo.EVENTEXPENSE
                    where
                        EVENTEXPENSE.VENDORID is not null
                    ) VENDOR
                    on VENDOR.VENDORID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                VENDOR.VENDORID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @VENDORDEFINITIONID
                and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else
        begin
            insert into dbo.CONSTITUENCYDATERANGE
            (
                ID,
                CONSTITUENTID,
                CONSTITUENCYDEFINITIONID,
                REFRESHDATE
            )
            select
                newid(),
                VENDOR.VENDORID,
                @VENDORDEFINITIONID,
                @CURRENTDATE
            from (
                select distinct
                    EVENTEXPENSE.VENDORID
                from
                    dbo.EVENTEXPENSE
                where
                    EVENTEXPENSE.VENDORID is not null
                ) VENDOR
                left join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = VENDOR.VENDORID
                    and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @VENDORDEFINITIONID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = VENDOR.VENDORID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYDATERANGE.ID is null;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            update dbo.CONSTITUENCYDATERANGE
            set
                CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
            from (
                select distinct
                    EVENTEXPENSE.VENDORID
                from
                    dbo.EVENTEXPENSE
                where
                    EVENTEXPENSE.VENDORID is not null
                ) VENDOR
                inner join dbo.CONSTITUENCYDATERANGE
                    on CONSTITUENCYDATERANGE.CONSTITUENTID = VENDOR.VENDORID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = VENDOR.VENDORID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @VENDORDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join (
                    select distinct
                        EVENTEXPENSE.VENDORID
                    from
                        dbo.EVENTEXPENSE
                    where
                        EVENTEXPENSE.VENDORID is not null
                    ) VENDOR
                    on VENDOR.VENDORID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                VENDOR.VENDORID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @VENDORDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch;
end