Latest revision as of 11:00, 9 February 2018

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 reports 3
5 Booking
6 Flight time reports
7 Flight reports
8 Fuel/Oil reports
9 Error message queries
- 9.1 Flight without account movement
- 9.2 movement without an account
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 and the Database description 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 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.*, flight_type.id AS activity_type_id, flight_type.name AS activity_type_name
        FROM flight
        LEFT JOIN flight_type ON flight.flight_type_id&flight_type.id
        WHERE YEAR(flight.start_date)=$year
          AND flight.airborne=0
          AND ( flight_type.id IN ('$flightTypeId') OR '-' IN ('$flightTypeId') )
    ) 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 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 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.*, flight_type.id AS activity_type_id, flight_type.name AS activity_type_name
        FROM flight
        LEFT JOIN flight_type ON flight.flight_type_id&flight_type.id
        WHERE YEAR(flight.start_date)=$year
          AND flight.airborne=0
          AND ( flight_type.id IN ('$flightTypeId') OR '-' IN ('$flightTypeId') )
          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 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 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, 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 nb_visite
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 nb_visite 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

Pilots who have flown without required validity

SELECT CONCAT(person.last_name, ' ', person.first_name) AS pilot, validity_type.name AS 'Validity'
FROM flight_type_mandatory_validity_type
LEFT JOIN flight_type ON flight_type.id = flight_type_mandatory_validity_type.flight_type_id
LEFT JOIN flight ON flight.flight_type_id & flight_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)
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
GROUP BY person.id, validity_type.id
ORDER BY 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() - 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

Variable $month should be defined first and should be of integer value type.

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(f1.duration*6 ) ,'%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 DESC 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 IF($month='', 1, $month*30) 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
WHERE flight_pilot.pilot_id = a.id 
AND (flight.aircraft_id = 2 OR flight.aircraft_id = 3 OR flight.aircraft_id = 4 OR flight.aircraft_id = 18)
AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS DR400,
 
(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 = 23
AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS DR500,
 
(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 = 8
AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS 'MC-IP',
 
(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 = 21
AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS 'CAP10-DL'
 
FROM person a
 
LEFT JOIN profile AS p1 ON (a.profile & p1.id)
LEFT JOIN validity valid ON valid.person_id = a.id
LEFT JOIN validity_type valid_typ ON valid_typ.id = valid.validity_type_id
 
WHERE YEAR(grant_date) >= YEAR(NOW())
AND a.activated=1 AND p1.name='Pilot' 
 
GROUP BY last_name, first_name
ORDER BY last_name

Flight hours without up to date validities

SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS '_tr(DATE)', last_name AS '_tr(LAST_NAME)', first_name AS '_tr(FIRST_NAME)', 
       IF ( (SELECT COUNT(*) FROM flight_pilot AS tmp_flight_pilot WHERE flight_pilot.flight_id=tmp_flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'Solo', 'DC' ) AS DC,
       resource.name AS '_tr(RESOURCE)',
       sexa2HoursMinute(duration) AS '_tr(DURATION)',
       flight_type.name AS '_tr(flight_type)',
       validity_type.name AS '_tr(VALIDITY)'
FROM flight
LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.flight_type_id & flight.flight_type_id 
LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id
LEFT JOIN person ON flight_pilot.pilot_id = person.id
LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id
LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id
LEFT JOIN resource ON flight.aircraft_id = resource.id
LEFT JOIN flight_type ON flight_type_mandatory_validity_type.flight_type_id = flight_type.id
WHERE YEAR(start_date) = $year
  AND flight_pilot.num = 0
  AND validity_type.time_limitation=1
  AND validity.person_id = flight_pilot.pilot_id
  AND flight.start_date > validity.expire_date
  AND person.activated=1
ORDER BY flight.start_date, person.last_name, validity_type.name

Number of men, women over and under X years for profile P, validity V up to date for year A

Variable $age should be of integer value type.
Variable $profileId should be of dbOject::Profile value type.
Variable $validityTypeId should be of dbObject::validityType value type.
Variable $year should be of Year value type.

SELECT
    IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex',
    IF( ( $year - YEAR(birthdate) >= '$age' ) , 'Yes', 'No') AS 'Adult',
    COUNT(DISTINCT person.id) AS NUMBER  
FROM person
LEFT JOIN validity ON (person.id=validity.person_id)
LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
WHERE activated=1
  AND (profile & '$profileId' OR '-'='$profileId')
  AND (
      (validity_type.id='$validityTypeId' AND validity.expire_date >= '$year-12-31')
      OR
      '-'='$validityTypeId'
  )
GROUP BY Sex, Adult

Validities in the year : Total of youngs/adults with 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.
Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need.

SELECT 
    validity_type.name AS 'Validity',
    SUM( IF( ( $year - YEAR(person.birthdate) < 21 ), 1, 0 ) ) AS 'Young',
    SUM( IF( ( $year - YEAR(person.birthdate) >= 21 ), 1, 0 ) ) AS 'Adult'
FROM validity_type
LEFT JOIN validity ON (validity.validity_type_id=validity_type.id)
LEFT JOIN person ON (person.id=validity.person_id)
WHERE YEAR(validity.grant_date)=$year
  AND person.activated=1
  AND (person.profile & '$profileId' OR '-'='$profileId')
GROUP BY validity_type.id
ORDER BY validity_type.name

Validities obtained in the year

Variable $validityId should be defined first and should be of dbOject::ValidityType value type.
21 value is the age limit to be young. Should be changed according local rules

SELECT 
    last_name AS Last_name, 
    first_name AS First_name, 
    IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
    IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) 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.id IN ('$validityTypeId') OR '-' IN ('$validityTypeId') )
  AND person.activated=1
ORDER BY last_name, first_name

Accounting reports 3

Booking

Bookings

Variable $endDate of Date and time value type.
Variable $startDate of Date and time value type.
Variable $personId of dbObject::Person value type.
Variable $resourceId of dbObject::Resource value type.

SELECT booking.start_date AS 'Date de début', booking.end_date AS 'Date de fin', resource.name AS 'Ressource',
       CONCAT(left_person.last_name, ' ', left_person.first_name) AS 'Place gauche',
       CONCAT(right_person.last_name, ' ', right_person.first_name) AS 'Place droite'
FROM booking
LEFT JOIN resource ON (resource.id=booking.resource_id)
LEFT JOIN person AS left_person ON (left_person.id=booking.person_id)
LEFT JOIN person AS right_person ON (right_person.id=booking.instructor_id)
WHERE booking.start_date >= '$startDate' AND booking.end_date < '$endDate'
  AND (booking.resource_id='$resourceId' OR '-'='$resourceId')
  AND (
      (left_person.id='$personId' AND left_person.activated=1)
      OR
      (right_person.id='$personId' AND right_person.activated=1)
      OR
      '-'='$personId'
  )
ORDER BY booking.start_date, booking.end_date

Cumulated maintenance hours between a period

Following extrafields are needed :

startDate (Type : Date)
endDate (Type : Date or DateTime)

SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours'
UNION ALL
SELECT resource.name AS 'Ressource', SUM(TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date)), SUM(TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date))
FROM booking
LEFT JOIN log ON log.field_value=booking.id
LEFT JOIN journal ON journal.id=log.journal_id
LEFT JOIN resource ON resource.id = booking.resource_id
WHERE (booking.slot_type=2)
  AND (log.action='INSERT')
  AND (log.table_name='booking')
  AND (log.field_name='id')
  AND (booking.start_date >= '$startDate')
  AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate'))
GROUP BY resource.id
ORDER BY 1

Maintenance hours per resource on a period

Following extrafields are needed :

startDate (Type : Date)
endDate (Type : Date or DateTime)

SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Date debut maintenance', '' AS 'Date fin maintenance', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours'
UNION ALL
SELECT resource.name, booking.start_date AS 'Date debut maintenance', booking.end_date, TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date),  TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date)
FROM booking
LEFT JOIN log ON log.field_value=booking.id
LEFT JOIN journal ON journal.id=log.journal_id
LEFT JOIN resource ON resource.id = booking.resource_id
WHERE (booking.slot_type=2)
  AND (log.action='INSERT')
  AND (log.table_name='booking')
  AND (log.field_name='id')
  AND (booking.start_date >= '$startDate')
  AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate'))
ORDER BY 1, 2

Booking for maintenance ordered by resource and date with name of the responsible

SELECT resource.name AS 'Resource name', booking.start_date AS 'Start date', TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date) AS 'booking duration', person.first_name AS 'First name', person.last_name AS 'Last name'
FROM booking
LEFT JOIN log ON log.field_value=booking.id
LEFT JOIN journal ON journal.id=log.journal_id
LEFT JOIN person ON person.name=journal.login
LEFT JOIN resource ON resource.id = booking.resource_id
WHERE (booking.slot_type=2) AND (log.action='INSERT') AND (log.table_name='booking') AND (log.field_name='id')
AND YEAR(booking.start_date) = $year
AND person.activated=1
ORDER BY resource.name, booking.start_date

Flight time reports

Flight hours per month

SELECT MONTH( start_date) AS months, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
FROM flight 
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id 
WHERE YEAR(start_date) = $year 
GROUP BY months

Flight hours total and last recorded counter to the date X

Variable $endDate should be of Datetime value type.

SELECT resource.name, sexa2HoursMinute( SUM(duration) + aircraft.ref_hours ) AS 'Total heures', sexa2HoursMinute( MAX(counter_arrival) ) AS 'Dernier compteur'
FROM flight
LEFT JOIN resource ON (resource.id=flight.aircraft_id)
LEFT JOIN aircraft ON (aircraft.id=resource.id)
WHERE flight.start_date <= '$endDate'
  AND resource.activated=1
GROUP BY resource.id
ORDER BY resource.name

Flight hours total over a 12 months period

SELECT 
    CONCAT(last_name,' ',first_name) AS 'Person', 
    IF (( SELECT 
            COUNT(*) 
          FROM flight_pilot fp2 
          WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0,
      'Solo',
      'double') AS Solo_DC, 
    CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total 
  FROM flight_pilot fp 
  LEFT JOIN flight ON fp.flight_id=flight.id 
  LEFT JOIN person a ON a.id=fp.pilot_id 
  WHERE start_date > DATE_SUB(NOW(), INTERVAL 1 YEAR)  AND fp.num=0 
  GROUP BY Person, Solo_DC

Flights hours total per aircraft per year

SELECT 
    name AS Callsign, 
    YEAR( start_date ) AS Year, 
    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
  FROM flight 
    LEFT JOIN resource ON resource.id = flight.aircraft_id 
  WHERE YEAR(start_date) = $year AND airborne = 0 
  GROUP BY name 
  UNION 
  SELECT 
    "Total", 
    $year AS year, 
    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total      
  FROM flight 
  WHERE YEAR(start_date) = $year AND airborne = 0 
  GROUP BY year

Flights hours total per aircraft per year and per month (for a flight type)

Variable $flightTypeId should be defined first and should be of dbOject::FlightType value type.
Variable $year should be defined first and should be of Year value type.

SELECT aircraft_id AS Num, resource.name, 
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS Janu,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS Febr,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS Marc,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS Apri,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS May,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS June,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS July,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS Augu,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS Sept,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS Octo,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS Nove,
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS Dece,
    sexa2HoursMinute( SUM( duration ) ) AS SUM 
FROM flight
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
LEFT JOIN resource ON resource.id = aircraft.id
WHERE YEAR(start_date)= $year AND airborne = 0
  AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')
GROUP BY resource.id
UNION 
SELECT  "Sum per","month", 
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( duration ) ) AS Total
FROM flight
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
LEFT JOIN resource ON resource.id = aircraft.id
WHERE YEAR(start_date)= $year AND airborne = 0 
  AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')
UNION 
SELECT  "Cumulative","per month", 
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=1, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=2, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=3, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=4, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=5, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=6, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=7, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=8, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=9, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=10, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=11, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=12, duration, 0 ) ) ),
    sexa2HoursMinute( SUM( duration ) ) AS Total
FROM flight
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
LEFT JOIN resource ON resource.id = aircraft.id
WHERE YEAR(start_date)= $year AND airborne = 0
  AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')

Flight hours total per pilot

SELECT
    CONCAT(last_name,' ',first_name) AS Nom,
    sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) ) AS 'Solo',
    sexa2HoursMinute( SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS 'Double',
    sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) + SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS 'Total'
