Difference between revisions of "Export generator 4"

Jump to: navigation, search
(→‎Flight hours total per person)
(→‎Flights hours total per aircraft per year and per month (for an activity type))
(30 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 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 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 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 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 573: Line 592:
  
 
<sql>SELECT
 
<sql>SELECT
−
     CONCAT( '[LINK=index.php&#63;menuAction=admin_add_modify_user&menuParameter=',person.id,'&menuParameterBis=last_name&menuParameter3=', LEFT(person.last_name, 1),']', person.id, '[/LINK]' ) AS '_tr(ID)',
+
     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.first_name AS _tr(FIRST_NAME),
−
     person.last_name AS '_tr(LAST_NAME)',
+
     person.last_name AS _tr(LAST_NAME),
−
     DATE_FORMAT(birthdate ,'%m-%d-%Y') AS '_tr(USER_BIRTHDATE)',  
+
     DATE_FORMAT(birthdate ,'%m-%d-%Y') AS _tr(USER_BIRTHDATE),  
−
     IF ( sex = 0, '_tr(SEX_MALE)', '_tr(SEX_FEMALE)' ) AS '_tr(USER_SEX)'
+
     IF ( sex = 0, _tr(SEX_MALE), _tr(SEX_FEMALE) ) AS _tr(USER_SEX)
 
FROM person
 
FROM person
 
WHERE ($year-YEAR(birthdate))<=$age AND activated=1</sql>
 
WHERE ($year-YEAR(birthdate))<=$age AND activated=1</sql>
Line 592: 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(
Line 620: 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 636: 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 650: 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 661: 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 675: 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 707: 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 760: 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==
 
==Flight hours total per pilot==
Line 890: Line 917:
 
This report is useful for a group of pilots from a common customer (like DGAC in France)
 
This report is useful for a group of pilots from a common customer (like DGAC in France)
  
−
<sql>SELECT CONCAT(person.last_name,' ',person.first_name) AS '_tr(FULL_NAME)',
+
<sql>SELECT CONCAT(person.last_name,' ',person.first_name) AS _tr(FULL_NAME),
−
CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS '_tr(TOTAL_FLIGHT_TIME)',
+
CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS _tr(TOTAL_FLIGHT_TIME),
−
SUM(account_entry.debit) - SUM(account_entry.credit) AS '_tr(TOTAL_AMOUNT_ACTIVITIES)'
+
SUM(account_entry.debit) - SUM(account_entry.credit) AS _tr(TOTAL_AMOUNT_ACTIVITIES)  
 
FROM flight
 
FROM flight
 
RIGHT JOIN flight_pilot ON flight_pilot.flight_id=flight.id
 
RIGHT JOIN flight_pilot ON flight_pilot.flight_id=flight.id
−
     AND (IF((('$occupiedSeat'=0) OR ('$occupiedSeat'='')), 0, -1) = flight_pilot.num
+
     AND (IF((($occupiedSeat=0) OR ($occupiedSeat='')), 0, -1) = flight_pilot.num
−
     OR  IF((('$occupiedSeat'=1) OR ('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
+
     OR  IF((($occupiedSeat=1) OR ($occupiedSeat='')), 1, -1) = flight_pilot.num)
 
RIGHT JOIN person ON person.id=flight_pilot.pilot_id
 
RIGHT JOIN person ON person.id=flight_pilot.pilot_id
 
RIGHT JOIN profile ON profile.id&person.profile
 
RIGHT JOIN profile ON profile.id&person.profile
Line 903: Line 930:
 
LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
 
LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
 
LEFT JOIN account ON account.id=account_entry.account_id
 
LEFT JOIN account ON account.id=account_entry.account_id
−
WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate' AND (account.category = 11 OR account.category = 2)
+
WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate AND (account.category = 11 OR account.category = 2)
 
GROUP BY person.id</sql>
 
GROUP BY person.id</sql>
  
Line 911: Line 938:
 
*activityTypeId (Type : dbObject::FlightType)
 
*activityTypeId (Type : dbObject::FlightType)
 
<sql>SELECT  
 
<sql>SELECT  
−
     CONCAT(UPPER(person.last_name), ' ', person.first_name) AS '_tr(LAST_NAME)',
+
     CONCAT(UPPER(person.last_name), ' ', person.first_name) AS _tr(LAST_NAME),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_JAN)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JANUARY),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_FEB)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_FEBRUARY),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_MAR)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_MARCH),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_APR)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_APRIL),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_MAY)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_MAY),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_JUN)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JUN),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_JUL)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_JULY),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_AUG)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_AUGUST),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_SEP)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_SEPTEMBER),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_OCT)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_OCTOBER),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_NOV)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(MONTH_NOVEMBER),
−
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & '$activityTypeId' != 0 OR ''='$activityTypeId' ) ) AS '_tr(MONTH_DEC)',
+
     (SELECT sexa2HoursMinute( SUM( duration ) ) FROM flight INNER JOIN flight_pilot ON flight_pilot.flight_id = flight.id WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND flight_pilot.pilot_id = person.id AND flight_pilot.num = 1 AND ( flight.activity_type_id & $activityTypeId != 0 OR ''=$activityTypeId ) ) AS _tr(