Difference between revisions of "Export generator 3"

Jump to: navigation, search
(Total Flight hours per pilot with total duration and total amount)
(Detailed flight list per month)
Line 1,904: Line 1,904:
 
*month (Type : Integer)
 
*month (Type : Integer)
 
*profile (Type : dbObject::Profile)
 
*profile (Type : dbObject::Profile)
 +
 +
This report is useful for a group of pilots from a common customer (like DGAC in France).
  
 
<sql>SELECT  
 
<sql>SELECT  

Revision as of 09:50, 18 April 2013

Contents

Introduction

The goal of this page is to propose a list of statistic generation queries (SQL).

Do not forget to read the OpenFlyers SQL stored functions and procedures page.

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

Extra field

For more flexibility, extra field can be defined per the user

Two extra field types are available:

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

Extra Field creation

  • Go to Menu Admin > Reports > Structure > Extra Field(s)

In the bottom line add

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

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

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

Extra Field creation examples

We create a new extra field for the current year:

  • Name: $year
  • Label: Year
  • Value type: Year

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

We create a new extra field to list the reservation of an aircraft:

  • Name: $aircraftId
  • Label: Aircraft
  • Value type: dbObject::Aircaft

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

SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId

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

SQL tips and tricks

Return only last entry

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

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

French administration Examples

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 
  GROUP BY name 
  UNION 
  SELECT 
    "Total", 
    $year AS year, 
    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total      
  FROM flight 
  WHERE YEAR(start_date) = $year 
  GROUP BY year

Flights hours total per aircraft per year and per month

SELECT  aircraft_id AS Num, name, 
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num) AS Janu,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num) AS Febr,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num) AS Marc,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num) AS Apri,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num) AS May,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num) AS June,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num) AS July,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num) AS Augu,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num) AS Sept,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num) AS Octo,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num) AS Nove,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num) AS Dece,
  CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Sum 
  FROM flight
  LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
  LEFT JOIN resource ON resource.id = aircraft.id
  WHERE YEAR(start_date)= $year  AND resource.virtual = 0 
  GROUP BY order_num
  UNION 
  SELECT  "Sum per","month", 
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12),
  CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
  FROM flight
  LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
  LEFT JOIN resource ON resource.id = aircraft.id
  WHERE YEAR(start_date)= $year AND resource.virtual = 0 
  UNION 
  SELECT  "Cumulative","per month", 
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 1),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 2),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 3),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 4),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 5),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 6),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 7),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 8),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 9),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 10),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 11),
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 12),
  $year
  FROM flight
  LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
  LEFT JOIN resource ON resource.id = aircraft.id
  WHERE YEAR(start_date)= $year  AND resource.virtual = 0

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

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

Flights hours : less than 21 years, more than 21 years, male, female

SELECT 
    IF( a.sex=0, 'Men','Women') AS Sexe,
    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', 'double') AS 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 YEAR( start_date ) = $year 
    AND fp.num=0 
  GROUP BY 
    Sex,
    Young,
    DC
  UNION 
  SELECT 
    IF ( (sex = 0), 'Male', 'Female' ) AS Sex,"All",
    IF ( num = 0, 'Total', 'DC') AS DC,
    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
  FROM flight 
    LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id 
    LEFT JOIN person a ON a.id=fp.pilot_id 
  WHERE fp.num=0 AND YEAR(start_date)=$year 
  GROUP BY Sex
  UNION 
  SELECT 
    "All",
    IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
    IF ( num = 0, 'Total', 'DC') AS DC,
    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
  FROM flight 
    LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id 
    LEFT JOIN person a ON a.id=fp.pilot_id  
  WHERE YEAR( start_date ) = $year 
    AND fp.num=0 
  GROUP BY Young 
  UNION 
  SELECT 
    "Total", 
    $year AS year, 
    IF ( num = 0, 'Total', 'DC') AS DC,
    CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
  FROM flight 
    LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id 
    LEFT JOIN person a ON a.id=fp.pilot_id 
  WHERE YEAR( start_date ) = $year 
    AND fp.num=0 
  GROUP BY year

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

Licence in the year

SELECT 
    last_name AS Last_name, 
    first_name AS First_name, 
    IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
    IF ( (sex = 0), 'Male', 'Female' ) AS Sex, DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, validity_type.name AS 'Validity'
  FROM person 
    LEFT JOIN validity ON person_id=person.id 
    LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
  WHERE YEAR(grant_date)=$year

List of pilots with specific validity

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

Note: replace name of validity_type.name by yours

Young list

SELECT 
    last_name AS Last_name,
    first_name AS First_name, 
    DATE_FORMAT(birthdate ,'%m-%d-%Y') AS Birthdate, 
    IF ( (sex = 0), 'Male', 'Female' ) AS sex
  FROM person
  WHERE ($year-YEAR(birthdate))<=21

Flights hours total per flight type

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 fp.num=0 
  GROUP BY 
    ft.id, 
    DC HAVING (Name <> 'Instruction' OR DC <> 'Double') 
  ORDER BY order_num ASC

Number of visit on other airfield

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

Number of movements on based platform due to country flight

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

Number of take-off and landings on based airfield

SELECT SUM( landing_number )*2 AS nb_mouvement 
FROM flight f, club c 
WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao))
  AND (f.arrival_location_id  = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) 
  AND YEAR( start_date ) = $year

List of movements on based airfield

SELECT resource.name, DATE_FORMAT(flight.start_date, '%d %m %Y' ) AS Date, sum(landing_number) AS 'Nb Att'
FROM flight
LEFT JOIN resource ON resource.id = flight.aircraft_id
WHERE ( flight.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao))
  AND (flight.arrival_location_id  = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) 
  AND YEAR( start_date ) = $year
GROUP BY date,name

Flights hours total per instructor

SELECT 
    last_name AS Last_name,
    first_name AS First_name, 
    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 fp.num = 1 
    AND YEAR( start_date ) = $year
  GROUP BY a.id 
  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,flight_pilot fp
  WHERE flight.id = fp.flight_id 
    AND fp.num = 1 
    AND YEAR( start_date ) = $year
  GROUP BY year

User list with up to date subscription

SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity'
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE YEAR(validity.grant_date) = $year AND validity_type.id = $validityTypeId

