USP_DATALIST_CREDITPAYMENT

CREATE procedure dbo.USP_DATALIST_CREDITPAYMENT(@SALESORDERID uniqueidentifier)
as
    set nocount on;

    if exists (select * from dbo.RESERVATION where ID = @SALESORDERID) begin  -- Group Sales

        select
            FT.ID,
            'Refunded for $' + convert(nvarchar(100), FT.TRANSACTIONAMOUNT) as DESCRIPTION,
            dbo.UFN_CREDIT_ISDEPOSITED(FT.ID) as ISDEPOSITED
        from
            dbo.FINANCIALTRANSACTION as FT
        inner join
            dbo.CREDIT_EXT on CREDIT_EXT.ID = FT.ID
        where
            @SALESORDERID = CREDIT_EXT.SALESORDERID
            and exists (
                select *
                from dbo.FINANCIALTRANSACTIONLINEITEM as LI
                inner join dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = LI.ID
                where LI.FINANCIALTRANSACTIONID = FT.ID
                and CREDITITEM_EXT.TYPECODE = 255  -- Unearned revenue

            )
        order by
            FT.DATE;
    end

    else begin
        declare @CREDITITEMSCOUNT table (
            CREDITID uniqueidentifier,
            TICKETCOMBINATIONID uniqueidentifier,
            PRICETYPECODEID uniqueidentifier,
            QUANTITY int
        )

        insert into @CREDITITEMSCOUNT (CREDITID, TICKETCOMBINATIONID, PRICETYPECODEID, QUANTITY)
        select
            CREDITINFO.ID,
            TICKETCOMBINATIONID,
            SALESORDERITEMTICKET.PRICETYPECODEID,
            case 
                when TICKETCOMBINATIONID is null then sum(CREDITINFO.QUANTITY)
                else sum(CREDITINFO.QUANTITY) / count(SALESORDERITEMTICKETCOMBINATION.PROGRAMGROUPID)
            end as QUANTITY
        from (
            select 
                FT.ID,
                EXT.SALESORDERITEMID,
                LI.QUANTITY
            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
            where
                SALESORDERITEM.SALESORDERID = @SALESORDERID
            group by EXT.SALESORDERITEMID, FT.ID, LI.QUANTITY    
        ) as CREDITINFO
        left join dbo.SALESORDERITEMTICKET on CREDITINFO.SALESORDERITEMID = SALESORDERITEMTICKET.ID
        left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
        left join (dbo.SALESORDERITEM as ITEMLEVELFEE inner join dbo.SALESORDERITEMFEE
                    on ITEMLEVELFEE.ID = SALESORDERITEMFEE.SALESORDERITEMID) on SALESORDERITEMFEE.ID = CREDITINFO.SALESORDERITEMID
        where
            ITEMLEVELFEE.ID is null
        group by
            CREDITINFO.ID, TICKETCOMBINATIONID, SALESORDERITEMTICKET.PRICETYPECODEID

        select
            FT.ID,
            case sum(ITEMCOUNT.QUANTITY)
                when 1 then
                    'Refunded 1 item for $' + convert(nvarchar(100), FT.TRANSACTIONAMOUNT)
                else
                    'Refunded ' + convert(nvarchar(100), sum(coalesce(ITEMCOUNT.QUANTITY, 0)))
                    + ' items for $' + convert(nvarchar(100), FT.TRANSACTIONAMOUNT)
            end as DESCRIPTION,
            dbo.UFN_CREDIT_ISDEPOSITED(FT.ID) as ISDEPOSITED
        from
            dbo.FINANCIALTRANSACTION as FT
        inner join
            @CREDITITEMSCOUNT as ITEMCOUNT on ITEMCOUNT.CREDITID = FT.ID
        group by FT.ID, FT.TRANSACTIONAMOUNT, FT.DATE
        order by FT.DATE
    end

    return 0;