Re: [OF version 3.5] Liste des vols par instructeur
Date: jeudi 1 mai 2014 17:35:21
Bonjour,
Merci pour votre réponse.
J'ai créé deux rapports à partir de ceux que vous proposez dans la documentation :
Vols Instruction : liste de l'ensemble des vols en instruction pour un instructeur ($membre) entre deux dates données (Date début période $per_debut et Date fin période $per_fin)
SELECT
DATE_FORMAT(start_date, '%d/%m/%Y') AS Date,
CONCAT(last_name, ' ', first_name) AS Membre,
ar.name AS Appareil,
(
SELECT CONCAT(a.last_name,' ',a.first_name) AS Instructeur
FROM flight_pilot fp3
LEFT JOIN person a ON a.id=fp3.pilot_id
WHERE fp3.flight_id=fp.flight_id AND fp3.num=1
) AS Instructeur,
CONCAT(FLOOR(flight.duration /600),':',TIME_FORMAT(SEC_TO_TIME(( flight.duration /600 - FLOOR( flight.duration /600))*3600),'%i')) AS Durée,
flight.comments
FROM flight_pilot fp
LEFT JOIN flight ON fp.flight_id=flight.id
LEFT JOIN flight_type ON (flight_type.id & flight.flight_type_id)
LEFT JOIN person a ON a.id=fp.pilot_id
LEFT JOIN resource ar ON ar.id = flight.aircraft_id
WHERE flight_type.name="+ Instruction"
AND (
SELECT pilot_id
FROM flight_pilot fp3
LEFT JOIN person a ON a.id=fp3.pilot_id
WHERE fp3.flight_id=fp.flight_id AND fp3.num=1 AND a.id=$membre
) AND '$per_debut' <= start_date AND start_date <= '$per_fin' AND fp.num=0
ORDER BY start_date
Total HDV par instructeur : liste du nombre d'heures de vol par instructeur entre deux dates données (Date début période $per_debut et Date fin période $per_fin)
SELECT
last_name AS Last_name,
first_name AS First_name,
CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
FROM flight
LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
LEFT JOIN person a ON a.id=fp.pilot_id
WHERE fp.num = 1
AND '$per_debut' <= start_date AND start_date <= '$per_fin'
GROUP BY a.id
UNION
SELECT
"Total",
"",
CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
FROM flight,flight_pilot fp
WHERE flight.id = fp.flight_id
AND fp.num = 1
AND '$per_debut' <= start_date AND start_date <= '$per_fin'
GROUP BY $per_debut
Cordialement,
Marjorie
ACBB