UFN_RESERVATION_HAVEQUANTITIESCHANGED

create function dbo.UFN_RESERVATION_HAVEQUANTITIESCHANGED
(
    @ID uniqueidentifier,
    @ORDER xml
)
returns bit
as begin
    declare @ITINERARIES xml;
    declare @RESOURCES xml;
    declare @STAFFRESOURCES xml;

    set @ITINERARIES = @ORDER.query('/ORDERS/ITEM/ITINERARIES');

    if @ITINERARIES.exist('/ITINERARIES') = 1 and exists (
        select
            ITINERARYID,
            PRICETYPECODEID,
            QUANTITY
        from (
            select distinct
                ITINERARY.ID as ITINERARYID,
                PRICETYPECODEID as PRICETYPECODEID,
                QUANTITY as QUANTITY
            from
                dbo.ITINERARY
            inner join
                dbo.ITINERARYATTENDEE on ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
            where
                RESERVATIONID = @ID

            union all

            select distinct
                T.itineraries.value('(ITINERARYID)[1]', 'uniqueidentifier') as ITINERARYID,
                T.itineraries.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') as PRICETYPECODEID,
                T.itineraries.value('(QUANTITY)[1]', 'integer') as QUANTITY
            from
                @ITINERARIES.nodes('/ITINERARIES/ITEM') T(itineraries)
            where
                T.itineraries.value('(QUANTITY)[1]', 'integer') > 0
        ) [ITINERARIES]
        group by
            ITINERARYID,
            PRICETYPECODEID,
            QUANTITY
        having
            count(*) = 1
    )
        return 1;

    set @RESOURCES = @ORDER.query('/ORDERS/ITEM/RESOURCES');

    if @RESOURCES.exist('/RESOURCES') = 1 and exists (
        select
            ID,
            ITINERARYID,
            ITINERARYITEMID,
            QUANTITYNEEDED,
            RESOURCEID
        from (
            -- All itinerary resources

            select distinct
                ITINERARYRESOURCE.ID AS ID,
                ITINERARY.ID as ITINERARYID,
                null as ITINERARYITEMID,
                ITINERARYRESOURCE.QUANTITYNEEDED,
                ITINERARYRESOURCE.RESOURCEID
            from
                dbo.ITINERARYRESOURCE
            inner join
                dbo.ITINERARY on ITINERARYRESOURCE.ITINERARYID = ITINERARY.ID
            where 
                ITINERARY.RESERVATIONID = @ID

            union all

            -- All itinerary item resources

            select distinct
                ITINERARYITEMRESOURCE.ID AS ID,
                ITINERARY.ID as ITINERARYID,
                ITINERARYITEM.ID as ITINERARYITEMID,
                ITINERARYITEMRESOURCE.QUANTITYNEEDED,
                ITINERARYITEMRESOURCE.RESOURCEID
            from
                dbo.ITINERARYITEMRESOURCE
            inner join
                dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
            inner join
                dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
            where 
                ITINERARY.RESERVATIONID = @ID and
                ITINERARYITEM.ITEMTYPECODE <> 3

            union all

            select distinct
                T.resources.value('(ID)[1]', 'uniqueidentifier') as ID,
                T.resources.value('(ITINERARYID)[1]', 'uniqueidentifier') as ITINERARYID,
                T.resources.value('(ITINERARYITEMID)[1]', 'uniqueidentifier') as ITINERARYITEMID,
                T.resources.value('(QUANTITYNEEDED)[1]', 'int') as QUANTITYNEEDED,
                T.resources.value('(RESOURCEID)[1]', 'uniqueidentifier') as RESOURCEID
            from
                @RESOURCES.nodes('/RESOURCES/ITEM') T(resources)
            where
                T.resources.value('(QUANTITYNEEDED)[1]', 'int') > 0
        ) [RESOURCES]
        group by
            ID,
            ITINERARYID,
            ITINERARYITEMID,
            QUANTITYNEEDED,
            RESOURCEID
        having
            count(*) = 1
    )
        return 1;

    set @STAFFRESOURCES = @ORDER.query('/ORDERS/ITEM/STAFFRESOURCES');

    if @STAFFRESOURCES.exist('/STAFFRESOURCES') = 1 and exists (
        select
            ID,
            ITINERARYID,
            ITINERARYITEMID,
            QUANTITYNEEDED,
            VOLUNTEERTYPEID
        from
        (
            select 
                ITINERARYSTAFFRESOURCE.ID AS ID,
                ITINERARY.ID as ITINERARYID,
                null as ITINERARYITEMID,
                ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
                ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID
            from
                dbo.ITINERARYSTAFFRESOURCE
            inner join
                dbo.ITINERARY on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
            where 
                ITINERARY.RESERVATIONID = @ID

            union all

            select 
                ITINERARYITEMSTAFFRESOURCE.ID AS ID,
                ITINERARY.ID as ITINERARYID,
                ITINERARYITEM.ID as ITINERARYITEMID,
                ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
                ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID
            from
                dbo.ITINERARYITEMSTAFFRESOURCE
            inner join
                dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
            inner join
                dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
            where 
                ITINERARY.RESERVATIONID = @ID and 
                ITINERARYITEM.ITEMTYPECODE <> 3

            union all

            select
                T.staffresources.value('(ID)[1]', 'uniqueidentifier') as ID,
                T.staffresources.value('(ITINERARYID)[1]', 'uniqueidentifier') as ITINERARYID,
                T.staffresources.value('(ITINERARYITEMID)[1]', 'uniqueidentifier') as ITINERARYITEMID,
                T.staffresources.value('(QUANTITYNEEDED)[1]', 'int') as QUANTITYNEEDED,
                T.staffresources.value('(VOLUNTEERTYPEID)[1]', 'uniqueidentifier') as VOLUNTEERTYPEID
            from
                @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(staffresources)
            where
                T.staffresources.value('(QUANTITYNEEDED)[1]', 'int') > 0
        ) [STAFFRESOURCES]
        group by
            ID,
            ITINERARYID,
            ITINERARYITEMID,
            QUANTITYNEEDED,
            VOLUNTEERTYPEID
        having
            count(*) = 1
    )
        return 1;

    return 0;
end