USP_RESERVATION_UPDATEATTENDEES

        CREATE procedure dbo.USP_RESERVATION_UPDATEATTENDEES
        (
            @ID uniqueidentifier = null,
            @IGNORECAPACITY bit = null,
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTDATE datetime = null,
            @UPDATEPERTICKETBALANCE bit = 1,
            @CALCULATEFEESDISCOUNTSANDTAXES bit = 1
        )
        as
        begin

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

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

            exec dbo.USP_RESERVATION_UPDATEITEMQUANTITIES @ID, @CHANGEAGENTID, @CURRENTDATE, @UPDATEPERTICKETBALANCE;

            declare @ORDERITEMS table
            (
                ID uniqueidentifier,
                QUANTITY decimal(20,4),
                PRICETYPECODEID uniqueidentifier,
                DESCRIPTION nvarchar(255),
                EVENTID uniqueidentifier,
                PROGRAMID uniqueidentifier,
                PRICE money,
                PRICINGSTRUCTURECODE tinyint,
                ITINERARYITEMTYPECODE tinyint
            );

            -- Scheduled Events, distinct per event

            with CTE_DISTINCT_EVENTS as
            (
                select distinct
                    ITINERARYATTENDEE.QUANTITY as QUANTITY,
                    ITINERARYATTENDEE.PRICETYPECODEID as PRICETYPECODEID,
                    EVENTID,
                    EVENT.PROGRAMID
                from dbo.ITINERARY
                inner join dbo.ITINERARYATTENDEE on
                    ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
                inner join dbo.ITINERARYITEM on
                    ITINERARY.ID = ITINERARYITEM.ITINERARYID
                inner join dbo.EVENT on
                    ITINERARYITEM.EVENTID = EVENT.ID
                where
                    ITINERARY.RESERVATIONID = @ID and
                    ITINERARYITEM.ITEMTYPECODE = 0 and
                    ITINERARYATTENDEE.PRICETYPECODEID not in 
                    (
                        select PRICETYPECODEID 
                        from dbo.SALESORDERITEMTICKET
                        inner join dbo.SALESORDERITEM on
                            SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                        where 
                            SALESORDERITEM.SALESORDERID = @ID and
                            SALESORDERITEMTICKET.EVENTID = ITINERARYITEM.EVENTID
                    )
            )            
            insert into @ORDERITEMS
            ( QUANTITY, PRICETYPECODEID, EVENTID, PROGRAMID, PRICINGSTRUCTURECODE, ITINERARYITEMTYPECODE)
            select
                sum(QUANTITY),
                PRICETYPECODEID,
                EVENTID,
                PROGRAMID,
                0,  -- Per quantity

                0
            from CTE_DISTINCT_EVENTS
            group by PRICETYPECODEID, EVENTID, PROGRAMID;

            -- daily admission programs, distinct per program per day    

            with CTE_DISTINCT as
            (
                select distinct
                    ITINERARYATTENDEE.QUANTITY as QUANTITY,
                    ITINERARYATTENDEE.PRICETYPECODEID as PRICETYPECODEID,
                    PROGRAMID
                from dbo.ITINERARY
                inner join dbo.ITINERARYATTENDEE on
                    ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
                inner join dbo.ITINERARYITEM on
                    ITINERARY.ID = ITINERARYITEM.ITINERARYID
                where
                    ITINERARY.RESERVATIONID = @ID and
                    ITINERARYITEM.ITEMTYPECODE = 1 and
                    ITINERARYATTENDEE.PRICETYPECODEID not in 
                    (
                        select PRICETYPECODEID 
                        from dbo.SALESORDERITEMTICKET
                        inner join dbo.SALESORDERITEM on
                            SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                        where 
                            SALESORDERITEM.SALESORDERID = @ID and
                            SALESORDERITEMTICKET.PROGRAMID = ITINERARYITEM.PROGRAMID
                    )
            )            
            insert into @ORDERITEMS
            ( QUANTITY, PRICETYPECODEID, PROGRAMID, PRICINGSTRUCTURECODE, ITINERARYITEMTYPECODE)
            select
                sum(QUANTITY),
                PRICETYPECODEID,
                PROGRAMID,
                0,  -- Per quantity

                1
            from CTE_DISTINCT
            group by PRICETYPECODEID, PROGRAMID

            -- additional information that couldn't be done with distinct

            update @ORDERITEMS set
                DESCRIPTION = EVENT.NAME + ' - '
            from dbo.EVENT
            where
                EVENT.ID = [@ORDERITEMS].EVENTID and
                [@ORDERITEMS].ID is null

            update @ORDERITEMS set
                DESCRIPTION = PROGRAM.NAME + ' - '
            from dbo.PROGRAM
            where
                PROGRAM.ID = [@ORDERITEMS].PROGRAMID and
                [@ORDERITEMS].ID is null

            -- final information for daily admission programs and events

            update @ORDERITEMS set
                [@ORDERITEMS].ID = newid(),
                DESCRIPTION = [@ORDERITEMS].DESCRIPTION + PRICETYPECODE.DESCRIPTION
            from dbo.PRICETYPECODE
            where
                PRICETYPECODE.ID = [@ORDERITEMS].PRICETYPECODEID and
                [@ORDERITEMS].ID is null

            -- Events with specific prices

            update @ORDERITEMS set
                PRICE = PROGRAMEVENTPRICE.FACEPRICE
            from dbo.PROGRAMEVENTPRICE
            where 
                PROGRAMEVENTPRICE.EVENTID = [@ORDERITEMS].EVENTID and
                [@ORDERITEMS].PRICETYPECODEID = PROGRAMEVENTPRICE.PRICETYPECODEID

            -- Events without specific prices

            update @ORDERITEMS set
                PRICE = PROGRAMPRICE.FACEPRICE
            from dbo.PROGRAMPRICE 
            inner join dbo.EVENT on 
                EVENT.PROGRAMID = PROGRAMPRICE.PROGRAMID
            where 
                EVENT.ID = [@ORDERITEMS].EVENTID and
                [@ORDERITEMS].PRICE is null and
                [@ORDERITEMS].PRICETYPECODEID = PROGRAMPRICE.PRICETYPECODEID and
                not exists 
                (
                    select 1 from dbo.PROGRAMEVENTPRICE
                    where PROGRAMEVENTPRICE.EVENTID = EVENT.ID
                )

            --Programs with specific prices

            update @ORDERITEMS set
                PRICE = PROGRAMPRICE.FACEPRICE
            from dbo.PROGRAMPRICE
            where 
                PROGRAMPRICE.PROGRAMID = [@ORDERITEMS].PROGRAMID and
                [@ORDERITEMS].PRICETYPECODEID = PROGRAMPRICE.PRICETYPECODEID

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

            declare @ISFLATRATE bit = 0
            select @ISFLATRATE = 1 from dbo.RESERVATION
            where ID = @ID and PRICINGCODE = 1
            if @ISFLATRATE = 1
            begin
                update @ORDERITEMS set PRICINGSTRUCTURECODE = 1
                where 
                    dbo.UFN_RESERVATIONRATESCALE_ISEVENTINCLUDED(@ID,[@ORDERITEMS].EVENTID) = 1 or
                    dbo.UFN_RESERVATIONRATESCALE_ISPROGRAMINCLUDED(@ID,[@ORDERITEMS].PROGRAMID) = 1
            end

            if exists (select 1 from @ORDERITEMS where PRICE is null)
                raiserror('BBERR_INVALIDPRICETYPE', 13, 1);

            -- insert new flat rate items

            insert into dbo.SALESORDERITEM
                (ID, SALESORDERID, TYPECODE, DESCRIPTION, QUANTITY, PRICE, PRICINGSTRUCTURECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                ITEMS.ID, 
                @ID, 
                0, 
                ITEMS.DESCRIPTION, 
                ITEMS.QUANTITY, 
                ITEMS.PRICE,
                ITEMS.PRICINGSTRUCTURECODE,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from
                @ORDERITEMS ITEMS
            where
                ITEMS.PRICINGSTRUCTURECODE = 1;  -- Flat rate


            -- insert new non-flat rate items

            if @UPDATEPERTICKETBALANCE = 1
                insert into dbo.SALESORDERITEM
                    (ID, SALESORDERID, TYPECODE, DESCRIPTION, QUANTITY, PRICE, PRICINGSTRUCTURECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    ITEMS.ID, 
                    @ID, 
                    0, 
                    ITEMS.DESCRIPTION, 
                    ITEMS.QUANTITY, 
                    ITEMS.PRICE,
                    ITEMS.PRICINGSTRUCTURECODE,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CURRENTDATE, 
                    @CURRENTDATE
                from
                    @ORDERITEMS ITEMS
                where
                    ITEMS.PRICINGSTRUCTURECODE <> 1;  -- Not flat rate


            else
                insert into dbo.SALESORDERITEM
                    (ID, SALESORDERID, TYPECODE, DESCRIPTION, QUANTITY, PRICE, PRICINGSTRUCTURECODE, FLATRATEPRICE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    ITEMS.ID, 
                    @ID, 
                    0, 
                    ITEMS.DESCRIPTION, 
                    ITEMS.QUANTITY, 
                    ITEMS.PRICE,
                    2,  -- Override rate

                    0,  -- Add zero dollar tickets to keep the reservation price the same

                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CURRENTDATE, 
                    @CURRENTDATE
                from
                    @ORDERITEMS ITEMS
                where
                    ITEMS.PRICINGSTRUCTURECODE <> 1;  -- Not flat rate


            insert into dbo.SALESORDERITEMTICKET
            (
                ID,
                EVENTID,
                PROGRAMID,
                PROGRAMNAME,
                PROGRAMCATEGORYNAME,
                PRICETYPECODEID,
                PRICE, 
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED, 
                DATECHANGED
            )
            select
                ITEMS.ID, 
                ITEMS.EVENTID,
                ITEMS.PROGRAMID,
                case ITEMS.ITINERARYITEMTYPECODE
                    when 1 then coalesce((select [NAME] from dbo.[PROGRAM] where [ID] = [ITEMS].[PROGRAMID]),'')
                    when 0 then coalesce((
                        select [PROGRAM].[NAME] 
                        from dbo.[EVENT]
                        inner join dbo.[PROGRAM]
                            on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                        where [EVENT].[ID] = [ITEMS].[EVENTID]
                    ),'')
                end,
                case ITEMS.ITINERARYITEMTYPECODE
                    when 1 then coalesce((
                        select [PROGRAMCATEGORYCODE].[DESCRIPTION] 
                        from dbo.[PROGRAM] 
                        inner join dbo.[PROGRAMCATEGORYCODE]
                            on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
                        where [PROGRAM].[ID] = [ITEMS].[PROGRAMID]
                    ),'')
                    when 0 then coalesce((
                        select [PROGRAMCATEGORYCODE].[DESCRIPTION]
                        from dbo.[EVENT]
                        inner join dbo.[PROGRAM]
                            on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                        inner join dbo.[PROGRAMCATEGORYCODE]
                            on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
                        where [EVENT].[ID] = [ITEMS].[EVENTID]
                    ),'')
                end,
                ITEMS.PRICETYPECODEID,
                ITEMS.PRICE,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @ORDERITEMS ITEMS

            -- Be sure the Event capacity isn't over maximum

            if not @IGNORECAPACITY = 1
            begin
                declare @ERROREVENTID uniqueidentifier
                select top(1) @ERROREVENTID = EVENTID from dbo.SALESORDERITEMTICKET SOITOUTER
                    inner join dbo.SALESORDERITEM SOIOUTER on 
                        SOITOUTER.ID = SOIOUTER.ID
                    where
                        SOIOUTER.SALESORDERID = @ID and
                        dbo.UFN_SALESORDERTICKET_VALIDQUANTITY(
                            SOITOUTER.EVENTID, 
                            @ID,
                            isnull((
                                select sum(SOIINNER.QUANTITY) 
                                from dbo.SALESORDERITEM SOIINNER
                                inner join dbo.SALESORDERITEMTICKET SOITINNER on
                                    SOIINNER.ID = SOITINNER.ID
                                where
                                    SOIINNER.SALESORDERID = @ID and
                                    SOITINNER.EVENTID = SOITOUTER.EVENTID
                                ),0)                                
                        ) = 0

                if @ERROREVENTID is not null
                begin
                    declare @ERRORMESSAGE nvarchar(500)
                    declare @DIFFERENCE integer = 0;
                    declare @QUANTITY integer = 0;

                    set @ERRORMESSAGE = dbo.UFN_TRANSLATIONFUNCTION_EVENT_GETNAME(@ERROREVENTID)
                    set @DIFFERENCE = 0 - dbo.UFN_EVENT_GETAVAILABILITY(@ERROREVENTID)

                    set @ERRORMESSAGE = @ERRORMESSAGE + ' does not have enough capacity for this group. You can either reduce the group size by ' + 
                                convert(nvarchar(10), @DIFFERENCE) + ' or choose a different event.' 

                    raiserror(@ERRORMESSAGE, 13, 1)            
                end
            end

            if @ISFLATRATE = 1
            begin
                exec dbo.USP_RESERVATION_DISTRIBUTEAPPLICATIONS @ID, @CHANGEAGENTID, @CURRENTDATE;

                if @CALCULATEFEESDISCOUNTSANDTAXES = 1
                begin
                    exec dbo.USP_SALESORDER_CALCULATEFEES @ID, @CHANGEAGENTID;
                    exec dbo.USP_RESERVATION_CALCULATEFLATRATEFEES @ID, @CHANGEAGENTID;

                    --exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ID, @CHANGEAGENTID;

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


                    -- Generate taxes last

                    exec dbo.USP_SALESORDER_CALCULATETAXES @ID, @CHANGEAGENTID;
                    exec dbo.USP_RESERVATIONRATESCALE_UPDATETAXES @ID, @CHANGEAGENTID, @CURRENTDATE;
                end
            end
            else
            begin
                if @CALCULATEFEESDISCOUNTSANDTAXES = 1
                begin
                    exec dbo.USP_SALESORDER_CALCULATEFEES @ID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_CALCULATETAXES @ID, @CHANGEAGENTID;
                end
            end

        end