Difference between revisions of "Export generator 4"

Jump to: navigation, search
(→‎Detailed flight between two dates)
(→‎Flight between two dates)
Line 1,305: Line 1,305:
 
GROUP BY flight.id</sql>
 
GROUP BY flight.id</sql>
  
−
==Flight between two dates==
+
==Flight between date==
 
Following variables are needed:
 
Following variables are needed:
 
*startDate (Type : Datetime)
 
*startDate (Type : Datetime)
 
*endDate (Type : Datetime)
 
*endDate (Type : Datetime)
  
−
<sql>SELECT flight.start_date AS '_tr(START_DATE)', resource.name AS '_tr(RESOURCE)',  
+
<sql>SELECT
−
CONCAT(UPPER(person.last_name),' ',person.first_name) AS '_tr(LEFT_PLACE)',
+
    virtual_flight.start_date AS '_tr(START_DATE)',
−
     (  
+
    virtual_flight.resource_name AS '_tr(RESOURCE)',
−
        SELECT CONCAT(UPPER(tmp_person.last_name), ' ', tmp_person.first_name)
+
    virtual_flight.left_place AS '_tr(LEFT_PLACE)',
−
        FROM flight_pilot AS tmp_flight_pilot
+
    virtual_flight.right_place AS '_tr(RIGHT_PLACE)',
−
        LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id
+
    virtual_flight.activity_list AS '_tr(ACTIVITY_TYPE)',
−
        WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 1
+
    virtual_flight.departure AS '_tr(DEPARTURE)',
−
    ) AS '_tr(RIGHT_PLACE)',
+
    virtual_flight.arrival AS '_tr(ARRIVAL)',
−
    (
+
    virtual_flight.landing_number AS '_tr(FLIGHT_LANDING_NUMBER)',
−
        SELECT GROUP_CONCAT( activity_type.name SEPARATOR ', ')
+
     sexa2HoursMinute(virtual_flight.duration) AS '_tr(DURATION)',
−
        FROM flight AS tmp_flight
+
    virtual_flight.comments AS '_tr(COMMENTS)',
−
        LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
+
    virtual_flight.amount AS '_tr(AMOUNT)'
−
        WHERE flight.id=tmp_flight.id
+
FROM
−
    ) AS '_tr(ACTIVITY_TYPE)',
+
(SELECT
−
    (
+
    flight.id,
−
        SELECT location.name
+
 
−
        FROM location
+
    flight.start_date,
−
        WHERE location.id=flight.departure_location_id
+
 
−
    ) AS '_tr(DEPARTURE)',
+
    resource.name AS resource_name,
−
    (
+
 
−
        SELECT location.name
+
    (  
−
        FROM location
+
        SELECT CONCAT(UPPER(tmp_person.last_name), ' ', tmp_person.first_name)
−
        WHERE location.id=flight.arrival_location_id
+
        FROM flight_pilot AS tmp_flight_pilot
−
    ) AS '_tr(ARRIVAL)',
+
        LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id
−
    flight.landing_number AS '_tr(FLIGHT_LANDING_NUMBER)',
+
        WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 0
−
sexa2HoursMinute(flight.duration) AS '_tr(DURATION)'
+
    ) AS left_place,
 +
 
 +
    (  
 +
        SELECT CONCAT(UPPER(tmp_person.last_name), ' ', tmp_person.first_name)
 +
        FROM flight_pilot AS tmp_flight_pilot
 +
        LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id
 +
        WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 1
 +
    ) AS right_place,
 +
 
 +
    (
 +
        SELECT GROUP_CONCAT( activity_type.name SEPARATOR ', ')
 +
        FROM flight AS tmp_flight
 +
        LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
 +
        WHERE flight.id=tmp_flight.id
 +
    ) AS activity_list,
 +
 
 +
    (
 +
        SELECT location.name
 +
        FROM location
 +
        WHERE location.id=flight.departure_location_id
 +
    ) AS departure,
 +
 
 +
    (
 +
        SELECT location.name
 +
        FROM location
 +
        WHERE location.id=flight.arrival_location_id
 +
    ) AS arrival,
 +
 
 +
    flight.landing_number AS landing_number,
 +
 
 +
    flight.duration AS duration,
 +
 
 +
    (
 +
        SELECT GROUP_CONCAT(business_field_content.content SEPARATOR ', ')
 +
        FROM business_field_content
 +
        RIGHT JOIN business_field ON (business_field.id=business_field_content.business_field_id AND business_field.variable='activityComment')
 +
        WHERE business_field_content.category_id=flight.id
 +
    ) AS comments,
 +
   
 +
    (
 +
        SELECT SUM(account_entry.debit) - SUM(account_entry.credit)
 +
        FROM account_entry
 +
        RIGHT JOIN account ON (account.id=account_entry.account_id AND (account.category = 11 OR account.category = 2))
 +
        WHERE account_entry.flow_id = flight_account_entry.account_entry_id
 +
    )  AS amount
 +
 
 
FROM flight
 
FROM flight
 
LEFT JOIN resource ON resource.id = flight.aircraft_id
 
LEFT JOIN resource ON resource.id = flight.aircraft_id
−
LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
+
LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
−
LEFT JOIN person ON flight_pilot.pilot_id = person.id
+
WHERE flight.start_date >= '$startDate' AND flight.start_date <= '$endDate'
−
WHERE flight.start_date >= '$startDate' AND flight.start_date <= '$endDate' AND flight_pilot.num = 0</sql>
+
)
 +
