Difference between revisions of "Export generator 4"

Jump to: navigation, search
(Undo revision 9007 by Csaccoccio (talk))
(→‎Booking)
 
(66 intermediate revisions by 3 users not shown)
Line 52: Line 52:
 
Example with last entry from variable #1 in variable_value table:
 
Example with last entry from variable #1 in variable_value table:
 
<sql>SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC  LIMIT 1</sql>
 
<sql>SELECT * FROM `variable_value` WHERE variable_id=1 ORDER BY start_date DESC  LIMIT 1</sql>
 +
 +
==Return a user list telling if each user has a profile A, B, C, etc.==
 +
<sql>SELECT
 +
    person.first_name,
 +
    person.last_name,
 +
    IF(person.profile & 1, 'Yes', 'No') AS 'Profile A',
 +
    IF(person.profile & 2, 'Yes', 'No') AS 'Profile B',
 +
    IF(person.profile & 4, 'Yes', 'No') AS 'Profile C'
 +
FROM person
 +
WHERE activated=1;</sql>
 +
 +
==Return the whole content of a given table==
 +
Example with table "profile"
 +
<sql>SELECT * FROM profile;</sql>
 +
 +
This type of SELECT does not work within OpenFlyers to access restricted table like the '''person''' table
  
 
==Test valid entries==
 
==Test valid entries==
Line 92: Line 108:
 
         WHERE YEAR(flight.start_date)=$year
 
         WHERE YEAR(flight.start_date)=$year
 
           AND flight.airborne=0
 
           AND flight.airborne=0
−
           AND ( activity_type.id IN ('$activityTypeId') OR '-' IN ('$activityTypeId') )
+
           AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) )
 
     ) AS flightWithActivityType
 
     ) AS flightWithActivityType
 
     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1)
 
     LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flightWithActivityType.id AND flight_pilot.num=1)
Line 99: Line 115:
 
         FROM person
 
         FROM person
 
         LEFT JOIN profile ON person.profile&profile.id
 
         LEFT JOIN profile ON person.profile&profile.id
−
         WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) )
+
         WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
 
           AND person.activated=1
 
           AND person.activated=1
 
         GROUP BY person.id
 
         GROUP BY person.id
Line 133: Line 149:
 
         WHERE YEAR(flight.start_date)=$year
 
         WHERE YEAR(flight.start_date)=$year
 
           AND flight.airborne=0
 
           AND flight.airborne=0
−
           AND ( activity_type.id IN ('$activityTypeId') OR '-' IN ('$activityTypeId') )
+
           AND ( activity_type.id IN ($activityTypeId) OR '' IN ($activityTypeId) )
 
           AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1)
 
           AND flight.id NOT IN (SELECT flight_id FROM flight_pilot WHERE num=1)
 
     ) AS flightWithActivityType
 
     ) AS flightWithActivityType
Line 141: Line 157:
 
         FROM person
 
         FROM person
 
         LEFT JOIN profile ON person.profile&profile.id
 
         LEFT JOIN profile ON person.profile&profile.id
−
         WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) )
+
         WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
 
           AND person.activated=1
 
           AND person.activated=1
 
         GROUP BY person.id
 
         GROUP BY person.id
Line 162: Line 178:
 
   AND person.activated=1</sql>
 
   AND person.activated=1</sql>
  
−
==Number of movements on based airfield==
+
==Number of landings at the base airfield==
 +
<sql>SELECT only_base.sum AS _tr(FLIGHT_LANDING_NUMBER_WITH_TAKEOFF_AND_LANDING_AT_THE_BASE), all_landing.sum AS _tr(REPORT_NUMBER_LANDING_BASED_AIRFIELD) FROM
 +
(SELECT SUM(flight.landing_number) AS sum
 +
FROM flight, structure
 +
WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year) AS only_base,
  
 +
(SELECT SUM(flight.landing_number) AS sum
 +
FROM flight, structure
 +
WHERE ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year) AS all_landing;</sql>
 +
 +
==Number of movements on based airfield==
 
* Variable '''$year''' should be defined first and should be of '''Year''' value type.
 
* Variable '''$year''' should be defined first and should be of '''Year''' value type.
  
Line 191: Line 216:
 
         FROM person
 
         FROM person
 
         LEFT JOIN profile ON person.profile&profile.id
 
         LEFT JOIN profile ON person.profile&profile.id
−
         WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) )
