Export generator 2.0

From OpenFlyers Documentation
Revision as of 18:53, 12 August 2024 by imported>Claratte (Text replacement - "<sql>" to "<syntaxhighlight lang="sql">")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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: <syntaxhighlight lang="sql">SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId</sql> To use this report, we just have to select a "Aircraft" in the Reports/Structure/View form, to check "Aircraft booking" then to click on "View"

French administration Examples

Flights hours total per aircraft per year

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

Flights hours total per aircraft per year and per month

 <SQL>SELECT  aircraft_id AS Num, callsign, 
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_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 
 WHERE YEAR(start_date)= $year  AND aircraft.non_flightable = 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 
 WHERE YEAR(start_date)= $year AND aircraft.non_flightable = 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 
 WHERE YEAR(start_date)= $year  AND aircraft.non_flightable = 0</SQL>

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

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

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

 <SQL>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 authentication 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 authentication 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 authentication 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 authentication a ON a.id=fp.pilot_id 
 WHERE YEAR( start_date ) = $year 
   AND fp.num=0 
 GROUP BY year</SQL>

Flights hours following nationality

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

Licence in the year

 <SQL>SELECT 
   last_name AS Last_name, 
   first_name AS First_name, 
   IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
   IF ( (sex = 0), 'Male', 'Female' ) AS Sex, DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, qualification.name AS Qualif 
 FROM authentication 
   LEFT JOIN member_qualif ON member_id=authentication.id 
   LEFT JOIN qualification ON qualification.id=member_qualif.qualif_id 
 WHERE YEAR(grant_date)=$year</SQL>

List of pilots with specific qualification

 <SQL>SELECT 
   last_name AS Last_name,
   first_name AS First_name, 
   qualification.name AS Qualification, 
  DATE_FORMAT(member_qualif.expire_date, '%m-%d-%Y')  AS Expirate 
 FROM authentication 
   LEFT JOIN member_qualif ON member_id=authentication.id 
   LEFT JOIN qualification ON qualification.id=qualif_id 
 WHERE grant_date IS NULL 
   AND qualification.time_limitation=1 
   AND qualification.name="Private Pilot License" 
   AND authentication.activated=1 
 ORDER BY 
   last_name,
   first_name</SQL>

Note: replace name of qualification by yours

Young list

 <SQL>SELECT 
   last_name AS Last_name,
   first_name AS First_name, 
   DATE_FORMAT(birthdate ,'%m-%d-%Y') AS Birthdate, 
   IF ( (sex = 0), 'Male', 'Female' ) AS sex
 FROM authentication 
 WHERE (2011-YEAR(birthdate))<=21</SQL>

Flights hours total per flight type

 <SQL>SELECT 
   ft.name AS Type_vol,
   IF ((SELECT
          COUNT(*) 
        FROM flight_pilot fp2 
        WHERE fp2.flight_id=fp.flight_id  
          AND fp2.num=1)=0, 'Solo', 'Double') AS DC,
   CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
 FROM flight 
   LEFT JOIN flight_type ft ON ft.id & flight.flight_type_id 
   LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id 
 WHERE YEAR( start_date ) = $year 
   AND fp.num=0 
 GROUP BY 
   ft.id, 
   DC HAVING (Name <> 'Instruction' OR DC <> 'Double') 
 ORDER BY order_num ASC</SQL>

Number of visit on other airfield

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

Number of take-off and landings on flying club airfield

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

Flights hours total per instructor

 <SQL>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 authentication 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 
   "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, instructor i 
 WHERE flight.id = fp.flight_id 
   AND fp.pilot_id = i.id 
   AND fp.num = 1 
   AND YEAR( start_date ) = $year 
 GROUP BY year</SQL>

Accounting

Global account balance

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

<syntaxhighlight lang="sql">SELECT account.id, export_account, IFNULL(account.name, CONCAT(authentication.last_name, ' ', authentication.first_name)) AS nom, IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS solde FROM account LEFT JOIN authentication ON authentication.id=account.owner_id WHERE account.activated=1 ORDER BY export_account</sql>

Global non null account balance

  • List whole account balance at the end of a given date (Extra field $endDate, type dateTime, should be defined) but for only non null account balance :

