Difference between revisions of "Export generator 3"
(→Monthly revenue breakdown per account) |
(→Flight log book) |
||
(371 intermediate revisions by 5 users not shown) | |||
Line 2: | Line 2: | ||
The goal of this page is to propose a list of statistic generation queries (SQL). | The goal of this page is to propose a list of statistic generation queries (SQL). | ||
− | Do not forget to read the [[OpenFlyers SQL stored functions and procedures]] page. | + | Do not forget to read the [[OpenFlyers SQL stored functions and procedures]] page and the [[Database description]] page. |
Please note that you can make "public" a report, ie. allows standard users to see and interact with it. | Please note that you can make "public" a report, ie. allows standard users to see and interact with it. | ||
Line 19: | Line 19: | ||
===Extra Field creation=== | ===Extra Field creation=== | ||
− | *Go to Menu Admin | + | *Go to Menu '''Admin > Reports > Structure > Extra Field(s)''' |
In the bottom line add | In the bottom line add | ||
*Fill the '''name''' field with a name that will be use within the SQL queries (prefixed with the $ character) | *Fill the '''name''' field with a name that will be use within the SQL queries (prefixed with the $ character) | ||
Line 40: | Line 40: | ||
*Name: $aircraftId | *Name: $aircraftId | ||
*Label: Aircraft | *Label: Aircraft | ||
− | *Value type: dbObject:: | + | *Value type: dbObject::Aircraft |
Then in Admin/Reports/Structure/Criteria we create a new query labeled "Aircraft booking" with the following query: | Then in Admin/Reports/Structure/Criteria we create a new query labeled "Aircraft booking" with the following query: | ||
<sql>SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId</sql> | <sql>SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId</sql> | ||
To use this report, we just have to select a "Aircraft" in the Reports/Structure/View form, to check "Aircraft booking" then to click on "View" | To use this report, we just have to select a "Aircraft" in the Reports/Structure/View form, to check "Aircraft booking" then to click on "View" | ||
− | = | + | =SQL tips and tricks= |
+ | ==Return only last entry== | ||
+ | Example with last entry from variable #1 in variable_value table: | ||
+ | <sql>SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC LIMIT 1</sql> | ||
− | == | + | ==Test valid entries== |
+ | For extra fields that are text inputs, any entry can be submitted and added to SQL query. In order to test valid entries, it is recommended to use the '''IF''' statement in the '''WHERE''' statement. | ||
− | + | Per example, $occupiedSeat can have following values (0, 1 or NULL) : | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | == | + | <sql>SELECT * |
+ | FROM flight_pilot | ||
+ | WHERE | ||
+ | ( | ||
+ | IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num | ||
+ | OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num | ||
+ | )</sql> | ||
− | + | =French administration Examples= | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | == | + | ==Flight type : Flight hours total on instruction with specific profile== |
− | + | * Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type. | |
− | + | * Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type. | |
− | + | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | <sql>SELECT tmp_flight.activity_type_name AS name, | |
− | + | sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', | |
− | + | sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', | |
− | + | sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur', | |
− | + | sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM' | |
− | + | FROM ( | |
− | + | SELECT | |
− | + | flightWithActivityType.activity_type_id, | |
− | + | flightWithActivityType.activity_type_name, | |
− | + | flightWithActivityType.duration, | |
− | + | resource_type.category | |
− | + | FROM ( | |
− | + | SELECT flight.*, flight_type.id AS activity_type_id, flight_type.name AS activity_type_name | |
− | + | FROM flight | |
− | + | LEFT JOIN flight_type ON flight.flight_type_id&flight_type.id | |
− | + | WHERE YEAR(flight.start_date)=$year | |
− | + | AND flight.airborne=0 | |
− | + | AND ( flight_type.id IN ('$flightTypeId') OR '-' IN ('$flightTypeId') ) | |
− | + | ) AS flightWithActivityType | |
− | + | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1) | |
− | + | LEFT JOIN ( | |
− | + | SELECT person.* | |
− | + | FROM person | |
− | + | LEFT JOIN profile ON person.profile&profile.id | |
− | + | WHERE ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) | |
− | + | AND person.activated=1 | |
− | + | GROUP BY person.id | |
− | + | ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id) | |
− | + | LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id) | |
− | + | LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) | |
− | + | WHERE personWithProfile.id IS NOT NULL | |
− | + | ) AS tmp_flight | |
− | + | GROUP BY tmp_flight.activity_type_id</sql> | |
− | + | ||
− | LEFT JOIN flight_pilot | + | |
− | LEFT JOIN person | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | LEFT JOIN | + | |
− | LEFT JOIN | + | |
− | + | ||
− | + | ||
− | + | ||
− | == | + | ==Flight type : Flight hours total on non-instruction with specific profile== |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | * Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type. | |
+ | * Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type. | ||
+ | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
− | + | <sql>SELECT tmp_flight.activity_type_name AS name, | |
− | + | sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', | |
− | + | sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', | |
− | + | sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur', | |
− | + | sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM' | |
− | + | FROM ( | |
− | LEFT JOIN | + | SELECT |
− | LEFT JOIN | + | flightWithActivityType.activity_type_id, |
− | + | flightWithActivityType.activity_type_name, | |
+ | flightWithActivityType.duration, | ||
+ | resource_type.category | ||
+ | FROM ( | ||
+ | SELECT flight.*, flight_type.id AS activity_type_id, flight_type.name AS activity_type_name | ||
+ | FROM flight | ||
+ | LEFT JOIN flight_type ON flight.flight_type_id&flight_type.id | ||
+ | WHERE YEAR(flight.start_date)=$year | ||
+ | AND flight.airborne=0 | ||
+ | AND ( flight_type.id IN ('$flightTypeId') OR '-' IN ('$flightTypeId') ) | ||
+ | AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) | ||
+ | ) AS flightWithActivityType | ||
+ | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=0) | ||
+ | LEFT JOIN ( | ||
+ | SELECT person.* | ||
+ | FROM person | ||
+ | LEFT JOIN profile ON person.profile&profile.id | ||
+ | WHERE ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) | ||
+ | AND person.activated=1 | ||
+ | GROUP BY person.id | ||
+ | ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id) | ||
+ | LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id) | ||
+ | LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) | ||
+ | WHERE personWithProfile.id IS NOT NULL | ||
+ | ) AS tmp_flight | ||
+ | GROUP BY tmp_flight.activity_type_id</sql> | ||
− | == | + | ==Users with a specific validity without expiration date== |
+ | *Variable '''$validityId''' should be defined first and should be of '''dbOject::ValidityType''' value type. | ||
− | + | <sql>SELECT person.first_name, person.last_name FROM validity | |
− | + | LEFT JOIN person ON person.id=validity.person_id | |
− | + | WHERE validity_type_id=$validityTypeId AND expire_date IS NULL AND person.activated=1</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ==Number of movements on based airfield== | |
− | + | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | == | + | <sql>SELECT SUM(movement) AS 'Movement' |
+ | FROM | ||
+ | ( | ||
+ | SELECT COUNT(*) AS movement | ||
+ | FROM flight | ||
+ | LEFT JOIN location ON (flight.departure_location_id = location.id) | ||
+ | WHERE ( | ||
+ | ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) | ||
+ | OR | ||
+ | ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) | ||
+ | ) | ||
+ | AND ( location.id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) | ||
+ | AND YEAR( start_date ) = $year | ||
+ | UNION | ||
+ | SELECT SUM(landing_number)*2 AS movement | ||
+ | FROM flight | ||
+ | LEFT JOIN ( | ||
+ | SELECT location.id | ||
+ | FROM location | ||
+ | LEFT JOIN club ON location.icao_name=club.icao | ||
+ | WHERE club.id IS NOT NULL | ||
+ | LIMIT 1 | ||
+ | ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id) | ||
+ | WHERE YEAR(flight.start_date) = $year | ||
+ | AND structure_icao.id IS NOT NULL | ||
+ | ) AS tmp_movement</sql> | ||
− | + | ==Number of movements on based airfield on specific profile== | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | * Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type. | |
+ | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
− | + | <sql>SELECT SUM(movement) AS 'Movement' | |
− | + | FROM | |
− | COUNT( | + | ( |
− | + | SELECT COUNT(*) AS movement | |
− | + | FROM flight | |
− | + | LEFT JOIN location ON (flight.departure_location_id = location.id) | |
− | + | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) | |
− | + | LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0) | |
− | + | WHERE ( | |
− | + | ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) | |
+ | OR | ||
+ | ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) | ||
+ | ) | ||
+ | AND ( location.id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) | ||
+ | AND person.activated=1 | ||
+ | AND (person.profile & '$profileId' OR '-'='$profileId') | ||
+ | AND YEAR(flight.start_date) = $year | ||
+ | UNION | ||
+ | SELECT SUM(landing_number)*2 AS movement | ||
+ | FROM flight | ||
+ | LEFT JOIN ( | ||
+ | SELECT location.id | ||
+ | FROM location | ||
+ | LEFT JOIN club ON location.icao_name=club.icao | ||
+ | WHERE club.id IS NOT NULL | ||
+ | LIMIT 1 | ||
+ | ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id) | ||
+ | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) | ||
+ | LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0) | ||
+ | WHERE person.activated=1 | ||
+ | AND (person.profile & '$profileId' OR '-'='$profileId') | ||
+ | AND YEAR(flight.start_date) = $year | ||
+ | AND structure_icao.id IS NOT NULL | ||
+ | ) AS tmp_movement</sql> | ||
− | == | + | ==Visited airfields== |
− | + | <sql>SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS nb_visite | |
− | + | FROM flight | |
− | + | LEFT JOIN location ON (flight.departure_location_id = location.id) | |
− | + | WHERE ( | |
− | + | ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) | |
− | + | OR | |
− | + | ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) | |
+ | ) | ||
AND YEAR( start_date ) = $year | AND YEAR( start_date ) = $year | ||
− | + | GROUP BY icao_name | |
− | + | ORDER BY nb_visite DESC</sql> | |
==Number of take-off and landings on based airfield== | ==Number of take-off and landings on based airfield== | ||
Line 295: | Line 246: | ||
AND YEAR( start_date ) = $year</SQL> | AND YEAR( start_date ) = $year</SQL> | ||
− | == | + | ==Number of take-off and landings on based airfield on specific profile== |
− | + | * Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type. | |
− | + | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | == | + | <sql>SELECT SUM(landing_number)*2 AS 'Movement' |
+ | FROM flight | ||
+ | LEFT JOIN ( | ||
+ | SELECT location.id | ||
+ | FROM location | ||
+ | LEFT JOIN club ON location.icao_name=club.icao | ||
+ | WHERE club.id IS NOT NULL | ||
+ | LIMIT 1 | ||
+ | ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id) | ||
+ | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) | ||
+ | LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0) | ||
+ | WHERE person.activated=1 | ||
+ | AND (person.profile & '$profileId' OR '-'='$profileId') | ||
+ | AND YEAR(flight.start_date) = $year | ||
+ | AND structure_icao.id IS NOT NULL</sql> | ||
− | + | ==List of movements on based airfield== | |
− | + | <sql>SELECT resource.name, DATE_FORMAT(flight.start_date, '%d %m %Y' ) AS Date, sum(landing_number) AS 'Nb Att' | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | <sql>SELECT | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
FROM flight | FROM flight | ||
− | |||
− | |||
− | |||
− | |||
− | |||
LEFT JOIN resource ON resource.id = flight.aircraft_id | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
− | + | WHERE ( flight.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) | |
− | WHERE | + | AND (flight.arrival_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) |
− | + | AND YEAR( start_date ) = $year | |
+ | GROUP BY date,name</sql> | ||
− | == | + | ==Pilots who have flown without required validity== |
− | <sql>SELECT CONCAT( | + | <sql>SELECT CONCAT(person.last_name, ' ', person.first_name) AS pilot, validity_type.name AS 'Validity' |
− | FROM flight_type_mandatory_validity_type | + | FROM flight_type_mandatory_validity_type |
− | LEFT JOIN flight_type | + | LEFT JOIN flight_type ON flight_type.id = flight_type_mandatory_validity_type.flight_type_id |
− | LEFT JOIN flight | + | LEFT JOIN flight ON flight.flight_type_id & flight_type.id |
− | LEFT JOIN resource | + | LEFT JOIN resource ON resource.id = flight.aircraft_id |
− | LEFT JOIN resource_type | + | LEFT JOIN resource_type ON resource.resource_type_id = resource_type.id |
− | LEFT JOIN aircraft_type_validity_type | + | LEFT JOIN aircraft_type_validity_type ON resource_type.id = aircraft_type_validity_type.aircraft_type_id |
− | LEFT JOIN flight_pilot | + | LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id |
− | LEFT JOIN person | + | LEFT JOIN person ON person.id = flight_pilot.pilot_id |
− | LEFT JOIN validity_type | + | LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id) |
− | WHERE | + | LEFT JOIN validity ON (validity_type.id=validity.validity_type_id AND person.id=validity.person_id) |
− | AND | + | WHERE flight.airborne = 0 |
− | AND | + | AND validity_type.id IS NOT NULL |
− | GROUP BY | + | AND validity_type.experience_formula IS NULL |
− | ORDER BY pilot, | + | AND validity.validity_type_id IS NULL |
+ | AND person.activated=1 | ||
+ | GROUP BY person.id, validity_type.id | ||
+ | ORDER BY pilot, validity_type.name</sql> | ||
==List of pilots who have flown less than X hours during last Y days== | ==List of pilots who have flown less than X hours during last Y days== | ||
Line 424: | Line 326: | ||
==Pilots without flight in the last 3 months== | ==Pilots without flight in the last 3 months== | ||
+ | |||
+ | * Variable '''$month''' should be defined first and should be of '''integer''' value type. | ||
<sql>SELECT | <sql>SELECT | ||
− | + | person.last_name, person.first_name, | |
IFNULL((SELECT CONCAT(DATE_FORMAT( f1.start_date, '%d/%m/%Y' ),' ', resource.name,' Duration: ', | IFNULL((SELECT CONCAT(DATE_FORMAT( f1.start_date, '%d/%m/%Y' ),' ', resource.name,' Duration: ', | ||
− | TIME_FORMAT(SEC_TO_TIME | + | TIME_FORMAT(SEC_TO_TIME(f1.duration*6 ) ,'%H h %i')) |
FROM flight AS f1 | FROM flight AS f1 | ||
LEFT JOIN flight_pilot AS fp ON fp.flight_id=f1.id | LEFT JOIN flight_pilot AS fp ON fp.flight_id=f1.id | ||
LEFT JOIN resource ON resource.id=f1.aircraft_id | LEFT JOIN resource ON resource.id=f1.aircraft_id | ||
− | WHERE fp.pilot_id= | + | WHERE fp.pilot_id=person.id |
− | ORDER BY f1.start_date | + | ORDER BY f1.start_date DESC LIMIT 1),'UNKNOWN') AS 'Last flight' |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
FROM person | FROM person | ||
− | + | WHERE person.activated=1 | |
− | + | AND person.id NOT IN ( | |
− | + | SELECT person2.id | |
− | + | FROM flight AS f | |
− | + | LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id | |
− | + | LEFT JOIN person AS person2 ON person2.id=fp.pilot_id | |
− | + | WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL IF($month='', 1, $month*30) DAY AND NOW() | |
− | + | GROUP BY person2.id HAVING SUM(f.duration)/600 > 0 | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
) | ) | ||
− | + | ORDER BY person.last_name, person.first_name</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
==Number of landings per pilot, per resource== | ==Number of landings per pilot, per resource== | ||
Line 614: | Line 426: | ||
ORDER BY last_name</sql> | ORDER BY last_name</sql> | ||
− | == | + | ==Flight hours without up to date validities== |
+ | <sql>SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS '_tr(DATE)', last_name AS '_tr(LAST_NAME)', first_name AS '_tr(FIRST_NAME)', | ||
+ | IF ( (SELECT COUNT(*) FROM flight_pilot AS tmp_flight_pilot WHERE flight_pilot.flight_id=tmp_flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'Solo', 'DC' ) AS DC, | ||
+ | resource.name AS '_tr(RESOURCE)', | ||
+ | sexa2HoursMinute(duration) AS '_tr(DURATION)', | ||
+ | flight_type.name AS '_tr(flight_type)', | ||
+ | validity_type.name AS '_tr(VALIDITY)' | ||
+ | FROM flight | ||
+ | LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.flight_type_id & flight.flight_type_id | ||
+ | LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id | ||
+ | LEFT JOIN person ON flight_pilot.pilot_id = person.id | ||
+ | LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id | ||
+ | LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id | ||
+ | LEFT JOIN resource ON flight.aircraft_id = resource.id | ||
+ | LEFT JOIN flight_type ON flight_type_mandatory_validity_type.flight_type_id = flight_type.id | ||
+ | WHERE YEAR(start_date) = $year | ||
+ | AND flight_pilot.num = 0 | ||
+ | AND validity_type.time_limitation=1 | ||
+ | AND validity.person_id = flight_pilot.pilot_id | ||
+ | AND flight.start_date > validity.expire_date | ||
+ | AND person.activated=1 | ||
+ | ORDER BY flight.start_date, person.last_name, validity_type.name</sql> | ||
− | Variable $ | + | ==Number of men, women over and under X years for profile P, validity V up to date for year A== |
+ | * Variable '''$age''' should be of '''integer''' value type. | ||
+ | * Variable '''$profileId''' should be of '''dbOject::Profile''' value type. | ||
+ | * Variable '''$validityTypeId''' should be of '''dbObject::validityType''' value type. | ||
+ | * Variable '''$year''' should be of '''Year''' value type. | ||
− | < | + | <sql>SELECT |
+ | IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', | ||
+ | IF( ( $year - YEAR(birthdate) >= '$age' ) , 'Yes', 'No') AS 'Adult', | ||
+ | COUNT(DISTINCT person.id) AS NUMBER | ||
FROM person | FROM person | ||
− | LEFT JOIN validity ON ( | + | LEFT JOIN validity ON (person.id=validity.person_id) |
− | LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) | + | LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id) |
− | WHERE | + | WHERE activated=1 |
− | AND validity_type.id = $validityTypeId</ | + | AND (profile & '$profileId' OR '-'='$profileId') |
+ | AND ( | ||
+ | (validity_type.id='$validityTypeId' AND validity.expire_date >= '$year-12-31') | ||
+ | OR | ||
+ | '-'='$validityTypeId' | ||
+ | ) | ||
+ | GROUP BY Sex, Adult</sql> | ||
− | == | + | ==Validities in the year : Total of youngs/adults with specific profile== |
− | + | * Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type. | |
− | + | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | |
− | + | * Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need. | |
− | + | <sql>SELECT | |
− | + | validity_type.name AS 'Validity', | |
− | + | SUM( IF( ( $year - YEAR(person.birthdate) < 21 ), 1, 0 ) ) AS 'Young', | |
− | + | SUM( IF( ( $year - YEAR(person.birthdate) >= 21 ), 1, 0 ) ) AS 'Adult' | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | <sql>SELECT | + | |
− | + | ||
− | + | ||
− | validity_type.name AS Validity, | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
FROM validity_type | FROM validity_type | ||
− | LEFT JOIN validity ON validity.validity_type_id =validity_type.id | + | LEFT JOIN validity ON (validity.validity_type_id=validity_type.id) |
− | LEFT JOIN person ON person.id=validity.person_id | + | LEFT JOIN person ON (person.id=validity.person_id) |
− | WHERE YEAR(grant_date) | + | WHERE YEAR(validity.grant_date)=$year |
− | + | AND person.activated=1 | |
+ | AND (person.profile & '$profileId' OR '-'='$profileId') | ||
+ | GROUP BY validity_type.id | ||
+ | ORDER BY validity_type.name</sql> | ||
− | == | + | ==Validities obtained in the year== |
− | + | *Variable '''$validityId''' should be defined first and should be of '''dbOject::ValidityType''' value type. | |
+ | *'''21''' value is the age limit to be young. Should be changed according local rules | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
<sql>SELECT | <sql>SELECT | ||
− | + | last_name AS Last_name, | |
− | + | first_name AS First_name, | |
− | + | IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young, | |
− | + | IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, validity_type.name AS 'Validity' | |
− | + | FROM person | |
− | + | LEFT JOIN validity ON person_id=person.id | |
− | + | LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id | |
− | + | WHERE YEAR(grant_date)=$year | |
− | + | AND ( validity_type.id IN ('$validityTypeId') OR '-' IN ('$validityTypeId') ) | |
− | + | AND person.activated=1 | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | LEFT JOIN validity ON | + | |
− | LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
ORDER BY last_name, first_name</sql> | ORDER BY last_name, first_name</sql> | ||
− | = | + | =[[Accounting reports 3]]= |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | = | + | =Booking= |
− | + | ==Bookings== | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | * Variable '''$endDate''' of '''Date and time''' value type. | |
+ | * Variable '''$startDate''' of '''Date and time''' value type. | ||
+ | * Variable '''$personId''' of '''dbObject::Person''' value type. | ||
+ | * Variable '''$resourceId''' of '''dbObject::Resource''' value type. | ||
− | + | <sql>SELECT booking.start_date AS 'Date de début', booking.end_date AS 'Date de fin', resource.name AS 'Ressource', | |
− | + | CONCAT(left_person.last_name, ' ', left_person.first_name) AS 'Place gauche', | |
− | ', | + | CONCAT(right_person.last_name, ' ', right_person.first_name) AS 'Place droite' |
− | + | FROM booking | |
− | + | LEFT JOIN resource ON (resource.id=booking.resource_id) | |
− | + | LEFT JOIN person AS left_person ON (left_person.id=booking.person_id) | |
− | + | LEFT JOIN person AS right_person ON (right_person.id=booking.instructor_id) | |
− | + | WHERE booking.start_date >= '$startDate' AND booking.end_date < '$endDate' | |
− | FROM | + | AND (booking.resource_id='$resourceId' OR '-'='$resourceId') |
− | LEFT JOIN | + | AND ( |
− | LEFT JOIN person ON | + | (left_person.id='$personId' AND left_person.activated=1) |
− | LEFT JOIN | + | OR |
− | + | (right_person.id='$personId' AND right_person.activated=1) | |
− | + | OR | |
− | + | '-'='$personId' | |
− | + | ) | |
− | + | ORDER BY booking.start_date, booking.end_date</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | ORDER BY | + | |
− | == | + | ==Cumulated maintenance hours between a period== |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | Following extrafields are needed : | |
− | + | *startDate (Type : Date) | |
+ | *endDate (Type : Date or DateTime) | ||
− | + | <sql>SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours' | |
− | < | + | UNION ALL |
+ | SELECT resource.name AS 'Ressource', SUM(TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date)), SUM(TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date)) | ||
+ | FROM booking | ||
+ | LEFT JOIN log ON log.field_value=booking.id | ||
+ | LEFT JOIN journal ON journal.id=log.journal_id | ||
+ | LEFT JOIN resource ON resource.id = booking.resource_id | ||
+ | WHERE (booking.slot_type=2) | ||
+ | AND (log.action='INSERT') | ||
+ | AND (log.table_name='booking') | ||
+ | AND (log.field_name='id') | ||
+ | AND (booking.start_date >= '$startDate') | ||
+ | AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate')) | ||
+ | GROUP BY resource.id | ||
+ | ORDER BY 1</sql> | ||
− | == | + | ==Maintenance hours per resource on a period== |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | Following extrafields are needed : | |
− | + | *startDate (Type : Date) | |
− | ( | + | *endDate (Type : Date or DateTime) |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | <sql>SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Date debut maintenance', '' AS 'Date fin maintenance', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours' | |
− | <sql>SELECT | + | UNION ALL |
− | + | SELECT resource.name, booking.start_date AS 'Date debut maintenance', booking.end_date, TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date), TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date) | |
− | + | FROM booking | |
− | + | LEFT JOIN log ON log.field_value=booking.id | |
− | + | LEFT JOIN journal ON journal.id=log.journal_id | |
− | + | LEFT JOIN resource ON resource.id = booking.resource_id | |
− | + | WHERE (booking.slot_type=2) | |
− | + | AND (log.action='INSERT') | |
− | + | AND (log.table_name='booking') | |
− | + | AND (log.field_name='id') | |
− | + | AND (booking.start_date >= '$startDate') | |
− | + | AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate')) | |
− | + | ORDER BY 1, 2</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | UNION | + | |
− | SELECT | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | =Booking | + | ==Booking for maintenance ordered by resource and date with name of the responsible== |
− | + | ||
<sql>SELECT resource.name AS 'Resource name', booking.start_date AS 'Start date', TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date) AS 'booking duration', person.first_name AS 'First name', person.last_name AS 'Last name' | <sql>SELECT resource.name AS 'Resource name', booking.start_date AS 'Start date', TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date) AS 'booking duration', person.first_name AS 'First name', person.last_name AS 'Last name' | ||
FROM booking | FROM booking | ||
Line 975: | Line 588: | ||
WHERE (booking.slot_type=2) AND (log.action='INSERT') AND (log.table_name='booking') AND (log.field_name='id') | WHERE (booking.slot_type=2) AND (log.action='INSERT') AND (log.table_name='booking') AND (log.field_name='id') | ||
AND YEAR(booking.start_date) = $year | AND YEAR(booking.start_date) = $year | ||
+ | AND person.activated=1 | ||
ORDER BY resource.name, booking.start_date</sql> | ORDER BY resource.name, booking.start_date</sql> | ||
− | =Flight time | + | =Flight time reports= |
− | ==Flight hours | + | ==Flight hours per month== |
+ | <sql>SELECT MONTH( start_date) AS months, 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 aircraft ON aircraft.id = flight.aircraft_id | ||
+ | WHERE YEAR(start_date) = $year | ||
+ | GROUP BY months</sql> | ||
− | < | + | ==Flight hours total and last recorded counter to the date X== |
− | CONCAT(last_name,' ',first_name) AS | + | |
+ | * Variable '''$endDate''' should be of '''Datetime''' value type. | ||
+ | |||
+ | <sql>SELECT resource.name, sexa2HoursMinute( SUM(duration) + aircraft.ref_hours ) AS 'Total heures', sexa2HoursMinute( MAX(counter_arrival) ) AS 'Dernier compteur' | ||
+ | FROM flight | ||
+ | LEFT JOIN resource ON (resource.id=flight.aircraft_id) | ||
+ | LEFT JOIN aircraft ON (aircraft.id=resource.id) | ||
+ | WHERE flight.start_date <= '$endDate' | ||
+ | AND resource.activated=1 | ||
+ | GROUP BY resource.id | ||
+ | ORDER BY resource.name</sql> | ||
+ | |||
+ | ==Flight hours total over a 12 months period== | ||
+ | |||
+ | <sql>SELECT | ||
+ | CONCAT(last_name,' ',first_name) AS 'Person', | ||
IF (( SELECT | IF (( SELECT | ||
COUNT(*) | COUNT(*) | ||
Line 992: | Line 626: | ||
LEFT JOIN flight ON fp.flight_id=flight.id | LEFT JOIN flight ON fp.flight_id=flight.id | ||
LEFT JOIN person a ON a.id=fp.pilot_id | LEFT JOIN person a ON a.id=fp.pilot_id | ||
− | WHERE YEAR( start_date ) = $year AND | + | WHERE start_date > DATE_SUB(NOW(), INTERVAL 1 YEAR) AND fp.num=0 |
− | GROUP BY Nom, Solo_DC</ | + | GROUP BY Person, Solo_DC</sql> |
+ | |||
+ | ==Flights hours total per aircraft per year== | ||
+ | |||
+ | <sql>SELECT | ||
+ | name AS Callsign, | ||
+ | YEAR( start_date ) AS Year, | ||
+ | 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 resource ON resource.id = flight.aircraft_id | ||
+ | WHERE YEAR(start_date) = $year AND airborne = 0 | ||
+ | GROUP BY name | ||
+ | UNION | ||
+ | SELECT | ||
+ | "Total", | ||
+ | $year AS year, | ||
+ | CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total | ||
+ | FROM flight | ||
+ | WHERE YEAR(start_date) = $year AND airborne = 0 | ||
+ | GROUP BY year</sql> | ||
+ | |||
+ | ==Flights hours total per aircraft per year and per month (for a flight type)== | ||
+ | |||
+ | * Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type. | ||
+ | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
+ | |||
+ | <sql>SELECT aircraft_id AS Num, resource.name, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS Janu, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS Febr, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS Marc, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS Apri, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS May, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS June, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS July, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS Augu, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS Sept, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS Octo, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS Nove, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS Dece, | ||
+ | sexa2HoursMinute( SUM( duration ) ) AS SUM | ||
+ | FROM flight | ||
+ | LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id | ||
+ | LEFT JOIN resource ON resource.id = aircraft.id | ||
+ | WHERE YEAR(start_date)= $year AND airborne = 0 | ||
+ | AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId') | ||
+ | GROUP BY resource.id | ||
+ | UNION | ||
+ | SELECT "Sum per","month", | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( duration ) ) AS Total | ||
+ | FROM flight | ||
+ | LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id | ||
+ | LEFT JOIN resource ON resource.id = aircraft.id | ||
+ | WHERE YEAR(start_date)= $year AND airborne = 0 | ||
+ | AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId') | ||
+ | UNION | ||
+ | SELECT "Cumulative","per month", | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=1, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=2, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=3, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=4, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=5, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=6, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=7, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=8, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=9, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=10, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=11, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date)<=12, duration, 0 ) ) ), | ||
+ | sexa2HoursMinute( SUM( duration ) ) AS Total | ||
+ | FROM flight | ||
+ | LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id | ||
+ | LEFT JOIN resource ON resource.id = aircraft.id | ||
+ | WHERE YEAR(start_date)= $year AND airborne = 0 | ||
+ | AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')</sql> | ||
+ | |||
+ | ==Flight hours total per pilot== | ||
+ | |||
+ | <sql>SELECT | ||
+ | CONCAT(last_name,' ',first_name) AS Nom, | ||
+ | sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) ) AS 'Solo', | ||
+ | sexa2HoursMinute( SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS 'Double', | ||
+ | sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) + SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS 'Total' | ||
+ | FROM person | ||
+ | LEFT JOIN ( | ||
+ | SELECT | ||
+ | pilot_id, | ||
+ | IF ( | ||
+ | (SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, | ||
+ | 'Solo', | ||
+ | 'DC' | ||
+ | ) AS Solo_DC, | ||
+ | SUM(flight.duration) AS sum_duration | ||
+ | FROM flight_pilot fp | ||
+ | LEFT JOIN flight ON fp.flight_id=flight.id | ||
+ | WHERE flight.airborne=0 AND YEAR(start_date) = $year AND fp.num=0 | ||
+ | GROUP BY pilot_id, Solo_DC | ||
+ | ) AS Solo ON person.id=Solo.pilot_id AND Solo.Solo_DC='Solo' | ||
+ | LEFT JOIN ( | ||
+ | SELECT | ||
+ | pilot_id, | ||
+ | IF ( | ||
+ | (SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, | ||
+ | 'Solo', | ||
+ | 'DC' | ||
+ | ) AS Solo_DC, | ||
+ | SUM(flight.duration) AS sum_duration | ||
+ | FROM flight_pilot fp | ||
+ | LEFT JOIN flight ON fp.flight_id=flight.id | ||
+ | WHERE flight.airborne=0 AND YEAR(start_date) = $year AND fp.num=0 | ||
+ | GROUP BY pilot_id, Solo_DC | ||
+ | ) AS DC ON person.id=DC.pilot_id AND DC.Solo_DC='DC' | ||
+ | WHERE (Solo.pilot_id IS NOT NULL OR DC.pilot_id IS NOT NULL) | ||
+ | AND person.activated=1 | ||
+ | GROUP BY person.id | ||
+ | ORDER BY Nom</sql> | ||
==Flight hours total per pilot per profile== | ==Flight hours total per pilot per profile== | ||
Line 1,006: | Line 766: | ||
'Solo', | 'Solo', | ||
'double') AS Solo_DC, | 'double') AS Solo_DC, | ||
− | + | sexa2HoursMinute( SUM( flight.duration ) ) AS Total | |
FROM flight_pilot fp | FROM flight_pilot fp | ||
LEFT JOIN flight ON fp.flight_id=flight.id | LEFT JOIN flight ON fp.flight_id=flight.id | ||
LEFT JOIN person ON person.id=fp.pilot_id | LEFT JOIN person ON person.id=fp.pilot_id | ||
LEFT JOIN profile ON (person.profile & profile.id) | LEFT JOIN profile ON (person.profile & profile.id) | ||
− | WHERE YEAR( start_date ) = $year AND fp.num=0 | + | WHERE flight.airborne=0 AND YEAR( start_date ) = $year AND fp.num=0 |
GROUP BY Profil, Nom, Solo_DC</sql> | GROUP BY Profil, Nom, Solo_DC</sql> | ||
− | == | + | ==Total Flight hours per pilot with total duration and total amount== |
+ | Following extrafields are needed | ||
+ | *year (Type : Year) | ||
+ | *month (Type : Integer) '''OR''' startDate and endDate (Type : Date) | ||
+ | *$occupiedSeat (Type : Integer) | ||
+ | *profileId (Type : dbObject::Profile) | ||
+ | This report is useful for a group of pilots from a common customer (like DGAC in France) | ||
+ | |||
+ | <sql>SELECT CONCAT(person.last_name,' ',person.first_name) AS Nom_et_prenom_pilotes, | ||
+ | CONCAT(FLOOR(SUM( flight_list.duree )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight_list.duree )/600 - FLOOR(SUM( flight_list.duree )/600))*3600),'%i')) AS nombre_hdv_effectuees, | ||
+ | SUM(flight_list.montant) AS montant_total_des_vols | ||
+ | FROM person | ||
+ | JOIN | ||
+ | (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person | ||
+ | LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id | ||
+ | LEFT JOIN flight ON flight.id=flight_pilot.flight_id | ||
+ | LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id | ||
+ | LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id | ||
+ | LEFT JOIN account ON account.id=account_entry.account_id | ||
+ | WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate' | ||
+ | AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num | ||
+ | OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num) | ||
+ | AND person.profile&$profileId AND (account.category = 11 OR account.category = 2) | ||
+ | GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id | ||
+ | GROUP BY person.id</sql> | ||
+ | |||
+ | ==Flight hours per instructor, month (for a flight type)== | ||
+ | Following extrafields are needed : | ||
+ | *year (Type : Year) | ||
+ | *flightTypeId (Type : dbObject::FlightType) | ||
<sql>SELECT | <sql>SELECT | ||
− | UPPER(last_name) AS | + | UPPER(last_name) AS Nom, |
− | first_name AS | + | first_name AS Prénom, |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Janv, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Fevr, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mars, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Avri, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mai, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juin, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juil, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Aout, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Sept, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Oct, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Nov, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Dece, | |
− | + | sexa2HoursMinute( SUM( duration ) ) 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 flight.airborne=0 | |
− | + | AND a.activated=1 | |
− | + | AND fp.num = 1 | |
− | + | AND YEAR( start_date ) = $year | |
− | GROUP BY a.id | + | AND ( flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId' ) |
− | UNION | + | GROUP BY a.id |
− | SELECT " | + | |
− | + | UNION | |
− | + | ||
− | + | SELECT "Sum per","month", | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Janv, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Fevr, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mars, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Avri, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mai, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juin, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juil, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Aout, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Sept, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Oct, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Nov, | |
− | + | (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Dece, | |
− | + | sexa2HoursMinute( SUM( duration ) ) 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 flight.airborne=0 | |
− | + | AND a.activated=1 | |
− | SELECT | + | AND fp.num = 1 |
− | + | AND YEAR( start_date ) = $year | |
− | + | AND ( flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId' )</sql> | |
− | + | ||
− | + | ==Flights hours following nationality== | |
− | + | <SQL>SELECT | |
− | + | nationality.label, | |
− | + | CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total | |
− | + | FROM flight , flight_pilot fp, person a, nationality | |
− | + | WHERE flight.id = fp.flight_id | |
− | + | AND fp.pilot_id = a.id | |
− | + | AND a.nationality = nationality.code | |
− | + | GROUP BY nationality.code</SQL> | |
− | + | ||
− | + | ==Flights hours total per flight type per month== | |
− | + | ||
− | LEFT JOIN person | + | <sql>SELECT |
− | LEFT JOIN profile | + | flight_type.name AS Type_vol, |
− | WHERE | + | flight_pilot_DC.DC, |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu, | |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr, | |
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 6, duration, 0 ) ) ) AS June, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 7, duration, 0 ) ) ) AS July, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 8, duration, 0 ) ) ) AS Augu, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 9, duration, 0 ) ) ) AS Sept, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 10, duration, 0 ) ) ) AS Octo, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 11, duration, 0 ) ) ) AS Nove, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 12, duration, 0 ) ) ) AS Dece, | ||
+ | sexa2HoursMinute( SUM( duration ) ) AS Total | ||
+ | FROM flight | ||
+ | LEFT JOIN flight_type ON flight_type.id & flight.flight_type_id | ||
+ | LEFT JOIN ( | ||
+ | SELECT flight_id, IF( MAX(num) = 1, 'Double', 'Solo' ) AS DC | ||
+ | FROM flight_pilot | ||
+ | WHERE flight_pilot.num <= 1 | ||
+ | GROUP BY flight_id | ||
+ | ) AS flight_pilot_DC ON flight.id=flight_pilot_DC.flight_id | ||
+ | WHERE YEAR(start_date) = $year | ||
+ | AND airborne = 0 | ||
+ | GROUP BY flight_type.id, flight_pilot_DC.DC | ||
+ | HAVING (Name <> 'Instruction' OR flight_pilot_DC.DC <> 'Double') | ||
+ | ORDER BY order_num ASC, DC DESC</sql> | ||
+ | |||
+ | ==Flights hours : less than 21 years, more than 21 years, male, female, for a given profile== | ||
+ | *Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type. | ||
+ | *Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
+ | |||
+ | <sql>SELECT Sex, Young, Instruction, | ||
+ | sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', | ||
+ | sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', | ||
+ | sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur', | ||
+ | sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM' | ||
+ | FROM ( | ||
+ | SELECT | ||
+ | IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', | ||
+ | IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young, | ||
+ | IF ((SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'Instruction') AS Instruction, | ||
+ | flight.duration, | ||
+ | resource_type.category | ||
+ | FROM flight_pilot fp | ||
+ | LEFT JOIN flight ON fp.flight_id=flight.id | ||
+ | LEFT JOIN person ON person.id=fp.pilot_id | ||
+ | LEFT JOIN profile ON person.profile&profile.id | ||
+ | LEFT JOIN resource ON (resource.id=flight.aircraft_id) | ||
+ | LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) | ||
+ | WHERE YEAR( start_date )=$year | ||
+ | AND fp.num=0 | ||
+ | AND flight.airborne = 0 | ||
+ | AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) | ||
+ | AND person.activated=1 | ||
+ | GROUP BY flight.id | ||
+ | ) AS tmp_stat | ||
+ | GROUP BY Sex, Young, Instruction WITH ROLLUP</sql> | ||
==Computation of flight time per month of "local flight"== | ==Computation of flight time per month of "local flight"== | ||
Line 1,140: | Line 984: | ||
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id | LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id | ||
LEFT JOIN resource ON resource.id = aircraft.id | LEFT JOIN resource ON resource.id = aircraft.id | ||
− | WHERE YEAR(start_date)= $year | + | WHERE YEAR(start_date)= $year AND departure_location_id = arrival_location_id AND duration < 90*600 |
− | GROUP BY | + | GROUP BY resource.id</sql> |
==Computation of non-"local flight" time per month== | ==Computation of non-"local flight" time per month== | ||
Line 1,197: | Line 1,041: | ||
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id | LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id | ||
LEFT JOIN resource ON resource.id = aircraft.id | LEFT JOIN resource ON resource.id = aircraft.id | ||
− | WHERE YEAR(start_date)= $year | + | WHERE YEAR(start_date)= $year AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id) |
− | GROUP BY | + | GROUP BY resource.id</sql> |
+ | |||
+ | ==Stats all flight hours per month per year== | ||
+ | |||
+ | <sql>SELECT YEAR(start_date) AS Year, 'Monthly' AS Type, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 6, duration, 0 ) ) ) AS June, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 7, duration, 0 ) ) ) AS July, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 8, duration, 0 ) ) ) AS Augu, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 9, duration, 0 ) ) ) AS Sept, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 10, duration, 0 ) ) ) AS Octo, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 11, duration, 0 ) ) ) AS Nove, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) = 12, duration, 0 ) ) ) AS Dece, | ||
+ | sexa2HoursMinute( SUM( duration ) ) AS Total | ||
+ | FROM flight | ||
+ | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
+ | GROUP BY Year | ||
+ | UNION | ||
+ | SELECT YEAR(start_date) AS Year, 'Accumulation' AS Type, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 1, duration, 0 ) ) ) AS Janu, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 2, duration, 0 ) ) ) AS Febr, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 3, duration, 0 ) ) ) AS Marc, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 4, duration, 0 ) ) ) AS Apri, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 5, duration, 0 ) ) ) AS May, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 6, duration, 0 ) ) ) AS June, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 7, duration, 0 ) ) ) AS July, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 8, duration, 0 ) ) ) AS Augu, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 9, duration, 0 ) ) ) AS Sept, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 10, duration, 0 ) ) ) AS Octo, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 11, duration, 0 ) ) ) AS Nove, | ||
+ | sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 12, duration, 0 ) ) ) AS Dece, | ||
+ | sexa2HoursMinute( SUM( duration ) ) AS Total | ||
+ | FROM flight | ||
+ | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
+ | GROUP BY Year | ||
+ | ORDER BY Type DESC, Year</sql> | ||
+ | |||
+ | ==Flight hours total per resource per flight type between two dates== | ||
+ | |||
+ | * Variable '''$startDate''' should be defined first and should be of '''Date and time''' value type. | ||
+ | * Variable '''$endDate''' should be defined first and should be of '''Date and time''' value type. | ||
+ | |||
+ | <sql>[OF_DYNAMIC_SQL] | ||
+ | SELECT CONCAT( | ||
+ | 'SELECT resource.name AS Nom, ', | ||
+ | GROUP_CONCAT( | ||
+ | CONCAT( | ||
+ | 'sexa2HoursMinute( SUM( IF( tmp_flight.flight_type_id=', | ||
+ | flight_type.id, | ||
+ | ', tmp_flight.total_duration, 0 ) ) )', | ||
+ | ' AS \'', | ||
+ | flight_type.name, | ||
+ | '\'' | ||
+ | ) | ||
+ | ), | ||
+ | ', sexa2HoursMinute( SUM( tmp_flight.total_duration ) ) AS Total | ||
+ | FROM resource | ||
+ | LEFT JOIN | ||
+ | ( | ||
+ | SELECT flight.aircraft_id, flight_type.id AS flight_type_id, SUM(duration) as total_duration | ||
+ | FROM flight | ||
+ | LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) | ||
+ | WHERE flight.start_date >= \'$startDate\' AND flight.start_date <= \'$endDate\' AND flight.airborne = 0 | ||
+ | GROUP BY flight.aircraft_id, flight_type.id | ||
+ | ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id) | ||
+ | WHERE tmp_flight.aircraft_id IS NOT NULL | ||
+ | GROUP BY resource.id | ||
+ | UNION | ||
+ | SELECT \'Total\', ', | ||
+ | GROUP_CONCAT( | ||
+ | CONCAT( | ||
+ | 'sexa2HoursMinute( SUM( IF( tmp_flight.flight_type_id=', | ||
+ | flight_type.id, | ||
+ | ', tmp_flight.total_duration, 0 ) ) )', | ||
+ | ' AS \'', | ||
+ | flight_type.name, | ||
+ | '\'' | ||
+ | ) | ||
+ | ), | ||
+ | ', sexa2HoursMinute( SUM( tmp_flight.total_duration ) ) AS Total | ||
+ | FROM resource | ||
+ | LEFT JOIN | ||
+ | ( | ||
+ | SELECT flight.aircraft_id, flight_type.id AS flight_type_id, SUM(duration) as total_duration | ||
+ | FROM flight | ||
+ | LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) | ||
+ | WHERE flight.start_date >= \'$startDate\' AND flight.start_date <= \'$endDate\' AND flight.airborne = 0 | ||
+ | GROUP BY flight.aircraft_id, flight_type.id | ||
+ | ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id) | ||
+ | WHERE tmp_flight.aircraft_id IS NOT NULL | ||
+ | ORDER BY 1' | ||
+ | ) | ||
+ | FROM flight_type | ||
+ | WHERE flight_type.activated=1</sql> | ||
+ | |||
+ | =Flight reports= | ||
+ | ==Aircraft(s) which no flight were done for at least 30 day(s)== | ||
+ | <sql>SELECT resource.id, resource.name | ||
+ | FROM flight | ||
+ | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
+ | WHERE start_date < ( NOW() - INTERVAL 30 DAY ) | ||
+ | AND virtual=0 AND activated=1 | ||
+ | GROUP BY resource.id</sql> | ||
+ | |||
+ | ==Detailed flight between two dates== | ||
+ | |||
+ | Following extrafields are needed | ||
+ | *startDate and endDate (Type : Date) | ||
+ | *profileId (Type : dbObject::Profile) | ||
+ | *occupiedSeat (Type : Integer) | ||
+ | |||
+ | This report is useful for a group of pilots from a common customer (like french administration DGAC). | ||
+ | |||
+ | <sql>SELECT | ||
+ | ( | ||
+ | SELECT CONCAT(last_name,' ',first_name) | ||
+ | FROM person | ||
+ | LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id | ||
+ | WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0 | ||
+ | GROUP BY flight.id | ||
+ | ) AS Left_seat_Pilot_name, | ||
+ | ( | ||
+ | SELECT CONCAT(last_name,' ',first_name) | ||
+ | FROM person | ||
+ | LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id | ||
+ | WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1 | ||
+ | GROUP BY flight.id | ||
+ | ) AS Right_seat_Pilot_name, | ||
+ | flight.start_date, | ||
+ | resource.name AS resource, | ||
+ | CONCAT(FLOOR(flight.duration/600),':',TIME_FORMAT(SEC_TO_TIME((flight.duration /600 - FLOOR(flight.duration/600))*3600),'%i')) AS flight_duration, | ||
+ | ( | ||
+ | SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') | ||
+ | FROM flight AS f2 | ||
+ | LEFT JOIN flight_type ON (f2.flight_type_id & flight_type.id) | ||
+ | WHERE f2.id=flight.id | ||
+ | ) AS Flight_types, | ||
+ | flight.landing_number, | ||
+ | SUM(account_entry.debit)-SUM(account_entry.credit) AS amount, | ||
+ | flight.comments | ||
+ | FROM person | ||
+ | LEFT JOIN profile ON person.profile&profile.id | ||
+ | LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id | ||
+ | LEFT JOIN flight ON flight.id=flight_pilot.flight_id | ||
+ | LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id | ||
+ | LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id | ||
+ | LEFT JOIN account ON account.id=account_entry.account_id | ||
+ | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
+ | WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate' | ||
+ | AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num | ||
+ | OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num) | ||
+ | AND ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND (account.category = 11 OR account.category = 2) | ||
+ | GROUP BY flight.id</sql> | ||
+ | |||
+ | ==Detailed flight per month== | ||
+ | Following extrafields are needed | ||
+ | *year (Type : Year) | ||
+ | *month (Type : Integer) | ||
+ | *profileId (Type : dbObject::Profile) | ||
+ | *occupiedSeat (Type : Integer) | ||
+ | |||
+ | This report is useful for a group of pilots from a common customer (like french administration DGAC). | ||
+ | |||
+ | <sql>SELECT | ||
+ | ( | ||
+ | SELECT CONCAT(last_name,' ',first_name) | ||
+ | FROM person | ||
+ | LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id | ||
+ | WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0 | ||
+ | GROUP BY flight.id | ||
+ | ) AS Left_seat_Pilot_name, | ||
+ | ( | ||
+ | SELECT CONCAT(last_name,' ',first_name) | ||
+ | FROM person | ||
+ | LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id | ||
+ | WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1 | ||
+ | GROUP BY flight.id | ||
+ | ) AS Right_seat_Pilot_name, | ||
+ | flight.start_date, | ||
+ | resource.name AS resource, | ||
+ | CONCAT(FLOOR(flight.duration/600),':',TIME_FORMAT(SEC_TO_TIME((flight.duration /600 - FLOOR(flight.duration/600))*3600),'%i')) AS flight_duration, | ||
+ | ( | ||
+ | SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') | ||
+ | FROM flight AS f2 | ||
+ | LEFT JOIN flight_type ON (f2.flight_type_id & flight_type.id) | ||
+ | WHERE f2.id=flight.id | ||
+ | ) AS Flight_types, | ||
+ | flight.landing_number, | ||
+ | SUM(account_entry.debit)-SUM(account_entry.credit) AS amount, | ||
+ | flight.comments | ||
+ | FROM person | ||
+ | LEFT JOIN profile ON person.profile&profile.id | ||
+ | LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id | ||
+ | LEFT JOIN flight ON flight.id=flight_pilot.flight_id | ||
+ | LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id | ||
+ | LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id | ||
+ | LEFT JOIN account ON account.id=account_entry.account_id | ||
+ | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
+ | WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)='$month' | ||
+ | AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num | ||
+ | OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num) | ||
+ | AND ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND (account.category = 11 OR account.category = 2) | ||
+ | GROUP BY flight.id</sql> | ||
+ | |||
+ | ==Flight between two dates== | ||
+ | Following variables are needed: | ||
+ | *startDate (Type : Datetime) | ||
+ | *endDate (Type : Datetime) | ||
− | == | + | <sql>SELECT flight.start_date AS '_tr(START_DATE)', resource.name AS '_tr(RESOURCE)', |
+ | CONCAT(UPPER(person.last_name),' ',person.first_name) AS '_tr(LEFT_PLACE)', | ||
+ | ( | ||
+ | SELECT CONCAT(UPPER(tmp_person.last_name), ' ', tmp_person.first_name) | ||
+ | FROM flight_pilot AS tmp_flight_pilot | ||
+ | LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id | ||
+ | WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 1 | ||
+ | ) AS '_tr(RIGHT_PLACE)', | ||
+ | ( | ||
+ | SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') | ||
+ | FROM flight AS tmp_flight | ||
+ | LEFT JOIN flight_type ON (tmp_flight.flight_type_id & flight_type.id) | ||
+ | WHERE flight.id=tmp_flight.id | ||
+ | ) AS '_tr(FLIGHT_TYPE)', | ||
+ | ( | ||
+ | SELECT location.name | ||
+ | FROM location | ||
+ | WHERE location.id=flight.departure_location_id | ||
+ | ) AS '_tr(DEPARTURE_FIELD)', | ||
+ | ( | ||
+ | SELECT location.name | ||
+ | FROM location | ||
+ | WHERE location.id=flight.arrival_location_id | ||
+ | ) AS '_tr(ARRIVAL_FIELD)', | ||
+ | flight.landing_number AS '_tr(FLIGHT_LANDING_NUMBER)', | ||
+ | sexa2HoursMinute(flight.duration) AS '_tr(DURATION)' | ||
+ | FROM flight | ||
+ | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
+ | LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id | ||
+ | LEFT JOIN person ON flight_pilot.pilot_id = person.id | ||
+ | WHERE flight.start_date >= '$startDate' AND flight.start_date <= '$endDate' AND flight_pilot.num = 0</sql> | ||
+ | |||
+ | ==Flight log book== | ||
+ | <sql>SELECT | ||
+ | DATE_FORMAT(start_date, '%d/%m/%Y') AS '_tr(START_DATE)', | ||
+ | resource.name AS '_tr(RESOURCE)', | ||
+ | CONCAT(last_name, ' ', first_name) AS '_tr(LEFT_PLACE)', | ||
+ | ( | ||
+ | SELECT CONCAT(person.last_name,' ',person.first_name) AS '_tr(RIGHT_PLACE)' | ||
+ | FROM flight_pilot tmp2_flight_pilot | ||
+ | LEFT JOIN person ON person.id=tmp2_flight_pilot.pilot_id | ||
+ | WHERE tmp2_flight_pilot.flight_id=flight_pilot.flight_id AND tmp2_flight_pilot.num=1 | ||
+ | ) AS '_tr(RIGHT_PLACE)', | ||
+ | IF ( (SELECT COUNT(*) FROM flight_pilot tmp_flight_pilot WHERE tmp_flight_pilot.flight_id=flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'S', 'D' ) AS 'Solo_DC', | ||
+ | sexa2HoursMinute(flight.duration) AS '_tr(DURATION)', | ||
+ | ( | ||
+ | SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') | ||
+ | FROM flight AS tmp_flight | ||
+ | LEFT JOIN flight_type ON (tmp_flight.flight_type_id & flight_type.id) | ||
+ | WHERE tmp_flight.id=flight.id | ||
+ | ) AS '_tr(FLIGHT_TYPE)', | ||
+ | flight.landing_number AS 'Nombre atterissages', | ||
+ | flight.comments AS '_tr(COMMENT)' | ||
+ | FROM flight_pilot | ||
+ | LEFT JOIN flight ON flight_pilot.flight_id=flight.id | ||
+ | LEFT JOIN flight_type ON (flight_type.id & flight.flight_type_id) | ||
+ | LEFT JOIN person ON person.id=flight_pilot.pilot_id | ||
+ | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
+ | WHERE YEAR( start_date ) = $year | ||
+ | AND flight_pilot.num=0 | ||
+ | AND flight.airborne=0 | ||
+ | GROUP BY flight.id | ||
+ | ORDER BY CONCAT(last_name, ' ', first_name), start_date</sql> | ||
+ | |||
+ | ==Flights which have landed elsewhere than the base field== | ||
Variable $icao shall be defined (dbObject:Location) | Variable $icao shall be defined (dbObject:Location) | ||
<sql> | <sql> | ||
Line 1,220: | Line 1,339: | ||
</sql> | </sql> | ||
− | == | + | ==Flights with a remark in one or two additional entries== |
− | + | * Extra field '''$maintenanceUserComment''' should be defined first and should be of '''Text multi line''' value type and '''Flight''' category. | |
− | + | * Extra field '''$mechanicAnswer''' should be defined first and should be of '''Text multi line''' value type and '''Flight''' category. | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | <sql>SELECT flight.start_date, resource.name AS Callsign, | |
− | <sql>SELECT | + | CONCAT(FLOOR( duration/600),':',TIME_FORMAT(SEC_TO_TIME((duration/600 - FLOOR(duration/600))*3600), "%i")) AS Duration, |
− | + | person.last_name AS Nom, person.first_name AS "First name", | |
− | + | l1.icao_name AS "Departure", | |
− | + | l2.icao_name AS "Arrival", | |
− | + | maintenanceUserComment.content AS "User remark", | |
− | + | mechanicAnswer.content AS "Maintenance answer" | |
− | + | FROM flight | |
− | + | LEFT JOIN resource ON resource.id = flight.aircraft_id | |
− | + | LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id | |
− | + | LEFT JOIN person ON person.id = flight_pilot.pilot_id | |
− | + | LEFT JOIN location AS l1 ON l1.id = flight.departure_location_id | |
− | + | LEFT JOIN location AS l2 ON l2.id = flight.arrival_location_id | |
− | + | LEFT JOIN extra_field_content AS maintenanceUserComment ON maintenanceUserComment.category_id = flight.id | |
− | + | LEFT JOIN extra_field_content AS mechanicAnswer ON mechanicAnswer.category_id = flight.id | |
− | + | WHERE flight_pilot.num = 0 | |
− | + | AND maintenanceUserComment.extra_field_id = (SELECT extra_field.id FROM extra_field WHERE (extra_field.variable = "maintenanceUserComment")) | |
− | + | AND mechanicAnswer.extra_field_id = (SELECT extra_field.id FROM extra_field WHERE (extra_field.variable = "mechanicAnswer")) | |
− | + | AND (maintenanceUserComment.content <> "" OR mechanicAnswer.content <> "") | |
− | + | AND start_date>DATE_SUB( NOW(), INTERVAL 3 MONTH ) | |
− | + | GROUP BY flight.id;</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ==Flight with their location code (ICAO)== | |
− | + | <sql>SELECT flight.*, L1.icao_name AS departure_location, L2.icao_name AS arrival_location | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | <sql>SELECT | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
FROM flight | FROM flight | ||
− | LEFT JOIN | + | LEFT JOIN location AS L1 ON flight.departure_location_id = L1.id |
− | + | LEFT JOIN location AS L2 ON flight.arrival_location_id = L2.id</sql> | |
− | + | ||
− | == | + | =Fuel/Oil reports= |
− | < | + | ==Fuel/Oil consumption == |
− | + | Unit is: unit of the tank per hour | |
− | + | <SQL>SELECT | |
− | + | resource_name AS CallSign, | |
− | + | CASE tank_type | |
− | + | WHEN 1 THEN "AVGAS" | |
− | + | WHEN 2 THEN "JET A1" | |
− | + | WHEN 3 THEN "Oil" | |
− | == | + | WHEN 4 THEN "SP95" |
− | + | WHEN 5 THEN "SP98" | |
− | FROM | + | WHEN 6 THEN "GAZOLE" |
− | + | END AS Tank, | |
− | LEFT JOIN flight_tank_qty ON | + | formatDecimal( CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Janu, |
− | LEFT JOIN flight ON | + | formatDecimal( CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Febr, |
− | LEFT JOIN resource ON | + | formatDecimal( CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Marc, |
− | WHERE YEAR(flight.start_date) = $year | + | formatDecimal( CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Apri, |
− | GROUP BY | + | formatDecimal( CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS May, |
+ | formatDecimal( CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS June, | ||
+ | formatDecimal( CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS July, | ||
+ | formatDecimal( CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Augu, | ||
+ | formatDecimal( CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Sept, | ||
+ | formatDecimal( CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Octo, | ||
+ | formatDecimal( CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Nove, | ||
+ | formatDecimal( CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Dece, | ||
+ | formatDecimal( CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource.id AND YEAR (start_date) = $year)*600 AS DECIMAL(10,2) ) ) AS Sum | ||
+ | FROM | ||
+ | (SELECT resource.name AS resource_name, | ||
+ | resource.id AS resource_id, | ||
+ | tank.tank_type_id AS tank_type, | ||
+ | flight_tank_qty.quantity AS quantity, | ||
+ | MONTH(flight.start_date) AS month_num, | ||
+ | (SELECT sum(flight.duration) FROM flight WHERE aircraft_id = resource_id AND YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = month_num ) AS flight_time | ||
+ | FROM tank | ||
+ | LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id | ||
+ | LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id | ||
+ | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
+ | WHERE YEAR(flight.start_date) = $year | ||
+ | ) AS my_table | ||
+ | GROUP BY resource_name, tank_type</SQL> | ||
==Fuel/Oil quantities per tank and aircraft type== | ==Fuel/Oil quantities per tank and aircraft type== | ||
<sql>SELECT | <sql>SELECT | ||
aircraft_type_name AS CallSign, tank_label AS Tank, | aircraft_type_name AS CallSign, tank_label AS Tank, | ||
− | SUM(IF (month_num=1, quantity, 0)) AS Janu, | + | formatDecimal( SUM(IF (month_num=1, quantity, 0)) ) AS Janu, |
− | SUM(IF (month_num=2, quantity, 0)) AS Febr, | + | formatDecimal( SUM(IF (month_num=2, quantity, 0)) ) AS Febr, |
− | SUM(IF (month_num=3, quantity, 0)) AS Marc, | + | formatDecimal( SUM(IF (month_num=3, quantity, 0)) ) AS Marc, |
− | SUM(IF (month_num=4, quantity, 0)) AS Apri, | + | formatDecimal( SUM(IF (month_num=4, quantity, 0)) ) AS Apri, |
− | SUM(IF (month_num=5, quantity, 0)) AS May, | + | formatDecimal( SUM(IF (month_num=5, quantity, 0)) ) AS May, |
− | SUM(IF (month_num=6, quantity, 0)) AS June, | + | formatDecimal( SUM(IF (month_num=6, quantity, 0)) ) AS June, |
− | SUM(IF (month_num=7, quantity, 0)) AS July, | + | formatDecimal( SUM(IF (month_num=7, quantity, 0)) ) AS July, |
− | SUM(IF (month_num=8, quantity, 0)) AS Augu, | + | formatDecimal( SUM(IF (month_num=8, quantity, 0)) ) AS Augu, |
− | SUM(IF (month_num=9, quantity, 0)) AS Sept, | + | formatDecimal( SUM(IF (month_num=9, quantity, 0)) ) AS Sept, |
− | SUM(IF (month_num=10, quantity, 0)) AS Octo, | + | formatDecimal( SUM(IF (month_num=10, quantity, 0)) ) AS Octo, |
− | SUM(IF (month_num=11, quantity, 0)) AS Nove, | + | formatDecimal( SUM(IF (month_num=11, quantity, 0)) ) AS Nove, |
− | SUM(IF (month_num=12, quantity, 0)) AS Dece, | + | formatDecimal( SUM(IF (month_num=12, quantity, 0)) ) AS Dece, |
− | SUM(quantity) AS Sum | + | formatDecimal( SUM(quantity) ) AS Sum |
FROM | FROM | ||
( | ( | ||
Line 1,466: | Line 1,453: | ||
WHEN 4 THEN "SP95" | WHEN 4 THEN "SP95" | ||
WHEN 5 THEN "SP98" | WHEN 5 THEN "SP98" | ||
+ | WHEN 6 THEN "GAZOLE" | ||
END AS Tank, | END AS Tank, | ||
− | SUM(IF (month_num=1, quantity, 0)) AS Janu, | + | formatDecimal( SUM(IF (month_num=1, quantity, 0)) ) AS Janu, |
− | SUM(IF (month_num=2, quantity, 0)) AS Febr, | + | formatDecimal( SUM(IF (month_num=2, quantity, 0)) ) AS Febr, |
− | SUM(IF (month_num=3, quantity, 0)) AS Marc, | + | formatDecimal( SUM(IF (month_num=3, quantity, 0)) ) AS Marc, |
− | SUM(IF (month_num=4, quantity, 0)) AS Apri, | + | formatDecimal( SUM(IF (month_num=4, quantity, 0)) ) AS Apri, |
− | SUM(IF (month_num=5, quantity, 0)) AS May, | + | formatDecimal( SUM(IF (month_num=5, quantity, 0)) ) AS May, |
− | SUM(IF (month_num=6, quantity, 0)) AS June, | + | formatDecimal( SUM(IF (month_num=6, quantity, 0)) ) AS June, |
− | SUM(IF (month_num=7, quantity, 0)) AS July, | + | formatDecimal( SUM(IF (month_num=7, quantity, 0)) ) AS July, |
− | SUM(IF (month_num=8, quantity, 0)) AS Augu, | + | formatDecimal( SUM(IF (month_num=8, quantity, 0)) ) AS Augu, |
− | SUM(IF (month_num=9, quantity, 0)) AS Sept, | + | formatDecimal( SUM(IF (month_num=9, quantity, 0)) ) AS Sept, |
− | SUM(IF (month_num=10, quantity, 0)) AS Octo, | + | formatDecimal( SUM(IF (month_num=10, quantity, 0)) ) AS Octo, |
− | SUM(IF (month_num=11, quantity, 0)) AS Nove, | + | formatDecimal( SUM(IF (month_num=11, quantity, 0)) ) AS Nove, |
− | SUM(IF (month_num=12, quantity, 0)) AS Dece, | + | formatDecimal( SUM(IF (month_num=12, quantity, 0)) ) AS Dece, |
− | SUM(quantity) AS Sum, | + | formatDecimal( SUM(quantity) ) AS Sum, |
− | CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600 AS DECIMAL(10,2) ) AS Consumption | + | formatDecimal( CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600 AS DECIMAL(10,2) ) ) AS Consumption |
FROM | FROM | ||
(SELECT resource.name AS resource_name, | (SELECT resource.name AS resource_name, | ||
Line 1,495: | Line 1,483: | ||
GROUP BY resource_name, tank_type</SQL> | GROUP BY resource_name, tank_type</SQL> | ||
− | == | + | ==Total tank quantity for each tank and for each aircraft== |
− | + | <sql>SELECT resource.name AS aircraft_callsign, tank.label AS tank_label, formatDecimal( SUM( quantity ) ) AS total_qty | |
− | < | + | FROM tank |
− | + | LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) | |
− | + | LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) | |
− | + | LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) | |
− | + | LEFT JOIN resource ON (flight.aircraft_id = resource.id) | |
− | + | WHERE YEAR(flight.start_date) = $year | |
− | + | GROUP BY resource.id, tank_type_id</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | GROUP BY | + | |
− | == | + | ==Total tank quantity for each tank and for each aircraft type== |
− | <sql>SELECT | + | <sql>SELECT resource_type.name AS aircraft_type, tank.label AS tank_label, formatDecimal( SUM( quantity ) ) AS total_qty |
− | + | FROM tank | |
− | + | LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) | |
− | + | LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) | |
− | + | LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) | |
− | + | WHERE YEAR(flight.start_date) = $year | |
− | + | GROUP BY aircraft_type_id, tank_type_id</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | LEFT JOIN | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
=Error message queries= | =Error message queries= | ||
Line 1,634: | Line 1,524: | ||
AND flight_pilot.num=0</SQL> | AND flight_pilot.num=0</SQL> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
== movement without an account== | == movement without an account== | ||
<SQL>SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE account_id is null | <SQL>SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE account_id is null | ||
SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE flow_id = 'xxxxxx'</SQL> | SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE flow_id = 'xxxxxx'</SQL> | ||
+ | |||
+ | =[[Users-reports-3|Users]]= | ||
+ | [[Users-reports-3|Users exports SQL queries]]. |
Latest revision as of 11:00, 9 February 2018
Contents
- 1 Introduction
- 2 SQL tips and tricks
- 3 French administration Examples
- 3.1 Flight type : Flight hours total on instruction with specific profile
- 3.2 Flight type : Flight hours total on non-instruction with specific profile
- 3.3 Users with a specific validity without expiration date
- 3.4 Number of movements on based airfield
- 3.5 Number of movements on based airfield on specific profile
- 3.6 Visited airfields
- 3.7 Number of take-off and landings on based airfield
- 3.8 Number of take-off and landings on based airfield on specific profile
- 3.9 List of movements on based airfield
- 3.10 Pilots who have flown without required validity
- 3.11 List of pilots who have flown less than X hours during last Y days
- 3.12 List of pilots who have flown less than X hours during last Y days on aircraft type Z
- 3.13 Pilots without flight in the last 3 months
- 3.14 Number of landings per pilot, per resource
- 3.15 Flight hours without up to date validities
- 3.16 Number of men, women over and under X years for profile P, validity V up to date for year A
- 3.17 Validities in the year : Total of youngs/adults with specific profile
- 3.18 Validities obtained in the year
- 4 Accounting reports 3
- 5 Booking
- 6 Flight time reports
- 6.1 Flight hours per month
- 6.2 Flight hours total and last recorded counter to the date X
- 6.3 Flight hours total over a 12 months period
- 6.4 Flights hours total per aircraft per year
- 6.5 Flights hours total per aircraft per year and per month (for a flight type)
- 6.6 Flight hours total per pilot
- 6.7 Flight hours total per pilot per profile
- 6.8 Total Flight hours per pilot with total duration and total amount
- 6.9 Flight hours per instructor, month (for a flight type)
- 6.10 Flights hours following nationality
- 6.11 Flights hours total per flight type per month
- 6.12 Flights hours : less than 21 years, more than 21 years, male, female, for a given profile
- 6.13 Computation of flight time per month of "local flight"
- 6.14 Computation of non-"local flight" time per month
- 6.15 Stats all flight hours per month per year
- 6.16 Flight hours total per resource per flight type between two dates
- 7 Flight reports
- 7.1 Aircraft(s) which no flight were done for at least 30 day(s)
- 7.2 Detailed flight between two dates
- 7.3 Detailed flight per month
- 7.4 Flight between two dates
- 7.5 Flight log book
- 7.6 Flights which have landed elsewhere than the base field
- 7.7 Flights with a remark in one or two additional entries
- 7.8 Flight with their location code (ICAO)
- 8 Fuel/Oil reports
- 9 Error message queries
- 10 Users
Introduction
The goal of this page is to propose a list of statistic generation queries (SQL).
Do not forget to read the OpenFlyers SQL stored functions and procedures page and the Database description page.
Please note that you can make "public" a report, ie. allows standard users to see and interact with it.
Extra field
For more flexibility, extra field can be defined per the user
Two extra field types are available:
- "external parameters" of following types:
- Whole number
- Text string
- Date
- Time
- Date and time
- database parameters (dbObject::something Value type) advanced options to access at the database field
Extra Field creation
- Go to Menu Admin > Reports > Structure > Extra Field(s)
In the bottom line add
- Fill the name field with a name that will be use within the SQL queries (prefixed with the $ character)
- Fill the label field with a name used to describe the parameter as you want it to appear within the export query form.
- Choose a Value type (the most common are at the beginning of the list).
- Click on "Add"
Then within your SQL query, you may add this parameter which will be replace by the value chosen by the user filling the export form.
If you define a parameter as a database parameters (for example dbObject::Authentication) then the form will display a combo with the list of users and your parameter will be replace by the id integer of the chosen person.
Extra Field creation examples
We create a new extra field for the current year:
- Name: $year
- Label: Year
- Value type: Year
Parameter Year must be fill in Reports/Structure/View form before to call the query (by default current year is filled when you call the page)
We create a new extra field to list the reservation of an aircraft:
- Name: $aircraftId
- Label: Aircraft
- Value type: dbObject::Aircraft
Then in Admin/Reports/Structure/Criteria we create a new query labeled "Aircraft booking" with the following query:
SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId
To use this report, we just have to select a "Aircraft" in the Reports/Structure/View form, to check "Aircraft booking" then to click on "View"
SQL tips and tricks
Return only last entry
Example with last entry from variable #1 in variable_value table:
SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC LIMIT 1
Test valid entries
For extra fields that are text inputs, any entry can be submitted and added to SQL query. In order to test valid entries, it is recommended to use the IF statement in the WHERE statement.
Per example, $occupiedSeat can have following values (0, 1 or NULL) :
SELECT * FROM flight_pilot WHERE ( IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num )
French administration Examples
Flight type : Flight hours total on instruction with specific profile
- Variable $flightTypeId should be defined first and should be of dbOject::FlightType value type.
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT tmp_flight.activity_type_name AS name, sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur', sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM' FROM ( SELECT flightWithActivityType.activity_type_id, flightWithActivityType.activity_type_name, flightWithActivityType.duration, resource_type.category FROM ( SELECT flight.*, flight_type.id AS activity_type_id, flight_type.name AS activity_type_name FROM flight LEFT JOIN flight_type ON flight.flight_type_id&flight_type.id WHERE YEAR(flight.start_date)=$year AND flight.airborne=0 AND ( flight_type.id IN ('$flightTypeId') OR '-' IN ('$flightTypeId') ) ) AS flightWithActivityType LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1) LEFT JOIN ( SELECT person.* FROM person LEFT JOIN profile ON person.profile&profile.id WHERE ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND person.activated=1 GROUP BY person.id ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id) LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id) LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) WHERE personWithProfile.id IS NOT NULL ) AS tmp_flight GROUP BY tmp_flight.activity_type_id
Flight type : Flight hours total on non-instruction with specific profile
- Variable $flightTypeId should be defined first and should be of dbOject::FlightType value type.
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT tmp_flight.activity_type_name AS name, sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur', sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM' FROM ( SELECT flightWithActivityType.activity_type_id, flightWithActivityType.activity_type_name, flightWithActivityType.duration, resource_type.category FROM ( SELECT flight.*, flight_type.id AS activity_type_id, flight_type.name AS activity_type_name FROM flight LEFT JOIN flight_type ON flight.flight_type_id&flight_type.id WHERE YEAR(flight.start_date)=$year AND flight.airborne=0 AND ( flight_type.id IN ('$flightTypeId') OR '-' IN ('$flightTypeId') ) AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) ) AS flightWithActivityType LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=0) LEFT JOIN ( SELECT person.* FROM person LEFT JOIN profile ON person.profile&profile.id WHERE ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND person.activated=1 GROUP BY person.id ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id) LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id) LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) WHERE personWithProfile.id IS NOT NULL ) AS tmp_flight GROUP BY tmp_flight.activity_type_id
Users with a specific validity without expiration date
- Variable $validityId should be defined first and should be of dbOject::ValidityType value type.
SELECT person.first_name, person.last_name FROM validity LEFT JOIN person ON person.id=validity.person_id WHERE validity_type_id=$validityTypeId AND expire_date IS NULL AND person.activated=1
Number of movements on based airfield
- Variable $year should be defined first and should be of Year value type.
SELECT SUM(movement) AS 'Movement' FROM ( SELECT COUNT(*) AS movement FROM flight LEFT JOIN location ON (flight.departure_location_id = location.id) WHERE ( ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) OR ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) ) AND ( location.id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) AND YEAR( start_date ) = $year UNION SELECT SUM(landing_number)*2 AS movement FROM flight LEFT JOIN ( SELECT location.id FROM location LEFT JOIN club ON location.icao_name=club.icao WHERE club.id IS NOT NULL LIMIT 1 ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id) WHERE YEAR(flight.start_date) = $year AND structure_icao.id IS NOT NULL ) AS tmp_movement
Number of movements on based airfield on specific profile
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT SUM(movement) AS 'Movement' FROM ( SELECT COUNT(*) AS movement FROM flight LEFT JOIN location ON (flight.departure_location_id = location.id) LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0) WHERE ( ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) OR ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) ) AND ( location.id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) AND person.activated=1 AND (person.profile & '$profileId' OR '-'='$profileId') AND YEAR(flight.start_date) = $year UNION SELECT SUM(landing_number)*2 AS movement FROM flight LEFT JOIN ( SELECT location.id FROM location LEFT JOIN club ON location.icao_name=club.icao WHERE club.id IS NOT NULL LIMIT 1 ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id) LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0) WHERE person.activated=1 AND (person.profile & '$profileId' OR '-'='$profileId') AND YEAR(flight.start_date) = $year AND structure_icao.id IS NOT NULL ) AS tmp_movement
Visited airfields
SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS nb_visite FROM flight LEFT JOIN location ON (flight.departure_location_id = location.id) WHERE ( ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) OR ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) ) AND YEAR( start_date ) = $year GROUP BY icao_name ORDER BY nb_visite DESC
Number of take-off and landings on based airfield
SELECT SUM( landing_number )*2 AS nb_mouvement FROM flight f, club c WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND (f.arrival_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND YEAR( start_date ) = $year
Number of take-off and landings on based airfield on specific profile
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT SUM(landing_number)*2 AS 'Movement' FROM flight LEFT JOIN ( SELECT location.id FROM location LEFT JOIN club ON location.icao_name=club.icao WHERE club.id IS NOT NULL LIMIT 1 ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id) LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0) WHERE person.activated=1 AND (person.profile & '$profileId' OR '-'='$profileId') AND YEAR(flight.start_date) = $year AND structure_icao.id IS NOT NULL
List of movements on based airfield
SELECT resource.name, DATE_FORMAT(flight.start_date, '%d %m %Y' ) AS Date, sum(landing_number) AS 'Nb Att' FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE ( flight.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND (flight.arrival_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND YEAR( start_date ) = $year GROUP BY date,name
Pilots who have flown without required validity
SELECT CONCAT(person.last_name, ' ', person.first_name) AS pilot, validity_type.name AS 'Validity' FROM flight_type_mandatory_validity_type LEFT JOIN flight_type ON flight_type.id = flight_type_mandatory_validity_type.flight_type_id LEFT JOIN flight ON flight.flight_type_id & flight_type.id LEFT JOIN resource ON resource.id = flight.aircraft_id LEFT JOIN resource_type ON resource.resource_type_id = resource_type.id LEFT JOIN aircraft_type_validity_type ON resource_type.id = aircraft_type_validity_type.aircraft_type_id LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id LEFT JOIN person ON person.id = flight_pilot.pilot_id LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id) LEFT JOIN validity ON (validity_type.id=validity.validity_type_id AND person.id=validity.person_id) WHERE flight.airborne = 0 AND validity_type.id IS NOT NULL AND validity_type.experience_formula IS NULL AND validity.validity_type_id IS NULL AND person.activated=1 GROUP BY person.id, validity_type.id ORDER BY pilot, validity_type.name
List of pilots who have flown less than X hours during last Y days
SELECT CONCAT(au.last_name, ' ', au.first_name) AS pilot, TIME_FORMAT( SEC_TO_TIME( SUM(f.duration)*6 ), '%H:%i' ) AS total_time FROM flight AS f RIGHT JOIN flight_pilot AS fp ON fp.flight_id=f.id LEFT JOIN aircraft AS ai ON ai.id = f.aircraft_id LEFT JOIN person AS au ON fp.pilot_id = au.id WHERE f.airborne = 0 AND start_date BETWEEN NOW() - INTERVAL Y DAY AND NOW() GROUP BY au.id HAVING SUM(f.duration)/600 < X ORDER BY pilot
List of pilots who have flown less than X hours during last Y days on aircraft type Z
SELECT CONCAT(au.last_name, ' ', au.first_name) AS pilot, TIME_FORMAT( SEC_TO_TIME( SUM(f.duration)*6 ), '%H:%i' ) AS total_time FROM flight AS f RIGHT JOIN flight_pilot AS fp ON fp.flight_id=f.id LEFT JOIN resource AS ai ON ai.id = f.aircraft_id LEFT JOIN resource_type AS at ON ai.resource_type_id = at.id LEFT JOIN person AS au ON fp.pilot_id = au.id WHERE f.airborne = 0 AND start_date BETWEEN NOW() - INTERVAL Y DAY AND NOW() AND at.id IN ( Z1, Z2, Z3, Z... ) GROUP BY au.id HAVING SUM(f.duration)/600 <= X ORDER BY pilot
Pilots without flight in the last 3 months
- Variable $month should be defined first and should be of integer value type.
SELECT person.last_name, person.first_name, IFNULL((SELECT CONCAT(DATE_FORMAT( f1.start_date, '%d/%m/%Y' ),' ', resource.name,' Duration: ', TIME_FORMAT(SEC_TO_TIME(f1.duration*6 ) ,'%H h %i')) FROM flight AS f1 LEFT JOIN flight_pilot AS fp ON fp.flight_id=f1.id LEFT JOIN resource ON resource.id=f1.aircraft_id WHERE fp.pilot_id=person.id ORDER BY f1.start_date DESC LIMIT 1),'UNKNOWN') AS 'Last flight' FROM person WHERE person.activated=1 AND person.id NOT IN ( SELECT person2.id FROM flight AS f LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id LEFT JOIN person AS person2 ON person2.id=fp.pilot_id WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL IF($month='', 1, $month*30) DAY AND NOW() GROUP BY person2.id HAVING SUM(f.duration)/600 > 0 ) ORDER BY person.last_name, person.first_name
Number of landings per pilot, per resource
SELECT last_name as "Last_name", first_name as "First_name", (IF(DATE(valid.grant_date) < date(NOW()),'B', 'E')) as 'S', -- (IF(DATE(valid.grant_date) < date(NOW()),valid_typ.name, 'Eleve')) as 'S2', (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS atterrissages, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id -- AND flight.aircraft_id = 1 AND flight.aircraft_id = 18 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS KT, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id -- AND flight.aircraft_id = 10 AND flight.aircraft_id = 2 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS QR, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id -- AND flight.aircraft_id = 2 AND flight.aircraft_id = 3 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS KH, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id -- AND flight.aircraft_id = 3 AND flight.aircraft_id = 4 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS XF, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND (flight.aircraft_id = 2 OR flight.aircraft_id = 3 OR flight.aircraft_id = 4 OR flight.aircraft_id = 18) AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS DR400, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND flight.aircraft_id = 23 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS DR500, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND flight.aircraft_id = 8 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS 'MC-IP', (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND flight.aircraft_id = 21 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS 'CAP10-DL' FROM person a LEFT JOIN profile AS p1 ON (a.profile & p1.id) LEFT JOIN validity valid ON valid.person_id = a.id LEFT JOIN validity_type valid_typ ON valid_typ.id = valid.validity_type_id WHERE YEAR(grant_date) >= YEAR(NOW()) AND a.activated=1 AND p1.name='Pilot' GROUP BY last_name, first_name ORDER BY last_name
Flight hours without up to date validities
SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS '_tr(DATE)', last_name AS '_tr(LAST_NAME)', first_name AS '_tr(FIRST_NAME)', IF ( (SELECT COUNT(*) FROM flight_pilot AS tmp_flight_pilot WHERE flight_pilot.flight_id=tmp_flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'Solo', 'DC' ) AS DC, resource.name AS '_tr(RESOURCE)', sexa2HoursMinute(duration) AS '_tr(DURATION)', flight_type.name AS '_tr(flight_type)', validity_type.name AS '_tr(VALIDITY)' FROM flight LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.flight_type_id & flight.flight_type_id LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id LEFT JOIN person ON flight_pilot.pilot_id = person.id LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id LEFT JOIN resource ON flight.aircraft_id = resource.id LEFT JOIN flight_type ON flight_type_mandatory_validity_type.flight_type_id = flight_type.id WHERE YEAR(start_date) = $year AND flight_pilot.num = 0 AND validity_type.time_limitation=1 AND validity.person_id = flight_pilot.pilot_id AND flight.start_date > validity.expire_date AND person.activated=1 ORDER BY flight.start_date, person.last_name, validity_type.name
Number of men, women over and under X years for profile P, validity V up to date for year A
- Variable $age should be of integer value type.
- Variable $profileId should be of dbOject::Profile value type.
- Variable $validityTypeId should be of dbObject::validityType value type.
- Variable $year should be of Year value type.
SELECT IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', IF( ( $year - YEAR(birthdate) >= '$age' ) , 'Yes', 'No') AS 'Adult', COUNT(DISTINCT person.id) AS NUMBER FROM person LEFT JOIN validity ON (person.id=validity.person_id) LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id) WHERE activated=1 AND (profile & '$profileId' OR '-'='$profileId') AND ( (validity_type.id='$validityTypeId' AND validity.expire_date >= '$year-12-31') OR '-'='$validityTypeId' ) GROUP BY Sex, Adult
Validities in the year : Total of youngs/adults with specific profile
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
- Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need.
SELECT validity_type.name AS 'Validity', SUM( IF( ( $year - YEAR(person.birthdate) < 21 ), 1, 0 ) ) AS 'Young', SUM( IF( ( $year - YEAR(person.birthdate) >= 21 ), 1, 0 ) ) AS 'Adult' FROM validity_type LEFT JOIN validity ON (validity.validity_type_id=validity_type.id) LEFT JOIN person ON (person.id=validity.person_id) WHERE YEAR(validity.grant_date)=$year AND person.activated=1 AND (person.profile & '$profileId' OR '-'='$profileId') GROUP BY validity_type.id ORDER BY validity_type.name
Validities obtained in the year
- Variable $validityId should be defined first and should be of dbOject::ValidityType value type.
- 21 value is the age limit to be young. Should be changed according local rules
SELECT last_name AS Last_name, first_name AS First_name, IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young, IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, validity_type.name AS 'Validity' FROM person LEFT JOIN validity ON person_id=person.id LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE YEAR(grant_date)=$year AND ( validity_type.id IN ('$validityTypeId') OR '-' IN ('$validityTypeId') ) AND person.activated=1 ORDER BY last_name, first_name
Accounting reports 3
Booking
Bookings
- Variable $endDate of Date and time value type.
- Variable $startDate of Date and time value type.
- Variable $personId of dbObject::Person value type.
- Variable $resourceId of dbObject::Resource value type.
SELECT booking.start_date AS 'Date de début', booking.end_date AS 'Date de fin', resource.name AS 'Ressource', CONCAT(left_person.last_name, ' ', left_person.first_name) AS 'Place gauche', CONCAT(right_person.last_name, ' ', right_person.first_name) AS 'Place droite' FROM booking LEFT JOIN resource ON (resource.id=booking.resource_id) LEFT JOIN person AS left_person ON (left_person.id=booking.person_id) LEFT JOIN person AS right_person ON (right_person.id=booking.instructor_id) WHERE booking.start_date >= '$startDate' AND booking.end_date < '$endDate' AND (booking.resource_id='$resourceId' OR '-'='$resourceId') AND ( (left_person.id='$personId' AND left_person.activated=1) OR (right_person.id='$personId' AND right_person.activated=1) OR '-'='$personId' ) ORDER BY booking.start_date, booking.end_date
Cumulated maintenance hours between a period
Following extrafields are needed :
- startDate (Type : Date)
- endDate (Type : Date or DateTime)
SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours' UNION ALL SELECT resource.name AS 'Ressource', SUM(TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date)), SUM(TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date)) FROM booking LEFT JOIN log ON log.field_value=booking.id LEFT JOIN journal ON journal.id=log.journal_id LEFT JOIN resource ON resource.id = booking.resource_id WHERE (booking.slot_type=2) AND (log.action='INSERT') AND (log.table_name='booking') AND (log.field_name='id') AND (booking.start_date >= '$startDate') AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate')) GROUP BY resource.id ORDER BY 1
Maintenance hours per resource on a period
Following extrafields are needed :
- startDate (Type : Date)
- endDate (Type : Date or DateTime)
SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Date debut maintenance', '' AS 'Date fin maintenance', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours' UNION ALL SELECT resource.name, booking.start_date AS 'Date debut maintenance', booking.end_date, TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date), TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date) FROM booking LEFT JOIN log ON log.field_value=booking.id LEFT JOIN journal ON journal.id=log.journal_id LEFT JOIN resource ON resource.id = booking.resource_id WHERE (booking.slot_type=2) AND (log.action='INSERT') AND (log.table_name='booking') AND (log.field_name='id') AND (booking.start_date >= '$startDate') AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate')) ORDER BY 1, 2
Booking for maintenance ordered by resource and date with name of the responsible
SELECT resource.name AS 'Resource name', booking.start_date AS 'Start date', TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date) AS 'booking duration', person.first_name AS 'First name', person.last_name AS 'Last name' FROM booking LEFT JOIN log ON log.field_value=booking.id LEFT JOIN journal ON journal.id=log.journal_id LEFT JOIN person ON person.name=journal.login LEFT JOIN resource ON resource.id = booking.resource_id WHERE (booking.slot_type=2) AND (log.action='INSERT') AND (log.table_name='booking') AND (log.field_name='id') AND YEAR(booking.start_date) = $year AND person.activated=1 ORDER BY resource.name, booking.start_date
Flight time reports
Flight hours per month
SELECT MONTH( start_date) AS months, 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 aircraft ON aircraft.id = flight.aircraft_id WHERE YEAR(start_date) = $year GROUP BY months
Flight hours total and last recorded counter to the date X
- Variable $endDate should be of Datetime value type.
SELECT resource.name, sexa2HoursMinute( SUM(duration) + aircraft.ref_hours ) AS 'Total heures', sexa2HoursMinute( MAX(counter_arrival) ) AS 'Dernier compteur' FROM flight LEFT JOIN resource ON (resource.id=flight.aircraft_id) LEFT JOIN aircraft ON (aircraft.id=resource.id) WHERE flight.start_date <= '$endDate' AND resource.activated=1 GROUP BY resource.id ORDER BY resource.name
Flight hours total over a 12 months period
SELECT CONCAT(last_name,' ',first_name) AS 'Person', IF (( SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'double') AS Solo_DC, CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id LEFT JOIN person a ON a.id=fp.pilot_id WHERE start_date > DATE_SUB(NOW(), INTERVAL 1 YEAR) AND fp.num=0 GROUP BY Person, Solo_DC
Flights hours total per aircraft per year
SELECT name AS Callsign, YEAR( start_date ) AS Year, 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 resource ON resource.id = flight.aircraft_id WHERE YEAR(start_date) = $year AND airborne = 0 GROUP BY name UNION SELECT "Total", $year AS year, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight WHERE YEAR(start_date) = $year AND airborne = 0 GROUP BY year
Flights hours total per aircraft per year and per month (for a flight type)
- Variable $flightTypeId should be defined first and should be of dbOject::FlightType value type.
- Variable $year should be defined first and should be of Year value type.
SELECT aircraft_id AS Num, resource.name, sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS Janu, sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS Febr, sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS Marc, sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS Apri, sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS May, sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS June, sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS July, sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS Augu, sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS Sept, sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS Octo, sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS Nove, sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS Dece, sexa2HoursMinute( SUM( duration ) ) AS SUM FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND airborne = 0 AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId') GROUP BY resource.id UNION SELECT "Sum per","month", sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ), sexa2HoursMinute( SUM( duration ) ) AS Total FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND airborne = 0 AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId') UNION SELECT "Cumulative","per month", sexa2HoursMinute( SUM( IF( MONTH(start_date)<=1, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=2, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=3, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=4, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=5, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=6, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=7, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=8, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=9, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=10, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=11, duration, 0 ) ) ), sexa2HoursMinute( SUM( IF( MONTH(start_date)<=12, duration, 0 ) ) ), sexa2HoursMinute( SUM( duration ) ) AS Total FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND airborne = 0 AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')
Flight hours total per pilot
SELECT CONCAT(last_name,' ',first_name) AS Nom, sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) ) AS 'Solo', sexa2HoursMinute( SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS 'Double', sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) + SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS 'Total' FROM person LEFT JOIN ( SELECT pilot_id, IF ( (SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'DC' ) AS Solo_DC, SUM(flight.duration) AS sum_duration FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id WHERE flight.airborne=0 AND YEAR(start_date) = $year AND fp.num=0 GROUP BY pilot_id, Solo_DC ) AS Solo ON person.id=Solo.pilot_id AND Solo.Solo_DC='Solo' LEFT JOIN ( SELECT pilot_id, IF ( (SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'DC' ) AS Solo_DC, SUM(flight.duration) AS sum_duration FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id WHERE flight.airborne=0 AND YEAR(start_date) = $year AND fp.num=0 GROUP BY pilot_id, Solo_DC ) AS DC ON person.id=DC.pilot_id AND DC.Solo_DC='DC' WHERE (Solo.pilot_id IS NOT NULL OR DC.pilot_id IS NOT NULL) AND person.activated=1 GROUP BY person.id ORDER BY Nom
Flight hours total per pilot per profile
SELECT profile.name AS Profil, CONCAT(last_name,' ',first_name) AS Nom, IF (( SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'double') AS Solo_DC, sexa2HoursMinute( SUM( flight.duration ) ) AS Total FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id LEFT JOIN person ON person.id=fp.pilot_id LEFT JOIN profile ON (person.profile & profile.id) WHERE flight.airborne=0 AND YEAR( start_date ) = $year AND fp.num=0 GROUP BY Profil, Nom, Solo_DC
Total Flight hours per pilot with total duration and total amount
Following extrafields are needed
- year (Type : Year)
- month (Type : Integer) OR startDate and endDate (Type : Date)
- $occupiedSeat (Type : Integer)
- profileId (Type : dbObject::Profile)
This report is useful for a group of pilots from a common customer (like DGAC in France)
SELECT CONCAT(person.last_name,' ',person.first_name) AS Nom_et_prenom_pilotes, CONCAT(FLOOR(SUM( flight_list.duree )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight_list.duree )/600 - FLOOR(SUM( flight_list.duree )/600))*3600),'%i')) AS nombre_hdv_effectuees, SUM(flight_list.montant) AS montant_total_des_vols FROM person JOIN (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id LEFT JOIN flight ON flight.id=flight_pilot.flight_id LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id LEFT JOIN account ON account.id=account_entry.account_id WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate' AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num) AND person.profile&$profileId AND (account.category = 11 OR account.category = 2) GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id GROUP BY person.id
Flight hours per instructor, month (for a flight type)
Following extrafields are needed :
- year (Type : Year)
- flightTypeId (Type : dbObject::FlightType)
SELECT UPPER(last_name) AS Nom, first_name AS Prénom, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Janv, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Fevr, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mars, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Avri, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mai, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juin, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juil, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Aout, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Sept, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Oct, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Nov, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Dece, sexa2HoursMinute( SUM( duration ) ) 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 flight.airborne=0 AND a.activated=1 AND fp.num = 1 AND YEAR( start_date ) = $year AND ( flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId' ) GROUP BY a.id UNION SELECT "Sum per","month", (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Janv, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Fevr, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mars, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Avri, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mai, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juin, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juil, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Aout, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Sept, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Oct, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Nov, (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Dece, sexa2HoursMinute( SUM( duration ) ) 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 flight.airborne=0 AND a.activated=1 AND fp.num = 1 AND YEAR( start_date ) = $year AND ( flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId' )
Flights hours following nationality
SELECT nationality.label, CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total FROM flight , flight_pilot fp, person a, nationality WHERE flight.id = fp.flight_id AND fp.pilot_id = a.id AND a.nationality = nationality.code GROUP BY nationality.code
Flights hours total per flight type per month
SELECT flight_type.name AS Type_vol, flight_pilot_DC.DC, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 6, duration, 0 ) ) ) AS June, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 7, duration, 0 ) ) ) AS July, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 8, duration, 0 ) ) ) AS Augu, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 9, duration, 0 ) ) ) AS Sept, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 10, duration, 0 ) ) ) AS Octo, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 11, duration, 0 ) ) ) AS Nove, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 12, duration, 0 ) ) ) AS Dece, sexa2HoursMinute( SUM( duration ) ) AS Total FROM flight LEFT JOIN flight_type ON flight_type.id & flight.flight_type_id LEFT JOIN ( SELECT flight_id, IF( MAX(num) = 1, 'Double', 'Solo' ) AS DC FROM flight_pilot WHERE flight_pilot.num <= 1 GROUP BY flight_id ) AS flight_pilot_DC ON flight.id=flight_pilot_DC.flight_id WHERE YEAR(start_date) = $year AND airborne = 0 GROUP BY flight_type.id, flight_pilot_DC.DC HAVING (Name <> 'Instruction' OR flight_pilot_DC.DC <> 'Double') ORDER BY order_num ASC, DC DESC
Flights hours : less than 21 years, more than 21 years, male, female, for a given profile
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT Sex, Young, Instruction, sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur', sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM' FROM ( SELECT IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young, IF ((SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'Instruction') AS Instruction, flight.duration, resource_type.category FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id LEFT JOIN person ON person.id=fp.pilot_id LEFT JOIN profile ON person.profile&profile.id LEFT JOIN resource ON (resource.id=flight.aircraft_id) LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) WHERE YEAR( start_date )=$year AND fp.num=0 AND flight.airborne = 0 AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND person.activated=1 GROUP BY flight.id ) AS tmp_stat GROUP BY Sex, Young, Instruction WITH ROLLUP
Computation of flight time per month of "local flight"
"Local flight" is defined as a flight less than 1h30 and with the same departure and destination
SELECT aircraft_id AS Num, name AS Immat, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Janv, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Fevr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Mars, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Avri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Mai, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Juin, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Juil, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Aout, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Octo, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Nove, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num ) AS Dece, 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 aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND departure_location_id = arrival_location_id AND duration < 90*600 GROUP BY resource.id
Computation of non-"local flight" time per month
SELECT aircraft_id AS Num, name AS Immat, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Janv, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Fevr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Mars, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Avri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Mai, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Juin, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Juil, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Aout, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Octo, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Nove, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Dece, 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 aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id) GROUP BY resource.id
Stats all flight hours per month per year
SELECT YEAR(start_date) AS Year, 'Monthly' AS Type, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 6, duration, 0 ) ) ) AS June, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 7, duration, 0 ) ) ) AS July, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 8, duration, 0 ) ) ) AS Augu, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 9, duration, 0 ) ) ) AS Sept, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 10, duration, 0 ) ) ) AS Octo, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 11, duration, 0 ) ) ) AS Nove, sexa2HoursMinute( SUM( IF( MONTH(start_date) = 12, duration, 0 ) ) ) AS Dece, sexa2HoursMinute( SUM( duration ) ) AS Total FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id GROUP BY Year UNION SELECT YEAR(start_date) AS Year, 'Accumulation' AS Type, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 1, duration, 0 ) ) ) AS Janu, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 2, duration, 0 ) ) ) AS Febr, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 3, duration, 0 ) ) ) AS Marc, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 4, duration, 0 ) ) ) AS Apri, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 5, duration, 0 ) ) ) AS May, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 6, duration, 0 ) ) ) AS June, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 7, duration, 0 ) ) ) AS July, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 8, duration, 0 ) ) ) AS Augu, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 9, duration, 0 ) ) ) AS Sept, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 10, duration, 0 ) ) ) AS Octo, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 11, duration, 0 ) ) ) AS Nove, sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 12, duration, 0 ) ) ) AS Dece, sexa2HoursMinute( SUM( duration ) ) AS Total FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id GROUP BY Year ORDER BY Type DESC, Year
Flight hours total per resource per flight type between two dates
- Variable $startDate should be defined first and should be of Date and time value type.
- Variable $endDate should be defined first and should be of Date and time value type.
[OF_DYNAMIC_SQL] SELECT CONCAT( 'SELECT resource.name AS Nom, ', GROUP_CONCAT( CONCAT( 'sexa2HoursMinute( SUM( IF( tmp_flight.flight_type_id=', flight_type.id, ', tmp_flight.total_duration, 0 ) ) )', ' AS \'', flight_type.name, '\'' ) ), ', sexa2HoursMinute( SUM( tmp_flight.total_duration ) ) AS Total FROM resource LEFT JOIN ( SELECT flight.aircraft_id, flight_type.id AS flight_type_id, SUM(duration) as total_duration FROM flight LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) WHERE flight.start_date >= \'$startDate\' AND flight.start_date <= \'$endDate\' AND flight.airborne = 0 GROUP BY flight.aircraft_id, flight_type.id ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id) WHERE tmp_flight.aircraft_id IS NOT NULL GROUP BY resource.id UNION SELECT \'Total\', ', GROUP_CONCAT( CONCAT( 'sexa2HoursMinute( SUM( IF( tmp_flight.flight_type_id=', flight_type.id, ', tmp_flight.total_duration, 0 ) ) )', ' AS \'', flight_type.name, '\'' ) ), ', sexa2HoursMinute( SUM( tmp_flight.total_duration ) ) AS Total FROM resource LEFT JOIN ( SELECT flight.aircraft_id, flight_type.id AS flight_type_id, SUM(duration) as total_duration FROM flight LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) WHERE flight.start_date >= \'$startDate\' AND flight.start_date <= \'$endDate\' AND flight.airborne = 0 GROUP BY flight.aircraft_id, flight_type.id ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id) WHERE tmp_flight.aircraft_id IS NOT NULL ORDER BY 1' ) FROM flight_type WHERE flight_type.activated=1
Flight reports
Aircraft(s) which no flight were done for at least 30 day(s)
SELECT resource.id, resource.name FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE start_date < ( NOW() - INTERVAL 30 DAY ) AND virtual=0 AND activated=1 GROUP BY resource.id
Detailed flight between two dates
Following extrafields are needed
- startDate and endDate (Type : Date)
- profileId (Type : dbObject::Profile)
- occupiedSeat (Type : Integer)
This report is useful for a group of pilots from a common customer (like french administration DGAC).
SELECT ( SELECT CONCAT(last_name,' ',first_name) FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0 GROUP BY flight.id ) AS Left_seat_Pilot_name, ( SELECT CONCAT(last_name,' ',first_name) FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1 GROUP BY flight.id ) AS Right_seat_Pilot_name, flight.start_date, resource.name AS resource, CONCAT(FLOOR(flight.duration/600),':',TIME_FORMAT(SEC_TO_TIME((flight.duration /600 - FLOOR(flight.duration/600))*3600),'%i')) AS flight_duration, ( SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') FROM flight AS f2 LEFT JOIN flight_type ON (f2.flight_type_id & flight_type.id) WHERE f2.id=flight.id ) AS Flight_types, flight.landing_number, SUM(account_entry.debit)-SUM(account_entry.credit) AS amount, flight.comments FROM person LEFT JOIN profile ON person.profile&profile.id LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id LEFT JOIN flight ON flight.id=flight_pilot.flight_id LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id LEFT JOIN account ON account.id=account_entry.account_id LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate' AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num) AND ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND (account.category = 11 OR account.category = 2) GROUP BY flight.id
Detailed flight per month
Following extrafields are needed
- year (Type : Year)
- month (Type : Integer)
- profileId (Type : dbObject::Profile)
- occupiedSeat (Type : Integer)
This report is useful for a group of pilots from a common customer (like french administration DGAC).
SELECT ( SELECT CONCAT(last_name,' ',first_name) FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0 GROUP BY flight.id ) AS Left_seat_Pilot_name, ( SELECT CONCAT(last_name,' ',first_name) FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1 GROUP BY flight.id ) AS Right_seat_Pilot_name, flight.start_date, resource.name AS resource, CONCAT(FLOOR(flight.duration/600),':',TIME_FORMAT(SEC_TO_TIME((flight.duration /600 - FLOOR(flight.duration/600))*3600),'%i')) AS flight_duration, ( SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') FROM flight AS f2 LEFT JOIN flight_type ON (f2.flight_type_id & flight_type.id) WHERE f2.id=flight.id ) AS Flight_types, flight.landing_number, SUM(account_entry.debit)-SUM(account_entry.credit) AS amount, flight.comments FROM person LEFT JOIN profile ON person.profile&profile.id LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id LEFT JOIN flight ON flight.id=flight_pilot.flight_id LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id LEFT JOIN account ON account.id=account_entry.account_id LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)='$month' AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num) AND ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND (account.category = 11 OR account.category = 2) GROUP BY flight.id
Flight between two dates
Following variables are needed:
- startDate (Type : Datetime)
- endDate (Type : Datetime)
SELECT flight.start_date AS '_tr(START_DATE)', resource.name AS '_tr(RESOURCE)', CONCAT(UPPER(person.last_name),' ',person.first_name) AS '_tr(LEFT_PLACE)', ( SELECT CONCAT(UPPER(tmp_person.last_name), ' ', tmp_person.first_name) FROM flight_pilot AS tmp_flight_pilot LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 1 ) AS '_tr(RIGHT_PLACE)', ( SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') FROM flight AS tmp_flight LEFT JOIN flight_type ON (tmp_flight.flight_type_id & flight_type.id) WHERE flight.id=tmp_flight.id ) AS '_tr(FLIGHT_TYPE)', ( SELECT location.name FROM location WHERE location.id=flight.departure_location_id ) AS '_tr(DEPARTURE_FIELD)', ( SELECT location.name FROM location WHERE location.id=flight.arrival_location_id ) AS '_tr(ARRIVAL_FIELD)', flight.landing_number AS '_tr(FLIGHT_LANDING_NUMBER)', sexa2HoursMinute(flight.duration) AS '_tr(DURATION)' FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id LEFT JOIN person ON flight_pilot.pilot_id = person.id WHERE flight.start_date >= '$startDate' AND flight.start_date <= '$endDate' AND flight_pilot.num = 0
Flight log book
SELECT DATE_FORMAT(start_date, '%d/%m/%Y') AS '_tr(START_DATE)', resource.name AS '_tr(RESOURCE)', CONCAT(last_name, ' ', first_name) AS '_tr(LEFT_PLACE)', ( SELECT CONCAT(person.last_name,' ',person.first_name) AS '_tr(RIGHT_PLACE)' FROM flight_pilot tmp2_flight_pilot LEFT JOIN person ON person.id=tmp2_flight_pilot.pilot_id WHERE tmp2_flight_pilot.flight_id=flight_pilot.flight_id AND tmp2_flight_pilot.num=1 ) AS '_tr(RIGHT_PLACE)', IF ( (SELECT COUNT(*) FROM flight_pilot tmp_flight_pilot WHERE tmp_flight_pilot.flight_id=flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'S', 'D' ) AS 'Solo_DC', sexa2HoursMinute(flight.duration) AS '_tr(DURATION)', ( SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') FROM flight AS tmp_flight LEFT JOIN flight_type ON (tmp_flight.flight_type_id & flight_type.id) WHERE tmp_flight.id=flight.id ) AS '_tr(FLIGHT_TYPE)', flight.landing_number AS 'Nombre atterissages', flight.comments AS '_tr(COMMENT)' FROM flight_pilot LEFT JOIN flight ON flight_pilot.flight_id=flight.id LEFT JOIN flight_type ON (flight_type.id & flight.flight_type_id) LEFT JOIN person ON person.id=flight_pilot.pilot_id LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR( start_date ) = $year AND flight_pilot.num=0 AND flight.airborne=0 GROUP BY flight.id ORDER BY CONCAT(last_name, ' ', first_name), start_date
Flights which have landed elsewhere than the base field
Variable $icao shall be defined (dbObject:Location)
SELECT DATE_FORMAT(flight.start_date,'%d/%m/%Y') AS Date, resource.name AS immatriculation, CONCAT(FLOOR( duration/600),':',TIME_FORMAT(SEC_TO_TIME((duration/600 - FLOOR(duration/600))*3600),'%i')) AS Durée, person.last_name AS Nom, person.first_name AS Prénom, l1.icao_name AS Départ, l2.icao_name AS Arrivée FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id LEFT JOIN person ON person.id = flight_pilot.pilot_id LEFT JOIN location AS l1 ON l1.id = flight.departure_location_id LEFT JOIN location AS l2 ON l2.id = flight.arrival_location_id WHERE flight_pilot.num = 0 AND ( l1.icao_name LIKE '$icao' OR l2.icao_name LIKE '$icao') GROUP BY flight.id
Flights with a remark in one or two additional entries
- Extra field $maintenanceUserComment should be defined first and should be of Text multi line value type and Flight category.
- Extra field $mechanicAnswer should be defined first and should be of Text multi line value type and Flight category.
SELECT flight.start_date, resource.name AS Callsign, CONCAT(FLOOR( duration/600),':',TIME_FORMAT(SEC_TO_TIME((duration/600 - FLOOR(duration/600))*3600), "%i")) AS Duration, person.last_name AS Nom, person.first_name AS "First name", l1.icao_name AS "Departure", l2.icao_name AS "Arrival", maintenanceUserComment.content AS "User remark", mechanicAnswer.content AS "Maintenance answer" FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id LEFT JOIN person ON person.id = flight_pilot.pilot_id LEFT JOIN location AS l1 ON l1.id = flight.departure_location_id LEFT JOIN location AS l2 ON l2.id = flight.arrival_location_id LEFT JOIN extra_field_content AS maintenanceUserComment ON maintenanceUserComment.category_id = flight.id LEFT JOIN extra_field_content AS mechanicAnswer ON mechanicAnswer.category_id = flight.id WHERE flight_pilot.num = 0 AND maintenanceUserComment.extra_field_id = (SELECT extra_field.id FROM extra_field WHERE (extra_field.variable = "maintenanceUserComment")) AND mechanicAnswer.extra_field_id = (SELECT extra_field.id FROM extra_field WHERE (extra_field.variable = "mechanicAnswer")) AND (maintenanceUserComment.content <> "" OR mechanicAnswer.content <> "") AND start_date>DATE_SUB( NOW(), INTERVAL 3 MONTH ) GROUP BY flight.id;
Flight with their location code (ICAO)
SELECT flight.*, L1.icao_name AS departure_location, L2.icao_name AS arrival_location FROM flight LEFT JOIN location AS L1 ON flight.departure_location_id = L1.id LEFT JOIN location AS L2 ON flight.arrival_location_id = L2.id
Fuel/Oil reports
Fuel/Oil consumption
Unit is: unit of the tank per hour
SELECT resource_name AS CallSign, CASE tank_type WHEN 1 THEN "AVGAS" WHEN 2 THEN "JET A1" WHEN 3 THEN "Oil" WHEN 4 THEN "SP95" WHEN 5 THEN "SP98" WHEN 6 THEN "GAZOLE" END AS Tank, formatDecimal( CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Janu, formatDecimal( CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Febr, formatDecimal( CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Marc, formatDecimal( CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Apri, formatDecimal( CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS May, formatDecimal( CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS June, formatDecimal( CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS July, formatDecimal( CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Augu, formatDecimal( CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Sept, formatDecimal( CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Octo, formatDecimal( CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Nove, formatDecimal( CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Dece, formatDecimal( CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource.id AND YEAR (start_date) = $year)*600 AS DECIMAL(10,2) ) ) AS Sum FROM (SELECT resource.name AS resource_name, resource.id AS resource_id, tank.tank_type_id AS tank_type, flight_tank_qty.quantity AS quantity, MONTH(flight.start_date) AS month_num, (SELECT sum(flight.duration) FROM flight WHERE aircraft_id = resource_id AND YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = month_num ) AS flight_time FROM tank LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR(flight.start_date) = $year ) AS my_table GROUP BY resource_name, tank_type
Fuel/Oil quantities per tank and aircraft type
SELECT aircraft_type_name AS CallSign, tank_label AS Tank, formatDecimal( SUM(IF (month_num=1, quantity, 0)) ) AS Janu, formatDecimal( SUM(IF (month_num=2, quantity, 0)) ) AS Febr, formatDecimal( SUM(IF (month_num=3, quantity, 0)) ) AS Marc, formatDecimal( SUM(IF (month_num=4, quantity, 0)) ) AS Apri, formatDecimal( SUM(IF (month_num=5, quantity, 0)) ) AS May, formatDecimal( SUM(IF (month_num=6, quantity, 0)) ) AS June, formatDecimal( SUM(IF (month_num=7, quantity, 0)) ) AS July, formatDecimal( SUM(IF (month_num=8, quantity, 0)) ) AS Augu, formatDecimal( SUM(IF (month_num=9, quantity, 0)) ) AS Sept, formatDecimal( SUM(IF (month_num=10, quantity, 0)) ) AS Octo, formatDecimal( SUM(IF (month_num=11, quantity, 0)) ) AS Nove, formatDecimal( SUM(IF (month_num=12, quantity, 0)) ) AS Dece, formatDecimal( SUM(quantity) ) AS Sum FROM ( SELECT resource_type.id AS aircraft_type_id, resource_type.name AS aircraft_type_name, tank.id AS tank_id, tank.label AS tank_label, CAST( flight_tank_qty.quantity AS DECIMAL(10,2) ) AS quantity, MONTH(flight.start_date) AS month_num FROM tank LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) WHERE YEAR(flight.start_date) = $year ) AS my_table GROUP BY aircraft_type_id, tank_id
Fuel/Oil sum for each aircraft
Unit is: unit of the tank
SELECT resource_name AS CallSign, CASE tank_type WHEN 1 THEN "AVGAS" WHEN 2 THEN "JET A1" WHEN 3 THEN "Oil" WHEN 4 THEN "SP95" WHEN 5 THEN "SP98" WHEN 6 THEN "GAZOLE" END AS Tank, formatDecimal( SUM(IF (month_num=1, quantity, 0)) ) AS Janu, formatDecimal( SUM(IF (month_num=2, quantity, 0)) ) AS Febr, formatDecimal( SUM(IF (month_num=3, quantity, 0)) ) AS Marc, formatDecimal( SUM(IF (month_num=4, quantity, 0)) ) AS Apri, formatDecimal( SUM(IF (month_num=5, quantity, 0)) ) AS May, formatDecimal( SUM(IF (month_num=6, quantity, 0)) ) AS June, formatDecimal( SUM(IF (month_num=7, quantity, 0)) ) AS July, formatDecimal( SUM(IF (month_num=8, quantity, 0)) ) AS Augu, formatDecimal( SUM(IF (month_num=9, quantity, 0)) ) AS Sept, formatDecimal( SUM(IF (month_num=10, quantity, 0)) ) AS Octo, formatDecimal( SUM(IF (month_num=11, quantity, 0)) ) AS Nove, formatDecimal( SUM(IF (month_num=12, quantity, 0)) ) AS Dece, formatDecimal( SUM(quantity) ) AS Sum, formatDecimal( CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600 AS DECIMAL(10,2) ) ) AS Consumption FROM (SELECT resource.name AS resource_name, resource.id AS resource_id, tank.tank_type_id AS tank_type, CAST( flight_tank_qty.quantity AS DECIMAL(10,2) ) AS quantity, MONTH(flight.start_date) AS month_num FROM tank LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR(flight.start_date) = $year ) AS my_table GROUP BY resource_name, tank_type
Total tank quantity for each tank and for each aircraft
SELECT resource.name AS aircraft_callsign, tank.label AS tank_label, formatDecimal( SUM( quantity ) ) AS total_qty FROM tank LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) LEFT JOIN resource ON (flight.aircraft_id = resource.id) WHERE YEAR(flight.start_date) = $year GROUP BY resource.id, tank_type_id
Total tank quantity for each tank and for each aircraft type
SELECT resource_type.name AS aircraft_type, tank.label AS tank_label, formatDecimal( SUM( quantity ) ) AS total_qty FROM tank LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) WHERE YEAR(flight.start_date) = $year GROUP BY aircraft_type_id, tank_type_id
Error message queries
Flight without account movement
SELECT DATE_FORMAT(flight.start_date,'%d/%m/%Y' ) AS Date, TIME_FORMAT(flight.start_date,'%H:%i' ) AS Time, resource.name AS Callsign, person.last_name AS Lastname, person.first_name AS Firstname, CONCAT(FLOOR( flight.duration /600),':', TIME_FORMAT(SEC_TO_TIME(( flight.duration/600 - FLOOR( flight.duration /600))*3600),'%i')) AS FlightTime FROM flight LEFT JOIN flight_account_entry ON flight.id=flight_account_entry.flight_id LEFT JOIN resource ON resource.id=flight.aircraft_id LEFT JOIN flight_pilot ON flight_pilot.flight_id=flight.id LEFT JOIN person ON person.id=flight_pilot.pilot_id WHERE flight_account_entry.account_entry_id IS NULL AND flight.airborne=0 AND flight_pilot.num=0
movement without an account
SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE account_id is null SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE flow_id = 'xxxxxx'