+
         WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
 
           AND person.activated=1
 
           AND person.activated=1
 
         GROUP BY person.id
 
         GROUP BY person.id
Line 211: Line 236:
 
         FROM person
 
         FROM person
 
         LEFT JOIN profile ON person.profile&profile.id
 
         LEFT JOIN profile ON person.profile&profile.id
−
         WHERE ( profile.id IN ($profileId) OR '-' IN ($profileId) )
+
         WHERE ( profile.id IN ($profileId) OR '' IN ($profileId) )
 
           AND person.activated=1
 
           AND person.activated=1
 
         GROUP BY person.id
 
         GROUP BY person.id
Line 220: Line 245:
 
     AND personWithProfile.id IS NOT NULL
 
     AND personWithProfile.id IS NOT NULL
 
  ) AS tmp_movement</sql>
 
  ) AS tmp_movement</sql>
−
 
−
==Number of take-off and landings on based airfield==
 
−
 
−
<SQL>SELECT SUM(flight.landing_number) * 2 AS nb_mouvement
 
−
FROM flight, structure
 
−
WHERE ( flight.departure_icao_id = structure.icao ) AND ( flight.arrival_icao_id = structure.icao ) AND YEAR(start_date) = $year </SQL>
 
  
 
==Number of take-off and landings on based airfield on specific profile==
 
==Number of take-off and landings on based airfield on specific profile==
Line 238: Line 257:
 
     SELECT person.*
 
     SELECT person.*
 
     FROM person
 
     FROM person
−
     LEFT JOIN PROFILE ON person.profile & PROFILE.id
+
     LEFT JOIN profile ON person.profile & profile.id
−
     WHERE (PROFILE.id IN($profileId) OR '-' IN($profileId)) AND person.activated = 1
+
     WHERE (profile.id IN($profileId) OR '-' IN($profileId)) AND person.activated = 1
 
     GROUP BY person.id
 
     GROUP BY person.id
 
) AS personWithProfile ON ( personWithProfile.id = flight_pilot.pilot_id AND flight_pilot.num = 0 )
 
) AS personWithProfile ON ( personWithProfile.id = flight_pilot.pilot_id AND flight_pilot.num = 0 )
Line 252: Line 271:
 
ORDER BY nb_flight DESC </sql>
 
ORDER BY nb_flight DESC </sql>
  
−
==Pilots who have flown without required validity==
+
==User flying without validity==
  
−
<sql>SELECT CONCAT(person.last_name, ' ', person.first_name) AS pilot, validity_type.name AS 'Validity'
+
<sql>SELECT
 +
    CONCAT(person.last_name, ' ', person.first_name) AS _tr(PILOT),
 +
    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 264: Line 285:
 
LEFT JOIN person ON person.id = flight_pilot.pilot_id
 
LEFT JOIN person ON person.id = flight_pilot.pilot_id
 
LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id)
 
LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id)
−
LEFT JOIN validity ON (validity_type.id=validity.validity_type_id AND person.id=validity.person_id)
+
LEFT JOIN validity ON validity_type.id = validity.validity_type_id AND person.id = validity.person_id AND validity.is_current_validity = 1
 
WHERE flight.airborne = 0
 
WHERE flight.airborne = 0
 
   AND validity_type.id IS NOT NULL
 
   AND validity_type.id IS NOT NULL
 
   AND validity_type.experience_formula IS NULL
 
   AND validity_type.experience_formula IS NULL
 
   AND validity.validity_type_id IS NULL
 
   AND validity.validity_type_id IS NULL
−
   AND person.activated=1
+
   AND person.activated = 1
 +
  AND validity_type.activated = 1
 
GROUP BY person.id, validity_type.id
 
GROUP BY person.id, validity_type.id
−
ORDER BY pilot, validity_type.name</sql>
+
ORDER BY `_tr(PILOT)`, validity_type.name</sql>
  
 
==List of pilots who have flown less than X hours during last Y days==
 
==List of pilots who have flown less than X hours during last Y days==
Line 321: 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
 
)
 
)
 
ORDER BY person.last_name, person.first_name</sql>
 
ORDER BY person.last_name, person.first_name</sql>
  
−
==Number of men, women over and under X years for profile P, validity V up to date for year A==
+
==Total user per age profile validity: Number of men, women over and under X years for profile P, validity V up to date for year A==
 
* Variable '''$age''' should be of '''integer''' value type.
 
* Variable '''$age''' should be of '''integer''' value type.
 