User list without up to date subscription

SELECT 
	last_name AS Nom, 
	first_name AS prénom,
	email, 
	home_phone AS tel_dommicile, 
	work_phone AS tel_travail, 
	cell_phone AS tel_mobile, 
DATE_FORMAT(validity.grant_date,'%d/%m/%Y') AS date_cotisation
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE YEAR(validity.grant_date) < YEAR(NOW()) AND validity_type.name = 'Cotisation' AND activated = 1
ORDER BY last_name,first_name

Validity per user

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

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 without up to date validities

SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS Date,last_name AS Nom,
first_name AS Prénom, 
IF ((SELECT COUNT(*) 
    FROM flight_pilot fp2 
    WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'DC') AS DC,
resource.name AS Immat,
CONCAT(FLOOR( duration /600),
	 ':',
	 TIME_FORMAT(SEC_TO_TIME(( duration /600 - FLOOR( duration /600))*3600),'%i')) AS Tdv,
ft.name As Type_Vol,
vt.name AS Qualif
FROM flight
LEFT JOIN flight_type_mandatory_validity_type fq ON fq.flight_type_id & flight.flight_type_id 
LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id
LEFT JOIN person p ON p.id = fp.pilot_id
LEFT JOIN validity_type vt ON vt.id = fq.validity_type_id 
LEFT JOIN validity v ON v.validity_type_id = fq.validity_type_id 
LEFT JOIN resource ON resource.id = flight.aircraft_id
LEFT JOIN flight_type ft ON ft.id = fq.flight_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
ORDER BY Nom

List of pilots who have flown without required validity

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

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

SELECT CONCAT(au.last_name, ' ', au.first_name) AS pilot, TIME_FORMAT( SEC_TO_TIME( SUM(f.duration)*6 ), '%H:%i' ) AS total_time
FROM flight AS f
RIGHT JOIN flight_pilot AS fp ON fp.flight_id=f.id
LEFT JOIN aircraft AS ai ON ai.id = f.aircraft_id
LEFT JOIN person AS au ON fp.pilot_id = au.id
WHERE f.airborne = 0
  AND start_date BETWEEN NOW() - INTERVAL Y DAY AND NOW()
GROUP BY au.id
HAVING SUM(f.duration)/600 < X
ORDER BY pilot

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

 
SELECT CONCAT(au.last_name, ' ', au.first_name) AS pilot, TIME_FORMAT( SEC_TO_TIME( SUM(f.duration)*6 ), '%H:%i' ) AS total_time
FROM flight AS f
RIGHT JOIN flight_pilot AS fp ON fp.flight_id=f.id
LEFT JOIN resource AS ai ON ai.id = f.aircraft_id
LEFT JOIN resource_type AS at ON ai.resource_type_id = at.id
LEFT JOIN person AS au ON fp.pilot_id = au.id
WHERE f.airborne = 0
  AND start_date BETWEEN NOW() - INTERVAL Y DAY AND NOW()
  AND at.id IN ( Z1, Z2, Z3, Z... )
GROUP BY au.id
HAVING SUM(f.duration)/600 <= X
ORDER BY pilot

Pilots without flight in the last 3 months

SELECT 
    CONCAT(UPPER(a.last_name), ' ', a.first_name) AS pilot,
    IFNULL((SELECT CONCAT(DATE_FORMAT( f1.start_date, '%d/%m/%Y' ),' ', resource.name,' Duration: ', 
               TIME_FORMAT(SEC_TO_TIME((SUM( f1.duration )/600 - FLOOR(SUM( f1.duration )/600))*3600),'%H h %i'))
            FROM flight AS f1
            LEFT JOIN flight_pilot AS fp ON fp.flight_id=f1.id 
            LEFT JOIN resource ON resource.id=f1.aircraft_id
            WHERE fp.pilot_id=a.id 
            ORDER BY f1.start_date ASC LIMIT 1),'UNKNOWN') AS "Last flight" 
FROM person AS a
LEFT JOIN profile AS p1 ON (a.profile & p1.id)
WHERE a.activated=1 AND p1.name='Pilot'
AND a.id NOT IN (
SELECT au.id
FROM flight AS f
LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id
LEFT JOIN person AS au ON au.id=fp.pilot_id
WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL 90 DAY AND NOW() GROUP BY au.id HAVING SUM(f.duration)/600 > 0)
ORDER BY pilot

Members list with age and birth date

(SELECT last_name AS Last_name,
 
first_name AS First_name, 
 
DATE_FORMAT(birthdate ,'%d-%m-%Y') AS Birth_date, 
 
IF ( (sex = 0), 'Man', 'Woman' ) AS sex,
 
(DATE_FORMAT(DATE('$day'), '%Y') - 
DATE_FORMAT(birthdate, '%Y') - 
(SELECT DATE_FORMAT(DATE('$day'), '00-%m-%d') < DATE_FORMAT( birthdate, '00-%m-%d'))) AS 'age',
 
DATE_FORMAT(DATE('$day'),'%d-%m-%Y') AS on_date,
 
IF((DATE_FORMAT(DATE('$day'), '%Y') - 
DATE_FORMAT(birthdate, '%Y') - 
(SELECT DATE_FORMAT(DATE('$day'), '00-%m-%d') < DATE_FORMAT( birthdate, '00-%m-%d')))>=21, '>= 21', '< 21') AS Major,
 
-- inscription_date,
 
-- member.subscription,
 
-- entry.account_date as Adhesion
DATE_FORMAT(entry.account_date,'%d-%m-%Y') as Grant_date
 
-- ,MAX(entry.account_date)
 
-- , $day
-- , entry.flow_id
-- , account.name
 
FROM person
 
RIGHT JOIN validity ON validity.person_id = person.id 
 
RIGHT JOIN account acc2 on acc2.owner_id = person.id
 
RIGHT JOIN account_entry linked_entry on linked_entry.account_id = acc2.id
 
RIGHT JOIN account_entry entry on linked_entry.flow_id = entry.flow_id
 
RIGHT JOIN account on entry.account_id = account.id 
 
WHERE year(validity.grant_date) = $year
 
AND account.name = 'Cotisations ACB'
 
AND (year(entry.account_date)=$year OR (year(entry.account_date)=$year -1 AND month(entry.account_date)=12) )
 
AND entry.account_date < DATE('$day')
 
ORDER BY Last_name  LIMIT 999999
)
UNION
(
SELECT COUNT(*) AS Last_name,
 
COUNT(*) AS First_name, 
 
COUNT(*) AS Birth_date, 
 
COUNT(*) AS sex,
 
COUNT(*) AS 'age',
 
COUNT(*) AS on_date,
 
COUNT(*) AS Major,
 
COUNT(*) as Grant_date
 
FROM person
 
RIGHT JOIN validity ON validity.person_id = person.id 
 
RIGHT JOIN account acc2 on acc2.owner_id = person.id
 
RIGHT JOIN account_entry linked_entry on linked_entry.account_id = acc2.id
 
RIGHT JOIN account_entry entry on linked_entry.flow_id = entry.flow_id
 
RIGHT JOIN account on entry.account_id = account.id 
 
WHERE year(validity.grant_date) = $year
 
AND account.name = 'Cotisations ACB'
 
AND (year(entry.account_date)=$year OR (year(entry.account_date)=$year -1 AND month(entry.account_date)=12) )
 
AND entry.account_date < DATE('$day')
)

Member List with subscription grant date and profiles

SELECT validity_type.name AS 'Validity',
    DATE_FORMAT(grant_date,'%d/%m/%Y') AS grant_date,
    last_name AS Name,
    first_name AS Firstname,
    ident_value AS Comment,
    ( SELECT GROUP_CONCAT(profile.name SEPARATOR ', ') 
      FROM profile WHERE (person.profile & profile.id)
    )AS Profile
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id =validity_type.id 
LEFT JOIN person ON person.id=validity.person_id
WHERE YEAR(grant_date)>=$year AND MONTH(grant_date)>=1 
AND validity_type.name = 'Cotisation' 
ORDER BY Name, Firstname
 

Member email list

SELECT
	last_name, 
	first_name,
	person.email
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id =validity_type.id 
LEFT JOIN person ON person.id=validity.person_id
WHERE YEAR(grant_date)>=$year
AND validity_type.name = 'Cotisation'  AND person.activated=1

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

Get members list with membership ending before a specific date

Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.

SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity'
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE validity.grant_date <= '2007-12-31'
  AND validity_type.id = $validityTypeId

Get activated user list

SELECT id, last_name, first_name
FROM person
WHERE activated=1

User list coordinates

SELECT 
	last_name, 
	first_name, 
	name AS login, 
	email, 
	address, 
	zipcode, 
	city AS Ville, 
	state AS etat_region, 
	country AS pays, 
	home_phone AS tel_domicile, 
	work_phone AS tel_travail, 
	cell_phone AS tel_mobile
FROM person
WHERE activated=1

User coordinates with registration date

Extra field required :

  1. registrationDate
    • label : Registration date
    • category : User
    • value type : DateTime

Validity type required :

  1. Cotisation
SELECT 
    last_name, 
    first_name,
    email, 
    address, 
    zipcode, 
    city, 
    state, 
    country, 
    home_phone, 
    work_phone,
    cell_phone, 
    sex,
    DATE_FORMAT(birthdate, '%Y-%m-%d') AS birthdate,
    nationality,
    validity.grant_date AS subscription_date,
    (
        SELECT DATE_FORMAT(extra_field_content.content, '%Y-%m-%d')
        FROM extra_field
        LEFT JOIN extra_field_content ON extra_field.id=extra_field_content.extra_field_id
        WHERE extra_field.variable="registrationDate" and extra_field_content.category_id=person.id
    )AS registration_date
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE activated = 1 AND validity_type.name = 'Cotisation'
ORDER BY last_name, first_name

User list by profile

Variable $profile should be defined first and should be of dbOject::Profile value type.

SELECT
    last_name AS Lastname,
    first_name AS Firstname,
    profile.name AS Profile
FROM person
LEFT JOIN profile ON (person.profile & profile.id)
WHERE person.activated = 1 AND profile.id = $profile
ORDER BY Profile, Lastname, Firstname

User list by validity

SELECT validity_type.name AS 'Validity',
    DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date,
    last_name AS Name,
    first_name AS Firstname,
    ident_value AS Comment
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id =validity_type.id 
LEFT JOIN person ON person.id=validity.person_id
WHERE YEAR(grant_date)>$year AND MONTH(grant_date)>=1
ORDER BY 'Validity', Name, Firstname

User list by profile

Name of profile (i.e. Pilot), shall be adapted to the profile designation that want to be listed

SELECT last_name AS Lastname, first_name AS Firstname, p1.name AS Profil 
FROM person AS a1
LEFT JOIN profile AS p1 ON (a1.profile & p1.id)
WHERE a1.activated=1 AND p1.name='Pilot'
ORDER BY last_name,first_name

User list with validities viewer

SELECT 
      last_name AS Last_name,
      first_name AS First_name,
      validity_type.name AS 'Validity',
      DATE_FORMAT(expire_date,'%d/%m/%Y') AS Expiry_date,
      if((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS Expired,
     -- ident_value AS Commentaire,
      DATE_FORMAT(grant_date,'%d/%m/%Y') AS Grant_date
 
   FROM validity_type
   LEFT JOIN validity ON validity.validity_type_id =validity_type.id 
   LEFT JOIN person ON person.id=validity.person_id
-- Validity recently expired or about to expire
WHERE period_diff(DATE_FORMAT(expire_date,'%y%m'), DATE_FORMAT(Now(),'%y%m')) < 2 AND
-- period_diff(DATE_FORMAT(expire_date,'%y%m'), -- DATE_FORMAT(Now(),'%y%m')) > -2 AND
-- Deactivated members
person.activated = 1
-- Exclude Night qualification 
AND validity_type.id <> 6
   ORDER BY Last_name, First_name, validity_type.name

User list ordered by member number

SELECT 
    efc.content AS numero,
	last_name AS Nom, 
	first_name AS prénom, 
	name AS login, 
	email, 
	address AS adresse, 
	zipcode AS code_postal, 
	city AS Ville, 
	state AS etat_region, 
	country AS pays, 
	home_phone AS tel_dommicile, 
	work_phone AS tel_travail, 
	cell_phone AS tel_mobile, 
        activated AS actif
FROM person
LEFT JOIN extra_field_content AS efc ON person.id = efc.category_id
LEFT JOIN extra_field AS ef ON efc.extra_field_id = ef.id
WHERE ef.variable = 'memberNum' AND person.activated = 1
ORDER BY CAST(efc.content AS SIGNED)

Accounting

Global account balance

List whole account balance at the end of a given date (Extra field $endDateAccount, type dateTime, should be defined)

SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom,
IFNULL(sumAccountEntry(account.id,'$endDateAccount'),0) AS solde
FROM account
LEFT JOIN person ON person.id=account.owner_id 
WHERE account.activated=1
ORDER BY export_account

Global account balance for specific accounting

Replace XXXX values by chosen accounting's label

SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom,
IFNULL(sumAccountEntry(account.id,'$endDateAccount'),0) AS solde
FROM account
LEFT JOIN person ON person.id=account.owner_id 
LEFT JOIN accounting ON accounting.id=account.accounting_id 
WHERE account.activated=1 AND accounting.name='XXXX'
ORDER BY export_account

Global account balance of pilots who last subscription was 2 years ago

Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.

SELECT last_name, first_name, validity_type.name as 'Validity', grant_date,
      IFNULL((SELECT  SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry  WHERE account_entry.account_id = a.id
              AND account_entry.account_date > (SELECT balance_date  FROM balance_date ORDER BY balance_date DESC LIMIT  1)),0)+balance.credit-balance.debit AS Total
FROM `person`
LEFT JOIN validity ON validity.person_id = person.id
LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id
LEFT JOIN account AS a ON a.owner_id = person.id AND category = 2
LEFT JOIN balance ON balance.account_id = a.id
          AND balance.balance_date_id = (SELECT id FROM balance_date  ORDER BY balance_date ASC LIMIT 1)
WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)
  AND validity_type.id = $validityTypeId
GROUP BY person.id
ORDER BY last_name,first_name

Global account balance of pilots who last subscription was 2 years ago for specific accounting

Replace XXXX values by chosen accounting's label

SELECT last_name, first_name, validity_type.name AS 'Validity', grant_date,
      IFNULL((SELECT  SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry  WHERE account_entry.account_id = a.id
              AND account_entry.account_date > (SELECT balance_date  FROM balance_date ORDER BY balance_date DESC LIMIT  1)),0)+balance.credit-balance.debit AS Total
FROM `person`
LEFT JOIN validity ON validity.person_id = person.id
LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id
LEFT JOIN account AS a ON a.owner_id = person.id AND category = 2
LEFT JOIN accounting ON accounting.id=a.accounting_id 
LEFT JOIN balance ON balance.account_id = a.id
          AND balance.balance_date_id = (SELECT id FROM balance_date  ORDER BY balance_date ASC LIMIT 1)
WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR) AND accounting.name='XXXX'
  AND validity_type.id = $validityTypeId
GROUP BY person.id
ORDER BY last_name,first_name

Global non null account balance

  • List whole account balance at the end of a given date (Extra field $endDateAccount has to be defined and should be of DateTime value type) but for only non null account balance :
SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom,
IFNULL(sumAccountEntry(account.id,'$endDateAccount'),0) AS solde
FROM account
LEFT JOIN person ON person.id=account.owner_id 
WHERE account.activated=1 AND sumAccountEntry(account.id,'$endDateAccount') <> 0
ORDER BY export_account 
  • List whole account balance at the end of a given year (Extra field $year has to be defined and should be of Year value type) but for only non null account balance :
SELECT id, export_account, name, sumAccountEntry(id,'$year-12-31 23:59:59') AS solde
FROM account WHERE activated=1 AND sumAccountEntry(id,'$year-12-31 23:59:59') <> 0
ORDER BY export_account

Global non null account balance for specific accounting

Replace XXXX values by chosen accounting's label

SELECT a.id, export_account, a.name, sumAccountEntry(a.id,'$year-12-31 23:59:59') AS solde
FROM account AS a
LEFT JOIN accounting ON accounting.id=a.accounting_id 
WHERE activated=1 AND sumAccountEntry(a.id,'$year-12-31 23:59:59') <> 0 AND accounting.name='XXXX'
ORDER BY export_account

Account balance list

Export_generator_3.0#Extra field $endDateAccount has to be defined first and should be of value type DateTime.

List of pilot account balance

SELECT person.last_name, person.first_name, account_type.name AS account_type_name,
sumAccountEntry(account.id, '$endDateAccount') AS balance
FROM account
LEFT JOIN account_type ON (account.account_type=account_type.id)
LEFT JOIN person ON (account.owner_id=person.id)
WHERE account.category=2 AND account.activated=1
ORDER BY last_name, first_name

List of pilot account balance for specific accounting

Replace XXXX values by chosen accounting's label

SELECT person.last_name, person.first_name, account_type.name AS account_type_name,
sumAccountEntry(account.id, '$endDateAccount') AS balance
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN account_type ON (account.account_type=account_type.id)
LEFT JOIN person ON (account.owner_id=person.id)
WHERE account.category=2 AND account.activated=1 AND accounting.name='XXXX'
ORDER BY last_name, first_name

List of all account debit, credit

List whole account balance at the end of a given date (Extra field $endDateAccount, type dateTime, should be defined)

SELECT
    account.export_account AS 'Export account',
    CASE
        WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('Member account ', account_type.name, ' of ', person.last_name, ' ', person.first_name)
        WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name)
        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
        ELSE account.name
    END AS account_name,
    IF (sumAccountEntry(account.id,'$endDateAccount')<0, sumAccountEntry(account.id,'$endDateAccount'), 0)  AS Debit,
    IF (sumAccountEntry(account.id,'$endDateAccount')<0, 0, sumAccountEntry(account.id,'$endDateAccount') ) AS Credit
