USP_CONSTITUENCYDATERANGE_REFRESHRELATIONONLYCONSTITUENCY

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

    set @RECORDCOUNT = 0;

    declare @CURRENTDATE datetime = getdate();

    declare @RELATIONONLYDEFINITIONID uniqueidentifier = '8B6ECCFD-6829-4FBC-B092-58CA083F9322';

    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 object_id('tempdb..#RELATIONONLYCONSTITUENTS') is not null
        begin
            drop table #RELATIONONLYCONSTITUENTS;
        end

        create table #RELATIONONLYCONSTITUENTS
        (
            CONSTITUENTID uniqueidentifier,
            DATEFROM date
        );

        if @CONSTITUENCYREFRESHPROCESSID is null and @CONSTITUENTID is null
        begin
            with CONSTITUENCYCONSTITUENTS as
            (
                select distinct
                    CONSTITUENCYDATERANGE.CONSTITUENTID
                from
                    dbo.CONSTITUENCYDATERANGE
                where
                    CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID <> @RELATIONONLYDEFINITIONID
            )
            insert into #RELATIONONLYCONSTITUENTS
            (
                CONSTITUENTID,
                DATEFROM
            )
            select
                RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
                cast(min(RELATIONSHIP.DATEADDED) as date)
            from
                dbo.RELATIONSHIP
                left join CONSTITUENCYCONSTITUENTS
                    on CONSTITUENCYCONSTITUENTS.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
            where
                CONSTITUENCYCONSTITUENTS.CONSTITUENTID is null
            group by
                RELATIONSHIP.RELATIONSHIPCONSTITUENTID;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join #RELATIONONLYCONSTITUENTS as RELATIONONLYCONSTITUENTS
                    on RELATIONONLYCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                RELATIONONLYCONSTITUENTS.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @RELATIONONLYDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else if @CONSTITUENCYREFRESHPROCESSID is null
        begin
            with CONSTITUENCYCONSTITUENTS as
            (
                select distinct
                    CONSTITUENCYDATERANGE.CONSTITUENTID
                from
                    dbo.CONSTITUENCYDATERANGE
                where
                    CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID <> @RELATIONONLYDEFINITIONID
            )
            insert into #RELATIONONLYCONSTITUENTS
            (
                CONSTITUENTID,
                DATEFROM
            )
            select
                RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
                cast(min(RELATIONSHIP.DATEADDED) as date)
            from
                dbo.RELATIONSHIP
                left join CONSTITUENCYCONSTITUENTS
                    on CONSTITUENCYCONSTITUENTS.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
            where
                CONSTITUENCYCONSTITUENTS.CONSTITUENTID is null
                and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
            group by
                RELATIONSHIP.RELATIONSHIPCONSTITUENTID;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join #RELATIONONLYCONSTITUENTS as RELATIONONLYCONSTITUENTS
                    on RELATIONONLYCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                RELATIONONLYCONSTITUENTS.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @RELATIONONLYDEFINITIONID
                and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else
        begin
            with CONSTITUENCYCONSTITUENTS as
            (
                select distinct
                    CONSTITUENCYDATERANGE.CONSTITUENTID
                from
                    dbo.CONSTITUENCYDATERANGE
                where
                    CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID <> @RELATIONONLYDEFINITIONID
            )
            insert into #RELATIONONLYCONSTITUENTS
            (
                CONSTITUENTID,
                DATEFROM
            )
            select
                RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
                cast(min(RELATIONSHIP.DATEADDED) as date)
            from
                dbo.RELATIONSHIP
                left join CONSTITUENCYCONSTITUENTS
                    on CONSTITUENCYCONSTITUENTS.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                CONSTITUENCYCONSTITUENTS.CONSTITUENTID is null
            group by
                RELATIONSHIP.RELATIONSHIPCONSTITUENTID;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join #RELATIONONLYCONSTITUENTS as RELATIONONLYCONSTITUENTS
                    on RELATIONONLYCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            where
                RELATIONONLYCONSTITUENTS.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @RELATIONONLYDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end

        insert into dbo.CONSTITUENCYDATERANGE
        (
            ID,
            CONSTITUENTID,
            CONSTITUENCYDEFINITIONID,
            DATEFROM,
            REFRESHDATE
        )
        select
            newid(),
            RELATIONONLYCONSTITUENTS.CONSTITUENTID,
            @RELATIONONLYDEFINITIONID,
            RELATIONONLYCONSTITUENTS.DATEFROM,
            @CURRENTDATE
        from
            #RELATIONONLYCONSTITUENTS as RELATIONONLYCONSTITUENTS
            left join dbo.CONSTITUENCYDATERANGE
                on CONSTITUENCYDATERANGE.CONSTITUENTID = RELATIONONLYCONSTITUENTS.CONSTITUENTID
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @RELATIONONLYDEFINITIONID
        where
            CONSTITUENCYDATERANGE.ID is null;
        set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

        update dbo.CONSTITUENCYDATERANGE
        set
            CONSTITUENCYDATERANGE.DATEFROM = RELATIONONLYCONSTITUENTS.DATEFROM,
            CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
        from
            dbo.CONSTITUENCYDATERANGE
            inner join #RELATIONONLYCONSTITUENTS as RELATIONONLYCONSTITUENTS
                on RELATIONONLYCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
        where
            CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @RELATIONONLYDEFINITIONID;
        set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

        if object_id('tempdb..#RELATIONONLYCONSTITUENTS') is not null
        begin
            drop table #RELATIONONLYCONSTITUENTS;
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end