* Variable '''$profileId''' should be of '''dbOjectMulti::Profile''' value type.
 
* Variable '''$profileId''' should be of '''dbOjectMulti::Profile''' value type.
Line 333: Line 355:
  
 
<sql>SELECT
 
<sql>SELECT
−
     IF( personWithProfile.sex=0, 'Male', IF( personWithProfile.sex=1, 'Female', 'Undefined') ) AS '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' ) , 'Yes', 'No') AS 'Adult',
+
     IF( ( $year - YEAR(birthdate) >= $age ) , _tr(YES), _tr(NO)) AS _tr(ADULT),
−
     COUNT(DISTINCT personWithProfile.id) AS NUMBER   
+
     COUNT(DISTINCT personWithProfile.id) AS _tr(NUMBER)  
 
FROM (
 
FROM (
 
     SELECT person.*
 
     SELECT person.*
Line 343: Line 365:
 
       AND person.activated=1
 
       AND person.activated=1
 
) AS personWithProfile
 
) AS personWithProfile
−
LEFT JOIN validity ON (personWithProfile.id=validity.person_id)
+
LEFT JOIN validity ON personWithProfile.id = validity.person_id AND validity.is_current_validity = 1
 
LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
 
LEFT JOIN validity_type ON (validity_type.id=validity.validity_type_id)
−
WHERE (validity_type.id IN ($validityTypeId) AND validity.expire_date >= '$year-12-31')
+
WHERE ((validity_type.id IN ($validityTypeId) AND validity.expire_date >= '$year-12-31') OR '-' IN ($validityTypeId))
−
      OR '-' IN ($validityTypeId)
+
  AND validity_type.activated = 1
−
GROUP BY Sex, Adult</sql>
+
GROUP BY `_tr(SEX)`, `_tr(ADULT)`</sql>
  
 
==Number of landings per pilot, per resource==
 
==Number of landings per pilot, per resource==
Line 426: Line 448:
  
 
==Flight hours without up to date validities==
 
==Flight hours without up to date validities==
−
<sql>SELECT DATE_FORMAT(start_date, '%d %m %Y' ) AS '_tr(DATE)', last_name AS '_tr(LAST_NAME)', first_name AS '_tr(FIRST_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,
+
*Variable '''$year'''
−
      resource.name AS '_tr(RESOURCE)',
+
 
−
      sexa2HoursMinute(duration) AS '_tr(DURATION)',
+
<sql>SELECT
−
      activity_type.name AS '_tr(ACTIVITY_TYPE)',
+
    DATE_FORMAT(start_date, '%d %m %Y' ) AS _tr(DATE),
−
      validity_type.name AS '_tr(VALIDITY)'
+
    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,
 +
    resource.name AS _tr(RESOURCE),
 +
    sexa2HoursMinute(duration) AS _tr(DURATION),
 +
    activity_type.name AS _tr(ACTIVITY_TYPE),
 +
    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 437: Line 464:
 
LEFT JOIN person ON flight_pilot.pilot_id = person.id
 
LEFT JOIN person ON flight_pilot.pilot_id = person.id
 
LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id
 
LEFT JOIN validity_type ON flight_type_mandatory_validity_type.validity_type_id = validity_type.id
−
LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id
+
LEFT JOIN validity ON flight_type_mandatory_validity_type.validity_type_id = validity.validity_type_id AND validity.is_current_validity = 1
 
LEFT JOIN resource ON flight.aircraft_id = resource.id
 
LEFT JOIN resource ON flight.aircraft_id = resource.id
 
LEFT JOIN activity_type ON flight_type_mandatory_validity_type.activity_type_id = activity_type.id
 
LEFT JOIN activity_type ON flight_type_mandatory_validity_type.activity_type_id = activity_type.id
Line 445: Line 472:
 
   AND validity.person_id = flight_pilot.pilot_id
 
   AND validity.person_id = flight_pilot.pilot_id
 
   AND flight.start_date > validity.expire_date
 
   AND flight.start_date > validity.expire_date
−
   AND person.activated=1
+
   AND person.activated = 1
−
ORDER BY flight.start_date, person.last_name, validity_type.name</sql>
+
  AND validity_type.activated = 1
 +
ORDER BY flight.start_date, `_tr(FULL_NAME)`, validity_type.name</sql>
  
 
==Number of men, women over and under 21 years for profile X, validity Y up to date for year Z==
 
