Difference between revisions of "Export generator 4"

Jump to: navigation, search
(→‎Flights with mechanic remark)
(→‎Flights hours total per aircraft per year and per month (for an activity type))
(23 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 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 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
Line 779: Line 805:
 
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 1,168: Line 1,195:
 
<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(
Line 1,178: Line 1,205:
 
         )
 
         )
 
     ),
 
     ),
−
     ', sexa2HoursMinute( SUM( tmp_flight.duration ) ) AS \'_tr(ALL_ACTIVITY_TYPES)\'
+
     ', sexa2HoursMinute( SUM( tmp_flight.duration ) ) AS _tr(ALL_ACTIVITY_TYPES)
 
     FROM resource
 
     FROM resource
 
     INNER JOIN
 
     INNER JOIN
Line 1,184: Line 1,211:
 
         SELECT flight.aircraft_id, flight.activity_type_id, duration
 
         SELECT flight.aircraft_id, flight.activity_type_id, duration
 
         FROM flight
 
         FROM flight
−
         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
 
     ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
 
     ) AS tmp_flight ON (resource.id=tmp_flight.aircraft_id)
 
     GROUP BY resource.id'
 
     GROUP BY resource.id'
Line 1,228: Line 1,255:
 
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,237: Line 1,264:
 
         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,243: Line 1,270:
 
         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 = 1
 
         WHERE tmp_flight_pilot.flight_id = flight.id AND tmp_flight_pilot.num = 1
−
     ) AS \'_tr(RIGHT_PLACE)\',
+
     ) AS _tr(RIGHT_PLACE),
 
     (
 
     (
 
         SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \')
 
         SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \')
Line 1,249: Line 1,276:
 
         LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
 
         LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
 
         WHERE flight.id=tmp_flight.id
 
         WHERE flight.id=tmp_flight.id
−
     ) AS \'_tr(ACTIVITY_TYPE)\',
+
     ) AS _tr(ACTIVITY_TYPE),
 
     (
 
     (
 
         SELECT location.name
 
         SELECT location.name
 
         FROM location
 
         FROM location
 
         WHERE location.icao_name=flight.departure_icao_id
 
         WHERE location.icao_name=flight.departure_icao_id
−
     ) AS \'_tr(DEPARTURE)\',
+
     ) AS _tr(DEPARTURE),
 
     (
 
     (
 
         SELECT location.name
 
         SELECT location.name
 
         FROM location
 
         FROM location
 
         WHERE location.icao_name=flight.arrival_icao_id
 
         WHERE location.icao_name=flight.arrival_icao_id
−
     ) AS \'_tr(ARRIVAL)\',
+
     ) AS _tr(ARRIVAL),
−
     flight.landing_number AS \'_tr(FLIGHT_LANDING_NUMBER)\',
+
     flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER),
−
     flight.people_onboard AS \'_tr(FLIGHT_PEOPLE_ON_BOARD)\',
+
     flight.people_onboard AS _tr(FLIGHT_PEOPLE_ON_BOARD),
−
     sexa2HoursMinute(flight.counter_departure) AS \'_tr(FLIGHT_COUNTER_DEPARTURE)\',
+
     sexa2HoursMinute(flight.counter_departure) AS _tr(FLIGHT_COUNTER_DEPARTURE),
−
     sexa2HoursMinute(flight.counter_arrival) AS \'_tr(FLIGHT_COUNTER_ARRIVAL)\',
+
     sexa2HoursMinute(flight.counter_arrival) AS _tr(FLIGHT_COUNTER_ARRIVAL),
−
     sexa2HoursMinute(flight.duration) AS \'_tr(DURATION_IN_HOURS_AND_MINUTES)\',
+
     sexa2HoursMinute(flight.duration) AS _tr(DURATION_IN_HOURS_AND_MINUTES),
−
     sexa2HoursHundredths(flight.duration) AS \'_tr(DURATION_IN_HOURS_AND_HUNDREDTHS)\',',
+
     sexa2HoursHundredths(flight.duration) AS _tr(DURATION_IN_HOURS_AND_HUNDREDTHS),',
 
     IFNULL(GROUP_CONCAT(
 
     IFNULL(GROUP_CONCAT(
 
         CONCAT(
 
         CONCAT(
Line 1,289: Line 1,316:
 
         RIGHT JOIN account ON (account.id=account_entry.account_id AND (account.category = 11 OR account.category = 2))
 
         RIGHT JOIN account ON (account.id=account_entry.account_id AND (account.category = 11 OR account.category = 2))
 
         WHERE account_entry.flow_id = flight_account_entry.account_entry_id
 
         WHERE account_entry.flow_id = flight_account_entry.account_entry_id
−
     )  AS \'_tr(AMOUNT)\'
+
     )  AS _tr(AMOUNT)
 
FROM flight
 
FROM flight
 
LEFT JOIN resource ON resource.id = flight.aircraft_id
 
LEFT JOIN resource ON resource.id = flight.aircraft_id
 
LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
 
LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
−
WHERE flight.start_date >= '$startDate' AND flight.start_date <= '$endDate'
+
WHERE flight.start_date >= $startDate AND flight.start_date <= $endDate
 
ORDER BY flight.start_date;
 
ORDER BY flight.start_date;
 
'
 
'
 
)
 
)
 
FROM business_field
 
FROM business_field
−
WHERE business_field.category='FLIGHT'</sql>
+
WHERE business_field.category='FLIGHT' </sql>
  
 
==Flight log book==
 
==Flight log book==
Line 1,354: Line 1,381:
 
LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
 
LEFT JOIN flight_pilot ON flight_pilot.flight_id = flight.id
 
LEFT JOIN person ON person.id = flight_pilot.pilot_id
 
LEFT JOIN person ON person.id = flight_pilot.pilot_id
−
WHERE flight_pilot.num = 0 AND( flight.departure_icao_id LIKE '$icao' OR flight.arrival_icao_id LIKE '$icao' )
+
WHERE flight_pilot.num = 0 AND( flight.departure_icao_id LIKE $icao OR flight.arrival_icao_id LIKE $icao )
−
GROUP BY flight.id</sql>
+
GROUP BY flight.id </sql>
  
 
==Flights with mechanic remark==
 
==Flights with mechan