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)
(→‎Flights hours total per aircraft per year and per month (for an activity type))
(61 intermediate revisions by 2 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 162: Line 178:
 
   AND person.activated=1</sql>
 
   AND person.activated=1</sql>
  
−
==Number of movements on based airfield==
+
==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==
 
* 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.
  
Line 191: Line 216:
 
         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 211: Line 236:
 
         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 220: Line 245:
 
     AND personWithProfile.id IS NOT NULL
 
     AND personWithProfile.id IS NOT NULL
 
  ) AS tmp_movement</sql>
 
  ) AS tmp_movement</sql>
−
 
−
==Number of take-off and landings on based airfield==
 
−
 
−
<SQL>SELECT SUM(flight.landing_number) * 2 AS nb_mouvement
 
−
FROM flight, structure
 
−
WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.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 238: Line 257:
 
     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)) AND person.activated = 1
+
     WHERE (profile.id IN($profileId) OR '-' IN($profileId)) 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 )
Line 255: Line 274:
  
 
<sql>SELECT
 
<sql>SELECT
−
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(PILOT)',
+
     CONCAT(person.last_name, ' ', person.first_name) AS _tr(PILOT),
−
     validity_type.name AS '_tr(VALIDITY)'
+
     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 324: 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
 
)
 
)
Line 336: Line 355:
  
 
<sql>SELECT
 
<sql>SELECT
−
     IF( personWithProfile.sex=0, '_tr(SEX_MALE_INITIAL)', IF( personWithProfile.sex=1, '_tr(SEX_FEMALE_INITIAL)', '_tr(UNDEFINED)') ) AS '_tr(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' ) , '_tr(YES)', '_tr(NO)') AS '_tr(ADULT)',
+
     IF( ( $year - YEAR(birthdate) >= $age ) , _tr(YES), _tr(NO)) AS _tr(ADULT),
−
     COUNT(DISTINCT personWithProfile.id) AS '_tr(NUMBER)'  
+
     COUNT(DISTINCT personWithProfile.id) AS _tr(NUMBER)   
 
