Difference between revisions of "Export generator 4"
m (→Flight hours total per pilot) |
(→Booking) |
||
(258 intermediate revisions by 5 users not shown) | |||
Line 6: | Line 6: | ||
Please note that you can make "public" a report, ie. allows standard users to see and interact with it. | Please note that you can make "public" a report, ie. allows standard users to see and interact with it. | ||
− | == | + | ==Business field== |
− | + | ||
− | Two | + | For more flexibility, business field can be defined by the user. |
+ | |||
+ | Two business field types are available: | ||
*"external parameters" of following types: | *"external parameters" of following types: | ||
− | |||
− | |||
**Date | **Date | ||
− | |||
**Date and time | **Date and time | ||
− | *database parameters (dbObject::something Value type) advanced options to access | + | **Month |
+ | **Text string | ||
+ | **Time | ||
+ | **Whole number | ||
+ | **Year | ||
+ | *database parameters (dbObject::something or dbObjectMulti::something Value type) advanced options to access to the database field | ||
− | === | + | ===[[OF_doc4-fr::Gestion-des-rapports#Ajouter-un-champ-dans-le-formulaire-des-rapports|Business field creation]]=== |
− | *Go to Menu '''Admin > Reports > | + | *Go to Menu '''Admin > Reports > Custom reports > Business Field(s)''' |
In the bottom line add | 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 '''name''' field with a name that will be use within the SQL queries (prefixed with the $ character) | ||
Line 27: | Line 30: | ||
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. | 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:: | + | 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 | + | We create a new business field for the current year: |
*Name: $year | *Name: $year | ||
*Label: Year | *Label: Year | ||
*Value type: Year | *Value type: Year | ||
− | Parameter Year must be | + | 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 | + | We create a new business field to list the reservation of a resource: |
− | *Name: $ | + | *Name: $resourceId |
− | *Label: | + | *Label: Resource |
− | *Value type: dbObject:: | + | *Value type: dbObject::Resource |
− | Then in Admin/Reports | + | Then in '''Admin/Reports''' we create a new query labeled "Aircraft booking" with the following query: |
− | <sql>SELECT * FROM booking WHERE booking. | + | <sql>SELECT * FROM booking WHERE booking.resource_id=$resourceId</sql> |
− | To use this report, we just have to select a " | + | 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= | =SQL tips and tricks= | ||
Line 49: | 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== | ||
− | For | + | 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) : | Per example, $occupiedSeat can have following values (0, 1 or NULL) : | ||
Line 65: | Line 84: | ||
=French administration Examples= | =French administration Examples= | ||
− | == | + | ==Flight hours total on instruction with specific activity type/profile== |
− | * Variable '''$ | + | Requirement: |
− | * Variable '''$profileId''' | + | * Variable '''$activityTypeId''' of '''dbOjectMulti::ActivityType''' value type |
− | * Variable '''$year''' | + | * Variable '''$profileId''' of '''dbOjectMulti::Profile''' value type |
+ | * Variable '''$year''' of '''Year''' value type | ||
− | <sql>SELECT name, | + | <sql>SELECT tmp_flight.activity_type_name AS name, |
sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', | sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', | ||
sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', | sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', | ||
Line 78: | Line 98: | ||
FROM ( | FROM ( | ||
SELECT | SELECT | ||
− | + | flightWithActivityType.activity_type_id, | |
− | + | flightWithActivityType.activity_type_name, | |
− | + | flightWithActivityType.duration, | |
resource_type.category | resource_type.category | ||
− | FROM flight | + | FROM ( |
− | + | SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name | |
− | LEFT JOIN flight_pilot ON (flight_pilot.flight_id= | + | FROM flight |
− | LEFT JOIN person ON (person.id=flight_pilot.pilot_id) | + | LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id |
− | LEFT JOIN resource ON (resource.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) | LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) | ||
− | WHERE | + | WHERE personWithProfile.id IS NOT NULL |
− | + | ||
− | + | ||
− | + | ||
) AS tmp_flight | ) AS tmp_flight | ||
− | GROUP BY | + | GROUP BY tmp_flight.activity_type_id</sql> |
− | == | + | ==Flight hours total on non-instruction with specific activity type/profile== |
− | * Variable '''$ | + | Requirement: |
− | * Variable '''$profileId''' | + | * Variable '''$activityTypeId''' of '''dbOjectMulti::ActivityType''' value type |
− | * Variable '''$year''' | + | * Variable '''$profileId''' of '''dbOjectMulti::Profile''' value type |
+ | * Variable '''$year''' of '''Year''' value type | ||
− | <sql>SELECT name, | + | <sql>SELECT tmp_flight.activity_type_name AS name, |
sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', | sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion', | ||
sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', | sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère', | ||
Line 108: | Line 139: | ||
FROM ( | FROM ( | ||
SELECT | SELECT | ||
− | + | flightWithActivityType.activity_type_id, | |
− | + | flightWithActivityType.activity_type_name, | |
− | + | flightWithActivityType.duration, | |
resource_type.category | resource_type.category | ||
− | FROM flight | + | FROM ( |
− | + | SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name | |
− | LEFT JOIN flight_pilot ON (flight_pilot.flight_id= | + | FROM flight |
− | LEFT JOIN person ON (person.id=flight_pilot.pilot_id) | + | LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id |
− | LEFT JOIN resource ON (resource.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) | LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) | ||
− | WHERE | + | WHERE personWithProfile.id IS NOT NULL |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
) AS tmp_flight | ) AS tmp_flight | ||
− | GROUP BY | + | GROUP BY tmp_flight.activity_type_id</sql> |
− | == | + | ==Users with a specific validity without expiration date== |
− | *Variable '''$validityId''' should be defined first and should be of ''' | + | *Variable '''$validityId''' should be defined first and should be of '''dbOjectMulti::ValidityType''' value type. |
− | <sql>SELECT person.first_name, person.last_name FROM validity | + | <sql>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 | LEFT JOIN person ON person.id=validity.person_id | ||
− | WHERE validity_type_id | + | WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) ) |
+ | AND expire_date IS NULL | ||
+ | AND person.activated=1</sql> | ||
+ | |||
+ | ==Number of landings at the base airfield== | ||
+ | <sql>SELECT only_base.sum AS _tr(FLIGHT_LANDING_NUMBER_WITH_TAKEOFF_AND_LANDING_AT_THE_BASE), all_landing.sum AS _tr(REPORT_NUMBER_LANDING_BASED_AIRFIELD) FROM | ||
+ | (SELECT SUM(flight.landing_number) AS sum | ||
+ | FROM flight, structure | ||
+ | WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year) AS only_base, | ||
+ | |||
+ | (SELECT SUM(flight.landing_number) AS sum | ||
+ | FROM flight, structure | ||
+ | WHERE ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year) AS all_landing;</sql> | ||
==Number of movements on based airfield== | ==Number of movements on based airfield== | ||
− | |||
* Variable '''$year''' should be defined first and should be of '''Year''' value type. | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
<sql>SELECT SUM(movement) AS 'Movement' | <sql>SELECT SUM(movement) AS 'Movement' | ||
− | FROM | + | FROM ( |
− | ( | + | |
SELECT COUNT(*) AS movement | SELECT COUNT(*) AS movement | ||
− | FROM flight | + | 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 | |
− | WHERE ( | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
UNION | UNION | ||
− | SELECT SUM(landing_number)*2 AS movement | + | SELECT SUM(landing_number) * 2 AS movement |
− | FROM flight | + | 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</sql> | ) AS tmp_movement</sql> | ||
==Number of movements on based airfield on specific profile== | ==Number of movements on based airfield on specific profile== | ||
− | * Variable '''$profileId''' should be defined first and should be of ''' | + | * 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. | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
<sql>SELECT SUM(movement) AS 'Movement' | <sql>SELECT SUM(movement) AS 'Movement' | ||
− | FROM | + | FROM ( |
− | ( | + | SELECT COUNT(*) AS movement |
− | + | FROM structure, flight | |
− | + | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) | |
− | + | 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 AND flight_pilot.num=0) | |
− | + | WHERE ( | |
− | + | ( flight.departure_icao_id != structure.icao) | |
− | + | OR | |
− | + | ( flight.arrival_icao_id != structure.icao) | |
− | + | ) | |
− | + | AND ( flight.departure_icao_id != structure.icao) | |
− | + | AND YEAR(flight.start_date) = 2019 | |
− | + | AND personWithProfile.id IS NOT NULL | |
− | + | UNION | |
− | + | SELECT SUM(flight.landing_number)*2 AS movement | |
− | + | FROM structure, flight | |
− | + | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id) | |
− | + | 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 AND flight_pilot.num=0) | |
− | ) AS | + | WHERE YEAR(flight.start_date) = $year |
− | + | AND flight.departure_icao_id = structure.icao | |
− | + | AND flight.arrival_icao_id = structure.icao | |
− | + | AND personWithProfile.id IS NOT NULL | |
− | + | ) AS tmp_movement</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
==Number of take-off and landings on based airfield on specific profile== | ==Number of take-off and landings on based airfield on specific profile== | ||
− | * Variable '''$profileId''' should be defined first and should be of ''' | + | * 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. | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
− | <sql>SELECT SUM(landing_number)*2 AS 'Movement' | + | <sql>SELECT SUM(flight.landing_number) * 2 AS 'Movement' |
− | FROM flight | + | FROM structure, flight |
+ | LEFT JOIN flight_pilot ON ( flight_pilot.flight_id = flight.id ) | ||
LEFT JOIN ( | LEFT JOIN ( | ||
− | SELECT | + | SELECT person.* |
− | FROM | + | FROM person |
− | LEFT JOIN |