Difference between revisions of "Export generator 4"

Jump to: navigation, search
(→‎Number of movements on based airfield on specific profile)
(→‎Booking)
(44 intermediate revisions by 2 users not shown)
Line 92: Line 92:
 
         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 133:
 
         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 238: Line 238:
 
     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 255:
  
 
<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 324:
 
     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 336:
  
 
<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 433:
  
 
<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 484:
  
 
<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 500:
 
         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 569:
 
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 592:
 
<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 609: Line 627:
 
       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 643:
 
*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 657:
 
   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 668:
 
*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 682:
 
   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 714:
 
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 767: Line 785:
 
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
 
UNION  
 
UNION  
Line 788: Line 806:
 
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)
 
UNION  
 
UNION  
 
SELECT  "Cumulative","per month",  
 
SELECT  "Cumulative","per month",  
Line 808: Line 826:
 
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)</sql>
  
 
==Flight hours total per pilot==
 
==Flight hours total per pilot==
Line 879: Line 897:
 
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 892: Line 910:
 
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 900: Line 918:
 
*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(MONTH_DECEMBER),
−
     sexa2HoursMinute( SUM( duration ) ) AS '_tr(TOTAL)'
+
     sexa2HoursMinute( SUM( duration ) ) AS _tr(TOTAL)
 
FROM flight  
 
FROM flight  
 
LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id  
 
LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id  
Line 921: Line 939:
 
   AND fp.num = 1  
 
   AND fp.num = 1  
 
   AND YEAR( start_date ) = $year   
 
   AND YEAR( start_date ) = $year   
−
   AND ( flight.activity_type_id & '$activityTypeId' OR '-'='$activityTypeId' )
+
   AND ( flight.activity_type_id & $activityTypeId OR ''=$activityTypeId )
 
GROUP BY person.id</sql>
 
GROUP BY person.id</sql>
  
Line 1,157: Line 1,175:
 
<sql>[OF_DYNAMIC_SQL]
 
<sql>[OF_DYNAMIC_SQL]
 
