Revision as of 15:05, 6 December 2013

1 Introduction
- 1.1 Extra field
  - 1.1.1 Extra Field creation
  - 1.1.2 Extra Field creation examples
2 SQL tips and tricks
- 2.1 Return only last entry
- 2.2 Test valid entries
3 French administration Examples
4 Accounting
5 Booking
- 5.1 List of booking for maintenance ordered by resource and date with name of the responsible
6 Flight time management
7 Error message queries
- 7.1 Flight without account movement
- 7.2 movement without an account
8 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.

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::Aircaft

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

Number of members : male, female, less than 21 years and more than 21 years

SELECT
    IF ( (sex = 0), 'Men', 'Women' ) AS Sex, 
    IF( ( $year - YEAR( birthdate ) >= 21), 'No', 'Yes') AS Young, 
    COUNT( id ) AS Number  
  FROM person
  WHERE activated='1' 
  GROUP BY 
    Sex,
    Young

Licence in the year

Note:

21 limit of age to change according yours limit
Replace X and Y in the following query by the right validity names.

SELECT 
    last_name AS Last_name, 
    first_name AS First_name, 
    IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
    IF ( (sex = 0), 'Male', 'Female' ) 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.name = 'X' OR validity_type.name = 'Y')

List of pilots with specific validity

SELECT 
    last_name AS Last_name,
    first_name AS First_name, 
    validity_type.name AS 'Validity', 
   DATE_FORMAT(validity.expire_date, '%m-%d-%Y')  AS Expirate 
  FROM person
    LEFT JOIN validity ON person_id=person.id 
    LEFT JOIN validity_type ON validity_type.id=validity_type_id
  WHERE grant_date IS NULL 
    AND validity_type.time_limitation=1 
    AND validity_type.name="Private Pilot License" 
    AND person.activated=1 
  ORDER BY 
    last_name,
    first_name

Note: replace name of validity_type.name by yours

Number of visit on other airfield

SELECT ap.icao_name AS ICAO, 
    ap.name AS Name, 
    COUNT( ap.icao_name ) AS nb_visite 
  FROM flight f
  LEFT JOIN location AS ap ON f.departure_location_id = ap.id
  WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) OR (f.arrival_location_id  = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) 
     AND departure_location_id != arrival_location_id 
     AND YEAR( start_date ) = $year 
  GROUP BY icao_name 
  ORDER BY nb_visite DESC

Number of movements on based platform due to country flight

SELECT ap.icao_name AS ICAO, 
    ap.name AS Name, 
    COUNT( ap.icao_name ) AS nb_flight
  FROM flight f
  LEFT JOIN location AS ap ON f.departure_location_id = ap.id
  WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) OR (f.arrival_location_id  = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) 
     AND departure_location_id != arrival_location_id 
    AND YEAR( start_date ) = $year
   GROUP BY icao_name 
  ORDER BY nb_flight 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

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

Validity per user

SELECT last_name as "Last_name", first_name as "First_name", 
      validity_type.name AS 'Validity',
      DATE_FORMAT(expire_date,'%d/%m/%Y') AS Expiry_date,
      if((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired", "Valid") AS Expired
 
   FROM validity_type
   LEFT JOIN validity ON validity.validity_type_id =validity_type.id 
   LEFT JOIN person ON person.id=validity.person_id
   LEFT JOIN account ON person.id=account.owner_id
-- Validity recently expired or about to expire
WHERE
-- Deactivated members
account.activated = 1 AND
-- Only active members for current year(N) and following (N+1)
YEAR(grant_date)>=YEAR(NOW())
-- Exclude Night qualification 
AND validity_type.id != 6
AND person.id = $person 
ORDER BY 'Validity'

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)*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

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((SUM( f1.duration )/600 - FLOOR(SUM( f1.duration )/600))*3600),'%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 ASC 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 90 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

@@ Line 864: / Line 864: @@
    FROM flight_pilot fp
    LEFT JOIN flight ON fp.flight_id=flight.id
-   LEFT JOIN person a ON a.id=fp.pilot_id
+   LEFT JOIN person ON person.id=fp.pilot_id
    WHERE YEAR( start_date ) = $year AND fp.num=0
    GROUP BY Nom, Solo_DC</SQL>

Difference between revisions of "Export generator 3"