FROM account
LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3))
LEFT JOIN person ON (account.owner_id = person.id AND account.category=2)
LEFT JOIN aircraft ON (account.owner_id = aircraft.id AND account.category=3)
LEFT JOIN resource ON aircraft.id = resource.id
WHERE person.activated = 1
GROUP BY account.id 
ORDER BY account.export_account ASC

List of all account debit, credit for specific accounting

Replace XXXX values by chosen accounting's label

SELECT
    account.export_account AS 'Export account',
    CASE
        WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('Member account ', account_type.name, ' of ', person.last_name, ' ', person.first_name)
        WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name)
        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
        ELSE account.name
    END AS account_name,
    IF (sumAccountEntry(account.id,'$endDateAccount')<0, sumAccountEntry(account.id,'$endDateAccount'), 0)  AS Debit,
    IF (sumAccountEntry(account.id,'$endDateAccount')<0, 0, sumAccountEntry(account.id,'$endDateAccount') ) AS Credit
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN account_type ON account.account_type = account_type.id 
LEFT JOIN person ON account.owner_id = person.id
LEFT JOIN aircraft ON account.owner_id = aircraft.id
LEFT JOIN resource ON aircraft.id = resource.id
WHERE person.activated = 1 AND accounting.name='XXXX'
GROUP BY account.id
ORDER BY account.export_account ASC

