Difference between revisions of "Export generator 3"
(→Extra Field creation) |
(→Total Flight hours per pilot with total duration and total amount) |
||
| Line 1,259: | Line 1,259: | ||
*month (Type : Integer) | *month (Type : Integer) | ||
*profile (Type : dbObject::Profile) | *profile (Type : dbObject::Profile) | ||
| + | |||
| + | This report is useful for a group of pilots from a common customer (like DGAC in France) | ||
<sql>SELECT CONCAT(person.last_name,' ',person.first_name) AS pilots_name, | <sql>SELECT CONCAT(person.last_name,' ',person.first_name) AS pilots_name, | ||
Revision as of 10:48, 18 April 2013
Contents
- 1 Introduction
- 2 SQL tips and tricks
- 3 French administration Examples
- 3.1 Flights hours total per aircraft per year
- 3.2 Flights hours total per aircraft per year and per month
- 3.3 Number of members : male, female, less than 21 years and more than 21 years
- 3.4 Flights hours : less than 21 years, more than 21 years, male, female
- 3.5 Flights hours following nationality
- 3.6 Licence in the year
- 3.7 List of pilots with specific validity
- 3.8 Young list
- 3.9 Flights hours total per flight type
- 3.10 Number of visit on other airfield
- 3.11 Number of movements on based platform due to country flight
- 3.12 Number of take-off and landings on based airfield
- 3.13 List of movements on based airfield
- 3.14 Flights hours total per instructor
- 3.15 User list with up to date subscription
- 3.16 User list without up to date subscription
- 3.17 Validity per user
- 3.18 Flight hours per month
- 3.19 Flight hours without up to date validities
- 3.20 List of pilots who have flown without required validity
- 3.21 List of pilots who have flown less than X hours during last Y days
- 3.22 List of pilots who have flown less than X hours during last Y days on aircraft type Z
- 3.23 Pilots without flight in the last 3 months
- 3.24 Members list with age and birth date
- 3.25 Member List with subscription grant date and profiles
- 3.26 Member email list
- 3.27 Number of landings per pilot, per resource
- 3.28 Get members list with membership ending before a specific date
- 3.29 Get activated user list
- 3.30 User list coordinates
- 3.31 User coordinates with registration date
- 3.32 User list by profile
- 3.33 User list by validity
- 3.34 User list by profile
- 3.35 User list with validities viewer
- 3.36 User list ordered by member number
- 4 Accounting
- 4.1 Global account balance
- 4.2 Global account balance of pilots who last subscription was 2 years ago
- 4.3 Global non null account balance
- 4.4 Account balance list
- 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 pilot 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 total per pilot
- 6.2 Flight hours total per pilot per profile
- 6.3 Total Flight hours per pilot with total duration and total amount
- 6.4 Flights hours total per instructor per year and per month
- 6.5 Computation of flight time per month of "local flight"
- 6.6 Computation of non-"local flight" time per month
- 6.7 List of flights have landed on a airfield
- 6.8 Total instructed flight hours
- 6.9 Total DCA instructed flight hours per instructor per month
- 6.10 Indemnified flight hours
- 6.11 Flight hours per instructor function to flight type
- 6.12 Aircraft(s) which no flight were done for at least 30 day(s)
- 6.13 Total tank quantity for each tank and for each aircraft type
- 6.14 Total tank quantity for each tank and for each aircraft
- 6.15 Fuel/Oil quantities per tank and aircraft type
- 6.16 Fuel/Oil sum for each aircraft
- 6.17 Fuel/Oil consumption
- 6.18 Flight with their location code (ICAO)
- 6.19 Flight list between two dates
- 6.20 Detailed flight list per month
- 6.21 Stats all flight hours per month per year
- 7 Error message queries
Introduction
The goal of this page is to propose a list of statistic generation queries (SQL).
Do not forget to read the OpenFlyers SQL stored functions and procedures page.
Please note that you can make "public" a report, ie. allows standard users to see and interact with it.
Extra field
For more flexibility, extra field can be defined per the user
Two extra field types are available:
- "external parameters" of following types:
- Whole number
- Text string
- Date
- Time
- Date and time
- database parameters (dbObject::something Value type) advanced options to access at the database field
Extra Field creation
- Go to Menu Admin > Reports > Structure > Extra Field(s)
In the bottom line add
- Fill the name field with a name that will be use within the SQL queries (prefixed with the $ character)
- Fill the label field with a name used to describe the parameter as you want it to appear within the export query form.
- Choose a Value type (the most common are at the beginning of the list).
- Click on "Add"
Then within your SQL query, you may add this parameter which will be replace by the value chosen by the user filling the export form.
If you define a parameter as a database parameters (for example dbObject::Authentication) then the form will display a combo with the list of users and your parameter will be replace by the id integer of the chosen person.
Extra Field creation examples
We create a new extra field for the current year:
- Name: $year
- Label: Year
- Value type: Year
Parameter Year must be fill in Reports/Structure/View form before to call the query (by default current year is filled when you call the page)
We create a new extra field to list the reservation of an aircraft:
- Name: $aircraftId
- Label: Aircraft
- Value type: dbObject::Aircaft
Then in Admin/Reports/Structure/Criteria we create a new query labeled "Aircraft booking" with the following query:
SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId
To use this report, we just have to select a "Aircraft" in the Reports/Structure/View form, to check "Aircraft booking" then to click on "View"
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
French administration Examples
Flights hours total per aircraft per year
SELECT name AS Callsign, YEAR( start_date ) AS Year, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight LEFT JOIN resource ON resource.id = flight.aircraft_id WHERE YEAR(start_date) = $year GROUP BY name UNION SELECT "Total", $year AS year, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total FROM flight WHERE YEAR(start_date) = $year GROUP BY year
Flights hours total per aircraft per year and per month
SELECT aircraft_id AS Num, name, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num) AS Janu, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num) AS Febr, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num) AS Marc, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num) AS Apri, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num) AS May, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num) AS June, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num) AS July, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num) AS Augu, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num) AS Sept, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num) AS Octo, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num) AS Nove, (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num) AS Dece, CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Sum FROM flight LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id LEFT JOIN resource ON resource.id = aircraft.id WHERE YEAR(start_date)= $year AND resource.virtual = 0 GROUP BY order_num UNION SELECT "Sum per","month", (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9), (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 -