<syntaxhighlight lang="sql">SELECT account.id, export_account, IFNULL(account.name, CONCAT(authentication.last_name, ' ', authentication.first_name)) AS nom, IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS solde FROM account LEFT JOIN authentication ON authentication.id=account.owner_id WHERE account.activated=1 AND sumAccountEntry(account.id,'$endDate') <> 0 ORDER BY export_account </sql>

  • List whole account balance at the end of a given year (Extra field $year, type numeric, should be defined) but for only non null account balance :

<syntaxhighlight lang="sql">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</sql>

Account balance list

Extra field $endDate has to be defined first and should be of value type DateTime.

List of pilote account balance

<syntaxhighlight lang="sql">SELECT authentication.last_name, authentication.first_name, account_type.name AS account_type_name, sumAccountEntry(account.id, '$endDate') AS balance FROM account LEFT JOIN account_type ON (account.account_type=account_type.id) LEFT JOIN authentication ON (account.owner_id=authentication.id) WHERE account.category=2 AND account.activated=1 ORDER BY last_name, first_name</sql>

List of all account debit, credit

<syntaxhighlight lang="sql">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 ', authentication.last_name, ' ', authentication.first_name)
       WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', aircraft.callsign)
       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,'$endDate')<0, sumAccountEntry(account.id,'$endDate'), 0) AS Debit, IF (sumAccountEntry(account.id,'$endDate')<0, 0, sumAccountEntry(account.id,'$endDate') ) AS Credit FROM account LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3)) LEFT JOIN authentication ON (account.owner_id = authentication.id AND account.category=2) LEFT JOIN aircraft ON (account.owner_id = aircraft.id AND account.category=3) WHERE authentication.activated = 1 GROUP BY account.id ORDER BY account.export_account ASC</sql>

Carry forwards

 <SQL>SELECT  
     IF (account_member.account_id IS NOT NULL, 
         CONCAT(authentication.first_name,' ',authentication.last_name,' ',account_member_type.name),
         IF (account_aircraft.account_id IS NULL,
             account.name,
             CONCAT(aircraft.callsign,' ',account_aircraft_type.name))) AS Name,
         export_account AS Export_Account, 
     debit AS Debit,
     credit AS Credit
 FROM account
 LEFT JOIN balance ON balance.account_id=account.id
 LEFT JOIN account_member ON account_member.account_id=account.id
 LEFT JOIN authentication ON authentication.id=account_member.member_id
 LEFT JOIN account_member_type ON account_member_type.id=account_member.account_type
 LEFT JOIN account_aircraft ON account_aircraft.account_id=account.id
 LEFT JOIN aircraft ON aircraft.id=account_aircraft.aircraft_id
 LEFT JOIN account_aircraft_type ON account_aircraft_type.id=account_aircraft.account_type
 WHERE account.activated = 1 AND balance_date_id IN (SELECT MAX(balance_date.id) FROM balance_date)
 ORDER BY Name ASC</SQL>

Flight time management

Flight hours total per pilot

 <SQL>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 authentication a ON a.id=fp.pilot_id 
 WHERE YEAR( start_date ) = $year AND fp.num=0 
 GROUP BY Nom, Solo_DC</SQL>

Flight hours total per pilot per pool

 <SQL>SELECT
   member_pool.name as Pool,
   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 authentication a ON a.id=fp.pilot_id
 LEFT JOIN member ON member.id = a.id
 LEFT JOIN member_pool_join ON member_pool_join.member_id = member.id
 LEFT JOIN member_pool ON member_pool.id = member_pool_join.member_pool_id
 WHERE YEAR( start_date ) = $year AND fp.num=0
 GROUP BY Pool, Nom, Solo_DC</SQL>

Flights hours total per instructor per year and per month

<syntaxhighlight lang="sql"> 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 authentication 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 authentication 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 authentication 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

</sql>

Computation of flight time per month of "local flight"

"Local flight" is defined as a flight less than 1h30 and with the same departure and destination <syntaxhighlight lang="sql">SELECT aircraft_id AS Num, callsign 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 WHERE YEAR(start_date)= $year AND aircraft.non_flightable = 0 AND departure_location_id = arrival_location_id AND duration < 90*600 GROUP BY order_num;</sql>

Computation of non-"local flight" time per month

<syntaxhighlight lang="sql">SELECT aircraft_id AS Num, callsign 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 WHERE YEAR(start_date)= $year AND aircraft.non_flightable = 0 AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id) GROUP BY order_num;</sql>

Aditionnal queries

Member list coordinates

<SQL>SELECT 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, subscription AS date_cotisation,

       activated AS actif