Balance per account category

SELECT 
    CASE
        WHEN account_type.name IS NOT NULL AND (account.category=2 OR account.category=3) THEN account_type.name
        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
        ELSE account.name
    END AS 'Account type',
    IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde
FROM account
LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3))
GROUP BY account.category

Balance per account category for specific accounting

Replace XXXX values by chosen accounting's label

SELECT 
    CASE
        WHEN account_type.name IS NOT NULL AND (account.category=2 OR account.category=3) THEN account_type.name
        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
        ELSE account.name
    END AS 'Account type',
    IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3))
WHERE accounting.name='XXXX'
GROUP BY account.category

Carry forwards

SELECT
    IF (account.category = 2, CONCAT(person.last_name,'  
',person.first_name,' (',account_type.name,')'),
    IF (account.category = 3,CONCAT(resource.name,'  
(',account_type.name,')'),account.name)) AS Nom,
    export_account AS Code_comptable,
    debit AS 'Debit',
    credit AS 'Credit'
FROM account
LEFT JOIN balance ON balance.account_id=account.id
LEFT JOIN person ON person.id=account.owner_id AND account.category = 2
LEFT JOIN aircraft ON aircraft.id=account.owner_id AND account.category = 3
LEFT JOIN resource ON resource.id=aircraft.id
LEFT JOIN account_type ON account_type.id=account.account_type
WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1)
UNION
SELECT
    'zzzzzzz',
    'Total',
    SUM(debit) AS 'Debit',
    SUM(credit) AS 'Credit'
FROM account
LEFT JOIN balance ON balance.account_id=account.id
WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1)
ORDER BY Nom ASC

Carry forwards for specific accounting

Replace XXXX values by chosen accounting's label

SELECT
    IF (account.category = 2, CONCAT(person.last_name,'  
',person.first_name,' (',account_type.name,')'),
    IF (account.category = 3,CONCAT(resource.name,'  
(',account_type.name,')'),account.name)) AS Nom,
    export_account AS Code_comptable,
    debit AS 'Debit',
    credit AS 'Credit'
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN balance ON balance.account_id=account.id
LEFT JOIN person ON person.id=account.owner_id AND account.category = 2
LEFT JOIN aircraft ON aircraft.id=account.owner_id AND account.category = 3
LEFT JOIN resource ON resource.id=aircraft.id
LEFT JOIN account_type ON account_type.id=account.account_type
WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) AND accounting.name='XXXX'
UNION
SELECT
    'zzzzzzz',
    'Total',
    SUM(debit) AS 'Debit',
    SUM(credit) AS 'Credit'
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN balance ON balance.account_id=account.id
WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) AND accounting.name='XXXX'
ORDER BY Nom ASC

Payments list ordered by type

SELECT payment_type.name AS Type, if(account.category = 2, 
	CONCAT( p.last_name, ' ', p.first_name),account.name) AS Name, 
	DATE_FORMAT(ae.account_date, '%d %m %Y' ) AS Date, 
	ae.payment_description, ae.credit AS Description
FROM account_entry AS ae
LEFT JOIN payment_type ON payment_type.id = ae.payment_type
LEFT JOIN account ON account.id = ae.account_id
LEFT JOIN person AS p ON p.id = account.owner_id
WHERE ae.payment_type IS NOT NULL AND credit > 0 AND YEAR(ae.account_date) >= $year
ORDER BY ae.payment_type, ae.account_date, p.last_name, p.first_name

Get the details (specially the id) of an account according is name

SELECT * FROM `account` WHERE `name` LIKE 'account name to search'

Get the pilot owner of an account

SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114

Payment dispatching

SELECT
  payment_type as Num, payment_type.name as name,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type = Num) AS Janu,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type = Num) AS Febr,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type = Num) AS Marc,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type = Num) AS Apri,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type = Num) AS May,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type = Num) AS June,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type = Num) AS July,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type = Num) AS Augu,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type = Num) AS Sept,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type = Num) AS Octo,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type = Num) AS Nove,
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type = Num) AS Dece,
  SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total 
  FROM account_entry
  LEFT JOIN payment_type ON payment_type = payment_type.id
  WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL
  GROUP BY payment_type
  UNION
  SELECT  'Sum per', 'month',
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type IS NOT NULL),
  (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type IS NOT NULL),
  SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total
  FROM account_entry
  LEFT JOIN payment_type ON payment_type = payment_type.id
  WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL

Cheque deposit slip

SELECT date_format(ae.registration_date, "%d/%m/%Y") as "date", a.name, ae.credit as montant, ae.payment_description as libelle, 
(SELECT a2.name FROM account_entry ae2 JOIN account a2 ON a2.id = ae2.account_id WHERE ae2.flow_id = ae.flow_id AND ae2.debit > 0 LIMIT 0,1) as compte_banque, ae.comments as commentaire
FROM account_entry ae
JOIN account a
  ON ae.account_id = a.id
WHERE a.account_type = 1
  AND ae.validated = 1
  AND ae.credit > 0
ORDER BY ae.registration_date DESC

Monthly distribution of revenue

  • In french accounting, this query reports each month's revenue distribution. It only concerns product accounts (prefixed 7xx) except those that are prefixed 709xxx
  • Additional field $year is needed
SELECT IF( (SELECT at2.category FROM account AS a2 LEFT JOIN account_type AS at2 ON at2.id=a2.account_type WHERE a2.id=a.id) = 3, CONCAT(a.name, ' - ', (SELECT at3.name FROM account AS a3 LEFT JOIN account_type AS at3 ON at3.id=a3.account_type WHERE a3.id=a.id) ), a.name) AS nom,
    (IF (sumAccountEntry(a.id,'$year-02-01')<0, sumAccountEntry(a.id,'$year-02-01'), 0) - IF (sumAccountEntry(a.id,'$year-01-01')<0, sumAccountEntry(a.id,'$year-01-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-02-01')<0, 0, sumAccountEntry(a.id,'$year-02-01')) - IF (sumAccountEntry(a.id,'$year-01-01')<0, 0, sumAccountEntry(a.id,'$year-01-01'))) AS Janu,
    (IF (sumAccountEntry(a.id,'$year-03-01')<0, sumAccountEntry(a.id,'$year-03-01'), 0) - IF (sumAccountEntry(a.id,'$year-02-01')<0, sumAccountEntry(a.id,'$year-02-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-03-01')<0, 0, sumAccountEntry(a.id,'$year-03-01')) - IF (sumAccountEntry(a.id,'$year-02-01')<0, 0, sumAccountEntry(a.id,'$year-02-01'))) AS Febr,
    (IF (sumAccountEntry(a.id,'$year-04-01')<0, sumAccountEntry(a.id,'$year-04-01'), 0) - IF (sumAccountEntry(a.id,'$year-03-01')<0, sumAccountEntry(a.id,'$year-03-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-04-01')<0, 0, sumAccountEntry(a.id,'$year-04-01')) - IF (sumAccountEntry(a.id,'$year-03-01')<0, 0, sumAccountEntry(a.id,'$year-03-01'))) AS Marc,
    (IF (sumAccountEntry(a.id,'$year-05-01')<0, sumAccountEntry(a.id,'$year-05-01'), 0) - IF (sumAccountEntry(a.id,'$year-04-01')<0, sumAccountEntry(a.id,'$year-04-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-05-01')<0, 0, sumAccountEntry(a.id,'$year-05-01')) - IF (sumAccountEntry(a.id,'$year-04-01')<0, 0, sumAccountEntry(a.id,'$year-04-01'))) AS April,
    (IF (sumAccountEntry(a.id,'$year-06-01')<0, sumAccountEntry(a.id,'$year-06-01'), 0) - IF (sumAccountEntry(a.id,'$year-05-01')<0, sumAccountEntry(a.id,'$year-05-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-06-01')<0, 0, sumAccountEntry(a.id,'$year-06-01')) - IF (sumAccountEntry(a.id,'$year-05-01')<0, 0, sumAccountEntry(a.id,'$year-05-01'))) AS May,
    (IF (sumAccountEntry(a.id,'$year-07-01')<0, sumAccountEntry(a.id,'$year-07-01'), 0) - IF (sumAccountEntry(a.id,'$year-06-01')<0, sumAccountEntry(a.id,'$year-06-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-07-01')<0, 0, sumAccountEntry(a.id,'$year-07-01')) - IF (sumAccountEntry(a.id,'$year-06-01')<0, 0, sumAccountEntry(a.id,'$year-06-01'))) AS June,
    (IF (sumAccountEntry(a.id,'$year-08-01')<0, sumAccountEntry(a.id,'$year-08-01'), 0) - IF (sumAccountEntry(a.id,'$year-07-01')<0, sumAccountEntry(a.id,'$year-07-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-08-01')<0, 0, sumAccountEntry(a.id,'$year-08-01')) - IF (sumAccountEntry(a.id,'$year-07-01')<0, 0, sumAccountEntry(a.id,'$year-07-01'))) AS July,
    (IF (sumAccountEntry(a.id,'$year-09-01')<0, sumAccountEntry(a.id,'$year-09-01'), 0) - IF (sumAccountEntry(a.id,'$year-08-01')<0, sumAccountEntry(a.id,'$year-08-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-09-01')<0, 0, sumAccountEntry(a.id,'$year-09-01')) - IF (sumAccountEntry(a.id,'$year-08-01')<0, 0, sumAccountEntry(a.id,'$year-08-01'))) AS Augu,
    (IF (sumAccountEntry(a.id,'$year-10-01')<0, sumAccountEntry(a.id,'$year-10-01'), 0) - IF (sumAccountEntry(a.id,'$year-09-01')<0, sumAccountEntry(a.id,'$year-09-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-10-01')<0, 0, sumAccountEntry(a.id,'$year-10-01')) - IF (sumAccountEntry(a.id,'$year-09-01')<0, 0, sumAccountEntry(a.id,'$year-09-01'))) AS Sept,
    (IF (sumAccountEntry(a.id,'$year-11-01')<0, sumAccountEntry(a.id,'$year-11-01'), 0) - IF (sumAccountEntry(a.id,'$year-10-01')<0, sumAccountEntry(a.id,'$year-10-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-11-01')<0, 0, sumAccountEntry(a.id,'$year-11-01')) - IF (sumAccountEntry(a.id,'$year-10-01')<0, 0, sumAccountEntry(a.id,'$year-10-01'))) AS Octo,
    (IF (sumAccountEntry(a.id,'$year-12-01')<0, sumAccountEntry(a.id,'$year-12-01'), 0) - IF (sumAccountEntry(a.id,'$year-11-01')<0, sumAccountEntry(a.id,'$year-11-01'), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-12-01')<0, 0, sumAccountEntry(a.id,'$year-12-01')) - IF (sumAccountEntry(a.id,'$year-11-01')<0, 0, sumAccountEntry(a.id,'$year-11-01'))) AS Nove,
    (IF (sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))<0, sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH)), 0) - IF (sumAccountEntry(a.id,'$year-12-01')<0, sumAccountEntry(a.id,'$year-12-01'), 0)) 
    + (IF (sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))<0, 0, sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))) - IF (sumAccountEntry(a.id,'$year-12-01')<0, 0, sumAccountEntry(a.id,'$year-12-01'))) AS Dece