FROM (
 
FROM (
 
     SELECT person.*
 
     SELECT person.*
Line 433: Line 452:
  
 
<sql>SELECT
 
<sql>SELECT
−
     DATE_FORMAT(start_date, '%d %m %Y' ) AS '_tr(DATE)',
+
     DATE_FORMAT(start_date, '%d %m %Y' ) AS _tr(DATE),
−
     CONCAT(person.last_name, ' ', person.first_name) AS '_tr(FULL_NAME)',
+
     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,
 
     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)',
+
     resource.name AS _tr(RESOURCE),
−
     sexa2HoursMinute(duration) AS '_tr(DURATION)',
+
     sexa2HoursMinute(duration) AS _tr(DURATION),
−
     activity_type.name AS '_tr(ACTIVITY_TYPE)',
+
     activity_type.name AS _tr(ACTIVITY_TYPE),
−
     validity_type.name AS '_tr(VALIDITY)'
+
     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 484: 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 500: 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 569: Line 588:
 
GROUP BY location.icao_name
 
GROUP BY location.icao_name
 
ORDER BY nb_visite DESC</sql>
 
ORDER BY nb_visite DESC</sql>
 +
 +
==Young from this year==
 +
 +
<sql>SELECT
 +
    CONCAT( '[LINK=index.php[QUESTION_MARK]menuAction=admin_add_modify_user&menuParameter=',person.id,'&menuParameterBis=last_name&menuParameter3=', LEFT(person.last_name, 1),']', person.id, '[/LINK]' ) AS _tr(ID),
 +
    person.first_name AS _tr(FIRST_NAME),
 +
    person.last_name AS _tr(LAST_NAME),
 +
    DATE_FORMAT(birthdate ,'%m-%d-%Y') AS _tr(USER_BIRTHDATE),
 +
    IF ( sex = 0, _tr(SEX_MALE), _tr(SEX_FEMALE) ) AS _tr(USER_SEX)
 +
FROM person
 +
WHERE ($year-YEAR(birthdate))<=$age AND activated=1</sql>
  
 
=[[Accounting-exports-4|Accounting]]=
 
=[[Accounting-exports-4|Accounting]]=
Line 581: Line 611:
 
<sql>[OF_DYNAMIC_SQL]
 
<sql>[OF_DYNAMIC_SQL]
 
SELECT CONCAT(
 
SELECT CONCAT(
−
'SELECT booking.id AS \'_tr(ID)\', DATE_FORMAT(booking.start_date, \'%Y-%m-%d %H:%i\') AS \'_tr(START_DATE)\', DATE_FORMAT(booking.end_date, \'%Y-%m-%d %H:%i\') AS \'_tr(END_DATE)\', resource.name AS \'_tr(RESOURCE)\',
+
'SELECT booking.id AS _tr(ID), DATE_FORMAT(booking.start_date, \'%Y-%m-%d %H:%i\') AS _tr(START_DATE), DATE_FORMAT(booking.end_date, \'%Y-%m-%d %H:%i\') AS _tr(END_DATE),
−
       left_booking_person.full_name AS \'_tr(LEFT_PLACE)\', right_booking_person.full_name AS \'_tr(RIGHT_PLACE)\', ',
+
    (
 +
        SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \')
 +
        FROM booking_activity_type
 +
        LEFT JOIN activity_type ON (booking_activity_type.activity_type_id = activity_type.id)
 +
        WHERE booking_activity_type.booking_id=booking.id
 +
    ) AS _tr(ACTIVITY_TYPE),
 +
resource.name AS _tr(RESOURCE),
 +
       left_booking_person.full_name AS _tr(LEFT_PLACE), right_booking_person.full_name AS _tr(RIGHT_PLACE), ',
 
IFNULL(GROUP_CONCAT(
 
IFNULL(GROUP_CONCAT(
 
         CONCAT(
 
         CONCAT(
 
             ' (SELECT business_field_content.content FROM business_field_content WHERE booking.id=business_field_content.category_id AND business_field_content.business_field_id=',
 
             ' (SELECT business_field_content.content FROM business_field_content WHERE booking.id=business_field_content.category_id AND business_field_content.business_field_id=',
 
             business_field.id,
 
             business_field.id,
−
             ') AS \'',
+
             ' LIMIT 1) AS \'',
 
             REPLACE(business_field.label, '\'', '\\\''),
 
             REPLACE(business_field.label, '\'', '\\\''),
 
             '\''
 
             '\''
Line 609: Line 646:
 
       AND person.activated=1
 
       AND person.activated=1
 
) AS right_booking_person ON (booking.id=right_booking_person.booking_id)
 
) AS right_booking_person ON (booking.id=right_booking_person.booking_id)
−
WHERE booking.start_date >= \'$startDate\' AND booking.end_date < \'$endDate\'
+
WHERE booking.start_date >= $startDate AND booking.end_date < $endDate
−
   AND (booking_resource.resource_id=\'$resourceId\' OR \'-\'=\'$resourceId\')
+
   AND (booking_resource.resource_id=$resourceId OR \'\'=$resourceId)
 
   AND (
 
   AND (
−
       left_booking_person.person_id=\'$personId\' OR right_booking_person.person_id=\'$personId\' OR \'-\'=\'$personId\'
+
       left_booking_person.person_id=$personId OR right_booking_person.person_id=$personId OR \'\'=$personId
 
   )
 
   )
 
ORDER BY booking.start_date, booking.end_date'
 
ORDER BY booking.start_date, booking.end_date'
Line 625: Line 662:
 
*endDate (Type : Date or DateTime)
 
*endDate (Type : Date or DateTime)
  
−
<sql>SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours'
+
<sql>SELECT IF(LEAST($startDate + INTERVAL 366 DAY, $endDate) <> $endDate, ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours'
 
UNION ALL
 
UNION ALL
 
SELECT resource.name AS 'Ressource', SUM(TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date)), SUM(TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date))
 
SELECT resource.name AS 'Ressource', SUM(TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date)), SUM(TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date))
Line 639: Line 676:
 
   AND (log.table_name='booking')
 
   AND (log.table_name='booking')
 
   AND (log.field_name='id')
 
   AND (log.field_name='id')
−
   AND (booking.start_date >= '$startDate')
+
   AND (booking.start_date >= $startDate)
−
   AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate'))
+
   AND (booking.start_date <= LEAST($startDate + INTERVAL 366 DAY, $endDate))
 
GROUP BY resource.id
 
GROUP BY resource.id
 
ORDER BY 1</sql>
 
ORDER BY 1</sql>
Line 650: Line 687:
 
*endDate (Type : Date or DateTime)
 
*endDate (Type : Date or DateTime)
  
−
<sql>SELECT IF(LEAST('$startDate' + INTERVAL 366 DAY, '$endDate') <> '$endDate', ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Date debut maintenance', '' AS 'Date fin maintenance', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours'
+
<sql>SELECT IF(LEAST($startDate + INTERVAL 366 DAY, $endDate) <> $endDate, ' Attention la date de fin retenue correspond à la date de début + 366 jours', '') AS 'Ressource', '' AS 'Date debut maintenance', '' AS 'Date fin maintenance', '' AS 'Duree de maintenance en heures', '' AS 'Duree en jours'
 
UNION ALL
 
UNION ALL
 
SELECT resource.name, booking.start_date AS 'Date debut maintenance', booking.end_date, TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date),  TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date)
 
SELECT resource.name, booking.start_date AS 'Date debut maintenance', booking.end_date, TIMESTAMPDIFF(HOUR,booking.start_date, booking.end_date),  TIMESTAMPDIFF(DAY,booking.start_date, booking.end_date)
Line 664: Line 701:
 
   AND (log.TABLE_NAME='booking')
 
   AND (log.TABLE_NAME='booking')
 
   AND (log.field_name='id')
 
   AND (log.field_name='id')
−
   AND (booking.start_date >= '$startDate')
+
   AND (booking.start_date >= $startDate)
−
   AND (booking.start_date <= LEAST('$startDate' + INTERVAL 366 DAY, '$endDate'))
+
   AND (booking.start_date <= LEAST($startDate + INTERVAL 366 DAY, $endDate))
 
ORDER BY 1, 2</sql>
 
ORDER BY 1, 2</sql>
  
Line 696: Line 733:
 
LEFT JOIN resource ON (resource.id=flight.aircraft_id)
 
LEFT JOIN resource ON (resource.id=flight.aircraft_id)
 
LEFT JOIN aircraft ON (aircraft.id=resource.id)
 
LEFT JOIN aircraft ON (aircraft.id=resource.id)
−
WHERE flight.start_date <= '$endDate'
+
WHERE flight.start_date <= $endDate
 
   AND resource.activated=1
 
   AND resource.activated=1
 
GROUP BY resource.id
 
GROUP BY resource.id
Line 749: Line 786:
 
* 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 aircraft_id AS Num, resource.name,  
+
<sql>SELECT aircraft_id AS _tr(IDENT), resource.name AS _tr(RESOURCE_NAME),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS Janu,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS _tr(MONTH_JAN_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS Febr,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS _tr(MONTH_FEB_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS Marc,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS _tr(MONTH_MAR_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS Apri,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS _tr(MONTH_APR_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS May,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS _tr(MONTH_MAY_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS June,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS _tr(MONTH_JUN_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS July,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS _tr(MONTH_JUL_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS Augu,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS _tr(MONTH_AUG_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS Sept,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS _tr(MONTH_SEP_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS Octo,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS _tr(MONTH_OCT_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS Nove,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS _tr(MONTH_NOV_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS Dece,
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS _tr(MONTH_DEC_ABBR),
−
     sexa2HoursMinute( SUM( duration ) ) AS SUM  
+
     sexa2HoursMinute( SUM( duration ) ) AS _tr(SUM)
 
FROM flight
 
FROM flight
 
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
 
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
 
LEFT JOIN resource ON resource.id = aircraft.id
 
LEFT JOIN resource ON resource.id = aircraft.id
 
WHERE YEAR(start_date)= $year AND airborne = 0
 
WHERE YEAR(start_date)= $year AND airborne = 0
−
   AND (flight.activity_type_id & '$activityTypeId' OR '-'='$activityTypeId')
+
   AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId)
−
GROUP BY resource.id
+
GROUP BY resource.id</sql>
−
UNION
+
 
−
SELECT  "Sum per","month",
+
==Flights hours total per aircraft, per year and per month (for a given type of activity and profile)==
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ),
+
 
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ),
+
* Variable '''$activityTypeId''' should be defined first and should be of '''dbOject::ActivityType''' value type.
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ),
+
* Variable '''$year''' should be defined first and should be of '''Year''' value type.
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ),
+
* Variable '''$occupiedSeat''' represent the seat number of the person in the flight, can have following values (0: left place, 1: right place, '': both)
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ),
+
* Variable '''$profileId''' of '''dbOjectMulti::Profile''' value type
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ),
+
<sql>SELECT aircraft_id AS _tr(IDENT), resourceName AS _tr(RESOURCE_NAME), profileName as _tr(PROFILE),
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ),
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=1, duration, 0 ) ) ) AS _tr(MONTH_JAN_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ),
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=2, duration, 0 ) ) ) AS _tr(MONTH_FEB_ABBR),
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ),
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=3, duration, 0 ) ) ) AS _tr(MONTH_MAR_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ),
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=4, duration, 0 ) ) ) AS _tr(MONTH_APR_ABBR),
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ),
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=5, duration, 0 ) ) ) AS _tr(MONTH_MAY_ABBR),
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ),
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=6, duration, 0 ) ) ) AS _tr(MONTH_JUN_ABBR),
−
    sexa2HoursMinute( SUM( duration ) ) AS Total
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=7, duration, 0 ) ) ) AS _tr(MONTH_JUL_ABBR),
−
FROM flight
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=8, duration, 0 ) ) ) AS _tr(MONTH_AUG_ABBR),
−
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=9, duration, 0 ) ) ) AS _tr(MONTH_SEP_ABBR),
−
LEFT JOIN resource ON resource.id = aircraft.id
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=10, duration, 0 ) ) ) AS _tr(MONTH_OCT_ABBR),
−
WHERE YEAR(start_date)= $year AND airborne = 0
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=11, duration, 0 ) ) ) AS _tr(MONTH_NOV_ABBR),
−
  AND (flight.activity_type_id & '$activityTypeId' OR '-'='$activityTypeId')
