USP_CONSTITUENCYDATERANGE_REFRESHMAJORDONORCONSTITUENCY

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

    set @RECORDCOUNT = 0;

    declare @CURRENTDATE datetime = getdate();
    declare @MAJORDONORCONSTITUENCYDEFINITIONID uniqueidentifier = '1A9BFE80-604D-4B5B-8065-E751DDF6EF39';

    declare @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT money;
    declare @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT money;
    declare @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION bit;

    select top 1
        @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT = MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT,
        @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT = MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT,
        @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 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..#MAJORDONORCONSTITUENTS') is not null
        begin
            drop table #MAJORDONORCONSTITUENTS;
        end

        create table #MAJORDONORCONSTITUENTS
        (
            CONSTITUENTID uniqueidentifier
        );

        if @CONSTITUENCYREFRESHPROCESSID is null and @CONSTITUENTID is null
        begin
            if @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1
            begin
                insert into #MAJORDONORCONSTITUENTS
                (
                    CONSTITUENTID
                )
                select distinct
                    REVENUEANDRECOGNITION.CONSTITUENTID
                from (
                    select
                        REVENUE.CONSTITUENTID,
                        REVENUE.ORGAMOUNT as AMOUNT
                    from
                        dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                        inner join dbo.REVENUE_EXT with (nolock)
                            on REVENUE.ID = REVENUE_EXT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock)
                            on FTLI.FINANCIALTRANSACTIONID = REVENUE.ID
                    where
                        REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                        REVENUE.DELETEDON is null and FTLI.DELETEDON is null and
                        FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())

                    union all

                    select
                        REVENUERECOGNITION.CONSTITUENTID,
                        REVENUERECOGNITION.ORGANIZATIONAMOUNT as AMOUNT
                    from
                        dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                        inner join dbo.REVENUE_EXT with (nolock)
                            on REVENUE.ID = REVENUE_EXT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT with (nolock)
                            on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                        inner join dbo.REVENUESPLIT_EXT with (nolock)
                            on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                        inner join dbo.REVENUERECOGNITION with (nolock)
                            on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                    where
                        REVENUERECOGNITION.CONSTITUENTID is not null and -- Altru allows revenue without donors

                        REVENUE.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID and
                        REVENUE.DELETEDON is null and
                        REVENUESPLIT.DELETEDON is null and
                        REVENUESPLIT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())

                    union all

                    select
                        RECOGNITIONCREDIT.CONSTITUENTID,
                        RECOGNITIONCREDIT.ORGANIZATIONAMOUNT as AMOUNT
                    from
                        dbo.RECOGNITIONCREDIT with (nolock)
                        inner join dbo.DONORCHALLENGEENCUMBERED with (nolock)
                            on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock)
                            on FTLI.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
                    where
                        RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and
                        FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                    ) as REVENUEANDRECOGNITION
                group by
                    REVENUEANDRECOGNITION.CONSTITUENTID
                having (
                    @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT <> 0 and
                    max(REVENUEANDRECOGNITION.AMOUNT) >= @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT
                ) or (
                    @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT <> 0 and
                    sum(REVENUEANDRECOGNITION.AMOUNT) >= @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
                )
            end
            else
            begin
                insert into #MAJORDONORCONSTITUENTS
                (
                    CONSTITUENTID
                )
                select distinct
                    REVENUE.CONSTITUENTID
                from
                    dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                    inner join dbo.REVENUE_EXT with (nolock)
                        on REVENUE.ID = REVENUE_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
                        on FTLI.FINANCIALTRANSACTIONID = REVENUE.ID
                where 
                    REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                    REVENUE.DELETEDON is null and
                    FTLI.DELETEDON is null and
                    FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                group by
                    REVENUE.CONSTITUENTID
                having
                (
                    @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT <> 0 and 
                    max(FTLI.ORGAMOUNT) >= @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT
                ) or
                (
                    @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT <> 0 and 
                    sum(FTLI.ORGAMOUNT) >= @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
                )
            end

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join #MAJORDONORCONSTITUENTS as MAJORDONORCONSTITUENTS
                    on MAJORDONORCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                MAJORDONORCONSTITUENTS.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MAJORDONORCONSTITUENCYDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else if @CONSTITUENCYREFRESHPROCESSID is null
        begin
            if @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1
            begin
                insert into #MAJORDONORCONSTITUENTS
                (
                    CONSTITUENTID
                )
                select distinct
                    REVENUEANDRECOGNITION.CONSTITUENTID
                from (
                    select
                        REVENUE.CONSTITUENTID,
                        REVENUE.ORGAMOUNT as AMOUNT
                    from
                        dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                        inner join dbo.REVENUE_EXT with (nolock)
                            on REVENUE.ID = REVENUE_EXT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock)
                            on FTLI.FINANCIALTRANSACTIONID = REVENUE.ID
                    where
                        REVENUE.CONSTITUENTID = @CONSTITUENTID and
                        REVENUE.DELETEDON is null and FTLI.DELETEDON is null and
                        FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())

                    union all

                    select
                        REVENUERECOGNITION.CONSTITUENTID,
                        REVENUERECOGNITION.ORGANIZATIONAMOUNT as AMOUNT
                    from
                        dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                        inner join dbo.REVENUE_EXT with (nolock)
                            on REVENUE.ID = REVENUE_EXT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT with (nolock)
                            on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                        inner join dbo.REVENUESPLIT_EXT with (nolock)
                            on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                        inner join dbo.REVENUERECOGNITION with (nolock)
                            on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                    where
                        REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID and
                        REVENUE.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID and
                        REVENUE.DELETEDON is null and
                        REVENUESPLIT.DELETEDON is null and
                        REVENUESPLIT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())

                    union all

                    select
                        RECOGNITIONCREDIT.CONSTITUENTID,
                        RECOGNITIONCREDIT.ORGANIZATIONAMOUNT as AMOUNT
                    from
                        dbo.RECOGNITIONCREDIT with (nolock)
                        inner join dbo.DONORCHALLENGEENCUMBERED with (nolock)
                            on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock)
                            on FTLI.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
                    where
                        RECOGNITIONCREDIT.CONSTITUENTID = @CONSTITUENTID and
                        RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and
                        FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                    ) as REVENUEANDRECOGNITION
                group by
                    REVENUEANDRECOGNITION.CONSTITUENTID
                having (
                    @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT <> 0 and
                    max(REVENUEANDRECOGNITION.AMOUNT) >= @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT
                ) or (
                    @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT <> 0 and
                    sum(REVENUEANDRECOGNITION.AMOUNT) >= @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
                )
            end
            else
            begin
                insert into #MAJORDONORCONSTITUENTS
                (
                    CONSTITUENTID
                )
                select distinct
                    REVENUE.CONSTITUENTID
                from
                    dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                    inner join dbo.REVENUE_EXT with (nolock)
                        on REVENUE.ID = REVENUE_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
                        on FTLI.FINANCIALTRANSACTIONID = REVENUE.ID
                where
                    REVENUE.CONSTITUENTID = @CONSTITUENTID and
                    REVENUE.DELETEDON is null and
                    FTLI.DELETEDON is null and
                    FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                group by
                    REVENUE.CONSTITUENTID
                having
                (
                    @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT <> 0 and 
                    max(FTLI.ORGAMOUNT) >= @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT
                ) or
                (
                    @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT <> 0 and 
                    sum(FTLI.ORGAMOUNT) >= @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
                )
            end

            delete dbo.CONSTITUENCYDATERANGE
            from
                dbo.CONSTITUENCYDATERANGE
                left join #MAJORDONORCONSTITUENTS as MAJORDONORCONSTITUENTS
                    on MAJORDONORCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
            where
                CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID
                and MAJORDONORCONSTITUENTS.CONSTITUENTID is null
                and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MAJORDONORCONSTITUENCYDEFINITIONID;
            set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
        end
        else
        begin
            if @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1
            begin
                insert into #MAJORDONORCONSTITUENTS
                (
                    CONSTITUENTID
                )
                select distinct
                    REVENUEANDRECOGNITION.CONSTITUENTID
                from (
                    select
                        REVENUE.CONSTITUENTID,
                        REVENUE.ORGAMOUNT as AMOUNT
                    from
                        dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                        inner join dbo.REVENUE_EXT with (nolock)
                            on REVENUE.ID = REVENUE_EXT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock)
                            on FTLI.FINANCIALTRANSACTIONID = REVENUE.ID
                    where
                        REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                        REVENUE.DELETEDON is null and FTLI.DELETEDON is null and
                        FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())

                    union all

                    select
                        REVENUERECOGNITION.CONSTITUENTID,
                        REVENUERECOGNITION.ORGANIZATIONAMOUNT as AMOUNT
                    from
                        dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                        inner join dbo.REVENUE_EXT with (nolock)
                            on REVENUE.ID = REVENUE_EXT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT with (nolock)
                            on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                        inner join dbo.REVENUESPLIT_EXT with (nolock)
                            on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                        inner join dbo.REVENUERECOGNITION with (nolock)
                            on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                    where
                        REVENUERECOGNITION.CONSTITUENTID is not null and -- Altru allows revenue without donors

                        REVENUE.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID and
                        REVENUE.DELETEDON is null and
                        REVENUESPLIT.DELETEDON is null and
                        REVENUESPLIT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())

                    union all

                    select
                        RECOGNITIONCREDIT.CONSTITUENTID,
                        RECOGNITIONCREDIT.ORGANIZATIONAMOUNT as AMOUNT
                    from
                        dbo.RECOGNITIONCREDIT with (nolock)
                        inner join dbo.DONORCHALLENGEENCUMBERED with (nolock)
                            on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock)
                            on FTLI.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
                    where
                        RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and
                        FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                    ) as REVENUEANDRECOGNITION
                    inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                        on CONSTITUENTS.CONSTITUENTID = REVENUEANDRECOGNITION.CONSTITUENTID
                        and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
                group by
                    REVENUEANDRECOGNITION.CONSTITUENTID
                having (
                    @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT <> 0 and
                    max(REVENUEANDRECOGNITION.AMOUNT) >= @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT
                ) or (
                    @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT <> 0 and
                    sum(REVENUEANDRECOGNITION.AMOUNT) >= @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
                )
            end
            else
            begin
                insert into #MAJORDONORCONSTITUENTS
                (
                    CONSTITUENTID
                )
                select distinct
                    REVENUE.CONSTITUENTID
                from
                    dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                    inner join dbo.REVENUE_EXT with (nolock)
                        on REVENUE.ID = REVENUE_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
                        on FTLI.FINANCIALTRANSACTIONID = REVENUE.ID
                    inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
                        on CONSTITUENTS.CONSTITUENTID = REVENUE.CONSTITUENTID
                        and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
                where 
                    REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                    REVENUE.DELETEDON is null and
                    FTLI.DELETEDON is null and
                    FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                group by
                    REVENUE.CONSTITUENTID
                having
                (
                    @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT <> 0 and 
                    max(FTLI.ORGAMOUNT) >= @MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT
                ) or
                (
                    @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT <> 0 and 
                    sum(FTLI.ORGAMOUNT) >= @MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
                )
            end

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

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

        update dbo.CONSTITUENCYDATERANGE
        set
            CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE
        from
            dbo.CONSTITUENCYDATERANGE
            inner join #MAJORDONORCONSTITUENTS as MAJORDONORCONSTITUENTS
                on MAJORDONORCONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
        where
            CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @MAJORDONORCONSTITUENCYDEFINITIONID;
        set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;

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

    return 0;
end