USP_DATAFORMTEMPLATE_VIEW_ITINERARYCALENDARSUMMARY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ITINERARYCALENDARSUMMARY
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ARRIVALDATE datetime = null output,
    @ARRIVALTIME dbo.UDT_HOURMINUTE = null output,
    @ATTENDEES xml = null output,
    @COST money = null output,        -- By design, cost is an expression of potential cost - not the sum of the actual sales order items
    @TRACKNAME nvarchar(100) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    declare @RESERVATIONID uniqueidentifier

    select 
        @DATALOADED = 1,
        @ARRIVALDATE = RESERVATION.ARRIVALDATE,
        @ARRIVALTIME = RESERVATION.ARRIVALTIME,
        @RESERVATIONID = RESERVATION.ID,
        @TRACKNAME = TRACK.NAME
    from 
        dbo.ITINERARY
    inner join 
        dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
    inner join 
        dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
    left join 
        dbo.TRACK on TRACK.ID = ITINERARY.TRACKID
    where 
        ITINERARY.ID = @ID;

    if @DATALOADED= 1
    begin
        set @ATTENDEES =
        (
            select 
                QUANTITY,
                dbo.UFN_PRICETYPECODE_GETDESCRIPTION(PRICETYPECODEID) as PRICETYPE,
                PRICETYPECODEID as PRICETYPECODEID,
                SEQUENCE
            from 
                dbo.ITINERARYATTENDEE
            where 
                ITINERARYATTENDEE.ITINERARYID = @ID
            for xml raw('ITEM'),type,elements,root('ATTENDEES'),binary base64
        );

    with DISTINCTITEMS_CTE as
    (
        select 
            PROGRAMID,
            EVENTID,
            ITINERARYID
        from 
            dbo.ITINERARYITEM
        where 
            ITINERARYID = @ID
        group by PROGRAMID, EVENTID, STARTDATE, ITINERARYID
    )
    select 
        @COST = 
            isnull
            (
                sum
                (
                    case SALESORDERITEM.PRICINGSTRUCTURECODE
                        when 1 then    0
                    else
                        T.items.value('(QUANTITY)[1]','integer') * SALESORDERITEM.PRICE
                    end
                ), 0
            )
    from 
        dbo.SALESORDERITEM
    inner join 
        dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    inner join 
        dbo.ITINERARY on ITINERARY.RESERVATIONID = SALESORDERITEM.SALESORDERID
    inner join 
        DISTINCTITEMS_CTE DI on DI.ITINERARYID = ITINERARY.ID
    inner join 
        @ATTENDEES.nodes('/ATTENDEES/ITEM') T(items) on T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') = SALESORDERITEMTICKET.PRICETYPECODEID
    where 
        SALESORDERITEM.SALESORDERID = @RESERVATIONID and
        ((DI.PROGRAMID = SALESORDERITEMTICKET.PROGRAMID and SALESORDERITEMTICKET.EVENTID is null) or DI.EVENTID = SALESORDERITEMTICKET.EVENTID)

    declare @FEETABLE table
    (
        FEEAMOUNT decimal(10,3),
        QUANTITY integer
    );

    with DISTINCTITEMS_CTE as
    (
        select 
            PROGRAMID,
            EVENTID,
            ITINERARYID
        from 
            dbo.ITINERARYITEM
        where 
            ITINERARYID = @ID
        group by 
            PROGRAMID, EVENTID, STARTDATE, ITINERARYID
    )

    insert into @FEETABLE
    (
        FEEAMOUNT,
        QUANTITY
    )
    select
        case SALESORDERITEM.PRICINGSTRUCTURECODE
            when 1 then 0
            else
                case FEE.TYPECODE 
                    when 0 then FEE.AMOUNT 
                    else (FEE.[PERCENT] * .01 * SALESORDERITEMTICKET.PRICE)  
                end
        end as FEEAMOUNT,
        T.items.value('(QUANTITY)[1]', 'integer') as QUANTITY
    from 
        dbo.SALESORDERITEMFEE
    inner join 
        dbo.FEE on SALESORDERITEMFEE.FEEID = FEE.ID
    inner join 
        dbo.SALESORDERITEM on SALESORDERITEMFEE.SALESORDERITEMID = SALESORDERITEM.ID
    inner join 
        dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    inner join 
        dbo.RESERVATION on RESERVATION.ID = SALESORDERITEM.SALESORDERID
    inner join 
        dbo.ITINERARY ON ITINERARY.RESERVATIONID = RESERVATION.ID
    inner join 
        DISTINCTITEMS_CTE DI on DI.ITINERARYID = ITINERARY.ID
    inner join 
        @ATTENDEES.nodes('/ATTENDEES/ITEM') T(items) on T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') = SALESORDERITEMTICKET.PRICETYPECODEID
    where 
        SALESORDERITEMFEE.APPLIESTOCODE = 1
        and SALESORDERITEM.SALESORDERID = @RESERVATIONID
        and ITINERARY.ID = @ID
        and ((DI.PROGRAMID = SALESORDERITEMTICKET.PROGRAMID and SALESORDERITEMTICKET.EVENTID is null) or DI.EVENTID = SALESORDERITEMTICKET.EVENTID)

    declare @FEES money
    select @FEES = isnull(SUM(FEEAMOUNT * QUANTITY), 0)
    from @FEETABLE 

    -- Add resource costs to the total cost
    declare @SUPPLYRESOURCECOST money

    declare @ATTENDEECOUNT as integer
    select @ATTENDEECOUNT = 
        COALESCE
        (
            (
                select 
                    sum(QUANTITY)
                from 
                    dbo.ITINERARYATTENDEE
                where ITINERARYID = @ID
            ) ,0
        )

    declare @STARTDATETIME as datetime
    select @STARTDATETIME = dbo.UFN_ITINERARY_STARTDATETIME(@ID)

    declare @ENDDATETIME as datetime
    select @ENDDATETIME = dbo.UFN_ITINERARY_ENDDATETIME(@ID)

    declare @DURATION as decimal(20,4)
    if (@ENDDATETIME is null or @STARTDATETIME is null)
        set @DURATION = 0
    else
        select @DURATION = DATEDIFF(s, @STARTDATETIME, @ENDDATETIME) / (3600.0)


    -- stolen from reservation page - resource section    
    select @SUPPLYRESOURCECOST =  coalesce(sum(TOTAL), 0)
    from
    (
        select 
            SALESORDERITEM.TOTAL as TOTAL
        from 
            dbo.SALESORDERITEM
        inner join 
            dbo.SALESORDERITEMITINERARYRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID
        inner join 
            dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
        inner join 
            dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
        outer apply (
            select 
                sum(ITINERARYATTENDEE.QUANTITY) as QUANTITY
            from 
                dbo.ITINERARYATTENDEE
            where 
                ITINERARYATTENDEE.ITINERARYID = ITINERARYRESOURCE.ITINERARYID
        ) as ITINERARYATTENDEECOUNT
        where 
            ITINERARYRESOURCE.ITINERARYID = @ID
            and SALESORDERITEM.TYPECODE = 8

        union all

        select 
            SALESORDERITEM.TOTAL as TOTAL
        from 
            dbo.SALESORDERITEM
        inner join 
            dbo.SALESORDERITEMITINERARYITEMRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID
        inner join 
            dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
        inner join 
            dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
        inner join 
            dbo.RESOURCE on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID
        outer apply (
            select 
                sum(ITINERARYATTENDEE.QUANTITY) as QUANTITY
            from 
                dbo.ITINERARYATTENDEE
            where 
                ITINERARYATTENDEE.ITINERARYID = ITINERARYITEM.ITINERARYID
        ) as ITINERARYATTENDEECOUNT
        where 
            ITINERARYITEM.ITINERARYID = @ID
            and SALESORDERITEM.TYPECODE = 9
    ) AS T

    declare @STAFFINGRESOURCECOST money
    select @STAFFINGRESOURCECOST = coalesce(sum(TOTAL), 0)
    from
    (
        select 
            SALESORDERITEM.TOTAL
        from 
            dbo.SALESORDERITEM
        inner join 
            dbo.SALESORDERITEMITINERARYSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID
        inner join 
            dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
        where 
            ITINERARYSTAFFRESOURCE.ITINERARYID = @ID
            and SALESORDERITEM.TYPECODE = 10

        union all

        select 
            SALESORDERITEM.TOTAL
        from 
            dbo.SALESORDERITEM
        inner join 
            dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID
        inner join 
            dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
        inner join 
            dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
        where 
            ITINERARYITEM.ITINERARYID = @ID
            and SALESORDERITEM.TYPECODE = 11
    ) as G

    -- Add location costs to the total cost
    declare @LOCATIONCOST money
    select
        @LOCATIONCOST = coalesce(sum(TOTAL),0)
    from 
        dbo.SALESORDERITEM
    inner join 
        dbo.ITINERARYITEMLOCATION on ITINERARYITEMLOCATION.SALESORDERITEMID = SALESORDERITEM.ID
    inner join 
        dbo.ITINERARYITEM on ITINERARYITEMLOCATION.ID = ITINERARYITEM.ID
    where
        ITINERARYITEM.ITINERARYID = @ID and
        SALESORDERITEM.SALESORDERID = @RESERVATIONID and
        SALESORDERITEM.TYPECODE = 7


    set @COST = @COST + @FEES + @SUPPLYRESOURCECOST + @STAFFINGRESOURCECOST + @LOCATIONCOST
end

    return 0;