Difference between revisions of "Export generator 3"
m (→Users with specific profile: male, female, less than 21 years and more than 21 years) |
(→Validities in the year : Total of youngs/adults with specific profile) |
||
Line 454: | Line 454: | ||
WHERE YEAR(validity.grant_date)=$year | WHERE YEAR(validity.grant_date)=$year | ||
AND person.activated=1 | AND person.activated=1 | ||
− | AND (person.profile & $profileId) | + | AND (person.profile & $profileId OR ''='$profileId') |
GROUP BY validity_type.id | GROUP BY validity_type.id | ||
ORDER BY validity_type.name</sql> | ORDER BY validity_type.name</sql> |
Revision as of 14:24, 28 January 2014
Contents
- 1 Introduction
- 2 SQL tips and tricks
- 3 French administration Examples
- 3.1 Flight hours total on instruction : male, female, less than 21 years and more than 21 years with specific profile
- 3.2 Flight hours total on non-instruction : male, female, less than 21 years and more than 21 years with specific profile
- 3.3 Flight type : Flight hours total on instruction with specific profile
- 3.4 Flight type : Flight hours total on non-instruction with specific profile
- 3.5 Licence in the year
- 3.6 List of users with specific validity
- 3.7 Number of visit on other airfield
- 3.8 Number of take-off and landings on based airfield
- 3.9 Number of take-off and landings on based airfield on specific profile
- 3.10 List of movements on based airfield
- 3.11 Validity per user
- 3.12 List of pilots who have flown without required validity
- 3.13 List of pilots who have flown less than X hours during last Y days
- 3.14 List of pilots who have flown less than X hours during last Y days on aircraft type Z
- 3.15 Pilots without flight in the last 3 months
- 3.16 Number of landings per pilot, per resource
- 3.17 Flight hours without up to date validities
- 3.18 Users: male, female, less than 21 years and more than 21 years
- 3.19 Users with specific profile: male, female, less than 21 years and more than 21 years
- 3.20 Validities in the year : Total of youngs/adults with specific profile
- 4 Accounting
- 4.1 Global account balance
- 4.2 Global account balance of users who last subscription was 2 years ago
- 4.3 Global non null account balance
- 4.4 Balances of accounts
- 4.5 Balance per account category
- 4.6 Carry forwards
- 4.7 Payments list ordered by type
- 4.8 Get the details (specially the id) of an account according is name
- 4.9 Get the user owner of an account
- 4.10 Payment dispatching
- 4.11 Cheque deposit slip
- 4.12 Monthly distribution of revenue
- 5 Booking
- 6 Flight time management
- 6.1 Flight hours per month
- 6.2 Flight hours total over a 12 months period
- 6.3 Flights hours total per aircraft per year
- 6.4 Flights hours total per aircraft per year and per month
- 6.5 Flight hours total per pilot
- 6.6 Flight hours total per pilot per profile
- 6.7 Flight log book
- 6.8 Total Flight hours per pilot with total duration and total amount
- 6.9 Flights hours total per instructor
- 6.10 Flights hours total per instructor per year and per month
- 6.11 Flight hours per instructor function to flight type
- 6.12 Flights hours following nationality
- 6.13 Flights hours total per flight type
- 6.14 Flights hours : less than 21 years, more than 21 years, male, female
- 6.15 Computation of flight time per month of "local flight"
- 6.16 Computation of non-"local flight" time per month
- 6.17 List of flights have landed on a airfield
- 6.18 Total flight hours per instructor per month depending on flight type
- 6.19 Aircraft(s) which no flight were done for at least 30 day(s)
- 6.20 Total tank quantity for each tank and for each aircraft type
- 6.21 Total tank quantity for each tank and for each aircraft
- 6.22 Fuel/Oil quantities per tank and aircraft type
- 6.23 Fuel/Oil sum for each aircraft
- 6.24 Fuel/Oil consumption
- 6.25 Flight with their location code (ICAO)
- 6.26 Flight list between two dates
- 6.27 Detailed flight list per month
- 6.28 Stats all flight hours per month per year
- 7 Error message queries
- 8 Users
- 8.1 Users list with membership ending before a specific date
- 8.2 Activated user list
- 8.3 Activated user list with email
- 8.4 Young user list
- 8.5 User list coordinates
- 8.6 User coordinates with registration date (using ExtraField)
- 8.7 User coordinates with registration date (using Validity)
- 8.8 User list by profile
- 8.9 User list by validity
- 8.10 User list with validities viewer
- 8.11 User list with age and birth date
- 8.12 User list ordered by member number
- 8.13 User list with subscription grant date and profiles
- 8.14 User list with registration date, profiles and total flight time
- 8.15 User list with up to date subscription
- 8.16 User list without up to date subscription
- 8.17 User list without an account
- 9 Log history
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::Aircraft
Then in Admin/Reports/Structure/Criteria we create a new query labeled "Aircraft booking" with the following query:
SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId
To use this report, we just have to select a "Aircraft" in the Reports/Structure/View form, to check "Aircraft booking" then to click on "View"
SQL tips and tricks
Return only last entry
Example with last entry from variable #1 in variable_value table:
SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC LIMIT 1
Test valid entries
For extra fields that are text inputs, any entry can be submitted and added to SQL query. In order to test valid entries, it is recommended to use the IF statement in the WHERE statement.
Per example, $occupiedSeat can have following values (0, 1 or NULL) :
SELECT * FROM flight_pilot WHERE ( IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num )
French administration Examples
Flight hours total on instruction : male, female, less than 21 years and more than 21 years with specific profile
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', IF( ( $year - YEAR(birthdate) >= 21 ) , 'Yes', 'No') AS 'Adult', 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 LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1) LEFT JOIN person ON (person.id=flight_pilot.pilot_id) WHERE YEAR(flight.start_date)=$year AND person.activated=1 AND (person.profile & $profileId) GROUP BY Sex, Adult
Flight hours total on non-instruction : male, female, less than 21 years and more than 21 years with specific profile
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', IF( ( $year - YEAR(birthdate) >= 21 ) , 'Yes', 'No') AS 'Adult', 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 LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=0) LEFT JOIN person ON (person.id=flight_pilot.pilot_id) WHERE YEAR(flight.start_date)=$year AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) AND person.activated=1 AND (person.profile & $profileId) GROUP BY Sex, Adult
Flight type : Flight hours total on instruction with specific profile
- Variable $flightTypeId should be defined first and should be of dbOject::FlightType value type.
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT flight_type.name AS 'Flight Type', 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 LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1) LEFT JOIN person ON (person.id=flight_pilot.pilot_id) WHERE YEAR(flight.start_date)=$year AND flight_type.id=$flightTypeId AND person.activated=1 AND (person.profile & $profileId) GROUP BY flight_type.id
Flight type : Flight hours total on non-instruction with specific profile
- Variable $flightTypeId should be defined first and should be of dbOject::FlightType value type.
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT flight_type.name AS 'Flight Type', 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 LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=0) LEFT JOIN person ON (person.id=flight_pilot.pilot_id) WHERE YEAR(flight.start_date)=$year AND flight_type.id=$flightTypeId AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) AND person.activated=1 AND (person.profile & $profileId) GROUP BY flight_type.id
Licence in the year
Note:
- 21 limit of age to change according yours limit
- Replace X and Y in the following query by the right validity names.
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 AND (validity_type.name = 'X' OR validity_type.name = 'Y')
List of users with specific validity
Replace XXX by the validity type name.
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="XXX" AND person.activated=1 ORDER BY last_name, first_name
Note: replace name of validity_type.name by yours
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 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
Number of take-off and landings on based airfield on specific profile
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT SUM(landing_number)*2 AS 'Movement' FROM flight LEFT JOIN ( SELECT location.id FROM location LEFT JOIN club ON location.icao_name=club.icao WHERE club.id IS NOT NULL LIMIT 1 ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id) LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0) WHERE person.activated=1 AND (person.profile & $profileId) AND YEAR(flight.start_date) = $year
List of movements on based airfield
SELECT resource.name, DATE_FORMAT(flight.start_date, '%d %m %Y' ) AS Date, sum(landing_number) AS 'Nb Att' FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE ( flight.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND (flight.arrival_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao)) AND YEAR( start_date ) = $year GROUP BY date,name
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'
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 person.last_name, person.first_name, 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=person.id ORDER BY f1.start_date ASC LIMIT 1),'UNKNOWN') AS 'Last flight' FROM person WHERE person.activated=1 AND person.id NOT IN ( SELECT person2.id FROM flight AS f LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id LEFT JOIN person AS person2 ON person2.id=fp.pilot_id WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL 90 DAY AND NOW() GROUP BY person2.id HAVING SUM(f.duration)/600 > 0 ) ORDER BY person.last_name, person.first_name
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
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
Users: 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
Users with specific profile: male, female, less than 21 years and more than 21 years
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
SELECT IF( person.sex=0, 'Male', IF( person.sex=1, 'Female', 'Undefined') ) AS 'Sex', IF( ( $year - YEAR(birthdate) >= 21 ) , 'Yes', 'No') AS 'Adult', COUNT(id) AS Number FROM person WHERE activated=1 AND (profile & '$profileId' OR ''='$profileId') GROUP BY Sex, Adult
Validities in the year : Total of youngs/adults with specific profile
- Variable $profileId should be defined first and should be of dbOject::Profile value type.
- Variable $year should be defined first and should be of Year value type.
- Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need.
SELECT validity_type.name AS 'Validity', SUM( IF( ( $year - YEAR(person.birthdate) < 21 ), 1, 0 ) ) AS 'Young', SUM( IF( ( $year - YEAR(person.birthdate) >= 21 ), 1, 0 ) ) AS 'Adult' 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(validity.grant_date)=$year AND person.activated=1 AND (person.profile & $profileId OR ''='$profileId') GROUP BY validity_type.id ORDER BY validity_type.name
Accounting
Global account balance
List whole account balance at the end of a given date (Extra field $endDate, 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,'$endDate'),0) AS solde FROM account LEFT JOIN person ON person.id=account.owner_id WHERE account.activated=1 ORDER BY export_account
Global account balance for specific accounting
Replace XXXX values by chosen accounting's label
SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom, IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS solde FROM account LEFT JOIN person ON person.id=account.owner_id LEFT JOIN accounting ON accounting.id=account.accounting_id WHERE account.activated=1 AND accounting.name='XXXX' ORDER BY export_account
Global account balance of users who last subscription was 2 years ago
Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.
SELECT last_name, first_name, validity_type.name as 'Validity', grant_date, IFNULL((SELECT SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry WHERE account_entry.account_id = a.id AND account_entry.account_date > (SELECT balance_date FROM balance_date ORDER BY balance_date DESC LIMIT 1)),0)+balance.credit-balance.debit AS Total FROM `person` LEFT JOIN validity ON validity.person_id = person.id LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id LEFT JOIN account AS a ON a.owner_id = person.id AND category = 2 LEFT JOIN balance ON balance.account_id = a.id AND balance.balance_date_id = (SELECT id FROM balance_date ORDER BY balance_date ASC LIMIT 1) WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR) AND validity_type.id = $validityTypeId GROUP BY person.id ORDER BY last_name,first_name
Global account balance of users who last subscription was 2 years ago for specific accounting
Replace XXXX values by chosen accounting's label
SELECT last_name, first_name, validity_type.name AS 'Validity', grant_date, IFNULL((SELECT SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry WHERE account_entry.account_id = a.id AND account_entry.account_date > (SELECT balance_date FROM balance_date ORDER BY balance_date DESC LIMIT 1)),0)+balance.credit-balance.debit AS Total FROM `person` LEFT JOIN validity ON validity.person_id = person.id LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id LEFT JOIN account AS a ON a.owner_id = person.id AND category = 2 LEFT JOIN accounting ON accounting.id=a.accounting_id LEFT JOIN balance ON balance.account_id = a.id AND balance.balance_date_id = (SELECT id FROM balance_date ORDER BY balance_date ASC LIMIT 1) WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR) AND accounting.name='XXXX' AND validity_type.id = $validityTypeId GROUP BY person.id ORDER BY last_name,first_name
Global non null account balance
- List whole account balance at the end of a given date (Extra field $endDate 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,'$endDate'),0) AS solde FROM account LEFT JOIN person ON person.id=account.owner_id WHERE account.activated=1 AND sumAccountEntry(account.id,'$endDate') <> 0 ORDER BY export_account
- List whole account balance at the end of a given year (Extra field $year has to be defined and should be of Year value type) but for only non null account balance :
SELECT id, export_account, name, sumAccountEntry(id,'$year-12-31 23:59:59') AS solde FROM account WHERE activated=1 AND sumAccountEntry(id,'$year-12-31 23:59:59') <> 0 ORDER BY export_account
Global non null account balance for specific accounting
Replace XXXX values by chosen accounting's label
SELECT a.id, export_account, a.name, sumAccountEntry(a.id,'$year-12-31 23:59:59') AS solde FROM account AS a LEFT JOIN accounting ON accounting.id=a.accounting_id WHERE activated=1 AND sumAccountEntry(a.id,'$year-12-31 23:59:59') <> 0 AND accounting.name='XXXX' ORDER BY export_account
Balances of accounts
Extra field $endDate has to be defined first and should be of value type DateTime.
Balances of user accounts
Balances of user accounts
SELECT person.last_name, person.first_name, account_type.name AS account_type_name, sumAccountEntry(account.id, '$endDate') AS balance FROM account LEFT JOIN account_type ON (account.account_type=account_type.id) LEFT JOIN person ON (account.owner_id=person.id) WHERE account.category=2 AND account.activated=1 ORDER BY last_name, first_name
Balances of user accounts whose got a specific profile
Replace X and Y by right profile id.
SELECT person.last_name AS NOM, person.first_name AS PRENOM, profile.name AS Profil, IFNULL(sumAccountEntry(account.id,NOW()),0) AS Solde FROM account LEFT JOIN person ON person.id=account.owner_id LEFT JOIN profile ON (person.profile & profile.id) WHERE (person.activated = 1) AND (account.activated = 1) AND ((profile.id = X) OR (profile.id = Y)) ORDER BY NOM, PRENOM
Balances of user accounts for specific accounting
Replace XXXX values by chosen accounting's label
SELECT person.last_name, person.first_name, account_type.name AS account_type_name, sumAccountEntry(account.id, '$endDate') AS balance FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN account_type ON (account.account_type=account_type.id) LEFT JOIN person ON (account.owner_id=person.id) WHERE account.category=2 AND account.activated=1 AND accounting.name='XXXX' ORDER BY last_name, first_name
List of resource account balance
SELECT account.name AS resource, account_type.name AS account_type_name, sumAccountEntry(account.id, '$endDate') AS balance FROM account LEFT JOIN account_type ON (account.account_type=account_type.id) WHERE account.category=3 AND account.activated=1 ORDER BY account.name
List of all account debit, credit
List whole account balance at the end of a given date (Extra field $endDate, type dateTime, should be defined)
SELECT account.export_account AS 'Export account', CASE WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('Member account ', account_type.name, ' of ', person.last_name, ' ', person.first_name) WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name) WHEN account.category=1 THEN CONCAT('Other account ', account.name) WHEN account.category=4 THEN CONCAT('Supplier account ', account.name) WHEN account.category=6 THEN CONCAT('VAT account ', account.name) WHEN account.category=7 THEN CONCAT('Expense account ', account.name) WHEN account.category=8 THEN CONCAT('Treasury account ', account.name) WHEN account.category=9 THEN CONCAT('Product account ', account.name) ELSE account.name END AS account_name, IF (sumAccountEntry(account.id,'$endDate')<0, sumAccountEntry(account.id,'$endDate'), 0) AS Debit, IF (sumAccountEntry(account.id,'$endDate')<0, 0, sumAccountEntry(account.id,'$endDate') ) AS Credit FROM account LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3)) LEFT JOIN person ON (account.owner_id = person.id AND account.category=2) LEFT JOIN aircraft ON (account.owner_id = aircraft.id AND account.category=3) LEFT JOIN resource ON aircraft.id = resource.id WHERE person.activated = 1 GROUP BY account.id ORDER BY account.export_account ASC
List of all account debit, credit for specific accounting
Replace XXXX values by chosen accounting's label
SELECT account.export_account AS 'Export account', CASE WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('Member account ', account_type.name, ' of ', person.last_name, ' ', person.first_name) WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name) WHEN account.category=1 THEN CONCAT('Other account ', account.name) WHEN account.category=4 THEN CONCAT('Supplier account ', account.name) WHEN account.category=6 THEN CONCAT('VAT account ', account.name) WHEN account.category=7 THEN CONCAT('Expense account ', account.name) WHEN account.category=8 THEN CONCAT('Treasury account ', account.name) WHEN account.category=9 THEN CONCAT('Product account ', account.name) ELSE account.name END AS account_name, IF (sumAccountEntry(account.id,'$endDate')<0, sumAccountEntry(account.id,'$endDate'), 0) AS Debit, IF (sumAccountEntry(account.id,'$endDate')<0, 0, sumAccountEntry(account.id,'$endDate') ) AS Credit FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN account_type ON account.account_type = account_type.id LEFT JOIN person ON account.owner_id = person.id LEFT JOIN aircraft ON account.owner_id = aircraft.id LEFT JOIN resource ON aircraft.id = resource.id WHERE person.activated = 1 AND accounting.name='XXXX' GROUP BY account.id ORDER BY account.export_account ASC
Balance per account category
SELECT CASE WHEN account_type.name IS NOT NULL AND (account.category=2 OR account.category=3) THEN account_type.name WHEN account.category=1 THEN CONCAT('Other account ', account.name) WHEN account.category=4 THEN CONCAT('Supplier account ', account.name) WHEN account.category=6 THEN CONCAT('VAT account ', account.name) WHEN account.category=7 THEN CONCAT('Expense account ', account.name) WHEN account.category=8 THEN CONCAT('Treasury account ', account.name) WHEN account.category=9 THEN CONCAT('Product account ', account.name) ELSE account.name END AS 'Account type', IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde FROM account LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3)) GROUP BY account.category
Balance per account category for specific accounting
Replace XXXX values by chosen accounting's label
SELECT CASE WHEN account_type.name IS NOT NULL AND (account.category=2 OR account.category=3) THEN account_type.name WHEN account.category=1 THEN CONCAT('Other account ', account.name) WHEN account.category=4 THEN CONCAT('Supplier account ', account.name) WHEN account.category=6 THEN CONCAT('VAT account ', account.name) WHEN account.category=7 THEN CONCAT('Expense account ', account.name) WHEN account.category=8 THEN CONCAT('Treasury account ', account.name) WHEN account.category=9 THEN CONCAT('Product account ', account.name) ELSE account.name END AS 'Account type', IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3)) WHERE accounting.name='XXXX' GROUP BY account.category
Carry forwards
SELECT IF (account.category = 2, CONCAT(person.last_name,' ',person.first_name,' (',account_type.name,')'), IF (account.category = 3,CONCAT(resource.name,' (',account_type.name,')'),account.name)) AS Nom, export_account AS Code_comptable, debit AS 'Debit', credit AS 'Credit' FROM account LEFT JOIN balance ON balance.account_id=account.id LEFT JOIN person ON person.id=account.owner_id AND account.category = 2 LEFT JOIN aircraft ON aircraft.id=account.owner_id AND account.category = 3 LEFT JOIN resource ON resource.id=aircraft.id LEFT JOIN account_type ON account_type.id=account.account_type WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) UNION SELECT 'zzzzzzz', 'Total', SUM(debit) AS 'Debit', SUM(credit) AS 'Credit' FROM account LEFT JOIN balance ON balance.account_id=account.id WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) ORDER BY Nom ASC
Carry forwards for specific accounting
Replace XXXX values by chosen accounting's label
SELECT IF (account.category = 2, CONCAT(person.last_name,' ',person.first_name,' (',account_type.name,')'), IF (account.category = 3,CONCAT(resource.name,' (',account_type.name,')'),account.name)) AS Nom, export_account AS Code_comptable, debit AS 'Debit', credit AS 'Credit' FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN balance ON balance.account_id=account.id LEFT JOIN person ON person.id=account.owner_id AND account.category = 2 LEFT JOIN aircraft ON aircraft.id=account.owner_id AND account.category = 3 LEFT JOIN resource ON resource.id=aircraft.id LEFT JOIN account_type ON account_type.id=account.account_type WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) AND accounting.name='XXXX' UNION SELECT 'zzzzzzz', 'Total', SUM(debit) AS 'Debit', SUM(credit) AS 'Credit' FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN balance ON balance.account_id=account.id WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) AND accounting.name='XXXX' ORDER BY Nom ASC
Payments list ordered by type
SELECT payment_type.name AS Type, if(account.category = 2, CONCAT( p.last_name, ' ', p.first_name),account.name) AS Name, DATE_FORMAT(ae.account_date, '%d %m %Y' ) AS Date, ae.payment_description, ae.credit AS Description FROM account_entry AS ae LEFT JOIN payment_type ON payment_type.id = ae.payment_type LEFT JOIN account ON account.id = ae.account_id LEFT JOIN person AS p ON p.id = account.owner_id WHERE ae.payment_type IS NOT NULL AND credit > 0 AND YEAR(ae.account_date) >= $year ORDER BY ae.payment_type, ae.account_date, p.last_name, p.first_name
Get the details (specially the id) of an account according is name
SELECT * FROM `account` WHERE `name` LIKE 'account name to search'
Get the user owner of an account
SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114
Payment dispatching
SELECT payment_type as Num, payment_type.name as name, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type = Num) AS Janu, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type = Num) AS Febr, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type = Num) AS Marc, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type = Num) AS Apri, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type = Num) AS May, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type = Num) AS June, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type = Num) AS July, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type = Num) AS Augu, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type = Num) AS Sept, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type = Num) AS Octo, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type = Num) AS Nove, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type = Num) AS Dece, SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total FROM account_entry LEFT JOIN payment_type ON payment_type = payment_type.id WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL GROUP BY payment_type UNION SELECT 'Sum per', 'month', (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type IS NOT NULL), SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total FROM account_entry LEFT JOIN payment_type ON payment_type = payment_type.id WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL
Cheque deposit slip
SELECT date_format(ae.registration_date, "%d/%m/%Y") as "date", a.name, ae.credit as montant, ae.payment_description as libelle, (SELECT a2.name FROM account_entry ae2 JOIN account a2 ON a2.id = ae2.account_id WHERE ae2.flow_id = ae.flow_id AND ae2.debit > 0 LIMIT 0,1) as compte_banque, ae.comments as commentaire FROM account_entry ae JOIN account a ON ae.account_id = a.id WHERE a.account_type = 1 AND ae.validated = 1 AND ae.credit > 0 ORDER BY ae.registration_date DESC
Monthly distribution of revenue
- In french accounting, this query reports each month's revenue distribution. It only concerns product accounts (prefixed 7xx) except those that are prefixed 709xxx
- Additional field $year is needed
SELECT account_name, ( IF(febSum<0, febSum, 0) - IF(janSum<0, janSum, 0) + IF(febSum<0, 0, febSum) - IF(janSum<0, 0, janSum) ) AS Janu, ( IF(marSum<0, marSum, 0) - IF(febSum<0, febSum, 0) + IF(marSum<0, 0, marSum) - IF(febSum<0, 0, febSum) ) AS Febr, ( IF(aprSum<0, aprSum, 0) - IF(marSum<0, marSum, 0) + IF(aprSum<0, 0, aprSum) - IF(marSum<0, 0, marSum) ) AS Marc, ( IF(maySum<0, maySum, 0) - IF(aprSum<0, aprSum, 0) + IF(maySum<0, 0, maySum) - IF(aprSum<0, 0, aprSum) ) AS April, ( IF(junSum<0, junSum, 0) - IF(maySum<0, maySum, 0) + IF(junSum<0, 0, junSum) - IF(maySum<0, 0, maySum) ) AS May, ( IF(julSum<0, julSum, 0) - IF(junSum<0, junSum, 0) + IF(julSum<0, 0, julSum) - IF(junSum<0, 0, junSum) ) AS June, ( IF(augSum<0, augSum, 0) - IF(julSum<0, julSum, 0) + IF(augSum<0, 0, augSum) - IF(julSum<0, 0, julSum) ) AS July, ( IF(sepSum<0, sepSum, 0) - IF(augSum<0, augSum, 0) + IF(sepSum<0, 0, sepSum) - IF(augSum<0, 0, augSum) ) AS Augu, ( IF(octSum<0, octSum, 0) - IF(sepSum<0, sepSum, 0) + IF(octSum<0, 0, octSum) - IF(sepSum<0, 0, sepSum) ) AS Sept, ( IF(novSum<0, novSum, 0) - IF(octSum<0, octSum, 0) + IF(novSum<0, 0, novSum) - IF(octSum<0, 0, octSum) ) AS Octo, ( IF(decSum<0, decSum, 0) - IF(novSum<0, novSum, 0) + IF(decSum<0, 0, decSum) - IF(novSum<0, 0, novSum) ) AS Nove, ( IF(nextJanSum<0, nextJanSum, 0) - IF(decSum<0, decSum, 0) + IF(nextJanSum<0, 0, nextJanSum) - IF(decSum<0, 0, decSum) ) AS Dece FROM ( SELECT IF(account.category=3, CONCAT(account.name, ' - ', account_type.name), account.name) AS 'account_name', sumAccountEntry(account.id, '$year-01-01') AS 'janSum', sumAccountEntry(account.id, '$year-02-01') AS 'febSum', sumAccountEntry(account.id, '$year-03-01') AS 'marSum', sumAccountEntry(account.id, '$year-04-01') AS 'aprSum', sumAccountEntry(account.id, '$year-05-01') AS 'maySum', sumAccountEntry(account.id, '$year-06-01') AS 'junSum', sumAccountEntry(account.id, '$year-07-01') AS 'julSum', sumAccountEntry(account.id, '$year-08-01') AS 'augSum', sumAccountEntry(account.id, '$year-09-01') AS 'sepSum', sumAccountEntry(account.id, '$year-10-01') AS 'octSum', sumAccountEntry(account.id, '$year-11-01') AS 'novSum', sumAccountEntry(account.id, '$year-12-01') AS 'decSum', sumAccountEntry(account.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH)) AS 'nextJanSum' FROM account LEFT JOIN account_type ON (account_type.id = account.account_type) WHERE account.export_account LIKE '7%' AND account.export_account NOT LIKE '709%' AND account.activated=1 ) AS sumAccountEntryForAccount UNION SELECT 'Total', ( IF(febSum<0, febSum, 0) - IF(janSum<0, janSum, 0) + IF(febSum<0, 0, febSum) - IF(janSum<0, 0, janSum) ) AS Janu, ( IF(marSum<0, marSum, 0) - IF(febSum<0, febSum, 0) + IF(marSum<0, 0, marSum) - IF(febSum<0, 0, febSum) ) AS Febr, ( IF(aprSum<0, aprSum, 0) - IF(marSum<0, marSum, 0) + IF(aprSum<0, 0, aprSum) - IF(marSum<0, 0, marSum) ) AS Marc, ( IF(maySum<0, maySum, 0) - IF(aprSum<0, aprSum, 0) + IF(maySum<0, 0, maySum) - IF(aprSum<0, 0, aprSum) ) AS April, ( IF(junSum<0, junSum, 0) - IF(maySum<0, maySum, 0) + IF(junSum<0, 0, junSum) - IF(maySum<0, 0, maySum) ) AS May, ( IF(julSum<0, julSum, 0) - IF(junSum<0, junSum, 0) + IF(julSum<0, 0, julSum) - IF(junSum<0, 0, junSum) ) AS June, ( IF(augSum<0, augSum, 0) - IF(julSum<0, julSum, 0) + IF(augSum<0, 0, augSum) - IF(julSum<0, 0, julSum) ) AS July, ( IF(sepSum<0, sepSum, 0) - IF(augSum<0, augSum, 0) + IF(sepSum<0, 0, sepSum) - IF(augSum<0, 0, augSum) ) AS Augu, ( IF(octSum<0, octSum, 0) - IF(sepSum<0, sepSum, 0) + IF(octSum<0, 0, octSum) - IF(sepSum<0, 0, sepSum) ) AS Sept, ( IF(novSum<0, novSum, 0) - IF(octSum<0, octSum, 0) + IF(novSum<0, 0, novSum) - IF(octSum<0, 0, octSum) ) AS Octo, ( IF(decSum<0, decSum, 0) - IF(novSum<0, novSum, 0) + IF(decSum<0, 0, decSum) - IF(novSum<0, 0, novSum) ) AS Nove, ( IF(nextJanSum<0, nextJanSum, 0) - IF(decSum<0, decSum, 0) + IF(nextJanSum<0, 0, nextJanSum) - IF(decSum<0, 0, decSum) ) AS Dece FROM ( SELECT SUM(sumAccountEntry(account.id, '$year-01-01')) AS 'janSum', SUM(sumAccountEntry(account.id, '$year-02-01')) AS 'febSum', SUM(sumAccountEntry(account.id, '$year-03-01')) AS 'marSum', SUM(sumAccountEntry(account.id, '$year-04-01')) AS 'aprSum', SUM(sumAccountEntry(account.id, '$year-05-01')) AS 'maySum', SUM(sumAccountEntry(account.id, '$year-06-01')) AS 'junSum', SUM(sumAccountEntry(account.id, '$year-07-01')) AS 'julSum', SUM(sumAccountEntry(account.id, '$year-08-01')) AS 'augSum', SUM(sumAccountEntry(account.id, '$year-09-01')) AS 'sepSum', SUM(sumAccountEntry(account.id, '$year-10-01')) AS 'octSum', SUM(sumAccountEntry(account.id, '$year-11-01')) AS 'novSum', SUM(sumAccountEntry(account.id, '$year-12-01')) AS 'decSum', SUM(sumAccountEntry(account.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH))) AS 'nextJanSum' FROM account WHERE account.export_account LIKE '7%' AND account.export_account NOT LIKE '709%' AND account.activated=1 ) AS sumAccountEntryForAccount
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 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 total over a 12 months period
SELECT CONCAT(last_name,' ',first_name) AS 'Person', 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 start_date > DATE_SUB(NOW(), INTERVAL 1 YEAR) AND fp.num=0 GROUP BY Person, Solo_DC
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 AND airborne = 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 AND airborne = 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 AND airborne = 0
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 ON person.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
Flight log book
SELECT DATE_FORMAT(start_date, '%d/%m/%Y') AS Date, CONCAT(last_name, ' ', first_name) AS Membre, ar.name AS Appareil, IF ( (SELECT COUNT(*) FROM flight_pilot fp2 WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'S', 'D' ) AS Solo_DC, ( SELECT CONCAT(a.last_name,' ',a.first_name) AS Instructeur FROM flight_pilot fp3 LEFT JOIN person a ON a.id=fp3.pilot_id WHERE fp3.flight_id=fp.flight_id AND fp3.num=1 ) AS Instructeur, CONCAT(FLOOR(flight.duration /600),':',TIME_FORMAT(SEC_TO_TIME(( flight.duration /600 - FLOOR( flight.duration /600))*3600),'%i')) AS Durée, flight_type.name AS 'Type de vol', flight.landing_number, flight.comments FROM flight_pilot fp LEFT JOIN flight ON fp.flight_id=flight.id LEFT JOIN flight_type ON (flight_type.id & flight.flight_type_id) LEFT JOIN person a ON a.id=fp.pilot_id LEFT JOIN resource ar ON ar.id = flight.aircraft_id WHERE YEAR( start_date ) = $year AND fp.num=0 ORDER BY Membre, start_date
Total Flight hours per pilot with total duration and total amount
Following extrafields are needed
- year (Type : Year)
- month (Type : Integer) OR startDate et endDate (Type : Date)
- profile (Type : dbObject::Profile)
This report is useful for a group of pilots from a common customer (like DGAC in France)
SELECT CONCAT(person.last_name,' ',person.first_name) AS Nom_et_prenom_pilotes, CONCAT(FLOOR(SUM( flight_list.duree )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight_list.duree )/600 - FLOOR(SUM( flight_list.duree )/600))*3600),'%i')) AS nombre_hdv_effectuees, SUM(flight_list.montant) AS montant_total_des_vols FROM person JOIN (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id LEFT JOIN flight ON flight.id=flight_pilot.flight_id LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id LEFT JOIN account ON account.id=account_entry.account_id WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate' AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num) AND person.profile&$profile AND (account.category = 11 OR account.category = 2) GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id GROUP BY person.id
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
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
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
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
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
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
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 flight hours per instructor per month depending on flight type
Following extrafields are needed :
- year (Type : Year)
- flightType (Type : dbObject::FlightType)
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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 WHERE a.activated=1 AND fp.num = 1 AND YEAR( start_date ) = $year AND flight.flight_type_id & $flightType 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType != 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 & $flightType
Aircraft(s) which no flight were done for at least 30 day(s)
SELECT resource.id, resource.name FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE start_date < ( NOW() - INTERVAL 30 DAY ) GROUP BY resource.id
Total tank quantity for each tank and for each aircraft type
SELECT resource_type.name AS aircraft_type, tank.label AS tank_label, SUM( quantity ) AS total_qty FROM tank LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) WHERE YEAR(flight.start_date) = $year GROUP BY aircraft_type_id, tank_type_id
Total tank quantity for each tank and for each aircraft
SELECT resource.name AS aircraft_callsign, tank.label AS tank_label, SUM( quantity ) AS total_qty FROM tank LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) LEFT JOIN resource ON (flight.aircraft_id = resource.id) WHERE YEAR(flight.start_date) = $year GROUP BY resource.id, tank_type_id
Fuel/Oil quantities per tank and aircraft type
SELECT aircraft_type_name AS CallSign, tank_label AS Tank, SUM(IF (month_num=1, quantity, 0)) AS Janu, SUM(IF (month_num=2, quantity, 0)) AS Febr, SUM(IF (month_num=3, quantity, 0)) AS Marc, SUM(IF (month_num=4, quantity, 0)) AS Apri, SUM(IF (month_num=5, quantity, 0)) AS May, SUM(IF (month_num=6, quantity, 0)) AS June, SUM(IF (month_num=7, quantity, 0)) AS July, SUM(IF (month_num=8, quantity, 0)) AS Augu, SUM(IF (month_num=9, quantity, 0)) AS Sept, SUM(IF (month_num=10, quantity, 0)) AS Octo, SUM(IF (month_num=11, quantity, 0)) AS Nove, SUM(IF (month_num=12, quantity, 0)) AS Dece, SUM(quantity) AS Sum FROM ( SELECT resource_type.id AS aircraft_type_id, resource_type.name AS aircraft_type_name, tank.id AS tank_id, tank.label AS tank_label, CAST( flight_tank_qty.quantity AS DECIMAL(10,2) ) AS quantity, MONTH(flight.start_date) AS month_num FROM tank LEFT JOIN resource_type ON ( resource_type.id = tank.aircraft_type_id ) LEFT JOIN flight_tank_qty ON ( tank.id = flight_tank_qty.tank_id ) LEFT JOIN flight ON (flight.id = flight_tank_qty.flight_id) WHERE YEAR(flight.start_date) = $year ) AS my_table GROUP BY aircraft_type_id, tank_id
Fuel/Oil sum for each aircraft
Unit is: unit of the tank
SELECT resource_name AS CallSign, CASE tank_type WHEN 1 THEN "AVGAS" WHEN 2 THEN "JET A1" WHEN 3 THEN "Oil" WHEN 4 THEN "SP95" WHEN 5 THEN "SP98" END AS Tank, SUM(IF (month_num=1, quantity, 0)) AS Janu, SUM(IF (month_num=2, quantity, 0)) AS Febr, SUM(IF (month_num=3, quantity, 0)) AS Marc, SUM(IF (month_num=4, quantity, 0)) AS Apri, SUM(IF (month_num=5, quantity, 0)) AS May, SUM(IF (month_num=6, quantity, 0)) AS June, SUM(IF (month_num=7, quantity, 0)) AS July, SUM(IF (month_num=8, quantity, 0)) AS Augu, SUM(IF (month_num=9, quantity, 0)) AS Sept, SUM(IF (month_num=10, quantity, 0)) AS Octo, SUM(IF (month_num=11, quantity, 0)) AS Nove, SUM(IF (month_num=12, quantity, 0)) AS Dece, SUM(quantity) AS Sum, CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600 AS DECIMAL(10,2) ) AS Consumption FROM (SELECT resource.name AS resource_name, resource.id AS resource_id, tank.tank_type_id AS tank_type, CAST( flight_tank_qty.quantity AS DECIMAL(10,2) ) AS quantity, MONTH(flight.start_date) AS month_num FROM tank LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR(flight.start_date) = $year ) AS my_table GROUP BY resource_name, tank_type
Fuel/Oil consumption
Unit is: unit of the tank per hour
SELECT resource_name AS CallSign, CASE tank_type WHEN 1 THEN "AVGAS" WHEN 2 THEN "JET A1" WHEN 3 THEN "Oil" WHEN 4 THEN "SP95" WHEN 5 THEN "SP98" END AS Tank, CAST(SUM(IF (month_num=1, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Janu, CAST(SUM(IF (month_num=2, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Febr, CAST(SUM(IF (month_num=3, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Marc, CAST(SUM(IF (month_num=4, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Apri, CAST(SUM(IF (month_num=5, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS May, CAST(SUM(IF (month_num=6, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS June, CAST(SUM(IF (month_num=7, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS July, CAST(SUM(IF (month_num=8, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Augu, CAST(SUM(IF (month_num=9, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Sept, CAST(SUM(IF (month_num=10, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Octo, CAST(SUM(IF (month_num=11, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Nove, CAST(SUM(IF (month_num=12, IF (flight_time >0 ,quantity/flight_time*600 , 0), 0)) AS DECIMAL(10,2) ) AS Dece, CAST(SUM(quantity)/(SELECT SUM(duration) FROM flight WHERE aircraft_id = resource_id AND YEAR (start_date) = $year)*600 AS DECIMAL(10,2) ) AS Sum FROM (SELECT resource.name AS resource_name, resource.id AS resource_id, tank.tank_type_id AS tank_type, flight_tank_qty.quantity AS quantity, MONTH(flight.start_date) AS month_num, (SELECT sum(flight.duration) FROM flight WHERE aircraft_id = resource_id AND YEAR(flight.start_date) = $year AND MONTH(flight.start_date) = month_num ) AS flight_time FROM tank LEFT JOIN flight_tank_qty ON flight_tank_qty.tank_id = tank.id LEFT JOIN flight ON flight.id = flight_tank_qty.flight_id LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR(flight.start_date) = $year ) AS my_table GROUP BY resource_name, tank_type
Flight with their location code (ICAO)
SELECT flight.*, L1.icao_name AS departure_location, L2.icao_name AS arrival_location FROM flight LEFT JOIN location AS L1 ON flight.departure_location_id = L1.id LEFT JOIN location AS L2 ON flight.arrival_location_id = L2.id
Flight list between two dates
Two variables should be defined first and should be of Date value type :
- startDate
- endDate
SELECT f.start_date, r.name, CONCAT(UPPER(p.last_name),' ',p.first_name) AS Pilot, ( SELECT CONCAT(UPPER(p2.last_name), ' ', p2.first_name) FROM flight_pilot AS fp2 LEFT JOIN person AS p2 ON fp2.pilot_id = p2.id WHERE fp2.flight_id = f.id AND fp2.num = 1 ) AS Instructor, ( SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') FROM flight LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id) WHERE flight.id=f.id ) AS Flight_types, ( SELECT location.name FROM location WHERE location.id=f.departure_location_id ) AS departure_location, ( SELECT location.name FROM location WHERE location.id=f.arrival_location_id ) AS arrival_location, f.landing_number AS landing_number, CONCAT(FLOOR( f.duration/600),':',TIME_FORMAT(SEC_TO_TIME((f.duration/600 - FLOOR(f.duration/600))*3600),'%i')) AS duration FROM flight AS f LEFT JOIN resource AS r ON r.id = f.aircraft_id LEFT JOIN flight_pilot AS fp ON fp.flight_id = f.id LEFT JOIN person AS p ON fp.pilot_id = p.id WHERE '$startDate' <= f.start_date AND f.start_date <= '$endDate' AND fp.num = 0;
Detailed flight list per month
Following extrafields are needed
- year (Type : Year)
- month (Type : Integer) OR startDate and endDate (Type : Date)
- profile (Type : dbObject::Profile)
- occupiedSeat (Type : Integer)
This report is useful for a group of pilots from a common customer (like DGAC in France).
SELECT ( SELECT CONCAT(last_name,' ',first_name) FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0 ) AS Left_seat_Pilot_name, ( SELECT CONCAT(last_name,' ',first_name) FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1 ) AS Right_seat_Pilot_name, flight.start_date, resource.name AS resource, CONCAT(FLOOR(flight.duration/600),':',TIME_FORMAT(SEC_TO_TIME((flight.duration /600 - FLOOR(flight.duration/600))*3600),'%i')) AS flight_duration, ( SELECT GROUP_CONCAT( flight_type.name SEPARATOR ', ') FROM flight AS f2 LEFT JOIN flight_type ON (f2.flight_type_id & flight_type.id) WHERE f2.id=flight.id ) AS Flight_types, flight.landing_number, SUM(account_entry.debit)-SUM(account_entry.credit) AS amount, flight.comments FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id LEFT JOIN flight ON flight.id=flight_pilot.flight_id LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id LEFT JOIN account ON account.id=account_entry.account_id LEFT JOIN resource ON resource.id = flight.aircraft_id -- Chose one of the two following WHERE statements -- 1) Month selection WHERE YEAR(flight.start_date)=$year AND MONTH(flight.start_date)=$month -- 2) Selection between two dates WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate' AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num) AND person.profile&$profile AND (account.category = 11 OR account.category = 2) GROUP BY flight.id
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
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'
Users
Users 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
Activated user list
SELECT id, last_name, first_name FROM person WHERE activated=1
Activated user list with email
SELECT last_name, first_name, email FROM person WHERE activated=1 ORDER BY last_name, first_name
Young user list
SELECT last_name AS Last_name, first_name AS First_name, DATE_FORMAT(birthdate ,'%m-%d-%Y') AS Birthdate, IF ( (sex = 0), 'Male', 'Female' ) AS sex FROM person WHERE ($year-YEAR(birthdate))<=21
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 ORDER BY last_name,first_name
User coordinates with registration date (using ExtraField)
Extra field required :
- registrationDate
- label : Registration date
- category : User
- value type : DateTime
Validity type required :
- Cotisation
SELECT last_name, first_name, email, address, zipcode, city, state, country, home_phone, work_phone, cell_phone, sex, DATE_FORMAT(birthdate, '%Y-%m-%d') AS birthdate, nationality, validity.grant_date AS subscription_date, ( SELECT DATE_FORMAT(extra_field_content.content, '%Y-%m-%d') FROM extra_field LEFT JOIN extra_field_content ON extra_field.id=extra_field_content.extra_field_id WHERE extra_field.variable="registrationDate" and extra_field_content.category_id=person.id )AS registration_date FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE activated = 1 AND validity_type.name = 'Cotisation' ORDER BY last_name, first_name
User coordinates with registration date (using Validity)
SELECT person.last_name, person.first_name, validity.grant_date AS registration_date FROM person LEFT JOIN validity ON validity.person_id=person.id LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id WHERE validity_type.name = "Date d'inscription" AND person.activated=1 GROUP BY last_name, first_name
User list by profile
Variable $profile should be defined first and should be of dbOject::Profile value type.
SELECT last_name AS Lastname, first_name AS Firstname, profile.name AS Profile FROM person LEFT JOIN profile ON (person.profile & profile.id) WHERE person.activated = 1 AND profile.id = $profile ORDER BY Profile, Lastname, Firstname
User list by validity
Following extra field required :
- validityTypeId (Type : dbObject:ValidityType)
SELECT validity_type.name AS 'Validity', DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date_d_obtention, 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 validity_type.id=$validityTypeId ORDER BY Name, Firstname
User list with validities viewer
SELECT last_name AS Last_name, first_name AS First_name, validity_type.name AS 'Validity', DATE_FORMAT(expire_date,'%d/%m/%Y') AS Expiry_date, if((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS Expired, -- ident_value AS Commentaire, DATE_FORMAT(grant_date,'%d/%m/%Y') AS Grant_date FROM validity_type LEFT JOIN validity ON validity.validity_type_id =validity_type.id LEFT JOIN person ON person.id=validity.person_id -- Validity recently expired or about to expire WHERE period_diff(DATE_FORMAT(expire_date,'%y%m'), DATE_FORMAT(Now(),'%y%m')) < 2 AND -- period_diff(DATE_FORMAT(expire_date,'%y%m'), -- DATE_FORMAT(Now(),'%y%m')) > -2 AND -- Deactivated members person.activated = 1 -- Exclude Night qualification AND validity_type.id <> 6 ORDER BY Last_name, First_name, validity_type.name
User list 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') )
User list ordered by member number
SELECT efc.content AS numero, last_name AS Nom, first_name AS prénom, name AS login, email, address AS adresse, zipcode AS code_postal, city AS Ville, state AS etat_region, country AS pays, home_phone AS tel_dommicile, work_phone AS tel_travail, cell_phone AS tel_mobile, activated AS actif FROM person LEFT JOIN extra_field_content AS efc ON person.id = efc.category_id LEFT JOIN extra_field AS ef ON efc.extra_field_id = ef.id WHERE ef.variable = 'memberNum' AND person.activated = 1 ORDER BY CAST(efc.content AS SIGNED)
User list with subscription grant date and profiles
SELECT validity_type.name AS 'Validity', DATE_FORMAT(grant_date,'%d/%m/%Y') AS grant_date, last_name AS Name, first_name AS Firstname, ident_value AS Comment, ( SELECT GROUP_CONCAT(profile.name SEPARATOR ', ') FROM profile WHERE (person.profile & profile.id) )AS Profile FROM validity_type LEFT JOIN validity ON validity.validity_type_id =validity_type.id LEFT JOIN person ON person.id=validity.person_id WHERE YEAR(grant_date)>=$year AND MONTH(grant_date)>=1 AND validity_type.name = 'Cotisation' ORDER BY Name, Firstname
User list with registration date, profiles and total flight time
SELECT person.last_name, person.first_name, validity.grant_date AS registration_date, person.birthdate AS birthdate, IF ( (person.sex = 0), 'M', 'F' ) AS Gender, ( SELECT GROUP_CONCAT(profile.name SEPARATOR ', ') FROM profile WHERE (person.profile & profile.id) )AS Profile, IFNULL(( SELECT 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 WHERE fp.pilot_id=person.id AND fp.num=0 ), 0) AS Total_flight_time 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_type.name = "Date d'inscription" AND person.activated=1 GROUP BY last_name, first_name
User list with up to date subscription
SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity' FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE YEAR(validity.grant_date) = $year AND validity_type.id = $validityTypeId
User list without up to date subscription
SELECT last_name AS Nom, first_name AS prénom, email, home_phone AS tel_dommicile, work_phone AS tel_travail, cell_phone AS tel_mobile, DATE_FORMAT(validity.grant_date,'%d/%m/%Y') AS date_cotisation FROM person LEFT JOIN validity ON (validity.person_id = person.id) LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id) WHERE YEAR(validity.grant_date) < YEAR(NOW()) AND validity_type.name = 'Cotisation' AND activated = 1 ORDER BY last_name,first_name
User list without an account
SELECT person.last_name, person.first_name FROM person LEFT JOIN account ON account.owner_id=person.id LEFT JOIN account_type ON account.account_type=account_type.id WHERE person.activated=1 AND account.category=2 AND account_type.activated=1 AND account.activated=0
Log history
People who have deleted encashments/flows without having the permission
SELECT journal_id, journal.login, journal.date_log, journal.action, log.table_name, log.field_value AS flowId FROM journal LEFT JOIN log ON (log.journal_id=journal.id) WHERE journal.action IN ('account_pay_into_drop', 'admin_account_flow_transfer_drop', 'delete_account_entry') AND field_name = 'id' AND NOT(rights & 2097152) GROUP BY journal.login, journal.action, log.field_name, log.field_value HAVING COUNT(*) = 1
Historic of changes for a specific encashment/flow
Variable $flowId should be defined first and should be of "integer" value type.
SELECT * FROM log LEFT JOIN ( SELECT journal_id FROM log WHERE table_name='account_entry' AND field_name IN ('id', 'flow_id') AND field_value = '$flowId' ) AS tmp_log ON (tmp_log.journal_id=log.journal_id) WHERE tmp_log.journal_id IS NOT NULL