Difference between revisions of "Export generator 4"

Jump to: navigation, search
(→‎Flights with notice in additional entries)
(→‎Booking)
 
(97 intermediate revisions by 4 users not shown)
Line 52: 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==
Line 92: Line 108:
 
         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 99: 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 133: Line 149:
 
         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 141: 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 161: Line 177:
 
   AND expire_date IS NULL
 
   AND expire_date IS NULL
 
   AND person.activated=1</sql>
 
   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, 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
 
     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 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</sql>
 
) AS tmp_movement</sql>
  
Line 199: Line 208:
  
 
<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 structure, flight
−
    LEFT JOIN location ON (flight.departure_location_id = location.id)
+
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
−
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
+
    LEFT JOIN (
−
    LEFT JOIN (
+
        SELECT person.*
−
        SELECT person.*
+
        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
+
    ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
−
    ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
+
    WHERE (
−
    WHERE (
+
        ( flight.departure_icao_id != structure.icao)
−
        ( flight.departure_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
+
        OR
−
        OR
+
        ( flight.arrival_icao_id != structure.icao)
−
        ( flight.arrival_location_id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
+
    )
−
    )
+
    AND ( flight.departure_icao_id != structure.icao)
−
    AND ( location.id != (SELECT ap2.id FROM location ap2, club c WHERE ap2.icao_name = c.icao) )
+
    AND YEAR(flight.start_date) = 2019
−
    AND YEAR(flight.start_date) = $year
+
    AND personWithProfile.id IS NOT NULL
−
    AND personWithProfile.id IS NOT NULL
+
    UNION
−
    UNION
+
SELECT SUM(flight.landing_number)*2 AS movement
−
    SELECT SUM(landing_number)*2 AS movement
+
    FROM structure, flight
−
    FROM 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) )
−
        LIMIT 1
+
          AND person.activated=1
−
    ) AS structure_icao ON (flight.departure_location_id=structure_icao.id AND flight.arrival_location_id=structure_icao.id)
+
        GROUP BY person.id
−
    LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
+
    ) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
−
    LEFT JOIN (
+
    WHERE YEAR(flight.start_date) = $year
−
        SELECT person.*
+
    AND flight.departure_icao_id = structure.icao
−
        FROM person
+
    AND flight.arrival_icao_id = structure.icao
−
        LEFT JOIN profile ON person.profile&profile.id
+
    AND personWithProfile.id IS NOT NULL
−
        WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) )
+
  ) AS tmp_movement</sql>
−
          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 structure_icao.id IS NOT NULL
+
−
    AND personWithProfile.id IS NOT NULL
+
−
) AS tmp_movement</sql>
+
−
 
+
−
==Visited airfields==
+
−
 