FROM authentication LEFT JOIN member ON member.id = authentication.id </SQL>

Member list by pool

 <SQL>SELECT
   last_name AS Lastname,
   first_name AS Firstname,
   DATE_FORMAT(subscription, '%d %m %Y' ) AS Date,
   member_pool.name AS Pool
 FROM authentication
   LEFT JOIN member ON member.id = authentication.id
   LEFT JOIN member_pool_join ON member_pool_join.member_id = member.id
   LEFT JOIN member_pool ON member_pool.id = member_pool_join.member_pool_id
 WHERE YEAR(subscription) >= $year
 ORDER BY Pool, Lastname, Firstname</SQL>

Member list by qualification

  <SQL>SELECT qualification.name AS Qualif,
     DATE_FORMAT(subscription,'%d/%m/%Y') AS Date,
     last_name AS Name,
     first_name AS Firstname,
     ident_value AS Comment
  FROM qualification 
  LEFT JOIN member_qualif ON member_qualif.qualif_id =qualification.id 
  LEFT JOIN authentication  ON  authentication.id=member_qualif.member_id
  LEFT JOIN member ON member.id=authentication.id
  WHERE YEAR(subscription)>$year AND MONTH(subscription)>=1
  ORDER BY Qualif, Name, Firstname</SQL>

Member list by profil

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

  <SQL>SELECT last_name AS Lastname, first_name AS Firstname, p1.name AS Profil, member.subscription  AS Subcription_Date  
  FROM authentication AS a1
  LEFT JOIN member ON member.id=a1.id 
  LEFT JOIN profile AS p1 ON a1.profile&(SELECT p2.id FROM profile AS p2 WHERE p2.name="Pilot")
  WHERE  a1.activated=1 AND p1.name="Pilot"
  ORDER BY last_name,first_name</SQL>

Payment dispatching

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

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

<syntaxhighlight lang="sql">SELECT aircraft.id, aircraft.callsign FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id WHERE start_date < ( NOW() - INTERVAL 30 DAY ) GROUP BY aircraft.id</sql>

Total tank quantity for each tank and for each aircraft type

