Planche de vol
Envoyé par:
aeromorestel
()
Date: dimanche 14 juin 2015 18:02:05
Bonjour à tous,
Avant de passer sous OF (3.5), on avait une planche de vol papier comme tout le monde, bien pratique pour savoir si un avion est en vol, avoir une vue des vols de la journée, etc.
J'ai créé un petit rapport dans OF pour avoir la même chose : "Planche de vol entre 2 dates", qui s'appuie sur les champs standards "Date début / date fin" (donc pas de critères à rajouter.
Ci-dessous le SQL ...
SELECT date_format(f.start_date, '%d-%m-%Y') as 'Date Vol',
date_format(f.start_date, '%T') as 'Heure depart',
r.name as 'Immat.',
CONCAT(UPPER(p.last_name),' ',p.first_name) AS 'Pilote',
(
SELECT CONCAT(UPPER(p2.last_name), ' ', p2.first_name)
FROM flight_pilot AS fp2
LEFT JOIN person AS p2 ON fp2.pilot_id = p2.id
WHERE fp2.flight_id = f.id AND fp2.num = 1
) AS Instructeur,
(
SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
FROM flight
LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
WHERE flight.id=f.id
) AS 'Type Vol',
(
SELECT location.name
FROM location
WHERE location.id=f.departure_location_id
) AS Depart,
(
SELECT location.name
FROM location
WHERE location.id=f.arrival_location_id
) AS Arrivee,
f.people_onboard AS 'Nb Pax',
Case airborne when 0 then f.landing_number else 'En vol' end AS 'Nb Att.',
CONCAT(FLOOR( f.duration/600),':',TIME_FORMAT(SEC_TO_TIME((f.duration/600 - FLOOR(f.duration/600))*3600),'%i'), CASE AIRBORNE WHEN 1 THEN ' Prevu' ELSE '' END) AS 'Tps Vol',
f.comments as 'Commentaires'
FROM flight AS f
LEFT JOIN resource AS r ON r.id = f.aircraft_id
LEFT JOIN flight_pilot AS fp ON fp.flight_id = f.id
LEFT JOIN person AS p ON fp.pilot_id = p.id
WHERE date_format(f.start_date,'%Y-%m-%d') BETWEEN '$startDate' AND '$endDate' AND fp.num = 0
ORDER BY f.start_date;