==Number of men, women over and under 21 years for profile X, validity Y up to date for year Z==
Line 475: Line 503:
  
 
<sql>SELECT tmp_flight.activity_type_name AS name,
 
<sql>SELECT tmp_flight.activity_type_name AS name,
−
       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS '_tr(AIRCRAFT_CATEGORY)',
+
       sexa2HoursMinute(SUM(IF(category=1, duration, 0))) AS _tr(AIRCRAFT_CATEGORY),
−
       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS '_tr(HELICOPTER_CATEGORY)',
+
       sexa2HoursMinute(SUM(IF(category=18, duration, 0))) AS _tr(HELICOPTER_CATEGORY),
−
       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS '_tr(GLIDER_CATEGORY)',
+
       sexa2HoursMinute(SUM(IF(category=19, duration, 0))) AS _tr(GLIDER_CATEGORY),
−
       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS '_tr(ULTRA_LIGHT_CATEGORY)',
+
       sexa2HoursMinute(SUM(IF(category=17, duration, 0))) AS _tr(ULTRA_LIGHT_CATEGORY),
−
       sexa2HoursMinute(SUM(IF(category=9, duration, 0))) AS '_tr(CLASSROOM_CATEGORY)'
+
       sexa2HoursMinute(SUM(IF(category=9, duration, 0))) AS _tr(CLASSROOM_CATEGORY)
 
FROM (
 
FROM (
 
     SELECT
 
     SELECT
Line 491: Line 519:
 
         LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
 
         LEFT JOIN activity_type ON flight.activity_type_id&activity_type.id
 
         LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1)
 
         LEFT JOIN flight_pilot ON (flight_pilot.flight_id=flight.id AND flight_pilot.num=1)
−
         WHERE YEAR(flight.start_date)='$year'
+
         WHERE YEAR(flight.start_date)=$year
−
           AND flight.airborne=0 AND flight_pilot.pilot_id='$personId'
+
           AND flight.airborne=0 AND flight_pilot.pilot_id=$personId
 
     ) AS flightWithActivityType
 
     ) AS flightWithActivityType
 
     LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
 
     LEFT JOIN resource ON (resource.id=flightWithActivityType.aircraft_id)
Line 499: Line 527:
 
GROUP BY tmp_flight.activity_type_id</sql>
 
GROUP BY tmp_flight.activity_type_id</sql>
  
−
==Validities in the year : Total of youngs/adults with specific profile==
+
==Validity year young specific profile: Total of youngs/adults with specific profile==
  
 
* Variable '''$profileId''' should be defined first and should be of '''dbOjectMulti::Profile''' value type.
 
* Variable '''$profileId''' should be defined first and should be of '''dbOjectMulti::Profile''' value type.
Line 505: Line 533:
 
* Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need.
 
* Note: "21" is the limit of age to categorize a young and adult person. Change it according to your need.
  
−
<sql>SELECT validity_type.name AS 'Validity',
+
<sql>SELECT
−
      SUM( IF( ( $year - YEAR(personWithProfile.birthdate) < 21 ), 1, 0 ) ) AS 'Young',
+
    validity_type.name AS '_tr(VALIDITY)',
−
      SUM( IF( ( $year - YEAR(personWithProfile.birthdate) >= 21 ), 1, 0 ) ) AS 'Adult'
+
    SUM( IF( ( $year - YEAR(personWithProfile.birthdate) < 21 ), 1, 0 ) ) AS '_tr(YOUNG)',
 +
    SUM( IF( ( $year - YEAR(personWithProfile.birthdate) >= 21 ), 1, 0 ) ) AS '_tr(ADULT)'
 
FROM validity_type
 
FROM validity_type
−
LEFT JOIN validity ON (validity.validity_type_id=validity_type.id)
+
LEFT JOIN validity ON validity.validity_type_id = validity_type.id AND validity.is_current_validity = 1
 
LEFT JOIN (
 
LEFT JOIN (
 
     SELECT person.*
 
     SELECT person.*
Line 519: Line 548:
 
) AS personWithProfile ON (validity.person_id=personWithProfile.id)
 
) AS personWithProfile ON (validity.person_id=personWithProfile.id)
 
WHERE YEAR(validity.grant_date)=$year
 
WHERE YEAR(validity.grant_date)=$year
−
    AND personWithProfile.id IS NOT NULL
+
  AND personWithProfile.id IS NOT NULL
 +
  AND validity_type.activated = 1
 
