Difference between revisions of "Export generator 4"

Jump to: navigation, search
(→‎Flights hours : less than 21 years, more than 21 years, male, female, for a given profile)
(→‎Flights hours total per aircraft per year and per month (for an activity type))
(168 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.
  
−
==Extra field==
+
==Business field==
−
For more flexibility, extra field can be defined per the user
+
  
−
Two extra field types are available:
+
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:
−
**Whole number
 
−
**Text string
 
 
**Date
 
**Date
−
**Time
 
 
**Date and time
 
**Date and time
−
*database parameters (dbObject::something Value type) advanced options to access at the database field
+
**Month
 +
**Text string
 +
**Time
 +
**Whole number
 +
**Year
 +
*database parameters (dbObject::something or dbObjectMulti::something Value type) advanced options to access to the database field
  
−
===Extra Field creation===
+
===[[OF_doc4-fr::Gestion-des-rapports#Ajouter-un-champ-dans-le-formulaire-des-rapports|Business field creation]]===
−
*Go to Menu '''Admin > Reports > Structure > Extra Field(s)'''
+
*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::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.
+
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.
  
−
===Extra Field creation examples===
+
===Business field creation examples===
−
We create a new extra field for the current year:
+
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 fill in Reports/Structure/View form before to call the query (by default current year is filled when you call the page)
+
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 extra field to list the reservation of an aircraft:
+
We create a new business field to list the reservation of a resource:
−
*Name: $aircraftId
+
*Name: $resourceId
−
*Label: Aircraft
+
*Label: Resource
−
*Value type: dbObject::Aircraft
+
*Value type: dbObject::Resource
−
Then in Admin/Reports/Structure/Criteria we create a new query labeled "Aircraft booking" with the following query:
+
Then in '''Admin/Reports''' we create a new query labeled "Aircraft booking" with the following query:
−
<sql>SELECT * FROM booking WHERE booking.aircraft_id=$aircraftId</sql>
+
<sql>SELECT * FROM booking WHERE booking.resource_id=$resourceId</sql>
−
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"
+
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 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.
+
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 86:
 
==Flight hours total on instruction with specific activity type/profile==
 
==Flight hours total on instruction with specific activity type/profile==
  
−
* Variable '''$activityTypeId''' should be defined first and should be of '''dbOject::ActivityType''' or '''dbOjectMulti::ActivityType''' value type.
+
Requirement:
−
* Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' or '''dbOjectMulti::Profile''' value type.
+
* Variable '''$activityTypeId''' of '''dbOjectMulti::ActivityType''' value type
−
* Variable '''$year''' should be defined first and should be of '''Year''' value type.
+
* 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 103:
 
         resource_type.category
 
         resource_type.category
 
     FROM (
 
     FROM (
−
         SELECT flight.*, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
+
         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.flight_type_id&activity_type.id
+
         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 ('$activityTypeId') OR '-' IN ('$activityTypeId') )
+
           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 115:
 
         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 '-' IN ($profileId) )
+
         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 127:
 
==Flight hours total on non-instruction with specific activity type/profile==
 
==Flight hours total on non-instruction with specific activity type/profile==
  
−
* Variable '''$activityTypeId''' should be defined first and should be of '''dbOject::ActivityType''' or '''dbOjectMulti::ActivityType''' value type.
+
Requirement:
−
* Variable '''$profileId''' should be defined first and should be of '''dbOject::Profile''' or '''dbOjectMulti::Profile''' value type.
+
* Variable '''$activityTypeId''' of '''dbOjectMulti::ActivityType''' value type
−
* Variable '''$year''' should be defined first and should be of '''Year''' value type.
+
* 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 144:
 
         resource_type.category
 
         resource_type.category
 
     FROM (
 
     FROM (
−
         SELECT flight.*, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
+
         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.flight_type_id&activity_type.id
+
         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 ('$activityTypeId') OR '-' IN ('$activityTypeId') )
+
           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 157:
 
         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 '-' IN ($profileId) )
+
         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 168:
  
 
==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 '''dbOject::ValidityType''' value type.  
+
*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=$validityTypeId AND expire_date IS NULL AND person.activated=1</sql>
+
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
−
    LEFT JOIN location ON (flight.departure_location_id = location.id)
+
     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 (
+
−
            ( 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
 
     UNION
−
     SELECT SUM(landing_number)*2 AS movement
+
     SELECT SUM(landing_number) * 2 AS movement
−
     FROM flight
+
     FROM flight, structure
−
     LEFT JOIN (
+
     WHERE YEAR(flight.start_date) = $year AND flight.departure_icao_id = structure.icao AND flight.arrival_icao_id = structure.icao
−
        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</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 '''dbOject::Profile''' value type.  
+
* 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
−
    SELECT COUNT(*) AS movement
+
    FROM structure, flight
−
    FROM flight
+
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
−
    LEFT JOIN location ON (flight.departure_location_id = location.id)
+
    LEFT JOIN (
−
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
+
        SELECT person.*
−
    LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0)
+
        FROM person
−
    WHERE (
+
        LEFT JOIN profile ON person.profile&profile.id
−
            ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
+
        WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
−
            OR
+
          AND person.activated=1
−
            ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
+
        GROUP BY person.id
−
        )
+
    ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
−
        AND ( location.id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
+
    WHERE (
−
        AND person.activated=1
+
        ( flight.departure_icao_id != structure.icao)
−
        AND (person.profile & '$profileId' OR '-'='$profileId')
+
        OR
−
        AND YEAR(flight.start_date) = $year
+
        ( flight.arrival_icao_id != structure.icao)
−
    UNION
+
    )
−
    SELECT SUM(landing_number)*2 AS movement
+
    AND ( flight.departure_icao_id != structure.icao)
−
    FROM flight
+
    AND YEAR(flight.start_date) = 2019
−
    LEFT JOIN (
+
    AND personWithProfile.id IS NOT NULL
−
        SELECT location.id
+
    UNION
−
        FROM location
+
SELECT SUM(flight.landing_number)*2 AS movement
−
        LEFT JOIN club ON location.icao_name=club.icao
+
    FROM structure, flight
−
        WHERE club.id IS NOT NULL
+
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
−
        LIMIT 1
+
    LEFT JOIN (
−
    ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id)
+
        SELECT person.*
−
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
+
        FROM person
−
    LEFT JOIN person ON (person.id=flight_pilot.pilot_id AND flight_pilot.num=0)
+
        LEFT JOIN profile ON person.profile&profile.id
−
    WHERE person.activated=1
+
        WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
−
      AND (person.profile & '$profileId' OR '-'='$profileId')
+
          AND person.activated=1
−
      AND YEAR(flight.start_date) = $year
+
        GROUP BY person.id
−
      AND structure_icao.id IS NOT NULL
+
    ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
−
) AS tmp_movement</sql>
+
    WHERE YEAR(flight.start_date) = $year
−
 
+
    AND flight.departure_icao_id = structure.icao
−
==Visited airfields==
+
    AND flight.arrival_icao_id = structure.icao
−
 
+
    AND personWithProfile.id IS NOT NULL
−
<sql>SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS nb_visite
+
) AS tmp_movement</sql>
−
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</sql>
+
−
 
+
−
==Number of take-off and landings on based airfield==
+
−
 
+
−
<SQL>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</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 '''dbOject::Profile''' value type.  
+
* 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 location.id
+
     SELECT person.*
−
     FROM location
+
     FROM person
−
     LEFT JOIN club ON location.icao_name=club.icao
+
     LEFT JOIN profile ON person.profile & profile.id
−
     WHERE club.id IS NOT NULL
+
     WHERE (profile.id IN($profileId) OR '-' IN($profileId)) AND person.activated = 1
−
    LIMIT 1
+
    GROUP BY person.id
−
) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id)
+
) AS personWithProfile ON ( personWithProfile.id = flight_pilot.pilot_id AND flight_pilot.num = 0 )
−
LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
+
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>
−
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</sql>
+
  
 
==List of movements on based airfield==
 
==List of movements on based airfield==
−
<sql>SELECT resource.name, DATE_FORMAT(flight.start_date, '%d %m %Y' ) AS Date, sum(landing_number) AS 'Nb Att'
+
<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 resource ON resource.id = flight.aircraft_id
+
LEFT JOIN location ON flight.departure_icao_id = location.icao_name
−
WHERE ( flight.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao))
+
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
−
  AND (flight.arrival_location_id  = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao))
+
GROUP BY icao_name
−
  AND YEAR( start_date ) = $year
+
ORDER BY nb_flight DESC </sql>
−
GROUP BY date,name</sql>
+
  
−
==Pilots who have flown without required validity==
+
==User flying without validity==
  
−
<sql>SELECT CONCAT(person.last_name, ' ', person.first_name) AS pilot, validity_type.name AS 'Validity'
+
<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.flight_type_id
+
LEFT JOIN activity_type ON activity_type.id = flight_type_mandatory_validity_type.activity_type_id
−
LEFT JOIN flight ON flight.