Difference between revisions of "Export generator 4"
(→Number of take-off and landings on based airfield) |
(→Flights hours total per aircraft per year and per month (for an activity type)) |
||
(3 intermediate revisions by 2 users not shown) | |||
Line 177: | Line 177: | ||
AND expire_date IS NULL | AND expire_date IS NULL | ||
AND person.activated=1</sql> | AND person.activated=1</sql> | ||
+ | |||
+ | ==Number of landings at the base airfield== | ||
+ | <sql>SELECT only_base.sum AS _tr(FLIGHT_LANDING_NUMBER_WITH_TAKEOFF_AND_LANDING_AT_THE_BASE), all_landing.sum AS _tr(REPORT_NUMBER_LANDING_BASED_AIRFIELD) FROM | ||
+ | (SELECT SUM(flight.landing_number) AS sum | ||
+ | FROM flight, structure | ||
+ | WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year) AS only_base, | ||
+ | |||
+ | (SELECT SUM(flight.landing_number) AS sum | ||
+ | FROM flight, structure | ||
+ | WHERE ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year) AS all_landing;</sql> | ||
==Number of movements on based airfield== | ==Number of movements on based airfield== | ||
− | |||
* Variable '''$year''' should be defined first and should be of '''Year''' value type. | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
Line 236: | Line 245: | ||
AND personWithProfile.id IS NOT NULL | AND personWithProfile.id IS NOT NULL | ||
) AS tmp_movement</sql> | ) AS tmp_movement</sql> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
==Number of take-off and landings on based airfield on specific profile== | ==Number of take-off and landings on based airfield on specific profile== | ||
Line 788: | Line 786: | ||
* Variable '''$year''' should be defined first and should be of '''Year''' value type. | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
− | <sql>SELECT aircraft_id AS | + | <sql>SELECT aircraft_id AS _tr(IDENT), resource.name AS _tr(RESOURCE_NAME), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS _tr(MONTH_JAN_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS _tr(MONTH_FEB_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS _tr(MONTH_MAR_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS _tr(MONTH_APR_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS _tr(MONTH_MAY_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS _tr(MONTH_JUN_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS _tr(MONTH_JUL_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS _tr(MONTH_AUG_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS _tr(MONTH_SEP_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS _tr(MONTH_OCT_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS _tr(MONTH_NOV_ABBR), |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS _tr(MONTH_DEC_ABBR), |
− | sexa2HoursMinute( SUM( duration ) ) AS SUM | + | sexa2HoursMinute( SUM( duration ) ) AS _tr(SUM) |
FROM flight | FROM flight | ||
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id | LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id | ||
Line 807: | Line 805: | ||
WHERE YEAR(start_date)= $year AND airborne = 0 | WHERE YEAR(start_date)= $year AND airborne = 0 | ||
AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId) | AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId) | ||
− | GROUP BY resource.id | + | GROUP BY resource.id</sql> |
− | + | ||
− | + | ==Flights hours total per aircraft, per year and per month (for a given type of activity and profile)== | |
− | + | ||
− | + | * Variable '''$activityTypeId''' should be defined first and should be of '''dbOject::ActivityType''' value type. | |
− | + | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | |
− | + | * Variable '''$occupiedSeat''' represent the seat number of the person in the flight, can have following values (0: left place, 1: right place, '': both) | |
− | + | * Variable '''$profileId''' of '''dbOjectMulti::Profile''' value type | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)= | + | <sql>SELECT aircraft_id AS _tr(IDENT), resourceName AS _tr(RESOURCE_NAME), profileName as _tr(PROFILE), |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS _tr(MONTH_JAN_ABBR), | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)= | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS _tr(MONTH_FEB_ABBR), |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS _tr(MONTH_MAR_ABBR), | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)= | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS _tr(MONTH_APR_ABBR), |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS _tr(MONTH_MAY_ABBR), | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date)= | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS _tr(MONTH_JUN_ABBR), |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS _tr(MONTH_JUL_ABBR), | |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS _tr(MONTH_AUG_ABBR), | |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS _tr(MONTH_SEP_ABBR), | |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS _tr(MONTH_OCT_ABBR), | |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS _tr(MONTH_NOV_ABBR), | |
− | + | sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS _tr(MONTH_DEC_ABBR), | |
− | + | sexa2HoursMinute( SUM( duration ) ) AS _tr(SUM) | |
− | + | FROM | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date) | + | ( |
− | + | SELECT | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date) | + | flight.aircraft_id, resource.name AS resourceName, flight.duration, flight.start_date, profile.name AS profileName |
− | + | FROM | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date) | + | flight |
− | + | LEFT JOIN flight_pilot ON flight_pilot.flight_id=flight.id | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date) | + | LEFT JOIN person ON person.id=flight_pilot.pilot_id |
− | + | LEFT JOIN profile ON person.profile&profile.id | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date) | + | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date) | + | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date) | + | |
− | sexa2HoursMinute( SUM( IF( MONTH(start_date) | + | |
− | sexa2HoursMinute( SUM( duration ) ) AS | + | |
− | FROM flight | + | |
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 AND airborne = 0 | WHERE YEAR(start_date)= $year AND airborne = 0 | ||
− | + | AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId) | |
+ | AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) | ||
+ | AND (IF((('$occupiedSeat'=0) OR ('$occupiedSeat'='')), 0, -1) = flight_pilot.num | ||
+ | OR IF((('$occupiedSeat'=1) OR ('$occupiedSeat'='')), 1, -1) = flight_pilot.num) | ||
+ | AND person.activated=1 | ||
+ | GROUP BY resourceName, profileName, flight.id | ||
+ | ) AS flightTmp | ||
+ | GROUP BY resourceName, profileName</sql> | ||
==Flight hours total per pilot== | ==Flight hours total per pilot== |
Revision as of 11:50, 12 April 2023
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 landings at the base airfield
- 3.5 Number of movements on based airfield
- 3.6 Number of movements on based airfield on specific profile
- 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 Flights hours total per aircraft, per year and per month (for a given type of activity and profile)
- 6.7 Flight hours total per pilot
- 6.8 Flight hours total per pilot per profile
- 6.9 Total Flight hours per pilot with total
- 6.10 Flight hours per instructor, month (for a flight type)
- 6.11 Flights hours following nationality
- 6.12 Flights hours total per flight type per month
- 6.13 Flights hours : less than 21 years, more than 21 years, male, female, for a given profile
- 6.14 Computation of flight time per month of "local flight"
- 6.15 Computation of non-"local flight" time per month
- 6.16 Stats all flight hours per month per year
- 6.17 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
Return a user list telling if each user has a profile A, B, C, etc.
SELECT person.first_name, person.last_name, IF(person.profile & 1, 'Yes', 'No') AS 'Profile A', IF(person.profile & 2, 'Yes', 'No') AS 'Profile B', IF(person.profile & 4, 'Yes', 'No') AS 'Profile C' FROM person WHERE activated=1;
Return the whole content of a given table
Example with table "profile"
SELECT * FROM profile;
This type of SELECT does not work within OpenFlyers to access restricted table like the person table
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 landings at the base airfield
SELECT only_base.sum AS _tr(FLIGHT_LANDING_NUMBER_WITH_TAKEOFF_AND_LANDING_AT_THE_BASE), all_landing.sum AS _tr(REPORT_NUMBER_LANDING_BASED_AIRFIELD) FROM (SELECT SUM(flight.landing_number) AS sum FROM flight, structure WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year) AS only_base, (SELECT SUM(flight.landing_number) AS sum FROM flight, structure WHERE