GROUP BY validity_type.id
 
GROUP BY validity_type.id
 
ORDER BY validity_type.name</sql>
 
ORDER BY validity_type.name</sql>
Line 558: Line 588:
 
GROUP BY location.icao_name
 
GROUP BY location.icao_name
 
ORDER BY nb_visite DESC</sql>
 
ORDER BY nb_visite DESC</sql>
 +
 +
==Young from this year==
 +
 +
<sql>SELECT
 +
    CONCAT( '[LINK=index.php[QUESTION_MARK]menuAction=admin_add_modify_user&menuParameter=',person.id,'&menuParameterBis=last_name&menuParameter3=', LEFT(person.last_name, 1),']', person.id, '[/LINK]' ) AS _tr(ID),
 +
    person.first_name AS _tr(FIRST_NAME),
 +
    person.last_name AS _tr(LAST_NAME),
 +
    DATE_FORMAT(birthdate ,'%m-%d-%Y') AS _tr(USER_BIRTHDATE),
 +
    IF ( sex = 0, _tr(SEX_MALE), _tr(SEX_FEMALE) ) AS _tr(USER_SEX)
 +
FROM person
 +
WHERE ($year-YEAR(birthdate))<=$age AND activated=1</sql>
  
 
=[[Accounting-exports-4|Accounting]]=
 
=[[Accounting-exports-4|Accounting]]=
Line 570: Line 611:
 
<sql>[OF_DYNAMIC_SQL]
 
<sql>[OF_DYNAMIC_SQL]
 
SELECT CONCAT(
 
SELECT CONCAT(
−
'SELECT booking.id AS \'_tr(ID)\', DATE_FORMAT(booking.start_date, \'%Y-%m-%d %H:%i\') AS \'_tr(START_DATE)\', DATE_FORMAT(booking.end_date, \'%Y-%m-%d %H:%i\') AS \'_tr(END_DATE)\', resource.name AS \'_tr(RESOURCE)\',
+
'SELECT booking.id AS _tr(ID), DATE_FORMAT(booking.start_date, \'%Y-%m-%d %H:%i\') AS _tr(START_DATE), DATE_FORMAT(booking.end_date, \'%Y-%m-%d %H:%i\') AS _tr(END_DATE),
−
       left_booking_person.full_name AS \'_tr(LEFT_PLACE)\', right_booking_person.full_name AS \'_tr(RIGHT_PLACE)\', ',
+
    (
 +
        SELECT GROUP_CONCAT( activity_type.name SEPARATOR \', \')
 +
        FROM booking_activity_type
 +
        LEFT JOIN activity_type ON (booking_activity_type.activity_type_id = activity_type.id)
 +
        WHERE booking_activity_type.booking_id=booking.id
 +
    ) AS _tr(ACTIVITY_TYPE),
 +
resource.name AS _tr(RESOURCE),
 +
       left_booking_person.full_name AS _tr(LEFT_PLACE), right_booking_person.full_name AS _tr(RIGHT_PLACE), ',
 
IFNULL(GROUP_CONCAT(
 
IFNULL(GROUP_CONCAT(
 
         CONCAT(
 
         CONCAT(
 
             ' (SELECT business_field_content.content FROM business_field_content WHERE booking.id=business_field_content.category_id AND business_field_content.business_field_id=',
 
             ' (SELECT business_field_content.content FROM business_field_content WHERE booking.id=business_field_content.category_id AND business_field_content.business_field_id=',
 
             business_field.id,
 
             business_field.id,
−
             ') AS \'',
+
             ' LIMIT 1) AS \'',
 
             REPLACE(business_field.label, '\'', '\\\''),
 
             REPLACE(business_field.label, '\'', '\\\''),
 
             '\''
 
             '\''
Line 598: 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'
 
)
 
)
 
FROM business_field
 
FROM business_field
−
WHERE business_field.category='BOOKING' AND business_field.variable IN ('bookingEstimatedFlightTime', 'bookingComment')</sql>
+
WHERE business_field.category='BOOKING'
 +
ORDER BY business_field.order_num</sql>
  
 
==Cumulated maintenance hours on a period==
 
==Cumulated maintenance hours on a period==
Line 614: Line 663:
 
*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 628: Line 677:
 
   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 639: Line 688:
 
*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 653: Line 702:
 
   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 685: Line 734:
 
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 738: Line 787:
 
* 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
</