Difference between revisions of "Export generator 4"
(→Flight hours total per person) |
(→Flights with mechanic remark) |
||
| (19 intermediate revisions by the same user 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 ( | + | 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 ( | + | 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 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( | + | 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, | + | 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) >= | + | IF( ( $year - YEAR(birthdate) >= $age ) , _tr(YES), _tr(NO)) AS _tr(ADULT), |
| − | COUNT(DISTINCT personWithProfile.id) AS | + | COUNT(DISTINCT personWithProfile.id) AS _tr(NUMBER) |
FROM ( | FROM ( | ||
SELECT person.* | SELECT person.* | ||
| Line 573: | Line 573: | ||
<sql>SELECT | <sql>SELECT | ||
| − | CONCAT( '[LINK=index.php | + | 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 | + | person.first_name AS _tr(FIRST_NAME), |
| − | person.last_name AS | + | person.last_name AS _tr(LAST_NAME), |
| − | DATE_FORMAT(birthdate ,'%m-%d-%Y') AS | + | DATE_FORMAT(birthdate ,'%m-%d-%Y') AS _tr(USER_BIRTHDATE), |
| − | IF ( sex = 0, | + | 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 592: | ||
<sql>[OF_DYNAMIC_SQL] | <sql>[OF_DYNAMIC_SQL] | ||
SELECT CONCAT( | SELECT CONCAT( | ||
| − | 'SELECT booking.id AS | + | '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), |
| − | left_booking_person.full_name AS | + | 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 620: | ||
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 >= | + | WHERE booking.start_date >= $startDate AND booking.end_date < $endDate |
| − | AND (booking_resource.resource_id= | + | AND (booking_resource.resource_id=$resourceId OR \'\'=$resourceId) |
AND ( | AND ( | ||
| − | left_booking_person.person_id= | + | 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 636: | ||
*endDate (Type : Date or DateTime) | *endDate (Type : Date or DateTime) | ||
| − | <sql>SELECT IF(LEAST( | + | <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 650: | ||
AND (log.table_name='booking') | AND (log.table_name='booking') | ||
AND (log.field_name='id') | AND (log.field_name='id') | ||
| − | AND (booking.start_date >= | + | AND (booking.start_date >= $startDate) |
| − | AND (booking.start_date <= LEAST( | + | 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 661: | ||
*endDate (Type : Date or DateTime) | *endDate (Type : Date or DateTime) | ||
| − | <sql>SELECT IF(LEAST( | + | <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 675: | ||
AND (log.TABLE_NAME='booking') | AND (log.TABLE_NAME='booking') | ||
AND (log.field_name='id') | AND (log.field_name='id') | ||
| − | AND (booking.start_date >= | + | AND (booking.start_date >= $startDate) |
| − | AND (booking.start_date <= LEAST( | + | AND (booking.start_date <= LEAST($startDate + INTERVAL 366 DAY, $endDate)) |
ORDER BY 1, 2</sql> | ORDER BY 1, 2</sql> | ||
| Line 707: | Line 707: | ||
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 <= | + | WHERE flight.start_date <= $endDate |
AND resource.activated=1 | AND resource.activated=1 | ||
GROUP BY resource.id | GROUP BY resource.id | ||
| Line 778: | Line 778: | ||
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 & | + | AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId) |
GROUP BY resource.id | GROUP BY resource.id | ||
UNION | UNION | ||
| Line 799: | Line 799: | ||
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 & | + | AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId) |
UNION | UNION | ||
SELECT "Cumulative","per month", | SELECT "Cumulative","per month", | ||
| Line 819: | Line 819: | ||
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 & | + | AND (flight.activity_type_id & $activityTypeId OR ''=$activityTypeId)</sql> |
==Flight hours total per pilot== | ==Flight hours total per pilot== | ||
| Line 890: | Line 890: | ||
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 | + | <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 | + | 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 | + | 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((( | + | AND (IF((($occupiedSeat=0) OR ($occupiedSeat='')), 0, -1) = flight_pilot.num |
| − | OR IF((( | + | 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 903: | ||
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 | + | 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 911: | ||
*activityTypeId (Type : dbObject::FlightType) | *activityTypeId (Type : dbObject::FlightType) | ||
<sql>SELECT | <sql>SELECT | ||
| − | CONCAT(UPPER(person.last_name), ' ', person.first_name) AS | + | 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 & | + | (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 & | + | (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 & | + | (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 & | + | (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 & | + | (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 & | + | (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 & | + | (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 & | + | (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 & | + | (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 & | + | (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 & | + | (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 & | + | (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 | + | 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 932: | Line 932: | ||
AND fp.num = 1 | AND fp.num = 1 | ||
AND YEAR( start_date ) = $year | AND YEAR( start_date ) = $year | ||
| − | AND ( flight.activity_type_id & | + | AND ( flight.activity_type_id & $activityTypeId OR ''=$activityTypeId ) |
GROUP BY person.id</sql> | GROUP BY person.id</sql> | ||
| Line 1,168: | Line 1,168: | ||
<sql>[OF_DYNAMIC_SQL] | <sql>[OF_DYNAMIC_SQL] | ||
SELECT CONCAT( | SELECT CONCAT( | ||
| − | 'SELECT resource.name AS | + | 'SELECT resource.name AS _tr(RESOURCE_NAME), ', |
GROUP_CONCAT( | GROUP_CONCAT( | ||
CONCAT( | CONCAT( | ||
| Line 1,178: | Line 1,178: | ||
) | ) | ||
), | ), | ||
| − | ', sexa2HoursMinute( SUM( tmp_flight.duration ) ) AS | + | ', sexa2HoursMinute( SUM( tmp_flight.duration ) ) AS _tr(ALL_ACTIVITY_TYPES) |
FROM resource | FROM resource | ||
INNER JOIN | INNER JOIN | ||
| Line 1,184: | Line 1,184: | ||
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 >= | + | 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,228: | ||
SELECT CONCAT( | SELECT CONCAT( | ||
'SELECT | 'SELECT | ||
| − | flight.id AS | + | flight.id AS _tr(ID), |
| − | IF(flight.validated=1, | + | IF(flight.validated=1, _tr(YES), _tr(NO)) AS _tr(VALIDATED), |
| − | flight.start_date AS | + | flight.start_date AS _tr(START_DATE), |
| − | resource.name AS | + | 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,237: | ||
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 | + | ) 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,243: | ||
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 | + | ) AS _tr(RIGHT_PLACE), |
( | ( | ||
SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \') | SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \') | ||
| Line 1,249: | Line 1,249: | ||
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 | + | ) 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 | + | ) 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 | + | ) AS _tr(ARRIVAL), |
| − | flight.landing_number AS | + | flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER), |
| − | flight.people_onboard AS | + | flight.people_onboard AS _tr(FLIGHT_PEOPLE_ON_BOARD), |
| − | sexa2HoursMinute(flight.counter_departure) AS | + | sexa2HoursMinute(flight.counter_departure) AS _tr(FLIGHT_COUNTER_DEPARTURE), |
| − | sexa2HoursMinute(flight.counter_arrival) AS | + | sexa2HoursMinute(flight.counter_arrival) AS _tr(FLIGHT_COUNTER_ARRIVAL), |
| − | sexa2HoursMinute(flight.duration) AS | + | sexa2HoursMinute(flight.duration) AS _tr(DURATION_IN_HOURS_AND_MINUTES), |
| − | sexa2HoursHundredths(flight.duration) AS | + | sexa2HoursHundredths(flight.duration) AS _tr(DURATION_IN_HOURS_AND_HUNDREDTHS),', |
IFNULL(GROUP_CONCAT( | IFNULL(GROUP_CONCAT( | ||
CONCAT( | CONCAT( | ||
| Line 1,289: | Line 1,289: | ||
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 | + | ) 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 >= | + | 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,354: | ||
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 | + | 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 mechanic remark== | ||
| Line 1,365: | Line 1,365: | ||
<sql>SELECT CONCAT( | <sql>SELECT CONCAT( | ||
| − | '[LINK=index.php | + | '[LINK=index.php[QUESTION_MARK]menuAction=flight_record&menuParameter=', |
flight.id, | flight.id, | ||
'&menuParameterBis=flight_resource_logbook&menuParameter3=1]', | '&menuParameterBis=flight_resource_logbook&menuParameter3=1]', | ||
flight.start_date, | flight.start_date, | ||
'[/LINK]' | '[/LINK]' | ||
| − | ) AS | + | ) AS _tr(DATE), |
| − | resource.name AS | + | resource.name AS _tr(RESOURCE), |
| − | sexa2HoursMinute(flight.duration) AS | + | sexa2HoursMinute(flight.duration) AS _tr(DURATION), |
person.last_name AS Nom, | person.last_name AS Nom, | ||
| − | person.first_name AS | + | person.first_name AS _tr(FIRST_NAME), |
| − | departure_location.icao_name AS | + | departure_location.icao_name AS _tr(DEPARTURE), |
| − | arrival_location.icao_name AS | + | arrival_location.icao_name AS _tr(ARRIVAL), |
| − | maintenanceUserComment.content AS | + | maintenanceUserComment.content AS _tr(REMARK), |
| − | mechanicAnswer.content AS | + | mechanicAnswer.content AS _tr(ANSWER) |
FROM flight | FROM flight | ||
LEFT JOIN resource ON resource.id = flight.aircraft_id | LEFT JOIN resource ON resource.id = flight.aircraft_id | ||
| Line 1,392: | Line 1,392: | ||
AND ( maintenanceUserComment.content <> "" OR mechanicAnswer.content <> "" ) | AND ( maintenanceUserComment.content <> "" OR mechanicAnswer.content <> "" ) | ||
AND start_date > DATE_SUB( NOW(), INTERVAL $numberMonth MONTH) | AND start_date > DATE_SUB( NOW(), INTERVAL $numberMonth MONTH) | ||
| − | AND ( (resource.id = | + | AND ( (resource.id = $resourceId) OR($resourceId < '1') ) |
GROUP BY flight.id | GROUP BY flight.id | ||
| − | ORDER BY resource.name, start_date DESC </sql> | + | ORDER BY resource.name, start_date DESC </sql> |
==Flight with their location code (ICAO)== | ==Flight with their location code (ICAO)== | ||