Revision as of 18:03, 18 February 2022

1 Introduction
- 1.1 Business field
  - 1.1.1 Business field creation
  - 1.1.2 Business field creation examples
2 SQL tips and tricks
- 2.1 Return only last entry
- 2.2 Test valid entries
3 French administration Examples
4 Accounting
5 Bookings
6 Flight time management
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.

Please note that you can make "public" a report, ie. allows standard users to see and interact with it.

Business field

For more flexibility, business field can be defined by the user.

Two business field types are available:

"external parameters" of following types:
- Date
- Date and time
- Month
- Text string
- Time
- Whole number
- Year
database parameters (dbObject::something or dbObjectMulti::something Value type) advanced options to access to the database field

Business field creation

Go to Menu Admin > Reports > Custom reports > Business Field(s)

In the bottom line add

Fill the name field with a name that will be use within the SQL queries (prefixed with the $ character)
Fill the label field with a name used to describe the parameter as you want it to appear within the export query form.
Choose a Value type (the most common are at the beginning of the list).
Click on "Add"

Then within your SQL query, you may add this parameter which will be replace by the value chosen by the user filling the export form.

If you define a parameter as a database parameters (for example dbObject::Person) then the form will display a combo with the list of users and your parameter will be replace by the id integer of the chosen person.

Business field creation examples

We create a new business field for the current year:

Name: $year
Label: Year
Value type: Year

Parameter Year must be filled in Reports/View form before to call the query (by default current year is filled when you call the page)

We create a new business field to list the reservation of a resource:

Name: $resourceId
Label: Resource
Value type: dbObject::Resource

Then in Admin/Reports we create a new query labeled "Aircraft booking" with the following query:

SELECT * FROM booking WHERE booking.resource_id=$resourceId

To use this report, we just have to select a "Resource" in the Reports/View form, to check "Resource booking" then to click on "View"

SQL tips and tricks

Return only last entry

Example with last entry from variable #1 in variable_value table:

SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC  LIMIT 1

Test valid entries

For business fields that are text inputs, any entry can be submitted and added to SQL query. In order to test valid entries, it is recommended to use the IF statement in the WHERE statement.

Per example, $occupiedSeat can have following values (0, 1 or NULL) :

SELECT *
FROM flight_pilot
WHERE 
(
    IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
    OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num
)

French administration Examples

Flight hours total on instruction with specific activity type/profile

Requirement:

Variable $activityTypeId of dbOjectMulti::ActivityType value type
Variable $profileId of dbOjectMulti::Profile value type
Variable $year of Year value type

SELECT tmp_flight.activity_type_name AS name,
       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion',
       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère',
       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur',
       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM'
FROM (
    SELECT
        flightWithActivityType.activity_type_id,
        flightWithActivityType.activity_type_name,
        flightWithActivityType.duration,
        resource_type.category
    FROM (
        SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
        FROM flight
        LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
        WHERE YEAR(flight.start_date)=$year
          AND flight.airborne=0
          AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) )
    ) AS flightWithActivityType
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1)
    LEFT JOIN (
        SELECT person.*
        FROM person
        LEFT JOIN profile ON person.profile&profile.id
        WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
          AND person.activated=1
        GROUP BY person.id
    ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id)
    LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
    LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
    WHERE personWithProfile.id IS NOT NULL
) AS tmp_flight
GROUP BY tmp_flight.activity_type_id

Flight hours total on non-instruction with specific activity type/profile

Requirement:

Variable $activityTypeId of dbOjectMulti::ActivityType value type
Variable $profileId of dbOjectMulti::Profile value type
Variable $year of Year value type

SELECT tmp_flight.activity_type_name AS name,
       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion',
       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère',
       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur',
       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM'
FROM (
    SELECT
        flightWithActivityType.activity_type_id,
        flightWithActivityType.activity_type_name,
        flightWithActivityType.duration,
        resource_type.category
    FROM (
        SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
        FROM flight
        LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
        WHERE YEAR(flight.start_date)=$year
          AND flight.airborne=0
          AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) )
          AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1)
    ) AS flightWithActivityType
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=0)
    LEFT JOIN (
        SELECT person.*
        FROM person
        LEFT JOIN profile ON person.profile&profile.id
        WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
          AND person.activated=1
        GROUP BY person.id
    ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id)
    LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
    LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
    WHERE personWithProfile.id IS NOT NULL
) AS tmp_flight
GROUP BY tmp_flight.activity_type_id

Users with a specific validity without expiration date

Variable $validityId should be defined first and should be of dbOjectMulti::ValidityType value type.

SELECT person.first_name, person.last_name, validity_type.name AS 'Validite'
FROM validity
LEFT JOIN validity_type ON validity.validity_type_id=validity_type.id
LEFT JOIN person ON person.id=validity.person_id
WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
  AND expire_date IS NULL
  AND person.activated=1

Number of movements on based airfield

Variable $year should be defined first and should be of Year value type.

SELECT SUM(movement) AS 'Movement'
FROM (
    SELECT COUNT(*) AS movement
    FROM flight, structure
    WHERE ( ( flight.departure_icao_id != structure.icao ) OR( flight.arrival_icao_id != structure.icao ) ) AND( flight.departure_icao_id != structure.icao ) AND YEAR(start_date) = $year
    UNION
    SELECT SUM(landing_number) * 2 AS movement
    FROM flight, structure
    WHERE YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao
) AS tmp_movement

Number of movements on based airfield on specific profile

Variable $profileId should be defined first and should be of dbOjectMulti::Profile value type.
Variable $year should be defined first and should be of Year value type.

