Export generator 2.0
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>