Difference between revisions of "Export generator 4"
(→Visited airfields) |
(→List of booking for maintenance ordered by resource and date with name of the responsible) |
||
Line 530: | Line 530: | ||
<sql>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' | <sql>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 | FROM booking | ||
+ | LEFT JOIN booking_activity_type ON booking.id=booking_activity_type.booking_id | ||
+ | LEFT JOIN activity_type ON activity_type.id=booking_activity_type.activity_type_id | ||
LEFT JOIN log ON log.field_value=booking.id | LEFT JOIN log ON log.field_value=booking.id | ||
LEFT JOIN journal ON journal.id=log.journal_id | LEFT JOIN journal ON journal.id=log.journal_id | ||
LEFT JOIN person ON person.name=journal.login | LEFT JOIN person ON person.name=journal.login | ||
LEFT JOIN resource ON resource.id = booking.resource_id | LEFT JOIN resource ON resource.id = booking.resource_id | ||
− | WHERE | + | WHERE activity_type.name='Maintenance' |
− | AND YEAR(booking.start_date) = $year | + | 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</sql> | ORDER BY resource.name, booking.start_date</sql> | ||
Revision as of 17:26, 31 January 2017
Contents
- 1 Introduction
- 2 SQL tips and tricks
- 3 French administration Examples
- 3.1 Flight type : Flight hours total on instruction with specific profile
- 3.2 Flight type : Flight hours total on non-instruction with specific profile
- 3.3 List of users with a specific validity without expiration date
- 3.4 Number of movements on based airfield
- 3.5 Number of movements on based airfield on specific profile
- 3.6 Visited airfields
- 3.7 Number of take-off and landings on based airfield
- 3.8 Number of take-off and landings on based airfield on specific profile
- 3.9 List of movements on based airfield
- 3.10 List of pilots who have flown without required validity
- 3.11 List of pilots who have flown less than X hours during last Y days
- 3.12 List of pilots who have flown less than X hours during last Y days on aircraft type Z
- 3.13 Pilots without flight in the last 3 months
- 3.14 Number of landings per pilot, per resource
- 3.15 Flight hours without up to date validities
- 3.16 Number of men, women over and under 21 years for profile X, validity Y up to date for year Z
- 3.17 Validities in the year : Total of youngs/adults with specific profile
- 3.18 Validities obtained in the year
- 4 Accounting
- 5 Booking
- 6 Flight time management
- 6.1 Flight hours total and last recorded counters to the date X
- 6.2 Flight hours per month
- 6.3 Flight hours total over a 12 months period
- 6.4 Flights hours total per aircraft per year
- 6.5 Flights hours total per aircraft per year and per month (for an activity type)
- 6.6 Flight hours total per pilot
- 6.7 Flight hours total per pilot per profile
- 6.8 Total Flight hours per pilot with total duration and total amount
- 6.9 Flight hours per instructor, month (for a flight type)
- 6.10 Flights hours following nationality
- 6.11 Flights hours total per flight type per month
- 6.12 Flights hours : less than 21 years, more than 21 years, male, female, for a given profile
- 6.13 Computation of flight time per month of "local flight"
- 6.14 Computation of non-"local flight" time per month
- 6.15 Stats all flight hours per month per year
- 7 Flight reports
- 7.1 Aircraft(s) which no flight were done for at least 30 day(s)
- 7.2 Detailed flight between two dates
- 7.3 Detailed flight per month
- 7.4 Flight between two dates
- 7.5 Flight log book
- 7.6 Flights which have landed elsewhere than the base field
- 7.7 Flights with a remark in one or two additional entries
- 7.8 Flight with their location code (ICAO)
- 8 Fuel/Oil reports
- 9 Error message queries
- 10 Users
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 type : Flight hours total on instruction with specific profile
- Variable $activityTypeId should be defined first and should be of dbOject::ActivityType 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 name, sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur', sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM' FROM ( SELECT activity_type.id, activity_type.name, flight.duration, resource_type.category FROM flight LEFT JOIN activity_type ON (flight.flight_type_id & activity_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) LEFT JOIN resource ON (resource.id=flight.aircraft_id) LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) WHERE YEAR(flight.start_date)=$year AND (activity_type.id='$activityTypeId' OR '-'='$activityTypeId') AND person.activated=1 AND (person.profile & '$profileId' OR '-'='$profileId') ) AS tmp_flight GROUP BY id
Flight type : Flight hours total on non-instruction with specific profile
- Variable $activityTypeId should be defined first and should be of dbOject::ActivityType 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 name, sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur', sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM' FROM ( SELECT activity_type.id, activity_type.name, flight.duration, resource_type.category FROM flight LEFT JOIN activity_type ON (flight.flight_type_id & activity_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) LEFT JOIN resource ON (resource.id=flight.aircraft_id) LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) WHERE YEAR(flight.start_date)=$year AND (activity_type.id='$activityTypeId' OR '-'='$activityTypeId') AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) AND person.activated=1 AND (person.profile & '$profileId' OR '-'='$profileId') ) AS tmp_flight GROUP BY id
List of users with a specific validity without expiration date
- Variable $validityId should be defined first and should be of dbOject::ValidityType value type.
SELECT person.first_name, person.last_name FROM validity LEFT JOIN person ON person.id=validity.person_id WHERE validity_type_id=$validityTypeId AND expire_date IS NULL
Number of movements on based airfield
- Variable $year should be defined first and should be of Year value type.
SELECT SUM(movement) AS 'Movement' FROM ( SELECT COUNT(*) AS movement FROM flight LEFT JOIN location ON (flight.departure_location_id = location.id) WHERE ( ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) OR ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) ) AND ( location.id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) AND YEAR( start_date ) = $year UNION 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) WHERE YEAR(flight.start_date) = $year AND structure_icao.id IS NOT NULL ) AS tmp_movement
Number of movements 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(movement) AS 'Movement' FROM ( SELECT COUNT(*) AS movement FROM flight LEFT JOIN location ON (flight.departure_location_id = location.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 ( ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) OR ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) ) AND ( location.id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) AND person.activated=1 AND (person.profile & '$profileId' OR '-'='$profileId') AND YEAR(flight.start_date) = $year UNION 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' OR '-'='$profileId') AND YEAR(flight.start_date) = $year AND structure_icao.id IS NOT NULL ) AS tmp_movement
Visited airfields
SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS nb_visite FROM flight LEFT JOIN location ON (flight.departure_location_id = location.id) WHERE ( ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) ) OR ( 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 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' OR '-'='$profileId') AND YEAR(flight.start_date) = $year AND structure_icao.id IS NOT NULL
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
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 activity_type AS activity_type ON activity_type.id = ftpvt.flight_type_id LEFT JOIN flight AS f ON f.flight_type_id & activity_type.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