The update implements independent fares for each flight, based on the anticipated duration of the flight, multiplied by a factor for each seating class (50 per hour for economy, 100 for business, 200 for first class).
Here's the SQL, if anybody is interested:
Code: Select all
INSERT INTO flight_fare (flight_id, fare_id, price, cost, capacity, created_at, updated_at)
SELECT
f.id as flight_id,
fare.id as fare_id,
CASE
WHEN fare.code = 'Y' THEN (f.flight_time / 60.0) * 50
WHEN fare.code = 'B' THEN (f.flight_time / 60.0) * 100
WHEN fare.code = 'F' THEN (f.flight_time / 60.0) * 200
END as price,
fare.cost,
fare.capacity,
NOW() as created_at,
NOW() as updated_at
FROM flights f
CROSS JOIN fares fare
WHERE fare.code IN ('Y', 'B', 'F')
AND fare.type = 0
AND f.flight_time IS NOT NULL
AND f.flight_time > 0
AND f.flight_number >= 1000
AND NOT EXISTS (
SELECT 1 FROM flight_fare ff
WHERE ff.flight_id = f.id AND ff.fare_id = fare.id
);
The result of this? You'll actually make profit on long-haul flights.
Oh - and I multiplied the rates for any "Virtual Flight Business" flights (the business jets) to 10x the commercial rates - so it's 500 an hour economy for a seat on a Learjet, for example.
Code: Select all
UPDATE flight_fare ff
INNER JOIN flights f ON ff.flight_id = f.id
INNER JOIN airlines a ON f.airline_id = a.id
SET ff.price = ff.price * 10
WHERE a.name = 'VFO Business';