AS virtual_flight
 +
ORDER BY virtual_flight.start_date;</sql>
  
 
==Flight log book==
 
==Flight log book==

Revision as of 18:18, 6 March 2018

Contents

Introduction

The goal of this page is to propose a list of statistic generation queries (SQL).

Do not forget to read the OpenFlyers SQL stored functions and procedures page.

Please note that you can make "public" a report, ie. allows standard users to see and interact with it.

Business field

For more flexibility, business field can be defined by the user.

Two business field types are available:

  • "external parameters" of following types:
    • Date
    • Date and time
    • Month
    • Text string
    • Time
    • Whole number
    • Year
  • database parameters (dbObject::something or dbObjectMulti::something Value type) advanced options to access to the database field

Business field creation

  • Go to Menu Admin > Reports > Custom reports > Business Field(s)

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 label field with a name used to describe the parameter as you want it to appear within the export query form.
  • Choose a Value type (the most common are at the beginning of the list).
  • Click on "Add"

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::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 business field for the current year:

  • Name: $year
  • Label: Year
  • Value type: Year

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 business field to list the reservation of a resource:

  • Name: $resourceId
  • Label: Resource
  • Value type: dbObject::Resource

Then in Admin/Reports we create a new query labeled "Aircraft booking" with the following query: 

SELECT * FROM booking WHERE booking.resource_id=$resourceId

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

Return only last entry

Example with last entry from variable #1 in variable_value table: 

SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC  LIMIT 1

Test valid entries

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) : 

SELECT *
FROM flight_pilot
WHERE 
(
    IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
    OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num
)

French administration Examples

Flight hours total on instruction with specific activity type/profile

Requirement:

  • Variable $activityTypeId of dbOjectMulti::ActivityType value type
  • Variable $profileId of dbOjectMulti::Profile value type
  • Variable $year of Year value type
SELECT tmp_flight.activity_type_name AS name,
       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion',
       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère',
       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur',
       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM'
FROM (
    SELECT
        flightWithActivityType.activity_type_id,
        flightWithActivityType.activity_type_name,
        flightWithActivityType.duration,
        resource_type.category
    FROM (
        SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
        FROM flight
        LEFT JOIN activity_type ON flight.activity_type_id&activity_type.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)
    WHERE personWithProfile.id IS NOT NULL
) AS tmp_flight
GROUP BY tmp_flight.activity_type_id

Flight hours total on non-instruction with specific activity type/profile

Requirement:

  • Variable $activityTypeId of dbOjectMulti::ActivityType value type
  • Variable $profileId of dbOjectMulti::Profile value type
  • Variable $year of Year value type
SELECT tmp_flight.activity_type_name AS name,
       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS 'Avion',
       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS 'Hélicoptère',
       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS 'Planeur',
       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS 'ULM'
FROM (
    SELECT
        flightWithActivityType.activity_type_id,
        flightWithActivityType.activity_type_name,
        flightWithActivityType.duration,
        resource_type.category
    FROM (
        SELECT flight.id, flight.duration, flight.aircraft_id, activity_type.id AS activity_type_id, activity_type.name AS activity_type_name
        FROM flight
        LEFT JOIN activity_type ON flight.activity_type_id&activity_type.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)
    WHERE personWithProfile.id IS NOT NULL
) AS tmp_flight
GROUP BY tmp_flight.activity_type_id

Users with a specific validity without expiration date

  • Variable $validityId should be defined first and should be of dbOjectMulti::ValidityType value type. 
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
WHERE ( validity_type_id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
  AND expire_date IS NULL
  AND person.activated=1

Number of movements on based airfield

  • Variable $year should be defined first and should be of Year value type.
SELECT SUM(movement) AS 'Movement'
FROM
(
    SELECT COUNT(*) AS movement
    FROM flight
    LEFT JOIN location ON (flight.departure_location_id = location.id)
    WHERE (
            ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao) )
            OR
            ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao) )
        )
        AND ( location.id != (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao) )
        AND YEAR( start_date ) = $year
    UNION
    SELECT SUM(landing_number)*2 AS movement
    FROM flight
    LEFT JOIN (
        SELECT location.id
        FROM location
        LEFT JOIN structure ON location.icao_name=structure.icao
        WHERE structure.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

Number of movements on based airfield on specific profile

  • 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.
SELECT SUM(movement) AS 'Movement'
FROM (
    SELECT COUNT(*) AS movement
    FROM flight
    LEFT JOIN location ON (flight.departure_location_id = location.id)
    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_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(flight.start_date) = $year
    AND personWithProfile.id IS NOT NULL
    UNION
    SELECT SUM(landing_number)*2 AS movement
    FROM flight
    LEFT JOIN (
        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)
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
    LEFT JOIN (
        SELECT person.*
        FROM person
        LEFT JOIN profile ON person.profile&profile.