USP_REPORT_DESIGNATIONHIERARCHYSUMMARY

            CREATE procedure dbo.USP_REPORT_DESIGNATIONHIERARCHYSUMMARY
                (
                    @DESIGNATIONLEVELID uniqueidentifier = null,
                    @GOALID uniqueidentifier = null,
                    @GROUPBY tinyint = 0, -- 2 by year, else by quarter

                    @CURRENCYCODE tinyint = 0 -- 0 base, else organization

                )
            as
                declare @SQLTOEXEC nvarchar(max);

                declare
                    @STARTDATE datetime,
                    @ENDDATE datetime;

                select
                    @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(coalesce(STARTDATE,convert(datetime, '17530101'))),
                    @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(coalesce(ENDDATE,convert(datetime, '99991230')))
                from dbo.DESIGNATIONLEVELGOAL
                where ID = @GOALID;

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                set @SQLTOEXEC =
                    N'
                        with DESIGNATION_CTE as (
                        -- Find the designations we are interested in

                        select
                            DESIGNATION.ID,
                            DESIGNATION.DESIGNATIONLEVEL1ID,
                            DESIGNATION.DESIGNATIONLEVEL2ID,
                            DESIGNATIONLEVELTYPE.DESCRIPTION LEVEL1TYPE,
                            DESIGNATIONLEVELGOAL.GOAL as DESIGNATIONLEVELGOALGOAL,
                            CURRENCY.ID as CURRENCYID,
                            CURRENCY.ISO4217 as CURRENCYISO,
                            CURRENCY.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                            CURRENCY.CURRENCYSYMBOL as CURRENCYSYMBOL,
                            CURRENCY.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                            CURRENCY.ROUNDINGTYPECODE as CURRENCYROUNDINGTYPECODE
                        from 
                            dbo.DESIGNATION 
                            inner join dbo.DESIGNATIONLEVEL on DESIGNATION.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL.ID
                            inner join dbo.DESIGNATIONLEVELTYPE on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = DESIGNATIONLEVELTYPE.ID
                            inner join dbo.DESIGNATIONLEVELGOAL on DESIGNATIONLEVEL.ID = DESIGNATIONLEVELGOAL.DESIGNATIONLEVELID
                        ' + case when @CURRENCYCODE = 0
                            then N' outer apply dbo.UFN_CURRENCY_GETPROPERTIES(DESIGNATION.BASECURRENCYID) CURRENCY '
                            else N' cross join dbo.CURRENCY '
                        end + N'
                        where DESIGNATIONLEVELGOAL.ID = @GOALID
                            and DESIGNATION.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
                            and DESIGNATION.DESIGNATIONLEVEL3ID is null
                            ' + case when @CURRENCYCODE = 0
                                then N' '
                                else N' and CURRENCY.ID = @ORGANIZATIONCURRENCYID '
                            end + N'
                    )
                    , REVENUESPLIT_CTE as (
                        -- Populate a zerod record for each designation in each time period, to allow the sums later to return some value over all time periods of interest

                        select
                            DESIGNATIONLEVELQUARTERLYGOAL.YEARNAME as YEAR,
                            ' + case when @GROUPBY = 2
                                then N' 0 as QUARTER, '
                                else N' DESIGNATIONLEVELQUARTERLYGOAL.QUARTER as QUARTER, '
                            end + N'
                            DESIGNATION_CTE.ID as DESIGNATIONID,
                            0 as AMOUNTINCURRENCY,
                            0 as CATAGORY
                        from
                            DESIGNATION_CTE
                            inner join dbo.UFN_DESIGNATIONLEVELGOAL_GETQUARTERLYGOALS(@GOALID) as DESIGNATIONLEVELQUARTERLYGOAL on DESIGNATION_CTE.DESIGNATIONLEVEL1ID = DESIGNATIONLEVELQUARTERLYGOAL.DESIGNATIONLEVELID

                        union all
                        -- Find all revenue for child designations of each designation in the specified time period

                        select
                            datepart(year,
                            ' + case when @CURRENCYCODE = 0
                                then N'REVENUESPLIT'
                                else N'REVENUE'
                            end + N'
                                .DATE) as YEAR,
                            ' + case when @GROUPBY = 2
                                then N' 0 as QUARTER, '
                                else N' datepart(qq,
                                ' + case when @CURRENCYCODE = 0
                                    then N'REVENUESPLIT'
                                    else N'REVENUE'
                                end + N'
                                    .DATE) as QUARTER, '
                            end + N'
                            DESIGNATION_CTE.ID as DESIGNATIONID,
                            ' + case when @CURRENCYCODE = 0
                                then N' REVENUESPLIT.AMOUNTINCURRENCY - isnull(REFUNDSPLIT.REFUNDTOTAL, 0), '
                                else N' LI.ORGAMOUNT - isnull(REFUNDSPLIT.REFUNDTOTAL, 0) as ORGANIZATIONAMOUNT, '
                            end + N'
                            case
                                when
                                    ' + case when @CURRENCYCODE = 0
                                        then N' REVENUESPLIT.TRANSACTIONTYPECODE'
                                        else N' REVENUE.TYPECODE'
                                        /*    
                                            In the case of a sales order, use the order split applied to the gift to aid incorporating refunds.
                                            Filter out any payments with application type 'Order' so as not to double count sales order splits. 
                                        */    
                                    end + N' in (0,5) and
                                    REVENUESPLIT.APPLICATIONCODE in (0,1,3,6,7,13)
                                    then 1
                                when
                                    ' + case when @CURRENCYCODE = 0
                                        then N' REVENUESPLIT.TRANSACTIONTYPECODE'
                                        else N' REVENUE.TYPECODE'
                                    end + N' = 4
                                    then 2
                                when
                                    ' + case when @CURRENCYCODE = 0
                                        then N' REVENUESPLIT.TRANSACTIONTYPECODE'
                                        else N' REVENUE.TYPECODE'
                                    end + N' in (1,6)
                                    then 3
                            else null end as CATAGORY
                        from DESIGNATION_CTE
                    '
                --exceeded 4000 chars, restarting char concatenation to continue

                set @SQLTOEXEC = @SQLTOEXEC + N'
                        inner join
                            (
                                select
                                    D.ID as DESIGNATIONID,
                                    D2.ID as CHILDDESIGNATIONID
                                from DESIGNATION D
                                inner join dbo.DESIGNATION D2 on (D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
                                    and (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                                    and (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                                    and (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                                    and (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
                            ) as DESIGNATION on DESIGNATION.DESIGNATIONID = DESIGNATION_CTE.ID
                    ' + case when @CURRENCYCODE = 0
                        then N'
                        cross apply dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
                            DESIGNATION_CTE.CURRENCYID
                            ,@ORGANIZATIONCURRENCYID
                            ,DESIGNATION_CTE.CURRENCYDECIMALDIGITS
                            ,DESIGNATION_CTE.CURRENCYROUNDINGTYPECODE
                            ) as REVENUESPLIT
                            left outer join dbo.UFN_CREDIT_GETSPLITREFUNDAMOUNT_BULK() REFUNDSPLIT on REFUNDSPLIT.SOURCELINEITEMID = REVENUESPLIT.ID '
                        else N'
                        inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.DESIGNATIONID = DESIGNATION.CHILDDESIGNATIONID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = REVENUESPLIT.ID
                        inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = LI.FINANCIALTRANSACTIONID
                        left outer join dbo.UFN_CREDIT_GETSPLITREFUNDAMOUNT_BULK() REFUNDSPLIT on REFUNDSPLIT.SOURCELINEITEMID = REVENUESPLIT.ID '
                    end + N'
                        where
                            ' + case when @CURRENCYCODE = 0
                                then N' REVENUESPLIT.DATE'
                                else N' cast(REVENUE.DATE as datetime)'
                            end + N' between @STARTDATE and @ENDDATE
                            and
                            (
                                (
                                    ' + case when @CURRENCYCODE = 0
                                        then N' REVENUESPLIT.TRANSACTIONTYPECODE'
                                        else N' REVENUE.TYPECODE'
                                        /*    
                                            In the case of a sales order, use the order split applied to the gift to aid incorporating refunds.
                                            Filter out any payments with application type 'Order' so as not to double count sales order splits. 
                                        */    
                                    end + N' in (0,5) and
                                    REVENUESPLIT.APPLICATIONCODE in (0,1,3,6,7,13)
                                )
                                or(' + case when @CURRENCYCODE = 0
                                        then N' REVENUESPLIT.TRANSACTIONTYPECODE'
                                        else N' REVENUE.TYPECODE'
                                    end + N' in (1,4,6))
                            )
                            ' + case when @CURRENCYCODE = 0
                                then N' and REVENUESPLIT.DESIGNATIONID = DESIGNATION.CHILDDESIGNATIONID and REVENUESPLIT.SPLITDELETEDON is NULL'
                                else N' and LI.DELETEDON is NULL'
                            end + N'
                        union all
                        -- Find all writeoff for child designations of each designation in the specified time period

                        select
                            datepart(year,REVENUE.DATE) as YEAR,
                            ' + case when @GROUPBY = 2
                                then N' 0 as QUARTER, '
                                else N' datepart(qq,REVENUE.DATE) as QUARTER, '
                            end + N'
                            DESIGNATION_CTE.ID as DESIGNATIONID,
                            ' + case when @CURRENCYCODE = 0
                                then N' WRITEOFFSPLIT.AMOUNTINCURRENCY, '
                                else N' LI.ORGAMOUNT as ORGANIZATIONAMOUNT, '
                            end + N'
                            4 as CATAGORY
                        from DESIGNATION_CTE
                        inner join
                            (
                                select
                                    D.ID as DESIGNATIONID,
                                    D2.ID as CHILDDESIGNATIONID
                                from DESIGNATION D
                                inner join dbo.DESIGNATION D2 on (D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
                                    and (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                                    and (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                                    and (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                                    and (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
                            ) as DESIGNATION on DESIGNATION.DESIGNATIONID = DESIGNATION_CTE.ID
                        ' + case when @CURRENCYCODE = 0
                            then N' cross apply dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(
                                        DESIGNATION_CTE.CURRENCYID
                                        ,@ORGANIZATIONCURRENCYID
                                        ,DESIGNATION_CTE.CURRENCYDECIMALDIGITS
                                        ,DESIGNATION_CTE.CURRENCYROUNDINGTYPECODE
                                        ) as WRITEOFFSPLIT
                                    inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = WRITEOFFSPLIT.REVENUEID '
                            else N' inner join dbo.REVENUESPLIT_EXT WRITEOFFSPLIT on WRITEOFFSPLIT.DESIGNATIONID = DESIGNATION.CHILDDESIGNATIONID
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on WRITEOFFSPLIT.ID = LI.ID
                                    inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFF.ID = LI.FINANCIALTRANSACTIONID and WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null
                                    inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = WRITEOFF.PARENTID '
                        end + N'

                        where
                            cast(REVENUE.DATE as datetime) between @STARTDATE and @ENDDATE
                            and REVENUE.TYPECODE in (1,6)
                        ' + case when @CURRENCYCODE = 0
                                then N' and WRITEOFFSPLIT.DESIGNATIONID = DESIGNATION.CHILDDESIGNATIONID '
                                else N' '
                            end + N'
                    )
                    , TOTALS_CTE as (
                        select distinct
                            REVENUESPLIT_CTE.DESIGNATIONID,
                            REVENUESPLIT_CTE.YEAR,
                            REVENUESPLIT_CTE.QUARTER,
                            -- Find the sum of specific types of revenue in each time period

                            sum(case when REVENUESPLIT_CTE.CATAGORY = 1 then REVENUESPLIT_CTE.AMOUNTINCURRENCY else 0 end) over(partition by DESIGNATIONID,YEAR,QUARTER) as TOTALRECEIVED,
                            sum((case when REVENUESPLIT_CTE.CATAGORY = 3 then REVENUESPLIT_CTE.AMOUNTINCURRENCY else 0 end) - (case when REVENUESPLIT_CTE.CATAGORY = 4 then REVENUESPLIT_CTE.AMOUNTINCURRENCY else 0 end)) over(partition by DESIGNATIONID,YEAR,QUARTER) as TOTALEXPECTED,
                            sum(case when REVENUESPLIT_CTE.CATAGORY = 2 then REVENUESPLIT_CTE.AMOUNTINCURRENCY else 0 end) over(partition by DESIGNATIONID,YEAR,QUARTER) as TOTALPLANNEDGIFT,
                            -- Find the sum of specific types of revenue overall

                            sum(case when REVENUESPLIT_CTE.CATAGORY = 1 then REVENUESPLIT_CTE.AMOUNTINCURRENCY else 0 end) over(partition by DESIGNATIONID) OVERALLTOTALRECEIVED,
                            sum((case when REVENUESPLIT_CTE.CATAGORY = 3 then REVENUESPLIT_CTE.AMOUNTINCURRENCY else 0 end) - (case when REVENUESPLIT_CTE.CATAGORY = 4 then REVENUESPLIT_CTE.AMOUNTINCURRENCY else 0 end)) over(partition by DESIGNATIONID) as OVERALLTOTALEXPECTED,
                            sum(case when REVENUESPLIT_CTE.CATAGORY = 2 then REVENUESPLIT_CTE.AMOUNTINCURRENCY else 0 end) over(partition by DESIGNATIONID) OVERALLTOTALPLANNEDGIFT
                        from
                            REVENUESPLIT_CTE
                    )
                    select
                        dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION_CTE.DESIGNATIONLEVEL1ID) LEVEL1NAME,
                        dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION_CTE.DESIGNATIONLEVEL2ID) LEVEL2NAME,
                        DESIGNATION_CTE.LEVEL1TYPE,
                        DESIGNATION_CTE.DESIGNATIONLEVEL1ID,
                        DESIGNATION_CTE.DESIGNATIONLEVEL2ID,
                        DESIGNATIONGOAL.GOAL DESIGNATIONGOAL,
                        DESIGNATION_CTE.DESIGNATIONLEVELGOALGOAL OVERALLGOAL,
                        TOTALS_CTE.YEAR as DATEYEAR,
                        TOTALS_CTE.QUARTER as DATEQUARTER,
                        TOTALS_CTE.TOTALRECEIVED as TOTALRECEIVED,
                        TOTALS_CTE.TOTALEXPECTED as TOTALEXPECTED, 
                        TOTALS_CTE.TOTALPLANNEDGIFT as TOTALPLANNEDGIFT,
                        TOTALS_CTE.OVERALLTOTALRECEIVED as OVERALLTOTALRECEIVED,
                        TOTALS_CTE.OVERALLTOTALEXPECTED as OVERALLTOTALEXPECTED,
                        TOTALS_CTE.OVERALLTOTALPLANNEDGIFT as OVERALLTOTALPLANNEDGIFT,
                        DESIGNATION_CTE.CURRENCYID,
                        DESIGNATION_CTE.CURRENCYISO,
                        DESIGNATION_CTE.CURRENCYDECIMALDIGITS,
                        DESIGNATION_CTE.CURRENCYSYMBOL,
                        DESIGNATION_CTE.CURRENCYSYMBOLDISPLAYSETTINGCODE,
                        DESIGNATION_CTE.ID
                    from 
                        DESIGNATION_CTE
                        cross join TOTALS_CTE
                        left join dbo.DESIGNATIONGOAL on DESIGNATION_CTE.ID = DESIGNATIONGOAL.DESIGNATIONID and DESIGNATIONGOAL.DESIGNATIONLEVELGOALID = @GOALID
                    where DESIGNATION_CTE.ID = TOTALS_CTE.DESIGNATIONID
                    order by
                        DESIGNATION_CTE.DESIGNATIONLEVEL1ID,
                        DESIGNATION_CTE.DESIGNATIONLEVEL2ID,
                        TOTALS_CTE.YEAR,
                        TOTALS_CTE.QUARTER
                    '

                exec sp_executesql @SQLTOEXEC, 
                    N'@STARTDATE datetime,
                    @ENDDATE datetime,
                    @DESIGNATIONLEVELID uniqueidentifier,
                    @GOALID uniqueidentifier,
                    @ORGANIZATIONCURRENCYID uniqueidentifier',
                    @STARTDATE=@STARTDATE,
                    @ENDDATE=@ENDDATE,
                    @DESIGNATIONLEVELID=@DESIGNATIONLEVELID,
                    @GOALID=@GOALID,
                    @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID