UFN_RESERVATION_GETEXTRAFLATRATEPEOPLE

CREATE function dbo.UFN_RESERVATION_GETEXTRAFLATRATEPEOPLE
(
    @RESERVATIONID uniqueidentifier
)
returns @EXTRAPEOPLE table
(
    ITINERARYID uniqueidentifier,
    PRICETYPEID uniqueidentifier,
    QUANTITY int
)
as begin

    if exists ( select 1 
                from dbo.RESERVATIONRATESCALEPRICE RRSP 
                    inner join dbo.RESERVATIONRATESCALE RRS on RRSP.RESERVATIONRATESCALEID = RRS.ID 
                where 
                    RRSP.INUSE = 1 and
                    RRSP.GROUPMAXIMUM = -1 and
                    RRS.ID = @RESERVATIONID and
                    RRS.USEPERTICKETAFTERMAX = 1 )
    begin

        declare @ALLPEOPLE table
        (
            ITINERARYID uniqueidentifier,
            PRICETYPEID uniqueidentifier,
            PRICE money,
            QUANTITY int
        )

        insert into @ALLPEOPLE
        (
            ITINERARYID,
            PRICETYPEID,
            PRICE,
            QUANTITY
        ) select
            ITINERARYID,
            PRICETYPECODEID,
            PRICE,
            QUANTITY
        from dbo.UFN_RESERVATION_GETPERPERSONCHARGES(@RESERVATIONID, 1);

        declare @EXCESSATTENDEES int;
        declare @ITINERARYID uniqueidentifier;
        declare @PRICETYPEID uniqueidentifier;
        declare @QUANTITY int;

        select 
            @EXCESSATTENDEES = isnull(sum(QUANTITY),0)
        from dbo.ITINERARYATTENDEE
        inner join dbo.ITINERARY on
            ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
        where
            ITINERARY.RESERVATIONID = @RESERVATIONID

        select
            @EXCESSATTENDEES = case GROUPMINIMUM
                when 0 then @EXCESSATTENDEES 
                else @EXCESSATTENDEES - coalesce(GROUPMINIMUM - 1, 0)
            end
        from dbo.RESERVATIONRATESCALEPRICE
        where 
            RESERVATIONRATESCALEID = @RESERVATIONID and
            INUSE = 1

        while @EXCESSATTENDEES > 0
        begin
            select top(1)
                @ITINERARYID = ITINERARYID,
                @PRICETYPEID = PRICETYPEID,
                @QUANTITY = QUANTITY
            from @ALLPEOPLE
            order by PRICE asc

            if @ITINERARYID is null
            begin
                set @EXCESSATTENDEES = 0;
            end
            else
            begin
                if @QUANTITY >= @EXCESSATTENDEES
                begin
                    insert into @EXTRAPEOPLE (ITINERARYID, PRICETYPEID, QUANTITY) values (@ITINERARYID, @PRICETYPEID, @EXCESSATTENDEES)
                    set @EXCESSATTENDEES = 0;
                end
                else
                begin
                    insert into @EXTRAPEOPLE (ITINERARYID, PRICETYPEID, QUANTITY) values (@ITINERARYID, @PRICETYPEID, @QUANTITY)
                    set @EXCESSATTENDEES -= @QUANTITY;
                end
                delete from @ALLPEOPLE where ITINERARYID = @ITINERARYID and PRICETYPEID = @PRICETYPEID;
            end
        end
    end

    return
end