FROM person
LEFT JOIN (
    SELECT 
        pilot_id,
        IF (
            (SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0,
            'Solo',
            'DC'
        ) AS Solo_DC,
        SUM(flight.duration) AS sum_duration
    FROM flight_pilot fp 
    LEFT JOIN flight ON fp.flight_id=flight.id
    WHERE flight.airborne=0 AND YEAR(start_date) = $year AND fp.num=0 
    GROUP BY pilot_id, Solo_DC
) AS Solo ON person.id=Solo.pilot_id AND Solo.Solo_DC='Solo'
LEFT JOIN (
    SELECT 
        pilot_id,
        IF (
            (SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0,
            'Solo',
            'DC'
        ) AS Solo_DC,
        SUM(flight.duration) AS sum_duration
    FROM flight_pilot fp 
    LEFT JOIN flight ON fp.flight_id=flight.id
    WHERE flight.airborne=0 AND YEAR(start_date) = $year AND fp.num=0 
    GROUP BY pilot_id, Solo_DC
) AS DC ON person.id=DC.pilot_id AND DC.Solo_DC='DC'
WHERE (Solo.pilot_id IS NOT NULL OR DC.pilot_id IS NOT NULL)
  AND person.activated=1
GROUP BY person.id
ORDER BY Nom

Flight hours total per pilot per profile

SELECT
    profile.name AS Profil,
    CONCAT(last_name,' ',first_name) AS Nom,
    IF (( SELECT
            COUNT(*)
          FROM flight_pilot fp2
          WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0,
        'Solo',
        'double') AS Solo_DC,
    sexa2HoursMinute( SUM( flight.duration ) ) AS Total
  FROM flight_pilot fp
  LEFT JOIN flight ON fp.flight_id=flight.id
  LEFT JOIN person ON person.id=fp.pilot_id
  LEFT JOIN profile ON (person.profile & profile.id)
  WHERE flight.airborne=0 AND YEAR( start_date ) = $year AND fp.num=0
  GROUP BY Profil, Nom, Solo_DC

Total Flight hours per pilot with total duration and total amount

Following extrafields are needed

year (Type : Year)
month (Type : Integer) OR startDate and endDate (Type : Date)
$occupiedSeat (Type : Integer)
profileId (Type : dbObject::Profile)

This report is useful for a group of pilots from a common customer (like DGAC in France)

SELECT CONCAT(person.last_name,' ',person.first_name) AS Nom_et_prenom_pilotes,
CONCAT(FLOOR(SUM( flight_list.duree )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight_list.duree )/600 - FLOOR(SUM( flight_list.duree )/600))*3600),'%i')) AS nombre_hdv_effectuees,
SUM(flight_list.montant) AS montant_total_des_vols 
FROM person
JOIN
(SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
LEFT JOIN flight ON flight.id=flight_pilot.flight_id
LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
LEFT JOIN account ON account.id=account_entry.account_id
WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
    AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
    OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
        AND person.profile&$profileId AND (account.category = 11 OR account.category = 2)
GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
GROUP BY person.id

Flight hours per instructor, month (for a flight type)

Following extrafields are needed :

year (Type : Year)
flightTypeId (Type : dbObject::FlightType)

SELECT 
    UPPER(last_name) AS Nom,
    first_name AS Prénom,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Janv,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Fevr,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mars,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Avri,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mai,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juin,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juil,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Aout,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Sept,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Oct,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Nov,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Dece,
    sexa2HoursMinute( SUM( duration ) ) AS Total
FROM flight 
LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id 
LEFT JOIN person a ON a.id=fp.pilot_id 
WHERE flight.airborne=0
  AND a.activated=1
  AND fp.num = 1 
  AND YEAR( start_date ) = $year  
  AND ( flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId' )
GROUP BY a.id
 
UNION
 
SELECT  "Sum per","month",
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Janv,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Fevr,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mars,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Avri,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mai,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juin,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juil,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Aout,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Sept,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Oct,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Nov,
    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Dece,
    sexa2HoursMinute( SUM( duration ) ) AS Total
FROM flight 
LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
LEFT JOIN person a ON a.id=fp.pilot_id 
WHERE flight.airborne=0
  AND a.activated=1
  AND fp.num = 1
  AND YEAR( start_date ) = $year  
  AND ( flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId' )

Flights hours following nationality

SELECT  
     nationality.label, 
     CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 -   FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total 
   FROM flight , flight_pilot fp, person a, nationality
   WHERE flight.id = fp.flight_id
     AND fp.pilot_id = a.id
     AND a.nationality = nationality.code
   GROUP BY nationality.code

Flights hours total per flight type per month

SELECT 
    flight_type.name AS Type_vol,
    flight_pilot_DC.DC,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 6, duration, 0 ) ) ) AS June,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 7, duration, 0 ) ) ) AS July,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 8, duration, 0 ) ) ) AS Augu,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 9, duration, 0 ) ) ) AS Sept,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 10, duration, 0 ) ) ) AS Octo,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 11, duration, 0 ) ) ) AS Nove,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 12, duration, 0 ) ) ) AS Dece,
    sexa2HoursMinute( SUM( duration ) ) AS Total
FROM flight
LEFT JOIN flight_type ON flight_type.id & flight.flight_type_id
LEFT JOIN (
    SELECT flight_id, IF( MAX(num) = 1, 'Double', 'Solo' ) AS DC
    FROM flight_pilot
    WHERE flight_pilot.num <= 1
    GROUP BY flight_id
) AS flight_pilot_DC ON flight.id=flight_pilot_DC.flight_id
WHERE YEAR(start_date) = $year
  AND airborne = 0
GROUP BY flight_type.id, flight_pilot_DC.DC
HAVING (Name <> 'Instruction' OR flight_pilot_DC.DC <> 'Double')
ORDER BY order_num ASC, DC DESC

Flights hours : less than 21 years, more than 21 years, male, female, for a given 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 Sex, Young, Instruction,
       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
        IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex',
        IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
        IF ((SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'Instruction') AS Instruction,
        flight.duration,
        resource_type.category
    FROM flight_pilot fp
    LEFT JOIN flight ON fp.flight_id=flight.id
    LEFT JOIN person ON person.id=fp.pilot_id
    LEFT JOIN profile ON person.profile&profile.id
    LEFT JOIN resource ON (resource.id=flight.aircraft_id)
    LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
    WHERE YEAR( start_date )=$year
      AND fp.num=0
      AND flight.airborne = 0
      AND ( profile.id IN ($profileId) OR '-' IN ($profileId) )
      AND person.activated=1
    GROUP BY flight.id
) AS tmp_stat
GROUP BY Sex, Young, Instruction WITH ROLLUP

Computation of flight time per month of "local flight"

"Local flight" is defined as a flight less than 1h30 and with the same departure and destination

SELECT  aircraft_id AS Num, name AS Immat, 
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Janv,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Fevr,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Mars,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Avri,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Mai,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Juin,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Juil,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Aout,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Sept,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Octo,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Nove,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num ) AS Dece,
    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
FROM flight
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id 
LEFT JOIN resource ON resource.id = aircraft.id
WHERE YEAR(start_date)= $year AND departure_location_id = arrival_location_id AND duration < 90*600
GROUP BY resource.id

Computation of non-"local flight" time per month

SELECT  aircraft_id AS Num, name AS Immat, 
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Janv,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Fevr,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Mars,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Avri,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Mai,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Juin,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Juil,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Aout,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Sept,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Octo,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Nove,
    (SELECT 
        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) 
    FROM flight 
    WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Dece,
    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
FROM flight
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id 
LEFT JOIN resource ON resource.id = aircraft.id
WHERE YEAR(start_date)= $year AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)
GROUP BY resource.id

Stats all flight hours per month per year

SELECT YEAR(start_date) AS Year, 'Monthly' AS Type,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 6, duration, 0 ) ) ) AS June,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 7, duration, 0 ) ) ) AS July,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 8, duration, 0 ) ) ) AS Augu,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 9, duration, 0 ) ) ) AS Sept,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 10, duration, 0 ) ) ) AS Octo,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 11, duration, 0 ) ) ) AS Nove,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 12, duration, 0 ) ) ) AS Dece,
    sexa2HoursMinute( SUM( duration ) ) AS Total
FROM flight
LEFT JOIN resource ON resource.id = flight.aircraft_id
GROUP BY Year
UNION
SELECT YEAR(start_date) AS Year, 'Accumulation' AS Type,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 1, duration, 0 ) ) ) AS Janu,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 2, duration, 0 ) ) ) AS Febr,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 3, duration, 0 ) ) ) AS Marc,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 4, duration, 0 ) ) ) AS Apri,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 5, duration, 0 ) ) ) AS May,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 6, duration, 0 ) ) ) AS June,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 7, duration, 0 ) ) ) AS July,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 8, duration, 0 ) ) ) AS Augu,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 9, duration, 0 ) ) ) AS Sept,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 10, duration, 0 ) ) ) AS Octo,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 11, duration, 0 ) ) ) AS Nove,
    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 12, duration, 0 ) ) ) AS Dece,
    sexa2HoursMinute( SUM( duration ) ) AS Total
FROM flight
LEFT JOIN resource ON resource.id = flight.aircraft_id
GROUP BY Year
ORDER BY Type DESC, Year

Flight hours total per resource per flight type between two dates

Variable $startDate should be defined first and should be of Date and time value type.
Variable $endDate should be defined first and should be of Date and time value type.