FROM account AS a
WHERE a.export_account LIKE '7%' AND a.export_account NOT LIKE '709%'
UNION
SELECT "Total",
    (IF (sumAccountEntry(a.id,'$year-02-01')<0, SUM(sumAccountEntry(a.id,'$year-02-01')), 0) - IF (sumAccountEntry(a.id,'$year-01-01')<0, SUM(sumAccountEntry(a.id,'$year-01-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-02-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-02-01'))) - IF (sumAccountEntry(a.id,'$year-01-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-01-01')))) AS Janu,
    (IF (sumAccountEntry(a.id,'$year-03-01')<0, SUM(sumAccountEntry(a.id,'$year-03-01')), 0) - IF (sumAccountEntry(a.id,'$year-02-01')<0, SUM(sumAccountEntry(a.id,'$year-02-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-03-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-03-01'))) - IF (sumAccountEntry(a.id,'$year-02-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-02-01')))) AS Febr,
    (IF (sumAccountEntry(a.id,'$year-04-01')<0, SUM(sumAccountEntry(a.id,'$year-04-01')), 0) - IF (sumAccountEntry(a.id,'$year-03-01')<0, SUM(sumAccountEntry(a.id,'$year-03-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-04-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-04-01'))) - IF (sumAccountEntry(a.id,'$year-03-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-03-01')))) AS Marc,
    (IF (sumAccountEntry(a.id,'$year-05-01')<0, SUM(sumAccountEntry(a.id,'$year-05-01')), 0) - IF (sumAccountEntry(a.id,'$year-04-01')<0, SUM(sumAccountEntry(a.id,'$year-04-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-05-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-05-01'))) - IF (sumAccountEntry(a.id,'$year-04-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-04-01')))) AS April,
    (IF (sumAccountEntry(a.id,'$year-06-01')<0, SUM(sumAccountEntry(a.id,'$year-06-01')), 0) - IF (sumAccountEntry(a.id,'$year-05-01')<0, SUM(sumAccountEntry(a.id,'$year-05-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-06-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-06-01'))) - IF (sumAccountEntry(a.id,'$year-05-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-05-01')))) AS May,
    (IF (sumAccountEntry(a.id,'$year-07-01')<0, SUM(sumAccountEntry(a.id,'$year-07-01')), 0) - IF (sumAccountEntry(a.id,'$year-06-01')<0, SUM(sumAccountEntry(a.id,'$year-06-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-07-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-07-01'))) - IF (sumAccountEntry(a.id,'$year-06-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-06-01')))) AS June,
    (IF (sumAccountEntry(a.id,'$year-08-01')<0, SUM(sumAccountEntry(a.id,'$year-08-01')), 0) - IF (sumAccountEntry(a.id,'$year-07-01')<0, SUM(sumAccountEntry(a.id,'$year-07-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-08-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-08-01'))) - IF (sumAccountEntry(a.id,'$year-07-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-07-01')))) AS July,
    (IF (sumAccountEntry(a.id,'$year-09-01')<0, SUM(sumAccountEntry(a.id,'$year-09-01')), 0) - IF (sumAccountEntry(a.id,'$year-08-01')<0, SUM(sumAccountEntry(a.id,'$year-08-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-09-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-09-01'))) - IF (sumAccountEntry(a.id,'$year-08-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-08-01')))) AS Augu,
    (IF (sumAccountEntry(a.id,'$year-10-01')<0, SUM(sumAccountEntry(a.id,'$year-10-01')), 0) - IF (sumAccountEntry(a.id,'$year-09-01')<0, SUM(sumAccountEntry(a.id,'$year-09-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-10-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-10-01'))) - IF (sumAccountEntry(a.id,'$year-09-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-09-01')))) AS Sept,
    (IF (sumAccountEntry(a.id,'$year-11-01')<0, SUM(sumAccountEntry(a.id,'$year-11-01')), 0) - IF (sumAccountEntry(a.id,'$year-10-01')<0, SUM(sumAccountEntry(a.id,'$year-10-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-11-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-11-01'))) - IF (sumAccountEntry(a.id,'$year-10-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-10-01')))) AS Octo,
    (IF (sumAccountEntry(a.id,'$year-12-01')<0, SUM(sumAccountEntry(a.id,'$year-12-01')), 0) - IF (sumAccountEntry(a.id,'$year-11-01')<0, SUM(sumAccountEntry(a.id,'$year-11-01')), 0)) 
    + (IF (sumAccountEntry(a.id,'$year-12-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-12-01'))) - IF (sumAccountEntry(a.id,'$year-11-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-11-01')))) AS Nove,
    (IF (sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))<0, SUM(sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))), 0) - IF (sumAccountEntry(a.id,'$year-12-01')<0, SUM(sumAccountEntry(a.id,'$year-12-01')), 0)) 
    + (IF (sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))<0, 0, SUM(sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH)))) - IF (sumAccountEntry(a.id,'$year-12-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-12-01')))) AS Dece
