Difference between revisions of "Export generator 4"
(→Total hours by activities and resource categories for an instructor for one year) |
(→Flight hours total per person) |
||
Line 1,406: | Line 1,406: | ||
<sql>SELECT | <sql>SELECT | ||
− | CONCAT(person.last_name,' ',person.first_name) AS | + | CONCAT(person.last_name,' ',person.first_name) AS _tr(LAST_NAME), |
− | sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) ) AS _tr(ALONE), |
− | sexa2HoursMinute( SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS _tr(LEGEND_INSTRUCTION), |
− | sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) + SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) + SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS _tr(TOTAL) |
FROM person | FROM person | ||
LEFT JOIN ( | LEFT JOIN ( | ||
Line 1,422: | Line 1,422: | ||
FROM flight_pilot flight_pilot_1 | FROM flight_pilot flight_pilot_1 | ||
LEFT JOIN flight ON flight_pilot_1.flight_id=flight.id | LEFT JOIN flight ON flight_pilot_1.flight_id=flight.id | ||
− | WHERE flight.airborne=0 AND flight.start_date >= | + | WHERE flight.airborne=0 AND flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight_pilot_1.num=0 |
GROUP BY pilot_id, Solo_DC | GROUP BY pilot_id, Solo_DC | ||
) AS Solo ON person.id=Solo.pilot_id AND Solo.Solo_DC='Solo' | ) AS Solo ON person.id=Solo.pilot_id AND Solo.Solo_DC='Solo' | ||
Line 1,436: | Line 1,436: | ||
FROM flight_pilot flight_pilot_1 | FROM flight_pilot flight_pilot_1 | ||
LEFT JOIN flight ON flight_pilot_1.flight_id=flight.id | LEFT JOIN flight ON flight_pilot_1.flight_id=flight.id | ||
− | WHERE flight.airborne=0 AND flight.start_date >= | + | WHERE flight.airborne=0 AND flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight_pilot_1.num=0 |
GROUP BY pilot_id, Solo_DC | GROUP BY pilot_id, Solo_DC | ||
) AS DC ON person.id=DC.pilot_id AND DC.Solo_DC='DC' | ) AS DC ON person.id=DC.pilot_id AND DC.Solo_DC='DC' | ||
Line 1,442: | Line 1,442: | ||
AND person.activated=1 | AND person.activated=1 | ||
GROUP BY person.id | GROUP BY person.id | ||
− | ORDER BY | + | ORDER BY Nom</sql> |
=Fuel/Oil reports= | =Fuel/Oil reports= |
Revision as of 19:19, 5 October 2021
Contents
- 1 Introduction
- 2 SQL tips and tricks
- 3 French administration Examples
- 3.1 Flight hours total on instruction with specific activity type/profile
- 3.2 Flight hours total on non-instruction with specific activity type/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 Number of take-off and landings on based airfield
- 3.7 Number of take-off and landings on based airfield on specific profile
- 3.8 List of movements on based airfield
- 3.9 User flying without validity
- 3.10 List of pilots who have flown less than X hours during last Y days
- 3.11 List of pilots who have flown less than X hours during last Y days on aircraft type Z
- 3.12 Pilots without flight in the last X months
- 3.13 Total user per age profile validity: Number of men, women over and under X years for profile P, validity V up to date for year A
- 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 21 years for profile X, validity Y up to date for year Z
- 3.17 Total hours by activities and resource categories for an instructor for one year
- 3.18 Validity year young specific profile: Total of youngs/adults with specific profile
- 3.19 Validities obtained in the year
- 3.20 Visited airfields
- 3.21 Visited outsider airfield
- 3.22 Young from this year
- 4 Accounting
- 5 Bookings
- 6 Flight time management
- 6.1 Flight hours total and last recorded counters to the date X
- 6.2 Flight hours per month
- 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 an activity 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
- 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 per activity type between date
- 7 Flight reports
- 7.1 Activities summary for a person on the right place for a given month
- 7.2 Aircraft(s) which no flight were done for at least 30 day(s)
- 7.3 Flight between two dates
- 7.4 Flight log book
- 7.5 Flights which have landed elsewhere than the base field
- 7.6 Flights with mechanic remark
- 7.7 Flight with their location code (ICAO)
- 7.8 Flight hours total per person
- 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.
Please note that you can make "public" a report, ie. allows standard users to see and interact with it.
Business field
For more flexibility, business field can be defined by the user.
Two business field types are available:
- "external parameters" of following types:
- Date
- Date and time
- Month
- Text string
- Time
- Whole number
- Year
- database parameters (dbObject::something or dbObjectMulti::something Value type) advanced options to access to the database field
Business field creation
- Go to Menu Admin > Reports > Custom reports > Business 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::Person) 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.
Business field creation examples
We create a new business field for the current year:
- Name: $year
- Label: Year
- Value type: Year
Parameter Year must be filled in Reports/View form before to call the query (by default current year is filled when you call the page)
We create a new business field to list the reservation of a resource:
- Name: $resourceId
- Label: Resource
- Value type: dbObject::Resource
Then in Admin/Reports we create a new query labeled "Aircraft booking" with the following query:
SELECT * FROM booking WHERE booking.resource_id=$resourceId
To use this report, we just have to select a "Resource" in the Reports/View form, to check "Resource 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 business 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 hours total on instruction with specific activity type/profile
Requirement:
- Variable $activityTypeId of dbOjectMulti::ActivityType value type
- Variable $profileId of dbOjectMulti::Profile value type
- Variable $year 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.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name FROM flight LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id WHERE YEAR(flight.start_date)=$year AND flight.airborne=0 AND ( activity_type.id IN ('$activityTypeId') OR '' IN ('$activityTypeId') ) ) 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 hours total on non-instruction with specific activity type/profile
Requirement:
- Variable $activityTypeId of dbOjectMulti::ActivityType value type
- Variable $profileId of dbOjectMulti::Profile value type
- Variable $year 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.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name FROM flight LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id WHERE YEAR(flight.start_date)=$year AND flight.airborne=0 AND ( activity_type.id IN ('$activityTypeId') OR '' IN ('$activityTypeId') ) 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 dbOjectMulti::ValidityType value type.
SELECT person.first_name, person.last_name, validity_type.name AS 'Validite' FROM validity LEFT JOIN validity_type ON validity.validity_type_id=validity_type.id LEFT JOIN person ON person.id=validity.person_id WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($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, structure WHERE ( ( flight.departure_icao_id != structure.icao ) OR( flight.arrival_icao_id != structure.icao ) ) AND( flight.departure_icao_id != structure.icao ) AND YEAR(start_date) = $year UNION SELECT SUM(landing_number) * 2 AS movement FROM flight, structure WHERE YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao ) AS tmp_movement
Number of movements on based airfield on specific profile
- Variable $profileId should be defined first and should be of dbOjectMulti::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 structure, flight LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) 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 AND flight_pilot.num=0) WHERE ( ( flight.departure_icao_id != structure.icao) OR ( flight.arrival_icao_id != structure.icao) ) AND ( flight.departure_icao_id != structure.icao) AND YEAR(flight.start_date) = 2019 AND personWithProfile.id IS NOT NULL UNION SELECT SUM(flight.landing_number)*2 AS movement FROM structure, flight LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) 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 AND flight_pilot.num=0) WHERE YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao AND personWithProfile.id IS NOT NULL ) AS tmp_movement
Number of take-off and landings on based airfield
SELECT SUM(flight.landing_number) * 2 AS nb_mouvement FROM flight, structure WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.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 dbOjectMulti::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT SUM(flight.landing_number) * 2 AS 'Movement' FROM structure, flight LEFT JOIN flight_pilot ON ( flight_pilot.flight_id = flight.id ) 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 AND flight_pilot.num = 0 ) WHERE personWithProfile.id IS NOT NULL AND YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao
List of movements on based airfield
SELECT location.icao_name AS ICAO, location.name AS NAME, COUNT(location.icao_name) AS nb_flight FROM structure, flight LEFT JOIN location ON flight.departure_icao_id = location.icao_name WHERE ( flight.departure_icao_id = structure.icao ) OR( flight.arrival_icao_id = structure.icao ) AND departure_icao_id != arrival_icao_id AND YEAR(start_date) = $year GROUP BY icao_name ORDER BY nb_flight DESC
User flying without validity
SELECT CONCAT(person.last_name, ' ', person.first_name) AS _tr(PILOT), validity_type.name AS _tr(VALIDITY) FROM flight_type_mandatory_validity_type LEFT JOIN activity_type ON activity_type.id = flight_type_mandatory_validity_type.activity_type_id LEFT JOIN flight ON flight.activity_type_id & activity_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 AND validity.is_current_validity = 1 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 AND validity_type.activated = 1 GROUP BY person.id, validity_type.id ORDER BY `_tr(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()