SELECT CONCAT(
 
SELECT CONCAT(
−
     'SELECT resource.name AS \'_tr(RESOURCE_NAME)\', ',
+
     'SELECT resource.name AS _tr(RESOURCE_NAME), ',
 
     GROUP_CONCAT(
 
     GROUP_CONCAT(
 
         CONCAT(
 
         CONCAT(
−
             'sexa2HoursMinute( SUM( IF( tmp_flight.activity_type_id=',
+
             'sexa2HoursMinute( SUM( IF( tmp_flight.activity_type_id & ',
 
             activity_type.id,
 
             activity_type.id,
−
             ', tmp_flight.total_duration, 0 ) ) )',
+
             ', tmp_flight.duration, 0 ) ) )',
−
             ' AS \'',
+
             ' AS ',
−
             activity_type.name,
+
             QUOTE(activity_type.name)
−
            '\''
+
 
         )
 
         )
 
     ),
 
     ),
−
     ', sexa2HoursMinute( SUM( tmp_flight.total_duration ) ) AS \'_tr(TOTAL)\'
+
     ', sexa2HoursMinute( SUM( tmp_flight.duration ) ) AS _tr(ALL_ACTIVITY_TYPES)
 
     FROM resource
 
     FROM resource
−
     LEFT JOIN
+
     INNER JOIN
 
     (
 
     (
−
         SELECT flight.aircraft_id, activity_type.id AS activity_type_id, SUM(duration) as total_duration
+
         SELECT flight.aircraft_id, flight.activity_type_id, duration
 
         FROM flight
 
         FROM flight
−
        LEFT JOIN activity_type ON (flight.activity_type_id & activity_type.id)
+
         WHERE flight.start_date >= $startDate AND flight.start_date <= $endDate AND flight.airborne = 0
−
         WHERE flight.start_date >= \'$startDate\' AND flight.start_date <= \'$endDate\' AND flight.airborne = 0
+
−
        GROUP BY flight.aircraft_id, activity_type.id
+
 
     ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
 
     ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
−
    WHERE tmp_flight.aircraft_id IS NOT NULL
 
 
     GROUP BY resource.id'
 
     GROUP BY resource.id'
 
)
 
)
Line 1,193: Line 1,207:
 
* Variable '''$personId''' of '''dbOjectMulti::Person''' value type
 
* Variable '''$personId''' of '''dbOjectMulti::Person''' value type
  
−
<sql>SELECT '$month' AS Mois, CONCAT(left_person.last_name, ' ', left_person.first_name) AS 'Personne en première place', activity_type.name AS 'Type d\'activité', sexa2HoursMinute( SUM( IFNULL(duration, 0) ) ) AS 'Durée'
+
<sql>SELECT $month AS Mois, CONCAT(left_person.last_name, ' ', left_person.first_name) AS 'Personne en première place', activity_type.name AS 'Type d\'activité', sexa2HoursMinute( SUM( IFNULL(duration, 0) ) ) AS 'Durée'
 
FROM flight
 
FROM flight
 
LEFT JOIN activity_type ON (flight.activity_type_id & activity_type.id)
 
LEFT JOIN activity_type ON (flight.activity_type_id & activity_type.id)
Line 1,199: Line 1,213:
 
LEFT JOIN person AS left_person ON (left_place.pilot_id=left_person.id)
 
LEFT JOIN person AS left_person ON (left_place.pilot_id=left_person.id)
 
LEFT JOIN flight_pilot AS right_place ON (flight.id=right_place.flight_id AND right_place.num=1)
 
LEFT JOIN flight_pilot AS right_place ON (flight.id=right_place.flight_id AND right_place.num=1)
−
WHERE YEAR(flight.start_date)='$year'
+
WHERE YEAR(flight.start_date)=$year
−
   AND MONTH(flight.start_date)='$month'
+
   AND MONTH(flight.start_date)=$month
−
   AND (right_place.pilot_id='$personId' OR '-'=right_place.pilot_id='$personId')
+
   AND (right_place.pilot_id=$personId OR '-'=right_place.pilot_id='$personId')
 
GROUP BY left_person.id, activity_type.id
 
GROUP BY left_person.id, activity_type.id
 
ORDER BY left_person.last_name, left_person.first_name, activity_type.name</sql>
 
ORDER BY left_person.last_name, left_person.first_name, activity_type.name</sql>
Line 1,221: Line 1,235:
 
SELECT CONCAT(
 
SELECT CONCAT(
 
'SELECT
 
'SELECT
−
     flight.id AS \'_tr(ID)\',
+
     flight.id AS _tr(ID),
−
     IF(flight.validated=1, \'_tr(YES)\', \'_tr(NO)\') AS \'_tr(VALIDATED)\',
+
     IF(flight.validated=1, _tr(YES), _tr(NO)) AS _tr(VALIDATED),
−
     flight.start_date AS \'_tr(START_DATE)\',
+
     flight.start_date AS _tr(START_DATE),
−
     resource.name AS \'_tr(RESOURCE)\',
+
     resource.name AS _tr(RESOURCE),
 
     (
 
     (
 
         SELECT CONCAT(UPPER(tmp_person.last_name), \' \', tmp_person.first_name)
 
         SELECT CONCAT(UPPER(tmp_person.last_name), \' \', tmp_person.first_name)
Line 1,230: Line 1,244:
 
         LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id
 
         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 = 0
 
         WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 0
−
     ) AS \'_tr(LEFT_PLACE)\',
+
     ) AS _tr(LEFT_PLACE),
 
     (
 
     (
 
         SELECT CONCAT(UPPER(tmp_person.last_name), \' \', tmp_person.first_name)
 
         SELECT CONCAT(UPPER(tmp_person.last_name), \' \', tmp_person.first_name)
Line 1,236: Line 1,250:
 
         LEFT JOIN person AS tmp_person ON tmp_flight_pilot.pilot_id = tmp_person.id