USP_DATALIST_SALESORDER_EVENTREGISTRATIONS

CREATE procedure dbo.USP_DATALIST_SALESORDER_EVENTREGISTRATIONS
(
    @CONTEXTID uniqueidentifier
)
as
    set nocount on;

    --The top node

    select 
        SALESORDERITEM.ID,
        null as SALESORDERITEMID,
        case 
            when FT.DATE is not null then
                'Refund ' + convert(nvarchar(20), cast(FT.DATE as datetime), 1) + ', ' + NF.NAME
            else
                NF.NAME
        end REGISTRANTNAME,
        EVENT.NAME as DESCRIPTION,
        EVENT.STARTDATE,
        EVENT.STARTTIME,
        REGISTRANTAMOUNTS.QUANTITY,
        REGISTRANTAMOUNTS.AMOUNT,
        SALESORDERITEM.TOTAL as ORDERAMOUNT,
        SALESORDERITEMEVENTREGISTRATION.REGISTRANTID as REGISTRANTID,
        case 
            when FT.DATE is not null then
                1
            else
                0
        end ISREFUND
    from
        dbo.SALESORDERITEM
    inner join
        dbo.SALESORDERITEMEVENTREGISTRATION on SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
    inner join
        dbo.REGISTRANT on SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
    inner join
        dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
    left outer join
        dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
    left outer join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
    left outer join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) as NF
    outer apply (
        select
            isnull(sum(SALESORDERITEMEVENTREGISTRANTREGISTRATION.QUANTITY), 0) as QUANTITY,
            isnull(sum(SALESORDERITEMEVENTREGISTRANTREGISTRATION.AMOUNT), 0) as AMOUNT
        from dbo.SALESORDERITEMEVENTREGISTRANTREGISTRATION
        where SALESORDERITEMEVENTREGISTRANTREGISTRATION.SALESORDERITEMEVENTREGISTRATIONID = SALESORDERITEM.ID
    ) as REGISTRANTAMOUNTS
    where
        SALESORDERITEM.SALESORDERID = @CONTEXTID
    group by 
        EXT.GROUPID,
        SALESORDERITEM.ID,
        EVENT.NAME,
        EVENT.STARTDATE,
        EVENT.STARTTIME,
        SALESORDERITEM.TOTAL,
        SALESORDERITEMEVENTREGISTRATION.REGISTRANTID,
        FT.DATE,
        NF.NAME,
        REGISTRANTAMOUNTS.QUANTITY,
        REGISTRANTAMOUNTS.AMOUNT

    --The leaf nodes

    union all
    select 
        null,
        SALESORDERITEM.ID as SALESORDERITEMID,
        null as REGISTRANTNAME,
        EVENT.NAME + ' - ' + EVENTPRICE.NAME as DESCRIPTION,
        null as STARTDATE,
        null as STARTTIME,
        SALESORDERITEMEVENTREGISTRANTREGISTRATION.QUANTITY,
        SALESORDERITEMEVENTREGISTRANTREGISTRATION.AMOUNT as REGISTRATIONAMOUNT,
        null as ORDERAMOUNT,
        SALESORDERITEMEVENTREGISTRATION.REGISTRANTID as REGISTRANTID,
        case 
            when exists (select * from dbo.CREDITITEM_EXT as EXT where EXT.SALESORDERITEMID = SALESORDERITEM.ID) then
                1
            else
                0
        end ISREFUND
    from
        dbo.SALESORDERITEM
    inner join
        dbo.SALESORDERITEMEVENTREGISTRATION on SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
    inner join
        dbo.SALESORDERITEMEVENTREGISTRANTREGISTRATION on SALESORDERITEMEVENTREGISTRATION.ID = SALESORDERITEMEVENTREGISTRANTREGISTRATION.SALESORDERITEMEVENTREGISTRATIONID
    inner join
        dbo.REGISTRANT on SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
    inner join
        dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
    inner join
        dbo.EVENTPRICE on SALESORDERITEMEVENTREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
    where
        SALESORDERITEM.SALESORDERID = @CONTEXTID
    order by
        DESCRIPTION;

    return 0;