<syntaxhighlight lang="sql">SELECT aircraft_type.name AS aircraft_type, tank.label AS tank_label, SUM( quantity ) AS total_qty FROM tank LEFT JOIN aircraft_type ON ( aircraft_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</sql>

Total tank quantity for each tank and for each aircraft

<syntaxhighlight lang="sql"> SELECT aircraft.callsign AS aircraft_callsign, tank.label AS tank_label, SUM( quantity ) AS total_qty FROM tank LEFT JOIN aircraft_type ON ( aircraft_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 aircraft ON (flight.aircraft_id = aircraft.id) WHERE YEAR(flight.start_date) = $year GROUP BY aircraft.id, tank_type_id</sql>

Monthly total tank quantities for each tank and for each aircraft type

<syntaxhighlight lang="sql">SELECT

   aircraft_type_name, tank_label,
   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 Mai,
   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 total

FROM (

   SELECT aircraft_type.id AS aircraft_type_id,
          aircraft_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 aircraft_type ON ( aircraft_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</sql>

List of pilots who have flown without required qualification

<syntaxhighlight lang="sql">SELECT CONCAT(a.last_name, ' ', a.first_name) AS pilot, q.name AS qualif FROM flight_type_mandatory_qualification AS ftmp LEFT JOIN flight_type AS ft ON ft.id = ftmp.flight_type_id LEFT JOIN flight AS f ON f.flight_type_id & ft.id LEFT JOIN aircraft AS ai ON ai.id = f.aircraft_id LEFT JOIN aircraft_type AS at ON ai.type = at.id LEFT JOIN aircraft_type_qualif AS atq ON at.id = atq.aircraft_type_id LEFT JOIN flight_pilot AS fp ON f.id = fp.flight_id LEFT JOIN authentication AS a ON a.id = fp.pilot_id LEFT JOIN qualification AS q ON (q.id = ftmp.qualification_id OR q.id = atq.qualification_id) WHERE f.airborne = 0

 AND ROW(a.id, q.id) NOT IN (SELECT member_id, qualif_id FROM member_qualif)
 AND q.experience_formula IS NULL

GROUP BY a.id, q.id ORDER BY pilot, qualif</sql>

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

<syntaxhighlight lang="sql">SELECT CONCAT(au.last_name, ' ', au.first_name) AS pilot, TIME_FORMAT( SEC_TO_TIME( SUM(f.duration)*6 ), '%H:%i' ) AS total_time FROM flight AS f RIGHT JOIN flight_pilot AS fp ON fp.flight_id=f.id LEFT JOIN aircraft AS ai ON ai.id = f.aircraft_id LEFT JOIN authentication AS au ON fp.pilot_id = au.id WHERE f.airborne = 0

 AND start_date BETWEEN NOW() - INTERVAL Y DAY AND NOW()

GROUP BY au.id HAVING SUM(f.duration)/600 < X ORDER BY pilot</sql>

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

<syntaxhighlight lang="sql"> SELECT CONCAT(au.last_name, ' ', au.first_name) AS pilot, TIME_FORMAT( SEC_TO_TIME( SUM(f.duration)*6 ), '%H:%i' ) AS total_time FROM flight AS f RIGHT JOIN flight_pilot AS fp ON fp.flight_id=f.id LEFT JOIN aircraft AS ai ON ai.id = f.aircraft_id LEFT JOIN aircraft_type AS at ON ai.type = at.id LEFT JOIN authentication AS au ON fp.pilot_id = au.id WHERE f.airborne = 0

 AND start_date BETWEEN NOW() - INTERVAL Y DAY AND NOW()
 AND at.id IN ( Z1, Z2, Z3, Z... )

GROUP BY au.id HAVING SUM(f.duration)/600 <= X ORDER BY pilot</sql>

Flight with their location code (ICAO)

<syntaxhighlight lang="sql">SELECT flight.*, L1.icao_name AS departure_location, L2.icao_name AS arrival_location FROM flight LEFT JOIN location AS L1 ON flight.departure_location_id = L1.id LEFT JOIN location AS L2 ON flight.arrival_location_id = L2.id</sql>

Pilots without flight in the last 3 months

<syntaxhighlight lang="sql">SELECT

   CONCAT(a.last_name, ' ', a.first_name) AS pilot,
   IFNULL((SELECT CONCAT(DATE_FORMAT( f1.start_date, '%d/%m/%Y' ),' ', aircraft.callsign,' 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 aircraft ON aircraft.id=f1.aircraft_id
           WHERE fp.pilot_id=a.id 
           ORDER BY f1.start_date ASC LIMIT 1),'INCONNU') AS "Dernier Vol" 

FROM authentication 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 authentication AS au ON au.id=fp.pilot_id WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL 90 DAY AND NOW() GROUP BY au.id HAVING SUM(f.duration)/600 > 0) ORDER BY pilot</sql>

Error message queries

Flight without account movement

<SQL>SELECT DATE_FORMAT(flight.start_date,'%d/%m/%Y' ) AS Date, TIME_FORMAT(flight.start_date,'%H:%i' ) AS Time, aircraft.callsign AS Callsign, authentication.last_name AS Lastname, authentication.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 aircraft ON aircraft.id=flight.aircraft_id LEFT JOIN flight_pilot ON flight_pilot.flight_id=flight.id LEFT JOIN authentication ON authentication.id=flight_pilot.pilot_id WHERE flight_account_entry.account_entry_id IS NULL AND flight.airborne=0 AND flight_pilot.num=0</SQL>

list members without an account

<SQL>SELECT member.id, authentication.last_name, authentication.first_name FROM member LEFT JOIN account_member ON account_member.member_id=member.id LEFT JOIN authentication ON authentication.id=member.id WHERE authentication.activated=1 AND account_member.account_id IS NULL</SQL>

movement without an account

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

various queries

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

<SQL>SELECT * FROM `account` WHERE `name` LIKE 'account name to search'</SQL>

Get the counter account of an account entry according the id of one of both accounts and the amount value

<SQL>SELECT account.* FROM account_entry AS entry1 LEFT JOIN account ON account.id=entry1.account_id RIGHT JOIN account_entry AS entry2 ON entry2.flow_id=entry1.flow_id WHERE entry2.account_id=46 AND entry2.debit = 484.60</SQL>

Get the pilot owner of an account

<SQL>SELECT * FROM authentication RIGHT JOIN account ON authentication.id=account.owner_id WHERE account.id=114</SQL>

Get members list with membership ending before a specific date

<SQL>SELECT authentication.id, authentication.first_name, authentication.last_name FROM authentication RIGHT JOIN member ON member.id=authentication.id WHERE member.subscription<='2007-12-31'</SQL>