Difference between revisions of "Export generator 4"
(→Number of men, women over and under X years for profile P, validity V up to date for year A) |
(→Booking) |
||
(156 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 51: | Line 54: | ||
==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 67: | Line 70: | ||
==Flight hours total on instruction with specific activity type/profile== | ==Flight hours total on instruction with specific activity type/profile== | ||
− | * Variable '''$activityTypeId''' | + | 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 tmp_flight.activity_type_name AS name, | <sql>SELECT tmp_flight.activity_type_name AS name, | ||
Line 83: | Line 87: | ||
resource_type.category | resource_type.category | ||
FROM ( | FROM ( | ||
− | SELECT flight. | + | SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name |
FROM flight | FROM flight | ||
− | LEFT JOIN activity_type ON flight. | + | LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id |
WHERE YEAR(flight.start_date)=$year | WHERE YEAR(flight.start_date)=$year | ||
AND flight.airborne=0 | AND flight.airborne=0 | ||
− | AND ( activity_type.id IN ( | + | AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) ) |
) AS flightWithActivityType | ) AS flightWithActivityType | ||
LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1) | LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1) | ||
Line 95: | Line 99: | ||
FROM person | FROM person | ||
LEFT JOIN profile ON person.profile&profile.id | LEFT JOIN profile ON person.profile&profile.id | ||
− | WHERE ( profile.id IN ($profileId) OR ' | + | WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) ) |
AND person.activated=1 | AND person.activated=1 | ||
GROUP BY person.id | GROUP BY person.id | ||
Line 107: | Line 111: | ||
==Flight hours total on non-instruction with specific activity type/profile== | ==Flight hours total on non-instruction with specific activity type/profile== | ||
− | * Variable '''$activityTypeId''' | + | 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 tmp_flight.activity_type_name AS name, | <sql>SELECT tmp_flight.activity_type_name AS name, | ||
Line 123: | Line 128: | ||
resource_type.category | resource_type.category | ||
FROM ( | FROM ( | ||
− | SELECT flight. | + | SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name |
FROM flight | FROM flight | ||
− | LEFT JOIN activity_type ON flight. | + | LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id |
WHERE YEAR(flight.start_date)=$year | WHERE YEAR(flight.start_date)=$year | ||
AND flight.airborne=0 | AND flight.airborne=0 | ||
− | AND ( activity_type.id IN ( | + | AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) ) |
AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) | AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1) | ||
) AS flightWithActivityType | ) AS flightWithActivityType | ||
Line 136: | Line 141: | ||
FROM person | FROM person | ||
LEFT JOIN profile ON person.profile&profile.id | LEFT JOIN profile ON person.profile&profile.id | ||
− | WHERE ( profile.id IN ($profileId) OR ' | + | WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) ) |
AND person.activated=1 | AND person.activated=1 | ||
GROUP BY person.id | GROUP BY person.id | ||
Line 147: | Line 152: | ||
==Users with a specific validity without expiration date== | ==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 movements on based airfield== | ==Number of movements on based airfield== | ||
Line 158: | Line 167: | ||
<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) | |
− | + | 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== | ==Number of take-off and landings on based airfield== | ||
− | <SQL>SELECT SUM( landing_number )*2 AS nb_mouvement | + | <SQL>SELECT SUM(flight.landing_number) * 2 AS nb_mouvement |
− | FROM flight | + | FROM flight, structure |
− | WHERE ( | + | WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year </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 | + | LEFT JOIN profile ON person.profile & profile.id |
− | WHERE | + | 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 personWithProfile.id IS NOT NULL AND YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao</sql> | |
− | + | ||
− | WHERE | + | |
− | + | ||
− | + | ||
− | + | ||
==List of movements on based airfield== | ==List of movements on based airfield== | ||
− | <sql>SELECT | + | <sql>SELECT location.icao_name AS ICAO, location.name AS NAME, COUNT(location.icao_name) AS nb_flight |
− | FROM flight | + | FROM structure, flight |
− | LEFT JOIN | + | LEFT JOIN location ON flight.departure_icao_id = location.icao_name |
− | WHERE ( flight. | + | WHERE ( flight.departure_icao_id = structure.icao ) OR( flight.arrival_icao_id = structure.icao ) AND departure_icao_id != arrival_icao_id AND YEAR(start_date) = $year |
− | + | GROUP BY icao_name | |
− | + | ORDER BY nb_flight DESC </sql> | |
− | GROUP BY | + | |
− | == | + | ==User flying without validity== |
− | <sql>SELECT CONCAT(person.last_name, ' ', person.first_name) AS | + | <sql>SELECT |
+ | CONCAT(person.last_name, ' ', person.first_name) AS _tr(PILOT), | ||
+ | validity_type.name AS _tr(VALIDITY) | ||
FROM flight_type_mandatory_validity_type | FROM flight_type_mandatory_validity_type | ||
− | LEFT JOIN activity_type ON activity_type.id = flight_type_mandatory_validity_type. | + | LEFT JOIN activity_type ON activity_type.id = flight_type_mandatory_validity_type.activity_type_id |
− | LEFT JOIN flight ON flight. | + | LEFT JOIN flight ON flight.activity_type_id & activity_type.id |
LEFT JOIN resource ON resource.id = flight.aircraft_id | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
LEFT JOIN resource_type ON resource.resource_type_id = resource_type.id | LEFT JOIN resource_type ON resource.resource_type_id = resource_type.id | ||
Line 288: | Line 266: | ||
LEFT JOIN person ON person.id = flight_pilot.pilot_id | LEFT JOIN person ON person.id = flight_pilot.pilot_id | ||
LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id) | LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id) | ||
− | LEFT JOIN validity ON | + | LEFT JOIN validity ON validity_type.id = validity.validity_type_id AND person.id = validity.person_id AND validity.is_current_validity = 1 |
WHERE flight.airborne = 0 | WHERE flight.airborne = 0 | ||
AND validity_type.id IS NOT NULL | AND validity_type.id IS NOT NULL | ||
AND validity_type.experience_formula IS NULL | AND validity_type.experience_formula IS NULL | ||
AND validity.validity_type_id IS NULL | AND validity.validity_type_id IS NULL | ||
− | AND person.activated=1 | + | AND person.activated = 1 |
+ | AND validity_type.activated = 1 | ||
GROUP BY person.id, validity_type.id | GROUP BY person.id, validity_type.id | ||
− | ORDER BY | + | ORDER BY `_tr(PILOT)`, validity_type.name</sql> |
==List of pilots who have flown less than X hours during last Y days== | ==List of pilots who have flown less than X hours during last Y days== | ||
Line 325: | Line 304: | ||
ORDER BY pilot</sql> | ORDER BY pilot</sql> | ||
− | ==Pilots without flight in the last | + | ==Pilots without flight in the last X months== |
− | * Variable '''$ | + | * Variable '''$numberMonth''' should be defined first and should be of '''integer''' value type. |