Difference between revisions of "Export generator 4"
(→Booking) |
(→Return a user list telling if each user has a profile A, B, C, etc.) |
||
(8 intermediate revisions by 2 users not shown) | |||
Line 52: | Line 52: | ||
Example with last entry from variable #1 in variable_value table: | Example with last entry from variable #1 in variable_value table: | ||
<sql>SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC LIMIT 1</sql> | <sql>SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC LIMIT 1</sql> | ||
+ | |||
+ | ==Return a user list telling if each user has a profile A, B, C, etc.== | ||
+ | <sql>SELECT | ||
+ | person.first_name, | ||
+ | person.last_name, | ||
+ | IF(person.profile & 1, 'Yes', 'No') AS 'Profile A', | ||
+ | IF(person.profile & 2, 'Yes', 'No') AS 'Profile B', | ||
+ | IF(person.profile & 4, 'Yes', 'No') AS 'Profile C' | ||
+ | FROM person | ||
+ | WHERE activated=1;</sql> | ||
+ | |||
+ | ==Return the whole content of a given table== | ||
+ | Example with table "profile" | ||
+ | <sql>SELECT * FROM profile;</sql> | ||
+ | |||
+ | This type of SELECT does not work within OpenFlyers to access restricted table like the '''person''' table | ||
==Test valid entries== | ==Test valid entries== | ||
Line 573: | Line 589: | ||
<sql>SELECT | <sql>SELECT | ||
− | CONCAT( '[LINK=index.php | + | CONCAT( '[LINK=index.php[QUESTION_MARK]menuAction=admin_add_modify_user&menuParameter=',person.id,'&menuParameterBis=last_name&menuParameter3=', LEFT(person.last_name, 1),']', person.id, '[/LINK]' ) AS _tr(ID), |
person.first_name AS _tr(FIRST_NAME), | person.first_name AS _tr(FIRST_NAME), | ||
person.last_name AS _tr(LAST_NAME), | person.last_name AS _tr(LAST_NAME), | ||
Line 592: | Line 608: | ||
<sql>[OF_DYNAMIC_SQL] | <sql>[OF_DYNAMIC_SQL] | ||
SELECT CONCAT( | SELECT CONCAT( | ||
− | 'SELECT booking.id AS _tr(ID), DATE_FORMAT(booking.start_date, \'%Y-%m-%d %H:%i\') AS _tr(START_DATE), DATE_FORMAT(booking.end_date, \'%Y-%m-%d %H:%i\') AS _tr(END_DATE), resource.name AS _tr(RESOURCE), | + | 'SELECT booking.id AS _tr(ID), DATE_FORMAT(booking.start_date, \'%Y-%m-%d %H:%i\') AS _tr(START_DATE), DATE_FORMAT(booking.end_date, \'%Y-%m-%d %H:%i\') AS _tr(END_DATE), |
+ | ( | ||
+ | SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \') | ||
+ | FROM booking_activity_type | ||
+ | LEFT JOIN activity_type ON (booking_activity_type.activity_type_id = activity_type.id) | ||
+ | WHERE booking_activity_type.booking_id=booking.id | ||
+ | ) AS _tr(ACTIVITY_TYPE), | ||
+ | resource.name AS _tr(RESOURCE), | ||
left_booking_person.full_name AS _tr(LEFT_PLACE), right_booking_person.full_name AS _tr(RIGHT_PLACE), ', | left_booking_person.full_name AS _tr(LEFT_PLACE), right_booking_person.full_name AS _tr(RIGHT_PLACE), ', | ||
IFNULL(GROUP_CONCAT( | IFNULL(GROUP_CONCAT( | ||
Line 1,168: | Line 1,191: | ||
<sql>[OF_DYNAMIC_SQL] | <sql>[OF_DYNAMIC_SQL] | ||
SELECT CONCAT( | SELECT CONCAT( | ||
− | 'SELECT resource.name AS | + | 'SELECT resource.name AS _tr(RESOURCE_NAME), ', |
GROUP_CONCAT( | GROUP_CONCAT( | ||
CONCAT( | CONCAT( | ||
Line 1,178: | Line 1,201: | ||
) | ) | ||
), | ), | ||
− | ', sexa2HoursMinute( SUM( tmp_flight.duration ) ) AS | + | ', sexa2HoursMinute( SUM( tmp_flight.duration ) ) AS _tr(ALL_ACTIVITY_TYPES) |
FROM resource | FROM resource | ||
INNER JOIN | INNER JOIN | ||
Line 1,184: | Line 1,207: | ||
SELECT flight.aircraft_id, flight.activity_type_id, duration | SELECT flight.aircraft_id, flight.activity_type_id, duration | ||
FROM flight | FROM flight | ||
− | WHERE flight.start_date >= | + | WHERE flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight.airborne = 0 |
) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id) | ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id) | ||
GROUP BY resource.id' | GROUP BY resource.id' | ||
Line 1,228: | Line 1,251: | ||
SELECT CONCAT( | SELECT CONCAT( | ||
'SELECT | 'SELECT | ||
− | flight.id AS | + | flight.id AS _tr(ID), |
− | IF(flight.validated=1, | + | IF(flight.validated=1, _tr(YES), _tr(NO)) AS _tr(VALIDATED), |
− | flight.start_date AS | + | flight.start_date AS _tr(START_DATE), |
− | resource.name AS | + | resource.name AS _tr(RESOURCE), |
( | ( | ||
SELECT CONCAT(UPPER(tmp_person.last_name), \' \', tmp_person.first_name) | SELECT CONCAT(UPPER(tmp_person.last_name), \' \', tmp_person.first_name) | ||
Line 1,237: | Line 1,260: | ||
LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id | LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id | ||
WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 0 | WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 0 | ||
− | ) AS | + | ) AS _tr(LEFT_PLACE), |
( | ( | ||
SELECT CONCAT(UPPER(tmp_person.last_name), \' \', tmp_person.first_name) | SELECT CONCAT(UPPER(tmp_person.last_name), \' \', tmp_person.first_name) | ||
Line 1,243: | Line 1,266: | ||
LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id | LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id | ||
WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 1 | WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 1 | ||
− | ) AS | + | ) AS _tr(RIGHT_PLACE), |
( | ( | ||
SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \') | SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \') | ||
Line 1,249: | Line 1,272: | ||
LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id) | LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id) | ||
WHERE flight.id=tmp_flight.id | WHERE flight.id=tmp_flight.id | ||
− | ) AS | + | ) AS _tr(ACTIVITY_TYPE), |
( | ( | ||
SELECT location.name | SELECT location.name | ||
FROM location | FROM location | ||
WHERE location.icao_name=flight.departure_icao_id | WHERE location.icao_name=flight.departure_icao_id | ||
− | ) AS | + | ) AS _tr(DEPARTURE), |
( | ( | ||
SELECT location.name | SELECT location.name | ||
FROM location | FROM location | ||
WHERE location.icao_name=flight.arrival_icao_id | WHERE location.icao_name=flight.arrival_icao_id | ||
− | ) AS | + | ) AS _tr(ARRIVAL), |
− | flight.landing_number AS | + | flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER), |
− | flight.people_onboard AS | + | flight.people_onboard AS _tr(FLIGHT_PEOPLE_ON_BOARD), |
− | sexa2HoursMinute(flight.counter_departure) AS | + | sexa2HoursMinute(flight.counter_departure) AS _tr(FLIGHT_COUNTER_DEPARTURE), |
− | sexa2HoursMinute(flight.counter_arrival) AS | + | sexa2HoursMinute(flight.counter_arrival) AS _tr(FLIGHT_COUNTER_ARRIVAL), |
− | sexa2HoursMinute(flight.duration) AS | + | sexa2HoursMinute(flight.duration) AS _tr(DURATION_IN_HOURS_AND_MINUTES), |
− | sexa2HoursHundredths(flight.duration) AS | + | sexa2HoursHundredths(flight.duration) AS _tr(DURATION_IN_HOURS_AND_HUNDREDTHS),', |
IFNULL(GROUP_CONCAT( | IFNULL(GROUP_CONCAT( | ||
CONCAT( | CONCAT( | ||
Line 1,289: | Line 1,312: | ||
RIGHT JOIN account ON (account.id=account_entry.account_id AND (account.category = 11 OR account.category = 2)) | RIGHT JOIN account ON (account.id=account_entry.account_id AND (account.category = 11 OR account.category = 2)) | ||
WHERE account_entry.flow_id = flight_account_entry.account_entry_id | WHERE account_entry.flow_id = flight_account_entry.account_entry_id | ||
− | ) AS | + | ) AS _tr(AMOUNT) |
FROM flight | FROM flight | ||
LEFT JOIN resource ON resource.id = flight.aircraft_id | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id | LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id | ||
− | WHERE flight.start_date >= | + | WHERE flight.start_date >= $startDate AND flight.start_date <= $endDate |
ORDER BY flight.start_date; | ORDER BY flight.start_date; | ||
' | ' | ||
) | ) | ||
FROM business_field | FROM business_field | ||
− | WHERE business_field.category='FLIGHT'</sql> | + | WHERE business_field.category='FLIGHT' </sql> |
==Flight log book== | ==Flight log book== | ||
Line 1,365: | Line 1,388: | ||
<sql>SELECT CONCAT( | <sql>SELECT CONCAT( | ||
− | '[LINK=index.php | + | '[LINK=index.php[QUESTION_MARK]menuAction=flight_record&menuParameter=', |
flight.id, | flight.id, | ||
'&menuParameterBis=flight_resource_logbook&menuParameter3=1]', | '&menuParameterBis=flight_resource_logbook&menuParameter3=1]', |
Revision as of 14:36, 5 September 2022
Contents
- 1 Introduction
- 2 SQL tips and tricks
- 3 French administration Examples
- 3.1 Flight hours total on instruction with specific activity type/profile
- 3.2 Flight hours total on non-instruction with specific activity type/profile
- 3.3 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 Number of take-off and landings on based airfield
- 3.7 Number of take-off and landings on based airfield on specific profile
- 3.8 List of movements on based airfield
- 3.9 User flying without validity
- 3.10 List of pilots who have flown less than X hours during last Y days
- 3.11 List of pilots who have flown less than X hours during last Y days on aircraft type Z
- 3.12 Pilots without flight in the last X months
- 3.13 Total user per age profile validity: Number of men, women over and under X years for profile P, validity V up to date for year A
- 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 Total hours by activities and resource categories for an instructor for one year
- 3.18 Validity year young specific profile: Total of youngs/adults with specific profile
- 3.19 Validities obtained in the year
- 3.20 Visited airfields
- 3.21 Visited outsider airfield
- 3.22 Young from this year
- 4 Accounting
- 5 Bookings
- 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
- 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
- 6.16 Flight hours per activity type between date
- 7 Flight reports
- 7.1 Activities summary for a person on the right place for a given month
- 7.2 Aircraft(s) which no flight were done for at least 30 day(s)
- 7.3 Flight between two dates
- 7.4 Flight log book
- 7.5 Flights which have landed elsewhere than the base field
- 7.6 Flights with mechanic remark
- 7.7 Flight with their location code (ICAO)
- 7.8 Flight hours total per person
- 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.
Business field
For more flexibility, business field can be defined by the user.
Two business field types are available:
- "external parameters" of following types:
- Date
- Date and time
- Month
- Text string
- Time
- Whole number
- Year
- database parameters (dbObject::something or dbObjectMulti::something Value type) advanced options to access to the database field
Business field creation
- Go to Menu Admin > Reports > Custom reports > Business 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::Person) 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.
Business field creation examples
We create a new business field for the current year:
- Name: $year
- Label: Year
- Value type: Year
Parameter Year must be filled in Reports/View form before to call the query (by default current year is filled when you call the page)
We create a new business field to list the reservation of a resource:
- Name: $resourceId
- Label: Resource
- Value type: dbObject::Resource
Then in Admin/Reports we create a new query labeled "Aircraft booking" with the following query:
SELECT * FROM booking WHERE booking.resource_id=$resourceId
To use this report, we just have to select a "Resource" in the Reports/View form, to check "Resource 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
Return a user list telling if each user has a profile A, B, C, etc.
SELECT person.first_name, person.last_name, IF(person.profile & 1, 'Yes', 'No') AS 'Profile A', IF(person.profile & 2, 'Yes', 'No') AS 'Profile B', IF(person.profile & 4, 'Yes', 'No') AS 'Profile C' FROM person WHERE activated=1;
Return the whole content of a given table
Example with table "profile"
SELECT * FROM profile;
This type of SELECT does not work within OpenFlyers to access restricted table like the person table
Test valid entries
For business 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 with specific activity type/profile
Requirement:
- Variable $activityTypeId of dbOjectMulti::ActivityType value type
- Variable $profileId of dbOjectMulti::Profile value type
- Variable $year of Year value type
SELECT tmp_flight.activity_type_name AS 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 flightWithActivityType.activity_type_id, flightWithActivityType.activity_type_name, flightWithActivityType.duration, resource_type.category FROM ( SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name FROM flight LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id WHERE YEAR(flight.start_date)=$year AND flight.airborne=0 AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) ) ) AS flightWithActivityType LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1) LEFT JOIN ( SELECT person.* FROM person LEFT JOIN profile ON person.profile&profile.id WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) ) AND person.activated=1 GROUP BY person.id ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id) LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id) LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) WHERE personWithProfile.id IS NOT NULL ) AS tmp_flight GROUP BY tmp_flight.activity_type_id
Flight hours total on non-instruction with specific activity type/profile
Requirement:
- Variable $activityTypeId of dbOjectMulti::ActivityType value type
- Variable $profileId of dbOjectMulti::Profile value type
- Variable $year of Year value type
SELECT tmp_flight.activity_type_name AS 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 flightWithActivityType.activity_type_id, flightWithActivityType.activity_type_name, flightWithActivityType.duration, resource_type.category FROM ( SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name FROM flight LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id WHERE YEAR(flight.start_date)=$year AND flight.airborne=0 AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) ) AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) ) AS flightWithActivityType LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=0) LEFT JOIN ( SELECT person.* FROM person LEFT JOIN profile ON person.profile&profile.id WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) ) AND person.activated=1 GROUP BY person.id ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id) LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id) LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) WHERE personWithProfile.id IS NOT NULL ) AS tmp_flight GROUP BY tmp_flight.activity_type_id
Users with a specific validity without expiration date
- Variable $validityId should be defined first and should be of dbOjectMulti::ValidityType value type.
SELECT person.first_name, person.last_name, validity_type.name AS 'Validite' FROM validity LEFT JOIN validity_type ON validity.validity_type_id=validity_type.id LEFT JOIN person ON person.id=validity.person_id WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) ) AND expire_date IS NULL AND person.activated=1
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, structure WHERE ( ( flight.departure_icao_id != structure.icao ) OR( flight.arrival_icao_id != structure.icao ) ) AND( flight.departure_icao_id != structure.icao ) AND YEAR(start_date) = $year UNION SELECT SUM(landing_number) * 2 AS movement FROM flight, structure WHERE YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao ) AS tmp_movement
Number of movements on based airfield on specific profile
- Variable $profileId should be defined first and should be of dbOjectMulti::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 structure, flight LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) LEFT JOIN