SELECT SUM(movement) AS 'Movement'
 FROM (
     SELECT COUNT(*) AS movement
     FROM structure, flight
     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
     LEFT JOIN (
         SELECT person.*
         FROM person
         LEFT JOIN profile ON person.profile&profile.id
         WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
           AND person.activated=1
         GROUP BY person.id
     ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
     WHERE (
         ( flight.departure_icao_id != structure.icao)
         OR
         ( flight.arrival_icao_id != structure.icao)
     )
     AND ( flight.departure_icao_id != structure.icao)
     AND YEAR(flight.start_date) = 2019
     AND personWithProfile.id IS NOT NULL
     UNION
SELECT SUM(flight.landing_number)*2 AS movement
     FROM structure, flight
     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
     LEFT JOIN (
         SELECT person.*
         FROM person
         LEFT JOIN profile ON person.profile&profile.id
         WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
           AND person.activated=1
         GROUP BY person.id
     ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
     WHERE YEAR(flight.start_date) = $year
     AND flight.departure_icao_id = structure.icao
     AND flight.arrival_icao_id = structure.icao
     AND personWithProfile.id IS NOT NULL
 ) AS tmp_movement

Number of take-off and landings on based airfield

SELECT SUM(flight.landing_number) * 2 AS nb_mouvement
FROM flight, structure
WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year

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

Variable $profileId should be defined first and should be of dbOjectMulti::Profile value type.
Variable $year should be defined first and should be of Year value type.

SELECT SUM(flight.landing_number) * 2 AS 'Movement'
FROM structure, flight
LEFT JOIN flight_pilot ON ( flight_pilot.flight_id = flight.id )
LEFT JOIN (
    SELECT person.*
    FROM person
    LEFT JOIN profile ON person.profile & profile.id
    WHERE (profile.id IN($profileId) OR '-' IN($profileId)) AND person.activated = 1
    GROUP BY person.id
) AS personWithProfile ON ( personWithProfile.id = flight_pilot.pilot_id AND flight_pilot.num = 0 )
WHERE personWithProfile.id IS NOT NULL AND YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao

List of movements on based airfield

SELECT location.icao_name AS ICAO, location.name AS NAME, COUNT(location.icao_name) AS nb_flight
FROM structure, flight
LEFT JOIN location ON flight.departure_icao_id = location.icao_name
WHERE ( flight.departure_icao_id = structure.icao ) OR( flight.arrival_icao_id = structure.icao ) AND departure_icao_id != arrival_icao_id AND YEAR(start_date) = $year
GROUP BY icao_name
ORDER BY nb_flight DESC

User flying without validity

SELECT
    CONCAT(person.last_name, ' ', person.first_name) AS _tr(PILOT),
    validity_type.name AS _tr(VALIDITY)
FROM flight_type_mandatory_validity_type
LEFT JOIN activity_type ON activity_type.id = flight_type_mandatory_validity_type.activity_type_id
LEFT JOIN flight ON flight.activity_type_id & activity_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 AND validity.is_current_validity = 1
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
  AND validity_type.activated = 1
GROUP BY person.id, validity_type.id
ORDER BY `_tr(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 X months

Variable $numberMonth 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($numberMonth='', 1, $numberMonth*30) DAY AND NOW()
    GROUP BY person2.id HAVING SUM(f.duration)/600 > 0
)
ORDER BY person.last_name, person.first_name

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

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

SELECT
    IF( personWithProfile.sex=0, _tr(SEX_MALE_INITIAL), IF( personWithProfile.sex=1, _tr(SEX_FEMALE_INITIAL), _tr(UNDEFINED)) ) AS _tr(SEX),
    IF( ( $year - YEAR(birthdate) >= $age ) , _tr(YES), _tr(NO)) AS _tr(ADULT),
    COUNT(DISTINCT personWithProfile.id) AS _tr(NUMBER)  
FROM (
    SELECT person.*
    FROM person
    LEFT JOIN profile ON person.profile&profile.id
    WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) )
      AND person.activated=1
) AS personWithProfile
LEFT JOIN validity ON personWithProfile.id = validity.person_id AND validity.is_current_validity = 1
LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
WHERE ((validity_type.id IN ($validityTypeId) AND validity.expire_date >= '$year-12-31') OR '-' IN ($validityTypeId))
  AND validity_type.activated = 1
GROUP BY `_tr(SEX)`, `_tr(ADULT)`

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

Variable $year

SELECT
     DATE_FORMAT(start_date, '%d %m %Y' ) AS _tr(DATE),
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_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),
     activity_type.name AS _tr(ACTIVITY_TYPE),
     validity_type.name AS _tr(VALIDITY)
FROM flight
LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.activity_type_id & flight.activity_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 AND validity.is_current_validity = 1
LEFT JOIN resource ON flight.aircraft_id = resource.id
LEFT JOIN activity_type ON flight_type_mandatory_validity_type.activity_type_id = activity_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
  AND validity_type.activated = 1
ORDER BY flight.start_date, `_tr(FULL_NAME)`, validity_type.name

Number of men, women over and under 21 years for profile X, validity Y up to date for year Z

Variable $age should be of integer value type.
Variable $profileId should be of dbOjectMulti::Profile value type.
Variable $validityTypeId should be of dbObjectMulti::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

Total hours by activities and resource categories for an instructor for one year

Variable $personId should be defined first and should be of dbOjectMulti::Person 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 _tr(AIRCRAFT_CATEGORY),
       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS _tr(HELICOPTER_CATEGORY),
       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS _tr(GLIDER_CATEGORY),
       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS _tr(ULTRA_LIGHT_CATEGORY),
       sexa2HoursMinute(SUM(IF(category=9, duration, 0))) AS _tr(CLASSROOM_CATEGORY)
FROM (
    SELECT
        flightWithActivityType.activity_type_id,
        flightWithActivityType.activity_type_name,
        flightWithActivityType.duration,
        resource_type.category
    FROM (
        SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
        FROM flight
        LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
        LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1)
        WHERE YEAR(flight.start_date)=$year
          AND flight.airborne=0 AND flight_pilot.pilot_id=$personId
    ) AS flightWithActivityType
    LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
    LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
) AS tmp_flight
GROUP BY tmp_flight.activity_type_id

Validity year young specific profile: Total of youngs/adults with specific profile

Variable $profileId should be defined first and should be of dbOjectMulti::Profile value type.
Variable $year should be defined first and should be of Year value type.
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 '_tr(VALIDITY)',
    SUM( IF( ( $year - YEAR(personWithProfile.birthdate) < 21 ), 1, 0 ) ) AS '_tr(YOUNG)',
    SUM( IF( ( $year - YEAR(personWithProfile.birthdate) >= 21 ), 1, 0 ) ) AS '_tr(ADULT)'
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 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 (validity.person_id=personWithProfile.id)
WHERE YEAR(validity.grant_date)=$year
  AND personWithProfile.id IS NOT NULL
  AND validity_type.activated = 1
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 dbOjectMulti::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

Visited airfields

SELECT location.icao_name AS ICAO, location.name AS NAME, COUNT(location.icao_name) AS Visit
FROM structure, flight
LEFT JOIN location ON ( flight.departure_icao_id = location.icao_name )
WHERE ( ( flight.departure_icao_id != structure.icao ) OR ( flight.arrival_icao_id != structure.icao ) ) AND YEAR(start_date) = $year
GROUP BY location.icao_name
ORDER BY Visit
DESC

Visited outsider airfield

SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS nb_visite
FROM structure, flight
LEFT JOIN location ON ( flight.departure_icao_id = location.icao_name )
WHERE ( ( flight.departure_icao_id !=structure.icao ) OR( flight.arrival_icao_id !=structure.icao ) ) AND YEAR(start_date) = $year
GROUP BY location.icao_name
ORDER BY nb_visite DESC

Young from this year

SELECT
    CONCAT( '[LINK=index.php[QUESTION_MARK]menuAction=admin_add_modify_user&menuParameter=',person.id,'&menuParameterBis=last_name&menuParameter3=', LEFT(person.last_name, 1),']', person.id, '[/LINK]' ) AS _tr(ID),
    person.first_name AS _tr(FIRST_NAME),
    person.last_name AS _tr(LAST_NAME),
    DATE_FORMAT(birthdate ,'%m-%d-%Y') AS _tr(USER_BIRTHDATE), 
    IF ( sex = 0, _tr(SEX_MALE), _tr(SEX_FEMALE) ) AS _tr(USER_SEX)
FROM person
WHERE ($year-YEAR(birthdate))<=$age AND activated=1

Accounting

Bookings

Booking

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.

[OF_DYNAMIC_SQL]
SELECT CONCAT(
'SELECT booking.id AS _tr(ID), DATE_FORMAT(booking.start_date, \'%Y-%m-%d %H:%i\') AS _tr(START_DATE), DATE_FORMAT(booking.end_date, \'%Y-%m-%d %H:%i\') AS _tr(END_DATE),
     (
         SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \')
         FROM booking_activity_type
         LEFT JOIN activity_type ON (booking_activity_type.activity_type_id = activity_type.id)
         WHERE booking_activity_type.booking_id=booking.id
     ) AS _tr(ACTIVITY_TYPE),
 resource.name AS _tr(RESOURCE),
       left_booking_person.full_name AS _tr(LEFT_PLACE), right_booking_person.full_name AS _tr(RIGHT_PLACE), ',
IFNULL(GROUP_CONCAT(
        CONCAT(
            ' (SELECT business_field_content.content FROM business_field_content WHERE booking.id=business_field_content.category_id AND business_field_content.business_field_id=',
            business_field.id,
            ' LIMIT 1) AS \'',
            REPLACE(business_field.label, '\'', '\\\''),
            '\''
        )
    ), '\'_\''),
'FROM booking
LEFT JOIN booking_resource ON (booking.id=booking_resource.booking_id)
LEFT JOIN resource ON (resource.id=booking_resource.resource_id)
LEFT JOIN (
    SELECT booking_person.booking_id, booking_person.person_id, CONCAT(person.last_name, \' \', person.first_name) AS full_name
    FROM booking_person
    LEFT JOIN person ON (person.id=booking_person.person_id)
    WHERE booking_person.place_num=0
      AND person.activated=1
) AS left_booking_person ON (booking.id=left_booking_person.booking_id)
LEFT JOIN (
    SELECT booking_person.booking_id, booking_person.person_id, CONCAT(person.last_name, \' \', person.first_name) AS full_name
    FROM booking_person
    LEFT JOIN person ON (person.id=booking_person.person_id)
    WHERE booking_person.place_num=1
      AND person.activated=1
) AS right_booking_person ON (booking.id=right_booking_person.booking_id)
WHERE booking.start_date >= $startDate AND booking.end_date < $endDate
  AND (booking_resource.resource_id=$resourceId OR \'\'=$resourceId)
  AND (
      left_booking_person.person_id=$personId OR right_booking_person.person_id=$personId OR \'\'=$personId
  )
ORDER BY booking.start_date, booking.end_date'
)
FROM business_field
WHERE business_field.category='BOOKING' AND business_field.variable IN ('bookingEstimatedFlightTime', 'bookingComment')

Cumulated maintenance hours 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 '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 booking_activity_type ON booking.id=booking_activity_type.booking_id
LEFT JOIN activity_type ON activity_type.id=booking_activity_type.activity_type_id
LEFT JOIN booking_resource ON booking.id=booking_resource.booking_id
LEFT JOIN resource ON resource.id = booking_resource.resource_id
LEFT JOIN log ON log.field_value=booking.id
LEFT JOIN journal ON journal.id=log.journal_id
WHERE activity_type.name='Maintenance'
  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 booking_activity_type ON booking.id=booking_activity_type.booking_id
LEFT JOIN activity_type ON activity_type.id=booking_activity_type.activity_type_id
LEFT JOIN booking_resource ON booking.id=booking_resource.booking_id
LEFT JOIN resource ON resource.id = booking_resource.resource_id
LEFT JOIN log ON log.field_value=booking.id
LEFT JOIN journal ON journal.id=log.journal_id
WHERE activity_type.name='Maintenance'
  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 booking_activity_type ON booking.id=booking_activity_type.booking_id
LEFT JOIN activity_type ON activity_type.id=booking_activity_type.activity_type_id
LEFT JOIN booking_resource ON booking.id=booking_resource.booking_id
LEFT JOIN resource ON resource.id = booking_resource.resource_id
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
WHERE activity_type.name='Maintenance'
  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 management

Flight hours total and last recorded counters 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 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 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 an activity type)

Variable $activityTypeId should be defined first and should be of dbOject::ActivityType value type.
Variable $year should be defined first and should be of Year value type.

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.activity_type_id & $activityTypeId OR ''=$activityTypeId)
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.activity_type_id & $activityTypeId OR ''=$activityTypeId)
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.activity_type_id & $activityTypeId OR ''=$activityTypeId)

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

Following extrafields are needed

year (Type : Year)
month (Type : Integer) OR startDate and endDate (Type : Date)
$occupiedSeat (Type : Integer)
profileId (Type : dbObjectMulti::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 _tr(FULL_NAME),
CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS _tr(TOTAL_FLIGHT_TIME),
SUM(account_entry.debit) - SUM(account_entry.credit) AS _tr(TOTAL_AMOUNT_ACTIVITIES) 
FROM flight
RIGHT JOIN flight_pilot ON flight_pilot.flight_id=flight.id
    AND (IF((($occupiedSeat=0) OR ($occupiedSeat='')), 0, -1) = flight_pilot.num
     OR  IF((($occupiedSeat=1) OR ($occupiedSeat='')), 1, -1) = flight_pilot.num)
RIGHT JOIN person ON person.id=flight_pilot.pilot_id
RIGHT JOIN profile ON profile.id&person.profile
    AND ( profile.id IN ($profileId) OR '-' IN ($profileId) )
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 (account.category = 11 OR account.category = 2)
GROUP BY person.id

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

Following extrafields are needed :

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

SELECT 
    CONCAT(UPPER(person.last_name), ' ', person.first_name) AS _tr(LAST_NAME),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JANUARY),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_FEBRUARY),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_MARCH),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_APRIL),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_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)= 6 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JUN),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_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)= 8 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_AUGUST),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_SEPTEMBER),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_OCTOBER),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_NOVEMBER),
    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_DECEMBER),
    sexa2HoursMinute( SUM( duration ) ) AS _tr(TOTAL)
FROM flight 
LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id 
LEFT JOIN person ON person.id=fp.pilot_id 
WHERE flight.airborne=0
  AND person.activated=1
  AND fp.num = 1 
  AND YEAR( start_date ) = $year  
  AND ( flight.activity_type_id & $activityTypeId OR ''=$activityTypeId )
GROUP BY person.id

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 
    activity_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 activity_type ON activity_type.id & flight.activity_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 activity_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 dbOjectMulti::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 flight.aircraft_id AS Num, name AS Immat,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 1 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Janv,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 2 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Fevr,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 3 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Mars,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 4 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Avri,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 5 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Mai,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 6 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Juin,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 7 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Juil,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 8 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Aout,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 9 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Sept,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 10 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Octo,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 11 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Nove,
    ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
    FROM flight
    WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 12 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
    ) AS Dece,
    CONCAT( FLOOR(SUM(duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.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(flight.start_date) = $year AND flight.departure_icao_id = flight.arrival_icao_id AND flight.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 per activity type between date

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 _tr(RESOURCE_NAME), ',
    GROUP_CONCAT(
        CONCAT(
            'sexa2HoursMinute( SUM( IF( tmp_flight.activity_type_id & ',
            activity_type.id,
            ', tmp_flight.duration, 0 ) ) )',
            ' AS ',
            QUOTE(activity_type.name)
        )
    ),
    ', sexa2HoursMinute( SUM( tmp_flight.duration ) ) AS _tr(ALL_ACTIVITY_TYPES)
    FROM resource
    INNER JOIN
    (
        SELECT flight.aircraft_id, flight.activity_type_id, duration
        FROM flight
        WHERE flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight.airborne = 0
    ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
    GROUP BY resource.id'
)
FROM activity_type
WHERE activity_type.activated=1

Flight reports

Activities summary for a person on the right place for a given month

Requirement:

Variable $year of Year value type
Variable $month of Month value type
Variable $personId of dbOjectMulti::Person value type

SELECT $month AS Mois, CONCAT(left_person.last_name, ' ', left_person.first_name) AS 'Personne en première place', activity_type.name AS 'Type d\'activité', sexa2HoursMinute( SUM( IFNULL(duration, 0) ) ) AS 'Durée'
FROM flight
LEFT JOIN activity_type ON (flight.activity_type_id & activity_type.id)
LEFT JOIN flight_pilot AS left_place ON (flight.id=left_place.flight_id AND left_place.num=0)
LEFT JOIN person AS left_person ON (left_place.pilot_id=left_person.id)
LEFT JOIN flight_pilot AS right_place ON (flight.id=right_place.flight_id AND right_place.num=1)
WHERE YEAR(flight.start_date)=$year
  AND MONTH(flight.start_date)=$month
  AND (right_place.pilot_id=$personId OR '-'=right_place.pilot_id='$personId')
GROUP BY left_person.id, activity_type.id
ORDER BY left_person.last_name, left_person.first_name, activity_type.name

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 physical=1 AND activated=1
GROUP BY resource.id

Flight between two dates

Following variables are needed:

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

[OF_DYNAMIC_SQL]
SELECT CONCAT(
'SELECT
     flight.id AS _tr(ID),
     IF(flight.validated=1, _tr(YES), _tr(NO)) AS _tr(VALIDATED),
     flight.start_date AS _tr(START_DATE),
     resource.name AS _tr(RESOURCE),
     (
         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 = 0
     ) 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( activity_type.name SEPARATOR \', \')
         FROM flight AS tmp_flight
         LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
         WHERE flight.id=tmp_flight.id
     ) AS _tr(ACTIVITY_TYPE),
     (
         SELECT location.name
         FROM location
         WHERE location.icao_name=flight.departure_icao_id
     ) AS _tr(DEPARTURE),
     (
         SELECT location.name
         FROM location
         WHERE location.icao_name=flight.arrival_icao_id
     ) AS _tr(ARRIVAL),
     flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER),
     flight.people_onboard AS _tr(FLIGHT_PEOPLE_ON_BOARD),
     sexa2HoursMinute(flight.counter_departure) AS _tr(FLIGHT_COUNTER_DEPARTURE),
     sexa2HoursMinute(flight.counter_arrival) AS _tr(FLIGHT_COUNTER_ARRIVAL),
     sexa2HoursMinute(flight.duration) AS _tr(DURATION_IN_HOURS_AND_MINUTES),
     sexa2HoursHundredths(flight.duration) AS _tr(DURATION_IN_HOURS_AND_HUNDREDTHS),',
     IFNULL(GROUP_CONCAT(
        CONCAT(
            IF(business_field.value_type LIKE ('dbObject::Person%'),
            ' (SELECT CONCAT (UPPER(tmp_person.last_name), \' \', tmp_person.first_name)
            FROM business_field_content
            RIGHT JOIN business_field ON (business_field.id=business_field_content.business_field_id)
            RIGHT JOIN person AS tmp_person ON (tmp_person.id=business_field_content.content)
            WHERE business_field_content.category_id=flight.id AND business_field_content.business_field_id=',
            ' (SELECT GROUP_CONCAT(business_field_content.content SEPARATOR \', \')
            FROM business_field_content
            RIGHT JOIN business_field ON (business_field.id=business_field_content.business_field_id)
            WHERE business_field_content.category_id=flight.id AND business_field_content.business_field_id='),
            business_field.id,
            ') AS \'',
            REPLACE(business_field.label, '\'', '\\\''),
            '\''
        )
    ), '\'_\''),
     ',(
         SELECT SUM(account_entry.debit) - SUM(account_entry.credit)
         FROM account_entry
         RIGHT JOIN account ON (account.id=account_entry.account_id AND (account.category = 11 OR account.category = 2))
         WHERE account_entry.flow_id = flight_account_entry.account_entry_id
     )  AS _tr(AMOUNT)
FROM flight
LEFT JOIN resource ON resource.id = flight.aircraft_id
LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
WHERE flight.start_date >= $startDate AND flight.start_date <= $endDate
ORDER BY flight.start_date;
'
)
FROM business_field
WHERE business_field.category='FLIGHT'

Flight log book

SELECT 
     DATE_FORMAT(start_date, '%d/%m/%Y %H:%i') 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 AS 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 AS 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( activity_type.name SEPARATOR ', ')
         FROM flight AS tmp_flight
         LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
         WHERE tmp_flight.id=flight.id
     ) AS _tr(ACTIVITY_TYPE),
     flight.departure_icao_id AS _tr(DEPARTURE),
     flight.arrival_icao_id AS _tr(ARRIVAL),
     flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER),
     flight.people_onboard AS _tr(FLIGHT_PEOPLE_ON_BOARD),
     business_field_comment.content AS _tr(COMMENT)
 FROM flight_pilot 
 LEFT JOIN flight ON flight_pilot.flight_id=flight.id
 LEFT JOIN activity_type ON (activity_type.id & flight.activity_type_id)
 LEFT JOIN person ON person.id=flight_pilot.pilot_id
 LEFT JOIN resource ON resource.id = flight.aircraft_id
 LEFT JOIN (
     SELECT business_field_content.category_id, business_field_content.content
     FROM business_field_content
     LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id)
     WHERE business_field.variable='activityComment'
 ) AS business_field_comment ON (business_field_comment.category_id=flight.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,
    flight.departure_icao_id AS Départ,
    flight.arrival_icao_id 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
WHERE flight_pilot.num = 0 AND( flight.departure_icao_id LIKE $icao OR flight.arrival_icao_id LIKE $icao )
GROUP BY flight.id

Flights with mechanic remark

Following variables are needed:

Business field $maintenanceUserComment should be defined first and should be of Text multi line value type and Flight category.
Business field $mechanicAnswer should be defined first and should be of Text multi line value type and Flight category.
Business field $resourceId should be defined first and should be of dbObject::Resource value type and Report category.
Business field $numberMonth should be defined first and should be of integer value type and Report category.

SELECT CONCAT(
        '[LINK=index.php[QUESTION_MARK]menuAction=flight_record&menuParameter=',
        flight.id,
        '&menuParameterBis=flight_resource_logbook&menuParameter3=1]',
        flight.start_date,
        '[/LINK]'
    ) AS _tr(DATE),
    resource.name AS _tr(RESOURCE),
    sexa2HoursMinute(flight.duration) AS _tr(DURATION),
    person.last_name AS Nom,
    person.first_name AS _tr(FIRST_NAME),
    departure_location.icao_name AS _tr(DEPARTURE),
    arrival_location.icao_name AS _tr(ARRIVAL),
    maintenanceUserComment.content AS _tr(REMARK),
    mechanicAnswer.content AS _tr(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 departure_location ON departure_location.icao_name = flight.departure_icao_id
LEFT JOIN location AS arrival_location ON arrival_location.icao_name = flight.arrival_icao_id
LEFT JOIN business_field_content AS maintenanceUserComment ON maintenanceUserComment.category_id = flight.id
LEFT JOIN business_field_content AS mechanicAnswer ON mechanicAnswer.category_id = flight.id
WHERE flight_pilot.num = 0 
    AND maintenanceUserComment.business_field_id =( SELECT business_field.id FROM business_field WHERE ( business_field.variable = "maintenanceUserComment" ) )
    AND mechanicAnswer.business_field_id =( SELECT business_field.id FROM business_field WHERE ( business_field.variable = "mechanicAnswer" ) )
    AND ( maintenanceUserComment.content <> "" OR mechanicAnswer.content <> "" )
    AND start_date > DATE_SUB( NOW(), INTERVAL $numberMonth MONTH)
    AND ( (resource.id = $resourceId) OR($resourceId < '1') )
GROUP BY flight.id
ORDER BY resource.name, start_date DESC

Flight with their location code (ICAO)

SELECT flight.*
FROM flight

Flight hours total per person

Following variables are needed:

Variable $endDate of Date and time value type.
Variable $startDate of Date and time value type.

SELECT
     CONCAT(person.last_name,' ',person.first_name) AS  _tr(LAST_NAME),
     sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) ) AS _tr(ALONE),
     sexa2HoursMinute( SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS _tr(LEGEND_INSTRUCTION),
     sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) + SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS _tr(TOTAL)
 FROM person
 LEFT JOIN (
     SELECT 
         pilot_id,
         IF (
             (SELECT COUNT(*) FROM flight_pilot flight_pilot_2 WHERE flight_pilot_2.flight_id=flight_pilot_1.flight_id AND flight_pilot_2.num=1)=0,
             'Solo',
             'DC'
         ) AS Solo_DC,
         SUM(flight.duration) AS sum_duration
     FROM flight_pilot flight_pilot_1
     LEFT JOIN flight ON flight_pilot_1.flight_id=flight.id
     WHERE flight.airborne=0 AND flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight_pilot_1.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 flight_pilot_2 WHERE flight_pilot_2.flight_id=flight_pilot_1.flight_id AND flight_pilot_2.num=1)=0,
             'Solo',
             'DC'
         ) AS Solo_DC,
         SUM(flight.duration) AS sum_duration
     FROM flight_pilot flight_pilot_1 
     LEFT JOIN flight ON flight_pilot_1.flight_id=flight.id
     WHERE flight.airborne=0 AND flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight_pilot_1.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

@@ Line 6: / Line 6: @@
 Please note that you can make "public" a report, ie. allows standard users to see and interact with it.
-==Extra field==
+==Business field==
-For more flexibility, extra field can be defined per the user
-Two extra field types are available:
+For more flexibility, business field can be defined by the user.
+Two business 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
+**Month
+**Text string
+**Time
+**Whole number
+**Year
+*database parameters (dbObject::something or dbObjectMulti::something Value type) advanced options to access to the database field
-===Extra Field creation===
+===[[OF_doc4-fr::Gestion-des-rapports#Ajouter-un-champ-dans-le-formulaire-des-rapports|Business field creation]]===
-*Go to Menu '''Admin > Reports > Structure > Extra Field(s)'''
+*Go to Menu '''Admin > Reports > Custom reports > Business Field(s)'''
 In the bottom line add
 *Fill the '''name''' field with a name that will be use within the SQL queries (prefixed with the $ character)
@@ Line 27: / Line 30: @@
 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.
+If you define a parameter as a database parameters (for example dbObject::Person) then the form will display a combo with the list of users and your parameter will be replace by the id integer of the chosen person.
-===Extra Field creation examples===
+===Business field creation examples===
-We create a new extra field for the current year:
+We create a new business 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)
+Parameter Year must be filled in '''Reports/View''' form before to call the query (by default current year is filled when you call the page)
-We create a new extra field to list the reservation of an aircraft:
+We create a new business field to list the reservation of a resource:
-*Name: $aircraftId
+*Name: $resourceId
-*Label: Aircraft
+*Label: Resource
-*Value type: dbObject::Aircraft
+*Value type: dbObject::Resource
-Then in Admin/Reports/Structure/Criteria we create a new query labeled "Aircraft booking" with the following query:
+Then in '''Admin/Reports''' we create a new query labeled "Aircraft booking" with the following query:
-<sql>SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId</sql>
+<sql>SELECT * FROM booking WHERE booking.resource_id=$resourceId</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"
+To use this report, we just have to select a "Resource" in the '''Reports/View''' form, to check "Resource booking" then to click on "View"
 =SQL tips and tricks=
@@ Line 51: / Line 54: @@
 ==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.
+For business fields that are text inputs, any entry can be submitted and added to SQL query. In order to test valid entries, it is recommended to use the '''IF''' statement in the '''WHERE''' statement.
 Per example, $occupiedSeat can have following values (0, 1 or NULL) :
@@ Line 65: / Line 68: @@
 =French administration Examples=
-==Flight type : Flight hours total on instruction with specific profile==
+==Flight hours total on instruction with specific activity type/profile==
-* Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type.
+Requirement:
-* Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type.
+* Variable '''$activityTypeId''' of '''dbOjectMulti::ActivityType''' value type
-* Variable '''$year''' should be defined first and should be of '''Year''' value type.
+* Variable '''$profileId''' of '''dbOjectMulti::Profile''' value type
+* Variable '''$year''' of '''Year''' value type
-<sql>SELECT name,
+<sql>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',
@@ Line 78: / Line 82: @@
 FROM (
      SELECT
-         flight_type.id,
+         flightWithActivityType.activity_type_id,
-         flight_type.name,
+         flightWithActivityType.activity_type_name,
-         flight.duration,
+         flightWithActivityType.duration,
          resource_type.category
-     FROM flight
+     FROM (
-    LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
+        SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
-     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1)
+        FROM flight
-     LEFT JOIN person ON (person.id=flight_pilot.pilot_id)
+        LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
-     LEFT JOIN resource ON (resource.id=flight.aircraft_id)
+        WHERE YEAR(flight.start_date)=$year
+          AND flight.airborne=0
+          AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) )
+    ) AS flightWithActivityType
+     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1)
+     LEFT JOIN (
+        SELECT person.*
+        FROM person
+        LEFT JOIN profile ON person.profile&profile.id
+        WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
+          AND person.activated=1
+        GROUP BY person.id
+    ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id)
+     LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
      LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
-     WHERE YEAR(flight.start_date)=$year
+     WHERE personWithProfile.id IS NOT NULL
-      AND (flight_type.id='$flightTypeId' OR '-'='$flightTypeId')
-      AND person.activated=1
-      AND (person.profile & '$profileId' OR '-'='$profileId')
 ) AS tmp_flight
-GROUP BY id</sql>
+GROUP BY tmp_flight.activity_type_id</sql>
-==Flight type : Flight hours total on non-instruction with specific profile==
+==Flight hours total on non-instruction with specific activity type/profile==
-* Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type.
+Requirement:
-* Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type.
+* Variable '''$activityTypeId''' of '''dbOjectMulti::ActivityType''' value type
-* Variable '''$year''' should be defined first and should be of '''Year''' value type.
+* Variable '''$profileId''' of '''dbOjectMulti::Profile''' value type
+* Variable '''$year''' of '''Year''' value type
-<sql>SELECT name,
+<sql>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',
@@ Line 108: / Line 123: @@
 FROM (
      SELECT
-         flight_type.id,
+         flightWithActivityType.activity_type_id,
-         flight_type.name,
+         flightWithActivityType.activity_type_name,
-         flight.duration,
+         flightWithActivityType.duration,
          resource_type.category
-     FROM flight
+     FROM (
-    LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
+        SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
-     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=0)
+        FROM flight
-     LEFT JOIN person ON (person.id=flight_pilot.pilot_id)
+        LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
-     LEFT JOIN resource ON (resource.id=flight.aircraft_id)
+        WHERE YEAR(flight.start_date)=$year
+          AND flight.airborne=0
+          AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) )
+          AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1)
+    ) AS flightWithActivityType
+     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=0)
+     LEFT JOIN (
+        SELECT person.*
+        FROM person
+        LEFT JOIN profile ON person.profile&profile.id
+        WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
+          AND person.activated=1
+        GROUP BY person.id
+    ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id)
+     LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
      LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
-     WHERE YEAR(flight.start_date)=$year
+     WHERE personWithProfile.id IS NOT NULL
-      AND (flight_type.id='$flightTypeId' OR '-'='$flightTypeId')
-      AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1)
-      AND person.activated=1
-      AND (person.profile & '$profileId' OR '-'='$profileId')
 ) AS tmp_flight
-GROUP BY id</sql>
+GROUP BY tmp_flight.activity_type_id</sql>
-==List of users with a specific validity without expiration date==
+==Users with a specific validity without expiration date==
-*Variable '''$validityId''' should be defined first and should be of '''dbOject::ValidityType''' value type.
+*Variable '''$validityId''' should be defined first and should be of '''dbOjectMulti::ValidityType''' value type.
-<sql>SELECT person.first_name, person.last_name FROM validity
+<sql>SELECT person.first_name, person.last_name, validity_type.name AS 'Validite'
+FROM validity
+LEFT JOIN validity_type ON validity.validity_type_id=validity_type.id
 LEFT JOIN person ON person.id=validity.person_id
-WHERE validity_type_id=$validityTypeId AND expire_date IS NULL</sql>
+WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
+  AND expire_date IS NULL
+  AND person.activated=1</sql>
 ==Number of movements on based airfield==
@@ Line 138: / Line 167: @@
 <sql>SELECT SUM(movement) AS 'Movement'
-FROM
+FROM (
-(
      SELECT COUNT(*) AS movement
-     FROM flight
+     FROM flight, structure
-    LEFT JOIN location ON (flight.departure_location_id = location.id)
+     WHERE ( ( flight.departure_icao_id != structure.icao ) OR( flight.arrival_icao_id != structure.icao ) ) AND( flight.departure_icao_id != structure.icao ) AND YEAR(start_date) = $year
-     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
+     SELECT SUM(landing_number) * 2 AS movement
-     FROM flight
+     FROM flight, structure
-     LEFT JOIN (
+     WHERE YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao
-        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>
 ==Number of movements on based airfield on specific profile==
-* Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type.
+* Variable '''$profileId''' should be defined first and should be of '''dbOjectMulti::Profile''' value type.
 * Variable '''$year''' should be defined first and should be of '''Year''' value type.
 <sql>SELECT SUM(movement) AS 'Movement'
-FROM
+ FROM (
-(
+     SELECT COUNT(*) AS movement
-    SELECT COUNT(*) AS movement
+     FROM structure, flight
-    FROM flight
+     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
-    LEFT JOIN location ON (flight.departure_location_id = location.id)
+     LEFT JOIN (
-    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
+         SELECT person.*
-    LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0)
+         FROM person
-    WHERE (
+         LEFT JOIN profile ON person.profile&profile.id
-            ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
+         WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
-            OR
+           AND person.activated=1
-            ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
+         GROUP BY person.id
-        )
+     ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
-        AND ( location.id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
+     WHERE (
-        AND person.activated=1
+         ( flight.departure_icao_id != structure.icao)
-        AND (person.profile & '$profileId' OR '-'='$profileId')
+         OR
-        AND YEAR(flight.start_date) = $year
+         ( flight.arrival_icao_id != structure.icao)
-    UNION
+     )
-    SELECT SUM(landing_number)*2 AS movement
+     AND ( flight.departure_icao_id != structure.icao)
-    FROM flight
+     AND YEAR(flight.start_date) = 2019
-    LEFT JOIN (
+     AND personWithProfile.id IS NOT NULL
-        SELECT location.id
+     UNION
-        FROM location
+SELECT SUM(flight.landing_number)*2 AS movement
-        LEFT JOIN club ON location.icao_name=club.icao
+     FROM structure, flight
-        WHERE club.id IS NOT NULL
+     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
-        LIMIT 1
+     LEFT JOIN (
-    ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id)
+         SELECT person.*
-    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
+         FROM person
-    LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0)
+         LEFT JOIN profile ON person.profile&profile.id
-    WHERE person.activated=1
+         WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
-      AND (person.profile & '$profileId' OR '-'='$profileId')
+           AND person.activated=1
-      AND YEAR(flight.start_date) = $year
+         GROUP BY person.id
-      AND structure_icao.id IS NOT NULL
+     ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
-) AS tmp_movement</sql>
+     WHERE YEAR(flight.start_date) = $year
+     AND flight.departure_icao_id = structure.icao
-==Visited airfields==
+     AND flight.arrival_icao_id = structure.icao
+     AND personWithProfile.id IS NOT NULL
-<sql>SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS Visit
+ ) AS tmp_movement</sql>
-FROM flight
-LEFT JOIN location ON (flight.departure_location_id = location.id)
-WHERE (
-        ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
-        OR
-        ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
-    )
-    AND YEAR( start_date ) = $year
-GROUP BY icao_name
-ORDER BY Visit DESC</sql>
 ==Number of take-off and landings on based airfield==
-<SQL>SELECT SUM( landing_number )*2 AS nb_mouvement
+<SQL>SELECT SUM(flight.landing_number) * 2 AS nb_mouvement
-FROM flight f, club c
+FROM flight, structure
-WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao))
+WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year </SQL>
-  AND (f.arrival_location_id  = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao))
-  AND YEAR( start_date ) = $year</SQL>
 ==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 '''$profileId''' should be defined first and should be of '''dbOjectMulti::Profile''' value type.
 * Variable '''$year''' should be defined first and should be of '''Year''' value type.
-<sql>SELECT SUM(landing_number)*2 AS 'Movement'
+<sql>SELECT SUM(flight.landing_number) * 2 AS 'Movement'
-FROM flight
+FROM structure, flight
+LEFT JOIN flight_pilot ON ( flight_pilot.flight_id = flight.id )
 LEFT JOIN (
-     SELECT location.id
+     SELECT person.*
-     FROM location
+     FROM person
-     LEFT JOIN club ON location.icao_name=club.icao
+     LEFT JOIN profile ON person.profile & profile.id
-     WHERE club.id IS NOT NULL
+     WHERE (profile.id IN($profileId) OR '-' IN($profileId)) AND person.activated = 1
-    LIMIT 1
+    GROUP BY person.id
-) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id)
+) AS personWithProfile ON ( personWithProfile.id = flight_pilot.pilot_id AND flight_pilot.num = 0 )
-LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
+WHERE personWithProfile.id IS NOT NULL AND YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao</sql>
-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'
+<sql>SELECT location.icao_name AS ICAO, location.name AS NAME, COUNT(location.icao_name) AS nb_flight
-FROM flight
+FROM structure, flight
-LEFT JOIN resource ON resource.id = flight.aircraft_id
+LEFT JOIN location ON flight.departure_icao_id = location.icao_name
-WHERE ( flight.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao))
+WHERE ( flight.departure_icao_id = structure.icao ) OR( flight.arrival_icao_id = structure.icao ) AND departure_icao_id != arrival_icao_id AND YEAR(start_date) = $year
-  AND (flight.arrival_location_id  = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao))
+GROUP BY icao_name
-  AND YEAR( start_date ) = $year
+ORDER BY nb_flight DESC </sql>
-GROUP BY date,name</sql>
-==List of pilots who have flown without required validity==
+==User flying without validity==
-<sql>SELECT CONCAT(a.last_name, ' ', a.first_name) AS pilot, vt.name AS 'Validity'
+<sql>SELECT
-FROM flight_type_mandatory_validity_type AS ftpvt
+    CONCAT(person.last_name, ' ', person.first_name) AS _tr(PILOT),
-LEFT JOIN flight_type AS ft ON ft.id = ftpvt.flight_type_id
+    validity_type.name AS _tr(VALIDITY)
-LEFT JOIN flight AS f ON f.flight_type_id & ft.id
+FROM flight_type_mandatory_validity_type
-LEFT JOIN resource AS ai ON ai.id = f.aircraft_id
+LEFT JOIN activity_type ON activity_type.id = flight_type_mandatory_validity_type.activity_type_id
-LEFT JOIN resource_type AS at ON ai.resource_type_id = at.id
+LEFT JOIN flight ON flight.activity_type_id & activity_type.id
-LEFT JOIN aircraft_type_validity_type AS atvt ON at.id = atvt.aircraft_type_id
+LEFT JOIN resource ON resource.id = flight.aircraft_id
-LEFT JOIN flight_pilot AS fp ON f.id = fp.flight_id
+LEFT JOIN resource_type ON resource.resource_type_id = resource_type.id
-LEFT JOIN person AS a ON a.id = fp.pilot_id
+LEFT JOIN aircraft_type_validity_type ON resource_type.id = aircraft_type_validity_type.aircraft_type_id
-LEFT JOIN validity_type AS vt ON (vt.id = ftpvt.validity_type_id OR vt.id = atvt.validity_type_id)
+LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id
-WHERE f.airborne = 0
+LEFT JOIN person ON person.id = flight_pilot.pilot_id
-   AND ROW(a.id, vt.id) NOT IN (SELECT person_id, validity_type_id FROM validity)
+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)
-   AND vt.experience_formula IS NULL
+LEFT JOIN validity ON validity_type.id = validity.validity_type_id AND person.id = validity.person_id AND validity.is_current_validity = 1
-GROUP BY a.id, vt.id
+WHERE flight.airborne = 0
-ORDER BY pilot, vt.name</sql>
+   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
+  AND validity_type.activated = 1
+GROUP BY person.id, validity_type.id
+ORDER BY `_tr(PILOT)`, validity_type.name</sql>
 ==List of pilots who have flown less than X hours during last Y days==
@@ Line 302: / Line 304: @@
 ORDER BY pilot</sql>
-==Pilots without flight in the last 3 months==
+==Pilots without flight in the last X months==
+* Variable '''$numberMonth''' should be defined first and should be of '''integer''' value type.
 <sql>SELECT
@@ Line 320: / Line 324: @@
      LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id
      LEFT JOIN person AS person2 ON person2.id=fp.pilot_id
-     WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL 90 DAY AND NOW()
+     WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL IF($numberMonth='', 1, $numberMonth*30) DAY AND NOW()
      GROUP BY person2.id HAVING SUM(f.duration)/600 > 0
 )
 ORDER BY person.last_name, person.first_name</sql>
+==Total user per age profile validity: Number of men, women over and under X years for profile P, validity V up to date for year A==
+* Variable '''$age''' should be of '''integer''' value type.
+* Variable '''$profileId''' should be of '''dbOjectMulti::Profile''' value type.
+* Variable '''$validityTypeId''' should be of '''dbObjectMulti::validityType''' value type.
+* Variable '''$year''' should be of '''Year''' value type.
+<sql>SELECT
+    IF( personWithProfile.sex=0, _tr(SEX_MALE_INITIAL), IF( personWithProfile.sex=1, _tr(SEX_FEMALE_INITIAL), _tr(UNDEFINED)) ) AS _tr(SEX),
+    IF( ( $year - YEAR(birthdate) >= $age ) , _tr(YES), _tr(NO)) AS _tr(ADULT),
+    COUNT(DISTINCT personWithProfile.id) AS _tr(NUMBER)
+FROM (
+    SELECT person.*
+    FROM person
+    LEFT JOIN profile ON person.profile&profile.id
+    WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) )
+      AND person.activated=1
+) AS personWithProfile
+LEFT JOIN validity ON personWithProfile.id = validity.person_id AND validity.is_current_validity = 1
+LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
+WHERE ((validity_type.id IN ($validityTypeId) AND validity.expire_date >= '$year-12-31') OR '-' IN ($validityTypeId))
+  AND validity_type.activated = 1
+GROUP BY `_tr(SEX)`, `_tr(ADULT)`</sql>
 ==Number of landings per pilot, per resource==
@@ Line 402: / Line 429: @@
 ==Flight hours without up to date validities==
-<sql>SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS Date,last_name AS Nom,
-first_name AS Prénom,
+*Variable '''$year'''
-IF ((SELECT COUNT(*)
-    FROM flight_pilot fp2
+<sql>SELECT
-    WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'DC') AS DC,
+     DATE_FORMAT(start_date, '%d %m %Y' ) AS _tr(DATE),
-resource.name AS Immat,
+     CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
-CONCAT(FLOOR( duration /600),
+     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),
-	 TIME_FORMAT(SEC_TO_TIME(( duration /600 - FLOOR( duration /600))*3600),'%i')) AS Tdv,
+     sexa2HoursMinute(duration) AS _tr(DURATION),
-ft.name As Type_Vol,
+     activity_type.name AS _tr(ACTIVITY_TYPE),
-vt.name AS Qualif
+     validity_type.name AS _tr(VALIDITY)
 FROM flight
-LEFT JOIN flight_type_mandatory_validity_type fq ON fq.flight_type_id & flight.flight_type_id
+LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.activity_type_id & flight.activity_type_id
-LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
+LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id
-LEFT JOIN person p ON p.id = fp.pilot_id
+LEFT JOIN person ON flight_pilot.pilot_id = person.id
-LEFT JOIN validity_type vt ON vt.id = fq.validity_type_id
+LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id
-LEFT JOIN validity v ON v.validity_type_id = fq.validity_type_id
+LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id AND validity.is_current_validity = 1
-LEFT JOIN resource ON resource.id = flight.aircraft_id
+LEFT JOIN resource ON flight.aircraft_id = resource.id
-LEFT JOIN flight_type ft ON ft.id = fq.flight_type_id
+LEFT JOIN activity_type ON flight_type_mandatory_validity_type.activity_type_id = activity_type.id
-WHERE YEAR(start_date) = $year AND fp.num = 0 AND v.person_id = fp.pilot_id AND flight.start_date > v.expire_date
+WHERE YEAR(start_date) = $year
-ORDER BY Nom</sql>
+  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
+  AND validity_type.activated = 1
+ORDER BY flight.start_date, `_tr(FULL_NAME)`, validity_type.name</sql>
 ==Number of men, women over and under 21 years for profile X, validity Y up to date for year Z==
-* Variable '''$profileId''' should be of '''dbOject::Profile''' value type.
+* Variable '''$age''' should be of '''integer''' value type.
-* Variable '''$validityTypeId''' should be of '''dbObject::validityType''' value type.
+* Variable '''$profileId''' should be of '''dbOjectMulti::Profile''' value type.
+* Variable '''$validityTypeId''' should be of '''dbObjectMulti::validityType''' value type.
 * Variable '''$year''' should be of '''Year''' value type.
 <sql>SELECT
      IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex',
-     IF( ( $year - YEAR(birthdate) >= 21 ) , 'Yes', 'No') AS 'Adult',
+     IF( ( $year - YEAR(birthdate) >= '$age' ) , 'Yes', 'No') AS 'Adult',
      COUNT(DISTINCT person.id) AS NUMBER
 FROM person
@@ Line 438: / Line 472: @@
 WHERE activated=1
    AND (profile & '$profileId' OR '-'='$profileId')
-   AND (validity_type.id='$validityTypeId' OR '-'='$validityTypeId')
+   AND (
-  AND validity.expire_date >= '$year-12-31'
+      (validity_type.id='$validityTypeId' AND validity.expire_date >= '$year-12-31')
+      OR
+      '-'='$validityTypeId'
+  )
 GROUP BY Sex, Adult</sql>
-==Validities in the year : Total of youngs/adults with specific profile==
+==Total hours by activities and resource categories for an instructor for one year==
+* Variable '''$personId''' should be defined first and should be of '''dbOjectMulti::Person''' value type.
+* Variable '''$year''' should be defined first and should be of '''Year''' value type.
+<sql>SELECT tmp_flight.activity_type_name AS name,
+       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS _tr(AIRCRAFT_CATEGORY),
+       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS _tr(HELICOPTER_CATEGORY),
+       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS _tr(GLIDER_CATEGORY),
+       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS _tr(ULTRA_LIGHT_CATEGORY),
+       sexa2HoursMinute(SUM(IF(category=9, duration, 0))) AS _tr(CLASSROOM_CATEGORY)
+FROM (
+    SELECT
+        flightWithActivityType.activity_type_id,
+        flightWithActivityType.activity_type_name,
+        flightWithActivityType.duration,
+        resource_type.category
+    FROM (
+        SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
+        FROM flight
+        LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
+        LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1)
+        WHERE YEAR(flight.start_date)=$year
+          AND flight.airborne=0 AND flight_pilot.pilot_id=$personId
+    ) AS flightWithActivityType
+    LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
+    LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
+) AS tmp_flight
+GROUP BY tmp_flight.activity_type_id</sql>
+==Validity year young specific profile: Total of youngs/adults with specific profile==
-* Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' value type.
+* Variable '''$profileId''' should be defined first and should be of '''dbOjectMulti::Profile''' value type.
 * Variable '''$year''' should be defined first and should be of '''Year''' value type.
 * Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need.
 <sql>SELECT
-     validity_type.name AS 'Validity',
+     validity_type.name AS '_tr(VALIDITY)',
-     SUM( IF( ( $year - YEAR(person.birthdate) < 21 ), 1, 0 ) ) AS 'Young',
+     SUM( IF( ( $year - YEAR(personWithProfile.birthdate) < 21 ), 1, 0 ) ) AS '_tr(YOUNG)',
-     SUM( IF( ( $year - YEAR(person.birthdate) >= 21 ), 1, 0 ) ) AS 'Adult'
+     SUM( IF( ( $year - YEAR(personWithProfile.birthdate) >= 21 ), 1, 0 ) ) AS '_tr(ADULT)'
 FROM validity_type
-LEFT JOIN validity ON (validity.validity_type_id=validity_type.id)
+LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
-LEFT JOIN person ON (person.id=validity.person_id)
+LEFT JOIN (
+    SELECT person.*
+    FROM person
+    LEFT JOIN profile ON person.profile&profile.id
+    WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) )
+      AND person.activated=1
+    GROUP BY person.id
+) AS personWithProfile ON (validity.person_id=personWithProfile.id)
 WHERE YEAR(validity.grant_date)=$year
-   AND person.activated=1
+   AND personWithProfile.id IS NOT NULL
-   AND (person.profile & '$profileId' OR '-'='$profileId')
+   AND validity_type.activated = 1
 GROUP BY validity_type.id
 ORDER BY validity_type.name</sql>
 ==Validities obtained in the year==
-*Variable '''$validityId''' should be defined first and should be of '''dbOject::ValidityType''' value type.
+*Variable '''$validityId''' should be defined first and should be of '''dbOjectMulti::ValidityType''' value type.
 *'''21''' value is the age limit to be young. Should be changed according local rules
 <sql>SELECT
      last_name AS Last_name,
      first_name AS First_name,
      IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
-     IF ( (sex = 0), 'Male', 'Female' ) AS Sex, DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, validity_type.name AS 'Validity'
+     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
+FROM person
-    LEFT JOIN validity ON person_id=person.id
+LEFT JOIN validity ON person_id=person.id
-    LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
+LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
-  WHERE YEAR(grant_date)=$year AND (validity_type.id = $validityTypeId)</sql>
+WHERE YEAR(grant_date)=$year
+  AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
+  AND person.activated=1
+ORDER BY last_name, first_name</sql>
-=[[Accounting_exports_4|Accounting]]=
+==Visited airfields==
-=Booking=
+<sql>SELECT location.icao_name AS ICAO, location.name AS NAME, COUNT(location.icao_name) AS Visit
+FROM structure, flight
+LEFT JOIN location ON ( flight.departure_icao_id = location.icao_name )
+WHERE ( ( flight.departure_icao_id != structure.icao ) OR ( flight.arrival_icao_id != structure.icao ) ) AND YEAR(start_date) = $year
+GROUP BY location.icao_name
+ORDER BY Visit
+DESC</sql>
-==Cumulated maintenance hours between a period==
+==Visited outsider airfield==
+<sql>SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS nb_visite
+FROM structure, flight
+LEFT JOIN location ON ( flight.departure_icao_id = location.icao_name )
+WHERE ( ( flight.departure_icao_id !=structure.icao ) OR( flight.arrival_icao_id !=structure.icao ) ) AND YEAR(start_date) = $year
+GROUP BY location.icao_name
+ORDER BY nb_visite DESC</sql>
+==Young from this year==
+<sql>SELECT
+    CONCAT( '[LINK=index.php[QUESTION_MARK]menuAction=admin_add_modify_user&menuParameter=',person.id,'&menuParameterBis=last_name&menuParameter3=', LEFT(person.last_name, 1),']', person.id, '[/LINK]' ) AS _tr(ID),
+    person.first_name AS _tr(FIRST_NAME),
+    person.last_name AS _tr(LAST_NAME),
+    DATE_FORMAT(birthdate ,'%m-%d-%Y') AS _tr(USER_BIRTHDATE),
+    IF ( sex = 0, _tr(SEX_MALE), _tr(SEX_FEMALE) ) AS _tr(USER_SEX)
+FROM person
+WHERE ($year-YEAR(birthdate))<=$age AND activated=1</sql>
+=[[Accounting-exports-4|Accounting]]=
+=Bookings=
+==Booking==
+* 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>[OF_DYNAMIC_SQL]
+SELECT CONCAT(
+'SELECT booking.id AS _tr(ID), DATE_FORMAT(booking.start_date, \'%Y-%m-%d %H:%i\') AS _tr(START_DATE), DATE_FORMAT(booking.end_date, \'%Y-%m-%d %H:%i\') AS _tr(END_DATE),
+     (
+         SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \')
+         FROM booking_activity_type
+         LEFT JOIN activity_type ON (booking_activity_type.activity_type_id = activity_type.id)
+         WHERE booking_activity_type.booking_id=booking.id
+     ) AS _tr(ACTIVITY_TYPE),
+ resource.name AS _tr(RESOURCE),
+       left_booking_person.full_name AS _tr(LEFT_PLACE), right_booking_person.full_name AS _tr(RIGHT_PLACE), ',
+IFNULL(GROUP_CONCAT(
+        CONCAT(
+            ' (SELECT business_field_content.content FROM business_field_content WHERE booking.id=business_field_content.category_id AND business_field_content.business_field_id=',
+            business_field.id,
+            ' LIMIT 1) AS \'',
+            REPLACE(business_field.label, '\'', '\\\''),
+            '\''
+        )
+    ), '\'_\''),
+'FROM booking
+LEFT JOIN booking_resource ON (booking.id=booking_resource.booking_id)
+LEFT JOIN resource ON (resource.id=booking_resource.resource_id)
+LEFT JOIN (
+    SELECT booking_person.booking_id, booking_person.person_id, CONCAT(person.last_name, \' \', person.first_name) AS full_name
+    FROM booking_person
+    LEFT JOIN person ON (person.id=booking_person.person_id)
+    WHERE booking_person.place_num=0
+      AND person.activated=1
+) AS left_booking_person ON (booking.id=left_booking_person.booking_id)
+LEFT JOIN (
+    SELECT booking_person.booking_id, booking_person.person_id, CONCAT(person.last_name, \' \', person.first_name) AS full_name
+    FROM booking_person
+    LEFT JOIN person ON (person.id=booking_person.person_id)
+    WHERE booking_person.place_num=1
+      AND person.activated=1
+) AS right_booking_person ON (booking.id=right_booking_person.booking_id)
+WHERE booking.start_date >= $startDate AND booking.end_date < $endDate
+  AND (booking_resource.resource_id=$resourceId OR \'\'=$resourceId)
+  AND (
+      left_booking_person.person_id=$personId OR right_booking_person.person_id=$personId OR \'\'=$personId
+  )
+ORDER BY booking.start_date, booking.end_date'
+)
+FROM business_field
+WHERE business_field.category='BOOKING' AND business_field.variable IN ('bookingEstimatedFlightTime', 'bookingComment')</sql>
+==Cumulated maintenance hours on a period==
 Following extrafields are needed :
@@ Line 484: / Line 643: @@
 *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'
+<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 booking_activity_type ON booking.id=booking_activity_type.booking_id
+LEFT JOIN activity_type ON activity_type.id=booking_activity_type.activity_type_id
+LEFT JOIN booking_resource ON booking.id=booking_resource.booking_id
+LEFT JOIN resource ON resource.id = booking_resource.resource_id
 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 activity_type.name='Maintenance'
-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 >= $startDate)
-   AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate'))
+   AND (booking.start_date <= LEAST($startDate + INTERVAL 366 DAY, $endDate))
 GROUP BY resource.id
 ORDER BY 1</sql>
@@ Line 506: / Line 668: @@
 *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'
+<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 booking_activity_type ON booking.id=booking_activity_type.booking_id
+LEFT JOIN activity_type ON activity_type.id=booking_activity_type.activity_type_id
+LEFT JOIN booking_resource ON booking.id=booking_resource.booking_id
+LEFT JOIN resource ON resource.id = booking_resource.resource_id
 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 activity_type.name='Maintenance'
-WHERE (booking.slot_type=2)
    AND (log.action='INSERT')
-   AND (log.table_name='booking')
+   AND (log.TABLE_NAME='booking')
    AND (log.field_name='id')
-   AND (booking.start_date >= '$startDate')
+   AND (booking.start_date >= $startDate)
-   AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate'))
+   AND (booking.start_date <= LEAST($startDate + INTERVAL 366 DAY, $endDate))
 ORDER BY 1, 2</sql>
-==List of booking for maintenance ordered by resource and date with name of the responsible==
+==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 booking_activity_type ON booking.id=booking_activity_type.booking_id
+LEFT JOIN activity_type ON activity_type.id=booking_activity_type.activity_type_id
+LEFT JOIN booking_resource ON booking.id=booking_resource.booking_id
+LEFT JOIN resource ON resource.id = booking_resource.resource_id
 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 activity_type.name='Maintenance'
-WHERE (booking.slot_type=2) AND (log.action='INSERT') AND (log.table_name='booking') AND (log.field_name='id')
+  AND (log.action='INSERT')
-AND YEAR(booking.start_date) = $year
+  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 management=
+==Flight hours total and last recorded counters 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 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
@@ Line 576: / Line 762: @@
    GROUP BY year</sql>
-==Flights hours total per aircraft per year and per month (for a flight type)==
+==Flights hours total per aircraft per year and per month (for an activity type)==
-* Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type.
+* Variable '''$activityTypeId''' should be defined first and should be of '''dbOject::ActivityType''' value type.
 * Variable '''$year''' should be defined first and should be of '''Year''' value type.
@@ Line 599: / Line 785: @@
 LEFT JOIN resource ON resource.id = aircraft.id
 WHERE YEAR(start_date)= $year AND airborne = 0
-   AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')
+   AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId)
 GROUP BY resource.id
 UNION
@@ Line 620: / Line 806: @@
 LEFT JOIN resource ON resource.id = aircraft.id
 WHERE YEAR(start_date)= $year AND airborne = 0
-   AND (flight.flight_type_id & '$flightTypeId' OR '-'='$flightTypeId')
+   AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId)
 UNION
 SELECT  "Cumulative","per month",
@@ Line 640: / Line 826: @@
 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>
+   AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId)</sql>
 ==Flight hours total per pilot==
@@ Line 678: / Line 864: @@
      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
+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>
@@ Line 701: / Line 888: @@
    GROUP BY Profil, Nom, Solo_DC</sql>
-==Total Flight hours per pilot with total duration and total amount==
+==Total Flight hours per pilot with total==
 Following extrafields are needed
 *year (Type : Year)
 *month (Type : Integer) '''OR''' startDate and endDate (Type : Date)
 *$occupiedSeat (Type : Integer)
-*profileId (Type : dbObject::Profile)
+*profileId (Type : dbObjectMulti::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,
+<sql>SELECT CONCAT(person.last_name,' ',person.first_name) AS _tr(FULL_NAME),
-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,
+CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS _tr(TOTAL_FLIGHT_TIME),
-SUM(flight_list.montant) AS montant_total_des_vols
+SUM(account_entry.debit) - SUM(account_entry.credit) AS _tr(TOTAL_AMOUNT_ACTIVITIES)
-FROM person
+FROM flight
-JOIN
+RIGHT JOIN flight_pilot ON flight_pilot.flight_id=flight.id
-(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
+    AND (IF((($occupiedSeat=0) OR ($occupiedSeat='')), 0, -1) = flight_pilot.num
-LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
+     OR  IF((($occupiedSeat=1) OR ($occupiedSeat='')), 1, -1) = flight_pilot.num)
-LEFT JOIN flight ON flight.id=flight_pilot.flight_id
+RIGHT JOIN person ON person.id=flight_pilot.pilot_id
+RIGHT JOIN profile ON profile.id&person.profile
+    AND ( profile.id IN ($profileId) OR '-' IN ($profileId) )
 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'
+WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate AND (account.category = 11 OR account.category = 2)
-    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>
-==Flights hours total per instructor==
+==Flight hours per instructor, month (for a flight type)==
-Following extrafields are needed:
+Following extrafields are needed :
 *year (Type : Year)
+*activityTypeId (Type : dbObject::FlightType)
 <sql>SELECT
-    last_name AS Last_name,
+     CONCAT(UPPER(person.last_name), ' ', person.first_name) AS _tr(LAST_NAME),
-    first_name AS First_name,
+     (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JANUARY),
-     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)= 2 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_FEBRUARY),
-  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)= 3 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_MARCH),
-    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)= 4 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_APRIL),
-    LEFT JOIN person a ON a.id=fp.pilot_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)= 5 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_MAY),
-  WHERE fp.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.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JUN),
-    AND YEAR( start_date ) = $year
+    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JULY),
-  GROUP BY a.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)= 8 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_AUGUST),
-  UNION
+    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_SEPTEMBER),
-  SELECT
+    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_OCTOBER),
-    "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)= 11 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_NOVEMBER),
-    $year AS year,
+    (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 = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_DECEMBER),
-    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 _tr(TOTAL)
-  FROM flight,flight_pilot fp
+FROM flight
-  WHERE flight.id = fp.flight_id
+LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
-     AND fp.num = 1
+LEFT JOIN person ON person.id=fp.pilot_id
-    AND YEAR( start_date ) = $year
+WHERE flight.airborne=0
-  GROUP BY year</sql>
+   AND person.activated=1
+   AND fp.num = 1
-==Flights hours total per instructor per year and per month==
+   AND YEAR( start_date ) = $year
-Replace 'Instructor' label by the instructor profile name following the configuration.
+   AND ( flight.activity_type_id & $activityTypeId OR ''=$activityTypeId )
+GROUP BY person.id</sql>
-Following extrafields are needed:
-*year (Type : Year)
-<sql>SELECT
-    UPPER(last_name) AS Last_name,
-    first_name AS First_name,
-  (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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 = a.id AND flight_pilot.num = 1) 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 flight_pilot fp ON fp.flight_id = flight.id
-    LEFT JOIN person a ON a.id=fp.pilot_id
-    LEFT JOIN profile AS p1 ON (a.profile & p1.id)
-    WHERE a.activated=1 AND p1.name='Instructor'
-    AND fp.num = 1
-    AND YEAR( start_date ) = $year
-  GROUP BY a.id
-UNION
-SELECT  "Total","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),
-  (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 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 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 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 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 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 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 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 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 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),
-  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
-    LEFT JOIN profile AS p1 ON (a.profile & p1.id)
-    WHERE a.activated=1 AND p1.name='Instructor'
-    AND fp.num = 1
-    AND YEAR( start_date ) = $year
- UNION
-  SELECT  "Accumulation","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),
-  (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 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 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 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 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 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 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 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 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 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),
-  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
-    LEFT JOIN profile AS p1 ON (a.profile & p1.id)
-    WHERE a.activated=1 AND p1.name='Instructor'
-    AND fp.num = 1
-    AND YEAR( start_date ) = $year</sql>
-==Flight hours per instructor function to flight type==
-*Variable '''$flightTypeId''' should be defined first and should be a '''dbOject::FlightType''' value type.
-<sql>SELECT
-    UPPER(last_name) AS Nom,
-     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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightTypeId != 0) 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 flight_pilot fp ON fp.flight_id = flight.id
-    LEFT JOIN person a ON a.id=fp.pilot_id
-    LEFT JOIN profile AS p1 ON (a.profile & p1.id)
-    WHERE a.activated=1 AND p1.name='Instructor'
-    AND fp.num = 1
-    AND YEAR( start_date ) = $year
-    AND  flight.flight_type_id & $flightTypeId != 0
-   GROUP BY a.id
-  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  AND  flight.flight_type_id & $flightTypeId != 0) 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  flight.flight_type_id & $flightTypeId != 0) 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  flight.flight_type_id & $flightTypeId != 0) 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  flight.flight_type_id & $flightTypeId != 0) 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  flight.flight_type_id & $flightTypeId != 0) 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  flight.flight_type_id & $flightTypeId != 0) 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  flight.flight_type_id & $flightTypeId != 0) 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  flight.flight_type_id & $flightTypeId != 0) 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  flight.flight_type_id & $flightTypeId != 0) 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  flight.flight_type_id & $flightTypeId != 0) AS Oct,
-  (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  flight.flight_type_id & $flightTypeId != 0) AS Nov,
-  (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  flight.flight_type_id & $flightTypeId != 0) 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 flight_pilot fp ON fp.flight_id = flight.id
-      WHERE
-    fp.num = 1 AND
-     YEAR( start_date ) = $year
-    AND  flight.flight_type_id & $flightTypeId != 0</sql>
 ==Flights hours following nationality==
@@ Line 891: / Line 952: @@
     GROUP BY nationality.code</SQL>
-==Flights hours total per flight type==
+==Flights hours total per flight type per month==
-  <SQL>SELECT
-    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 airborne = 0
-    AND fp.num=0
-  GROUP BY
-    ft.id,
-    DC HAVING (Name <> 'Instruction' OR DC <> 'Double')
-  ORDER BY order_num ASC</SQL>
-==Flights hours : less than 21 years, more than 21 years, male, female==
+<sql>SELECT
-  <SQL>SELECT
+     activity_type.name AS Type_vol,
-     IF( a.sex=0, 'Men','Women') AS Sexe,
+    flight_pilot_DC.DC,
-     IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu,
-     IF ((SELECT COUNT(*)
+     sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr,
-         FROM flight_pilot fp2
+     sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc,
-         WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'double') AS DC,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri,
-   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( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May,
-  FROM flight_pilot fp
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 6, duration, 0 ) ) ) AS June,
-     LEFT JOIN flight ON fp.flight_id=flight.id
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 7, duration, 0 ) ) ) AS July,
-    LEFT JOIN person a ON a.id=fp.pilot_id
+     sexa2HoursMinute( SUM( IF( MONTH(start_date) = 8, duration, 0 ) ) ) AS Augu,
-  WHERE YEAR( start_date ) = $year AND airborne = 0
+     sexa2HoursMinute( SUM( IF( MONTH(start_date) = 9, duration, 0 ) ) ) AS Sept,
-    AND fp.num=0
+     sexa2HoursMinute( SUM( IF( MONTH(start_date) = 10, duration, 0 ) ) ) AS Octo,
-  GROUP BY
+     sexa2HoursMinute( SUM( IF( MONTH(start_date) = 11, duration, 0 ) ) ) AS Nove,
-    Sex,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 12, duration, 0 ) ) ) AS Dece,
-     Young,
+    sexa2HoursMinute( SUM( duration ) ) AS Total
-    DC
+FROM flight
-  UNION
+LEFT JOIN activity_type ON activity_type.id & flight.activity_type_id
-  SELECT
+LEFT JOIN (
-    IF ( (sex = 0), 'Male', 'Female' ) AS Sex,"All",
+    SELECT flight_id, IF( MAX(num) = 1, 'Double', 'Solo' ) AS DC
-     IF ( num = 0, 'Total', 'DC') AS DC,
+    FROM flight_pilot
-     CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+    WHERE flight_pilot.num <= 1
-  FROM flight
+    GROUP BY flight_id
-    LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id
+) AS flight_pilot_DC ON flight.id=flight_pilot_DC.flight_id
-    LEFT JOIN person a ON a.id=fp.pilot_id
+WHERE YEAR(start_date) = $year
-  WHERE fp.num=0 AND YEAR(start_date)=$year AND airborne = 0
+  AND airborne = 0
-  GROUP BY Sex
+GROUP BY activity_type.id, flight_pilot_DC.DC
-  UNION
+HAVING (Name <> 'Instruction' OR flight_pilot_DC.DC <> 'Double')
-  SELECT
+ORDER BY order_num ASC, DC DESC</sql>
-    "All",
-    IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
+==Flights hours : less than 21 years, more than 21 years, male, female, for a given profile==
-    IF ( num = 0, 'Total', 'DC') AS DC,
+*Variable '''$profileId''' should be defined first and should be of '''dbOjectMulti::Profile''' value type.
-    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+*Variable '''$year''' should be defined first and should be of '''Year''' value type.
-  FROM flight
-     LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id
+<sql>SELECT Sex, Young, Instruction,
-    LEFT JOIN person a ON a.id=fp.pilot_id
+       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion',
-  WHERE YEAR( start_date ) = $year AND airborne = 0
+       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère',
-    AND fp.num=0
+       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur',
-  GROUP BY Young
+       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM'
-  UNION
+FROM (
-  SELECT
+     SELECT
-    "Total",
+        IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex',
-    $year AS year,
+        IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
-    IF ( num = 0, 'Total', 'DC') AS DC,
+        IF ((SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'Instruction') AS Instruction,
-    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+        flight.duration,
-  FROM flight
+        resource_type.category
-     LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id
+     FROM flight_pilot fp
-     LEFT JOIN person a ON a.id=fp.pilot_id
+    LEFT JOIN flight ON fp.flight_id=flight.id
-  WHERE YEAR( start_date ) = $year AND airborne = 0
+     LEFT JOIN person ON person.id=fp.pilot_id
-    AND fp.num=0
+    LEFT JOIN profile ON person.profile&profile.id
-  GROUP BY year</SQL>
+    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"==
 "Local flight" is defined as a flight less than 1h30 and with the same departure and destination
-<sql>SELECT  aircraft_id AS Num, name AS Immat,
+<sql>SELECT flight.aircraft_id AS Num, name AS Immat,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 1 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Janv,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 2 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Fevr,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 3 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Mars,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 4 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Avri,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 5 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Mai,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 6 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Juin,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 7 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Juil,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 8 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Aout,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 9 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Sept,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 10 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Octo,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 11 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     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,
+    ) AS Nove,
-     (SELECT
+     ( SELECT CONCAT( FLOOR(SUM(flight.duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.duration) / 600) ) * 3600 ), '%i' ) )
-        CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i'))
+     FROM flight
-     FROM flight
+     WHERE YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = 12 AND flight.aircraft_id = Num AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-     WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num ) AS Dece,
+    ) AS Dece,
-     CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
+     CONCAT( FLOOR(SUM(duration) / 600), ':', TIME_FORMAT( SEC_TO_TIME( ( SUM(flight.duration) / 600 - FLOOR(SUM(flight.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 AND departure_location_id = arrival_location_id AND duration < 90*600
+WHERE YEAR(flight.start_date) = $year AND flight.departure_icao_id = flight.arrival_icao_id AND flight.duration < 90 * 600
-GROUP BY order_num</sql>
+GROUP BY resource.id</sql>
 ==Computation of non-"local flight" time per month==
@@ Line 1,074: / Line 1,127: @@
 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>
-==List of flights have landed on a airfield==
+==Stats all flight hours per month per year==
-Variable $icao shall be defined (dbObject:Location)
-<sql>
+<sql>SELECT YEAR(start_date) AS Year, 'Monthly' AS Type,
-SELECT DATE_FORMAT(flight.start_date,'%d/%m/%Y') AS Date, resource.name AS immatriculation,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 1, duration, 0 ) ) ) AS Janu,
-     CONCAT(FLOOR( duration/600),':',TIME_FORMAT(SEC_TO_TIME((duration/600 - FLOOR(duration/600))*3600),'%i')) AS Durée,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 2, duration, 0 ) ) ) AS Febr,
-     person.last_name AS Nom, person.first_name AS Prénom,
+     sexa2HoursMinute( SUM( IF( MONTH(start_date) = 3, duration, 0 ) ) ) AS Marc,
-     l1.icao_name AS Départ,
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 4, duration, 0 ) ) ) AS Apri,
-     l2.icao_name AS Arrivée
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) = 5, duration, 0 ) ) ) AS May,
-FROM flight
+    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
-LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
+GROUP BY Year
-LEFT JOIN person ON person.id = flight_pilot.pilot_id
+UNION
-LEFT JOIN location AS l1 ON l1.id = flight.departure_location_id
+SELECT YEAR(start_date) AS Year, 'Accumulation' AS Type,
-LEFT JOIN location AS l2 ON l2.id = flight.arrival_location_id
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 1, duration, 0 ) ) ) AS Janu,
-WHERE flight_pilot.num = 0 AND (
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 2, duration, 0 ) ) ) AS Febr,
-     l1.icao_name LIKE '$icao'  OR
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 3, duration, 0 ) ) ) AS Marc,
-     l2.icao_name LIKE '$icao')
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 4, duration, 0 ) ) ) AS Apri,
-GROUP BY flight.id
+    sexa2HoursMinute( SUM( IF( MONTH(start_date) <= 5, duration, 0 ) ) ) AS May,
-</sql>
+    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>
-==Total flight hours per instructor per month depending on flight type==
+==Flight hours per activity type between date==
-Following extrafields are needed :
+* Variable '''$startDate''' should be defined first and should be of '''Date and time''' value type.
-*year (Type : Year)
+* Variable '''$endDate''' should be defined first and should be of '''Date and time''' value type.
-*flightType (Type : dbObject::FlightType)
-<sql>SELECT
-    UPPER(last_name) AS Nom,
-    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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 = a.id AND flight_pilot.num = 1 AND  flight.flight_type_id & $flightType != 0) 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 flight_pilot fp ON fp.flight_id = flight.id
-    LEFT JOIN person a ON a.id=fp.pilot_id
-    WHERE a.activated=1
-    AND fp.num = 1
-    AND YEAR( start_date ) = $year
-    AND  flight.flight_type_id & $flightType
-  GROUP BY a.id
-  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 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 & $flightType != 0) AS Janv,
+<sql>[OF_DYNAMIC_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)= 2 AND flight_pilot.num = 1 AND flight.flight_type_id & $flightType != 0) AS Fevr,
+SELECT CONCAT(
-  (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 AND flight.flight_type_id & $flightType != 0) AS Mars,
+    'SELECT resource.name AS _tr(RESOURCE_NAME), ',
-  (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 AND flight.flight_type_id & $flightType != 0) AS Avri,
+    GROUP_CONCAT(
-  (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 AND flight.flight_type_id & $flightType != 0) AS Mai,
+        CONCAT(
-  (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 AND flight.flight_type_id & $flightType != 0) AS Juin,
+            'sexa2HoursMinute( SUM( IF( tmp_flight.activity_type_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)= 7 AND flight_pilot.num = 1 AND flight.flight_type_id & $flightType != 0) AS Juil,
+            activity_type.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)= 8 AND flight_pilot.num = 1 AND flight.flight_type_id & $flightType != 0) AS Aout,
+            ', tmp_flight.duration, 0 ) ) )',
-  (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 AND flight.flight_type_id & $flightType != 0) AS Sept,
+            ' AS ',
-  (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 flight.flight_type_id & $flightType != 0) AS Oct,
+            QUOTE(activity_type.name)
-  (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 flight.flight_type_id & $flightType != 0) 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.num = 1 AND flight.flight_type_id & $flightType != 0) 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( tmp_flight.duration ) ) AS _tr(ALL_ACTIVITY_TYPES)
-  FROM flight
+    FROM resource
-  LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
+    INNER JOIN
-      WHERE
+    (
-    fp.num = 1 AND
+        SELECT flight.aircraft_id, flight.activity_type_id, duration
-     YEAR( start_date ) = $year
+        FROM flight
-    AND  flight.flight_type_id & $flightType</sql>
+        WHERE flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight.airborne = 0
+    ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
+    GROUP BY resource.id'
+)
+FROM activity_type
+WHERE activity_type.activated=1</sql>
+=Flight reports=
+==Activities summary for a person on the right place for a given month==
+Requirement:
+* Variable '''$year''' of '''Year''' value type
+* Variable '''$month''' of '''Month''' value type
+* Variable '''$personId''' of '''dbOjectMulti::Person''' value type
+<sql>SELECT $month AS Mois, CONCAT(left_person.last_name, ' ', left_person.first_name) AS 'Personne en première place', activity_type.name AS 'Type d\'activité', sexa2HoursMinute( SUM( IFNULL(duration, 0) ) ) AS 'Durée'
+FROM flight
+LEFT JOIN activity_type ON (flight.activity_type_id & activity_type.id)
+LEFT JOIN flight_pilot AS left_place ON (flight.id=left_place.flight_id AND left_place.num=0)
+LEFT JOIN person AS left_person ON (left_place.pilot_id=left_person.id)
+LEFT JOIN flight_pilot AS right_place ON (flight.id=right_place.flight_id AND right_place.num=1)
+WHERE YEAR(flight.start_date)=$year
+  AND MONTH(flight.start_date)=$month
+  AND (right_place.pilot_id=$personId OR '-'=right_place.pilot_id='$personId')
+GROUP BY left_person.id, activity_type.id
+ORDER BY left_person.last_name, left_person.first_name, activity_type.name</sql>
 ==Aircraft(s) which no flight were done for at least 30 day(s)==
@@ Line 1,156: / Line 1,224: @@
 LEFT JOIN resource ON resource.id = flight.aircraft_id
 WHERE start_date < ( NOW() - INTERVAL 30 DAY )
+  AND physical=1 AND activated=1
 GROUP BY resource.id</sql>
-==Total tank quantity for each tank and for each aircraft type==
+==Flight between two dates==
-<sql>SELECT resource_type.name AS aircraft_type, tank.label AS tank_label, SUM( quantity ) AS total_qty
+Following variables are needed:
-FROM tank
+*startDate (Type : Datetime)
-LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
+*endDate (Type : Datetime)
-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</sql>
-==Total tank quantity for each tank and for each aircraft==
+<sql>[OF_DYNAMIC_SQL]
-<sql>SELECT resource.name AS aircraft_callsign, tank.label AS tank_label, SUM( quantity ) AS total_qty
+SELECT CONCAT(
-FROM tank
+'SELECT
-LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
+     flight.id AS _tr(ID),
-LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
+     IF(flight.validated=1, _tr(YES), _tr(NO)) AS _tr(VALIDATED),
-LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
+     flight.start_date AS _tr(START_DATE),
-LEFT JOIN resource ON (flight.aircraft_id = resource.id)
+     resource.name AS _tr(RESOURCE),
-WHERE YEAR(flight.start_date) = $year
+     (
-GROUP BY resource.id, tank_type_id</sql>
+         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 = 0
+     ) 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( activity_type.name SEPARATOR \', \')
+         FROM flight AS tmp_flight
+         LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
+         WHERE flight.id=tmp_flight.id
+     ) AS _tr(ACTIVITY_TYPE),
+     (
+         SELECT location.name
+         FROM location
+         WHERE location.icao_name=flight.departure_icao_id
+     ) AS _tr(DEPARTURE),
+     (
+         SELECT location.name
+         FROM location
+         WHERE location.icao_name=flight.arrival_icao_id
+     ) AS _tr(ARRIVAL),
+     flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER),
+     flight.people_onboard AS _tr(FLIGHT_PEOPLE_ON_BOARD),
+     sexa2HoursMinute(flight.counter_departure) AS _tr(FLIGHT_COUNTER_DEPARTURE),
+     sexa2HoursMinute(flight.counter_arrival) AS _tr(FLIGHT_COUNTER_ARRIVAL),
+     sexa2HoursMinute(flight.duration) AS _tr(DURATION_IN_HOURS_AND_MINUTES),
+     sexa2HoursHundredths(flight.duration) AS _tr(DURATION_IN_HOURS_AND_HUNDREDTHS),',
+     IFNULL(GROUP_CONCAT(
+        CONCAT(
+            IF(business_field.value_type LIKE ('dbObject::Person%'),
+            ' (SELECT CONCAT (UPPER(tmp_person.last_name), \' \', tmp_person.first_name)
+            FROM business_field_content
+            RIGHT JOIN business_field ON (business_field.id=business_field_content.business_field_id)
+            RIGHT JOIN person AS tmp_person ON (tmp_person.id=business_field_content.content)
+            WHERE business_field_content.category_id=flight.id AND business_field_content.business_field_id=',
+            ' (SELECT GROUP_CONCAT(business_field_content.content SEPARATOR \', \')
+            FROM business_field_content
+            RIGHT JOIN business_field ON (business_field.id=business_field_content.business_field_id)
+            WHERE business_field_content.category_id=flight.id AND business_field_content.business_field_id='),
+            business_field.id,
+            ') AS \'',
+            REPLACE(business_field.label, '\'', '\\\''),
+            '\''
+        )
+    ), '\'_\''),
+     ',(
+         SELECT SUM(account_entry.debit) - SUM(account_entry.credit)
+         FROM account_entry
+         RIGHT JOIN account ON (account.id=account_entry.account_id AND (account.category = 11 OR account.category = 2))
+         WHERE account_entry.flow_id = flight_account_entry.account_entry_id
+     )  AS _tr(AMOUNT)
+FROM flight
+LEFT JOIN resource ON resource.id = flight.aircraft_id
+LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
+WHERE flight.start_date >= $startDate AND flight.start_date <= $endDate
+ORDER BY flight.start_date;
+'
+)
+FROM business_field
+WHERE business_field.category='FLIGHT' </sql>
+==Flight log book==
+<sql>SELECT
+     DATE_FORMAT(start_date, '%d/%m/%Y %H:%i') 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 AS 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 AS 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( activity_type.name SEPARATOR ', ')
+         FROM flight AS tmp_flight
+         LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
+         WHERE tmp_flight.id=flight.id
+     ) AS _tr(ACTIVITY_TYPE),
+     flight.departure_icao_id AS _tr(DEPARTURE),
+     flight.arrival_icao_id AS _tr(ARRIVAL),
+     flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER),
+     flight.people_onboard AS _tr(FLIGHT_PEOPLE_ON_BOARD),
+     business_field_comment.content AS _tr(COMMENT)
+ FROM flight_pilot
+ LEFT JOIN flight ON flight_pilot.flight_id=flight.id
+ LEFT JOIN activity_type ON (activity_type.id & flight.activity_type_id)
+ LEFT JOIN person ON person.id=flight_pilot.pilot_id
+ LEFT JOIN resource ON resource.id = flight.aircraft_id
+ LEFT JOIN (
+     SELECT business_field_content.category_id, business_field_content.content
+     FROM business_field_content
+     LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id)
+     WHERE business_field.variable='activityComment'
+ ) AS business_field_comment ON (business_field_comment.category_id=flight.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>
+==Flights which have landed elsewhere than the base field==
+Variable $icao shall be defined (dbObject:Location)
+<sql>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,
+    flight.departure_icao_id AS Départ,
+    flight.arrival_icao_id 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
+WHERE flight_pilot.num = 0 AND( flight.departure_icao_id LIKE $icao OR flight.arrival_icao_id LIKE $icao )
+GROUP BY flight.id </sql>
+==Flights with mechanic remark==
+Following variables are needed:
+*Business field '''$maintenanceUserComment''' should be defined first and should be of '''Text multi line''' value type and '''Flight''' category.
+*Business field '''$mechanicAnswer''' should be defined first and should be of '''Text multi line''' value type and '''Flight''' category.
+*Business field '''$resourceId''' should be defined first and should be of '''dbObject::Resource''' value type and '''Report''' category.
+*Business field '''$numberMonth''' should be defined first and should be of '''integer''' value type and '''Report''' category.
+<sql>SELECT CONCAT(
+        '[LINK=index.php[QUESTION_MARK]menuAction=flight_record&menuParameter=',
+        flight.id,
+        '&menuParameterBis=flight_resource_logbook&menuParameter3=1]',
+        flight.start_date,
+        '[/LINK]'
+    ) AS _tr(DATE),
+    resource.name AS _tr(RESOURCE),
+    sexa2HoursMinute(flight.duration) AS _tr(DURATION),
+    person.last_name AS Nom,
+    person.first_name AS _tr(FIRST_NAME),
+    departure_location.icao_name AS _tr(DEPARTURE),
+    arrival_location.icao_name AS _tr(ARRIVAL),
+    maintenanceUserComment.content AS _tr(REMARK),
+    mechanicAnswer.content AS _tr(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 departure_location ON departure_location.icao_name = flight.departure_icao_id
+LEFT JOIN location AS arrival_location ON arrival_location.icao_name = flight.arrival_icao_id
+LEFT JOIN business_field_content AS maintenanceUserComment ON maintenanceUserComment.category_id = flight.id
+LEFT JOIN business_field_content AS mechanicAnswer ON mechanicAnswer.category_id = flight.id
+WHERE flight_pilot.num = 0
+    AND maintenanceUserComment.business_field_id =( SELECT business_field.id FROM business_field WHERE ( business_field.variable = "maintenanceUserComment" ) )
+    AND mechanicAnswer.business_field_id =( SELECT business_field.id FROM business_field WHERE ( business_field.variable = "mechanicAnswer" ) )
+    AND ( maintenanceUserComment.content <> "" OR mechanicAnswer.content <> "" )
+    AND start_date > DATE_SUB( NOW(), INTERVAL $numberMonth MONTH)
+    AND ( (resource.id = $resourceId) OR($resourceId < '1') )
+GROUP BY flight.id
+ORDER BY resource.name, start_date DESC  </sql>
+==Flight with their location code (ICAO)==
+<sql>SELECT flight.*
+FROM flight</sql>
+==Flight hours total per person==
+Following variables are needed:
+* Variable '''$endDate''' of '''Date and time''' value type.
+* Variable '''$startDate''' of '''Date and time''' value type.
-==Fuel/Oil quantities per tank and aircraft type==
 <sql>SELECT
-     aircraft_type_name AS CallSign, tank_label AS Tank,
+     CONCAT(person.last_name,' ',person.first_name) AS  _tr(LAST_NAME),
+     sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) ) AS _tr(ALONE),
+     sexa2HoursMinute( SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS _tr(LEGEND_INSTRUCTION),
+     sexa2HoursMinute( SUM( IFNULL( Solo.sum_duration, 0 ) ) + SUM( IFNULL( DC.sum_duration, 0 ) ) ) AS _tr(TOTAL)
+ FROM person
+ LEFT JOIN (
+     SELECT
+         pilot_id,
+         IF (
+             (SELECT COUNT(*) FROM flight_pilot flight_pilot_2 WHERE flight_pilot_2.flight_id=flight_pilot_1.flight_id AND flight_pilot_2.num=1)=0,
+             'Solo',
+             'DC'
+         ) AS Solo_DC,
+         SUM(flight.duration) AS sum_duration
+     FROM flight_pilot flight_pilot_1
+     LEFT JOIN flight ON flight_pilot_1.flight_id=flight.id
+     WHERE flight.airborne=0 AND flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight_pilot_1.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 flight_pilot_2 WHERE flight_pilot_2.flight_id=flight_pilot_1.flight_id AND flight_pilot_2.num=1)=0,
+             'Solo',
+             'DC'
+         ) AS Solo_DC,
+         SUM(flight.duration) AS sum_duration
+     FROM flight_pilot flight_pilot_1
+     LEFT JOIN flight ON flight_pilot_1.flight_id=flight.id
+     WHERE flight.airborne=0 AND flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight_pilot_1.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>
+=Fuel/Oil reports=
+==Hourly consumption==
+Unit is: unit of the tank per hour
+<SQL>SELECT
+    resource_name AS '_tr(REPORT_RESOURCE)',
+    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 '_tr(TANK)',
+    CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS '_tr(MONTH_JAN)',
+    CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS '_tr(MONTH_FEB)',
+    CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_MAR)',
+    CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_APR)',
+    CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_MAY)',
+    CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_JUN)',
+    CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_JUL)',
+    CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_AUG)',
+    CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_SEP)',
+    CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_OCT)',
+    CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_NOV)',
+    CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0))  AS DECIMAL(10,2) ) AS '_tr(MONTH_DEC)',
+    CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600  AS DECIMAL(10,2) ) AS '_tr(ANNUAL_AVERAGE)'
+FROM
+    (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>
+==Monthly refueling by resource type==
+<sql>SELECT
+     aircraft_type_name AS _tr(RESOURCE_TYPE), tank_label AS _tr(TANK),
      SUM(IF (month_num=1, quantity, 0)) AS Janu,
      SUM(IF (month_num=2, quantity, 0)) AS Febr,
@@ Line 1,192: / Line 1,507: @@
      SUM(IF (month_num=11, quantity, 0)) AS Nove,
      SUM(IF (month_num=12, quantity, 0)) AS Dece,
-     SUM(quantity) AS Sum
+     SUM(quantity) AS _tr(QUANTITY)
 FROM
 (
@@ Line 1,218: / Line 1,533: @@
          WHEN 4 THEN "SP95"
          WHEN 5 THEN "SP98"
+        WHEN 6 THEN "GAZOLE"
      END AS Tank,
      SUM(IF (month_num=1, quantity, 0)) AS Janu,
@@ Line 1,231: / Line 1,547: @@
      SUM(IF (month_num=11, quantity, 0)) AS Nove,
      SUM(IF (month_num=12, quantity, 0)) AS Dece,
-     SUM(quantity) AS Sum,
+     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
 FROM
@@ Line 1,247: / Line 1,563: @@
 GROUP BY resource_name, tank_type</SQL>
-==Fuel/Oil consumption ==
+==Total refuelings by resource over a year==
-Unit is: unit of the tank per hour
+<sql>SELECT resource.name AS _tr(RESOURCE), tank.label AS _tr(TANK), SUM( quantity ) AS _tr(QUANTITY)
-<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>
-==Flight with their location code (ICAO)==
+==Total refuelings by resource type over a year==
-<sql>SELECT flight.*, L1.icao_name AS departure_location, L2.icao_name AS arrival_location
+<sql>SELECT resource_type.name AS _tr(RESOURCE_TYPE), tank.label AS _tr(TANK), SUM( quantity ) AS _tr(QUANTITY)
-FROM flight
+FROM tank
-LEFT JOIN location AS L1 ON flight.departure_location_id = L1.id
+LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id )
-LEFT JOIN location AS L2 ON flight.arrival_location_id = L2.id</sql>
+LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id )
+LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id)
-==Flight list between two dates==
+WHERE YEAR(flight.start_date) = $year
-Two variables should be defined first and should be of Date value type :
+GROUP BY aircraft_type_id, tank_type_id</sql>
-*startDate
-*endDate
-<sql>SELECT f.start_date, r.name,
-CONCAT(UPPER(p.last_name),' ',p.first_name) AS Pilot,
-    (
-        SELECT CONCAT(UPPER(p2.last_name), ' ', p2.first_name)
-        FROM flight_pilot AS fp2
-        LEFT JOIN person AS p2 ON fp2.pilot_id = p2.id
-        WHERE fp2.flight_id = f.id AND fp2.num = 1
-    ) AS Instructor,
-    (
-        SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ')
-        FROM flight
-        LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
-        WHERE flight.id=f.id
-    ) AS Flight_types,
-    (
-        SELECT location.name
-        FROM location
-        WHERE location.id=f.departure_location_id
-    ) AS departure_location,
-    (
-        SELECT location.name
-        FROM location
-        WHERE location.id=f.arrival_location_id
-    ) AS arrival_location,
-    f.landing_number AS landing_number,
-CONCAT(FLOOR( f.duration/600),':',TIME_FORMAT(SEC_TO_TIME((f.duration/600 - FLOOR(f.duration/600))*3600),'%i')) AS duration
-FROM flight AS f
-LEFT JOIN resource AS r ON r.id = f.aircraft_id
-LEFT JOIN flight_pilot AS fp ON fp.flight_id = f.id
-LEFT JOIN person AS p ON fp.pilot_id = p.id
-WHERE '$startDate' <= f.start_date AND f.start_date <= '$endDate' AND fp.num = 0;</sql>
-==Detailed flight list per month==
-Following extrafields are needed
-*year (Type : Year)
-*month (Type : Integer) '''OR''' startDate and endDate (Type : Date)
-*profile (Type : dbObject::Profile)
-*occupiedSeat (Type : Integer)
-This report is useful for a group of pilots from a common customer (like DGAC in France).
-<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
-    ) 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
-    ) 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 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
-    -- Chose one of the two following WHERE statements
-    -- 1) Month selection
-    WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month
-    -- 2) Selection between two dates
-    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&$profile AND (account.category = 11 OR account.category = 2)
-    GROUP BY flight.id</sql>
-==Stats all flight hours per month per year==
-<sql>  SELECT  YEAR(start_date) AS year, 'Monthly' AS Type,
-  (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 ) 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 ) 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 ) 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 ) 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 ) 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 ) 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 ) 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 ) 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 ) 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 ) 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 ) 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 ) AS Dece,
-  (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)  AS Sum
-  FROM flight
-  LEFT JOIN resource ON resource.id = flight.aircraft_id
-  WHERE resource.virtual = 0
-  GROUP BY year
-  UNION
-  SELECT  YEAR(start_date) AS year, 'Accumulation',
-  (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),
-  (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) AS Sum
-  FROM flight
-  LEFT JOIN resource ON resource.id = flight.aircraft_id
-  WHERE resource.virtual = 0
-  GROUP BY year
-  ORDER BY year DESC</sql>
 =Error message queries=
@@ Line 1,450: / Line 1,609: @@
 =[[Users exports 4|Users]]=
-[[Users_exports_4|Users exports SQL queries]].

Difference between revisions of "Export generator 4"

Revision as of 18:03, 18 February 2022

Contents

Introduction

Business field

Business field creation

Business field creation examples

SQL tips and tricks

Return only last entry

Test valid entries

French administration Examples

Flight hours total on instruction with specific activity type/profile

Flight hours total on non-instruction with specific activity type/profile

Users with a specific validity without expiration date

Number of movements on based airfield

Number of movements on based airfield on specific profile

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

User flying without 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 X months

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

Number of landings per pilot, per resource

Flight hours without up to date validities

Number of men, women over and under 21 years for profile X, validity Y up to date for year Z

Total hours by activities and resource categories for an instructor for one year

Validity year young specific profile: Total of youngs/adults with specific profile

Validities obtained in the year

Visited airfields

Visited outsider airfield

Young from this year

Accounting

Bookings

Booking

Cumulated maintenance hours on a period

Maintenance hours per resource on a period

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

Flight time management

Flight hours total and last recorded counters to the date X

Flight hours per month

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 an activity type)

Flight hours total per pilot

Flight hours total per pilot per profile

Total Flight hours per pilot with total

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 per activity type between date

Flight reports

Activities summary for a person on the right place for a given month

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

Flight between two dates

Flight log book

Flights which have landed elsewhere than the base field

Flights with mechanic remark

Flight with their location code (ICAO)

Flight hours total per person