USP_RESERVATION_UPDATEFLATRATEPERTICKETPRICE

CREATE procedure dbo.USP_RESERVATION_UPDATEFLATRATEPERTICKETPRICE
(
    @RESERVATIONID uniqueidentifier,
    @RESERVATIONRATESCALEPRICEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime
)
as
begin

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

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

    if @RESERVATIONRATESCALEPRICEID is null
        set @RESERVATIONRATESCALEPRICEID = dbo.UFN_RESERVATION_GETRATESCALEPRICE(@RESERVATIONID)

    declare @ISFLATRATE bit;
    declare @USEPERTICKETAFTERMAX bit;
    declare @GROUPMAXIMUM int;

    select
        @ISFLATRATE = PRICINGCODE,
        @USEPERTICKETAFTERMAX = coalesce(RESERVATIONRATESCALE.USEPERTICKETAFTERMAX, 0),
        @GROUPMAXIMUM = coalesce(RESERVATIONRATESCALEPRICE.GROUPMAXIMUM, 0)
    from dbo.RESERVATION
        left join RESERVATIONRATESCALE on RESERVATIONRATESCALE.ID = RESERVATION.ID
        left join RESERVATIONRATESCALEPRICE on RESERVATIONRATESCALEPRICE.RESERVATIONRATESCALEID = RESERVATIONRATESCALE.ID
    where
        RESERVATION.ID = @RESERVATIONID and
        RESERVATIONRATESCALEPRICE.ID = @RESERVATIONRATESCALEPRICEID

    if @ISFLATRATE = 1 and @USEPERTICKETAFTERMAX = 1 and @GROUPMAXIMUM = -1
    begin
        declare @EXCESSATTENDEES int;
        declare @TOTALAMOUNT money;

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

        select
            @EXCESSATTENDEES = case GROUPMINIMUM
                when 0 then @EXCESSATTENDEES 
                else @EXCESSATTENDEES - coalesce(GROUPMINIMUM - 1, 0)
            end
        from dbo.RESERVATIONRATESCALEPRICE
        where ID = @RESERVATIONRATESCALEPRICEID

        select TOP(1)
            @TOTALAMOUNT = coalesce(AMOUNT, 0.0)
        from dbo.RESERVATIONRATESCALEPRICE
        where RESERVATIONRATESCALEID = @RESERVATIONID
        order by GROUPMAXIMUM desc

        declare @TICKETTYPES table
        (
            ID uniqueidentifier,
            QUANTITY int,
            AMOUNT money
        );

        insert into @TICKETTYPES
        (
            ID,
            AMOUNT,
            QUANTITY
        )
        select
            newid(),
            PRICE,
            QUANTITY
        from dbo.UFN_RESERVATION_GETPERPERSONCHARGES(@RESERVATIONID, 1)

        declare @PRICETYPEID uniqueidentifier;
        declare @AMOUNT money;
        declare @QUANTITY int;

        while @EXCESSATTENDEES > 0
        begin
            select top(1)
                @PRICETYPEID = ID,
                @AMOUNT = AMOUNT,
                @QUANTITY = QUANTITY
            from @TICKETTYPES
            order by AMOUNT asc

            if @PRICETYPEID is null
            begin
                set @EXCESSATTENDEES = 0;
            end
            else
            begin
                if @QUANTITY >= @EXCESSATTENDEES
                begin
                    set @TOTALAMOUNT = @TOTALAMOUNT + (@AMOUNT * @EXCESSATTENDEES);
                    set @EXCESSATTENDEES = 0;
                end
                else
                begin
                    set @TOTALAMOUNT = @TOTALAMOUNT + (@AMOUNT * @QUANTITY);
                    set @EXCESSATTENDEES = @EXCESSATTENDEES - @QUANTITY;
                end
                delete from @TICKETTYPES where ID = @PRICETYPEID;
            end
        end

        declare @OLDTOTAL money
        select @OLDTOTAL = AMOUNT 
        from dbo.RESERVATIONRATESCALEPRICE 
        where ID = @RESERVATIONRATESCALEPRICEID

        if @TOTALAMOUNT <> @OLDTOTAL
        begin
            update dbo.RESERVATIONRATESCALEPRICE
            set
                AMOUNT = @TOTALAMOUNT,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @RESERVATIONRATESCALEPRICEID

            -- Re-calculate the applications

            exec dbo.USP_RESERVATIONRATESCALE_RECREATEAPPLICATIONS @RESERVATIONID, @CHANGEAGENTID, @CURRENTDATE, @OLDTOTAL;
        end
    end

    return 0
end