[OF_DYNAMIC_SQL]
SELECT CONCAT(
    'SELECT resource.name AS Nom, ',
    GROUP_CONCAT(
        CONCAT(
            'sexa2HoursMinute( SUM( IF( tmp_flight.flight_type_id=',
            flight_type.id,
            ', tmp_flight.total_duration, 0 ) ) )',
            ' AS \'',
            flight_type.name,
            '\''
        )
    ),
    ', sexa2HoursMinute( SUM( tmp_flight.total_duration ) ) AS Total
    FROM resource
    LEFT JOIN
    (
        SELECT flight.aircraft_id, flight_type.id AS flight_type_id, SUM(duration) as total_duration
        FROM flight
        LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
        WHERE flight.start_date >= \'$startDate\' AND flight.start_date <= \'$endDate\' AND flight.airborne = 0
        GROUP BY flight.aircraft_id, flight_type.id
    ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
    WHERE tmp_flight.aircraft_id IS NOT NULL
    GROUP BY resource.id
    UNION
    SELECT \'Total\', ',
    GROUP_CONCAT(
        CONCAT(
            'sexa2HoursMinute( SUM( IF( tmp_flight.flight_type_id=',
            flight_type.id,
            ', tmp_flight.total_duration, 0 ) ) )',
            ' AS \'',
            flight_type.name,
            '\''
        )
    ),
    ', sexa2HoursMinute( SUM( tmp_flight.total_duration ) ) AS Total
    FROM resource
    LEFT JOIN
    (
        SELECT flight.aircraft_id, flight_type.id AS flight_type_id, SUM(duration) as total_duration
        FROM flight
        LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
        WHERE flight.start_date >= \'$startDate\' AND flight.start_date <= \'$endDate\' AND flight.airborne = 0
        GROUP BY flight.aircraft_id, flight_type.id
    ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
    WHERE tmp_flight.aircraft_id IS NOT NULL
    ORDER BY 1'
)
FROM flight_type
WHERE flight_type.activated=1

Flight reports

Aircraft(s) which no flight were done for at least 30 day(s)

SELECT resource.id, resource.name
FROM flight
LEFT JOIN resource ON resource.id = flight.aircraft_id
WHERE start_date < ( NOW() - INTERVAL 30 DAY )
  AND virtual=0 AND activated=1
GROUP BY resource.id

Detailed flight between two dates

Following extrafields are needed

startDate and endDate (Type : Date)
profileId (Type : dbObject::Profile)
occupiedSeat (Type : Integer)

This report is useful for a group of pilots from a common customer (like french administration DGAC).

SELECT 
    (
        SELECT CONCAT(last_name,' ',first_name)
        FROM person
        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
        WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0
        GROUP BY flight.id
    ) AS Left_seat_Pilot_name,
    (
        SELECT CONCAT(last_name,' ',first_name)
        FROM person
        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
        WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1
        GROUP BY flight.id
    ) AS Right_seat_Pilot_name,
    flight.start_date,
    resource.name AS resource,
    CONCAT(FLOOR(flight.duration/600),':',TIME_FORMAT(SEC_TO_TIME((flight.duration /600 - FLOOR(flight.duration/600))*3600),'%i')) AS flight_duration,
    (
        SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
        FROM flight AS f2
        LEFT JOIN flight_type ON (f2.flight_type_id & flight_type.id)
        WHERE f2.id=flight.id
    ) AS Flight_types,
    flight.landing_number,
    SUM(account_entry.debit)-SUM(account_entry.credit) AS amount,
    flight.comments
    FROM person
    LEFT JOIN profile ON person.profile&profile.id
    LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
    LEFT JOIN flight ON flight.id=flight_pilot.flight_id
    LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
    LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
    LEFT JOIN account ON account.id=account_entry.account_id
    LEFT JOIN resource ON resource.id = flight.aircraft_id
    WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
    AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
    OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
        AND ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND (account.category = 11 OR account.category = 2)
    GROUP BY flight.id

Detailed flight per month

Following extrafields are needed

year (Type : Year)
month (Type : Integer)
profileId (Type : dbObject::Profile)
occupiedSeat (Type : Integer)

This report is useful for a group of pilots from a common customer (like french administration DGAC).

SELECT 
    (
        SELECT CONCAT(last_name,' ',first_name)
        FROM person
        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
        WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0
        GROUP BY flight.id
    ) AS Left_seat_Pilot_name,
    (
        SELECT CONCAT(last_name,' ',first_name)
        FROM person
        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
        WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1
        GROUP BY flight.id
    ) AS Right_seat_Pilot_name,
    flight.start_date,
    resource.name AS resource,
    CONCAT(FLOOR(flight.duration/600),':',TIME_FORMAT(SEC_TO_TIME((flight.duration /600 - FLOOR(flight.duration/600))*3600),'%i')) AS flight_duration,
    (
        SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
        FROM flight AS f2
        LEFT JOIN flight_type ON (f2.flight_type_id & flight_type.id)
        WHERE f2.id=flight.id
    ) AS Flight_types,
    flight.landing_number,
    SUM(account_entry.debit)-SUM(account_entry.credit) AS amount,
    flight.comments
    FROM person
    LEFT JOIN profile ON person.profile&profile.id
    LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
    LEFT JOIN flight ON flight.id=flight_pilot.flight_id
    LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
    LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
    LEFT JOIN account ON account.id=account_entry.account_id
    LEFT JOIN resource ON resource.id = flight.aircraft_id
    WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)='$month' 
    AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
    OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
        AND ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND (account.category = 11 OR account.category = 2)
    GROUP BY flight.id

Flight between two dates

Following variables are needed:

startDate (Type : Datetime)
endDate (Type : Datetime)

SELECT flight.start_date AS '_tr(START_DATE)', resource.name AS '_tr(RESOURCE)',
CONCAT(UPPER(person.last_name),' ',person.first_name) AS '_tr(LEFT_PLACE)',
    ( 
        SELECT CONCAT(UPPER(tmp_person.last_name), ' ', tmp_person.first_name)
        FROM flight_pilot AS tmp_flight_pilot
        LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id
        WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 1
    ) AS '_tr(RIGHT_PLACE)',
    (
        SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
        FROM flight AS tmp_flight
        LEFT JOIN flight_type ON (tmp_flight.flight_type_id & flight_type.id)
        WHERE flight.id=tmp_flight.id
    ) AS '_tr(FLIGHT_TYPE)',
    (
        SELECT location.name
        FROM location
        WHERE location.id=flight.departure_location_id
    ) AS '_tr(DEPARTURE_FIELD)',
    (
        SELECT location.name
        FROM location
        WHERE location.id=flight.arrival_location_id
    ) AS '_tr(ARRIVAL_FIELD)',
    flight.landing_number AS '_tr(FLIGHT_LANDING_NUMBER)',
sexa2HoursMinute(flight.duration) AS '_tr(DURATION)'
FROM flight
LEFT JOIN resource ON resource.id = flight.aircraft_id
LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
LEFT JOIN person ON flight_pilot.pilot_id = person.id
WHERE flight.start_date >= '$startDate' AND flight.start_date <= '$endDate' AND flight_pilot.num = 0

Flight log book

SELECT 
    DATE_FORMAT(start_date, '%d/%m/%Y') AS '_tr(START_DATE)',
    resource.name AS '_tr(RESOURCE)',
    CONCAT(last_name, ' ', first_name) AS '_tr(LEFT_PLACE)',
    (
        SELECT CONCAT(person.last_name,' ',person.first_name) AS '_tr(RIGHT_PLACE)'
        FROM flight_pilot tmp2_flight_pilot 
        LEFT JOIN person ON person.id=tmp2_flight_pilot.pilot_id
        WHERE tmp2_flight_pilot.flight_id=flight_pilot.flight_id AND tmp2_flight_pilot.num=1
    ) AS '_tr(RIGHT_PLACE)',
    IF ( (SELECT COUNT(*) FROM flight_pilot tmp_flight_pilot WHERE tmp_flight_pilot.flight_id=flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'S', 'D' ) AS 'Solo_DC',
    sexa2HoursMinute(flight.duration) AS '_tr(DURATION)',
    (
        SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
        FROM flight AS tmp_flight
        LEFT JOIN flight_type ON (tmp_flight.flight_type_id & flight_type.id)
        WHERE tmp_flight.id=flight.id
    ) AS '_tr(FLIGHT_TYPE)',
    flight.landing_number AS 'Nombre atterissages',
    flight.comments AS '_tr(COMMENT)'
FROM flight_pilot 
LEFT JOIN flight ON flight_pilot.flight_id=flight.id
LEFT JOIN flight_type ON (flight_type.id & flight.flight_type_id)
LEFT JOIN person ON person.id=flight_pilot.pilot_id
LEFT JOIN resource ON resource.id = flight.aircraft_id 
WHERE YEAR( start_date ) = $year 
  AND flight_pilot.num=0
  AND flight.airborne=0
GROUP BY flight.id
ORDER BY CONCAT(last_name, ' ', first_name), start_date

Flights which have landed elsewhere than the base field

Variable $icao shall be defined (dbObject:Location)

 
SELECT DATE_FORMAT(flight.start_date,'%d/%m/%Y') AS Date, resource.name AS immatriculation, 
    CONCAT(FLOOR( duration/600),':',TIME_FORMAT(SEC_TO_TIME((duration/600 - FLOOR(duration/600))*3600),'%i')) AS Durée,
    person.last_name AS Nom, person.first_name AS Prénom,
    l1.icao_name AS Départ,
    l2.icao_name AS Arrivée
FROM flight 
LEFT JOIN resource ON resource.id = flight.aircraft_id
LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id 
LEFT JOIN person ON person.id = flight_pilot.pilot_id 
LEFT JOIN location AS l1 ON l1.id = flight.departure_location_id
LEFT JOIN location AS l2 ON l2.id = flight.arrival_location_id
WHERE flight_pilot.num = 0 AND (
    l1.icao_name LIKE '$icao'  OR 
    l2.icao_name LIKE '$icao')
GROUP BY flight.id

Flights with a remark in one or two additional entries

Extra field $maintenanceUserComment should be defined first and should be of Text multi line value type and Flight category.
Extra field $mechanicAnswer should be defined first and should be of Text multi line value type and Flight category.

SELECT flight.start_date, resource.name AS Callsign, 
    CONCAT(FLOOR( duration/600),':',TIME_FORMAT(SEC_TO_TIME((duration/600 - FLOOR(duration/600))*3600), "%i")) AS Duration,
    person.last_name AS Nom, person.first_name AS "First name",
    l1.icao_name AS "Departure",
    l2.icao_name AS "Arrival",
    maintenanceUserComment.content AS "User remark",
    mechanicAnswer.content AS "Maintenance answer"
FROM flight 
LEFT JOIN resource ON resource.id = flight.aircraft_id
LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id 
LEFT JOIN person ON person.id = flight_pilot.pilot_id 
LEFT JOIN location AS l1 ON l1.id = flight.departure_location_id
LEFT JOIN location AS l2 ON l2.id = flight.arrival_location_id
LEFT JOIN extra_field_content AS maintenanceUserComment ON maintenanceUserComment.category_id = flight.id
LEFT JOIN extra_field_content AS mechanicAnswer ON mechanicAnswer.category_id = flight.id
WHERE flight_pilot.num = 0
AND maintenanceUserComment.extra_field_id = (SELECT extra_field.id FROM extra_field WHERE (extra_field.variable = "maintenanceUserComment"))
AND mechanicAnswer.extra_field_id = (SELECT extra_field.id FROM extra_field WHERE (extra_field.variable = "mechanicAnswer"))
AND (maintenanceUserComment.content <> "" OR mechanicAnswer.content <> "")
AND start_date>DATE_SUB( NOW(), INTERVAL 3 MONTH )
GROUP BY flight.id;

Flight with their location code (ICAO)

SELECT flight.*, L1.icao_name AS departure_location, L2.icao_name AS arrival_location
FROM flight
LEFT JOIN location AS L1 ON flight.departure_location_id = L1.id
LEFT JOIN location AS L2 ON flight.arrival_location_id = L2.id

Fuel/Oil reports

Fuel/Oil consumption

Unit is: unit of the tank per hour

SELECT
    resource_name AS CallSign,
    CASE tank_type
        WHEN 1 THEN "AVGAS"
        WHEN 2 THEN "JET A1"
        WHEN 3 THEN "Oil"
        WHEN 4 THEN "SP95"
        WHEN 5 THEN "SP98"
        WHEN 6 THEN "GAZOLE"
    END AS Tank,
    formatDecimal( CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Janu,
    formatDecimal( CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Febr,
    formatDecimal( CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Marc,
    formatDecimal( CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Apri,
    formatDecimal( CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS May,
    formatDecimal( CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS June,
    formatDecimal( CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS July,
    formatDecimal( CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Augu,
    formatDecimal( CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Sept,
    formatDecimal( CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Octo,
    formatDecimal( CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Nove,
    formatDecimal( CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Dece,
    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 Sum
FROM
    (SELECT resource.name AS resource_name,
        resource.id AS resource_id,
        tank.tank_type_id AS tank_type,
        flight_tank_qty.quantity  AS quantity,
        MONTH(flight.start_date) AS month_num,
        (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
    LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id
    LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id
    LEFT JOIN resource ON resource.id = flight.aircraft_id
    WHERE YEAR(flight.start_date) = $year
) AS my_table
GROUP BY resource_name, tank_type

Fuel/Oil quantities per tank and aircraft type

SELECT
    aircraft_type_name AS CallSign, tank_label AS Tank,
    formatDecimal( SUM(IF (month_num=1, quantity, 0)) ) AS Janu,
    formatDecimal( SUM(IF (month_num=2, quantity, 0)) ) AS Febr,
    formatDecimal( SUM(IF (month_num=3, quantity, 0)) ) AS Marc,
    formatDecimal( SUM(IF (month_num=4, quantity, 0)) ) AS Apri,
    formatDecimal( SUM(IF (month_num=5, quantity, 0)) ) AS May,
    formatDecimal( SUM(IF (month_num=6, quantity, 0)) ) AS June,
    formatDecimal( SUM(IF (month_num=7, quantity, 0)) ) AS July,
    formatDecimal( SUM(IF (month_num=8, quantity, 0)) ) AS Augu,
    formatDecimal( SUM(IF (month_num=9, quantity, 0)) ) AS Sept,
    formatDecimal( SUM(IF (month_num=10, quantity, 0)) ) AS Octo,
    formatDecimal( SUM(IF (month_num=11, quantity, 0)) ) AS Nove,
    formatDecimal( SUM(IF (month_num=12, quantity, 0)) ) AS Dece,
    formatDecimal( SUM(quantity) ) AS Sum
FROM
(
    SELECT resource_type.id AS aircraft_type_id,
           resource_type.name AS aircraft_type_name,
           tank.id AS tank_id, tank.label AS tank_label,
           CAST( flight_tank_qty.quantity AS DECIMAL(10,2) ) AS quantity,
           MONTH(flight.start_date) AS month_num
    FROM tank
    LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
    LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
    LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
    WHERE YEAR(flight.start_date) = $year
) AS my_table
GROUP BY aircraft_type_id, tank_id

Fuel/Oil sum for each aircraft

Unit is: unit of the tank

SELECT
    resource_name AS CallSign,
    CASE tank_type
        WHEN 1 THEN "AVGAS"
        WHEN 2 THEN "JET A1"
        WHEN 3 THEN "Oil"
        WHEN 4 THEN "SP95"
        WHEN 5 THEN "SP98"
        WHEN 6 THEN "GAZOLE"
    END AS Tank,
    formatDecimal( SUM(IF (month_num=1, quantity, 0)) ) AS Janu,
    formatDecimal( SUM(IF (month_num=2, quantity, 0)) ) AS Febr,
    formatDecimal( SUM(IF (month_num=3, quantity, 0)) ) AS Marc,
    formatDecimal( SUM(IF (month_num=4, quantity, 0)) ) AS Apri,
    formatDecimal( SUM(IF (month_num=5, quantity, 0)) ) AS May,
    formatDecimal( SUM(IF (month_num=6, quantity, 0)) ) AS June,
    formatDecimal( SUM(IF (month_num=7, quantity, 0)) ) AS July,
    formatDecimal( SUM(IF (month_num=8, quantity, 0)) ) AS Augu,
    formatDecimal( SUM(IF (month_num=9, quantity, 0)) ) AS Sept,
    formatDecimal( SUM(IF (month_num=10, quantity, 0)) ) AS Octo,
    formatDecimal( SUM(IF (month_num=11, quantity, 0)) ) AS Nove,
    formatDecimal( SUM(IF (month_num=12, quantity, 0)) ) AS Dece,
    formatDecimal( SUM(quantity) ) AS Sum,
    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 Consumption
FROM
    (SELECT resource.name AS resource_name,
        resource.id AS resource_id,
        tank.tank_type_id AS tank_type,
        CAST( flight_tank_qty.quantity AS DECIMAL(10,2) ) AS quantity,
        MONTH(flight.start_date) AS month_num
    FROM tank
    LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id
    LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id
    LEFT JOIN resource ON resource.id = flight.aircraft_id
    WHERE YEAR(flight.start_date) = $year
) AS my_table
GROUP BY resource_name, tank_type

Total tank quantity for each tank and for each aircraft

SELECT resource.name AS aircraft_callsign, tank.label AS tank_label, formatDecimal( SUM( quantity ) ) AS total_qty
FROM tank
LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
LEFT JOIN resource ON (flight.aircraft_id = resource.id)
WHERE YEAR(flight.start_date) = $year
GROUP BY resource.id, tank_type_id

Total tank quantity for each tank and for each aircraft type

SELECT resource_type.name AS aircraft_type, tank.label AS tank_label, formatDecimal( SUM( quantity ) ) AS total_qty
FROM tank
LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
WHERE YEAR(flight.start_date) = $year
GROUP BY aircraft_type_id, tank_type_id

Error message queries

Flight without account movement

SELECT 
DATE_FORMAT(flight.start_date,'%d/%m/%Y' ) AS Date,
TIME_FORMAT(flight.start_date,'%H:%i' ) AS Time,
resource.name AS Callsign,
person.last_name AS Lastname,
person.first_name AS Firstname,
CONCAT(FLOOR( flight.duration /600),':',
TIME_FORMAT(SEC_TO_TIME(( flight.duration/600 - FLOOR( flight.duration /600))*3600),'%i'))
AS FlightTime
FROM flight 
LEFT JOIN flight_account_entry ON flight.id=flight_account_entry.flight_id
LEFT JOIN resource ON resource.id=flight.aircraft_id
LEFT JOIN flight_pilot ON flight_pilot.flight_id=flight.id
LEFT JOIN person ON person.id=flight_pilot.pilot_id
WHERE
flight_account_entry.account_entry_id IS NULL
AND flight.airborne=0
AND flight_pilot.num=0

movement without an account

SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE account_id is null
SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE flow_id = 'xxxxxx'

Users

Users exports SQL queries.

@@ Line 2: / Line 2: @@
 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.
+Do not forget to read the [[OpenFlyers SQL stored functions and procedures]] page and the [[Database description]] page.
 Please note that you can make "public" a report, ie. allows standard users to see and interact with it.
@@ Line 19: / Line 19: @@
 ===Extra Field creation===
-*Go to Menu Admin/Reports/Structure/Extra Field(s)
+*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)
@@ Line 40: / Line 40: @@
 *Name: $aircraftId
 *Label: Aircraft
-*Value type: dbObject::Aircaft
+*Value type: dbObject::Aircraft
 Then in Admin/Reports/Structure/Criteria we create a new query labeled "Aircraft booking" with the following query:
 <sql>SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId</sql>
 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"
-=French administration Examples=
+=SQL tips and tricks=
+==Return only last entry==
+Example with last entry from variable #1 in variable_value table:
+<sql>SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC  LIMIT 1</sql>
-==Flights hours total per aircraft per year==
+==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.
-<sql>SELECT
+Per example, $occupiedSeat can have following values (0, 1 or NULL) :
-    name AS Callsign,
-    YEAR( start_date ) AS Year,
-    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
-  FROM flight
-    LEFT JOIN resource ON resource.id = flight.aircraft_id
-  WHERE YEAR(start_date) = $year
-  GROUP BY name
-  UNION
-  SELECT
-    "Total",
-    $year AS year,
-    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
-  FROM flight
-  WHERE YEAR(start_date) = $year
-  GROUP BY year</sql>
-==Flights hours total per aircraft per year and per month==
+<sql>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
+)</sql>
-  <SQL>SELECT  aircraft_id AS Num, name,
+=French administration Examples=
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num) AS Janu,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num) AS Febr,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num) AS Marc,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num) AS Apri,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num) AS May,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num) AS June,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num) AS July,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num) AS Augu,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num) AS Sept,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num) AS Octo,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num) AS Nove,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num) AS Dece,
-  CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Sum
-  FROM flight
-  LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
-  LEFT JOIN resource ON resource.id = aircraft.id
-  WHERE YEAR(start_date)= $year  AND resource.virtual = 0
-  GROUP BY order_num
-  UNION
-  SELECT  "Sum per","month",
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12),
-  CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
-  FROM flight
-  LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
-  LEFT JOIN resource ON resource.id = aircraft.id
-  WHERE YEAR(start_date)= $year AND resource.virtual = 0
-  UNION
-  SELECT  "Cumulative","per month",
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 1),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 2),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 3),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 4),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 5),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 6),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 7),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 8),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 9),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 10),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 11),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 12),
-  $year
-  FROM flight
-  LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
-  LEFT JOIN resource ON resource.id = aircraft.id
-  WHERE YEAR(start_date)= $year  AND resource.virtual = 0</SQL>
-==Number of members : male, female, less than 21 years and more than 21 years==
+==Flight type : Flight hours total on instruction with specific profile==
-  <SQL>SELECT
+* Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type.
-    IF ( (sex = 0), 'Men', 'Women' ) AS Sex,
+* Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type.
-    IF( ( $year - YEAR( birthdate ) >= 21), 'No', 'Yes') AS Young,
+* Variable '''$year''' should be defined first and should be of '''Year''' value type.
-    COUNT( id ) AS Number
-  FROM person
-  WHERE activated='1'
-  GROUP BY
-    Sex,
-    Young</SQL>
-==Flights hours : less than 21 years, more than 21 years, male, female==
+<sql>SELECT tmp_flight.activity_type_name AS name,
-  <SQL>SELECT
+       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion',
-    IF( a.sex=0, 'Men','Women') AS Sexe,
+       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère',
-    IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
+       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur',
-    IF ((SELECT COUNT(*)
+       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM'
-         FROM flight_pilot fp2
+FROM (
-         WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'double') AS DC,
+     SELECT
-   CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total
+        flightWithActivityType.activity_type_id,
-  FROM flight_pilot fp
+        flightWithActivityType.activity_type_name,
-     LEFT JOIN flight ON fp.flight_id=flight.id
+        flightWithActivityType.duration,
-    LEFT JOIN person a ON a.id=fp.pilot_id
+        resource_type.category
-  WHERE YEAR( start_date ) = $year
+     FROM (
-    AND fp.num=0
+        SELECT flight.*, flight_type.id AS activity_type_id, flight_type.name AS activity_type_name
-  GROUP BY
+        FROM flight
-    Sex,
+        LEFT JOIN flight_type ON flight.flight_type_id&flight_type.id
-    Young,
+        WHERE YEAR(flight.start_date)=$year
-    DC
+          AND flight.airborne=0
-  UNION
+          AND ( flight_type.id IN ('$flightTypeId') OR '-' IN ('$flightTypeId') )
-  SELECT
+     ) AS flightWithActivityType
-     IF ( (sex = 0), 'Male', 'Female' ) AS Sex,"All",
+     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1)
-    IF ( num = 0, 'Total', 'DC') AS DC,
+     LEFT JOIN (
-    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+        SELECT person.*
-  FROM flight
+        FROM person
-    LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id
+        LEFT JOIN profile ON person.profile&profile.id
-    LEFT JOIN person a ON a.id=fp.pilot_id
+        WHERE ( profile.id IN ('$profileId') OR '-' IN ('$profileId') )
-  WHERE fp.num=0 AND YEAR(start_date)=$year
+          AND person.activated=1
-  GROUP BY Sex
+        GROUP BY person.id
-  UNION
+     ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id)
-  SELECT
+     LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
-    "All",
+     LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
-    IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
+     WHERE personWithProfile.id IS NOT NULL
-    IF ( num = 0, 'Total', 'DC') AS DC,
+) AS tmp_flight
-     CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+GROUP BY tmp_flight.activity_type_id</sql>
-  FROM flight
-     LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id
-     LEFT JOIN person a ON a.id=fp.pilot_id
-  WHERE YEAR( start_date ) = $year
-    AND fp.num=0
-  GROUP BY Young
-  UNION
-  SELECT
-     "Total",
-    $year AS year,
-    IF ( num = 0, 'Total', 'DC') AS DC,
-    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
-  FROM flight
-     LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id
-     LEFT JOIN person a ON a.id=fp.pilot_id
-  WHERE YEAR( start_date ) = $year
-     AND fp.num=0
-  GROUP BY year</SQL>
-==Flights hours following nationality==
+==Flight type : Flight hours total on non-instruction with specific profile==
-   <SQL>SELECT
-     nationality.label,
-     CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 -   FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total
-   FROM flight , flight_pilot fp, person a, nationality
-   WHERE flight.id = fp.flight_id
-     AND fp.pilot_id = a.id
-     AND a.nationality = nationality.code
-   GROUP BY nationality.code</SQL>
-==Licence in the year==
+* 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.
-  <SQL>SELECT
+<sql>SELECT tmp_flight.activity_type_name AS name,
-    last_name AS Last_name,
+       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion',
-    first_name AS First_name,
+       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère',
-    IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
+       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur',
-    IF ( (sex = 0), 'Male', 'Female' ) AS Sex, DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, validity_type.name AS 'Validity'
+       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM'
-  FROM person
+FROM (
-     LEFT JOIN validity ON person_id=person.id
+    SELECT
-     LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
+        flightWithActivityType.activity_type_id,
-  WHERE YEAR(grant_date)=$year</SQL>
+        flightWithActivityType.activity_type_name,
+        flightWithActivityType.duration,
+        resource_type.category
+    FROM (
+        SELECT flight.*, flight_type.id AS activity_type_id, flight_type.name AS activity_type_name
+        FROM flight
+        LEFT JOIN flight_type ON flight.flight_type_id&flight_type.id
+        WHERE YEAR(flight.start_date)=$year
+          AND flight.airborne=0
+          AND ( flight_type.id IN ('$flightTypeId') OR '-' IN ('$flightTypeId') )
+          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</sql>
-==List of pilots with specific validity==
+==Users with a specific validity without expiration date==
+*Variable '''$validityId''' should be defined first and should be of '''dbOject::ValidityType''' value type.
-  <SQL>SELECT
+<sql>SELECT person.first_name, person.last_name FROM validity
-    last_name AS Last_name,
+LEFT JOIN person ON person.id=validity.person_id
-    first_name AS First_name,
+WHERE validity_type_id=$validityTypeId AND expire_date IS NULL AND person.activated=1</sql>
-    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</SQL>
-Note: replace name of validity_type.name by yours
+==Number of movements on based airfield==
-==Young list==
+* Variable '''$year''' should be defined first and should be of '''Year''' value type.
-  <SQL>SELECT
-    last_name AS Last_name,
-    first_name AS First_name,
-    DATE_FORMAT(birthdate ,'%m-%d-%Y') AS Birthdate,
-    IF ( (sex = 0), 'Male', 'Female' ) AS sex
-  FROM person
-  WHERE (2011-YEAR(birthdate))<=21</SQL>
-==Flights hours total per flight type==
+<sql>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</sql>
-  <SQL>SELECT
+==Number of movements on based airfield on specific profile==
-    ft.name AS Type_vol,
-    IF ((SELECT
-           COUNT(*)
-         FROM flight_pilot fp2
-         WHERE fp2.flight_id=fp.flight_id
-           AND fp2.num=1)=0, 'Solo', 'Double') AS DC,
-    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
-  FROM flight
-    LEFT JOIN flight_type ft ON ft.id & flight.flight_type_id
-    LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id
-  WHERE YEAR( start_date ) = $year
-    AND fp.num=0
-  GROUP BY
-    ft.id,
-    DC HAVING (Name <> 'Instruction' OR DC <> 'Double')
-  ORDER BY order_num ASC</SQL>
-==Number of visit on other airfield==
+* 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.
-  <SQL>SELECT ap.icao_name AS ICAO,
+<sql>SELECT SUM(movement) AS 'Movement'
-    ap.name AS Name,
+FROM
-     COUNT( ap.icao_name ) AS nb_visite
+(
-  FROM flight f
+     SELECT COUNT(*) AS movement
-  LEFT JOIN location AS ap ON f.departure_location_id = ap.id
+    FROM flight
-  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))
+    LEFT JOIN location ON (flight.departure_location_id = location.id)
-     AND departure_location_id != arrival_location_id
+    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
-     AND YEAR( start_date ) = $year
+    LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0)
-  GROUP BY icao_name
+    WHERE (
-  ORDER BY nb_visite DESC</SQL>
+            ( 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</sql>
-==Number of movements on based platform due to country flight==
+==Visited airfields==
-  <SQL>SELECT ap.icao_name AS ICAO,
+<sql>SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS nb_visite
-    ap.name AS Name,
+FROM flight
-    COUNT( ap.icao_name ) AS nb_flight
+LEFT JOIN location ON (flight.departure_location_id = location.id)
-  FROM flight f
+WHERE (
-  LEFT JOIN location AS ap ON f.departure_location_id = ap.id
+        ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
-  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))
+        OR
-     AND departure_location_id != arrival_location_id
+        ( 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
+GROUP BY icao_name
-  ORDER BY nb_flight DESC</SQL>
+ORDER BY nb_visite DESC</sql>
 ==Number of take-off and landings on based airfield==
@@ Line 295: / Line 246: @@
    AND YEAR( start_date ) = $year</SQL>
-==Flights hours total per instructor==
+==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.
+<sql>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</sql>
+==List of movements on based airfield==
+<sql>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</sql>
+==Pilots who have flown without required validity==
+<sql>SELECT CONCAT(person.last_name, ' ', person.first_name) AS pilot, validity_type.name AS 'Validity'
+FROM flight_type_mandatory_validity_type
+LEFT JOIN flight_type ON flight_type.id = flight_type_mandatory_validity_type.flight_type_id
+LEFT JOIN flight ON flight.flight_type_id & flight_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)
+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
+GROUP BY person.id, validity_type.id
+ORDER BY pilot, validity_type.name</sql>
+==List of pilots who have flown less than X hours during last Y days==
+<sql>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</sql>
+==List of pilots who have flown less than X hours during last Y days on aircraft type Z==
+<sql>
+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</sql>
+==Pilots without flight in the last 3 months==
+* Variable '''$month''' should be defined first and should be of '''integer''' value type.
 <sql>SELECT
-     last_name AS Last_name,
+     person.last_name, person.first_name,
-    first_name AS First_name,
+     IFNULL((SELECT CONCAT(DATE_FORMAT( f1.start_date, '%d/%m/%Y' ),' ', resource.name,' Duration: ',
-     CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+               TIME_FORMAT(SEC_TO_TIME(f1.duration*6 ) ,'%H h %i'))
-  FROM flight
+            FROM flight AS f1
-    LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
+            LEFT JOIN flight_pilot AS fp ON fp.flight_id=f1.id
-    LEFT JOIN person a ON a.id=fp.pilot_id
+            LEFT JOIN resource ON resource.id=f1.aircraft_id
-  WHERE fp.num = 1
+            WHERE fp.pilot_id=person.id
-    AND YEAR( start_date ) = $year
+            ORDER BY f1.start_date DESC LIMIT 1),'UNKNOWN') AS 'Last flight'
-  GROUP BY a.id
+FROM person
-  UNION
+WHERE person.activated=1
-  SELECT
+AND person.id NOT IN (
-     "Total",
+    SELECT person2.id
-    $year AS year,
+     FROM flight AS f
-     CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+     LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id
-  FROM flight,flight_pilot fp
+    LEFT JOIN person AS person2 ON person2.id=fp.pilot_id
-  WHERE flight.id = fp.flight_id
+     WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL IF($month='', 1, $month*30) DAY AND NOW()
-     AND fp.num = 1
+    GROUP BY person2.id HAVING SUM(f.duration)/600 > 0
-    AND YEAR( start_date ) = $year
+)
-  GROUP BY year</sql>
+ORDER BY person.last_name, person.first_name</sql>
-=Accounting=
+==Number of landings per pilot, per resource==
+<sql>SELECT last_name as "Last_name", first_name as "First_name",
+(IF(DATE(valid.grant_date) < date(NOW()),'B', 'E')) as 'S',
-==Global account balance==
+-- (IF(DATE(valid.grant_date) < date(NOW()),valid_typ.name, 'Eleve')) as 'S2',
-List whole account balance at the end of a given date ([[#Extra field|Extra field]] $endDateAccount, type dateTime, should be defined)
-<sql>SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom,
+(SELECT SUM(landing_number) FROM flight
-IFNULL(sumAccountEntry(account.id,'$endDateAccount'),0) AS solde
+LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
-FROM account
+WHERE flight_pilot.pilot_id = a.id
-LEFT JOIN person ON person.id=account.owner_id
+AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS atterrissages,
-WHERE account.activated=1
-ORDER BY export_account</sql>
-==Global account balance of pilots who last subscription was 2 years ago==
+(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,
-Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.
+(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,
-<sql>SELECT last_name, first_name, validity_type.name as 'Validity', grant_date,
+(SELECT SUM(landing_number) FROM flight
-      IFNULL((SELECT  SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry  WHERE account_entry.account_id = a.id
+LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
-              AND account_entry.account_date > (SELECT balance_date  FROM balance_date ORDER BY balance_date DESC LIMIT  1)),0)+balance.credit-balance.debit AS Total
+WHERE flight_pilot.pilot_id = a.id
-FROM `person`
+-- AND flight.aircraft_id = 2
-LEFT JOIN validity ON validity.person_id = person.id
+AND flight.aircraft_id = 3
-LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id
+AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS KH,
-LEFT JOIN account AS a ON a.owner_id = person.id AND category = 2
-LEFT JOIN balance ON balance.account_id = a.id
-          AND balance.balance_date_id = (SELECT id FROM balance_date  ORDER BY balance_date ASC LIMIT 1)
-WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)
-  AND validity_type.id = $validityTypeId
-GROUP BY person.id
-ORDER BY last_name,first_name</sql>
-==Global non null account balance==
+(SELECT SUM(landing_number) FROM flight
-*List whole account balance at the end of a given date ([[#Extra field|Extra field]] $endDateAccount has to be defined and should be of DateTime value type) but for only non null account balance :
+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,
-<sql>SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom,
+(SELECT SUM(landing_number) FROM flight
-IFNULL(sumAccountEntry(account.id,'$endDateAccount'),0) AS solde
+LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
-FROM account
+WHERE flight_pilot.pilot_id = a.id
-LEFT JOIN person ON person.id=account.owner_id
+AND (flight.aircraft_id = 2 OR flight.aircraft_id = 3 OR flight.aircraft_id = 4 OR flight.aircraft_id = 18)
-WHERE account.activated=1 AND sumAccountEntry(account.id,'$endDateAccount') <> 0
+AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS DR400,
-ORDER BY export_account </sql>
-*List whole account balance at the end of a given year ([[#Extra field|Extra field]] $year has to be defined and should be of Year value type) but for only non null account balance :
+(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 = 23
+AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS DR500,
-<sql>SELECT id, export_account, name, sumAccountEntry(id,'$year-12-31 23:59:59') AS solde
+(SELECT SUM(landing_number) FROM flight
-FROM account WHERE activated=1 AND sumAccountEntry(id,'$year-12-31 23:59:59') <> 0
+LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
-ORDER BY export_account</sql>
+WHERE flight_pilot.pilot_id = a.id
+AND flight.aircraft_id = 8
+AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS 'MC-IP',
-==Account balance list==
+(SELECT SUM(landing_number) FROM flight
-[[Export_generator_3.0#Extra field]] $endDateAccount has to be defined first and should be of value type DateTime.
+LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
-===List of pilot account balance===
+WHERE flight_pilot.pilot_id = a.id
+AND flight.aircraft_id = 21
+AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS 'CAP10-DL'
-<sql>SELECT person.last_name, person.first_name, account_type.name AS account_type_name,
+FROM person a
-sumAccountEntry(account.id, '$endDateAccount') AS balance
-FROM account
+LEFT JOIN profile AS p1 ON (a.profile & p1.id)
-LEFT JOIN account_type ON (account.account_type=account_type.id)
+LEFT JOIN validity valid ON valid.person_id = a.id
-LEFT JOIN person ON (account.owner_id=person.id)
+LEFT JOIN validity_type valid_typ ON valid_typ.id = valid.validity_type_id
-WHERE account.category=2 AND account.activated=1
-ORDER BY last_name, first_name</sql>
+WHERE YEAR(grant_date) >= YEAR(NOW())
+AND a.activated=1 AND p1.name='Pilot'
+GROUP BY last_name, first_name
+ORDER BY last_name</sql>
+==Flight hours without up to date validities==
+<sql>SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS '_tr(DATE)', last_name AS '_tr(LAST_NAME)', first_name AS '_tr(FIRST_NAME)',
+       IF ( (SELECT COUNT(*) FROM flight_pilot AS tmp_flight_pilot WHERE flight_pilot.flight_id=tmp_flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'Solo', 'DC' ) AS DC,
+       resource.name AS '_tr(RESOURCE)',
+       sexa2HoursMinute(duration) AS '_tr(DURATION)',
+       flight_type.name AS '_tr(flight_type)',
+       validity_type.name AS '_tr(VALIDITY)'
+FROM flight
+LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.flight_type_id & flight.flight_type_id
+LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id
+LEFT JOIN person ON flight_pilot.pilot_id = person.id
+LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id
+LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id
+LEFT JOIN resource ON flight.aircraft_id = resource.id
+LEFT JOIN flight_type ON flight_type_mandatory_validity_type.flight_type_id = flight_type.id
+WHERE YEAR(start_date) = $year
+  AND flight_pilot.num = 0
+  AND validity_type.time_limitation=1
+  AND validity.person_id = flight_pilot.pilot_id
+  AND flight.start_date > validity.expire_date
+  AND person.activated=1
+ORDER BY flight.start_date, person.last_name, validity_type.name</sql>
+==Number of men, women over and under X years for profile P, validity V up to date for year A==
+* Variable '''$age''' should be of '''integer''' value type.
+* Variable '''$profileId''' should be of '''dbOject::Profile''' value type.
+* Variable '''$validityTypeId''' should be of '''dbObject::validityType''' value type.
+* Variable '''$year''' should be of '''Year''' value type.
-===List of all account debit, credit===
 <sql>SELECT
-     account.export_account AS 'Export account',
+     IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex',
-    CASE
+    IF( ( $year - YEAR(birthdate) >= '$age' ) , 'Yes', 'No') AS 'Adult',
-        WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('Member account ', account_type.name, ' of ', person.last_name, ' ', person.first_name)
+     COUNT(DISTINCT person.id) AS NUMBER
-        WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name)
+FROM person
-        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
+LEFT JOIN validity ON (person.id=validity.person_id)
-        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
+LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
-        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
+WHERE activated=1
-        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
+  AND (profile & '$profileId' OR '-'='$profileId')
-        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
+  AND (
-        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
+      (validity_type.id='$validityTypeId' AND validity.expire_date >= '$year-12-31')
-        ELSE account.name
+      OR
-    END AS account_name,
+      '-'='$validityTypeId'
-    IF (sumAccountEntry(account.id,'$endDateAccount')<0, sumAccountEntry(account.id,'$endDateAccount'), 0)  AS Debit,
+  )
-     IF (sumAccountEntry(account.id,'$endDateAccount')<0, 0, sumAccountEntry(account.id,'$endDateAccount') ) AS Credit
+GROUP BY Sex, Adult</sql>
-FROM account
-LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3))
-LEFT JOIN person ON (account.owner_id = person.id AND account.category=2)
-LEFT JOIN aircraft ON (account.owner_id = aircraft.id AND account.category=3)
-LEFT JOIN resource ON aircraft.id = resource.id
-WHERE person.activated = 1
-GROUP BY account.id
-ORDER BY account.export_account ASC</sql>
-==Balance per account category==
+==Validities in the year : Total of youngs/adults with 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.
+* Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need.
 <sql>SELECT
-     CASE
+     validity_type.name AS 'Validity',
-        WHEN account_type.name IS NOT NULL AND (account.category=2 OR account.category=3) THEN account_type.name
+    SUM( IF( ( $year - YEAR(person.birthdate) < 21 ), 1, 0 ) ) AS 'Young',
-        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
+     SUM( IF( ( $year - YEAR(person.birthdate) >= 21 ), 1, 0 ) ) AS 'Adult'
-        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
+FROM validity_type
-        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
+LEFT JOIN validity ON (validity.validity_type_id=validity_type.id)
-        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
+LEFT JOIN person ON (person.id=validity.person_id)
-        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
+WHERE YEAR(validity.grant_date)=$year
-        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
+  AND person.activated=1
-        ELSE account.name
+  AND (person.profile & '$profileId' OR '-'='$profileId')
-    END AS 'Account type',
+GROUP BY validity_type.id
-     IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde
+ORDER BY validity_type.name</sql>
-FROM account
-LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3))
-GROUP BY account.category</sql>
-==Carry forwards==
+==Validities obtained in the year==
+*Variable '''$validityId''' should be defined first and should be of '''dbOject::ValidityType''' value type.
+*'''21''' value is the age limit to be young. Should be changed according local rules
-  <SQL>SELECT
+<sql>SELECT
-     IF (account.category = 2, CONCAT(person.last_name,'
+     last_name AS Last_name,
-',person.first_name,' (',account_type.name,')'),
+    first_name AS First_name,
-     IF (account.category = 3,CONCAT(resource.name,'
+    IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
-(',account_type.name,')'),account.name)) AS Nom,
+     IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, validity_type.name AS 'Validity'
-    export_account AS Code_comptable,
+FROM person
-    debit AS 'Debit',
+LEFT JOIN validity ON person_id=person.id
-    credit AS 'Credit'
+LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
-FROM account
+WHERE YEAR(grant_date)=$year
-LEFT JOIN balance ON balance.account_id=account.id
+  AND ( validity_type.id IN ('$validityTypeId') OR '-' IN ('$validityTypeId') )
-LEFT JOIN person ON person.id=account.owner_id AND account.category = 2
+  AND person.activated=1
-LEFT JOIN aircraft ON aircraft.id=account.owner_id AND account.category = 3
+ORDER BY last_name, first_name</sql>
-LEFT JOIN resource ON resource.id=aircraft.id
-LEFT JOIN account_type ON account_type.id=account.account_type
-WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1)
-UNION
-SELECT
-    'zzzzzzz',
-    'Total',
-    SUM(debit) AS 'Debit',
-    SUM(credit) AS 'Credit'
-FROM account
-LEFT JOIN balance ON balance.account_id=account.id
-WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1)
-ORDER BY Nom ASC</SQL>
-==Payments list ordered by type==
+=[[Accounting reports 3]]=
-<sql>SELECT payment_type.name AS Type, if(account.category = 2,
-	CONCAT( p.last_name, ' ', p.first_name),account.name) AS Name,
-	DATE_FORMAT(ae.account_date, '%d %m %Y' ) AS Date,
-	ae.payment_description, ae.credit AS Description
-FROM account_entry AS ae
-LEFT JOIN payment_type ON payment_type.id = ae.payment_type
-LEFT JOIN account ON account.id = ae.account_id
-LEFT JOIN person AS p ON p.id = account.owner_id
-WHERE ae.payment_type IS NOT NULL AND credit > 0 AND YEAR(ae.account_date) >= $year
-ORDER BY ae.payment_type, ae.account_date, p.last_name, p.first_name</sql>
-=Flight time management=
+=Booking=
-==Flight hours total per pilot==
-   <SQL>SELECT
+==Bookings==
-     CONCAT(last_name,' ',first_name) AS Nom,
+* Variable '''$endDate''' of '''Date and time''' value type.
+* Variable '''$startDate''' of '''Date and time''' value type.
+* Variable '''$personId''' of '''dbObject::Person''' value type.
+* Variable '''$resourceId''' of '''dbObject::Resource''' value type.
+<sql>SELECT booking.start_date AS 'Date de début', booking.end_date AS 'Date de fin', resource.name AS 'Ressource',
+       CONCAT(left_person.last_name, ' ', left_person.first_name) AS 'Place gauche',
+       CONCAT(right_person.last_name, ' ', right_person.first_name) AS 'Place droite'
+FROM booking
+LEFT JOIN resource ON (resource.id=booking.resource_id)
+LEFT JOIN person AS left_person ON (left_person.id=booking.person_id)
+LEFT JOIN person AS right_person ON (right_person.id=booking.instructor_id)
+WHERE booking.start_date >= '$startDate' AND booking.end_date < '$endDate'
+   AND (booking.resource_id='$resourceId' OR '-'='$resourceId')
+  AND (
+      (left_person.id='$personId' AND left_person.activated=1)
+      OR
+      (right_person.id='$personId' AND right_person.activated=1)
+      OR
+      '-'='$personId'
+  )
+ORDER BY booking.start_date, booking.end_date</sql>
+==Cumulated maintenance hours between a period==
+Following extrafields are needed :
+*startDate (Type : Date)
+*endDate (Type : Date or DateTime)
+<sql>SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours'
+UNION ALL
+SELECT resource.name AS 'Ressource', SUM(TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date)), SUM(TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date))
+FROM booking
+LEFT JOIN log ON log.field_value=booking.id
+LEFT JOIN journal ON journal.id=log.journal_id
+LEFT JOIN resource ON resource.id = booking.resource_id
+WHERE (booking.slot_type=2)
+  AND (log.action='INSERT')
+  AND (log.table_name='booking')
+  AND (log.field_name='id')
+  AND (booking.start_date >= '$startDate')
+  AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate'))
+GROUP BY resource.id
+ORDER BY 1</sql>
+==Maintenance hours per resource on a period==
+Following extrafields are needed :
+*startDate (Type : Date)
+*endDate (Type : Date or DateTime)
+<sql>SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Date debut maintenance', '' AS 'Date fin maintenance', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours'
+UNION ALL
+SELECT resource.name, booking.start_date AS 'Date debut maintenance', booking.end_date, TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date),  TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date)
+FROM booking
+LEFT JOIN log ON log.field_value=booking.id
+LEFT JOIN journal ON journal.id=log.journal_id
+LEFT JOIN resource ON resource.id = booking.resource_id
+WHERE (booking.slot_type=2)
+  AND (log.action='INSERT')
+  AND (log.table_name='booking')
+  AND (log.field_name='id')
+  AND (booking.start_date >= '$startDate')
+  AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate'))
+ORDER BY 1, 2</sql>
+==Booking for maintenance ordered by resource and date with name of the responsible==
+<sql>SELECT resource.name AS 'Resource name', booking.start_date AS 'Start date', TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date) AS 'booking duration', person.first_name AS 'First name', person.last_name AS 'Last name'
+FROM booking
+LEFT JOIN log ON log.field_value=booking.id
+LEFT JOIN journal ON journal.id=log.journal_id
+LEFT JOIN person ON person.name=journal.login
+LEFT JOIN resource ON resource.id = booking.resource_id
+WHERE (booking.slot_type=2) AND (log.action='INSERT') AND (log.table_name='booking') AND (log.field_name='id')
+AND YEAR(booking.start_date) = $year
+AND person.activated=1
+ORDER BY resource.name, booking.start_date</sql>
+=Flight time reports=
+==Flight hours per month==
+<sql>SELECT MONTH( start_date) AS months, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+FROM flight
+LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
+WHERE YEAR(start_date) = $year
+GROUP BY months</sql>
+==Flight hours total and last recorded counter to the date X==
+* Variable '''$endDate''' should be of '''Datetime''' value type.
+<sql>SELECT resource.name, sexa2HoursMinute( SUM(duration) + aircraft.ref_hours ) AS 'Total heures', sexa2HoursMinute( MAX(counter_arrival) ) AS 'Dernier compteur'
+FROM flight
+LEFT JOIN resource ON (resource.id=flight.aircraft_id)
+LEFT JOIN aircraft ON (aircraft.id=resource.id)
+WHERE flight.start_date <= '$endDate'
+  AND resource.activated=1
+GROUP BY resource.id
+ORDER BY resource.name</sql>
+==Flight hours total over a 12 months period==
+<sql>SELECT
+     CONCAT(last_name,' ',first_name) AS 'Person',
      IF (( SELECT
              COUNT(*)
@@ Line 474: / Line 626: @@
    LEFT JOIN flight ON fp.flight_id=flight.id
    LEFT JOIN person a ON a.id=fp.pilot_id
-   WHERE YEAR( start_date ) = $year AND fp.num=0
+  WHERE start_date > DATE_SUB(NOW(), INTERVAL 1 YEAR)  AND fp.num=0
-   GROUP BY Nom, Solo_DC</SQL>
+  GROUP BY Person, Solo_DC</sql>
+==Flights hours total per aircraft per year==
+<sql>SELECT
+    name AS Callsign,
+    YEAR( start_date ) AS Year,
+    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+  FROM flight
+    LEFT JOIN resource ON resource.id = flight.aircraft_id
+   WHERE YEAR(start_date) = $year AND airborne = 0
+  GROUP BY name
+  UNION
+  SELECT
+    "Total",
+    $year AS year,
+    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+  FROM flight
+  WHERE YEAR(start_date) = $year AND airborne = 0
+  GROUP BY year</sql>
+==Flights hours total per aircraft per year and per month (for a flight type)==
+* Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type.
+* Variable '''$year''' should be defined first and should be of '''Year''' value type.
+<sql>SELECT aircraft_id AS Num, resource.name,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS Janu,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS Febr,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS Marc,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS Apri,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS May,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS June,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS July,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS Augu,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS Sept,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS Octo,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS Nove,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS Dece,
+    sexa2HoursMinute( SUM( duration ) ) AS SUM
+FROM flight
+LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
+LEFT JOIN resource ON resource.id = aircraft.id
+WHERE YEAR(start_date)= $year AND airborne = 0
+   AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')
+GROUP BY resource.id
+UNION
+SELECT  "Sum per","month",
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( duration ) ) AS Total
+FROM flight
+LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
+LEFT JOIN resource ON resource.id = aircraft.id
+WHERE YEAR(start_date)= $year AND airborne = 0
+  AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')
+UNION
+SELECT  "Cumulative","per month",
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=1, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=2, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=3, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=4, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=5, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=6, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=7, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=8, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=9, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=10, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=11, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=12, duration, 0 ) ) ),
+    sexa2HoursMinute( SUM( duration ) ) AS Total
+FROM flight
+LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
+LEFT JOIN resource ON resource.id = aircraft.id
+WHERE YEAR(start_date)= $year AND airborne = 0
+  AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')</sql>
+==Flight hours total per pilot==
+<sql>SELECT
+    CONCAT(last_name,' ',first_name) AS Nom,
+    sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) ) AS 'Solo',
+    sexa2HoursMinute( SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS 'Double',
+    sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) + SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS 'Total'
+FROM person
+LEFT JOIN (
+    SELECT
+        pilot_id,
+        IF (
+            (SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0,
+            'Solo',
+            'DC'
+        ) AS Solo_DC,
+        SUM(flight.duration) AS sum_duration
+    FROM flight_pilot fp
+    LEFT JOIN flight ON fp.flight_id=flight.id
+    WHERE flight.airborne=0 AND YEAR(start_date) = $year AND fp.num=0
+    GROUP BY pilot_id, Solo_DC
+) AS Solo ON person.id=Solo.pilot_id AND Solo.Solo_DC='Solo'
+LEFT JOIN (
+    SELECT
+        pilot_id,
+        IF (
+            (SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0,
+            'Solo',
+            'DC'
+        ) AS Solo_DC,
+        SUM(flight.duration) AS sum_duration
+    FROM flight_pilot fp
+    LEFT JOIN flight ON fp.flight_id=flight.id
+    WHERE flight.airborne=0 AND YEAR(start_date) = $year AND fp.num=0
+    GROUP BY pilot_id, Solo_DC
+) AS DC ON person.id=DC.pilot_id AND DC.Solo_DC='DC'
+WHERE (Solo.pilot_id IS NOT NULL OR DC.pilot_id IS NOT NULL)
+  AND person.activated=1
+GROUP BY person.id
+ORDER BY Nom</sql>
 ==Flight hours total per pilot per profile==
@@ Line 488: / Line 766: @@
          'Solo',
          'double') AS Solo_DC,
-     CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total
+     sexa2HoursMinute( SUM( flight.duration ) ) AS Total
    FROM flight_pilot fp
    LEFT JOIN flight ON fp.flight_id=flight.id
    LEFT JOIN person ON person.id=fp.pilot_id
    LEFT JOIN profile ON (person.profile & profile.id)
-   WHERE YEAR( start_date ) = $year AND fp.num=0
+   WHERE flight.airborne=0 AND YEAR( start_date ) = $year AND fp.num=0
    GROUP BY Profil, Nom, Solo_DC</sql>
-==Flights hours total per instructor per year and per month==
+==Total Flight hours per pilot with total duration and total amount==
+Following extrafields are needed
+*year (Type : Year)
+*month (Type : Integer) '''OR''' startDate and endDate (Type : Date)
+*$occupiedSeat (Type : Integer)
+*profileId (Type : dbObject::Profile)
+This report is useful for a group of pilots from a common customer (like DGAC in France)
+<sql>SELECT CONCAT(person.last_name,' ',person.first_name) AS Nom_et_prenom_pilotes,
+CONCAT(FLOOR(SUM( flight_list.duree )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight_list.duree )/600 - FLOOR(SUM( flight_list.duree )/600))*3600),'%i')) AS nombre_hdv_effectuees,
+SUM(flight_list.montant) AS montant_total_des_vols
+FROM person
+JOIN
+(SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
+LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
+LEFT JOIN flight ON flight.id=flight_pilot.flight_id
+LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
+LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
+LEFT JOIN account ON account.id=account_entry.account_id
+WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
+    AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
+    OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
+        AND person.profile&$profileId AND (account.category = 11 OR account.category = 2)
+GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
+GROUP BY person.id</sql>
+==Flight hours per instructor, month (for a flight type)==
+Following extrafields are needed :
+*year (Type : Year)
+*flightTypeId (Type : dbObject::FlightType)
 <sql>SELECT
-     last_name AS Last_name,
+     UPPER(last_name) AS Nom,
-     first_name AS First_name,
+     first_name AS Prénom,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Janu,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Janv,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Febr,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Fevr,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Marc,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mars,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Apri,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Avri,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS May,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mai,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS June,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juin,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS July,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juil,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Augu,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Aout,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Sept,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Sept,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Octo,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Oct,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Novo,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Nov,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Dece,
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND ( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Dece,
-     CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+     sexa2HoursMinute( SUM( duration ) ) AS Total
-  FROM flight
+FROM flight
-    LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
+LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
-    LEFT JOIN person a ON a.id=fp.pilot_id
+LEFT JOIN person a ON a.id=fp.pilot_id
-    LEFT JOIN instructor i ON i.id = a.id
+WHERE flight.airborne=0
-   WHERE i.id = fp.pilot_id
+   AND a.activated=1
-    AND fp.num = 1
+  AND fp.num = 1
-    AND YEAR( start_date ) = $year
+  AND YEAR( start_date ) = $year
-   GROUP BY a.id
+   AND ( flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId' )
-UNION
+GROUP BY a.id
-SELECT  "Sum per","month",
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.num = 1),
+UNION
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.num = 1),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.num = 1),
+SELECT  "Sum per","month",
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.num = 1),
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Janv,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.num = 1),
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Fevr,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.num = 1),
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mars,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.num = 1),
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Avri,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.num = 1),
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Mai,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.num = 1),
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juin,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.num = 1),
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Juil,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.num = 1),
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Aout,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.num = 1),
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Sept,
-  CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Oct,
-  FROM flight
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Nov,
-    LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
+    (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.num = 1 AND( flight.flight_type_id & '$flightTypeId' != 0 OR '-'='$flightTypeId' ) ) AS Dece,
-    LEFT JOIN person a ON a.id=fp.pilot_id
+    sexa2HoursMinute( SUM( duration ) ) AS Total
-    LEFT JOIN instructor i ON i.id = a.id
+FROM flight
-   WHERE i.id = fp.pilot_id
+LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
-    AND fp.num = 1
+LEFT JOIN person a ON a.id=fp.pilot_id
-    AND YEAR( start_date ) = $year
+WHERE flight.airborne=0
-  UNION
+   AND a.activated=1
-   SELECT  "Cumulative","per month",
+  AND fp.num = 1
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 1 AND flight_pilot.num = 1),
+  AND YEAR( start_date ) = $year
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 2 AND flight_pilot.num = 1),
+   AND ( flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId' )</sql>
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 3 AND flight_pilot.num = 1),
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 4 AND flight_pilot.num = 1),
+==Flights hours following nationality==
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 5 AND flight_pilot.num = 1),
+   <SQL>SELECT
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 6 AND flight_pilot.num = 1),
+     nationality.label,
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 7 AND flight_pilot.num = 1),
+     CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 -   FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 8 AND flight_pilot.num = 1),
+   FROM flight , flight_pilot fp, person a, nationality
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 9 AND flight_pilot.num = 1),
+   WHERE flight.id = fp.flight_id
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 10 AND flight_pilot.num = 1),
+     AND fp.pilot_id = a.id
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 11 AND flight_pilot.num = 1),
+     AND a.nationality = nationality.code
-  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 12 AND flight_pilot.num = 1),
+   GROUP BY nationality.code</SQL>
-  CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
-  FROM flight
+==Flights hours total per flight type per month==
-     LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
-     LEFT JOIN person a ON a.id=fp.pilot_id
+<sql>SELECT
-     LEFT JOIN instructor i ON i.id = a.id
+    flight_type.name AS Type_vol,
-  WHERE i.id = fp.pilot_id
+    flight_pilot_DC.DC,
-     AND fp.num = 1
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu,
-    AND YEAR( start_date ) = $year</sql>
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 6, duration, 0 ) ) ) AS June,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 7, duration, 0 ) ) ) AS July,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 8, duration, 0 ) ) ) AS Augu,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 9, duration, 0 ) ) ) AS Sept,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 10, duration, 0 ) ) ) AS Octo,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 11, duration, 0 ) ) ) AS Nove,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 12, duration, 0 ) ) ) AS Dece,
+    sexa2HoursMinute( SUM( duration ) ) AS Total
+FROM flight
+LEFT JOIN flight_type ON flight_type.id & flight.flight_type_id
+LEFT JOIN (
+    SELECT flight_id, IF( MAX(num) = 1, 'Double', 'Solo' ) AS DC
+    FROM flight_pilot
+    WHERE flight_pilot.num <= 1
+    GROUP BY flight_id
+) AS flight_pilot_DC ON flight.id=flight_pilot_DC.flight_id
+WHERE YEAR(start_date) = $year
+  AND airborne = 0
+GROUP BY flight_type.id, flight_pilot_DC.DC
+HAVING (Name <> 'Instruction' OR flight_pilot_DC.DC <> 'Double')
+ORDER BY order_num ASC, DC DESC</sql>
+==Flights hours : less than 21 years, more than 21 years, male, female, for a given 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.
+<sql>SELECT Sex, Young, Instruction,
+       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
+        IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex',
+        IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
+        IF ((SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'Instruction') AS Instruction,
+        flight.duration,
+        resource_type.category
+     FROM flight_pilot fp
+    LEFT JOIN flight ON fp.flight_id=flight.id
+     LEFT JOIN person ON person.id=fp.pilot_id
+     LEFT JOIN profile ON person.profile&profile.id
+    LEFT JOIN resource ON (resource.id=flight.aircraft_id)
+    LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
+     WHERE YEAR( start_date )=$year
+      AND fp.num=0
+      AND flight.airborne = 0
+      AND ( profile.id IN ($profileId) OR '-' IN ($profileId) )
+      AND person.activated=1
+    GROUP BY flight.id
+) AS tmp_stat
+GROUP BY Sex, Young, Instruction WITH ROLLUP</sql>
 ==Computation of flight time per month of "local flight"==
