UFN_SALESORDER_TICKETS

CREATE function dbo.UFN_SALESORDER_TICKETS (@SALESORDERID uniqueidentifier)
returns @TICKETS table (ID uniqueidentifier, DESCRIPTION nvarchar(1024), STARTDATE date, STARTTIME char(4), LOCATION nvarchar(500), QUANTITY int, PRICE money, DISCOUNTS money, FEES money, TOTAL money)
as
begin
declare @FEESDISCOUNTSANDREFUNDS table (
    SALESORDERITEMID uniqueidentifier,
    FEES money,
    DISCOUNTS money,
    QUANTITYREFUNDED int,
    AMOUNTREFUNDED money
)

insert into @FEESDISCOUNTSANDREFUNDS (SALESORDERITEMID, FEES, DISCOUNTS, QUANTITYREFUNDED, AMOUNTREFUNDED)
select 
    [SALESORDERITEM].[ID],
    (
        (select coalesce(sum([FEESALESORDERITEM].[TOTAL]),0.0)
        from dbo.[SALESORDERITEM] as [FEESALESORDERITEM]
        inner join dbo.[SALESORDERITEMFEE] on [FEESALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
        where [SALESORDERITEMFEE].[SALESORDERITEMID] = [SALESORDERITEM].[ID])
        -
        (select isnull(sum((LI.QUANTITY * LI.UNITVALUE) - CREDITITEM_EXT.DISCOUNTS),0)
        from dbo.SALESORDERITEMFEE
        inner join dbo.CREDITITEM_EXT on SALESORDERITEMFEE.ID = CREDITITEM_EXT.SALESORDERITEMID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM_EXT.ID
        where SALESORDERITEMFEE.SALESORDERITEMID = SALESORDERITEM.ID)
    ) as [FEES],
    (
        select coalesce(sum([AMOUNT]),0.0)
        from dbo.[SALESORDERITEMITEMDISCOUNT]
        where [SALESORDERITEMID] = [SALESORDERITEM].[ID]
    ) as [DISCOUNTS], --Item Discounts

    (
        select coalesce(count([ID]), 0)
        from dbo.[TICKET]
        where [TICKET].SALESORDERITEMTICKETID = [SALESORDERITEM].[ID] and
            [TICKET].[ISREFUNDED] = 1

    ) as [REFUNDEDQUANTITY],
    (
        select
            isnull(sum((LI.QUANTITY * LI.UNITVALUE) - CREDITITEM_EXT.DISCOUNTS), 0)
        from
            dbo.CREDITITEM_EXT
        inner join
          dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM_EXT.ID
        inner join
            dbo.FINANCIALTRANSACTION FT on FT.ID = CREDITITEM_EXT.CREDITID
        where
            CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEM.ID
            and FT.TYPECODE = 23  -- Refund

    ) as [AMOUNTREFUNDED]
from dbo.[SALESORDERITEM]
where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID

declare @TICKETCOMBINATION table (
SALESORDERID uniqueidentifier,
TICKETCOMBINATIONID uniqueidentifier,
PRICETYPECODEID uniqueidentifier
)

insert into @TICKETCOMBINATION (SALESORDERID, TICKETCOMBINATIONID, PRICETYPECODEID)
select distinct SALESORDERITEM.SALESORDERID, SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID, SALESORDERITEMTICKET.PRICETYPECODEID
from dbo.[SALESORDERITEM]
    inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
    where SALESORDERITEM.SALESORDERID = @SALESORDERID

declare @CREDITTICKETCOMBINATION table (
CREDITID uniqueidentifier,
TICKETCOMBINATIONID uniqueidentifier,
PRICETYPECODEID uniqueidentifier
)

insert into @CREDITTICKETCOMBINATION (CREDITID, TICKETCOMBINATIONID, PRICETYPECODEID)
select distinct FT.ID, SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID, SALESORDERITEMTICKET.PRICETYPECODEID
from dbo.FINANCIALTRANSACTION FT
inner join dbo.CREDIT_EXT on FT.ID = CREDIT_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = LI.ID
inner join dbo.SALESORDERITEM on CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where CREDIT_EXT.SALESORDERID = @SALESORDERID and FT.TYPECODE = 23;

insert into @TICKETS (ID, DESCRIPTION, STARTDATE, STARTTIME, LOCATION, QUANTITY, PRICE, DISCOUNTS, FEES, TOTAL)
select
        [ID],
        [DESCRIPTION],
        [STARTDATE],
        [STARTTIME],
        [LOCATION],
        [QUANTITY],
        [PRICE],
        [DISCOUNTS],
        [FEES],
        [TOTAL]
from(
    --Tickets

    select 
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[DESCRIPTION],
            [EVENT].[STARTDATE],
            [EVENT].[STARTTIME],
            dbo.UFN_EVENT_GETLOCATIONNAME([EVENT].[ID]) as [LOCATION],
            [SALESORDERITEM].[QUANTITY] - [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED] as [QUANTITY],
            [SALESORDERITEM].[PRICE],
            [FEESDISCOUNTSANDREFUNDS].[DISCOUNTS],
            [FEESDISCOUNTSANDREFUNDS].[FEES],
            SALESORDERITEM.TOTAL + FEESDISCOUNTSANDREFUNDS.FEES - FEESDISCOUNTSANDREFUNDS.DISCOUNTS - FEESDISCOUNTSANDREFUNDS.AMOUNTREFUNDED as [TOTAL]
        from dbo.[SALESORDERITEM]
        inner join @FEESDISCOUNTSANDREFUNDS as [FEESDISCOUNTSANDREFUNDS] on [SALESORDERITEM].[ID] = [FEESDISCOUNTSANDREFUNDS].[SALESORDERITEMID]
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
    left join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
    left join @TICKETCOMBINATION as TICKETCOMBINATION on [SALESORDERITEM].[SALESORDERID] = TICKETCOMBINATION.[SALESORDERID] 
                                                              and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = TICKETCOMBINATION.TICKETCOMBINATIONID
                                                              and SALESORDERITEMTICKET.PRICETYPECODEID = TICKETCOMBINATION.PRICETYPECODEID
        left join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
        left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
        where ([FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED] = 0) or ([SALESORDERITEM].[QUANTITY] > [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED])

    union all 
    -- combination tickets

    select distinct
    null,
      [COMBINATION].[NAME] + ' - ' + [PRICETYPECODE].[DESCRIPTION] as [DESCRIPTION],
    null as [STARTDATE],
    null as [STARTTIME],
    null as [LOCATION],
    [SALESORDERITEM].[QUANTITY] - [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED] as [QUANTITY],
      dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]),
      0,
    dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEES([SALESORDERITEM].[ID]) - dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEESREFUNDED([SALESORDERITEM].[ID]),
      ([SALESORDERITEM].[QUANTITY] - [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED]) * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]) + dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEES([SALESORDERITEM].[ID])
    from dbo.[SALESORDERITEM]
        inner join @FEESDISCOUNTSANDREFUNDS as [FEESDISCOUNTSANDREFUNDS] on [SALESORDERITEM].[ID] = [FEESDISCOUNTSANDREFUNDS].[SALESORDERITEMID]
    inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
    inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
    inner join @TICKETCOMBINATION as TICKETCOMBINATION on [SALESORDERITEM].[SALESORDERID] = TICKETCOMBINATION.[SALESORDERID] 
                                                              and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = TICKETCOMBINATION.TICKETCOMBINATIONID
                                                              and SALESORDERITEMTICKET.PRICETYPECODEID = TICKETCOMBINATION.PRICETYPECODEID
    inner join dbo.[COMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
    inner join dbo.[COMBINATIONPRICETYPE] on [COMBINATIONPRICETYPE].PRICETYPECODEID  = [SALESORDERITEMTICKET].[PRICETYPECODEID]
                                      and [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID] 
    inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
    where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and 
    [SALESORDERITEM].[QUANTITY] - [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED] > 0

    union all

        --Refunded tickets

    select
            CREDITITEM_EXT.[ID] as [ID],
            'Refund ' + convert(nvarchar(20),cast(FT.DATE as datetime), 1) + ', ' + [SALESORDERITEM].[DESCRIPTION] as [DESCRIPTION],
            [EVENT].[STARTDATE],
            [EVENT].[STARTTIME],
            dbo.UFN_EVENT_GETLOCATIONNAME([EVENT].[ID]) as [LOCATION],
            LI.[QUANTITY],
            LI.UNITVALUE as [PRICE],
            CREDITITEM_EXT.[DISCOUNTS],
            CREDITITEM_EXT.[FEES],
            (LI.QUANTITY * LI.UNITVALUE) - CREDITITEM_EXT.DISCOUNTS + CREDITITEM_EXT.FEES
        from dbo.CREDITITEM_EXT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM_EXT.ID
        inner join dbo.FINANCIALTRANSACTION FT on FT.[ID] = LI.FINANCIALTRANSACTIONID
        inner join dbo.CREDIT_EXT on CREDIT_EXT.ID = FT.ID
        inner join dbo.[SALESORDERITEM] on CREDITITEM_EXT.[SALESORDERITEMID] = [SALESORDERITEM].[ID]
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
    left join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
    left join @CREDITTICKETCOMBINATION as CREDITTICKETCOMBINATION on CREDIT_EXT.[ID] = CREDITTICKETCOMBINATION.[CREDITID] 
                                                              and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = CREDITTICKETCOMBINATION.TICKETCOMBINATIONID
                                                              and SALESORDERITEMTICKET.PRICETYPECODEID = CREDITTICKETCOMBINATION.PRICETYPECODEID
        left join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
        left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
        where CREDIT_EXT.[SALESORDERID] = @SALESORDERID and FT.[TYPECODE] = 23

    union all
    -- Refunded combination tickets

        select distinct
            null,
            'Refund ' + [COMBINATION].[NAME] + ' - ' + [PRICETYPECODE].[DESCRIPTION] as [DESCRIPTION],
            null,
            null,
            null,
            LI.[QUANTITY],
            dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]),
            CREDITITEM_EXT.[DISCOUNTS],
            dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEESREFUNDED(LI.ID) as FEES,
            LI.[QUANTITY] * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]) + dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEESREFUNDED(LI.ID)
        from dbo.CREDITITEM_EXT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM_EXT.ID
        inner join dbo.FINANCIALTRANSACTION FT on FT.[ID] = LI.FINANCIALTRANSACTIONID
        inner join dbo.CREDIT_EXT on CREDIT_EXT.ID = FT.ID
        inner join dbo.[SALESORDERITEM] on CREDITITEM_EXT.[SALESORDERITEMID] = [SALESORDERITEM].[ID]
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
    inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
    inner join @CREDITTICKETCOMBINATION as CREDITTICKETCOMBINATION on CREDIT_EXT.[ID] = CREDITTICKETCOMBINATION.[CREDITID] 
                                                              and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = CREDITTICKETCOMBINATION.TICKETCOMBINATIONID
                                                              and SALESORDERITEMTICKET.PRICETYPECODEID = CREDITTICKETCOMBINATION.PRICETYPECODEID
    inner join dbo.[COMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
    inner join dbo.[COMBINATIONPRICETYPE] on [COMBINATIONPRICETYPE].PRICETYPECODEID  = [SALESORDERITEMTICKET].[PRICETYPECODEID]
                                                              and [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID] 
    inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
    left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
    where CREDIT_EXT.[SALESORDERID] = @SALESORDERID and FT.[TYPECODE] = 23
    ) as [TICKETS] 

return
end