USP_DATAFORMTEMPLATE_EDIT_ITINERARYGROUP

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ITINERARYGROUP (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @LEADERID uniqueidentifier,
    @NAME nvarchar(154),
    @ATTENDEES xml,
    @EXCEEDCAPACITY bit,
    @GROUPSALESGROUPTYPECODEID uniqueidentifier,
    @IGNORECONFLICTS bit
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @NEWATTENDEECOUNT decimal;
    select 
        @NEWATTENDEECOUNT = isnull(sum(QUANTITY),0) 
    from dbo.UFN_ITINERARY_GETATTENDEES_FROMITEMLISTXML(@ATTENDEES);

    declare @OLDATTENDEECOUNT int;
    select 
        @OLDATTENDEECOUNT = isnull(sum(ITINERARYATTENDEE.QUANTITY),0)
    from dbo.ITINERARYATTENDEE
    where ITINERARYATTENDEE.ITINERARYID = @ID;

    begin try
        declare @START datetime;
        declare @END datetime;
        declare @RESERVATIONID uniqueidentifier;
        declare @CURRENTGROUPSALESGROUPTYPECODEID uniqueidentifier = null;
        declare @STATUSCODE tinyint;

        select 
            @START = isnull(ITINERARY.STARTDATETIME, RESERVATION.ARRIVALDATE),
            @END = isnull(ITINERARY.ENDDATETIME, RESERVATION.ARRIVALDATE),
            @RESERVATIONID = RESERVATION.ID,
            @CURRENTGROUPSALESGROUPTYPECODEID = ITINERARY.GROUPSALESGROUPTYPECODEID,
            @STATUSCODE = SALESORDER.STATUSCODE
        from dbo.RESERVATION
        inner join dbo.ITINERARY on 
            RESERVATION.ID = ITINERARY.RESERVATIONID
        inner join dbo.SALESORDER on 
            SALESORDER.ID = RESERVATION.ID
        where ITINERARY.ID = @ID;     

        update dbo.ITINERARY set
            NAME = @NAME,
            LEADERID = @LEADERID,
            GROUPSALESGROUPTYPECODEID = @GROUPSALESGROUPTYPECODEID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID;        

        if @STATUSCODE = 5
            raiserror('BBERR_INVALIDSTATUS', 13, 1);        

        if (@GROUPSALESGROUPTYPECODEID is not null) and (@CURRENTGROUPSALESGROUPTYPECODEID is null or (@CURRENTGROUPSALESGROUPTYPECODEID != @GROUPSALESGROUPTYPECODEID))
            or exists
            (
                select
                    PRICETYPECODEID,
                    QUANTITY
                from (
                    select distinct
                        PRICETYPECODEID as PRICETYPECODEID,
                        QUANTITY as QUANTITY
                    from
                        dbo.ITINERARY
                    inner join
                        dbo.ITINERARYATTENDEE on ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
                    where
                        ITINERARY.ID = @ID

                    union all

                    select distinct
                        T.attendees.value('(PRICETYPECODEID)[1]', 'uniqueidentifier') as PRICETYPECODEID,
                        T.attendees.value('(QUANTITY)[1]', 'integer') as QUANTITY
                    from
                        @ATTENDEES.nodes('/ATTENDEES/ITEM') T(attendees)
                    where
                        T.attendees.value('(QUANTITY)[1]', 'integer') > 0
                ) [ITINERARIES]
                group by
                    PRICETYPECODEID,
                    QUANTITY
                having
                    count(*) = 1        
            )
        begin        
            -- Validate that the attendee count is not greater than the capacity available
            if @EXCEEDCAPACITY = 0
            begin
                declare @MAXIMUMCAPACITY int;
                select @MAXIMUMCAPACITY = MAXIMUMCAPACITY
                from dbo.GROUPSALESDEFAULT

                if @MAXIMUMCAPACITY > 0
                begin            
                    if @NEWATTENDEECOUNT > dbo.UFN_GROUPSALESCAPACITY_MINCAPACITYREMAININGFORDATES(@START, @END, @ID, 1)
                        raiserror('ERR_EXCEEDSCAPACITY', 13, 1);
                end
            end

            declare @ITINERARYITEMSEXIST bit = 0;
            if exists 
                (
                    select 1 from dbo.ITINERARYITEM 
                    where 
                        ITINERARYITEM.ITINERARYID = @ID and 
                        ITINERARYITEM.ITEMTYPECODE <> 3
                )
                set @ITINERARYITEMSEXIST = 1;

            -- Validate the the itinerary does not already have a group type
            if    @CURRENTGROUPSALESGROUPTYPECODEID is not null and 
                @CURRENTGROUPSALESGROUPTYPECODEID != @GROUPSALESGROUPTYPECODEID
                raiserror('ERR_ITINERARY_ALREADYASSOCIATEDWITHGROUPTYPE', 13, 1);

            if @NAME = '' and @LEADERID is not null
                set @NAME = dbo.UFN_CONSTITUENT_BUILDNAME(@LEADERID);


            exec dbo.USP_ITINERARY_GETATTENDEES_UPDATEFROMXML @ID, @ATTENDEES, @CHANGEAGENTID, @CURRENTDATE;

            if    @CURRENTGROUPSALESGROUPTYPECODEID is null and 
                @GROUPSALESGROUPTYPECODEID is not null
            begin
                exec dbo.USP_ITINERARY_LOADREQUIREDRESOURCESBYGROUPTYPE @ID, @CHANGEAGENTID;
            end

            if @NEWATTENDEECOUNT <> @OLDATTENDEECOUNT
            begin
                update dbo.ITINERARYSTAFFRESOURCE
                    set QUANTITYNEEDED = ceiling(@NEWATTENDEECOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE),
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                from dbo.ITINERARYSTAFFRESOURCE
                inner join dbo.VOLUNTEERTYPE on 
                    ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
                where 
                    ITINERARYSTAFFRESOURCE.ITINERARYID = @ID and
                    VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 and
                    ITINERARYSTAFFRESOURCE.QUANTITYNEEDED <> ceiling(@NEWATTENDEECOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE);

                if @ITINERARYITEMSEXIST = 1
                    update dbo.ITINERARYITEMSTAFFRESOURCE set 
                        QUANTITYNEEDED = ceiling(@NEWATTENDEECOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE),
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    from dbo.ITINERARYITEMSTAFFRESOURCE
                    inner join dbo.VOLUNTEERTYPE on 
                        ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
                    inner join dbo.ITINERARYITEM on 
                        ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
                    where 
                        ITINERARYITEM.ITINERARYID = @ID and
                        VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 and
                        ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED <> ceiling(@NEWATTENDEECOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE);

                /* Since the number of attendees has changed we need to make sure to update the 
                    resource sales order items.  This has been incorporated into Update Flat Rate */

            end    

            exec dbo.USP_ITINERARY_UPDATEITEMQUANTITIES @ID, @CHANGEAGENTID;        

            exec dbo.USP_RESERVATION_UPDATEFLATRATE @RESERVATIONID, @CHANGEAGENTID, @CURRENTDATE;            

            --Check for conflicts, happens after loading group type so we just need to check in one place
            if @ITINERARYITEMSEXIST = 1
            begin
                exec dbo.USP_ITINERARY_CHECKPERTICKETRESOURCES @ID;

                if @IGNORECONFLICTS = 0
                begin
                    /* Get the resources from the itinerary AND the group type */
                    declare @RESOURCES xml;

                    set @RESOURCES = dbo.UFN_ITINERARYRESOURCE_GETRESOURCES_TOITEMLISTXML(@ID);

                    /* Get the staff resources from the itinerary AND the group type */
                    declare @STAFFRESOURCES xml;
                    set @STAFFRESOURCES = dbo.UFN_ITINERARY_GETSTAFFRESOURCES_TOITEMLISTXML(@ID);                                

                    /* Check for conflicts with the combined resources */
                    if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                    (
                        @START,@END,
                        null, @RESOURCES, @STAFFRESOURCES,
                        null, @ID, null,
                        0,
                        1, -- Ignore Itinerary Resources
                        0,
                        0
                    ) = 1
                    begin                    
                        raiserror('BBERR_CONFLICTSEXIST', 13, 1);
                        return 1;
                    end    

                    declare @CONFLICTRESOURCES table
                    (
                        ITINERARYITEMID uniqueidentifier,
                        STARTTIME datetime,
                        ENDTIME datetime,
                        RESOURCES xml,
                        STAFFRESOURCES xml
                    )
                    insert into @CONFLICTRESOURCES
                    select 
                        ITINERARYITEM.ID,
                        ITINERARYITEM.STARTDATETIME, 
                        ITINERARYITEM.ENDDATETIME,
                        dbo.UFN_ITINERARYITEMRESOURCE_GETRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID),
                        dbo.UFN_ITINERARYITEM_GETSTAFFRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID)
                    from dbo.ITINERARYITEM 
                    where 
                        ITINERARYID = @ID and
                        (
                            exists
                            (
                                select 1 from dbo.ITINERARYITEMRESOURCE
                                inner join dbo.RESOURCE on RESOURCE.ID = ITINERARYITEMRESOURCE.RESOURCEID
                                where
                                    ITINERARYITEMID = ITINERARYITEM.ID and
                                    RESOURCE.ISPERTICKETITEM = 1
                            )
                            or
                            exists
                            (
                                select 1 from dbo.ITINERARYITEMSTAFFRESOURCE
                                inner join dbo.VOLUNTEERTYPE on VOLUNTEERTYPE.ID = ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID
                                where
                                    ITINERARYITEMID = ITINERARYITEM.ID and
                                    VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0
                            )
                        )

                    if exists
                    (
                        select top 1 1 from @CONFLICTRESOURCES
                        where 
                            dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                            (
                                STARTTIME,ENDTIME,
                                null, RESOURCES, STAFFRESOURCES,
                                null, @ID, ITINERARYITEMID,
                                0,
                                1, -- Ignore Itinerary
                                1, -- Ignore Itinerary Item
                                0
                            ) = 1
                    )
                    begin                    
                        raiserror('BBERR_ITINERARYITEMCONFLICTSEXIST', 13, 1);
                        return 1;
                    end
                end
            end

            if @STATUSCODE = 1
                exec dbo.USP_RESERVATION_ADJUSTCOMPLETEDORDER @RESERVATIONID, @CHANGEAGENTID, @CURRENTDATE
        end
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;