@@ Line 622: / Line 984: @@
 LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
 LEFT JOIN resource ON resource.id = aircraft.id
-WHERE YEAR(start_date)= $year  AND resource.virtual = 0 AND departure_location_id = arrival_location_id AND duration < 90*600
+WHERE YEAR(start_date)= $year AND departure_location_id = arrival_location_id AND duration < 90*600
-GROUP BY order_num</sql>
+GROUP BY resource.id</sql>
 ==Computation of non-"local flight" time per month==
@@ Line 679: / Line 1,041: @@
 LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
 LEFT JOIN resource ON resource.id = aircraft.id
-WHERE YEAR(start_date)= $year  AND resource.virtual = 0  AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)
+WHERE YEAR(start_date)= $year AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)
-GROUP BY order_num</sql>
+GROUP BY resource.id</sql>
+==Stats all flight hours per month per year==
+<sql>SELECT YEAR(start_date) AS Year, 'Monthly' AS Type,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 6, duration, 0 ) ) ) AS June,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 7, duration, 0 ) ) ) AS July,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 8, duration, 0 ) ) ) AS Augu,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 9, duration, 0 ) ) ) AS Sept,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 10, duration, 0 ) ) ) AS Octo,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 11, duration, 0 ) ) ) AS Nove,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 12, duration, 0 ) ) ) AS Dece,
+    sexa2HoursMinute( SUM( duration ) ) AS Total
+FROM flight
+LEFT JOIN resource ON resource.id = flight.aircraft_id
+GROUP BY Year
+UNION
+SELECT YEAR(start_date) AS Year, 'Accumulation' AS Type,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 1, duration, 0 ) ) ) AS Janu,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 2, duration, 0 ) ) ) AS Febr,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 3, duration, 0 ) ) ) AS Marc,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 4, duration, 0 ) ) ) AS Apri,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 5, duration, 0 ) ) ) AS May,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 6, duration, 0 ) ) ) AS June,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 7, duration, 0 ) ) ) AS July,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 8, duration, 0 ) ) ) AS Augu,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 9, duration, 0 ) ) ) AS Sept,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 10, duration, 0 ) ) ) AS Octo,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 11, duration, 0 ) ) ) AS Nove,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 12, duration, 0 ) ) ) AS Dece,
+    sexa2HoursMinute( SUM( duration ) ) AS Total
+FROM flight
+LEFT JOIN resource ON resource.id = flight.aircraft_id
+GROUP BY Year
+ORDER BY Type DESC, Year</sql>
+==Flight hours total per resource per flight type between two dates==
+* Variable '''$startDate''' should be defined first and should be of '''Date and time''' value type.
+* Variable '''$endDate''' should be defined first and should be of '''Date and time''' value type.
+<sql>[OF_DYNAMIC_SQL]
+SELECT CONCAT(
+    'SELECT resource.name AS Nom, ',
+    GROUP_CONCAT(
+        CONCAT(
+            'sexa2HoursMinute( SUM( IF( tmp_flight.flight_type_id=',
+            flight_type.id,
+            ', tmp_flight.total_duration, 0 ) ) )',
+            ' AS \'',
+            flight_type.name,
+            '\''
+        )
+    ),
+    ', sexa2HoursMinute( SUM( tmp_flight.total_duration ) ) AS Total
+    FROM resource
+    LEFT JOIN
+    (
+        SELECT flight.aircraft_id, flight_type.id AS flight_type_id, SUM(duration) as total_duration
+        FROM flight
+        LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
+        WHERE flight.start_date >= \'$startDate\' AND flight.start_date <= \'$endDate\' AND flight.airborne = 0
+        GROUP BY flight.aircraft_id, flight_type.id
+    ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
+    WHERE tmp_flight.aircraft_id IS NOT NULL
+    GROUP BY resource.id
+    UNION
+    SELECT \'Total\', ',
+    GROUP_CONCAT(
+        CONCAT(
+            'sexa2HoursMinute( SUM( IF( tmp_flight.flight_type_id=',
+            flight_type.id,
+            ', tmp_flight.total_duration, 0 ) ) )',
+            ' AS \'',
+            flight_type.name,
+            '\''
+        )
+    ),
+    ', sexa2HoursMinute( SUM( tmp_flight.total_duration ) ) AS Total
+    FROM resource
+    LEFT JOIN
+    (
+        SELECT flight.aircraft_id, flight_type.id AS flight_type_id, SUM(duration) as total_duration
+        FROM flight
+        LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
+        WHERE flight.start_date >= \'$startDate\' AND flight.start_date <= \'$endDate\' AND flight.airborne = 0
+        GROUP BY flight.aircraft_id, flight_type.id
+    ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
+    WHERE tmp_flight.aircraft_id IS NOT NULL
+    ORDER BY 1'
+)
+FROM flight_type
+WHERE flight_type.activated=1</sql>
+=Flight reports=
+==Aircraft(s) which no flight were done for at least 30 day(s)==
+<sql>SELECT resource.id, resource.name
+FROM flight
+LEFT JOIN resource ON resource.id = flight.aircraft_id
+WHERE start_date < ( NOW() - INTERVAL 30 DAY )
+  AND virtual=0 AND activated=1
+GROUP BY resource.id</sql>
+==Detailed flight between two dates==
+Following extrafields are needed
+*startDate and endDate (Type : Date)
+*profileId (Type : dbObject::Profile)
+*occupiedSeat (Type : Integer)
+This report is useful for a group of pilots from a common customer (like french administration DGAC).
+<sql>SELECT
+    (
+        SELECT CONCAT(last_name,' ',first_name)
+        FROM person
+        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
+        WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0
+        GROUP BY flight.id
+    ) AS Left_seat_Pilot_name,
+    (
+        SELECT CONCAT(last_name,' ',first_name)
+        FROM person
+        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
+        WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1
+        GROUP BY flight.id
+    ) AS Right_seat_Pilot_name,
+    flight.start_date,
+    resource.name AS resource,
+    CONCAT(FLOOR(flight.duration/600),':',TIME_FORMAT(SEC_TO_TIME((flight.duration /600 - FLOOR(flight.duration/600))*3600),'%i')) AS flight_duration,
+    (
+        SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
+        FROM flight AS f2
+        LEFT JOIN flight_type ON (f2.flight_type_id & flight_type.id)
+        WHERE f2.id=flight.id
+    ) AS Flight_types,
+    flight.landing_number,
+    SUM(account_entry.debit)-SUM(account_entry.credit) AS amount,
+    flight.comments
+    FROM person
+    LEFT JOIN profile ON person.profile&profile.id
+    LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
+    LEFT JOIN flight ON flight.id=flight_pilot.flight_id
+    LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
+    LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
+    LEFT JOIN account ON account.id=account_entry.account_id
+    LEFT JOIN resource ON resource.id = flight.aircraft_id
+    WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
+    AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
+    OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
+        AND ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND (account.category = 11 OR account.category = 2)
+    GROUP BY flight.id</sql>
+==Detailed flight per month==
+Following extrafields are needed
+*year (Type : Year)
+*month (Type : Integer)
+*profileId (Type : dbObject::Profile)
+*occupiedSeat (Type : Integer)
+This report is useful for a group of pilots from a common customer (like french administration DGAC).
+<sql>SELECT
+    (
+        SELECT CONCAT(last_name,' ',first_name)
+        FROM person
+        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
+        WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0
+        GROUP BY flight.id
+    ) AS Left_seat_Pilot_name,
+    (
+        SELECT CONCAT(last_name,' ',first_name)
+        FROM person
+        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
+        WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1
+        GROUP BY flight.id
+    ) AS Right_seat_Pilot_name,
+    flight.start_date,
+    resource.name AS resource,
+    CONCAT(FLOOR(flight.duration/600),':',TIME_FORMAT(SEC_TO_TIME((flight.duration /600 - FLOOR(flight.duration/600))*3600),'%i')) AS flight_duration,
+    (
+        SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
+        FROM flight AS f2
+        LEFT JOIN flight_type ON (f2.flight_type_id & flight_type.id)
+        WHERE f2.id=flight.id
+    ) AS Flight_types,
+    flight.landing_number,
+    SUM(account_entry.debit)-SUM(account_entry.credit) AS amount,
+    flight.comments
+    FROM person
+    LEFT JOIN profile ON person.profile&profile.id
+    LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
+    LEFT JOIN flight ON flight.id=flight_pilot.flight_id
+    LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
+    LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
+    LEFT JOIN account ON account.id=account_entry.account_id
+    LEFT JOIN resource ON resource.id = flight.aircraft_id
+    WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)='$month'
+    AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
+    OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
+        AND ( profile.id IN ('$profileId') OR '-' IN ('$profileId') ) AND (account.category = 11 OR account.category = 2)
+    GROUP BY flight.id</sql>
+==Flight between two dates==
+Following variables are needed:
+*startDate (Type : Datetime)
+*endDate (Type : Datetime)
+<sql>SELECT flight.start_date AS '_tr(START_DATE)', resource.name AS '_tr(RESOURCE)',
+CONCAT(UPPER(person.last_name),' ',person.first_name) AS '_tr(LEFT_PLACE)',
+    (
+        SELECT CONCAT(UPPER(tmp_person.last_name), ' ', tmp_person.first_name)
+        FROM flight_pilot AS tmp_flight_pilot
+        LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id
+        WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 1
+    ) AS '_tr(RIGHT_PLACE)',
+    (
+        SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
+        FROM flight AS tmp_flight
+        LEFT JOIN flight_type ON (tmp_flight.flight_type_id & flight_type.id)
+        WHERE flight.id=tmp_flight.id
+    ) AS '_tr(FLIGHT_TYPE)',
+    (
+        SELECT location.name
+        FROM location
+        WHERE location.id=flight.departure_location_id
+    ) AS '_tr(DEPARTURE_FIELD)',
+    (
+        SELECT location.name
+        FROM location
+        WHERE location.id=flight.arrival_location_id
+    ) AS '_tr(ARRIVAL_FIELD)',
+    flight.landing_number AS '_tr(FLIGHT_LANDING_NUMBER)',
+sexa2HoursMinute(flight.duration) AS '_tr(DURATION)'
+FROM flight
+LEFT JOIN resource ON resource.id = flight.aircraft_id
+LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
+LEFT JOIN person ON flight_pilot.pilot_id = person.id
+WHERE flight.start_date >= '$startDate' AND flight.start_date <= '$endDate' AND flight_pilot.num = 0</sql>
+==Flight log book==
+<sql>SELECT
+    DATE_FORMAT(start_date, '%d/%m/%Y') AS '_tr(START_DATE)',
+    resource.name AS '_tr(RESOURCE)',
+    CONCAT(last_name, ' ', first_name) AS '_tr(LEFT_PLACE)',
+    (
+        SELECT CONCAT(person.last_name,' ',person.first_name) AS '_tr(RIGHT_PLACE)'
+        FROM flight_pilot tmp2_flight_pilot
+        LEFT JOIN person ON person.id=tmp2_flight_pilot.pilot_id
+        WHERE tmp2_flight_pilot.flight_id=flight_pilot.flight_id AND tmp2_flight_pilot.num=1
+    ) AS '_tr(RIGHT_PLACE)',
+    IF ( (SELECT COUNT(*) FROM flight_pilot tmp_flight_pilot WHERE tmp_flight_pilot.flight_id=flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'S', 'D' ) AS 'Solo_DC',
+    sexa2HoursMinute(flight.duration) AS '_tr(DURATION)',
+    (
+        SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
+        FROM flight AS tmp_flight
+        LEFT JOIN flight_type ON (tmp_flight.flight_type_id & flight_type.id)
+        WHERE tmp_flight.id=flight.id
+    ) AS '_tr(FLIGHT_TYPE)',
+    flight.landing_number AS 'Nombre atterissages',
+    flight.comments AS '_tr(COMMENT)'
+FROM flight_pilot
+LEFT JOIN flight ON flight_pilot.flight_id=flight.id
+LEFT JOIN flight_type ON (flight_type.id & flight.flight_type_id)
+LEFT JOIN person ON person.id=flight_pilot.pilot_id
+LEFT JOIN resource ON resource.id = flight.aircraft_id
+WHERE YEAR( start_date ) = $year
+  AND flight_pilot.num=0
+  AND flight.airborne=0
+GROUP BY flight.id
+ORDER BY CONCAT(last_name, ' ', first_name), start_date</sql>
-==List of flights have landed on a airfield==
+==Flights which have landed elsewhere than the base field==
 Variable $icao shall be defined (dbObject:Location)
 <sql>
@@ Line 702: / Line 1,339: @@
 </sql>
-=Aditionnal queries=
+==Flights with a remark in one or two additional entries==
+* Extra field '''$maintenanceUserComment''' should be defined first and should be of '''Text multi line''' value type and '''Flight''' category.
+* Extra field '''$mechanicAnswer''' should be defined first and should be of '''Text multi line''' value type and '''Flight''' category.
-==User list coordinates==
+<sql>SELECT flight.start_date, resource.name AS Callsign,
+    CONCAT(FLOOR( duration/600),':',TIME_FORMAT(SEC_TO_TIME((duration/600 - FLOOR(duration/600))*3600), "%i")) AS Duration,
-<SQL>SELECT
+    person.last_name AS Nom, person.first_name AS "First name",
-	last_name,
+    l1.icao_name AS "Departure",
-	first_name,
+    l2.icao_name AS "Arrival",
-	name AS login,
+    maintenanceUserComment.content AS "User remark",
-	email,
+    mechanicAnswer.content AS "Maintenance answer"
-	address,
+FROM flight
-	zipcode,
+LEFT JOIN resource ON resource.id = flight.aircraft_id
-	city AS Ville,
+LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
-	state AS etat_region,
+LEFT JOIN person ON person.id = flight_pilot.pilot_id
-	country AS pays,
+LEFT JOIN location AS l1 ON l1.id = flight.departure_location_id
-	home_phone AS tel_domicile,
+LEFT JOIN location AS l2 ON l2.id = flight.arrival_location_id
-	work_phone AS tel_travail,
+LEFT JOIN extra_field_content AS maintenanceUserComment ON maintenanceUserComment.category_id = flight.id
-	cell_phone AS tel_mobile
+LEFT JOIN extra_field_content AS mechanicAnswer ON mechanicAnswer.category_id = flight.id
-FROM person
+WHERE flight_pilot.num = 0
-WHERE activated=1</SQL>
+AND maintenanceUserComment.extra_field_id = (SELECT extra_field.id FROM extra_field WHERE (extra_field.variable = "maintenanceUserComment"))
+AND mechanicAnswer.extra_field_id = (SELECT extra_field.id FROM extra_field WHERE (extra_field.variable = "mechanicAnswer"))
+AND (maintenanceUserComment.content <> "" OR mechanicAnswer.content <> "")
+AND start_date>DATE_SUB( NOW(), INTERVAL 3 MONTH )
+GROUP BY flight.id;</sql>
-==User list by profile==
+==Flight with their location code (ICAO)==
+<sql>SELECT flight.*, L1.icao_name AS departure_location, L2.icao_name AS arrival_location
-Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.
-<sql>SELECT
-    last_name AS Lastname,
-    first_name AS Firstname,
-    validity_type.name AS Validity,
-    grant_date AS Date,
-    profile.name AS Profile
-FROM person
-LEFT JOIN profile ON (person.profile & profile.id)
-LEFT JOIN validity ON (validity.person_id = person.id)
-LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
-WHERE YEAR(grant_date) >= $year
-  AND validity_type.id = $validityTypeId
-ORDER BY Profile, Lastname, Firstname</sql>
-==Member list by validity==
-<sql>SELECT validity_type.name AS 'Validity',
-    DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date,
-    last_name AS Name,
-    first_name AS Firstname,
-    ident_value AS Comment
-FROM validity_type
-LEFT JOIN validity ON validity.validity_type_id =validity_type.id
-LEFT JOIN person ON person.id=validity.person_id
-WHERE YEAR(grant_date)>$year AND MONTH(grant_date)>=1
-ORDER BY 'Validity', Name, Firstname</sql>
-==Member list by profil==
-Name of profil (i.e. Pilot), shall be adapted to the profil designation that want to be listed
-<sql>SELECT last_name AS Lastname, first_name AS Firstname, p1.name AS Profil
-FROM person AS a1
-LEFT JOIN profile AS p1 ON (a1.profile & p1.id)
-WHERE a1.activated=1 AND p1.name='Pilot'
-ORDER BY last_name,first_name</sql>
-==Payment dispatching==
-  <SQL>SELECT
-  payment_type as Num, payment_type.name as name,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type = Num) AS Janu,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type = Num) AS Febr,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type = Num) AS Marc,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type = Num) AS Apri,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type = Num) AS May,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type = Num) AS June,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type = Num) AS July,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type = Num) AS Augu,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type = Num) AS Sept,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type = Num) AS Octo,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type = Num) AS Nove,
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type = Num) AS Dece,
-  SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total
-  FROM account_entry
-  LEFT JOIN payment_type ON payment_type = payment_type.id
-  WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL
-  GROUP BY payment_type
-  UNION
-  SELECT  'Sum per', 'month',
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type IS NOT NULL),
-  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type IS NOT NULL),
-  SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total
-  FROM account_entry
-  LEFT JOIN payment_type ON payment_type = payment_type.id
-  WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL</SQL>
-==Aircraft(s) which no flight were done for at least 30 day(s)==
-<sql>SELECT resource.id, resource.name
 FROM flight