+
−
<sql>SELECT location.icao_name AS ICAO, location.name AS Name, COUNT(location.icao_name) AS Visit
+
−
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 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, structure c
+
−
WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao))
+
−
  AND (f.arrival_location_id = (SELECT ap2.id FROM location ap2, structure 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==
Line 271: Line 251:
 
* 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 (
+
LEFT JOIN flight_pilot ON ( flight_pilot.flight_id = flight.id )
−
    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)
+
−
LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id)
+
 
LEFT JOIN (
 
LEFT JOIN (
 
     SELECT person.*
 
     SELECT person.*
 
     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
−
) AS personWithProfile ON (personWithProfile.id=flight_pilot.pilot_id AND flight_pilot.num=0)
+
) AS personWithProfile ON ( personWithProfile.id = flight_pilot.pilot_id AND flight_pilot.num = 0 )
−
WHERE personWithProfile.id IS NOT NULL
+
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>
−
  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 ap.icao_name AS ICAO, ap.name AS Name, COUNT( ap.icao_name ) AS nb_flight
+
<sql>SELECT location.icao_name AS ICAO, location.name AS NAME, COUNT(location.icao_name) AS nb_flight
−
FROM flight f
+
FROM structure, flight
−
LEFT JOIN location AS ap ON f.departure_location_id = ap.id
+
LEFT JOIN location ON flight.departure_icao_id = location.icao_name
−
WHERE ( f.departure_location_id = (SELECT ap2.id FROM location ap2, structure 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
−
  OR (f.arrival_location_id  = (SELECT ap2.id FROM location ap2, structure c WHERE ap2.icao_name = c.icao))
+
−
  AND departure_location_id != arrival_location_id
+
−
  AND YEAR( start_date ) = $year
+
 
GROUP BY icao_name
 
GROUP BY icao_name
−
ORDER BY nb_flight DESC</sql>
+
ORDER BY nb_flight DESC </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.activity_type_id
 
LEFT JOIN activity_type ON activity_type.id = flight_type_mandatory_validity_type.activity_type_id
Line 316: Line 285:
 
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 373: Line 343:
 
     LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id
 
     LEFT JOIN flight_pilot AS fp ON fp.flight_id=f.id
 
     LEFT JOIN person AS person2 ON person2.id=fp.pilot_id
 
     LEFT JOIN person AS person2 ON person2.id=fp.pilot_id
−
     WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL IF('$numberMonth'='', 1, '$numberMonth'*30) DAY AND NOW()
+
     WHERE f.airborne = 0 AND f.start_date BETWEEN NOW() - INTERVAL IF($numberMonth='', 1, $numberMonth*30) DAY AND NOW()
 
     GROUP BY person2.id HAVING SUM(f.duration)/600 > 0
 
     GROUP BY person2.id HAVING SUM(f.duration)/600 > 0
 
)
 
)
 
ORDER BY person.last_name, person.first_name</sql>
 
ORDER BY person.last_name, person.first_name</sql>
  
−
==Number of men, women over and under X years for profile P, validity V up to date for year A==
+
==Total user per age profile validity: Number of men, women over and under X years for profile P, validity V up to date for year A==
 
* Variable '''$age''' should be of '''integer''' value type.
 
* Variable '''$age''' should be of '''integer''' value type.
 
* Variable '''$profileId''' should be of '''dbOjectMulti::Profile''' value type.
 
* Variable '''$profileId''' should be of '''dbOjectMulti::Profile''' value type.
Line 385: Line 355:
  
 
<sql>SELECT
 
<sql>SELECT
−
     IF( personWithProfile.sex=0, 'Male', IF( personWithProfile.sex=1, 'Female', 'Undefined') ) AS 'Sex',
+
     IF( personWithProfile.sex=0, _tr(SEX_MALE_INITIAL), IF( personWithProfile.sex=1, _tr(SEX_FEMALE_INITIAL), _tr(UNDEFINED)) ) AS _tr(SEX),
−
     IF( ( $year - YEAR(birthdate) >= '$age' ) , 'Yes', 'No') AS 'Adult',
+
     IF( ( $year - YEAR(birthdate) >= $age ) , _tr(YES), _tr(NO)) AS _tr(ADULT),
−
     COUNT(DISTINCT personWithProfile.id) AS NUMBER   
+
     COUNT(DISTINCT personWithProfile.id) AS _tr(NUMBER)  
 
FROM (
 
FROM (
 
     SELECT person.*
 
     SELECT person.*
Line 395: Line 365:
 
       AND person.activated=1
 
       AND person.activated=1
 
) AS personWithProfile
 
) AS personWithProfile
−
LEFT JOIN validity ON (personWithProfile.id=validity.person_id)
+
LEFT JOIN validity ON personWithProfile.id = validity.person_id AND validity.is_current_validity = 1
 
LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
 
LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
−
WHERE (validity_type.id IN ($validityTypeId) AND validity.expire_date >= '$year-12-31')
+
WHERE ((validity_type.id IN ($validityTypeId) AND validity.expire_date >= '$year-12-31') OR '-' IN ($validityTypeId))
−
      OR '-' IN ($validityTypeId)
+
  AND validity_type.activated = 1
−
GROUP BY Sex, Adult</sql>
+
GROUP BY `_tr(SEX)`, `_tr(ADULT)`</sql>
  
 
==Number of landings per pilot, per resource==
 
==Number of landings per pilot, per resource==
Line 478: Line 448:
  
 
==Flight hours without up to date validities==
 
==Flight hours without up to date validities==
−
<sql>SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS '_tr(DATE)', last_name AS '_tr(LAST_NAME)', first_name AS '_tr(FIRST_NAME)',  
+
 
−
      IF ( (SELECT COUNT(*) FROM flight_pilot AS tmp_flight_pilot WHERE flight_pilot.flight_id=tmp_flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'Solo', 'DC' ) AS DC,
+
*Variable '''$year'''
−
      resource.name AS '_tr(RESOURCE)',
+
 
−
      sexa2HoursMinute(duration) AS '_tr(DURATION)',
+
<sql>SELECT
−
      activity_type.name AS '_tr(ACTIVITY_TYPE)',
+
    DATE_FORMAT(start_date, '%d %m %Y' ) AS _tr(DATE),
−
      validity_type.name AS '_tr(VALIDITY)'
+
    CONCAT(person.last_name, ' ', person.first_name) AS _tr(FULL_NAME),
 +
    IF ( (SELECT COUNT(*) FROM flight_pilot AS tmp_flight_pilot WHERE flight_pilot.flight_id=tmp_flight_pilot.flight_id AND tmp_flight_pilot.num=1)=0, 'Solo', 'DC' ) AS DC,
 +
    resource.name AS _tr(RESOURCE),
 +
    sexa2HoursMinute(duration) AS _tr(DURATION),
 +
    activity_type.name AS _tr(ACTIVITY_TYPE),
 +
    validity_type.name AS _tr(VALIDITY)
 
FROM flight
 
FROM flight
 
LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.activity_type_id & flight.activity_type_id  
 
LEFT JOIN flight_type_mandatory_validity_type ON flight_type_mandatory_validity_type.activity_type_id & flight.activity_type_id  
Line 489: Line 464:
 
LEFT JOIN person ON flight_pilot.pilot_id = person.id
 
LEFT JOIN person ON flight_pilot.pilot_id = person.id
 
LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id
 
LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id
−
LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id
+
LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id AND validity.is_current_validity = 1
 
LEFT JOIN resource ON flight.aircraft_id = resource.id
 
LEFT JOIN resource ON flight.aircraft_id = resource.id
 
LEFT JOIN activity_type ON flight_type_mandatory_validity_type.activity_type_id = activity_type.id
 
LEFT JOIN activity_type ON flight_type_mandatory_validity_type.activity_type_id = activity_type.id
Line 497: Line 472:
 
   AND validity.person_id = flight_pilot.pilot_id
 
   AND validity.person_id = flight_pilot.pilot_id
 
   AND flight.start_date > validity.expire_date
 
   AND flight.start_date > validity.expire_date
−
   AND person.activated=1
+
   AND person.activated = 1
−
ORDER BY flight.start_date, person.last_name, validity_type.name</sql>
+
  AND validity_type.activated = 1
 +
ORDER BY flight.start_date, `_tr(FULL_NAME)`, validity_type.name</sql>
  
 
==Number of men, women over and under 21 years for profile X, validity Y up to date for year Z==
 
==Number of men, women over and under 21 years for profile X, validity Y up to date for year Z==
Line 527: Line 503:
  
 
<sql>SELECT tmp_flight.activity_type_name AS name,
 
<sql>SELECT tmp_flight.activity_type_name AS name,
−
       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS '_tr(AIRCRAFT_CATEGORY)',
+
       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS _tr(AIRCRAFT_CATEGORY),
−
       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS '_tr(HELICOPTER_CATEGORY)',
+
       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS _tr(HELICOPTER_CATEGORY),
−
       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS '_tr(GLIDER_CATEGORY)',
+
       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS _tr(GLIDER_CATEGORY),
−
       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS '_tr(ULTRA_LIGHT_CATEGORY)',
+
       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS _tr(ULTRA_LIGHT_CATEGORY),
−
       sexa2HoursMinute(SUM(IF(category=9, duration, 0))) AS '_tr(CLASSROOM_CATEGORY)'
+
       sexa2HoursMinute(SUM(IF(category=9, duration, 0))) AS _tr(CLASSROOM_CATEGORY)
 
FROM (
 
FROM (
 
     SELECT
 
     SELECT
Line 543: Line 519:
 
         LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
 
         LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
 
         LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1)
 
         LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1)
−
         WHERE YEAR(flight.start_date)='$year'
+
         WHERE YEAR(flight.start_date)=$year
−
           AND flight.airborne=0 AND flight_pilot.pilot_id='$personId'
+
           AND flight.airborne=0 AND flight_pilot.pilot_id=$personId
 
     ) AS flightWithActivityType
 
     ) AS flightWithActivityType
 
     LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
 
     LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
Line 551: Line 527:
 
GROUP BY tmp_flight.activity_type_id</sql>
 
GROUP BY tmp_flight.activity_type_id</sql>
  
−
==Validities in the year : Total of youngs/adults with specific profile==
+