USP_DATALIST_CREDIT_TICKETS2

CREATE procedure dbo.USP_DATALIST_CREDIT_TICKETS2
(
    @CREDITID uniqueidentifier
)
as
    set nocount on;

    with COMBOGROUPNUMBERS_CTE as (
        select TICKETID, TICKETGROUPNUMBER
        from dbo.UFN_SALESORDER_GETDETERMINISTICCOMBOTICKETNUMBERS((select SALESORDERID from dbo.CREDIT_EXT where ID = @CREDITID))
    )

    -- Pre-2014 S1 refunded tickets (aggregated)

    select
        SALESORDERITEM.ID,
        SALESORDERITEM.[DESCRIPTION] + ' (' + convert(varchar, convert(int, LI.QUANTITY)) + ')' [DESCRIPTION],
        [EVENT].STARTDATE,
        [EVENT].STARTTIME,
        SALESORDERITEM.PRICE * LI.QUANTITY as PRICE,
        EXT.DISCOUNTS + isnull(ORDERLEVELDISCOUNTS.AMOUNT, 0) as DISCOUNTS,  -- EXT.DISCOUNTS only includes item-level discounts

        LI.BASEAMOUNT - isnull(ORDERLEVELDISCOUNTS.AMOUNT, 0) AMOUNTPAID,  -- Hacky. Old-style refunds forced you to refund the whole discounted item, so PAID and REFUNDED are the same.

        LI.BASEAMOUNT - isnull(ORDERLEVELDISCOUNTS.AMOUNT, 0) as AMOUNTREFUNDED,
        SALESORDERITEM.SALESORDERID
    from dbo.FINANCIALTRANSACTIONLINEITEM LI
    inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
    inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
    left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID
    left join dbo.EVENT on EVENT.ID = SALESORDERITEMTICKET.EVENTID
    outer apply (
        select sum(AMOUNT) AMOUNT
        from dbo.SALESORDERITEMORDERDISCOUNTDETAIL ODD
        where ODD.SALESORDERITEMID = EXT.SALESORDERITEMID
    ) ORDERLEVELDISCOUNTS
    where 
        LI.FINANCIALTRANSACTIONID = @CREDITID
        and SALESORDERITEMTICKETCOMBINATION.ID is null
        and EXT.SALESORDERITEMIZEDITEMID is null

    union all

    -- Pre-2014 S1 refunded combo tickets (aggregated)

    select
        EXT.SALESORDERITEMID as ID,
        COMBINATION.NAME + ' - ' + PRICETYPECODE.[DESCRIPTION] + ' - ' +
        stuff((
                select
                    ';' + case
                            when ITEMTICKET.EVENTID is null then P.NAME
                            else E.NAME + ' ' + convert(nvarchar(8),e.STARTDATE, 1) + ' ' + dbo.UFN_HOURMINUTE_DISPLAYTIME(e.STARTTIME)
                    end NAME
                from dbo.SALESORDERITEMTICKET ITEMTICKET
                inner join dbo.SALESORDERITEMTICKETCOMBINATION TICKETCOMBINATION on ITEMTICKET.ID = TICKETCOMBINATION.ID
                left join dbo.PROGRAM P on ITEMTICKET.PROGRAMID = P.ID
                left join dbo.[EVENT] E on ITEMTICKET.EVENTID = E.ID
                where
                    TICKETCOMBINATION.TICKETCOMBINATIONID = SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID
                    and ITEMTICKET.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID
                    order by NAME
                for xml path(''),type).value('.','nvarchar(max)'
            ), 1, 1, '')
            + ' (' + convert(varchar, convert(int, LI.QUANTITY)) + ')'
        as [DESCRIPTION],
        null STARTDATE,
        null STARTTIME,
        dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE(SALESORDERITEM.ID) * LI.QUANTITY as PRICE,
        isnull(ORDERLEVELDISCOUNTS.AMOUNT, 0) + EXT.DISCOUNTS as DISCOUNTS,  -- Combos are not item-level-discountable as of January 2014, but we're leaving EXT.DISCOUNTS here for now.

        dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE(SALESORDERITEM.ID) * LI.QUANTITY - EXT.DISCOUNTS - isnull(ORDERLEVELDISCOUNTS.AMOUNT, 0) AMOUNTPAID,
        dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE(SALESORDERITEM.ID) * LI.QUANTITY - EXT.DISCOUNTS - isnull(ORDERLEVELDISCOUNTS.AMOUNT, 0) AMOUNTREFUNDED,
        SALESORDERITEM.SALESORDERID
    from dbo.FINANCIALTRANSACTION as FT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
    inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
    inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID
    inner join dbo.COMBINATION on COMBINATION.ID = SALESORDERITEMTICKETCOMBINATION.COMBINATIONID
    inner join dbo.PRICETYPECODE on PRICETYPECODE.ID = SALESORDERITEMTICKET.PRICETYPECODEID
    left join dbo.PROGRAM on PROGRAM.ID = SALESORDERITEMTICKET.PROGRAMID
    left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
    outer apply (
        select sum(AMOUNT) AMOUNT
        from dbo.SALESORDERITEMORDERDISCOUNTDETAIL ODD
        where ODD.SALESORDERITEMID in (
            select COMBOITEM.ID
            from dbo.SALESORDERITEMTICKETCOMBINATION COMBOITEM
            inner join dbo.SALESORDERITEMTICKET TICKETITEM on TICKETITEM.ID = COMBOITEM.ID
            where COMBOITEM.TICKETCOMBINATIONID = SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID
                and TICKETITEM.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID
        )
    ) ORDERLEVELDISCOUNTS
    where
        FT.ID = @CREDITID
        and EXT.SALESORDERITEMIZEDITEMID is null
        and SALESORDERITEMTICKETCOMBINATION.PROGRAMGROUPID in (
            select top 1 ID from dbo.PROGRAMGROUP
            where PROGRAMGROUP.COMBINATIONID = SALESORDERITEMTICKETCOMBINATION.COMBINATIONID
        )

    union all

    -- Post-2014 S1 refunded tickets (itemized and potentially partially refunded)

    select
        SALESORDERITEM.ID,
        SALESORDERITEM.[DESCRIPTION],
        [EVENT].STARTDATE,
        [EVENT].STARTTIME,
        TICKET.PRICE,
        TICKET.ITEMLEVELDISCOUNTSAPPLIED + TICKET.ORDERLEVELDISCOUNTSAPPLIED DISCOUNTS,
        TICKET.AMOUNTPAID,
        case when FTLI.BASEAMOUNT > TICKET.AMOUNTPAID then TICKET.AMOUNTPAID else FTLI.BASEAMOUNT end AMOUNTREFUNDED,  -- Accounting for order-level discounts.

        SALESORDERITEM.SALESORDERID
    from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
    inner join dbo.CREDITITEM_EXT EXT on EXT.ID = FTLI.ID
    inner join dbo.TICKET on TICKET.ID = EXT.SALESORDERITEMIZEDITEMID
    inner join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = TICKET.SALESORDERITEMTICKETID
    inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEMTICKET.ID
    left join dbo.[EVENT] on [EVENT].ID = SALESORDERITEMTICKET.EVENTID
    where
        FTLI.FINANCIALTRANSACTIONID = @CREDITID
        and SALESORDERITEMTICKETCOMBINATION.ID is null

    union all

    -- Post-2014 S1 refunded combo tickets (itemized)

    select
        newid() ID,  -- ID doesn't matter

        COMBINATION.NAME + ' ' + cast(COMBOGROUPNUMBERS.TICKETGROUPNUMBER as nvarchar(3)) + ' - ' + SALESORDERITEM.[DESCRIPTION] as [DESCRIPTION],
        [EVENT].STARTDATE,
        [EVENT].STARTTIME,
        TICKET.PRICE,
        TICKET.ITEMLEVELDISCOUNTSAPPLIED + TICKET.ORDERLEVELDISCOUNTSAPPLIED as DISCOUNTS,
        TICKET.AMOUNTPAID,
        case when FTLI.BASEAMOUNT > TICKET.AMOUNTPAID then TICKET.AMOUNTPAID else FTLI.BASEAMOUNT end AMOUNTREFUNDED,  -- Accounting for order-level discounts.

        SALESORDERITEM.SALESORDERID
    from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
    inner join dbo.CREDITITEM_EXT EXT on EXT.ID = FTLI.ID
    inner join dbo.TICKET on TICKET.ID = EXT.SALESORDERITEMIZEDITEMID
    inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID
    inner join dbo.SALESORDERITEMTICKETCOMBINATION COMBOITEM on COMBOITEM.ID = TICKET.SALESORDERITEMTICKETID
    inner join COMBOGROUPNUMBERS_CTE COMBOGROUPNUMBERS on COMBOGROUPNUMBERS.TICKETID = TICKET.ID
    inner join dbo.COMBINATION on COMBINATION.ID = COMBOITEM.COMBINATIONID
    left join dbo.[EVENT] on [EVENT].ID = TICKET.EVENTID
    where FTLI.FINANCIALTRANSACTIONID = @CREDITID

    order by [DESCRIPTION];

    return 0;