-LEFT JOIN resource ON resource.id = flight.aircraft_id
+LEFT JOIN location AS L1 ON flight.departure_location_id = L1.id
-WHERE start_date < ( NOW() - INTERVAL 30 DAY )
+LEFT JOIN location AS L2 ON flight.arrival_location_id = L2.id</sql>
-GROUP BY resource.id</sql>
-==Total tank quantity for each tank and for each aircraft type==
+=Fuel/Oil reports=
-<sql>SELECT resource_type.name AS aircraft_type, tank.label AS tank_label, SUM( quantity ) AS total_qty
+==Fuel/Oil consumption ==
-FROM tank
+Unit is: unit of the tank per hour
-LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
+<SQL>SELECT
-LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
+    resource_name AS CallSign,
-LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
+    CASE tank_type
-WHERE YEAR(flight.start_date) = $year
+        WHEN 1 THEN "AVGAS"
-GROUP BY aircraft_type_id, tank_type_id</sql>
+        WHEN 2 THEN "JET A1"
+        WHEN 3 THEN "Oil"
-==Total tank quantity for each tank and for each aircraft==
+        WHEN 4 THEN "SP95"
-<sql>SELECT resource.name AS aircraft_callsign, tank.label AS tank_label, SUM( quantity ) AS total_qty
+        WHEN 5 THEN "SP98"
-FROM tank
+        WHEN 6 THEN "GAZOLE"
-LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
+    END AS Tank,
-LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
+    formatDecimal( CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Janu,
-LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
+    formatDecimal( CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) ) AS Febr,
-LEFT JOIN resource ON (flight.aircraft_id = resource.id)
+    formatDecimal( CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Marc,
-WHERE YEAR(flight.start_date) = $year
+    formatDecimal( CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Apri,
-GROUP BY resource.id, tank_type_id</sql>
+    formatDecimal( CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS May,
+    formatDecimal( CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS June,
+    formatDecimal( CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS July,
+    formatDecimal( CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Augu,
+    formatDecimal( CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Sept,
+    formatDecimal( CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Octo,
+    formatDecimal( CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Nove,
+    formatDecimal( CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) ) AS Dece,
+    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 Sum
+FROM
+    (SELECT resource.name AS resource_name,
+        resource.id AS resource_id,
+        tank.tank_type_id AS tank_type,
+        flight_tank_qty.quantity  AS quantity,
+        MONTH(flight.start_date) AS month_num,
+        (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
+    LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id
+    LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id
+    LEFT JOIN resource ON resource.id = flight.aircraft_id
+    WHERE YEAR(flight.start_date) = $year
+) AS my_table
+GROUP BY resource_name, tank_type</SQL>
 ==Fuel/Oil quantities per tank and aircraft type==
 <sql>SELECT
      aircraft_type_name AS CallSign, tank_label AS Tank,
-     SUM(IF (month_num=1, quantity, 0)) AS Janu,
+     formatDecimal( SUM(IF (month_num=1, quantity, 0)) ) AS Janu,
-     SUM(IF (month_num=2, quantity, 0)) AS Febr,
+     formatDecimal( SUM(IF (month_num=2, quantity, 0)) ) AS Febr,
-     SUM(IF (month_num=3, quantity, 0)) AS Marc,
+     formatDecimal( SUM(IF (month_num=3, quantity, 0)) ) AS Marc,
-     SUM(IF (month_num=4, quantity, 0)) AS Apri,
+     formatDecimal( SUM(IF (month_num=4, quantity, 0)) ) AS Apri,
-     SUM(IF (month_num=5, quantity, 0)) AS May,
+     formatDecimal( SUM(IF (month_num=5, quantity, 0)) ) AS May,
-     SUM(IF (month_num=6, quantity, 0)) AS June,
+     formatDecimal( SUM(IF (month_num=6, quantity, 0)) ) AS June,
-     SUM(IF (month_num=7, quantity, 0)) AS July,
+     formatDecimal( SUM(IF (month_num=7, quantity, 0)) ) AS July,
-     SUM(IF (month_num=8, quantity, 0)) AS Augu,
+     formatDecimal( SUM(IF (month_num=8, quantity, 0)) ) AS Augu,
-     SUM(IF (month_num=9, quantity, 0)) AS Sept,
+     formatDecimal( SUM(IF (month_num=9, quantity, 0)) ) AS Sept,
-     SUM(IF (month_num=10, quantity, 0)) AS Octo,
+     formatDecimal( SUM(IF (month_num=10, quantity, 0)) ) AS Octo,
-     SUM(IF (month_num=11, quantity, 0)) AS Nove,
+     formatDecimal( SUM(IF (month_num=11, quantity, 0)) ) AS Nove,
-     SUM(IF (month_num=12, quantity, 0)) AS Dece,
+     formatDecimal( SUM(IF (month_num=12, quantity, 0)) ) AS Dece,
-     SUM(quantity) AS Sum
+     formatDecimal( SUM(quantity) ) AS Sum
 FROM
 (
@@ Line 868: / Line 1,453: @@
          WHEN 4 THEN "SP95"
          WHEN 5 THEN "SP98"
+        WHEN 6 THEN "GAZOLE"
      END AS Tank,
-     SUM(IF (month_num=1, quantity, 0)) AS Janu,
+     formatDecimal( SUM(IF (month_num=1, quantity, 0)) ) AS Janu,
-     SUM(IF (month_num=2, quantity, 0)) AS Febr,
+     formatDecimal( SUM(IF (month_num=2, quantity, 0)) ) AS Febr,
-     SUM(IF (month_num=3, quantity, 0)) AS Marc,
+     formatDecimal( SUM(IF (month_num=3, quantity, 0)) ) AS Marc,
-     SUM(IF (month_num=4, quantity, 0)) AS Apri,
+     formatDecimal( SUM(IF (month_num=4, quantity, 0)) ) AS Apri,
-     SUM(IF (month_num=5, quantity, 0)) AS May,
+     formatDecimal( SUM(IF (month_num=5, quantity, 0)) ) AS May,
-     SUM(IF (month_num=6, quantity, 0)) AS June,
+     formatDecimal( SUM(IF (month_num=6, quantity, 0)) ) AS June,
-     SUM(IF (month_num=7, quantity, 0)) AS July,
+     formatDecimal( SUM(IF (month_num=7, quantity, 0)) ) AS July,
-     SUM(IF (month_num=8, quantity, 0)) AS Augu,
+     formatDecimal( SUM(IF (month_num=8, quantity, 0)) ) AS Augu,
-     SUM(IF (month_num=9, quantity, 0)) AS Sept,
+     formatDecimal( SUM(IF (month_num=9, quantity, 0)) ) AS Sept,
-     SUM(IF (month_num=10, quantity, 0)) AS Octo,
+     formatDecimal( SUM(IF (month_num=10, quantity, 0)) ) AS Octo,
-     SUM(IF (month_num=11, quantity, 0)) AS Nove,
+     formatDecimal( SUM(IF (month_num=11, quantity, 0)) ) AS Nove,
-     SUM(IF (month_num=12, quantity, 0)) AS Dece,
+     formatDecimal( SUM(IF (month_num=12, quantity, 0)) ) AS Dece,
-     SUM(quantity) AS Sum,
+     formatDecimal( SUM(quantity) ) AS Sum,
-     CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600  AS DECIMAL(10,2) ) AS Consumption
+     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 Consumption
 FROM
      (SELECT resource.name AS resource_name,
@@ Line 897: / Line 1,483: @@
 GROUP BY resource_name, tank_type</SQL>
-==Fuel/Oil consumption ==
+==Total tank quantity for each tank and for each aircraft==
-Unit is: unit of the tank per hour
+<sql>SELECT resource.name AS aircraft_callsign, tank.label AS tank_label, formatDecimal( SUM( quantity ) ) AS total_qty
-<SQL>SELECT
+FROM tank
-    resource_name AS CallSign,
+LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
-    CASE tank_type
+LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
-        WHEN 1 THEN "AVGAS"
+LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
-        WHEN 2 THEN "JET A1"
+LEFT JOIN resource ON (flight.aircraft_id = resource.id)
-        WHEN 3 THEN "Oil"
+WHERE YEAR(flight.start_date) = $year
-        WHEN 4 THEN "SP95"
+GROUP BY resource.id, tank_type_id</sql>
-        WHEN 5 THEN "SP98"
-    END AS Tank,
-    CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Janu,
-    CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Febr,
-    CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS Marc,
-    CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS Apri,
-    CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS May,
-    CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS June,
-    CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS July,
-    CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS Augu,
-    CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS Sept,
-    CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS Octo,
-    CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS Nove,
-    CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS Dece,
-    CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600  AS DECIMAL(10,2) ) AS Sum
-FROM
-    (SELECT resource.name AS resource_name,
-        resource.id AS resource_id,
-        tank.tank_type_id AS tank_type,
-        flight_tank_qty.quantity  AS quantity,
-        MONTH(flight.start_date) AS month_num,
-        (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
-    LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id
-    LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id
-    LEFT JOIN resource ON resource.id = flight.aircraft_id
-    WHERE YEAR(flight.start_date) = $year
-) AS my_table
-GROUP BY resource_name, tank_type</SQL>
-==List of pilots who have flown without required validity==
+==Total tank quantity for each tank and for each aircraft type==
+<sql>SELECT resource_type.name AS aircraft_type, tank.label AS tank_label, formatDecimal( SUM( quantity ) ) AS total_qty
-<sql>SELECT CONCAT(a.last_name, ' ', a.first_name) AS pilot, vt.name AS 'Validity'
+FROM tank
-FROM flight_type_mandatory_validity_type AS ftpvt
+LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
-LEFT JOIN flight_type AS ft ON ft.id = ftpvt.flight_type_id
+LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
-LEFT JOIN flight AS f ON f.flight_type_id & ft.id
+LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
-LEFT JOIN resource AS ai ON ai.id = f.aircraft_id
+WHERE YEAR(flight.start_date) = $year
-LEFT JOIN resource_type AS at ON ai.resource_type_id = at.id
+GROUP BY aircraft_type_id, tank_type_id</sql>
-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</sql>
-==List of pilots who have flown less than X hours during last Y days==
-<sql>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</sql>
-==List of pilots who have flown less than X hours during last Y days on aircraft type Z==
-<sql>
-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</sql>
-==Flight with their location code (ICAO)==
-<sql>SELECT flight.*, L1.icao_name AS departure_location, L2.icao_name AS arrival_location
-FROM flight
-LEFT JOIN location AS L1 ON flight.departure_location_id = L1.id
-LEFT JOIN location AS L2 ON flight.arrival_location_id = L2.id</sql>
-==Pilots without flight in the last 3 months==
-<sql>SELECT
-    CONCAT(a.last_name, ' ', a.first_name) AS pilot,
-    IFNULL((SELECT CONCAT(DATE_FORMAT( f1.start_date, '%d/%m/%Y' ),' ', resource.name,' Durée: ',
-               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=a.id
-            ORDER BY f1.start_date DESC LIMIT 1),'INCONNU') AS "Dernier Vol"
-FROM person AS a
-LEFT JOIN member ON member.id=a.id
-WHERE a.activated = 1 AND member.subscription = '2011-12-31'
-AND a.id NOT IN (
-SELECT au.id
-FROM flight AS f
-LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id
-LEFT JOIN person AS au ON au.id=fp.pilot_id
-WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL 90 DAY AND NOW() GROUP BY au.id HAVING SUM(f.duration)/600 > 0)
-ORDER BY pilot</sql>
 =Error message queries=
 ==Flight without account movement==
@@ Line 1,033: / Line 1,524: @@
 AND flight_pilot.num=0</SQL>
-==list members without an account==
-<SQL>SELECT member.id, person.last_name, person.first_name FROM member
-LEFT JOIN account ON account.owner_id=member.id
-LEFT JOIN person ON person.id=member.id
-WHERE person.activated=1 AND account.category=2 AND account.id IS NULL</SQL>
 == movement without an account==
 <SQL>SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE account_id is null
 SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE flow_id = 'xxxxxx'</SQL>
-=various queries=
+=[[Users-reports-3|Users]]=
-==Get the details (specially the id) of an account according is name==
+[[Users-reports-3|Users exports SQL queries]].
-<SQL>SELECT * FROM `account` WHERE `name` LIKE 'account name to search'</SQL>
-==Get the pilot owner of an account==
-<SQL>SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114</SQL>
-==Get members list with membership ending before a specific date==
-Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.
-<SQL>SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity'
-FROM person
-LEFT JOIN validity ON (validity.person_id = person.id)
-LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
-WHERE validity.grant_date <= '2007-12-31'
-  AND validity_type.id = $validityTypeId</SQL>
-==Get activated user list==
-<sql>SELECT id, last_name, first_name
-FROM person
-WHERE activated=1</sql>

Difference between revisions of "Export generator 3"

Latest revision as of 11:00, 9 February 2018

Contents

Introduction

Extra field

Extra Field creation

Extra Field creation examples

SQL tips and tricks

Return only last entry

Test valid entries

French administration Examples

Flight type : Flight hours total on instruction with specific profile

Flight type : Flight hours total on non-instruction with specific profile

Users with a specific validity without expiration date

Number of movements on based airfield

Number of movements on based airfield on specific profile

Visited airfields

Number of take-off and landings on based airfield

Number of take-off and landings on based airfield on specific profile

List of movements on based airfield

Pilots who have flown without required validity

List of pilots who have flown less than X hours during last Y days

List of pilots who have flown less than X hours during last Y days on aircraft type Z

Pilots without flight in the last 3 months

Number of landings per pilot, per resource

Flight hours without up to date validities

Number of men, women over and under X years for profile P, validity V up to date for year A

Validities in the year : Total of youngs/adults with specific profile

Validities obtained in the year

Accounting reports 3

Booking

Bookings

Cumulated maintenance hours between a period

Maintenance hours per resource on a period

Booking for maintenance ordered by resource and date with name of the responsible

Flight time reports

Flight hours per month

Flight hours total and last recorded counter to the date X

Flight hours total over a 12 months period

Flights hours total per aircraft per year

Flights hours total per aircraft per year and per month (for a flight type)

Flight hours total per pilot

Flight hours total per pilot per profile

Total Flight hours per pilot with total duration and total amount

Flight hours per instructor, month (for a flight type)

Flights hours following nationality

Flights hours total per flight type per month

Flights hours : less than 21 years, more than 21 years, male, female, for a given profile

Computation of flight time per month of "local flight"

Computation of non-"local flight" time per month

Stats all flight hours per month per year

Flight hours total per resource per flight type between two dates

Flight reports

Aircraft(s) which no flight were done for at least 30 day(s)

Detailed flight between two dates

Detailed flight per month

Flight between two dates

Flight log book

Flights which have landed elsewhere than the base field

Flights with a remark in one or two additional entries

Flight with their location code (ICAO)

Fuel/Oil reports

Fuel/Oil consumption

Fuel/Oil quantities per tank and aircraft type

Fuel/Oil sum for each aircraft

Total tank quantity for each tank and for each aircraft

Total tank quantity for each tank and for each aircraft type

Error message queries

Flight without account movement

movement without an account

Users

Navigation menu

Search