UFN_CONSTITUENT_ISDONOR

CREATE function dbo.UFN_CONSTITUENT_ISDONOR(@CONSTITUENTID uniqueidentifier)
returns bit
with execute as caller
as begin
    declare @DONORCONSTITUENCYDEFTYPECODE int;
    declare @DONORCONSTITUENCYDEFLASTYEARS int;
    declare @DONORCONSTITUENCYDEFSTARTDATE datetime;
    declare @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION bit;

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

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    declare @UPPERBOUND datetime;
    declare @LOWERBOUND datetime;

    set @UPPERBOUND = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

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

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

    if @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1
    begin
        if exists(
            select top 1
                1
            from 
                dbo.FINANCIALTRANSACTION as FT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock) on FTLI.FINANCIALTRANSACTIONID = FT.ID
            where FT.CONSTITUENTID = @CONSTITUENTID
                and (FT.[DATE] between @LOWERBOUND and @UPPERBOUND)
                and FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())    
                and FT.DELETEDON is null)

      return 1;

        else if exists(
            select top 1
                1
            from 
                dbo.REVENUERECOGNITION with (nolock)
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock) on FTLI.ID = REVENUERECOGNITION.REVENUESPLITID
                inner join dbo.FINANCIALTRANSACTION as FT with (nolock) on FTLI.FINANCIALTRANSACTIONID = FT.ID
            where 
                REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID
                and (FT.[DATE] between @LOWERBOUND and @UPPERBOUND)
                and FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                and FT.DELETEDON is null)                

      return 1;

        else if exists(
            select top 1
                1
            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
                inner join dbo.FINANCIALTRANSACTION as FT with (nolock) on FTLI.FINANCIALTRANSACTIONID = FT.ID
            where 
                RECOGNITIONCREDIT.CONSTITUENTID = @CONSTITUENTID and RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1
                and (FT.[DATE] between @LOWERBOUND and @UPPERBOUND)
                and FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT()))

            return 1;
    end
    else
    begin
        if exists(
            select top 1
                1
            from 
                dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock)
                inner join dbo.FINANCIALTRANSACTION as FT  with (nolock) on FTLI.FINANCIALTRANSACTIONID = FT.ID
            where FT.CONSTITUENTID = @CONSTITUENTID
                and (FT.[DATE] between @LOWERBOUND and @UPPERBOUND)
                and FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
                and FT.DELETEDON is null
        ) 

            return 1;
    end

            return 0;
end