USP_DATALIST_SALESORDERTICKETS2

CREATE procedure dbo.USP_DATALIST_SALESORDERTICKETS2
(
    @CONTEXTID uniqueidentifier
)
as
    declare @FEESANDDISCOUNTS table (
        SALESORDERITEMID uniqueidentifier,
        FEES money,
        DISCOUNTS money
    );

    insert into @FEESANDDISCOUNTS
    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
        ) FEES,
        (
            select coalesce(sum(AMOUNT),0.0)
            from dbo.SALESORDERITEMITEMDISCOUNT
            where SALESORDERITEMID = SALESORDERITEM.ID
        ) DISCOUNTS
    from dbo.SALESORDERITEM
    where SALESORDERITEM.SALESORDERID = @CONTEXTID;

    declare @TICKETCOMBINATION table (
        SALESORDERID uniqueidentifier,
        TICKETCOMBINATIONID uniqueidentifier,
        PRICETYPECODEID uniqueidentifier,
        SALESORDERTICKETCOMBINATIONID 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 = @CONTEXTID;

    update @TICKETCOMBINATION set SALESORDERTICKETCOMBINATIONID = newid();

    select
        TICKETS.TICKETCOMBINATIONID,
        TICKETS.ID,
        TICKETS.DESCRIPTION,
        TICKETS.STARTDATE,
        TICKETS.STARTTIME,
        TICKETS.LOCATION,
        TICKETS.QUANTITY,
        TICKETS.PRICE,
        TICKETS.DISCOUNTS,
        TICKETS.FEES,
        TICKETS.TOTAL,
        TICKETS.EVENTID,
        TICKETS.PROGRAMID,
        TICKETS.PRICETYPECODEID,
        case TICKETS.PRICINGSTRUCTURECODE
            when 2 then 1  -- when structure is override rate

            else 0
        end as ISPRICEOVERRIDDEN,
        TICKETS.ORDERDISCOUNT,
        case
            when exists(
                select top 1 RESCHEDULETICKETSHISTORY.ID
                from dbo.RESCHEDULETICKETSHISTORY 
                where RESCHEDULETICKETSHISTORY.SALESORDERITEMTICKETID = TICKETS.ID
            )
                then 1
            else 0
        end ISRESCHEDULED,
        [NAME],
        [COMBINATIONID]
    from (
        --Tickets

        select
            TICKETCOMBINATION.SALESORDERTICKETCOMBINATIONID as TICKETCOMBINATIONID,
            SALESORDERITEM.ID,
            SALESORDERITEM.DESCRIPTION,
            EVENT.STARTDATE,
            EVENT.STARTTIME,
            dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATION,
            SALESORDERITEM.QUANTITY,
            SALESORDERITEM.PRICE,
            FEESANDDISCOUNTS.DISCOUNTS,
            FEESANDDISCOUNTS.FEES,
            SALESORDERITEM.TOTAL + FEESANDDISCOUNTS.FEES - FEESANDDISCOUNTS.DISCOUNTS as TOTAL,
            EVENT.ID as EVENTID,
            PROGRAM.ID as PROGRAMID,
            SALESORDERITEMTICKET.PRICETYPECODEID,
            SALESORDERITEM.DATEADDED,
            SALESORDERITEM.PRICINGSTRUCTURECODE,
            (
                select coalesce(sum(AMOUNT),0)
                from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
                where SALESORDERITEMID = SALESORDERITEM.ID
            ) as ORDERDISCOUNT,  --Only calculated here for performance reasons (and not on the other unioned pieces) since this value is only one used online

            isnull([EVENT].[NAME], PROGRAM.NAME) as NAME,
            [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID]
        from dbo.SALESORDERITEM
        inner join @FEESANDDISCOUNTS as FEESANDDISCOUNTS on SALESORDERITEM.ID = FEESANDDISCOUNTS.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

        union all

        -- Combination tickets

        select distinct
            null,
            TICKETCOMBINATION.SALESORDERTICKETCOMBINATIONID,
            COMBINATION.NAME + ' - ' + PRICETYPECODE.DESCRIPTION as DESCRIPTION,
            null STARTDATE,
            null STARTTIME,
            null LOCATION,
            SALESORDERITEM.QUANTITY,
            dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE(SALESORDERITEM.ID),
            0,
            dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEES(SALESORDERITEM.ID),
            SALESORDERITEM.QUANTITY * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE(SALESORDERITEM.ID) + dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEES(SALESORDERITEM.ID),
            null,
            null,
            SALESORDERITEMTICKET.PRICETYPECODEID,
            SALESORDERITEM.DATEADDED,
            SALESORDERITEM.PRICINGSTRUCTURECODE,
            0 as ORDERDISCOUNT,
            COMBINATION.NAME,
            COMBINATION.ID as COMBINATIONID
        from dbo.SALESORDERITEM
        inner join @FEESANDDISCOUNTS as FEESANDDISCOUNTS on SALESORDERITEM.ID = FEESANDDISCOUNTS.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 = @CONTEXTID
    ) TICKETS
    order by TICKETS.DATEADDED, TICKETS.ID;