Difference between revisions of "Export generator 4"

Jump to: navigation, search
(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.
  
==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 51: Line 54:
  
 
==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 70:
 
==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 87:
 
         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 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 '-' 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 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''' 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 128:
 
         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 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 '-' 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 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 '''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 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
    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==
 
==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 f, club c
+
FROM flight, structure
WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao))
+
WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year </SQL>
  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.flight_type_id & activity_type.id
+
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 (validity_type.id=validity.validity_type_id AND person.id=validity.person_id)
+
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 pilot, validity_type.name</sql>
+
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 3 months==
+
==Pilots without flight in the last X months==
  
* Variable '''$month''' should be defined first and should be of '''integer''' value type.  
+
* Variable '''$numberMonth''' should be defined first and should be of '''integer''' value type.