USP_ITINERARY_COPYFROMEXISTING

        CREATE procedure dbo.USP_ITINERARY_COPYFROMEXISTING
        (
            @ID uniqueidentifier,
            @COPYITINERARYID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier = null,
            @IGNORECONFLICTS bit = 0
        )
        as
        begin

            declare @COPYRESERVATIONDATE datetime;
            declare @RESERVATIONDATE datetime;
            declare @RESERVATIONID uniqueidentifier

            declare @COPYSTARTDATETIME datetime;
            declare @COPYENDDATETIME datetime;

            select
                @COPYRESERVATIONDATE = ARRIVALDATE,
                @COPYSTARTDATETIME = ITINERARY.STARTDATETIME,
                @COPYENDDATETIME = ITINERARY.ENDDATETIME
            from dbo.RESERVATION
            inner join dbo.ITINERARY on
                ITINERARY.RESERVATIONID = RESERVATION.ID
            where
                ITINERARY.ID = @COPYITINERARYID

            select
                @RESERVATIONDATE = ARRIVALDATE,
                @RESERVATIONID = RESERVATION.ID
            from dbo.RESERVATION
            inner join dbo.ITINERARY on
                ITINERARY.RESERVATIONID = RESERVATION.ID
            where
                ITINERARY.ID = @ID

            /* Fix up the change agent ID incase we were passed null. */
            if @CHANGEAGENTID is null
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output        

            /* Setup the current date */
            declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

            /* First clear the itinerary */
            exec dbo.USP_ITINERARY_CLEAR @ID, @RESERVATIONID, @CHANGEAGENTID;

            /* Update existing required resources with the max of the existing qty and the new track qty */
            update DESTINATION set 
                    QUANTITYNEEDED = case 
                                        when (DESTINATION.QUANTITYNEEDED > SOURCE.QUANTITYNEEDED) then 
                                            DESTINATION.QUANTITYNEEDED
                                        else SOURCE.QUANTITYNEEDED
                                    end, 
                    PRICE = COALESCE(RESOURCEPRICING.PRICE, 0),
                    PRICINGSTRUCTURECODE = COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0),
                    PERTICKETQUANTITY = RESOURCE.PERTICKETQUANTITY,
                    PERTICKETDIVISOR = RESOURCE.PERTICKETDIVISOR,
                    CHANGEDBYID = @CHANGEAGENTID, 
                    DATECHANGED = @CURRENTDATE
            from dbo.ITINERARYRESOURCE DESTINATION
            inner join dbo.ITINERARYRESOURCE SOURCE on DESTINATION.RESOURCEID = SOURCE.RESOURCEID
            inner join dbo.RESOURCE on SOURCE.RESOURCEID = RESOURCE.ID 
            left join dbo.RESOURCEPRICING on RESOURCE.ID = RESOURCEPRICING.ID
            where 
                SOURCE.ITINERARYID = @COPYITINERARYID and 
                DESTINATION.ITINERARYID = @ID

            -- Now add the itinerary resources 

            insert into dbo.ITINERARYRESOURCE
            (
                ITINERARYID,
                RESOURCEID,
                QUANTITYNEEDED,
                PRICE,
                PRICINGSTRUCTURECODE,
                PERTICKETQUANTITY,
                PERTICKETDIVISOR,
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
            )
            select 
                @ID,
                SOURCE.RESOURCEID,
                SOURCE.QUANTITYNEEDED,
                COALESCE(RESOURCEPRICING.PRICE, 0),
                COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0),
                PERTICKETQUANTITY = RESOURCE.PERTICKETQUANTITY,
                PERTICKETDIVISOR = RESOURCE.PERTICKETDIVISOR,
                @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
            from dbo.ITINERARYRESOURCE SOURCE
            inner join dbo.RESOURCE on SOURCE.RESOURCEID = RESOURCE.ID 
            left join dbo.RESOURCEPRICING on RESOURCE.ID = RESOURCEPRICING.ID
            left join dbo.ITINERARYRESOURCE DESTINATION on DESTINATION.RESOURCEID = RESOURCE.ID and DESTINATION.ITINERARYID = @ID
            where 
                SOURCE.ITINERARYID = @COPYITINERARYID and 
                DESTINATION.ID is null;

            /* Get the total attendees for the itinerary */
            declare @ATTENDEECOUNT decimal;
            select @ATTENDEECOUNT = sum(ITINERARYATTENDEE.QUANTITY)
            from dbo.ITINERARYATTENDEE
            where ITINERARYATTENDEE.ITINERARYID = @ID;

            update DESTINATION set 
                QUANTITYNEEDED = case 
                                    when VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 then 
                                        ceiling(@ATTENDEECOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE)
                                    else
                                        case 
                                            when (DESTINATION.QUANTITYNEEDED > SOURCE.QUANTITYNEEDED) then
                                                DESTINATION.QUANTITYNEEDED
                                            else
                                                SOURCE.QUANTITYNEEDED
                                        end
                                end,
                PRICE = COALESCE(VOLUNTEERTYPEPRICING.PRICE, 0),
                PRICINGSTRUCTURECODE = COALESCE(VOLUNTEERTYPEPRICING.PRICINGSTRUCTURECODE,0),
                CHANGEDBYID = @CHANGEAGENTID, 
                DATECHANGED = @CURRENTDATE
            from dbo.ITINERARYSTAFFRESOURCE DESTINATION
            inner join dbo.ITINERARYSTAFFRESOURCE SOURCE on 
                DESTINATION.VOLUNTEERTYPEID = SOURCE.VOLUNTEERTYPEID and 
                (
                    (DESTINATION.JOBID is null and SOURCE.JOBID is null) or 
                    DESTINATION.JOBID = SOURCE.JOBID
                )
            left join dbo.VOLUNTEERTYPEPRICING on SOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPEPRICING.ID
            inner join dbo.VOLUNTEERTYPE on VOLUNTEERTYPE.ID = SOURCE.VOLUNTEERTYPEID
            where 
                SOURCE.ITINERARYID = @COPYITINERARYID and 
                DESTINATION.ITINERARYID = @ID;

            -- Now add the itinerary staffresources 

            insert into dbo.ITINERARYSTAFFRESOURCE
            (
                ITINERARYID,
                VOLUNTEERTYPEID,
                QUANTITYNEEDED,
                FILLEDBYCODE,
                JOBID,
                PRICE,
                PRICINGSTRUCTURECODE,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select 
                @ID,
                SOURCE.VOLUNTEERTYPEID,
                case 
                    when VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 then 
                        ceiling(@ATTENDEECOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE) 
                    else SOURCE.QUANTITYNEEDED 
                end, 
                SOURCE.FILLEDBYCODE,
                SOURCE.JOBID,
                COALESCE(VOLUNTEERTYPEPRICING.PRICE, 0),
                COALESCE(VOLUNTEERTYPEPRICING.PRICINGSTRUCTURECODE,0),
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from dbo.ITINERARYSTAFFRESOURCE SOURCE
            left join dbo.VOLUNTEERTYPEPRICING on SOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPEPRICING.ID
            inner join dbo.VOLUNTEERTYPE on VOLUNTEERTYPE.ID = SOURCE.VOLUNTEERTYPEID
            left join dbo.ITINERARYSTAFFRESOURCE DESTINATION on 
                DESTINATION.VOLUNTEERTYPEID = SOURCE.VOLUNTEERTYPEID and 
                (
                    (DESTINATION.JOBID is null and SOURCE.JOBID is null) or 
                    DESTINATION.JOBID = SOURCE.JOBID
                ) and 
                DESTINATION.ITINERARYID = @ID
            where 
                SOURCE.ITINERARYID = @COPYITINERARYID and 
                DESTINATION.ID is null;

            declare @ITEMS table
            (
                COPYITINERARYITEMID uniqueidentifier,
                EVENTID uniqueidentifier,
                PROGRAMID uniqueidentifier,
                NAME nvarchar(100),
                NOTES nvarchar(500),
                BLOCKEVENT bit,
                STARTTIME dbo.UDT_HOURMINUTE,
                ENDTIME dbo.UDT_HOURMINUTE,
                STARTDATE datetime,
                ENDDATE datetime,
                EVENTLOCATIONID uniqueidentifier,
                ITEMTYPECODE tinyint
            )

            insert into @ITEMS
            select
                ITINERARYITEM.ID,
                null,
                case
                    when ITINERARYITEM.PROGRAMID is null then EVENT.PROGRAMID
                    else ITINERARYITEM.PROGRAMID
                end,
                ITINERARYITEM.NAME,
                ITINERARYITEM.NOTES,
                ITINERARYITEM.BLOCKEVENT,
                ITINERARYITEM.STARTTIME,
                ITINERARYITEM.ENDTIME,
                dateadd(day,datediff(day,@COPYRESERVATIONDATE,ITINERARYITEM.STARTDATE),@RESERVATIONDATE),
                dateadd(day,datediff(day,@COPYRESERVATIONDATE,ITINERARYITEM.ENDDATE),@RESERVATIONDATE),
                ITINERARYITEM.EVENTLOCATIONID,
                ITINERARYITEM.ITEMTYPECODE
            from dbo.ITINERARYITEM
            left join dbo.EVENT on
                EVENT.ID = ITINERARYITEM.EVENTID
            where
                ITINERARYITEM.ITINERARYID = @COPYITINERARYID and
                ITINERARYITEM.ITEMTYPECODE <> 3

            declare @ITINERARYITEMS xml

            set @ITINERARYITEMS = 
                (
                    select
                        COPYITINERARYITEMID,
                        EVENTID,
                        PROGRAMID,
                        NAME,
                        NOTES,
                        BLOCKEVENT,
                        STARTTIME,
                        ENDTIME,
                        STARTDATE,
                        ENDDATE,
                        EVENTLOCATIONID,
                        ITEMTYPECODE
                    from @ITEMS
                    for xml raw ('ITEM'), type, elements, root('ITINERARYITEMS'), BINARY BASE64
                )

            exec dbo.USP_ITINERARY_INSERTITEMS @ID, @ITINERARYITEMS, @CHANGEAGENTID, 0;

        end