USP_REPORT_REVENUEANNUALSTATEMENT_GROUPGIFTSUM

CREATE procedure [dbo].[USP_REPORT_REVENUEANNUALSTATEMENT_GROUPGIFTSUM]
(
    @CONSTITUENTID uniqueidentifier,
    @INCLUDEGROUPS bit,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null
)
as
    set nocount on

    if @INCLUDEGROUPS = 1
    begin
        declare @CURRENTDATEEARLIESTTIME datetime;
        set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate())

        declare @STARTDATEEARLIESTTIME datetime
        set @STARTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)

        declare @ENDDATEEARLIESTTIME datetime
        set @ENDDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@ENDDATE)

        declare @BASICPROGRAMSINSTALLED bit = 0;
        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1
            set @BASICPROGRAMSINSTALLED = 1;

        if @BASICPROGRAMSINSTALLED = 1
            with REVENUE_CTE as
            (
                select
                    case c.ISORGANIZATION when 1 then
                        case c.KEYNAMEPREFIX when '' then c.KEYNAME else c.KEYNAMEPREFIX + ' ' +  c.KEYNAME end
                    else
                        case c.ISGROUP when 1 then
                            case c.DISPLAYNAME when '' then c.KEYNAME else c.DISPLAYNAME end
                        else
                            case c.FIRSTNAME when '' then '' else c.FIRSTNAME + ' ' end
                            +
                            case c.MIDDLENAME when '' then '' else LEFT(c.MIDDLENAME,1) + '. ' end
                            + 
                            c.KEYNAME
                        end
                    end as NAME,
                    coalesce(sum(R.TRANSACTIONAMOUNT), 0) as REVENUEAMOUNT,
                    coalesce(sum(REVENUE_EXT.RECEIPTAMOUNT), 0) as RECEIPTAMOUNT,
                    CURRENCY.ISO4217 [ISOCURRENCYCODE],
                    CURRENCY.CURRENCYSYMBOL,
                    CURRENCY.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    CURRENCY.DECIMALDIGITS,
                    [R].[ID]
                from dbo.GROUPMEMBER GM
                inner join dbo.CONSTITUENT C with (nolock) on GM.GROUPID = C.ID
                inner join dbo.FINANCIALTRANSACTION R with (nolock) on R.CONSTITUENTID = C.ID
                inner join dbo.REVENUE_EXT on R.ID = REVENUE_EXT.ID
                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                left join dbo.CURRENCY on R.TRANSACTIONCURRENCYID = CURRENCY.ID
                where
                    GM.MEMBERID = @CONSTITUENTID and
                    (@STARTDATEEARLIESTTIME is null or R.DATE >= @STARTDATEEARLIESTTIME) and
                    (@ENDDATEEARLIESTTIME is null or R.DATE <= @ENDDATEEARLIESTTIME) and
                    R.TYPECODE in (0,7) and
                    -- Verify user is currently a member of the group

                    ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME)) or 
                    (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) or 
                    (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                    and R.DELETEDON is null
                group by C.ID, C.ISORGANIZATION, C.KEYNAMEPREFIX, C.KEYNAME, C.ISGROUP, C.DISPLAYNAME, C.FIRSTNAME, C.MIDDLENAME, CURRENCY.ISO4217, CURRENCY.CURRENCYSYMBOL, CURRENCY.SYMBOLDISPLAYSETTINGCODE, CURRENCY.DECIMALDIGITS, [R].[ID]
            )
            select
                [NAME],
                [REVENUEAMOUNT],
                [RECEIPTAMOUNT],
                [ISOCURRENCYCODE],
                [CURRENCYSYMBOL],
                [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                [DECIMALDIGITS]
            from REVENUE_CTE

            union all

            select
                case CONSTITUENT.ISORGANIZATION when 1 then
                    case CONSTITUENT.KEYNAMEPREFIX when '' then CONSTITUENT.KEYNAME else CONSTITUENT.KEYNAMEPREFIX + ' ' +  CONSTITUENT.KEYNAME end
                else
                    case CONSTITUENT.ISGROUP when 1 then
                        case CONSTITUENT.DISPLAYNAME when '' then CONSTITUENT.KEYNAME else CONSTITUENT.DISPLAYNAME end
                    else
                        case CONSTITUENT.FIRSTNAME when '' then '' else CONSTITUENT.FIRSTNAME + ' ' end
                        +
                        case CONSTITUENT.MIDDLENAME when '' then '' else LEFT(CONSTITUENT.MIDDLENAME,1) + '. ' end
                        + 
                        CONSTITUENT.KEYNAME
                    end
                end as NAME,
                -1 * [CREDITPAYMENT].[AMOUNT] as [REVENUEAMOUNT],
                -1 * dbo.UFN_CREDITPAYMENT_GETRECEIPTAMOUNT([CREDITPAYMENT].[ID]) as [RECEIPTAMOUNT],
                [REVENUE].[ISOCURRENCYCODE],
                [REVENUE].[CURRENCYSYMBOL],
                [REVENUE].[CURRENCYSYMBOLDISPLAYSETTINGCODE],
                [REVENUE].[DECIMALDIGITS]
            from dbo.[CREDITPAYMENT]
            inner join dbo.[FINANCIALTRANSACTION] on [CREDITPAYMENT].[CREDITID] = [FINANCIALTRANSACTION].[ID]
            inner join dbo.[CREDIT_EXT] on FINANCIALTRANSACTION.ID = CREDIT_EXT.ID
            left join dbo.[FINANCIALTRANSACTIONLINEITEM] on [CREDITPAYMENT].[REVENUESPLITID] = [FINANCIALTRANSACTIONLINEITEM].[ID] and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            inner join REVENUE_CTE [REVENUE] on ([REVENUE].[ID] = [CREDITPAYMENT].[REVENUEID] or [REVENUE].[ID] = [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID])
            left join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
            where FINANCIALTRANSACTION.DELETEDON is null

            order by NAME
        else
            with REVENUE_CTE as
            (
                select
                    case c.ISORGANIZATION when 1 then
                        case c.KEYNAMEPREFIX when '' then c.KEYNAME else c.KEYNAMEPREFIX + ' ' +  c.KEYNAME end
                    else
                        case c.ISGROUP when 1 then
                            case c.DISPLAYNAME when '' then c.KEYNAME else c.DISPLAYNAME end
                        else
                            case c.FIRSTNAME when '' then '' else c.FIRSTNAME + ' ' end
                            +
                            case c.MIDDLENAME when '' then '' else LEFT(c.MIDDLENAME,1) + '. ' end
                            + 
                            c.KEYNAME
                        end
                    end as NAME,
                    coalesce(sum(R.TRANSACTIONAMOUNT), 0) as REVENUEAMOUNT,
                    coalesce(sum(REVENUE_EXT.RECEIPTAMOUNT), 0) as RECEIPTAMOUNT,
                    CURRENCY.ISO4217 [ISOCURRENCYCODE],
                    CURRENCY.CURRENCYSYMBOL,
                    CURRENCY.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    CURRENCY.DECIMALDIGITS,
                    [R].[ID]
                from dbo.GROUPMEMBER GM
                inner join dbo.CONSTITUENT C with (nolock) on GM.GROUPID = C.ID
                inner join dbo.FINANCIALTRANSACTION R with (nolock) on R.CONSTITUENTID = C.ID
                inner join dbo.REVENUE_EXT on R.ID = REVENUE_EXT.ID
                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                left join dbo.CURRENCY on R.TRANSACTIONCURRENCYID = CURRENCY.ID
                where
                    GM.MEMBERID = @CONSTITUENTID and
                    (@STARTDATEEARLIESTTIME is null or R.DATE >= @STARTDATEEARLIESTTIME) and
                    (@ENDDATEEARLIESTTIME is null or R.DATE <= @ENDDATEEARLIESTTIME) and
                    R.TYPECODE in (0,7) and
                    -- Verify user is currently a member of the group

                    ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME)) or 
                    (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) or 
                    (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                    and R.DELETEDON is null
                group by C.ID, C.ISORGANIZATION, C.KEYNAMEPREFIX, C.KEYNAME, C.ISGROUP, C.DISPLAYNAME, C.FIRSTNAME, C.MIDDLENAME, CURRENCY.ISO4217, CURRENCY.CURRENCYSYMBOL, CURRENCY.SYMBOLDISPLAYSETTINGCODE, CURRENCY.DECIMALDIGITS, [R].[ID]
            )
            select
                [NAME],
                [REVENUEAMOUNT],
                [RECEIPTAMOUNT],
                [ISOCURRENCYCODE],
                [CURRENCYSYMBOL],
                [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                [DECIMALDIGITS]
            from REVENUE_CTE
            order by NAME
    end