Export generator 3
Contents
- 1 Introduction
- 2 French administration Examples
- 2.1 Flights hours total per aircraft per year
- 2.2 Flights hours total per aircraft per year and per month
- 2.3 Number of members : male, female, less than 21 years and more than 21 years
- 2.4 Flights hours : less than 21 years, more than 21 years, male, female
- 2.5 Flights hours following nationality
- 2.6 Licence in the year
- 2.7 List of pilots with specific validity
- 2.8 Young list
- 2.9 Flights hours total per flight type
- 2.10 Number of visit on other airfield
- 2.11 Number of movements on based platform due to country flight
- 2.12 Number of take-off and landings on based airfield
- 2.13 Flights hours total per instructor
- 2.14 User list with up to date subscription
- 2.15 Validity per user
- 2.16 Flight hours per month
- 2.17 Flight hours without up to date validities
- 2.18 List of pilots who have flown without required validity
- 2.19 List of pilots who have flown less than X hours during last Y days
- 2.20 List of pilots who have flown less than X hours during last Y days on aircraft type Z
- 2.21 Pilots without flight in the last 3 months
- 2.22 Members list with age and birth date
- 2.23 Number of landings per pilot, per resource
- 2.24 Get members list with membership ending before a specific date
- 2.25 Get activated user list
- 2.26 User list coordinates
- 2.27 User list by profile
- 2.28 User list by validity
- 2.29 User list by profile
- 2.30 User list with validities viewer
- 3 Accounting
- 3.1 Global account balance
- 3.2 Global account balance of pilots who last subscription was 2 years ago
- 3.3 Global non null account balance
- 3.4 Account balance list
- 3.5 Balance per account category
- 3.6 Carry forwards
- 3.7 Payments list ordered by type
- 3.8 Get the details (specially the id) of an account according is name
- 3.9 Get the pilot owner of an account
- 3.10 Payment dispatching
- 3.11 Cheque deposit slip
- 3.12 Monthly revenue breakdown per account
- 4 Booking
- 5 Flight time management
- 5.1 Flight hours total per pilot
- 5.2 Flight hours total per pilot per profile
- 5.3 Flights hours total per instructor per year and per month
- 5.4 Computation of flight time per month of "local flight"
- 5.5 Computation of non-"local flight" time per month
- 5.6 List of flights have landed on a airfield
- 5.7 Total instructed flight hours
- 5.8 Total DCA instructed flight hours per instructor per month
- 5.9 Indemnified flight hours
- 5.10 Flight hours per instructor function to flight type
- 5.11 Aircraft(s) which no flight were done for at least 30 day(s)
- 5.12 Total tank quantity for each tank and for each aircraft type
- 5.13 Total tank quantity for each tank and for each aircraft
- 5.14 Fuel/Oil quantities per tank and aircraft type
- 5.15 Fuel/Oil sum for each aircraft
- 5.16 Fuel/Oil consumption
- 5.17 Flight with their location code (ICAO)
- 5.18 Flight list between two dates
- 5.19 Stats all flight hours per month per year
- 6 Error message queries
Introduction
The goal of this page is to propose a list of statistic generation queries (SQL).
Do not forget to read the OpenFlyers SQL stored functions and procedures page.
Please note that you can make "public" a report, ie. allows standard users to see and interact with it.
Extra field
For more flexibility, extra field can be defined per the user
Two extra field types are available:
- "external parameters" of following types:
- Whole number
- Text string
- Date
- Time
- Date and time
- database parameters (dbObject::something Value type) advanced options to access at the database field
Extra Field creation
- Go to Menu Admin/Reports/Structure/Extra Field(s)
In the bottom line add
- Fill the name field with a name that will be use within the SQL queries (prefixed with the $ character)
- Fill the label field with a name used to describe the parameter as you want it to appear within the export query form.
- Choose a Value type (the most common are at the beginning of the list).
- Click on "Add"
Then within your SQL query, you may add this parameter which will be replace by the value chosen by the user filling the export form.
If you define a parameter as a database parameters (for example dbObject::Authentication) then the form will display a combo with the list of users and your parameter will be replace by the id integer of the chosen person.
Extra Field creation examples
We create a new extra field for the current year:
- Name: $year
- Label: Year
- Value type: Year
Parameter Year must be fill in Reports/Structure/View form before to call the query (by default current year is filled when you call the page)
We create a new extra field to list the reservation of an aircraft:
- Name: $aircraftId
- Label: Aircraft
- Value type: dbObject::Aircaft
Then in Admin/Reports/Structure/Criteria we create a new query labeled "Aircraft booking" with the following query:
SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId
To use this report, we just have to select a "Aircraft" in the Reports/Structure/View form, to check "Aircraft booking" then to click on "View"
French administration Examples
Flights hours total per aircraft per year
SELECT name AS Callsign, YEAR( start_date ) AS Year, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR(start_date) = $year GROUP BY name UNION SELECT "Total", $year AS year, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight WHERE YEAR(start_date) = $year GROUP BY year
Flights hours total per aircraft per year and per month
SELECT aircraft_id AS Num, name, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num) AS Janu, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num) AS Febr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num) AS Marc, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num) AS Apri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num) AS May, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num) AS June, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num) AS July, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num) AS Augu, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num) AS Octo, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num) AS Nove, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num) AS Dece, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Sum FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND resource.virtual = 0 GROUP BY order_num UNION SELECT "Sum per","month", (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12), CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND resource.virtual = 0 UNION SELECT "Cumulative","per month", (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 2), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 3), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 4), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 5), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 6), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 7), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 8), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 9), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 10), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 11), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 12), $year FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND resource.virtual = 0
Number of members : male, female, less than 21 years and more than 21 years
SELECT IF ( (sex = 0), 'Men', 'Women' ) AS Sex, IF( ( $year - YEAR( birthdate ) >= 21), 'No', 'Yes') AS Young, COUNT( id ) AS Number FROM person WHERE activated='1' GROUP BY Sex, Young
Flights hours : less than 21 years, more than 21 years, male, female
SELECT IF( a.sex=0, 'Men','Women') AS Sexe, IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young, IF ((SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'double') AS DC, CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id LEFT JOIN person a ON a.id=fp.pilot_id WHERE YEAR( start_date ) = $year AND fp.num=0 GROUP BY Sex, Young, DC UNION SELECT IF ( (sex = 0), 'Male', 'Female' ) AS Sex,"All", IF ( num = 0, 'Total', 'DC') AS DC, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id LEFT JOIN person a ON a.id=fp.pilot_id WHERE fp.num=0 AND YEAR(start_date)=$year GROUP BY Sex UNION SELECT "All", IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young, IF ( num = 0, 'Total', 'DC') AS DC, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id LEFT JOIN person a ON a.id=fp.pilot_id WHERE YEAR( start_date ) = $year AND fp.num=0 GROUP BY Young UNION SELECT "Total", $year AS year, IF ( num = 0, 'Total', 'DC') AS DC, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id LEFT JOIN person a ON a.id=fp.pilot_id WHERE YEAR( start_date ) = $year AND fp.num=0 GROUP BY year
Flights hours following nationality
SELECT nationality.label, CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total FROM flight , flight_pilot fp, person a, nationality WHERE flight.id = fp.flight_id AND fp.pilot_id = a.id AND a.nationality = nationality.code GROUP BY nationality.code
Licence in the year
SELECT last_name AS Last_name, first_name AS First_name, IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young, IF ( (sex = 0), 'Male', 'Female' ) AS Sex, DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, validity_type.name AS 'Validity' FROM person LEFT JOIN validity ON person_id=person.id LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE YEAR(grant_date)=$year
List of pilots with specific validity
SELECT last_name AS Last_name, first_name AS First_name, validity_type.name AS 'Validity', DATE_FORMAT(validity.expire_date, '%m-%d-%Y') AS Expirate FROM person LEFT JOIN validity ON person_id=person.id LEFT JOIN validity_type ON validity_type.id=validity_type_id WHERE grant_date IS NULL AND validity_type.time_limitation=1 AND validity_type.name="Private Pilot License" AND person.activated=1 ORDER BY last_name, first_name
Note: replace name of validity_type.name by yours
Young list
SELECT last_name AS Last_name, first_name AS First_name, DATE_FORMAT(birthdate ,'%m-%d-%Y') AS Birthdate, IF ( (sex = 0), 'Male', 'Female' ) AS sex FROM person WHERE (2011-YEAR(birthdate))<=21
Flights hours total per flight type
SELECT ft.name AS Type_vol, IF ((SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'Double') AS DC, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_type ft ON ft.id & flight.flight_type_id LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id WHERE YEAR( start_date ) = $year AND fp.num=0 GROUP BY ft.id, DC HAVING (Name <> 'Instruction' OR DC <> 'Double') ORDER BY order_num ASC
Number of visit on other airfield
SELECT ap.icao_name AS ICAO, ap.name AS Name, COUNT( ap.icao_name ) AS nb_visite FROM flight f LEFT JOIN location AS ap ON f.departure_location_id = ap.id WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) OR (f.arrival_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND departure_location_id != arrival_location_id AND YEAR( start_date ) = $year GROUP BY icao_name ORDER BY nb_visite DESC
Number of movements on based platform due to country flight
SELECT ap.icao_name AS ICAO, ap.name AS Name, COUNT( ap.icao_name ) AS nb_flight FROM flight f LEFT JOIN location AS ap ON f.departure_location_id = ap.id WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) OR (f.arrival_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND departure_location_id != arrival_location_id AND YEAR( start_date ) = $year GROUP BY icao_name ORDER BY nb_flight DESC
Number of take-off and landings on based airfield
SELECT SUM( landing_number )*2 AS nb_mouvement FROM flight f, club c WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND (f.arrival_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND YEAR( start_date ) = $year
Flights hours total per instructor
SELECT last_name AS Last_name, first_name AS First_name, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id LEFT JOIN person a ON a.id=fp.pilot_id WHERE fp.num = 1 AND YEAR( start_date ) = $year GROUP BY a.id UNION SELECT "Total", $year AS year, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight,flight_pilot fp WHERE flight.id = fp.flight_id AND fp.num = 1 AND YEAR( start_date ) = $year GROUP BY year
User list with up to date subscription
SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity' FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE YEAR(validity.grant_date) = $year AND validity_type.id = $validityTypeId
Validity per user
SELECT last_name as "Last_name", first_name as "First_name", validity_type.name AS 'Validity', DATE_FORMAT(expire_date,'%d/%m/%Y') AS Expiry_date, if((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired", "Valid") AS Expired FROM validity_type LEFT JOIN validity ON validity.validity_type_id =validity_type.id LEFT JOIN person ON person.id=validity.person_id LEFT JOIN account ON person.id=account.owner_id -- Validity recently expired or about to expire WHERE -- Deactivated members account.activated = 1 AND -- Only active members for current year(N) and following (N+1) YEAR(grant_date)>=YEAR(NOW()) -- Exclude Night qualification AND validity_type.id != 6 AND person.id = $person ORDER BY 'Validity'
Flight hours per month
SELECT MONTH( start_date) AS months, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id WHERE YEAR(start_date) = $year GROUP BY months
Flight hours without up to date validities
SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS Date,last_name AS Nom, first_name AS Prénom, IF ((SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'DC') AS DC, resource.name AS Immat, CONCAT(FLOOR( duration /600), ':', TIME_FORMAT(SEC_TO_TIME(( duration /600 - FLOOR( duration /600))*3600),'%i')) AS Tdv, ft.name As Type_Vol, vt.name AS Qualif FROM flight LEFT JOIN flight_type_mandatory_validity_type fq ON fq.flight_type_id & flight.flight_type_id LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id LEFT JOIN person p ON p.id = fp.pilot_id LEFT JOIN validity_type vt ON vt.id = fq.validity_type_id LEFT JOIN validity v ON v.validity_type_id = fq.validity_type_id LEFT JOIN resource ON resource.id = flight.aircraft_id LEFT JOIN flight_type ft ON ft.id = fq.flight_type_id WHERE YEAR(start_date) = $year AND fp.num = 0 AND v.person_id = fp.pilot_id AND flight.start_date > v.expire_date ORDER BY Nom
List of pilots who have flown without required validity
SELECT CONCAT(a.last_name, ' ', a.first_name) AS pilot, vt.name AS 'Validity' FROM flight_type_mandatory_validity_type AS ftpvt LEFT JOIN flight_type AS ft ON ft.id = ftpvt.flight_type_id LEFT JOIN flight AS f ON f.flight_type_id & ft.id LEFT JOIN resource AS ai ON ai.id = f.aircraft_id LEFT JOIN resource_type AS at ON ai.resource_type_id = at.id LEFT JOIN aircraft_type_validity_type AS atvt ON at.id = atvt.aircraft_type_id LEFT JOIN flight_pilot AS fp ON f.id = fp.flight_id LEFT JOIN person AS a ON a.id = fp.pilot_id LEFT JOIN validity_type AS vt ON (vt.id = ftpvt.validity_type_id OR vt.id = atvt.validity_type_id) WHERE f.airborne = 0 AND ROW(a.id, vt.id) NOT IN (SELECT person_id, validity_type_id FROM validity) AND vt.experience_formula IS NULL GROUP BY a.id, vt.id ORDER BY pilot, vt.name
List of pilots who have flown less than X hours during last Y days
SELECT CONCAT(au.last_name, ' ', au.first_name) AS pilot, TIME_FORMAT( SEC_TO_TIME( SUM(f.duration)*6 ), '%H:%i' ) AS total_time FROM flight AS f RIGHT JOIN flight_pilot AS fp ON fp.flight_id=f.id LEFT JOIN aircraft AS ai ON ai.id = f.aircraft_id LEFT JOIN person AS au ON fp.pilot_id = au.id WHERE f.airborne = 0 AND start_date BETWEEN NOW() - INTERVAL Y DAY AND NOW() GROUP BY au.id HAVING SUM(f.duration)/600 < X ORDER BY pilot
List of pilots who have flown less than X hours during last Y days on aircraft type Z
SELECT CONCAT(au.last_name, ' ', au.first_name) AS pilot, TIME_FORMAT( SEC_TO_TIME( SUM(f.duration)*6 ), '%H:%i' ) AS total_time FROM flight AS f RIGHT JOIN flight_pilot AS fp ON fp.flight_id=f.id LEFT JOIN resource AS ai ON ai.id = f.aircraft_id LEFT JOIN resource_type AS at ON ai.resource_type_id = at.id LEFT JOIN person AS au ON fp.pilot_id = au.id WHERE f.airborne = 0 AND start_date BETWEEN NOW() - INTERVAL Y DAY AND NOW() AND at.id IN ( Z1, Z2, Z3, Z... ) GROUP BY au.id HAVING SUM(f.duration)/600 <= X ORDER BY pilot
Pilots without flight in the last 3 months
SELECT CONCAT(UPPER(a.last_name), ' ', a.first_name) AS pilot, IFNULL((SELECT CONCAT(DATE_FORMAT( f1.start_date, '%d/%m/%Y' ),' ', resource.name,' Duration: ', TIME_FORMAT(SEC_TO_TIME((SUM( f1.duration )/600 - FLOOR(SUM( f1.duration )/600))*3600),'%H h %i')) FROM flight AS f1 LEFT JOIN flight_pilot AS fp ON fp.flight_id=f1.id LEFT JOIN resource ON resource.id=f1.aircraft_id WHERE fp.pilot_id=a.id ORDER BY f1.start_date ASC LIMIT 1),'UNKNOWN') AS "Last flight" FROM person AS a LEFT JOIN profile AS p1 ON (a.profile & p1.id) WHERE a.activated=1 AND p1.name='Pilot' AND a.id NOT IN ( SELECT au.id FROM flight AS f LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id LEFT JOIN person AS au ON au.id=fp.pilot_id WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL 90 DAY AND NOW() GROUP BY au.id HAVING SUM(f.duration)/600 > 0) ORDER BY pilot
Members list with age and birth date
(SELECT last_name AS Last_name, first_name AS First_name, DATE_FORMAT(birthdate ,'%d-%m-%Y') AS Birth_date, IF ( (sex = 0), 'Man', 'Woman' ) AS sex, (DATE_FORMAT(DATE('$day'), '%Y') - DATE_FORMAT(birthdate, '%Y') - (SELECT DATE_FORMAT(DATE('$day'), '00-%m-%d') < DATE_FORMAT( birthdate, '00-%m-%d'))) AS 'age', DATE_FORMAT(DATE('$day'),'%d-%m-%Y') AS on_date, IF((DATE_FORMAT(DATE('$day'), '%Y') - DATE_FORMAT(birthdate, '%Y') - (SELECT DATE_FORMAT(DATE('$day'), '00-%m-%d') < DATE_FORMAT( birthdate, '00-%m-%d')))>=21, '>= 21', '< 21') AS Major, -- inscription_date, -- member.subscription, -- entry.account_date as Adhesion DATE_FORMAT(entry.account_date,'%d-%m-%Y') as Grant_date -- ,MAX(entry.account_date) -- , $day -- , entry.flow_id -- , account.name FROM person RIGHT JOIN validity ON validity.person_id = person.id RIGHT JOIN account acc2 on acc2.owner_id = person.id RIGHT JOIN account_entry linked_entry on linked_entry.account_id = acc2.id RIGHT JOIN account_entry entry on linked_entry.flow_id = entry.flow_id RIGHT JOIN account on entry.account_id = account.id WHERE year(validity.grant_date) = $year AND account.name = 'Cotisations ACB' AND (year(entry.account_date)=$year OR (year(entry.account_date)=$year -1 AND month(entry.account_date)=12) ) AND entry.account_date < DATE('$day') ORDER BY Last_name LIMIT 999999 ) UNION ( SELECT COUNT(*) AS Last_name, COUNT(*) AS First_name, COUNT(*) AS Birth_date, COUNT(*) AS sex, COUNT(*) AS 'age', COUNT(*) AS on_date, COUNT(*) AS Major, COUNT(*) as Grant_date FROM person RIGHT JOIN validity ON validity.person_id = person.id RIGHT JOIN account acc2 on acc2.owner_id = person.id RIGHT JOIN account_entry linked_entry on linked_entry.account_id = acc2.id RIGHT JOIN account_entry entry on linked_entry.flow_id = entry.flow_id RIGHT JOIN account on entry.account_id = account.id WHERE year(validity.grant_date) = $year AND account.name = 'Cotisations ACB' AND (year(entry.account_date)=$year OR (year(entry.account_date)=$year -1 AND month(entry.account_date)=12) ) AND entry.account_date < DATE('$day') )
Number of landings per pilot, per resource
SELECT last_name as "Last_name", first_name as "First_name", (IF(DATE(valid.grant_date) < date(NOW()),'B', 'E')) as 'S', -- (IF(DATE(valid.grant_date) < date(NOW()),valid_typ.name, 'Eleve')) as 'S2', (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS atterrissages, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id -- AND flight.aircraft_id = 1 AND flight.aircraft_id = 18 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS KT, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id -- AND flight.aircraft_id = 10 AND flight.aircraft_id = 2 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS QR, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id -- AND flight.aircraft_id = 2 AND flight.aircraft_id = 3 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS KH, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id -- AND flight.aircraft_id = 3 AND flight.aircraft_id = 4 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS XF, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND (flight.aircraft_id = 2 OR flight.aircraft_id = 3 OR flight.aircraft_id = 4 OR flight.aircraft_id = 18) AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS DR400, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND flight.aircraft_id = 23 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS DR500, (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND flight.aircraft_id = 8 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS 'MC-IP', (SELECT SUM(landing_number) FROM flight LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE flight_pilot.pilot_id = a.id AND flight.aircraft_id = 21 AND flight.start_date BETWEEN NOW() - INTERVAL 3 MONTH AND NOW()) AS 'CAP10-DL' FROM person a LEFT JOIN profile AS p1 ON (a.profile & p1.id) LEFT JOIN validity valid ON valid.person_id = a.id LEFT JOIN validity_type valid_typ ON valid_typ.id = valid.validity_type_id WHERE YEAR(grant_date) >= YEAR(NOW()) AND a.activated=1 AND p1.name='Pilot' GROUP BY last_name, first_name ORDER BY last_name
Get members list with membership ending before a specific date
Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.
SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity' FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE validity.grant_date <= '2007-12-31' AND validity_type.id = $validityTypeId
Get activated user list
SELECT id, last_name, first_name FROM person WHERE activated=1
User list coordinates
SELECT last_name, first_name, name AS login, email, address, zipcode, city AS Ville, state AS etat_region, country AS pays, home_phone AS tel_domicile, work_phone AS tel_travail, cell_phone AS tel_mobile FROM person WHERE activated=1
User list by profile
Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.
SELECT last_name AS Lastname, first_name AS Firstname, validity_type.name AS Validity, grant_date AS Date, profile.name AS Profile FROM person LEFT JOIN profile ON (person.profile & profile.id) LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE YEAR(grant_date) >= $year AND validity_type.id = $validityTypeId ORDER BY Profile, Lastname, Firstname
User list by validity
SELECT validity_type.name AS 'Validity', DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date, last_name AS Name, first_name AS Firstname, ident_value AS Comment FROM validity_type LEFT JOIN validity ON validity.validity_type_id =validity_type.id LEFT JOIN person ON person.id=validity.person_id WHERE YEAR(grant_date)>$year AND MONTH(grant_date)>=1 ORDER BY 'Validity', Name, Firstname
User list by profile
Name of profile (i.e. Pilot), shall be adapted to the profile designation that want to be listed
SELECT last_name AS Lastname, first_name AS Firstname, p1.name AS Profil FROM person AS a1 LEFT JOIN profile AS p1 ON (a1.profile & p1.id) WHERE a1.activated=1 AND p1.name='Pilot' ORDER BY last_name,first_name
User list with validities viewer
SELECT last_name AS Last_name, first_name AS First_name, validity_type.name AS 'Validity', DATE_FORMAT(expire_date,'%d/%m/%Y') AS Expiry_date, if((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS Expired, -- ident_value AS Commentaire, DATE_FORMAT(grant_date,'%d/%m/%Y') AS Grant_date FROM validity_type LEFT JOIN validity ON validity.validity_type_id =validity_type.id LEFT JOIN person ON person.id=validity.person_id -- Validity recently expired or about to expire WHERE period_diff(DATE_FORMAT(expire_date,'%y%m'), DATE_FORMAT(Now(),'%y%m')) < 2 AND -- period_diff(DATE_FORMAT(expire_date,'%y%m'), -- DATE_FORMAT(Now(),'%y%m')) > -2 AND -- Deactivated members person.activated = 1 -- Exclude Night qualification AND validity_type.id <> 6 ORDER BY Last_name, First_name, validity_type.name
Accounting
Global account balance
List whole account balance at the end of a given date (Extra field $endDateAccount, type dateTime, should be defined)
SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom, IFNULL(sumAccountEntry(account.id,'$endDateAccount'),0) AS solde FROM account LEFT JOIN person ON person.id=account.owner_id WHERE account.activated=1 ORDER BY export_account
Global account balance of pilots who last subscription was 2 years ago
Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.
SELECT last_name, first_name, validity_type.name as 'Validity', grant_date, IFNULL((SELECT SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry WHERE account_entry.account_id = a.id AND account_entry.account_date > (SELECT balance_date FROM balance_date ORDER BY balance_date DESC LIMIT 1)),0)+balance.credit-balance.debit AS Total FROM `person` LEFT JOIN validity ON validity.person_id = person.id LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id LEFT JOIN account AS a ON a.owner_id = person.id AND category = 2 LEFT JOIN balance ON balance.account_id = a.id AND balance.balance_date_id = (SELECT id FROM balance_date ORDER BY balance_date ASC LIMIT 1) WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR) AND validity_type.id = $validityTypeId GROUP BY person.id ORDER BY last_name,first_name
Global non null account balance
- List whole account balance at the end of a given date (Extra field $endDateAccount has to be defined and should be of DateTime value type) but for only non null account balance :
SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom, IFNULL(sumAccountEntry(account.id,'$endDateAccount'),0) AS solde FROM account LEFT JOIN person ON person.id=account.owner_id WHERE account.activated=1 AND sumAccountEntry(account.id,'$endDateAccount') <> 0 ORDER BY export_account
- List whole account balance at the end of a given year (Extra field $year has to be defined and should be of Year value type) but for only non null account balance :
SELECT id, export_account, name, sumAccountEntry(id,'$year-12-31 23:59:59') AS solde FROM account WHERE activated=1 AND sumAccountEntry(id,'$year-12-31 23:59:59') <> 0 ORDER BY export_account
Account balance list
Export_generator_3.0#Extra field $endDateAccount has to be defined first and should be of value type DateTime.
List of pilot account balance
SELECT person.last_name, person.first_name, account_type.name AS account_type_name, sumAccountEntry(account.id, '$endDateAccount') AS balance FROM account LEFT JOIN account_type ON (account.account_type=account_type.id) LEFT JOIN person ON (account.owner_id=person.id) WHERE account.category=2 AND account.activated=1 ORDER BY last_name, first_name
List of all account debit, credit
SELECT account.export_account AS 'Export account', CASE WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('Member account ', account_type.name, ' of ', person.last_name, ' ', person.first_name) WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name) WHEN account.category=1 THEN CONCAT('Other account ', account.name) WHEN account.category=4 THEN CONCAT('Supplier account ', account.name) WHEN account.category=6 THEN CONCAT('VAT account ', account.name) WHEN account.category=7 THEN CONCAT('Expense account ', account.name) WHEN account.category=8 THEN CONCAT('Treasury account ', account.name) WHEN account.category=9 THEN CONCAT('Product account ', account.name) ELSE account.name END AS account_name, IF (sumAccountEntry(account.id,'$endDateAccount')<0, sumAccountEntry(account.id,'$endDateAccount'), 0) AS Debit, IF (sumAccountEntry(account.id,'$endDateAccount')<0, 0, sumAccountEntry(account.id,'$endDateAccount') ) AS Credit FROM account LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3)) LEFT JOIN person ON (account.owner_id = person.id AND account.category=2) LEFT JOIN aircraft ON (account.owner_id = aircraft.id AND account.category=3) LEFT JOIN resource ON aircraft.id = resource.id WHERE person.activated = 1 GROUP BY account.id ORDER BY account.export_account ASC
Balance per account category
SELECT CASE WHEN account_type.name IS NOT NULL AND (account.category=2 OR account.category=3) THEN account_type.name WHEN account.category=1 THEN CONCAT('Other account ', account.name) WHEN account.category=4 THEN CONCAT('Supplier account ', account.name) WHEN account.category=6 THEN CONCAT('VAT account ', account.name) WHEN account.category=7 THEN CONCAT('Expense account ', account.name) WHEN account.category=8 THEN CONCAT('Treasury account ', account.name) WHEN account.category=9 THEN CONCAT('Product account ', account.name) ELSE account.name END AS 'Account type', IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde FROM account LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3)) GROUP BY account.category
Carry forwards
SELECT IF (account.category = 2, CONCAT(person.last_name,' ',person.first_name,' (',account_type.name,')'), IF (account.category = 3,CONCAT(resource.name,' (',account_type.name,')'),account.name)) AS Nom, export_account AS Code_comptable, debit AS 'Debit', credit AS 'Credit' FROM account LEFT JOIN balance ON balance.account_id=account.id LEFT JOIN person ON person.id=account.owner_id AND account.category = 2 LEFT JOIN aircraft ON aircraft.id=account.owner_id AND account.category = 3 LEFT JOIN resource ON resource.id=aircraft.id LEFT JOIN account_type ON account_type.id=account.account_type WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) UNION SELECT 'zzzzzzz', 'Total', SUM(debit) AS 'Debit', SUM(credit) AS 'Credit' FROM account LEFT JOIN balance ON balance.account_id=account.id WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) ORDER BY Nom ASC
Payments list ordered by type
SELECT payment_type.name AS Type, if(account.category = 2, CONCAT( p.last_name, ' ', p.first_name),account.name) AS Name, DATE_FORMAT(ae.account_date, '%d %m %Y' ) AS Date, ae.payment_description, ae.credit AS Description FROM account_entry AS ae LEFT JOIN payment_type ON payment_type.id = ae.payment_type LEFT JOIN account ON account.id = ae.account_id LEFT JOIN person AS p ON p.id = account.owner_id WHERE ae.payment_type IS NOT NULL AND credit > 0 AND YEAR(ae.account_date) >= $year ORDER BY ae.payment_type, ae.account_date, p.last_name, p.first_name
Get the details (specially the id) of an account according is name
SELECT * FROM `account` WHERE `name` LIKE 'account name to search'
Get the pilot owner of an account
SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114
Payment dispatching
SELECT payment_type as Num, payment_type.name as name, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type = Num) AS Janu, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type = Num) AS Febr, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type = Num) AS Marc, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type = Num) AS Apri, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type = Num) AS May, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type = Num) AS June, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type = Num) AS July, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type = Num) AS Augu, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type = Num) AS Sept, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type = Num) AS Octo, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type = Num) AS Nove, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type = Num) AS Dece, SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total FROM account_entry LEFT JOIN payment_type ON payment_type = payment_type.id WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL GROUP BY payment_type UNION SELECT 'Sum per', 'month', (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type IS NOT NULL), SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total FROM account_entry LEFT JOIN payment_type ON payment_type = payment_type.id WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL
Cheque deposit slip
SELECT date_format(ae.registration_date, "%d/%m/%Y") as "date", a.name, ae.credit as montant, ae.payment_description as libelle, (SELECT a2.name FROM account_entry ae2 JOIN account a2 ON a2.id = ae2.account_id WHERE ae2.flow_id = ae.flow_id AND ae2.debit > 0 LIMIT 0,1) as compte_banque, ae.comments as commentaire FROM account_entry ae JOIN account a ON ae.account_id = a.id WHERE a.account_type = 1 AND ae.validated = 1 AND ae.credit > 0 ORDER BY ae.registration_date DESC
Monthly revenue breakdown per account
SELECT name, (IF (sumAccountEntry(a.id,'$year-02-01')<0, sumAccountEntry(a.id,'$year-02-01'), 0) - IF (sumAccountEntry(a.id,'$year-01-01')<0, sumAccountEntry(a.id,'$year-01-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-02-01')<0, 0, sumAccountEntry(a.id,'$year-02-01')) - IF (sumAccountEntry(a.id,'$year-01-01')<0, 0, sumAccountEntry(a.id,'$year-01-01'))) AS Janu, (IF (sumAccountEntry(a.id,'$year-03-01')<0, sumAccountEntry(a.id,'$year-03-01'), 0) - IF (sumAccountEntry(a.id,'$year-02-01')<0, sumAccountEntry(a.id,'$year-02-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-03-01')<0, 0, sumAccountEntry(a.id,'$year-03-01')) - IF (sumAccountEntry(a.id,'$year-02-01')<0, 0, sumAccountEntry(a.id,'$year-02-01'))) AS Febr, (IF (sumAccountEntry(a.id,'$year-04-01')<0, sumAccountEntry(a.id,'$year-04-01'), 0) - IF (sumAccountEntry(a.id,'$year-03-01')<0, sumAccountEntry(a.id,'$year-03-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-04-01')<0, 0, sumAccountEntry(a.id,'$year-04-01')) - IF (sumAccountEntry(a.id,'$year-03-01')<0, 0, sumAccountEntry(a.id,'$year-03-01'))) AS Marc, (IF (sumAccountEntry(a.id,'$year-05-01')<0, sumAccountEntry(a.id,'$year-05-01'), 0) - IF (sumAccountEntry(a.id,'$year-04-01')<0, sumAccountEntry(a.id,'$year-04-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-05-01')<0, 0, sumAccountEntry(a.id,'$year-05-01')) - IF (sumAccountEntry(a.id,'$year-04-01')<0, 0, sumAccountEntry(a.id,'$year-04-01'))) AS April, (IF (sumAccountEntry(a.id,'$year-06-01')<0, sumAccountEntry(a.id,'$year-06-01'), 0) - IF (sumAccountEntry(a.id,'$year-05-01')<0, sumAccountEntry(a.id,'$year-05-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-06-01')<0, 0, sumAccountEntry(a.id,'$year-06-01')) - IF (sumAccountEntry(a.id,'$year-05-01')<0, 0, sumAccountEntry(a.id,'$year-05-01'))) AS May, (IF (sumAccountEntry(a.id,'$year-07-01')<0, sumAccountEntry(a.id,'$year-07-01'), 0) - IF (sumAccountEntry(a.id,'$year-06-01')<0, sumAccountEntry(a.id,'$year-06-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-07-01')<0, 0, sumAccountEntry(a.id,'$year-07-01')) - IF (sumAccountEntry(a.id,'$year-06-01')<0, 0, sumAccountEntry(a.id,'$year-06-01'))) AS June, (IF (sumAccountEntry(a.id,'$year-08-01')<0, sumAccountEntry(a.id,'$year-08-01'), 0) - IF (sumAccountEntry(a.id,'$year-07-01')<0, sumAccountEntry(a.id,'$year-07-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-08-01')<0, 0, sumAccountEntry(a.id,'$year-08-01')) - IF (sumAccountEntry(a.id,'$year-07-01')<0, 0, sumAccountEntry(a.id,'$year-07-01'))) AS July, (IF (sumAccountEntry(a.id,'$year-09-01')<0, sumAccountEntry(a.id,'$year-09-01'), 0) - IF (sumAccountEntry(a.id,'$year-08-01')<0, sumAccountEntry(a.id,'$year-08-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-09-01')<0, 0, sumAccountEntry(a.id,'$year-09-01')) - IF (sumAccountEntry(a.id,'$year-08-01')<0, 0, sumAccountEntry(a.id,'$year-08-01'))) AS Augu, (IF (sumAccountEntry(a.id,'$year-10-01')<0, sumAccountEntry(a.id,'$year-10-01'), 0) - IF (sumAccountEntry(a.id,'$year-09-01')<0, sumAccountEntry(a.id,'$year-09-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-10-01')<0, 0, sumAccountEntry(a.id,'$year-10-01')) - IF (sumAccountEntry(a.id,'$year-09-01')<0, 0, sumAccountEntry(a.id,'$year-09-01'))) AS Sept, (IF (sumAccountEntry(a.id,'$year-11-01')<0, sumAccountEntry(a.id,'$year-11-01'), 0) - IF (sumAccountEntry(a.id,'$year-10-01')<0, sumAccountEntry(a.id,'$year-10-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-11-01')<0, 0, sumAccountEntry(a.id,'$year-11-01')) - IF (sumAccountEntry(a.id,'$year-10-01')<0, 0, sumAccountEntry(a.id,'$year-10-01'))) AS Octo, (IF (sumAccountEntry(a.id,'$year-12-01')<0, sumAccountEntry(a.id,'$year-12-01'), 0) - IF (sumAccountEntry(a.id,'$year-11-01')<0, sumAccountEntry(a.id,'$year-11-01'), 0)) + (IF (sumAccountEntry(a.id,'$year-12-01')<0, 0, sumAccountEntry(a.id,'$year-12-01')) - IF (sumAccountEntry(a.id,'$year-11-01')<0, 0, sumAccountEntry(a.id,'$year-11-01'))) AS Nove, (IF (sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))<0, sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH)), 0) - IF (sumAccountEntry(a.id,'$year-12-01')<0, sumAccountEntry(a.id,'$year-12-01'), 0)) + (IF (sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))<0, 0, sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))) - IF (sumAccountEntry(a.id,'$year-12-01')<0, 0, sumAccountEntry(a.id,'$year-12-01'))) AS Dece FROM account AS a WHERE a.export_account LIKE '7%' AND a.export_account NOT LIKE '709%' UNION SELECT "Total", (IF (sumAccountEntry(a.id,'$year-02-01')<0, SUM(sumAccountEntry(a.id,'$year-02-01')), 0) - IF (sumAccountEntry(a.id,'$year-01-01')<0, SUM(sumAccountEntry(a.id,'$year-01-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-02-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-02-01'))) - IF (sumAccountEntry(a.id,'$year-01-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-01-01')))) AS Janu, (IF (sumAccountEntry(a.id,'$year-03-01')<0, SUM(sumAccountEntry(a.id,'$year-03-01')), 0) - IF (sumAccountEntry(a.id,'$year-02-01')<0, SUM(sumAccountEntry(a.id,'$year-02-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-03-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-03-01'))) - IF (sumAccountEntry(a.id,'$year-02-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-02-01')))) AS Febr, (IF (sumAccountEntry(a.id,'$year-04-01')<0, SUM(sumAccountEntry(a.id,'$year-04-01')), 0) - IF (sumAccountEntry(a.id,'$year-03-01')<0, SUM(sumAccountEntry(a.id,'$year-03-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-04-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-04-01'))) - IF (sumAccountEntry(a.id,'$year-03-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-03-01')))) AS Marc, (IF (sumAccountEntry(a.id,'$year-05-01')<0, SUM(sumAccountEntry(a.id,'$year-05-01')), 0) - IF (sumAccountEntry(a.id,'$year-04-01')<0, SUM(sumAccountEntry(a.id,'$year-04-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-05-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-05-01'))) - IF (sumAccountEntry(a.id,'$year-04-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-04-01')))) AS April, (IF (sumAccountEntry(a.id,'$year-06-01')<0, SUM(sumAccountEntry(a.id,'$year-06-01')), 0) - IF (sumAccountEntry(a.id,'$year-05-01')<0, SUM(sumAccountEntry(a.id,'$year-05-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-06-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-06-01'))) - IF (sumAccountEntry(a.id,'$year-05-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-05-01')))) AS May, (IF (sumAccountEntry(a.id,'$year-07-01')<0, SUM(sumAccountEntry(a.id,'$year-07-01')), 0) - IF (sumAccountEntry(a.id,'$year-06-01')<0, SUM(sumAccountEntry(a.id,'$year-06-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-07-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-07-01'))) - IF (sumAccountEntry(a.id,'$year-06-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-06-01')))) AS June, (IF (sumAccountEntry(a.id,'$year-08-01')<0, SUM(sumAccountEntry(a.id,'$year-08-01')), 0) - IF (sumAccountEntry(a.id,'$year-07-01')<0, SUM(sumAccountEntry(a.id,'$year-07-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-08-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-08-01'))) - IF (sumAccountEntry(a.id,'$year-07-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-07-01')))) AS July, (IF (sumAccountEntry(a.id,'$year-09-01')<0, SUM(sumAccountEntry(a.id,'$year-09-01')), 0) - IF (sumAccountEntry(a.id,'$year-08-01')<0, SUM(sumAccountEntry(a.id,'$year-08-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-09-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-09-01'))) - IF (sumAccountEntry(a.id,'$year-08-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-08-01')))) AS Augu, (IF (sumAccountEntry(a.id,'$year-10-01')<0, SUM(sumAccountEntry(a.id,'$year-10-01')), 0) - IF (sumAccountEntry(a.id,'$year-09-01')<0, SUM(sumAccountEntry(a.id,'$year-09-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-10-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-10-01'))) - IF (sumAccountEntry(a.id,'$year-09-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-09-01')))) AS Sept, (IF (sumAccountEntry(a.id,'$year-11-01')<0, SUM(sumAccountEntry(a.id,'$year-11-01')), 0) - IF (sumAccountEntry(a.id,'$year-10-01')<0, SUM(sumAccountEntry(a.id,'$year-10-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-11-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-11-01'))) - IF (sumAccountEntry(a.id,'$year-10-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-10-01')))) AS Octo, (IF (sumAccountEntry(a.id,'$year-12-01')<0, SUM(sumAccountEntry(a.id,'$year-12-01')), 0) - IF (sumAccountEntry(a.id,'$year-11-01')<0, SUM(sumAccountEntry(a.id,'$year-11-01')), 0)) + (IF (sumAccountEntry(a.id,'$year-12-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-12-01'))) - IF (sumAccountEntry(a.id,'$year-11-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-11-01')))) AS Nove, (IF (sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))<0, SUM(sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))), 0) - IF (sumAccountEntry(a.id,'$year-12-01')<0, SUM(sumAccountEntry(a.id,'$year-12-01')), 0)) + (IF (sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))<0, 0, SUM(sumAccountEntry(a.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH)))) - IF (sumAccountEntry(a.id,'$year-12-01')<0, 0, SUM(sumAccountEntry(a.id,'$year-12-01')))) AS Dece FROM account AS a WHERE export_account LIKE '7%' AND export_account NOT LIKE '709%'
Booking
List of booking for maintenance ordered by resource and date with name of the responsible
SELECT resource.name AS 'Resource name', booking.start_date AS 'Start date', TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date) AS 'booking duration', person.first_name AS 'First name', person.last_name AS 'Last name' FROM booking LEFT JOIN log ON log.field_value=booking.id LEFT JOIN journal ON journal.id=log.journal_id LEFT JOIN person ON person.name=journal.login LEFT JOIN resource ON resource.id = booking.resource_id WHERE (booking.slot_type=2) AND (log.action='INSERT') AND (log.table_name='booking') AND (log.field_name='id') AND YEAR(booking.start_date) = $year ORDER BY resource.name, booking.start_date
Flight time management
Flight hours total per pilot
SELECT CONCAT(last_name,' ',first_name) AS Nom, IF (( SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'double') AS Solo_DC, CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id LEFT JOIN person a ON a.id=fp.pilot_id WHERE YEAR( start_date ) = $year AND fp.num=0 GROUP BY Nom, Solo_DC
Flight hours total per pilot per profile
SELECT profile.name AS Profil, CONCAT(last_name,' ',first_name) AS Nom, IF (( SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'double') AS Solo_DC, CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id LEFT JOIN person ON person.id=fp.pilot_id LEFT JOIN profile ON (person.profile & profile.id) WHERE YEAR( start_date ) = $year AND fp.num=0 GROUP BY Profil, Nom, Solo_DC
Flights hours total per instructor per year and per month
SELECT UPPER(last_name) AS Last_name, first_name AS First_name, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Janv, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Fevr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Mars, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Avri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Mai, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Juin, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Juil, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Aout, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Oct, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Nov, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1) AS Dece, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id LEFT JOIN person a ON a.id=fp.pilot_id LEFT JOIN profile AS p1 ON (a.profile & p1.id) WHERE a.activated=1 AND p1.name='Instructor' AND fp.num = 1 AND YEAR( start_date ) = $year GROUP BY a.id UNION SELECT "Total","Month", (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.num = 1), CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id LEFT JOIN person a ON a.id=fp.pilot_id LEFT JOIN profile AS p1 ON (a.profile & p1.id) WHERE a.activated=1 AND p1.name='Instructor' AND fp.num = 1 AND YEAR( start_date ) = $year UNION SELECT "Accumulation","per month", (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 1 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 2 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 3 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 4 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 5 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 6 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 7 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 8 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 9 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 10 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 11 AND flight_pilot.num = 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 12 AND flight_pilot.num = 1), CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id LEFT JOIN person a ON a.id=fp.pilot_id LEFT JOIN profile AS p1 ON (a.profile & p1.id) WHERE a.activated=1 AND p1.name='Instructor' AND fp.num = 1 AND YEAR( start_date ) = $year
Computation of flight time per month of "local flight"
"Local flight" is defined as a flight less than 1h30 and with the same departure and destination
SELECT aircraft_id AS Num, name AS Immat, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Janv, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Fevr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Mars, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Avri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Mai, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Juin, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Juil, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Aout, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Octo, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num AND departure_location_id = arrival_location_id AND duration < 90*600) AS Nove, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num ) AS Dece, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND resource.virtual = 0 AND departure_location_id = arrival_location_id AND duration < 90*600 GROUP BY order_num
Computation of non-"local flight" time per month
SELECT aircraft_id AS Num, name AS Immat, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Janv, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Fevr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Mars, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Avri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Mai, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Juin, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Juil, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Aout, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Octo, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Nove, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id)) AS Dece, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND resource.virtual = 0 AND ((departure_location_id = arrival_location_id AND duration >= 90*600) OR departure_location_id <> arrival_location_id) GROUP BY order_num
List of flights have landed on a airfield
Variable $icao shall be defined (dbObject:Location)
SELECT DATE_FORMAT(flight.start_date,'%d/%m/%Y') AS Date, resource.name AS immatriculation, CONCAT(FLOOR( duration/600),':',TIME_FORMAT(SEC_TO_TIME((duration/600 - FLOOR(duration/600))*3600),'%i')) AS Durée, person.last_name AS Nom, person.first_name AS Prénom, l1.icao_name AS Départ, l2.icao_name AS Arrivée FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id LEFT JOIN person ON person.id = flight_pilot.pilot_id LEFT JOIN location AS l1 ON l1.id = flight.departure_location_id LEFT JOIN location AS l2 ON l2.id = flight.arrival_location_id WHERE flight_pilot.num = 0 AND ( l1.icao_name LIKE '$icao' OR l2.icao_name LIKE '$icao') GROUP BY flight.id
Total instructed flight hours
SELECT UPPER(last_name) AS Last_name, first_name AS First_name, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id LEFT JOIN person a ON a.id=fp.pilot_id WHERE fp.num = 1 AND a.profile = 8 AND YEAR( start_date ) = $year GROUP BY a.id UNION SELECT 'Total', $year AS year, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight,flight_pilot fp, person a WHERE flight.id = fp.flight_id AND a.profile = 8 AND fp.num = 1 AND YEAR( start_date ) = $year GROUP BY year
Total DCA instructed flight hours per instructor per month
SELECT UPPER(last_name) AS Nom, first_name AS Prénom, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Janv, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Fevr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Mars, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Avri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Mai, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Juin, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Juil, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Aout, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Oct, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Nov, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Dece, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i') ) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id LEFT JOIN person a ON a.id=fp.pilot_id LEFT JOIN profile AS p1 ON (a.profile & p1.id) WHERE a.activated=1 AND p1.name='Instructor' AND fp.num = 1 AND YEAR( start_date ) = $year AND flight.flight_type_id & 8192 != 0 GROUP BY a.id UNION SELECT "Sum per","month", (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Janv, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Fevr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Mars, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Avri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Mai, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Juin, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Juil, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Aout, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Oct, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Nov, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.num = 1 AND flight.flight_type_id & 8192 != 0) AS Dece, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i') ) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id WHERE fp.num = 1 AND YEAR( start_date ) = $year AND flight.flight_type_id & 8192 != 0
Indemnified flight hours
SELECT UPPER(last_name) AS Nom, first_name AS Prénom, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Janv, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Fevr, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Mars, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)))AS Avri, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)))AS Mai, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)))AS Juin, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)))AS Juil, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)))AS Aout, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)))AS Sept, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)))AS Oct, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)))AS Nov, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = a.id AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)))AS Dece, CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id LEFT JOIN person a ON a.id = fp.pilot_id LEFT JOIN profile AS p1 ON (a.profile & p1.id) WHERE a.activated=1 AND p1.name='Instructor' AND YEAR(start_date) = $year AND ((fp.num = 0 AND flight.flight_type_id & 16 != 0) OR(fp.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0)) GROUP BY a.id UNION SELECT "Sum per", "month", (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 1 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Janv, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 2 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Fevr, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 3 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Mars, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 4 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Avri, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 5 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Mai, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 6 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Juin, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 7 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Juil, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 8 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Aout, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 9 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Sept, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 10 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Oct, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 11 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Nov, (SELECT CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date) = $year AND MONTH(start_date)= 12 AND ((flight_pilot.num = 0 AND flight.flight_type_id & 16 != 0) OR(flight_pilot.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))) AS Dece, CONCAT(FLOOR(SUM(duration)/600),':',TIME_FORMAT(SEC_TO_TIME((SUM(duration)/600 - FLOOR(SUM(duration)/600))*3600),'%i')) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id WHERE YEAR(start_date) = $year AND ((fp.num = 0 AND flight.flight_type_id & 16 != 0) OR(fp.num = 1 AND flight.flight_type_id & 8192 = 0 AND flight.flight_type_id & 256 = 0))
Flight hours per instructor function to flight type
- Variable $flightTypeId should be defined first and should be a dbOject::FlightType value type.
SELECT UPPER(last_name) AS Nom, first_name AS Prénom, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Janv, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Fevr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Mars, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Avri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Mai, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Juin, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Juil, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Aout, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Oct, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Nov, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = a.id AND flight_pilot.num = 1 AND flight.flight_type_id & $flightTypeId != 0) AS Dece, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i') ) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id LEFT JOIN person a ON a.id=fp.pilot_id LEFT JOIN profile AS p1 ON (a.profile & p1.id) WHERE a.activated=1 AND p1.name='Instructor' AND fp.num = 1 AND YEAR( start_date ) = $year AND flight.flight_type_id & $flightTypeId != 0 GROUP BY a.id UNION SELECT "Sum per","month", (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight.flight_type_id & $flightTypeId != 0) AS Janv, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight.flight_type_id & $flightTypeId != 0) AS Fevr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight.flight_type_id & $flightTypeId != 0) AS Mars, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight.flight_type_id & $flightTypeId != 0) AS Avri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight.flight_type_id & $flightTypeId != 0) AS Mai, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight.flight_type_id & $flightTypeId != 0) AS Juin, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight.flight_type_id & $flightTypeId != 0) AS Juil, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight.flight_type_id & $flightTypeId != 0) AS Aout, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight.flight_type_id & $flightTypeId != 0) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight.flight_type_id & $flightTypeId != 0) AS Oct, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight.flight_type_id & $flightTypeId != 0) AS Nov, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight.flight_type_id & $flightTypeId != 0) AS Dece, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i') ) AS Total FROM flight LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id WHERE fp.num = 1 AND YEAR( start_date ) = $year AND flight.flight_type_id & $flightTypeId != 0
Aircraft(s) which no flight were done for at least 30 day(s)
SELECT resource.id, resource.name FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE start_date < ( NOW() - INTERVAL 30 DAY ) GROUP BY resource.id
Total tank quantity for each tank and for each aircraft type
SELECT resource_type.name AS aircraft_type, tank.label AS tank_label, SUM( quantity ) AS total_qty FROM tank LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) WHERE YEAR(flight.start_date) = $year GROUP BY aircraft_type_id, tank_type_id
Total tank quantity for each tank and for each aircraft
SELECT resource.name AS aircraft_callsign, tank.label AS tank_label, SUM( quantity ) AS total_qty FROM tank LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) LEFT JOIN resource ON (flight.aircraft_id = resource.id) WHERE YEAR(flight.start_date) = $year GROUP BY resource.id, tank_type_id
Fuel/Oil quantities per tank and aircraft type
SELECT aircraft_type_name AS CallSign, tank_label AS Tank, SUM(IF (month_num=1, quantity, 0)) AS Janu, SUM(IF (month_num=2, quantity, 0)) AS Febr, SUM(IF (month_num=3, quantity, 0)) AS Marc, SUM(IF (month_num=4, quantity, 0)) AS Apri, SUM(IF (month_num=5, quantity, 0)) AS May, SUM(IF (month_num=6, quantity, 0)) AS June, SUM(IF (month_num=7, quantity, 0)) AS July, SUM(IF (month_num=8, quantity, 0)) AS Augu, SUM(IF (month_num=9, quantity, 0)) AS Sept, SUM(IF (month_num=10, quantity, 0)) AS Octo, SUM(IF (month_num=11, quantity, 0)) AS Nove, SUM(IF (month_num=12, quantity, 0)) AS Dece, SUM(quantity) AS Sum FROM ( SELECT resource_type.id AS aircraft_type_id, resource_type.name AS aircraft_type_name, tank.id AS tank_id, tank.label AS tank_label, CAST( flight_tank_qty.quantity AS DECIMAL(10,2) ) AS quantity, MONTH(flight.start_date) AS month_num FROM tank LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) WHERE YEAR(flight.start_date) = $year ) AS my_table GROUP BY aircraft_type_id, tank_id
Fuel/Oil sum for each aircraft
Unit is: unit of the tank
SELECT resource_name AS CallSign, CASE tank_type WHEN 1 THEN "AVGAS" WHEN 2 THEN "JET A1" WHEN 3 THEN "Oil" WHEN 4 THEN "SP95" WHEN 5 THEN "SP98" END AS Tank, SUM(IF (month_num=1, quantity, 0)) AS Janu, SUM(IF (month_num=2, quantity, 0)) AS Febr, SUM(IF (month_num=3, quantity, 0)) AS Marc, SUM(IF (month_num=4, quantity, 0)) AS Apri, SUM(IF (month_num=5, quantity, 0)) AS May, SUM(IF (month_num=6, quantity, 0)) AS June, SUM(IF (month_num=7, quantity, 0)) AS July, SUM(IF (month_num=8, quantity, 0)) AS Augu, SUM(IF (month_num=9, quantity, 0)) AS Sept, SUM(IF (month_num=10, quantity, 0)) AS Octo, SUM(IF (month_num=11, quantity, 0)) AS Nove, SUM(IF (month_num=12, quantity, 0)) AS Dece, SUM(quantity) AS Sum, CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600 AS DECIMAL(10,2) ) AS Consumption FROM (SELECT resource.name AS resource_name, resource.id AS resource_id, tank.tank_type_id AS tank_type, CAST( flight_tank_qty.quantity AS DECIMAL(10,2) ) AS quantity, MONTH(flight.start_date) AS month_num FROM tank LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR(flight.start_date) = $year ) AS my_table GROUP BY resource_name, tank_type
Fuel/Oil consumption
Unit is: unit of the tank per hour
SELECT resource_name AS CallSign, CASE tank_type WHEN 1 THEN "AVGAS" WHEN 2 THEN "JET A1" WHEN 3 THEN "Oil" WHEN 4 THEN "SP95" WHEN 5 THEN "SP98" END AS Tank, CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Janu, CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Febr, CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Marc, CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Apri, CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS May, CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS June, CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS July, CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Augu, CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Sept, CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Octo, CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Nove, CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Dece, CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600 AS DECIMAL(10,2) ) AS Sum FROM (SELECT resource.name AS resource_name, resource.id AS resource_id, tank.tank_type_id AS tank_type, flight_tank_qty.quantity AS quantity, MONTH(flight.start_date) AS month_num, (SELECT sum(flight.duration) FROM flight WHERE aircraft_id = resource_id AND YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = month_num ) AS flight_time FROM tank LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR(flight.start_date) = $year ) AS my_table GROUP BY resource_name, tank_type
Flight with their location code (ICAO)
SELECT flight.*, L1.icao_name AS departure_location, L2.icao_name AS arrival_location FROM flight LEFT JOIN location AS L1 ON flight.departure_location_id = L1.id LEFT JOIN location AS L2 ON flight.arrival_location_id = L2.id
Flight list between two dates
Two variables should be defined first and should be of Date value type :
- firstDateLimit
- secondDateLimit
SELECT f.start_date, r.name, UPPER(p.last_name) AS Pilot_last_name, p.first_name AS Pilot_first_name, ( SELECT UPPER(p2.last_name) FROM flight_pilot AS fp2 LEFT JOIN person AS p2 ON fp2.pilot_id = p2.id WHERE fp2.flight_id = f.id AND fp2.num = 1 ) AS Instructor_last_name, ( SELECT UPPER(p2.first_name) FROM flight_pilot AS fp2 LEFT JOIN person AS p2 ON fp2.pilot_id = p2.id WHERE fp2.flight_id = f.id AND fp2.num = 1 ) AS Instructor_fist_name, ( SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') FROM flight LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) WHERE flight.id=f.id ) AS Flight_types, CONCAT(FLOOR( f.duration/600),':',TIME_FORMAT(SEC_TO_TIME((f.duration/600 - FLOOR(f.duration/600))*3600),'%i')) AS Durée FROM flight AS f LEFT JOIN resource AS r ON r.id = f.aircraft_id LEFT JOIN flight_pilot AS fp ON fp.flight_id = f.id LEFT JOIN person AS p ON fp.pilot_id = p.id WHERE '$firstDateLimit' <= f.start_date AND f.start_date <= '$secondDateLimit' AND fp.num = 0;
Stats all flight hours per month per year
SELECT YEAR(start_date) AS year, 'Monthly' AS Type, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 1 ) AS Janu, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 2 ) AS Febr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 3 ) AS Marc, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 4 ) AS Apri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 5 ) AS May, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 6 ) AS June, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 7 ) AS July, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 8 ) AS Augu, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 9 ) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 10 ) AS Octo, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 11 ) AS Nove, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)= 12 ) AS Dece, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year) AS Sum FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE resource.virtual = 0 GROUP BY year UNION SELECT YEAR(start_date) AS year, 'Accumulation', (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 2), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 3), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 4), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 5), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 6), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 7), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 8), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 9), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 10), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 11), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year AND MONTH(start_date)<= 12), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= year) AS Sum FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE resource.virtual = 0 GROUP BY year ORDER BY year DESC
Error message queries
Flight without account movement
SELECT DATE_FORMAT(flight.start_date,'%d/%m/%Y' ) AS Date, TIME_FORMAT(flight.start_date,'%H:%i' ) AS Time, resource.name AS Callsign, person.last_name AS Lastname, person.first_name AS Firstname, CONCAT(FLOOR( flight.duration /600),':', TIME_FORMAT(SEC_TO_TIME(( flight.duration/600 - FLOOR( flight.duration /600))*3600),'%i')) AS FlightTime FROM flight LEFT JOIN flight_account_entry ON flight.id=flight_account_entry.flight_id LEFT JOIN resource ON resource.id=flight.aircraft_id LEFT JOIN flight_pilot ON flight_pilot.flight_id=flight.id LEFT JOIN person ON person.id=flight_pilot.pilot_id WHERE flight_account_entry.account_entry_id IS NULL AND flight.airborne=0 AND flight_pilot.num=0
list members without an account
SELECT member.id, person.last_name, person.first_name FROM member LEFT JOIN account ON account.owner_id=member.id LEFT JOIN person ON person.id=member.id WHERE person.activated=1 AND account.category=2 AND account.id IS NULL
movement without an account
SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE account_id is null SELECT id,flow_id,account_date,credit,debit,payment_description,comments FROM account_entry WHERE flow_id = 'xxxxxx'