Difference between revisions of "Export generator 4"

Jump to: navigation, search
(→‎Computation of non-"local flight" time per month)
(→‎Booking)
 
(247 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 65: Line 84:
 
=French administration Examples=
 
=French administration Examples=
  
−
==Flight type : Flight hours total on instruction with specific profile==
+
==Flight hours total on instruction with specific activity type/profile==
  
−
* Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type.
+
Requirement:
−
* Variable '''$profileId''' should be defined first and should be of '''dbOject::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 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
−
         flight_type.id,
+
         flightWithActivityType.activity_type_id,
−
         flight_type.name,
+
         flightWithActivityType.activity_type_name,
−
         flight.duration,
+
         flightWithActivityType.duration,
 
         resource_type.category
 
         resource_type.category
−
     FROM flight
+
     FROM (
−
    LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
+
        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=flight.id AND flight_pilot.num=1)
+
        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=flight.aircraft_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 YEAR(flight.start_date)=$year
+
     WHERE personWithProfile.id IS NOT NULL
−
      AND (flight_type.id='$flightTypeId' OR '-'='$flightTypeId')
+
−
      AND person.activated=1
+
−
      AND (person.profile & '$profileId' OR '-'='$profileId')
+
 
) AS tmp_flight
 
) AS tmp_flight
−
GROUP BY id</sql>
+
GROUP BY tmp_flight.activity_type_id</sql>
  
−
==Flight type : Flight hours total on non-instruction with specific profile==
+
==Flight hours total on non-instruction with specific activity type/profile==
  
−
* Variable '''$flightTypeId''' should be defined first and should be of '''dbOject::FlightType''' value type.
+
Requirement:
−
* Variable '''$profileId''' should be defined first and should be of '''dbOject::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 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
−
         flight_type.id,
+
         flightWithActivityType.activity_type_id,
−
         flight_type.name,
+
         flightWithActivityType.activity_type_name,
−
         flight.duration,
+
         flightWithActivityType.duration,
 
         resource_type.category
 
         resource_type.category
−
     FROM flight
+
     FROM (
−
    LEFT JOIN flight_type ON (flight.flight_type_id & flight_type.id)
+
        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=flight.id AND flight_pilot.num=0)
+
        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=flight.aircraft_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 YEAR(flight.start_date)=$year
+
     WHERE personWithProfile.id IS NOT NULL
−
      AND (flight_type.id='$flightTypeId' OR '-'='$flightTypeId')
+
−
      AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1)
+
−
      AND person.activated=1
+
−
      AND (person.profile & '$profileId' OR '-'='$profileId')
+
 
) AS tmp_flight
 
) AS tmp_flight
−
GROUP BY id</sql>
+
GROUP BY tmp_flight.activity_type_id</sql>
  
−
==List of 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</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 Visit
+
) 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 Visit 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