Difference between revisions of "Export generator 3"

Jump to: navigation, search
(Member list by profile)
(Member list by validity)
Line 740: Line 740:
 
ORDER BY Profile, Lastname, Firstname</sql>
 
ORDER BY Profile, Lastname, Firstname</sql>
  
==Member list by validity==
+
==User list by validity==
  
 
<sql>SELECT validity_type.name AS 'Validity',
 
<sql>SELECT validity_type.name AS 'Validity',

Revision as of 16:18, 30 December 2012

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"

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 (2011-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

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

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

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 all account debit, credit

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

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

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

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

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

Flights hours total per instructor per year and per month

SELECT 
    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 = i.id AND flight_pilot.num = 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 INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Febr,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Marc,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Apri,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS May,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS June,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS July,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Augu,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Sept,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Octo,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Novo,
  (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = i.id AND flight_pilot.num = 1) AS Dece,
    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 instructor i ON i.id = a.id 
  WHERE i.id = fp.pilot_id 
    AND fp.num = 1 
    AND YEAR( start_date ) = $year  
  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),
  (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 instructor i ON i.id = a.id 
  WHERE i.id = fp.pilot_id 
    AND fp.num = 1 
    AND YEAR( start_date ) = $year
 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 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 instructor i ON i.id = a.id 
  WHERE i.id = fp.pilot_id 
    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
 

Aditionnal queries

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 list by profile

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

SELECT
    last_name AS Lastname,
    first_name AS Firstname,
    validity_type.name AS Validity,
    grant_date AS Date,
    profile.name AS Profile
FROM person
LEFT JOIN profile ON (person.profile & profile.id)
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE YEAR(grant_date) >= $year
  AND validity_type.id = $validityTypeId
ORDER BY Profile, Lastname, Firstname

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

Member list by profil

Name of profil (i.e. Pilot), shall be adapted to the profil 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

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

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

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

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

Pilots without flight in the last 3 months

SELECT 
    CONCAT(a.last_name, ' ', a.first_name) AS pilot,
    IFNULL((SELECT CONCAT(DATE_FORMAT( f1.start_date, '%d/%m/%Y' ),' ', resource.name,' Durée: ', 
               TIME_FORMAT(SEC_TO_TIME((SUM( f1.duration )/600 - FLOOR(SUM( f1.duration )/600))*3600),'%H h %i'))
            FROM flight AS f1
            LEFT JOIN flight_pilot AS fp ON fp.flight_id=f1.id 
            LEFT JOIN resource ON resource.id=f1.aircraft_id
            WHERE fp.pilot_id=a.id 
            ORDER BY f1.start_date DESC LIMIT 1),'INCONNU') AS "Dernier Vol" 
FROM person AS a
LEFT JOIN member ON member.id=a.id
WHERE a.activated = 1 AND member.subscription = '2011-12-31'
AND a.id NOT IN (
SELECT au.id
FROM flight AS f
LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id
LEFT JOIN person AS au ON au.id=fp.pilot_id
WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL 90 DAY AND NOW() GROUP BY au.id HAVING SUM(f.duration)/600 > 0)
ORDER BY pilot

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'

various queries

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

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