FROM account AS a
WHERE export_account LIKE '7%' AND export_account NOT LIKE '709%'

Booking

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

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

Flight time management

Flight hours total per pilot

SELECT 
    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, 
    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 YEAR( start_date ) = $year AND fp.num=0 
  GROUP BY Nom, Solo_DC

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,
    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 ON person.id=fp.pilot_id
  LEFT JOIN profile ON (person.profile & profile.id)
  WHERE YEAR( start_date ) = $year AND fp.num=0
  GROUP BY Profil, Nom, Solo_DC

Total Flight hours per pilot with total duration and total amount

Following extrafields are needed

  • year (Type : Year)
  • month (Type : Integer)
  • profile (Type : dbObject::Profile)

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

SELECT CONCAT(person.last_name,' ',person.first_name) AS pilots_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 flight_hours_performed,
SUM(flight_list.montant) AS flights_total_amount  
FROM person
JOIN
(SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
LEFT JOIN flight ON flight.id=flight_pilot.flight_id
LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
LEFT JOIN account ON account.id=account_entry.account_id
WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND person.profile=$profile AND flight_pilot.num=0 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
UNION
SELECT 'Total',
    CONCAT( 
        (
             SELECT FLOOR(SUM(flight_list.duree)/600)
             FROM person
             JOIN
             (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
             LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
             LEFT JOIN flight ON flight.id=flight_pilot.flight_id
             LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
             LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
             LEFT JOIN account ON account.id=account_entry.account_id
             WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND person.profile=$profile AND flight_pilot.num=0 AND (account.category = 11 OR account.category = 2)
             GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
        ),':',
        TIME_FORMAT(SEC_TO_TIME((
            (
                SELECT SUM(flight_list.duree)/600
                FROM person
                JOIN
                (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
                LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
                LEFT JOIN flight ON flight.id=flight_pilot.flight_id
                LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
                LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
                LEFT JOIN account ON account.id=account_entry.account_id
                WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND person.profile=$profile AND flight_pilot.num=0 AND (account.category = 11 OR account.category = 2)
                GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
            )
            - 
            (
                SELECT FLOOR(SUM(flight_list.duree)/600)
                FROM person
                JOIN
                (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
                LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
                LEFT JOIN flight ON flight.id=flight_pilot.flight_id
                LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
                LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
                LEFT JOIN account ON account.id=account_entry.account_id
                WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND person.profile=$profile AND flight_pilot.num=0 AND (account.category = 11 OR account.category = 2)
                GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
            )
        )*3600),'%i')
    ),
    (
        SELECT SUM(flight_list.montant)
        FROM person
        JOIN
        (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
        LEFT JOIN flight ON flight.id=flight_pilot.flight_id
        LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
        LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
        LEFT JOIN account ON account.id=account_entry.account_id
        WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND person.profile=$profile AND flight_pilot.num=0 AND (account.category = 11 OR account.category = 2)
        GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
    )

Flights hours total per instructor per year and per month

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

Computation of flight time per month of "local flight"

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

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

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 resource.virtual = 0  AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)
GROUP BY order_num

List of flights have landed on a airfield

Variable $icao shall be defined (dbObject:Location)

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

Total instructed flight hours

SELECT 
    UPPER(last_name) AS Last_name,
    first_name AS First_name, 
    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 fp.num = 1 
    AND a.profile = 8
    AND YEAR( start_date ) = $year  
  GROUP BY a.id 
  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,flight_pilot fp, person a
  WHERE flight.id = fp.flight_id
    AND a.profile = 8
    AND fp.num = 1 
    AND YEAR( start_date ) = $year 
  GROUP BY year

Total DCA instructed flight hours per instructor per month

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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 & 8192 != 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 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 & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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.num = 1 AND flight.flight_type_id & 8192 != 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 & 8192 != 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 & 8192 != 0

Indemnified flight hours

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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 YEAR(start_date) = $year  
    AND ((fp.num = 0 AND flight.flight_type_id & 16 != 0) OR(fp.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 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 
      YEAR(start_date) = $year  
      AND ((fp.num = 0 AND flight.flight_type_id & 16 != 0) OR(fp.num = 1 AND flight.flight_type_id & 8192 = 0 AND  flight.flight_type_id & 256 = 0))

Flight hours per instructor function to flight type

  • Variable $flightTypeId should be defined first and should be a dbOject::FlightType value type.
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

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 )
GROUP BY resource.id

Total tank quantity for each tank and for each aircraft type

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

Total tank quantity for each tank and for each aircraft

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

Fuel/Oil quantities per tank and aircraft type

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

Fuel/Oil sum for each aircraft

Unit is: unit of the tank

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

Fuel/Oil consumption

Unit is: unit of the tank per hour

SELECT
    resource_name AS CallSign,
    CASE tank_type
        WHEN 1 THEN "AVGAS"
        WHEN 2 THEN "JET A1"
        WHEN 3 THEN "Oil"
        WHEN 4 THEN "SP95"
        WHEN 5 THEN "SP98"
    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

Flight with their location code (ICAO)

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

Flight list between two dates

Two variables should be defined first and should be of Date value type :

  • startDate
  • endDate
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;

Detailed flight list per month

Following extrafields are needed

  • year (Type : Year)
  • month (Type : Integer)
  • profile (Type : dbObject::Profile)

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

SELECT 
    CONCAT(last_name,' ',first_name) AS 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
    WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND flight_pilot.num=0 AND person.profile=$profile AND (account.category = 11 OR account.category = 2)
    GROUP BY flight.id
UNION
SELECT 'zzz Total', null, null,
    CONCAT( 
        (
            SELECT FLOOR(SUM(flight.duration)/600)
            FROM flight
            JOIN
            (
                SELECT flight.id AS flight_id, account_entry.id, flight.duration
                    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
                    WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND flight_pilot.num=0 AND person.profile=$profile AND (account.category = 11 OR account.category = 2)
                    GROUP BY flight.id
            ) AS flight_durations ON flight_durations.flight_id=flight.id
        ),':',
        TIME_FORMAT(SEC_TO_TIME((
            (
                SELECT SUM(flight.duration)/600
                FROM flight
                JOIN
                (
                    SELECT flight.id AS flight_id, account_entry.id, flight.duration
                        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
                        WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND flight_pilot.num=0 AND person.profile=$profile AND (account.category = 11 OR account.category = 2)
                        GROUP BY flight.id
                ) AS flight_durations ON flight_durations.flight_id=flight.id
            )
            - 
            (
                SELECT FLOOR(SUM(flight.duration)/600)
                FROM flight
                JOIN
                (
                    SELECT flight.id AS flight_id, account_entry.id, flight.duration
                        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
                        WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND flight_pilot.num=0 AND person.profile=$profile AND (account.category = 11 OR account.category = 2)
                        GROUP BY flight.id
                ) AS flight_durations ON flight_durations.flight_id=flight.id
            )
        )*3600),'%i')
    ), null, null, 
        (SELECT SUM(account_entry.debit)-SUM(account_entry.credit)
            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
            WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month AND flight_pilot.num=0 AND person.profile=$profile AND (account.category = 11 OR account.category = 2)
        ), null
  ORDER BY Pilot_name, start_date
 

Stats all flight hours per month per year

  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

Error message queries

Flight without account movement

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

list members without an account

SELECT member.id, person.last_name, person.first_name FROM member
LEFT JOIN account ON account.owner_id=member.id
LEFT JOIN person ON person.id=member.id
WHERE person.activated=1 AND account.category=2 AND account.id IS NULL

movement without an account

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