Revision as of 12:20, 20 January 2017

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.

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 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
        flight_type.id,
        flight_type.name,
        flight.duration,
        resource_type.category
    FROM flight
    LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1)
    LEFT JOIN person ON (person.id=flight_pilot.pilot_id)
    LEFT JOIN resource ON (resource.id=flight.aircraft_id)
    LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
    WHERE YEAR(flight.start_date)=$year
      AND (flight_type.id='$flightTypeId' OR '-'='$flightTypeId')
      AND person.activated=1
      AND (person.profile & '$profileId' OR '-'='$profileId')
) AS tmp_flight
GROUP BY 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 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
        flight_type.id,
        flight_type.name,
        flight.duration,
        resource_type.category
    FROM flight
    LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=0)
    LEFT JOIN person ON (person.id=flight_pilot.pilot_id)
    LEFT JOIN resource ON (resource.id=flight.aircraft_id)
    LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
    WHERE YEAR(flight.start_date)=$year
      AND (flight_type.id='$flightTypeId' OR '-'='$flightTypeId')
      AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1)
      AND person.activated=1
      AND (person.profile & '$profileId' OR '-'='$profileId')
) AS tmp_flight
GROUP BY id

List of 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

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 Visit
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 Visit 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

List of pilots who have flown without required validity

SELECT CONCAT(a.last_name, ' ', a.first_name) AS pilot, vt.name AS 'Validity'
FROM flight_type_mandatory_validity_type AS ftpvt
LEFT JOIN flight_type AS ft ON ft.id = ftpvt.flight_type_id
LEFT JOIN flight AS f ON f.flight_type_id & ft.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 aircraft_type_validity_type AS atvt ON at.id = atvt.aircraft_type_id
LEFT JOIN flight_pilot AS fp ON f.id = fp.flight_id
LEFT JOIN person AS a ON a.id = fp.pilot_id
LEFT JOIN validity_type AS vt ON (vt.id = ftpvt.validity_type_id OR vt.id = atvt.validity_type_id)
WHERE f.airborne = 0
  AND ROW(a.id, vt.id) NOT IN (SELECT person_id, validity_type_id FROM validity)
  AND vt.experience_formula IS NULL
GROUP BY a.id, vt.id
ORDER BY pilot, vt.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