USP_CONSTITUENCYDATERANGE_REFRESHDONORCONSTITUENCY

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

    set @RECORDCOUNT = 0;

    declare @CURRENTDATE datetime = getdate();
    declare @DONORCONSTITUENCYDEFINITIONID uniqueidentifier = '70165682-4324-46EC-9439-83FC0CC67E7F';

    declare @CONSIDERRECOGNITION bit;
    declare @DONORCONSTITUENCYLOWERDATEBOUND datetime;
    declare @DONORCONSTITUENCYDEFTYPECODE int;
    declare @DONORCONSTITUENCYDEFLASTYEARS int;
    declare @DONORCONSTITUENCYDEFSTARTDATE datetime;

    select
        @DONORCONSTITUENCYDEFTYPECODE = DONORCONSTITUENCYDEFTYPECODE,
        @DONORCONSTITUENCYDEFLASTYEARS = DONORCONSTITUENCYDEFLASTYEARS,
        @DONORCONSTITUENCYDEFSTARTDATE = DONORCONSTITUENCYDEFSTARTDATE
    from
        dbo.INSTALLATIONINFO;

    if @DONORCONSTITUENCYDEFTYPECODE = 0
    begin
        declare @SQLMINDATE datetime = '17530101';

        if datediff(year, @SQLMINDATE, dbo.UFN_DATE_GETLATESTTIME(getdate())) >= @DONORCONSTITUENCYDEFLASTYEARS
            set @DONORCONSTITUENCYLOWERDATEBOUND = dateadd(year, 0 - @DONORCONSTITUENCYDEFLASTYEARS, dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE));
        else
            set @DONORCONSTITUENCYLOWERDATEBOUND = @SQLMINDATE;
    end
    else
        set @DONORCONSTITUENCYLOWERDATEBOUND = dbo.UFN_DATE_GETEARLIESTTIME(@DONORCONSTITUENCYDEFSTARTDATE);

    select top 1 
        @CONSIDERRECOGNITION = MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION
    from 
        dbo.CONSTITUENCYCRITERIASPOUSE;

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

        create table #DONORCONSTITUENTS
        (
            CONSTITUENTID uniqueidentifier,
            DATEFROM datetime,
            DATETO datetime
        );

        if @CONSTITUENCYREFRESHPROCESSID is null and @CONSTITUENTID is null
        begin
            insert into #DONORCONSTITUENTS
            (
                CONSTITUENTID,
                DATEFROM,
                DATETO
            )
            select
                DATA.CONSTITUENTID,
                min(DATA.REVENUEDATE) as DATEFROM,
                case 
                    when max(DATA.REVENUEDATE) < @DONORCONSTITUENCYLOWERDATEBOUND
                        then case 
                                when @DONORCONSTITUENCYDEFTYPECODE = 0
                                    then dateadd(year, @DONORCONSTITUENCYDEFLASTYEARS, max(DATA.REVENUEDATE))
                                else max(DATA.REVENUEDATE)
                                end
                    else null
                end as DATETO
            from (
                select
                    [FT].[DATE] as REVENUEDATE,
                    FT.CONSTITUENTID
                from
                    /* JamesWill WI122678 Make the donor constituency match up with UFN_CONSTITUENT_ISDONOR by using UFN_REVENUELIFECYCLE_GETVALIDREVENUE*/
                    dbo.FINANCIALTRANSACTION as FT with (nolock)
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
                        on FT.ID = FTLI.FINANCIALTRANSACTIONID
                where
                    FTLI.ID in (
                        select ID
                        from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER
                    )

                union all

                select
                    [FT].[DATE] as REVENUEDATE,
                    REVENUERECOGNITION.CONSTITUENTID
                from
                    /* JamesWill WI122678 Make the donor constituency match up with UFN_CONSTITUENT_ISDONOR by using UFN_REVENUELIFECYCLE_GETVALIDREVENUE*/
                    dbo.REVENUERECOGNITION
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
                        on FTLI.ID = REVENUERECOGNITION.REVENUESPLITID
                    inner join dbo.FINANCIALTRANSACTION as FT with (nolock)
                        on FT.ID = FTLI.FINANCIALTRANSACTIONID
                where
                    FTLI.ID in (
                        select ID
                        from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER
                        )
                    and @CONSIDERRECOGNITION = 1
                ) DATA
            where
                DATA.CONSTITUENTID is not null
            group by DATA.CONSTITUENTID
            having min(DATA.REVENUEDATE) is not null;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join #DONORCONSTITUENTS as DONORCONSTITUENTS
                    on DONORCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                DONORCONSTITUENTS.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @DONORCONSTITUENCYDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else if @CONSTITUENCYREFRESHPROCESSID is null
        begin
            insert into #DONORCONSTITUENTS
            (
                CONSTITUENTID,
                DATEFROM,
                DATETO
            )
            select
                DATA.CONSTITUENTID,
                min(DATA.REVENUEDATE) as DATEFROM,
                case 
                    when max(DATA.REVENUEDATE) < @DONORCONSTITUENCYLOWERDATEBOUND
                        then case 
                                when @DONORCONSTITUENCYDEFTYPECODE = 0
                                    then dateadd(year, @DONORCONSTITUENCYDEFLASTYEARS, max(DATA.REVENUEDATE))
                                else max(DATA.REVENUEDATE)
                                end
                    else null
                end as DATETO
            from (
                select
                    [FT].[DATE] as REVENUEDATE,
                    FT.CONSTITUENTID
                from
                    /* JamesWill WI122678 Make the donor constituency match up with UFN_CONSTITUENT_ISDONOR by using UFN_REVENUELIFECYCLE_GETVALIDREVENUE*/
                    dbo.FINANCIALTRANSACTION as FT with (nolock)
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
                        on FT.ID = FTLI.FINANCIALTRANSACTIONID
                where
                    FT.CONSTITUENTID = @CONSTITUENTID
                    and FTLI.ID in (
                        select ID
                        from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER
                    )

                union all

                select
                    [FT].[DATE] as REVENUEDATE,
                    REVENUERECOGNITION.CONSTITUENTID
                from
                    /* JamesWill WI122678 Make the donor constituency match up with UFN_CONSTITUENT_ISDONOR by using UFN_REVENUELIFECYCLE_GETVALIDREVENUE*/
                    dbo.REVENUERECOGNITION
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
                        on FTLI.ID = REVENUERECOGNITION.REVENUESPLITID
                    inner join dbo.FINANCIALTRANSACTION as FT with (nolock)
                        on FT.ID = FTLI.FINANCIALTRANSACTIONID
                where
                    FT.CONSTITUENTID = @CONSTITUENTID
                    and FTLI.ID in (
                        select ID
                        from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER
                        )
                    and @CONSIDERRECOGNITION = 1
                ) DATA
            group by DATA.CONSTITUENTID
            having min(DATA.REVENUEDATE) is not null;

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join #DONORCONSTITUENTS as DONORCONSTITUENTS
                    on DONORCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID
                and DONORCONSTITUENTS.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @DONORCONSTITUENCYDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else
        begin
            insert into #DONORCONSTITUENTS
            (
                CONSTITUENTID,
                DATEFROM,
                DATETO
            )
            select
                DATA.CONSTITUENTID,
                min(DATA.REVENUEDATE) as DATEFROM,
                case 
                    when max(DATA.REVENUEDATE) < @DONORCONSTITUENCYLOWERDATEBOUND
                        then case 
                                when @DONORCONSTITUENCYDEFTYPECODE = 0
                                    then dateadd(year, @DONORCONSTITUENCYDEFLASTYEARS, max(DATA.REVENUEDATE))
                                else max(DATA.REVENUEDATE)
                                end
                    else null
                end as DATETO
            from (
                select
                    [FT].[DATE] as REVENUEDATE,
                    FT.CONSTITUENTID
                from
                    /* JamesWill WI122678 Make the donor constituency match up with UFN_CONSTITUENT_ISDONOR by using UFN_REVENUELIFECYCLE_GETVALIDREVENUE*/
                    dbo.FINANCIALTRANSACTION as FT with (nolock)
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
                        on FT.ID = FTLI.FINANCIALTRANSACTIONID
                where
                    FTLI.ID in (
                        select ID
                        from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER
                    )

                union all

                select
                    [FT].[DATE] as REVENUEDATE,
                    REVENUERECOGNITION.CONSTITUENTID
                from
                    /* JamesWill WI122678 Make the donor constituency match up with UFN_CONSTITUENT_ISDONOR by using UFN_REVENUELIFECYCLE_GETVALIDREVENUE*/
                    dbo.REVENUERECOGNITION
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
                        on FTLI.ID = REVENUERECOGNITION.REVENUESPLITID
                    inner join dbo.FINANCIALTRANSACTION as FT with (nolock)
                        on FT.ID = FTLI.FINANCIALTRANSACTIONID
                where
                    FTLI.ID in (
                        select ID
                        from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER
                        )
                    and @CONSIDERRECOGNITION = 1
                ) DATA
                inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                    on CONSTITUENTS.CONSTITUENTID = DATA.CONSTITUENTID
                    and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
            group by DATA.CONSTITUENTID
            having min(DATA.REVENUEDATE) is not null;

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

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

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

        drop table #DONORCONSTITUENTS;
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end