+
     sexa2HoursMinute( SUM( IF( MONTH(start_date)=12, duration, 0 ) ) ) AS _tr(MONTH_DEC_ABBR),
−
UNION
+
     sexa2HoursMinute( SUM( duration ) ) AS _tr(SUM)
−
SELECT  "Cumulative","per month",  
+
FROM  
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)<=1, duration, 0 ) ) ),
+
(
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=2, duration, 0 ) ) ),
+
SELECT
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)<=3, duration, 0 ) ) ),
+
flight.aircraft_id, resource.name AS resourceName, flight.duration, flight.start_date, profile.name AS profileName
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=4, duration, 0 ) ) ),
+
FROM
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)<=5, duration, 0 ) ) ),
+
flight
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=6, duration, 0 ) ) ),
+
LEFT JOIN flight_pilot ON flight_pilot.flight_id=flight.id
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)<=7, duration, 0 ) ) ),
+
LEFT JOIN person ON person.id=flight_pilot.pilot_id
−
    sexa2HoursMinute( SUM( IF( MONTH(start_date)<=8, duration, 0 ) ) ),
+
LEFT JOIN profile ON person.profile&profile.id
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)<=9, duration, 0 ) ) ),
+
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)<=10, duration, 0 ) ) ),
+
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)<=11, duration, 0 ) ) ),
+
−
     sexa2HoursMinute( SUM( IF( MONTH(start_date)<=12, duration, 0 ) ) ),
+
−
     sexa2HoursMinute( SUM( duration ) ) AS Total
+
−
FROM flight
+
 
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
 
LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id
 
LEFT JOIN resource ON resource.id = aircraft.id
 
LEFT JOIN resource ON resource.id = aircraft.id
 
WHERE YEAR(start_date)= $year AND airborne = 0
 
WHERE YEAR(start_date)= $year AND airborne = 0
−
  AND (flight.activity_type_id & '$activityTypeId' OR '-'='$activityTypeId')</sql>
+
AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId)
 +
AND ( profile.id IN ($profileId) OR '-' IN ($profileId) )
 +
AND (IF((('$occupiedSeat'=0) OR ('$occupiedSeat'='')), 0, -1) = flight_pilot.num
 +
    OR IF((('$occupiedSeat'=1) OR ('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
 +
AND person.activated=1
 +
GROUP BY resourceName, profileName, flight.id
 +
) AS flightTmp
 +
GROUP BY resourceName, profileName</sql>
  
 
==Flight hours total per pilot==