USP_ITINERARYITEMDAILYADMISSION_ADD

        CREATE procedure dbo.USP_ITINERARYITEMDAILYADMISSION_ADD
        (
            @ID uniqueidentifier = null output,
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTDATE datetime = null,
            @ITINERARYID uniqueidentifier,
            @PROGRAMID uniqueidentifier = null,
            @STARTDATE date = null,
            @STARTTIME dbo.UDT_HOURMINUTE = null,
            @ENDTIME dbo.UDT_HOURMINUTE = null,
            @NOTES nvarchar(500) = '',
            @RESOURCES xml = null,
            @IGNORECONFLICTS bit = 0,
            @STAFFRESOURCES xml = null
        )
        as
        begin
            set nocount on;

            if @ID is null
                set @ID = newid()

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

            if @CURRENTDATE is null
                set @CURRENTDATE = getdate()

            if @IGNORECONFLICTS = 0
            begin
                declare @OLDITINERARYSTART datetime;
                declare @OLDITINERARYEND datetime;
                declare @ITINERARYSTART datetime;
                declare @ITINERARYEND datetime;
                declare @ITINERARYITEMSTART datetime;
                declare @ITINERARYITEMEND datetime;

                declare @CHECKITINERARYCONFLICTS bit = 0;

                set @ITINERARYITEMSTART = dbo.UFN_DATE_ADDHOURMINUTE(@STARTDATE, @STARTTIME);
                set @ITINERARYITEMEND   = dbo.UFN_DATE_ADDHOURMINUTE(@STARTDATE, @ENDTIME);

                select
                    @OLDITINERARYSTART = ITINERARY.STARTDATETIME,
                    @OLDITINERARYEND   = ITINERARY.ENDDATETIME
                from dbo.ITINERARY
                where ID = @ITINERARYID

                select
                    @ITINERARYSTART = min(STARTDATETIME),
                    @ITINERARYEND = max(ENDDATETIME)
                from
                (
                    select 
                        @ITINERARYITEMSTART as STARTDATETIME,
                        @ITINERARYITEMEND as ENDDATETIME

                    union all

                    select 
                        ITINERARYITEM.STARTDATETIME as STARTDATETIME,
                        ITINERARYITEM.ENDDATETIME as ENDDATETIME
                    from dbo.ITINERARYITEM
                    where 
                        ITINERARYID = @ITINERARYID and
                        ID <> @ID
                ) T

                if @OLDITINERARYSTART is null or @OLDITINERARYSTART <> @ITINERARYSTART or
                    @OLDITINERARYEND is null or @OLDITINERARYEND <> @ITINERARYEND
                    set @CHECKITINERARYCONFLICTS = 1

                if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                (
                    @ITINERARYITEMSTART, @ITINERARYITEMEND, 
                    null, @RESOURCES, null,
                    null, @ITINERARYID, @ID,
                    0, -- Ignore Super Record 

                    0, -- Ignore Record

                    1, -- Ignore Sub Record

                    0 -- Ignore All Sub Records of Record

                ) = 1
                begin
                    raiserror('BBERR_RESOURCECONFLICTSEXIST', 13, 1);
                    return 1;
                end

                if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                (
                    @ITINERARYITEMSTART, @ITINERARYITEMEND, 
                    null, null, @STAFFRESOURCES,
                    null, @ITINERARYID, @ID,
                    0, -- Ignore Super Record 

                    0, -- Ignore Record

                    1, -- Ignore Sub Record

                    0 -- Ignore All Sub Records of Record

                ) = 1
                begin
                    raiserror('BBERR_STAFFRESOURCECONFLICTSEXIST', 13, 1);
                    return 1;
                end

                -- If itinerary times changing, need to check on conflicts

                if @CHECKITINERARYCONFLICTS = 1
                begin

                    declare @ITINERARYRESOURCES xml
                    set @ITINERARYRESOURCES = dbo.UFN_ITINERARYRESOURCE_GETRESOURCES_TOITEMLISTXML(@ITINERARYID);

                    declare @ITINERARYSTAFFRESOURCES xml
                    set @ITINERARYSTAFFRESOURCES = dbo.UFN_ITINERARY_GETSTAFFRESOURCES_TOITEMLISTXML(@ITINERARYID);

                    if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                    (
                        @ITINERARYSTART, @ITINERARYEND, 
                        null, @ITINERARYRESOURCES, @ITINERARYSTAFFRESOURCES,
                        null, @ITINERARYID, @ID,
                        0, -- Ignore Super Record 

                        1, -- Ignore Record

                        1, -- Ignore Sub Record

                        0 -- Ignore All Sub Records of Record

                    ) = 1
                    begin
                        raiserror('BBERR_ITINERARYRESOURCECONFLICTSEXIST', 13, 1);
                        return 1;
                    end
                end
            end

            begin try

                declare @ORDERID uniqueidentifier;
                declare @ARRIVALDATE datetime;
                declare @ARRIVALTIME dbo.UDT_HOURMINUTE;
                declare @DAILYADMISSIONDATE datetime;
                declare @ISFLATRATE bit = 0
                declare @STATUSCODE tinyint;

                select @ORDERID = RESERVATION.ID,
                    @ARRIVALDATE = ARRIVALDATE,
                    @ARRIVALTIME = ARRIVALTIME,
                    @ISFLATRATE = case when PRICINGCODE = 1 then 1 else 0 end,
                    @STATUSCODE = SALESORDER.STATUSCODE
                from dbo.RESERVATION
                inner join dbo.ITINERARY on ITINERARY.RESERVATIONID = RESERVATION.ID
                inner join dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
                where ITINERARY.ID = @ITINERARYID

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

                set @ARRIVALDATE = dbo.UFN_DATE_ADDHOURMINUTE(@ARRIVALDATE, @ARRIVALTIME);

                declare @DESCRIPTION nvarchar(100);
                select @DESCRIPTION = PROGRAM.NAME from dbo.PROGRAM where PROGRAM.ID = @PROGRAMID;

                if datediff(day, @ARRIVALDATE, @STARTDATE) > 4
                    raiserror('BBERR_OUTSIDEDATERANGE', 13, 1)

                if not exists
                ( 
                    select 1 from dbo.ITINERARYITEM 
                    where 
                        ITINERARYITEM.ITINERARYID = @ITINERARYID and 
                        ITINERARYITEM.ITEMTYPECODE = 1 and 
                        PROGRAMID = @PROGRAMID and 
                        STARTDATE = @STARTDATE
                )
                begin

                    declare @ORDERITEMS table
                    (
                        ID uniqueidentifier,
                        QUANTITY decimal(20,4),
                        PRICETYPE nvarchar(100),
                        PRICETYPECODEID uniqueidentifier,
                        PRICE money,
                        PRICINGSTRUCTURECODE tinyint
                    );

                    insert into @ORDERITEMS
                    select
                        newid(),
                        IT.QUANTITY,
                        dbo.UFN_PRICETYPECODE_GETDESCRIPTION(IT.PRICETYPECODEID) as PRICETYPE,
                        IT.PRICETYPECODEID,
                        PP.FACEPRICE,
                        0
                    from dbo.ITINERARYATTENDEE IT
                    inner join dbo.PROGRAMPRICE PP on PP.PRICETYPECODEID = IT.PRICETYPECODEID
                    inner join dbo.PRICETYPECODE PTC on PTC.ID = PP.PRICETYPECODEID
                    where PP.PROGRAMID = @PROGRAMID and
                        IT.ITINERARYID = @ITINERARYID and
                        not exists (select 1 
                                    from dbo.SALESMETHODEXCLUDEDPRICETYPE 
                                    inner join dbo.SALESMETHOD on SALESMETHOD.ID = SALESMETHODEXCLUDEDPRICETYPE.SALESMETHODID
                                    where SALESMETHOD.TYPECODE = 3 and
                                    SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID = PP.PRICETYPECODEID)

                    if exists 
                    (
                        select ITINERARYATTENDEE.PRICETYPECODEID 
                        from dbo.ITINERARYATTENDEE
                        where ITINERARYATTENDEE.ITINERARYID = @ITINERARYID and
                            PRICETYPECODEID not in (select PRICETYPECODEID from @ORDERITEMS)
                    )
                    begin
                            raiserror('BBERR_INVALIDPRICETYPE', 13, 1);
                    end


                    update dbo.SALESORDERITEM set
                        SALESORDERITEM.QUANTITY = SALESORDERITEM.QUANTITY + ITEMS.QUANTITY,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    from @ORDERITEMS items
                    where 
                        SALESORDERID = @ORDERID and
                        SALESORDERITEM.ID in
                        ( 
                            select ID from dbo.SALESORDERITEMTICKET 
                            where PROGRAMID = @PROGRAMID and 
                            SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID
                        )

                    -- Set items to be flat rate if the reservation is flat rate and the program is included

                    if @ISFLATRATE = 1
                    begin
                        update @ORDERITEMS set PRICINGSTRUCTURECODE = 1
                        where dbo.UFN_RESERVATIONRATESCALE_ISPROGRAMINCLUDED(@ORDERID,@PROGRAMID) = 1
                    end

                    insert into dbo.SALESORDERITEM
                    (
                        ID, 
                        SALESORDERID, 
                        TYPECODE, 
                        DESCRIPTION, 
                        QUANTITY, 
                        PRICE, 
                        PRICINGSTRUCTURECODE, 
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        ITEMS.ID, 
                        @ORDERID, 
                        0, 
                        @DESCRIPTION + ' - ' + ITEMS.PRICETYPE, 
                        ITEMS.QUANTITY, 
                        ITEMS.PRICE,
                        ITEMS.PRICINGSTRUCTURECODE,
                        @CHANGEAGENTID, @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
                    from @ORDERITEMS ITEMS
                    where 
                        not exists
                        (
                            select 1 from dbo.SALESORDERITEM
                            inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                            where 
                                SALESORDERID = @ORDERID and
                                PROGRAMID = @PROGRAMID and
                                SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID
                        );

                    insert into dbo.SALESORDERITEMTICKET
                    (
                        ID,
                        PROGRAMID,
                        PROGRAMNAME,
                        PROGRAMCATEGORYNAME,
                        PRICETYPECODEID,
                        PRICE,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED, 
                        DATECHANGED
                    )
                    select
                        ITEMS.ID, 
                        @PROGRAMID,
                        coalesce((select [NAME] from dbo.[PROGRAM] where [ID] = @PROGRAMID),''),
                        coalesce((
                            select [PROGRAMCATEGORYCODE].[DESCRIPTION] 
                            from dbo.[PROGRAM] 
                            inner join dbo.[PROGRAMCATEGORYCODE]
                                on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
                            where [PROGRAM].[ID] = @PROGRAMID
                        ),''),
                        ITEMS.PRICETYPECODEID,
                        ITEMS.PRICE,
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE
                    from @ORDERITEMS ITEMS
                    where
                        ITEMS.ID in
                        ( select ID from dbo.SALESORDERITEM where SALESORDERID = @ORDERID )
                end

                if @ENDTIME = '0000'
                begin
                    set @ENDTIME = '2359'
                end

                insert into dbo.ITINERARYITEM
                (
                    ID,
                    STARTTIME,
                    ENDTIME,
                    NOTES,
                    PROGRAMID,
                    ITINERARYID,
                    NAME,
                    ITEMTYPECODE,
                    STARTDATE,
                    ENDDATE,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                values
                (
                    @ID,
                    @STARTTIME,
                    @ENDTIME,
                    @NOTES,
                    @PROGRAMID,
                    @ITINERARYID,
                    @DESCRIPTION,
                    1,
                    @STARTDATE,
                    @STARTDATE,
                    @CHANGEAGENTID, @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
                )

                -- Need to create the job, the job occurrence will be created using the trigger

                exec dbo.USP_RESOURCE_PROCESS @RESOURCES output, @CHANGEAGENTID, 0;
                exec dbo.USP_STAFFRESOURCE_PROCESS @STAFFRESOURCES output, @CHANGEAGENTID, 0, 0, 0;

                exec dbo.USP_ITINERARYITEMRESOURCE_GETRESOURCES_ADDFROMXML @ID, @RESOURCES, @CHANGEAGENTID;
                exec dbo.USP_ITINERARYITEM_GETSTAFFRESOURCES_ADDFROMXML @ID, @STAFFRESOURCES, @CHANGEAGENTID;

                exec dbo.USP_ITINERARY_CHECKPERTICKETRESOURCES @ITINERARYID;
                exec dbo.USP_ITINERARY_RESOURCES_SALESORDERSYNC @ITINERARYID, @ORDERID, @CHANGEAGENTID
                exec dbo.USP_ITINERARY_STAFFRESOURCES_SALESORDERSYNC @ITINERARYID, @ORDERID, @CHANGEAGENTID
                exec dbo.USP_ITINERARYITEM_RESOURCES_SALESORDERSYNC @ID, @ORDERID, @CHANGEAGENTID
                exec dbo.USP_ITINERARYITEM_STAFFRESOURCES_SALESORDERSYNC @ID, @ORDERID, @CHANGEAGENTID


                if @ISFLATRATE = 1
                begin
                    exec dbo.USP_RESERVATION_UPDATEFLATRATEPERTICKETPRICE @ORDERID, null, @CHANGEAGENTID, @CURRENTDATE;
                    exec dbo.USP_RESERVATION_DISTRIBUTEAPPLICATIONS @ORDERID, @CHANGEAGENTID, @CURRENTDATE;    
                    exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;
                    exec dbo.USP_RESERVATION_CALCULATEFLATRATEFEES @ORDERID, @CHANGEAGENTID;
                    --exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ORDERID, @CHANGEAGENTID;

                    --exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ORDERID, @CHANGEAGENTID;                

                    exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;
                    exec dbo.USP_RESERVATIONRATESCALE_UPDATETAXES @ORDERID, @CHANGEAGENTID, @CURRENTDATE;
                end
                else
                begin
                    exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ORDERID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ORDERID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;
                end

            end try
            begin catch
                exec dbo.USP_RAISE_ERROR
                return 1
            end catch

            return 0;
        end