USP_DATALIST_RESERVATION_FLATRATETICKETS

CREATE procedure dbo.USP_DATALIST_RESERVATION_FLATRATETICKETS
(
    @CONTEXTID uniqueidentifier
)
as

    declare @EXTRAFLATRATEPEOPLE table
    (
        EVENTID uniqueidentifier,
        PROGRAMID uniqueidentifier,
        PRICETYPEID uniqueidentifier,
        ITINERARYID uniqueidentifier,
        QUANTITY int
    ) 

    insert into @EXTRAFLATRATEPEOPLE (EVENTID, PROGRAMID, PRICETYPEID, ITINERARYID, QUANTITY) 
    select
        EVENTID,
        PROGRAMID,
        PRICETYPEID,
        EFRP.ITINERARYID,
        QUANTITY
    from dbo.UFN_RESERVATION_GETEXTRAFLATRATEPEOPLE(@CONTEXTID) as EFRP
    inner join ITINERARYITEM on 
        ITINERARYITEM.ITINERARYID = EFRP.ITINERARYID

    declare @GENERATEDITINERARYNAMES table 
    (
        ID uniqueidentifier,
        NAME nvarchar(256)
    )

    insert into @GENERATEDITINERARYNAMES (ID, NAME)
    select
        ID,
        'Itinerary ' + cast(row_number() over (partition by RESERVATIONID order by DATEADDED) as nvarchar(10)) + ' (' + NAME + ')' as NAME
    from dbo.ITINERARY
    where RESERVATIONID = @CONTEXTID

    declare @SHOWITINERARYNAME bit
    select @SHOWITINERARYNAME = case count(ID)
                                    when 1 then 0
                                    else 1
                                end
    from dbo.ITINERARY
    where RESERVATIONID = @CONTEXTID

    select
        [ID],
        [DESCRIPTION],
        [STARTDATE],
        [STARTTIME],
        [LOCATION],
        [QUANTITY],
        [EVENTID],
        [PROGRAMID],
        [PRICETYPECODEID],
        [ITINERARYNAME],
        [PRICE]
    from(
        --Flat Rate Tickets

        select 
            [SALESORDERITEM].[ID],
            coalesce(EVENT.NAME, PROGRAM.NAME) as [DESCRIPTION],
            [EVENT].[STARTDATE],
            [EVENT].[STARTTIME],
            dbo.UFN_EVENT_GETLOCATIONNAME([EVENT].[ID]) as [LOCATION],
            [ITINERARYATTENDEE].[QUANTITY] - coalesce(EFRP.QUANTITY, 0) as [QUANTITY],
            [EVENT].[ID] as [EVENTID],
            [PROGRAM].[ID] as [PROGRAMID],
            [SALESORDERITEMTICKET].[PRICETYPECODEID],
            [SALESORDERITEM].[DATEADDED],
            PRICETYPECODE.DESCRIPTION + case @SHOWITINERARYNAME when 1 then ' - ' + INAMES.NAME else '' end as [ITINERARYNAME],
            null as PRICE
        from dbo.[SALESORDERITEM]
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
        inner join ITINERARYITEM on SALESORDERITEMTICKET.PROGRAMID = ITINERARYITEM.PROGRAMID or SALESORDERITEMTICKET.EVENTID = ITINERARYITEM.EVENTID
        inner join ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
        inner join @GENERATEDITINERARYNAMES INAMES on ITINERARY.ID = INAMES.ID
        inner join ITINERARYATTENDEE on ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID and SALESORDERITEMTICKET.PRICETYPECODEID = ITINERARYATTENDEE.PRICETYPECODEID
        left join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
        left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
        left join @EXTRAFLATRATEPEOPLE EFRP on PRICETYPECODE.ID = EFRP.PRICETYPEID and (EVENT.ID = EFRP.EVENTID or PROGRAM.ID = EFRP.PROGRAMID) and ITINERARY.ID = EFRP.ITINERARYID
        where [SALESORDERITEM].[PRICINGSTRUCTURECODE] = 1
            and [SALESORDERITEM].[SALESORDERID] = @CONTEXTID

        union all

        --Extra Tickets

        select 
            [SALESORDERITEM].[ID],
            coalesce(EVENT.NAME, PROGRAM.NAME) as [DESCRIPTION],
            [EVENT].[STARTDATE],
            [EVENT].[STARTTIME],
            dbo.UFN_EVENT_GETLOCATIONNAME([EVENT].[ID]) as [LOCATION],
            EFRP.QUANTITY as [QUANTITY],
            [EVENT].[ID] as [EVENTID],
            [PROGRAM].[ID] as [PROGRAMID],
            [SALESORDERITEMTICKET].[PRICETYPECODEID],
            [SALESORDERITEM].[DATEADDED],
            'Additional ' + PRICETYPECODE.DESCRIPTION + case @SHOWITINERARYNAME when 1 then ' - ' + INAMES.NAME else '' end as [ITINERARYNAME],
            SALESORDERITEM.PRICE as PRICE
        from dbo.[SALESORDERITEM]
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
        inner join ITINERARYITEM on SALESORDERITEMTICKET.PROGRAMID = ITINERARYITEM.PROGRAMID or SALESORDERITEMTICKET.EVENTID = ITINERARYITEM.EVENTID
        inner join ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
        inner join @GENERATEDITINERARYNAMES INAMES on ITINERARY.ID = INAMES.ID
        inner join ITINERARYATTENDEE on ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID and SALESORDERITEMTICKET.PRICETYPECODEID = ITINERARYATTENDEE.PRICETYPECODEID
        left join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
        left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
        inner join @EXTRAFLATRATEPEOPLE EFRP on PRICETYPECODE.ID = EFRP.PRICETYPEID and (EVENT.ID = EFRP.EVENTID or PROGRAM.ID = EFRP.PROGRAMID) and ITINERARY.ID = EFRP.ITINERARYID
        where [SALESORDERITEM].[PRICINGSTRUCTURECODE] = 1
            and [SALESORDERITEM].[SALESORDERID] = @CONTEXTID

    ) as [TICKETS] 
            where QUANTITY > 0
            order by DATEADDED