Difference between revisions of "Export generator 4"
(→Flight log book) |
(→Fuel/Oil consumption) |
||
Line 1,489: | Line 1,489: | ||
Unit is: unit of the tank per hour | Unit is: unit of the tank per hour | ||
<SQL>SELECT | <SQL>SELECT | ||
− | resource_name AS | + | resource_name AS '_tr(REPORT_RESOURCE)', |
CASE tank_type | CASE tank_type | ||
WHEN 1 THEN "AVGAS" | WHEN 1 THEN "AVGAS" | ||
Line 1,497: | Line 1,497: | ||
WHEN 5 THEN "SP98" | WHEN 5 THEN "SP98" | ||
WHEN 6 THEN "GAZOLE" | WHEN 6 THEN "GAZOLE" | ||
− | END AS | + | END AS '_tr(TANK)', |
− | formatDecimal( CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_JAN)', |
− | formatDecimal( CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_FEB)', |
− | formatDecimal( CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_MAR)', |
− | formatDecimal( CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_APR)', |
− | formatDecimal( CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_MAY)', |
− | formatDecimal( CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_JUN)', |
− | formatDecimal( CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_JUL)', |
− | formatDecimal( CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_AUG)', |
− | formatDecimal( CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_SEP)', |
− | formatDecimal( CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_OCT)', |
− | formatDecimal( CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_NOV)', |
− | formatDecimal( CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS | + | formatDecimal( CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS '_tr(MONTH_DEC)', |
− | formatDecimal( CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = | + | 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 '_tr(ANNUAL_AVERAGE)' |
FROM | FROM | ||
(SELECT resource.name AS resource_name, | (SELECT resource.name AS resource_name, | ||
Line 1,517: | Line 1,517: | ||
flight_tank_qty.quantity AS quantity, | flight_tank_qty.quantity AS quantity, | ||
MONTH(flight.start_date) AS month_num, | MONTH(flight.start_date) AS month_num, | ||
− | (SELECT | + | (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 | FROM tank | ||
LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id | LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id |
Revision as of 18:27, 5 February 2018
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 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 X months
- 3.14 Number of men, women over and under X years for profile P, validity V up to date for year A
- 3.15 Number of landings per pilot, per resource
- 3.16 Flight hours without up to date validities
- 3.17 Number of men, women over and under 21 years for profile X, validity Y up to date for year Z
- 3.18 Validities in the year : Total of youngs/adults with specific profile
- 3.19 Validities obtained in the year
- 4 Accounting
- 5 Booking
- 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 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 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 Detailed flight between two dates
- 7.4 Detailed flight per month
- 7.5 Flight between two dates
- 7.6 Flight log book
- 7.7 Flights which have landed elsewhere than the base field
- 7.8 Flights with a remark in one or two additional entries
- 7.9 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.
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 LEFT JOIN location ON (flight.departure_location_id = location.id) WHERE ( ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao) ) OR ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao) ) ) AND ( location.id != (SELECT ap2.id FROM location ap2, structure 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 structure ON location.icao_name=structure.icao WHERE structure.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 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 flight LEFT JOIN location ON (flight.departure_location_id = location.id) 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_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(flight.start_date) = $year AND personWithProfile.id IS NOT NULL 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 ( 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 structure_icao.id IS NOT NULL AND personWithProfile.id IS NOT NULL ) AS tmp_movement
Visited airfields
SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS Visit FROM flight LEFT JOIN location ON (flight.departure_location_id = location.id) WHERE ( ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao) ) OR ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao) ) ) AND YEAR( start_date ) = $year GROUP BY icao_name ORDER BY Visit DESC
Number of take-off and landings on based airfield
SELECT SUM( landing_number )*2 AS nb_mouvement FROM flight f, structure c WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao)) AND (f.arrival_location_id = (SELECT ap2.id FROM location ap2, structure 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 dbOjectMulti::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 structure ON location.icao_name=structure.icao WHERE structure.id IS NOT NULL LIMIT 1 ) AS structure_icao ON (flight.departure_location_id=structure_ica